Bonsoir,
Envoyé par
Firlfire
Il y a un point que je ne comprends pas dans votre schéma : pourquoi "familleId" est présent dans "POSTE" ? (idem dans ACTION_CONTRAINTE_PAR_VERSION)
Je ne vois pas l'utilité car comme VERSION.versionId est une PK elle sera unique dans la table, donc si POSTE ne contient que versionID il est possible de remonter a FAMILLE.
Il est exact que la présence de l’attribut familleId dans POSTE n’est pas nécessaire. Le MLD que j’ai présenté est en fait dérivé d’un MCD dans lequel VERSION et POSTE sont des entités-types considérées comme faibles (weak entity-types, alias characteristics chez Codd), c’est ce qu’a expliqué escartefigue. Ce type de modélisation est surtout utile quand les MCD comportent des boucles (cf. par exemple le post Gestion Facturation et devis), ce qui n’est pas le cas du vôtre (mais presque...)
En considérant maintenant ces entités-types comme fortes (regular entity-types, alias kernels chez Codd), alors les tables SQL sont les suivantes :
CREATE TABLE ACTION
(
actionId INT NOT NULL,
actionNom VARCHAR(48) NOT NULL,
CONSTRAINT ACTION_PK PRIMARY KEY (actionId),
CONSTRAINT ACTION_AK UNIQUE (actionNom)
) ;
CREATE TABLE FAMILLE
(
familleId INT NOT NULL,
familleNom VARCHAR(48) NOT NULL,
CONSTRAINT FAMILLE_PK PRIMARY KEY (familleId),
CONSTRAINT FAMILLE_AK UNIQUE (familleNom)
) ;
CREATE TABLE ACTION_CONTRAINTE_PAR_FAMILLE
(
actionId INT NOT NULL,
familleId INT NOT NULL,
CONSTRAINT ACTION_CONTRAINTE_PAR_FAMILLE_PK PRIMARY KEY (actionId, familleId),
CONSTRAINT ACTION_CONTRAINTE_PAR_FAMILLE_ACTION_FK FOREIGN KEY (actionId)
REFERENCES ACTION (actionId),
CONSTRAINT ACTION_CONTRAINTE_PAR_FAMILLE_FAMILLE_FK FOREIGN KEY (familleId)
REFERENCES FAMILLE (familleId)
) ;
CREATE TABLE VERSION
(
familleId INT NOT NULL,
versionId INT NOT NULL,
versionNom VARCHAR(48) NOT NULL,
CONSTRAINT VERSION_PK PRIMARY KEY (versionId),
CONSTRAINT VERSION_FAMILLE_FK FOREIGN KEY (familleId)
REFERENCES FAMILLE (familleId)
) ;
CREATE TABLE ACTION_CONTRAINTE_PAR_VERSION
(
actionId INT NOT NULL,
familleId INT NOT NULL,
versionId INT NOT NULL,
CONSTRAINT ACTION_CONTRAINTE_PAR_VERSION_PK PRIMARY KEY (actionId, familleId, versionId),
CONSTRAINT ACTION_CONTRAINTE_PAR_VERSION_ACTION_FK FOREIGN KEY (actionId)
REFERENCES ACTION (actionId),
CONSTRAINT ACTION_CONTRAINTE_PAR_VERSION_VERSION_FK FOREIGN KEY (versionId)
REFERENCES VERSION (versionId)
) ;
CREATE TABLE POSTE
(
posteId INT NOT NULL,
posteNom VARCHAR(48) NOT NULL,
versionId INT null,
CONSTRAINT POSTE_PK PRIMARY KEY (posteId),
CONSTRAINT POSTE_AK UNIQUE (posteNom),
CONSTRAINT POSTE_VERSION_FK FOREIGN KEY (versionId)
REFERENCES VERSION (versionId)
) ;
CREATE TABLE TACHE
(
actionId INT NOT NULL,
posteId INT NOT NULL,
CONSTRAINT TACHE_PK PRIMARY KEY (actionId, posteId),
CONSTRAINT TACHE_ACTION_FK FOREIGN KEY (actionId)
REFERENCES ACTION (actionId),
CONSTRAINT TACHE_POSTE_FK FOREIGN KEY (posteId)
REFERENCES POSTE (posteId)
) ;
CREATE ASSERTION ASSERT01 CHECK
(EXISTS
SELECT *
FROM POSTE as x
JOIN TACHE as y on x.posteId = y.posteId
JOIN ACTION_CONTRAINTE_PAR_FAMILLE as z on y.actionId = z.actionId
AND x.familleId = z.familleId
) ;
CREATE ASSERTION ASSERT01 CHECK
(EXISTS
SELECT *
FROM POSTE as x
JOIN VERSION as v on x.versionId = v.versionId
JOIN TACHE as y on x.posteId = y.posteId
JOIN ACTION_CONTRAINTE_PAR_FAMILLE as z on y.actionId = z.actionId
AND v.familleId = z.familleId
) ;
insert into ACTION values
(1, 'action 1')
, (2, 'action 2')
;
insert into FAMILLE values
(1, 'famille 1')
, (2, 'famille 2')
;
insert into VERSION (familleId, versionId, versionNom) values
(1, 1, 'famille 1, version 1')
, (2, 2, 'famille 2, version 2')
;
insert into ACTION_CONTRAINTE_PAR_FAMILLE (actionId, familleId) values
(2, 2)
;
insert into ACTION_CONTRAINTE_PAR_VERSION (actionId, familleId, versionId) values
(1, 1, 1)
;
insert into POSTE (posteId, posteNom, versionId) values
(1, 'poste 1', 1)
;
insert into TACHE (actionId, posteId) values
(2, 1) /* action 2, poste 1 qui => version 1 => famille 1 */
;
Schema BdD : FK sur plusieurs tables ?
Répondre avec citation
Partager