Bonsoir Anthony,
Envoyé par
Antonitzer
Pourquoi ComposantPN ne devient pas un fk dans les classes héritées ?
La relation entre une clé candidate K (primaire ou alternative) et une clé étrangère F qui la référence, ne doit pas subir les conséquences d’une modification affectant K.
Yves Tabourier l’a bien montré au niveau conceptuel, je vous renvoie à ce que j’en dis ici.
On est toujours à la merci de l’utilisateur, il a tout pouvoir sur ses données et peut les changer ad infinitum. J’ai assisté à des refontes de système provoquées par l’évolution de la structure des clés (passage à l’extension à 9, puis à 10 chiffres des numéros de téléphone, et autres facéties redoutables). Les clés omniprésentes dans les relations entre tables doivent donc être invariantes, échapper au contrôle de l’utilisateur, d’où la mise en oeuvre de l’attribut ComposantId (privé) qu’on retrouve effectivement dans toutes les tables, en tant que clé primaire et clé étrangère, tandis que l’attribut ComposantPN (public) n’est donc présent qu’une fois, dans la table Composant : si l’utilisateur le chahute, alors ça n’est pas un problème.
Envoyé par
Antonitzer
pour ajouter un CND dans la base, avec la première solution si il faut regarder à quel CDR cela correspond pour ensuite créer un objet CND et lui attribuer en Con_ComposantId le ComposantId du CDR cela peut demander beaucoup de travail.
En fait, avec un SGBD permettant d’affecter des triggers à des vues (DB2, Oracle, SQL Server, etc., MySQL étant pour sa part exclu...), le travail de l’utilisateur peut être très largement simplifié, en consultation et en mise à jour.
Reprenons le MCD :
Ainsi que le MLD correspondant :
Script de création des tables :
(j’ai renommé la colonne Con_ComposantId de la table Connecteur en CDR_ComposantId)
CREATE TABLE Composant
(
ComposantId INT NOT NULL IDENTITY,
ComposantPN VARCHAR(64) NOT NULL,
Etc VARCHAR(64) NOT NULL,
CONSTRAINT Composant_PK PRIMARY KEY (ComposantId),
CONSTRAINT Composant_AK UNIQUE (ComposantPN)
) ;
CREATE TABLE ConnecteurDeRepos
(
ComposantId INT NOT NULL,
ClassConnecteur VARCHAR(64) NOT NULL,
FinishPlating VARCHAR(64) NOT NULL,
ShellSize VARCHAR(64) NOT NULL,
Designation VARCHAR(64) NOT NULL,
CONSTRAINT ConnecteurDeRepos_PK PRIMARY KEY (ComposantId),
CONSTRAINT ConnecteurDeRepos_AK UNIQUE (ClassConnecteur, FinishPlating, ShellSize),
CONSTRAINT ConnecteurDeRepos_Composant_FK FOREIGN KEY (ComposantId)
REFERENCES Composant (ComposantId) ON DELETE CASCADE
) ;
CREATE TABLE Connecteur
(
ComposantId INT NOT NULL,
CDR_ComposantId INT NOT NULL,
Designation VARCHAR(64) NOT NULL,
Arrangement VARCHAR(64) NOT NULL,
Polarisation VARCHAR(64) NOT NULL,
ConnecteurType VARCHAR(64) NOT NULL,
CONSTRAINT Connecteur_PK PRIMARY KEY (ComposantId),
CONSTRAINT Connecteur_ConnecteurDeRepos_FK FOREIGN KEY (CDR_ComposantId)
REFERENCES ConnecteurDeRepos (ComposantId),
CONSTRAINT Connecteur_Composant_FK FOREIGN KEY (ComposantId)
REFERENCES Composant (ComposantId) ON DELETE CASCADE
) ;
(1) La contrainte Connecteur_Composant_FK présente dans la table Connecteur est dotée d’une clause « ON DELETE CASCADE ». Cela signifie que si l’on supprime dans la table Composant le composant pour lequel la colonne ComposantId prend la valeur <c>, la suppression est de facto déclenchée en plus pour la ligne la table Connecteur pour laquelle la colonne ComposantId prend aussi la valeur <c> (suppression en cascade) : cette répercussion est logique, puisqu’il n’y a en réalité qu’un seul objet Connecteur, dont les données sont réparties dans deux tables au niveau SQL.
(2) Si la contrainte Connecteur_Composant_FK n’est pas dotée d’une clause « ON DELETE CASCADE », alors si on cherche à supprimer directement dans la table Composant, on se fera jeter tant que la valeur <c> partie prenante dans cette histoire sera présente à la fois dans les deux tables. Pour arriver à supprimer le composant, il faudra donc d’abord supprimer la ligne concernée dans la table Connecteur, suite à quoi on pourra enfin effectuer la suppression dans la table Composant.
Dans le cas de la généralisation appliquée aux composants, il est évident qu’il est préférable de mettre en œuvre la clause « ON DELETE CASCADE » : la contrainte ConnecteurDeRepos_Composant_FK (table ConnecteurDeRepos) est donc aussi concernée par cette clause.
Par contre, si l’on ne peut pas impunément répercuter la suppression d’un connecteur de repos sur les connecteurs qui le référencent, alors on ne doit pas utiliser la clause « ON DELETE CASCADE », cas de la contrainte Connecteur_ConnecteurDeRepos_FK (table Connecteur).
Faciliter la tâche de l’utilisateur, tout en lui cachant l’attribut ComposantId qui ne le concerne pas :
Plutôt que de le laisser agir sur les tables de base, on l’autorisera à seulement agir sur des tables virtuelles, c'est-à-dire des vues.
Prenons le cas d’un connecteur de repos Cr (CDR). Pour répondre au besoin fonctionnel de visualisation, la vue ConnecteurDeReposV ci-dessous suffit (ne figurent que les attributs naturels, c'est-à-dire ceux auxquels l’utilisateur est sensible : l’attribut ComposantId est artificiel, purement technique et ne le concerne pas) :
ConnecteurDeReposV {ComposantPN, Etc, ClassConnecteur, FinishPlating, ShellSize, Designation}
Où :
— ComposantPN est l’attribut qui identifie Cr dans la table Composant ;
— Etc résume dans cette table les autres attributs de Cr ;
— ClassConnecteur, FinishPlating, ShellSize représentent le ClassConnecteur, le FinishPlating et le ShellSize de Cr.
Déclaration SQL de cette vue (à faire vous-même ou par votre DBA...) :
CREATE VIEW ConnecteurDeReposV (ComposantPN, Etc, ClassConnecteur, FinishPlating, ShellSize, Designation)
AS
SELECT x.ComposantPN, x.Etc, y.ClassConnecteur, y.FinishPlating, y.ShellSize, y.Designation
FROM Composant as x JOIN ConnecteurDeRepos as y on x.ComposantId = y.ComposantId
;
Prenons le cas d’un connecteur Cx (CNR). Comme dans le cas du connecteur de repos Cr, pour répondre au besoin fonctionnel de visualisation, la vue ConnecteurVtout ci-dessous suffit (là encore, ne figurent que les attributs naturels, l’attribut ComposantId n’en fait donc pas partie) :
ConnecteurVtout {ComposantPN, Etc, ConnecteurDeReposPN, ClassConnecteur, FinishPlating, ShellSize, Arrangement, Designation, Polarisation, ConnecteurType}
Où :
— ComposantPN est l’attribut qui identifie Cx dans la table Composant ;
— Etc résume dans cette table les autres attributs de Cx ;
— ConnecteurDeReposPN représente l’identifiant du connecteur de repos Cr référencé par Cx ;
— ClassConnecteur, FinishPlating, ShellSize représentent le ClassConnecteur, le FinishPlating et le ShellSize du connecteur de repos Cr ;
— Arrangement, Designation, Polarisation, ConnecteurType représentent l’arrangement, la désignation, la polarisation et le type du connecteur Cx.
Déclaration SQL de cette vue :
CREATE VIEW ConnecteurVtout (ComposantPN, Etc, ConnecteurDeReposPN, ClassConnecteur, FinishPlating, ShellSize, Arrangement, Designation, Polarisation, ConnecteurType)
AS
SELECT x.ComposantPN, x.Etc, t.ComposantPN, z.ClassConnecteur, z.FinishPlating, z.ShellSize, y.Arrangement, y.Designation, y.Polarisation, y.ConnecteurType
FROM Composant as x JOIN Connecteur as y on x.ComposantId = y.ComposantId
JOIN ConnecteurDeRepos as z on y.CDR_ComposantId = z.ComposantId
JOIN Composant AS t ON z.ComposantId = t.ComposantId
;
Maintenant, il faut que l’utilisateur puisse mettre à jour la base de données. Étant donné qu’il n’a accès qu’aux vues, on doit l’autoriser à mettre celles-ci à jour. Si le SGBD ne nous autorise pas la mise à jour directe des vues, mais permet de définir des triggers pour celles-ci, on s’en sortira quand même.
Triggers pour mettre à jour les connecteurs de repos. Exemples (SQL Server) :
Un trigger permettant de créer des connecteurs de repos :
CREATE TRIGGER ConnecteurDeReposTR ON ConnecteurDeReposV INSTEAD OF INSERT
AS
INSERT INTO Composant (ComposantPN, Etc)
SELECT ComposantPN, Etc
FROM INSERTED
;
INSERT INTO ConnecteurDeRepos (ComposantId, ClassConnecteur, FinishPlating, ShellSize, Designation)
SELECT ComposantId, ClassConnecteur, FinishPlating, ShellSize, Designation
FROM INSERTED as x JOIN Composant as y ON x.ComposantPN = y.ComposantPN
;
CREATE TRIGGER ConnecteurDeReposTRdel ON ConnecteurDeReposV INSTEAD OF DELETE
AS
DELETE FROM Composant
WHERE ComposantPN IN (SELECT ComposantPN FROM DELETED)
CREATE TRIGGER ConnecteurDeReposTRupd ON ConnecteurDeReposV INSTEAD OF UPDATE
AS
IF UPDATE (ComposantPN)
BEGIN
IF (SELECT COUNT(ComposantPN) FROM INSERTED) > 1
BEGIN
RAISERROR ('Veuillez ne modifier qu''une clé ComposantPN à la fois. ', 16, 1)
ROLLBACK TRAN
RETURN
END
UPDATE Composant
SET ComposantPN = (SELECT ComposantPN FROM INSERTED)
WHERE Composant.ComposantPN = (SELECT ComposantPN FROM DELETED)
;
END
IF UPDATE (Etc)
BEGIN
UPDATE Composant
SET Etc = (SELECT Etc
FROM INSERTED
WHERE Composant.ComposantPN = INSERTED.ComposantPN)
WHERE ComposantPN IN (SELECT Composant.ComposantPN
FROM Composant JOIN INSERTED
ON Composant.ComposantPN = INSERTED.ComposantPN)
;
END
IF UPDATE (ClassConnecteur)
BEGIN
UPDATE ConnecteurDeRepos
SET ClassConnecteur = (SELECT ClassConnecteur
FROM INSERTED
WHERE ConnecteurDeRepos.ComposantId = (SELECT ComposantId FROM composant WHERE composantPN = INSERTED.ComposantPN)
)
WHERE ComposantId IN (SELECT Composant.ComposantId
FROM Composant JOIN INSERTED
ON Composant.ComposantPN = INSERTED.ComposantPN)
END
Ainsi, grâce aux vues et aux triggers, le travail de l’utilisateur (développeur ou utilisateur final) est réduit au minimum, la complexité est encapsulée une bonne fois pour toutes.
Exemple d’ajout d’un connecteur de repos :
INSERT INTO ConnecteurDeReposV (ComposantPN, Etc, ClassConnecteur, FinishPlating, ShellSize, Designation)
VALUES ('cpn01', 'etc01', 'classe01', 'finish01', 'shell01', 'design cpn01') ;
DELETE FROM ConnecteurDeReposV WHERE ShellSize = 'shell02' ;
Modification du connecteur de repos pour lequel ComposantPN = 'cpn03'
UPDATE ConnecteurDeReposV
SET ComposantPN = 'cpn77'
, Etc = 'cpn99'
, ClassConnecteur = 'classe 12'
WHERE ComposantPN = 'cpn03' ;
SELECT ComposantPN, Etc, ClassConnecteur, FinishPlating, ShellSize, Designation
FROM ConnecteurDeReposV ;
Ainsi, la tâche de l’utilisateur est simple, il se sert de la vue ConnecteurDeReposV comme d’une table banale, sans même qu’il ait à savoir que sous le capot les données sont en réalité réparties dans les deux tables de base Composant et ConnecteurDeRepos, tandis que les triggers interceptent les opérations de mise à jour appliquées à la vue et ventilent dans les deux tables les données déclarées dans la vue.
Triggers pour mettre à jour les connecteurs CNR
On a vu que la vue ConnecteurVtout permet de visualiser non seulement les données du connecteur Cx, mais aussi celles de son connecteur de repos Cr :
CREATE VIEW ConnecteurVtout (ComposantPN, Etc, ConnecteurDeReposPN, ClassConnecteur, FinishPlating, ShellSize, Arrangement, Designation, Polarisation, ConnecteurType)
AS
SELECT x.ComposantPN, x.Etc, t.ComposantPN, z.ClassConnecteur, z.FinishPlating, z.ShellSize, y.Arrangement, y.Designation, y.Polarisation, y.ConnecteurType
FROM Composant as x JOIN Connecteur as y on x.ComposantId = y.ComposantId
JOIN ConnecteurDeRepos as z on y.CDR_ComposantId = z.ComposantId
JOIN Composant AS t ON z.ComposantId = t.ComposantId
On peut aussi se contenter de la vue ConnecteurV ci-dessous :
ConnecteurV {ComposantPN, Etc, ConnecteurDeReposPN, Designation, Arrangement, Polarisation, ConnecteurType}
Où, je le rappelle :
— ComposantPN est l’attribut qui identifie le connecteur Cx dans la table Composant ;
— Etc résume dans cette table les autres attributs de Cx ;
— ConnecteurDeReposPN est l’identifiant (naturel) du connecteur de repos Cr auquel fait référence Cx ;
— Designation, Arrangement, Polarisation, ConnecteurType représentent la désignation, l’arrangement et le type de connecteur de Cx.
Comme dans le cas des connecteurs de repos, l’utilisateur utilisera des vues pour mettre à jour la base de données, d’où la nécessité de coder les triggers interceptant les opérations de mise à jour, quand le SGBD les rejette.
Exemple :
CREATE TRIGGER ConnecteurTR ON ConnecteurV INSTEAD OF INSERT
AS
INSERT INTO Composant (ComposantPN, Etc)
SELECT ComposantPN, Etc
FROM INSERTED
;
INSERT INTO Connecteur (ComposantId, CDR_ComposantId, Designation, Arrangement, Polarisation, ConnecteurType)
SELECT y.ComposantId, z.ComposantId, Designation, Arrangement, Polarisation, ConnecteurType
FROM INSERTED AS x JOIN Composant AS y on x.ComposantPN = y.ComposantPN
JOIN composant AS z on x.ConnecteurDeReposPN = z.ComposantPN
;
INSERT INTO ConnecteurV (ComposantPN, Etc, ConnecteurDeReposPN, Designation, Arrangement, Polarisation, ConnecteurType)
VALUES ('cpn33', 'etc33', 'cpn03', 'design33', 'arr33', 'polar33', 'conntype33') ;
INSERT INTO ConnecteurVtout (ComposantPN, Etc, ConnecteurDeReposPN, ClassConnecteur, FinishPlating, ShellSize, Arrangement, Designation, Polarisation, ConnecteurType)
VALUES ('cpn34', 'etc34', 'cpn01', '', '', '', 'arr34', 'design34', 'polar34', 'conntype34') ;
CREATE TRIGGER ConnecteurToutTR ON ConnecteurVtout INSTEAD OF INSERT
AS
INSERT INTO ConnecteurV (ComposantPN, Etc, ConnecteurDeReposPN, Arrangement, Designation, Polarisation, ConnecteurType)
SELECT ComposantPN, Etc, ConnecteurDeReposPN, Arrangement, Designation, Polarisation, ConnecteurType
FROM INSERTED
;
Tout ce que j’ai développé dans ce message est un peu long, mais a pour but de montrer que le travail de l’utilisateur peut être rendu simple, à condition que nous prenions pour notre part en charge la création des vues et des triggers.
Je vais m'occuper du 2e scénario.
En passant, vous ne votez plus ?
Sémantique des connecteurs
Répondre avec citation
Partager