Discussion :

[Sebajuste]

Bonjour,

Je tiens à signaler, avant de commencer à exposer mon problème, que mes connaissances théoriques en matière relationnelle ne sont pas très développées.
Je demanderais donc votre indulgence quand à l'utilisation de mauvais termes


Je rencontre quelques difficultés à créer un schéma de base de données qui répondent aux problématiques suivante:

J'ai quatre entités A, B, C et D, dont les relations sont définit comme suit:

A -> B
A -> C
D -> B

que je représenterais ainsi en SQL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE A (
	id INTEGER,
	PRIMARY KEY(id)
);
 
CREATE TABLE B (
	id INTEGER,
	a_id INTEGER,
	PRIMARY KEY(id)
	FOREIGN KEY(a_id) REFERENCES A(id)
);
 
CREATE TABLE C (
	id INTEGER,
	a_id INTEGER,
	PRIMARY KEY(id)
	FOREIGN KEY(a_id) REFERENCES A(id)
);
 
CREATE TABLE D (
	id INTEGER,
	b_id INTEGER,
	PRIMARY KEY(id)
	FOREIGN KEY(b_id) REFERENCES B(id)
);

Je souhaite crée une nouvelle relation entre C et D

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE E (
	c_id INTEGER,
	d_id INTEGER,
	PRIMARY KEY(c_id, d_id),
	FOREIGN KEY(c_id) REFERENCES C(id)
	FOREIGN KEY(d_id) REFERENCES D(id)
);

Le problème, c'est que cette table permet des relations incohérentes. En effet, cette relation ne doit se limiter qu'aux entités partageant la même relation avec A.

Il faudrait donc rajouter un champ a_id, et modifier les clef étrangères en conséquences.
Seulement voilà, l'entité D ne dispose pas de relation direct avec A ! (sa relation avec A est indirect, et passe par B)

Du coups, je corrigerais ainsi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE E (
	c_id INTEGER,
	d_id INTEGER,
	a_id INTEGER,
	b_id INTEGER,
	PRIMARY KEY(c_id, d_id, a_id),
	FOREIGN KEY(c_id, a_id) REFERENCES C(id, a_id),
	FOREIGN KEY(d_id, b_id) REFERENCES D(id, b_id),
	FOREIGN KEY(b_id, a_id) REFERENCES B(id, a_id)
);

Mais là, je ne respecte plus la deuxième forme normale, car j'ai un élément non clef (b_id) qui dépend d'une partie de la clef (a_id).

Avez vous des suggestions pour à la fois limiter les incohérences fonctionnelles, et respecter au moins les trois premières formes normales ?

[alassanediakite]

Salut
Il faut plus d'explication sur ce point...

Le problème, c'est que cette table permet des relations incohérentes. En effet, cette relation ne doit se limiter qu'aux entités partageant la même relation avec A.

Il y incohérences entre

A -> B
A -> C
D -> B

et

Seulement voilà, l'entité D ne dispose pas de relation direct avec A ! (sa relation avec A est indirect, et passe par B)

on ne voit pas de transition entre A et D.
Pour une transition, il faut...
A -> B
B -> D
@+

[Sebajuste]

Effectivement, j'ai fais une erreur sur l'énoncé...

Les relations sont donc:
A -> B
A -> C
B -> D

Ceci dit, le SQL est juste.


Je vais essayer d'être plus clair avec un exemple où l'on souhaite attribuer des ressources d'entreprise à des salariés.
Avec:
A -> les entreprises
B -> des groupes de salariés
C -> des ressources propres à une entreprise
D -> les salariés

Ainsi, chaque groupe de salariés n'appartient qu'à une seule entreprise.
Chaque ressource n'appartient qu'à une seule entreprise
Chaque salarié n'appartient qu'à un seul groupe de salariés, et donc qu'à une seule entreprise.

Notre relation C permet d'affecter une ressource à un salarié, mais sans contrainte particulière on pourrait attribué une ressource d'une entreprise "Toto" à un salarié de l'entreprise "Tata".
Or il est nécessaire de faire en sorte que les ressources d'une entreprise ne puissent être attribuées qu'à des salariés de cette même entreprise.

[escartefigue]

Bonjour,

Votre question se rapporte à une contrainte d'intégrité fonctionnelle du modèle conceptuel. Il s'agit d'une CIF de type inclusion
D'un point de vue SQL, cela se traduit par une contrainte sur l'identifiant du salarié de la table issue de la relation entre "ressource" et "salarié" qui fera référence à l'identifiant du salarié issu de la relation entre "entreprise" et "salarié"

[Sebajuste]

Je ne suis pas sur d'avoir bien compris...

Dans ma table devant lier la ressource au salarié, il est évident que je vais rajouter des contraintes issue des tables ressources et salarié, comme suit:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE JOINTURE_RESSOURCE_SALARIE (
    id_salarie INTEGER NOT NULL,
    id_ressource INTEGER NOT NULL,
    PRIMARY KEY(id_salarie, id_ressource ),
    FOREIGN KEY(id_salarie) REFERENCES SALARIE(id),
    FOREIGN KEY(id_ressource ) REFERENCES RESSOURCE(id)
);

Mais ceci n'est pas suffisant, car je dois m'assurer que seuls les ressources et les salariés d'une même entreprises puissent être associé.

D'un point de vue SQL, cela se traduit par une contrainte sur l'identifiant du salarié de la table issue de la relation entre "ressource" et "salarié" qui fera référence à l'identifiant du salarié issu de la relation entre "entreprise" et "salarié"

Si je comprends bien, il faut également faire référencer les champs id_salarie et id_ressource à la table des entreprises.
Dans ce cas, je devrait obtenir ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE JOINTURE_RESSOURCE_SALARIE (
    id_salarie INTEGER NOT NULL,
    id_ressource INTEGER NOT NULL,
    id_entreprise INTEGER NOT NULL, 
    PRIMARY KEY(id_salarie, id_ressource ),
    FOREIGN KEY(id_salarie, id_entreprise  ) REFERENCES SALARIE(id, ???????),
    FOREIGN KEY(id_ressource, id_entreprise  ) REFERENCES RESSOURCE(id, id_entreprise ),
);

Mais que mettre à la place des ???, en effet, la table salarie ne dispose pas de clef vers l'entreprise, mais uniquement de la clef vers un groupe de salariés d'entreprise.

On pourrait donc faire ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE JOINTURE_RESSOURCE_SALARIE (
    id_salarie INTEGER NOT NULL,
    id_ressource INTEGER NOT NULL,
    id_entreprise INTEGER NOT NULL, 
    id_groupe_salarie INTEGER NOT NULL,
    PRIMARY KEY(id_salarie, id_ressource ),
    FOREIGN KEY(id_salarie, id_groupe_salarie) REFERENCES SALARIE(id, id_groupe_salarie ),
    FOREIGN KEY(id_groupe_salarie , id_entreprise  ) REFERENCES GROUPE_SALARIE(id, id_entreprise),
    FOREIGN KEY(id_ressource, id_entreprise  ) REFERENCES RESSOURCE(id, id_entreprise ),
);

La clef de cette relation, qui permet d'identifier de manière unique chaque tuple est bien (id_salarie, id_ressource)

Seulement, il y a une dépendance entre id_groupe_salarie et id_entreprise. J'ai donc un attribut non clef qui dépend d'un autre attribut non clef.
Si je ne dis pas de bêtise, dans ces conditions c'est la troisième forme normale qui n'est pas respectée.

[escartefigue]

Mais que mettre à la place des ???, en effet, la table salarie ne dispose pas de clef vers l'entreprise, mais uniquement de la clef vers un groupe de salariés d'entreprise.

Je vous ai dit une bétise : comme un salarié n'est embauché (a priori) que par une seule entreprise, on a donc une cardinalité 1,1 coté salarié, du coup la relation employé<=>salarié ne devient pas une table et l'attribut date d'embauche va migrer dans la table salarié.
Du coup il faut mettre la contrainte sur la table salarié qui hérite de la FK identifiant entreprise

Du moins si on se base sur un MCD comme suit :


Qui génère un MPD comme suit :


En effet je n'ai pas appliqué stricto sensu votre règle "Chaque salarié n'appartient qu'à un seul groupe de salariés, et donc qu'à une seule entreprise." car il me semble que la relation se fait bien entre l'entreprise et le salarié (via l'embauche) et que le groupe est une toute autre relation.

Note : je n'ai pas matérialisé la contrainte d'inclusion sur le MCD (un I cerclé reliant la relation "attribuer" et la relation "embaucher"), je ne trouve pas le mode op dans power AMC (j'avoue etre un peu rouillé après quelques années d'abstinence )

[Sebajuste]

J'ai pris un exemple d'entreprise / salarié car c'est un cas classique qui me venait à l'esprit, et qui me semblait plutôt bien représenter mon problème.
Aussi, il ne faut pas forcément prendre en compte de vraies contraintes du monde de l'entreprise (comme les dates d'embauche), ça n'a pas d'équivalent dans mon cas.


Concernant la solution proposée, séparer le groupe ne fait que déplacer le problème.
En effet, il faudrait dans ce cas rajouter une clef étrangère au groupe, pour rattacher un groupe à une entreprise. Mais dans ce cas, la clef étrangère en_id dépend de gr_id.
Le non respect de la forme normale est déplacée dans la table des salariés.


Bon, peut être que faire une entorse avec une redondance n'est pas si grave que ça. Mais dans la mesure où le programme est neuf, j'aurais aimé avoir un schéma propre :p


Concernant la règle "Chaque salarié n'appartient qu'à un seul groupe de salariés, et donc qu'à une seule entreprise."; celle-ci est importante dans mon cas.
Mon exemple n'est peut être pas bien choisi...

Il faudrait peut-être plus le voir comme:
- les groupes de salariés sont des modèle des produits
- les salariés sont des produits, définit par leur modèle.
- chaque entreprise dispose de plusieurs modèles de produits.

[crica]

Je souhaite crée une nouvelle relation entre C et D

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE E (
	c_id INTEGER,
	d_id INTEGER,
	PRIMARY KEY(c_id, d_id),
	FOREIGN KEY(c_id) REFERENCES C(id)
	FOREIGN KEY(d_id) REFERENCES D(id)
);

Le problème, c'est que cette table permet des relations incohérentes. En effet, cette relation ne doit se limiter qu'aux entités partageant la même relation avec A.

Un SGBDR, où R est relatif "relationnel", n'est pas toujours aussi évident à aborder, il sert à créer le moins de tables et d'éléments possibles. Il s'agirait donc, dans votre cas, plutôt de créer des "relations", comme vous l'énoncez très bien, que d'ajouter de la structure.

Lorsque vous dessinez votre modèle de données (il y en a un plus haut dans les réponses), vous matérialisez les liens dont vous avez besoin. Ces liens deviendront les contraintes et si vous avez un bon modèle, vous saurez dans quel sens elles vont. Une contrainte est toujours sur une colonne (un champ), sur cette base vous :
1. vous vous assurez que les deux tables en relation ont une colonne en commun, si besoin vous créerez l'autre colonne dans la table destination (pour vous C.id et D.c_id, je présume) et ces deux éléments auront les mêmes caractéristiques (type, longueur);
2. vous intégrez la contrainte dans le sens voulu avec une clé étrangère : D.c_id (destination) clé_étrangère sur C.id (source) qui vous dit que l'on ne peut pas indiquer une entrée dans D qui n'existe pas dans C (il y a d'autres solutions que les clés étrangères mais ici c'est la plus simple);
3. si en plus, vous voulez que la destination soit toujours renseignée, vous ajoutez une contrainte NOT NULL

Nota :
A : Ces constructions sont très faciles à mettre en oeuvre, elles requièrent par contre une vision claire de leur déroulement car ici, par exemple, du moment où vous aurez besoin d'effacer une entrée dans C, vous devrez préalablement contrôler qu'elle n'est pas utilisée dans D, faute de quoi vous allez créer un problème d'intégrité. Les meilleurs SGBDR contrôlent ce type de contraintes en cascade pour vous mais dans certains cas... c'est pas évident et justement toute la complexité d'un langage simple comme SQL....
B : la syntaxe est très dépendante du SGBDR utilisé, MySQL et Oracle ont des approches différentes de certains sujets et le code de l'un ne peut pas être collé chez l'autre sans une pré-analyse...

Lorsque dans vos réponses vous créez une table pour faire le lien, c'est en fait au cœur de SQL que vous vous trouvez... ce sont les sélects qui vous donneront un résultat où toutes les colonnes sont visibles. N'hésitez pas à observer les modèles de données complexes que l'on trouve sur le net : dans le modèle l'on stocke le moins d'éléments possibles et par le SELECT l'on reconstitue l'entier des données pour les utiliser...

Bref, hope this helps,
Bonne suite :-)

[skuatamad]

Dans votre modèle "corrigé", je ne vois pas pourquoi a_id fait partie de la clé primaire.

Cependant, ça ne corrige pas le non respect de la 2NF car :
http://fsmrel.developpez.com/basesre...?page=3#L3.4.2

Une relation R est en deuxième forme normale si elle est en première forme normale et si chaque attribut n'appartenant à aucune clé candidate est en dépendance totale de chaque clé candidate de R.

b_id n'étant dépendant que de d_id, la 2NF n'est à mon sens pas respectée.

Le modèle "classique" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE E (
	c_id INTEGER,
	d_id INTEGER,
	PRIMARY KEY(c_id, d_id),
	FOREIGN KEY(c_id) REFERENCES C(id)
	FOREIGN KEY(d_id) REFERENCES D(id)
);

ne permet pas de valider la correspondance avec la table A, mais par trigger il est possible de valider les données.

Je pense que c'est la solution la plus simple, une autre solution à base de VM et contrainte check permet aussi de valider cette correspondance.

Vous pouvez également poster votre question sur le forum merise, où des experts en modélisation (fsmrel pour ne citer que lui) pourront vous donner un avis plus éclairé :
http://www.developpez.net/forums/f25...thodes/merise/

[StringBuilder]

Bonjour,

Votre problème peut se résoudre de deux manières "propres".

La première, en utilisant des clés composées.

La seconde, en utilisant un trigger.

La première provoque malheureusement une certaine redondance dans les stockage des informations (et selon le SGBD, peut dégrader légèrement les performances des requêtes sur ces tables).

La seconde permet de conserver votre modèle initial (qui me semble tout à fait propre), en contre partie de l'écriture d'un trigger potentiellement complexe et contre-performant (en création/mise à jour uniquement), sans altérer la partie lecture.


Solution 1 :
A PK(id_a)
B PK(id_b, id_a) FK(A:id_a)
C PK(id_c, id_a) FK(A:id_a)
D PK(id_d, id_b, id_a) FK(B:id_b, id_a)
E PK(id_d, id_b, id_c, id_a) FK1(D:id_d, id_b, id_a) FK2(C:id_c, id_a)

Mais comme on voit, on se traîne des clés primaires mammouth, ce qui va rendre la rédaction des requêtes difficiles, sujettes à erreur, et surtout, on va se taper une pression mémoire et CPU plus forte pour la gestion des jointures.

Solution 2 :
Le modèle comme décrit dans votre premier post.

Un trigger sur E sur "INSERT, UPDATE" :
- Qui vérifie que le A lié à C est égal au A lié à D (en passant par B).


Personnellement, j'opterais pour la solution 2 : on alourdi un peu (pas des masses) la création/mise à jour de lignes dans E, sans pour autant perturber les perforpances à la lecture.

Attention cependant, si B, C ou D peuvent changer de parent, alors il faudra autant de trigger pour s'assurer que les lignes de E restent cohérentes.


[edit] Finalement, y'a même pas besoin de contrainte CHECK pour la solution 1 [/edit]

[fsmrel]

Vous pouvez également poster votre question sur le forum merise

Exact skuatamad , la solution passe par la mise en oeuvre de surclés (on ne touche évidemment pas aux clés primaires). Voir ici.