Relational Data Model – Part 2
Integrity Constraints
Integrity Constraints over Relations
Constraints are conditions that must hold on all relations in a database
state.
The main types of constraints in the relational data model include:
1 Domain constraints;
2 Key constraints;
3 Entity integrity constraints;
4 Referential integrity constraints.
(1) Domain Constraints
Every value in a tuple must be from the domain of its attribute.
INT
VARCHAR
DATE
SMALLINT
NOT NULL
(2) Key Constraints – Observation
We observe that: data does not occur independently from one another
within individual relations.
No two students have the same student ID:
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
… … … …
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
… … … … …
(2) 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
′
⊂ SK held
on R. A minimal superkey is also known as a candidate key.
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.
(2) Key Constraints – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
460 Tyrion 11/09/1987
Is {DoB} a superkey of STUDENT? No!
Is {StudentID, DoB} a superkey of STUDENT? Yes!
Is {StudentID, DoB} a candidate key of STUDENT? No!
Is {StudentID} a candidate key of STUDENT? Yes!
Can {StudentID} be chosen as a primary key of STUDENT? Yes!
Can {DoB} be chosen as a primary key of STUDENT? No!
(2) Key Constraints – Example
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
458 COMP1130 2015 S1 inactive 20/02/2015
Is {CourseNo, Semester} a superkey of ENROL? No!
Is {StudentID, CourseNo, Semester} a candidate key of ENROL? Yes!
Can {StudentID, CourseNo} be chosen as a primary key of ENROL? No!
(3) 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.
(3) Entity Integrity Constraints – Example
If STUDENTID is specified as the primary key of STUDENT, then the
following relation violates the entity integrity constraint.
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
NULL Peter 23/05/1993
459 Fran 11/09/1987
How about the case when EMAIL is the primary key of STUDENT?
Answer: The relation does not violate the entity integrity constraint.
(4) Referential Integrity Constraints – Observation
We 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
458 Peter 23/05/1993
459 Fran 11/09/1987
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.
(4) 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, ),
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 has 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.
(4) Referential Integrity Constraints – Example
What foreign keys can be established in the database STUENROL?
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
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
(4) 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 t
′
1 in ENROL, there is a tuple t
′
2 in STUDENT such that the
StudentID value in t
′
1 is the same with the StudentID value in t
′
2.
(4) 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
458 Peter 23/05/1993
459 Fran 11/09/1987
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 COMP2600 2016 S2 active 11/06/2016
(4) Referential Integrity Constraints – Question
Answer: The following database does not satisfy the foreign key of
ENROL: [CourseNo]⊆ COURSE[No].
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
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 COMP2600 2016 S2 active 11/06/2016
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 …