Lecture 5 - Normalization#

Announcements#

Lecture Exercise 5 due at midnight today.
Lecture Exercise 6 is out later today, due on thursday at 2pm.
Homework #1 due Today at midnight today.
Homework #2 out on Thursday, due next week thursday.
If you have accommodations, please reserve an exam slow 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#

Functional Dependencies
Functional Dependency Inferences
Keys, superkeys and prime attributers
Closure of a set of attributes
Closure of a set of functional dependencies
Boyce-Codd Normal Form
Third Normal Form

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.

Students(RIN, Name, RCS, Class, Major, Advisor, Phone)
A student can have many majors
RIN -> RIN Name RCS Class
RIN Major -> RIN Major Advisor

123456 Joe doej 2026 CSCI Adali 8033
123456 Joe doej 2026 CSYS Julius 8033
123456 Joe doej 2026 CSCI Adali 9322
123456 Joe doej 2026 CSYS Julius 9322

Functional Dependency Inference: Given a relation R and a set of functional dependencies F, we can find new fds implied by F by using any of the possible inference rules.

Trivial fd: If \(Y \subseteq X\), then X -> Y (always true for all relations)

A -> A
AB -> AB
AB -> B

Transivitity: If X->Y and Y->Z, then X->Z.
Decomposition: If X->YZ then X->Y and X->Z

RIN -> RIN Name RCS Class
RIN -> RIN
RIN -> Name
RIN -> RCS
RIN -> Class

Combining: If X->Y and X->Z, then X->YZ

RIN -> Name
RIN -> RCS
RIN -> Class

RIN -> Name RCS Class

Augmentation: If X->Y then XZ->YZ

Closure of a set of functional dependencies: Given a relation R and a set F of functional dependencies, then F+ is the closure of F over R, the set of all functional dependencies implied by F.

The closure of F, F+ is the set of all fd that can be obtained by F.

R(A, B, C) F = {A->B, B->C}

F+ = {A->B, B->C, A->A, B->B, C->C, AB->A, AB->B, AB->AB, BC->B, BC->C, BC->BC, AC->A, AC->C, AC->AC, ABC->A, ABC->B, ABC->C, ABC->A, ABC->B, ABC->C, ABC->AB, ABC->AC, ABC->BC, ABC->ABC, A->C,AC->BC, AB-> AC, AB->BC, A->BC, A->ABC}

A->C (transitivity)
AC->BC (augmentation)

Implication: Given a relation R and a set F of functional dependencies, a functional dependency X->Y is implied by F 1. I can obtain X->Y from F using inference rules, or 2. X->Y is in F+ (1 and 2 are the same thing)

Remember,

\[F \subseteq F+\]

Closure of a set of attributes: Given a relation R and a set of functional dependencies F, the closure of a set of attributes X, given by X+ is the set of all attributes implied by X.

- It is then the case that X->X+ is implied by F.

Algorithm:

1. Initialize, X+ = X
Repeat until no new attributes are added:
    2. Given a functional dependency  Y->Z, if 
       $$Y \subseteq X+$$, then add Z to X+.


R(A,B,C,D,E,F,G)  
F = {A->BC, BD->EF, CF->GB}

1. AC+ = {A,C}
2. AC+ = {A,B,C} (A->BC)

CDF+ = {B,C,D,E,G,F}

CDF -> BCDEFG  is implied by F

CF -> GB  (given)
CDF -> BDG (augmentation)
CDF -> BD (decomposition)  BD->EF 
CDF -> EF  (transitivity)
CDF -> CDF (trivial)
CDF -> BCDEFG (combining rule!)
  
F = {A->BC, BD->EF, CF->GB}
  
ACD+ = {A,B,C,D,E,F,G}
AD+ = {A,B,C,D,E,F,G} 
A+ = {A,B,C}
D+ = {D}

Superkey: Given a relation R and a set of functional dependencies F, a superkey is a set of attributes X such that - X implies all the attributes in R according to F.

Alternatively, X-> Y is implied by F where Y is the set of all attributes in R.

R(A,B,C,D,E,F,G)  
F = {A->BC, BD->EF, CF->GB}

Superkeys: ACD, AD, ABD, ABCD, ...      

Key: Given a relation R and a set of functional dependencies F, a key is a superkey X that is also minimal, i.e. no subset of X is a set superkey.
```
Key: AD
```
Prime attributes: Given a relation R and a set of functional dependencies F, a prime attribute is any attribute appearing in a key.
Prime attributes: A and D

R0(A,B) F = {}

Key: AB (StudentHobbies(RIN, Hobby), Key: RIN, Hobby)

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

Key: AB

A+ AD B+ BC

AB+ ABDCE

R1(A,B,C,D,E,F) F = {AB->CDE, ABC->EF}

Key: AB

R2(A,B,C,D,E,F,G) F = {AB->CDEF}

Key: ABG

R3(A,B,C,D,E,F,G) F = {AB->CD, CE->AF, AE->G}

Key: ABE, BCE

R4(A,B,C,D,E,F,G) F = {ABC->DEF, AD->G}

Key: ABC

R5(A,B,C,D,E,F,G) F = {ABC->DE, DB->F, FG->A}

Key: ABCG, BCFG, BCDG

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.

R0(A,B) F = {} Key: AB

in BCNF and in 3NF

R00(A,B,C,D,E) F={A->D, B->C, AB->E} Key: AB

A->D not trivial, A is not a superkey, violates BCNF B->C not trivial, B is not a superkey, violates BCNF AB->E not trivial, AB is a superkey, does not violate BCNF

Not in BCNF

A->D not trivial, A is not a superkey, D is not prime, violates 3NF B->C not trivial, B is not a superkey, C is not prime, violates 3NF AB->E not trivial, AB is a superkey, does not violate BCNF/3NF

Not in 3NF

R1(A,B,C,D,E,F) F = {AB->CDE, ABC->EF}

Key: AB

AB->CDE, not trivial, AB is a superkey ok ABC->EF, not trivial, ABC superkey, ok

in BCNF and in 3NF

R2(A,B,C,D,E,F,G) F = {AB->CDEF}

Key: ABG

Not in BCNF, AB is not a superkey

Not in 3NF because CDEF are not prime

R3(A,B,C,D,E,F,G) F = {AB->CD, CE->AF, AE->G}

Key: ABE, BCE

Prime attributes: A,B,C,E

Not in BCNF, all fds violate BCNF

AB->CD, AB is not a superkey, C is prime, but D is not prime, violates 3NF

CE->AF, violates 3NF because CE is not a superkey and F is not prime

AE->G, violates 3NF because AE is not a superkey and G is not prime

not in 3NF

R4(A,B,C,D,E,F,G) F = {ABC->DEF, AD->G}

Key: ABC

Not in BCNF, AD is not a superkey

Not in 3NF because G is not a prime

R5(A,B,C,D,E,F,G) F = {ABC->DE, DB->F, FG->A}

Key: ABCG, BCFG, BCDG

Prime attributes: A,B,C,D,F,G

Not in BCNF, all fds violate it

Not in 3NF becaues ABC->DE and ABC is not a superkey and E is not a prime attribute

R6(A,B,C,D,E,F,G) F = {ABC->DEFG, BF->A}

Keys: ABC, BCF prime attributes: ABCF

Not in BCNF because BF is not a superkey

in 3NF because

ABC->DEFG ok, ABC is a superkey

BF->A ok because A is a prime attribute

Faculty(RIN, DeptCode, DeptName)

F={RIN-> DeptCode, DeptCode-> DeptName}
   
F = {A->B, B->C}

Key: A

B is not a superkey, not in BCNF

  Cell In[1], line 3
    F={RIN-> DeptCode, DeptCode-> DeptName}
          ^
SyntaxError: invalid syntax

Lecture 5 - Normalization

Contents

Lecture 5 - Normalization#

Announcements#

Today’s topics#