5 NF and other normal forms 1 5

5 NF and other normal forms 1

5 NF and other normal forms Outline w w w w n-decomposability 3 D constraint join dependency 5 NF non-5 NF - update anomalies problems in bringing a relation to 5 NF other normal forms 2

5 NF and other normal forms Always two projections? w so far every relation was non-loss decomposable into two projections Ÿ is this always possible? w n-decomposable relations 3

5 NF and other normal forms Courses - tutors - levels (CTL) 4

5 NF and other normal forms CTL - 2 attribute projections CT TL CL 5

5 NF and other normal forms CTL - 3 -decomposable w the join of any two projections is not CTL; e. g: join(CT, TL) Extra! 6

5 NF and other normal forms Constraint 3 D 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

5 NF and other normal forms Constraint 3 D illustrated on the CTL relation IF AND THEN tutor t 1 teaches subject s 1 level l 1 studies subject s 1 tutor t 1 teaches level l 1 tutor t 1 teaches subject s 1 for level l 1 w note: this constraint is not expressed in CTL 8

5 NF and other normal forms Constraint 3 D and Join Dependency w 4 NF does not express the constraint 3 D w the constraint 3 D is a facet of a more general constraint: join dependency 9

5 NF 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

5 NF and other normal forms 5 NF w R is in 5 NF if and only if every join dependency in R is implied by the candidate keys of R w 5 NF is always achievable 11

5 NF and other normal forms Explanation w a join dependency, (A, B, …, Z), is implied by the candidate keys, K 1, …, Km of R if w the fact that K 1, …, Km are candidate keys for R determine the fact that R has the JD (A, B, …, Z) 12

5 NF and other normal forms Illustration - positive example w consider R (S_id, S_name, Status, City) with S_id and S_name candidate keys w ({S_id, S_name, Status}, {S_id, City}) is a JD because S_id is a candidate key in R w ({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

5 NF and other normal forms Illustration - negative example w consider CTL (Course, Tutor, Level) with (Course, Tutor, Level) - candidate key (and an extra constraint : constraint 3 D) w ({Course, Tutor}, {Course, Level}, {Tutor, Level}) is a JD, but this is not due to the CK, but to the constraint 3 D w if CTL had not had constraint 3 D, would it have been in 5 NF? 14

5 NF and other normal forms Not 5 NF - update anomalies w CTL satisfies Ÿ ( {Course, Tutor}, {Tutor, Level}, {Course, Level} ) w insert (Programming, M. Ursu, Level 2) w what else must be done? 15

5 NF and other normal forms Not 5 NF - update anomalies w CTL satisfies the same JD as before w delete (Databases, M. Ursu, Level 2) w what else must be done? 16

5 NF and other normal forms JDs and MVDs w Fagin’s theorem restated Ÿ R ( A, B, C ) satisfies ( AB, AC ) if and only if it satisfies the MVDs A B|C w JD is the most general form of dependency (read as determination) possible between the attributes of a relation (in the relational model) 17

5 NF and other normal forms Activity w Is 4 NF subsumed by 5 NF? Can you prove this using Fagin’s theorem and the definitions for 4 and 5 NF? 18

5 NF and other normal forms Problems in bringing a relation to 5 NF w 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 w discover all JDs for a given relation § they do not have that intuitive meaning as FDs or MVDs 19

5 NF and other normal forms Concluding remarks w 5 NF is the ultimate normal form with respect to projection / join w 5 NF is guaranteed to be free of all anomalies that can be eliminated via projections w determining whether a relation is in 4 NF but not in 5 NF is still fuzzy § very rare in practice 20

5 NF and other normal forms Recap w JD - a more general constraint than MD w a relation can be in 4 NF and have un-expressed JDs § this results in update anomalies w such a relation can be decomposed (via projection) into an equivalent set of 5 NF relations w a relation is 5 NF if all its JDs are deducible from its candidate keys w for a relation in 4 NF but not in 5 NF, an unexpressed JD is a possible decomposition (towards 5 NF) 21

5 NF and other normal forms Other normal forms w FDs, MVDs or JDs are not used w 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 w 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