DBMS - Program 6 - Insurance Database

 

6. INSURANCE DATABASE

Consider the Insurance database given below. The primary keys are underlined and the data types are specified.

PERSON (Driverld: text; Name: text; Address: text)

CAR (RegNo: text; Model: text; Year: number)
OWNS (DriverId: text; RegNo: text)

ACCIDENT (ReportNo: number, AccDate: Date; Location: text)

PARTICIPATED (DriverId; text; RegNo: text; ReportNo; number, Dmg_Amt:number (10, 2))

a)  Create the above tables by specifying the primary and foreign keys.

b) Enter at least five tuples for each relation

c)  Update the damage amount for each car accident.

d) Add a new accident to the database.

e)  Find the total number of people who owned cars that were involved in accidents in the
                                                                                             year 2002.

f)  Find the number of accidents in which cars belonging to a specific model were involved.

g) Display the owners and their car details.

 

Create 4 tables:

 

a)  Create the above tables by specifying the primary and foreign keys.

 

-- a)  Create the above tables by specifying the primary and foreign keys.

--PERSON (Driverld: text; Name: text; Address: text) 

create table person(driverid varchar2(20) primary key,name varchar2(20),address varchar2(20));

desc person;

 

--CAR (RegNo: text; Model: text; Year: number) 

create table car(regno varchar2(20) primary key,model varchar2(20),year number);

desc car;

 

--OWNS (DriverId: text; RegNo: text)

create table owns(driverid varchar2(20) references person(driverid),regno varchar2(20) references car(regno));

desc owns;

 

 

--ACCIDENT (ReportNo: number, AccDate: Date; Location: text)

create table accident(reportno number primary key,accdate date,location varchar2(20));

desc accident;

 

--PARTICIPATED (DriverId; text; RegNo: text; ReportNo; number, Dmg_Amt:number (10,2))

 

create table participated(dirverid varchar2(20) references person(driverid), regno varchar2(20) references car(regno),reportno number references accident(reportno),  dmg_amt number(10,2));

desc participated;

 

b) Enter at least five tuples for each relation

 

Insert values into person table:

 

-- b) Enter at least five tuples for each relation

 

insert into person values(:driverid,:name,:address);

select * from person;

 

 

Insert values into car table:

 

insert into car values(:regno,:model,:year);

select * from car;

 

 

 

Insert values into owns table:

 

-- insert into owns table

 

insert into owns values(:driverid,:regno);

select * from owns;

 

 

 

Insert values into accident table:

-- insert into accident table

insert into accident values(:reportno,:accdate,:location);

 

select * from accident;

 

 

Insert values into participated table:

-- insert into participated table

 

insert into participated values(:driverid,:regno,:reportno,:dmg_amt);

 

select * from participated;

 

 

 

c)  Update the damage amount for each car accident.

 

--c)  Update the damage amount for each car accident.

                                                                     

update participated set dmg_amt=dmg_amt +2500;

 

select * from participated;

 

 

 

d) Add a new accident to the database.

--d) Add a new accident to the database.

 

insert into accident values(666,'07/17/2015','paris');

 

select * from accident;

 

 

e)  Find the total number of people who owned cars that were involved in accidents in the
                                                                                             year 2002.

--e)  Find the total number of people who owned cars that were involved in accidents in the year 2002.

 

select count(*) from accident where accdate between '01/01/2002' and '12/31/2002';

 

 

f)  Find the number of accidents in which cars belonging to a specific model were involved.

--f)  Find the number of accidents in which cars belonging to a specific model were involved.

 

select count(*) as Maruthi_Accident from car,accident,participated where car.regno= participated.regno and accident.reportno=participated.reportno and car.model='maruthi';

 

 

 

g) Display the owners and their car details.

 

--g) Display the owners and their car details.

 

select person.driverid,person.name,person.address,car.regno,car.model from person,car,owns where owns.regno=car.regno and person.driverid=owns.driverid;

 

    

 

OVERALL QUERY:

 

-- a)  Create the above tables by specifying the primary and foreign keys.

--PERSON (Driverld: text; Name: text; Address: text) 

create table person(driverid varchar2(20) primary key,name varchar2(20),address varchar2(20));

desc person;

 

--CAR (RegNo: text; Model: text; Year: number) 

create table car(regno varchar2(20) primary key,model varchar2(20),year number);

desc car;

--OWNS (DriverId: text; RegNo: text)

create table owns(driverid varchar2(20) references person(driverid),regno varchar2(20) references car(regno));

desc owns;

 

--ACCIDENT (ReportNo: number, AccDate: Date; Location: text)

create table accident(reportno number primary key,accdate date,location varchar2(20));

desc accident;

 

--PARTICIPATED (DriverId; text; RegNo: text; ReportNo; number, Dmg_Amt:number (10,2))

 

create table participated(dirverid varchar2(20) references person(driverid), regno varchar2(20) references car(regno),reportno number references accident(reportno),dmg_amt number(10,2));

desc participated;

 

-- b) Enter at least five tuples for each relation

-- insert into person table

 

insert into person values(:driverid,:name,:address);

select * from person;

 

-- insert into cars table

insert into car values(:regno,:model,:year);

select * from car;

 

-- insert into owns table

 

insert into owns values(:driverid,:regno);

select * from owns;

 

-- insert into accident table

insert into accident values(:reportno,:accdate,:location);

select * from accident;

 

-- insert into participatd table

 

insert into participated values(:driverid,:regno,:reportno,:dmg_amt);

select * from participated;

 

--c)  Update the damage amount for each car accident.

 

update participated set dmg_amt=dmg_amt + 2500;

 

select * from participated;

 

--d) Add a new accident to the database.

 

insert into accident values(666,'07/17/2015','paris');

 

select * from accident;

 

e)  Find the total number of people who owned cars that were involved in accidents in the year 2002.

 

select count(*) from accident where accdate between '01/01/2002' and '12/31/2002';

 

f)  Find the number of accidents in which cars belonging to a specific model were involved.

 

select count(*) as Maruthi_Accident from car,accident,participated where car.regno=participated.regno and accident.reportno=participated.reportno and car.model='maruthi';

 

g) Display the owners and their car details.

 

select person.driverid,person.name,person.address,car.regno,car.model from person,car,owns where owns.regno=car.regno and person.driverid=owns.driverid;