Database Concepts - Data Warehouse Systems: Design and Implementation - page 47

Each race at a date offers several betting types (e.g., tierce, quarte+),
each type offering zero or more betting options (e.g., in order, in any
order, and bonus for the quarte+). The payoffs are given for a betting
type and a base amount (e.g., quarte+ for

e

2) and specify for each option
the win amount and the number of winners.
A horse has a name, a breed (e.g., thoroughbred), a sex, a foaling date
(i.e., birth date), a gelding date (i.e., castration date for male horses, if
any), a death date (if any), a sire (i.e., father), a dam (i.e., mother), a
coat color (e.g., bay, chestnut, white), an owner, a breeder, and a trainer.
A horse that participates in a race with a jockey is assigned a number
and carries a weight according to the conditions attached to the race
or to equalize the difference in ability between the runners. Finally, the
arrival place and the margin of victory of the horses are kept by the
application.

(a) Design an ER schema for this application. If you need additional
information, you may look at the various existing French horse racing
web sites.
(b) Translate the ER schema above into the relational model. Indicate
the keys of each relation, the referential integrity constraints, and
the non-null constraints.

2.2 A Formula One fan club wants to set up a database to keep track of the
results of all the seasons since the first Formula One World championship
in 1950.
A season is held on a year, between a starting date and an ending
date, has a number of races, and is described by a summary and a set
of regulations. A race has a round number (stating the ordering of the
race in a season), an ocial name (e.g., 2013 Formula One Shell Belgian
Grand Prix), a race date, a race time (expressed in both local and UTC
time), a description of the weather when the race took place, the pole
position (consisting of driver name and time realized), and the fastest
lap (consisting of driver name, time, and lap number).
Each race of a season belongs to a Grand Prix (e.g., Belgian Grand
Prix), for which the following information is kept: active years (e.g.,
1950-1956, 1958, etc. for the Belgian Grand Prix), total number of
races (58 races as of 2013 for the Belgian Grand Prix), and a short
historical description. The race of a season is held on a circuit, described
by its name (e.g., Circuit de Spa-Francorchamps), location (e.g., Spa,
Belgium), type (such as race, road, street), number of laps, circuit
length, race distance (the latter two expressed in kilometers), and lap
record (consisting of time, driver, and year). Notice that over the years,
the course of the circuits may be modified several times. For example,
the Spa-Francorchamps circuit was shortened from 14 to 7 km in 1979.
Further, a Grand Prix may use several circuits over the years. For