solutions please
CST 2002 2016 Autumn Semester

Assignment 2 specification
Description Marks out of Wtg(%) Due date
ASSIGNMENT 2 100.00 20% 26 Dec 2016
IMPORTANT INFORMATION
You must submit the assignment electronically by the due date on the study desk. If you are unable to complete the whole assignment, submit what you have done rather than nothing.
You must use the data modelling and normalization methodology introduced in the course. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures and the tutorials use this methodology.
If you do not use the methodology, you will probably be awarded a mark of zero.
It is perfectly acceptable if you submit neat hand-drawn ERD’s. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the methodology.
This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are at examination standard.
SECTION A (Data Modelling) (40 marks)
MARKING CRITERIA
1. Entities – no missing entities, appropriate names, no redundant entities, etc.
2. Cardinalities and optionalities all shown and correct.
3. Complete list of relations, showing all applicable attributes, primary keys and foreign keys.
4. Sophistication: well presented solution; good layout; innovative approach; correct
diagrams/notation; solution easy to read and understand; solution comprehensive
5. SQL CREATE TABLE uses an appropriate relation, is syntactically correct and meets the business rules.
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) (20 marks)
MARKING CRITERIA
1. Relations – no missing relations, appropriate names, no redundant relations.
2. All primary keys present and correctly notated.
3. All foreign keys present and correctly notated.
4. All attributes present.
5. All repeating groups resolved.
6. Derived attributes indicated in brackets.
7. All 2NF and transitive dependencies resolved.
8. All relations correctly notated using USQ methodology.
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) (40 marks)
MARKING CRITERIA
1. Four marks awarded for each correct SQL statement and one mark for the correct output.
2. Alternative approaches to the model answer could be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).
3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed.
For each question, four marks will be awarded for the SQL and one mark for the output.
The following E-R diagram represents a Car Hire database.
In this question, you will use the CAR HIRE database. The CAR HIRE database including
appropriate data will be made available on the 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 server, download the script file that will create the database for you.
The table descriptions appear below, including the column names and data types.
I_CAR
Column Name Type and Size Constraints Description
Registration VARCHAR2(7) NOT NULL Registration number of the car. This is the Primary key.
Model_name VARCHAR2(8) FK Model for the car. Foreign key into the Models table.
Car_group_name VARCHAR2(2) FK Group code defining type of car and rental cost. Foreign key into the CarGroups table.
Date_bought DATE Date the car was purchased.
Cost NUMBER(8,2) The original cost of the car.
Miles_to_date NUMBER(6) The current mileage of the car as read at the end of the most recent rental.
Miles_last_service NUMBER(6) The mileage of the car when it was last serviced.
Status CHAR(1) The current status of the car. ‘A’ for available, ‘H’ for on hire, ‘S’ for being serviced, ‘X’ for in need of service or repair.
I_CARGROUP
Column Name Type and Size Constraints Description
Car_group_name VARCHAR2(2) NOT NULL The car group code. This will be one of the following values: ‘A1’, ‘A2’, ‘A3’, ‘A4’, ‘B1’, ‘B2’, ‘B3’, or ‘B4’. This column is the primary key for this table.
Rate_per_mile NUMBER(3) The charge per mile for cars in this group in cents.
Rate_per_day NUMBER(5,2) The rental charge per day for cars in this group in dollars and cents.
I_MODEL
Column Name Type and Size Constraints Description
Model_name VARCHAR2(8) NOT NULL The model name, an abbreviation of the full model name. This is the primary key for this table.
Car_group_name VARCHAR2(2) FK The group to which this model of car belongs.
Description VARCHAR2(30) Full description of the model.
Maint_int NUMBER(5) Number of miles between services for this model.
I_CUSTOMER
Column Name Type and Size Constraints Description
Cust_no NUMBER(5) NOT NULL The customer account number. This is the primary key for this table.
Cust_name VARCHAR2(20) NOT NULL The name of the customer.
Address VARCHAR2(20) Street address of the customer.
Town VARCHAR2(20) Town the customer lives in.
County VARCHAR2(20) County the customer lives in. Default is Australia
Post_code VARCHAR2(10) Postcode for the town.
Contact VARCHAR2(20) Name of person to contact.
Pay_method CHAR(1) Code to indicate the usual payment method for this customer. ‘A’ indicates an account, ‘C’ indicates cash or credit card, NULL indicates unknown.
I_BOOKING
Column Name Type and Size Constraints Description
Booking_no NUMBER(5) NOT NULL A serial number used to uniquely identify the booking. This is the primary key for this table.
Cust_no NUMBER(5) FK Customer number of the customer making the booking.
Date_reserved DATE Date on which the booking was made.
Reserved_by VARCHAR2(12) Name of the person who took the reservation.
Date_rent_start DATE Date on which the rental commences.
Rental_period NUMBER(3) Length of rental period in days.
Registration VARCHAR2(7) FK Registration of the car actually rented.
Model_name VARCHAR2(8) Model of the car rented.
Miles_out NUMBER(6) Miles on the odometer at the start of the rental.
Miles_in NUMBER(6) Miles on the odometer at the end of the rental.
Amount_due NUMBER(6,2) Cost of the rental. Calculated when the car is returned.
Paid CHAR(1) Flag to indicate if this rental has been paid for. ‘Y’ if it has been paid and ‘N’ if not.
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.