A relationship is an association between entities that has a meaning in a given context, which needs to be recorded.
In the context of our university example, one relationship that is of interest is that between a member of staff and a student being counselled. Figure 14 shows one way of representing some of this data.
In this diagram, the lines show that the staff member with StaffNo 3158 is associated with students S01 and S07 for the purpose of counselling. The diagram also shows counselling relationships between staff member 5212 and students S02, S05, S09 and S10.
Drawing lines on a diagram to show all counselling relationships would make the diagram impossible to read, particularly in practical situations where there are many staff and students. So, in the same way that a data model includes entity types, which define the properties common to a collection of entities, we can define the properties common to a collection of relationships, that is, the type of the relationship. In this example, the most basic property is that of relating entities of the Staff entity type to the entities of the Student entity type for the purpose of counselling: we shall name this relationship Counsels and depict it as follows (see Figure 15).
As it stands, this diagram should only be interpreted as ‘there is a relationship, relating to counselling, between staff and students’. But we can do better than this. There are certain kinds of useful information about relationships that can be added to such a diagram. From the problem description, we know that a member of staff can counsel more than one student so, reminiscent of the many lines between staff member 5212 and the set of students in Figure 15, we place a ‘crow's foot’ on the line representing the relationship to indicate this fact, as shown in Figure 16.
It is important to remember that this notation is directional. Putting the crow's foot at the Student end of the relationship line means that, reading from left to right on our diagram, one member of staff may be related (for the purpose of counselling) to many students.
Conversely, we know that a single student has only one counsellor. The fact that an occurrence of one entity type is related to only one occurrence of another entity type is denoted, on the diagram, by not having a special symbol at the end of the relationship line (as shown in Figure 16). So, reading from right to left, we find that an occurrence of the Student entity type is related, for counselling, to only one occurrence of the Staff entity type.
This additional information denoted by the presence or otherwise of crow's feet at the ends of the line denoting a relationship, is known as the degree of the relationship. In our example, the Counsels relationship is said to be one-to-many (also written 1 : n) from Staff to Student (the degree is directional). We could equally well have said that the Counsels relationship is many-to-one from Student to Staff.
The degree of a relationship is not an observation of relationship occurrences as they exist at some time, but it reflects an important part of the meaning of the relationship which holds for all possible occurrences. Thus the 1 : n Counsels relationship asserts that a Staff occurrence may be associated with many Student occurrences, and a Staff occurrence may be associated with no more than one Student occurrence via the Counsels relationship.
Note that the assertion that a Staff occurrence may be associated with many Student occurrences indicates what is possible, but does not constrain the allowable associations between two entity occurrences because ‘many’ can be any number, including zero. In contrast, the assertion that a Staff occurrence may be associated with no more than one Student occurrence is a constraint because it forbids any association of a Student occurrence with more than one Staff occurrence.
The degree of a relationship is not always one-to-many; two other degrees exist:
one-to-one (written 1 : 1) in which precisely one occurrence of an entity type may be related to precisely one occurrence of another entity type;
many-to-many (m : n) in which many occurrences of one entity type may be related to many occurrences of another entity type, and vice versa.
Another aspect of the meaning of a relationship that can be displayed on an E–R diagram is known as a participation condition. An entity type has a participation condition of optional if entities of that type need not be involved in an occurrence of the relationship. Staff provides an example of optional participation in the Counsels relationship because not every member of Staff need counsel a student. Conversely, a participation condition of mandatory means each entity of a type must be involved in an occurrence of the relationship. Student provides an example of mandatory participation in the Counsels relationship because every student must have a counsellor. An optional participation condition is depicted as a circle (an ‘O’) and a mandatory participation is depicted with a solid black circle (a black ‘blob’). Note that a participation condition that is optional is not a constraint because it does not forbid anything; it merely states what may be the case. In contrast, a participation condition that is mandatory for a relationship is a constraint because an entity of the type of relationship concerned cannot exist without being involved in an occurrence of the relationship.
With the notational conventions used on E–R diagrams, it is not possible to show more complex constraints such as each student occurrence must not be enrolled on courses having a total of more than 180 credit points. Therefore, we need a written ‘Constraints’ section to be part of the data model, in addition to the E–R diagram. In a similar vein, the ‘Assumptions’ part of the data model records any other aspect of the modelling that may not be otherwise explicitly represented in the model, and it enables the detail of the E–R diagram and entity types to be understood.
Figure 17 is a data model for the data requirements of a hospital. Examine the model and answer the following questions.
Ward (WardNo, WardName)
Patient (PatientId, PatientName)
Nurse (StaffNo, NurseName)
Doctor (StaffNo, DoctorName, Position, Specialism)
Team (TeamCode, TelephoneNo)
Treatment (StaffNo, PatientId, StartDate, Reason)
Drug (DrugCode, DrugName)
Prescription (PrescriptionNo, Quantity, DailyDosage)
A doctor responsible for a patient must have a position of consultant.
A doctor heading a team must have a position of consultant.
A doctor providing treatment for a patient must be from the same team as the consultant who is responsible for the patient.
A consultant belongs to a team via the HeadedBy relationship, whereas other doctors belong to a team via the ConsistsOf relationship.
Two nurses involved in an occurrence of the Supervises relationship must be assigned to the same ward.
The attribute Position (of Doctor) may have a value of Consultant, Registrar or House Officer.
The attribute Specialism (of Doctor) only has a value if the value for the Position attribute is Consultant.
Only the details of a patient's current stay in hospital are recorded (that is, only as an inpatient).
(a) Explain (in words) the OccupiedBy relationship type between Ward and Patient.
(b) Why are there two relationship types between Team and Doctor?
(c) Describe the relationship type named Supervises between the entity type Nurse and itself.
(d) What do you understand by the entity type Team?
(e) How many nurses can staff a ward?
(f) Can a patient receive more than one drug at a time?
(a) Patients occupy wards. A ward can be occupied by many patients (although it may be empty). An occurrence of a Patient type must occupy one and only one ward.
(b) There are two relationship types between Team and Doctor because there are two different associations that need to be recorded: one dealing with the composition of a team and the other dealing with the head of a team. The HeadedBy relationship tells us about the fact that a single occurrence of Doctor heads a team, but every doctor need not head a team. A team need not have a head. The ConsistsOf relationship type says that a team consists of many doctors, although a team may exist that does not have any doctors in it! Every doctor must belong to a team.
Constraint 4 states that the HeadedBy relationship involves only consultants whereas the relationship type ConsistsOf involves only doctors that are not consultants. Thus a consultant can only be a member of a team if that consultant is the head of the team. You may feel that this is somewhat bizarre, but that is one of the advantages of building data models – they can reveal mistakes in thinking which one would like to avoid prior to the design and implementation of software.
(c) The Supervises relationship says that several nurses may be supervised by another nurse. There is no compulsion for nurses to have a supervisor. Constraint 5 says that two nurses involved in an occurrence of the Supervises relationship must be assigned to the same ward.
(d) The Team entity type consists of zero, one or more doctors. If a team has a head, the head of the team must be a doctor with the position of consultant.
(e) A ward can be staffed by many nurses and there must be at least one nurse.
(f) Yes, a patient can receive more than one drug. The Receives relationship between Patient and Treatment says that an individual patient must have at least one, possibly more, treatments. A treatment requires at least one, possibly more, prescriptions and a prescription is for a drug. Thus different drugs will require different prescriptions, but a single treatment is permitted to have several prescriptions.