Lecture 6 - Normalization continued

Lecture 6 - Normalization continued#

Announcements#

  1. Lecture Exercise 7 is out later today, due on monday at 2pm.

  2. Homework #2 out on later today, due next week thursday.

  3. If you have accommodations, please reserve an exam slot with the test center for Exam #1 on October 1st. See: https://provost.rpi.edu/faculty-resources/testing-center

    • Note the exam will be 110 minutes.

    • For 50% extra time, reserve a 3 hour block

    • For 100% extra time, reserve a 4 hour block

    • Ideally your block should start at 2pm, but if you cannot do this, please notify me.

Today’s topics#

  • Boyce-Codd Normal Form and Third Normal Form revisited

  • Meaning of a set of functional dependencies (closure)

  • Minimal Functional Dependencies and how to find them

  • Decompositions

    • What is a decomposition

    • How can you find the functional dependencies that hold for a decomposition

    • When is a decomposition functional dependency preserving

    • When is a decomposition lossless

    • How can we check a decomposition is lossless with the Chase Algorithm

  • How to compute the 3NF decomposition

  • How to compute BCNF decomposition

Functional Dependency (fd): Given a relation R and a set of attributes X and Y of R, a fd is an expression of the form X -> Y which means if two tuples in R have the same values for X, then they must have the same values for Y.

Boyce-Codd Normal Form (BCNF): Given a relation R and a set of functional dependencies F, the relation R is said to be in Boyce-Codd Normal Form (BCNF) iff for all functional dependencies X->Y in R, one of the following is true: 1. X-> Y is trivial, or 2. X is a superkey of R

In other words, if there is a single violation of BCNF, where X->Y in F, and X->Y is not trivial and X is not a superkey, then R is not in BCNF.

Third Normal Form (3NF): Given a relation R and a set of functional dependencies F, the relation R is said to be in Third Normal Form (3NF) iff for all functional dependencies X->Y in R, one of the following is true: 1. X-> Y is trivial, or 2. X is a superkey of R, or 3. Y is contains only prime attributes.

Everything in BCNF is also in 3NF, but 3NF allows additional relations.

import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(3, 3))

# Circle B (outer) - Relations in 3NF
circle_b = plt.Circle((0.5, 0.5), 0.4, fill=False, color='black', linewidth=1.5)
ax.add_patch(circle_b)

# Circle A (inner) - Relations in BCNF, fully inside B
circle_a = plt.Circle((0.5, 0.45), 0.2, fill=False, color='black', linewidth=1.5)
ax.add_patch(circle_a)

# Label inside A
ax.text(0.5, 0.45, "Relations in BCNF", ha='center', va='center', fontsize=6)

# Label inside B but outside A (near the top of B)
ax.text(0.5, 0.82, "Relation in 3NF", ha='center', va='center', fontsize=6)

ax.set_xlim(0, 1)
ax.set_ylim(0, 1)
ax.set_aspect('equal')
ax.axis('off')

plt.tight_layout()
plt.savefig('normal_forms.png', dpi=150)
plt.show()
../../_images/1a358ee84d5f36264f58bfb2657a0198952419707949d20b316d55cb61084f42.png

We are given a relation R and a set F of functional dependencies.

The closure F+ of F is the set of all functional dependencies implied by F.
Check if X->Y is in F+, I can find the closure X+ of X, check if Y is in the X+.

R(A,B,C,D,E,F) F={A->BC, AD->F}

Is ABD-> ACEF implied by F? ABD+ = {A,B,C,D,F} No, because ACEF is not a subset of ABD+

ACD -> BF implied by F? ACD+ = {A,C,D,B,F} BF is in ACD+, so ACD->BF is implied by F.

Two set of functional dependencies F1 and F2 are equivalent if they have the same closure. Equivalent set of fds have the same meaning, same implications.

\[F1 \equiv F2 \mbox{ iff } (F1)+ = (F2)+\]

Another way to test if F1 and F2 are equivalent is to test:

  1. All fds X->Y in F1 are implied by F2, and

  2. All fds X->Y in F2 are implied by F1.

If all these are true then, these two sets are equivalent.

A set F of functional dependencies is said to be minimal if there is no way to simplify F to get F’ where F is equivalent to F. Possible simplifications include:

  1. removing an fd

  2. removing an attribute on the right hand side of an fd

  3. removing an attribute on the left hand side of an fd.

A set of fds F is said to be a basis if there is only one attribute on the right hand side of all fds. Note that you can easily put an fd in this form using the decomposition rule.

Algorithm for finding a minimal basis of an input set F of functional dependencies:

  1. Put F in basis form

  2. Remove all trivial fds from F

  3. For all fds of the form XZ->Y in F, try replacing it with X->Y to get F’. Check X+ in F and X+ in F’, if it is the same, then we can remove Z.

F1={A->B, B->C}, F2={A->B, A->A, B->C, AB->AB}

Is F1 equivalent to F2? Yes!

F1={A->B, B->C}, F3={A->B, B->C, AB->C}

Is F1 equivalent to F3? Yes, because AB->C is implied by F1.

Is AB->C implied by F1? Given F1, AB+ = {A,B,C}, C is in AB+, AB->C is implied by F1.

F1={A->B, B->C}, F4={A->B, B->C, C->A}

Is C->A implied by F1? Using F1, C+ = {C}, Using F4, C+={A,B,C}

F5={A->BC, C->A}, F4={A->B, B->C, C->A}

IS F5 Equivalent to F4?

  1. Given F5: A->BC implied by F4? Yes C->A implied by F4? Yes

  2. Given F4: A->B implied by F5? Yes B->C implied by F5? In F5, B+ = {B} NO!!! C->A implied by F5? Yes

No, they are not equivalent.

How can we simplify F={A->B, A->A, AB->C, AB->AB, A->C} ?

  1. Remove all trivial fds F={A->B, AB->C, A->C}

Can I remove AB->C? F1={A->B, A->C}, Does F1 imply AB->C? AB+={A,B,C} Yes Can I remove A->C? F2={A->B, AB->C} Does F2 imply A->C? A+={A,B,C} Yes

Assume, we take F2, F2={A->B, AB->C}

  1. Can I remove A from AB->C? No

F2={A->B, AB->C} B+= {B} F3={A->B, B->C} B+= {B,C}

  1. Can I remove B from AB->C?

F2={A->B, AB->C} A+={A,B,C} F3={A->B, A->C} A+={A,B,C}

Yes, final result: {A->B, A->C} or {A->BC}

F={AB->CD, ABD->ACE, AB->E}

  1. Put it in basis form

F={AB->C, AB->D, ABD->A, ABD->C, ABD->E, AB->E}

  1. Remove trivial fds

F={AB->C, AB->D, ABD->C, ABD->E, AB->E}

  1. Which fds are implied by the rest?

F={AB->D, ABD->C, ABD->E}

  1. Which fds can be simplified on the left hand side?

F={AB->D, AB->C, AB->E}

  1. Use combining rule for all fds. with the same left hand side

F={AB->CDE}

Given a relation R and a set of functional dependencies F, a decomposition of R is a set of relations R1, R2, … Rn such that

  1. R1, R2, …, Rn all contain attributes of R and

  2. the set of all attributes in R1, R2, …, Rn is the set of all attributes in R.

A decomposition is computed by projecting R into the attributes in each relation: Given a decomposition R1(A,B) and R2(B,C) of R(A,B,C):

we compute R1 = PROJECT_(A,B) ® and R2 = PROJECT_(B,C) ®

A decomposition R1, R2, … Rn of R with functional dependencies F is said be lossless iff R1 * R2 * … * Rn = R for all instances of R.

Chase algorithm for checking whether a decomposition R1,R2,…,Rn is lossless (given a set of functional dependencies)

  1. First construct a canonical database with a single tuple for each relation in the decomposition, each tuple for relation Ri is going to have attributes in Ri with no subscripts and the attributes not in Ri with a new subscript

  2. Use the functional dependencies such as X->Y to find two rows with the same X value, and we will make Y values the same (if one of the Y has no subscript, then make the other one also no subscript, known!, otherwise they should have the same subscript)

  3. Apply until no more changes are possible.

    • If there is a row with no subscripts, then this decomposition is lossless.

    • If there is No row with no subscripts, then this decomposition lossy and the resulting relation is a counter example of why.

Given a decomposition R1, R2, … Rn of relation R and a set of functional dependencies F, the projection of F into R1, given by F1 is the set of all fds in F+ that only contain the attributes in R1.

Given a decomposition R1, R2, … Rn of relation R and fds F, the decomposition is said to be dependency preserving iff

  1. F1, F2, … , Fn are the projection of F onto R1, R2, …, Rn respectively

  2. F1 union F2 union … union Fn is equivalent to F, i.e. (F1 union F2 union … union Fn)+ = F+

R(A,B,C) {A->BC}

R
a b c
d b e

R1(A,B)
a b
d b

R2(B,C)
b c
b e

R1*R2
a b c
a b e
d b c
d b e

This decomposition is not lossless since I am not guaranteed to get the same result!

MusicGroups(Artist, Group, Genre, DOB, DFound, DJoined)

Artist-> DOB Group-> Genre DFound Artist Group -> DJoined

Key: Artist Group

R(A,B,C,D,E,F)

A->D
B->CE
AB->F

R1(A,B,D,F)
R2(B,E)
R3(A,C)

R1: a b c1 d e1 f
R2: a2 b c2 d2 e f2
R3: a b3 c d3 e3 f3

B->CE

R1: a b c1 d e f
R2: a2 b c1 d2 e f2
R3: a b3 c d3 e3 f3

A->D

R1: a b c1 d e f
R2: a2 b c1 d2 e f2
R3: a b3 c d e3 f3

Not lossless, i.e. lossy because there is no row with no subscripts.

R(A,B,C,D,E,F)

A->D
B->CE
AB->F

R1(A,D)
R2(B,C,E)
R3(A,B,F)

R1: a b1 c1 d e1 f1
R2: a2 b c d2 e f2
R3: a b c3 d3 e3 f

A->D

R1: a b1 c1 d e1 f1
R2: a2 b c d2 e f2
R3: a b c3 d e3 f

B->CE

R1: a b1 c1 d e1 f1
R2: a2 b c d2 e f2
R3: a b c d e f

R3 has no subscript! So it is lossless.

R(A,B,C,D,E,F) F= {A->D, B->CE, AB->F}

Decomposition:

R1(A,B,D,F), Project onto R1 to find F1= {A->D, AB->F}

A+ B D F AB AD AF BD BF DF

R2(B,E), Project F onto R2 to find F2 = {B->E}

R3(A,C), Project F onto R3 to find F3 = {}

F1 union F2 union F3 = Fnew = {A->D, AB->F, B->E} equivalent to F?

F= {A->D, B->CE, AB->F}

A->D implied by Fnew? Yes AB->F implied by Fnew? Yes

B->CE?

According to Fnew, B+= {B,E} since C is not in B+, then B->CE is not implied by Fnew.

Then, this decomposition is not dependency preserving because we lost B-> CE.

Contents