Data Analysis for Database Design, 3rd Edition



Skeleton entity-relationship models

 ,QWURGXFWLRQ

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 define 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 first 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 define a simple high-level,

implementation-independent model which can be used as a basis for further

refinement; 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 identifiers.

 5HSUHVHQWDWLRQRIUHODWLRQVKLSV

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 identified 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.

 3DUWLFLSDWLRQREOLJDWRU\IRUERWKHQWLW\W\SHV)LJ

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)

Employee

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).

Questions

1. Name two candidate identifiers for the Employee table type defined in Fig. 12.1.

(1 min)

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)

 3DUWLFLSDWLRQREOLJDWRU\IRURQO\RQHHQWLW\W\SH)LJD

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

11