Data Analysis for Database Design, 3rd Edition

Questions

1. (a) Write down a pair of enterprise rules which would define a 1:many

relationship ‘TaughtBy’, in the direction Course to Lecturer.

(b) Express your rules in the form of determinancy constraints, assuming Course and

Lecturer have identifiers courseNo and lecturerName, respectively. (1min)

2. What is the minimum number of relationship occurrences which must be deleted

from Fig. 9.1b to make the resulting diagram consistent with a 1:1 relationship? Does

the resulting diagram necessarily imply a 1:1 relationship? (1 min)

3. What is the minimum number of relationship occurrences which must be deleted

from Fig. 9.1c to make the resulting diagram consistent with a 1:many relationship?

Does the resulting diagram necessarily imply a 1:many relationship? (1 min)

4. For each of the following pairs of enterprise rules, identify two entity types and one

relationship type. State the degree of the relationship in each case.

(a) A department employs many persons.

A person is employed by, at most, one department.

(b) A manager manages, at most, one department.

A department is managed by, at most, one manager.

(c) An author may write many books.

A book may be written by many authors.

(d) A team consists of many players.

A player plays for only one team.

(e) A lecturer teaches, at most, one course.

A course is taught by exactly one lecturer.

(f) A flight-leg connects two airports.

An airport is used by many flight-legs.

(g) A purchase-order may be for many products.

A product may appear on many purchase-orders.

(h) A customer may submit many orders.

An order is for exactly one customer. (5 min)

5. In a group medical practice, each doctor has many patients on his or her list, but a

patient can register with only one doctor at a time. If the conceptual data model includes

only current patient registrations, what is the degree of the DoctorPatientRegister

relationship between the entity types Doctor and Patient? Draw an entity-relationship

type diagram, and a sample entity-relationship occurrence diagram assuming that a

doctor is identified by doctorName and a patient by patientNo. (3 min)

6. How does the answer to question 5 change if the model is extended to include both

present and past registrations? (2 min)

7. How does the answer to question 5 change if a patient may simultaneously register

with several doctors? (1 min)

 3DUWLFLSDWLRQFRQGLWLRQV

In section 9.1 there are examples of two different ways in which an entity type can

participate in a relationship. Some of the enterprise rules insist that every occurrence of

an entity participates in the relationship, other enterprise rules allow occurrences of an

entity to exist independently. The terms obligatory and non-obligatory will be used to

distinguish between these situations.

102 Data Analysis for Database Design

Suppose the enterprise rules for an Employs relationship between Department and

Employee are:

‘Every employee must be employed within a department.’

‘A department may exist even if it has no employees.’

(The second rule would allow information to be held about a new department, even

though it does not yet have any employees.) We will say that the participation of

Employee in the Employs relationship is obligatory, in other words an Employee

occurrence must participate in (at least one) Employs relationship occurrence.

Conversely, the participation of Department in the Employs relationship is non-

obligatory, in other words a Department occurrence can exist without participating in

an Employs relationship occurrence.

For a relationship between two entity types, there are then four possible

combinations of participation conditions, as illustrated in Fig. 9.4. The diagrams also

show the notation which will be used to represent participation on an entity-

relationship type diagram. A blob inside a stripe on an entity symbol means that the

entity’s participation is obligatory; a blob outside an entity symbol means that the

entity’s participation is non-obligatory. Knowledge of the participation conditions of

entities is important, as it may influence the design of data models and schemas.

Fig. 9.4 Possible combinations of participation conditions

(a) Department participation obligatory; Employee participation obligatory

(b) Department participation non-obligatory; Employee participation non-obligatory

(c) Department participation non-obligatory; Employee participation obligatory

(d) Department participation obligatory; Employee participation non-obligatory

Properties of relationships 103

A department must employ at least one employee

(a) An employee must be employed by at least one department

A department need not employ any employees

(b) An employee need not be employed by any department

A department need not employ any employees

(c) An employee must be employed by at least one department

A department must employ at least one employee

(d) An employee need not be employed by any department