What is a Database System?
Download
Report
Transcript What is a Database System?
Relational data integrity
Relational data integrity
Lecture 8
1
Relational data integrity
Outline
integrity constraints and data definition
candidate keys
foreign keys
nulls
domains
conditional expressions
normal forms
2
Relational data integrity
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
3
Relational data integrity
Examples of possible inaccuracies
Persons
ID
Name
1
M. Jackson
3
P$%ffY780&&&
3
F. Mercury
Departments
Department
MM01
PPP
DoB
22/12/1960
01/04/2099
07/07/1957
Name
Manufacturing management
Personnel
Income
34000
28000
-50000
Department
MM01
MM01
Dev10
?
No_of_employees
3
4
?
how would you express, in NL, integrity constraints
that would avoid the above situations?
4
Relational data integrity
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
5
Relational data integrity
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
6
Relational data integrity
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>
7
Relational data integrity
Candidate key - example
Registration-no
Name
one candidate key
DOB
Tel-no
Course
…
other candidate keys
8
Relational data integrity
Candidate key
candidate key
uniqueness property
irreducibility property
entity integrity constraint
simple/composite
primary/alternate
9
Relational data integrity
Foreign key - example
Registrations
Students
Address
London
London
York
Leeds
Bath
SName
M. Jagger
S. Smiths
S. Smiths
S. Bruce
J. Kelly
Course
CIS
CIS
MCS
MAS
MAS
SName
M. Jagger
M. Jagger
M. Jagger
S. Smiths
S. Smiths
S. Smiths
S. Smiths
S. Smiths
J. Kelly
J. Kelly
Course
CIS
CIS
CIS
CIS
CIS
CIS
MCS
MCS
MAS
MAS
Module
Languages
Databases
Compilers
Languages
Databases
AI
Languages
Calculus
Calculus
Statistics
10
Relational data integrity
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
11
Relational data integrity
Referential diagram
Students
Tutors
Registrations
Teaching
Modules
12
Relational data integrity
Foreign key rules - motivation
Type
curr
s-sav
l-sav
…
Name
MinDep
Interest
current
short savings
long savings
…
100
1000
10000
…
Name
No
M. Smith
M. Smith
M. Jagger
S. Hunt
…
0099455321
1011334522
0099677432
1011377856
…
2.4
3.3
4.5
…
Type
curr
s-sav
curr
s-sav
…
Balance
-342.22
1500
235.99
2300
…
13
Relational data integrity
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%?
14
Relational data integrity
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
15
Relational data integrity
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
16
Relational data integrity
Nulls
Name
A. Johnson
S. Bruce
P. Harris
DOB
04/66
02/72
10/73
Sex
M
F
NULL
Course
CIS
NULL
MAS
Year1
68
NULL
0
Year2
72
NULL
48
Year3
NULL
NULL
NULL
17
Relational data integrity
Nulls
representing missing/unavailable information
primary key and nulls
foreign key and nulls
18
Relational data integrity
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
19
Relational data integrity
Domains - example of integrity constraints
20
Relational data integrity
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”
21
Relational data integrity
FDs, MDs, and JDs
particular constraints
functional dependencies
multiple dependencies
join dependencies
expressed by means of normal forms
extensively studied in the following lectures
22
Relational data integrity
Normal forms - example
redundant data
Employees
ID
D1
D2
D3
D4
D5
D6
F1
Name
M. Rick
F. Boyle
P. Wale
M. Grin
F. Weller
S. Strauss
P. Johnson
Department
Development
Development
Development
Development
Development
Development
Finance
YearsInService
5
5
5
6
8
8
9
Qualification
programmer
programmer
programmer
analyst
analyst
analyst
accountant
Salary
28000
28000
28000
32000
41000
41000
28000
23
Relational data integrity
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
24
Relational data integrity
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
25