250 likes | 643 Vues
Relational data integrity . Lecture 8 . Outline. integrity constraints and data definition candidate keys foreign keys nulls domains conditional expressions normal forms. Constraints. in real life systems constraints exist between data values
E N D
Relational data integrity Lecture 8
Outline • integrity constraints and data definition • candidate keys • foreign keys • nulls • domains • conditional expressions • normal forms
Constraints • in real life systems constraints exist between data values • it would be useful to communicate these constraints to the database system • data is associated with a meaning • stating some constraints on data describing a part of the meaning • 90% should be spent on integrity constraints definition
Examples of possible inaccuracies how would you express, in NL, integrity constraints that would avoid the above situations?
Types of integrity constraints • integrity constraint • application specific integrity constraints • examples • mechanisms • domains • conditional expressions • normal forms • generic (inherent to the relational model - resulting from definitions) • entity integrity : primary key • referential integrity : foreign key
Data definition • integrity constraints definition in a relational language should include • primary key definition • candidate keys definition • foreign key definition - including foreign key rules • conditional expressions
Example - SQL data definition (in brief) • CREATE TABLE <relation name> ( • @<attribute definition><conditional expression>, • <primary key definition>, • @<candidate key definition>, • @<foreign key definition>, • @<conditional expressions> • ) ; • <primary key definition> ::= PRIMARY KEY ( <set of attributes> ) • <candidate key definition > ::= CANDIDATE KEY ( <set of attributes> ) • <foreign key definition> ::= FOREIGN KEY ( <set of attributes> ) • REFERENCES <relation name> • ON DELETE <option> • ON UPDATE <option> • CREATE ASSERTION <name> CHECK <conditional expression>
Candidate key • candidate key • uniqueness property • irreducibility property • entity integrity constraint • simple/composite • primary/alternate
Foreign key • foreign key (FK) • corresponding candidate key (CK) in another relation • FK CK such that FK = CK (reverse not required) • target/referenced relation/tuple | referring relation/tuple • referential integrity constraint • foreign keys and PostgreSQL
What happens if ... • short saving accounts are not offered by the bank anymore? • the “code” and name for small saving accounts is to be changed to ‘i-sav’ and ‘instant saving’ respectively? • the interest for s-sav is to be decreased by 0.3%?
Foreign key rules • the modifications are performed in the REFERRED relation • rules • ON DELETE RESTRICT • ON DELETE CASCADE • ON UPDATE RESTRICT • ON UPDATE CASCADE • split into four groups: think of one example for each situation; don’t use the study guide
Examples • on delete restrict • students and books • on delete cascade • employees and children • on update restrict • students and modules • on update cascade • employees and departments
Nulls • representing missing/unavailable information • primary key and nulls • foreign key and nulls
Domains • expressing integrity constraints on scalar values • constraints on permissible scalar values • constraints on the applicability of scalar operators • SQL • does not support domains • offers other mechanisms
Conditional expressions • will be studied with SQL • pointer forward: • the university’s database:“a student has two choose two options (1/2cu courses) in the final year; if the students chooses an extra optional course than the final year project will count as only 1/2cu”
FDs, MDs, and JDs • particular constraints • functional dependencies • multiple dependencies • join dependencies • expressed by means of normal forms • extensively studied in the following lectures
Integrity constraints - further considerations • state and transition integrity constraints • example • when and how are the integrity constraints applied • depends on the type • ‘normal forms’ and ‘domains’ - always • entity and referential - after each transaction • conditional expressions • default : after each transaction • other possibilities? • correct database = the logical AND of the set of integrity constraints is satisfied after each transaction
Summary • the relational data model • data objects • operators • integrity constraints • SQL implements the relational model • the subject of the next lectures • you know what a relational model is, but do not know yet how to design one