Bonsoir,
Il n’est pas inutile de voir comment les choses se passent concrètement dans la base de données, laquelle est la conséquence du MCD quant à sa structure. Il est intéressant par ailleurs de voir comment traduire les contraintes du genre « pas plus de 5 formations par employé ».
Un jeu d’essai (utilisation de SQL Server)
Les employés
CREATE TABLE EMPLOYE
(
employeId INT,
matricule CHAR(4) NOT NULL,
employeNom VARCHAR(32) NOT NULL,
employePrenom VARCHAR(32) NOT NULL,
CONSTRAINT EMPLOYE_PK PRIMARY KEY(employeId)
);
INSERT INTO EMPLOYE (employeId, matricule, employeNom, employePrenom)
VALUES
(1, 'lm', 'Le Mexicain', 'Louis')
, (2, 'fn', 'Naudin', 'Fernand')
, (3, 'ff', 'Folace', 'Francis')
, (4, 'rv', 'Volfoni', 'Raoul')
, (5, 'pv', 'Volfoni', 'Paul')
, (6, 'jy', 'Yes-Sir', 'Jean')
, (7, 'hh', 'Henri', 'Henri')
, (8, 'pm', 'Monpetit', 'Patricia')
, (9, 'ad', 'Delafoy', 'Antoine')
, (10, 'pc', 'Cousin', 'Pascal')
, (11, 'sc', 'Cousin', 'Sébastien')
;
SELECT matricule, employeNom, employePrenom FROM EMPLOYE ;
matricule employeNom employePrenom
lm Le Mexicain Louis
fn Naudin Fernand
ff Folace Francis
rv Volfoni Raoul
pv Volfoni Paul
jy Yes-Sir Jean
hh Henri Henri
pm Monpetit Patricia
ad Delafoy Antoine
pc Cousin Pascal
sc Cousin Sébastien
La hiérarchie des employés
CREATE TABLE EMP_HIERARCHIE
(
employeId INT,
chefId INT NOT NULL,
CONSTRAINT EMP_HIERARCHIE_PK PRIMARY KEY(employeId),
CONSTRAINT EMP_HIERARCHIE_EMPLOYE_FK FOREIGN KEY(employeId)
REFERENCES EMPLOYE(employeId),
CONSTRAINT EMP_HIERARCHIE_EMPLOYE_1_FK FOREIGN KEY(chefId)
REFERENCES EMPLOYE(employeId)
);
INSERT INTO EMP_HIERARCHIE (employeId, chefId)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'fn')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'fn'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'hh')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'ff'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'ff'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'pm')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'fn'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'rv')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'pv')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'lm'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'pc')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'rv'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'sc')
, (SELECT employeId FROM EMPLOYE WHERE matricule = 'rv'))
;
SELECT CONCAT(y.employePrenom, ' ' , y.employeNom) as employe
, CONCAT(z.employePrenom, ' ' , z.employeNom) as chef
FROM EMP_HIERARCHIE as x
JOIN EMPLOYE as y ON x.employeId = y.employeId
JOIN EMPLOYE as z ON x.chefId = z.employeId
;
employe chef
Fernand Naudin Louis Le Mexicain
Francis Folace Fernand Naudin
Raoul Volfoni Louis Le Mexicain
Paul Volfoni Louis Le Mexicain
Jean Yes-Sir Francis Folace
Henri Henri Francis Folace
Patricia Monpetit Fernand Naudin
Pascal Cousin Raoul Volfoni
Sébastien Cousin Raoul Volfoni
Les formations
CREATE TABLE FORMATION
(
formationId INT,
formationCode CHAR(8) NOT NULL,
formationNom VARCHAR(48) NOT NULL,
CONSTRAINT FORMATION_PK PRIMARY KEY(formationId)
);
INSERT INTO FORMATION (formationId, formationCode, formationNom)
VALUES
(1, 'judo', 'judo')
, (2, 'karate', 'karaté')
, (3, 'jazzman', 'jazz manouche')
, (4, 'dirorch', 'direction d''orchestre')
, (5, 'philo', 'philosophie')
, (6, 'compta', 'comptabilité')
, (7, 'peint', 'peinture')
, (8, 'sculpt', 'sculpture')
, (11, 'droit', 'droit')
, (12, 'moutons', 'élevage des moutons')
, (13, 'pligne', 'pêche à la ligne')
, (14, 'cheval', 'équitation')
, (15, 'presti', 'prestidigitation')
, (16, 'nage', 'natation')
, (17, 'alchi', 'alchimie')
, (18, 'cordes', 'théorie des cordes')
, (19, 'trapvol', 'trapèze volant')
, (20, 'agro', 'agronomie')
, (21, 'astro', 'astronomie')
, (22, 'geo', 'géographie')
, (23, 'chimie', 'chimie')
, (24, 'api', 'apiculture')
, (25, 'huitres', 'conchyliculture')
, (26, 'plongee', 'plongée')
;
SELECT formationCode, formationNom FROM FORMATION ;
formationCode formationNom
judo judo
karate karaté
jazzman jazz manouche
dirorch direction d'orchestre
philo philosophie
compta comptabilité
peint peinture
sculpt sculpture
droit droit
moutons élevage des moutons
pligne pêche à la ligne
cheval équitation
presti prestidigitation
nage natation
alchi alchimie
cordes théorie des cordes
trapvol trapèze volant
agro agronomie
astro astronomie
geo géographie
chimie chimie
api apiculture
huitres conchyliculture
plongee plongée
Les formations internes
CREATE TABLE FORMATION_INT
(
formationId INT,
CONSTRAINT FORMATION_INT_PK PRIMARY KEY(formationId),
CONSTRAINT FORMATION_INT_FORMATION_FK FOREIGN KEY(formationId)
REFERENCES FORMATION(formationId)
);
INSERT INTO FORMATION_INT (formationId)
VALUES
((SELECT formationId FROM FORMATION WHERE formationCode = 'judo'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'karate'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'philo'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'compta'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'geo'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'droit'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'peint'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'astro'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'chimie'))
;
SELECT formationCode, formationNom as formationInterne
FROM FORMATION_INT as x
JOIN FORMATION as y ON x.formationId = y.formationId
;
formationCode formationInterne
judo judo
karate karaté
philo philosophie
compta comptabilité
peint peinture
droit droit
astro astronomie
geo géographie
chimie chimie
Les formations externes
CREATE TABLE FORMATION_EXT
(
formationId INT,
CONSTRAINT FORMATION_EXT_PK PRIMARY KEY(formationId),
CONSTRAINT FORMATION_EXT_FORMATION_FK FOREIGN KEY(formationId)
REFERENCES FORMATION(formationId),
);
Au moins pour respecter la contrainte formulée dans l’énoncé :
GO
CREATE FUNCTION FORMATION_EXT_EXCLUE_FN()
RETURNS INT
AS
BEGIN
DECLARE @n INT
SELECT @n = COUNT(*)
FROM FORMATION_INT as x
JOIN FORMATION_EXT as y ON x.formationId = y.formationId
RETURN @n
END ;
ALTER TABLE FORMATION_EXT
ADD CONSTRAINT FORMATION_EXT_EXCLUE CHECK (dbo.FORMATION_EXT_EXCLUE_FN() = 0) ;
GO
INSERT INTO FORMATION_EXT (formationId)
VALUES
((SELECT formationId FROM FORMATION WHERE formationCode = 'jazzman'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'dirorch'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'pligne'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'moutons'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'agro'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'sculpt'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'api'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'huitres'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'nage'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'cheval'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'plongee'))
, ((SELECT formationId FROM FORMATION WHERE formationCode = 'presti'))
SELECT formationCode, formationNom as formationExterne
FROM FORMATION_EXT as x
JOIN FORMATION as y ON x.formationId = y.formationId
;
formationCode formationExterne
jazzman jazz manouche
dirorch direction d'orchestre
sculpt sculpture
moutons élevage des moutons
pligne pêche à la ligne
cheval équitation
presti prestidigitation
nage natation
agro agronomie
api apiculture
huitres conchyliculture
plongee plongée
Formations suivies par les employés (structure des tables)
CREATE TABLE EMP_FORM_INT
(
employeId INT,
formationId INT,
CONSTRAINT EMP_FORM_INT_PK PRIMARY KEY(employeId, formationId),
CONSTRAINT EMP_FORM_INT_EMPLOYE_FK FOREIGN KEY(employeId)
REFERENCES EMPLOYE(employeId),
CONSTRAINT EMP_FORM_INT_FORMATION_INT_FK FOREIGN KEY(formationId)
REFERENCES FORMATION_INT(formationId)
);
CREATE TABLE EMP_FORM_EXT
(
employeId INT,
formationId INT,
validationParLeChef BIT NOT NULL DEFAULT 0,
CONSTRAINT EMP_FORM_EXT_PK PRIMARY KEY(employeId, formationId),
CONSTRAINT EMP_FORM_EXT_EMP_HIERARCHIE_FK FOREIGN KEY(employeId)
REFERENCES EMP_HIERARCHIE(employeId),
CONSTRAINT EMP_FORM_EXT_FORMATION_EXT_FK FOREIGN KEY(formationId)
REFERENCES FORMATION_EXT(formationId)
);
En prévision des opérations ultérieures : vue globale des formations suivies (internes et externes)
GO
CREATE VIEW FORM_VUE_GLOBALE
(matricule, employe, typeFormation, formation, accordDuChef)
AS
SELECT matricule
, CONCAT(y.employePrenom, ' ' , y.employeNom) as employe
, 'interne' as 'type de formation'
, formationNom as formationInterne
, 'sans objet' as 'accord du chef'
FROM EMP_FORM_INT as x
JOIN EMPLOYE as y ON x.employeId = y.employeId
JOIN FORMATION as z ON x.formationId = z.formationId
UNION
SELECT matricule
, CONCAT(y.employePrenom, ' ' , y.employeNom) as employe
, 'externe'
, formationNom as formationExterne
, "accord du chef" =
CASE
WHEN validationParLeChef = 1
THEN 'oui'
ELSE 'non'
END
FROM EMP_FORM_EXT as x
JOIN EMPLOYE as y ON x.employeId = y.employeId
JOIN FORMATION as z ON x.formationId = z.formationId
;
GO
Surveillance du nombre maximum de formations qu’un employé peut suivre.
Dans ce qui suit on met en oeuvre des fonctions à cet effet. Si les tables étaient volumineuses, il serait préférable d’en passer par des triggers car alors le SGBD transmet seulement les lignes en cours de création ou de modification. Cette remarque vaut pour la fonction FORMATION_EXT_EXCLUE_FN.
Un employé peut suivre au maximum 5 formations. Pour que le SGBD assure, on lui fournit les moyens ad-hoc.
GO
CREATE FUNCTION EMP_FORM_COUNT_GLOBAL_FN()
RETURNS INT
AS
BEGIN
DECLARE @n INT
SELECT @n = MAX(kount)
FROM
(
SELECT COUNT(*) as kount
FROM FORM_VUE_GLOBALE
WHERE accordDuChef IN ('oui', 'sans objet')
GROUP BY matricule
HAVING COUNT(*) > 5
) as maxi
RETURN @n
END ;
GO
ALTER TABLE EMP_FORM_INT
ADD CONSTRAINT EMP_FORM_INT_MAX_GLOBAL CHECK (dbo.EMP_FORM_COUNT_GLOBAL_FN() = 0) ;
ALTER TABLE EMP_FORM_EXT
ADD CONSTRAINT EMP_FORM_EXT_MAX_GLOBAL CHECK (dbo.EMP_FORM_COUNT_GLOBAL_FN() = 0) ;
CREATE FUNCTION EMP_FORM_INT_COUNT_FN()
RETURNS INT
AS
BEGIN
DECLARE @n INT
SELECT @n = COUNT(*)
FROM EMP_FORM_INT
GROUP BY employeId
HAVING COUNT(*) > 5
RETURN @n
END ;
GO
ALTER TABLE EMP_FORM_INT
ADD CONSTRAINT EMP_FORM_INT_MAX CHECK (dbo.EMP_FORM_INT_COUNT_FN() = 0) ;
INSERT INTO EMP_FORM_INT (employeId, formationId)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'compta'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'philo'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'droit'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'geo'))
Quelques formations internes concernant Jean Yes-Sir
INSERT INTO EMP_FORM_INT (employeId, formationId)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'compta'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'judo'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'peint'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'geo'))
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_INT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'astro'))
matricule employe formationInterne
ff Francis Folace philosophie
ff Francis Folace comptabilité
ff Francis Folace droit
ff Francis Folace géographie
jy Jean Yes-Sir judo
jy Jean Yes-Sir comptabilité
jy Jean Yes-Sir peinture
jy Jean Yes-Sir astronomie
jy Jean Yes-Sir géographie
Un employé peut suivre au maximum 4 formations externes, seules sont prises en compte les demandes validées par son chef. Pour que le SGBD assure, on lui fournit les moyens ad-hoc.
CREATE FUNCTION EMP_FORM_EXT_COUNT_FN()
RETURNS INT
AS
BEGIN
DECLARE @n INT
SELECT @n = COUNT(*)
FROM EMP_FORM_EXT
WHERE validationParLeChef = 1
GROUP BY employeId
HAVING COUNT(*) > 4
RETURN @n
END ;
GO
ALTER TABLE EMP_FORM_EXT
ADD CONSTRAINT EMP_FORM_EXT_MAX CHECK (dbo.EMP_FORM_EXT_COUNT_FN() = 0) ;
Quelques formations externes concernant Francis Folace (les formations non validées par son chef comptent pour du beurre.
INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'jazzman')
, 1)
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'pligne')
, 0)
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'plongee')
, 0)
;
Quelques formations externes concernant Jean Yes-Sir
INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'plongee')
, 0)
,
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'presti')
, 0)
;
Voyons voir la situation des formations externes des employés.
SELECT matricule
, CONCAT(y.employePrenom, ' ' , y.employeNom) as employe
, formationNom as formationExterne
, accordDuChef =
CASE
WHEN validationParLeChef = 1
THEN 'oui'
ELSE 'non'
END
FROM EMP_FORM_EXT as x
JOIN EMPLOYE as y ON x.employeId = y.employeId
JOIN FORMATION as z ON x.formationId = z.formationId
;
matricule employe formationExterne accord du chef
ff Francis Folace jazz manouche oui
ff Francis Folace pêche à la ligne non
ff Francis Folace natation non
ff Francis Folace plongée non
jy Jean Yes-Sir prestidigitation non
jy Jean Yes-Sir plongée non
Vue globale (situation de l’ensemble des formations des employés)
SELECT employe, typeFormation, formation, accordDuChef
FROM FORM_VUE_GLOBALE
ORDER BY matricule, typeFormation DESC, accordDuChef DESC, formation
;
employe typeFormation formation accordDuChef
Francis Folace interne comptabilité sans objet
Francis Folace interne droit sans objet
Francis Folace interne géographie sans objet
Francis Folace interne philosophie sans objet
Francis Folace externe jazz manouche oui
Francis Folace externe natation non
Francis Folace externe pêche à la ligne non
Francis Folace externe plongée non
Jean Yes-Sir interne astronomie sans objet
Jean Yes-Sir interne comptabilité sans objet
Jean Yes-Sir interne géographie sans objet
Jean Yes-Sir interne judo sans objet
Jean Yes-Sir interne peinture sans objet
Jean Yes-Sir externe plongée non
Jean Yes-Sir externe prestidigitation non
Tentatives de délit :
INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'ff')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'cheval')
, 1)
;
Msg 547, Niveau 16
L'instruction INSERT est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL".
Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".
INSERT INTO EMP_FORM_EXT(employeId, formationId, validationParLeChef)
VALUES
((SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
, (SELECT x.formationId
FROM FORMATION_EXT as x
JOIN FORMATION AS y ON x.formationId = y.formationId
WHERE formationCode = 'pligne')
, 0)
;
Msg 547, Niveau 16
L'instruction INSERT est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL".
Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".
UPDATE EMP_FORM_EXT
SET validationParLeChef = 1
WHERE employeId = (SELECT employeId FROM EMPLOYE WHERE matricule = 'jy')
AND formationId = (SELECT formationId FROM FORMATION WHERE formationCode = 'plongee')
;
Msg 547, Niveau 16
L'instruction UPDATE est en conflit avec la contrainte CHECK "EMP_FORM_EXT_MAX_GLOBAL".
Le conflit s'est produit dans la base de données "temp", table "dbo.EMP_FORM_EXT".
Désolé, chef !
