Relational Database Constraints
Slide 5- 1
Characteristics Of Relations
• Values in a tuple:
– All values are considered atomic (indivisible).
– Each value in a tuple must be from the domain of
the attribute for that column
• If tuple t = <v1, v2, …, vn> is a tuple (row) in
the relation state r of R(A1, A2, …, An)
• Then each vi must be a value from dom(Ai)
– A special null value is used to represent values
that are unknown or inapplicable to certain
tuples.
Slide 5- 2
Characteristics Of Relations
• Notation:
– We refer to component values of a tuple t
by:
• t[Ai] or t.Ai
• This is the value vi of attribute Ai for tuple t
– Similarly, t[Au, Av, ..., Aw] refers to the
subtuple of t containing the values of
attributes Au, Av, ..., Aw, respectively in t
Slide 5- 3
Relational Integrity Constraints
• Constraints are conditions that must hold on all valid
relation states.
• There are three main types of constraints in the
relational model:
– Key constraints
– Entity integrity constraints
– Referential integrity constraints
• Another 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)
Slide 5- 4
Key Constraints
• Superkey of R:
– Is a set of attributes SK of R with the following condition:
• No two tuples in any valid relation state r(R) will have the
same value for SK
• That is, for any distinct tuples t1 and t2 in r(R), t1[SK]
t2[SK]
• This condition must hold in any valid state r(R)
• Key of R:
– A "minimal" superkey
– That is, a key is a superkey K such that removal of any
attribute from K results in a set of attributes that is not a
superkey (does not possess the superkey uniqueness
property)
Slide 5- 5
Key Constraints (continued)
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– CAR has two keys:
• Key1 = {State, Reg#}
• Key2 = {SerialNo}
– Both are also superkeys of CAR
– {SerialNo, Make} is a superkey but not a key.
• In general:
– Any key is a superkey (but not vice versa)
– Any set of attributes that includes a key is a superkey
– A minimal superkey is also a key
Slide 5- 6
Key Constraints (continued)
• If a relation has several candidate keys, one is chosen
arbitrarily to be the primary key.
– The primary key attributes are underlined.
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– We chose SerialNo as the primary key
• The primary key value is used to uniquely identify each
tuple in a relation
– Provides the tuple identity
• Also used to reference the tuple from another tuple
– General rule: Choose as primary key the smallest of the
candidate keys (in terms of size)
– Not always applicable – choice is sometimes subjective
Slide 5- 7
CAR table with two candidate keys –
LicenseNumber chosen as Primary Key
Slide 5- 8
Relational Database Schema
• Relational Database Schema:
– A set S of relation schemas that belong to the
same database.
– S is the name of the whole database
schema
– S = {R1, R2, ..., Rn}
– R1, R2, …, Rn are the names of the individual
relation schemas within the database S
• Following slide shows a COMPANY database
schema with 6 relation schemas
Slide 5- 9
COMPANY Database Schema
Slide 5- 10
Entity Integrity
• Entity Integrity:
– The primary key attributes PK of each relation
schema R in S cannot have null values in any tuple of
r(R).
• This is because primary key values are used to identify the
individual tuples.
• t[PK] null for any tuple t in r(R)
• If PK has several attributes, null is not allowed in any of
these attributes
– Note: Other attributes of R may be constrained to
disallow null values, even though they are not
members of the primary key.
Slide 5- 11
Referential Integrity
• A constraint involving two relations
– The previous constraints involve a single
relation.
• Used to specify a relationship among tuples in
two relations:
– The referencing relation and the
referenced relation.
Slide 5- 12
Referential Integrity
• Tuples in the referencing relation R1 have
attributes FK (called foreign key attributes)
that reference the primary key attributes PK of
the referenced relation R2.
– A tuple t1 in R1 is said to reference a tuple
t2 in R2 if t1[FK] = t2[PK].
• A referential integrity constraint can be displayed
in a relational database schema as a directed arc
from R1.FK to R2.
Slide 5- 13
Referential Integrity (or foreign key)
Constraint
• Statement of the constraint
– The value in the foreign key column (or
columns) FK of the the referencing relation
R1 can be either:
• (1) a value of an existing primary key value
of a corresponding primary key PK in the
referenced relation R2, or
• (2) a null.
• In case (2), the FK in R1 should not be a part of
its own primary key.
Slide 5- 14
Displaying a relational database schema and
its constraints
• Each relation schema can be displayed as a row of
attribute names
• The name of the relation is written above the attribute
names
• The primary key attribute (or attributes) will be
underlined
• A foreign key (referential integrity) constraints is
displayed as a directed arc (arrow) from the foreign key
attributes to the referenced table
– Can also point the the primary key of the referenced
relation for clarity
• Next slide shows the COMPANY relational schema
diagram
Slide 5- 15
Referential Integrity Constraints for COMPANY database
Slide 5- 16
Other Types of Constraints
• Semantic Integrity Constraints:
– based on application semantics and cannot be
expressed by the model per se
– Example: “the max. no. of hours per
employee for all projects he or she works on
is 56 hrs per week”
• A constraint specification language may have
to be used to express these
• SQL-99 allows triggers and ASSERTIONS to
express for some of these
Slide 5- 17
Populated database state
• Each relation will have many tuples in its current relation
state
• The relational database state is a union of all the
individual relation states
• Whenever the database is changed, a new state arises
• Basic operations for changing the database:
– INSERT a new tuple in a relation
– DELETE an existing tuple from a relation
– MODIFY an attribute of an existing tuple
• Next slide shows an example state for the COMPANY
database
Slide 5- 18
Populated database state for COMPANY
Slide 5- 19
