Entity Relationship Diagram

Entity-Relationship Diagram

Notes

Past Paper Questions

Wolmer’s Trust High School for Girls

CAPE COMPUTER SCIENCE

Upper and Lower Six Teacher: Mrs. McCallum-Rodney

Questions on Entity Relationship Diagrams

1. Past Paper 2004; Paper 02; Question 2(c)

The following data must be recorded in a hospital system:

Ward: ward number, name, type, number of beds

Doctor: doctor number, name, telephone

Patient: patient number, name, address, date of birth

Drug: drug number, name, cost per unit

At any given time, a ward can contain zero or more patients. A patient is treated by exactly one doctor who may prescribe zero or more drugs, depending on the illness.

Draw an Entity/Relationship model that captures all the above information.

[10 marks]

2. Past Paper 2005; Paper 02; Question 2(b)

Carisoft is a company that develops software in several Caribbean countries. In each of its software development projects, a project is decomposed into a set of tasks (for example, analysis, design, writing code or testing). Each task is performed at a particular location in the Caribbean (country, town/city) and may employ one or more CASE tools. The employees at Carisoft may be assigned to one or more projects.

Note the primary key for employee, project and task is EmpID, ProjID and TaskID, respectively. As task takes a certain number of days to complete.

Draw an entity relationship-diagram that shows the entities and the relationships between the entities described in the narrative above. [15 marks]

3. Past Paper 2006; Paper 02; Question 2(a)(iii)

A certain bank has many customers who may choose to open one or more accounts. Data stored on accounts include account number and balance. A customer can open more than one account and an account can be owned by one or more customer. Withdrawals and deposits may be performed on each account. When a withdrawal or deposit takes place, the amount and date of the transaction is recorded.

Draw an entity-relationship model based on the above narrative. [16 marks]

4. Past Paper 2008; Paper 02; Question 2(a) (ii)

A certain university has several departments. A department is responsible for the management of one or more courses. A department hires lecturers who may teach several courses being managed by that department. Many students attend the university and may register for one or more courses.

The following data must be stored on each student: student id, last name, first name, and date of birth, where student id is the primary key for student.

Draw an entity-relationship model based on the above narrative, stating any assumptions you make. [13 marks]

Additional Questions

5. Use an Entity-Relationship diagram to depict the information needs of the following engineering enterprise:

- Each engineer works on a number of projects. For every engineer a record is kept of his/her number, name, title and salary.

- For every project a record is kept of its number, name, budget and location. Additionally, the responsibility each engineer has on a particular project and the amount of time spent on the project is noted.

- Clients contract these projects. The clients name, address and phone number must be recorded.

- The contract date, i.e. the date at which the client signs the contract for a specific project is also noted.

- A record is also kept of the machinery being used for a particular project. The name, amount and the date the machinery is needed for the project is recorded.

- Include any attributes you think should be represented (state why). State any assumptions you have made.

6. Use an Entity-Relationship Diagram to depict the following requirements for a restaurant:

- The restaurant employs a number of chefs. A record is kept of each chef’s name, address, phone number and salary.

- Each chef can prepare a number of meals. The name of the meal and the price of the meal is recorded.

- Each meal consists of a number of ingredients. The name of the ingredient and the quantity required for that particular meal is recorded.

- These meals are ordered by customers. A record is kept of the customers name, address and phone number. A record is kept of the time and date the meal is ordered.

State any assumptions made in the design of the E-R diagram.

7. A database for a local garage is needed. The database contains data items for customer's name, customer's address, customer's home telephone number, customer's work telephone number, the vehicle's make, the vehicle's model, description of the work done, cost of the work, date of work done, part number, part name, cost of each part and the total cost.

Draw an Entity-Relationship Diagram to depict this enterprise, noting any assumptions made.________________________________________________________________________________________________________