Recall Normal Forms - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Recall Normal Forms - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Multivalued Dependencies

Normal Forms

1NF 2NF^ 3NF BCNF^ 4NF 5NF

Functional dependencies Multivalued dependencies Join dependencies

Normal Form of R (A,B,C,D,E)

1NF : no multivalues

2NF : no FDs where a subset of the key to the relation

is on the left

3NF : no non-trivial FDs, either the determinant is a

superkey or the RHS of the FD is a member of

some key

BCNF : the determinant of any non-trivial FD is a superkey

for the relation

Normal Form (cont)

Name AddrStreet AddrCity FilmName FilmYear

C. Fisher 123 Maple Dr. Hollywood Star Wars 1977

C. Fisher 5 Locust Ln. Malibu Star Wars 1977

C. Fisher 123 Maple Dr. Hollywood The Empire Strikes Back

1980

C. Fisher 5 Locust Ln. Malibu The Empire Strikes Back

1980

C. Fisher 123 Maple Dr. Hollywood Return of the Jedi 1983

C. Fisher 5 Locust Ln. Malibu Return of the Jedi 1983

Stars

Problems with relation Stars

• No reason to associate address with one movie and not

another

• When we repeat address and movie facts in all combinations,

there is obvious redundancy

• Relation R contains unnecessary duplication of data

The relation Stars doesn’t seem to be sufficiently normalized

Reasons:

Introducing MVD

Definitions:

• A MVD: A1A2…An  B1B2…Bn for a Relation R is “non- trival” if 1. none of the Bs are among the As 2. not all of the attributes of R are among the As and Bs
• A MVD is “trivial” if A1A2…An  B1B2…Bm where B1B2…Bm is a subset of A1A2…An or ( A1A2…An U B1B2…Bn ) contains all attributes of R
• A relation cannot be decomposed any further (under 4NF rules) if it has a trivial MVD

• Intuitively, A1A2…An →→ B1B2…Bm says that the relationship between A1A2…An and B1B2…Bm is independent of the relationship between A1A2…An and R -{B1B2…Bm} - MVD's uncover situations where independent facts related to a certain object are being squished together in one relation
• Functional dependencies rule out certain tuples from being in a relation - if A  B, then we can’t have two tuples with the same A **values but different B
• a.k.a.** equality-generating dependencies

Multivalued dependencies require that other tuples of a certain form be present in the relation

- a.k.a. tuple-generating dependencies

More on MVD

MVD

Example: is A  C

A B C D

A C B D

r

r

r

r

r

r

r

t1=r t2=r t3=r t4=r

t1=r t2=r t3=r t4=r

2 4 1 1 2 2 1 2 2 4 1 2 2 2 1 1

t1=r t2=r t3=r t4=r

TRANSITIVITY

If X  Y and Y  Z then X  (Z-Y)

REFLEXIVITY

if A is a set of attributes and A ⊇ B , then A  B holds.

REPLICATION

if A  B holds, then A  B.

Theories of MVD (cont)