Transcript Relational database schema and MS Access

ZEIT2301
Design of Information Systems
Relational Database Schema
School of Engineering and Information Technology
UNSW@ADFA
Dr Kathryn Merrick
Topic 09: Relational Database
Schema

Objectives



To study relational database schema in practice
To study the conversion between ER diagrams and
relational database schema
We will also start to look at MS Access today
Relational Data Model (Review)

Identifies entities, attributes and relationships.

Is the theoretical basis for Relational Database
Management Systems


Data is organised into tables (relations), with columns
(attributes) and rows (records)




Dominant model for data-processing in use in enterprises today
Can be accessed in any sorted order
Doesn’t have hidden pointers to connect entities - just uses data
Any entity can be connected to any other entity by using data
Relational model is therefore VERY flexible
3
A Relation (table)
Relation or table name
Attributes or columns
Row: an entity instance
4
Missing (Null) Values

Null means “no value/unknown”, and is not
the same as zero, blank or an empty string
Null
5
Primary Key

Every row in a table must be distinguishable
from every other row

Must have a PK
Primary Key
6
Primary Key
Primary key
(table in “design
view” in
MsAccess)
7
A Composite Key
Composite
key
Session 2, 2010
8
Foreign Keys


A foreign key (FK) is a “copy” of a primary key that has been
exported from one table and added as a new column in
another table to represent the relationship between them
A foreign key is a copy of the whole of its parent primary key


if the primary key is composite then so is the foreign key
Foreign keys are crucial in the relational model (and
consequently in relational databases)

They are the ‘glue’ that connects the relations in the database
9
Foreign Key - Example
Foreign Key
Textbook
Title
ISBN
0-201-34287-1
Database Systems
Database Processing 0-02-366881-4
Analysis and Design 0-202-36995-4
Edition Publisher ID
2
5
null
P091
P473
P091
Primary Key
Publisher
Primary Key Publisher ID
P091
P473
Name
Longman
University Press10
A Foreign Key in MSAccess
Primary key
of Lecturer
table
Primary key
of Course
table
Foreign key
NB. The name of the FK attribute does not need to match
the PK – but the data type should be the same.
11
Foreign Keys & Referential Integrity

A foreign key can only take on a value that matches a
valid value in its parent primary key, or (possibly) be null

A database in which all foreign keys contain such
matching values is said to exhibit referential integrity

Helps ensure database consistency if the DBMS enforces
referential integrity
Referential Integrity Constraint
Foreign Key
Textbook
Edition Publisher ID
ISBN
Title
0-201-34287-1
Database Systems
Database Processing 0-02-366881-4
Analysis and Design 0-202-36995-4
2
5
null
null
P473
P807
Violates referential integrity
Publisher
Primary Key Publisher ID
P091
P473
Name
Longman
University Press
Referential Integrity Constraint
Foreign Key
Course
Name
Level
Database Systems
Data Networks
Speech Processing
Undergrad
Undergrad
Postgrad
Primary Key
Note:
Foreign Key
attribute
Lecturer
name not
necessarily Primary Key Staff ID
the same as
91027
primary key
38421
Credits Lecturer ID
3
6
null
38421
null
38421
StaffName
Goscinski
Nguyen
14
Relation Schema
The Schema for a relation represents its structure.

Single table example:
Book (ISBN, Title, Edition, Pages)


where Book is the name of the relation and “ISBN, Title, Edition
and Pages” is the unordered list of its attributes
Primary Key is underlined and, by convention, shown first
Relational Database Schema

The complete design of a database is termed its schema

A Relational Database Schema consists of a number of
relation (tables)


Table attributes and PKs are listed
Foreign keys that link the tables, are identified either by arrows
(see next slide) or by a textual description of the links
16
A Relational Database Schema
primary key
lecturer (lecturerName, school, address, telephone, title)
referential integrity
link
contact (lecturerName, courseCode, hours)
table (relation) name
course (courseCode, courseName, lecturerName)
foreign key
enrol (studentID, courseCode, mark)
attribute name(s)
student (studentID, name, DOB, address, telephone, gender, degree)
17
Summary of key features of the
Relational Model

In the relational model data is stored in relations,
represented as tables with columns and rows.


Columns represent attributes
Rows represent entity instances

Primary Keys uniquely identify each row.

Foreign Keys provide the link between tables.
18
Converting ER diagrams to a
Relational Schema


Once an ER model has been developed it needs to be
converted into a “relational schema”

A relational schema is a specification of the required table definitions
and their foreign key links

The basis for design of a relational database
There are well-defined principles for converting from one to
the other
19
Conversion Rules for Entities

Each entity becomes a relation (table)

Each single-valued attribute of the entity becomes a column
(attribute) of the table representing the entity

Composite attributes are represented only by their components

Derived attributes are ignored (record in data dictionary)

ER key primary key
20
Entity Example
player
playerID {PK}
name
DOB
height
weight
gender
Relation/table:
player(playerID, name, DOB, height, weight, gender)
21
In-class exercise: Derive the
relational schema
Car
regoNo {PK}
make
model
year
engineCapacity
Solution
car
regoNo {PK}
make
model
year
engineCapacity
car (regoNo, make, model, year, engineCapacity)
In-class exercise: Derive the
relational schema
player
playerID {PK}
name
DOB
/age
address
street
suburb
postcode
Solution
player
Derived attribute ‘age’ not
included in relational
schema.
Composite attribute
‘address’ not included (only
component parts)
playerID {PK}
name
DOB
/age
address
street
suburb
postcode
player(playerID, name, DOB, street, suburb, postcode)
25
Multivalued Attributes


Multivalued attributes are not dealt with by having repeating
columns in the table.
That is:
person
personID {PK}
qualification [1..*]
……..
…….
should not be represented by:
person (... qual1, qual2, qual3, ... )
26
Multivalued Attributes


The correct way to represent multivalued attributes is with
another table
person
Example:
personID {PK}
qualification [1..*]
……..
…….
person (personID, name… , address… , ...etc...)
personQual (personID, qualification)
Note: Composite PK consists of PK from
original table plus the multi-valued attribute
27
In-class exercise: Derive the
relational schema
wine
wineID {PK}
wineName
year
producer
winemaker
type
prize [0..*]
tastingNote [1..*]
Solution
tastingNote (wineID, tastingNote)
wine (wineID wineName, year, producer, winemaker, type)
winePrize (wineID, prize)
Representing Relationships

How an ER relationship is represented depends on the degree
(unary, binary, ternary) of the relationship and its multiplicity

We consider binary relationships here

Three possible multiplicities are:



one-to-one
one-to-many
many-to-many
1:1
1:*
*:*
Note: * includes zero
1:1 Relationships

To represent a 1:1 relationship, a foreign key is migrated from
either relation into the other - but not both ways

Which direction is chosen generally depends on how the
connected entities participate in the relationship

If the relationship itself has attributes, those attributes are
included in the relation that contains the foreign key
1:1 Relationship – Schema
staff
staffID {PK}
name
department
0..1
1..1
is head of
deptName {PK}
location
Could be represented by the schema:
staff (staffID, name, ...etc... )
department (deptName, location, deptHead)
To implement the relationship, staffID is migrated to the
department relation as a FK called deptHead.
32
1:1 Relationships
The 1:1 relationship could also be represented by the schema:
staff (staffID, name, ..... , headOfDept)
department (deptName, ...etc...)
To implement the relationship, deptName is migrated
to the staff relation, as a FK called headOfDept.
The first option (previous slide) is better because all
departments have heads but few staff are heads of departments
1:* Relationships



Convert entities into relations (tables)
Include the primary key from the ‘one’ side relation as a foreign
key in the ‘many’ side relation
Include attributes of the relationship, if any, in the relation
containing the foreign key
34
1:* Relationship - schema
club
clubName {PK}
contactNo
team
1..*
1..1
has
teamName {PK}
grade
Migrate the PK from the one side of the
relationship as a Foreign Key in the many side.
club(clubName, contactNo)
team(teamName, grade, clubName)
35
*:* Relationships

Represented by a new table (often called an associative
relation)



New table contains two foreign keys - one from each of the
participants in the relationship
The PK of the new table is a composite of the two foreign keys
Attributes of the relationship, if any, become attributes of the
new table
*:* Relationship - schema
player
playerID {PK}
Name
DOB
….
team
0..*
1..*
playsIn
teamName {PK}
grade
player(playerID, name, DOB, ….. )
New table (from
*..* relationship) playsIn(playerID, teamName)
team(teamName, grade)
*:* Relationship with an attribute
mark
student
course
1..*
studentID {PK}
enrols In
1..*
*
courseCode {PK}
student (studentID, name, ...etc...)
New table (from
enrolment (studentID, courseCode, mark)
*..* relationship)
course (courseCode, name, ...etc...)
38
In-class exercise: Convert to
relational schema
yacht
yachtNo {PK}
name
length
breadth
depth
0..*
berth
0..*
tiedTo
0..1 jetty {PK}
berthNo {PK}
length
depth
ownedBy
member
1..*
memberNo {PK}
name
address
phoneNo
Session 2, 2010
39
In-class exercise
Step 1: Create tables for entities
yacht(yachtNo, name, length, breadth, depth, ….)
berth(jetty, berthNo, length, depth, ….)
member(memberNo, name, address, phoneNo, ….)
In-class exercise
Step 2: Implement 1:* relationship
yacht(yachtNo, yachtName, length, breadth, depth, jettyTied, berthTied)
berth(jetty, berthNo, length, depth, ….)
member(memberNo, name, address, phoneNo, ….)
In-class exercise
Step 3: implement *:* relationship
yacht(yachtNo, yachtName, length, breadth, depth, jettyTied, berthTied)
berth(jetty, berthNo, length, depth)
ownedBy(yachtNo, memberNo)
member(memberNo, name, address, phoneNo)