SEMESTER 3, 2012
| Description | Marks out of | Wtg(%) | Due date |
| ASSIGNMENT 1 | 100.00 | 13.00 | 08 January 2012 |
You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book and the tutorials use this methodology.
If you do not use the USQ 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 USQ methodology.
SECTION A (APPLIED DATABASE THEORY) (20 marks)
FASHION SHOES is a ladies footwear business with over 60 retail outlets in NSW, VIC and QLD. The company wishes to develop a system that controls and manages their inventory to better coordinate operations between their outlets and three warehouses in the three states.
The company head office in Melbourne has initiated development of a database-driven inventory management system. The general manager of the company wants to develop the system in the head office with web-based access for all store and warehouse managers from their offices. The project proposal has recently been approved and initial requirements gathering and analysis has been undertaken.
As part of the next phase in the project, the project manager has appointed you as the data analyst/ modeler and you are responsible for refining the data requirements for the project and modelling the database for the system. The project manager firstly wants you to research the expectations that database users (data entry/ admins), store managers and warehouse managers may have for the new database-driven inventory system during a stakeholder analysis.
Write a memorandum to the project manager and present your research findings on the expectations of the three different types of users. Your memorandum should be no longer than 500 words.
A template for the memorandum will be placed on the Study Desk in the ASSIGNMENTS folder. You should use this template.
SECTION B (SQL) (40 marks)
For each question, three marks will be awarded for the SQL and one mark for the correct 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 USQ Oracle server. You must use this data.
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 tables for you. Due to copyright issues, you will need to insert the data yourself but you will be provided with a template.
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. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT/PRNTSCRN command sequence.
1. Display full details for all customers.
2. Display the registration, cost and status for all cars. Order by cost in descending order.
3. Display full details for all bookings where the car rental has not been ended (i.e. the miles_in has not yet been entered).
4. Display the model name for all cars which have been rented at least once. Do not display duplicates.
5. Display the expected rental end date for each booking and sort the output by the rental end date in descending order. Label the output: RENTAL END DATE
6. Display full details for all bookings where the amount due was greater than $1,000.00 and the booking has not been paid.
7. Display full details for any customer who
· Lives in a town where name starts with a B and whose customer number has 7 as the first character OR
· Has a customer number greater than or equal to 600 and has a post code whose second character is N.
8. Display full details for all cargroups where the cargroup name is A2 or B2 OR the rate per mile is either 115 or 125. Find a way of optimising your code so that the complete SQL statement has only logical operator (AND, OR, NOT).
9. Display the registration, car group name and car model name for all cars. Order by model name descending within car group name ascending. Your query must not use the WHERE clause.
10. Using a traditional join (WHERE CLAUSE) display the customer name, car model name and the booking number for all bookings. Order by booking number within car model name (both ascending). Your query must use the alias B for I_BOOKING and the alias C for I_CUSTOMER.
SECTION C (Data Modelling) (40 marks)
In assignment 2, you will construct a more substantial data model. These exercises provide you with the opportunity to develop your skill on a few very small problems.
PART MARKING: We will only mark TWO of the four questions below but you must submit answers to all four. We will choose the questions we mark randomly and we will supply you with model answers to all the questions.Construct data models for the following specifications. Include an ERD and a list of relations (entity list). Your relations must show all attributes, primary keys and foreign keys. You must use the USQ (Finkelstein) methodology as described in your Study Book, the lectures and the tutorials.
Question 1
A warehouse may send purchase orders to many suppliers over time. A supplier can receive multiple orders from different warehouses. For warehouse, we store the warehouse code, location and state. We also need to store the date a warehouse has sent purchase order to a supplier; and company name, address and contact number of every supplier.
Question 2
A course may have many prerequisite courses. A prerequisite must relate to only one course. A prerequisite is also a course. For course, we store the course number and course name.
Question 3
An ambulance driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to twelve. For driver, we store the name, address and birth date. For team, we store the team id and the location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the rating of the driver within the team.
Question 4
A shoe must be assigned to one category. A shoe may come in multiple colours and sizes. For shoe, we store the shoe SKU (unique), brand name and retail price, while we store a category number and name for category. Colour consists of colour code and colour name. Size stores size code, European number and Australian number. The shoe must be distributed by one distributor for whom we store name, address and a unique id.
Prepare the following for all four questions:
1 An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials.
2 A list of relations (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 but do not create any other additional attributes.
MARKING CRITERIA
SECTION A
1. Solution addresses the business problem and provides a workable resolution to the problem.
2. Solution demonstrates appropriate reference to relevant sections of the selected readings, textbook and, if applicable, other reference material.
3. Solution addresses the assumptions of the problem appropriately.
4. Solution uses the correct template.
5. Solution is grammatically and structurally sound.
6. Presentation is neat and professional.
SECTION B
1.Three marks awarded for each correct SQL statement and one mark for the correct output.
2. Alternative approaches to the model answer will often 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.
SECTION C
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
drop table i_cargroup cascade constraints;
drop table i_model cascade constraints;
drop table i_customer cascade constraints;
drop table i_car cascade constraints;
drop table i_booking cascade constraints;
drop table cargroups cascade constraints;
drop table models cascade constraints;
drop table customers cascade constraints;
drop table cars cascade constraints;
drop table bookings cascade constraints;
CREATE TABLE i_cargroup
(car_group_name VARCHAR(2) constraint cargroups_pk primary key,
Rate_per_mile NUMBER(3),
Rate_per_day NUMBER(5,2));
CREATE TABLE i_model
(Model_name VARCHAR2(8) constraint models_pk primary key,
Car_group_name VARCHAR2(2) constraint models_fk references i_cargroup(car_group_name),
Description VARCHAR2(30),
Maint_int NUMBER(5));
CREATE TABLE i_customer
(Cust_no NUMBER(5) constraint customers_fk primary key,
Cust_name VARCHAR2(20) not null,
Address VARCHAR2(20),
Town VARCHAR2(20),
County VARCHAR2(20),
Post_code VARCHAR2(10),
Contact VARCHAR2(20),
Pay_method CHAR(1));
CREATE TABLE i_car
(Registration VARCHAR2(7) constraint cars_pk primary key,
Model_name VARCHAR2(8) constraint cars_fk1 references i_model(model_name),
Car_group_name VARCHAR2(2) constraint cars_fk2 references i_cargroup(car_group_name),
Date_bought DATE,
Cost NUMBER(8,2),
Miles_to_date NUMBER(6),
Miles_last_service NUMBER(6),
Status CHAR(1));
CREATE TABLE i_booking
(Booking_no NUMBER(5) constraint bookings_pk primary key,
Cust_no NUMBER(5) constraint bookings_fk1 references i_customer(cust_no),
Date_reserved DATE,
Reserved_by VARCHAR2(12),
Date_rent_start DATE,
Rental_period NUMBER(3),
Registration VARCHAR2(7) constraint bookings_fk2 references i_car(registration),
model_name VARCHAR2(8),
Miles_out NUMBER(6),
Miles_in NUMBER(6),
Amount_due NUMBER(6,2),
Paid CHAR(1));
/* 2. Populate the tables. */
SET SCAN OFF;
INSERT into i_cargroup VALUES
('A1',110,25);
INSERT into i_cargroup VALUES
('A2',115,24.5);
INSERT into i_cargroup VALUES
('A3',155,36.75);
INSERT into i_cargroup VALUES
('A4',125,33.5);
INSERT into i_cargroup VALUES
('B1',110,25.9);
INSERT into i_cargroup VALUES
('B2',125,33);
INSERT into i_cargroup VALUES
('B3',135,37.75);
INSERT into i_cargroup VALUES
('B4',135,38.5);
/* --------------------------*/
INSERT into i_model VALUES
('ASTON V8','A1','ASTON MARTIN V8 VANTAGE',10000);
INSERT into i_model VALUES
('BMW 635','B2','BMW 635 csi',12000);
INSERT into i_model VALUES
('BMW 750','B3','BMW 750',12000);
INSERT into i_model VALUES
('FERR TR','A4','Ferrari Testarossa',10000);
INSERT into i_model VALUES
('JAG XJS','A2','Jaguar XJS V12 5.2l',15000);
INSERT into i_model VALUES
('JAG XJ6','A3','Jaguar XJ6 V12 Sovereign',15000);
INSERT into i_model VALUES
('LAMB COU','A4','Lamborghini Countach',6000);
INSERT into i_model VALUES
('MERC 560','B3','Mercedes 560 SEL',24000);
INSERT into i_model VALUES
('P911 TC','A3','Porsche 911 TC Cabriolet',12000);
INSERT into i_model VALUES
('P944 T','A3','Porsche 944 Turbo',12000);
INSERT into i_model VALUES
('RR SSPIR','B4','Rolls Royce Silver Spirit',6000);
/* --------------------------*/
INSERT into i_car VALUES
('H266MHU','ASTON V8','A1','01-APR-91',63000,2597,1000,'A');
INSERT into i_car VALUES
('G899VDU','BMW 635','B2','23-OCT-89',45500.3,18675,12667,'A');
INSERT into i_car VALUES
('G123RMR','BMW 750','B3','16-NOV-89',56829,42400,25366,'A');
INSERT into i_car VALUES
('E246WFC','RR SSPIR','B4','06-JAN-88',84500,52861,48253,'H');
INSERT into i_car VALUES
('G994PBR','FERR TR','A4','31-MAR-90',102450,10662,6004,'A');
INSERT into i_car VALUES
('H626RPG','JAG XJS','A2','26-SEP-90',32650.3,14533,12769,'A');
INSERT into i_car VALUES
('G551JBA','JAG XJ6','A3','02-SEP-89',45500,13788,11950,'A');
INSERT into i_car VALUES
('H203PBR','LAMB COU','A4','10-NOV-90',130250,7025,903,'H');
INSERT into i_car VALUES
('F651DEK','RR SSPIR','B4','10-JAN-89',92500,29610,24460,'A');
INSERT into i_car VALUES
('H311MHG','MERC 560','B3','09-NOV-90',60800,12450,11832,'A');
INSERT into i_car VALUES
('F111ENT','P911 TC','A3','03-SEP-88',75200,30286,24502,'A');
INSERT into i_car VALUES
('G202XRP','P944 T','A3','14-FEB-89',48350,28604,23666,'A');
INSERT into i_car VALUES
('J516BTA','P944 T','A3','02-SEP-91',50301,1502,NULL,'A');
INSERT into i_car VALUES
('H935CSA','FERR TR','A4','23-JUN-91',105600,3005,998,'H');
INSERT into i_car VALUES
('J706BEG','JAG XJ6','A3','16-SEP-91',39250.5,2110,1132,'H');
INSERT into i_car VALUES
('H875DES','BMW 750','B3','08-APR-91',58321,8354,973,'A');
INSERT into i_car VALUES
('H235BMA','LAMB COU','A4','10-NOV-90',130250,9611,1156,'H');
INSERT into i_car VALUES
('J185NED','P911 TC','A3','15-OCT-91',78350,875,NULL,'A');
INSERT into i_car VALUES
('J644TNR','MERC 560','B3','10-OCT-91',65800,1821,1201,'H');
INSERT into i_car VALUES
('J933RCE','FERR TR','A4','30-OCT-91',111200,957,NULL,'A');
/* --------------------------*/
/* --------------------------*/
INSERT into i_customer VALUES
(2338,'PETER BOGDANOVICH','52 LONG DEAN','WINDSOR','BERKS',
'SL14 6TQ','Peter Bogdanovich','A');
INSERT into i_customer VALUES
(8979,'J PARSONS LTD','PARSONS PLAZA','WEYBRIDGE','SURREY',
'KT12 8PP','Paul Bentley','A');
INSERT into i_customer VALUES
(2804,'FOCUST MARKETING LTD','555 SLOUGH ROAD','WOKINGHAM','BERKS',
'RG23 1JK','Jeanna Polly','C');
INSERT into i_customer VALUES
(2267,'WORTHINGTON and CO','16 LENANT ST','LONDON',NULL,
'SE1 5SF','David Worthington','A');
INSERT into i_customer VALUES
(1086,'ADT LTD','BUMPERS IND EST','CHIPPENHAM','WILTS',
'SN14 6LH','James Nugent','C');
INSERT into i_customer VALUES
(1553,'EYRIE BOOKS LTD','2 ST JOHNS AVE','LONDON',NULL,
'NW3 6TQ','Alisdair Aird','A');
INSERT into i_customer VALUES
(1452,'STEVEN BEARE','NETTLETON HOUSE','CASTLE COMBE','WILTSHIRE',
'SN13 7NJ','Steven Beare','C');
INSERT into i_customer VALUES
(2029,'SQL STARS LTD','10 PENN LEA ROAD','BATH','AVON',
'BA2 5MX','Stan Blethyn','A');
INSERT into i_customer VALUES
(667,'G G WHITTAKER and CO','WHEATLEY ROAD','DONCASTER','YORKSHIRE',
'DN5 8AA','Chris Whittaker','A');
INSERT into i_customer VALUES
(701,'MCGHIE ENTERPRISES','CAMPFIELD ROAD','BASINGSTOKE','HANTS',
'BK10 1BC','Bob McGhie','C');
INSERT into i_customer VALUES
(734,'CARL PETROV LTD','62 CADOGAN AVE','BRISTOL','AVON',
'BS1 6TQ','Jim Slattery','A');
INSERT into i_customer VALUES
(668,'THRINGS AND CO','MOUNTEBANK HOUSE','BASINGSTOKE','HANTS',
'RG22 1EL','Brian Jones','A');
INSERT into i_customer VALUES
(8981,'CARDINAL CARDS LTD','56 LOWER REGENT ST','LONDON',NULL,
'W1A 8TW','Linden Boyne','A');
/* --------------------------*/
INSERT into i_booking VALUES
(502,1553,'29-NOV-90','JANE B','20-DEC-90',15,
'G123RMR','BMW 750',500,3045,4002,'N');
INSERT into i_booking VALUES
(118,8979,'23-JAN-91','JANE B','23-JAN-91',4,
'H626RPG','JAG XJS',800,1692,1123.8,'N');
INSERT into i_booking VALUES
(265,2029,'09-APR-91','ERIC','04-MAY-91',16,
'E246WFC','RR SSPIR',35262,35818,1366.6,'N');
INSERT into i_booking VALUES
(503,2267,'12-APR-91','ERIC','26-APR-91',5,
'H203PBR','LAMB COU',3521,3862,593.75,'Y');
INSERT into i_booking VALUES
(504,1553,'12-APR-91','JANE H','12-APR-91',1,
'G551JBA','JAG XJ6',9527,9811,476.95,'N');
INSERT into i_booking VALUES
(586,667,'13-APR-91','JANE B','26-APR-91',21,
'F111ENT','P911 TC',25967,26818,2090.8,'Y');
INSERT into i_booking VALUES
(810,1452,'03-SEP-91','ERIC','10-SEP-91',3,
'J516BTA','P944 T',103,767,1139.45,'Y');
INSERT into i_booking VALUES
(811,701,'04-SEP-91','JANE H','05-SEP-91',8,
'G202XRP','P944 T',26197,26833,1279.8,'N');
INSERT into i_booking VALUES
(812,2338,'04-SEP-91','ERIC','15-OCT-91',15,
'J644TNR','MERC 560',119,1535,2477.85,'N');
INSERT into i_booking VALUES
(813,2804,'04-SEP-91','JANE H','08-SEP-91',1,
'H235BMA','LAMB COU',7899,8054,227.25,'Y');
INSERT into i_booking VALUES
(999,123,'13-JUN-91',NULL,'13-JUN-91',7,
'H286MHU','ASTON V8',NULL,NULL,NULL,NULL);
INSERT into i_booking VALUES
(2122,734,'21-SEP-91',NULL,'21-SEP-91',1,
'G994PBR','FERR TR',NULL,NULL,NULL,NULL);
INSERT into i_booking VALUES
(599,667,'21-DEC-91',NULL,'01-JAN-92',3,
'H311MHG','MERC 560',NULL,NULL,NULL,NULL);
INSERT into i_booking VALUES
(2123,667,'8-SEP-91',NULL,'19-SEP-91',5,
'H935CSA','FERR TR',23774,25865,501.8,'N');
INSERT into i_booking VALUES
(2124,667,'18-SEP-91',NULL,'19-SEP-91',5,
'G123RMR','BMW 750',61554,72334,1023,'N');
INSERT into i_booking VALUES
(2125,668,'02-OCT-91',NULL,'02-OCT-91',2,
'G551JBA','JAG XJ6',1098,882,100,'Y');
INSERT into i_booking VALUES
(2126,667,'02-OCT-91',NULL,'02-OCT-91',10,
'H266MHU','ASTON V8',5583,6254,873.6,'N');
INSERT into i_booking VALUES
(2127,668,'01-OCT-91',NULL,'01-OCT-91',1,
'J644TNR','MERC 560',8891,9024,106,'N');
INSERT into i_booking VALUES
(2128,701,'13-APR-91',NULL,'14-APR-91',10,
'H235BMA','LAMB COU',44512,45277,512,'N');
update i_booking
set date_reserved = add_months(date_reserved, 204),
date_rent_start = add_months(date_rent_start, 205)
where booking_no != 2129;
update i_booking
set date_rent_start = add_months(date_rent_start, 2)
where booking_no = 2122;
update i_booking
set date_rent_start = add_months(date_rent_start, -1)
where booking_no = 812;
update i_booking
set date_reserved = add_months(date_reserved, -2)
where booking_no = 599;
COMMIT;
No comments:
Post a Comment