Solution to DBMS Question – 2

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.
View DBMS Assignment Questions

Step 1: Make an ER Model of the Question 2. If you want to check the Question 2 click the link above.

ER Diagram - Customer Order Processing Database
ER Diagram – Customer Order Processing Database

Step 2: Convert the above ER Model into a Relational Model as given below.

Relational Model - Customer Order Processing Database
Relational Model – Customer Order Processing Database

Queries

Solution (A)

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 Customer – Customer Order Processing Database

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 - Customer Order Processing Database
Create Table Customer Order – Customer Order Processing Database
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 Item - Customer Order Processing Database
Create Table Item – Customer Order Processing Database
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);

Create Table Order Item - Customer Order Processing Database
Create Table Order Item – Customer Order Processing Database

SOLUTION (B)
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 Into Customer Table
Insert Data Into Customer Table

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.

Insert Data Into Customer Order
Insert Data Into Customer Order

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);

Instance of Order Item Table
Instance of Order Item Table

Now we update the customer order table with order amount as follows
Update Command Customer Order Table
Update Command Customer Order Table

The result should look like the following.

Updated Cust_Order Table
Updated Cust_Order Table

SOLUTION (C)

SELECT * FROM CUSTOMER C WHERE C.CUSTOMERNO IN (SELECT O.CUSTOMERNO FROM CUST_ORDER O GROUP BY O.CUSTOMERNO HAVING COUNT (O.ORDERNO) > 2);

Solution (C)
Solution (C)

SOLUTION (D)

SELECT * FROM ITEM I WHERE I.UNIT_PRICE < (SELECT AVG (UNIT_PRICE) FROM ITEM);

Solution (D)
Solution (D)

SOLUTION (E)

SELECT ORDERNO, SUM (OTY) FROM ORDER_ITEM GROUP BY ORDERNO;

Solution (E): Total Quantity of Items
Solution (E): Total Quantity of Items

SOLUTION (F)

SELECT * FROM ITEM WHERE ITEMNO IN (SELECT ITEMNO FROM ORDER_ITEM GROUP BY ITEMNO HAVING COUNT (ITEMNO) >= (SELECT (COUNT (*)/4) FROM CUST_ORDER));

Solution (F): Item Count that is present in 25% of Orders
Solution (F): Item Count that is present in 25% of Orders

SOLUTION (G)

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);

Solution (G): Update Customer Order Table
Solution (G): Update Customer Order Table

Create a view the customer order table after update and its called Customer_Details.
View For Customer Order Table
View For Customer Order Table

Run the following command to view and instance of Customer_Details View.
An Instance of Customer_Detail View
An Instance of Customer_Detail View

Advertisements