Normalization ( 4NF and 5NF): Normalization ( 4NF and 5NF)
Multivalued Dependencies: Multivalued Dependencies Course teacher book Physics Green Mechanics Physics Green Optics Physics Brown Mechanics Physics Brown Optics Maths Green Mechanics Maths Green Vectors Maths Green Geometry
PowerPoint Presentation: The relation schema CTB is in BCNF There is redundancy. The redundancy can be eliminated by decomposing CTB into CT and CB
PowerPoint Presentation: course teacher Physics Green Physics Brown Maths Green CT Course Book Physics Mechanics Physics Optics Maths Mechanics Maths Vectors Maths Geometry CB
PowerPoint Presentation: Let R be a relation schema and let X and Y be subsets of the attributes of R. The multivalued dependency X →→ Y is said to hold over R if , in every legal instance r of R , each X value is associated with a set of Y values and this set is independent of the values in the other attributes
Fourth Normal Form: Fourth Normal Form Let R be a relation schema , X and Y be nonempty subsets of the attributes of R, an F be a set of dependencies that includes both FDs and MVDs. R is said to be in fourth normal form ( 4NF) , if , for every MVD X →→ Y that holds over R , one of the following statement is true: Y X or XY = R , or X is a super key
Join Dependencies: Join Dependencies A join dependency (JD) { R1, …., Rn } is said to hold over a relation R if R1 , ……, Rn is a lossless-join decomposition of R
Fifth Normal Form: Fifth Normal Form A relation schema R is said to be in fifth normal form ( 5 NF) if for every JD {R1, …., Rn } that holds over R , one of the following statement is true Ri = R for some i , or The JD is implied by the set of those FDs over R in which the left side is a key for R.
PowerPoint Presentation: Teacher Class Subject 100 IV English 100 V Hindi Teacher Class 100 IV 100 V Class Subject IV English V Hindi Subject Teacher English 100 Hindi 100 Teacher Class Subject 100 IV English 100 V Hindi
PowerPoint Presentation: Teacher Class Subject 100 IV English 100 V Hindi 101 IV Hindi Teacher Class 100 IV 100 V 101 IV Class Subject IV English V Hindi IV Hindi Subject Teacher English 100 Hindi 100 Hindi 101
PowerPoint Presentation: Teacher Class Subject 100 IV English 100 IV Hindi 100 V Hindi 101 IV Hindi We get an additional tuple ( 100 , IV , Hindi) which was not in original relation Teacher_class_Subject
PowerPoint Presentation: TitleId Tname Price Discount T1 Oracle 399 15 T2 C 299 10 T3 C++ 199 10 T4 Sybase 100 10 The relation Title has two candidates keys: - TitleId , Tname The Join dependencies are ( TitleId , Tname , Price) , TitleId , Discount) , ( TitleId , Tname ) , ( TitleId , Price) and ( Tname , Discount) These are valid join dependencies since each one contains valis candiate key.The join dependencies are those which contain candidates keys. If a relation is in 5NF the only valid decomposition are those which contain candiate keys. Title relation is in 5NF The relation Teacher_Class_Subject is not in 5NF since its only candidate key ( Teacher#, Class# , Subject#) does not imply that this relation can be decomposed into projections.