Question 2: An online store uses a database with the following schema: Product (pid, pname, price) Customer (cid, cname,

[answerhappygod]

Question 2 An Online Store Uses A Database With The Following Schema Product Pid Pname Price Customer Cid Cname 1 (71.37 KiB) Viewed 32 times

Question 2: An online store uses a database with the following schema: Product (pid, pname, price) Customer (cid, cname, address) Orders (oid, cid, date, total) cid foreign key to Customer Lineltem (lid, oid, pid, quantity) oid, pid foreign keys to Orders, Product Products and Customer contains a set of products and of customers respectively. Orders represent orders placed by customers: each order has several lines, called Lineliems, each line total value of the order, which is the sum of price+quantity for all line items in that order. The following constraints must be enforced by the database system: . All keys and foreign keys. All attributes are not null. • price > 0 In addition, the business logic is such that the total in each order equals the sum of price* quantity for all line items in that order. This constraint is maintained by the application logic. (a) (10 points) Write the CREATE TABLE statements. Include all constraints that must be enforced by the database system. All identifiers (pid, cid, oid, lid) are integers, quantity is an integer; price and total are floats; all other fields are text. (b) (10 points) In every order, the total must be equal to the sum of the line item quantity times the product price. If the total is incorrect, then that order must be reviewed by a human and the error must be corrected. Write a query that finds all inconsistent orders, your query should return a set of oid.