A: Normalization of Student_Course ( S_ID, S_Name, C_ID, C_Name ) : 57

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.

S_ID S_Name C_ID C_Name
1 Subham C01 English
1 Subham C02 Economics
2 Supriya C03 Mathematics
3 Sawan C01 English

 

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

S_ID S_Name
1 Subham
2 Supriya
3 Sawan

 

Table #2: Student with primary key C_ID.

Course

C_ID C_Name
C01 English
C02 Economics
C03 Mathematics

 

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

Student_Course

S_ID C_ID
1 C01
1 C02
2 C03
3 C01

 

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