Bonsoir Ethan,
Dans votre cas, il faut en passer par la généralisation/spécialisation, ce qui donne lieu à la représentation suivante (cas de PowerAMC) :
L’entité-type UTILISATEUR joue le rôle de surtype, tandis que les entités-types ETUDIANT, PROFESSEUR et ADMINISTRATIF jouent le rôle de sous-types, c'est-à-dire que ce sont des entités-types qui héritent des propriétés du surtype.
Cette façon de procéder permet en outre d’établir les associations pertinentes entre les entités-types : si l’on doit savoir, par exemple, quels professeurs dispensent leur enseignement à quels élèves, les utilisateurs administratifs ne sont pas concernés, et l’association ENSEIGNER ne fera intervenir que les sous-types PROFESSEUR et ETUDIANT.
NB. Noter les identifiants alternatifs : utilisateur_login, matricule et numero_ine.
MLD dérivé du MCD :
Du fait de l’héritage, L’AGL a recopié l’attribut utilisateur_id dans l’en-tête des tables ETUDIANT, PROFESSEUR et ADMINISTRATIF.
Code SQL correspondant :
CREATE TABLE UTILISATEUR
(
utilisateur_id INT NOT NULL,
utilisateur_nom VARCHAR(32) NOT NULL,
utilisateur_prenom VARCHAR(32) NOT NULL,
utilisateur_login VARCHAR(32) NOT NULL,
utilisateur_mot_de_passe VARCHAR(32) NOT NULL,
CONSTRAINT UTILISATEUR_PK PRIMARY KEY (utilisateur_id),
CONSTRAINT UTILISATEUR_AK UNIQUE (utilisateur_login)
) ;
CREATE TABLE PROFESSEUR
(
utilisateur_id INT NOT NULL,
CONSTRAINT PROFESSEUR_PK PRIMARY KEY (utilisateur_id),
CONSTRAINT PROFESSEUR_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
REFERENCES UTILISATEUR (utilisateur_id) ON DELETE CASCADE
) ;
CREATE TABLE ETUDIANT
(
utilisateur_id INT NOT NULL,
numero_ine VARCHAR(16) NOT NULL,
CONSTRAINT ETUDIANT_PK PRIMARY KEY (utilisateur_id),
CONSTRAINT ETUDIANT_AK UNIQUE (numero_ine),
CONSTRAINT ETUDIANT_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
REFERENCES UTILISATEUR (utilisateur_id) ON DELETE CASCADE
) ;
CREATE TABLE ADMINISTRATIF
(
utilisateur_id INT NOT NULL,
matricule VARCHAR(16) NOT NULL,
date_embauche DATE NOT NULL,
CONSTRAINT ADMINISTRATIF_PK PRIMARY KEY (utilisateur_id),
CONSTRAINT ADMINISTRATIF_AK UNIQUE (matricule),
CONSTRAINT ADMINISTRATIF_UTILISATEUR_FK FOREIGN KEY (utilisateur_id)
REFERENCES UTILISATEUR (utilisateur_id) ON DELETE CASCADE
) ;
Vous pouvez aussi créer des vues par sous-type, voire une vue globale :
CREATE VIEW INDIVIDU_PROFESSEUR (professeur_id, nom, prenom, login, mot_de_passe) AS
SELECT PROFESSEUR.utilisateur_id, utilisateur_nom, utilisateur_prenom, utilisateur_login, utilisateur_mot_de_passe
FROM PROFESSEUR JOIN UTILISATEUR ON PROFESSEUR.utilisateur_id = UTILISATEUR.utilisateur_id
;
CREATE VIEW INDIVIDU_ETUDIANT (etudiant_id, nom, prenom, login, mot_de_passe, numero_ine) AS
SELECT ETUDIANT.utilisateur_id, utilisateur_nom, utilisateur_prenom, utilisateur_login, utilisateur_mot_de_passe, numero_ine
FROM ETUDIANT JOIN UTILISATEUR ON ETUDIANT.utilisateur_id = UTILISATEUR.utilisateur_id
;
CREATE VIEW INDIVIDU_ADMINISTRATIF (administratif_id, nom, prenom, login, mot_de_passe, matricule, date_embauche) AS
SELECT ADMINISTRATIF.utilisateur_id, utilisateur_nom, utilisateur_prenom, utilisateur_login, utilisateur_mot_de_passe, matricule, date_embauche
FROM ADMINISTRATIF JOIN UTILISATEUR ON ADMINISTRATIF.utilisateur_id = UTILISATEUR.utilisateur_id
;
CREATE VIEW INDIVIDU_TOUT (type_individu, individu_id, nom, prenom, login, mot_de_passe, numero_ine, matricule, date_embauche) AS
SELECT 'étudiant', etudiant_id, nom, prenom, login, mot_de_passe, numero_ine, 'sans objet', 'sans objet'
FROM INDIVIDU_ETUDIANT
UNION
SELECT 'professeur', professeur_id, nom, prenom, login, mot_de_passe, 'sans objet', 'sans objet', 'sans objet'
FROM INDIVIDU_PROFESSEUR
UNION
SELECT 'administratif', administratif_id, nom, prenom, login, mot_de_passe, 'sans objet', matricule, CAST(date_embauche AS CHAR(10))
FROM INDIVIDU_ADMINISTRATIF
;
Un début de jeu d’essai :
INSERT INTO UTILISATEUR (utilisateur_id, utilisateur_nom, utilisateur_prenom, utilisateur_login, utilisateur_mot_de_passe) VALUES
(1, 'Naudin', 'Fernand', 'naudin', '********')
, (2, 'Volfoni', 'Raoul', 'volfor', '********')
, (3, 'Volfoni', 'Paul', 'paulo', '********')
, (4, 'Filoselle', 'Aristide', 'filo', '********')
, (5, 'Haddock', 'Archibald', 'millesabords', '********')
, (6, 'Tournesol', 'Tryphon', 'fonfon', '********')
, (7, 'Lampion', 'Séraphin', 'serapion', '********')
, (8, 'Trancène', 'Jean', 'jeannot', '********')
;
INSERT INTO PROFESSEUR (utilisateur_id) VALUES (1), (2), (3) ;
INSERT INTO ETUDIANT (utilisateur_id, numero_ine) VALUES (4, 'ine01'), (5, 'ine02'), (6, 'ine03') ;
INSERT INTO ADMINISTRATIF (utilisateur_id, matricule, date_embauche) VALUES (7, 'jb_007', '2010-01-01'), (8, 'gf_001', '2010-01-03') ;
Exemple : Qui sont les professeurs ?
SELECT * FROM INDIVIDU_PROFESSEUR ;
professeur_id nom prenom login mot_de_passe
------------- ------ ------- ------ ------------
1 Naudin Fernand naudin ********
2 Volfoni Raoul volfor ********
3 Volfoni Paul paulo ********
Vue globale :
SELECT * FROM INDIVIDU_TOUT ;
type_individu individu_id nom prenom login mot_de_passe numero_ine matricule date_embauche
------------- ----------- --------- -------- ------------ ------------- ---------
professeur 1 Naudin Fernand naudin ******** sans objet sans objet sans objet
professeur 2 Volfoni Raoul volfor ******** sans objet sans objet sans objet
professeur 3 Volfoni Paul paulo ******** sans objet sans objet sans objet
étudiant 4 Filoselle Aristide filo ******** ine01 sans objet sans objet
étudiant 5 Haddock Archibald millesabords ******** ine02 sans objet sans objet
étudiant 6 Tournesol Tryphon fonfon ******** ine03 sans objet sans objet
administratif 7 Lampion Séraphin serapion ******** sans objet jb_007 2010-01-01
administratif 8 Trancène Jean jeannot ******** sans objet gf_001 2010-01-03
Prévoir un trigger pour interdire qu’un étudiant soit aussi professeur ou administratif, etc.
Quel est votre SGBD ?
Avis sur une modélisation Merise
Répondre avec citation
Envoyé par Ethan 0x21
Partager