Announcements#

Hw#1 due this thursday at midnight
Lecture exercise 5 is due tonight (mistake in setting the deadline by me!)
Lecture exercise 6 is available at 2pm today due on wednesday at midnight

SuperKey#

Given a relation R and a set of fds F, the set X is a superkey if X+ contains all the attributes in R.

Key#

Given a relation R and a set of fds F, the set X is a key if

X+ contains all the attributes in R, and
X is minimal (no subset X1 of X has the property that X1+ contains all the attributes)

R(A,B,C,D,E,F) F={AC->DE, BD->AF, EC-> A}

BC+ = {B,C}
ABC+ ={A,B,C,D,E,F}
BCD+ = {A,B,C,D,E,F}
BCE+ = {B,C,E,A,D,F}

Keys: ABC, BCD, BCE

Prime Attribute#

An attribute in a key is called a prime attribute.

For the relation R(A,B,C,D,E,F) F={AC->DE, BD->AF, EC-> A}
Keys: ABC, BCD, BCE
Prime attributes: A,B,C,D,E

Normal Forms#

Boyce-Codd Normal Form (BCNF)#

Given a relation R and a set of functional dependencies F, we say that R is in BCNF (R is in Boyce-Codd Normal Form) iff for every functional dependency X->Y in F, one of the following is true:

X is a superkey, or
X->Y is trivial.

Students(RIN, Email, Name, Address)
RIN-> Email, Name, Address
Email -> RIN
Email RIN -> Name
Email Name -> Name
Name Address -> Name

Keys: RIN or Email
In BCNF (All fd follow the rules)

StudentMajors(RIN, Major, AdvisorRIN, AdvisorName) RIN Major -> AdvisorRIN AdvisorName AdvisorRIN -> AdvisorName

Key: RIN Major
Not in BCNF, because AdvisorRIN -> AdvisorName violates it

RIN Major AdvisorRIN AdvisorName 1 CSCI 456 Sibel 2 CSCI 456 Sibel 3 CSCI 234 Malik 4 GSAS 235 Mei

StudentHobbies(RIN, Name, Hobby) RIN-> Name

Key: RIN, Hobby
Not in BCNF, RIN is not a superkey and RIN->Name is not trivial

Third Normal Form (3NF)#

Given a relation R and a set of functional dependencies F, we say that R is in 3NF (R is in Third Normal Form) iff for every functional dependency X->Y in F, one of the following is true:

X is a superkey, or
X->Y is trivial, or
All attributes in Y are prime attributes.

All relations in BCNF are also in 3NF.

R(A,B,C,D,E,F) F={ABC->DEF, F->AB, CDE->CD}

Keys: ABC, FC
Prime attributes: A, B, C, F

Not in BCNF because F is not a superkey and F->AB is not trivial
Check 3NF:

ABC->DEF, ok because ABC is a superkey
F->AB, ok, F is not a superkey and A and B both are prime attributes
CDE->CD, ok trivial

Address(Street, State, City, Zip)
Street State City -> Zip
City Zip -> State

Key: Street State City, Street City Zip Prime attributes: all attributes

Basis#

A set of functional dependencies F is said to be in basis form, if there is a single attribute on the right hand side of all functional dependencies.

If F is not a basis, we can quickly put it in basis form by decomposition.

Minimal Basis#

A set of functional dependencies F in basis form, is said to be minimal if there is simplification F1 of F obtained by either removing a functional dependency or an attribute from a functional dependency such that F1+ = F+

Algorithm for finding minimal basis#

Given a set of functional dependencies F

Put it in basis form (decompose the right hand sides)
Remove all trivial functional dependencies
Remove a functional dependency X->Y such that after removing X->Y, X+ still contains Y. Suppose F’ = F - {X->Y}, then X+ in F’ contains Y.
Suppose XZ->Y in F, I can simplify this to X->Y is the closure does not change! Given F, F’ = F-{XZ->Y} union {X->Y} (assuming XZ->Y is in F) Check if X+ is the same in F and F’, then this simplication is possible.

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

F={AB->C, BCE->DG, ABC-> BDE, D->AH, ABE-> BH}

Put it in basis form

F={AB->C, BCE->D, BCE->G, ABC-> B, ABC-> D, ABC-> E, D->A, D->H, ABE-> B, ABE->H}

Remove all trivial fds

F={AB->C, BCE->D, BCE->G, ABC-> D, ABC-> E, D->A, D->H, ABE->H}

Remove X->Y if X->Y is implied by the rest of the functional dependencies

Can I remove BCE->D?
F1={AB->C, BCE->G, ABC-> D, ABC-> E, D->A, D->H, ABE->H}

BCE+ = {B,C,E,G}, no!

Can I remove ABC->D
F’={AB->C, BCE->D, BCE->G, ABC-> E, D->A, D->H, ABE->H}

ABC+ = {A,B,C,E,D,H}, yes!

Can I remove D->H?
F’’={AB->C, BCE->D, BCE->G, ABC-> E, D->A, ABE->H}

D+ = {D,A}, no!

Can I remove ABE->H? F’’={AB->C, BCE->D, BCE->G, ABC-> E, D->A, D->H}

ABE+ = {A,B,E,C,D,G,H}, yes!

F={AB->C, BCE->D, BCE->G, ABC-> E, D->A, D->H}

Can we simplify the right hand side?

For example (changing ABC->E to AB->E) F={AB->C, BCE->D, BCE->G, ABC-> E, D->A, D->H} AB+ = {A,B,C,E,D,G}
F’={AB->C, BCE->D, BCE->G, AB-> E, D->A, D->H} AB+ = {A,B,E,C,D,G}
Yes!

Can I remove E from BCE->D
F={AB->C, BCE->D, BCE->G,AB-> E, D->A, D->H} BC+={B,C} F’={AB->C, BC->D, BCE->G,AB-> E, D->A, D->H} BC+={B,C,D,A,H,E} No!

Can I remove C from BCE->D
F={AB->C, BCE->D, BCE->G,AB-> E, D->A, D->H} BE+={B,E} F’={AB->C, BE->D, BCE->G,AB-> E, D->A, D->H} BE+={B,E,D,A,C,H}
No!

No other removals!

F={AB->C, BCE->D, BCE->G,AB-> E, D->A, D->H} Minimal basis

Or combine fds with the same left hand side!

F={AB->CE, BCE->DG, D->AH} Minimal cover

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

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

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

R(A,B,C) F3={A->B, B->C}, F3+ ?= F+ , Yes!

Removed A->C, In F3, A+ ={A,B,C}, C is in A+, then F3 implies A->C

R(A,B,C) F={AB->C}
Can I remove B?

F’={A->C} F={AB->C}
A+ = {A,C} A+ = {A}

Can I remove A?
F’={B->C} F={AB->C}
B+ = {B,C} B+ = {B}

Decomposition of relations#

A decomposition of a relation R with functional dependencies F is given by a set of relations R1, R2,…, Rn such that all attributes in R1, R2,…, Rn are a subset of attributes in R

If you had an instance of R(A,B,C,D,E), and you decompose into R1(A,B,C) and R2(C,D,E)

Find R1 = Project_(A,B,C) ®
R2 = Project_(C,D,E) ®

A decomposition is lossless if R1*R2 = R (natural join of all decomposed relations is guaranteed to be identical to the original relation.

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

A B C
a b1 c
a2 b c

R1(A,C)

A C
a c
a2 c

R2(B,C)

B C
b1 c
b c

R’ = R1*R2

R’
A B C
a b1 c
a b c
a2 b1 c
a2 b c

Since R’ is not equal to R, this decomposition is lossy!

Announcements

Contents