INTEGRITY RULES
Click here for audio-text lecture and feed it to the speech agent
Click here for an audio lecture that can be played using RealPlayer
Integrity rules are needed to inform the
DBMS about certain constraints in the
real world.
Specific integrity rules apply to one
specific database.
Example: part weights must be greater than zero.
General integrity rules apply to all
databases.
Two general rules will be discussed to deal
with: primary keys and foreign keys.
PRIMARY KEYS
Primary key is a unique identifier for
a relation.
There could be several candidate keys,
as long as the they satisfy two properties:
1. uniqueness
2. minimality
From the set of candidate keys, one is
chosen to be the primary key.
The others become alternate keys.
EXAMPLE: The relation R has several candidate keys.
ID SSN License_Number NAME
If we select ID to be the primary key, then
the other candidate keys become alternate keys.
THE ENTITY INTEGRITY RULE
No component of the primary key of a base
relation is allowed to accept nulls.
WHAT ARE NULLS?
Null may mean "property does not apply". For
example, the supplier may be a country, in
which case the attribute CITY has a null value
because such property does not apply.
Null may mean "value is unknown". For example,
if the supplier is a person, then a null
value for CITY attribute means we do not know
the location of this supplier.
Nulls cannot be in primary keys, but can
be in alternate keys.
EXAMPLE: SSN may be null for one and only one
person (why?)
FOREIGN KEYS
A foreign key is an attribute of one
relation R2, whose values are required
to match those of the primary key of
some other relation R1
(R1 and R2 can be identical)
EXAMPLE: SP relation has attribute S#, and
S relation has primary key S#. Then
S# in SP is considered a foreign key.
SP is called the "referencing relation".
S is called the "referenced relation".
We can draw a "referential diagram"
SP ---S#---> S
or simply
SP --------> S
WHY ARE FOREIGN KEYS IMPORTANT?
Foreign-to-primary-key matching are the
"glue" which holds the database together.
Another way of saying it
Foreign keys provide the "links" between
two relations.
A relation's foreign key can refer to the
same relation.
EXAMPLE: EMP ( EMP#, SALARY, MGR_EMP#, ... )
EMP# is the primary key
MGR_EMP# is the foreign key
EMP is a "self-referencing relation".
THE REFERENTIAL INTEGRITY RULE
The database must not contain any
unmatched foreign key values.
REFERENTIAL INTEGRITY RULE
EXAMPLE: The three 3NF relations are:
SP(S#,P#,QTY)
SC(S#,CITY)
CS(CITY,STATUS)
The referential diagrams are:
SP ---S#---> SC ---CITY---> CS
DELETE INTEGRITY RULE:
We should not delete (S5,London) from SC
if S5 is present in SP.
INSERT INTEGRITY RULE:
We should not insert (S3,P2,200) into SP
unless S3 is present in SC.
(Please study these integrity rules in light of
the "update anomalies" for non-3NF relations)