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).