Chapter 9 Integrity Prof YinFu Huang CSIE NYUST

Chapter 9 Integrity Prof. Yin-Fu Huang CSIE, NYUST

9. 1 Introduction n 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 SC 1 Is_Empty (S Where Status < 1 Or Status > 100); Drop Constraint SC 1; Advanced Database System 2

9. 2 A Closer Look n The fact that a given variable is of some given type represents an a prior constraint on the variable in question. n Tutorial D examples: 1. Constraint SC 2 Forall SX (If SX. City = ‘London’ Then SX. Status = 20 End If); 2. Constraint PC 3 If Exists PX (True) Then Exists PX (PX. Color = Color(‘Blue’)) End If; Advanced Database System 3

9. 2 A Closer Look (Cont. ) 3. Constraint SC 4 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 SSP 5 Forall SPX Exists SX (Sx. S# = SPX. S#) 5. Constraint SSP 6 Forall SX Forall SPX (If Sx. S# = SPX. S# Then SX. Status 20 Or SPX. Qty 500 End If); Advanced Database System 4

9. 3 Predicates and Propositions n A constraint as formally stated is a predicate. n 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 5

9. 4 Relvar Predicates and Database Predicates n The relvar predicate for R is the logical And or conjunction of all of the constraints that apply to relvar R. n The Golden Rule No update operation must ever assign to any relvar a value that causes its relvar predicate to evaluate to FALSE. n The database predicate for D is the conjunction of all of those relvar predicates. n 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 6

9. 5 Checking the Constraints n 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! n All constraint checking is immediate? n 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 7

9. 6 Internal vs. External Predicates n a formal construct vs. an informal construct. n what the data means to the system vs. what the data means to the users. n A given internal predicate is the system’s approximation to the corresponding external predicate. n If (s S) = TRUE Then XPS(s) = TRUE If (s S) = FALSE Then XPS(s) = FALSE s S XPS(s) Advanced Database System 8

9. 7 Correctness vs. Consistency n 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 n 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 9

9. 9 A Constraint Classification Scheme n Four broad categories: database, relvar, attribute, and type constraints n 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. n Attribute constraints e. g. , Var S Base Relation { S# S#, Sname Name, Status Integer, City Char } … ; Advanced Database System 10

9. 9 A Constraint Classification Scheme (Cont. ) n 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 TRC 1 Forall SX’ Forall SX ( SX’. S# SX. S# Or SX’. Status SX. Status ); Constraint TRC 2 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 11

9. 10 Keys n Candidate Keys Ø Properties: 1. Uniqueness 2. Irreducibility Ø Key { <attribute name commalist> } Ø Tuple-level addressing mechanism n Primary Keys and Alternative Keys Ø Exactly one of candidate keys the primary key Ø The others alternative keys Advanced Database System 12

9. 10 Keys (Cont. ) n Foreign Keys Ø A foreign key in R 2 is a set of attributes of R 2, say FK, such that: 1. There exists a relvar R 1 (R 1 and R 2 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 R 2 yields a value for FK’ that is identical to the value of CK in some tuple in the current value of R 1. Advanced Database System 13

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 6. S# P# S ← SP → P 6. A given relvar can be both referenced and referencing. R 3 → R 2 → R 1 Advanced Database System 14

9. 10 Keys (Cont. ) 7. Relvars R 1 and R 2 in the foreign key definition are not necessarily distinct. Var Emp Base Relation {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 11. The database must not contain any unmatched foreign key 12. values. Advanced Database System 15

9. 10 Keys (Cont. ) n Referential Actions Ø Foreign Key {. . . } References <relvar> On Delete <action> On Update <action>; Ø <action> • Cascade • Restrict • No Action Advanced Database System 16

9. 11 Triggers (A Digression) n 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 17

9. 12 SQL Facilities n SQL does not support type constraints at all. n SQL does support attribute constraints. n SQL does not support relvar constraints as such. base table constraints n SQL does not support database constraints as such. general constraints (“assertions”) Advanced Database System 18

9. 12 SQL Facilities (Cont. ) n 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 19

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 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(5000))); Advanced Database System 20

9. 12 SQL Facilities (Cont. ) n Assertions Create Assertion <constraint name> Check (<bool exp>); Drop Assertion <constraint name>; e. g. , Create Assertion SC 1 Check (Not Exists (Select * From S Where S. Status < 0 Or S. Status > 100)); n Deferred Checking Ø In SQL, constraints can be defined to be Deferrable or Not Deferrable. Advanced Database System 21

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 n 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 22

The End. Advanced Database System 23