Draw a relational schema A company wishes to track orders for its products by cu
ID: 3669148 • Letter: D
Question
Draw a relational schema
A company wishes to track orders for its products by customers. The company assigns each customer a unique Customer ID. In addition to this identification number, customer information that is stored includes the customer’s Name (First and Last), Address (Street, City, State, and Zip), and Phone. Information stored about the company’s products includes Product ID (a unique identifier), Description, MSRP (Manufacturer Suggested Retail Price), and QOH (Quantity On Hand). Each order that is placed is assigned a unique identifier named Order ID. The Order Date is also recorded for each order placed. The company also wishes to track, for each order placed by a customer, what products were purchased, in what Quantity, and at what Actual Price (which can vary from the MSRP). The Total Price for an order can be calculated from the quantities and actual prices of the products on the order. Orders typically include more than one product. Some products have not yet been ordered, while others have been ordered numerous times. All orders are placed by a single customer. Some customers are merely prospects that have not yet ordered, while others are regular customers that have placed many orders.
Explanation / Answer
Customer Schema :
cid
CFname
cLname
Street
city
zip
Ph_no
cid is the primary key
Product Schema :
pid
Description
MSRP
QOH
pid is the primary key
pid, MSRP, QOH are compound keys
Order Schema :
oid
odate
cid
pid
Quantity
MSRP
oid is the primary key
cid, pid, quantity and MSRP are foreign keys
cid
CFname
cLname
Street
city
zip
Ph_no
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.