Normalisation is an important step in designing a good data model. Q.4.1 Explain one advantage of normalising data. (2) Q.4.2 The below data is in first normal form (1NF). Normalise the data to second normal form (2NF), showing all steps with explanations. All steps as well as the final answer must be in dependency diagram format. Primary key: Reporter ID, Article ID Reporter ID Reporter Name Reporter Surname Article ID Article Title Article Category ID Article Category Description 1 Jane Snow 1 10 best recipes 5 Food 2 John Smith 2 14 desserts to die for 5 Food 2 John Smith 3 Bucket list places 1 Travel 3 Sarah Gibbons 4 Romantic Paris 1 Travel 1 Jane Snow 5 Tornado strikes again 2 Current Events (15) Q.4.3 Normalise the answer from Q.4.2 to the third normal form (3NF), showing all steps with explanations. The final answer must be in dependency diagram format
4.1 One advantage of normalising data is that it helps reduce data redundancy, which in turn improves data integrity and consistency. By breaking down data into separate tables and establishing relationships between them, redundant data is eliminated and updates and modifications only need to be made in one place, improving overall data accuracy.
4.2 Steps to normalise data to second normal form (2NF):
1. Identify the functional dependencies:
- Reporter ID -> Reporter Name, Reporter Surname
- Article ID -> Article Title, Article Category ID
- Article Category ID -> Article Category Description
2. Create separate tables for Reporter, Article, and Article Category with their respective attributes:
Reporter Table:
- Reporter ID (Primary Key)
- Reporter Name
- Reporter Surname
Article Table:
- Article ID (Primary Key)
- Article Title
- Article Category ID (Foreign Key)
Article Category Table:
- Article Category ID (Primary Key)
- Article Category Description
3. Final dependency diagram in 2NF:
Reporter Table:
Reporter ID -> Reporter Name, Reporter Surname
Article Table:
Article ID -> Article Title
Article Category Table:
Article Category ID -> Article Category Description
4.3 Steps to normalise data to third normal form (3NF):
1. Identify the transitive dependencies:
- Article Category ID -> Article Category Description
2. Create a separate table for Article Category with Article Category ID as the primary key and Article Category Description as the only attribute.
3. Adjust the Article Table to remove the dependency on Article Category Description by replacing it with Article Category ID as a foreign key.
4. Final dependency diagram in 3NF:
Reporter Table:
Reporter ID -> Reporter Name, Reporter Surname
Article Table:
Article ID -> Article Title, Article Category ID
Article Category Table:
Article Category ID -> Article Category Description