Lecture 2 - Normalisation Basics

Question 1

What is another name for normalization?

Answer 1

progressive decomposition

• of unsatisfactory (bad) relations by breaking
  up their attributes into smaller good relations

Question 2

What is the degree of normalization?

Answer 2

The normal form i.e. how far relations have been decomposed

Question 3

What degrees of normalization do we need to know about?

Answer 3

1NF
2NF
3NF
BCNF

Question 4

What is the goal of normalization?

Answer 4

Optimal relations, so that when they’re rejoined we do not have fictions tuples and no redundancy. The guidelines are followed throughout the process!

Question 5

What is a prime attribute?

Answer 5

attribute that belong to some candidate key of a relation

Question 6

What is a non-prime attribute?

Answer 6

attribute that is not a member of any candidate key.

Question 7

What is 1NF?

Answer 7

First Normal Form

This form disallows nested attributes and multi valued attributes. i.e. attributes are atomic

Question 8

What do we expect from relations in 1NF?

Answer 8

Redundancy and repeated values

Question 9

What is 2NF?

Answer 9

Second normal form

A relation R is in 2NF if every non-prime attribute A in R is
fully functionally dependent on the primary key of R

Question 10

How do we go from 1NF to 3NF?

Answer 10

remove all prime attributes from the primary
key, which cause partial dependencies

Question 11

What does : verbosity in PK mean?

Answer 11

removing any attribute in PK can’t identify the instance uniquely anymore

Question 12

What is 3NF?

Answer 12

Third Normal Form

A relation R is in 3NF (being already in 2NF) if there is no non-
prime attribute which is transitively dependent on the primary key. This means all non-prime attributes should be directly determined from the PK

Question 13

How to go from 2NF to 3NF?

Answer 13

Split the original relation into two relations: the non-prime transitive attribute. (the one that joins prime and other non-prime).

transitive attribute becomes PK in new relation and FK in the original relation

Question 14

What is tricky with 3NF?

Answer 14

That the transitive attribute can be a candidate key / prime attribute, which would then mean the transitive functional dependency is ok.

Question 15

What is generalized 3nF?

Answer 15

Every non-prime attribute A in relation R:
- is fully functionally dependent on every candidate key in R.
- is non-transitively dependent on every candidate key in R

Question 16

What is BCNF>

Answer 16

Boyce-Codd Normal Form

any attribute should be functionally dependent only on the Primary Key (left hand side of a funcional dependency is a PK)

Question 17

Equation for split relation amount?

Answer 17

n! / k! (n-k)! where n is the amount of attributes and k is the amount of target relations

Question 18

How should we decompose a relation breaking BCNF ?

Answer 18

Then, the relation R should be decomposed into two relations:
- R1 with attributes: R{A} (all attributes in R apart from A)
- R2 with attributes: {X} U {A} (put together X and A)

• repeat if R1 or R2 not in BCNF (recursive process)