A Database application program for an order processing database application in a company

Consider the following relations for an order processing database application in a company:

CUSTOMER (cust #: int , cname: string, city: string)

ORDER (order #: int, odate: date, cust #: int, ord-Amt: int)

ORDER – ITEM (order #: int, item #: int, qty: int)

ITEM (item # : int, unit price: int)

SHIPMENT (order #: int, warehouse#: int, ship-date: date)

WAREHOUSE (warehouse #: int, city: string)

(i) Create the above tables by properly specifying the primary keys and the foreign keys.

(ii) Enter at least five tuples for each relation.

(iii) Produce a listing: CUSTNAME, #oforders, AVG_ORDER_AMT, where the middle column is the total numbers of orders by the customer and the last column is the average order amount for that customer.

(iv) List the order# for orders that were shipped from all the warehouses that the company has in a specific city.

(v) Demonstrate the deletion of an item from the ITEM table and demonstrate a method of handling the rows in the ORDER_ITEM table that contain this particular item.

CREATE TABLE CUSTOMER
(
 Cust_id INTEGER NOT NULL,
 Cust_name VARCHAR2(10),
 City VARCHAR2(10),
 PRIMARY KEY(Cust_id)
);

INSERT INTO CUSTOMER VALUES(&Cust_id,'&Cust_name','&City');

SELECT * FROM CUSTOMER;

CUST_ID CUST_NAME CITY
------- ---------- ----------
 101 SAHIL BELGAUM
 102 NEHA PUNE
 103 PREM BELGAUM
 104 MAYUR HUBLI
 105 AKSHAY KOLKATTA

CREATE TABLE ORDER_1
(
 Order_id INTEGER NOT NULL,
 Order_date DATE,
 Cust_id INTEGER,
 Order_amt INTEGER,
 PRIMARY KEY(Order_id),
 FOREIGN KEY(Cust_id) REFERENCES CUSTOMER(Cust_id)
);

INSERT INTO ORDER_1 values(&Order_id,'&Order_date',&Cust_id,&Order_amt);

SELECT * FROM ORDER_1;

 ORDER_ID ORDER_DAT CUST_ID ORDER_AMT
--------- --------- ---------- ----------
 201 01-OCT-04 101 50000
 202 15-JAN-05 102 30000
 203 20-JUN-06 101 60000
 204 01-FEB-04 104 15000
 205 19-MAR-05 104 40000

CREATE TABLE ITEM
(
 Item_no INTEGER NOT NULL,
 Unit_price INTEGER,
 PRIMARY KEY(Item_no)
);

INSERT INTO ITEM values(&Item_no,&Unit_price);

SELECT * FROM ITEM;

 ITEM_NO UNIT_PRICE
-------- ----------
 1 5000
 2 3000
 3 2500
 4 8000
 5 7000

CREATE TABLE ORDER_ITEM
(
 Order_id INTEGER NOT NULL,
 Item_no INTEGER,
 Quantity INTEGER,
 PRIMARY KEY(Order_id,Item_no),
 FOREIGN KEY(Item_no) REFERENCES ITEM(Item_no),
 FOREIGN KEY(order_id) REFERENCES order_1(order_id)
);

INSERT INTO ORDER_ITEM values(&Order_id,&Item_no,&Quantity);

SELECT * FROM ORDER_ITEM;

 ORDER_ID ITEM_NO QUANTITY
---------- ---------- ----------
 201 1 20
 202 2 10
 203 3 40
 204 4 3
 205 5 5

CREATE TABLE WAREHOUSE
(
 Warehouse1 INTEGER NOT NULL,
 City VARCHAR2(10),
 PRIMARY KEY(Warehouse1)
);

INSERT INTO WAREHOUSE values(&Warehouse1,'&City');

SELECT * FROM WAREHOUSE;

WAREHOUSE1 CITY
---------- ----------
 100 BANGALORE
 200 KOLKATTA
 300 PUNE
 400 BELGAUM
 500 MYSORE



for more >>

A Database application program for an order processing database application in a company