Employé de laboratoire

description d'un employé travaillant sur un projet d'un laboratoire.
Employé ( N°Emp, N°Lab, N°Proj, NomEmp, NomProj, adresse)
avec les dépendances fonctionnelles suivantes:
(N°Emp, N°Lab) → N°Proj
N°Emp → NomEmp
N°Emp → adresse
N°Proj → NomProj

est ce que cette DF est e FNBC????

Et quel est ta réponse à cet exercice qui t'est donné ?

Bonjour,

Pour t'aider la réponse est non.

Maintenant, tu essaies de démontrer pourquoi.

Si tu as des difficultés, indiques tes nouvelles interrogations.

Sur ce forum, il y a des intervenants qui ont toutes les compétences pour répondre à tes questions, ce qui n'est pas mon cas.

Bon courage

il existe la transisitvité en plus il existe des DF indirectes
moi je dirais non à cause de

(N°Emp, N°Lab) → N°Proj

N°Proj → NomProj


pour une information : la clé est N°Emp, N°Lab?????????,

en plus elle n'est pas en FN3(transitivité) et non en 2FN(une partie de la clé detrmine un attribut)

elle n'est en FN1

Le Maître du Relationland, fsmrel, aura toutes les compétences pour corriger ta réponse mais effectivement, selon moi et ma petite maîtrise du sujet, et plutôt de manière intuitive, la présence de "NomProj" est une faute de modélisation.

Il me semble qu'il y a aussi un autre problème si ta description de l'exercice est bien conforme à l'énoncé :

Citation:

---

description d'un employé
(N°Emp, N°Lab) → N°Proj
N°Emp → NomEmp

---

S'il s'agit bien de la description d'un employé, il est naturel d'y trouver son nom qui est une propriété intrinsèque de l'employé. Mais alors si le couple (N°Emp, N°Lab) détermine le numéro du projet, le numéro du projet ne doit pas figurer dans cette relation car 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.

Mais, pas du tout habitué à ce genre d'exercice, peut-être que je réfléchis trop en terme de MCD, de MLD et de futures tables et que je m'égare dans la réponse à cet exercice théorique ?

merci
voici l'ennoncé du problème:
http://www.hec.unil.ch/gcampono/teac...malisation.pdf

pour Exo 1 C

Bonjour,

Je vais faire en fonction de mes possibilités, le spécialiste @fsmrel ne manquera pas de corriger si je dis des idioties.

La relation est :
Employé ( N°Emp, N°Lab, N°Proj, NomEmp, NomProj, adresse)

Pour simplifier, je transforme en Employe (A, B, C, D, E, F)

Avec les dépendances fonctionnelles
DF01 {A, B} -> {C}
DF02 {A} -> {D}
DF03 {A} -> {F}
DF04 {C} -> {E}

Il convient d'abord d'éliminer les redondances. La DF04 est inférée de DF01 (axiomes d'Armstrong)

1 {A, B} -> {C} (donnée)
2 {C} -> {E} (donnée)
3 {A, B} -> {E} (Transitivité)

Nous aurons
DF01 {A, B} -> {C, E} (composition)
DF02 {A} -> {D, F} (Composition)

Pour permettre d'atteindre FNBC, il est nécessaire de décomposer notre relation ainsi

R1 (A, D, F) avec {A} surclé non réductible et {D} et {F} sont dépendants de {A}

R2 (A, B, C, E) avec {A, B} surclé non réductible {C} et {E} sont dépendants de {A, B}

Ces deux relations sont bien FNBC

Par jointure R1 et R2, il sera possible de recomposer Employé

Un peu d'indulgence de la part des correcteurs, je suis également en apprentissage.

Fait l'effort de lire cet très excellent article de @fsmrel, tu auras toutes les explications. Après, il faut consacrer beaucoup de temps pour tout comprendre.

http://fsmrel.developpez.com/basesre...?page=sommaire

Bon courage

Bonjour,

Citation:

---

Envoyé par seabs

Un peu d'indulgence de la part des correcteurs, je suis également en apprentissage.

---

Nous vous souhaitons la bienvenue au pays des relations et vous félicitons pour votre entreprise.

Ne vous inquiétez pas, c’est en forgeant qu’on devient forgeron. :D

Je vais répondre à sky88 et commenterai ensuite votre message.

Citation:

---

Envoyé par sky88

est ce que cette DF est e FNBC????

---

Ce ne sont pas les DF qui ont à respecter la FNBC, mais les variables relationnelles (tables en SQL). Si l’on reprend une définition de la FNBC (ou BCNF), par exemple celle de Zaniolo :

Soit R une relvar, X un sous-ensemble d'attributs de l'en-tête de R et A un attribut de cet en-tête. R est en forme normale de Boyce-Codd (BCNF) si et seulement si, pour chaque dépendance fonctionnelle X -> {A} qui doit être vérifiée par R, au moins une des conditions suivantes est satisfaite :

1. A est un élément de X (cette dépendance fonctionnelle est donc triviale).
2. X est une surclé de R.

Pour que la relvar EMPLOYE respecte la BCNF, il faut montrer que le déterminant (partie gauche) de chacune des dépendances fonctionnelles qui sont données constitue une clé candidate (une clé candidate est une surclé irréductible).

Les DF qui sont données sont les suivantes :

DF1 : {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp}
DF2 : {NoEmp} -> {NomEmp}
DF3 : {NoEmp} -> {Adresse}
DF4 : {NoProj} -> {NomProj}

Les déterminants correspondants sont les suivants :

Det1 : {NoEmp, NoLab}
Det2 : {NoEmp}
Det3 : {NoProj}

Affirmer par exemple que le déterminant Det2 : {NoEmp} constituerait une clé candidate, ça serait d'abord prouver que chacune des DF suivantes est vérifiée :

DF2 : {NoEmp} -> {NomEmp}
DF3 : {NoEmp} -> {Adresse}
DF4 : {NoEmp} -> {NoEmp}
DF5 : {NoEmp} -> {NoLab}
DF6 : {NoEmp} -> {NoProj}
DF7 : {NoEmp} -> {NomProj}

DF2 et DF3 sont vérifiées parce qu’elles sont données. DF4 est vérifiée parce qu’elle est triviale. DF5, DF6 et DF7 ne sont pas vérifiées, en effet elles ne sont ni données ni inférables à partir des axiomes et règles d’Armstrong (je vous laisse le soin de le démontrer, en utilisant par exemple l’algorithme du seau (plus exactement algorithme de Bernstein).

Comme il existe des DF dont le déterminant ne constitue pas une clé candidate de la relvar EMPLOYE, celle-ci viole la FNBC.

A titre d’exercice (salutaire), vous pouvez démontrer que le déterminant {NoEmp, NoLab} de la DF donnée ci-dessous constitue une clé candidate :

{NoEmp, NoLab} -> {NoProj, NomProj, NomEmp}

Une fois de plus, vous pouvez à cet effet utiliser le très précieux algorithme du seau.

Citation:

---

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 :

Code:

---

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 :

Code:

---

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 :

Code:

---

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 :

Code:

---

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 :

Code:

---

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 :

Code:

---

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 :

Code:

---

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 ;

---

=>

Code:

---

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 ! :P

Il y a quand même quelque chose qui me gène, mais c'est vrai que j'avais basé ma réponse sur le premier message dont la DF1 était mal retranscrite par rapport à l'énoncé réel.

La DF du message #1 était :

Citation:

---

(N°Emp, N°Lab) → N°Proj

---

Alors que la DF réelle de l'énoncé est :

Citation:

---

DF1 : {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp}

---

Je ne sais pas si ça change quelque chose.

Il en résulte pour toi cette table :

Citation:

---

Envoyé par fsmrel

Code:

---

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

---

---

Puisque la clé primaire est le couple {NoEmp, NoLab} Un employé ne peut pas figurer deux fois dans cette table en étant affecté au même labo.
Comme il y a une clé étrangère référençant le projet sur lequel l'employé travaille pour ce labo, un employé ne peut pas travailler pour plusieurs projets du labo.

C'est en ce sens que j'avais compris qu'un employé affecté à labo déterminait automatiquement le projet sur lequel il travaillait et que donc le laboratoire ne travaillait que sur un seul projet mais en fait, tu as démontré qu'un autre employé affecté au même labo peut travailler sur un autre projet.

Citation:

---

Envoyé par CinePhil

La DF du message #1 était :

Citation:

---

(N°Emp, N°Lab) → N°Proj

---

Alors que la DF réelle de l'énoncé est :

Citation:

---

DF1 : {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp}

---

Je ne sais pas si ça change quelque chose.

---

Effectivement, comme sky88 n’avait pas fourni l’ensemble des dépendances fonctionnelles, om pouvait avoir des conclusions diverses et (a)variées. Mais maintenant qu’on a toutes les billes, il n’est pas inintéressant de travailler un peu les axiomes d’Armstrong...

En vertu de la règle de décomposition, de {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp} on infère {NoEmp, NoLab} -> {NoProj}. Autrement dit, que l’on s’appuie sur la 1re ou la 2e de ces deux dépendances fonctionnelles, on ne peut quand même pas en tirer la conclusion qu’« un laboratoire ne travaille que sur un seul projet ».

Citation:

---

Envoyé par CinePhil

Puisque la clé primaire est le couple {NoEmp, NoLab} Un employé ne peut pas figurer deux fois dans cette table en étant affecté au même labo.

---

Exact. La paire (et non pas le couple) {NoEmp, NoLab} est effectivement clé (candidate ou primaire, peu importe, c’est une question de contexte relationnel ou SQL). Par conséquent, il est interdit qu’un employé donné travaille pour un même labo sur deux projets différents, mais il est bien sûr permis que deux employés puissent travailler pour un même labo sur le même projet ou des projets distincts.

Cela dit, il n'est pas inintéressant de prouver que {NoEmp, NoLab} est clé candidate, je dirais même que c'est un exercice salutaire. Le plus simple est d'utiliser l’algorithme du seau que j’ai évoqué dans les messages précédents, simple d’emploi mais d’une efficacité sans pareille :

En début d’algorithme, on jette {NoEmp, NoLab} dans le seau, appelé plus formellement fermeture F+ de {NoEmp, NoLab}. Grâce à la dépendance fonctionnelle {NoEmp, NoLab} -> {NoProj, NomProj, NomEmp} (délivrée un peu trop tardivement par sky88...), puisque le déterminant {NoEmp, NoLab} de cette DF est dans le seau, on alimente celui-ci avec le dépendant {NoProj, NomProj, NomEmp} de la DF : F+ est alors égale à {NoEmp, NoLab, NoProj, NomProj, NomEmp}. De la même façon, du fait de la dépendance fonctionnelle {NoEmp} -> {Adresse}, puisque le déterminant {NoEmp} de cette DF fait partie de F+, on fait tomber dans le seau le dépendant {Adresse} de la DF : F+ est alors égale à {NoEmp, NoLab, NoProj, NomProj, NomEmp, Adresse}.

On constate que tous les attributs de l’en-tête de la relvar sont tombés dans le seau et il s’ensuit que son contenu initial {NoEmp, NoLab} est clé candidate (donc clé primaire en SQL).

J'ai bien dit clé candidate et non pas seulement surclé, car {NoEmp, NoLab} est irréductible et on le prouve :

Le sous-ensemble strict {NoEmp} de {NoEmp, NoLab} a pour fermeture {NoEmp}+ : {NoEmp, NomEmp, Adresse} et ne constitue pas une clé candidate, car les attributs NoLab, NoProj et NomProj ne font pas partie de {NoEmp}+. Même chose pour le sous-ensemble strict {NoLab} de {NoEmp, NoLab} dont la fermeture {NoLab}+ est égale à {NoLab}, ce qui est pour le moins insuffisant.

Hello seabs !


Voyons donc voir...

Citation:

---

Envoyé par seabs

La DF04 est inférée de DF01 (axiomes d'Armstrong)

---

Que nenni, car elle est donnée. En revanche, vous avez correctement inféré {A, B} -> {E} par transitivité. Appelons DF05 cette DF. L’ensemble des DF devient :

DF01 : {A, B} -> {C}
DF02 : {A} -> {D}
DF03 : {A} -> {F}
DF04 : {C} -> {E}
DF05 : {A, B} -> {E}

Citation:

---

Envoyé par seabs

Nous aurons
DF01 {A, B} -> {C, E} (composition)
DF02 {A} -> {D, F} (Composition)

---

Ces DF sont effectivement produites par application de la règle de composition. Comme les noms DF01 et DF02 sont déjà utilisés, on va les renommer respectivement en DF06 et DF07. L’ensemble des DF devient :

DF01 : {A, B} -> {C}
DF02 : {A} -> {D}
DF03 : {A} -> {F}
DF04 : {C} -> {E}
DF05 : {A, B} -> {E}
DF06 : {A, B} -> {C, E}
DF07 : {A} -> {D, F}

Cela dit, dans l'intérêt de sky88 il faudrait expliquer ce qui vous a poussé à produire ces deux DF en particulier, et pas d’autres (en tout il y en a 4096...) Je sais bien, vous me direz que votre objectif est d’arriver aux deux relvars R1 et R2 moins redondantes, donc ayant plus de chances de respecter la FNBC, mais justement pourquoi R1 et R2 ?

Citation:

---

Envoyé par seabs

Pour permettre d'atteindre FNBC, il est nécessaire de décomposer notre relation ainsi

R1 (A, D, F) avec {A} surclé non réductible et {D} et {F} sont dépendants de {A}

R2 (A, B, C, E) avec {A, B} surclé non réductible {C} et {E} sont dépendants de {A, B}

Ces deux relations sont bien FNBC

---

Quod est demonstrandum !

Puisque j'en ai fait usage un peu plus tôt, reprenons la définition de la FNBC selon Zaniolo :

Soit R une relvar, X un sous-ensemble d'attributs de l'en-tête de R et A un attribut de cet en-tête. R est en forme normale de Boyce-Codd (BCNF) si et seulement si, pour chaque dépendance fonctionnelle X -> {A} qui doit être vérifiée par R, au moins une des conditions suivantes est satisfaite :

1. A est un élément de X (cette dépendance fonctionnelle est donc triviale).
2. X est une surclé de R.

La relvar R1 {A, D, F} respecte-t-elle la FNBC ? Les DF non triviales dont elle est dotée sont les suivantes et ce sont les seules (les attributs D et F ne participent à aucun déterminant de DF non triviale) :

DF02 : {A} -> {D}
DF03 : {A} -> {F}
DF07 : {A} -> {D, F}

Le déterminant de chacune de ces DF, c'est-à-dire {A} doit être surclé et c’est le cas (la DF : {A} -> {X} est vérifiée pour chaque attribut X de R1, y-compris {A} -> {A} qui est triviale). La relvar R1 respecte la FNBC.

La relvar R2 {A, B, C, E} respecte-t-elle la FNBC ? Les DF non triviales connues dont elle est dotée sont les suivantes (il y en a d’autres qu’il faudrait en principe ajouter à la liste, par exemple {A, B, C} -> {E}, {B, C} -> {E}, ...) :

DF01 : {A, B} -> {C}
DF04 : {C} -> {E}
DF05 : {A, B} -> {E}
DF06 : {A, B} -> {C, E}

Le déterminant de chacune de ces DF doit être surclé : ça n’est pas le cas de {C} -> {E}, car par exemple {C} ne pourra jamais déterminer {A} et ne peut donc pas être surclé : R2 viole la FNBC, il faut donc la décomposer.


De tout cela retenons que tant qu’une relvar viole la FNBC, il faut chercher à la décomposer. Les outils : le théorème de Heath (accompagné de la recommandation de Rissanen) en guise de pelle et l’algorithme du seau. Je ne parlerai pas ici des ensembles irréductibles de DF (alias couvertures minimales ou irréductibles) à mettre en évidence dans les cas tordus. Retenons encore qu’on peut être amené à violer la FNBC quand en contrepartie on est contraint à perdre une DF : on peut alors se limiter au respect de la 3FN, laquelle n’entraîne jamais de perte de DF.

=>

Bon début seabs, mais il faut prouver qu’une relvar est en FNBC en passant ses DF à la moulinette des théorèmes, par exemple celui de Zaniolo, et aller au bout. N’hésitez pas à poursuivre l’aventure et grimper dans les hauteurs éthérées...

Bonjour,

@fsmrel

Je vous remercie pour vos explications.

J'avais vu, lors de la présentation faite à @sky88 que R2 n'était pas en FNBC.

Entre comprendre et expliquer, il a quelques heures de travail à consacrer.

C'est vrai, que le nombre de DF devient vite décourageant 2 exposant 2n = 4 096 DF. Pour mes choix, j'ai fait à l'appréciation ce qui n'est pas très scientifique et dangereux dans certains situations et utiliser la technique du seau.

Un mélange pas encore maîtrisé.

Il me reste à étudier vos explications et je poserai des questions sur les points hors de ma compétence actuelle.

Avant d'atteindre les sommets, je vais rester dans la vallée.

A+