Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Homework for Chapter 4, Set 2
Due date: Wednesday, April 7

4.1

[24 pts.] Consider the insurance database of Figure 4.12, where the primary keys are underlined. [This design corresponds to the E-R diagram of Figure 3.38. Note that errors in the text's schema have been corrected: date is not part of the key of the accident table; the attribute identifying a car in the participated table is license; and driver-id# is not part of the key of participated because a car can only have one driver in any given accident.]

person ( driver-id#, name, address )

car ( license, model, year )

accident ( report-number, date, location )

owns ( driver-id#, license )

participated ( driver-id#, license, report-number, damage-amount )

Figure 4.12 Insurance database. [In the participated table, driver-id# identifies the driver of the car in the accident, who is not necessarily the owner.]

Construct the following SQL queries for this relational database. [Assume that attribute date is a character string of the form ``yyyy/mm/dd''.]

(a)

Find the total number of people who owned cars that were involved in accidents in 1989.

(b)

Find the number of accidents in which the cars belonging to [anyone named] ``John Smith'' were involved.

(c)

modified Add a new accident record to the database. [Assume the car involved in the accident was the Toyota belonging to ``Jill Jones,'' and that she owns only one Toyota. ``Junior Jones'' was the driver of the car in the accident, which occurred on 1998/07/25 and did $1500 worth of damage.] Assume appropriate values for any other required attributes.

(d)

modified Delete the car ``Mazda'' belonging to [anyone named] ``John Smith.'' [If he or they own more than one Mazda, delete them all. Delete not only the car record(s) but also the owns record(s) pertaining to the car. Take it as known that the car has not been involved in any accidents, so you need not update the accident and participated tables.]

(e)

Update the damage amount for the car with license number ``AABB2000'' in the accident with report number ``AR2197'' to $3000.

(f)

new Add a new customer and a new car owned by that customer to the database. The car has not been involved in any accidents. Assume appropriate values for any required attributes.

4.14

modified [15 pts.] Give an SQL schema definition for the warehouse database shown below. Choose an appropriate domain for each attribute and specify the underlined attributes as the primary keys for the relation schemas. A part_number can include letters and numbers, and is never more than 10 characters in length; weight is in pounds, and may include fractional quantities like 3.8. For a company, state is the standard 2-letter abbreviation; zip is the 5-digit zip code (define it as a number); contact and phone are the name and phone number, respectively, of a representative of the company.

part ( part_number, part_name, weight, no_in_stock )

company ( company_name, street, city, state, zip, contact, phone )

suppliers ( part_number, company_name )

Warehouse database.