I need 8 more queries on this database.Below there is question and their respective tables and sample queries.I need 8 more queries other than present below Create a database named Amazon using the attributes.Create individual tables based on below attributes Customers Products Product_types Orders Customer payment methods Tacking Order status Shipments Invoice create different tables and mention their primary keys and foreign keys Describe atleast 6 business rules create Entity Relationship Diagram.The ERD should be in Crow's Foot Model.It should include all the appropriate entities,connectivity,cardinalities and relationship participation. 4 sample queries in plain English and their SQL SELECT statements check_circle Expert Answer thumb_up thumb_down Step 1 BUSINESS RULES One customer can have one or more orders. One order belongs to only one customer. One customer has only one payment method. One payment methods can belong to one or more customers. One order contains only one product. One product be ordered one or more times. One order generates only one invoice. One invoice belongs to only one order. One order can have only one shipment. One shipment belongs to only one order. One product has only one product type. One or more products can have same product type. Step 2 The Entity Relationship Diagram in Crow's Foot Model is as shown. ERD Step 3 TABLES CUSTOMERS ( custID, fullName, Address, City, State, Email, Phone, payID ) PRIMARY KEY - custID FOREIGN KEY - payID CUST_PAY_METHOD ( payID, pay_method, cardNum, expiryDate ) PRIMARY KEY - payID PRODUCTS ( prodID, name, description, typeID, price, qtyOnHand ) PRIMARY KEY - prodID FOREIGN KEY - typeID PRODUCT_TYPE ( typeID, description ) PRIMARY KEY - typeID ORDERS ( orderID, custID, orderDate, orderQty ) PRIMARY KEY - orderID FOREIGN KEY - custID ORDER_STATUS ( statusID, status ) PRIMARY KEY - statusID INVOICE ( invID, orderID, subTotal, discount, total ) PRIMARY KEY - invID FOREIGN KEY - orderID SHIPMENT ( shipID, orderID, shipDate, shipCharges, estRecdDate ) PRIMARY KEY - shipID FOREIGN KEY - orderID SHIPMENT_STATUS ( statusID, shipID, shipStatus ) PRIMARY KEY - statusID FOREIGN KEY - shipID Step 4 QUERIES 1. List all the orders which have been shipped. SELECT orderID FROM ORDERS JOIN ORDER_STATUS ON ORDERS.orderID = ORDER_STATUS.orderID WHERE status = "Shipped" ; 2. List all the orders that have been delivered. SELECT orderID FROM ORDERS JOIN SHIPMENT ON ORDERS.orderID = SHIPMENT.orderID JOIN SHIPMENT_STATUS ON SHIPMENT.shipID = SHIPMENT_STATUS.shipID WHERE shipStatus = "Delivered" ; 3. List all the payment methods used by the customers. SELECT pay_method FROM CUST_PAY_METHOD ; 4. List all the products and their types sorted by types. SELECT name, type FROM PRODUCTS JOIN PRODUCT_TYPE ON PRODUCTS.typeID = PRODUCT_TYPE.typeID GROUP BY type ORDER BY type ;
I need 8 more queries on this
Create a database named Amazon using the attributes.Create individual tables based on below attributes
- Customers
- Products
- Product_types
- Orders
- Customer payment methods
- Tacking
- Order status
- Shipments
- Invoice
create different tables and mention their primary keys and foreign keys
Describe atleast 6 business rules
create Entity Relationship Diagram.The ERD should be in Crow's Foot Model.It should include all the appropriate entities,connectivity,cardinalities and relationship participation.
4 sample queries in plain English and their SQL SELECT statements
Expert Answer
BUSINESS RULES
One customer can have one or more orders. One order belongs to only one customer.
One customer has only one payment method. One payment methods can belong to one or more customers.
One order contains only one product. One product be ordered one or more times.
One order generates only one invoice. One invoice belongs to only one order.
One order can have only one shipment. One shipment belongs to only one order.
One product has only one product type. One or more products can have same product type.
The Entity Relationship Diagram in Crow's Foot Model is as shown.
ERD
TABLES
CUSTOMERS ( custID, fullName, Address, City, State, Email, Phone, payID )
PRIMARY KEY - custID
FOREIGN KEY - payID
CUST_PAY_METHOD ( payID, pay_method, cardNum, expiryDate )
PRIMARY KEY - payID
PRODUCTS ( prodID, name, description, typeID, price, qtyOnHand )
PRIMARY KEY - prodID
FOREIGN KEY - typeID
PRODUCT_TYPE ( typeID, description )
PRIMARY KEY - typeID
ORDERS ( orderID, custID, orderDate, orderQty )
PRIMARY KEY - orderID
FOREIGN KEY - custID
ORDER_STATUS ( statusID, status )
PRIMARY KEY - statusID
INVOICE ( invID, orderID, subTotal, discount, total )
PRIMARY KEY - invID
FOREIGN KEY - orderID
SHIPMENT ( shipID, orderID, shipDate, shipCharges, estRecdDate )
PRIMARY KEY - shipID
FOREIGN KEY - orderID
SHIPMENT_STATUS ( statusID, shipID, shipStatus )
PRIMARY KEY - statusID
FOREIGN KEY - shipID
QUERIES
1. List all the orders which have been shipped.
SELECT orderID
FROM ORDERS JOIN ORDER_STATUS ON ORDERS.orderID = ORDER_STATUS.orderID
WHERE status = "Shipped" ;
2. List all the orders that have been delivered.
SELECT orderID
FROM ORDERS JOIN SHIPMENT ON ORDERS.orderID = SHIPMENT.orderID
JOIN SHIPMENT_STATUS ON SHIPMENT.shipID = SHIPMENT_STATUS.shipID
WHERE shipStatus = "Delivered" ;
3. List all the payment methods used by the customers.
SELECT pay_method
FROM CUST_PAY_METHOD ;
4. List all the products and their types sorted by types.
SELECT name, type
FROM PRODUCTS JOIN PRODUCT_TYPE ON PRODUCTS.typeID = PRODUCT_TYPE.typeID
GROUP BY type
ORDER BY type ;
Step by step
Solved in 2 steps
