Integrity

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

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 {}
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?
• 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
• 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
• 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
• 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)
• 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)
• 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)
• 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)
• 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)
• 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)
• 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)
• 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
• 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
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 to update the foreign key in those matching also
Referential action example
• For our Supplier and parts database

VAR SP BASE RELATION {…}…

FOREIGN KEY {S#} REFERENCES S