DB Script/Design

Question 1:

The diagram below (DB Setup ERD.pdf ) is an ERD for an order entry database for a company. A customer orders products through company employee.

Study the ERD and answer the following questions:

1. Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

2. For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

3. Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

4. Write SQL statement for the following scenario:

   1. Add a new customer John Smith with custono 1 and custzip 23456.

   2. Save changes permanently.

   3. Changer customer John Smith zip from 23456 to 20001.

   4. Cancel the change made in step c, restore data to its original status prior to step c.

   5. Delete customer John Smith.

   6. Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

Question 2:

Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD.

Run the DB Setup.sql script attached and then respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem.

a. For Seattle, WA customers compute the average amount of their orders and the number of orders placed. The result should include the customer number, customer last name, average order amount and the number of orders placed. Show results in the order of average order amount from high to low.

b. For Seattle, WA customers compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include customer number, customer last name and the number of unique products ordered. Show results in the order of customer’s last name.

c. For each employee with a commission less than 0.05, compute the number of orders taken and the average product total quantity per order. The results should include the employee number, employee last name, number of orders taken and the average product total quantity per order. Show results in the order of number of orders taken from low to high.

d. For each Connex product compute the number of unique customers who ordered the product in Jan 2007. The results should include the product number, product name and the number of unique customers. Show results in the order of product name.