Normalisation and Functional Dependancies

Question 1

Define normalisation…

Answer 1

The process of optimising a data model in such a way to remove data duplication and reduce the possibility of insertion, update and modification anomalies.

Question 2

List some benefits that normalisation has…

Answer 2

Reduce data redundancy.
Reduce complexity of database.
Reduce storage space database takes up on disk.

Question 3

What are the 3 main characteristics desired in normalisation?

Answer 3

• Minimise number of attributes used whilst still achieving data requirements.
• Reduce data redundancy.
• Group similar attributes into the same relation.

Question 4

What are anomalies caused by?

Answer 4

Redundant data.

Question 5

What are the 3 types of anomalies? Define each…

Answer 5

Insertion : Data is needed to create a record, but the data can’t be obtained unless the record already exists.
Modification : Modifications made to data is not universally done so due to data duplication.
Delete : Unwanted data is deleted, and accidentally deletes desired data along with it.

Question 6

Explain each step of normalisation up to 3.5 (Boyce Codd Form)…

Answer 6

1NF : All rows must be unique, thus remove duplicate values and multi-values.
2NF : Remove all partial dependancies to ensure all non-prime attributes are fully functionally dependent on the primary key.
3NF : Remove transitive dependencies to ensure that all attributes in all relations are solely fully functionally dependant on the primary key.
BCNF : Make every determinant a candidate key.

Question 7

Define a functional dependancy…

Answer 7

A relationship in which the co-domain is functionally dependant on the domain. For example A > B means each A can only relate one B, but each B can have multiple A’s mapping to it.

Question 8

What is the determinant in a functional dependancy?

Answer 8

The LHS of the equation.

Question 9

What are the 3 types of functional dependancies? Define each…

Answer 9

Fully Functionally Dependant : A functionally determines B, but B is not dependent on any subset of A.
Partially Functionally Dependant : If some attributes of A are removes, A still functionally determines B.
Transitively Functionally Dependant : A -> B, B -> C, therefore, A -> C.