Database Normal Forms Quiz

Normal Forms

• 1NF: All attributes depend on the key.
• 2NF: All attributes depend on the whole key.
• 3NF: All attributes depend on nothing but the key.

Second Normal Form

• 2NF is defined for relations with multiple candidate keys.
• 2NF requires that every non-prime attribute is fully functionally dependent on every key of the relation.
• A relation is in 2NF if every non-prime attribute is not partially dependent on any key.

Third Normal Form

• 3NF is also defined for relations with multiple candidate keys.
• A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on any key.
• A non-prime attribute is transitively dependent on a key if it depends on another non-prime attribute that itself depends on the key.

Boyce-Codd Normal Form

• BCNF is a stricter form of normalization than 3NF.
• BCNF requires that every determinant of a nontrivial, left-irreducible functional dependency must be a candidate key.
• BCNF ensures that every functional dependency is based on a candidate key.

Fourth Normal Form

• 4NF addresses multi-valued dependencies (MVDs).
• A relation is in 4NF if it is in 3NF and every MVD has a determinant that is also a candidate key.
• 4NF ensures that a relation is free from redundant multi-valued dependencies.

Fifth Normal Form

• 5NF addresses join dependencies.
• A relation is in 5NF if it is in 4NF and every join dependency is implied by the candidate keys of the relation.
• 5NF ensures that a relation is free from redundant join dependencies.

Domain-Key Normal Form

• DKNF is a stricter form of normalization than 5NF.
• DKNF ensures that all constraints on the relation are logical consequences of the definition of keys and domains.
• DKNF is not always achievable.

Example Problems

• Problem 1: Consider a relation R(A,B,C,D,E) with functional dependencies: A→B, BC→E, and D→A. The key for R is CD.
• Problem 2: Consider a relation R(A,B,C,D,E) with functional dependencies: A→D, C→AB, and DB→E. The key for R is C.
• Problem 3: Consider the relation CARSALE(car#, date_sold, salesman#, commission%, discount). The relation is in 1NF but not in 2NF or 3NF. Normalization can be achieved by creating three relations:
  • R1(car#, date_sold, salesman#)
  • R2(salesman#, commission%)
  • R3(date_sold, discount)
• Problem 4: Given a relation R = {A, B, C, D, E, H} with functional dependencies F = {{A → BC}, {CD → E}, {E → C},{D → A, E, H}, {A, B, H → B, D},{D, H → B, C}}. The key for R is D.
• Problem 5: The relation TRIP (trip_id, startdate, cities_visited, cards_used) has functional dependencies: trip_id → startdate and multi-valued dependencies: trip_id →→ cities_visited and trip_id →→ cards_used. Normalization requires three relations:
  • R1(trip_id, startdate)
  • R2(trip_id, cities_visited)
  • R3(trip_id, cards_used)
• Problem 6: A 3-decomposable relation is a relation where every tuple can be reconstructed from combining any two projections of the relation.
• Problem 7: A join dependency specifies a constraint stating that every legal state of a relation should have a non-additive join decomposition into its projections.
• Problem 8: A join dependency is a generalization of a multi-valued dependency.

Normalization in Practice

• Normalization beyond 3NF is rarely done in practice.
• 5NF is often difficult to detect and may not be achievable for a given relation.
• DKNF is optimal in terms of normalization but is not achievable for all relations.