Answer to Question #48779 in Databases | SQL | Oracle | MS Access for jincyjayan

Question

. An insurance database has the tables given below. The data types are also specfified.

Table name : owner_details
Column name Format
Owner_id Char(4)
Name Varchar(25)
Address Varchar(40)

Table name : car
Column name Format
Reg_no Char(10)
Model Varchar(15)
Year Number(4)

Table name : accident
Column name Format
Report_no Number(5)
Acc_date Date
Location Varchar(20)

Table name : cars_owners
Column name Format
Owner_id Char(4)
Reg_no Char(10)

Table name : cars_in_accident
Column name Format
Owner_id Char(4)
Reg_no Char(10)
Report_no Number(5)
Cost_of_damage Number(5)


a) Create the above table with proper primary and foreign keys.
b) Enter at least 5 tuples for each relation
c) Update the cost of damage for a particular car involved in an accident with report
number 25 to 30000.
d) Add a new car to the database
e) Add a new accident to the database
f) List the car owners whose cars were involved in accidents in the year 2006
g) Find the number of

Answer 1

a) Create the above table with proper primary and foreign keys - Create_tables.sql and drop_tables.sql (if necessary)
b) Enter at least 5 tuples for each relation - Insert.sql
c) Update the cost of damage for a particular car involved in an accident with report
number 25 to 30000 - Update.sql
d) Add a new car to the database - Insert_new_car.sql
e) Add a new accident to the database - Insert_new_accident.sql
f) List the car owners whose cars were involved in accidents in the year 2006 - List_car_2006.sql
g) Find the number of ????
/* create_tables.sql */
-- CREATE TABLE
CREATE TABLE OWNER_DETAILS
(
OWNER_ID CHAR(4) NOT NULL,
NAME VARCHAR2(25),
ADDRESS VARCHAR2(40)
);
ALTER TABLE OWNER_DETAILS ADD CONSTRAINT PK_OWNER_DETAILS PRIMARY KEY (OWNER_ID);
-- CREATE TABLE
CREATE TABLE CAR
(
REG_NO CHAR(10) NOT NULL,
MODEL VARCHAR2(15),
YEAR VARCHAR2(4)
);
ALTER TABLE CAR ADD CONSTRAINT PK_CAR PRIMARY KEY (REG_NO);
-- CREATE TABLE
CREATE TABLE ACCIDENT
(
REPORT_NO NUMBER(5) NOT NULL,
ACC_DATE date,
LOCATION VARCHAR2(20)
);
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS
ALTER TABLE ACCIDENT
ADD CONSTRAINT PK_ACCIDENT PRIMARY KEY (REPORT_NO);

-- CREATE TABLE
CREATE TABLE CARS_OWNERS
(
OWNER_ID CHAR(4),
REG_NO CHAR(10)
);
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS
ALTER TABLE CARS_OWNERS
ADD CONSTRAINT FK_CARS_OWNERS_CAR FOREIGN KEY (REG_NO)
REFERENCES CAR (REG_NO);
ALTER TABLE CARS_OWNERS
ADD CONSTRAINT FK_CARS_OWNERS_OWNER_DETAILS FOREIGN KEY (OWNER_ID)
REFERENCES OWNER_DETAILS (OWNER_ID);

-- CREATE TABLE
CREATE TABLE CARS_IN_ACCIDENT
(
OWNER_ID CHAR(4),
REG_NO CHAR(10),
REPORT_NO NUMBER(5),
COST_OF_DAMAGE NUMBER(5)
);
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS
ALTER TABLE CARS_IN_ACCIDENT
ADD CONSTRAINT FK_CARS_IN_ACC_CAR FOREIGN KEY (REG_NO)
REFERENCES CAR (REG_NO);
ALTER TABLE CARS_IN_ACCIDENT
ADD CONSTRAINT FK_CARS_IN_ACC_OWNER_DETAILS FOREIGN KEY (OWNER_ID)
REFERENCES OWNER_DETAILS (OWNER_ID);
ALTER TABLE CARS_IN_ACCIDENT
ADD CONSTRAINT FK_CARS_IN_ACC_ACCIDENT FOREIGN KEY (REPORT_NO)
REFERENCES ACCIDENT (REPORT_NO);

/* drop_tables.sql */
drop table CARS_IN_ACCIDENT;
drop table CARS_OWNERS;
drop table OWNER_DETAILS;
drop table CAR;
drop table ACCIDENT;
/* insert.sql */
/*OWNER_DETAILS*/
insert into OWNER_DETAILS (OWNER_ID, NAME, ADDRESS)
values ('1', 'Ivanov', 'Kiev');
insert into OWNER_DETAILS (OWNER_ID, NAME, ADDRESS)
values ('2', 'Sidorov', 'Moscow');
insert into OWNER_DETAILS (OWNER_ID, NAME, ADDRESS)
values ('3', 'Petrov', 'Kiev');
insert into OWNER_DETAILS (OWNER_ID, NAME, ADDRESS)
values ('4', 'Gusev', 'New York');
insert into OWNER_DETAILS (OWNER_ID, NAME, ADDRESS)
values ('5', 'Vertod', 'Berlin');
commit;
/*CAR*/
insert into CAR (REG_NO, MODEL, YEAR)
values ('1', 'Chevrolet Aveo', '2007');
insert into CAR (REG_NO, MODEL, YEAR)
values ('2', 'Toyota Camry', '2002');
insert into CAR (REG_NO, MODEL, YEAR)
values ('3', 'Nissan X-Trail', '2012');
insert into CAR (REG_NO, MODEL, YEAR)
values ('4', 'Mercedes-Benz', '1998');
insert into CAR (REG_NO, MODEL, YEAR)
values ('5', 'Volkswagen', '2002');
commit;
/*ACCIDENT */
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (10, to_date('01-11-2006', 'dd-mm-yyyy'), 'USA');
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (20, to_date('05-05-2014', 'dd-mm-yyyy'), 'Ukraine');
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (30, to_date('12-08-2013', 'dd-mm-yyyy'), 'Poland');
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (400, to_date('12-11-2008', 'dd-mm-yyyy'), 'Poland');
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (500, to_date('13-08-2014', 'dd-mm-yyyy'), 'Canada');
commit;
/*CARS_OWNERS*/
insert into CARS_OWNERS (OWNER_ID, REG_NO)
values ('1', '5');
insert into CARS_OWNERS (OWNER_ID, REG_NO)
values ('2', '4');
insert into CARS_OWNERS (OWNER_ID, REG_NO)
values ('4', '1');
insert into CARS_OWNERS (OWNER_ID, REG_NO)
values ('3', '2');
insert into CARS_OWNERS (OWNER_ID, REG_NO)
values ('5', '3');
commit;
/*CARS_IN_ACCIDENT */
insert into CARS_IN_ACCIDENT (OWNER_ID, REG_NO, REPORT_NO, COST_OF_DAMAGE)
values ('1', '5', 20, 1500);
insert into CARS_IN_ACCIDENT (OWNER_ID, REG_NO, REPORT_NO, COST_OF_DAMAGE)
values ('2', '4', 30, 300);
insert into CARS_IN_ACCIDENT (OWNER_ID, REG_NO, REPORT_NO, COST_OF_DAMAGE)
values ('4', '1', 10, 488);
insert into CARS_IN_ACCIDENT (OWNER_ID, REG_NO, REPORT_NO, COST_OF_DAMAGE)
values ('3', '2', 400, 456);
insert into CARS_IN_ACCIDENT (OWNER_ID, REG_NO, REPORT_NO, COST_OF_DAMAGE)
values ('5', '3', 500, 100);
commit;
/* Insert_new_accident.sql */
insert into ACCIDENT (REPORT_NO, ACC_DATE, LOCATION)
values (6050, to_date('10-11-2014', 'dd-mm-yyyy'), 'USA');
commit;
/* insert_new_car.sql */
insert into CAR (REG_NO, MODEL, YEAR)
select
max(t.reg_no)+1 as REG_NO,
'Suzuki',
'2008'
from car t;
commit;
/* list_car_2006.sql */
select c.* from car c
inner join cars_in_accident c_a
on c.reg_no=c_a.reg_no
inner join accident a
on c_a.report_no=a.report_no
where a.acc_date=to_date('01-11-2006', 'dd-mm-yyyy');
/* update.sql */
update CARS_IN_ACCIDENT t
set t.cost_of_damage=50
where t.report_no between 25 and 30000;
commit;