Database application program of Insuranace database

By | March 24, 2015

This post explains how to create database objects based on a scenario. Also it explains about Primary key concept.

Consider the Insurance database given below.  The primary keys are underlined and the data types 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 to 25000 for the car with a specific Regno in the ACCIDENT table with report number 12.

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.

 

SQL>Create table person ( driver_id varchar2(20) not null, name varchar2(20) not null, address varchar2(20), primary key(driver_id) );SQL>insert into person1 values ( '&driver_id','&name','&address' );SQL>select * from person; DRIVER_ID NAME ADDRESS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 01A AAA BEL 01B BBB GOA 01C CCC MAH 01D DDD BEL 01E EEE RAJ1 5 rows selected SQL>create table car ( reg_no varchar2(20) not null, model varchar2(20) not null, year varchar2(20), primary key(reg_no) ); SQL>insert into car values ( '&reg_no','&model','&year' ); SQL>select* from car; REG_NO MODEL YEAR- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - A1 11A 2009 B2 22B 2009 C3 33C 2008 D4 44D 2005 E5 55E 2010 5 rows selectedSQL>create table accident ( rep_no number(5), acc_date date, location varchar2(20), primary key(rep_no) );SQL>insert into accident values (  &rep_no,'&acc_date','&location' );SQL>select * from accident; REP_NO ACC_DATE LOCATION- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1 1-DEC-09 ANGOL 2 12-FEB-08 NH4 3 3-MAY-08 ANGOL 4 4-FEB-10 RPD 12 31-DEC-08 RPD 5 rows selectedSQL>create table owns ( driver_id varchar2(20) not null, reg_no varchar2(20) not null, primary key(driver_id,reg_no), foreign key(driver_id) references person(driver_id), foreign key(reg_no) references car(reg_no) );SQL>insert into owns values ( '&driver_id','&reg_no' );SQL>select * from owns; DRIVER_ID REG_NO- - - - - - - - - - - - - - - - - - - - - - - - - - -  01A A1 01B B2 01C C3 01D D4 01E E5 5 rows selectedSQL>CREATE table participated ( driver_id varchar2(20) not null, reg_no varchar2(20) not null, rep_no number(5), damage number(5), primary key(driver_id,reg_no,rep_no), foreign key(driver_id)references person(driver_id), foreign key(reg_no) references car(reg_no), foreign key(rep_no) references accident(rep_no) ); SQL>insert into participated values ( '&driver_id','&reg_no',&rep_no,&damage ); SQL>select * from participated; DRIVER_ID REG_NO REP_NO DAMAGE - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 01A A1 1 10000  01B B2 2 15000 01C C3 3 20000 01D D4 4 40000 01E E5 12 80000 5 rows selected QUERIES:-QUERY 3a) SQL>update participateD set damage=25000 where rep_no=12 and reg_no='E5'; SQL>select * from participated; DRIVER_ID REG_NO REP_NO DAMAGE - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 01A A1 1 10000  01B B2 2 15000 01C C3 3 20000 01D D4 4 40000 01E E5 12 25000
QUERY 3b)
 SQL>insert into accident values (  &rep_no,'&acc_date','&location' ); SQL>select * from accident; REP_NO ACC_DATE LOCATION- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1 1-DEC-09 ANGOL 2 12-FEB-08 NH4 3 3-MAY-09 ANGOL 4 4-FEB-10 RPD 12 31-DEC-09 RPD 5 15-JUL-09 NH4
QUERY 4)
 SQL>select count(o.driver_id)  from ownS o,part p,accident1 a where o.driver_id=p.driver_id and p.rep_no=a.rep_no and acc_date like '%99'; COUNT - - - - - - - - - - - - -  1 
QUERY 5)
 SQL>select count(*) as count from car1 c,part p,accident1 a where model='SWIFT' and a.rep_no=p.rep_no and c.reg_no=p.reg_no; COUNT - - - - - - - - - - - 1

 

Please Share: Tweet about this on TwitterShare on FacebookShare on Google+Share on RedditPin on PinterestShare on LinkedInDigg thisShare on StumbleUponShare on TumblrBuffer this pageShare on VKEmail this to someone

4 thoughts on “Database application program of Insuranace database

Leave a Reply

Your email address will not be published. Required fields are marked *