Database Normalisation: Second Normal Form

Summary

This video explains how to normalize a database to Second Normal Form (2NF), following from First Normal Form (1NF). It illustrates how to remove redundancy and partial dependencies by splitting a table into three separate ones: Students, Courses, and Student-Course. The video highlights the importance of ensuring that non-key attributes are fully dependent on the entire primary key to prevent inconsistent data. Through a clear example, it demonstrates how normalization improves data integrity, reduces redundancy, and enhances space efficiency, making the database easier to maintain and scale.

Takeaways

• 😀 Second normal form (2NF) requires a table to be in first normal form (1NF) and to not contain any partial dependencies.
• 😀 A partial dependency occurs when a non-key attribute is dependent on part of the composite primary key, rather than the entire key.
• 😀 To satisfy 2NF, each non-key field must be fully dependent on the primary key and must describe only one entity type.
• 😀 Tables should only contain data about one type of thing; if they describe more than one, they must be split into separate tables.
• 😀 A composite primary key can be used when multiple columns are needed to uniquely identify a record, like in the student-course table.
• 😀 When a table contains redundant data, such as repeating student information for each course, it violates the principles of 2NF.
• 😀 If a student's marital status changes, for example, it must be updated in every instance where their data appears, leading to inconsistencies and errors.
• 😀 The process of normalizing a database to 2NF reduces data redundancy, improving efficiency and minimizing errors.
• 😀 A well-designed 2NF database has separate tables for distinct entities, such as students, courses, and student-course relationships.
• 😀 In a 2NF database, non-key attributes are fully dependent on the primary key, ensuring there are no partial dependencies or redundant data.

Q & A

What does Second Normal Form (2NF) require for a database table?

Second Normal Form (2NF) requires that a table is already in First Normal Form (1NF) and that it has no partial dependencies. In simpler terms, each non-key field must be fully dependent on the entire primary key.

What is the significance of partial dependencies in database normalization?

Partial dependencies occur when a non-key attribute is only dependent on part of a composite primary key, rather than the entire key. This can lead to data redundancy and inconsistency, which is why 2NF aims to eliminate them.

What is the difference between First Normal Form (1NF) and Second Normal Form (2NF)?

First Normal Form (1NF) requires that the data is atomic, with no repeating groups or multi-valued fields. Second Normal Form (2NF) builds on 1NF by eliminating partial dependencies, ensuring that every non-key attribute is fully dependent on the entire primary key.

Can a table be in 2NF if it is not in 1NF?

No, for a table to be in 2NF, it must first be in 1NF. The table must meet the criteria of 1NF (atomic values and no repeating groups) before you can apply the rules of 2NF.

What is a composite primary key, and why is it used in this example?

A composite primary key is a primary key made up of two or more attributes. In this example, the combination of 'Student ID' and 'Course Title' forms a composite key to uniquely identify each record in the student-course table.

What is the primary issue with the original table in the example, and how does it relate to normalization?

The original table contains redundant data, where student details are repeated for every course they take. This redundancy violates 2NF, as it indicates partial dependencies — student information is only partially dependent on the composite primary key of Student ID and Course Title.

What are the three tables created to normalize the data into 2NF?

The three tables created are: the 'Students' table (containing data only about students), the 'Courses' table (containing data only about courses), and the 'Student-Course' table (a bridging table that links students and courses, and records their grades).

Why is the 'Student-Course' table sometimes called a bridging or linked entity table?

The 'Student-Course' table is called a bridging table because it links the 'Students' and 'Courses' tables together, representing the many-to-many relationship between students and courses, and also stores additional information like grades.

How does splitting the data into separate tables in 2NF improve data consistency?

By splitting the data into separate tables, changes only need to be made in one place. For example, if a student's marital status changes, it only needs to be updated in the 'Students' table, reducing the risk of inconsistent data across multiple records.

What is the main benefit of normalizing a database to Second Normal Form (2NF)?

The main benefits of normalizing to 2NF are reduced data redundancy, improved data consistency, and better storage efficiency. It ensures that data is stored in a way that is easier to maintain and update without causing anomalies.