Consider the following schema:
Person(SSN, name, address) Car(license, year, model) Accident(license, accident_date, driver, damage_amount) Owns(SSN, license)
Primary keys are underlined. Note that the driver involved in a car accident may not always be the owner of the car. Assume that accident_date is of type integer, and represents a year (e.g. 1980). Year is also of type integer. Also, assume that a car cannot get involved in more than one accident at a certain date.
Which of the following query is correct to find the SSN of every person who owns one or more cars, none of which has ever been involved in a car accident.
(A)
SELECT O.SSN FROM Owns O WHERE O.license NOT IN (SELECT A.license FROM Accident A)
(B)
πSSN(Owns) - πSSN(Owns ⨝ Accident)
(C)
{ O | ∃O1 ∈ Owns( O.SSN = O1.SSN ∧ ¬∃A∈ Accident( O1.license = A.license )) }
(D) All of the above.
Answer: (B)
Explanation: Only option (B) is correct.
Option (A) and option (C) return the SSN of every person that has at least one car that is not involved in an accident, while the question asks for the people for whom all of their cars have never been involved in an accident.
Quiz of this Question