I_CAR

Section A (Data Modelling)

A local pharmacy store in Wollongong wants to develop an online system to track their product inventory as well as prescription records of their products for regulatory requirements. As part of systems development, a data model is required to determine data entities and associated attributes and business rules. You are required to develop a data model which will allow the information and activities to be recorded.

The most important data entity for the system is the ITEM entity – this entity represents all the products in the pharmacy. Item code, shelf, location and quantity are the attributes for ITEM entity. Every time an item is ordered, some of the order details such as order date, order quantity and special instructions to the patient for each item in every order are stored. Patient information includes an id, patient name, date of birth, contact number and next of kin details. Each patient may have multiple orders of several items. While most orders are from patients this is not always the case. An order can be created without necessarily being associated with a patient, e.g. order from a casual customer buying items.

An item can be of two types: medical or non-medical. Understandably more than 95% of items in the pharmacy are medical items. For non-medical items, promotion period is an additional attribute to store since the pharmacy only sells non-medical items during promotions. Medical items are further classified into three types: doctor-prescription items, over-the-counter prescription items, and no-prescription items. Prescription records are required to be stored in the system. One doctor can prescribe multiple doctor-prescription items and vice versa. We store the prescription date and special instructions by the doctor prescribing a particular item. It is also important to store the doctor’s name, specialty and registration number from the Medical Board of Australia.

An over-the-counter prescription item must be given by one of the store’s pharmacists after consultation with the customer. Several over-the-counter items can be prescribed by different store pharmacists; therefore we also need to store the prescription date and special instructions by the pharmacist prescribing a particular item.  A pharmacist is one of two types of staff at the pharmacy store. The other type is general staff. For all staff, we store their name, date of birth. joined date and qualifications and level. For a pharmacist, we also need to store their registration number from the Pharmacy Board of Australia.
 
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) A Sql Create Table statement for the ITEM relation along with the creation of primary and foreign key constraints as required.    

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 for a flight reservation system. You must use the Finkelstein methodology as used in the study book and tutorials.

FLIGHT (departure airport code, departure airport name, departure time, departure date, arrival airport code, arrival airport name, arrival time, arrival date,  aircraft name, manufacture date, first class seats, business class seats, economy class seats, ((flight crew member, date joined, hours worked, crew member position, ((crew member role)) )), (( booking number, booking type, booking date, ((passenger surname, passenger first name, passenger dob, passenger contact number, seat number, special requests)), ticket price, booking fee)) )

Notes:
1. Name of an aircraft can uniquely identify any aircraft.
2. The number of seats in the economy, business and first class can be different for each aircraft.
3. Each flight has a service of several crew members. Each crew member has one position but can have multiple roles such as cabin service and supervisor that must be recorded.
4. The number of hours that each crew member worked in a particular flight regardless of their role is stored in the “hours worked” attribute.
5. A departure airport for one flight can be an arrival airport for another flight and vice versa. Hence, it is important to uniquely identify an airport.
6. A particular booking can be of two types: direct or through agent.
7. Ticket price can be different for every flight under different bookings.
8. Seat number and special requests by a passenger are associated with the corresponding flight and booking reference along with the passenger.

Section C (SQL)

For each question, four marks will be awarded for the query and one mark for the screen capture of the results obtained after executing the query. Please copy the screenshot and paste it in your assignment solution document for every question.

The following E-R diagram represents a Car Hire database.

I_CAR_GROUP

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.

The table descriptions appear below, including the column names and data types.

I_CAR

I_CARGROUP

I_MODEL

I_CUSTOMER

I_BOOKING

Write SQL queries to solve the following specifications. Provide the queries and the output.

1. Display full details of all car bookings where (a) the odometer reading of cars is greater than 30,000 miles at the start of their rental and the reservation was done by a staff called REDMOND, OR (b) the car has not been returned and the car model name ends with ‘TR’. Order the output by the date on which the booking was made, showing the most recent date first.

2. Using a set operator, find out all names of the customers that have not had any bookings.

3. Display the booking number, rental rate per day, rental period and the current status of the car for all car bookings. Filter your results to show only the records where the car is on hire and the rental rate per day is greater than $35.

4. Display the car registration number and the average miles travelled in all booking by each car where the average miles travelled is less than 1,000 miles and registration number begins with H.

5. Display the total rental income to date from the least expensive car available for rental. Format the output as a currency value (i.e. formatted as $9,999.99).

6. Display the car registration, rental cost, mileage of the car when it was last serviced and current mileage of the car for all completed bookings where the car is rented more than 30 months after it was bought.

7. Display booking number, date on which the booking was made and the cost of the rental for all the bookings made in the year 2012 and the car is already returned. Also include the name and full description of the corresponding model of the car used in the rental.

8. Display the customer number and the average rental income from that customer from all bookings where the average rental income is greater than the highest rental income in all bookings reserved by a staff named REDMOND. Exclude all bookings where the rental period is less than 10 days.

1. ER diagram

2. A list of relations

Item (item code#, shelf, location)

Non medical item (item code#, shelf, location, promotion period)

Medical item (item code#, shelf, location)

Staff (staff id#, name, date of birth, joined date, qualifications, level)

Store pharmacist (registration number#, name, date of birth, joined date, qualifications, level)

Patient (patient id#, patient name, birth date, contact number, next kin detail)

Customer (customer id#, customer name, birth date, contact number)

Doctor (registration number#, doctor’s name, specialty)

Prescription (prescription code#, prescription date and special instructions, registration number#, item code#)

Order (order code#, order date, order quantity, special instruction, patient id#, item code#)

Consultation (consultation code#, registration number#, customer id#, item code#)

3. Oracle SQL table create statement:

Create statement for Item realtion

CREATE TABLE Item

(

Item code NUMBER(5) NOT NULL primary key,

Shelf VARCHAR(10) NOT NULL,

Location VARCHAR(10) NOT NULL

);

Un-normalised relation

FLIGHT (departure airport code, departure airport name, departure time, departure date, arrival airport code, arrival airport name, arrival time, arrival date,  aircraft name, manufacture date, first class seats, business class seats, economy class seats, ((flight crew member, date joined, hours worked, crew member position, ((crew member role)) )), (( booking number, booking type, booking date, ((passenger surname, passenger first name, passenger dob, passenger contact number, seat number, special requests)), ticket price, booking fee)) )

Primary key: Red color

Foreign key: Green color

PASSENGER (passenger number, passenger surname, passenger first name, passenger dob, passenger contact number, seat number, special requests)

ROLE (crew member role, description)

CREW MEMBER (flight crew member, date joined, hours worked, crew member position, crew member role)

BOOKING (booking number, booking type, booking date, ticket price, booking fee, passenger number)

AIRCRAFT (aircraft name, manufacture date, first class seats, business class seats, economy class seats, flight crew member, booking number)

AIRPORT (departure airport code, departure airport name, departure time, departure date, arrival airport code, arrival airport name, arrival time, arrival date, aircraft name)

1. SELECT * FROM I_Booking Where (Miles_out > 30000 AND Reserved_by = ‘REDMOND’) OR (Model_name LIKE ‘%TR’) ORDER BY Date_reserved DESC;

2. SELECT Cust_no FROM I_CUSTOMER Minus SELECT Cust_no FROM I_Booking;

3. SELECT Booking_no, Rate_per_day, Rental_period, I_Car.Status FROM I_Booking, I_Car, I_CARGROUP WHERE I_Booking.Registration = I_Car.Registration AND I_Car.Car_group_name = I_CARGROUP.Car_group_name;

4. CREATE VIEW AVERAGE_MILES AS SELECT Registration, AVG(Miles_in – Miles_out) AS AVG_MILES FROM I_Booking GROUP BY Registration; SELECT * FROM AVERAGE_MILES where AVG_MILES < 1000 AND Registration Like ‘H%’

5. CREATE VIEW INCOME_RENTAL AS SELECT I_Car.Registration, SUM(Rental_period * Rate_per_day) AS “Total rental income” FROM I_Booking, I_Car, I_CARGROUP WHERE I_Booking.Registration = I_Car.Registration AND I_Car.Car_group_name = I_CARGROUP.Car_group_name GROUP BY I_Car.Registration;

SELECT * FROM INCOME_RENTAL WHERE Registration = (SELECT Registration FROM I_CAR WHERE Cost = (SELECT MIN(I_CAR.Cost) FROM I_CAR INNER JOIN INCOME_RENTAL ON I_CAR.Registration = INCOME_RENTAL.Registration))

6. SELECT I_Car.Registration, Rate_per_day, Miles_last_service, Miles_to_date FROM I_Booking, I_Car, I_CARGROUP WHERE I_Booking.Registration = I_Car.Registration AND I_Car.Car_group_name = I_CARGROUP.Car_group_name AND (Date_reserved-Date_bought) > 1000;

7. SELECT I_Car.Registration, (Rental_period * Rate_per_day) AS “Total rental income”, I_Booking.Date_reserved FROM I_Booking, I_Car, I_CARGROUP WHERE I_Booking.Registration = I_Car.Registration AND I_Car.Car_group_name = I_CARGROUP.Car_group_name AND Date_reserved < ’30-DEC-2012′ AND Date_reserved > ’01-JAN-2012′;

8. SELECT Reserved_by, AVG(Rental_period * Rate_per_day) AS “Rental_income” FROM I_Booking, I_Car, I_CARGROUP WHERE I_Booking.Registration = I_Car.Registration AND I_Car.Car_group_name = I_CARGROUP.Car_group_name GROUP BY Reserved_by;