2. The Relational Data Model
2. The Relational Data Model
We will study:
Background
2 Key concepts
Basics (i.e., attribute and domain) Relation schema, tuple and relation Relational database schema and state
3 Relational integrity constraints
Key constraints
Entity integrity constraints Referential integrity constraints
4 Constraint violations
Insert, delete, update operations
1
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 1
2. The Relational Data Model
Background
Introduced by Edgar F. Codd of IBM Research in 1970.
“A Relational Model for Large Shared Data Banks”, Communications of the ACM.
Simple concepts
A database contains tables (called relations), and each table is made up of columns and rows.
Humans have used tables for centuries to keep track of data.
Efficient implementation
A logical data model with physical date independence.
A clear separation between schema and instance.
Mathematical foundation
It is based on set theory and first-order predicate logic. Mathematical methods can be used to verify properties.
Used as the standard for commercial relational DBMSs (e.g., IBM, Oracle, PostgreSQL).
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 2
2. The Relational Data Model
Relations – Examples
R1 ={(comp2400,RT1),(comp2400,CT1),(comp6240,RT1), (comp6240, CT 1), (comp1100, RT 1)}
Class
comp2400 comp6240 comp1100
Room
RT1
CT1
Class
Room
comp2400
RT1
comp2400
CT1
comp6240
RT1
comp6240
CT1
comp1100
RT1
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 3
2. The Relational Data Model
Relations – Examples
R2 ={(comp2400,RT1),(comp6240,RT1),(comp1100,CT1)}
Class
comp2400 comp6240 comp1100
Room
RT1
CT1
Class
Room
comp2400
RT1
comp6240
RT1
comp1100
CT1
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 4
2. The Relational Data Model
Class
comp2400 comp6240 comp1100
Room
RT1
CT1
Relations – Examples
R3 ={(comp2400,RT1),(comp6240,RT1),(comp1100,RT1)}
Class
Room
comp2400
RT1
comp6240
RT1
comp1100
RT1
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 5
2. The Relational Data Model
Relations – Examples
Let DClass and DRoom be two sets:
DClass ={comp2400,comp6240,comp1100} DRoom ={RT1,CT1}
R1 ={(comp2400,RT1),(comp2400,CT1),(comp6240,RT1), (comp6240, CT 1), (comp1100, RT 1)}
R2 ={(comp2400,RT1),(comp6240,RT1),(comp1100,CT1)}
R3 ={(comp2400,RT1),(comp6240,RT1),(comp1100,RT1)}
DClass×DRoom ={(c,r)|c∈DClass,r∈DRoom}=
{(comp2400, RT 1), (comp2400, CT 1), (comp6240, RT 1),
(comp6240, CT 1), (comp1100, RT 1), (comp1100, CT 1)} What is the relationship of R1, R2, R3 with DClass × DRoom?
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 6
2. The Relational Data Model
Relations – Examples
Let DClass and DRoom be two sets:
DClass ={comp2400,comp6240,comp1100} DRoom ={RT1,CT1}
R1 ={(comp2400,RT1),(comp2400,CT1),(comp6240,RT1), (comp6240, CT 1), (comp1100, RT 1)}
R2 ={(comp2400,RT1),(comp6240,RT1),(comp1100,CT1)}
R3 ={(comp2400,RT1),(comp6240,RT1),(comp1100,RT1)}
DClass×DRoom ={(c,r)|c∈DClass,r∈DRoom}=
{(comp2400, RT 1), (comp2400, CT 1), (comp6240, RT 1),
(comp6240, CT 1), (comp1100, RT 1), (comp1100, CT 1)} What is the relationship of R1, R2, R3 with DClass × DRoom?
Answer: R1, R2 and R3 are the subsets of DClass × DRoom, i.e., Ri ⊆ DClass × DRoom for i=1,2,3.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 7
2. The Relational Data Model
Relations
Does the order of columns or rows in a relation matter?
CourseCode
Name
Room
Date/Time
comp2400
RDB
RT1
Wed 15:00-16:00
comp1100
JV1
RT1
Mon 13:00-14:00
comp2100
CPP
RT1
Thu 15:00-17:00
comp6240
RDB
RT1
Tue 9:00-10:00
CourseCode
Name
Date/Time
Room
comp2400
RDB
Wed 15:00-16:00
RT1
comp6240
RDB
Tue 9:00-10:00
RT1
comp1100
JV1
Mon 13:00-14:00
RT1
comp2100
CPP
Thu 15:00-17:00
RT1
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 8
2. The Relational Data Model
Does the order of columns or rows in a relation matter?
Relations
CourseCode
Name
Room
Date/Time
comp2400
RDB
RT1
Wed 15:00-16:00
comp1100
JV1
RT1
Mon 13:00-14:00
comp2100
CPP
RT1
Thu 15:00-17:00
comp6240
RDB
RT1
Tue 9:00-10:00
CourseCode
Name
Date/Time
Room
comp2400
RDB
Wed 15:00-16:00
RT1
comp6240
RDB
Tue 9:00-10:00
RT1
comp1100
JV1
Mon 13:00-14:00
RT1
comp2100
CPP
Thu 15:00-17:00
RT1
Answer: The order of rows in a relation doesn’t matter, but the order of columns is important.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 9
2. The Relational Data Model
Informal Definitions
Relation: a table of values.
A relation is a table with columns and rows.
An attribute is a named column of a relation.
The domain of an attribute is the set of admissible values for the
attribute.
A tuple is a row of a relation.
A relational database is a collection of relations.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 10
2. The Relational Data Model
Properties of relation
Informal Definitions
In a relation, each tuple is distinct, i.e., there are no duplicate tuples. Is R = {(2, 1), (4, 1), (2, 1)} a relation?
The order of attributes has some significance.
The order of tuples has no significance.
Each relation has a distinct name in a database.
Each attribute has a distinct name in a relation (but attributes in
different relations may have the same name).
The values of an attribute are all from the same domain, i.e., the domain of the attribute.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 11
2. The Relational Data Model
Correspondence of informal and formal terms:
Relation
INFORMAL TERMS
FORMAL TERMS
Column heading
Attribute
Data type
Domain
Row
Tuple
Table definition
Relation schema
Table
Relation
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 12
2. The Relational Data Model
The Basics
Attributes are used to describe the properties of information. In the relational model, they usually refer to atomic data.
Example: To capture the information of a person, we can use attributes like Name, Age, Gender, Address and PhoneNumber.
Domains are the sets of all possible values for attributes.
STRING = {A, B, CD, …};
Example: DATE = {01/01/2005, 03/07/1978, …};
NAT = {1, 2, 3, …}.
Recall that, Cartesian product D1 × … × Dn is the set of all possible
combinations of values from the sets D1 , …, Dn .
Example: Let D1={book,pen}, D2={1,2} and D3={red}. Then
D1 × D2 × D3 ={(book,1,red),(book,2,red),(pen,1,red),(pen,2,red)}
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 13
2. The Relational Data Model
The Basics – Another Example
The attributes are StudentID, CourseNo, Semester, Status and EnrolDate.
The domains of attributes are as follows.
dom(StudentID)=NAT; dom(CourseNo)=STRING; dom(Semester)=STRING; dom(Status)=STRING; dom(EnrolDate)=DATE.
The whole table can be considered as a set {(456, COMP2400, 2016 S2, active, 25/05/2016), (458, COMP1130, 2016 S1, active, 20/02/2016), (459, COMP2400, 2016 S2, active, 11/06/2016)}.
Is the above set a subset of
NAT × STRING × STRING × STRING × DATE?
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 14
2. The Relational Data Model
The Basics – Another Example
The attributes are StudentID, CourseNo, Semester, Status and EnrolDate.
The domains of attributes are as follows.
dom(StudentID)=NAT; dom(CourseNo)=STRING; dom(Semester)=STRING; dom(Status)=STRING; dom(EnrolDate)=DATE.
The whole table can be considered as a set {(456, COMP2400, 2016 S2, active, 25/05/2016), (458, COMP1130, 2016 S1, active, 20/02/2016), (459, COMP2400, 2016 S2, active, 11/06/2016)}.
Is the above set a subset of
NAT × STRING × STRING × STRING × DATE?
Answer: Yes.
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 15
2. The Relational Data Model
Relation Schema
A relation schema has a relation name and a list of attributes.
Each attribute is associated with a domain.
A relation schema can be expressed by R(A1,…,An), or
R(A1 :dom(A1),…,An :dom(An)),
where A1 , .., An are attributes of R and dom(Ai ) is the domain of Ai . Example: The relation schema in the previous example is
ENROL(StudentID, CourseNo, Semester, Status, EnrolDate), or
ENROL(StudentID: NAT, CourseNo: STRING, Semester: STRING, Status: STRING, EnrolData: DATE).
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 16
2. The Relational Data Model
Tuples and Relations
Let R(A1, …, An) be a relation schema.
A n-tuple in R is a list t of values, i.e., t ∈ dom(A1) × … × dom(An). Example: The previous example has the following tuples:
(456, COMP2400, 2016 S2, active, 25/05/2016)∈
NAT × STRING × STRING × STRING × DATE.
(458, COMP1130, 2016 S1, active, 20/02/2016)∈
NAT × STRING × STRING × STRING × DATE.
(459, COMP2400, 2016 S2, active, 11/06/2016)∈
NAT × STRING × STRING × STRING × DATE.
A relation r(R) is a set of n-tuples r(R) ⊆ dom(A1) × … × dom(An). Example: The previous example has the following relation:
r(ENROL) ⊆ NAT × STRING × STRING × STRING × DATE.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 17
2. The Relational Data Model
Relational Database Schema and State
A relational database schema S is
a set of relation schemas S = {R1,…,Rm}, and a set of integrity constraints IC.
A relational database state of S is a set of relations such that there is just one relation for each relation schema in S, and all the relations satisfy the integrity constraints IC.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 18
2. The Relational Data Model
Relational Database Schema – Example
Consider a relational database schema STUENROL that has three relation schemas:
STUDENT(StudentID, Name, DoB, Email).
COURSE(No, Cname, Unit);
ENROL(StudentID, CourseNo, Semester, Status, EnrolDate);
STUDENT
StudentID
Name
DoB
Email
COURSE
No
Cname
Unit
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
That is, STUENROL={STUDENT, COURSE, ENROL}.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 19
2. The Relational Data Model
Relational Database State – Example
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 20
2. The Relational Data Model
Relational Integrity Constraints
Constraints are conditions that must hold on all relations in a database state.
The main types of constraints in the relational model include: Key constraints;
Entity integrity constraints; Referential integrity constraints.
An implicit constraint is the domain constraint.
Every value in a tuple must be from the domain of its attribute (or it
could be NULL, if allowed for that attribute)
We will talk about another important type of constraints: functional dependencies later, when evaluating database design quality.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 21
2. The Relational Data Model
Key Constraints – Observation
We observe that: data does not occur independently from one another within relations.
No two courses have the same course number:
No two students have the same student ID:
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
…
…
…
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
…
…
…
…
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 22
2. The Relational Data Model
Key Constraints – Observation
No two enrolments have the same student ID, the same course number in the same semester:
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
…
…
…
…
…
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 23
2. The Relational Data Model
Key Constraints – Definitions
Let R(A1, . . . , An) be a relation schema.
A superkey SK of R is a subset of attributes of R, i.e., SK ⊆ {A1,…,An},
such that
no two distinct tuples in r(R) can have the same value for SK.
′
A superkey SK of R is minimal if there is no other superkey SK on R. A minimal superkey is also known as a candidate key.
⊂ SK held
A primary key PK of R is a minimal superkey of R, (i.e., a primary key is one of the candidate keys). If a relation has only one candidate key then that would be the primary key.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 24
2. The Relational Data Model
Key Constraints – Example
What are superkeys of STUDENT and ENROL? Is {DoB} a superkey of STUDENT?
Is {StudentID} a superkey of ENROL?
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 25
2. The Relational Data Model
Key Constraints – Example
What are superkeys of STUDENT and ENROL?
Answer:
For STUDENT, a superkey can be any subset of attributes which includes StudentID or any subset of attributes which includes Email, e.g., {StudentID}, {StudentID, Name}, {StudentID, Email}, …1
For ENROL, a superkey can be any subset of attributes which includes StudentID, CourseNo and Semester, e.g., {StudentID, CourseNo, Semester, EnrolDate}, . . .
Is {DoB} a superkey of STUDENT? Answer: No.
Is {StudentID} a superkey of ENROL? Answer: No.
1
attributes which includes Name and DoB.
Under an assumption that {Name, DoB} can uniquely identify students, a superkey can also be any subset of
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 26
2. The Relational Data Model
Key Constraints – Example
What are the candidate keys for STUDENT, COURSE and ENROL? What primary keys can be chosen for STUDENT, COURSE and ENROL?
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 27
2. The Relational Data Model
Key Constraints – Example
What are the candidate keys for STUDENT, COURSE and ENROL?
Answer:
For STUDENT, the candidate keys are {StudentID} and {Email}.2 For COURSE, there is only one candidate key {StudentID, CourseNo,
Semester}.
What primary keys can be chosen for STUDENT, COURSE and ENROL?
Answer:
The primary key of STUDENT can be chosen from {StudentID} and {Email}.
The primary key of COURSE can only be {StudentID, CourseNo, Semester}.
2 primary key.
Under a proper assumption, {Name, DoB} may also become a candidate key, and then can be chosen as the
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 28
2. The Relational Data Model
Exercise
Question:
Can you find the candidate keys in the following relation?
Constraints:
1. Two courses are not taught in the same class.
2. A course may have more than one class in a week, possibly on the same day.
CourseCode
Name
Room
Day
Time
comp2400
RDB
RT1
Wed
15:00-16:00
comp1100
JV1
RT1
Mon
13:00-14:00
comp2100
CPP
RT1
Thu
15:00-17:00
comp6240
RDB
RT1
Tue
9:00-10:00
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 29
2. The Relational Data Model
Exercise
Question:
Can you find the candidate keys in the following relation?
Constraints:
1. Two courses are not taught in the same class.
2. A course may have more than one class in a week, possibly on the same day.
Answer: {CourseCode, Day, Time} and {Room, Day, Time}
By Constraint 2, we know that {CourseCode, Day} is not sufficient to be a candidate key.
By Constraint 1, we know that {Room, Day, Time} can also be a candidate key.
CourseCode
Name
Room
Day
Time
comp2400
RDB
RT1
Wed
15:00-16:00
comp1100
JV1
RT1
Mon
13:00-14:00
comp2100
CPP
RT1
Thu
15:00-17:00
comp6240
RDB
RT1
Tue
9:00-10:00
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 30
2. The Relational Data Model
Relational Keys – An Exercise
Find out candidate keys of BOOKING from the Following schema of an ACCOMMODATION database held in a relational DBMS:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Some additional constraints are as follows:
1 A booking can be made for one day only.
2 A guest can make several bookings in a hotel for different days.
3 A guest cannot make two or more bookings in the same hotel for the
same day.
4 A guest can make two or more bookings in different hotels for the
same day.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 31
2. The Relational Data Model
Contd.. Relational Keys – An Exercise
Candidate keys (CKs) for the relation BOOKING:
{guestNo, hotelNo, date} satisfies all the constraints (1-4) and hence is a CK.
{hotelNo, roomNo, date} is a CK because it satisfies all the constraints.
{guestNo, Date, roomNo} cannot be a CK as it does not satisfy constraint (4).
{hotelNo, guestNo, roomNo} is not a CK because of the constraint (2).
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 32
2. The Relational Data Model
Given the following relational schema, identify foreign keys, if any, in HOTEL, ROOM, BOOKING and GUEST relations.
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 33
2. The Relational Data Model
Identify foreign keys, if any, in HOTEL, ROOM, BOOKING and GUEST relations.
Given database schema is:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Answer:
ROOM: [hotelNo] ⊆ HOTEL[hotelNo];
BOOKING: [hotelNo] ⊆ HOTEL[hotelNo], [guestNo] ⊆ GUEST[guestNo],
[roomNo, hotelNo] ⊆ ROOM[roomNo, hotelNo].
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 34
2. The Relational Data Model
Let us copy our database schema again:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Is it possible to make a booking in the ACCOMMODATION database in the name of a person who is not listed in the GUEST relation? Give reasons for your answer.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 35
2. The Relational Data Model
For answering our question, let us look at the database schema:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
It is not possible to make a booking in the ACCOMMODATION database in the name of a person who is not listed in the GUEST relation, because, for that to happen, the guestNo of BOOKING must match with that of GUEST (referential integrity).
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 36
2. The Relational Data Model
For our schema:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Is it possible to have same room numbers in two different hotels of the ACCOMMODATION database? Justify your answer.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 37
2. The Relational Data Model
For the given rdatabase schema:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Indeed it is possible to have same room numbers in two different hotels of the ACCOMMODATION database because roomNo and hotelNo uniquely identify the rooms.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 38
2. The Relational Data Model
Exercise to find out CKs with modified Integrity Constraints
Find out CKs of BOOKING from the following database schema of an ACCOMMODATION database held in a relational DBMS:
HOTEL(hotelNo, hotelName, city) with the primary key {hotelNo}, ROOM(roomNo, hotelNo, type, price) with the primary key {roomNo, hotelNo},
GUEST(guestNo, guestName, guestAddress) with the primary key {guestNo},
BOOKING(hotelNo, guestNo, date, roomNo, discount).
Some additional constraints are as follows:
1 A booking can be made for one day only.
2 A guest can make several bookings in a hotel for different days.
3 A guest cannot make two or more bookings in the same or different
hotels for the same day.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 39
2. The Relational Data Model
Let us analyse the given constraints.
1 A booking can be made for one day only.
2 A guest can make several bookings in a hotel for different days.
3 A guest cannot make two or more bookings in the same or different
hotels for the same day.
We arrive at the following conclusions:
{hotelNo, roomNo, Date} will still be a CK as it satisfies the conditions (1-3).
{guestNo, date} will also be a CK as it satisfies all the conditions.
But {hotelNo, guestNo, date} would no longer be a CK.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 40
2. The Relational Data Model
Entity Integrity Constraints
Specifying a primary key also invokes the entity integrity constraint.
null is a special value, which represents the value of an attribute that may be unknown or inapplicable.
The entity integrity constraint states that no primary key value can be NULL.
This is because primary key values are used to identify individual tuples in a relation.
Note: Other attributes of R may be constrained to disallow null values, even though they are not attributes in the primary key.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 41
2. The Relational Data Model
Entity Integrity Constraints – Example
If NAME and DOB are specified as the primary key of STUDENT, then the following relation violates the entity integrity constraint.
How about the case when STUDENTID is the primary key of STUDENT?
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
NULL
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 42
2. The Relational Data Model
Entity Integrity Constraints – Example
If NAME and DOB are specified as the primary key of STUDENT, then the following relation violates the entity integrity constraint.
How about the case when STUDENTID is the primary key of STUDENT?
Answer: The relation does not violate the entity integrity constraint.
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
NULL
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 43
2. The Relational Data Model
Referential Integrity Constraints – Observation
We also observe that: data does not occur independently from one another across relations.
Every course number appearing in ENROL must exist in COURSE:
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Similarly, every student ID appearing in ENROL must exist in STUDENT.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 44
2. The Relational Data Model
Referential Integrity Constraints – Definition
We use t[A] to denote the value of attribute A in tuple t.
Example: For the tuple t=(459,Fran,11/09/1987,frankk@gmail.com),
t[Name]=Fran and t[DoB]=11/09/1987.
A referential integrity constraint specifies a reference between two
relations, while the previous constraints involve only one relation.
Let R1 and R2 be relation schemas in a database schema S, and R2 have
the primary key {B1, . . . , Bn}.
A foreign key on R1 is a statement [A1,…,An] ⊆ R2[B1,…,Bn] restricting
states of S to satisfy the following property:
for each tuple t ∈ r(R1) there exists a tuple t′ ∈ r(R2) with
t[Ai] = t′[Bi] for i = 1,…,n.
R1 is called the referencing relation and R2 is called the referenced relation.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 45
2. The Relational Data Model
Referential Integrity Constraints – Example
What foreign keys can be established in the database STUENROL?
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
459
COMP2400
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 46
2. The Relational Data Model
Referential Integrity Constraints – Example
In this case, we can establish the following foreign keys on ENROL:
1 [CourseNo]⊆ COURSE[No];
2 [StudentID]⊆ STUDENT[StudentID].
This database state satisfies the above two foreign keys because
for each tuple t1 in ENROL, there is a tuple t2 in COURSE such that the CourseNo value in t1 is the same with the No value in t2;
for each tuple t1 in ENROL, there is a tuple t2 in STUDENT such that the ′′
′′
StudentID value in t1 is the same with the StudentID value in t2.
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 47
2. The Relational Data Model
Referential Integrity Constraints – Question
If the database STUENROL is slightly changed as follows, does this database still satisfy the foreign keys in the previous example?
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
659
COMP2600
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 48
2. The Relational Data Model
Referential Integrity Constraints – Question
Answer: The following database does not satisfy the foreign key [CourseNo]⊆ COURSE[No].
STUDENT
StudentID
Name
DoB
Email
456
Tom
25/01/1988
tom@gmail.com
458
Peter
23/05/1993
peter@gmail.com
459
Fran
11/09/1987
frankk@gmail.com
COURSE
No
Cname
Unit
COMP1130
Introduction to Advanced Computing I
6
COMP2400
Relational Databases
6
ENROL
StudentID
CourseNo
Semester
Status
EnrolDate
456
COMP2400
2016 S2
active
25/05/2016
458
COMP1130
2016 S1
active
20/02/2016
659
COMP2600
2016 S2
active
11/06/2016
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 49
2. The Relational Data Model
A Common Pitfall – Foreign Key
Consider the following relation schemas: PERSON={Name, Birthday, Address} with
the primary key {Name, Birthday}; STUDENT={StudentID, Name, DoB, Email} with
the primary key {StudentID}. We find the correct foreign key on STUDENT:
[Name, DoB]⊆PERSON[Name, Birthday] This is NOT equivalent to: (Why?)
STUDENT[Name]⊆PERSON[Name], and STUDENT[DoB]⊆PERSON[Birthday]
PERSON
Name
Birthday
Address
Fran
11/09/1987
16 Park Ave
Tom
25/01/1988
98 Ada St.
STUDENT
StudentID
Name
DoB
Email
456 459
Tom Fran
25/01/1988 11/09/1987
tom@gmail.com frankk@gmail.com
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 50
2. The Relational Data Model
A Common Pitfall – Foreign Key
The following relations satisfy the foreign keys: STUDENT[Name]⊆PERSON[Name], and
STUDENT[DoB]⊆PERSON[Birthday],
but does not satisfy the foreign key:
STUDENT[Name, DoB]⊆PERSON[Name, Birthday].
PERSON
Name
Birthday
Address
Fran
11/09/1987
16 Park Ave
Tom
25/01/1988
98 Ada St.
STUDENT
StudentID
Name
DoB
Email
456 459
Fran Tom
25/01/1988 11/09/1987
tom@gmail.com frankk@gmail.com
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 51
2. The Relational Data Model
Constraint Violations
There are three basic operations that can change a database state:
Insert: insert one or more new tuples in a relation;
Delete: delete tuples in a relation;
Update (or Modify): change the values of attributes in existing tuples.
Whenever these operations are applied, the integrity constraints specified in a database schema should not be violated.
However,
Insert may violate …
Delete may violate … Update may violate …
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 52
2. The Relational Data Model
Summary: The Relational Data Model
The relational data model is the basis of relational databases.
The majority of business data is still kept in relational databases (there is
an increasing trend in using NoSQL databases though).
The way of storing data has significant impacts on how efficient the data can be retrieved and manipulated.
We need to analyse carefully on data being modelled and their associated assumptions so as to decide:
relational schemas (e.g., attributes in the same relation) integrity constraints (e.g., primary keys and foreign keys)
The better a database design is, then
the less inconsistent and redundant data you will get, and more efficiently data can be processed!
Comp2400/Comp6240 – Relational Databases, Semester 2, 2016 53