DBMS: Normalization of Table (Sid, Cid, SName, C_Name, Grade, Faculty, F_Phone) to 3NF

Back to top

1. First Normal Form(1NF)

Let's check if there are any non-atomic values. Looking at the original question paper, it is easy to get confused and think that the column 'Faculty' has multiple values.

We need to decide if it's a single person 'Padma B. Karki' or two persons 'Padma B' and 'Karki'. In some of the rows, there is a comma(,) between 'Padma B' and 'Karki' while it is 'Padma B. Karki' in others.

We can confirm that the comma(,) is a printing mistake and it's a single name 'Padma B. Karki' because:

1. 'Karki' is a common surname in Nepal. So, it's more likely to be Padma B's surname.
2. The corresponding F_Phone column has only single values.

 

After deciding that, we can say that all the attributes are atomic. Also, we utilize Sid and Cid as the composite primary key in our table R1. Thus the table R1 is in First Normal Form(1NF).

1. R1

Sid	Cid	SName	C_Name	Grade	Faculty	F_Phone
1	IS208	Adams	Database	A	Padam B. Karki	601245
1	IS301	Adams	Programming	B	Ravi Gurung	458695
2	IS208	Jones	Database	A	Padam B. Karki	601245
3	IS208	Smith	Database	B	Padam B. Karki	601245
4	IS301	Baker	Programming	A	Ram Sundar	452368
4	IS208	Baker	Database	B	Padam B. Karki	601245

 

 

 

Back to top

2. Second Normal Form(2NF)

In relation R1, the following partial dependencies occur.

Let's decompose the relation R1 into R2, R3 and R4 to get rid of partial dependencies.

1. R2

Sid	SName
1	Adams
2	Jones
3	Smith
4	Baker

 

2. R3

Cid	C_Name
IS208	Database
IS301	Programming

 

The other attributes are dependent on both Sid and Cid. In other words, the functional dependencies:

 

are fully functional dependencies because (Grade, Faculty, and F_Phone) can be derived only from the combination of {Sid, Cid}. That's why the relation R4 will still use {Sid, Cid} as the composite primary key.

3. R4

Sid	Cid	Grade	Faculty	F_Phone
1	IS208	A	Padam B. Karki	601245
1	IS301	B	Ravi Gurung	458695
2	IS208	A	Padam B. Karki	601245
3	IS208	B	Padam B. Karki	601245
4	IS301	A	Ram Sundar	452368
4	IS208	B	Padam B. Karki	601245

 

All the partial dependencies are removed and R2, R3 and R4 are in Second Normal Form(2NF).

 

Back to top

3. Third Normal Form(3NF)

The relations R2 and R3 do not have any transitive dependencies.

R4 however, has the following transitive dependencies:

 

 

So, we need to decompose R4 into R5 and R6 to get rid of the transitive dependency.

Note: Here, the column Faculty is not suitable to be a Primary Key because person names are not unique. In such cases, it is better to add a surrogate(artificial) primary key for it. So, we are adding the extra column Faculty_ID as a primary key for R6.

1. R5

Sid	Cid	Grade	Faculty_Id
1	IS208	A	1
1	IS301	B	2
2	IS208	A	1
3	IS208	B	1
4	IS301	A	3
4	IS208	B	1

 

2. R6

Faculty_Id	Faculty	F_Phone
1	Padam B. Karki	601245
2	Ravi Gurung	458695
3	Ram Sundar	452368

 

The final relations are R2, R3, R5 and R6 in the Third Normal Form(3NF).

Back to top