Fiche de révision : Modélisation et Transformation des Bases

📋 Plan du Cours

1. Modèle entité-association
2. Transformation relationnelle
3. Requêtes DML
4. Requêtes DDL
5. Analyse des tables
6. Contraintes d'intégrité
7. Requêtes SQL avancées
8. Schéma relationnel

📖 1. Modèle entité-association

🔑 Notions clés & Définitions

• Entité : Représentation d’un objet ou d’un concept du monde réel pouvant être identifié de façon unique dans le modèle. Elle est caractérisée par un ensemble d’attributs.
• Attribut : Propriété ou caractéristique d’une entité ou d’une relation. Il peut être simple, composé, ou dérivé.
• Relation : Association entre deux ou plusieurs entités, exprimant une dépendance ou une connexion. Elle est définie par ses cardinalités et sa participation.
• Cardinalité : Indication du nombre minimum et maximum d’occurrences d’une entité pouvant être associées à une occurrence d’une autre entité via une relation (ex : 1,1 ou 0,n).
• Participation : Niveau d’implication d’une entité dans une relation, pouvant être totale (toutes les occurrences doivent participer) ou partielle (certaines occurrences peuvent ne pas participer).
• Modifications du modèle : Ajustements ou contraintes appliquées au modèle entité-association, comme la spécification de cardinalités ou la modification des relations pour refléter des règles métier (ex : 1,1 entre chargement et préparer).

📝 Points essentiels

• Le modèle entité-association permet de représenter graphiquement les entités, leurs attributs, et leurs relations, facilitant la conception conceptuelle d’une base de données.
• La représentation graphique inclut généralement des rectangles pour les entités, des losanges pour les relations, et des lignes pour relier entités et relations, avec des annotations pour les cardinalités et la participation.
• La relation entre deux entités peut être de plusieurs types selon les cardinalités (ex : 1,n ou 0,1). La participation peut être totale ou partielle, influençant la contrainte d’intégrité du modèle.
• La modification du modèle, comme la mise en place d’une relation 1,1 entre chargement et préparer, impacte la structure et la gestion des données dans la base.
• L’analyse des contraintes fonctionnelles dans le modèle entité-association permet d’assurer la cohérence et la validité des données, notamment en vérifiant que les relations respectent les règles métier.

💡 À retenir

Le modèle entité-association est un outil graphique et conceptuel essentiel pour modéliser les données en identifiant clairement les entités, leurs attributs, et leurs relations, tout en précisant les contraintes de cardinalité et de participation.

📖 2. Transformation relationnelle

🔑 Notions clés & Définitions

Conversion d’entités en tables relationnelles : processus consistant à transformer chaque entité du modèle entité-association en une table dans le modèle relationnel, en attribuant une clé primaire unique à chaque table (voir aussi "nomination des tables et attributs").

Clé primaire : attribut ou ensemble d’attributs permettant d’identifier de façon unique chaque enregistrement dans une table. Elle doit être unique et non nulle (voir aussi "définition des clés primaires").

Clé étrangère : attribut ou ensemble d’attributs dans une table qui référence la clé primaire d’une autre table, assurant la cohérence référentielle (voir aussi "définition des clés étrangères").

Création des tables relationnelles à partir du modèle entité-association : étape où chaque entité devient une table, avec ses attributs, et où les relations sont traduites en clés étrangères ou tables associatives, notamment pour les relations n-n (voir aussi "gestion des relations n-n via tables associatives").

Nomination des tables et attributs dans le modèle relationnel : attribution de noms explicites et cohérents aux tables et attributs, en respectant la sémantique du modèle entité-association, pour faciliter la compréhension et la maintenance (voir aussi "nomination des tables et attributs").

Gestion des relations n-n via tables associatives : pour modéliser une relation n-n, création d’une table associative contenant les clés primaires des deux entités concernées comme clés étrangères, permettant de représenter la relation de façon normalisée (voir aussi "création des tables relationnelles à partir du modèle entité-association").

📝 Points essentiels

• La conversion d’entités en tables relationnelles repose sur la création d’une table par entité, avec une clé primaire unique (voir PERROUX (date) : l'augmentation pendant une ou plusieurs périodes d'un indicateur de dimension).
• La définition précise des clés primaires et clés étrangères est essentielle pour assurer l’intégrité référentielle et la cohérence des données (voir PERROUX (date)).
• Lors de la création des tables, chaque attribut doit être nommé de façon claire, et la correspondance avec le modèle entité-association doit être respectée pour garantir la traçabilité (voir PERROUX (date)).
• La gestion des relations n-n nécessite la création de tables associatives, contenant des clés étrangères, pour éviter la redondance et respecter la normalisation (voir PERROUX (date)).
• La transformation doit respecter la logique du modèle relationnel, en respectant la nomination, la clé primaire, et la gestion des relations via clés étrangères ou tables associatives.

💡 À retenir

La transformation relationnelle consiste à convertir chaque entité en table avec une clé primaire, à définir les clés étrangères pour représenter les relations, notamment n-n via des tables associatives, tout en nommant clairement les tables et attributs pour assurer la cohérence et la normalisation.

📖 3. Requêtes DML

🔑 Notions clés & Définitions

• Requête SELECT avec clause ORDER BY : Permet de récupérer des données triées selon un ou plusieurs attributs spécifiés, dans l’ordre croissant ou décroissant.
• Requête SELECT avec clause GROUP BY : Agrège les résultats en regroupant les lignes selon un ou plusieurs attributs, souvent utilisé avec des fonctions d’agrégation.
• Fonctions d’agrégation SQL (COUNT, SUM, MAX) : Fonctions qui opèrent sur un ensemble de valeurs pour produire un seul résultat. COUNT compte le nombre de lignes, SUM calcule la somme des valeurs numériques, MAX trouve la valeur maximale.
• Requêtes avec jointures internes (INNER JOIN) : Permettent de combiner des lignes de deux tables en fonction d’une condition d’égalité sur des clés, ne conservant que les correspondances.
• Requêtes de modification des données (UPDATE) : Modifient les valeurs existantes dans une ou plusieurs lignes d’une table selon une condition.

📝 Points essentiels

• La clause ORDER BY est essentielle pour trier les résultats d’une requête SELECT selon un ou plusieurs attributs, par exemple : ORDER BY Nom ASC, Date DESC.
• La clause GROUP BY est souvent associée aux fonctions d’agrégation pour résumer des données, par exemple : SELECT modele, COUNT(*) FROM TRAIN GROUP BY modele.
• Les fonctions d’agrégation telles que COUNT, SUM, MAX sont utilisées pour analyser ou résumer des données, comme calculer la capacité totale ou compter le nombre de trains par modèle.
• Les jointures internes (INNER JOIN) permettent de relier deux tables en ne conservant que les lignes où la condition de jointure est remplie, par exemple : SELECT ... FROM VOYAGE AS V INNER JOIN EFFECTUE AS E ON V.NUMVOYAGE=E.NUMVOYAGE.
• La requête UPDATE sert à modifier des valeurs existantes, par exemple pour augmenter le prix d’un voyage : UPDATE VOYAGE SET prix_place=prix_place+3 WHERE NUMVOYAGE=42.
• Les requêtes INSERT INTO permettent d’ajouter de nouvelles lignes dans une table, en précisant les valeurs pour chaque attribut, par exemple : INSERT INTO TRAIN(modele, capacité) VALUES('TER', 600).
• Les requêtes DELETE suppriment des lignes selon une condition, souvent en utilisant une sous-requête pour cibler précisément les enregistrements, comme supprimer un train spécifique.

💡 À retenir

Les requêtes DML permettent de manipuler, analyser et résumer efficacement les données dans une base relationnelle en utilisant des clauses de tri, de regroupement, des fonctions d’agrégation, des jointures, ainsi que des opérations de modification ou suppression conditionnelle.

📖 4. Requêtes DDL

🔑 Notions clés & Définitions

• CREATE TABLE : commande SQL permettant de créer une nouvelle table dans la base de données en spécifiant ses colonnes, leurs types et contraintes (source : ressources pédagogiques).
• Définition des types de données (INTEGER, VARCHAR, DATE, REAL) : spécification du type de chaque colonne lors de la création d'une table, pour définir la nature des données stockées (ex : INTEGER pour nombres entiers, VARCHAR pour chaînes de caractères, DATE pour dates, REAL pour nombres à virgule flottante).
• Clé primaire (PRIMARY KEY) : contrainte qui identifie de façon unique chaque enregistrement d'une table, assurant l'unicité et permettant d'établir des relations (source : ressources pédagogiques).
• Clé étrangère (FOREIGN KEY) : contrainte qui établit une relation entre une colonne d'une table et la clé primaire d'une autre, garantissant l'intégrité référentielle (source : ressources pédagogiques).
• Contraintes CHECK : contraintes appliquées lors de la définition d'une colonne pour limiter ses valeurs possibles, par exemple, pour restreindre les modèles autorisés dans une colonne (ex : CHECK(Modèle IN ('TER','RER','TGV'))).

📝 Points essentiels

• La commande CREATE TABLE doit inclure la définition précise des colonnes avec leur type de données, ainsi que les contraintes d'intégrité telles que PRIMARY KEY pour l'unicité et FOREIGN KEY pour les relations entre tables (ex : dans l'exemple du schéma, FOREIGN KEY sur NUMTRAIN dans VOYAGE).
• La déclaration des types de données doit respecter la nature des données : INTEGER pour les nombres entiers, VARCHAR pour les chaînes de caractères avec une limite de longueur, DATE pour les dates, et REAL pour les valeurs à virgule flottante (ex : Prix_place en REAL).
• Les contraintes CHECK permettent de restreindre les valeurs possibles d'une colonne, comme dans l'exemple où Modèle doit appartenir à un ensemble précis ('TER', 'RER', 'TGV').
• La syntaxe correcte de la commande DDL pour la création de schéma doit respecter l'ordre : déclaration de la table, colonnes avec types, contraintes de clé primaire et étrangère, et contraintes CHECK si nécessaire.

💡 À retenir

Les commandes DDL permettent de définir la structure des tables en précisant types, clés et contraintes, assurant ainsi la cohérence et l'intégrité du schéma de la base de données.

📖 5. Analyse des tables

🔑 Notions clés & Définitions

• Analyse des données contenues dans les tables : processus d'examen et d'interprétation des valeurs stockées dans une ou plusieurs tables pour en extraire des informations pertinentes, en tenant compte de leur organisation et de leur contenu (ex : tables T1 et T2).

• Utilisation des fonctions d’agrégation dans l’analyse : application de fonctions SQL telles que SUM, MIN, MAX, AVG pour calculer des valeurs globales ou résumées à partir des données d'une table ou d'un résultat de jointure, permettant d'obtenir des indicateurs synthétiques.

• Compréhension des jointures et de leur impact sur les résultats : maîtrise des opérations de jointure (ex : INNER JOIN, LEFT OUTER JOIN) pour combiner des tables en fonction de clés communes, influençant directement la nature et la quantité des résultats obtenus.

• Interprétation des résultats des requêtes SQL sur les tables : capacité à analyser et à donner du sens aux résultats issus des requêtes, en comprenant notamment la signification des agrégats, des valeurs distinctes, et des regroupements.

• Analyse des cardinalités et des valeurs distinctes dans les tables : étude du nombre d'occurrences (cardinalité) et de la diversité des valeurs (valeurs distinctes) dans un attribut ou un ensemble d'attributs, pour évaluer la multiplicité et la variété des données.

📝 Points essentiels

• La compréhension des jointures est cruciale pour analyser l’impact des relations entre tables, notamment pour déterminer si un résultat inclut toutes les correspondances ou uniquement celles communes (voir jointures internes et jointures externes).
• Les fonctions d’agrégation permettent de synthétiser des données en calculant des totaux, minima, maxima ou moyennes, facilitant l’analyse quantitative.
• La valeur de chaque requête dépend de la nature des jointures et des conditions appliquées, ce qui influence directement l’interprétation des résultats.
• L’analyse des cardinalités et des valeurs distinctes aide à comprendre la diversité et la multiplicité des données, essentielle pour optimiser les requêtes et assurer leur cohérence.
• La maîtrise de ces concepts permet d’interpréter efficacement les résultats pour répondre à des problématiques métier ou techniques.

💡 À retenir

L’analyse des tables repose sur l’étude des données, l’utilisation judicieuse des fonctions d’agrégation, la compréhension des jointures et leur impact, ainsi que l’évaluation des cardinalités et valeurs distinctes pour une interprétation précise des résultats SQL.

📖 6. Contraintes d'intégrité

🔑 Notions clés & Définitions

• Contraintes de clé primaire et unicité : La clé primaire identifie de manière unique chaque ligne d'une table. Elle doit être unique et non nulle, garantissant l'intégrité des identifiants (voir aussi "Contraintes de non-nullité"). Unicité assure qu'aucune valeur du champ ou de l'ensemble de champs ne se répète dans la table.
• Contraintes d’intégrité référentielle (FOREIGN KEY) : Garantissent la cohérence entre deux tables en imposant que la valeur d’un attribut dans une table corresponde à une valeur existante dans une autre table. Une FOREIGN KEY référence une clé primaire dans une autre table, assurant la cohérence des relations (voir aussi "Gestion des contraintes optionnelles").
• Contraintes de domaine (CHECK) : Limitent les valeurs possibles d’un attribut en imposant une condition ou une plage de valeurs autorisées. Par exemple, CHECK sur un attribut "Modèle" pour n’autoriser que 'TGV', 'RER', 'TER' (voir aussi "Contraintes d’intégrité de domaine").
• Contraintes de non-nullité (NOT NULL) : Assurent qu’un attribut ne peut pas contenir de valeur NULL, garantissant que l’information est toujours renseignée.
• Gestion des contraintes optionnelles : Certaines contraintes, comme Parrain dans la table VOYAGEUR, sont optionnelles, ce qui signifie que l’attribut peut être NULL, permettant une relation facultative (voir aussi "Contraintes d’intégrité référentielle").

📝 Points essentiels

• La clé primaire doit être unique et non nulle, ce qui garantit l’identification unique de chaque enregistrement dans une table. La contrainte UNIQUE peut également assurer l’unicité d’un ou plusieurs attributs sans en faire la clé primaire.
• La contraintes de clé étrangère (FOREIGN KEY) assure la cohérence entre les tables en empêchant la suppression ou la modification de valeurs référencées si elles sont utilisées dans d’autres tables, sauf si des actions de cascade sont définies.
• La contrainte CHECK permet de limiter les valeurs possibles d’un attribut, renforçant la cohérence des données. Par exemple, dans la création de la table TRAIN, le modèle doit appartenir à la liste {'TER', 'RER', 'TGV'}.
• La contraintes de non-nullité (NOT NULL) est essentielle pour garantir que des données critiques ne soient pas omises, notamment pour les attributs indispensables à l’intégrité du modèle.
• La gestion des contraintes optionnelles, comme le Parrain nullable dans VOYAGEUR, permet de modéliser des relations facultatives, évitant des contraintes trop strictes qui pourraient empêcher l’enregistrement de certaines données.

💡 À retenir

Les contraintes d’intégrité assurent la cohérence, la fiabilité et la validité des données dans une base, en imposant des règles sur les valeurs et les relations entre les tables.

📖 7. Requêtes SQL avancées

🔑 Notions clés & Définitions

• Requêtes avec sous-requêtes corrélées : Requêtes imbriquées où la sous-requête dépend de la requête extérieure, permettant d’effectuer des filtrages ou agrégations complexes en utilisant des valeurs de la requête principale.
  Source : "Requêtes imbriquées et agrégations complexes"

• Utilisation de HAVING pour filtrer les groupes : Clause permettant de filtrer les résultats après regroupement avec GROUP BY, en appliquant des conditions sur des fonctions d’agrégation.
  Source : "Requêtes avec jointures internes (INNER JOIN)" (impliquant aussi HAVING dans certains exemples)

• Requêtes avec jointures externes (LEFT OUTER JOIN) : Requêtes combinant deux tables en conservant tous les enregistrements de la table de gauche, même si aucune correspondance dans la table de droite, utile pour identifier les absences ou non-correspondances.
  Source : "Opérateurs de jointures obligatoires"

• Utilisation de fonctions temporelles (STRFTIME) : Fonctions permettant de manipuler et d’extraire des informations de dates/horaires dans les requêtes SQL, comme extraire l’année, le mois ou le jour d’une date.
  Source : "Utilisation de fonctions temporelles (STRFTIME) dans les requêtes"

📝 Points essentiels

• Les sous-requêtes corrélées sont essentielles pour réaliser des analyses complexes, notamment pour comparer chaque ligne à un sous-ensemble de données dépendant de cette ligne (exemple : trouver le voyage ayant le plus de voyages effectués par un voyageur).
• La clause HAVING est indispensable pour filtrer les résultats après un GROUP BY, par exemple pour ne conserver que les groupes ayant un certain nombre d’éléments ou une valeur agrégée spécifique.
• Les jointures externes, notamment LEFT OUTER JOIN, permettent d’identifier des éléments sans correspondance dans une autre table, ce qui est crucial pour détecter des absences ou des données manquantes.
• Les fonctions temporelles comme STRFTIME sont indispensables pour exploiter efficacement les données temporelles, notamment pour filtrer ou agréger par année, mois ou jour.
• Ces concepts permettent d’effectuer des requêtes avancées, souvent utilisées pour des analyses détaillées ou des rapports complexes.

💡 À retenir

Les requêtes SQL avancées combinent sous-requêtes corrélées, jointures externes, filtrages par HAVING et fonctions temporelles pour réaliser des analyses complexes et précises sur des bases de données relationnelles.

📖 8. Schéma relationnel

🔑 Notions clés & Définitions

Schéma relationnel : Représentation structurée d'une base de données sous forme de tables (ou relations), où chaque table est composée d'attributs (colonnes) et de clés (primaires et étrangères) permettant d'organiser et d'identifier les données de manière cohérente.

Clé primaire : Attribut ou ensemble d'attributs qui identifie de façon unique chaque ligne (enregistrement) d'une table. Elle est notée dans le schéma relationnel par une annotation spécifique (ex : soulignement ou en gras).

Clé étrangère : Attribut ou ensemble d'attributs dans une table qui référence la clé primaire d'une autre table, assurant la cohérence référentielle entre les tables. Elle est notée par une annotation indiquant sa relation avec une autre table.

Organisation des tables selon le modèle relationnel : Processus de structuration des données en tables, en respectant les contraintes d'intégrité (unicité, référentialité), pour assurer la cohérence et la facilité d'accès aux données.

Correspondance entre modèle entité-association et schéma relationnel : Transformation du modèle conceptuel (entités, relations, cardinalités) en tables relationnelles, où chaque entité devient une table et chaque relation une ou plusieurs tables, avec attribution de clés primaires et étrangères pour représenter les liens.

📝 Points essentiels

• Le schéma relationnel se construit à partir du modèle entité-association en transformant chaque entité en une table et chaque relation en une ou plusieurs tables, en utilisant des clés primaires pour l'identification unique et des clés étrangères pour représenter les relations (voir section 2).
• La notation des clés primaires dans le schéma relationnel se fait généralement par une mise en évidence (ex : soulignement ou gras), tandis que les clés étrangères sont indiquées par leur nom suivi de leur référence à une autre table.
• L'organisation des tables doit respecter les contraintes d'intégrité référentielle, de domaine, et d'unicité, pour assurer la cohérence des données.
• La correspondance entre le modèle entité-association et le schéma relationnel implique une traduction précise des cardinalités et des relations, notamment via la création de tables associatives pour les relations n-n (voir section 2).

💡 À retenir

Le schéma relationnel organise les données sous forme de tables reliées par des clés primaires et étrangères, permettant une gestion cohérente et structurée des bases de données, en respectant la correspondance avec le modèle entité-association.

📊 Tableaux de Synthèse

⚠️ Pièges & Confusions Fréquentes

1. Confondre entité et relation : une entité représente un objet, une relation une association.
2. Oublier de préciser la cardinalité lors de la modélisation, ce qui peut entraîner des incohérences.
3. Mal nommer les attributs ou les tables, rendant la traçabilité difficile.
4. Confondre clé primaire et clé étrangère : la première identifie, la seconde référence.
5. Négliger la participation totale ou partielle, impactant l’intégrité du modèle.
6. Lors de la transformation, oublier de créer une table associative pour une relation n-n.
7. Utiliser des noms d’attributs ou de tables peu explicites, compliquant la compréhension.
8. Ignorer les contraintes d’intégrité lors de la création des relations.

✅ Checklist Examen

1. Connaître la définition d’une entité, d’un attribut, et d’une relation selon Chen (1976).
2. Savoir représenter graphiquement un modèle entité-association avec ses cardinalités et participations.
3. Expliquer la différence entre modification du modèle et transformation relationnelle.
4. Décrire le processus de conversion d’une entité en table relationnelle, en précisant la clé primaire.
5. Savoir créer une table associative pour une relation n-n, en utilisant les clés primaires comme clés étrangères.
6. Connaître la définition de la clé primaire et de la clé étrangère selon Elmasri & Navathe (2015).
7. Maîtriser la syntaxe SQL pour une requête SELECT avec ORDER BY et GROUP BY.
8. Savoir utiliser les fonctions d’agrégation SQL (COUNT, SUM, MAX).
9. Expliquer le principe d’une jointure interne (INNER JOIN) dans une requête SQL.
10. Connaître la syntaxe SQL pour une requête UPDATE et DELETE.
11. Comprendre l’importance de la normalisation dans la transformation relationnelle.
12. Vérifier la maîtrise des contraintes d’intégrité référentielle lors de la création des relations.