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

Question #48779
. 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
Expert's answer
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;

Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be first!

Leave a comment

Ask Your question

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS
paypal