9. Normalization

Question 1

Normalization Goal:

Answer 1

A step-by-step process to replace relations with simpler, more regular structures to reduce unnecessary redundancy and avoid insertion, deletion, and update anomalies, and prevent spurious tuples. Approach is decomposition.

Question 2

Anomalies:

Answer 2

◦ Insertion Anomaly: Cannot insert data about one entity without having data about another.
◦ Deletion Anomaly: Deleting data about one entity unintentionally deletes data about another.
◦ Modification/Update Anomaly: Updating a value may require updating multiple tuples inconsistently.

Question 3

Spurious Tuples:

Answer 3

Incorrect tuples generated when relations are joined, typically when joining on attributes that are not PK/FK combinations. Avoid by ensuring joins are on PK/FK.

Question 4

Properties of Successful Decomposition:

Answer 4

Attribute preservation, Dependency preservation, Lossless join. Heath’s Theorem states R{X,Y,Z} with X→Y can be non-loss decomposed into R1{X,Y} and R2{X,Z}.

Question 5

Normal Forms:

Answer 5

A relation is in a specific normal form if it satisfies certain constraints.
◦ First Normal Form (1NF): A relation is in 1NF if and only if all attribute values are atomic (single-valued). No multivalued, composite attributes, or nested relations. By definition, all relations are in 1NF. To achieve 1NF, move multivalued attributes or repeating groups to a new relation with the original PK as a foreign key.
◦ Second Normal Form (2NF): A relation is in 2NF if it’s in 1NF and every non-key attribute is fully functionally dependent on the PK. Full functional dependency means the dependency X → Y no longer holds if any attribute is removed from X. Partial dependency is when it still holds. Only relevant for composite PKs. To normalize from 1NF to 2NF, remove attributes that are partially dependent on the PK into a new relation, taking the partial key as the PK of the new relation and leaving it as a FK in the original.
◦ Third Normal Form (3NF) / Boyce-Codd Normal Form (BCNF): A relation is in 3NF if it’s in 2NF and no non-key attribute is transitively dependent on a CK. A transitive dependency X → Y via Z exists if X → Z, Z → Y, X is a CK, and Z is not part of a CK. BCNF is stricter: LHS of every FD must be a CK. To normalize from 2NF to 3NF, remove attributes involved in transitive dependencies into a new relation, using the intermediate attribute(s) (Z in X→Z→Y) as the PK of the new relation and leaving it as a FK in the original.

Question 6

Normalization process:

Answer 6

A sequence of steps moving from lower to higher normal forms (e.g., 1NF → 2NF → 3NF/BCNF) by decomposition.

Question 7

Pros:

Answer 7

Reduces redundancy, avoids anomalies, separates entities, not biased towards specific queries.

Question 8

Cons:

Answer 8

More relations means more joins, potentially slower performance for some queries. Normalization can be taken too far.