# 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