Le projet en quelques mots

Le système d’éducation supérieure dans le monde a beaucoup évolué dans les dernières décennies à cause de plusieurs facteurs, notamment l’ouverture à l’international et les développements en technologie.

Dans le monde d’aujourd’hui, les établissements d’enseignement supérieur s’ouvrent de plus en plus à d’autres pays à travers des programmes d’échanges académiques et des projets en collaboration.

De plus, la technologie est de plus en plus présente dans l’éducation. Grâce à Internet, ils existent aujourd’hui des opportunités d’éducation en ligne, ce qui a donné naissance à des plateformes d’enseignement offrant des programmes diplômant.

Par conséquent, il a fallu développer des systèmes communs d’évaluation de compétences académiques. La solution adoptée par la majorités des établissements d’enseignement supérieur est le crédit académique qui est une unité de mesure de compétences obtenues liée au nombres d’heures passées pour acquérir ces compétences.

Ce système a permit les écoles et les universités de donner plus de libertés aux étudiants dans leurs parcours académiques.

Bien que ce système soit plus libre et démocratique, il est certainement plus complexe à mettre en place et nécessite un modèle bien défini.

Ce projet vise donc à modéliser une base de données relationnelles pour un établissement d’éducation supérieur libre, la base de données conçue est utilisable par un établissement scolaire réel ou une plateforme d’éducation complète. Ce modèle n’existe pas encore, mais s’inspire beaucoup du système existant adopté dans beaucoup de pays.

Le modèle de l’établissement

La philosophie principale de l’établissement est la liberté de l’éducation; dans le sens où les étudiants ont la possibilité de choisir leurs matières en toute liberté. L’établissement offre des formations en domaines différents (sciences de la nature et sciences sociales/humaines). La question qui se pose donc : comment obtenir un diplôme?

Les règles suivantes répondent à cette question :

• L’année est divisée en trois trimestres : l’automne (AUT), le printemps (PRN), et l’été (ETE). Il est possible de commencer ou terminer ses études n’importe quel trimestre.
• Chaque matière est associée à un certain nombre de crédits lié à la quantité de cours et de travail nécessaires pour la valider.
• Un diplôme est obtenu lors de la validation d’un certain nombre crédits divisés en deux catégories : crédits essentiels et crédits complémentaires.
  • Crédits essentiels : Crédits à obtenir en validant des matières associées à ce programme. On dira que ces matières appartiennent à ce programme. Une matière peut appartenir à plusieurs programmes. L’étudiant n’est pas obligé de valider toutes les matières qui appartiennent à un programme pour obtenir le diplôme, il suffit d’obtenir le nombre minimum de crédits essentiels par ces matières.
  • Crédits complémentaires : Ce sont des crédits d’enrichissement personnel. Les étudiants obtiendront ces crédits en validant des matières de leurs choix (hors programme).
• Il n’y a pas de contraintes sur le nombre de crédits/matières qu’un(e) étudiant(e) obtient en un trimestre.
• Un(e) étudiant(e) peut suivre plusieurs programmes diplômants à la fois (ou aucun).
• Les étudiants ont la liberté de viser un ou plusieurs diplômes ou de juste suivre des matières pour s’enrichir.
• L’existence d’un quota pour chaque matière entraine une critère de selection des étudiants, cette critère est détérminée par plusieurs facteurs:
  • Les étudiants qui visent des diplômes contenant tel matière sont prioritaires.
  • Le nombre de fois qu’un(e) étudiant(e) ait suivi la matière antérieurement est un facteur négatif.
  • L’avis du conseiller est un facteur très important.
• Le choix de viser un diplôme est analogue au projet professionnel, il est donc dynamique et évolue au cours des trimestres. On donne alors aux étudiants la possibilité de changer de diplômes pendant la première année à chaque fin de trimestre, il est aussi possible de changer de diplôme plus tard sous-reserve d’avoir au moins 50% de matières communs déjà validées.
• Certaines matières demandes la prédisposition de certaines notions exprimées en fonction d’autres matières dites les prérequises de la matière. Les matières sans prérequis sont dites des matières élémentaires.
• Afin de garder le niveau académique de l’établissement les matières sont catégorisées par domaines avec le poste du responsable académique qui veille sur le bon déroulement des matières de ce domaine. Il se peut que le responsable soient des enseignants.

Modèle Entité/Association

Modèle Entité/Association

Trimestre : Cette entité correspond à une période scolaire identifiée par une clé composée de 2 attributs : année + période. La période est l’une des trois : l’automne, le prinstemps ou l’été, il est possible d’associer une valeur numérique où un code à chaque période afin de minimiser la taille de stockage. Un trimestre est décrit par sa date de début et sa date de fin.

Cours : L’entité cours représente une instance concrète d’une Matière. Elle est identifié par la clé unique idCours et contient les attributs quota et dateExamen.

Professeur : Un professeur a un identifiant unique (idProfesseur) et ses informations de contact en tant qu’attributs.

Domaine : Un domaine est identifié par son nom car il est forcément unique (logiquement, si les domaines sont bien appelés ils portent des noms distinctes).

Matière : L’entité Matière a un identifiant unique et un certain nombre de crédits.

Diplôme : Un diplôme est identifié par la clé idDiplome et est caractérisé par les attributs titreDiplome, creditsEssentiels, et creditsEnrichissement.

Etudiant : Identifié par la clé idEtudiant et caractérisé par ses information de contact.

Conseiller : Identifié par la clé idConseiller et caractérisé par ses information de contact.

Les relations entre les entités

Relations binaires

Se dérouler pendant : Un cours se déroule pendant un trimestre définitivement, plusieurs cours se déroule pendant un trimestre.

Enseigner : Un cours est enseigné par un professeur, un professeur peut enseigné plusieurs cours. Il est possible qu’un professeur n’enseigne pas des cours s’il est responsable académique.

Spécialiser : Un professeur est spécialisé dans un ou plusieurs domaines, dans un domaine spécifique il y a un ou plusieurs professeurs spécialisés.

Être responsable : Un professeur peut être le responsable académique dans un de ces domaines de spécialité, le domaine a un professeur comme responsable académique.

Appartenir : Une matière appartient a un domaine académique, et un domaine contient au moins une matière.

Prérequire : Une matière peut avoir une ou plusieurs matières prérequises, et une matière peut être prérequise par une ou plusieure matières.

Être instance de : Un cours est une instance d’une maitère sur un trimestre, une matière peut être instanciée plusieurs par plusieurs cours.

Suivre : Un étudiant suit un ou plusieurs cours et obtient une note dans chaque cours. Un cours peut être est suivi par un ou plusieurs étudiants.

Se préinscrire : Un ou plusieurs étudiants se pré-inscrivent dans un cours, et en fonction d’une note de priorité en respectant le quota du cours un ou plusieurs étudiants suivent le cours. La liste de pré-inscrits peut contenir un ou plusieurs étudiants, s’il n’y a pas de pré-inscrits le cours ne s’ouvre pas.

Donner conseils : Un conseiller donne des conseils à un ou plusieurs étudiants concernant leur choix des matières, chaque étudiant est prend des conseils d’un conseiller.

Relation ternaire

Préparer :

• Une matière appartient à un ou plusieurs ou aucun programme diplômant, et un programme contient une ou plusieurs matière. (Dans le sens défini en Section 1.2)
• Un étudiant prépare un ou plusieurs ou aucun diplôme, un diplôme peut être préparé par un ou plusieurs étudiants.
• Un étudiant prépare une ou plusieurs matières, et une matière est préparé par un ou plusieurs ou aucun étudiant.

Schéma relationnel

On passe aux schéma relationnel à partir de notre modèle Entité/Association.

Schéma relationnel final

• Cours(code, matiere, quota, quotaExamen, dateDebut, dateExamen, enseignant, annee, periode)
• Professeur(id, nom, prenom, adresse, email, dateNaissance)
• Etudiant(id, nom, prenom, adresse, email, dateNaissance, idConseiller)
• Conseiller(id, nom, prenom, adresse, email, dateNaissance)
• Diplome(id, titre, niveau, creditsEssentiels, creditsEnrichissement)
• PreRequisition(matiere, matiereRequise)
• InscriptionCours(cours, etudiant, suivreCours, inscrit, noteConseiller, noteObtenue)
• Preparer(etudiant, diplome)
• Contenir(diplome, matiere)

Schéma Relationnel

Environnement

Pour des raisons de portabilité, j’ai sauvegardé les scripts SQL permettant de créer la base de donnée, et la populer par les tableau, des données aléatoires pour tester et les requêtes dans ce rapport (repertoire: db).

Pour faciliter la connection, j’ai créé des scripts shell qui lancent les requêtes SQL à partir du terminal (repertoire: bin).

Les tableaux dans ce rapport sont compilé en directe à l’aide des libraries R knitr et DBI.

Le code source du projet y compris ce rapport est disponible sur: github.com/rand-asswad/gm4_bdd_univ

Cette section est plus visible en format HTML: rand-asswad.github.io/gm4_bdd_univ/rapport/index.html

Connection à la base de données:

library(DBI)
univ <- dbConnect(RMariaDB::MariaDB(), dbname="univ", username="root")

Les requêtes

DROP VIEW IF EXISTS Valide;

CREATE VIEW Valide AS
  SELECT Etudiant.id as etudiant, Cours.matiere
  FROM InscriptionCours
  INNER JOIN Cours ON InscriptionCours.cours = Cours.code
  INNER JOIN Etudiant ON InscriptionCours.etudiant = Etudiant.id
  WHERE noteObtenue >= 10;

SELECT Etudiant.nom, Etudiant.prenom, Matiere.code, Matiere.nom, Matiere.credits
FROM Valide
INNER JOIN Etudiant ON Valide.etudiant = Etudiant.id
INNER JOIN Matiere ON Valide.matiere = Matiere.code

DROP View IF EXISTS matieresDiplome;

CREATE VIEW matieresDiplome AS
  SELECT Diplome.id, COUNT(*) AS nbMatieres 
  FROM Contenir
  INNER JOIN Diplome on Contenir.diplome = Diplome.id
  GROUP BY Contenir.diplome;

SELECT Diplome.titre, Diplome.niveau, matieresDiplome.nbMatieres, Diplome.creditsEssentiels
FROM matieresDiplome
INNER JOIN Diplome ON matieresDiplome.id = Diplome.id;

idEtudiant <- 1
idDiplome <- 5
domaine <- "Combinatoires"
codeCours <- "AUT18Ana01"

SELECT Matiere.code, Matiere.nom, Matiere.credits
FROM Matiere
INNER JOIN PreRequisition ON Matiere.code = PreRequisition.matiere
WHERE Matiere.code NOT IN (SELECT matiere
                           FROM Valide
                           WHERE Valide.etudiant = ?idEtudiant)
AND PreRequisition.matiereRequise = ANY (SELECT matiere
                                         FROM Valide
                                         WHERE Valide.etudiant = ?idEtudiant);

SELECT Cours.code, Cours.matiere, Matiere.nom, Matiere.credits, Cours.trimestre, Cours.enseignant
FROM Cours
INNER JOIN Matiere ON Cours.matiere = Matiere.code
INNER JOIN PreRequisition ON Cours.matiere = PreRequisition.matiere
WHERE Matiere.code NOT IN (SELECT matiere
                           FROM Valide
                           WHERE Valide.etudiant = ?idEtudiant)
AND PreRequisition.matiereRequise = ANY (SELECT matiere
                                         FROM Valide
                                         WHERE Valide.etudiant = ?idEtudiant)
AND Cours.annee = Year(curdate());

SELECT Diplome.titre, Diplome.niveau, COUNT(1) as nbValide,
    matieresDiplome.nbMatieres as nbTotal
FROM Contenir
INNER JOIN Diplome ON Contenir.diplome = Diplome.id
INNER JOIN Matiere ON Contenir.matiere = Matiere.code
INNER JOIN Valide ON Matiere.code = Valide.matiere
INNER JOIN matieresDiplome ON Diplome.id = matieresDiplome.id
WHERE Valide.etudiant = ?idEtudiant
GROUP BY Contenir.diplome
ORDER BY nbValide DESC;

SELECT Matiere.code, Matiere.nom, Matiere.credits
FROM Contenir
INNER JOIN Matiere ON Contenir.matiere = Matiere.code
WHERE Matiere.code NOT IN (SELECT Valide.matiere
                           FROM Valide
                           WHERE Valide.etudiant = ?idEtudiant)
AND Contenir.diplome = ?idDiplome;

SELECT Etudiant.id, Etudiant.prenom, Etudiant.nom,
    InscriptionCours.noteConseiller, InscriptionCours.suivreCours
FROM InscriptionCours
INNER JOIN Etudiant ON InscriptionCours.etudiant = Etudiant.id
WHERE InscriptionCours.cours = ?codeCours
ORDER BY InscriptionCours.noteConseiller DESC;

SELECT DISTINCT Professeur.id, Professeur.nom, Professeur.prenom
FROM Cours
INNER JOIN Professeur ON Cours.enseignant = Professeur.id
INNER JOIN Matiere ON Cours.matiere = Matiere.code
WHERE Matiere.nomDomaine = ?domaine;