Normalization

Question 1

Important properties of well designed relational schema

Answer 1

1) Relations consist of attributes that are logically related
2) Lossless-join property: Ensures that information decomposed across many relations can be reconstructed using natural joins
3) Dependencies preservation property - Ensures that constraints on the original relation can be maintained by enforcing constraints on normalized relations

Question 2

Functional dependency

Answer 2

X -> Y
X functionally determines Y
Y is functionally dependent on X

Question 3

Algorithm for attribute closure

Answer 3

Given a set of attributes X
Let X^+ = X
Repeat
- Find a FD in F whose left side is a subset of X^+
- Add the right side of F to X^+
Until (X^+ does not change)

Question 4

!NF

Answer 4

First normal form: All attribute values are atomic

Question 5

2NF

Answer 5

All non-key attributes full FD on a candidate key

Question 6

3NF

Answer 6

All attributes are FD by a candidate key or are a part of a candidate key

Question 7

BCNF

Answer 7

All attributes are only FD by a candidate key

Question 8

4NF

Answer 8

No multi-value dependencies exists

Question 9

5NF

Answer 9

No join dependencies exist

Question 10

Algorithm for cinverting to BCNF

Answer 10

Look among the given FDs for a BCNF violation X -> Y
Compute X^+
Replace R by relations with schemas
Project the FDs onto the two new relations