This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Higher Normalization
Higher Overview
Overview
Join Dependencies [JD]
Join
Fifth Normal Form – 5NF Join Dependency
Join
There exists relvars that cannot be nonlossdecomposed into two
relvars that
nonloss decomposed
projections but can be nonlossdecomposed into three or more .
nonloss
A relvar is “ndecomposable” if it can be nonlossdecomposed into n
relvar is
if
nonloss decomposed
projections.
projections.
Consider relvar SHIPMENTS from the supplierpartsprojects.
relvar
The relvar is all key and involves no nontrivial FDs or MVDs,hence in
relvar is
FDs or MVDs,hence in
4NF.
4NF. SHIPMENTS Join Dependency SHIPMENTS PJ JS Join Dependency
SHIPMENTS Join PJ over
PART_NUMBER Spurious
tuple Join over
PROJECT_NUMBER,
SUPPLIER_NUMBER
SHIPMENTS Join Dependency
SHIPMENTS is equal to the join of its three projections
SHIPMENTS, PJ, and JS is equivalent to: Cyclic nature of the constraint s1 linked p1,p1 linked j1,j1 linked s1
A relvar will be ndecomposable for some n>2 if and only if it
satisfies some such (nway) cyclic constraint. Join Dependency
If (s1,p1,j2),(s2,p1,j1) and (s1,p2,j1) appear in shipments then
(s1,p1,j1) also appears in shipments.
Constraint 3decomposable [3D] is satisfied if and only if the relvar
is equal to the join of certain of its projections – refer to that
constraint as a joindependency (JD).
The constraint 3D is a facet of a more general constraint: join
dependency
dependency Join Dependency
Let R be a relvar, and let A,B, ..., Z be subsets of the attributes of R.
Then we say that R satisfies the JD
* { A, B, ..., Z } [star A, B, ..., Z]
if and only if every legal value of R is equal to the join of its
projections on A, B, ..., Z.
For example, the set of attributes of SHIPMENTS
{SUPPLIER_NUMBER, PART_NUMBER}, PJ and JS, then relvar
SHIPMENTS satisfies the JD *{SHIPMENTS,PJ,JS}.
The relvar shipments with its JD *{SHIPMENTS,PJ,JS}
is 3D decomposed and it removes the update anomalies. Update anomalies
Sample update anomalies in SHIPMENTS SHIPMENTS
If (S2, P1, J1) is inserted, (S1, P1,J1) must also be inserted.
Yet converse is not true. Update anomalies Can delete (S2,P1,J1) without side effects.
If (S1,P1,J1) is deleted, another tuple must also be
deleted >(S2,P1,J1) Fifth Normal Form – 5NF Fifth Normal Form5nf SHIPMENTS PJ JS Concluding remarks
Concluding
5NF is the ultimate normal form with respect to projection / join
5NF
5NF is guaranteed to be free of all anomalies that can be eliminated
5NF
ated
via projections
via
determining whether a relation is in 4NF but not in 5NF is still fuzzy
determining
very rare in practice
very
JD  a more general constraint than MVD
a relation can be in 4NF and have unexpressed JDs
relation
this results in update anomalies
this
such a relation can be decomposed (via projection) into an
equivalent set of 5NF relations
equivalent
a relation is 5NF if all its JDs are deducible from its candidate keys
relation JDs and Fifth NF Normalization  Summary
Given 1NF relvar R and some set of FDs, MVDs, and JDs that
apply to R, systematically reduce to R to a collection of smaller
relvars that are equivalent to R.
The overall process:
1. Take projections of the 1NF relvar to eliminate FD that are
not irreducible – produce a collection of 2NF.
2. Take projections of those 2NF to eliminate transitive FDs
produce a collection of 3NF.
3. Eliminate FD in which the determinant is not a candidate key
– produce a collection of BCNF relvars. Normalization  Summary
Rules:
4. Take projections of those BCNF to eliminate MVDs that are
not also FD – produce a collection of 4NF relvars.
5. Take projections of those 4NF it eliminate JDs that are not
implied by the candidate keys – produce a collection of 5NF Objective – normalization
The process of taking projections at each step must be
done in a nonloss way, and in dependencypreserving
way as well.
Objective of Normalization:
To eliminate certain kinds of redundancy
To avoid certain update anomalies
To produce a design that is a good representation of the real
world
To simplify the enforcement of certain integrity constraints Denormalization
Let R1, R2, .. Rn be the set of relvars. Then denormalizing those
relvars means replacing them by their join R, such that for all i
(i=1,2,..,n) projecting R over the attributes of Ri is guaranteed to
yield Ri again.
The objective is to increase redundancy. ...
View
Full
Document
This note was uploaded on 04/01/2012 for the course CSE,IT 101 taught by Professor Mirunaalini during the Spring '12 term at Indian Institute of Technology, Chennai.
 Spring '12
 Mirunaalini

Click to edit the document details