Modelling Data in an Information System

# Modelling Data in an Information System ## Objectifs et prérequis **Objectifs** : À la fin de ce chapitre, l'élève sera capable de : - Expliquer les concepts fondamentaux du modélisation des données dans un système d'information. - Décrire les composants du modèle Entité-Relation (ER) et ses systèmes de représentation. - Concevoir un diagramme ER à partir d'un cahier des charges et appliquer les règles de normalisation. - Convertir un modèle ER en modèle relationnel et l'implémenter dans un SGBDR. - Utiliser un SGBDR pour effectuer des opérations de tri, de filtrage et de requêtes complexes. **Prérequis** : Connaissances de base en informatique (matériel, logiciels, systèmes d'exploitation), notions d'algorithmique et de bases de données simples (tables, enregistrements, champs), familiarité avec l'anglais technique. --- ## Introduction Le modélisation des données constitue une étape cruciale dans la conception de tout système d'information. Elle permet de représenter de manière abstraite mais structurée les données pertinentes pour une organisation, indépendamment de leur implémentation technique. Dans le contexte camerounais et africain, où de nombreuses institutions (banques, hôpitaux, écoles, administrations) modernisent leurs systèmes, une bonne modélisation évite les redondances, assure l'intégrité des données et facilite la prise de décision. Ce chapitre suit une progression logique : de la théorie des concepts de modélisation à la mise en pratique avec un SGBDR, en passant par la conception ER et la normalisation. --- ## I. Data Modelling Concepts ### Contexte La modélisation des données vise à créer une représentation conceptuelle des données nécessaires au fonctionnement d'un système d'information, sans se soucier initialement de la manière dont elles seront stockées ou traitées. Elle se situe au niveau conceptuel de l'architecture à trois niveaux (conceptuel, logique, physique). ### Définitions clés > **Data modelling** : Processus de création d'un modèle de données qui définit les types de données, leurs propriétés, leurs relations et les contraintes qui les régissent dans un système d'information. > **Entity** : Objet du monde réel distinguable et pertinent pour le système (ex. : étudiant, cours, patient). > **Attribute** : Propriété ou caractéristique décrivant une entité (ex. : nom, âge, numéro de matricule). > **Relationship** : Association entre deux ou plusieurs entités (ex. : un étudiant s'inscrit à un cours). > **Cardinalité** : Nombre d'instances d'une entité pouvant être associées à une instance d'une autre entité via une relation (ex. : un étudiant peut s'inscrire à plusieurs cours, un cours peut avoir plusieurs étudiants). > **Participation** : Indique si une entité doit obligatoirement participer à une relation (totale) ou peut ne pas y participer (partielle). ### Faits et concepts importants - La modélisation conceptuelle est indépendante du SGBDR choisi (Oracle, MySQL, PostgreSQL, etc.). - Elle facilite la communication entre analystes, concepteurs et utilisateurs métier. - Les erreurs à ce stade sont coûteuses à corriger plus tard dans le cycle de développement. - Au Cameroun, des projets comme le système d'information sanitaire (DHIS2 utilisé dans certaines régions) ou les systèmes de gestion scolaire s'appuient sur une bonne modélisation initiale. ### Vocabulaire à retenir Data modelling, Entity, Attribute, Relationship, Cardinality, Participation, Conceptual schema, Independence. ### Exemple concret (camerounais) Dans un système de gestion des bourses d'État au Cameroun : - Entités : Étudiant, Institution, Programme d'études, Bourse. - Attributs de Étudiant : Numéro de matricule (clé primaire), Nom, Prénom, Date de naissance, Sexe, Région d'origine. - Relation : Un étudiant bénéficie d'une bourse (1:1 ou 1:0,1 selon les cas). - Contrainte : Un étudiant ne peut bénéficier que d'une seule bourse d'État à la fois. ### Sujet type examen avec plan **Sujet** : Expliquez les concepts de base de la modélisation des données en distinguant clairement les notions d'entité, d'attribut et de relation. Illustrer votre réponse avec un exemple tiré du contexte éducatif camerounais. **Plan** : 1. Définition et objectif de la modélisation des données. 2. Explication détaillée des trois concepts fondamentaux : entité, attribut, relation (avec définitions précises). 3. Explication de la cardinalité et de la participation. 4. Exemple concret : système de gestion des inscriptions universitaires à l'Université de Yaoundé I. 5. Importance de la modélisation conceptuelle pour la qualité du système d'information. --- ## II. Entity Relationship Model Concepts and representation systems ### Contexte Le modèle Entité-Relation (ER), introduit par Peter Chen en 1976, est un outil graphique standard pour représenter les données au niveau conceptuel. Il utilise des symboles spécifiques pour représenter les entités, les attributs et les relations. ### Définitions clés > **Modèle ER** : Modèle conceptuel de données qui représente les données sous forme d'entités, d'attributs et de relations, en utilisant un diagramme graphique normalisé. > **Entité forte** : Entité qui possède une clé primaire propre et dont l'existence ne dépend pas d'une autre entité. > **Entité faible** : Entité qui n'a pas de clé primaire propre et dont l'identité dépend d'une autre entité (entité propriétaire). > **Attribut clé** : Attribut ou ensemble d'attributs qui identifie de manière unique une instance d'entité. > **Attribut dérivé** : Attribut dont la valeur peut être calculée à partir d'autres attributs (ex. : âge à partir de la date de naissance). > **Relation binaire** : Relation entre deux entités (la plus courante). > **Relation n-aire** : Relation entre plus de deux entités (moins fréquente, souvent évitée par décomposition). ### Systèmes de représentation Deux notations principales sont utilisées : - **Notation de Chen** : Losanges pour les relations, ovales pour les attributs, rectangles pour les entités. Les cardinalités sont indiquées sur les losanges. - **Notation de Crow's Foot** (pieds de corbeau) : Rectangles pour les entités, lignes pour les relations, pattes de différentes formes aux extrémités pour indiquer la cardinalité (un trait = un seul, trois pattes = plusieurs, cercle + trait = zéro ou un, cercle + trois pattes = zéro, un ou plusieurs). ### Faits et dates importants - Le modèle ER a été introduit par **Peter Chen** en 1976 dans son article *"The Entity-Relationship Model: Toward a Unified View of Data"*. - La notation Crow's Foot s'est popularisée avec les outils CASE dans les années 1980-1990 et est largement utilisée aujourd'hui (ex. : dans MySQL Workbench, Lucidchart). - Aucune date ou nom spécifique lié au Cameroun n'est associé à l'invention de ces modèles, mais leur application est répandue dans les projets informatiques nationaux. ### Vocabulaire à retenir Modèle ER, Peter Chen, Notation de Chen, Notation Crow's Foot, Entité forte, Entité faible, Attribut clé, Attribut dérivé, Relation binaire, Cardinalité, Participation. ### Exemple concret (africain) Dans un système de gestion des dossiers patients dans un hôpital sénégalais (inspiré de DHIS2) : - Entité forte : Patient (clé primaire : numéro d'identification national). - Entité faible : Rendez-vous (dépend du Patient ; clé partielle : date et heure ; clé complète : numéro Patient + date/heure). - Attributs du Patient : Nom, Prénom, Date de naissance, Sexe, Téléphone. - Attribut dérivé : Âge (calculé à partir de la date de naissance). - Relation : Un patient peut avoir zéro, un ou plusieurs rendez-vous (participation partielle du Patient, totale du Rendez-vous). ### Tableau comparatif : Notation de Chen vs Crow's Foot | Élément | Notation de Chen | Notation Crow's Foot | |-----------------------|----------------------------------|-------------------------------------| | Entité | Rectangle | Rectangle | | Attribut | Ovale | Texte dans le rectangle | | Clé primaire | Souligné dans l'ovale | Souligné dans le texte | | Relation | Losange | Ligne entre entités | | Cardinalité | Texte sur le losange | Symboles aux extrémités de la ligne | | Participation totale | Double trait vers le losange | Trait plein | | Participation partielle| Simple trait vers le losange | Traitillé | ### Sujet type examen avec plan **Sujet** : Décrivez les deux principales notations utilisées pour représenter un modèle ER. Comparer leurs avantages et inconvénients, et expliquer pourquoi la notation Crow's Foot est souvent préférée dans les outils modernes de conception de bases de données. **Plan** : 1. Présentation du modèle ER et de son origine (Peter Chen, 1976). 2. Description détaillée de la notation de Chen (symboles, représentation des cardinalités). 3. Description détaillée de la notation Crow's Foot (symboles, représentation des cardinalités et de la participation). 4. Tableau comparatif des deux notations. 5. Arguments en faveur de la notation Crow's Foot : lisibilité, intégration avec les SGBDR, usage dans les outils professionnels (ex. : MySQL Workbench). 6. Conclusion sur le choix de la notation selon le contexte (académique vs professionnel). --- ## III. Designing an ER diagram and normalisation ### Contexte La conception d'un diagramme ER commence par l'analyse des besoins utilisateurs (cahier des charges) pour identifier les entités, leurs attributs et les relations. La normalisation est ensuite appliquée pour éliminer les redondances et assurer l'intégrité des données, même si elle appartient davantage au niveau logique, elle influence la conception conceptuelle. ### Définitions clés > **Normalisation** : Processus d'organisation des données dans une base de données pour réduire la redondance et améliorer l'intégrité des données en divisant les tables en formes normales successives. > **Forme normale première (1NF)** : Une table est en 1NF si tous ses attributs contiennent des valeurs atomiques (indivisibles) et qu'il n'y a pas de groupes répétitifs. > **Forme normale seconde (2NF)** : Une table est en 2NF si elle est en 1NF et que chaque attribut non clé dépend entièrement de la clé primaire (pas de dépendance partielle). > **Forme normale troisième (3NF)** : Une table est en 3NF si elle est en 2NF et qu'aucun attribut non clé ne dépend transitivement de la clé primaire. > **Dépendance fonctionnelle** : Relation où la valeur d'un attribut (ou ensemble d'attributs) détermine de manière unique la valeur d'un autre attribut. > **Clé primaire** : Attribut ou ensemble d'attributs qui identifie de manière unique chaque ligne d'une table. > **Clé étrangère** : Attribut qui fait référence à la clé primaire d'une autre table, établissant une relation. ### Étapes de conception d'un diagramme ER 1. Identifier les entités à partir des noms substantifs du cahier des charges. 2. Identifier les attributs de chaque entité (à partir des adjectifs et des détails). 3. Déterminer les clés primaires pour chaque entité. 4. Identifier les relations entre les entités (à partir des verbes). 5. Déterminer la cardinalité et la participation de chaque relation. 6. Identifier les attributs dérivés et les entités faibles éventuelles. 7. Vérifier la complétude et la cohérence avec le cahier des charges. 8. Appliquer les principes de normalisation pour valider la pertinence du modèle (éviter les attributs multivalués, les dépendances partielles/transitives). ### Faits et concepts importants - La normalisation a été formalisée par **Edgar F. Codd** dans les années 1970, notamment avec son article de 1970 *"A Relational Model of Data for Large Shared Data Banks"*. - Bien que la 3NF soit généralement suffisante pour la plupart des applications, des formes normales plus élevées (BCNF, 4NF) existent pour des cas spécifiques. - Une sur-normalisation peut rendre les requêtes complexes et coûteuses en performance. - Au Cameroun, dans les systèmes de gestion des impôts ou de la sécurité sociale, une mauvaise normalisation entraîne des doublons de contribuables ou des erreurs de calcul des prestations. ### Vocabulaire à retenir Normalisation, 1NF, 2NF, 3NF, Dépendance fonctionnelle, Clé primaire, Clé étrangère, Atomique, Dépendance partielle, Dépendance transitive, Entité faible. ### Exemple concret (camerounais) Cahier des charges : Système de gestion des notes dans un lycée bilingue. - Entités identifiées : Étudiant, Matière, Enseignant, Évaluation. - Attributs : - Étudiant : Numéro de matricule (PK), Nom, Prénom, Sexe, Date de naissance, Classe. - Matière : Code matière (PK), Libellé, Coefficient. - Enseignant : Matricule enseignant (PK), Nom, Prénom, Spécialité. - Évaluation : Date, Type (DS, Interro), Coefficient, Note. - Relations : - Un étudiant passe plusieurs évaluations → relation Étudiant-Évaluation (1:N). - Une évaluation concerne une matière → relation Évaluation-Matière (N:1). - Une évaluation est donnée par un enseignant → relation Évaluation-Enseignant (N:1). - Vérification 1NF : Tous les attributs sont atomiques (ex. : pas de liste de notes dans un seul champ). - Vérification 2NF : Dans la table Évaluation, si la clé primaire était (Numéro Étudiant, Code Matière, Date), alors les attributs comme Type et Coefficient dépendent-ils entièrement de la clé ? Oui, si chaque évaluation est unique par étudiant, matière et date. Sinon, décomposition nécessaire. - Application 3NF : S'assurer qu'aucun attribut non clé (ex. : Libellé de la matière) ne dépend d'un autre attribut non clé (ex. : si on avait mis le libellé dans la table Évaluation, cela créerait une dépendance transitive via le code matière). ### Tableau : Vérification des formes normales sur un exemple | Table initiale (Évaluation) | Problème | Solution (décomposition) | |-----------------------------|------------------------------|----------------------------------------| | Numéro Étudiant, Code Matière, Date, Type, Note, Coefficient, Libellé Matière | Libellé Matière dépend de Code Matière seulement (dépendance partielle si clé composée) | Séparer Matière (Code, Libellé, Coefficient) et garder Évaluation (Numéro Étudiant, Code Matière, Date, Type, Note) | | Même table après décomposition | Note dépend de (Numéro Étudiant, Code Matière, Date) → OK en 2NF | Vérifier 3NF : aucun attribut non clé ne dépend d'un autre non clé → OK si Libellé Matière est dans sa propre table | ### Sujet type examen avec plan **Sujet** : Partant d'un cahier des charges pour un système de gestion des vaccinations dans un centre de santé camerounais, concevez un diagramme ER et expliquez comment vous appliquez les règles de normalisation (1NF, 2NF, 3NF) pour valider votre modèle. **Plan** : 1. Analyse du cahier des charges : identification des entités (Patient, Vaccin, Centre de santé, Dose de vaccination, Agent de santé). 2. Liste des attributs pour chaque entité et proposition des clés primaires. 3. Identification des relations et détermination de la cardinalité/participation (ex. : un patient reçoit plusieurs doses ; une dose est administrée par un agent). 4. Dessin du diagramme ER (description textuelle : entités sous forme de rectangles, relations sous forme de losanges ou lignes, cardinalités indiquées). 5. Vérification de la 1NF : tous les attributs sont atomiques (pas de listes ou de groupes répétitifs). 6. Vérification de la 2NF : suppression des dépendances partielles (ex. : si la clé d'une table est composée, s'assurer que les attributs non clés dépendent de toute la clé). 7. Vérification de la 3NF : suppression des dépendances transitives (ex. : pas de attribut comme "Nom du vaccin" dans la table Dose si on a déjà le code vaccin qui renvoie à une table Vaccin). 8. Conclusion : le modèle obtenu est en 3NF et adapté à l'implémentation relationnelle. --- ## IV. Normalisation and converting ER model to relational model ### Contexte Après la validation conceptuelle via le diagramme ER et la normalisation, l'étape suivante consiste à transformer le modèle ER en un modèle relationnel, qui sera directement implémentable dans un SGBDR. Cette transformation suit des règles précises pour préserver la sémantique du modèle conceptuel. ### Définitions clés > **Modèle relationnel** : Modèle de données basé sur la théorie des ensembles, où les données sont organisées en tables (relations) composés de lignes (tuples) et de colonnes (attributs). > **Transformation ER vers relationnel** : Ensemble de règles permettant de convertir un diagramme ER en un ensemble de tables relationnelles, en préservant les entités, les attributs, les relations et les contraintes. > **Table** : Structure contenant des enregistrements (lignes) partageant les mêmes attributs (colonnes). > **Tuple** : Ligne d'une table représentant une instance spécifique de données. > **Attribut** : Colonne d'une table représentant une propriété des données. > **Contrainte d'intégrité** : Règle qui garantit la validité et la cohérence des données (ex. : clé primaire, clé étrangère, NOT NULL, UNIQUE). ### Règles de transformation ER → Relationnel 1. **Entité forte** → Une table. Les attributs de l'entité deviennent des colonnes. La clé primaire de l'entité devient la clé primaire de la table. 2. **Entité faible** → Une table. La table inclut les attributs propres à l'entité faible + la clé primaire de l'entité propriétaire (comme clé étrangère faisant partie de la clé primaire composée). 3. **Attribut multivalué** → Une table séparée liée à l'entité par une clé étrangère. 4. **Relation binaire 1:1** → Peut être fusionnée dans une des tables (en ajoutant la clé de l'autre comme FK) ou gardée séparée avec une FK dans les deux sens (moins courant). 5. **Relation binaire 1:N** → La clé de l'entité du côté "1" devient une clé étrangère dans la table du côté "N". 6. **Relation binaire M:N** → Une nouvelle table est créée, contenant les clés primaires des deux entités comme clés étrangères (forming a composite primary key), plus éventuellement des attributs propres à la relation. 7. **Attribut dérivé** → Généralement non stocké ; calculé à la volée lors des requêtes (pas de colonne dédiée dans la table). ### Faits et dates importants - Le modèle relationnel a été introduit par **Edgar F. Codd** en 1970. - Les règles de transformation ER vers relationnel sont le résultat de travaux ultérieurs visant à rendre le modèle ER compatible avec l'implémentation relationnelle. - Aucune date ou nom spécifique lié au Cameroun n'est associé à ces théoriciens, mais leurs travaux sous-tendent tous les SGBDR utilisés dans le pays (MySQL, PostgreSQL, SQL Server, Oracle). ### Vocabulaire à retenir Modèle relationnel, Transformation ER-relationnel, Table, Tuple, Attribut, Clé primaire, Clé étrangère, Contrainte d'intégrité, Entité forte, Entité faible, Relation 1:N, Relation M:N, Table de jonction. ### Exemple concret (africain) Modèle ER pour un système de gestion des pêches artisanales au Ghana : - Entités : Pêcheur (forte), Bateau (faible, dépend du pêcheur), Espèce de poisson, Capture. - Relations : - Un pêcheur possède zéro, un ou plusieurs bateaux (1:N). - Une capture concerne un pêcheur et une espèce de poisson (M:N entre Pêcheur et Espèce, avec des attributs : date, poids, lieu). - Transformation : - Table Pêcheur : id_pêcheur (PK), nom, région, expérience. - Table Bateau : id_bateau (PK), nom, type, id_pêcheur (FK vers Pêcheur). - Table Espèce : id_espèce (PK), nom_commun, nom_scientifique. - Table Capture : id_pêcheur (FK), id_espèce (FK), date_capture, poids_kg, lieu → PK composé (id_pêcheur, id_espèce, date_capture). - Aucun attribut dérivé stocké (ex. : valeur marchande calculée = poids * prix_unitaire). ### Tableau : Règles de transformation ER → Relationnel | Élément ER | Transformation en modèle relationnel | Exemple | |-------------------------|-------------------------------------------------------------------|------------------------------------------| | Entité forte | Table avec ses attributs ; PK = PK de l'entité | Étudiant → Table Étudiant (matricule PK) | | Entité faible | Table avec ses attributs + PK de l'entité propriétaire ; PK composée | Bateau → Table Bateau (id_bateau, id_pêcheur PK) | | Attribut multivalué | Table séparée liée par FK | Téléphone étudiant → Table Téléphone (matricule ET, numéro) | | Relation 1:N | FK du côté "1" placée dans la table du côté "N" | Enseignant → Cours : mat_enseignant FK dans Cours | | Relation M:N | Nouvelle table avec FK des deux entités ; PK composée | Étudiant ↔ Cours : table Inscription (mat_et, code_cours PK) | | Attribut dérivé | Non stocké ; calculé en requête | Âge → calculé via DATE_DIFF(NOW(), date_naissance) | ### Sujet type examen avec plan **Sujet** : Expliquez les règles permettant de transformer un modèle ER en un modèle relationnel. Illustrer chaque règle avec un exemple tiré d'un système de gestion de bibliothèque scolaire au Cameroun. **Plan** : 1. Rappel de l'objectif de la transformation : passer du conceptuel au logique pour l'implémentation dans un SGBDR. 2. Règle 1 : Transformation des entités fortes en tables (ex. : Livre → Table Livre avec ISBN PK). 3. Règle 2 : Transformation des entités faibles (ex. : Exemplaire, dépend du Livre → Table Exemplaire avec numéro_exemplaire et ISBN FK, PK composée). 4. Règle 3 : Gestion des attributs multivalués (ex. : Auteur d'un livre → Table Auteur_Livre avec ISBN FK et nom_auteur). 5. Règle 4 : Transformation des relations 1:N (ex. : Un éditeur publie plusieurs livres → FK éditeur_id dans la table Livre). 6. Règle 5 : Transformation des relations M:N (ex. : Un livre peut avoir plusieurs sujets, un sujet concerne plusieurs livres → table de jonction Livre_Sujet avec ISBN et code_sujet FK, PK composée). 7. Règle 6 : Traitement des attributs dérivés (ex. : âge du livre = année courante - année de publication → non stocké). 8. Conclusion sur l'importance de respecter ces règles pour garantir l'intégrité et l'absence de redondance. --- ## V. Implementing a relational model ### Contexte L'implémentation du modèle relationnel consiste à créer physiquement les tables, les colonnes, les clés et les contraintes dans un SGBDR choisi. Cette étape passe du niveau logique au niveau physique et nécessite l'utilisation du langage de définition de données (DDL) du SQL. ### Définitions clés > **Data Definition Language (DDL)** : Sous-ensemble du SQL utilisé pour définir et modifier la structure des objets de la base de données (tables, index, contraintes). > **CREATE TABLE** : Instruction SQL permettant de créer une nouvelle table en spécifiant son nom, ses colonnes, leurs types de données et leurs contraintes. > **Contrainte de clé primaire (PRIMARY KEY)** : Garantit l'unicité et la non-nullité d'une colonne ou d'un ensemble de colonnes. > **Contrainte de clé étrangère (FOREIGN KEY)** : Garantit la référentialité en liant une colonne à la clé primaire d'une autre table. > **Contrainte NOT NULL** : Empêche une colonne d'accepter des valeurs nulles. > **Contrainte UNIQUE** : Garantit que toutes les valeurs d'une colonne sont différentes. > **Type de données** : Spécifie le genre de valeurs qu'une colonne peut contenir (ex. : INT, VARCHAR(50), DATE, DECIMAL(10,2)). > **SGBDR** : Système de Gestion de Base de Données Relationnelle (ex. : MySQL, PostgreSQL, Microsoft SQL Server, Oracle). ### Étapes d'implémentation 1. Choix du SGBDR (en fonction des besoins, du coût, de l'expertise disponible). 2. Écriture du script DDL : création de la base de données (`CREATE DATABASE`), puis des tables (`CREATE TABLE`). 3. Définition précise de chaque colonne : nom, type de données, contraintes (NOT NULL, UNIQUE, DEFAULT). 4. Déclaration des clés primaires (`PRIMARY KEY`). 5. Déclaration des clés étrangères (`FOREIGN KEY` REFERENCES autre_table(colonne)). 6. Ajout éventuel de contraintes supplémentaires (CHECK pour des plages de valeurs valides). 7. Exécution du script dans le SGBDR. 8. Vérification de la création correcte (via `DESCRIBE table` ou `SHOW COLUMNS`). ### Faits et dates importants - Le langage SQL a été standardisé par l'ANSI en 1986 (SQL-86), puis révisé plusieurs fois (SQL-92, SQL:1999, SQL:2003, etc.). - MySQL, l'un des SGBDR les plus utilisés dans l'éducation et les PME en Afrique, a été initialement développé par une entreprise suédoise en 1995. - Aucune date ou nom spécifique lié au Cameroun n'est associé à l'invention du SQL ou des SGBDR majeurs, mais leur adoption est généralisée dans les projets éducatifs et gouvernementaux (ex. : utilisation de MySQL dans les systèmes d'information scolaire pilotes). ### Vocabulaire à retenir DDL, CREATE TABLE, PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, Type de données (INT, VARCHAR, DATE, DECIMAL), SGBDR, Script d'implémentation. ### Exemple concret (camerounais) Implémentation d'un système de gestion des frais de scolarité dans une école privée à Douala : ```sql CREATE DATABASE SchoolManagement; USE SchoolManagement; CREATE TABLE Student ( student_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, date_of_birth DATE NOT NULL, gender ENUM('M', 'F') NOT NULL, phone_number VARCHAR(15) UNIQUE ); CREATE TABLE Class ( class_id INT PRIMARY KEY, class_name VARCHAR(20) NOT NULL UNIQUE, level VARCHAR(10) NOT NULL ); CREATE TABLE Enrollment ( student_id INT, class_id INT, enrollment_date DATE NOT NULL, PRIMARY KEY (student_id, class_id), FOREIGN KEY (student_id) REFERENCES Student(student_id), FOREIGN KEY (class_id) REFERENCES Class(class_id) ); CREATE TABLE Fee ( fee_id INT PRIMARY KEY, student_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0), due_date DATE NOT NULL, paid BOOLEAN DEFAULT FALSE, FOREIGN KEY (student_id) REFERENCES Student(student_id) ); ``` - Remarques : - `ENUM('M', 'F')` pour restreindre le genre. - `UNIQUE` sur `phone_number` pour éviter les doublons. - `CHECK (amount >= 0)` pour empêcher les montants négatifs. - `DEFAULT FALSE` sur `paid` pour supposer un impayé par défaut. - Clé primaire composée dans `Enrollment` car un étudiant ne peut s'inscrire qu'une seule fois à une même classe. ### Sujet type examen avec plan **Sujet** : Rédigez le script SQL DDL permettant d'implémenter le modèle relationnel correspondant à un système de gestion des prêts de livres dans une bibliothèque scolaire camerounaise, basé sur le modèle ER suivant : Entités Livre (ISBN, titre, auteur, année_publication), Adhérent (numéro_adhérent, nom, prénom, classe), Pret (date_pret, date_retour_prevue, date_retour_effet). Les relations sont : un adhérent peut avoir plusieurs prêts (1:N), un livre peut être prêté plusieurs fois mais seulement un exemplaire à la fois (supposer un exemplaire par titre pour simplifier → relation 1:N Livre-Pret). **Plan** : 1. Création de la base de données (`CREATE DATABASE Library;`). 2. Utilisation de la base de données (`USE Library;`). 3. Création de la table Livre avec colonnes : ISBN (PK, VARCHAR(13)), titre (VARCHAR(100) NOT NULL), auteur (VARCHAR(100)), année_publication (YEAR). 4. Création de la table Adhérent avec colonnes : numéro_adhérent (PK, INT), nom (VARCHAR(50) NOT NULL), prénom (VARCHAR(50) NOT NULL), classe (VARCHAR(10)). 5. Création de la table Pret avec colonnes : numéro_adhérent (FK), ISBN (FK), date_pret (DATE NOT NULL), date_retour_prevue (DATE NOT NULL), date_retour_effet (DATE NULL). 6. Déclaration de la clé primaire composée dans Pret (PRIMARY KEY (numero_adhérent, ISBN, date_pret)) pour permettre plusieurs prêts du même livre à différents moments. 7. Ajout des clés étrangères : FOREIGN KEY (numero_adhérent) REFERENCES Adhérent(numéro_adhérent), FOREIGN KEY (ISBN) REFERENCES Livre(ISBN). 8. Ajout d'une contrainte CHECK pour s'assurer que date_retour_prevue >= date_pret (optionnel mais pertinent). 9. Vérification finale : toutes les contraintes d'intégrité sont en place, aucune redondance évidente. ---