Person (driver_id, Name, address) - Car (license, model, year) - Accident (report_no, adate, location) - Owns (driver_id, license) - Participated (driver_id, license, report_no, damage_amount) - i. Find total number of people who owned cars that are involved in accidents in 2004. - ii. Find the number of accidents in which car belonging to ‘John Smith’ were involved. - iii. Add new accident to Database - iv. Delete ‘Santro’ belonging to ‘John Smith’ -

i. Find total number of people who owned cars that are involved in accidents in 2004.

SELECT COUNT (DISTINCT name)

FROM Accident, Participated, Person

WHERE Accident.report_no= Participated.report_no

AND Participated.driver id = Person.driver id

AND date BETWEEN DATE ’2004-00-00’ and DATE ’2004-12-31’

ii. Find the number of accidents in which car belonging to ‘John Smith’ were involved.

SELECT count (distinct *)

FROM Accident

WHERE exists

( SELECT *

FROM Participated, Person

WHERE Participated.driver_id= Person.driver_id

AND Person.name= “John Smith”

AND Accident.report_no= Participated.report_no

)

iii. Add new accident to Database

Let us assume that the driver’s name is ‘xyz’ and owns a car named ‘abc’. The license of the given car needs to be found out first, further Participated and Accident need to be updated for the accident to be recorded and associate it with the car.

INSERT INTOAccident

VALUES(9001, ‘2015-09-14’, ‘abc’)

INSERT INTO Participated

SELECT o.driver_id, c.license, 9001, 2500

FROM Personp, Ownso, Carc

WHERE p.Name= ‘xyz’

AND p.Driver_id= o.driver_id

AND o.license= c.license

AND c.model= ‘abc’

iv. Delete ‘Santro’ belonging to ‘John Smith’

DELETE Car

WHERE model = ‘Santro’

AND license IN

( SELECT license

FROM Person p, Owns o

WHERE p.name = ‘John Smith’

AND p.driver_id = o.driver_id

)