Database – Normalisation

All the examples will use this simple table for explanation.
Screenshot 2018-10-18 at 10.59.59 PM
Attribute is column, and tuple is row. In this table the attributes are from A to F.

Functional dependency
Screenshot 2018-10-18 at 11.03.44 PM
A is determinant, B is dependant.
B is functional dependent on A.

Screenshot 2018-10-18 at 11.05.35 PM
A and B are composite key which functionally determines C.

The determinant must only determine a unique value, the same determinant cannot have more than one unique value, the below example is not allowed. A is the determinant which cannot determine B and C, it must be either determining B or C not both.
Screenshot 2018-10-19 at 4.45.20 PM

Different determinants however can have the same values. Such as attribute A determines B, attribute C also determines B.
Screenshot 2018-10-19 at 4.48.07 PM

Partially functional dependency
Screenshot 2018-10-18 at 11.09.59 PM
A and B are candidate keys, but C is dependent only to A. This is partially functional dependent.

Transitive functional dependency
Screenshot 2018-10-18 at 11.21.02 PM

F is dependent on D, but D is not part of candidate key.

Screenshot 2018-10-18 at 11.21.53 PM
E and F dependent on D, but D is not part of candidate key.

Why normalisation?
Normalisation is to remove duplicates. Duplicates or redundant values in a database will create problems. These problems are:
Update anomaly, deletion anomaly, and insert anomaly.

  • Update anomaly.
    Consider below table, there supposed I need to change C5 to something else, I need to change all instances that has C5.
    Screenshot 2018-10-19 at 5.00.07 PM

  • Insert anomaly.
    Suppose I want to insert a new instance in attribute B, I will need to update a new value in attribute A as well. Insert anomaly arises when inserting a new value requires to insert another attribute as well.

  • Deletion anomaly.
    This anomaly happens when a deletion of a value in an attribute also deletes the values of other attribute(s). Consider if I delete A4, I inadvertently lost B4, C5, D4, E4 and F4.

1NF
To qualify for 1NF these are the requirements:
a. Attributes must be unique.
b. Values in tuple must be atomic i.e. no multiple values, only one value.
c. Values in tuple must be the same domain. Example if the attribute is date, the value must be date related.

2NF
2NF must have the qualification of 1NF and in addition must not have attributes that are partially functional dependent.
Example of 2NF


A and B determine C, A determine B, no partially dependent.

3NF
3NF must be qualified for 2NF, and must not have attribute(s) is/are dependent on another non-key attribute(s) i.e. no transitive dependency

Advertisements
This entry was posted in Database and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s