Normalization
What is a normalization: Normalisation is the process for evaluating and correcting tables structures,
This minimises the data redundancies and reduces data anomalies
• Normalization is the process of splitting relations into well structured relations that allow users
to insert, delete, and update records without introducing database inconsistencies.
- Four most commonly used normal forms are:
1. First normal form (1NF),
2. Second normal form (2NF),
3. Third normal form(3NF) – sufficient point for this unit, and
4. Boyce-Codd (BCNF)
Data anomalies: a fault in a database that usually emerges as a result of shoddy planning and storing
everything in a flat database.
For examples: Update Anomalies, Insertion Anomalies, and Deletion Anomalies.
Update anomaly: is a data inconsistency that results from data redundancy and a partial update
Delete anomaly: A deletion anomaly is the unintended loss of data due to deletion of other data
Insert anomaly: is the inability to add data to the database due to absence of other data.
Steps involved in normalization:
- UNF un-normalized file structure
- 1NF the first step to turn into normal form
- 2NF then turn it into second normal form
- 3NF finally into third normal form
UNF:
- Create an entity and give it a name
- List all the attributes that are required
- Identify a primary key
- Identify the repeating data
- You have one entity.
1NF:
- 1NF = No multi-valued (repeating) attributes
- Remove the repeating group to a new entity
- Give the new entity a name
- Identify a primary key for this table
- Take the key from the 1st table with you to link the two entities
- Label the foreign key
- You now have a composite key and two entities.
Things that violates the 1NF:
- Using row order to convey information violates 1NF
- Mixing data types within the same column. Ex: mixing strings and numbers
- Designing a table without a primary key
- Storing a Repeating group of data items on a single row
2NF: each non key attribute must depend on the entire primary key
- 2NF = No partial key dependencies
- First, identify your composite key (a key that has more than one attribute)
- Then, look for attributes which rely on only one of the keys to exist
- Remove these attributes and place in a new entity along with a copy of key they rely (are
dependant) on.
- Since the new key is a primary key in a new entity, make it a foreign key in the original entity.
- You now have three entities.
3NF: every key attribute in a table should depend on the key, the whole key and nothing but the key.
There should not be any transitive dependency
- 3NF = No non-key dependencies
- Examine at all the entities produced so far
- Are there any non-key attributes which rely on any other non-key attributes
- If there are any, remove these to an new entity
- Give the new entity a primary key
- Link the old and new entity with a foreign key
- You now have four entities.
Examples:
