5nf - Higher Normalization Higher Overview Overview Join...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 nonloss-decomposed into two relvars that nonloss decomposed projections but can be nonloss-decomposed into three or more . nonloss A relvar is “n-decomposable” if it can be nonloss-decomposed into n relvar is if nonloss decomposed projections. projections. Consider relvar SHIPMENTS from the supplier-parts-projects. 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 n-decomposable for some n>2 if and only if it satisfies some such (n-way) 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 3-decomposable [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 join-dependency (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 Form-5nf 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 un-expressed 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 non-loss way, and in dependency-preserving 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.

Ask a homework question - tutors are online