Identify primary key and foreign key for each of the following tables. 2. Linkin
ID: 3594080 • Letter: I
Question
Identify primary key and foreign key for each of the following tables.
2. Linking Multiple Tables for Query
For each of the following query, identify
1) which tables need to be linked or if not linked what table is used
2) which field(s) are used to link the tables
1. Display the P# and PName of products bought on order 1001.
2. How many different products were bought on order 1001?
3. How many units in total are ordered in order 1001? How many are shipped?
4. What is the total order amount of O#1001? What is the total shipped amount?
5. How many total units are purchased by Customer 0023? How many are shipped?
6. How many units of product 20100093 are purchased by customer 0023?
7. What is the total shipped amount customer 0023 paid for product 20100093?
8. “Short ship” happens when the shipped quantity is less than the order quantity. Display the o#
of orders where there is any “short ship”.
9. (Continue from #8) Display the Date where the short ship happens.
10. (Continue from #8) Display the name of the customers who experienced short ship.
Explanation / Answer
1. OrderDetails and Products table are required
P# is primary key in Products and Foreign key in OrderDetails. O# is Primary key in OrderDetails and Foreign key as well.
2.OrderDetails table is enough
3. OrderDetails table is Enough
4. OrderDetails table is Enough
5. OrderDetails, Orders and Customers are required.
6. Customers and OrderDetails are enough
7. Customers and OrderDetails are enough
8. Orders and OrderDetails are required.
9. Orders and OrderDetails are required.
10. Customers, Orders and OrderDetails are required.