The Relational Data Model
2/14/20 1
2. The Relational Data Model
• use a simple and uniform data structure: the
relation
• has been implemented in most commercial
database systems
• has a solid theoretic foundation.
2/14/20 2
2.1 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.
• The set of allowed values for an attribute is called its domain.
• Each row of the table is called a tuple of the relation.
• N.B. There is no ordering of column or rows.
2/14/20 3
Example
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
2/14/20 4
Above two tables are the same relation —- Player
• 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}.
Composite and multivalued attributes are disallowed!
2/14/20 5
• A tuple, t(A1,A2, …,An), is a point in dom(A1) × . . . × dom(An)
where each dom(Aj) is the domain of Aj.
• A relation (or a relation instance) is a set of tuples: a subset of
dom(A1) × . . . × dom(An).
• A relation schema is used to describe a relation.
• The degree of a relation is the number of attributes of its relation
schema.
2/14/20 6
Relational Data Model vs ER Model:
• Relation schema (intension) ⇄ entity or relationship type schema (intension).
• attributes ⇄ attributes
• tuple ⇄ instance of entity/relationship
• relation (instance, extension) ⇄ entity/relationship extension
• composite and multivalued attributes are allowed in ER model, but not allowed in
relational data model.
2/14/20 7
• Keys are used to identify tuples in a relation.
• A superkey is a set of attributes that uniquely determines a tuple.
• Note that this is a property of the relation that does not depend on the
current relation instance.
• A candidate key is a superkey, none of whose proper subsets is a superkey.
• Keys are determined by the applications.
• E.g. if {Name} is unique then it is a candidate key for PLAYER; otherwise
we need to use the whole tuple or create a candidate key, say PID.
• {Goals} usually cannot not be a candidate key since different players might
have the same number of goals.
• {Name, Goals} is a superkey but not a candidate key if {Name} is a key.
2/14/20 8
• 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_number.
• 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.
2/14/20 9
2.2 Integrity constraints
• There are several kinds of integrity constraints that are an integral part of the
relational model:
• 2.2.1 Key constraint: candidate key values must be unique for every relation
instance.
• 2.2.2 Entity integrity: an attribute that is part of a primary key cannot be NULL.
• 2.2.3 Referential integrity: The third kind has to do with “foreign keys”.
2/14/20 10
• Foreign keys are used to refer to a tuple in another relation.
• A set, FK, of attributes from a relation schema R1 may be a foreign key 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.
2/14/20 11
2.2.4 Checking constraints on updates
– To maintain the integrity of the database, we need to check that integrity
constraints will not be violated before proceeding with an update.
– Example: Suppose we have the following schema with foreign keys as
shown:
2/14/20 12
2/14/20 13
<2, Dr. V. Ciesielski>
insert
2/14/20 14
insert
2/14/20 15
<5, 6, 2, Psychology, Ph.D>
insert
2/14/20 16
• 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.
Examples:
1. Insert < 2, Dr.V.Ciesielski > into RESEARCHER
Allowed? No. Violates a key constraint.
Action? Reject or allow the user to correct.
2/14/20 17
2. Insert < Comp.Sci.,NULL > into COURSE
Allowed? No. Violates the entity integrity
constraint.
Action: Reject or correct.
3. Insert < 5, 6, 2, Psychology, Ph.D. > into
ENROLMENT
Allowed? No. Violates a referential integrity
constraint (There is no person number 6).
Action: Reject, correct or accept after insertion
of person number 6.
2/14/20 18
• Deletions: When deleting, we need to check referential
integrity – check whether the primary key occurs in another
relation.
Examples:
1. Delete tuple with Person# = 2 from RESEARCHER
Allowed? No. Violates the referential integrity.
Action: Reject, correct or modify the ENROLMENT tuple by
2/14/20 19
• deleting it (note that the this requires another
integrity check, possibly causing a cascade of
deletions), or
• setting the foreign key value to NULL (note this
can’t be done if it is part of a primary key), or
• setting the foreign key value to another
acceptable value.
2/14/20 20
Modifications:
If the modified attribute is a
• primary key: this is similar to deleting and
then reinserting.
• foreign key: check that the new value refers
to an existing tuple.
• neither: no problems can arise.
2/14/20 21
2.2.5 Relational database definition
– 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.
2/14/20 22
2.3 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.
2/14/20 23
• Example: ER RDB
2/14/20 24
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
* *
• Step 1 : For each regular (not weak) entity type E,
create a relation R with
– Attributes : All simple attributes (and simple components
of composite attributes) of E.
– Key : Choose one of the keys of E as the primary key for
the relation.
2/14/20 25
• Step 1a : For each specialised entity type E,
with parent entity type P, create a relation R
with
– Attributes : The attributes of the key of P, plus the
simple attributes of E.
– Key : The key of P.
2/14/20 26
• Example: ER RDB
2/14/20 27
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
2/14/20 28
SSN Fname Lname Birdate
Name Location
Pname Pnumber
Employee
Department
Project
• Step 2 : For each weak entity type W, with owner
entity type E, create a relation R with
– Attributes : All simple attributes (and simple
components of composite attributes) of W, and include
as a foreign key the prime attributes of the relation
derived from E.
– Key : The foreign key plus the partial key of W.
2/14/20 29
• Example: ER RDB
2/14/20 30
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
2/14/20 31
SSN Fname Lname Birdate
Name Location
Pname Pnumber
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
• 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 (prefer one that participates totally), say S.
– Add the attributes of the primary key of T to S as a foreign key.
– Add the simple attributes (and simple components of composite attributes) of B
as attributes of S.
(Alternative: merge the two entity types and the relationship into a single
relation, especially if both participate totally and do not participate in other
relationships).
2/14/20 32
• Example: ER RDB
2/14/20 33
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
2/14/20 34
SSN Fname Lname Birdate
Name Location MSSN Mdate
Pname Pnumber
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
• Step 4 : For each regular 1:N relationship type B.
– Let E and F be the participating entity types.
– Let E by the entity type on the 1 side, F the one on the N side.
– Let S and T be the corresponding relations.
– Add the attributes of the primary key of S to T as a foreign key.
– Add to T any simple attributes (or simple components of composite
attributes) of the relationship.
(Notice that this doesn’t add any new tuples, just attributes.)
2/14/20 35
• Example: ER RDB
2/14/20 36
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
2/14/20 37
SSN Fname Lname Birdate Dname
Name Location MSSN Mdate
Pname Pnumber
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
• Step 5 : For each N:M relationship type B. Create
a new relation R. Let E and F be the participating entity types. Let S
and T be the corresponding relations.
– Attributes : The key of S and the key of T as foreign keys, plus the
simple attributes (and simple components of composite attributes) of B.
– Key : The key of S and the key of T.
2/14/20 38
• Example: ER RDB
2/14/20 39
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
2/14/20 40
SSN Fname Lname Birdate Dname
Name Location MSSN Mdate
Pname Pnumber
Employee
Department
Project
SSN Fname Birdate Relation
Dependent
Pname ESSN Time
Participation
• Step 6 : For each multivalued attribute A. Create a new
relation R. Let A be an attribute of E.
– Attributes :
1. A (if A is a simple attribute) together with the key of
E as a foreign key.
2. The simple components of A (if A is a composite
attribute), together with the key of E as a foreign key.
– Key : All attributes.
2/14/20 41
• Example: ER RDB
2/14/20 42
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
2/14/20 43
SSN Fname Lname Birdate Dname
Name Location MSSN Msdate
Pname Pnumber
SSN Fname Birdate Relation
Pname ESSN Time
Dname Dnumber
Employee
Department
Project
Dependent
Participation
D_number
• Step 7 : For each n-ary relationship type (n > 2). Create a
new relation with
– Attributes : as for Step 5.
– Key : as for Step 5, except that if one of the participating entity
types has participation ratio 1, its key can be used as a key for
the new relation.
2/14/20 44
* *