Discussion :

[Sebajuste]

Bonjour,

Comme on me l'a suggéré dans le forum SQL, je viens poster mon problème ici.

Comme nous allons créer une nouvelle application, et partir d'une base de donnée vierge, je souhaite avec le schéma le plus propre possible.
Cette application doit pouvoir gérer différents clients et je rencontre des difficulté à modéliser correctement l'affectation de ressources.


Je dispose que 4 tables:
- Entreprise : cette table liste nos différents clients
- Model : cette table liste les différents modèle d'appareil proposé par chaque entreprise à ses clients finaux. Un Model d'appareil n'appartient qu'à une seule entreprise
- Appareil : cette table contient tous les appareils utilisés. Un appareil n'appartient qu'à un seul modèle d'appareil. Par extention, un appareil n'appartient qu'à une seule entreprise.
- Utilisateur : cette table contient les utilisateurs finaux. Chaque utilisateur n'est affilié qu'à une seule enrteprise

J'ai besoin de faire une jointure entre Utilisateur et Appareil, qui correspond à la fonctionnalité "un utilisateur manipule un appareil". Seulement, un utilisateur ne peut manipuler qu'un appareil appartenant à la même entreprise que lui.

Si ma jointure ne contient que deux clef étrangère id_utilisateur et id_appareil, je pourrais très bien affecter un appareil d'une aurte entreprise à un utilisateur.
Je pourrais rajouter une troisième clef étrangère id_entreprise, mais cette clef ne permet pas d'identifier qu'un appareil appartient bien à une entreprise, car Appareil ne possède pas cette clef.
Il faudrait donc en plus rajouter id_model. Là, la contrainte fonctionnelle serait correct. Mais j'aurais un élément de la clef qui en dépendrait d'une autre. Et la seconde forme normale ne serait pas respectée.


Désolé si je ne fais pas de schéma intermédiaire, et utilise directement du SQL, mais mon niveau théorique est trop faible pour modéliser correctement.

J'obtient donc :

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
26
27
28
29
30
31
32
33
34
CREATE TABLE Entreprise (
 id INTEGER PRIMARY KEY
);

CREATE TABLE Model (
 id INTEGER  PRIMARY KEY,
 entreprise_id INTEGER,
 FOREIGN KEY(entreprise_id) REFERENCES Entreprise(id)
);

CREATE TABLE Appareil (
 id INTEGER PRIMARY KEY,
 model_id INTEGER,
  FOREIGN KEY(model_id ) REFERENCES Model(id)
);

CREATE TABLE Utilisateur (
 id INTEGER PRIMARY KEY,
 entreprise_id INTEGER,
 FOREIGN KEY(entreprise_id) REFERENCES Entreprise(id)
);


CREATE TABLE Manipuler (
  utilisateur_id INTEGER,
  appareil_id INTEGER,
  entreprise_id INTEGER,
  model_id INTEGER,
  PRIMARY KEY(utilisateur_id, appareil_id),
  FOREIGN KEY(utilisateur_id, entreprise_id) REFERENCES Utilisateur(id, entreprise_id),
  FOREIGN KEY(appareil_id, model_id ) REFERENCES Appareil (id, model_id),
  FOREIGN KEY(model_id, entreprise_id) REFERENCES Model(id, entreprise_id)
);

Comme on le voit, appareil_id qui est un élément de la clef dépend de model_id, qui est un élément non clef.

Est-ce vraiment un problème ? Y a t'il une solution ?

[fsmrel]

Bonsoir Sebajuste,


Votre table Manipuler contient des clés étrangères faisant référence à des clés absentes des tables référencées, donc le SGBD ne peut que les rejeter. Par exemple, quand vous codez : FOREIGN KEY (utilisateur_id, entreprise_id) REFERENCES Utilisateur (id, entreprise_id), cela suppose que la table Utilisateur est dotée d’une clé {id, entreprise_id}, ce qui n’est pas le cas.

Pour arriver à respecter la contrainte selon laquelle un utilisateur n’a le droit de se servir que des appareils de son entreprise, on va injecter des surclés : Model_SK, Appareil_SK, Utilisateur_SK (cf. ci-dessous), une surclé étant un surensemble d’une clé (primaire dans votre cas). Comme une clé étrangère peut faire référence à une surclé, on ne va pas se priver d’utiliser cette possibilité, et au bout du compte on peut ainsi propager légalement (cette fois-ci !) la colonne entreprise_id jusque dans l’en-tête de la table Manipuler :

CREATE TABLE Entreprise (
 id INTEGER PRIMARY KEY
);

CREATE TABLE Model (
 id INTEGER  PRIMARY KEY,
 entreprise_id INTEGER,
 CONSTRAINT Model_SK UNIQUE  (entreprise_id, id),
 FOREIGN KEY(entreprise_id) REFERENCES Entreprise (id)
);

CREATE TABLE Appareil (
 id INTEGER PRIMARY KEY,
 model_id INTEGER,
 entreprise_id INTEGER,
 CONSTRAINT Appareil_SK UNIQUE  (entreprise_id, id),
  FOREIGN KEY(model_id ) REFERENCES Model (id)
);

CREATE TABLE Utilisateur (
 id INTEGER PRIMARY KEY,
 entreprise_id INTEGER,
 CONSTRAINT Utilisateur_SK UNIQUE  (entreprise_id, id),
 FOREIGN KEY(entreprise_id) REFERENCES Entreprise (id)
);

CREATE TABLE Manipuler (
  utilisateur_id INTEGER,
  appareil_id INTEGER,
  entreprise_id INTEGER,
  PRIMARY KEY(utilisateur_id, appareil_id),
  FOREIGN KEY(entreprise_id, utilisateur_id) REFERENCES Utilisateur(entreprise_id, id),
  FOREIGN KEY(entreprise_id, appareil_id) REFERENCES Appareil (entreprise_id, id)
 );

Un début de jeu d’essai :

insert into Entreprise (id) VALUES (1) ;
insert into Entreprise (id) VALUES (2) ;

insert into Model (entreprise_id, id) VALUES (1, 1) ;
insert into Model (entreprise_id, id) VALUES (1, 2) ;
insert into Model (entreprise_id, id) VALUES (2, 8) ;
insert into Model (entreprise_id, id) VALUES (2, 9) ;

insert into Appareil (entreprise_id, model_id, id) VALUES (1, 1, 11) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 1, 12) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 2, 21) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 2, 22) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 8, 81) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 8, 82) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 9, 91) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 9, 92) ;

insert into Utilisateur (entreprise_id, id) VALUES (1, 1) ;
insert into Utilisateur (entreprise_id, id) VALUES (1, 2) ;
insert into Utilisateur (entreprise_id, id) VALUES (2, 16) ;
insert into Utilisateur (entreprise_id, id) VALUES (2, 17) ;

insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (1, 1, 22) ;
insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (2, 16, 92) ;
insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (2, 1, 92) ;  -- délinquant, rejeté

Tout se passera bien sauf pour le dernier insert, car l’utilisateur 1 ne fait pas partie de l’entreprise 2. ∎


Tout ceci ressortit au problème bien connu de la contrainte de chemin.

[aieeeuuuuu]

Bonjour fsmrel,

Ayant suivi la discussion originale, j'avais envisagé exactement la même solution que vous.
Je ne l'avais toutefois pas proposée, car la table Manipuler me semblait alors contrevenir à la deuxième forme normale, puisque entreprise_id dépend fonctionnellement de utilisateur_id. (à double titre d'ailleurs, puisque entreprise_id dépend également de appareil_id ). Ainsi, entreprise_id n'est pas en dépendance fonctionnelle totale de la clé candidate (utilisateur_id, appareil_id).

Est-ce que j'ai raté une subtilité ?

[fsmrel]

Bonsoir aieeeuuuuu,

La table Manipuler me semblait alors contrevenir à la deuxième forme normale

Bonne remarque ! Pour contrevenir, elle contrevient, et sémal !

Pour empêcher cela, on devrait donc appliquer le théorème de Heath, donnant lieu à la décomposition de :

{utilisateur_id, appareil_id, entreprise_id}

en :

{utilisateur_id, entreprise_id}

{utilisateur_id, appareil_id}

{appareil_id, entreprise_id}

Ce qui nous ramène à la case Départ, et ne permet donc plus de garantir la contrainte selon laquelle un utilisateur n’a le droit de se servir que des appareils de son entreprise : avec un SGBD SQL, normaliser nous contraint à mettre en œuvre des triggers pour rejeter les inserts et les updates délinquants.

Par référence à tous les traités parlant de normalisation, quelles sont les conséquences du viol de la 2NF par la table Manipuler ? Si l’utilisateur <u1> fait partie de l’entreprise <e1>, alors pour chaque ligne de cette table où est présente la valeur <u1>, la valeur <e1> doit aussi être présente, et elle seule. C’est de la redondance, et on est prévenus : selon les traités, il y a toujours la possibilité de violer cette contrainte, mais dans le cas de la table Manipuler, on a démontré qu’on était à l’abri d’une telle mésaventure.

En outre, on peut nous opposer les observations classiques : par exemple en cas d’insert, si on ne disposait que de la table Manipuler un utilisateur ne pourrait être embauché qu’à condition d’utiliser un appareil, mais dans le cas présent, l’existence de la table Utilisateur rend sans objet ce genre d’observation. De même l’existence des autres tables fait qu’on peut supprimer sans problème des lignes de la table Manipuler. Même chose pour les updates, l’intégrité référentielle veille ! (Essayez par exemple ceci : UPDATE Manipuler SET appareil_id = 92 WHERE utilisateur_id = 1 ;)...

Ainsi, en conservant la table Manipuler dénormalisée, notre devoir demeure de garantir les dépendances fonctionnelles dont vous avez fait mention, et c’est bien ce que nous faisons. Certes ces DF sont cause de redondance, mais peu encombrantes ici, et le prix à payer pour la mise en oeuvre de triggers, conséquence de la normalisation est, à mon avis, incomparablement plus lourd. Mon choix est vite fait...

Vos scrupules demeurent-ils ?

[Sebajuste]

Toujours très clair est précis fsmrel. Merci beaucoup !

[fsmrel]

Merci Sebajuste.

A l’attention de aieeeuuuuu :

Pour la petite histoire et pour plus de précision, je sors un lapin de mon chapeau : vous noterez que si l’on utilise l’identification relative de préférence à l’identification absolue, alors la 2NF est respectée...

Exemple :

CREATE TABLE Entreprise (
        entreprise_id          INTEGER           NOT NULL,
      CONSTRAINT Entreprise PK PRIMARY KEY (entreprise_id)
);

CREATE TABLE Model (
        entreprise_id          INTEGER           NOT NULL,
        model_id               INTEGER           NOT NULL,
      CONSTRAINT Model_PK PRIMARY KEY (entreprise_id, model_id),
      CONSTRAINT Model_Entreprise_FK FOREIGN KEY (entreprise_id) 
          REFERENCES Entreprise(entreprise_id)
);

CREATE TABLE Appareil (
        entreprise_id          INTEGER           NOT NULL,
        model_id               INTEGER           NOT NULL,
        appareil_id            INTEGER           NOT NULL,
      CONSTRAINT Appareil_PK PRIMARY KEY (entreprise_id, model_id, appareil_id),
      CONSTRAINT Appareil_Model_FK FOREIGN KEY (entreprise_id, model_id)      
          REFERENCES Model(entreprise_id, model_id)
);

CREATE TABLE Utilisateur (
        entreprise_id          INTEGER           NOT NULL,
        utilisateur_id         INTEGER           NOT NULL,
      CONSTRAINT Utilisateur_PK PRIMARY KEY  (entreprise_id, utilisateur_id),
          FOREIGN KEY(entreprise_id) REFERENCES Entreprise (entreprise_id)
);

CREATE TABLE Manipuler (
        entreprise_id          INTEGER           NOT NULL,
        model_id               INTEGER           NOT NULL,
        appareil_id            INTEGER           NOT NULL,
        utilisateur_id         INTEGER           NOT NULL,
      CONSTRAINT Manipuler_PK PRIMARY KEY (entreprise_id, model_id, appareil_id, utilisateur_id),
      CONSTRAINT Manipuler_Utilisateur_FK FOREIGN KEY(entreprise_id, utilisateur_id) 
          REFERENCES Utilisateur (entreprise_id, utilisateur_id),
      CONSTRAINT Manipuler_Appareil_FK FOREIGN KEY(entreprise_id, model_id, appareil_id) 
          REFERENCES Appareil (entreprise_id, model_id, appareil_id)
 );

[aieeeuuuuu]

si l’on utilise l’identification relative de préférence à l’identification absolue, alors la 2NF est respectée...

J'avais remarqué cela aussi.
Je trouve d'ailleurs assez étrange que cette solution soit juste alors que la première est fausse (toujours d'un point de vue théorique). Alors que techniquement... c'est blanc bonnet blanc ou bonnet blanc.

Cela dit, cela répond exactement à la question initiale de Sebajuste qui tenait au respect des formes normales.

Et pour en revenir à la solution avec identification absolue, il est vrai qu'il faudrait tout un arsenal de déclencheurs pour respecter la contrainte.
dans l'autre fil, skuatamad propose une solution à base de contrainte check sur une vue matérialisée. Je ne connais pas suffisamment Oracle pour détailler sur ce point, mais sous SQL Server, il n'est pas possible de poser une contrainte CHECK sur une vue.

J'avais en revanche une solution similaire pour SQL Server, si on reprend votre modèle, sans la colonne id_entreprise dans la talbe Manipuler.

Il est possible de créer une vue comme suit :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE VIEW V_MANIPULER_CHECK_ENTREPRISE
WITH SCHEMABINDING
AS
	SELECT		1 as chk
	FROM		dbo.Entreprise E
	CROSS JOIN	dbo.Manipuler M
	INNER JOIN	dbo.Appareil A
		ON		A.id = M.appareil_id
	INNER JOIN	dbo.Utilisateur U
		ON		U.id = M.utilisateur_id
	INNER JOIN	dbo.Model Mo
		ON		Mo.id = A.model_id
	WHERE U.entreprise_id <> Mo.entreprise_id
;

Puis de lui ajouter une contrainte d'unicité :

Code SQL :

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

1
2
 
CREATE UNIQUE CLUSTERED INDEX CUIX_V_MANIPULER_CHECK_ENTREPRISE ON V_MANIPULER_CHECK_ENTREPRISE(chk);

Cela devrait suffire a assurer la contrainte fonctionelle :

Code SQL :

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

1
2
 
insert into Manipuler ( utilisateur_id, appareil_id) VALUES (1, 92) ;  -- délinquant, rejeté

Impossible d'insérer une ligne de clé en double dans l'objet «*dbo.V_MANIPULER_CHECK_ENTREPRISE*» avec un index unique «*CUIX_V_MANIPULER_CHECK_ENTREPRISE*». La valeur de clé dupliquée est (1).

Cela évite toute une série de déclencheurs sur toutes les tables en jeu. Mais il faudra bien sûr accompagner cela de commentaires adéquats afin d'aider à la compréhension de la raison d'être de cette vue indexée qui sera, par définition toujours vide !
il faudra aussi placer les index qui vont bien sur les tables de la requêtes, afin de minimiser l'impact de la mise à jour de la vue.

Bref, si on résume, on dispose de quatre solutions pour assurer le respect de la contrainte :

1/ faire une petite entorse au règlement, en ignorant sciemment la 2NF pour la table manipuler
2/ sortir le lapin du chapeau en mettant en place l'identification relative. Par rapport à la solution 1, c'est juste une nuance sémantique pour retourner dans les clous
3/ sortir l'artillerie lourde, en plaçant des triggers sur toutes les tables
4/ mettre en place la vue indexée (si le SGBD) le permet : on en arrive à une solution absconse.

PS : il existe une cinquième solution : créer une assertion, si le SGBD les prend en charge...