Transcript Lecture 8 Week 9 - Goldsmiths Homepages Server

5NF and other normal forms
5NF and other normal forms
1
5NF and other normal forms
Outline







n-decomposability
3D constraint
join dependency
5NF
non-5NF - update anomalies
problems in bringing a relation to 5NF
other normal forms
2
5NF and other normal forms
Always two projections?
 so far every relation was non-loss
decomposable into two projections
 is this always possible?
 n-decomposable relations
3
5NF and other normal forms
Courses - tutors - levels (CTL)
Course
Tutor
Level
Databases
M. Ursu
Level3
Databases
M. Marman
Level2
Programming
M. Ursu
Level2
Databases
M. Ursu
Level2
4
5NF and other normal forms
CTL - 2 attribute projections
Course
Tutor
Databases
M. Ursu
Databases
M. Harman
Programming M. Ursu
CT
Course
Tutor
M. Ursu
M. Harman
M. Ursu
Level
Databases
Level3
Databases
Level2
Level
Level3
Level2
Level2
TL
CL Programming Level2
5
5NF and other normal forms
CTL - 3-decomposable
 the join of any two projections is not CTL; e.g:
join(CT, TL)
Extra!
Course
Tutor
Level
Databases
M. Ursu
Level3
Databases
M. Ursu
Level2
Databases
M. Marman
Level2
Programming M. Ursu
Level3
Programming M. Ursu
Level2
6
5NF and other normal forms
Constraint 3D
Let R be a degree 3 relation.
IF
(a, b, x)  R
AND
(a, y, c)  R
AND
(z, b, c)  R
THEN (a, b, c)  R
7
5NF and other normal forms
Constraint 3D illustrated on the CTL relation
IF
AND
AND
THEN
tutor t1 teaches subject s1
level l1 studies subject s1
tutor t1 teaches level l1
tutor t1 teaches subject s1 for
level l1
 note: this constraint is not expressed in CTL
8
5NF and other normal forms
Constraint 3D and Join Dependency
 4NF does not express the constraint 3D
 the constraint 3D is a facet of a more general
constraint: join dependency
9
5NF and other normal forms
Join dependency
Let R be a relation. Let A, B, ..., Z be arbitrary
subsets of R’s attributes. R satisfies the JD
 ( A, B, ..., Z )
if and only if R is equal to the join of its
projections on A, B, ..., Z
10
5NF and other normal forms
5 NF
 R is in 5NF if and only if every join
dependency in R is implied by the candidate
keys of R
 5NF is always achievable
11
5NF and other normal forms
Explanation
 a join dependency,  (A, B, …, Z), is implied
by the candidate keys, K1, …, Km of R
if
 the fact that K1, …, Km are candidate keys
for R determine the fact that R has the JD
 (A, B, …, Z)
12
5NF and other normal forms
Illustration - positive example
 consider R (S_id, S_name, Status, City) with S_id
and S_name candidate keys
  ({S_id, S_name, Status}, {S_id, City}) is a JD
because S_id is a candidate key in R
  ({S_id, S_name}, {S_id, Status}, {S_name, City})
is a JD because S_id and S_name are both
candidate keys in R
13
5NF and other normal forms
Illustration - negative example
 consider CTL (Course, Tutor, Level) with
(Course, Tutor, Level) - candidate key (and an extra
constraint : constraint 3D)
 ({Course, Tutor}, {Course, Level}, {Tutor, Level})
is a JD, but this is not due to the CK, but to the
constraint 3D
 if CTL had not had constraint 3D, would it have been
in 5NF?
14
5NF and other normal forms
Not 5NF - update anomalies
 CTL satisfies
  ( {Course, Tutor}, {Tutor, Level}, {Course, Level} )
 insert (Programming, M. Ursu, Level2)
 what else must be done?
Course
Tutor
Level
Databases
M. Ursu
Level3
Databases
M. Marman
Level2
15
5NF and other normal forms
Not 5NF - update anomalies
 CTL satisfies the same JD as before
 delete (Databases, M. Ursu, Level2)
 what else must be done?
Course
Tutor
Level
Databases
M. Ursu
Level3
Databases
M. Marman
Level2
Programming
M. Ursu
Level2
Databases
M. Ursu
Level2
16
5NF and other normal forms
JDs and MVDs
 Fagin’s theorem restated
 R ( A, B, C ) satisfies  ( AB, AC ) if and only if it
satisfies the MVDs A
B|C
 JD is the most general form of dependency
(read as determination) possible between the
attributes of a relation (in the relational
model)
17
5NF and other normal forms
Activity
 Is 4NF subsumed by 5NF? Can you prove
this using Fagin’s theorem and the definitions
for 4 and 5 NF?
18
5NF and other normal forms
Problems in bringing a relation to 5NF
 check whether all JDs are implied by the
candidate keys
 Fagin : provides an algorithm for doing this for any relation,
given all its JDs and all candidate keys
 discover all JDs for a given relation
 they do not have that intuitive meaning as FDs or MVDs
19
5NF and other normal forms
Concluding remarks
 5NF is the ultimate normal form with respect
to projection / join
 5NF is guaranteed to be free of all anomalies
that can be eliminated via projections
 determining whether a relation is in 4NF but
not in 5NF is still fuzzy
 very rare in practice
20
5NF and other normal forms
Recap
 JD - a more general constraint than MD
 a relation can be in 4NF and have un-expressed JDs
 this results in update anomalies
 such a relation can be decomposed (via projection)
into an equivalent set of 5NF relations
 a relation is 5NF if all its JDs are deducible from its
candidate keys
 for a relation in 4NF but not in 5NF, an unexpressed
JD is a possible decomposition (towards 5NF)
21
5NF and other normal forms
Other normal forms
 FDs, MVDs or JDs are not used
 domain-key normal form
 R is in DK/NF if and only if every constraint of R is a
logical consequence of domain constraints and
(candidate) key constraints
 restriction-union normal form
 decomposing operator: restriction
 abusing the language it can be said that: this
normalisation theory is orthogonal on the “projection”
normalisation theory
22