Bonsoir à nouveau,
Traditionnellement, le passage du MCD au code SQL se passe ainsi (voyez l’ouvrage de Nanci) :
Une entité-type E donne lieu à une table.
L’identifiant de E devient la clé primaire de la table.
Pour la table PRODUIT, pas de problème :
CREATE TABLE PRODUIT
(
produitId INTEGER NOT NULL
, produitNom VARCHAR(48) NOT NULL
, prixVenteHT DECIMAL(5,2) NOT NULL
, CONSTRAINT PRODUIT_PK PRIMARY KEY (produitId)
);
Dans le cas des entités-types spécialisées, l’identifiant est hérité de l’entité-type « parente ». En conséquence la clé primaire d’une telle entité-type est la même que celle de sa parente, mais pour des raisons d’intégrité de référence, cette clé primaire est aussi clé étrangère (foreign key) :
CREATE TABLE PRODUIT
(
produitId INTEGER NOT NULL
, produitNom VARCHAR(48) NOT NULL
, prixVenteHT DECIMAL(5,2) NOT NULL
, CONSTRAINT PRODUIT_PK PRIMARY KEY (produitId)
);
CREATE TABLE VETEMENT
(
produitId INTEGER NOT NULL
, taille VARCHAR(5) NOT NULL
, couleur VARCHAR(16) NOT NULL
, CONSTRAINT VETEMENT_PK PRIMARY KEY (produitId)
, CONSTRAINT VETEMENT_PRODUIT_FK FOREIGN KEY (produitId)
REFERENCES PRODUIT (produitId)
ON DELETE CASCADE
);
CREATE TABLE ALIMENT
(
produitId INTEGER NOT NULL
, poids DECIMAL(5,2) NOT NULL
, CONSTRAINT ALIMENT_PK PRIMARY KEY (produitId)
, CONSTRAINT ALIMENT_PRODUIT_FK FOREIGN KEY (produitId)
REFERENCES PRODUIT (produitId)
ON DELETE CASCADE
);
CREATE TABLE INFORMATIQUE
(
produitId INTEGER NOT NULL
, CONSTRAINT INFORMATIQUE_PK PRIMARY KEY (produitId)
, CONSTRAINT INFORMATIQUE_PRODUIT_FK FOREIGN KEY (produitId)
REFERENCES PRODUIT (produitId)
ON DELETE CASCADE
);
CREATE TABLE MATERIEL
(
produitId INTEGER NOT NULL
, CONSTRAINT MATERIEL_PK PRIMARY KEY (produitId)
, CONSTRAINT MATERIEL_INFORMATIQUE_FK FOREIGN KEY (produitId)
REFERENCES INFORMATIQUE (produitId)
ON DELETE CASCADE
);
CREATE TABLE LOGICIEL
(
produitId INTEGER NOT NULL
, version VARCHAR(5) NOT NULL
, CONSTRAINT LOGICIEL_PK PRIMARY KEY (produitId)
, CONSTRAINT LOGICIEL_INFORMATIQUE_FK FOREIGN KEY (produitId)
REFERENCES INFORMATIQUE (produitId)
ON DELETE CASCADE
);
Vous aurez noté que l’attribut produitId se propage à tous les étages pour constituer les clés primaires et étrangères. Les clés étrangères sont dotées de l’action de compensation CASCADE, car en toute logique la destruction d’un produit doit être propagée à tous les étages par le SGBD.
Les applications doivent avoir une vision sémantique des choses : le découpage en tables qu’on a effectué ne les concernent pas vraiment, c’est de la technique « sous le capot » ; les applications ne s’intéressant qu’aux objets (la boîte de cassoulet maison), on va donc déclarer des vues pour restituer la dimension sémantique.
A noter que J’ajoute l’attribut identifiant naturel ean13 pour la table PRODUIT, on s’en servira par la suite (la mise en oeuvre d’un identifiant naturel, dont l’utilisateur a l’entière responsabilité, est absolument nécessaire) :
CREATE TABLE PRODUIT
(
produitId INTEGER NOT NULL IDENTITY
, produitNom VARCHAR(48) NOT NULL
, ean13 DECIMAL(13,0) NOT NULL
, prixVenteHT DECIMAL(5,2) NOT NULL
, CONSTRAINT PRODUIT_PK PRIMARY KEY (produitId)
, CONSTRAINT PRODUIT_AK UNIQUE (ean13)
);
La propriété IDENTITY est propre à SQL Server (équivalent PostgreSQL : SERIAL, MySQL : AUTO_INCREMENT), elle permet de sous-traiter au SGBD la valorisation des clés primaires. A ce propos, quel est votre SGBD ?
Intéressons-nous par exemple à l’alimentation. On crée une vue ALIMENT_v pour gérer les objets concernés, regroupant donc les attributs de la table PRODUIT et ceux de la table ALIMENT :
CREATE VIEW ALIMENT_V (ean13, produitNom, prixVenteHT, poids)
AS SELECT ean13, produitNom, prixVenteHT, poids
FROM PRODUIT as x
JOIN ALIMENT as y ON x.produitId = y.produitId
;
Si le SGBD (SQL Server notamment) refuse de mettre à jour les vues de jointure, ALIMENT_V en l’occurrence, on pallie en interceptant ces mises à jour au moyen de triggers affectés à la vue, et en mettant à jour nous-mêmes les tables composant la vue. Exemple de trigger interceptant les INSERT portant sur la vue :
CREATE TRIGGER ALIMENT_INSERT_TR ON ALIMENT_V INSTEAD OF INSERT
AS
INSERT INTO PRODUIT (ean13, produitNom, prixVenteHT)
SELECT ean13, produitNom, prixVenteHT
FROM INSERTED
;
INSERT INTO ALIMENT (produitId, poids)
SELECT produitId, poids
FROM INSERTED as x
JOIN PRODUIT as y ON x.ean13 = y.ean13
;
Dans ce trigger, SQL Server nous repasse dans la pseudo-table INSERTED les données mettant à jour la vue et que nous devons donc insérer dans les tables.
Servons-nous maintenant de la vue pour créer deux produits relevant de l’alimentation :
INSERT INTO ALIMENT_V (ean13, produitNom, prixVenteHT, poids)
VALUES
(1234567800000, 'cassoulet maison', 5, 250)
, (2345678900000, 'café Tartempion', 6, 125)
;
Dans cette affaire, on n’a utilisé que l’identifiant naturel ean13 et laissé le SGBD se débrouiller avec l’identifiant artificiel produitId dont nous nous désintéressons totalement.
Pour voir ce qui concerne l’alimentation :
SELECT * FROM ALIMENT_V ;
=>
ean13 produitNom prixVenteHT poids
1234567800000 cassoulet maison 5.00 250.00
2345678900000 café Tartempion 6.00 125.00
Et l’utilisateur est content (normalement...)
En passant, n’hésitez pas à liker les réponses qui ont pu vous aider, voire éclairer...
Partager