Relational Database Schema Designer Using Bernstein’s Algorithm

1. Relational Database Schema Designer Using Bernstein’s Algorithm Project Team: P10 adinda | daryl | xiaojie | woan ni

2. Introduction Non- 3NF 3NF Bernstein’s Algorithm

3. Key Features FD VALIDATION 3NF COMPLIANCE GUI

4. Key Features MANAGE SHORTCOMINGS STEP-BY-STEP CLOSURE

5. Software Demonstration

6. Code Implementation PRE-STEP FUNCTION DEPENDENCY FUNCTION DEPENDENCY FUNCTION DEPENDENCY FUNCTION DEPENDENCY STEP 1 FUNCTION DEPENDENCY FUNCTIONAL DEPENDENCY FUNCTIONAL DEPENDENCY 3NF RELATION FUNCTIONAL DEPENDENCY : : STEP 6 BERNSTEIN’S INPUT OUTPUT

7. Pre-step Merged dependencies PRE-STEP Merge same LHS Derive closures FUNCTION DEPENDENCY FUNCTION DEPENDENCY Validated dependencies FUNCTIONAL DEPENDENCY Functional Dependency FUNCTION DEPENDENCY FUNCTION DEPENDENCY FUNCTION DEPENDENCY FUNCTIONAL DEPENDENCY Functional Dependency Closures FUNCTION DEPENDENCY FUNCTION DEPENDENCY FUNCTIONAL DEPENDENCY Closure

8. STEP 1 LHS1 is subset of LHS2 LHS1 = A RHS1 = E no STEP 1 Remove extraneous attributes A → E yes A subset of RHS1, x, is subset of LHS2 LHS2 = AE RHS2 = B no AE → B yes LHS2 = LHS2 - x

9. STEP 1 A is subset of AE LHS1 = A RHS1 = E no STEP 1 Remove extraneous attributes A → E yes A subset of E, E, is subset of AE LHS2 = AE RHS2 = B no AE → B yes LHS2= AE - E = A

10. STEP 1 LHS1 = A RHS1 = E STEP 1 Remove extraneous attributes A → E A → E LHS2 = AE RHS2 = B AE → B A → B

11. STEP 1 LHS1 is a subset of LHS2 and RHS1 is a subset of RHS2 LHS1 = A RHS1 = E no STEP 1 Remove extraneous attributes A → E yes LHS2 - LHS1 = RHS2 - RHS1 LHS2 = AB RHS2 = BE no AB → BE yes Remove FD2

12. STEP 1 A is a subset of AB and E is a subset of BE LHS1 = A RHS1 = E no STEP 1 Remove extraneous attributes A → E yes AB - A = BE - E LHS2 = AB RHS2 = BE no AB → BE yes Remove AB → BE

13. STEP 1 LHS1 = A RHS1 = E STEP 1 Remove extraneous attributes A → E A → E LHS2 = AB RHS2 = BE AB → BE

14. STEP 2 RHS1 is a subset of LHS2 LHS1 = A RHS1 = BE no STEP 2 Find minimal cover A → BE yes Append RHS2 to closure of LHS1 LHS2 = E RHS2 = C E → C Remove FD2

15. STEP 2 E is a subset of BE LHS1 = A RHS1 = BE no STEP 2 Find minimal cover A → BE yes Append C to closure of A LHS2 = E RHS2 = C E → C Remove E → C

16. STEP 2 LHS1 = A RHS1 = BE STEP 2 Find minimal cover A → BE A+ → ABCE LHS2 = E RHS2 = C E → C

17. STEP 3 STEP 3 Partition into relations LHS1 = LHS2 A → B no A → C yes Put FD1 and FD2 in one relation, H B → C

18. STEP 3 H1 A → B STEP 3 Partition into relations A → B A → C A → C H2 B → C B → C

19. Group FDs with the same closure STEP 4 Group has more than 1 elements FD1 STEP 4 Merge equivalent keys FD2 Find equivalent FDs and put them in J Do nothing to corresponding H : : Find union of FDs in group FDN Subtract J from union as H

20. STEP 4 J J FD1 STEP 4 Merge equivalent keys J J FD2 H H : : H H H FDN

21. compare H(x) with H(y) where x != y STEP 5 Get closure of FD of H(x), C1 C1 = LHS of FD of H(y) STEP 5 Remove transitive dependencies H1 H H H compare H(x) with H(y+1) FD Get closure of FD of H(y), C2 C2 = RHS of FD of H(x) H2 H H FD Remove FD of H(x) Add Js to Hs

22. STEP 6 Relation STEP 6 Generate relations H H H FD Combine LHS and RHS as attributes Relation Set LHS of FD as key H H FD

23. Bernstein’s Shortcoming 1 Problem Cannot guarantee losslessness Solution Combine all attributes from step 6 Find relations that are subset of others Find missing attributes Obtain keys to form new relation

24. Bernstein’s Shortcoming 1 R = {A, B, C, D} F = {A, B → C, A → C, D} R1 = {A, C, D} [after Bernstein’s] R2 = {A, B} [new relation added]

25. Bernstein’s Shortcoming 2 Problem Does not include all keys Solution For two arbitrary keys (X,Y) of all keys that can be formed among relations • If the closure X+ shares some attributes with Y, • A new key Z formed, where Z = (Y - (X+ ∩ Y)) ∪ X • If Z ⊆ R (where Y is a key in R) and (all the keys in R)⊄Z • Z is the missing key. • Repeat above steps until no more new key found.

26. Bernstein’s Shortcoming 2 R = {A, B, C, D} F = {A, B → C, C → B} R1 = {A, B, C, D} [after Bernstein’s] R2 = {C, B} [after Bernstein’s]

27. Bernstein’s Shortcoming 3 • Problem • Superfluous attributes Solution • Get all functional dependencies that cannot be retrieved from J set. For two arbitrary functional dependencies from above set (X → Y and X’ → Y) • If X’+ ⊆ X+ • All attributes in X’ are all redundant attributes • Remove all these attributes from all the relations that have been affected by any J set

28. Bernstein’s Shortcoming 3 R = {A, B, C, D, E, F} F = {A, D → B, B → C, C → D, A, B → E, A, C → F} R1 = {A, B, C, D, E, F} R2 = {B, C} R3 = {C, D}

29. Bernstein’s Shortcoming 5 • Problem • Superfluous redundant attributes Solution • For all functional dependencies that have been computed after step 5 • Take two arbitrary functional dependencies (X → Y and X’ → Y’) • If Y ∩ Y’ ≠ ∅ • If Y ∩ Y’ ⊄keys(R) • Remove all the attributes in Y ∩ Y’ from Y • Repeat above steps until no more attribute can be removed

30. Bernstein’s Shortcoming 5 R = {Model#, Serial#, Price, Color, Name, Year} F = {{Model#, Serial#} → {Price, Color}, {Model# → Name}, {Serial# → Year}, {Name, Year → Price}} R1 (Model#, Serial#, Price, Color) R2 (Model#, Name) R3 (Serial#, Year) R4(Name, Year, Price)

31. Questions and Answers