Normalizing Database (2NF)

Normalizing a database to the Second Normal Form (2NF) involves removing partial dependencies and ensuring that all non-key attributes are fully functionally dependent on the entire primary key. To achieve 2NF, you must first ensure that your database is in 1NF and then address partial dependencies. Let’s go through the process of normalizing a sample database to 2NF with an example:

Example: Student Course Registration Database

Consider a database that tracks student course registrations, where each student can register for multiple courses, and each course can have multiple students. We’ll start with an unnormalized table and then proceed to normalize it to 2NF.

Unnormalized Table (1NF):

StudentID	CourseID	CourseName	Instructor
1	101	Math	Prof. Smith
1	102	Physics	Prof. Johnson
2	101	Math	Prof. Smith
3	103	Chemistry	Prof. Davis

In this table, (StudentID, CourseID) is the composite primary key.

Step 1: Identify Partial Dependencies

A partial dependency exists when a non-key attribute depends on only a part of the primary key. In our example, the Instructor column depends only on the CourseID, not the entire composite primary key.

Step 2: Create Separate Tables

We will create two separate tables: one for students and one for courses.

Students Table:

StudentID	StudentName
1	John
2	Jane
3	Alex

Courses Table:

CourseID	CourseName
101	Math
102	Physics
103	Chemistry

Step 3: Address Partial Dependencies

Now we need to address the partial dependency of the Instructor column. To do this, we create a new table for course instructors.

Instructors Table:

CourseID	Instructor
101	Prof. Smith
102	Prof. Johnson
103	Prof. Davis

Step 4: Update Original Table

We can now remove the CourseName and Instructor columns from the original table since this information is now stored in separate tables.

StudentRegistrations Table (2NF):

StudentID	CourseID
1	101
1	102
2	101
3	103

In this normalized structure, the StudentRegistrations table only contains the primary key attributes (StudentID, CourseID).

Conclusion:
The database is now in Second Normal Form (2NF). We’ve removed partial dependencies by separating the data into related tables, ensuring that non-key attributes depend on the entire primary key.

Remember, normalization doesn’t stop at 2NF. Depending on the complexity of your data and the specific requirements, you might need to further normalize the database to higher normal forms, such as 3NF or BCNF, to eliminate other types of anomalies and redundancies.