1 / 18

# Integrity - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Integrity' - bin

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

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

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

• 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

• 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

• 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

• 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

• 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

• Terminology

• Referenced tuple(target tuple): candidate key value

• Referential integrity: valid foreign key value

• Referential diagram

• Represents referential constraint

• Ex) S <- SP -> P

• 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

• 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;

• 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

• 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!

• 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

• 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 to delete those matching also

• 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 to update the foreign key in those matching also

• For our Supplier and parts database

VAR SP BASE RELATION {…}…

FOREIGN KEY {S#} REFERENCES S

• Assume we have the following references

• R3->R2->R1 and