1.
INSURANCE DATABASE
Table creation
(i)Sql> create table person(driverid varchar2(8) primary key, name varchar2(10),
address varchar2(14));
Sql>create table car(regno varchar2(4) primary key, model varchar2(6), year
number(4));
Sql>create table accident(repno number(4) primary key, accddate date, location
varchar2(10));
Sql>create table owns(driverid varchar2(8), regno varchar2(4), foreign key
(driverid) references person, foreign key (regno) references car);
Sql>create table participated(driverid varchar2(8), regno varchar2(4), repno
number(4), damage amount number (6), foreign key (driverid) references person,
foreign key (regno) references car, foreign key (repno) references accident);
2.ORDER PROCESSING DATABASE
Table Creation
Sql>create table customer(custno number(4) primary key, name varchar2(8), city
varchar2(8));
Sql>create table ordertab(ordno number(5) primary key,orddate date, custno
number(4), orderamount number(6), foreign key (custno) references customer);
Sql>create table item(itemno number(5) primary key, unitprice number(6));
Sql> create table orderitem(ordno number(5) , itemno number(5), quantity
number(5), foreign key (ordno) references ordertab, foreign key (itemno)
references item);
Sql>create table warehouse(warehouseno number(8) primary key, city
varchar2(8));
Sql>create table shipmenttab(ordno number(5), warehouseno number(8),
shipdate date, foreign key (ordno) references ordertab, foreign key
(warehouseno) references warehouse);
3.STUDENT ENROLLMENT DATABASE
Table creation
Sql>create table student(regno vrchar2(8) primary key, name varchar2(8), major
varchar2(8), bdate date);
Sql>create table coursetab(courseno number(4) primary key,cname
varchar2(8) ,dept varchar2(8));
Sql> create table enroll (regno varchr2(8) , courseno number(4), sem
number(4),marks number(4), foreign key (regno) references student, foreign key
(courseno) references coursetab);
Sql>create table text(bookisbn number(8) primary key, booktitle varchar2(8),
publisher varchar2(8),author varchar2(8));
Sql>create table bookadopt(courseno number(4), sem number(4), bookisbn
number(8), foreign key (courseno) references coursetab, foreign key (bookisbn)
references text);
4. BOOKS DEALER DATABASE
create table
Sql>create table author (authored number(8) primary key, name varchar2(8), city
varchar2(8), country varchar2(8));
Sql>create table publisher (publisherid number(8) primary key, name
varchar2(8) , city varchar2(8),country varchar2(8));
Sql> create table category (categoryid number(8) primary key, description
varchar2(8));
Sql>create table catalog (bookid number(8) primary key, authorid number(8),
publisherid number(8),categoryid number(8).year number(5),price
number(8),foreign key (authorid) references author, foreign key (publisherid)
references publisher,foreign key (categoryid) references category);
Sql>create table orderdetails(orderno number(8) primary key, bookid number(8),
quantity number(8), foreign key (bookid) references catalog);
5.BANKING ENTERPRISE DATABASE
Sql>create table branch (branchname varchar2(10) primary key, branchcity
varchar2(8), assets number (8,2));
Sql>create table acount (accno number(10) primary key, branchname
varchar2(10) , balance number(8,2), foreign key (branchname) references
branch);
Sql> create table customer (customername varchar2(20) primary key,
customerstreet varchar2(20), customercity varchar2(20));
Sql>create table depositor (customername varchar2(20), accno number(10),
foreign key (customername) references customer, foreign key (accno) references
acount);
Sql>create table loan (loannumber number(10) primary key, branchname
varchar2(10), amount number(8,2), foreign key (branchname) references branch);
Sql>create table borrower (customername varchar2(20), loannumber
number(10), foreign key (customername) references customer, foreign key
(loannumber) references loan);
