Transcript CH09

Chapter 9
Integrity
Prof. Yin-Fu Huang
CSIE, NYUST
9.1 Introduction
 An integrity constraint is a boolean expression that is associated
with some database and is required to evaluate at all times to
TRUE.
e.g. Constraint SC1
Is_Empty (S Where Status < 1 Or Status > 100);
Drop Constraint SC1;
Advanced Database System
Yin-Fu Huang
9.2 A Closer Look
 The fact that a given variable is of some given type represents an a
prior constraint on the variable in question.
 Tutorial D examples:
1. Constraint SC2
Forall SX (If SX.City = ‘London’
Then SX.Status = 20 End If);
2. Constraint PC3
If Exists PX (True)
Then Exists PX (PX.Color = Color(‘Blue’)) End If;
Advanced Database System
Yin-Fu Huang
9.2 A Closer Look (Cont.)
3. Constraint SC4
Forall SX Forall SY (If SX.S# = SY.S#
Then SX.Sname = SY.Sname
And SX.Status = SY.Status
And SX.City = SY.City
End If);
4. Constraint SSP5
Forall SPX Exists SX (Sx.S# = SPX.S#)
5. Constraint SSP6
Forall SX Forall SPX
(If Sx.S# = SPX.S#
Then SX.Status  20 Or SPX.Qty  500 End If);
Advanced Database System
Yin-Fu Huang
9.3 Predicates and Propositions
 A constraint as formally stated is a predicate.
 When that constraint is checked, arguments are substituted for the
parameters and the predicate is thereby reduced to a proposition –
and that proposition is then required to evaluated to TRUE.
Advanced Database System
Yin-Fu Huang
9.4 Relvar Predicates and Database Predicates
 The relvar predicate for R is the logical And or conjunction of all
of the constraints that apply to relvar R.
 The Golden Rule
No update operation must ever assign to any relvar a value
that causes its relvar predicate to evaluate to FALSE.
 The database predicate for D is the conjunction of all of those
relvar predicates.
 The Golden Rule
No update operation must ever assign to any database a
value that causes its database predicate to evaluate to FALSE.
Advanced Database System
Yin-Fu Huang
9.5 Checking the Constraints
 The sequence of events:
1. Insert the new tuple.
2. Check the constraint.
3. Undo the update (because the check fails).
But this is absurd!
 All constraint checking is immediate?
 Most of the literature argues that “the unit of integrity” is the
transaction and that at least some checking has to be deferred until
end-of-transaction (i.e., COMMIT time).
Advanced Database System
Yin-Fu Huang
9.6
Internal vs. External Predicates
 a formal construct vs. an informal construct.
 what the data means to the system vs. what the data means to the
users.
 A given internal predicate is the system’s approximation to the
corresponding external predicate.
 If (s  S) = TRUE Then XPS(s) = TRUE
If (s  S) = FALSE Then XPS(s) = FALSE
 s  S  XPS(s)
Advanced Database System
Yin-Fu Huang
9.7 Correctness vs. Consistency
 The system cannot enforce truth, only consistency.
 If the database contains only true propositions, then it is
consistency, but the converse is not necessarily so.
 If the database is inconsistency, then it contains at least one
false proposition, but the converse is not necessarily so.
9.8
Integrity and Views
 Views too are subject to constraints, and they have relvar
predicates, both internal and external.
e.g., There exists some city City such that supplier S# is under
contract, is named Sname, has status Status, and is located in City.
Advanced Database System
Yin-Fu Huang
9.9 A Constraint Classification Scheme
 Four broad categories: database, relvar, attribute, and type
constraints
 Type constraints
e.g., Type Weight Possrep { D Decimal (5, 1)
Constraint D > 0.0 And D < 5000.0 };
 Type constraints are always checked immediately.
 Attribute constraints
e.g., Var S Base Relation
{ S#
S#,
Sname
Name,
Status
Integer,
City
Char } … ;
Advanced Database System
Yin-Fu Huang
9.9 A Constraint Classification Scheme (Cont.)
 Relvar and database constraints
 A transition constraint is a constraint on the legal transitions
that a given variable-in particular, a given relvar or a given
database- can make from one value to another.
e.g. Constraint TRC1
Forall SX’ Forall SX ( SX’.S#  SX.S# Or
SX’.Status  SX.Status );
Constraint TRC2
Forall PX
Sum ( SPX’ Where SPX’.P# = PX.P#, Qty ) 
Sum ( SPX Where SPX .P# = PX.P#, Qty );
 The concept of transition constraints does not apply to type or
attribute constraints.
Advanced Database System
Yin-Fu Huang
9.10 Keys
 Candidate Keys
 Properties:
1. Uniqueness
2. Irreducibility
 Key { <attribute name commalist> }
 Tuple-level addressing mechanism
 Primary Keys and Alternative Keys
 Exactly one of candidate keys the primary key
 The others alternative keys
Advanced Database System
Yin-Fu Huang
9.10 Keys (Cont.)
 Foreign Keys
 A foreign key in R2 is a set of attributes of R2, say FK, such
that:
1. There exists a relvar R1 (R1 and R2 not necessarily distinct)
with a candidate key CK.
2. It is possible to rename some subset of the attributes of FK,
such that FK becomes FK’ (say) and FK’ and CK are of the
same (tuple) type.
3. For all time, each value of FK in the current value of R2
yields a value for FK’ that is identical to the value of CK in
some tuple in the current value of R1.
Advanced Database System
Yin-Fu Huang
9.10 Keys (Cont.)
 Points arising:
1. It will rarely be necessary in practice to perform any
actual renaming.
2. While every value of FK must appear as a value of CK,
the converse is not a requirement.
3. Simple or composite
4. Referencing relvar vs. referenced relvar
5. Referential diagrams
S# P#
S ← SP → P
6. A given relvar can be both referenced and referencing.
R3 → R2 → R1
Advanced Database System
Yin-Fu Huang
9.10 Keys (Cont.)
7. Relvars R1 and R2 in the foreign key definition are not
necessarily distinct.
Var Emp Base Relation
{Emp# Emp#, ..., Mgr_emp# Emp#, ...}
Key {Emp#}
Foreign Key {Rename Mgr_emp# As Emp#}
References Emp;
8. Referential cycles
9. Such matches represent certain relationships.
10.Referential integrity
The database must not contain any unmatched foreign key
values.
Advanced Database System
Yin-Fu Huang
9.10 Keys (Cont.)
 Referential Actions
 Foreign Key { ... } References <relvar>
On Delete <action>
On Update <action>;
 <action>
• Cascade
• Restrict
• No Action
Advanced Database System
Yin-Fu Huang
9.11 Triggers (A Digression)
 A triggered procedure is a precompiled procedure that is stored
along with the database and invoked automatically whenever some
specified event occurs.
e.g., Create Trigger London_Supplier_Insert
Instead Of Insert On London_Supplier
Referencing New Row As R
For Each Row
Insert Into S ( S#, Sname, Status, City )
Values ( R.S#, R.Sname, R.Status, ‘London’) ;
 The event
 The condition
 The action
Advanced Database System
Yin-Fu Huang
9.12 SQL Facilities
 SQL does not support type constraints at all.
 SQL does support attribute constraints.
 SQL does not support relvar constraints as such.
 base table constraints
 SQL does not support database constraints as such.
 general constraints (“assertions”)
Advanced Database System
Yin-Fu Huang
9.12 SQL Facilities (Cont.)
 Base Table Constraints
1. Candidate keys
Primary Key (<column name commalist>) at most one
Unique (<column name commalist>) any number
2. Foreign keys
Foreign key (<column name commalist>)
References <base table name>
[(<column name commalist>)]
[On Delete <referential action>]
[On Update <referential action>]
• Action: No Action, Restrict, Cascade, Set Default, Set Null
Advanced Database System
Yin-Fu Huang
9.12 SQL Facilities (Cont.)
3. Check constraints
Check (<bool exp>)
e.g.,
Create Table Sp
(S# S# Not Null, P# P# Not Null, Qty Qty Not Null,
Primary Key (S#, P#),
Foreign Key(S#) References S
On Delete Cascade
On Update Cascade,
Foreign Key(P#) References P
On Delete Cascade
On Update Cascade,
Check (Qty  Qty(0) And Qty  Qty(5000)));
Advanced Database System
Yin-Fu Huang
9.12 SQL Facilities (Cont.)
 Assertions
Create Assertion <constraint name>
Check (<bool exp>);
Drop Assertion <constraint name>;
e.g., Create Assertion SC1 Check
(Not Exists (Select * From S
Where
S.Status < 0
Or
S.Status > 100));
 Deferred Checking
 In SQL, constraints can be defined to be Deferrable or Not
Deferrable.
Advanced Database System
Yin-Fu Huang
9.12 SQL Facilities (Cont.)
 Deferrable constraints can be dynamically switched on and
off by means of the statement
Set Constraints <constraint name commalist> <option>;
• option: Immediate, Deferred
 Triggers
Create Trigger <trigger name>
<before or after> <event> On <base table name>
[ Referencing <naming commalist> ]
[ For Each <row or statement> ]
[ When ( <bool exp> ) ] <action>;
Drop Trigger <trigger name>;
Advanced Database System
Yin-Fu Huang
The End.
Advanced Database System
Yin-Fu Huang