loading...

CS403 Database Management Systems Assignment 3 Solution Fall 2013

Introduction:

The Educational and Recruitment Testing System (ERTS) is an organization that conducts academic performance evaluation tests. It is an autonomous, testing organization, conducts tests for admissions, scholarships, and recruitment purposes. It is governed by a Management Committee (Admin). ERTS is a testing and assessment organization facilitating in producing educated and market competitive human resource.

The whole system can be divided into following five different modules:

  1. ERTS Tests and Products
  2. Applicant Registration
  3. Application processing by ERTS
  4. Test
  5. Result

Detail of each module is given below:

  1. ERTS Tests and Products:

The system will use modern technologies for developing tests, test conduction, e-marking and compilation of results.

ERTS is assumed to conduct tests for the following categories:

  • Academic Tests
  • Recruitment Tests

2.      Applicant Registration:

The applicants who are seeking for admission (college/university) or recruitments, which requires an ERTS Test for its eligibility criteria, will have to apply for ERTS Test.

The applicant will be either already registered or will a new applicant. Already registered applicants could be login to the system by entering their username and password and could apply for their interested test category.

In case the applicant has not been registered yet, he will first register himself by filling and submitting the online application form available at ERTS website. After submitting the application form, the applicant will receive a confirmation message and the application will be stored in the ERTS database. After that the applicant will print the filled application along with a bank deposit slip, and will deposit the required amount in the mentioned branches of banks. He will post the application form along with the bank deposit slip on the mentioned address.

3.      Application processing by ERTS:

The applications received by ERTS will be processed according to required criteria and the eligible candidates will be issued a roll number slip. The accepted and rejected both applications will be stored in the ERTS database.

4.      Test:

On the mentioned test date the applicant appears in the test at allocated center.

The invigilator verifies the candidates via their original NIC and roll number slip. The invigilator starts distributing the question booklet and answer sheet according to the selected test category. The candidate attempts the test and returns both the answer sheet and question booklet to the respective invigilator.

5.      Result:

The invigilator will hand over both the answer sheet and question paper to the staff of Educational and Recruitment Testing System (ERTS). The staff will arrange the answer sheets in proper sequence by serial number. The staff starts marking of answer sheets through the scanned process of the system and the system will match the attempted questions with the stored question bank and will mark each question as correct or incorrect. The system will calculate the total marks and will store in the database. The system will declare the online result as well as dispatch the result card to successful candidates after approval from the management committee.

Tasks:

In this assignment you will focus on the task(s) given below:

1. You are given unnormalized relation as shown below.You are required to perform normalization (1st to 3rd Normal forms) on the given relation and write the resultant relations as an output after each normal form.

Relation:

APPLICANTapplicant_Id, applicant_Name, father_Name, gender, applicant_Dob, cell_no, ,application_Id, qualification , application_Name)

Applicant_Id, Applicant_Name father_Name gender applicant_Dob cell_no application_Id qualification application_Name
ap-1001, Hammad Sajid Ali Male 07/04/1987 0333-9895097,0312-5554442 appl-01 MCS GAT-Gen
ap-1002, Ali Umair Male 01/02/1988 0333-5556661 appl-02 MBA GAT-Sub
ap-1003, Salma Maqsood Female 04/05/1988 0300-2002001 appl-03 BCS GAT-Gen
ap-1004, Asad khan M. Faiz Male 06/01/1987 0345-1999888,0333-2223334  appl-04 BBA NAT

 

You have to perform all these tasks given above in your solution file.

Note: First perform 1st NF (write the resultant relations) then 2nd NF (write the resultant relations)  and at the end 3rd NF (write the resultant relations). Use all concepts you have studied so far and techniques in order to normalize the realtion.

Solution: 
Solution: 

I-NF:

Applicant_Id, Applicant_Name father_Name gender applicant_Dob cell_no application_Id qualification application_Name
ap-1001,  Hammad Sajid Ali Male 07/04/1987 0333-9895097,  appl-01 MCS GAT-Gen
ap-1002 Ali Umair Male 01/02/1988 0333-5556661 appl-02 MBA GAT-Sub
ap-1003 Salma Maqsood Female 04/05/1988 0300-2002001 appl-03 BCS GAT-Gen
ap-1004, Asad khan M. Faiz Male 

 

 

 

06/01/1987 0345-1999888,  appl-04 BBA NAT

2-NF

Applicant_Id_PK, Applicant_Name father_Name gender applicant_Dob cell_no qualification
ap-1001  Hammad Sajid Ali Male 07/04/1987 0333-9895097,0312-5554442 MCS
ap-1002  Ali Umair Male 01/02/1988 0333-5556661 MBA
ap-1003  Salma Maqsood Female 04/05/1988 0300-2002001 BCS
ap-1004  Asad khan M. Faiz Male 06/01/1987 0345-1999888,0333-2223334

 

BBA
Applicant id_Pk Cell no. Application name
Ap1001 0333-9895097 Gat-Gen
Ap1002 0333-5556661 Gat-Sub
Ap1003 0300-2002001 Gat –Gen
Ap1004 0345-1999888 Nat

3-NF

Applicant id Applicant name Father name Gender Applicant DOB Qualification
Ap1001 Hammad Sajid ali Male 07/04/1987 MCS
Ap1002 Ali Umair Male 01/02/1988 MBA
Ap1003 Salma Maqsood Female 04/05/1988 BCS
Ap1004 Asad khan M-Faiz Male 06/01/1987 BBA
Cell no. Applicant id_FK Application_id_PK Application name
0333-9895097 Ap1001 App1-01 Gat-Gen
0333-5556661 Ap1002 App1-02 Gat-Sub
0300-2002001 Ap1003 App1-03 Gat –Gen
0345-1999888 Ap1004 App1-04 Nat
DOWNLOAD SOLUTION HERE
loading...