Assignment 2 - CS743
Fall, 2014
This assignment is to be completed individually by each student.
Please collect your answers to all of the questions
into a single, readable document, in PDF format.
Be sure that your document includes your name.
Submit your PDF by e-mail
to the instructor. Use the subject line "CS743 A2 submission -
NAME", replacing NAME with your name.
Question 1:
A television network wants to record details of medal winning at the
Olympic games. Produce an E-R diagram that models the information described
in the following:
-
Athletes have a name which is unique, a gender and an age. This may or
may not be the first Olympic Games in which an athlete has competed.
-
Athletes must compete for a country and they can only compete for one country.
-
There are team events and individual events, so an entry to an event may
consist of one athlete or a team of athletes. All athletes in a team must
compete for the same country.
-
Athletes may be entered in more than one event.
-
Medals are awarded to the entrants (teams or individual) coming first,
second or third in each event. Gold for first, silver for second and bronze
for third. Usually only one entrant wins each type of medal in an event,
however if there is a tie it is possible for multiple medals of the same
type to be awarded. For example, in case of a two-way tie for first
place, two gold medals and a bronze would be awarded.
-
In team sports, medals are distributed to all members of the winning teams.
-
Olympic and/or world records may be set by any entrant in any event. A
record is either a fastest time or a highest score.
-
Events fall into different categories such as swimming, track, field etc.
You may not be able to exactly model everything that is described above
- do the best that you can. If you cannot model something exactly, it is
preferable to be under-constrained rather than over-constrained. If there
are constraints that you recognize but that you cannot capture your model,
list them explicitly below your diagram (or on a separate page).
Question 2:
Translate the E-R diagram you generated for the previous question into
a relational schema diagram using the procedure discussed in class and
in your notes. Show all of the attributes of each relation, indicate
which attributes form the primary key of each relation, and indicate any
foreign key constraints using the usual diagrammatic convention.
Question 3:
Is the relational schema you produced for Question 2 in BCNF?
If so, briefly justify your claim that it is BCNF.
If not, produce a new schema that is BCNF by performing a
lossless-join BCNF decomposition of your original schema.