Database Design with the Relational Normalization Theory

Reasons for studying normalization theory. Database design helps us model the real world in that it gives us a way to represent precisely the data/information pertinent to a company or an enterprise; we then can make use of the data we have stored. The design methodology that we learned in the previous chapter is adequate in the sense that it allows us to complete the task.

At the beginning of the database area, having the database and the tools for updating, inserting, and deleting data is good enough. It turns out that something more than that is required for databases. The most important issue in database is the maintainance of consistency of the database, especially when updates are made to the database. A good design can contribute — in several ways — to this process. It provides us the tools/theories for achieving schema that do not contain redundancy. Normalizations do help us remove redundancy from database (which is a source for data inconsistency.)

The note will be organized in the following way: first, I list the important definitions; second, the theorems and axioms; third, the algorithms; fourth, a list of questions — which, I believe, that if you can answer them — you are done with the chapter; last, the examples and precise definitions.

Important Definitions. functional dependency (FD); keys; superkeys; satisfaction of FD by a relation; unsatisfaction of FD by a relation; properties of FDs; entailment of a FD by a set of FDs; equivalence between two sets of FDs; normal forms (BCNF, 3NF, 4NF); decompositions (lossless and lossy, dependency preserving); join dependency JD); multivalued dependency (MVD); properties of MVDs; projection of FDs

Important Theoresm and Axioms. Amstrong axioms (trivial/reflexive, augmentation, transitivety) plus their derivations (union, decomposition); soundness and completeness of Amstrong axioms; Use of Amstrong axioms in checking for entailment; in deriving new FDs; Similar axioms for MVDs;

Important Algorithms. attribute closure computation; BCNF, 4NF decomposition; (we will skip the algorithm for 3NF);

Questions

1. Why normalization? (Address update-, insertion-, and delete- anormaly.)

2. What is an FD? (an expression of the form X̄ → Ȳ)

3. When does a relation satisfy a FD X̄ → Ȳ?

4. What are the Amstrong axioms? What properties do they have?

5. When does a set of FDs F entail a FD f? How can we check whether or not F entails f?

6. When are two set of FDs F and G equivalent?

7. What is the attribute closre X̄_{F}^{+}? How to compute X̄_{F}^{+}? Use of X̄_{F}^{+} for checking of entailment and equivalence?

8. What is the projection of FDs onto a set of attributes? How to compute it?

9. What is BCNF, 3NF, or 4NF? How can we check whether a relation is in BCNF, 3NF, or 4NF?

10. What is lossless and lossy decomposition? Why should a decomposition be lossless?

11. How do we decompose a relation into BCNF, 3NF, or 4NF? (We will skip 3NF decomposition)

Functional Dependencies. A functional dependency is of the form

X̄ → Ȳ

(Remember that X̄ is the notation that we use to denote a set of attributes.) It states that tuples agree on the values of the attributes X̄ must also agree on the value of Ȳ.

Keys. A set of attributes X̄ is a key of a relation R if

• it functionally determines all other attributes, and

• none of its proper subsets functionally determines all other attributes (i.e., it is minimal)

A set of attributes that contains a key is a superkey.

Rules to derive new functional dependencies

• Reflexivity: If Ȳ ⊆ X̄ then X̄ → Ȳ.

• Augmentation: If X̄ → Ȳ then \overline{XZ} → \overline{Y Z}.

• Transitivity: If X̄ → Ȳ and Ȳ → Z̄ then X̄ → Z̄.

Closure of Attributes with respect to a set of FDs.

X̄_{F}^{+} = {X̄ → Ȳ | X̄ → Ȳ is entailed by F}.

Computing the closure:

Input: F and X̄
Output:  X̄_{F}^{+}

Init:  closure = X̄

 while           there exists some FD X̄ → Ȳ in F such that

X̄ ⊂ closure and Ȳ \ closure ≠ ∅ do
closure = closre ∪ Ȳ

 endwhile

return closure

Non-trivial Functional Dependency. X̄ → Ȳ in nontrivial if at least on of the attribute in Ȳ is not among the attributes in X̄; it is completely nontrivial if none of the attributes in X̄ is an attribute in X̄.

Projection of FDs onto a set of attributes. Given F ann a set of attributes X̄, the projection of F onto X̄, denoted by πX̄(F) is defined by

πX̄(F) = {Ȳ → Z̄ | Ȳ ∪ Z̄ ⊆ X̄, Ȳ → Z̄ ∈ F^{+}}

i.e, πX̄(F) is the set of FDs entailed by F whose left hand side and right hand side are attributes belonging to X̄.

BCNF. R = (R̄, F) is in BCNF if for every nontrivial functional dependency X̄ → Ȳ in F, X̄ is a superkey for R.

Decomposing into BCNF: Input: R = (R̄, F)

Output:  a lossless decomposition of R

Init:  decomposition = {R}

 while      there exists some schema S = (S̄, F′) in decomposition which is not in BCNF

             do
             find X̄ → Ȳ in F′ which violates the BCNF condition

replace S with S_{1} = (S̄_{1}, F′_{1}) and S_{2} = (S̄_{2}, F′_{2}) where S̄_{1} = X̄ ∪ Ȳ, F′_{1} = π\overline{XY} (F) S̄_{2} = X̄ ∪ (R̄ − Ȳ), F′_{2} = π_{X̄∪(R̄−Ȳ)}(F)

 endwhile

return decomposition

Third Normal Forms. (or 3NF) R = (R̄, F) is in 3NF if for every nontrivial functional dependency functional dependency X̄ → Ȳ in F, X̄ is a superkey or every B ∈ Ȳ − X̄ is part of some key.

Decomposing into 3NF: Algorithm in the book. We skip this algorithm.

Projection of a set of FD onto a set of attributes. For a relation R = (R̄, F) and a set of attributes S̄ ⊆ R̄, the projection of F on S̄ is a set of FDs, denoted by πS̄(F), and is defined as follows.

πS̄(F) = {X̄ → Ȳ | X̄ → Ȳ ∈ F^{+} and X̄ ∪ Ȳ ⊆ S̄}.

This set can be computed by

• Compute X̄^{+} for each X̄ ⊆ S̄

• Determine the FDs deriveable from X̄^{+}

Decomposition. A decomposition of a relation schema R = (R̄, F) is a collection of schemas R_{1} = (R̄_{1}, F_{1}),…, R_{n} = (R̄_{n}, F_{n}) such that

1. R_{i} ≠ R_{j} for i ≠ j

2. R̄ = ⋃_{i=1}^{n} R̄_{i}

3. F entails F_{i}

This leads to a decomposition of a relation r (of the schema R) into a set of relations

r_{1} = πR̄_{1}(r), …, r_{n} = πR̄_{n}(r)

Lossless Decomposition A decompostion of R = (R̄, F) into R_{1} = (R̄_{1}, F_{1}),…, R_{n} = (R̄_{n}, F_{n}) is lossless if for every valid instance r of R,

r = r_{1}⊳⊲r_{2}⊳⊲ … ⊳⊲r_{n}

A binary decomposition of R = (R̄, F) into R_{1} = (R̄_{1}, F_{1}) and R_{2} = (R̄_{2}, F_{∈}) is lossless if

• R̄_{1} ∩ R̄_{2} → R̄_{1} ∈ F^{+} or

• R̄_{1} ∩ R̄_{2} → R̄_{2} ∈ F^{+}

A decomposition which is not lostless is lossy.

A decompostion of R = (R̄, F) into R_{1} = (R̄_{1}, F_{∞}),…, R_{n} = (R̄_{n}, F_{n}) is dependency preserving if F and F_{1} ∪ F_{2} ∪ … ∪ F_{n} are equivalent.

Multivalued Dependencies. A multivalued dependency (MVD) is of the form

X̄→ →Ȳ

It states that for each pair of tuples t and u of an instance r that agree on the values of the attributes X̄ we can find in r some tuple r that agrees

1. with both t and u on X̄,

2. with t on Ȳ, and

3. with u on the attributes that are not among the X̄’s or Ȳ ’s.

Rules to derive new multivalued dependencies

• Trivial: If X̄→ →Ȳ then X̄→ →Z̄ where Z̄ ⊆ X̄ ∪ Ȳ.

• Transitivity: If X̄→ →Ȳ and Ȳ → →Z̄ then X̄→ →Z̄.

• Complementation: If X̄→ →Ȳ then X̄→ →Z̄ where Z̄ = R̄ \ (X̄ ∪ Ȳ).

• FD implication: If X̄ → Ȳ then X̄→ →Ȳ.

Rules to derive new functional dependencies from multivalued dependencies:

• if W̄ ⊂ Ȳ and Ȳ ∩ Z̄ = ∅ then X̄→ →Ȳ and Z̄ → W̄ entail X̄ → W̄.

Non-trivial multivalued Dependency. X̄→ →Ȳ in nontrivial if none of the attributes in Ȳ is among the attributes in X̄ and X̄ ∪ Ȳ is not the set of all attributes of R.

Fourth Normal Form. R = (R̄, D) – where D is a set of multivalued dependencies – is in 4NF if for every nontrivial multivalued dependency X̄→ →Ȳ in D X̄ is a superkey.

Decomposing into 4NF: The algorithm for decomposing a relation into a set of 4NF relations is similar to the BCNF decomposition. We also take a nontrivial MVD X̄→ →Ȳ that violates the 4NF condition and split the relation into two, one with the set of attributes X̄ ∪ Ȳ and the other with the set of attributes X̄ ∪ (R̄ − Ȳ). The main difficulty in this process is to compute the projection of MVDs onto a set of attributes. We can use the following rule to do this:

Let V̄ = X̄ ∪ Ȳ and W̄ = ¯(X) ∪ (R̄ − V̄). The projection of X̄→ →Ȳ onto S̄ is undefined if V̄ ∩ W̄ ⊆ S̄; when it is defined, it is P̄→ →Q̄ where P̄ = (S̄ ∩ V̄) ∩ (S̄ ∩ W̄) and P̄ ∪ Q̄ = (S̄ ∩ V̄).

Remark 1. The notation in this note follows the textbook. X̄ stands for a set of attributes. Elsewhere, you will see A_{1} … A_{n} instead of X̄. The MVD part is a special case of what is called join dependency in the book. Each join dependency R̄ = V̄ ⊳⊲W̄ can be represented as X̄→ →Ȳ where X̄ = V̄ ∩ W̄ and X̄ ∪ Ȳ = V̄ or X̄ ∪ Ȳ = W̄.

Remark 2. The definitions of BCNF, 3NF, 4NF omit the case where Ȳ ⊆ X̄. This is the case of trivial dependency.

Remark 3. To determine whether or not a relation schema R = (R̄, F) is in BCNF (or 3NF, 4NF), we need to determine if any FD (or MVD) in F^{+} satisfies the BCNF (or 3NF, 4NF) condition, respectively. If one does not satisfy the BCNF (or 3NF, 4NF) condition, we can stop and conclude that the schema is not in BCNF (or 3NF, 4NF). Only after all FDs (or MVD) satisfy the BCNF (or 3NF, 4NF) condition, we can say that the schema is in BCNF (or 3NF, 4NF).

Remark 4. To determine whether or not a relation schema R = (R̄, F) is in BCNF, we do not really need to know the key of the relation. We just need to compute the closure of the left hand side of all the FDs in F.

Remark 5. To determine whether or not a relation schema R = (R̄, F) is in 3NF or 4NF, we need to know the key of the relation. Computing the key using brute-force search is not a good idea. We often can find the must-be-presented elements of the key by looking at the right hand side of the FDs in F.

Example of Computing Keys We wish to compute the keys of R = (ABCD, F) with F = {AB → C, C → D, D → A}.

We observe that the right handside of every FD in F does not contain B. Thus, any key need to contain B. Because B^{+} = {B}, B is not a key. So, any key will need to contain at least two attributes.

AB, BC, BD are three possible 2-attributes key of R. We can check that

AB^{+} = {A, B, C, D}
BC^{+} = {B, C, D, A}

and

BD^{+} = {B, D, A, C}

Thus, R has three keys AB, BC, BD.

Note that we stop here since a key needs to be minimal and every set of attributes with more than two attributes will contain some key.

Projection of a set of FD onto a set of attributes Let F = {AB → D, AC → E, BC → D, D → A, E → B}. We would like to compute π_{ABC}(F). This can be done in a systematic way as follows. First, we compute

\begin{aligned}A^{+} & = & \{A\} \\ B^{+} & = & \{B\} \\ C^{+} & = & \{C\} \\ AB^{+} & = & \{A, B, D\} \\ AC^{+} & = & \{A, C, E, B, D\} \\ BC^{+} & = & \{B, C, D, A, E\} \end{aligned}

This means that

π_{ABC}(F) = {AC → B, BC → A}.

Note that we only list non-trivial FDs and FDs with only one attribute on the right hand side.

Decomposition into BCNF Consider R = (ABCD, F) with F = {AB → C, C → D, D → A}. We can see that the non-trivial FD D → A violates the BCNF condition (D is not a superkey since D^{+} = {D, A}). Thus, R is not in BCNF.

We use D → A to decompose R. This results in R_{1} = (AD, F_{1}) and R_{2} = (BCD, F_{2}) with F_{1} = {D → A} and F_{2} = {C → D}.

The first schema is in BCNF but the second one is not due to C → D. Decompose the second one with respect to this FD, we have R_{21} = (CD, {C → D}) and R_{22} = (CB, ∅).