An ecommerce store uses a database with the following schema: Product(pid, pname, price) Customer(cid, cname, address) Order(oid, cid, date, total) cid foreign key to Customer
LineItem(lid, oid, pid, quantity) oid, pid foreign keys to Order, Product
Products and Customer contains a set of products and of customers respectively. Order represent orders placed by customers: each order has several lines, called LineItems, each line representing one product that the customer ordered. Each Orders record also stores the 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:
All keys (including primary 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.
a. Find all customers that placed an order in the past month where all line items are for products with price > 15.
b. The total cost of every order must be equal to the sum of each line item quantity times the associated product price. If the total is not accurate, then that order must be inspected by a staff. Write a query that finds all orders with inconsistencies.
c. The company’s revenue is the sum of all totals, from all orders. Write a query that computes how much the company’s revenue would increase, if the price of the ’puzzlebot’ product increased by 15%.