Review chapters 5 and 6 in the Elmasri & Navathe textbook, as well as the relevant slides and class exercises before you start working on this assignment. Respond to the questions below in the context of the following relational schema for an order processing database: CUSTOMER (custNo, cName, city, state)
ORDER (orderNo, ordDay, ordMth, ordYr, custNo, ordAmt)
ORDERITEM (orderNo, itemNo, qty)
ITEM (itemNo, unitPrice)
SHIPMENT (orderNo, wNo, shipDay, shipMth, shipYr)
WAREHOUSE (wNo, wName, city, state)
Notes: ordAmt refers to total dollar amount of an order; ordDay, ordMth, ordYr refers to the date the order is placed; shipDay, shipMth, shipYr refers to the date the order is shipped. An order is typically shipped within 7 days, and may be shipped from any warehouse. Primary keys are underlined and in bold print.
1. Specify the foreign keys for this schema and the relations they reference. State any assumptions you make.
2. What is union compatibility? Why do the UNION, INTERSECTION, and DIFFERENCE operations require that the relations on which they are applied be union compatible?
3. How are the OUTER JOIN operations different from the INNER JOIN operations?
4. Using the correct notation, specify the following queries in relational algebra:
a. List the names of all customers in New Jersey.
b. List all the orders, and their amounts, that are shipped from warehouses in the same city as the customer.
c. List the total value of orders at each warehouse in December 2017.
d. List the orders that have been / were delayed (not shipped within seven days).
e. List the total amount of orders from each warehouse, that were not shipped within seven days.
f. List the items that have been shipped from every warehouse in the database.
g. List the total number, total amount, and average amount of orders shipped to the customer named "ABC Retail", from each warehouse.
h. List the names of the customers that placed orders totaling more than $50,000.
i. List the items that have never been ordered.