Consider the following database: person (driver.. id, name, address) car (licens
ID: 3573676 • Letter: C
Question
Consider the following database: person (driver.. id, name, address) car (license. model, year) accident (report number, date, location) owns (driver id, license) participated (report number, license, driver id, damage amount). Write SQL DDL corresponding to the following schema. Make any reasonable assumptions about data types., and be sure to declare primary and foreign keys. Find the number of accidents in which the cars belonging to "John Smith" were involved. Update the damage amount for the car with license number "AABB2000" in the accident with report number "AR2197"to $3000.Explanation / Answer
1
Create table person
(
driver_id int not null,
name varchar(50) not null,
address varchar(100) not null,
primary key(driver_id)
)
create table car
(
license varchar(50) not null,
model varchar(50),
year int,
primary key(license)
)
create table accident
(
report_number varchar(50) not null,
date date not null,
location varchar(200) ,
primary key(report_number)
)
create table owns
(
driver_id int not null,
license varchar(50) not null,
primary key(driver_id,license),
foreign key(driver_id) references person(driver_id),
foreign key(license) references car(license)
)
create table participated
(
report_number varchar(50) not null,
license varchar(50) not null,
driver_id int not null,
damage_amount decimal(10,2),
primary key(report_number,license,driver_id),
foreign key(driver_id) references person(driver_id),
foreign key(license) references car(license),
foreign key(report_number) references accident(report_number)
)
2.
Select count(a.report_number) from accident a inner join participated pt on a.report_number = pt.report_number inner join person p on pt.driver_id = p.driver_id where p.name = 'John Smith';
3
Update participated
set damage_amount =3000
where license = 'AABB2000' and report_number ='AR2197';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.