|
Databases design : stage 3
Construction of the diagram of data
The last stage consists in connecting the entities
and associations found in the preceding stage in order to build
the general structure of the data : the diagram of data.
------------------------------------
The schema obtained by this method is the conceptual diagram of
data.
------------------------------------
Linking entities
The entities are linked together with the attributes
they share. The types of relations authorized to connect two entities
(associations) belong to the two following categories :
- Relation of type 1-1: to one element of the souce
set one can make correspond one element of the goal set and
reciprocally.
- Relation of type 1-m: to one element of the souce
set one can make correspond several (many)elements of the goal
set.
------------------------------------
With this method, the only possible relations between entities
and relationships are m-1 relations, thus functional dependencies.
So, this stage aims to find the functional dependencies existing
between entities and relationships: the building of the indexes
graph.
------------------------------------
To determine the relations between entities (associations), it is
thus necessary to examine the number of occurrences (cardinal) of
each entity which intervenes in the relation.
In our example, we have the following relations:
|
Source entity
|
Goal entity
|
Relation on attribute
|
Type of relation
|
|
SEXE
|
ETUDIANT
|
Cdsexe
|
1-m
|
|
ETUDIANT
|
NOTES
|
Numetu
|
1-m
|
|
MATIERE
|
NOTES
|
Numat
|
1-m
|
|
ENSEIGNANT
|
MATIERE
|
Numens
|
1-m
|
If we look at the first line of the table, we said
that the relation between SEXE and ETUDIANT was of type 1-m.
Indeed, in the entity SEXE, a particular value of the attribute
Cdsexe (H or F) will be present only once (1). On the other hand,
in the entity ETUDIANT, the same value of the Cdsexe attribute could
be present several times( m ), as many time as there are students
of this sex.
The diagram
of data
To have a synthetic vision of the structure of the
database ( the diagram of data) we use a figure of the following
type :
This representation is the diagram of the "STUDENTS"
database . It is the optimal structure of data of our problem :
maximum consistency and minimal redundancy.
Important remark for "purist"
------------------------------------
The above diagram is really the conceptual diagram of data,
not the logical diagram of data. In fact, relations m-1
represent the functional dependencies existing between entities
and relationships. The above schema must thus be view as
an image of the functional dependencies graph. It is, indeed,
the minimal cover of the graph.
With this method,
the building of the logical, then physical diagram is obvious:
Logical diagram: The functional dependencies between entities
and relationships become referential integrity constraints.
Physical diagram: Each entity/relationship become a table
------------------------------------
Previous stage
| DB Design
|
