Database Management System Design Assignment 2 Consider the relations:
Customer(CID, Name, City, State),
Order(OID, CID, Date), and
Product(PID, ProductName, Price)
LineItem(LID, OID, PID, Number, TotalPrice),
where CID is a customer id and is a key for Customer, OID is an order id and is a key for Order, and LID is a line item id and is a key for LineItem. In addition the attribute CID of Order is a foreign key referring to the CID of Customer, that is, for each CID c of Order there is exactly one tuple of Customer whose CID attribute is c. The OID of LineItem is a foreign key referring to the OID of Order. There are several line items for the same order, a line item refers to a product and contains the quantity ordered for the product.
Express queries below in SQL.
Create a database using Oracle or MySQL with the tables in the assignment, populate it so that you get at least 1 tuple as answer for every query. You need to show proof of execution for each of the query (a screen shot for example). The proof of execution is worth 20% of the grade.
1. List the customers who ordered computers
2. List the other products bought by customers who bought computers
3. List the customers who did not place any order since January 1 2014
4. List the products bought by at least one customer of Newark
5. List the products bought by all the customers of Newark
6. List the products ordered only by the customers of Newark
7. List the products never ordered by customers of New Jersey
Views and SQL Queries
1. Create a view ProductMonth (PID, ProductName, Month, QuantitySold, NumberCustomer, Revenue) that gives for each the product and each month, the quantity sold, the number of distinct customers who bought the product and amount of money the sale corresponds to for the month. 1
2. Which product is the most popular (in term of number of distinct customers it)? Answer this query with and without the view.