dbms - accident database

Consider the insurance database given below. The primary keys are made bold  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)

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

Person:

Create table person(

Driver_id varchar(5) primary key,

Driver_name varchar(10),

Driver_addr varchar(30));

Car:

Create table car(

Car_reg varchar(10) primary key,

Car_model varchar(10),

Car_year number(10));

Accident:

Create table accident(

Acc_rep_no number(10) primary key,

Acc_location varchar(30),

Acc_date varchar(10));

Owns:

Create table owns(

O_driver_id  varchar(5) primary key,

O_car_reg  varchar(10));

Participated:

Create table participated(

P_driver_id  varchar(5) references person(driver_id),

P_car_reg  varchar(10) references car(car_reg),

P_acc_rep_no  number(8)  references accident(acc_rep_no),

P_damage_amount  number(8) );

2) Enter at least five tuples for each relation.

SQL> insert into person values('d07','chethan','tumkur');

1 row created.

SQL> select * from person;

DRIVE_ID   DRIVER_NAME   DRIVER_ADDR

----- ---------- --------------------------------------------

d01                   arun                             yelahanka

d02                  bindu                           mysore

d03                  vikas                            bidar

d04                  zaman                           maleswarm

d05                  kiran                            hebbal

d06                  darshan                        kormangal

d07                  chethan                        tumkur

7 rows selected.

SQL> insert into accident values('111','tumkur','2013');

1 row created.

SQL> select * from accident;

ACC_REP_NO          ACC_LOCATION                   ACC_DATE

---------- ------------------------------ --------------------------------------

       101                       yelahanka                                2000

       102                       kormangal                               2001

       105                       hebbal                                      2001

       104                       maleswaram                            2001

       103                       penya                                       2001

       106                       yelahanka                                2005

       107                       kengeri                                                2008

       108                       yellahanka                               2008

       109                       majestic                                   2008

       110                       sindhanur                                1993

       111                       tumkur                                     2013

11 rows selected.

SQL> insert into car values('KA07','honda','2003');

1 row created.

SQL> select * from car;

CAR_REG    CAR_MODEL             CAR_YEAR

----------            ---------- ------               ------------------

KA01               honda                         2007

KA06              toyota                          2002

KA04              suzuki                          2005

KA03              hyundai                       2000

KA02              tata                              2005

KA05              tata                              2004

KA07              honda                          2003

7 rows selected.

SQL> insert into owns values('d07','KA07');

1 row created.

SQL> select * from owns;

O_DRIVER_ID         O_CAR_REG

----- ----------                ------------------

d01                              KA01

d02                              KA06

d03                              KA04

d04                              KA03

d05                              KA02

d06                              KA05

d07                              KA07

7 rows selected.

SQL> insert into participated values('d07','KA07','111','2300');

1 row created.

SQL> select * from participated;

P_DRI       P_CAR_REG      P_ACC_REP_NO     P_DAMAGE_AMOUNT

----- ---------- ------------ ---------------------------------------------------------------

d01                  KA01                          101                         2000

d02                  KA03                          102                        25000

d03                  KA06                          103                        2500

d04                  KA04                          104                        1400

d05                  KA05                          105                        1200

d06                  KA03                          106                        25000

d02                  KA02                          102                        25000

d03                  KA04                          107                        1000

d05                  KA05                          108                        1050

d02                  KA06                          109                        1550

d07                  KA07                          111                        2300

11 rows selected.

3) Demonstrate how you

a. Update the damage amount for the car with specific regno in the accident with report number 12 to 25000.

SQL> update participated set p_damage_amount=25000 where p_car_reg='KA03';

2 rows updated.

SQL> select * from participated;

P_DRIVER_ID    P_CAR_REG     P_ACC_REP_NO     P_DAMAGE_AMOUNT

----- ---------- ------------ ---------------

d01                              KA01                   101                             2000

d02                              KA03                   102                             25000

d03                              KA06                   103                             2500

d04                              KA04                   104                             1400

d05                              KA05                   105                             1200

d06                              KA03                   106                             5000

d02                              KA02                   102                             25000

d03                              KA04                107                             1000

d05                              KA05                108                             1050

d02                              KA06                109                             1550

10 rows selected.

b. Add a new accident to the database.

SQL> insert into accident values('110','sindhanur','1993');

1 row created.

SQL> select * from accident;

ACC_REP_NO          ACC_LOCATION                   ACC_DATE

---------- ------------------------------ ----------

       101                       yelahanka                                    2000

       102                       kormangal                                   2001

       105                       hebbal                                          2001

       104                       maleswaram                                2001

       103                       penya                                           2001

       106                       yelahanka                                    2005

       107                       kengeri                                                    2008

       108                       yellahanka                                   2008

       109                       majestic                                       2008

       110                       sindhanur                                    1993

10 rows selected.

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

SQL> select p.driver_name,count(p.driver_id) from person p, accident a, owns o,

participated pa where p.driver_id=o.o_driver_id and o.o_driver_id=pa.p_driver_id

 and a.acc_rep_no=pa.p_acc_rep_no and a.acc_date='2008' group by p.driver_name h

aving count(p.driver_id)>0;

DRIVER_NAME       COUNT(P.DRIVER_ID)

---------- ------------------------------------------------

kiran                                1

bindu                               1

vikas                                1

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

SQL> select c.car_model,count(pa.p_car_reg) from participated pa,car c,accident

a where a.acc_rep_no=pa.p_acc_rep_no and pa.p_car_reg=c.car_reg group by c.car_m

odel;

CAR_MODEL           COUNT(PA.P_CAR_REG)

---------- -------------------

tata                                  3

hyundai                           2

honda                              1

toyota                              2

suzuki                              2