Data Modeling, ER Diagram, Normalization

Assignment 2 specification

Description	Marks out of	Wtg(%)	Due date
ASSIGNMENT 2	100.00	25.00	24 January 2011

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. Section C in particular was designed to provide you with SQL practice. It is only worth ten marks and we urge you to attempt the other sections first.
Model answers will be released at 18h00 on 29 January.  Submissions will not be accepted after model answers have been posted.
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 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. Many 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. A few of the more advanced SQL questions are beyond the exam standard.

 

SECTION A  (Data Modelling) (50 marks)           

FASHION SHOES is a ladies footwear business with over 60 retail outlets in NSW, VIC and QLD. You have been asked to design a data model for the company based upon the following specification:

The business wants to store information about retail stores, as follows: store number, location and a store manager. Each retail store has only one manager but can have several other store staff. Information about staff includes first name, last name, hire date, address, phone, salary and commission. Note that a store manager is also considered a member of staff.

A retail store can be one of two types: city store or regional store. A city retail store can order all of its shoe supplies directly from one of the 15 authorised suppliers, each of which may supply multiple city stores. We need to store supplier company name, address, contact name, contact number and email for each supplier. Date of order and status of shipment must be recorded in each shipment from a supplier to a store. A regional retail store does not deal with a supplier directly and we need to record whether the store opens on Sunday.

Each retail store, whether city or regional, stocks a number of lines of shoes. A line of shoe is essentially a product line, for example a Hush Puppy Super Soft. A line of shoe must be assigned to one of three categories: flat, sandal or high heel. For line of shoe, we store the SKU (unique), brand name (e.g. Hush Puppy), product name (e.g. Super Soft) and retail price.  A line of shoe may be stocked by some retail stores and not others. For each line, we need to record the dates on which the store began / stopped stocking the line. For sandal shoes, we also need to store sandal style and we store heel height for high heel shoes. Each line of shoe comes in different sizes and different colours.  Although we do not record details for each individual shoe in stock, we need to know the current stock level for each combination of line of shoe, colour and size. A particular line of shoe may come on a variety of combinations of colour and size.

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.

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

c)      Two CREATE TABLE SQL statement that constructs the tables for any two relations of your choice from the ER diagram above. You must demonstrate at least one primary and one foreign key constraint while creating the two tables. Suitable data types for each attribute should be chosen.

SECTION B  (Normalisation) (20 marks) 

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.

JOURNAL( isbn, title, year of publication, publisher code, publisher name, publisher address, ((library id, library type code, library type description, library name, ((collection id, collection description)) )), journal category id, journal category description, ((editor id. editor name, editor address)), number of copies in stock)

Further Information (not ALL rules are specified as you need to derive some):

1.   isbn uniquely identifies a journal.

2.   A journal has one publisher.

3.   A journal may have many editors who may in turn edit many journals.

4.   One or many complimentary copies of a journal may be sent to one or many libraries. Libraries may receive a copy of many journals and may house a copy of a journal in a specialist collection.

5.   A journal must belong to one category.

SECTION C (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 five questions. We will choose these randomly and each question chosen for marking will be worth three marks. If you do not answer a question that is chosen for marking, you will receive no marks. You should therefore attempt as many questions as possible.  These questions are designed to hone your SQL skills before the exam and working on them should be viewed as exam preparation. If you are unable to finish them all, submit as many as you can bearing in mind that this question is only worth 10 marks.

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)

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 ordered
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  .
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 has placed orders to the value of
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).

SECTION D (20 marks)

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server.  Output is not required.

1.      Display full details for all purchase invoices where the cost of freight is greater than the overall average freight cost.

2.      For all creditors, display the name, address1 and postcode in a comma delimited form using the concatenation operator. Label the output column CREDITOR DETAILS.  Only display creditors where there is no outstanding balance and the first three digits of the telephone number are 911. Note that the area code appears before the first three digits of the telephone number, e.g. (02).

3.      Display the creditor number and the number of orders placed with that creditor for the creditor/s who have received the greatest number of orders.

4.      For each payment, display the payment date, the cheque number, the amount, the name of the creditor and the employee number of the employee who processed the purchase order.

 

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.

SECTION C

Five questions chosen at random will be marked.

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

SECTION D

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

Name		Null?	Type
CREDITORNO		NOT NULL	NUMBER(5)
NAME		NOT NULL	VARCHAR2(40)
ADDRESS1			VARCHAR2(40)
ADDRESS2			VARCHAR2(40)
ADDRESS3			VARCHAR2(40)
ADDRESS4			VARCHAR2(40)
POSTCODE		NOT NULL	NUMBER(4)
PHONENO			VARCHAR2(14)
FAXNO			VARCHAR2(14)
BALANCE		NOT NULL	NUMBER(10,2)

Describe creditrs

Describe Pords

Name		Null?	Type
PURCHORDNO		NOT NULL	NUMBER(7)
CREDITORNO		NOT NULL	NUMBER(5)
ORDDATE		NOT NULL	DATE
EMPNO		NOT NULL	NUMBER(4)

Describe payments

Name		Null?	Type
CHEQUENO		NOT NULL	NUMBER(6)
PAYMENTDATE		NOT NULL	DATE
AMOUNT		NOT NULL	NUMBER(10,2)
CREDITORNO		NOT NULL	NUMBER(5)
PURCHINVNO		NOT NULL	NUMBER(8)