Database
Assignment #2 – Exercises
Exercise 1: Design a
database suitable for a university registrar. This database should include
information about students, departments, professors, courses, which students
are enrolled in which courses, which professors are teaching which courses,
student grades, the TA for a course (TA¡¯s are students), which courses a department offers, and any other
information you deem appropriate. Note that this question is more free-form than
the questions above, and you need to make some decisions about multiplicities
of relationships, appropriate types, and even what information needs to be
represented.
Exercise 2: Figure 1 is
an UML diagram for a bank database involving customers and accounts. Since
customers may have several accounts, and accounts may be held jointly by
several customers, we associate with each customer an ¡°account set,¡± and
accounts are members of one or more account sets. Assuming the meaning of the
various relationships and attributes are as expected given their names,
criticize the design. What design rules are violated? Why? What modifications
would you suggest?
Figure 1
Exercise 3: In this and
following exercises we shall consider two design options in the E/R model for
describing births. At a birth, there is one baby (twins would be represented by
two births), one mother, any number of nurses, and any number of doctors.
Suppose, therefore, that we have entity sets Babies, Mothers, Nurses, and
Doctors. Suppose we also use a relationship Births, which connects these four
entity sets, as suggested in Fig. 2. Note that a tuple of the relationship set
for Births has the form (baby, mother, nurse, doctor). If there is more than
one nurse and/or doctor attending a birth, then there will be several tuples
with the same baby and mother, one for each combination of nurse and doctor.
Figure 2
There are certain assumptions that we might wish to
incorporate into our design. For each, tell how to add arrows or other elements
to the E/R diagram in order to express the assumption.
a)
For every combination of a baby and a mother there is a unique nurse.
b)
For every baby, there is a unique mother.
c)
For every combination of a baby, nurse, and doctor, there is a unique mother.
Exercise 4:
Another approach to the problem of Exercise 3 to connect the four entity sets
Babies, Mothers, Nurses, and Doctors by an entity set Births, with four
relationships, one between Births and each of the other entity sets, as
suggested in Fig. 2. Use
cardinality notations (indicating that certain of these relationships are
many-one) to represent the following conditions:
a)
Every baby is the result of a unique birth, and every birth is of a unique
baby.
b)
In addition to (a), every baby has a unique mother.
c)
In addition to (a) and (b). for every birth there is a unique doctor.
In
each case, what design flaws do you see?
Exercise 5: One way to
represent students and the grades they get in courses is to use entity sets
corresponding to students, to courses, and to "enrollments."
Enrollment entities form a "connecting" entity set between students
and courses and can be used to represent not only the fact that a student is
taking a certain course, but the grade of the student in the course. Draw an
UML class diagram for this situation, indicating weak entity sets and the keys
for the entity sets. Is the grade part of the key for enrollments?
Exercise 6:
Convert the E/R diagram of Fig. 3
to a UML class diagram
Figure 3
Submission Due Date: Nov. 26 (before DB Lecture).