super-type entities

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format.

Super-type Entities.
The super-type entities and sub-type entities represent part of a system that has a recognizable sub-set of occurrences of an existing entity type. The sub-set(s) are known as entity sub-types, the original entity type being the super-type. All attributes and relationships of the super-type must belong to all of its sub-types. However, some attributes and relationships of the sub-type are additional to those of the super-type. Sub-types are usefully defined where an identifiable group of an entity occurrence has attributes additional to those of the super-type. They can also be useful if a sub-type can be seen to be involved in discrete business roles. In a hypothetical airline information system the entity 'customer' is used to identify anyone who has expressed an interest in the company - having, for example, enquired about flights, requested a brochure or responded to marketing information. In this system 'passenger' could be a sub-entity of the super-entity customer. The passenger would have all of the attributes of the customer entity, plus additional attributes that would hold information relating to ticket bookings. In this example the relationship is only mandatory from the passenger end. A passenger must be a customer, whereas a customer may or may not be a passenger.

Validating Super-type Entities.
Let us examine the airline example in more detail. It has already been stated that one-to-one relationships are not usually permitted on the final data model and that unless they are valid super-type and subtype entities, they should be resolved into a one-to-many relationship. The question is 'Is this a valid super and sub type entity?' Think about this relationship carefully and try to resolve it. When you have drawn your own solution for this, go to the next page to see the suggested solution.
Two things point to customer being the master entity:
1) Customer exists first.
2) Customer is the 'optional' end.
In this example a person becomes a customer at the point of enquiry. If they then buy a ticket they become a passenger. Therefore, a customer could be a passenger more than once, by flying on more than one occasion. In practice this one-to-one relationship is really a one-to-many relationship, as shown above.

Resolving the Data Model.
This is the unresolved data model for the hospital example that was developed earlier in the course.
“Each Nurse must work on one and only one Ward
Each Ward must be staffed by one or more Nurses.”
“Each Ward may treat one or more Patients
Each Patient must reside in one and only one Ward.”
“Each Medical Record belongs to one and only one Patient
Each Patient has one and only one Medical Record.”
“Each Patient may receive one or more Drugs
Each Drug may be given to one or more Patients.”
It shows a one-to-one relationship and a many-to-many relationship, both of which you have already resolved through guided exercises.
Now, draw the final data model for the hospital study, with these relationships resolved. When you have drawn your own solution for this, go to the next page to see the suggested solution.

Highlighting Multiple Relationships.
It is not uncommon for two entities to share more than one relationship. This is called a multiple relationship and where these occur the naming of the relationship statements is particularly important. Multiple relationships are drawn independently with one line representing each. This diagram represents one small area of a personnel system.
It represents the relationship:
”Each manager may assess one or more employees
Each employee must be assessed by only one manager.”
Add the following relationship to the diagram.
“Each manager is responsible for one or more employees Each employee is responsible to only one manager.”
Remember, multiple relationships are drawn independently - one line showing each. The relationship link phrases' may need to be placed adjacently (rather than on opposite sides of the relationship line) as dictated by the available space.

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format. TOP