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.

[pastacode lang=”sql” manual=”CREATE%20TABLE%20personne%20(%20%0A%20%20%20%20personne_id%20INT%20PRIMARY%20KEY%20AUTO_INCREMENT%2C%20%0A%20%20%20%20nom%20VARCHAR(250)%20NOT%20NULL%2C%20%0A%20%20%20%20prenom%20VARCHAR(250)%20NOT%20NULL%2C%20%0A%20%20%20%20sexe%20CHARACTER%2C%20%0A%20%20%20%20age%20INT%2C%20%0A%20%20%20%20adresse%20VARCHAR(250)%2C%20%0A%20%20%20%20role%20VARCHAR(15)%2C%20%0A%20%20%20%20CONSTRAINT%20CHK_personne_sexe%20CHECK%20(sexe%3D’H’%20OR%20sexe%20%3D’F’)%2C%20%0A%20%20%20%20CONSTRAINT%20CHK_personne_role%20CHECK%20(role%3D’responsable’%20OR%20role%3D’prof’%20OR%20role%3D’etudiant’)%20%0A)%3B%0A” message=”table_personne.sql” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”CREATE%20TABLE%20module%20(%0A%20%20%20%20module_id%20INT%20PRIMARY%20KEY%20AUTO_INCREMENT%2C%0A%20%20%20%20description%20VARCHAR(250)%20NOT%20NULL%2C%0A%20%20%20%20responsable_id%20INT%20NOT%20NULL%2C%0A%20%20%20%20FOREIGN%20KEY%20(responsable_id)%20REFERENCES%20personne(personne_id)%20ON%20DELETE%20CASCADE%0A)%3B” message=”table_module.sql” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”CREATE%20TABLE%20cours%20(%0A%20%20%20%20cours_id%20INT%20PRIMARY%20KEY%20AUTO_INCREMENT%2C%0A%20%20%20%20description%20VARCHAR(250)%20NOT%20NULL%2C%0A%20%20%20%20module_id%20INT%20NOT%20NULL%2C%0A%20%20%20%20periode%20DATE%2C%0A%20%20%20%20FOREIGN%20KEY%20(module_id)%20REFERENCES%20module(module_id)%20ON%20DELETE%20CASCADE%0A)%3B” message=”table_cours.sql” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”CREATE%20TABLE%20personne_cours%20(%0A%20%20%20%20pc_id%20INT%20PRIMARY%20KEY%20AUTO_INCREMENT%2C%0A%20%20%20%20pc_personne_id%20INT%2C%0A%20%20%20%20pc_cours_id%20INT%2C%0A%09inscrit%20TIMESTAMP%20DEFAULT%20CURRENT_TIMESTAMP%2C%0A%20%20%20%20FOREIGN%20KEY%20(pc_personne_id)%20REFERENCES%20personne(personne_id)%20ON%20DELETE%20CASCADE%20ON%20UPDATE%20CASCADE%2C%0A%20%20%20%20FOREIGN%20KEY%20(pc_cours_id)%20REFERENCES%20cours(cours_id)%20ON%20DELETE%20CASCADE%20ON%20UPDATE%20CASCADE%0A)%3B” message=”table_personne_cours.sql” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”CREATE%20TABLE%20etu_note%20(%0A%20%20%20%20etu_note_pc_id%20INT%2C%0A%20%20%20%20note%20FLOAT(3%2C2)%2C%20CONSTRAINT%20CHECK%20(note%20%3E%200%20AND%20note%20%3C%3D%205)%2C%0A%09date_exa%20DATE%2C%0A%20%20%20%20FOREIGN%20KEY%20(etu_note_pc_id)%20REFERENCES%20personne_cours(pc_id)%20ON%20DELETE%20CASCADE%20ON%20UPDATE%20CASCADE%0A)%3B” message=”table_etu_note.sql” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”ALTER%20TABLE%20personne%20ADD%20COLUMN%20certificat%20VARCHAR(250)%20AFTER%20role%3B” message=”column_certificat.sql” highlight=”” provider=”manual”/]

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

[pastacode lang=”sql” manual=”%2F**%20Selectionner%20toutes%20les%20colonnes%20et%20toutes%20les%20donn%C3%A9es%20de%20la%20table%20personne%20**%2F%0ASELECT%20*%20FROM%20personne%3B” message=”Sélection *” highlight=”” provider=”manual”/]

Projection

[pastacode lang=”sql” manual=”%2F**%20Selectionner%20tous%20les%20%C3%A9tudiants%20de%20la%20table%20personne%20**%2F%0ASELECT%20*%20FROM%20personne%20WHERE%20role%3D%22etudiant%22%3B” message=”WHERE” highlight=”” provider=”manual”/]
[pastacode lang=”sql” manual=”%2F**%20Selectionner%20toutes%20les%20%C3%A9tudiantes%20de%20la%20table%20personne%20**%2F%0ASELECT%20*%20FROM%20personne%20WHERE%20role%3D%22etudiant%22%20AND%20sexe%3D%22F%22%3B” message=”WHERE … AND …” highlight=”” provider=”manual”/]

Jointure

[pastacode lang=”sql” manual=”%2F**%20Selectionner%20tous%20les%20professeurs%20et%20leurs%20cours%20**%2F%0ASELECT%20personne.nom%2C%20personne.prenom%2C%20cours.description%20FROM%20personne%2C%20personne_cours%2C%20cours%20%0AWHERE%20personne.role%3D%22professeur%22%20%0AAND%20personne.personne_id%20%3D%20personne_cours.pc_personne_id%20%0AAND%20personne_cours.pc_cours_id%20%3D%20cours.cours_id” message=”Parcours de plusieurs tables avec une nouvelle table en résultats” highlight=”” provider=”manual”/]

Exercices

Solution d’exercices.

Ressources

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

Références

1. https://www.w3schools.com/sql/default.asp
2. https://dev.mysql.com/doc/refman/5.7/en/