Normalization of Student_Course ( S_ID, S_Name, C_ID, C_Name ) to 2NF

First Normal Form(1NF)

The above relation is already in First Normal Form(1NF) as all four columns have atomic values.

Here, we consider the columns S_ID and C_ID as Composite Primary Key.

Second Normal Form(2NF)

The table Student_Loan has following functional dependencies.

{ S_ID, C_ID } → S_Name
{ S_ID, C_ID } → C_Name

Both of the above are Partial Dependencies because S_Name can be derived only from S_ID and C_Name can be derived only from C_ID.

Hence the table is not in 2NF and we need to decompose the table. While decomposing it, we need to make it a lossless decomposition. In other words, we need to keep the information on which students are enrolled in which courses. From the given data, we can see that a student can be enrolled on multiple courses and a course can enroll multiple students. This implies that the entity set Student and entity set Course have a many-to-many relationship. Thus, we decompose the table into 3 new tables.

Table #1: Student with primary key S_ID.

Student

Table #2: Student with primary key C_ID.

Course

Table #3: Student_Course with composite primary key S_ID and C_ID.

Student_Course

The tables Student, Course and Student_Course are in Second Normal Form(2NF).