Envoyé par
CinePhil
cela veut dire qu'un laboratoire ne travaille que sur un seul projet et que l'employé E travaillant pour le laboratoire L travaille implicitement sur le projet P
Hum... Rien n’empêche que le laboratoire L01 soit à la fois partie prenante dans les projets P01 et P02.
Reprenons l’ensemble S des dépendances fonctionnelles qui a été fourni :
DF1 : {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp}
DF2 : {NoEmp} -> {NomEmp}
DF3 : {NoEmp} -> {Adresse}
DF4 : {NoProj} -> {NomProj}
La relation suivante r1, valeur de la relvar (variable relationnelle) EMPLOYE, respecte manifestement S :
1 2 3 4 5
| NoEmp NoLab NoProj NomEmp NomProj Adresse
E007 L01 P01 Philippe Facturation Toulouse
E007 L02 P01 Philippe Facturation Toulouse
E008 L01 P02 Richard Prospection Rennes |
On pourrait en rester là, mais on peut profiter de l'occasion pour traiter des redondances et illustrer l'utilisation du théorème de Heath, l’engin à les éradiquer et à normaliser.
La relation r1 est redondante car on apprend deux fois que le projet P01 a pour nom Facturation, on apprend deux fois que l’employé E007 a pour nom Philippe et qu’il habite Toulouse : il n’y a pas à tortiller, on doit se dépêcher de récupérer dans notre arsenal l’engin à normaliser et éradiquer les redondances.
Allons-y. Reprenons l’en-tête de la variable EMPLOYE :
{NoEmp, NoLab, NoProj, NomEmp, NomProj, Adresse}
Du fait de la dépendance fonctionnelle DF4 : {NoProj} -> {NomProj}, on peut appliquer le théorème et décomposer EMPLOYE en deux relvars EMP1 et PROJET :
EMP1 {NoEmp, NoLab, NoProj, NomEmp, Adresse}
PROJET {NoProj, NomProj}
La relvar PROJET n'est pas décomposable et on peut prendre le temps de montrer qu'elle respecte la BCNF. Par contre, la relvar EMP1 est à déverminer, il faut poursuivre le processus de décomposition.
Dans un 2e temps, les dépendances fonctionnelles DF2 : {NoEmp} -> {NomEmp} et DF3 : {NoEmp} -> {Adresse} peuvent être réunies (règle de composition inférée des axiomes d'Armstrong et signalée par Seabs) :
DF5 : {NoEmp} -> {NomEmp, Adresse}
Du fait de cette dépendance fonctionnelle, on applique le théorème de Heath pour décomposer EMP1 en deux autres relvars, EMP et EMP_LAB :
EMP {NoEmp, NomEmp, Adresse}
EMP_LAB {NoEmp, NoLab, NoProj}
A ce stade, la relation r1 est ainsi décomposée :
1 2 3 4 5
| PROJET NoProj NomProj EMP NoEmp NomEmp Adresse EMP_LAB NoEmp NoLab NoProj
P01 Facturation E007 Philippe Toulouse E007 L01 P01
P02 Prospection E008 Richard Rennes E007 L02 P01
E008 L01 P02 |
Cette fois-ci les redondances ont disparu et l'on peut montrer que les décompositions respectent la BCNF, et ceci en ayant utilisé à fond le théorème de Heath, lequel nous permet d'affirmer que la décomposition de la relation r1 est sans perte, c'est-à-dire qu’on vérifie :
EMPLOYE = PROJET JOIN EMP JOIN EMP_LAB.
Confirmation par un exemple en SQL (SQL Server en l’occurrence) :
Table des employés :
1 2 3 4 5 6 7
| CREATE TABLE EMP
(
NoEmp CHAR(4) NOT NULL
, NomEmp VARCHAR(16) NOT NULL
, Adresse VARCHAR(16) NOT NULL
, PRIMARY KEY (NoEmp)
) ; |
Table des projets :
1 2 3 4 5 6
| CREATE TABLE PROJET
(
NoProj CHAR(4) NOT NULL
, NomProj VARCHAR(16) NOT NULL
, PRIMARY KEY (NoProj)
) ; |
Table des affectations dans les labos :
1 2 3 4 5 6 7 8 9
| CREATE TABLE EMP_LAB
(
NoEmp CHAR(4) NOT NULL
, NoLab CHAR(4) NOT NULL
, NoProj CHAR(4) NOT NULL
, PRIMARY KEY (NoEmp, NoLab)
, FOREIGN KEY (NoEmp) REFERENCES EMP
, FOREIGN KEY (NoProj) REFERENCES PROJET
) ; |
Caveat : Les attributs participant aux clés devraient être du type INT, mais CHAR permet de faciliter la lecture dans le cas cet exemple.
Quelques données :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| INSERT INTO EMP (NoEmp, NomEmp, Adresse) VALUES ('E007', 'Philippe', 'Toulouse') ;
INSERT INTO EMP (NoEmp, NomEmp, Adresse) VALUES ('E008', 'Richard', 'Rennes') ;
SELECT * FROM EMP ;
INSERT INTO PROJET (NoProj, NomProj) VALUES ('P01', 'Facturation') ;
INSERT INTO PROJET (NoProj, NomProj) VALUES ('P02', 'Prospection') ;
SELECT * FROM PROJET ;
INSERT INTO EMP_LAB (NoEmp, NoLab, NoProj) VALUES ('E007', 'L01', 'P01') ;
INSERT INTO EMP_LAB (NoEmp, NoLab, NoProj) VALUES ('E007', 'L02', 'P01') ;
INSERT INTO EMP_LAB (NoEmp, NoLab, NoProj) VALUES ('E008', 'L01', 'P02') ;
SELECT * FROM EMP_LAB ; |
Jointure naturelle des trois tables :
1 2 3
| SELECT x.NoEmp AS NoEmp, NoLab, z.NoProj AS NoProj, NomEmp, NomProj, Adresse
FROM EMP AS x JOIN EMP_LAB AS y ON x.NoEmp = y.NoEmp
JOIN PROJET AS z ON y.NoProj = z.NoProj ; |
=>
1 2 3 4 5
| NoEmp NoLab NoProj NomEmp NomProj Adresse
E007 L01 P01 Philippe Facturation Toulouse
E007 L02 P01 Philippe Facturation Toulouse
E008 L01 P02 Richard Prospection Rennes |
Merci M. Heath !
Partager