COMP2400/COMP6240 – Relational Databases Assignment 1

COMP2400/COMP6240 – Relational Databases

Assignment 1
Due date: 10am, 23 August 2016

This assignment will be marked out of 10. It will count for 10% of the final grade. Below you will find 3 questions to reach this score. Marks are assigned for the process of finding a solution, not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution. Whenever you feel that some information is missing, add an assumption and make it explicit in your solution.

Instructions:

• This assignment should be done individually or by a pair of students.
• Your submission must include Assignment Cover Sheet, which is available from the

  course website on Wattle.

• Hand written submissions will not be marked.
• You need to submit your assignment as a hard copy to the assignment box in the student foyer (i.e., on the ground floor of the CSIT building). You are also required to submit an electronic copy on Wattle.
• Late submission, unless authorized by the lecturers, will attract the penalties 5% per day. If submitted after one week of its deadline, the assignment will not be marked.
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. Good luck and enjoy the time you will spend on this assignment.

  Question 1 1.5 Marks

  Consider the following relational database schema of a database used for managing patient appointments of a medical centre, which operates in three shifts:

• MedicalPractitioner(MPRN, MPName, Address, PhNo, Email)
• Patient(MedicareNo, PName, DoB, Gender, Address, PhNo, Email)
• Appointment(MedicareNo, PName, MPRN, MPName, ApptDate, ApptTime, RoomNo, Fee)

1

2 Assignment 1 The description of some attributes, used in the above relational database schema, is as

follows:

• MedicareNo is the same for all members of a family but the PName uniquely identifies members within the same family.
• MPRN stands for Medical Practitioner Registration Number which uniquely identifies a medical practitioner.
• MPName stands for Medical Practitioner’s name which is not unique.
• ApptTime is the time for starting of a medical appointment.
• All appointments are of the same duration.

  The constraints for appointments are as follows:

1. A patient can only have one appointment on a given day at a given time.
2. Onanygivenday,adoctorisassignedaroomfortheentireshiftofher/hisconsultations with patients.
3. On any given day, a room may be allocated to two or more doctors.
4. On any given day, the same room cannot be allocated to two or more doctors working in the same shift.
5. Two appointments cannot take place in the same room at the same time on the same day.
6. Two members of the same family cannot have appointments at the same time on the same day.
7. On any given day, a patient can only have one appointment with the same doctor.

Your tasks are to
1.1 Find all candidate keys for the relation schemas Patient and Appointment;

1.2 Find all foreign keys for the relation schema Appointment.

COMP2400/COMP6240 – Relational Databases 3 Question 2 6 Marks

ServiceOne is a corporate helicopter company that operates on-demand fights with a number of helicopters of various kinds. The company provides four types of flights: direct flights, tours, utility projects, and areal filming. Direct flights operate on specified routes identified by route numbers, tours are specified by tour codes, areal filming is identified by area codes and utility projects are identified by category. ServiceOne uses di↵erent kinds of helicopters to suit di↵erent types of flights. A flight can be booked by a customer to be availed by one or more passengers. The company has its own workshop to conduct mechanical services on its fleet of helicopters. A mechanical service on a helicopter is conducted by a team of two service engineers. ServiceOne requires a relational database system for storing data of its sta↵, customers, bookings, flights, and helicopter maintenance records. Data about customers includes customer identification number, customer name, telephone number, and address. The employees of ServiceOne are classified as pilots, service engineers and administration sta↵. The data held for each employee includes employee identification number, first and last names, date of birth, salary, telephone number, and address. Additional data recorded for pilots includes license number, date of pilot license and pilot’s most recent check-up date. The data held on helicopters is registration number, model code and name, number of seats, mileage, engine strength, and manufacturer. The data held on each helicopter’s mechanical service includes type of service, date of service, time of service, service details and comments. The data held on each flight is helicopter registration number, type of flight, customer identification number, date, departure time, arrival time, cost and description including the details of the passengers. Some of the compulsory business assumptions are as follows.

• A service team is a temporary grouping of two service engineers for a particular me- chanical service on one or more helicopters.
• A flight can be booked in the name of one customer only.
• Each flight can only be of one type.
• A helicopter is operated by only one pilot.

Your task is to design, using the notation of lecture notes, an Enhanced Entity Relation- ship (EER) diagram for ServiceOne based on the above description. Your design should incorporate the following requirements:

1. 2.1  Identify the entities described above.
2. 2.2  Identify the relationships between the entities described above. Determine and repre- sent the cardinality ratios and participation constraints for each relationship.
3. 2.3  Identify all the important attributes and associate them with the entities or relation- ships. Determine the key attributes for each entity.

4 Assignment 1 2.4 Are there any integrity constraints or information that you are not able to represent

in your schema or diagram? If so, give some examples.

Remark: Whenever you feel that some information is missing in the description above, add an assumption and make this assumption explicit.

Question 3 2.5 Marks

Consider the following Entity Relationship (ER) diagram:

Figure 1: The ER diagram for Question 3

Your tasks are to

1. 3.1  Transform the ER diagram into a relational database schema, including to identify the primary and foreign keys for each relation schema;
2. 3.2  Implement the above relational database schema using PostgreSQL. Using the Post- greSQL command “\d table name” to present your implementation result for each relation schema in the relational database schema.

   +++++