CIS2002 SEMESTER 1, 2014
Assignment 2 specification

Description Marks out of Mode Wtg(%) Due date
ASSIGNMENT 2 100.00 External 25.00 30 May 2014
ASSIGNMENT 2 100.00 On Campus 18.00* 30 May 2014

*On-campus students take practical tests and the weightings for assignments are different due to this.

IMPORTANT INFORMATION
You must submit the assignment electronically by the due date via the EASE link on the study desk. Instructions will be provided on the course study desk.
If you are unable to complete the whole assignment, submit what you have done rather than nothing.
There is an automatic extension until 6 June. The extra time is only if you need it. You should aim to submit at the due date. There is no need to apply for this extension. Once the model answers are released no further assignments can be accepted.
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.
This assignment may require a significant amount of work but you should treat it as an exercise in examination preparation as well as an assignment. 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 although the data modelling exercise is somewhat more substantial than what would be expected in an examination setting.

SECTION A (Data Modelling) (40 marks)

Law Associates is a large legal practice based in Sydney. You have been asked to design a data model for the practice based upon the following specification:

The practice employs nearly sixty lawyers who work in a wide variety of specialty areas. A speciality id and description is stored for each speciality. Each lawyer employed by the practice is classified as a partner, an associate, a junior or an intern. The practice stores the following information for all lawyers: Name, address, telephone, email, base salary and the one area of law in which that lawyer specialises. For partners, the practice also stores information about the percentage of the partnership held by the lawyer and the area of speciality that lawyer leads. Each area of speciality has one partner who acts as a leader (or resident expert and consultant) in that area. For associates, the practice also stores details about the percentage of cases the associate has won.

Juniors and interns undertake prescribed training courses and a record is kept. All training courses are registered and the name of the course, the duration (in days), the start date, the end date and details about the training organisation. Although a course is only offered by one training organisation, these organisations typically offer many courses. We store the name, address, telephone, email and Law Society accreditation number for all training organisations. When a lawyer completes a course, the grade received on that course is recorded.

We store details about the qualifications of all lawyers. Each lawyer may have many qualifications and a list of qualifications is stored, including the name of the qualification, its level (undergraduate or postgraduate) and the name of the university offering the qualification.

Each lawyer may take on many cases. A case is assigned to at least one lawyer but may be assigned to as many as four. If more than one lawyer is assigned to the case, one of the lawyers must be designated as a leader. Only seniors may be leaders of cases involving multiple lawyers. A case must have one or more clients but a client may initiate many cases over time. For case, we store a unique identification number, the estimated duration of the case, the client number/s of the clients involved, and the estimated start date. For client, we store an identification number, name and address. When any lawyer is assigned to a case, we store the date assigned and the date the lawyer leaves the case. A lawyer may become involved in a case on multiple occasions.

As cases can take years to resolve during the cases proceedings the layer in charge of the case may be unavailable as they be working on other cases. During this period the work associated with the case is assigned to the junior lawyer with mentoring from the layer originally assigned to the case. Upon the return of the principal lawyer to the case an evaluation is done of the assisting lawyer to record their performance for future evaluation. In the instance where a junior is assigned to a case where more than one lawyer is involved the other layers provide an evaluation of the junior lawyer as well. During the length of the case a number of junior layers could be assigned to assist with the case.

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. (15 Marks)

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. (15 Marks)

NOTE: We use PART MARKING for the relations and will mark four relations chosen at random.

c) A single SQL statement or multiple statements that create/s the table for the relation training courses for jurors and interns. All key and attribute constraints should be included and data types suitable to each attribute should be chosen.
(10 Marks)

SECTION B (Normalisation) (30 marks)

Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entitles for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.

PATIENT(patient id, name, address, age, gender, admitting doctor id, admitting doctor name, admitting doctor grade, date of admission, date of release, number of days in hospital, ((ward id, ward name, (( bed number, date arrived, date departed )) )), reason for admission)

Notes:
1. A new patient id is assigned for each admission to hospital
2. The admission to hospital must be authorised by only one admitting doctor.
3. During the hospital stay, the patient may be moved from ward to ward and from bed to bed within a ward.
4. Bed numbers are not unique within the hospital but are unique within the ward.

SECTION C (SQL) (20 marks)
MARKING CRITERIA

1. Four marks awarded for each correct SQL statement.
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.
4. There are 5 questions for the total of 20 marks.

For each question, five marks will be awarded for the SQL.

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 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

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 only the queries – output is not required.
1. Display the total number of customers that do not have a county recorded in for their address information.
2. Display each customer number and the total number of bookings that customer has made. Order the result by largest number of bookings at the top of the list.
3. Display full details for the oldest booking recoded in the database.
4. Display the registration and the number of times the car has been rented for the car/s that has been rented the greatest number of times.
5. Display the count of number cars whose cost is less than the average cost of all the cars that are available for booking (status = A)

SECTION D (10 marks)
PART MARKING: For this section, we will use part marking. This means we will supply model answers to all questions but we will only mark 5 questions. We will choose these randomly and each question chosen for marking will be worth two marks. The choice of the 5 random questions will be from the odd numbered questions. If you do not answer a question that is chosen for marking, you will receive no marks. You should therefore attempt all the questions. These questions are designed to hone your SQL skills before the exam.


Use the relations below to write SQL queries to solve the business problems specified.

Note: You do not have access to the tables or the data for this database. This is the same conditions you would be experiencing in an exam citation. These questions are designed to hone your SQL skills before the exam.

CLIENT(clientno#,name, client_referred_by#)
ORDER(orderno#, clientno#, order_date, empid#)
ORDER_LINE(orderno#, order line number#, item_number#, no_of_items, item_ cost, shipping_date)
ITEM(item_number#, item_type, cost)
EMPLOYEE(empid#, emp_type#, deptno, salary, firstname, lastname)

Notes:

1. Each client may be referred by another client. If so, the client number of the referring client is stored in referred_by.
2. The total cost for a particular order line = no_of_items * item_cost.
3. While we will choose 5 from the odd numbered questions you are encouraged to attempt all the questions.

1. Display all the rows and columns in the CLIENT table. Sort by client name in reverse alphabetical order.
2. Display the item number and total cost for each order line (total cost = no of items X item cost). Name the calculated column TOTAL COST.
3. Display all the client numbers in the ORDER table. Remove duplicates.
4. Display the order number and client number from the ORDER table. Output the result in the format. Client clientno ordered orderno
5. Display full details from the ORDER_LINE table where the item number is (first condition) between 1 and 200 (no or operators) OR the item number is greater than 1000 AND (second condition) the item cost is not in the list 1000, 2000, 3000 OR the order number is not equal to 1000.
6. Display the client name and order date for all orders using the traditional method.
7. Repeat query (6) but also display all clients who have never ordered anything.
8. Display the client name and order date for all orders using the natural join keywords.
9. Display the client name and order date for all orders using the JOIN . . . USING method.
10. Display the client number, order date and shipping date for all orders where the shipping date is between three and six months after the order date.
11. Display the client number and name and the client number and name of the person who referred that client.
12. Display the client name in upper case only and in lower case only.
13. Display the second to fifth characters in each client name.
14. Display the item_cost and then round it to the nearest hundred, ten, unit, tenth and hundredth.
15. Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.
16. Display the order number, order line number and the shipping date. If the shipping date is null, display the string not shipped yet .
17. Display the order number and average item cost for each order.
18. Display the clientno and total value for all orders placed by that client. Output the result in the following format: Client clientno has placed orders to the value of total value
19. Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.
20. Using a set operator, display the client number of all clients who have never placed an order.
21. Using a set operator, display the client number of all clients who have ever placed an order and whose name does not contain the string Sm.
22. Display the order number, number of lines in the order, total number of items and total value for all orders that have a total value greater than $100
23. Display the client name for all clients who have placed an order where any order line has more than 3 items. Do not use a table join anywhere in your query.
24. Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.
25. Display the client number and the value of the highest value order placed by that client. (High challenge question – well above exam standard).
26. Display the earliest shipping date in the format: DD/MON/YYYY
27. Display the order number and the number of months since the order was shipped for all orders that have been shipped in the last year (365 days). (Hint: Unshipped orders will have a null value).
28. Display the item_number, the total number of times that item has been sold and the average cost for that item. Restrict that output to items that have been included in more than 500 orders. (Hint: Assume an item will appear in only one order line of an order).
29. Display the surname for all employees who earn less the average salary of all those employees in the department with the lowest average salary.
30. Display the client number and the total number of orders the client has placed for all clients who have placed at least one order in 2011. (Hint: Use exists).

MARKING CRITERIA

SECTION A

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

SECTION B

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.

SECTION C

1.Four marks awarded for each correct SQL statement.
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.
4. There are 5 questions for the total of 20 marks.

SECTION D

1.Two marks awarded for each correct SQL statement of the 5 chosen to mark.
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.