Question

Given the following six relations for an order-processing database application in a company:

CUSTOMER (Cust#, Cname, City)

ORDER (Order#, Odate, Cust#, Ord_Amt)

ORDER_ITEM (Order#, Item#, Qty)

ITEM (Item#, Unit_price)

SHIPMENT (Order#, Warehouse#, Ship_date)

WAREHOUSE (Warehouse#, City)

Here, Ord_Amt refers to total dollar amount of an order; Odate is the date the order was placed; Ship_date is the date an order (or part of an order) is shipped from the warehouse. Assume that an order can be shipped from several warehouses.
Hint: Remember, the relations (tables) describing entities carry the primary keys that are referenced by the relations (tables) describing relationships between entities.

1. Specify all the foreign keys for this schem State each foreign key using the following format “foreign key is the attribute ---------- of relation --------- that references relation
   -------------“.
2. State two queries involving more than one table that can be posed on this database indicating the tables to visit to answer the queries each time.

Answer 1

a)

There are five foreign keys in this schema they are as follows

a. The attribute Cust# of relation ORDER that references relation CUSTOMER.
b. The attribute Order# of relation ORDER_ITEM that references relation ORDER.
c. The attribute Item# of relation ORDER_ITEM that references relation ITEM.
d. The attribute Order# of relation SHIPMENT that references relation ORDER.
e. The attribute Warehouse# of relation SHIPMENT that references relation WAREHOUSE.

b)State two queries involving more than one table that can be posed on this database indicating the tables to visit to answer the queries each time.

1)List the Cust#,Order# for a particular item# (ex: oil)
2)List the Order# and Ship_date for all orders shipped form a particular Warehouse# (ex:warehouse 3)
3)List the Cust#,Item# and it's Unit_price
4)List the Order# for orders that were shipped from all warehouses Of a particular city (ex:Vegas).