loading...

CS614 VU Assignment No. 1 Spring 2012 Solution

Q: Normalize the below table up to 3rd normal form.

 

Student-ID

Student-Specialization

Student-Name

Specialization- Semester

Semester

Start-Date

Semester

End-Date

Specialization

Supervisor

Bc00001

 

SE

AI

Graphics

Ali

 

F08

Sp09

F09

15-07-08 21-12-08 Prof Khalid
15-02-09 25-06-09 Prof Ashraf
15-07-09 21-12-09 Prof Shaw
Bc00002 AI Safdar Sp09 15-02-09 25-06-09 Prof Ashraf
Bc00003

 

SE

Graphics

AI

Usman

 

F08

Sp09

F09

15-07-08 21-12-08 Prof Khalid
15-02-09 25-06-09 Prof Shaw
15-07-09 21-12-09 Prof Ashraf
Bc00004

 

SE

AI

Naeem

 

F09

Sp010

15-07-09 21-12-09 Prof Khalid
15-02-10 25-07-10 Prof Ashraf
Bc00005 Graphics Naveed Sp010 15-02-10 25-07-10 Prof Shaw
Bc00006 AI Zaman Sp010 15-02-10 25-07-10 Prof Ashraf
Bc00007 SE Zeshan Sp010 15-02-10 25-07-10 Prof Khalid

 

Solution:

To get the first normal form we will remove the multiple attributes without no loss.

 1NF:

Student-ID

Student-Specialization

Student-Name

Specialization- Semester

Semester

Start-Date

Semester

End-Date

Specialization

Supervisor

BC00001 SE Ali F08 15-07-08 21-12-08 Prof Khalid
BC00001 AI Ali SP09 15-02-09 25-06-09 Prof Ashraf
BC00001 Graphics Ali F09 15-07-09 21-12-09 Prof Shaw
Bc00002 AI Safdar Sp09 15-02-09 25-06-09 Prof Ashraf
Bc00003 SE Usman F08 15-07-08 21-12-08 Prof Khalid
Bc00003 Graphics Usman SP09 15-02-09 25-06-09 Prof Shaw
Bc00003 AI Usman F09 15-07-09 21-12-09 Prof Ashraf
Bc00004 SE Naeem F09 15-07-09 21-12-09 Prof Khalid
Bc00004 AI Naeem Sp010 15-02-10 25-07-10 Prof Ashraf
Bc00005 Graphics Naveed Sp010 15-02-10 25-07-10 Prof Shaw
Bc00006 AI Zaman Sp010 15-02-10 25-07-10 Prof Ashraf
Bc00007 SE Zeshan Sp010 15-02-10 25-07-10 Prof Khalid

 

2NF:

To get the 2NF we will move the redundant to a separate table and also the table should be in 1NF.


Student-ID

Student-Specialization

Student-Name

Specialization

Supervisor

BC00001 SE Ali Prof Khalid
BC00001 AI Ali Prof Ashraf
BC00001 Graphics Ali Prof Shaw
Bc00002 AI Safdar Prof Ashraf
Bc00003 SE Usman Prof Khalid
Bc00003 Graphics Usman Prof Shaw
Bc00003 AI Usman Prof Ashraf
Bc00004 SE Naeem Prof Khalid
Bc00004 AI Naeem Prof Ashraf
Bc00005 Graphics Naveed Prof Shaw
Bc00006 AI Zaman Prof Ashraf
Bc00007 SE Zeshan Prof Khalid

 

Semester table:

 

Student-ID

Specialization-

Semester

Semester

Start-Date

Semester

End-Date

BC00001 F08 15-07-08 21-12-08
BC00001 SP09 15-02-09 25-06-09
BC00001 F09 15-07-09 21-12-09
Bc00002 Sp09 15-02-09 25-06-09
Bc00003 F08 15-07-08 21-12-08
Bc00003 SP09 15-02-09 25-06-09
Bc00003 F09 15-07-09 21-12-09
Bc00004 F09 15-07-09 21-12-09
Bc00004 Sp010 15-02-10 25-07-10
Bc00005 Sp010 15-02-10 25-07-10
Bc00006 Sp010 15-02-10 25-07-10
Bc00007 Sp010 15-02-10 25-07-10

 

3NF:

Now we will eliminate the redundant data and also the tables should be in 2NF

Student Table:

Student-ID Student-Name
BC00001 Ali
BC00002 Safdar
BC00003 Usman
BC00004 Naeem
BC00005 Naveed
BC00006 Zaman
BC00007 Zeshan

 

Specialization Table:

Student-ID

Specialization

Supervisor

Specialization

Supervisor

BC00001 SE Prof Khalid
BC00001 AI Prof Ashraf
BC00001 Graphics Prof Shaw
Bc00002 AI Prof Ashraf
Bc00003 SE Prof Khalid
Bc00003 Graphics Prof Shaw
Bc00003 AI Prof Ashraf
Bc00004 SE Prof Khalid
Bc00004 AI Prof Ashraf
Bc00005 Graphics Prof Shaw
Bc00006 AI Prof Ashraf
Bc00007 SE Prof Khalid

 

Semester Table:

 

Student-ID

Specialization-

Semester

Semester

Start-Date

Semester

End-Date

BC00001 F08 15-07-08 21-12-08
BC00001 SP09 15-02-09 25-06-09
BC00001 F09 15-07-09 21-12-09
Bc00002 Sp09 15-02-09 25-06-09
Bc00003 F08 15-07-08 21-12-08
Bc00003 SP09 15-02-09 25-06-09
Bc00003 F09 15-07-09 21-12-09
Bc00004 F09 15-07-09 21-12-09
Bc00004 Sp010 15-02-10 25-07-10
Bc00005 Sp010 15-02-10 25-07-10
Bc00006 Sp010 15-02-10 25-07-10
Bc00007 Sp010 15-02-10 25-07-10
DOWNLOAD SOLUTION HERE
loading...