Solution to DBMS Question – 2

In this article, we 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

Step 2: ER Model into a Relational Model

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

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 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.

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 (D)

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

SOLUTION (E)

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

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

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.