Skeleton entity-relationship models
Although an entity-relationship type diagram describes many of the important features
of a conceptual model, it does not show the attributes associated with the entity and
relationship types. This additional information can be represented conveniently in the
form of a set of fully-normalised table types. In principle, one could deﬁne a table type
for each and every entity and relationship type, but the number of table types so
generated would often be unnecessarily large.
In practice, an entity type is almost always represented by its own table type, whereas
most relationship types are not. On E-R type diagrams, we will use a suitable
annotation within the symbols « » (known as guillemets) to indicate the exceptions. We
will indicate the absence of a table for an entity type by the annotation «no table» and
the presence of a table for a relationship type by «table». If preferred, every entity type
or relationship type could be annotated with «table» or «no table», but we will annotate
only the exceptions.
As a ﬁrst step in deciding which table types are required in practice, it will be helpful
to develop criteria for deciding how different kinds of relationship can best be
represented. Keep in mind that the initial objective is to deﬁne a simple high-level,
implementation-independent model which can be used as a basis for further
reﬁnement; decisions made at this stage are not irrevocable.
For the sake of brevity the term entity-relationship will be abbreviated to E-R. The
combination of an E-R type diagram and its associated set of fully-normalised table
types will be called an E-R model. It will be convenient to start by building a skeleton
E-R model, that is one in which the table types contain only identiﬁers.
Suppose company cars are assigned to company employees on a 1:1 basis, that is no car
is shared between employees, and no employee has the use of more than one car.
Employees and cars are identiﬁed by employeeNo and carNo (the vehicle registration
number on the licence plate), respectively. This situation will be modelled by a 1:1
relationship Uses between the entity types Employee and Car. The way in which the
Uses relationship is represented will depend, at this stage, purely on the participation
conditions of the entity types.
Suppose every employee has a company car and every company car is used by an
employee. In this case, all the attributes of both entity types will be put into a single
table type. Car attributes (such as carNo, make and model) can be regarded simply as
additional attributes of Employee, and included in an Employee table type. The Uses
relationship is represented implicitly by the presence of employeeNo and carNo in
the same table type, rather than explicitly by a separate Uses table type; similarly there
is no need for a separate Car table type. The Car attributes are said to be posted into the
Employee table type.
Employee (employeeNo, . . . , carNo, . . .)
Other attributes Other attributes
of an employee (e.g. of a car (e.g.
employeeName) make, model)
Fig. 12.1 1:1 relationship. Participation obligatory for both entity types. Strictly speaking, there is no
distinction between ‘other attributes of an employee’ and ‘other attributes of a car’, since they are all
attributes of the same Employee table
The E-R model could be redesigned at this point to show only an Employee entity
type but, partly because it is a nuisance to re-draw the E-R diagram, and partly because
having done so there might be reasons for re-instating Car and Uses at a later stage, it is
simpler to take the view that Car and Uses still exist in the model even though they will
not be represented explicitly by separate table types. In Fig. 12.1, the absence of a table
type for Car is indicated by «no table». There is no need to annotate Uses, as the default
for relationship types is «no table» (see section 12.1).
1. Name two candidate identiﬁers for the Employee table type deﬁned in Fig. 12.1.
2. An alternative solution to the model of Fig. 12.1 would be to specify a Car table type
and put the «no table» annotation in Employee, but not Car. Which solution, if any, is
better? (1 min)
Suppose every company car is used by an employee, but not every employee has a
company car. The fact that Employee has non-obligatory participation in Uses means
130 Data Analysis for Database Design
employeeNo employeeName carNo make model
E1 Shaw W123DRY Ford Mondeo
E6 Keats Y469JWS Ford Focus
E3 Byron Y743PDJ Fiat Punto
Get Data Analysis for Database Design, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.