Copyright©2016RamezElmasriandShamkantB.

Navathe
 CHAPTER 5

The Relational Data M odel and

Relational Database Constraints

Copyright©2016RamezElmasriandShamkantB.Navathe Slide1-2
Chapter Outline
■ Relational M odel Concepts
■ Relational M odel Constraints and Relational
Database Schem as
■ Update Operations and Dealing with Constraint
Violations

Copyright©2016RamezElmasriandShamkantB.Navathe Slide1-3
Relational M odel Concepts
■ The relational M odel of Data is based on the concept of a
Relation
■ The strength of the relational approach to data m anagem ent
comes from the form al foundation provided by the theory of
relations
■ W e review the essentials of the form al relational m odel in
this chapter
■ In practice, there is a standard m odel based on SQL – this
is described in Chapters 6 and 7 as a language
■ Note: There are several im portant differences between the
form al m odel and the practical m odel, as we sha l see

Copyright©2016RamezElmasriandShamkantB.Navathe Slide1-4
Relational M odel Concepts
■ A Relation is a m athem atical concept based on
the i deas of sets
■ The m odel was first proposed by Dr. E.F. Codd of
IBM Research in 1970 in the fo lowing paper:
■ "A Relational M odel for Large Shared Data
Banks," Com m unications of the ACM , June 1970
■ Theabovepapercausedamajorrevolutioninthe
field of database m anagem ent and earned Dr.
Codd the coveted ACM Turing Award

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-5
Informal Definitions

■ Inform a ly, a relation looks like a table of values.

■ A relation typically contains a setofrows.

■ The data elem ents in each row represent certain facts

thatcorrespondtoareal-world entity orrelationship
■
In the form al m odel, rows are ca led tuples

■ Each colum n has a colum n header that gives an indication

of the m eaning of the data item s in that colum n
■ In the form al m odel, the colum n header is ca led an
attribute nam e (orjust attribute)

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-6
Exam ple of a Relation

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-7
Informal Definitions
■ Key of a Rel ati on:
■
Each row has a value of a data item (or set of i tem s)
that uniquely identifies that row in the table
■
Called the key
■
In the STUDENT table, SSN is the key

■ Sometimesrow-ids or sequential num bers are

assigned as keys to identify the rows in a table
■
Called artif i cial key or surrogate key

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-8
Formal Definitions -Schem a
■ The Schem a (or description) of a Relation:
■ Denoted by R(A1, A2, .....An)
■ R is the nam e of the relation
■ The attributes of the relation are A1, A2, ..., An
■ Exam ple:
CUSTOMER (Cust-id,Cust-nam e, Address, Phone#)
■ CUSTOMER is the relation name
■ Definedoverthefourattributes:Cust-id,Cust-nam e,
Address, Phone#
■ Each attribute has a dom ain or a set of valid values.
■ Forexample,thedomainofCust-i d i s 6 di gi t num bers.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-9
Formal Definitions -Tuple
■ A tuple is an ordered set of values (enclosed in angled
brackets ‘< … > ’ )
■ Each value is derived from an appropriate dom ain.
■ A row in the CUSTOMER relation is a 4-tuple and would
consist of four values, for exam ple:
■ <632895, "John Smith", "101 Main St. Atlanta, GA 30332",
"(404)894-2000">
■ Thisiscaleda4- tupl e as i t has 4 val ues
■ A tuple (row) in the CUSTOMER relation.
■ A relation is a setof such tuples (rows)

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 0
Formal Definitions -Domain
■ A dom ain has a logical definition:
■ Example:“USA_phone_numbers”arethesetof10digitphone
numbersvalidintheU.S.
■ Adomainalsohasadata-typeoraformatdefinedforit.
■ TheUSA_phone_numbersmayhaveaformat:(ddd)ddd-ddddwhere
eachdisadecimaldigit.
■ Dates have various form ats such as year, m onth, date form atted as
yyyy-mm-d d , o r a s d d m m , y y y y e t c .

■ The attribute nam e designates the role played by a dom ain in a

relation:
■ Usedtointerpretthemeaningofthedataelementscorresponding

tothatattribute
■ Example:ThedomainDatemaybeusedtodefinetwoattributes
named“Invoice-date”and“Payment-date”withdifferentmeanings

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 1
Definition Summary
Informal Term s Form al Term s
Table Relation
Colum n Header Attribute
A l possible Colum n Domain
Values
Row Tuple

Table Definition Schem a of a Relation

Populated Table State of the Relation
Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 2
CONSTRAINTS
Constraints determ ine which values are perm issible and which
are not i n the database.
They are of three m ai n types:
1.InherentorIm plicitConstraints:These are based on the
data m odel itself. (E.g., relational m odel does not a low a list as
a val ue for any attri bute)
2.Schema-based orExplicitConstraints: They are expressed
in the schem a by using the facilities provided by the m odel.
(E.g., m ax. cardinality ratio constraint in the ER m odel)
3.Application based orsem antic constraints:These are
beyond the expressive power of the m odel and m ust be
specified and enforced by the application program s.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 3
Relational Integrity Constraints
■ Constraints are conditions that m ust hold on all valid
relation states.
■ Therearethreemaintypesof(explicitschema-based)
constraints that can be expressed in the relational m odel:
■ Key constraints
■ Entity integrity constraints
■ Referentialintegrity constraints
■ Anotherschema-based constraint is the dom ain
constraint
■ Every value in a tuple m ust be from the dom ain of its
attribute (or it could be null, if a lowed for that attribute)

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 4
Key Constraints
■ Superkey of R:
■ Is a set of attributes SK of R with the fo lowing condition:
■
No two tuples in any valid relation state r(R) wi l have the sam e
valueforSK
■ Thatis,foranydistincttuplest1andt2inr(R),t1[SK] t2[SK]
■ This condition m ust hold in any valid state r(R)
■ Key of R :
■ A "m inim al" superkey
■ That is, a key is a superkey K such that rem oval of any attribute
from K results in a set of attributes that is not a superkey (does
not possess the superkey uniqueness property)
■ A Key i s a Superkey but not vi ce versa

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 5
Key Constraints (continued)
■ Exam ple: Consider the CAR relation schem a:
■ CAR(State, Reg#, SerialNo, Make, Model, Year)
■ CAR has two keys:
■
Key1 = {State, Reg#}
■
Key2 = {SerialNo}
■ Both are also superkeys of CAR
■ {SerialNo, M ake} is a superkey but not a key.
■ In general :
■ Any key i s a superkey (but not vi ce versa)
■ Any set of attributes that includes a key is a superkey
■ A m inim al superkey is also a key

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 6
Key Constraints (continued)
■ If a relation has several candidate keys, one is chosen
arbitrarily to be the prim ary key.
■ The prim ary key attributes are underlined.
■ Exam ple: Consider the CAR relation schem a:
■ CAR(State, Reg#, SerialNo, Make, Model, Year)
■ W e chose SerialNo as the primary key
■ The prim ary 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 prim ary key the sm allest of the
candidate keys (in term s of size)
■ Not always applicable – choice is sometimes subjective

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 7
CAR table with two candidate keys –
LicenseNum ber chosen as Prim ary Key

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 8
Relational Database Schem a
■ RelationalDatabase Schem a:
■ A set S of relation schem as that belong to the
sam e database.
■ S is the nam e of the whole database schem a

■ S = {R1, R2, ..., Rn} and a set IC of i ntegri ty

constraints.
■ R1, R2,

… , Rn are the nam es of the individual

■ relationslide
Following schem as within
shows a COM the PANY
database S
database
schem a with 6 relation schem as

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-1 9
COMPANY Database Schema

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 0
Relational Database State
■ A relationaldatabase state DB of S is a set of
relation states DB = {r 1 , r 2 , ..., r m } such that each r i is a
state of R i and such that the r i relation states satisfy
the integrity constraints specified in IC.
■ A relational database state is som etim es ca led a
relational database snapshot or instance.
■ W e will not use the term instance since it also applies
to single tuples.
■ A database state that does not m eet the constraints
is an invalid state

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 1
Populated database state
■ Each relation wi l have m any tuples in its current relation
state
■ The relational database state is a union of a l the
individual relation states
■ W henever 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
■ Next slide (Fig.
MODIFY 5.6) shows
an attribute of anan examtuple
existing ple state for the
COM PANY database schem a shown in Fig. 5.5.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 2
Populated database state for COMPANY

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 3
Entity Integrity
■ Entity Integrity:
■
The prim ary key attributes PK of each relation schem a
R in S cannot have nu l values in any tuple of r(R).
■
This is because prim ary key values are used to identify the
individual tuples.
■
t[PK] nu l for any tupl e t i n r(R)
■
If PK has several attributes, nu l is not a lowed in any of these
attributes
■ Note: Other attributes of R m ay be constrained t o
disa low nu l values, even though they are not
m em bers of the prim ary key.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 4
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.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 5
Referential Integrity
■ Tuples in the referencing relation R1 have
attributes FK (ca led foreign key attributes) that
reference the prim ary 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 schem a as a directed arc
from R1.FK to R2.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 6
Referential Integrity (or foreign key)
Constrai nt
■ Statem ent of the constraint
■
The value in the foreign key colum n (or colum ns)
FK of the the referencing relation R1 can be
either:
■
(1) a value of an existing prim ary key value of a
corresponding primary key PK in the referenced
relation R2, or
■
(2) a null.
■ In case (2), the FK i n R1 shoul d not be a part of
its own prim ary key.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 7
Displaying a relational database
schem a and its constraints
■ Each relation schem a can be displayed as a row of
attribute nam es
■ The nam e of the relation is written above the attribute
nam es
■ The prim ary key attribute (or attributes) wi l be underlin
■ ed
A foreign key (referential integrity) constraints is displayed
as a directed arc (arrow) from the foreign key attributes to
the referenced
■ Can table
also point the the primary key of the referenced relation
for clarity
■ Nextslide showsthe COMPANY relationalschema
diagram with referentialintegrity constraints

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 8
Referential Integrity Constraints for COMPANY database

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-2 9
Other Types of Constraints
■ Sem antic Integrity Constraints:
■
based on application sem antics and cannot be expressed
by the m odel per se
■ Exam pl e: “the m ax. no. of hours per em pl oyee for a l

proj ects he or she works on i s 56 hrs per week”

■ A constraintspecification language may have to be used to
express these
■ SQL-99 alowsCREATE TRIGGER and CREATE
ASSERTION to express som e of these sem antic constraints
■ Keys, Perm issibility of Nu l values, Candidate Keys (Unique in
SQL), Foreign Keys, Referential Integrity etc. are expressed
by the CREATE TABLE statem ent in SQL.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 0
Update Operations on Relations
■ INSERT a tupl e.
■ DELETE a tupl e.
■ M ODIFY a tuple.
■ Integrity constraints should not be violated by the
update operations.
■ Several update operations m ay have to be grouped
together.
■ Updates may propagate to cause other updates
autom atica ly. This m ay be necessary to m aintain
integrity constraints.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 1
Update Operations on Relations
■ Incaseofintegrityviolation,severalactionscan
be t aken:
■
Cancel the operation that causes the violation
(RESTRICT or REJECT option)
■ Perform the operation but inform
the user of the violation
■ Trigger additional updates so the violation is
corrected (CASCADE option, SET NULL option)
■ Executeauser-specifiederror-correction routine

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 2
Possible violations for each operation
■ INSERT m ay violate any of the constraints:
■ Domain constraint:
■
if one of the attribute values provided for the new tuple is not of
the specified attribute domain
■ Key constraint:
■
if the value of a key attribute in the new tuple already exists in
another tuple in the relation
■ Referential integrity:
■
if a foreign key value in the new tuple references a prim ary key
value that does not exist in the referenced relation
■ Entity integrity:
■
if the prim ary key value is nu l in the new tuple

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 3
Possible violations for each operation
■ DELETE m ay violate only referential integrity:
■ If the prim ary key value of the tuple being deleted is
referenced from other tuples in the database
■
Can be rem edied by several actions: RESTRICT, CASCADE,
SET NULL (see Chapter 6 for m ore details)
■ RESTRICToption:rejectthedeletion
■ CASCADEoption:propagatethenewprimarykeyvalueintothe
foreignkeysofthereferencingtuples
■ SETNULLoption:settheforeignkeysofthereferencingtuples
toNULL
■ One of the above options must be specified during database
design for each foreign key constraint

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 4
Possible violations for each operation
■ UPDATE m ay violate dom ain constraint and NOT NULL
constraint on an attribute being modified
■ Any of the other constraints m ay also be violated,
depending on the attribute being updated:
■ Updating the prim ary key (PK):
■
Sim ilar to a DELETE fo lowed by an INSERT
■
Need to specify sim ilar options to DELETE
■ Updating a foreign key (FK):
■
May violate referential integrity
■ Updating an ordinary attribute (neither PK nor FK):
■
Can only violate dom ain constraints

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 5
Sum m ary
■ Presented Relational M odel Concepts
■ Definitions
■ Characteristics of relations
■ Discussed Relational M odel Constraints and Relational
Database Schem as
■ Domain constraints
■ Key constraints
■ Entity integrity
■ Referential integrity
■ Described the Relational Update Operations and Dealing
with Constraint Violations

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 6
In-Class Exercise
(Taken from Exercise5.15)
Considerthefollowingrelationsforadatabasethatkeepstrac
kofstudent
enrollmentincoursesandthebooksadoptedforeachcourse:
STUDENT(SSN,Name,M ajor,Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schem a diagram
specifying the foreign keys for this schem a.

Copyright©2016RamezElmasriandShamkantB.Navathe Slide5-3 7