Reference no: EM13141562
A medical practice in Brisbane needs to develop a patient appointment system so that the reception staff can easily check doctor schedule, allocate consultation times and manage medical tests for patients. You are required to develop a data model which will allow following information and activities to be recorded.
The system needs to store doctor's name, address, qualification, contact number and date of joining the practice. A doctor is categorised as either a specialist or general practitioner. For a specialist the system also stores the area of speciality. Both categories of doctors provide consultation to patients. For a patient the system stores patient identifier, name, address, date of birth, gender and medical history. Medical history is stored as a single field of text in the current design.
When a consultation is setup between a patient and a doctor, we record the consultation date, start time, duration and fees. A consultation may result in a referral to a specialist. A referral is stored in the system with the following details: referral date, reason for referral, the specialist being referred and the consultation that led to the referral.
A consultation may also result in requests for one or more tests that are conducted by the practice. It is mandatory to have a consultation by a doctor before a test can be conducted. There are a number of tests such as blood tests and fitness tests that can be administered by the staff employed by the practice. For every test we store test identifier, cost, consultation identifier that requested the test, test date, test result date and a follow up flag in case the client requires further urgent consultation or further tests.
Prepare the following:
a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.
b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.
c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.
SECTION B (Normalisation)
Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. You must use the Finkelstein methodology as used in the study book and tutorials.
STUDENT (student number, name, address, email, ((course code, course name, description, grade, ((lecturer name, lecturer contact number, date assigned)) )), number of courses enrolled, student contact phone)
1. Each student is identified by a student number.
2. A student undertakes multiple courses and each course comprises multiple lecturers.
3. The attribute 'grade' is achieved by a student in a course.
4. The attribute 'date assigned' represents the date when the lecturer is assigned to the course.
SECTION C (SQL)
In this question, you will use the CAR HIRE database. The CAR HIRE database including
appropriate data will be made available on the USQ Oracle server. You may query any of these tables but may not make changes.
If you are using Oracle on your own computer and are unable to access the USQ server, email the course leader for a script file that will create the database for you.
The table descriptions appear below, including the column names and data types.
Write SQL queries to solve the following specifications. Provide the queries and the output.
1. Display full details of all customers where (a) customer name starts with 'S' or ends with 'CO' and (b) county has not been specified. Order the output by customer name in ascending order.
2. Using a set operator, find out all customer numbers who have not made any bookings.
3. Display the car registration, cost, model name, model description, car group name and rental charge per day for each car bought after 31 December 2011.
4. Display the average cost of cars for each car group where the average cost is greater than $50,000.
5. Display the total number of bookings made for the most expensive car.
6. Display the car registration and model name for all cars that are more than eighteen months old.
7. Display all the bookings made in the year 2012 and reserved by 'JANE B'.
8. Display the total amount due for the bookings made on the month of February in the year 2013. Exclude the amount that has not been paid.