- By
**bin** - Follow User

- 142 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about 'Integrity' - bin

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

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

ON DELETE CASCADE;

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

Download Presentation

Connecting to Server..