Base de données University : structure et description des tables
University DB est une base de données d'exemple moderne pour MariaDB 11.7+ conçue pour l'apprentissage du SQL — pensée comme un remplacement riche en fonctionnalités de la base de données classique Sakila.
Elle couvre tous les types de données significatifs de MariaDB, notamment VECTOR(1536), JSON, SET et les index FULLTEXT, est entièrement normalisée en 3FN et contient suffisamment de données pour les exercices débutants comme pour les requêtes analytiques complexes.
La base de données University contient 16 tables principales décrivant la structure académique d'une université : départements, corps enseignant, étudiants, cours, inscriptions, projets de recherche, et bien plus encore.
Schéma ER de la base de données University
Liste des tables
semesters - table des semestres académiques.
- semester_ididentifiant unique de l'enregistrement (PK, TINYINT)
- termtype de période : Fall, Spring ou Summer (ENUM)
- academic_yearannée académique (type YEAR)
- namenom du semestre (ex. 'Fall 2024')
- start_datepremier jour du semestre
- end_datedernier jour du semestre
- enroll_deadlinedernière date pour l'inscription des étudiants
- is_activeindique si le semestre est actuellement en cours (BOOLEAN)
| semester_id |
term |
academic_year |
name |
start_date |
end_date |
enroll_deadline |
is_active |
| 1 |
Fall |
2024 |
Fall 2024 |
2024-09-02 |
2024-12-20 |
2024-09-13 |
1 |
- PRIMARY KEY, btree (semester_id)
- UNIQUE KEY (term, academic_year)
rooms - salles de cours et laboratoires du campus.
- room_ididentifiant unique de l'enregistrement (PK, SMALLINT)
- buildingnom du bâtiment
- room_numbernuméro ou étiquette de la salle
- capacitynombre maximum de places (SMALLINT)
- room_typetype de salle : lecture, seminar, lab, computer_lab ou online (ENUM)
- has_projectorindique si la salle est équipée d'un projecteur (BOOLEAN)
- has_videoindique si la salle est équipée d'un système de vidéoconférence (BOOLEAN)
| room_id |
building |
room_number |
capacity |
room_type |
has_projector |
has_video |
| 1 |
Science Hall |
101 |
120 |
lecture |
1 |
0 |
- PRIMARY KEY, btree (room_id)
- UNIQUE KEY (building, room_number)
scholarships - bourses disponibles.
- scholarship_ididentifiant unique de l'enregistrement (PK, SMALLINT)
- namenom de la bourse
- amountmontant de la récompense (DECIMAL)
- frequencyfréquence d'attribution : one-time, annual ou per-semester (ENUM)
- eligibilitycritères d'éligibilité au format JSON — ex.
{"min_gpa": 3.5, "need_based": true}
- is_activeindique si la bourse est actuellement proposée (BOOLEAN)
| scholarship_id |
name |
amount |
frequency |
eligibility |
is_active |
| 1 |
Dean's Excellence Award |
5000.00 |
annual |
{"min_gpa": 3.8, "need_based": false, "majors": ["CS","Math"]} |
1 |
- PRIMARY KEY, btree (scholarship_id)
departments - hiérarchie de départements à trois niveaux (Faculté → Département → Sous-département).
- department_ididentifiant unique de l'enregistrement (PK, TINYINT)
- parent_ididentifiant du département parent — FK auto-référençante (nullable)
- codecode court du département (CHAR)
- namenom du département
- levelniveau hiérarchique : 1 = Faculté, 2 = Département, 3 = Sous-département (TINYINT)
- head_faculty_ididentifiant du responsable du département (FK, nullable)
- establishedannée de création du département (YEAR, nullable)
| department_id |
parent_id |
code |
name |
level |
head_faculty_id |
established |
| 1 |
[null] |
ENG |
Faculty of Engineering |
1 |
1 |
1965 |
- PRIMARY KEY, btree (department_id)
- UNIQUE KEY (code)
- FOREIGN KEY (parent_id) REFERENCES departments(department_id)
- FOREIGN KEY (head_faculty_id) REFERENCES faculty(faculty_id)
faculty - membres du corps enseignant de l'université.
- faculty_ididentifiant unique de l'enregistrement (PK, SMALLINT)
- department_ididentifiant du département (FK)
- first_nameprénom du membre du corps enseignant
- last_namenom de famille du membre du corps enseignant
- emailadresse e-mail institutionnelle
- phonenuméro de téléphone du bureau (nullable)
- rankrang académique : Instructor, Assistant Professor, Associate Professor, Professor ou Emeritus (ENUM)
- hire_datedate d'embauche
- officenuméro de bureau ou emplacement (nullable)
- office_hoursheures de permanence hebdomadaires au format tableau JSON — ex.
[{"day":"Mon","start":"10:00","end":"12:00"}]
- biotexte biographique (TEXT, nullable)
- is_activeindique si le membre du corps enseignant est actuellement en activité (BOOLEAN)
| faculty_id |
department_id |
first_name |
last_name |
email |
phone |
rank |
hire_date |
office |
office_hours |
bio |
is_active |
| 1 |
3 |
Alice |
Carter |
a.carter@university.edu |
+15550100 |
Professor |
2010-08-15 |
ENG-204 |
[{"day":"Mon","start":"10:00","end":"12:00"}] |
Expert in distributed systems. |
1 |
- PRIMARY KEY, btree (faculty_id)
- UNIQUE KEY (email)
- FOREIGN KEY (department_id) REFERENCES departments(department_id)
students - étudiants inscrits.
- student_ididentifiant unique de l'enregistrement (PK, INT)
- department_ididentifiant du département d'appartenance (FK)
- student_numbernuméro d'étudiant unique (CHAR, ex. 'S000123')
- first_nameprénom de l'étudiant
- last_namenom de famille de l'étudiant
- emailadresse e-mail de l'étudiant
- date_of_birthdate de naissance de l'étudiant
- gendergenre : M, F, NB, Other ou Prefer not to say (ENUM, nullable)
- enrollment_datedate de la première inscription de l'étudiant
- expected_gradannée de diplôme prévue (YEAR, nullable)
- statusstatut d'inscription : active, inactive, graduated, suspended ou withdrawn (ENUM)
- gpamoyenne générale cumulative 0,000–4,000, maintenue par déclencheur (DECIMAL, nullable)
- contactscontact d'urgence et adresse au format JSON — ex.
{"emergency":{"name":"Jane Doe","phone":"+1-555-0100"}}
| student_id |
department_id |
student_number |
first_name |
last_name |
email |
date_of_birth |
gender |
enrollment_date |
expected_grad |
status |
gpa |
contacts |
| 1 |
3 |
S000123 |
James |
Miller |
j.miller@student.edu |
2002-04-23 |
M |
2021-09-01 |
2025 |
active |
3.720 |
{"emergency":{"name":"Susan Miller","phone":"+1-555-0100"}} |
- PRIMARY KEY, btree (student_id)
- UNIQUE KEY (student_number)
- UNIQUE KEY (email)
- FOREIGN KEY (department_id) REFERENCES departments(department_id)
courses - catalogue des cours avec prise en charge de la recherche plein texte et vectorielle.
- course_ididentifiant unique de l'enregistrement (PK, SMALLINT)
- department_ididentifiant du département propriétaire (FK)
- codecode du cours, ex. 'CS101' (CHAR)
- titleintitulé du cours
- creditsnombre de crédits (TINYINT)
- levelniveau académique : undergraduate, graduate ou doctoral (ENUM)
- descriptiondescription détaillée du cours (TEXT, index FULLTEXT avec title)
- is_activeindique si le cours est actuellement proposé (BOOLEAN)
- embeddingembedding sémantique de 1536 dimensions pour la recherche par similarité vectorielle (VECTOR(1536), nullable)
| course_id |
department_id |
code |
title |
credits |
level |
description |
is_active |
embedding |
| 1 |
3 |
CS301 |
Database Systems |
3 |
undergraduate |
Introduction to relational databases, SQL, and data modeling. |
1 |
[0.023, -0.011, ...] |
- PRIMARY KEY, btree (course_id)
- UNIQUE KEY (code)
- FULLTEXT (title, description)
- FOREIGN KEY (department_id) REFERENCES departments(department_id)
course_prerequisites - relations de prérequis entre cours (many-to-many auto-référençante).
- course_ididentifiant du cours (FK)
- prerequisite_ididentifiant du cours prérequis obligatoire (FK)
- is_mandatoryindique si le prérequis est obligatoire ou recommandé (BOOLEAN)
| course_id |
prerequisite_id |
is_mandatory |
| 5 |
1 |
1 |
- PRIMARY KEY, btree (course_id, prerequisite_id)
- FOREIGN KEY (course_id) REFERENCES courses(course_id)
- FOREIGN KEY (prerequisite_id) REFERENCES courses(course_id)
sections - groupes de cours (une offre spécifique d'un cours au cours d'un semestre).
- section_ididentifiant unique de l'enregistrement (PK, INT)
- course_ididentifiant du cours (FK)
- semester_ididentifiant du semestre (FK)
- faculty_ididentifiant de l'enseignant (FK)
- room_ididentifiant de la salle attribuée (FK, nullable — NULL pour les cours entièrement en ligne)
- section_numbernuméro du groupe au sein du cours et du semestre (TINYINT)
- deliverymode d'enseignement : in-person, online ou hybrid (ENUM)
- max_capacitynombre maximum d'inscriptions (SMALLINT)
- statusstatut du groupe : open, closed, cancelled ou completed (ENUM)
- schedulehoraires hebdomadaires au format JSON — ex.
[{"day":"Mon","start":"09:00","end":"10:30"}]
| section_id |
course_id |
semester_id |
faculty_id |
room_id |
section_number |
delivery |
max_capacity |
status |
schedule |
| 1 |
1 |
1 |
1 |
1 |
1 |
in-person |
30 |
open |
[{"day":"Mon","start":"09:00","end":"10:30"},{"day":"Wed","start":"09:00","end":"10:30"}] |
- PRIMARY KEY, btree (section_id)
- UNIQUE KEY (course_id, semester_id, section_number)
- FOREIGN KEY (course_id) REFERENCES courses(course_id)
- FOREIGN KEY (semester_id) REFERENCES semesters(semester_id)
- FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
- FOREIGN KEY (room_id) REFERENCES rooms(room_id)
enrollments - inscriptions des étudiants aux groupes de cours.
- enrollment_ididentifiant unique de l'enregistrement (PK, INT)
- student_ididentifiant de l'étudiant (FK)
- section_ididentifiant du groupe (FK)
- enrolled_atdate et heure de l'inscription (TIMESTAMP)
- statusstatut d'inscription : enrolled, dropped, completed, failed ou incomplete (ENUM)
- final_gradenote finale en lettre, ex. 'A', 'B+' (CHAR, nullable)
- final_scorescore numérique final 0,00–100,00 (DECIMAL, nullable)
| enrollment_id |
student_id |
section_id |
enrolled_at |
status |
final_grade |
final_score |
| 1 |
1 |
1 |
2024-08-25 10:34:02 |
completed |
A |
93.50 |
- PRIMARY KEY, btree (enrollment_id)
- UNIQUE KEY (student_id, section_id)
- FOREIGN KEY (student_id) REFERENCES students(student_id)
- FOREIGN KEY (section_id) REFERENCES sections(section_id)
student_scholarships - bourses attribuées aux étudiants.
- award_ididentifiant unique de l'enregistrement (PK, INT)
- student_ididentifiant de l'étudiant (FK)
- scholarship_ididentifiant de la bourse (FK)
- awarded_datedate d'attribution de la bourse
- expires_datedate d'expiration de la récompense (nullable)
- amount_awardedmontant réellement attribué (DECIMAL)
- notesnotes supplémentaires sur la récompense (TEXT, nullable)
| award_id |
student_id |
scholarship_id |
awarded_date |
expires_date |
amount_awarded |
notes |
| 1 |
1 |
1 |
2024-09-01 |
2025-08-31 |
5000.00 |
[null] |
- PRIMARY KEY, btree (award_id)
- FOREIGN KEY (student_id) REFERENCES students(student_id)
- FOREIGN KEY (scholarship_id) REFERENCES scholarships(scholarship_id)
research_projects - projets de recherche des départements.
- project_ididentifiant unique de l'enregistrement (PK, SMALLINT)
- department_ididentifiant du département (FK)
- lead_faculty_idinvestigateur principal (FK)
- titletitre du projet
- abstractdescription du projet (TEXT, nullable)
- start_datedate de début du projet
- end_datedate de fin du projet (nullable)
- statusstatut du projet : proposed, active, completed ou cancelled (ENUM)
- fundingsources de financement au format JSON — ex.
[{"source":"NSF","amount":150000,"grant_id":"NSF-2024-001"}]
| project_id |
department_id |
lead_faculty_id |
title |
abstract |
start_date |
end_date |
status |
funding |
| 1 |
5 |
1 |
AI-Assisted Drug Discovery |
Using machine learning to identify candidate molecules. |
2023-01-15 |
[null] |
active |
[{"source":"NSF","amount":150000,"grant_id":"NSF-2023-042"}] |
- PRIMARY KEY, btree (project_id)
- FOREIGN KEY (department_id) REFERENCES departments(department_id)
- FOREIGN KEY (lead_faculty_id) REFERENCES faculty(faculty_id)
publications - publications de recherche avec prise en charge de la recherche plein texte.
- publication_ididentifiant unique de l'enregistrement (PK, INT)
- project_idprojet de recherche associé (FK, nullable)
- titletitre de la publication
- abstractrésumé de la publication (MEDIUMTEXT, index FULLTEXT avec title)
- pub_yearannée de publication (YEAR)
- venuenom du journal ou de la conférence (nullable)
- doiidentifiant numérique d'objet DOI (nullable)
- keywordsmots-clés — une ou plusieurs valeurs parmi : AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
- citation_countnombre de citations reçues (INT)
| publication_id |
project_id |
title |
abstract |
pub_year |
venue |
doi |
keywords |
citation_count |
| 1 |
1 |
Deep Learning for Molecular Screening |
We present a transformer-based architecture for virtual screening... |
2024 |
Nature Machine Intelligence |
10.1038/s42256-024-00001-1 |
AI,ML,Bioinformatics |
12 |
- PRIMARY KEY, btree (publication_id)
- UNIQUE KEY (doi)
- FULLTEXT (title, abstract)
- FOREIGN KEY (project_id) REFERENCES research_projects(project_id)
project_members - participation des enseignants et des étudiants aux projets de recherche.
- member_ididentifiant unique de l'enregistrement (PK, INT)
- project_ididentifiant du projet de recherche (FK)
- faculty_ididentifiant du membre du corps enseignant (FK, nullable)
- student_ididentifiant de l'étudiant (FK, nullable)
- rolerôle du membre : Principal Investigator, Co-Investigator, Research Assistant, Graduate Student ou Undergraduate Student (ENUM)
- joined_datedate d'intégration du membre au projet
- left_datedate de départ du membre du projet (nullable)
| member_id |
project_id |
faculty_id |
student_id |
role |
joined_date |
left_date |
| 1 |
1 |
1 |
[null] |
Principal Investigator |
2023-01-15 |
[null] |
- PRIMARY KEY, btree (member_id)
- FOREIGN KEY (project_id) REFERENCES research_projects(project_id)
- FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
- FOREIGN KEY (student_id) REFERENCES students(student_id)
grade_events - éléments d'évaluation individuels par inscription (~120 000 lignes).
- event_ididentifiant unique de l'enregistrement (PK, BIGINT)
- enrollment_ididentifiant de l'inscription (FK)
- item_namenom de l'élément évalué, ex. 'Assignment 1', 'Midterm Exam'
- item_typetype d'élément : assignment, quiz, midterm, final, project, participation ou lab (ENUM)
- scorenote obtenue (DECIMAL)
- max_scorenote maximale possible, défaut 100,00 (DECIMAL)
- weightfraction de la note finale, ex. 0,1500 pour 15% (DECIMAL)
- graded_atdate et heure d'enregistrement de la note (DATETIME)
- grader_idmembre du corps enseignant ayant noté l'élément (FK, nullable)
- feedbacktexte de retour de l'évaluateur (TEXT, nullable)
| event_id |
enrollment_id |
item_name |
item_type |
score |
max_score |
weight |
graded_at |
grader_id |
feedback |
| 1 |
1 |
Midterm Exam |
midterm |
87.00 |
100.00 |
0.3000 |
2024-10-18 14:22:00 |
1 |
Bonne analyse, revoir la section 3. |
- PRIMARY KEY, btree (event_id)
- FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id)
- FOREIGN KEY (grader_id) REFERENCES faculty(faculty_id)
audit_log - historique des modifications généré par déclencheurs (~60 000 lignes).
- log_ididentifiant unique de l'enregistrement (PK, BIGINT)
- table_namenom de la table modifiée
- record_idclé primaire de l'enregistrement modifié (BIGINT)
- actiontype de modification : INSERT, UPDATE ou DELETE (ENUM)
- changed_atdate et heure de la modification (TIMESTAMP)
- changed_byutilisateur de la base de données ou contexte applicatif (nullable)
- old_valuesvaleurs précédentes des colonnes au format JSON (null pour INSERT)
- new_valuesnouvelles valeurs des colonnes au format JSON (null pour DELETE)
| log_id |
table_name |
record_id |
action |
changed_at |
changed_by |
old_values |
new_values |
| 1 |
enrollments |
1 |
UPDATE |
2024-12-21 09:05:33 |
app_user |
{"status":"enrolled","final_score":null} |
{"status":"completed","final_score":93.50} |
- PRIMARY KEY, btree (log_id)
Vues
v_student_gpa - moyenne pondérée par étudiant et par semestre.
- student_ididentifiant de l'étudiant
- student_numbernuméro d'étudiant unique
- first_nameprénom de l'étudiant
- last_namenom de famille de l'étudiant
- semester_ididentifiant du semestre
- semester_namenom du semestre
- semester_gpamoyenne pondérée du semestre
- credits_earnedcrédits obtenus au cours du semestre
| student_id |
student_number |
first_name |
last_name |
semester_id |
semester_name |
semester_gpa |
credits_earned |
| 1 |
S000123 |
James |
Miller |
1 |
Fall 2024 |
3.72 |
15 |
v_section_roster - étudiants inscrits avec coordonnées par groupe de cours.
- section_ididentifiant du groupe
- course_codecode du cours
- course_titleintitulé du cours
- semester_namenom du semestre
- student_ididentifiant de l'étudiant
- student_numbernuméro d'étudiant unique
- first_nameprénom de l'étudiant
- last_namenom de famille de l'étudiant
- emailadresse e-mail de l'étudiant
- statusstatut d'inscription
| section_id |
course_code |
course_title |
semester_name |
student_id |
student_number |
first_name |
last_name |
email |
status |
| 1 |
CS301 |
Database Systems |
Fall 2024 |
1 |
S000123 |
James |
Miller |
j.miller@student.edu |
enrolled |
v_course_pass_rate - taux de réussite/échec historique et score moyen par cours.
- course_ididentifiant du cours
- codecode du cours
- titleintitulé du cours
- semester_ididentifiant du semestre
- semester_namenom du semestre
- total_enrollednombre total d'étudiants inscrits
- passednombre d'étudiants ayant réussi
- pass_ratetaux de réussite en pourcentage
- avg_scorescore final moyen
| course_id |
code |
title |
semester_id |
semester_name |
total_enrolled |
passed |
pass_rate |
avg_score |
| 1 |
CS301 |
Database Systems |
1 |
Fall 2024 |
28 |
25 |
89.29 |
81.40 |
v_faculty_workload - groupes enseignés et taux de remplissage par enseignant et par semestre.
- faculty_ididentifiant du membre du corps enseignant
- first_nameprénom du membre du corps enseignant
- last_namenom de famille du membre du corps enseignant
- semester_ididentifiant du semestre
- semester_namenom du semestre
- sections_taughtnombre de groupes enseignés
- total_capacitycapacité totale en places sur tous les groupes
- total_enrollednombre total d'étudiants inscrits
- fill_ratetaux de remplissage en pourcentage
| faculty_id |
first_name |
last_name |
semester_id |
semester_name |
sections_taught |
total_capacity |
total_enrolled |
fill_rate |
| 1 |
Alice |
Carter |
1 |
Fall 2024 |
3 |
90 |
82 |
91.11 |
v_top_scholars - étudiants classés par montant total de bourses reçues.
- rank_positionclassement par montant total de bourses
- student_ididentifiant de l'étudiant
- student_numbernuméro d'étudiant unique
- first_nameprénom de l'étudiant
- last_namenom de famille de l'étudiant
- total_scholarshipsnombre de bourses attribuées
- total_amountmontant total de amount_awarded sur toutes les bourses
| rank_position |
student_id |
student_number |
first_name |
last_name |
total_scholarships |
total_amount |
| 1 |
1 |
S000123 |
James |
Miller |
2 |
8500.00 |
v_publication_stats - nombre d'articles et citations par département et par année.
- department_ididentifiant du département
- department_namenom du département
- pub_yearannée de publication
- paper_countnombre d'articles publiés
- total_citationsnombre total de citations sur tous les articles
| department_id |
department_name |
pub_year |
paper_count |
total_citations |
| 3 |
Computer Science |
2024 |
12 |
87 |
v_prerequisite_tree - prérequis directs pour chaque cours.
- course_ididentifiant du cours
- course_codecode du cours
- course_titleintitulé du cours
- prerequisite_ididentifiant du cours prérequis
- prerequisite_codecode du cours prérequis
- prerequisite_titleintitulé du cours prérequis
- is_mandatoryindique si le prérequis est obligatoire ou recommandé
| course_id |
course_code |
course_title |
prerequisite_id |
prerequisite_code |
prerequisite_title |
is_mandatory |
| 5 |
CS401 |
Advanced Database Systems |
1 |
CS301 |
Database Systems |
1 |