5. Normalization - 6marks

Question 1

A function dependency (FD) is

Answer 1

a constraint denoted X  Y between two sets of attributes X and Y from a relational schema R.

Question 2

The FD specifies a restriction on

Answer 2

the possible tuples that can form a relation instance r of R

Question 3

The inference rules for normalization are:

Answer 3

Acronym to remember: RATDUP

1. Reflexive Rule: A set of attributes or a subset always determines itself and its superset.
2. Augmentation Rule: Adding an additional attribute to both sides of a relation means it holds.
3. Transitive Rule: eg X –> Y , Y –> Z | = X –> Z
4. Decomposition Rule: If there is more than one attribute on the RHS this can be split (eg: X –> YZ |= X –> Y and X –> Z
5. Union Rule: The opposite of decomposition, combining.
6. Pseudotransitive Rule: Variation on rule 3: Eg: X –> Y , WY –> Z | = WX –> Z

Question 4

First Normal Form (1NF) is:

Answer 4

• Normal DB form, with no violations (only atomics values, no redundancy, few null)

Question 5

Second Normal Form (2NF) is:

Answer 5

• 1NF with No Partial dependency - so every non-prime attribute is fully functionally dependent on every key
(ie: fails 2NF if only part of the minimal key determines an attribute in that table)

Question 6

Third Normal Form (3NF) is:

Answer 6

• 2NF with No Transitive dependency (ie: fails 3NF if A –> B and B –> C )

Question 7

Boyce-Codd NF (BCNF) is:

Answer 7

• 3NF but with the LHS of functional dependencies are super keys.
Note that 3NF can be better than BCNF

Question 8

General steps to achieve the highest normal form (to 3NF) are:

Answer 8

1. Identify candidate keys
2. What is the highest normal form
3. Decompose if it is not in the highest normal form