The Relational
Data Model
COMP9311 23T2; Week 2.1
By Zhengyi Yang, UNSW
1
Announcement
Lab 1 (week2) is released
Assignment 1 released: Due by Friday 23th June 16:59:59
Practice questions also released
2
Wk1 Content – Data Modelling
Checklist on ER modelling
1. Did you model every significant entity that has independent
instances?
2. Did you model the entity in the correct type? Strong entity or
weak entity?
3. Did you capture all the main relationships between entities?
4. Does every relationship have the correct cardinality
5. Did you correctly capture participation? is it too loose? Too
strict?
6. Is each attribute modelled with the most appropriate
attribute type?
7. (For comp9311) did you use the comp9311 notation?
3
Wk1 Content – Standard Notation
4
Introduction
Most popular data model for database systems (see wk1 Monday)
English computer scientist Edgar F. Codd
A Relational Model of Data for Large Shared Data Banks (1970)
https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
5
Conceptual
Schema
Logical Schema/ Relational
Database Schema
ER:
• Entities,
• Relationships
Relations:
• tuples,
• attributes
• domain
Application
Relational Data Model Concepts
The relational data model is the most widely used data model for
database systems.
The relational data model describes the world as
• a collection of inter-connected relations
Goal of relational model:
• a simple, general data modelling formalism
• which maps easily to file structures (i.e. implementable)
Relational model has two styles of terminology:
• mathematical: relation, tuple, attribute, ...
• data-oriented: table, record, field/column, ...
6
Structures
In the relational model, everything is described using
relations.
A relation can be thought of as a named table.
● Each column of the table corresponds to a named
attribute.
● Each row of the table is called a tuple of the relation.
The set of allowed values for an attribute is called its
domain.
7
Example of a Relation
Name Position Goals Age Height Weight
Heady Half-forward 17 24 183 83
Sumich Full-forward 59 26 191 92
Langdon Utility 23 23 189 86
attributes
(or columns)
tuples
(or rows)
8
Mathematically,
◦ a domain D is a set of atomic values (having some fixed data type) which represent
some semantic meaning.
◦ an attribute A is the name of a role played by a domain, dom(A).
◦ a relation schema R, denoted by R(A1,A2, ...,An), is a set of attributes R = {A1,A2,
...,An}.
9
Composite and multivalued attributes are not allowed!
Relational Data Model
Relations are Unordered
• Why is the order of tuples irrelevant?
• An unordered collection of elements is a set:
{1, 2, 3} = {2, 1, 3}.
• An ordered collection of elements is a list:
(1, 2, 3) ≠ (2, 1, 3).
• A set expresses membership.
◦ Example: we care you are a student, but we don’t care whether you’re the
6th student to register (the order).
10
Example of Unordered Relation
Both are the same relation. Ordering of column or rows are irrelevant.
11
PLAYER
Name Position Goals Age Height Weight
Heady Half-forward 17 24 183 83
Sumich Full-forward 59 26 191 92
Langdon Utility 23 23 189 86
PLAYER
Name Age Height Weight Goals Position
Sumich 26 191 92 59 Full-forward
Langdon 23 189 86 23 Utility
Heady 24 183 83 17 Half-forward
=
Question: Ordering within tuple?
Yes, but alternative definition exists which contains no ordering.
Why Relational Model?
● Very simple model
● Often a good match for the way we think about our data
● Foundations in logic and set theory (will introduce in later
parts of the course)
12
Keys are used to identify tuples in a relation.
A superkey is a set of attributes that uniquely determines a tuple.
A candidate key is a minimal superkey, i.e., none of whose subsets is a superkey.
13
Keys
Example
Assuming no two people have the same name, then {Name} is unique and
therefore is a candidate key for PLAYER
{Goals} usually cannot be a candidate key since different players might have the
same number of goals.
{Name, Goals} is a super key but not a minimal key (because {Name} is a key).
14
PLAYER
Name Position Goals Age Height Weight
Heady Half-forward 17 24 183 83
Sumich Full-forward 59 26 191 92
Langdon Utility 23 23 189 86
A primary key is a designated candidate key.
In many applications it is necessary to invent a primary key if there is no
natural one - often this would be a non-negative integer
e.g. Person_ID.
When a relation schema has several candidate keys, usually better to choose a
primary key with a single attribute or a small number of attributes.
15
PLAYER
Person_ID Name Position Goals Age Height Weight
1 Heady Half-forward 17 24 183 83
2 Sumich Full-forward 59 26 191 92
3 Langdon Utility 23 23 189 86
Keys
Relation Referring to Another Relation
How do we store relationships? For example, ENROLLMENT in this case?
16
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
Store the values of the Primary Key?
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Student Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
17
Relation Referring to Another Relation
Foreign key: an attribute that keeps the value of a primary key of
another relation.
A set of attributes from a relation schema R1 may be a foreign key, FK, if
◦ the attributes have the same domains as the attributes in the primary
key of another relation schema R2, and
◦ a value of FK in a tuple t1 of R1 either occurs as a value of PK for some
tuple t2 in R2 or is null.
Referential integrity: The value of FK must occur in the other relation or be
entirely NULL.
18
Example of Foreign keys
This is what we mean
19
Person# NamePerson# Name Department Degree
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Student Supervisor Department Degree
f.k. f.k.
f.k.
Relational Integrity Constraints
We need to keep the relational database in a valid state:
Three integrity constraints are important
1. Key constraint: candidate key values must be unique for every relation
instance.
2. Entity integrity: an attribute that is part of a primary key cannot be NULL.
3. Referential integrity
Valid state: a relation does not violate any integrity constraints.
Invalid state: a relation violates at least one integrity constraint
20
Relational Integrity Constraints
How can a valid relation can it ever become invalid?
A: Operations on the database can result in an invalid state.
Before proceeding with an update, we need to…
• check that the result of the update will not be violate any integrity
constraints.
21
Insertions: When inserting, we need to check
◦ that the candidate keys are not already present,
◦ that the value of each foreign key either
– is all NULL, or
– is all non-NULL and occurs in the referenced relation.
22
Insertions
Insertion: Key constraint violation
23
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
1. Insert < 2, Dr.V.Ciesielski > into RESEARCHER

Insertion: Key constraint violation
24
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
1. Insert < 2, Dr.V.Ciesielski > into RESEARCHER
Allowed? No. Violates a key constraint.
Action? Reject or allow the user to correct.
Insertion: Entity integrity violation
25
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
2. Insert < Comp.Sci.,NULL > into COURSE
Insertion: Entity integrity violation
26
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
2. Insert < Comp.Sci.,NULL > into COURSE
Allowed? No. Violates the entity integrity constraint.
Action: Reject or correct.
Insertion: Referential integrity violation
27
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
3. Insert < 5, 6, 2, Psychology, Ph.D. > into ENROLMENT

Insertion: Referential integrity violation
28
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
Person# Name
1 Dr C.C.Chen
3 Ms K.Juliff
4 Ms J.Gledill
5 Ms B.K.Lee
Department Degree
Psychology Ph.D.
Comp.Sci. Ph.D.
Comp.Sci. M.Sc.
Psychology M.Sc.
STUDENT: RESEARCHER: COURSE:
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
3. Insert < 5, 6, 2, Psychology, Ph.D. > into ENROLMENT
Allowed? No. Violates a referential integrity constraint (There is no person 6).
Action: Reject, correct or accept after insertion of person number
Deletion
Deletions: When deleting, we need to check referential integrity
– check whether the primary key occurs in another relation.
Example: Delete tuple with Person# = 2 from RESEARCHER
Allowed? No. Violates the referential integrity.
Action: Reject, correct or modify the ENROLMENT tuple by the
actions on the next slides.
29
RESEARCHER:
Person# Name
1 Dr C.C.Chen
2 Dr R.G.Wilkinson
ENROLMENT:
Enrolment# Supervisee Supervisor Department Degree
1 1 2 Psychology Ph.D.
2 3 1 Comp.Sci. Ph.D.
3 4 1 Comp.Sci. M.Sc.
4 5 1 Comp.Sci. M.Sc.
Deletion: Constraint Checks
We do need to delete tuples from relations sometimes, and it’s possible
for the record to referred in other relations.
What can we do?
1. Delete it (this requires another integrity check, possibly causing
a cascade of deletions), or
2. Set foreign key value to NULL (note this can’t be done if it is
part of a primary key) or other values
30
Modifications
Can changing a value lead to an invalid state? Not unless you’re
modifying the value of a key.
If the modified attribute is primary key
• the same issues as deleting PK1 and then immediately
inserting PK2.
• make sure deletion and insertion don’t violate any steps.
if the modified attribute is foreign key
• check that the new value refers to an existing tuple.

Note: all relational integrity constraints are to do with the key values.
31
Break 15 min
2 hours of talking wasn’t as easy as 2 hours of listening.
Next: ER to Relational Schema
32
◦A relational database schema, is a set of relation schema {R1, . .
. ,Rm} and a set of integrity constraints.
◦A relational database instance is a set of relation instances {r1, .
. . , rm} such that each ri is an instance of Ri , and the integrity
constraints are satisfied.
33
Relational database definition
ER to Relational Data Model Mapping
One technique for database design is to first design a conceptual schema
using a high-level data model, and then map it to a conceptual schema in
the DBMS data model for the chosen DBMS.
Here we look at a way to do this mapping from the ER to the relational
data model.
It involves the following 7 steps.
34
Mapping ER to Relational: Guiding Example
35
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping Strong Entity Types
Step 1 : For each strong entity (not weak entity) type E,
create a new relation R with
• Attributes : all simple attributes (and simple components
of composite attributes) of E.
• Key : key of E as the primary key for the relation.
36
37
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping Strong Entity Types
Mapping Strong Entity Types
38
SSN Fname Lname Birdate
Name Location
Pnumber Pname
Employee
Department
Project
Mapping Strong Entity Types
Mapping Weak Entity Types
Step 2 : For each weak entity type W with the owner entity
type E, create a new relation R with
– Attributes :
– all simple attributes (and simple components of
composite attributes) of W,
– and include the primary key attributes of the relation
derived from E as the foreign key.
– Key of R: foreign key to E and partial key of W.
39
40
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping Weak Entity Types
Mapping Weak Entity Types
41
SSN Fname Lname Birdate
Name Location
Pnumber Pname
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
Mapping 1:1 Relationship Types
Step 3 : For each 1:1 relationship type B. Let E and F be the
participating entity types. Let S and T be the corresponding
relations.
– Choose one of S and T (let S be the one that participates
totally if there is one).
– Add attributes from the primary key of T to S as a foreign
key.
– Add all simple attributes (and simple components of
composite attributes) of B as attributes of S.
(Alternatively: merge the two entity types and the relationship into a single
relation, especially if both participate totally and do not participate in other
relationships).
42
43
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping 1:1 Relationship Types
Mapping 1:1 Relationship Types
44
SSN Fname Lname Birdate
Name Location MSSN Mdate
Pnumber Pname
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
Mapping 1:N Relationship Types
Step 4 : For each 1:N relationship type B. Let E and F be the
participating entity types. Let S and T be the corresponding
relations. Let E be the entity on the 1 side and F on the N
side.
Add to the relation belonging to entity T,
– the attributes from the primary key of S as a foreign key.
– any simple attributes (or simple components of
composite attributes) from relationship B.
(Notice that this doesn’t add any new tuples, just attributes.)
45
46
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping 1:N Relationship Types
Mapping 1:N Relationship Types
47
SSN Fname Lname Birdate Dname
Name Location MSSN Mdate
Pnumber Pname
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
Mapping M:N Relationship Types
Step 5 : For each N:M relationship type B. Let E and F be the
participating entity types. Let S and T be the corresponding
relations
Create a new relation R (cross-reference) with
Attributes :
– Attributes from the key of S as foreign key,
– And attributes from the key of T as foreign key,
– And simple attributes, and simple components of
composite attributes of relation B.
Key : All attributes from the key of S and the key of T.
48
49
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping M:N Relationship Types
50
SSN Fname Lname Birdate Dname
Name Location MSSN Mdate
Pnumber Pname
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
Pnumber ESSN Time
Participation
Mapping M:N Relationship Types
Mapping M:N Relationship Types
Mapping Multivalued Attributes
Step 6 : For each multivalued attribute A, where A is an
attribute of E, create a new relation R.
• If A is a multivalued simple attribute,
• Attributes of R = Simple attribute A, and key of E as a
foreign key.
• If A is a multivalued composite attribute,
• Attributes of R = All simple components of A, and key
of E as a foreign key.
In both cases, the primary key of R is the set of all attributes
in R.
51
52
SSN
SSN
Birdate
Fname
Lname
Employee
Works_For
Family
Dependent
Fname- - - - - Birdate Relation
Mdate
Manager
Participation
Time
Phone
Name Location
Department
Project
Pname Pnumber
m
m
m
n
1
11
1
Mapping Multivalued Attributes
53
SSN Fname Lname Birdate Dname
Name Location MSSN Msdate
Pnumber Pname
SSN Fname Birdate Relation
Pnumber ESSN Time
Dname Dnumber
Employee
Department
Project
Dependent
Participation
D_number
Mapping Multivalued Attributes
Mapping N-ary Relationship Types
Step 7 : For each n-ary relationship type (n > 2), create a
new relation with
◦ Attributes : same for Step 5.
◦ Key :
◦ same for Step 5, see exception below
◦ The exception is that that if one of the participating
entity types has participation ratio 1, its key may be
used as part of the key to the new relation.
(Advice: binary relationships are simpler to model)
54
Summary of Mapping
◦ Map Entities first
◦ Strong Entity Types (Step 1)
◦ Weak Entity Types (Step 2)
◦ Map Relationship
◦ 1:1 Relationship Types (Step 3)
◦ 1:N Relationship Types (Step 4)
◦ M:N Relationship Types (Step 5)
◦ N-ary Relationship Types (Step 7)
◦ Mapping
◦ Multivalued Attributes (Step 6)
55
ER vs Relational Model
ER MODEL RELATIONAL MODEL
Entity Type Entity relation
1:1 or 1:N relationship type Foreign key (or relationship
relation)
M:N relationship type Relationship relation and two
foreign key
n-ary relationship type Relationship relation and n foreign
key
Simple Attribute Attribute
Composite Attribute Set of simple component attributes
Multivalued Attribute Relation and foreign key
56
Takeaway
Learning Outcomes
1. An understanding of relational model.
2. Knowing how to convert an ERD to relational model.