In this post, I have given the solution to the DBMS lab assignment question 2. The solution is for practice and understand DBMS by running queries which we believe is the best way to learn.
Step 1: Make an ER Model of the Question 2. If you want to check the Question 2 click the link above.
Step 2: Convert the above ER Model into a Relational Model as given below.
Create all relations and enforced primary key, foreign key and check constraints according to relational model.
CREATE TABLE CUSTOMER (CUSTOMERNO VARCHAR2 (5), CNAME VARCHAR2 (25));
ALTER TABLE CUSTOMER ADD PRIMARY KEY (CUSTOMERNO)); ALTER TABLE CUSTOMER ADD CONSTRAINT CHK3 CHECK (CUSTOMERNO LIKE ‘C %’);
Create Table Customer – Customer-Order Processing Database.
CREATE TABLE CUST_ORDER (ORDERNO VARCHAR2 (5), ODATE DATE, CUSTOMERNO VARCHAR2 (5), ORD_AMT NUMBER (6) DEFAULT 0);
ALTER TABLE CUST_ORDER ADD PRIMARY KEY (ORDERNO); ALTER TABLE CUST_ORDER ADD FOREIGN KEY (CUSTOMERNO) REFERENCES CUSTOMER (CUSTOMERNO); ALTER TABLE CUST_ORDER ADD CONSTRAINT CK2 CHECK (ORDERNO LIKE ‘O %’);
Create Table Customer-Order
CREATE TABLE ITEM (ITEMNO VARCHAR2 (5), ITEM_NAME VARCHAR2 (30), UNIT_PRICE NUMBER (5));
ALTER TABLE ITEM ADD PRIMARY KEY (ITEMNO); ALTER TABLE ITEM ADD CONSTRAINT CK4 CHECK (ITEMNO LIKE ‘I %’);
CREATE TABLE ORDER_ITEM (ORDERNO VARCHAR2 (5), ITEMNO VARCHAR2 (5), QTY NUMBER (3));
ALTER TABLE ORDER_ITEM ADD PRIMARY KEY (ORDERNO, ITEMNO); ALTER TABLE ORDER_ITEM ADD FOREIGN KEY (ORDERNO) REFERENCES CUST_ORDER (ORDERNO); ALTER TABLE ORDER_ITEM ADD FOREIGN KEY (ITEMNO) REFERENCES ITEM(ITEMNO);
Now, we need to insert data to the relations created after following above steps. Insert data into the Customer table using insert command as follows.
INSERT INTO CUSTOMER VALUES (‘C0001’,’ANIL KUMAR’);
Insert Data to Customer Table – Customer Order-Processing Database
Inserting data into the customer order table is bit different, assume that customer placed an order on particular date then we cannot compute the total amount unless we know the total quantity of purchase make by the customer and for each purchase calculate the total amount as follows.
Total amount = Item1 unit price * qty + item2 unit price * quantity.
This means we set the total order amount to 0 by default.
INSERT INTO CUST_ORDER (‘O0001’, TO_DATE (’12-5-2013’,’DD-MM-YYYY’), ‘C0002’,0);
If we insert all data like this our cust_order table will look like following.
Now we insert data into the order_item table so that we know the total quantity and unit price of the item customer has purchased.
INSERT INTO ORDER_ITEM VALUES (‘O0001’, ‘I0002’, 4);
Now we update the customer order table with order amount as follows
The result should look like the following.
SELECT * FROM CUSTOMER C WHERE C.CUSTOMERNO IN (SELECT O.CUSTOMERNO FROM CUST_ORDER O GROUP BY O.CUSTOMERNO HAVING COUNT (O.ORDERNO) > 2);
SELECT * FROM ITEM I WHERE I.UNIT_PRICE < (SELECT AVG (UNIT_PRICE) FROM ITEM);
SELECT ORDERNO, SUM (OTY) FROM ORDER_ITEM GROUP BY ORDERNO;
Solution(E) – Total Quantity of Items
SELECT * FROM ITEM WHERE ITEMNO IN (SELECT ITEMNO FROM ORDER_ITEM GROUP BY ITEMNO HAVING COUNT (ITEMNO) >= (SELECT (COUNT (*)/4) FROM CUST_ORDER));
UPDATE CUST_ORDER SET ORD_AMT = (SELECT SUM (O.QTY * I.UNIT_PRICE) FROM ORDER_ITEM O, ITEM I WHERE CUST_ORDER.ORDERNO = O.ORDERNO AND O.ITEMNO = I.ITEMNO);
Create a view the customer order table after update and its called Customer_Details.
Run the following command to view and instance of Customer_Details View.