Data Normalization EXam2

Main goals of data normalization in relational database design. 1-3

1. represent all necessary entities and relationships.

2. reduce redundancies

3. make info retrieval more efficient

What issue can arise from careless splitting of data into separate relations?

Spurious tables and redundant info

What is normalization (justification for splitting relations?

process of breaking down relations into smaller ones to get rid of unwanted properties

First Normal Form (1NF) requirement

it’s attributes are not set-valued

How to convert to 1NF?

“flatten” the relation

What is a full functional dependency? FFD

A FFD is a FD x→y where you cannot drop any attributes from x and still have the FD hold.

What is a prime attribute?

An attribute that is part of an candidate key in the relation

What is 2NF?

1NF and every non prime attribute must depend on the whole primary key

How can we decompose first into multiple 2NF?

Divide the FDs into groups sharing common attributes

What is trivial functional dependency?

An FD x_>Y is trivial when y is in x

What is a superkey

a set of attributes that contain a ck

what is 3NF?

A relation is in 3Nf if for every non trivial FD X→Y X is a superkey or Y is a prime attribute

What is the Boyce Codd Normal form?

3NF but x must be a superkey

What is multivalue dependency?

Let R(XYZ) be a relation. X→Y in R wherever 2 row s and t have the same x values and we must also find rows u and w that

1. S and U share the same Y

2. S and W share the same Z

3. T and U share the same Z

4. T and W share the same Y

Why are MVDs important?

They explain a more general kind of redundancy than FDs.

What are trivial MVDs?

For MVD x→>Y in a relation, it is trivial if Y is in X or XUY=R.

FDs vs MVDs

An MVD x-»Y in a relation R each value of X determines a set of independent values of Y. In contrast, an FD x→Y specifies that for each value of X, there is a unique corresponding value of Y.

What is 4NF?

If for every non trivial MVD x→>Y in a relation R, X must be a superkey of the relation.

3 4NF time saving theorems

1. All Non-4NF relations can be decomposed into 2 or more relations

2. All 4NF relations are also BCNF

3. A BCNF relation whose CKs are single attrs is also 4NF

What is 5NF?

PJNF( project join NF) for join dependencies that survive after 4NF.