Le cas d’étude

 

Nous allons étudier un cas simplifié d’une plateforme e-learning. Cette plateforme permettrait la gestion des certificats d’études avancées.

Les activités des personnes impliquées sont décrites ci-dessous.

Les responsables de formation créent les modules et les cours. Ils assignent les cours aux enseignants. Les étudiants s’inscrivent aux cours. À la fin d’un cours, et après un examen, les enseignants donnent les notes aux étudiants. Si un étudiant obtient la note moyenne pour chaque cours d’un module il réussit la formation et obtient un certificat.

A partir de ce cas nous allons extraire certaines règles afin de mieux décrire notre modèle relationnel.

Un responsable crée plusieurs modules.

Un responsable crée plusieurs cours et les attribue aux modules et aux enseignants.

Un enseignant gère plusieurs cours.

Un étudiant s’inscrit à plusieurs cours.

Un enseignant donne une note d’examen pour chaque étudiant pour chaque cours.

Un étudiant peut obtenir un seul certificat.

Le modèle relationnel

Reprenons ces règles identifiées et modélisons les entités impliquées et leurs relations dans notre cas de la plateforme e-learning.

  1. Un responsable crée plusieurs modules.
    Relation « un à plusieurs », un responsable, plusieurs modules.
  2. Un responsable crée plusieurs cours et les attribue aux modules et aux enseignants.
    Relation « un à plusieurs » entre le responsable et les cours, « un à plusieurs » entre un module et les cours, « plusieurs à plusieurs » entre les enseignants et les cours.
  3. Un enseignant gère plusieurs cours.
    Relation « plusieurs à plusieurs » entre les enseignants et les cours.
  4. Un étudiant s’inscrit à plusieurs cours.
    Relation « plusieurs à plusieurs » entre les étudiants et les cours.
  5. Un enseignant donne une note d’examen pour chaque étudiant pour chaque cours.
    Relation « un à un » entre un étudiant, un cours et une note.
  6. Un étudiant peut obtenir un seul certificat.
    Relation « un à un » entre un étudiant et un certificat.

Cette description nous facilite la création d’un diagramme relationnel qui nous permettra de visualiser les relations.

Diagramme du modèle relationnel.

Langage de définition des données (DDL)

Une partie du langage SQL est dédiée à la définition des données. La syntaxe des expressions utilisées pour ces définitions est suivante:

DROP objecttype objectname

ALTER objecttype objectname parameters

RENAME TABLE old_name TO new_name

TRUNCATE TABLE table_name

(référence : https://en.wikipedia.org/wiki/Data_definition_language)

Nous allons nous en servir pour créer des tables et des relations.

Création des tables et des relations

Nous allons créer les tables personne, module, cours , personne_cours, etu_note.

CREATE TABLE personne ( 
    personne_id INT PRIMARY KEY AUTO_INCREMENT, 
    nom VARCHAR(250) NOT NULL, 
    prenom VARCHAR(250) NOT NULL, 
    sexe CHARACTER, 
    age INT, 
    adresse VARCHAR(250), 
    role VARCHAR(15), 
    CONSTRAINT CHK_personne_sexe CHECK (sexe='H' OR sexe ='F'), 
    CONSTRAINT CHK_personne_role CHECK (role='responsable' OR role='prof' OR role='etudiant') 
);
table_personne.sql
CREATE TABLE module (
    module_id INT PRIMARY KEY AUTO_INCREMENT,
    description VARCHAR(250) NOT NULL,
    responsable_id INT NOT NULL,
    FOREIGN KEY (responsable_id) REFERENCES personne(personne_id) ON DELETE CASCADE
);
table_module.sql
CREATE TABLE cours (
    cours_id INT PRIMARY KEY AUTO_INCREMENT,
    description VARCHAR(250) NOT NULL,
    module_id INT NOT NULL,
    periode DATE,
    FOREIGN KEY (module_id) REFERENCES module(module_id) ON DELETE CASCADE
);
table_cours.sql
CREATE TABLE personne_cours (
    pc_id INT PRIMARY KEY AUTO_INCREMENT,
    pc_personne_id INT,
    pc_cours_id INT,
	inscrit TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pc_personne_id) REFERENCES personne(personne_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (pc_cours_id) REFERENCES cours(cours_id) ON DELETE CASCADE ON UPDATE CASCADE
);
table_personne_cours.sql
CREATE TABLE etu_note (
    etu_note_pc_id INT,
    note FLOAT(3,2), CONSTRAINT CHECK (note > 0 AND note <= 5),
	date_exa DATE,
    FOREIGN KEY (etu_note_pc_id) REFERENCES personne_cours(pc_id) ON DELETE CASCADE ON UPDATE CASCADE
);
table_etu_note.sql
ALTER TABLE personne ADD COLUMN certificat VARCHAR(250) AFTER role;
column_certificat.sql

Langage de manipulation des données (DML)

Une partie du langage SQL nous permet de manipuler les données. La syntaxe des expressions utilisées pour la manipulation est suivante :

SELECT … FROM … WHERE …

INSERT INTO … VALUES …

UPDATE … SET … WHERE …

DELETE FROM … WHERE …

(référence: https://en.wikipedia.org/wiki/Data_manipulation_language)

Sélection

/** Selectionner toutes les colonnes et toutes les données de la table personne **/
SELECT * FROM personne;
Sélection *

Projection

/** Selectionner tous les étudiants de la table personne **/
SELECT * FROM personne WHERE role="etudiant";
WHERE

/** Selectionner toutes les étudiantes de la table personne **/
SELECT * FROM personne WHERE role="etudiant" AND sexe="F";
WHERE ... AND ...

Jointure

/** Selectionner tous les professeurs et leurs cours **/
SELECT personne.nom, personne.prenom, cours.description FROM personne, personne_cours, cours 
WHERE personne.role="professeur" 
AND personne.personne_id = personne_cours.pc_personne_id 
AND personne_cours.pc_cours_id = cours.cours_id
Parcours de plusieurs tables avec une nouvelle table en résultats

Exercices

 

Solution d’exercices.

Ressources

Fichiers PHP-SQL à télécharger: SQL-Requetes-simples.

print