1 / 18

Integrity

Integrity. Keys. Candidate Keys. K: a set of attributes of relvar R K is a candidate key for R iff Uniqueness No legal values of R contain two distinct tuples with the same value of K Irreducibility(minimality) No proper subset of k has the uniqueness Syntax

bin
Télécharger la présentation

Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Integrity Keys

  2. Candidate Keys • K: a set of attributes of relvar R • K is a candidate key for R iff • Uniqueness • No legal values of R contain two distinct tuples with the same value of K • Irreducibility(minimality) • No proper subset of k has the uniqueness • Syntax • Key {<attribute name commalist>}

  3. Example of Candidate keys • Ex) VAR S BASE RELATION {S# S#, SNAME NAME, STATUS INTEGER, CITY CHAR} KEY {S#}; VAR SP BASE RELATION {S# S#, P# P#, QTY QTY} KEY {S#, P#};

  4. What are candidate keys for? • The basic tuple-level addressing mechanism in a relational system • Ex) • S WHERE S# = S#(‘S3’)  at most one tuple • S WHERE CITY = ‘Paris’  unpredictable number of tuples

  5. Superkey • Superkey • Uniqueness property • Not necessarily irreducibility property • If SK is a superkey for relvar R and A is an attribute of R • Then the functional dependency SK A holds true in R Ex) in relation S • {S#}, {S#, SNAME}, {S#, CITY}, …., are superkeys

  6. Primary keys, alternate keys • Possible to have more than one candidate key • Primary key • Exactly one of candidate keys be chosen • Alternate keys • The remainder, if any • The choice of which is to be primary • arbitrary

  7. Foreign keys • In supplier-and-parts database • A given value for S# of relation SP should be permitted to appear in the database • only if that same value also appears as a value of the primary key S# of relation S • Definition • Let R2 be a relvar. Then foreign key in R2 is a subset of the set of attributes of R2, say FK, such that • There exists a base relation R1 with a candidate key CK • For all time each value of FK in the current value of R2 is identical to the value of CK in some tuple in the current value of R1

  8. Foreign keys (cont.2) • Points • The definition requires every value of a given foreign key to appear as a value of the matching candidate key • The converse of foreign key definition is not a requirements • Ex) the supplier S5 appears in relvar S, but not in relvar SP • Composite foreign key iff the candidate key it matched is composite too • The same name and type as the corresponding component of the matching candidate key

  9. Foreign Keys (cont.3) • Terminology • Referenced tuple(target tuple): candidate key value • Referential integrity: valid foreign key value • Referential diagram • Represents referential constraint • Ex) S <- SP -> P

  10. Foreign keys(cont.4) • Referential path • A relation can be • A referenced relation and • A referencing relation • Ex) R2 is the case R3 -> R2 -> R1 • In general, there is a referential constraint from Rn to R(n-1), a referential constraint from R(n-1) to R(n-2) ,…, R2 to R1, we write Rn -> R(n-1) -> …R2 -> R1

  11. Foreign keys(cont.5) • Self-referencing • A relation can • reference itself • Ex) VAR EMP BASE RELATION {EMP# EMP#, …………. MGR_EMP# EMP#, …………..} PRIMARY KEY {EMP#} FOREIGN KEY {RENAME MGR_EMP# AS EMP#} REFERENCES EMP;

  12. Foreign keys(cont.6) • Referential cycle • If Rn includes a foreign key referring to R(n-1), and • R(n-1) includes a foreign key referring to R(n-2), and • …… • If R1 includes a foreign key referring to Rn. Rn -> R(n-1) ->……->R1 -> Rn

  13. Foreign keys(cont.7) • Caution • The common attribute CITY in S and P does NOT have foreign key relationship • CITY is not a candidate key of S and P!

  14. Foreign keys(cont.8) • Referential integrity rule • The database must not contain any unmatched foreign key values • If B references A, then A must exist • ‘Foreign key’ and ‘referential integrity’ are defined in terms of each other

  15. Referential actions for deletion • What should happen on an attempt to delete the target of a foreign key reference? • RESTRICTED • Restricted to the case where there is no matching • CASCADES • Cascades to delete those matching also

  16. Referential actions for update • What should happen on an attempt to update the target of a foreign key reference? • RESTRICTED • Restricted to the case where there is no matching • CASCADES • Cascades to update the foreign key in those matching also

  17. Referential action example • For our Supplier and parts database VAR SP BASE RELATION {…}… FOREIGN KEY {S#} REFERENCES S ON DELETE CASCADE;

  18. Referential action for more than two relations • Assume we have the following references • R3->R2->R1 and • Referential constraint is CASCADE • Then • Deleting tuple of R1 implies deleting certain tuple of R2 • Deleting tuple of R2 implies deleting certain tuple of R3 • If one of DELETEs fails, entire operation fails and database remains unchanged • Atomic operation

More Related