Correction du regroupement B210 Bases de données

Sébastien Choplin

4 novembre 2003

Enoncé

Une société décide d'automatiser sa gestion de production et notamment de gérer l'utilisation de ses machines outils. L'analyse conceptuelle conduit aux spécifications suivantes :

A chaque machine sont associés :
- un identifiant ID_M,
- un type TY_M (plusieurs machines peuvent avoir le même type).
A chaque atelier sont associés :
- un identifiant ID_A,
- un nom NOM_A,
- le nombre de machines de l'atelier NB_A.
A chaque employé sont associés :
- un identifiant ID_E,
- un nom NOM_E,
- une qualification Q_E (niveau de qualification possédé par l'employé).
Tous les niveaux de qualification sont répertoriés et il leur correspond :
- un identifiant QL,
- un indice IND,
- la liste des types de machines (TY_M) sur lesquelles un employé possédant une qualification d'un niveau supérieur ou égal peut travailler,
- un quota QT indiquant le nombre d'heures d'intervention sur machine dues par un ouvrier possédant cette qualification.
Afin d'étudier la productivité des machines, pour chaque intervention d'un ouvrier on note :
- l'identifiant de la machine,
- l'identifiant de l'employé,
- la date de l'intervention DTI,
- la durée de l'intervention en minutes DI.
Une machine est située dans un et un seul atelier.
Un atelier est sous la responsabilité d'un seul employé.
Les employés ayant une fonction d'administratif n'effectuent pas d'intervention sur machine. A chaque employé correspond donc un attribut FCT qui a la valeur 'A' pour les administratifs.
Un employé ne peut travailler sur deux machines du même type situées dans des ateliers différents.
La date d'intervention permet l'établissement de récapitulatifs mensuels comme le calcul du temps de passage d'un ouvrier sur une machine d'un atelier donné pour un mois donné.
Un atelier comprend plusieurs machines.
Un ouvrier est susceptible de travailler sur plusieurs machines situées dans des ateliers différents.

Question : proposer un schéma relationnel 3NF de la base de données.

Correction

Dictionnaire des données

Il est plus commode de lister les données triées par ordre alphabétique des identifiants pour permettre de retrouver sa description rapidement. Il pourrait donc être plus élégant de choisir les identifiants de manière à les faire apparaître déjà groupés (en préfixant plutôt qu'en postfixant: A_ID, A_NOM, ...au lieu de ID_A, NOM_A, .... Mais pour 'coller' à l'énoncé nous garderons les identifiants proposés) (Table 1).

Tableau 1:Dictionnaire des données

N°	Identifiant	Description	Domaine
1	DI	durée d'intervention	date
2	DTI	date d'intervention	date
3	FCT	fonction de l'employé	caractère ('A' ou NULL)
4	ID_E	identifiant d'employé	entier
5	ID_M	identifiant de machine	entier
6	ID_I	identifiant d'intervention	entier
7	IND	indice de qualification	entier
8	NB_A	nombre de machines d'un atelier	entier
9	NOM_A	nom d'atelier	chaîne de caractères
10	NOM_E	nom d'employé	chaîne de caractères
11	QL	identifiant de qualification	chaîne de caractères
12	QT	nombre d'heures dûes sur machines	entier
13	TY_M	type de machine	chaîne de caractères

Remarque 1 Q_E n'est pas utilisé car QL désigne la même chose. Q_M n'est pas utilisé dans le dictionnaire des données car il apparaîtra comme dépendance fonctionnelle. ID_I est ajouté pour permettre de caractériser une intervention. ID_A a été supprimé car on va considérer que NOM_A suffit à identifier un atelier (et donc deux ateliers différents ne pourront pas porter le même nom).

Matrice de dépendances fonctionnelles

La matrice des dépendances fonctionnelles (Table 2) permet d'identifier de manière systématiques les dépendances fonctionnelles pour pouvoir construire facilement le graphe de ces dépendances et faire aparaître les éventuelles redondances ou dépendances issues de sources multiples. En colonne nous mettrons les sources des dépendances et en lignes les cibles (la dépendance Source → Cible signifie ``pour une donnée de type Source il ne peut exister qu'une donnée de type Cible). Dans la matrice, nous mettrons un 1 dans la colonne i et la ligne j s'il y la dépendance i → j. La relation de dépendance fonctionnelle étant reflexive (i → i est toujours vraie), pour ne pas allourdir la matrice de 1, nous mettrons juste *.

Tableau 2: Matrice de dépdendances fonctionnelles

	1	2	3	4	5	6	7	8	9	10	11	12	13
1	*					1
2		*				1
3			*	1
4				*		1			1
5					*	1
6						*
7							*				1
8								*	1
9					1				*
10				1						*
11				1							*		1
12											1	*
13					1								*

Pour vérifier qu'il n'y a pas de redondances ou de dépendances oubliées, on peut se poser les questions suivantes:

Y a-t-il une ligne de donnée non-source qui contient plusieurs 1 ?
Cela signifie qu'une des dépendances listées peut être obtenue par transitivité et doît donc être supprimée pour éviter la redondance qui entraîne des risques d'incohérence lors des modifications.
Y a-t-il une donnée non-source qui n'a aucun 1 dans sa ligne ?
Cela signifie que cette donnée est probablement cible d'une dépendance multiple (Source₁ + Source₂ → Cible), il faut donc faire apparaître cette source multiple dans la matrice en ajoutant une colonne Source₁ + Source₂ et en ajoutant la dépendance correspondante.

Graphe des dépendances fonctionnelles

Le graphe des dépendances (Figure 1) fonctionnelles ne fait que donner une représentation différente de la matrice, mais apporte un vision plus structurée de ces dépendances.

**Figure 1:** Graphe des dépendances fonctionnelles.

A ce niveau on peut vérifier s'il existe des dependances déductibles par transitivité: s'il existe plusieurs chemins pour aller d'un nœud du graphe à un autre. Remarque 2 On remarque qu'ici il y a deux chemins ayant même source et destination :

ID_I → ID_E → QL
ID_I → ID_M → TY_M → QL

mais en fait il n'y a pas de dépendance déductible par transitivité car ID_A → ID_E représente ``le responsable d'un atelier est un employé (et un seul)'', mais il n'y pas de corrélation avec l'utilisateur d'une machine dans l'atelier.

Modèle entité-association

Le passage des dépendances fonctionnelles au modèle entité-association (Figure 2) est relativement simple:

pour chaque donnée source on créé un entité avec pour attribut clef cette source,
chaque cible non-source est un attribut de l'entité correspondante,
pour chaque dépendendance entre sources on créé une association.

**Figure 2:** Modèle entité-association.

Remarque 3 Les dépendances fonctionnelles ayant des sources mutliples engendrent des associations n>-aire; il n'y en a pas dans cet exemple.

Modèle relationnel

Pour passer du modèle entité-association au modèle relationnel, il suffit d'appliquer ces règles:

chaque entité engendre une relation,
chaque association ayant l'une des cardinalités max. inférieures ou égales à 1 engendre une clef étrangère dans la relation de l'entité considérée
chaque association ayant ses cardinalités max. supérieures ou égales à 2 (de même toute relation n-aire avec n>=3), engendre une relation, les clefs des entités concernées se retrouveront en clefs étrangères dans cette relation et formeront la clef de la relation. Si cette association est porteuse de données, chaque donnée sera alors un attribut de la relation.

On peut noter par convention :

les clefs primaires soulignées
les clefs étrangères postfixées par #

Ce n'est qu'une convention pour y voir plus clair, il en existe d'autres.

: Qualification(QL,QT)
: Type(TY_M,Q_MIN#)
: Employe(ID_E,NOM_E,FCT)
: Atelier(NOM_A,NB_A,RESPONSABLE#)
: Machine(ID_M,NOM_A#,TY_M#)
: Intervention(ID_I,DTI,DI,ID_E#,ID_M#)

Remarque 4 Ici aucune association n'a engendré de relation car pour chacune d'entre elle il y a avait au moins l'une des cardinalités maximale à 1 sur l'une des entités concernées. Aucune n'était porteuse de données. Aucune clé primaire n'est composée de deux attributs, ce problème a une représentation relativement simple et ce schéma est donc en 3ème forme normale.

Description des schéma relationnels en SQL

CREATE TABLE Qualification (
 QL INTEGER,
 QT INTEGER NOT NULL,
 PRIMARY KEY(QT)
);

CREATE TABLE Type (
 TY_M VARCHAR(20),
 Q_MIN INTEGER,
 PRIMARY_KEY(TY_M),
 FOREIGN KEY(Q_MIN) REFERENCES Qualification
);

CREATE TABLE Employe (
 ID_E INTEGER AUTO_INCREMENT,
 NOM_E VARCHAR(20) NOT NULL,
 FCT   ENUM('A'),
 PRIMARY KEY(ID_E)
);

CREATE TABLE Atelier (
 NOM_A VARCHAR(20) NOT NULL,
 NB_A  INTEGER NOT NULL,
 RESPONSABLE VARCHAR(20),
 PRIMARY_KEY(NOM_A),
 FOREIGN KEY(RESPONSABLE) REFERENCES Employe
);

CREATE TABLE Machine (
 ID_M INTEGER AUTO_INCREMENT,
 NOM_A VARCHAR(20),
 TY_M VARCHAR(20),
 PRIMARY_KEY ID_M,
 FOREIGN KEY(NOM_A) REFERENCES Atelier,
 FOREIGN KEY(TY_M) REFERENCES Type
);


CREATE TABLE Intervention (
 ID_I INTEGER AUTO_INCREMENT,
 DTI   DATE,
 DI    DATE,
 ID_E INTEGER,
 ID_M INTEGER,
 PRIMARY KEY(ID_I),
 FOREIGN KEY (ID_E) REFERENCES Employe,
 FOREIGN KEY (ID_M) REFERENCES Machine
)