CIS 2002
SEMESTER 1, 2012
Assignment 2 specification
| Description |
Marks out of |
Wtg(%) |
Due date |
| ASSIGNMENT 2 |
100.00 |
25.00* |
1 June 2012 |
*On-campus students take practical tests and the weightings for assignments 1 and 2 are 9% and 19% respectively.
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.
I will grant automatic extensions until 8 June. There is no need to apply for this extension. Once the model answers are released (around 11 June), no further assignments can be accepted. If you submit after the due date (1 June), your assignment will probably not be marked before the exam.
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.
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.
6. USQ methodology used throughout.
Pax Europa Energy Corporation (PEEC) owns and runs twenty nuclear power stations throughout Europe. Currently, they have a manual incident reporting and movement monitoring (people) system. Due to security concerns, emergency management plans and the need to keep more detailed information about entries / exits and ‘incidents’ involving staff and visitors, PEEC wishes to develop a MOVEMENT AND INCIDENT SYSTEM. The first phase of this development will focus on entries / exits and incidents.
Below is the transcript from an interview the data architect conducted with the person responsible for the new system.
Details must be recorded for each power station, including the name of the power station, the location, date of construction and number of reactors. All movements in and out of a power station must be recorded. Each movement is called an ENTRY, and a particular entry is only for one power station but the power station will experience many entries. For each entry, PEEC wishes to record a unique entry number, the entry date/time, the date/time of departure, the duration of the entry, the reason for the entry and the number of people in the party. The reason for the entry is also called the entry type. Each entry must have one reason and we maintain a list of entry codes. There are currently three reasons for entry (entry types), namely visit (V), contract/maintenance (CM) and full-time employee (E).
For entry type E, we store the employee id only. For entry type CM, we store the contractor id and the reason for entry (one only). Employee id and contractor id relate to two entities (EMPLOYEE and CONTRACTOR) outside the scope of this system. For entry type V, we store the temporary identification card number and visit authorisation number of the visitor. Each visit must be authorised after a formal application, but a single authorisation could cover many visits (or one). For the authorisation, we store name, address, age, gender, email, reason for entry, name of person issuing authorisation, date of issue and period of validity.
During any entry, we record details of any incidents that might occur. A particular incident always relates to a specific entry but an entry may have many incidents associated with it. For incident, we store a unique incident id, the time of the incident and the location. For this, we use a list of locations which comprises a location id and a description. A particular incident must occur in a specific location but a location can be associated with many incidents over time.
An incident must be of a specific type, for example minor accident, but more complex incidents could comprise multiple incident types. We maintain a list of incident codes with descriptions. Each incident type could apply to many incidents over time. If an incident is complex and comprises more than one incident type, we rank the incident types in order of impact.
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) (25 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.
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:
- A new patient id is assigned for each admission to hospital
- The admission to hospital must be authorised by only one admitting doctor.
- During the hospital stay, the patient may be moved from ward to ward and from bed to bed within a ward.
- Bed numbers are not unique within the hospital but are unique within the ward.
SECTION C (SQL) (25 marks)
MARKING CRITERIA
1.Five 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.
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.
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.
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.
- Display the total number of customers (note: customers need not have made a booking).
- Display each customer number and the total number of bookings that customer has made.
- Display full details for the most recent booking.
- 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.
- Display the booking number, registration, date bought and cost for all cars whose cost is less than the average cost all available cars (status = A).
SECTION D (SQL) (10 marks)
SECTION D
1. PART MARKING: FIVE questions will be marked, with two marks awarded for each correct SQL statement.
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.
These questions are designed to hone your SQL skills before the exam. Note that this section is worth 10 marks only. You should therefore consider carefully how much time you wish to devote to the question as a whole and each of the questions in the secion in particular. The other sections of this assignment should receive more attention and, if time becomes an issue, hand in what you have completed.
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 five questions. We will choose these randomly and each question chosen for marking will be worth two marks. If you do not answer a question that is chosen for marking, you will receive no marks.
Use the relations below to write SQL queries to solve the business problems specified. You will not be provided with data or tables here – these are relation-based queries designed to mimic the examination environment.
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:
- Each client may be referred by another client. If so, the client number of the referring client is stored in referred_by.
- The total cost for a particular order line = no_of_items X item_cost.
- Display all the rows and columns in the CLIENT table. Sort by client name in reverse alphabetical order.
- 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.
- Display all the client numbers in the ORDER table. Remove duplicates.
- Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
- 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.
- Display the client name and order date for all orders using the traditional method.
- Repeat query (6) but also display all clients who have never ordered anything.
- Display the client name and order date for all orders using the natural join keywords.
- Display the client name and order date for all orders using the JOIN . . . USING method.
- 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.
- Display the client number and name and the client number and name of the person who referred that client.
- Display the client name in upper case only and in lower case only.
- Display the second to fifth characters in each client name.
- Display the item_cost and then round it to the nearest hundred, ten, unit, tenth and hundredth.
- Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.
- Display the order number, order line number and the shipping date. If the shipping date is null, display the string <not shipped yet>.
- Display the order number and average item cost for each order.
- 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>
- Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.
- Using a set operator, display the client number of all clients who have never placed an order.
- 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.
- 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
- 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.
- Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.
- Display the client number and the value of the highest value order placed by that client. (High challenge question – well above exam standard).
- Display the earliest shipping date in the format: DD/MON/YYYY
- 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).
- 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).
- Display the surname for all employees who earn less the average salary of all those employees in the department with the lowest average salary.
- 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).
SOLUTION
SECTION A (Data Modelling)
Entity Relationship diagram for Pax Europa Energy Corporation (PEEC) nuclear power station.
|
|
|
|
|
A list of relations
Attributes:
PEEC (Pax Europa Energy Corporation), movements, entry, entry type, customer, visitor, employee, contract and maintenance, namely visit, full-time entity.
Entities:
Temporary identification card number and visit authorisation number , name, address, age, gender, email, reason for entry, name of person issuing authorisation, date of issue and period of validity.
SECTION B (Normalisation)
Produce a set of relations in third normal form (3NF) from the following
un-normalized relation.
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)
select * from patient
where p_id, p_name, p_add, p_gender, and d_name, d_id or item_number
intersect
select * from d_grade
where patient_name
SECTION C (SQL)
Write SQL queries.
1. Display the total number of customers (note: customers need not have made a booking).
Select Cust_name from I-customer.
- Display each customer number and the total number of bookings that customer has made.
Select Cust_name, count(Cust_name) from I-Booking B inner join on I_customer C B.Cust_no =C. Cust_no
- Display full details for the most recent booking.
Select * from I-Booking
- 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.
select * from order_line
where c_number between 1 and 200
or c_number > 1000
intersect
select * from order_line
where c_number not in (1000,2000,3000)
or oc_number <> 1000
- Display the booking number, registration, date bought and cost for all cars whose cost is less than the average cost all available cars (status = A).
Select Booking_no,registration,date_bought,cost from I-car C
inner join on I-Booking B C. registration = B. registration
where C.cost<( select avg(cost) from I_car).
SECTION D (SQL)
Use the relations below to write SQL queries to solve the business problems specified.
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)
- Display all the rows and columns in the CLIENT table. Sort by client name in reverse alphabetical order.
select * from CLIENT order by name DESC
(or)
select * from emp order by reverse(ename) ;
- 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.
CREATE GLOBAL TEMPORARY TABLE today_cost
(total cost = no of items X item cost)
AS SELECT * FROM orders WHERE order_date = SYSDATE;
- Display all the client numbers in the ORDER table. Remove duplicates.
select distinct client_number from <table_name>;
- Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
select ‘Client ‘|| nvl (to_char(client_no),’unknown’) ||’
has placed order to the value of ‘|| nvl(sum(order_no),0)
order_summary from order group by client_no
- 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.
select * from order_line
where item_number between 1 and 200
or item_number > 1000
intersect
select * from order_line
where order_number not in (1000,2000,3000)
or order_number <> 1000
(or)
select * from order_line where
itemnum between 1 and 200 or itemnum>100
and
cost not in (1000,2000,3000) or ordernum<>1000
- Display the client name and order date for all orders using the traditional method.
- Repeat query (6) but also display all clients who have never ordered anything.
select c.custid,c.name,count(o.ordid) from customer c left
join orders o on c.custid=o.custid group by c.custid;
- Display the client name and order date for all orders using the natural join keywords.
Select ca.name, ord.orderdate From customer ca Natural Join
ord;
Natural join is a cartisn product.
- Display the client name and order date for all orders using the JOIN . . . USING method.
select c.name,o.ordid,o.orddate from customer c join oreder o
using(custid);
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.
select clientnumber,ordate,shipping date from order where
months_between(shipping date,ordate) between 3 and 6;
11.Display the client number and name and the client number and name of the person who referred that client.
Select ca.name, ora.ordid, Ora.Orderdate From customer ca,
ord Ora Where Ora.Custid = ca.custid;
12.Display the client name in upper case only and in lower case only.
Use function Lower and upper
select lower(ename), upper(ename) from emp;
13.Display the second to fifth characters in each client name.
Select Substr(ename, 2,4), ename From emp;
14.Display the item_cost and then round it to the nearest hundred, ten, unit, tenth and hundredth.
Select Item_Name,
Item_Cost,
Round(Item_Cost, -2) Hundred_Round,
Round(Item_Cost, -1) Ten_Round,
Round(Item_Cost, 0) Unit_Round,
Round(Item_Cost, 1) Tenth_Round,
Round(Item_Cost, 2) Hundredth_Round
From Items
15.Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.
select firstname||’ ‘||lastname as “Customer Name”,order# XXXXX BOOK_CUSTOMER natural join BOOK_ORDER order by customer#;
16.Display the order number, order line number and the shipping date. If the shipping date is null, display the string <not shipped yet>.
select order,orderline,decode(shipped date,null,'not
shipped',shipped date) from <TN>
17.Display the order number and average item cost for each order.
select order_no,avg(cost) from order group by order_no;
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>
select 'Client '||nvl(tochar(client_no,'unknown') ||' has
placed orders to the value of '|| nvl(sum(orders),0) as
order_summary
from order_table
group by client_no;
19.Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.
select client_name from client where client_name like 'j%'
or client_name like '%M%' or client_name like '___E%';
20.Using a set operator, display the client number of all clients who have never placed an order.
select firstname||’ ‘||lastname as “Customer Name”,order# XXXXX BOOK_CUSTOMER natural join BOOK_ORDER order by customer#;
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.
select order,orderline,decode(shipped date,null,'not
shipped',shipped date) from <TN>
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
select client_name,count(item) from clients where item=
(select Item from items )
having count(item)>3
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.
select client_name,count(item) from clients where item=
(select Item from items )
having count(item)>3
24.Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.
select order,orderline,decode(shipped date,null,'not
shipped',shipped date) from <TN>
25.Display the client number and the value of the highest value order placed by that client. (High challenge question – well above exam standard).
select employee_id,last_name,department_id from employees
where (salary,department_id );
26.Display the earliest shipping date in the format: DD/MON/YYYY
AS SELECT * FROM orders WHERE order_date = SYSDATE;
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).
select employee_id,last_name,department_id from employees
where (salary,department_id ) IN (select max(salary),department_id
from employees group by department_id);
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).
select employee_id,last_name from employees where salary>
(select avg(salary) from employees where department_id=10)
and department_id=20;
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.
select employee_id,last_name,department_id from employees
where (salary,department_id ) IN (select max(salary),department_id
from employees group by department_id);
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).
AS SELECT * FROM orders WHERE client_date = SYSDATE 2011;
LH49
But you can order it from our service and receive complete high-quality custom paper. Our service offers DATABASE essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”
