Database Management Assignment - SQL Queries for Pine Valley Furniture Corporation

Part 1: In this part, you will continue with the queries for Pine Valley Furniture Corporation that you worked with in Assignment 3.

Work to Submit

7-60 (You can defined two subqueries and then use UNION to merge rows from the two tables)

7-63 (This requires a self-join of table with itself.  Alternatively, you can you a nested query)

7-67 (You can defined two subqueries and then use INTERSECTION only rows that are common to both)

7-71 (You may want to define a view to find the average order quantity for each product and then use the result of this view to find orders that have orders for each product that exceed the average)

7-73 (This query assumes that a product can come up more than once in a query.  Use a nested query where the inner query finds the sum of quantity for each customer id and product id. The outer query then keeps only one row for each product corresponding to maximum value of the sum of quantity across different customers)

Part 2: you will use normalization to come up with 3NF tables.

4-28

4-31

4-54

4-55 Hint for part a: The functional dependencies for this relation are given in the following diagram.

Textbook - Modern Database Management, 12th Edition by Jeffrey A. Hoffer, V. Ramesh and Heikki Topi

Attachment:- Assignment Files.rar