Example 1

• A relational schema R = (A, B, C)
• Functional dependencies: AB → C
• Keys ?
• If AB → C is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, B)
• No other minimal keys
• Normal form ?
• Left hand side of AB → C is a minimal key K = (A, B)
• BCNF

Example 2

• A relational schema R =(A, B, C)
• Functional dependencies: AB → C, C → B
• Keys ?
• If AB → C is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, B)
• If C → B then through augmentation rule AC → AB
• If AC → AB is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, C)
• Normal form ?
• Not BCNF because left hand side of C → B is not a minimal key
• 3NF because right hand side of C → B is a prime attribute
• Decomposition into BCNF ?
• R1 = (B, C), R2 = (A, C)

Example 3

• A relational schema R = (A, B, C)
• Functional dependencies: AB → C, C → B, C → A
• Keys ?
• If AB → C is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, B)
• If C → B and C → A then through union rule C → AB
• If C → AB is valid in R and it covers entire relational schema then its left hand side is a minimal key (C)
• Normal form ?
• BCNF because left hand side of each functional dependency is a minimal key

Example 4

• A relational schema R = (A, B, C)
• Functional dependencies: A → B
• Keys ?
• If A → B is valid in R then through augmentation rule AC → BC
• If AC → BC is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, C)
• Normal form ?
• not 2NF because a nonprime attribute B functionally depends (A → B) on a subset of primary key (A, C)
• Decomposition into BCNF ?
• R1 = (A, B), R2 = (A, C) or
• R1 = (A, B), R2 = (B, C)

Example 5

• A relational schema R = (A, B, C)
• Functional dependencies: A → B, B → A
• Keys ?
• If A → B then through augmentation rule AC → BC
• If AC → BC is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, C)
• If B → A then through augmentation rule BC → AC
• If BC → AC is valid in R and it covers entire relational schema then its left hand side is a minimal key (B, C)

Example 5

• Normal form ?
• Not BCNF because left hand side of A → B is not a minimal key
• 3NF because right hand side of A → B is a prime attribute and right hand side of B → A is a prime attribute
• Decomposition into BCNF ?
• R1 = (A, B), R2 = (A, C) or
• R1 = (A, B), R2 = (B, C)

Example 6

• A relational schema R = (A, B, C)
• Functional dependencies: A → B, B → C
• Keys ?
• If A → B and B → C then through transitivity rule A → C
• If A → B and A → C then through union rule A → BC
• If A → BC is valid in R and it covers entire relational schema then its left hand side is a minimal key (A)
• Normal form ?
• Not 3NF because a non prime attribute C is transitively dependent on primary key A
• 2NF because no nonprime attribute depends on a part of primary key

Example 6

• Decomposition into BCNF ?
• R1 = (A, B), R2 = (B, C) or
• R1 = (A, B), R2 = (A, C)

Example 7

• A relational schema R =(A, B, C, D)
• Functional dependencies: A → B, A → C, B → D
• Keys ?
• If A →B and A → C then through union rule A → BC
• If A → B and B → D then through transitivity rule A → D
• If A → BC and A → D then through union rule A → BCD
• If A → BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A)
• Normal form ?
• Not 3NF because a non prime attribute D is transitively dependent on primary key A
• 2NF because no nonprime attribute depends on a part of primary key

Example 7

• Decomposition into BCNF ?
• R1 = (A, B, C), R2 = (B, D) or
• R1 = (A, B, C), R2 = (A, D)

Example 8

• A relational schema R =(A, B, C, D)
• Functional dependencies: A → B, B → D, C → B
• Keys ?
• If A → B and B → D then through transitivity rule A → D
• If A → D and A → B then through union rule A → BD
• If A → BD then through augmentation rule AC → BCD
• If AC → BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, C)
• If C → B and B → D then through transitivity rule C → D
• If C → D and C → B then through union rule C → BD
• If C → BD then through augmentation rule AC → ABD
• If AC → BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, C)

Example 8

• Normal form ?
• Not 2NF because a nonprime attribute B depends on a part of a primary key
  (A, C)
• Decomposition into BCNF ?
• R1 = (A, B), R2 = (B, C), R2 = (B, D)

Example 9

• A relational schema R =(A, B, C, D)
• Functional dependencies: A → B, A → C, B → A, B → C
• Keys ?
• If A → B and A → C then through union rule A → BC
• If A → BC then through augmentation rule AD → BCD
• If AD → BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, D)
• If B → A and B → C then through union rule B → AC
• If B → AC then through augmentation rule BD → ACD
• If BD → ACD is valid in R and it covers entire relational schema then its left hand side is a minimal key (B, D)

Example 9

• Normal form ?
• Not 2NF because a nonprime attribute C depends on a part of a primary key
  (B, D)
• Decomposition into BCNF ?
• R1 = (A, B), R2 = (B, C), R2 = (A, D)

Example 10

• A relational schema R = (A, B, C, D)
• Functional dependencies: AB → C, C → D, D → A, D → B
• Keys ?
• If AB → C and C → D then through transitivity rule AB → D
• If AB → D and AB → C then through union rule AB → CD
• If AB → CD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A, B)
• If D → A and D → B then through union rule D → AB
• If D → AB and AB → C then through transitivity rule D → C
• If D → C and D → AB then D → ABC
• If D → ABC is valid in R and it covers entire relational schema then its left hand side is a minimal key (D)

Example 10

• If C → D and D → AB then through transitivity rule C →AB
• If C → D and C → AB then through union rule C →ABD
• If C → ABD is valid in R and it covers entire relational schema then its left hand side is a minimal key (C)
• Normal form ?
• BCNF because left hand side of each functional dependency is a superky

Example 11

• A relational schema R = (A, B, C, D)
• Functional dependencies: A → B, B → C, C → D, D → A
• Keys ?
• If A → B and B → C then through transitivity rule A → C
• If A → C and C → D then through transitivity rule A → D
• If A → B and A → C and A → D then through union rule A → BCD
• If A →BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A)
• If B → C and C → D then through transitivity rule B → D
• If B → D and D → A then through transitivity rule B → A
• If B → C and B → D and B → A then through union rule B → ACD
• If B →ACD is valid in R and it covers entire relational schema then its left hand side is a minimal key (B)

Example 11

• If C → D and D → A then through transitivity rule C → A
• If C → A and A → B then through transitivity rule C → B
• If C → A and C → B and C → D then through union rule C → ABD
• If C → ABD is valid in R and it covers entire relational schema then its left hand side is a minimal key (C)
• If D → A and A → B then through transitivity rule D → B
• If D → B and B → C then through transitivity rule D → C
• If D → A and D → B and D → C then through union rule D → ABC
• If D → ABC is valid in R and it covers entire relational schema then its left hand side is a minimal key (D)
• Normal form ?
• BCNF because left hand side of each functional dependency is a superky

References

• T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 14.5 The Process of Normalization, Chapter 14.6 First Normal Form (1NF), Chapter 14.7 Second Normal Form (2NF), Chapter 14.8 Third Normal Form (3NF), Chapter 14.9 General definitions of 2NF and 3NF, Chapter 15.2 Boyce-Codd Normal Form (BCNF), Chapter 15.3 Review of Normalization Up to BCNF, Pearson Education Ltd, 2015