Lecture 7 - BCNF & 3NF Decompositions AND ER Models#

Announcements#

Lecture Exercise 7 is due today at 5pm (giving you a last change to submit!)
Lecture Exercise 8 is out later today, due on thursday at 2pm.
Homework #2 is out, due on friday midnight.

Today’s topics#

How to compute the 3NF decomposition
How to compute BCNF decomposition
Still problems even if a relation is in BCNF?
ER Modeling

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 a lossless decomposition iff R1 * R2 * … * Rn = R for all instances of R.

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

F1, F2, … , Fn are the projection of F onto R1, R2, …, Rn respectively, and
F1 union F2 union … union Fn is equivalent to F, i.e. (F1 union F2 union … union Fn)+ = F+

All decompositions should be lossless, but you can decide whether to reserve all functional dependencies depending on the application.

3NF Decomposition#

We are given a relation R and a set F of functional dependencies. Suppose R is not in 3NF. The 3NF decomposition of R is computed as follows:

Make sure F is a minimal set, i.e. convert it to minimal basis and optionally use the combinining rule to combine f.d.s with the same left hand side.
Create a new relation for each functional dependency X->Y, containing attributes X and Y.
If there is a relation Ri that contains all attributes in Rj, then remove Rj.
If there is no relation that contains all the attributes in one of the keys of R, then create a new relation for one of the keys (containing all the attributes of that key)

You can then find the projection of F into each new relation and find if the resulting relations are in BCNF as well.

3NF decomposition guarantees relations in 3NF
3NF decomposition is guaranteed to be lossless
3NF decomposition is guaranteed to be dependency preserving

BCNF Decomposition#

We are given a relation R and a set F of functional dependencies. Suppose R is not in BCNF. The BCNF decomposition of R is computed as follows:

Find a functional dependency X->Y that violates BCNF. Compute X+ according to F.
If there is no such dependency, stop.
Else:
- Create a relation R1 containing attributes in X+.
- Create a relation R2 containing all attributes in R except for (X+ - X).
- Find projection of F into R1 and R2, and put R1 and R2 into BCNF recursively.
BCNF decomposition guarantees relations in BCNF and in 3NF
BCNF decomposition is guaranteed to be lossless
BCNF decomposition is NOT guaranteed to be dependency preserving

3NF Decomposition Example

R(A,B,C,D,E,F,G,H) F={AB->CD, BE->F, F->E, AD->B}

Keys: ABEGH, ADEGH

Suppose F is minimal.

R1(A,B,C,D) {AB->CD,AD->B} Keys: AB, AD in BCNF
R2(B,E,F) {BE->F, F->E} Keys: BE, BF
not in BCNF because F is not a superkey (F->E)
R3(A,B,E,G,H) {} Key: ABEGH, in BCNF

(E,F) x do not need this because of R2
(A,B,D) X do not need this because of R1

BCNF Decomposition Example

R(A,B,C,D,E,F,G) F={A->BCDE, BE->AF, F->G}

Key: A, BE

F->G violates BCNF F+ = {F,G}

R1(F,G) F={F->G} Key: F, in BCNF

R2(A,B,C,D,E,F) F={A->BCDE, BE->AF}
Key: A, BE, in BCNF

Done!

Let’s use 3NF Decomposition insted:

R(A,B,C,D,E,F,G) F={A->BCDE, BE->AF, F->G}

(A,B,C,D,E) F={A->BCDE}
(A,B,E,F) F={BE->AF}
(F,G) F={F->G}

Not equivalent to BCNF decomposition, so you have a choice!

BCNF Decomposition

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

Key: ABC, BCD

BD->AF and F->G both violate BCNF
(we can take out either, but I will illustrate BD->AF)

Take out BD->AF, BD+={A,B,D,F,G} BD->AFG

R1(A,B,D,F,G) F1={BD->AF, F->G},
Key: BD, not in BCNF because F is not a superkey (F->G)
Take out F->G, F+ = {F,G}

R11(F,G)  {F->G}  in bcnf Key: F  
R12(A,B,D,F)  {BD->AF}  in bcnf  Key: BD  

For R2, take out BD±{B,D} = {A,F,G}
R2(B,C,D,E) F2={BCD->E} Key: BCD, in BCNF

Final result:

R11(F,G) {F->G} in bcnf
R12(A,B,D,F) {BD->AF} in bcnf
R2(B,C,D,E) {BCD->E} Key: BCD, in BCNF

Union of projected fds, F’={F->G, BD->AF,BCD->E}
Given F’, ABC+ = {A,B,C}, hence ABC->DE in F is lost.

Alternative solution:

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

Take out F->G first

(F,G) {F->G}
(A,B,C,D,E,F) F={ABC->DE, BD->AF}
Key: ABC, BDC

Given (A,B,C,D,E,F) F={ABC->DE, BD->AF}

Take out: BD->AF

(A,B,D,F) {BD->AF}
(B,C,D,E) {BCD->E}

Result (same as starting with BD->AF)
(F,G) {F->G} in bcnf
(A,B,D,F) {BD->AF} in bcnf
(B,C,D,E) {BCD->E} Key: BCD, in BCNF

Books(ISBN, Title, EditionNo, Author, Publisher, YearPublished)
{ISBN -> Title EditionNo Publisher YearPublished }

Key: ISBN Author

3NF decomposition:

B1(ISBN, Title, EditionNo, Publisher, YearPublished)
{ISBN -> Title EditionNo Publisher YearPublished }
Key: ISBN
B2(ISBN, Author)
{}
Key: ISBN, Author

Students(RIN, Name, RCS, Class, Major, Advisor, PhoneNo, PhoneType)
{RIN -> Name RCS Class, RCS-> RIN, RIN Major -> Advisor, RIN PhoneNo -> PhoneType}

Key: RIN, Major, PhoneNo

(RIN, Name, RCS, Class) {RIN -> Name RCS Class, RCS-> RIN}, key: RIN or RCS
(RCS, RIN) X do not need this one!
(RIN, Major, Advisor) {RIN Major -> Advisor} Key, RIN Major
(RIN, PhoneNo, PhoneType) {RIN PhoneNo->PhoneType} Key: RIN PhoneNo
(RIN, Major, PhoneNo) {} Key: RIN, Major, PhoneNo
???
All fds in BCNF and 3NF

MusicGroup(Group, Artist, Genre, DateFounded, DateJoined, DateofBirth)
{Group -> Genre, DateFounded, Artist -> DateofBirth, Group Artist -> DateJoined}

(Group, Genre, DateFounded) {Group -> Genre, DateFounded}
(Artist, DateofBirth) {Artist -> DateofBirth}
(Group, Artist, DateJoined) {Group Artist -> DateJoined}

Problem 1:

Two different 3NF decompositions:

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

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

Both solutions are fine, which one is the better solution will depend on the actual use cases (queries and the size of attributes etc.!)

Problem 2:

(RIN, Major, PhoneNo) {} Key: RIN, Major, PhoneNo

But Major and PhoneNo are not related!

Given a student RIN, there are multiple majors Given a student RIN, there are multiple phone numbers Majors and Phonenumbers are not related to each other!

4NF says that multivalued attributes that are not related to each other should not be in the same relation. So we will likely need to decompose this relation.

Entity-Relationship Models#

Not standardized, follow a mix of conventions (from the book and course notes)
ER models are object-oriented, but can be converted to relational data models
Main database modeling constructs:
- Entities: attributes and some attributes making up a key
  - Convention: only allow attributes with a single value
  - Represented with boxes
- Relationships that connect 2 or more entities
  - Convention: use numbers or arrows to indicate cardinalities
  - Relationships tell us how entities relate to each other
  - Often think of them as: Entity A has a relationship X to entity B
Entities are the main class of objects that we will store information about
- Each entity has a name
- A set of attributes, the information about that entity where
  - Each attribute should be atomic (i.e. simple valued)
  - Each attribute should be about that entity directly
- Each entity should have a key, a set of attributes that imply all the other attributes (so entities should be in BCNF)

Let’s build an example database:

Internet Movie Database

Entities:

Actors
- Actorid: Key
- Name
- Stage Name
- DOB
- Country of Birth
- Biography
Movies
- MovieId: Key
- Title
- ReleaseDate
TV Shows
- ShowId
- Title (seasons…)
Crew
- PersonId
- Name
- DOB
- Country of Birth
- Biography
Companies
- Name: key
- Address