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
- [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.]
Construct the following SQL queries for
this relational database.
[Assume that attribute date is a character string of the form
|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 )
Insurance database. [In the
driver of the car in the accident, who is not necessarily
- Find the total number of people who owned cars that were involved in accidents in 1989.
- Find the number of accidents in which the cars belonging to
[anyone named] ``John Smith'' were involved.
- 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.
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.]
- Update the damage amount for the car with license number ``AABB2000''
in the accident with report number ``AR2197'' to $3000.
- 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.
- modified [15 pts.] Give an SQL schema definition
for the warehouse database shown below. Choose an
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,
|suppliers ( part_number, company_name )