CS145 Lecture Notes (6) -- Redundancy, Redundancy, and Normalization

• Q: Is one schema better than another?
• A: Often, yes

Relational database design is one of the more theoretical topics of CS145, but it has fairly practical applications.

We will focus on one very important aspect of relational design: Redundancy, and the problems it causes. We will look at two different kinds of redundancy. For each, we will:

• Characterize the redundancy formally
• Define a "Normal Form" for relations that prevents it
• Learn how to detect it and eliminate it

Request for patience: This is a topic for which it is hard to see the importance at first. When designing schemas at the scale we have been dealing with, people tend to naturally avoid the redundancy problems we will discuss. Also, the formalization of the redundancy can get a little tedious. It will turn out to be useful though, so have patience.

   Student(ID, name, address, HScode, HSname, HScity, cumGPA, priority)
   Apply(ID, campus, date, major)

Anomalies

1. Redundancy

2. Update Anomaly

3. Deletion Anomaly

Functional Dependencies

(show example)

   For every pair of tuples t and u in Student:
   if t[cumGPA] = u[cumGPA] then t[priority] = u[priority]

• Based on knowledge of the real world
• All data must adhere to it

   A1, A2, ..., Am → B1, B2, ..., Bn   (commas may be omitted)

We will abbreviate A1, A2, ..., Am as AA (or "A-bar") and B1, B2, ..., Bn as BB (or "B-bar").

(simple abstract example)

Functional Dependencies and Keys

1. AA → BB, where BB is all attributes of R
2. no subset of AA satisfies (1), i.e., AA is minimal

Trivial FD

Nontrivial FD

Completely nontrivial FD

Rules for Functional Dependencies

Splitting Rule

Question: Can we also split the left-hand side?

Combining Rule

Trivial Dependency Rules

Transitive Rule

Closure of Attributes

   Find all attributes B in R such that A1, A2, ..., Am → B

Algorithm for computing closure:

   start with {A1, A2, ..., Am}
   repeat until no change:
      if current set of attributes includes LHS of a dependency,
      add RHS attributes to the set

Example: closure of {ID, HScode} in Student given FD's:

   ID → name, address, cumGPA
   cumGPA → priority
   HScode → HSname, HScity

Using FD's to Produce a Good Relational Schema

1. Start with set of relations
2. Define FD's (and therefore keys) for them based on real world
3. Transform relations to "normal form" ("normalize" the relations)

Relation Decomposition

Definition: R₁(A₁, ..., A_m) / R₂(B₁, ..., B_n) is a decomposition of R(C₁, ..., C_k) if {A₁, ..., A_m} U {B₁, ..., B_n} = {C₁, ..., C_k}

(diagram)

1. R₁ = π_{A1, ..., Am} (R), eliminating duplicates
2. R₂ = π_{B1, ..., Bn} (R), eliminating duplicates
3. R₁ NATURAL-JOIN R₂ = R

Example decomposition:

Boyce-Codd Normal Form (BCNF)

Given: relation R and set of FD's for R

Definition: R is in BCNF with respect to its FD's if for every nontrivial FD AA → BB, AA contains a key.

Question: Why does violating this requirement produce a "bad" relation?

   Student(ID, name, address, HScode, HSname, HScity, cumGPA, priority)

   FD's: ID → name, address, cumGPA
         cumGPA → priority
         HScode → HSname, HScity

   Key:  ID, HScode

Question: Is the relation in BCNF?

   Apply(ID, campus, date, major)

   FD's: ID, campus, date → major
   Key:  ID, campus, date

Question: Is the relation in BCNF?

   Input: relation schema R and set of FD's for R

   (1) compute keys for R based on FD's
   (2) repeat until no more BCNF violations:
         (2a) pick any R' with AA → BB that violates BCNF
         (2b) decompose R' into R1(AA,BB) and R2(AA,CC)
              where CC is all attributes in R' except (AA U BB)
         (2c) compute FD's for R1 and R2
         (2d) compute keys for R1 and R2 based on FD's

Question: How can we compute keys in steps (1) and (2d)?

Question: How do we compute FD's in step (2c)?

Multivalued Dependency (MVD)

Definition:

   AA ->> BB is an MVD for relation R if:
   For all tuples t,u in R:
      If t[AA] = u[AA] then there exists a v in R such that:
         (1) v[AA] = t[AA]
         (2) v[BB] = t[BB]
         (3) v[CC] = u[CC] where CC is all attributes in R except (AA U BB)

Example:

Apply(ID, campus, sport)
Assume all students apply to each campus with all sports.

Question: What are FD's?

Question: What is key?

Question: Is it in BCNF?

Question: What are MVD's?

(show example data to verify)

Trivial MVD

Nontrivial MVD

Rules for Multivalued Dependencies

FD-is-an-MVD Rule

Prove by showing (1), (2), (3) in MVD definition.

Transitive Rule

Complementation Rule

Question: Are there any rules for FD's that do not apply for MVD's?

Fourth Normal Form (4NF)

Definition: R is in 4NF with respect to its MVD's if for every nontrivial MVD AA ->>BB, AA contains a key.

Note: Since every FD is also an MVD, 4NF implies BCNF

Question: What happens in the MVD definition if AA contains a key?

   Input: relation schema R and set of FD's and MVD's for R

   (1) compute keys for R based on FD's
   (2) repeat until no more 4NF violations:
         (2a) pick any R' with AA ->>BB that violates 4NF
         (2b) decompose R' into R1(AA,BB) and R2(AA,CC)
              where CC is all attributes in R' except (AA U BB)
         (2c) compute FD's and MVD's for R1 and R2
         (2d) compute keys for R1 and R2 based on FD's

Question: How do we compute MVD's in step (2c)?