DATABASE DESIGN AND ANALYSIS

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:

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

I_CarGroup

I_Model

I_Car

I_Booking

I_Customer

 

 

 

 

 

 

 

 

 

 

 

 

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.

  1. Display the total number of customers (note: customers need not have made a booking).
  2. Display each customer number and the total number of bookings that customer has made.
  3. Display full details for the most recent booking.
  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 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:

 

  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 X item_cost.

 

  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).
  1. Display the surname for all employees who earn less the average salary of all those employees in the department with the lowest average salary.
  1. 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.

 

 

 

 

 

 

 

 

 

Entry type

 

 

 

 

 

 

 

 

 

 

VISITOR

 

 

 

 

 

 

 

 

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.

 

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

 

  1. Display full details for the most recent booking.

Select  * from   I-Booking

 

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

 

 

  1. 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)

 

 

  1. 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) ;

 

 

  1. 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;

 

 

  1. Display all the client numbers in the ORDER table. Remove duplicates.

 

          select distinct client_number from <table_name>;

 

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

 

 

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

 

  1. Display the client name and order date for all orders using the traditional method.

 

 

 

  1. 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;

 

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

 

 

 

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

“The presented piece of writing is a good example how the academic paper should be written. However, the text can’t be used as a part of your own and submitted to your professor – it will be considered as plagiarism.

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

Please  Click on the  below links to Chat Now  or fill the Order Form !
order-now-new                       chat-new (1)