Tutorial 2

• Before the Tutorial
• Extension Work
• Questions
  • ER Diagrams

Before the Tutorial

Revise the material from Lecture 07, Lecture 08 and Lecture 09.

[Top]

Questions

ER Diagrams

Draw an ER digram for each of the scenarios below.

For the first three scenarios, draw Level Zero diagrams.
For the second three scenarios, include attributes on your diagrams.

ER Scenarios

1. Reworded 9 August 2006
   During a semester, a student at the University of Woolloomooloo (UW) must pursue exactly one course of study (similar to a degree program at ANU). A UW student may enrol in several units (possibly none) each semester. Each unit (aka course) may run several tutorials. Each unit must run at least one tutorial. Unlike the situation at ANU (where COMP2400 and COMP6240 share tutorials), tutorials at UW are run for one unit.

   
   

2. A unit (aka course) at the University of Canberra may prescribe several books, or may prescribe no books at all. A few popular titles are prescribed by many units. Each book has a  least one author who may have authored several books. A unit is run by one lecturer-in-charge. Lecturers may run several units in the same semester. Each title has one publisher. Some publishers publish many thousands of different titles.

   
   

3. A factory has a machine shop which houses several machines. Each machine requires a specific skill to operate it. An operator may possess several relevant skills. (An operator who does not have at least one relevant skill will not be hired by the factory.) A particular skill can be possessed by several operators. It may be that the factory does not currently employ anyone with a particular skill.

   
   

4. A management system for a factory requires a database to record information about projects, tasks, machines and operators.

   Each project (identified by Proj-id) consists of one or more tasks (which are numbered serially within the project). Each project has a manager, and the factory estimates the cost of each project. Each task has a start date, and the factory estimates the time needed for each task. Each task requires at least one machine.

   Each machine may be used on several tasks. A machine may, or may not, require an operator. During each week, a particular machine (identified by Machine-no) will clock up a total time used. Similarly, an operator (identified by Operator-id) will clock up total hours worked.

   An operator may work several machines (possibly none as yet). A machine may, or may not, need an operator to work it. Management wants to record the total amount of time each operator has spent working each machine.

   The following information is also important to management:

   • location and type of each machine
   • name and base-wage of each operator
   
   
   

5. The management of O'Leary's Hospital requires a database to support the care of in-patients.

   When patients are admitted, they are given an identification number (unique), and asked to complete a form giving their name, Medicare number, birthdate, next-of-kin, and allergy details. The hospital also records the date each patient was admitted.

   If a patient is admitted more than once, s/he receives a different Patient Id for each admission.

   During each patient's stay in hospital s/he:

   • is in exactly one ward
   • receives at least one treatment
   • is cared for by exactly one house doctor

   The hospital also records the date each patient was discharged from hospital.

   Besides patient data, the database is to hold data on wards, house doctors and treatments.

   Each ward has a name (unique), a certain capacity (ie contains a certain number of beds), and a sister-in-charge. Only one sister can be in charge of a ward at any one time.

   Each house doctor has a title and a name and is allocated exactly one extension number.

   Treatments are identified by a code number. Associated with each treatment is a daily cost.

   
   

6. A drugs database is to be set up for a veterinary hospital.

   The following data is to be held on each drug:

   • Drug code (unique)
   • Name of drug
   • Description of drug
   • Quantity in stock
   • Supplier identifier
   • Antidote (in the event of poisoning)

   Data is also to be held on the treatments which uses these drugs. Each treat is allocated a Treatment-id (unique), and the veterinary hospital records the daily cost of the treatment.

   The veterinary hospital also records the quantity of each drug required for each treatment which uses it.

   Certain drugs are known to be incompatible with other drugs. If incompatible drugs are taken together, the animal being treated may suffer an adverse reaction. The database must be able to provide information on drugs which are incompatible and the recommended remedial action if the drugs are taken together.

[Top]

Extension Work

Draw an ER diagram which model the TVS movies database.

[Top]