60 595
Assignments Done
97,8%
Successfully Done
In April 2018

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

Submit
Privacy policy Terms and Conditions