Database Management System Design Assignment 2
Consider the relations:
Customer(CID, Name, Ci...
Computers and Technology, 05.07.2021 19:40 baseball1525
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.
Answers: 2
Computers and Technology, 21.06.2019 21:30, khikhi1705
What elements related to the release and success of the nes console do you see in modern console releases? what elements are no longer relevant to modern console gaming?
Answers: 3
Computers and Technology, 22.06.2019 17:00, Janznznz1121
Your computer running windows 10 is doing some very strange things with the operating system. you are fairly certain it is not a hardware issue. you need to try to get further insight into what is going on within the operating system. which tool would be best suited for this?
Answers: 2
Computers and Technology, 23.06.2019 12:30, Prettygirlyaya
How is the brightness of oled of the diaplay is controled
Answers: 1
Mathematics, 11.03.2020 17:03
Social Studies, 11.03.2020 17:03
Mathematics, 11.03.2020 17:03