www.bookspar.com | Website for students | VTU NOTES
INDEX
SERIAL NO. / CONTENTS / PAGENO. / MARKS
1. / INSURANCE DATABASE / 2
2. / ORDER PROCESSING DATABASE / 12
3. / STUDENT DATABASE / 23
4. / BOOK DATABASE / 34
5. / BANKING DATABASE / 44
PROBLEM 1
PROBLEM STATEMENT:
Consider the Insurance Database given below. The primary keys are underlined and the datatypes are specified.
PERSON (driver-id: string, name: string, address: string)
CAR (regno: string, model: string, year: int)
ACCIDENT (report-number: int, accd-date: date, location: string)
OWNS (driver-id: string, regno: string)
PARTICIPATED (driver-id: string, regno: string, report-number: int, damage amount: int)
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) Demonstrate how you
a. Update the damage amount for the car with a specific regno in the accident with report number 12 to 25000.
b. Add a new accident to the database.
iv) Find the total number of people who owned cars that were involved in accidents in 2008.
v) Find the number of accidents in which cars belonging to a specific model were involved.
vi) Generate suitable reports.
vii) Create a suitable front end for querying and displaying the results.
SCHEMA DESCRIPTION:
PERSON CAR
PARTICIPATED
ACCIDENT
OWNS
ER DIAGRAM:
TABLE CREATION
SQL> create table person
(
driver_id varchar(10) primary key,
name varchar(10),
address varchar(10)
);
Table created.
TABLE DESCRIPTION
SQL> describe person;
Name Null? Type
------
DRIVER_ID NOT NULL VARCHAR(10)
NAME VARCHAR(10)
ADDRESS VARCHAR(10)
INSERTING TUPLES
SQL> insert into person values('&driver_id','&name','&address');
Enter value for driver_id: 1
Enter value for name: aaa
Enter value for address: jbnagar
old 1: into person values ('&driver_id','&name','&address')
new 1: into person values ('1','aaa','jbnagar')
1 row created.
SQL> insert into person values(‘2’,’bbb’,’jaynagar’);
SQL> insert into person values(‘3’,’ccc’,’basvangudi’);
SQL> insert into person values(‘4’,’ddd’,’jpnagar’);
SQL> insert into person values(‘5’,’eee’,’coxtown’);
SQL> select * from person;
DRIVER_ID NAME ADDRESS
------
1 aaa jbnagar
2 bbb jaynagar
3 ccc basvangudi
4 ddd jpnagar
5 eee coxtown
TABLE CREATION
SQL> create table car
(
regno varchar(10) primary key,
model varchar(10),
year number(4)
);
Table created.
TABLE DESCRIPTION
SQL> describe car;
Name Null? Type
------
REGNO NOT NULL VARCHAR2(10)
MODEL VARCHAR2(10)
YEAR NUMBER(4)
INSERTING TUPLES
SQL> insert into car values ('®no','&model','&year');
Enter value for regno: 1000
Enter value for model: zen
Enter value for year: 2002
old 1: insert into car values ('®no','&model','&year')
new 1: insert into car values ('1000','zen','2002')
1 row created.
SQL> insert into car values(‘2000’,’innova’,’2005’);
SQL> insert into car values(‘3000’,’omni’,’1999’);
SQL> insert into car values(‘4000’,’zen’,’2004’);
SQL> insert into car values(‘5000’,’scorpio’,’2008’);
SQL> select * from car;
REGNO MODEL YEAR
------
1000 zen 2002
2000 innova 2005
3000 omni 1999
4000 zen 2004
5000 scorpio 2008
TABLE CREATION
SQL> create table accident
(
repno number(4) primary key,
ddate date,
location varchar(10)
);
Table created.
TABLE DESCRIPTION
SQL> describe accident;
Name Null? Type
------
REPNO NOT NULL NUMBER(4)
DDATE DATE
LOCATION VARCHAR2(10)
INSERTING TUPLES
SQL> insert into accident values('&repno','&ddate','&location');
Enter value for repno: 10
Enter value for ddate: 06-jul-2008
Enter value for location: rjngr
old 1: ('&repno','&ddate','&location')
new 1: ('10','06-jul-2008','rjngr')
1 row created.
SQL> insert into accident values(‘20’,’26-mar-2006’,’aaa’);
SQL> insert into accident values(‘30’,’24-apr-2008’,’bbb’);
SQL> insert into accident values(‘40’,’7-jan-2008’,’ccc’);
SQL> insert into accident values(‘50’,’17-feb-2009’,’ddd’);
SQL> select * from accident;
REPNO DDATE LOCATION
------
10 06-JUL-08 rjngr
20 26-MAR-06 aaa
30 24-APR-08 bbb
40 07-JAN-08 ccc
50 17-FEB-09 ddd
TABLE CREATION
SQL> create table owns
(
driver_id varchar(10) references person(driver_id),
regno varchar(10) references car(regno)
);
Table created.
TABLE DESCRIPTION
SQL> describe owns;
Name Null? Type
------
DRIVER_ID VARCHAR2(10)
REGNO VARCHAR2(10)
INSERTING TUPLES
SQL> insert into owns values('&driver_id','®no');
Enter value for driver_id: 1
Enter value for regno: 1000
old 1: insert into owns values ('&driver_id','®no')
new 1: insert into owns values ('1','1000')
1 row created.
SQL> insert into owns values(‘2’,’2000’);
SQL> insert into owns values(‘3’,’3000’);
SQL> insert into owns values(‘4’,’4000’);
SQL> insert into owns values(‘5’,’5000’);
SQL> select * from owns;
DRIVER_ID REGNO
------
1 1000
2 2000
3 3000
4 4000
5 5000
TABLE CREATION
SQL> create table participated
(
driver_id varchar(10) references person(driver_id),
regno varchar(10) references car(regno),
repno number(4) references accident(repno),
damage number(6)
);
Table created.
TABLE DESCRIPTION
SQL> describe participated;
Name Null? Type
------
DRIVER_ID VARCHAR2(10)
REGNO VARCHAR2(10)
REPNO NUMBER(4)
DAMAGE NUMBER(6)
INSERTING TUPLES
SQL> insert into participated values('&driver_id','®no','&repno','&damage');
Enter value for driver_id: 1
Enter value for regno: 1000
Enter value for repno: 10
Enter value for damage: 2000
old 1: insert into participated values ('&driver_id','®no','&repno','&damage')
new 1: insert into participated values ('1','1000','10','2000')
1 row created.
SQL> insert into participated values(‘2’,’2000’,’20’,’3000’);
SQL> insert into participated values(‘3’,’3000’,’30’,’4000’);
SQL> insert into participated values(‘4’,’4000’,’40’,’5000’);
SQL> insert into participated values(‘5’,’5000’,’50’,’6000’);
SQL> select * from participated;
DRIVER_ID REGNO REPNO DAMAGE
------
1 1000 10 2000
2 2000 20 3000
3 3000 30 4000
4 4000 40 5000
5 5000 50 6000
QUERIES
1. Demonstrate how you
a. Update the damage amount for the car with a specific Regno in the accident with report number 12 to 25000.
SQL> update participated set damage=7777 where regno=3000 and repno between 12
and 25000;
1 row updated.
RESULT
SQL> select * from participated;
DRIVER_ID REGNO REPNO DAMAGE
------
1 1000 10 2000
2 2000 20 3000
3 3000 30 7777
4 4000 40 5000
5 5000 50 6000
EXPLANATION
The SQL command Update is used to change the value of attribute damage in table participated. The where clause provides the condition which selects the tuples in where regno= 3000 and repno lies between 12 and 25000.
b. Add a new accident to the database.
SQL> insert into accident values(‘77’,'12-oct-2009','sevangr');
1 row created.
RESULT
SQL> select * from accident;
REPNO DDATE LOCATION
------
10 06-JUL-08 rjngr
20 26-MAR-06 aaa
30 24-APR-08 bbb
40 07-JAN-08 ccc
50 17-FEB-09 ddd
77 12-OCT-09 sevangr
EXPLANATION
The insert command is used here to insert the tuples into the accident tables. The attributes are specified in the braces.
2. Find the total number of people who owned cars that were involved in accidents in 2008
SQL> select count(p.driver_id)
from person p,participated pa,accident a
where p.driver_id=pa.driver_id and pa.repno=a.repno and ddate like ‘%08’;
RESULT
COUNT(P.DRIVER_ID)
------
3
EXPLANATION
The condition pa.repno and a.repno selects appropriate tuples with date ending in ’08’.The count function counts the number of such tuples.
3. Find the number of accidents in which cars belonging to a specific model were involved
SQL> select count(a.repno)
from accident a,participated p,car c
where a.repno=p.repno and p.regno=c.regno and c.model='zen';
RESULT
COUNT(A.REPNO)
------
2
EXPLANATION
The three relations are joined at their attributes correspondingly and the tuples with model name zen are selected and count function counts the number of such tuples.
PROBLEM-2
PROGRAM STATEMENT:
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, NO_OF_ORDERS, AVG_ORDER_AMT, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer.
iv) List the order# for the orders that were shipped from all the warehouses that the company has in a specific city.
v) Demonstrate how you delete an item from the ITEM table and make that field null in the ORDER-ITEM table.
vi) Generate suitable reports.
vii) Create a suitable front end for querying and displaying the results.
SCHEMA DESCRIPTION:
CUSTOMER
ORDER WAREHOUSE
ORDER_ITEM
ITEM
SHIPMENT
ER DIAGRAM:
TABLE CREATION
SQL> create table customer
(
custno number(4) primary key,
cname varchar(10) not null,
city varchar(10) not null
);
Table created.
TABLE DESCRIPTION
SQL> describe customer;
Name Null? Type
------
CUSTNO NOT NULL NUMBER(4)
CNAME NOT NULL VARCHAR2(10)
CITY NOT NULL VARCHAR2(10)
INSERTING TUPLES
SQL> insert into customer values('&custno','&cname','&city');
Enter value for custno: 1
Enter value for cname: ddd
Enter value for city: chennai
old 1:insert into customer values('&custno','&cname','&city')
new 1:insert into customer values('1','ddd','chennai')
1 row created.
SQL> insert into customer values('2','aaa','bangalore');
SQL> insert into customer values(‘3’,’bbb’,’delhi’);
SQL> insert into customer values(‘4’,’eee’,’kolkata’);
SQL> insert into customer values(‘5’,’fff’,’mumbai’);
SQL> select * from customer;
CUSTNO CNAME CITY
------
1 ddd chennai
2 aaa banglore
3 bbb delhi
4 eee kolkata
5 fff mumbai
TABLE CREATION
SQL> create table orders
(
ordno number(4) primary key,
odate date not null,
custno number(4) not null references customer(custno) on delete cascade,
amount number(4) not null
);
Table created.
TABLE DESCRIPTION
SQL> describe orders;
Name Null? Type
------
ORDNO NOT NULL NUMBER(4)
ODATE NOT NULL DATE
CUSTNO NOT NULL NUMBER(4)
AMOUNT NOT NULL NUMBER(4)
INSERTING TUPLES
SQL> insert into orders values ('&ordno','&odate','&custno','&amount');
Enter value for ordno: 11
Enter value for odate: 11-sep-2009
Enter value for custno: 1
Enter value for amount: 100
old 1: insert into orders values ('&ordno','&odate','&custno','&amount')
new 1: insert into orders values ('11','11-sep-2009','1','100')
SQL>insert into orders values (‘12’,’12-JAN-2004’,’2’,’200’);
SQL>insert into orders values (‘13’,’20-FEB-2005’,’1’,’300’);
SQL>insert into orders values (‘14’,’15-AUG-99’,’4’,’400’);
SQL>insert into orders values (‘15’,’12-DEC-2000’,’2’,’500’);
SQL> select * from orders;
ORDNO ODATE CUSTNO AMOUNT
------
11 11-SEP-09 1 100
12 12-JAN-04 2 200
13 20-FEB-05 1 300
14 15-AUG-99 4 400
15 12-DEC-00 2 500
TABLE CREATION
SQL> create table items
(
itemno number(4) primary key ,
price number(4) not null
);
Table created.
TABLE DESCRIPTION
SQL> describe items;
Name Null? Type
------
ITEMNO NOT NULL NUMBER(4)
PRICE NOT NULL NUMBER(4)
INSERTING TUPLES
SQL> insert into items values('&itemno','&price');
Enter value for itemno: 1
Enter value for price: 2000
old 1: ('&itemno','&price')
new 1: ('1','2000')
1 row created.
SQL> insert into items values(‘2’,’2000’);
SQL> insert into items values(‘3’,’1320’);
SQL> insert into items values(‘4’,’2300’);
SQL> insert into items values(‘5’,’1200’);
SQL> select * from items;
ITEMNO PRICE
------
1 2000
2 2000
3 1320
4 2300
5 1200
TABLE CREATION
SQL> create table order_item
(
ordno number(4) references orders(ordno) on delete cascade,
itemno number(4) references items(itemno) on delete set null,
qty number(4),
primary key(ordno)
);
Table created.
TABLE DESCRIPTION
SQL> describe order_item;
Name Null? Type
------
ORDNO NOT NULL NUMBER(4)
ITEMNO NUMBER(4)
QTY NUMBER(4)
INSERTING TUPLES
SQL> insert into order_item values ('&ordno','&itemno','&qty');
Enter value for ordno: 11
Enter value for itemno: 1
Enter value for qty: 8
old 1: insert into order_item values ('&ordno','&itemno','&qty')
new 1: insert into order_item values ('11','1','8')
1 row created.
SQL> insert into order_item values(‘12’,’2’,’1’);
SQL> insert into order_item values(‘13’,’3’,’2’);
SQL> insert into order_item values(‘14’,’4’,’5’);
SQL> insert into order_item values(‘15’,’3’,’9’);
SQL> select * from order_item;
ORDNO ITEMNO QTY
------
11 1 8
12 2 1
13 3 2
14 4 5
15 3 9
TABLE CREATION
SQL> create table warehouse
(
wareno number(4) primary key,
city varchar(10)
);
Table created.
TABLE DESCRIPTION
SQL> describe warehouse;
Name Null? Type
------
WARENO NOT NULL NUMBER(4)
CITY VARCHAR2(10)
INSERTING TUPLES
SQL> insert into warehouse values('&wareno','&city');
Enter value for wareno: 1
Enter value for city: delhi
old 1: insert into warehouse values('&wareno','&city')
new 1: insert into warehouse values ('1','delhi')
1 row created.
SQL> insert into warehouse values(‘2’,’poona’);
SQL> insert into warehouse values(‘3’,’delhi’);
SQL> insert into warehouse values(‘4’,’madras’);
SQL> insert into warehouse values(‘5’,’hyd’);
SQL> select * from warehouse;
WARENO CITY
------
1 delhi
2 poona
3 delhi
4 madras
5 hyd
TABLE CREATION
SQL> create table shipment
(
ordno number(4) references orders(ordno),
wareno number(4) references warehouse(wareno),
shipdate date,
primary key(ordno,wareno)
);
Table created.
TABLE DESCRIPTION
SQL> describe shipment;
Name Null? Type
------
ORDNO NOT NULL NUMBER(4)
WARENO NOT NULL NUMBER(4)
SHIPDATE DATE
INSERTING TUPLES
SQL> insert into shipment values('&ordno','&wareno','&sdate');
Enter value for ordno: 11
Enter value for wareno: 1
Enter value for sdate: 16-jul-1998
old 1: insert into shipment values ('&ordno','&wareno','&sdate')
new 1: insert into shipment values ('11','1','16-jul-1998')
1 row created.
SQL> insert into shipment values(‘12’,’2’,’22-NOV-03’);
SQL> insert into shipment values(‘13’,’3’,’16-FEB-95’);
SQL> insert into shipment values(‘14’,’4’,’31-JAN-08’);
SQL> insert into shipment values(‘15’,’5’,’16-AUG-00’);
SQL> select * from shipment;
ORDNO WARENO SHIPDATE
------
11 1 16-JUL-98
12 2 22-NOV-03
13 3 16-FEB-95
14 4 31-JAN-08
15 5 16-AUG-00
QUERIES
1.Produce a listing: CUSTNAME, NO_OF_ORDERS, 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.
SQL> select c.cname as custname,count(*) as no_of_orders,avg(oi.qty*i.price) as avg_order_amt
from customer c,orders o,order_item oi,items i
where c.custno=o.custno and o.ordno=oi.ordno and i.itemno=oi.itemno
group by(c.cname);
RESULT
CUSTNAME NO_OF_ORDERS AVG_ORDER_AMT
------
aaa 2 6940
eee 1 11500
ddd 2 9320
EXPLANATION
This query produces the list of customers with no of orders. The three relations are joined at corresponding attributes. The count function is used to display the names. The average is the product of quantity and price.
2. List the order# for orders that were shipped from all the warehouses that the company has in a specific city.
SQL> select s.ordno
from shipment s,warehouse w
where w.wareno=s.wareno and w.city='delhi';
RESULT
ORDNO
------
11
13
EXPLANATION
The two tables warehouse and shipment are joined and the tuples with given city name are selected and orderno is selected.
3. Demonstrate how you delete item# from the ITEM table and make that field null in the ORDER_ITEM table
SQL> delete
from items
where itemno=1;
1 row deleted.
RESULT
SQL> select * from item;
ITEMNO PRICE
------
2 2000
3 1320
4 2300
5 1200
SQL> select *from order_item;
ORDNO ITEMNO QTY
------
11 8
12 2 1
13 3 2
14 4 5
15 3 9
EXPLANATION
Since the attribute itemno is a primary key, we have eliminated that constraint and after that the tuples with the indicated itemno are deleted. Also in the order_item table the tuple which contained itemno 10 has been made null.
PROBLEM 3
PROBLEM STATEMENT:
Consider the following database of student enrollment in courses and books adopted for each course .