CS403 Database Management Systems Assignment 2 Solution Fall 2013

This Assignment is the continuation of First Assignment. Take same case study given in Assignment 1 (also provided at the end of this assignment), apply the techniques and methods given in lectures and by utilizing the solution of first assignment uploaded on LMS in the form of DFD in order to finalize the tasks of second assignment listed below in task description.  Some hints are provided below to link DFD with ERD which can be helpful in your design process.


  1. There should be processes in DFD which create, modify and delete instances of the entities in the ERD.
  2. For every relationship in ERD there should be a process in DFD which uses it.



Please refer to the case study given in assignment no. 1.

Note: Same case study is also given at the end of this assignment for your convenience.


In this assignment you will focus on the tasks given below:

Task#1: [Marks 8]

Identify all the Entities along with proper attributes.

Task#2: [Marks 12]

Draw a comprehensive ER-diagram consisting of these Entities (identified in Task#1) along with their relationships and cardinalities.

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


Use all concepts you have studied so far and techniques in order to identify Entities, attributes and their relationships of the given case study discussed particularly from 7 to 10 regarding ER data model.



Drawing Final ER Data Model:

You can use any tool for drawing like MS Office or Visio.

Important things to consider: 

  1. As happens in real world that everyone visualize a problem in different way so the solutions of all students should be according to their own thinking not taken from some source.
  2. As this is your first comprehensive design, so you have to work very keenly and with concentration. This design requires your full attention as this conceptual design will be transformed into a relational model in next assignment. Any mistake in this conceptual design can cause you much burden afterwards.


Case Study

Consider a scenario of an Educational and Recruitment Testing System (ERTS). We are going to automate the ERTS system so that there may be no chances of error and aim is to facilitate the applicant to fill the online application form with ease. To complete this system we will divide this whole project in small assignment chunks (probably 4) and will try to cover every phase of the database development life cycle from scratch to implementation (SQL Queries). Scenario of the ERTS system is given below.



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.


the external entities in this system (ERTS system).


These are:

1. Applicant

2. ERTS test and product. (means all available products)

3. Test ( that test which a applicant actually does in exam center)

4. Result

5. Announcement

6. Admin of ERTS

7. Staff of ERTS (permanent)

8. Invigilator (hired for exam only)

9. Exam center

10. Exam Center Manager

11. Account