Bonsoir,
Envoyé par
lemagnifique20
Je ne cherche pas à savoir quand est-ce que le personnel est affecté dans une classe, mais quel sous type de la table Personnel est intervenu, et plus exactement l'implémentation (requête) pour l'obtention d'un tel résultat.
Caveat : Tout ce qui suit a été réalisé avec SQL Server.
Allons-y pour un procédé (il y en a d’autres...) pour voir les types de personnes affectés aux classes.
Créons d’abord les tables, dans le style de ce qu’a généré Looping (post #2) :
CREATE TABLE ECOLE
(
ecoleCode CHAR(4) NOT NULL
, ecoleNom VARCHAR(32) NOT NULL
, CONSTRAINT ECOLE_PK PRIMARY KEY (ecoleCode)
) ;
CREATE TABLE PERSONNE
(
personneCode CHAR(4) NOT NULL
, personneNom VARCHAR(32) NOT NULL
, CONSTRAINT PERSONNE_PK PRIMARY KEY (personneCode)
) ;
CREATE TABLE PERMANENT
(
personneCode CHAR(4) NOT NULL
, niveauEtude INT NOT NULL
, CONSTRAINT PERMANENT_PK PRIMARY KEY (personneCode)
, CONSTRAINT PERMANENT_PERSONNE_FK FOREIGN KEY (personneCode)
REFERENCES PERSONNE
) ;
CREATE TABLE VACATAIRE
(
personneCode CHAR(4) NOT NULL
, tauxHoraire INT NOT NULL
, CONSTRAINT VACATAIRE_PK PRIMARY KEY (personneCode)
, CONSTRAINT VACATAIRE_PERSONNE_FK FOREIGN KEY (personneCode)
REFERENCES PERSONNE
) ;
CREATE TABLE ASSIGNER
(
personneCode CHAR(4) NOT NULL
, ecoleCode CHAR(4) NOT NULL
, CONSTRAINT ASSIGNER_PK PRIMARY KEY (personneCode, ecoleCode)
, CONSTRAINT ASSIGNER_PERSONNE_FK FOREIGN KEY (personneCode)
REFERENCES PERSONNE (personneCode)
, CONSTRAINT ASSIGNER_ECOLE_FK FOREIGN KEY (ecoleCode)
REFERENCES ECOLE (ecoleCode)
) ;
CREATE TABLE CLASSE
(
classeCode CHAR(4) NOT NULL
, classeNom CHAR(24) NOT NULL
, personneCode CHAR(4) NOT NULL
, ecoleCode CHAR(4) NOT NULL
, CONSTRAINT CLASSE_PK PRIMARY KEY (classeCode)
, CONSTRAINT CLASSE_ASSIGNER_FK FOREIGN KEY (personneCode, ecoleCode)
REFERENCES ASSIGNER (personneCode, ecoleCode)
) ;
Assurons l’exclusion entre les sous-types PERMANENT et VACATAIRE.
GO
CREATE FUNCTION PERSONNE_TYPE_EXCLUSION()
RETURNS INT
AS
BEGIN
DECLARE @n INT
SELECT @n = COUNT(*)
FROM PERMANENT as x
JOIN VACATAIRE as y ON x.personneCode = y.personneCode
RETURN @n
END ;
GO
ALTER TABLE PERMANENT
ADD CONSTRAINT PERMANENT_EXCLUSION_CHK
CHECK (dbo.PERSONNE_TYPE_EXCLUSION() = 0) ;
ALTER TABLE VACATAIRE
ADD CONSTRAINT VACATAIRE_EXCLUSION_CHK
CHECK (dbo.PERSONNE_TYPE_EXCLUSION() = 0) ;
Une fonction pour connaître le rôle d’une personne :
GO
-- La fonction PERSONNE_TYPE a en entrée le code d'une personne
-- et fournit en retour le type de celle-ci ('p' pour permanent,
-- 'v' pour vacataire et '?' si elle n'est ni l'un ni l'autre).
CREATE FUNCTION PERSONNE_TYPE(@personneCode char(4))
RETURNS CHAR(1)
AS
BEGIN
DECLARE @n INT ;
SELECT @n = COUNT(*) FROM PERMANENT WHERE personneCode = @personneCode
IF @n > 0
BEGIN
RETURN 'p'
END
ELSE
SELECT @n = COUNT(*) FROM VACATAIRE WHERE personneCode = @personneCode
IF @n > 0
BEGIN
RETURN 'v'
END
-- La personne n'est ni un permnent ni un vacataire
RETURN '?'
END ;
GO
Un jeu d’essai :
INSERT INTO ECOLE (ecoleCode, ecoleNom)
VALUES
('e001', 'Ecole 1'), ('e002', 'Ecole 2')
, ('e003', 'Ecole 3'), ('e004', 'Ecole 4')
;
INSERT INTO PERSONNE (personneCode, personneNom)
VALUES
('p001', 'Personne 1'), ('p002', 'Personne 2')
, ('p003', 'Personne 3'), ('p004', 'Personne 4')
;
INSERT INTO PERMANENT (personneCode, niveauEtude)
VALUES
('p001', 3)
;
INSERT INTO VACATAIRE (personneCode, tauxHoraire)
VALUES
('p002', 100), ('p004', 100)
INSERT INTO ASSIGNER (personneCode, ecoleCode)
VALUES
('p001', 'e001'), ('p001', 'e003')
, ('p002', 'e001'), ('p002', 'e002')
, ('p003', 'e001'), ('p003', 'e002'), ('p003', 'e004')
, ('p004', 'e002'), ('p004', 'e003'), ('p004', 'e004')
;
INSERT INTO CLASSE(classeCode, classeNom, personneCode, ecoleCode)
VALUES
('c001', 'classe 1', 'p001', 'e001')
, ('c002', 'classe 2', 'p001', 'e001')
, ('c003', 'classe 3', 'p002', 'e001')
, ('c004', 'classe 4', 'p002', 'e001')
, ('c005', 'classe 5', 'p002', 'e002')
, ('c006', 'classe 6', 'p004', 'e003')
, ('c007', 'classe 7', 'p003', 'e002')
;
Voyons voir le type de personne affecté à une classe :
SELECT classeCode, personneCode, dbo.PERSONNE_TYPE(personneCode) as personneType
FROM CLASSE ;
classeCode personneCode personneType
c001 p001 p
c002 p001 p
c003 p002 v
c004 p002 v
c005 p002 v
c006 p004 v
c007 p003 ?
La notion d'héritage de MERISE 2 dans HyperFile
Répondre avec citation
... En tout cas, il faut travailler sur cette base.
Partager