Integrity
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Integrity PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Integrity

Integrity

Keys


Candidate 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

    • Key {<attribute name commalist>}


Example of candidate keys

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#};


What are candidate keys for

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


Superkey

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


Primary keys alternate keys

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


Foreign keys

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


Foreign keys cont 2

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


Foreign keys cont 3

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


Foreign keys cont 4

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


Foreign keys cont 5

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;


Foreign keys cont 6

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


Foreign keys cont 7

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!


Foreign keys cont 8

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


Referential actions for deletion

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


Referential actions for update

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


Referential action example

Referential action example

  • For our Supplier and parts database

    VAR SP BASE RELATION {…}…

    FOREIGN KEY {S#} REFERENCES S

    ON DELETE CASCADE;


Referential action for more than two relations

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


  • Login