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 (TAs 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).