Corrigé Exercice 01 / Série 01 (ACSI)

Veuillez concevoir un Modèle Conceptuel de Données (MCD) et un Modèle Physique de Données (MPD) pour satisfaire les exigences suivantes :

Un étudiant participe à un examen écrit. L’examen est spécifique à une matière précise et se déroule au cours d’un semestre défini, dans le cadre d’une session d’examen qui se produit à une année universitaire spécifique. Il faut noter la matière, le semestre, la session d’examen et l’année universitaire pour chaque examen écrit auquel participe l’étudiant.
L’examen a lieu dans une localisation spécifique qui peut être une salle de classe, un amphithéâtre ou tout autre lieu approprié au sein du campus universitaire. Il est important de noter le lieu de chaque examen.
Pour assurer l’intégrité de l’examen, un surveillant est nécessaire. Ce surveillant peut avoir plusieurs statuts : il peut être un enseignant, un administrateur ou toute autre personne désignée par l’université pour superviser le déroulement de l’examen.

A partir de ce scénario, vous devez identifier les entités principales, les attributs pertinents pour chaque entité et les relations entre ces entités afin de construire le MCD. Ensuite, vous transformerez ce MCD en MPD, en définissant les tables de la base de données, leurs clés primaires et étrangères, tout en respectant les règles de normalisation.

Modèle conceptuel de données (MPD) :

Entités :
- Étudiant
- Examen
- Matière
- Semestre
- Session
- AnnéeUniversitaire
- Lieu
- Surveillant
- Statut
Attributs :
- Étudiant : id_étudiant, nom, prénom, date_de_naissance, etc.
- Examen : id_examen, date, heure, etc.
- Matière : id_matière, nom, etc.
- Semestre : id_semestre, numéro
- Session : id_session, type (normale, rattrapage)
- AnnéeUniversitaire : id_année_universitaire, année_de_début, année_de_fin
- Lieu : id_lieu, nom, type (salle, amphi), capacité, etc.
- Surveillant : id_surveillant, nom, prénom, etc.
- Statut: id_statut, occupation (enseignant, administrateur)
Relations :

Une relation « passe » entre Étudiant et Examen :
– Un étudiant passe un ou plusieurs examens
– Un examen est passé par un ou plusieurs étudiants
Une relation « concerne » entre Examen et Matière :
– Un examen concerne une et une seule matière
– Une matière peut concerner plusieurs examens
Une relation « a_lieu » entre Examen et Lieu :
– Un examen a lieu dans un et un seul lieu
– Un lieu peut accueillir plusieurs examens
Une relation « surveille » entre Surveillant et Examen :
– Un surveillant surveille un ou plusieurs examens
– Un examen est surveillé par un et un seul surveillant
Une relation « a_pour » entre Surveillant et Statut:
– Un surveillant a une et une seule statut
– Un statut peut concerner plusieurs surveillants
Une relation « durant » entre Examen et Semestre, Session, AnnéeUniversitaire :
– Un examen a lieu durant un et un seul semestre, une et une seule session, et une et une seule année universitaire

Cardinalités :
- passe(Étudiant, Examen) : (1, N) – (1, N)
- concerne(Examen, Matière) : (1, 1) – (1, N)
- a_lieu(Examen, Lieu) : (1, 1) – (1, N)
- surveille(Surveillant, Examen) : (1, N) – (1, 1)
- a_pour(Surveillant, Occupation) : (1, 1) – (1, N)
- durant(Examen, Semestre, Session, AnnéeUniversitaire) : (1, 1) – (1, N)

Modèle conceptuel de données (MCD) :

Modèle logique de données textuel (MCD) :

Etudiant = (id INT, nom VARCHAR(50), prenom VARCHAR(50));
Matiere = (id INT, nom_matiere VARCHAR(50), coefficient INT);
Semestre = (id INT, numero INT);
Annee_Universitaire = (id INT, annee_debut DATE, annee_fin DATE);
Type_Lieu_Examen = (id INT, type VARCHAR(50));
Statut = (id INT, intitule VARCHAR(50));
Surveillant = (id INT, nom VARCHAR(50), prenom VARCHAR(50), statut VARCHAR(50), #id_Statut);
Session = (id INT, type_session VARCHAR(50), #id_Annee_Universitaire);
LieuExamen = (id INT, numero INT, capacite INT, #id_Type_Lieu_Examen);
Examen = (id INT, date_heure DATETIME, #id_LieuExamen, #id_Session, #id_Semestre*, #id_Surveillant, #id_Matiere);
Participation = (#id_Etudiant, #id_Examen, note DECIMAL(2,2));

Modèle physique de données (MPD) :

Code SQL :

CREATE TABLE Etudiant(
   id INT,
   nom VARCHAR(50),
   prenom VARCHAR(50),
   PRIMARY KEY(id)
);

CREATE TABLE Matiere(
   id INT,
   nom_matiere VARCHAR(50),
   coefficient INT,
   PRIMARY KEY(id)
);

CREATE TABLE Semestre(
   id INT,
   numero INT,
   PRIMARY KEY(id)
);

CREATE TABLE Annee_Universitaire(
   id INT,
   annee_debut DATE,
   annee_fin DATE,
   PRIMARY KEY(id)
);

CREATE TABLE Type_Lieu_Examen(
   id INT,
   type VARCHAR(50),
   PRIMARY KEY(id)
);

CREATE TABLE Statut(
   id INT,
   intitule VARCHAR(50),
   PRIMARY KEY(id)
);

CREATE TABLE Surveillant(
   id INT,
   nom VARCHAR(50),
   prenom VARCHAR(50),
   statut VARCHAR(50),
   id_Statut INT NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(id_Statut) REFERENCES Statut(id)
);

CREATE TABLE Session(
   id INT,
   type_session VARCHAR(50),
   id_Annee_Universitaire INT NOT NULL,
   PRIMARY KEY(id),
   UNIQUE(id_Annee_Universitaire),
   FOREIGN KEY(id_Annee_Universitaire) REFERENCES Annee_Universitaire(id)
);

CREATE TABLE LieuExamen(
   id INT,
   numero INT,
   capacite INT,
   id_Type_Lieu_Examen INT NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(id_Type_Lieu_Examen) REFERENCES Type_Lieu_Examen(id)
);

CREATE TABLE Examen(
   id INT,
   date_heure DATETIME,
   id_LieuExamen INT NOT NULL,
   id_Session INT NOT NULL,
   id_Semestre INT,
   id_Surveillant INT NOT NULL,
   id_Matiere INT NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(id_LieuExamen) REFERENCES LieuExamen(id),
   FOREIGN KEY(id_Session) REFERENCES Session(id),
   FOREIGN KEY(id_Semestre) REFERENCES Semestre(id),
   FOREIGN KEY(id_Surveillant) REFERENCES Surveillant(id),
   FOREIGN KEY(id_Matiere) REFERENCES Matiere(id)
);

CREATE TABLE Participation(
   id_Etudiant INT,
   id_Examen INT,
   note DECIMAL(2,2),
   PRIMARY KEY(id_Etudiant, id_Examen),
   FOREIGN KEY(id_Etudiant) REFERENCES Etudiant(id),
   FOREIGN KEY(id_Examen) REFERENCES Examen(id)
);