The Hospital relational model

Recursive relationships

In the diagram above Supervises is a recursive relationship. A heading of:

Nurse (StaffNo, NurseName, WardNo, SupervisorNo)

will allow for the primary/foreign key mechanism where SupervisorNo is the foreign key and StaffNo is the primary key.

Both of these will have to be defined in the same domain to give the same values. This is due to the fact that the primary/foreign keys cannot take the same attribute name. These must be unique within a relation.

A value of StaffNo will identify only one tuple of Nurse, but the same SupervisorNo may occur in numerous Staff tuples.

Representing 1:1 relationships

In a 1:1 relationship we need to accomplish two things:

1. Decide which relation will have the attribute declared as the foreign key.
2. Declare that same attribute in the corresponding relation as an alternate key.

Representing m:n relationships

In the diagram above the relationship is m:n i.e. there are many instances of Staff examining many instances of Course.

A relation has to be created to represent the two entity types. This relation will have a primary key consisting of both primary keys from the original two relations.

• Course (CourseCode, Title, Credit
• Staff (StaffNo, Name, Region)
• Examines (StaffNo, CourseCode)

Introducing this third relation has the effect of decomposing the m:n relationship into two 1:n relationships as in the diagram below.

This third relation is termed an intersection relation and allows use of the primary/foreign key mechanism.

Comments, suggestions, ideas to
Stuart Banner