Base de Données et langage SQL
(IUT, département informatique, 1re année)
Institut Universitaire de Technologie de Villetaneuse – Département Informatique
Avenue Jean-Baptiste Clément
93430 Villetaneuse
Adresse électronique : laurent[dot]audibert[at]iutv[dot]univ-paris13[dot]fr
2
Aujourd’hui, la disponibilité de systèmes de gestion de base de données fiables permet aux organisations de toutes tailles de gérer des données efficacement, de déployer des applications utilisant ces données et de les stocker. Les bases de données sont actuellement au cœur du système d’information des entreprises.
Les bases de données relationnelles constituent l’objet de ce cours. Ces bases sont conçues suivant le modèle relationnel, dont les fondations théoriques sont solides, et manipulées en utilisant l’algèbre relationnelle. Il s’agit, à ce jour, de la méthode la plus courante pour organiser et accéder à des ensembles de données. Nous décrivons le modèle relationnel, le passage du modèle entités-associations au modèle relationnel et enfin l’algèbre relationnelle dans le chapitre 3.
Le chapitre 4 est entièrement consacré au langage SQL (Structured Query Language) qui peut être considéré comme le langage d’accès normalisé aux bases de données relationnelles. Ce langage est supporté par la plupart des systèmes de gestion de bases de données commerciaux (comme Oracle) et du domaine libre (comme PostgreSQL). Nous détaillons dans ce chapitre les instructions du langage de définition de données et celles du langage de manipulation de données.
Différents exercices de travaux dirigés et de travaux pratiques ponctuent ce cours. Des exemples de corrections de certains des exercices sont regroupés dans la dernière partie du document (chapitre 5).
Ce document constitue le support du cours « Base de Données et langage SQL » dispensé aux étudiants du département d’informatique de l’institut universitaire de technologie de Villetaneuse en semestre décalé. Ce support a été réalisé en utilisant les ouvrages cités en bibliographie.
Vous trouverez ce document en ligne (pour avoir la dernière version par exemple) à l’adresse suivante :
3
4
1 Introduction aux bases de données {S1} 9
1.1 Qu’est-ce qu’une base de données ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.1.1 Notion de base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.1.2 Modèle de base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.2 Système de gestion de base de données (SGBD) . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.1 Principes de fonctionnement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.2 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.3 Niveaux de description des données ANSI/SPARC . . . . . . . . . . . . . . . . . . 12
1.2.4 Quelques SGBD connus et utilisés . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.3 Travaux Dirigés – Sensibilisation à la problématique des bases de données {S1} . . . . . . 14
1.3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3.2 Approche naïve . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3.3 Affinement de la solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.3.4 Que retenir de ce TD ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Conception des bases de données (modèle E-A) {S2-3} 17
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.1.1 Pourquoi une modélisation préalable ? . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.1.2 Merise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.2 Éléments constitutifs du modèle entités-associations . . . . . . . . . . . . . . . . . . . . . . 18
2.2.1 Entité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.2.2 Attribut ou propriété, valeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2.3 Identifiant ou clé . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2.4 Association ou relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.2.5 Cardinalité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.3 Compléments sur les associations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.1 Associations plurielles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.2 Association réflexive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.3 Association n-aire (n > 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.4 Travaux Dirigés – Modèle entités-associations {S2} . . . . . . . . . . . . . . . . . . . . . . . 27
2.4.2 Étudiants, cours, enseignants, salles, . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.4.3 Deux associations ne peuvent lier un même ensemble d’entités . . . . . . . . . . . 27
2.4.4 Comprenez-vous les type-associations n-aire ? . . . . . . . . . . . . . . . . . . . . . 28
2.4.5 Cas d’une bibliothèque (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.5 Règles de bonne formation d’un modèle entités-associations . . . . . . . . . . . . . . . . . 30
2.5.1 Règles portant sur les noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.5.2 Règles de normalisation des attributs . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.5.3 Règles de fusion/suppression d’entités/associations . . . . . . . . . . . . . . . . . . 32
2.5.4 Normalisation des type-entités et type-associations . . . . . . . . . . . . . . . . . . 36
2.6 Élaboration d’un modèle entités-associations . . . . . . . . . . . . . . . . . . . . . . . . . . 38
2.6.1 Étapes de conceptions d’un modèle entités-associations . . . . . . . . . . . . . . . . 38
2.6.2 Conseils divers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
5
6 TABLE DES MATIÈRES
2.7.1 Mais qui a fait cette modélisation ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.7.2 Cas d’une bibliothèque (2e partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.7.3 Cas d’une entreprise de dépannage . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
3 Bases de données relationnelles {S4-5} 43
3.1 Introduction au modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.1 Présentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.2 Éléments du modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.3 Passage du modèle entités-associations au modèle relationnel . . . . . . . . . . . . 45
3.2 Normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.2 Dépendance fonctionnelle (DF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.3 Première et deuxième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.2.4 Troisième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.2.6 Quatrième et cinquième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . 50
3.2.7 Remarques au sujet de la normalisation . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.3 Travaux Dirigés – Modèle relationnel {S4} . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.3.1 Passage du modèle entités-associations au modèle relationnel . . . . . . . . . . . . 54
3.3.2 Normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.4 Algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.2 Sélection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.3 Projection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.4 Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
3.4.5 Intersection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
3.4.6 Différence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.4.7 Produit cartésien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.4.8 Jointure, theta-jointure, equi-jointure, jointure naturelle . . . . . . . . . . . . . . . . 60
3.4.9 Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3.5.1 Exercices de compréhension de requêtes . . . . . . . . . . . . . . . . . . . . . . . . 62
3.5.2 Trouver la bonne requête . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
4 Langage SQL 65
4.1 Introduction {S6} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1.1 Présentation générale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1.2 Catégories d’instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
4.1.3 PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
4.2 Définir une base – Langage de définition de données (LDD) . . . . . . . . . . . . . . . . . 68
4.2.1 Introduction aux contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . 68
4.2.2 Créer une table : CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
4.2.3 Contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.2.4 Supprimer une table : DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.2.5 Modifier une table : ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.3 Modifier une base – Langage de manipulation de données (LMD) . . . . . . . . . . . . . . 71
4.3.1 Insertion de n-uplets : INSERT INTO . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.3.3 Suppression de n-uplets : DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
4.4 Travaux Pratiques – SQL : Première base de données {S6} . . . . . . . . . . . . . . . . . . . 73
4.4.1 Informations pratiques concernant PostgreSQL . . . . . . . . . . . . . . . . . . . . 73
4.4.2 Première base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
4.5 Interroger une base – Langage de manipulation de données : SELECT (1re partie) {S7} . . . 76
4.5.1 Introduction à la commande SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
TABLE DES MATIÈRES 7
4.5.2 Traduction des opérateurs de l’algèbre relationnelle (1re partie) . . . . . . . . . . . 77
4.5.3 Syntaxe générale de la commande SELECT . . . . . . . . . . . . . . . . . . . . . . . . 77
4.5.4 La clause SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
4.5.5 La clause FROM (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
4.5.6 La clause ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
4.5.7 La clause WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
4.6 Travaux Pratiques – SQL : Premières requêtes {S7} . . . . . . . . . . . . . . . . . . . . . . . 84
4.6.1 Premières requêtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
4.6.2 Requêtes déjà résolues en utilisant l’algèbre relationnelle . . . . . . . . . . . . . . . 84
4.6.3 Utilisation des expressions régulières . . . . . . . . . . . . . . . . . . . . . . . . . . 85
4.7 Interroger une base – Langage de manipulation de données : SELECT (2e partie) {S8} . . . 86
4.7.1 La clause FROM (2e partie) : les jointures . . . . . . . . . . . . . . . . . . . . . . . . . 86
4.7.2 Les clauses GROUP BY et HAVING et les fonctions d’agrégation . . . . . . . . . . . . . 90
4.7.3 Opérateurs ensemblistes : UNION, INTERSECT et EXCEPT . . . . . . . . . . . . . . . . 92
4.7.4 Traduction des opérateurs de l’algèbre relationnelle (2e partie) . . . . . . . . . . . . 92
4.8 Travaux Pratiques – SQL : Requêtes avancées {S8} . . . . . . . . . . . . . . . . . . . . . . . 95
4.8.1 Prix de GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
4.8.2 Requêtes déjà résolues en utilisant l’algèbre relationnelle . . . . . . . . . . . . . . . 95
4.8.3 GROUP toujours ! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
4.9 Nouveaux objets – Langage de définition de données (LDD) {S9} . . . . . . . . . . . . . . 96
4.9.1 Séquences (CREATE SEQUENCE) et type SERIAL . . . . . . . . . . . . . . . . . . . . . 96
4.9.3 Vues (CREATE VIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
4.9.4 Schémas (CREATE SCHEMA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4.10 Travaux Pratiques – SQL : Nouveaux objets {S9} . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.1 Séquences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.2 Schéma et vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.3 Règles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.4 Toujours des requêtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.11 SQL intégré {S10} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.2 Connexion au serveur de bases de données . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.3 Exécuter des commandes SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.11.4 Les variables hôtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.11.5 Variables indicateur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.11.6 Gestion des erreurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
4.11.7 Curseurs pour résultats à lignes multiples . . . . . . . . . . . . . . . . . . . . . . . 107
4.11.9 Exemple complet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
5 Corrections 111
Bibliographie 113
8 TABLE DES MATIÈRES
Chapitre 1
1.1 Qu’est-ce qu’une base de données?
Il est difficile de donner une définition exacte de la notion de base de données. Une définition très générale pourrait être :
Définition 1.1 -Base de données-Un ensemble organisé d’informations avec un objectif commun.
Peu importe le support utilisé pour rassembler et stocker les données (papier, fichiers, etc.), dès lors que des données sont rassemblées et stockées d’une manière organisée dans un but spécifique, on parle de base de données.
Plus précisément, on appelle base de données un ensemble structuré et organisé permettant le stockagedegrandesquantitésd’informationsafind’enfaciliterl’exploitation(ajout,miseàjour,recherche de données). Bien entendu, dans le cadre de ce cours, nous nous intéressons aux bases de données informatisées.
Le résultat de la conception d’une base de données informatisée est une description des données. Par description on entend définir les propriétés d’ensembles d’objets modélisés dans la base de données et non pas d’objets particuliers. Les objets particuliers sont créés par des programmes d’applications ou des langages de manipulation lors des insertions et des mises à jour des données.
Cette description des données est réalisée en utilisant un modèle de données. Ce dernier est un outil formel utilisé pour comprendre l’organisation logique des données.
La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). Nous y reviendrons dans la section 1.2. Un SGBD est caractérisé par le modèle de description des données qu’il supporte (hiérarchique, réseau, relationnel, objet : cf. section 1.1.2). Les données sont décrites sous la forme de ce modèle, grâce à un Langage de Description des Données (LDD). Cette description est appelée schéma.
Une fois la base de données spécifiée, on peut y insérer des données, les récupérer, les modifier et les détruire. C’est ce qu’on appelle manipuler les données. Les données peuvent être manipulées non seulement par un Langage spécifique de Manipulation des Données (LMD) mais aussi par des langages de programmation classiques.
9
Ainsi, les bases de données de demain devront être capables de gérer plusieurs dizaines de téraoctets de données, géographiquement distribuées à l’échelle d’Internet, par plusieurs dizaines de milliers d’utilisateurs dans un contexte d’exploitation changeant (on ne sait pas très bien maîtriser ou prédire les débits de communication entre sites) voire sur des nœuds volatiles. En physique des hautes énergies, on prédit qu’une seule expérience produira de l’ordre du péta-octets de données par an.
Comme il est peu probable de disposer d’une technologie de disque permettant de stocker sur un unique disque cette quantité d’informations, les bases de données se sont orientées vers des architectures distribuées ce qui permet, par exemple, d’exécuter potentiellement plusieurs instructions d’entrée/sortie en même temps sur des disques différents et donc de diviser le temps total d’exécution par un ordre de grandeur.
Une base de données hiérarchique est une forme de système de gestion de base de données qui lie des enregistrements dans une structure arborescente de façon à ce que chaque enregistrement n’ait qu’un seul possesseur (par exemple, une paire de chaussures n’appartient qu’à une seule personne).
Les structures de données hiérarchiques ont été largement utilisées dans les premiers systèmes de gestion de bases de données conçus pour la gestion des données du programme Apollo de la NASA. Cependant, à cause de leurs limitations internes, elles ne peuvent pas souvent être utilisées pour décrire des structures existantes dans le monde réel.
Le modèle réseau est en mesure de lever de nombreuses difficultés du modèle hiérarchique grâce à la possibilité d’établir des liaisons de type n-n, les liens entre objets pouvant exister sans restriction. Pour retrouver une donnée dans une telle modélisation, il faut connaître le chemin d’accès (les liens) ce qui rend les programmes dépendants de la structure de données
Ce modèle de bases de données a été inventé par C.W. Bachman. Pour son modèle, il reçut en 1973 le prix Turing.
Une base de données relationnelle est une base de données structurée suivant les principes de l’algèbre relationnelle.
Le père des bases de données relationnelles est Edgar Frank Codd. Chercheur chez IBM à la fin des année 1960, il étudiait alors de nouvelles méthodes pour gérer de grandes quantités de données car les modèles et les logiciels de l’époque ne le satisfaisait pas. Mathématicien de formation, il était persuadé qu’il pourrait utiliser des branches spécifiques des mathématiques (la théorie des ensembles
1.2. SYSTÈME DE GESTION DE BASE DE DONNÉES (SGBD) 11
et la logique des prédicats du premier ordre) pour résoudre des difficultés telles que la redondance des données, l’intégrité des données ou l’indépendance de la structure de la base de données avec sa mise en œuvre physique.
En 1970, Codd (1970) publia un article où il proposait de stocker des données hétérogènes dans des tables, permettant d’établir des relations entre elles. De nos jours, ce modèle est extrêmement répandu, mais en 1970, cette idée était considérée comme une curiosité intellectuelle. On doutait que les tables puissent être jamais gérées de manière efficace par un ordinateur.
C’est dans ce type de modèle que se situe ce cours de base de données.
La notion de bases de données objet ou relationnel-objet est plus récente et encore en phase de recherche et de développement. Elle sera très probablement ajoutée au modèle relationnel.
La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). Un SGBD doit permettre l’ajout, la modification et la recherche de données. Un système de gestion de bases de données héberge généralement plusieurs bases de données, qui sont destinées à des logiciels ou des thématiques différents.
Actuellement, la plupart des SGBD fonctionnent selon un mode client/serveur. Le serveur (sous entendu la machine qui stocke les données) reçoit des requêtes de plusieurs clients et ceci de manière concurrente. Le serveur analyse la requête, la traite et retourne le résultat au client. Le modèle client/serveur est assez souvent implémenté au moyen de l’interface des sockets (voir le cours de réseau) ; le réseau étant Internet.
Une variante de ce modèle est le modèle ASP (Application Service Provider). Dans ce modèle, le client s’adresse à un mandataire (broker) qui le met en relation avec un SGBD capable de résoudre la requête. La requête est ensuite directement envoyée au SGBD sélectionné qui résout et retourne le résultat directement au client.
Des objectifs principaux ont été fixés aux SGBD dès l’origine de ceux-ci et ce, afin de résoudre les problèmes causés par la démarche classique. Ces objectifs sont les suivants :
Indépendance physique : La façon dont les données sont définies doit être indépendante des structures de stockage utilisées.
Indépendance logique : Un même ensemble de données peut être vu différemment par des utilisateurs différents. Toutes ces visions personnelles des données doivent être intégrées dans une vision globale.
Accès aux données : L’accès aux données se fait par l’intermédiaire d’un Langage de Manipulation de Données (LMD). Il est crucial que ce langage permette d’obtenir des réponses aux requêtes en un temps « raisonnable ». Le LMD doit donc être optimisé, minimiser le nombre d’accès disques, et tout cela de façon totalement transparente pour l’utilisateur.
Administration centralisée des données (intégration) : Toutes les données doivent être centralisées dans un réservoir unique commun à toutes les applications. En effet, des visions différentes des données (entre autres) se résolvent plus facilement si les données sont administrées de façon centralisée.
Non redondance des données : Afin d’éviter les problèmes lors des mises à jour, chaque donnée ne doit être présente qu’une seule fois dans la base.
Cohérence des données : Les données sont soumises à un certain nombre de contraintes d’intégrité qui définissent un état cohérent de la base. Elles doivent pouvoir être exprimées simplement et vérifiées automatiquement à chaque insertion, modification ou suppression des données. Les contraintes d’intégrité sont décrites dans le Langage de Description de Données (LDD).
Sécurité des données : Les données doivent pouvoir être protégées contre les accès non autorisés. Pour cela, il faut pouvoir associer à chaque utilisateur des droits d’accès aux données.
Résistance aux pannes : Que se passe-t-il si une panne survient au milieu d’une modification, si certains fichiers contenant les données deviennent illisibles ? Il faut pouvoir récupérer une base dans un état « sain ». Ainsi, après une panne intervenant au milieu d’une modification deux solutions sont possibles : soit récupérer les données dans l’état dans lequel elles étaient avant la modification, soit terminer l’opération interrompue.
Pour atteindre certains de ces objectifs (surtout les deux premiers), trois niveaux de description des données ont été définis par la norme ANSI/SPARC.
Le niveau externe correspond à la perception de tout ou partie de la base par un groupe donné d’utilisateurs, indépendamment des autres. On appelle cette description le schéma externe ou vue. Il peut exister plusieurs schémas externes représentant différentes vues sur la base de données avec des possibilités de recouvrement. Le niveau externe assure l’analyse et l’interprétation des requêtes en primitives de plus bas niveau et se charge également de convertir éventuellement les données brutes, issues de la réponse à la requête, dans un format souhaité par l’utilisateur.
Le niveau interne ou physique s’appuie sur un système de gestion de fichiers pour définir la politique de stockage ainsi que le placement des données. Le niveau physique est donc responsable du choix de l’organisation physique des fichiers ainsi que de l’utilisation de telle ou telle méthode d’accès en fonction de la requête. On appelle cette description le schéma interne.
Il existe de nombreux systèmes de gestion de bases de données, en voici une liste non exhaustive :
PostgreSQL : http – dans le domaine public ;
1.2. SYSTÈME DE GESTION DE BASE DE DONNÉES (SGBD) 13
MySQL : http – dans le domaine public ;
Oracle : http – de Oracle Corporation ;
IBM DB2 : http Microsoft SQL : http
Sybase : http
Informix : http
L’objectif de ce TD est de se faire une idée de l’intérêt de toute la théorie sur la conception des bases de données et de l’intérêt de l’utilisation des systèmes de gestion de base de données. En d’autres termes, nous allons essayer d’apporter des éléments de réponse à la question :
« Pourquoi dois-je m’embêter avec toute cette théorie et ces connaissances à assimiler alors que je sais très bien manipuler un fichier, y stocker des informations et les y retrouver avec mon langage de programmation favoris? »
Une solution simple et naïve . . .
Certains d’entre vous ont une expérience des bases de données (il s’agit vraiment de quelque chose d’incontournable aujourd’hui) ou une expérience importante en développement logiciel. Dans le cadre de cet exercice, oubliez toutes vos connaissances et vos réflexions sur le sujet.
1. Votreapplicationvadevoirstockertouteslesinformationsmentionnéesdansl’introduction(section Contexte), et de manière persistante, donc en utilisant un fichier. Quelle est la solution de stockage des données la plus naïve et la plus naturelle venant immédiatement à l’esprit ?
Supposons que nous adoptions la solution naïve et naturelle suivante :
– Nous créons un fichier texte comportant à l’origine une ligne par livre.
– Dans chaque ligne, on trouve les informations titre, auteur, éditeur, numéro du livre séparées par une tabulation.
– Quand une personne emprunte un livre, on complète la ligne du livre en question par les champsnom, prénom, téléphone, adresse et date-emprunt toujours en séparant ces informations par une tabulation.
– Lorsqu’une personne retourne un livre, il suffit d’ajouter un dernier champs date-retour sur la ligne du livre en question.
– Quand un livre est emprunté une nouvelle fois, on crée une nouvelle ligne avec toutes les informa-tions concernant le livre et la personne qui l’emprunte. Bien entendu, le bibliothécaire ne ressaisit pas tout, l’application va chercher la plupart de ces informations dans le fichier.
En fait, on peut voir ce fichier texte comme un tableau de chaînes de caractères dont l’entête des colonnes seraient les suivantes :
Titre | Auteur | Éditeur | N?Livre | Nom | Prénom | Téléphone | Adresse | Date-emprunt | Date-retour 1.3. TRAVAUXDIRIGÉS–SENSIBILISATIONÀLAPROBLÉMATIQUEDESBASESDEDONNÉES{S1}15 L’applicationfonctionnemaintenantdepuis10ans.Lenombredepersonnesinscritesàlabibliothèque est relativement constant (bien que l’on constate un roulement) et de 5000 personnes en moyenne par an. Un abonné emprunte en moyenne 5 livres par mois. 2. Quel est, approximativement, le nombre de lignes du fichier des données ? 3. Quelle est la taille approximative du fichier sachant que chaque caractère occupe 1 octet et qu’uneligne contient, en moyenne, 150 caractères ? 4. Supposons qu’une personne est abonnée depuis l’origine de l’application. Elle prévient le bibliothécaire que son prénom est mal orthographié. Combien de lignes, approximativement, doivent être modifiées pour corriger cette erreur dans tout le fichier de données ? 5. Lorsqu’un abonné emprunte un livre, le bibliothécaire saisit simplement le numéro du livre et lenom et le prénom de l’abonné. L’application se charge alors de parcourir le fichier pour rechercher les informations manquantes concernant le livre et l’abonné afin d’écrire, à la fin du fichier, la nouvelle ligne concernant l’emprunt. Dans le pire des cas, l’application doit parcourir tout le fichier. Supposons qu’un accès au fichier coûte 10ms, qu’une lecture de ligne coûte 6ms et qu’une recherche sur la ligne pour trouver le numéro du livre ou le nom et le prénom de l’abonné coûte 1ms. Quel est, dans le pire des cas, le temps mis par l’application pour compléter les informations saisies par le bibliothécaire ? 6. Énumérez ou résumez tous les problèmes que la représentation des données choisie (le fichier dedonnées) semble poser. Un premier affinage de la solution de la section précédente consiste à utiliser non pas un fichier unique mais quatre fichiers distincts : – Un premier fichier est dédié au stockage des informations concernant les livres de la bibliothèque.– Un second fichier est dédié au stockage des informations concernant les abonnés. – Les informations stockées dans le troisème fichier vont permettre de faire la correspondance entreles deux premiers pour signifier qu’un livre donné est en cours de prêt par un abonné donné depuis une date donnée. – Enfin, un dernier fichier va permettre de stocker l’historique des prêts. Il est similaire au troisièmefichier, mais il comporte en plus une information relative à la date de retour du livre. 7. Précisez le format et les informations stockées dans chacun de ces quatre fichiers. 8. Quels sont les avantages de cette nouvelle solution ? 9. Intéressons-nous au premier fichier (celui concernant les livres). Quels problèmes diagnostiquezvous dans ce fichier ? 10. Le format de ce fichier permet-il de prendre en compte des livres co-écrits par plusieurs auteurs ? 11. Quelle solution proposez-vous ? 1.3.4 Que retenir de ce TD? Les problèmes les plus courants rencontrés dans des bases de données mal conçues peuvent être regroupés selon les critères suivants : Redondance des données – Certains choix de conception entraînent une répétition des données lors de leur insertion dans la base. Cette redondance est souvent la cause d’anomalies provenant de la complexité des insertions. Incohérence en modification – La redondance de l’information entraîne également des risques en cas de modification d’une donnée car on oublie fréquemment de modifier toutes ses occurrences. Anomalie d’insertion – Une mauvaise conception peut parfois empêcher l’insertion d’une information, faute de connaître la valeur de tous ses champs. Pour remédier à ce problème, certains SGBD introduisent une valeur non typée qui signifie que la valeur d’un attribut est inconnue ou indéterminée. Cette valeur (appelée usuellement NULL) indique réellement une valeur inconnue et non une chaîne de caractères vide ou un entier égal à zéro. Dans la première solution proposée, insérer un nouvel abonné qui n’a jamais emprunté de livre peut poser des problèmes. Une solution serait d’insérer des champs vides (suite de tabulations consécutives) au début de la ligne. Anomalie de suppression – Enfin,unemauvaiseconceptionpeutentraîner,lorsdelasuppressiond’une information, la suppression d’autres informations, sémantiquement distinctes, mais indissociables dans la modélisation adoptée. Par exemple, dans la première solution proposée, si l’on désire supprimer toutes les traces d’un livre dans le fichier de données, on fera complètement disparaître tous les abonnés qui n’ont emprunté que ce livre. Bien d’autres enjeux, que ceux que nous avons abordés, sont inhérents aux bases de données. Ces enjeux ont été survolés dans la section 1.2.2 et concernent la gestion des bases de données : indépendance physique, indépendance logique, accès aux données, administration centralisée des données, cohérence des données, partage des données, sécurité des données, résistance aux pannes, etc. Pour toutes ces raisons, j’espère que l’intérêt la théorie sur la conception des bases de données ainsi que l’intérêt de l’utilisation des systèmes de gestion de base de données deviennent évidant pour vous. Chapitre 2 Conception des bases de données : le modèle entités-associations2.1 Introduction2.1.1 Pourquoi une modélisation préalable? Il est difficile de modéliser un domaine sous une forme directement utilisable par un SGBD. Une ou plusieurs modélisations intermédiaires sont donc utiles, le modèle entités-associations constitue l’une des premières et des plus courantes. Ce modèle, présenté par Chen (1976), permet une description naturelle du monde réel à partir des concepts d’entité et d’association. Basé sur la théorie des ensembles et des relations, ce modèle se veut universel et répond à l’objectif d’indépendance données-programmes. Ce modèle, utilisé pour la phase de conception, s’inscrit notamment dans le cadre d’une méthode plus générale et très répandue : Merise. 2.1.2 MeriseMERISE (Méthode d’Étude et de Réalisation Informatique pour les Systèmes d’Entreprise) est certainement le langage de spécification le plus répandu dans la communauté de l’informatique des systèmes d’information, et plus particulièrement dans le domaine des bases de données. Une représentation Merise permet de valider des choix par rapport aux objectifs, de quantifier les solutions retenues, de mettre en œuvre des techniques d’optimisation et enfin de guider jusqu’à l’implémentation. Reconnu comme standard, Merise devient un outil de communication. En effet, Merise réussit le compromis difficile entre le souci d’une modélisation précise et formelle, et la capacité d’offrir un outil et un moyen de communication accessible aux non-informaticiens. Merise propose une démarche, dite par niveaux, dans laquelle il s’agit de hiérarchiser les préoccupations de modélisation qui sont de trois ordres : la conception, l’organisation et la technique. En effet, pour aborder la modélisation d’un système, il convient de l’analyser en premier lieu de façon globale et de se concentrer sur sa fonction : c’est-à-dire de s’interroger sur ce qu’il fait avant de définir comment 17 il le fait. Ces niveaux de modélisation sont organisés dans une double approche données/traitements. Les trois niveaux de représentation des données, puisque ce sont eux qui nous intéressent, sont détaillés ci-dessous. Niveau conceptuel : le modèle conceptuel des données (MCD) décrit les entités du monde réel, en terme d’objets, de propriétés et de relations, indépendamment de toute technique d’organisation et d’implantation des données. Ce modèle se concrétise par un schéma entités-associations représentant la structure du système d’information, du point de vue des données. Niveau logique : lemodèlelogiquedesdonnées(MLD)préciselemodèleconceptuelpardeschoixorganisationnels. Il s’agit d’une transcription (également appelée dérivation) du MCD dans un formalisme adapté à une implémentation ultérieure, au niveau physique, sous forme de base de données relationnelle ou réseau, ou autres (cf. section 1.1.2). Les choix techniques d’implémentation (choix d’un SGBD) ne seront effectués qu’au niveau suivant. Niveau physique : le modèle physique des données (MPD) permet d’établir la manière concrète dont le système sera mis en place (SGBD retenu). 2.2 Éléments constitutifs du modèle entités-associationsLa représentation du modèle entités-associations s’appuie sur trois concepts de base : – l’objet ou entité, – l’association, – la propriété. 2.2.1 EntitéF??. 2.1 – Représentation graphique d’un exemple de type-entité. Définition 2.1 -entité-Une entité est un objet, une chose concrète ou abstraite qui peut être reconnue distinctement et qui est caractérisée par son unicité. Exemples d’entité : Jean Dupont, Pierre Bertrand, le livre que je tiens entre les mains, la Ferrari qui se trouve dans mon garage, etc. Les entités ne sont généralement pas représentées graphiquement. Définition 2.2 -type-entité-Un type-entité désigne un ensemble d’entités qui possèdent une sémantique et des propriétés communes. Les personnes, les livres et les voitures sont des exemples de type-entité. En effet, dans le cas d’une personne par exemple, les informations associées (i.e. les propriétés), comme le nom et le prénom, ne changent pas de nature. Une entité est souvent nommée occurrence ou instance de son type-entité. La figure 2.1 montre la représentation graphique d’un exemple de type-entité (Personne) sans ses propriétés associées. Les type-entité Personne, caractérisé par un nom et un prénom, et Voiture, caractérisé par un nom et une puissance fiscale, ne peuvent pas être regroupés car ils ne partagent leurs propriétés (le prénom est 2.2. ÉLÉMENTS CONSTITUTIFS DU MODÈLE ENTITÉS-ASSOCIATIONS une chaîne de caractères et la puissance fiscale un nombre). Les type-entité Personne, caractérisé par un nom et un prénom, et Livre, caractérisé un titre et un auteur, possèdent tous les deux deux attributs du type chaîne de caractères. Pourtant, ces deux type-entités ne peuvent pas être regroupés car ils ne partagent pas une même sémantique : le nom d’une personne n’a rien à voir avec le titre d’un livre, le prénom d’une personne n’a rien à voir avec un auteur. 2.2.2 Attribut ou propriété, valeurF??. 2.2 – Représentation graphique d’un exemple de type-entité comportant trois attributs Définition 2.3 -attribut, propriété-Un attribut (ou une propriété) est une caractéristique associée à un type-entité ou à un type-association. Exemples d’attribut : le nom d’une personne, le titre d’une livre, la puissance d’une voiture. Définition 2.4 -valeur-Au niveau du type-entité ou du type-association, chaque attribut possède un domaine qui définit l’ensemble des valeurs possibles qui peuvent être choisies pour lui (entier, chaîne de caractères, booléen, . . .). Au niveau de l’entité, chaque attribut possède une valeur compatible avec son domaine. La figure 2.2 montre la représentation graphique d’un exemple de type-entité (Personne) avec trois attributs. Règle 2.5Un attribut ne peut en aucun cas être partagé par plusieurs type-entités ou type-associations. Règle 2.6Un attribut est une donnée élémentaire, ce qui exclut des données calculées ou dérivées. Règle 2.7Un type-entité et ses attributs doivent être cohérents entre eux (i.e. ne traiter que d’un seul sujet). Par exemple, si le modèle doit comporter des informations relatives à des articles et à leur fournisseur, ces informations ne doivent pas coexister au sein d’un même type-entité. Il est préférable de mettre les informations relatives aux articles dans un type-entité Article et les informations relatives aux fournisseurs dans un type-entité Fournisseur. Ces deux type-entités seront probablement ensuite reliés par un type-association. 2.2.3 Identifiant ou cléDéfinition 2.8 -identifiant,clé-Unidentifiant(ouclé)d’untype-entitéoud’untype-associationestconstitué par un ou plusieurs de ses attributs qui doivent avoir une valeur unique pour chaque entité ou association de ce type. F??. 2.3 – Représentation graphique d’un exemple de type-entité comportant quatre attributs dont un est un identifiant : deux personnes peuvent avoir le même nom, le même prénom et le même âge, mais pas le même numéro de sécurité sociale. Règle 2.9Chaque type-entité possède au moins un identifiant, éventuellement formé de plusieurs attributs. Ainsi, chaque type-entité possède au moins un attribut qui, s’il est seul, est donc forcément l’identifiant. Dans la représentation graphique, les attributs qui constituent l’identifiant sont soulignés et placés en tête (cf. figure 2.3). 2.2.4 Association ou relationF??. 2.4 – Représentation graphique d’un exemple de type-association liant deux type-entités. Définition 2.10 -association-Une association (ou une relation) est un lien entre plusieurs entités. Exemples d’association : l’emprunt par l’étudiant Tanidute du 3e exemplaire du livre « Maîtrisez SQL ». Les associations ne sont généralement pas représentées graphiquement. Définition 2.11 -type-association-Un type-association (ou un type-relation) désigne un ensemble de relations qui possèdent les mêmes caractéristiques. Le type-association décrit un lien entre plusieurs type-entités. Les associations de ce type-association lient des entités de ces type-entités. Comme les type-entités, les type-associations sont définis à l’aide d’attributs qui prennent leur valeur dans les associations. Règle 2.12Un attribut peut être placé dans un type-association uniquement lorsqu’il dépend de toutes les entités liées par le type-association. Un type-association peut ne pas posséder d’attribut explicite et cela est relativement fréquent, mais on verra qu’il possède au moins des attributs implicites. Exemples de type-association : l’emprunt d’un livre à la bibliothèque. La figure 2.4 montre la représentation graphique d’un exemple de type-association. Par abus de langage, on utilise souvent le mot association en lieu et place du mot type-association, il faut cependant prendre garde à ne pas confondre les deux concepts. 2.2. ÉLÉMENTS CONSTITUTIFS DU MODÈLE ENTITÉS-ASSOCIATIONS Définition 2.13 -participant-Les type-entités intervenant dans un type-association sont appelés les participants de ce type-association. Définition 2.14 -collection-L’ensemble des participants d’un type-association est appelé la collection de ce type-association. Cette collection comporte au moins un type-entité (cf. section 2.3.2), mais elle peut en contenir plus, on parle alors de type-association n-aire (quand n = 2 on parle de type-association binaire, quand n = 3 de type-association ternaire, . . .). Définition 2.15 -dimensionouaritéd’untype-association-Ladimension,oul’aritéd’untype-association est le nombre de type-entités contenu dans la collection. Comme un type-entité, un type-association possède forcément un identifiant, qu’il soit explicite ou non. Règle 2.16La concaténation des identifiants des type-entités liés à un type-association constitue un identifiant de ce type-association et cet identifiant n’est pas mentionné sur le modèle (il est implicite). Cette règle implique que deux instances d’un même type-association ne peuvent lier un même ensemble d’entités. Souvent, un sous-ensemble de la concaténation des identifiants des type-entités liés suffit à identifier le type-association. On admet également un identifiant plus naturel et explicite, à condition qu’il ne soit qu’un moyen d’exprimer plus simplement cette concaténation. 2.2.5 CardinalitéF??. 2.5 – Représentation graphique des cardinalités d’un type-association. Dans cet exemple pédagogique, on suppose qu’un livre ne peut posséder qu’un auteur. Exemple de cardinalité : une personne peut être l’auteur de 0 à n livre, mais un livre ne peut être écrit que par une personne (cf. figure 2.5). Règle 2.18L’expression de la cardinalité est obligatoire pour chaque patte d’un type-association. Règle 2.19Une cardinalité minimal est toujours 0 ou 1 et une cardinalité maximale est toujours 1 ou n. Ainsi, si une cardinalité maximale est connue et vaut 2, 3 ou plus, alors nous considérons qu’elle est indéterminée et vaut n. En effet, si nous connaissons n au moment de la conception, il se peut que cette valeur évolue au cours du temps. Il vaut donc mieux considérer n comme inconnue dès le départ. De la même manière, on ne modélise pas des cardinalités minimales qui valent plus de 1 car ces valeurs sont également susceptibles d’évoluer. Enfin, une cardinalité maximale de 0 n’a pas de sens car elle rendrait le type-association inutile. Les seuls cardinalités admises sont donc : 0,1 : une occurrence du type-entité peut exister tout en étant impliquée dans aucune association et peut être impliquée dans au maximum une association. 0,n : c’estlacardinalitélaplusouverte ;uneoccurrencedutype-entitépeutexistertoutenétantimpliquée dans aucune association et peut être impliquée, sans limitation, dans plusieurs associations. 1,1 : une occurrence du type-entité ne peut exister que si elle est impliquée dans exactement (au moins et au plus) une association. 1,n : une occurrence du type-entité ne peut exister que si elle est impliquée dans au moins une association. RemarquesLa seule difficulté pour établir correctement les cardinalités est de se poser les question dans le bon sens. Pour augmenter le risque d’erreurs, il faut noter que, pour les habitués, ou les futurs habitués, du modèle UML, les cardinalités d’un type-association sont « à l’envers » (par référence à UML) pour les type-associations binaires et « à l’endroit » pour les n-aires avec n > 2. La notion de cardinalité n’est pas définie de la même manière dans le modèle Américain et dans le modèle Européen (Merise). Dans le premier n’existe que la notion de cardinalité maximale. Avec un SGBD relationnel, nous pourrons contraindre des cardinalités à des valeurs comme 2, 3 ou plus en utilisant des déclencheurs (trigger, cf. section ??). 2.3 Compléments sur les associations2.3.1 Associations pluriellesF??. 2.6 – Exemple d’associations plurielles entre un type-entité Personne et un type-entité Livre. Sur ce schéma, un type-association permet de modéliser que des personnes écrivent des livres et un autre que des personnes critiquent (au sens de critique littéraire) des livres. Deux mêmes entités peuvent être plusieurs fois en association (c’est le cas sur la figure 2.6). 2.3.2 Association réflexiveLes type-associations réflexifs sont présents dans la plupart des modèles. Définition 2.20 -Type-association réflexif-Un type-association est qualifié de réflexif quand il matérialise une relation entre un type-entité et lui-même (cf. figure 2.7). 2.3. COMPLÉMENTS SUR LES ASSOCIATIONS F??. 2.7 – Exemple d’associations reflexives sur le type-entité Personne. Le premier type-association permet de modéliser la relation parent/enfant et le deuxième type-association la relation de fraternité. 2.3.3 Association n-aire (n > 2)Dans la section 2.2.4 nous avons introduit la notion de type-association n-aire. Ce type-association met en relation n type-entités. Même s’il n’y a, en principe, pas de limite sur l’arité d’un type-association, dans la pratique on ne va rarement au-delà de trois. Les associations de degré supérieur à deux sont plus difficiles à manipuler et à interpréter, notamment au niveau des cardinalités. Exemple d’association n-aire inappropriéeF??. 2.8 – Exemple de type-association ternaire inapproprié. Le type-association ternaire Contient associant les type-entités Facture, Produit et Client représenté sur la figure 2.8 est inapproprié puisqu’une facture donnée est toujours adressée au même client. En effet, cette modélisation implique pour les associations (instances du type-association) Contient une répétition du numéro de client pour chaque produit d’une même facture. F??. 2.9 – Type-association ternaire de la figure 2.8 corrigé en deux type-associations binaires. La solution consiste à éclater le type-association ternaire Contient en deux type-associations binaires comme représenté sur la figure 2.9. Décomposition d’une association n-aireF??. 2.10 – Exemple de type association ternaire entre des type-entités Créneau horaire, Salle et Film. La figure 2.10 nous montre un exemple de type-association ternaire entre les type-entités Créneau horaire, Salle et Film. Il est toujours possible de s’affranchir d’un type-association n-aire (n > 2) en se ramenant à des type-associations binaires de la manière suivante : – On remplace le type-association n-aire par un type-entité et on lui attribut un identifiant. – On crée des type-associations binaire entre le nouveau type-entité et tous les type-entités de lacollection de l’ancien type-association n-aire. La figure 2.11 illustre le résultat de cette transformation sur le schéma de la figure 2.10. L’avantage du schéma de la figure 2.11 est de rendre plus intelligible la lecture des cardinalités. Il ne faut surtout pas le voir comme un aboutissement mais comme une étape intermédiaire avant d’aboutir au schéma de la figure 2.10 (cf. règle 2.27). Ainsi, le mécanisme, que nous venons de détailler ci-dessus, 2.3. COMPLÉMENTS SUR LES ASSOCIATIONS F??. 2.11 – Transformation du type-association ternaire de la figure 2.10 en un type-entité et trois typeassociations binaires. de passage d’un type-association n-aire (n > 2) à un type-entité et n type-associations binaires est tout à fait réversible à condition que : – toutes les pattes des type-associations binaires autour du type-entité central ont une cardinalitémaximale de 1 au centre et de n à l’extérieur ; – les attributs du type-entité central satisfont la règle de bonne formation des attributs de type-association (cf. section 2.5.2). Détection d’une erreur de modélisation par décomposition d’une association n-airePasser par cette étape intermédiaire ne comportant pas de type-association n-aire (n > 2) peut, dans certains cas, éviter d’introduire un type-association n-aire inapproprié. Imaginons par exemple un type-association ternaire Vol liant trois type-entités Avion, Trajet et Pilote comme représenté sur la figure 2.12. La transformation consistant à supprimer le type-association ternaire du modèle de la figure 2.12 produit le modèle de la figure 2.13. Ce modèle fait immédiatement apparaître une erreur de conception qui était jusque là difficile à diagnostiquer : généralement, à un vol donné sont affectés plusieur pilotes (par exemple le commandant de bord et un copilote) et non pas un seul. F??. 2.12 – Modèle représentant un type-association ternaire Vol liant trois type-entités Avion, Trajet et Pilote. F??. 2.13 – Transformation du type-association ternaire de la figure 2.12 en un type-entité et trois typeassociations binaires. F??. 2.14 – Modèle de la figure 2.13 corrigé au niveau des cardinalités. 2.4. TRAVAUXDIRIGÉS – MODÈLE ENTITÉS-ASSOCIATIONS {S2} 2.4 Travaux Dirigés – Modèle entités-associations (1repartie)2.4.1 Attention aux attributs multiplesF??. 2.15 – Modélisation incorrecte d’un enseignement. On désire modéliser par un modèle entités-associations le fait qu’un enseignement est dispensé par un enseignant à plusieurs étudiants qui ne suivent qu’un enseignement. On vous propose la modélisation représentée sur la figure 2.15. 1. Critiquez cette modélisation. 2. Proposez-en une correcte. 2.4.2 Étudiants, cours, enseignants, salles, . . . Modélisez indépendamment les situations suivantes : 3. Plusieurs cours sont offerts. Un cours peut être suivi par plusieurs étudiants et un étudiant peut s’inscrire à plusieurs cours. Pour chaque cours, on veut connaître la liste des étudiants et leur note (chaque cours ne comporte qu’une seule évaluation). 4. Plusieurs cours sont offerts. Un cours est dispensé par un seul enseignant et un enseignant peut dispenser plusieurs cours. Pour chaque cours, on veut connaître l’enseignant qui le dispense. On s’intéresse maintenant à la modélisation d’une situation globale et plus complexe : – Il existe plusieurs matières (mathématiques, sciences-physiques, français, anglais, philosophie). – Plusieurs cours sont offerts et il peut y avoir plusieurs cours de la même matière. – Un cours est dispensé par un, et un seul, enseignant et correspond à une matière. – Un étudiant peut s’inscrire à plusieurs cours. – Un cours est toujours dispensé dans une même salle, mais une salle peut recevoir plusieurs cours(successivement). – Chaque cours ne comporte qu’une seule évaluation. 5. Proposez un modèle entités-associations permettant de modéliser la situation décrite ci-dessus. 2.4.3 Deux instances d’un même type-association ne peuvent lier un même ensemble d’entitésConsidérons la modélisation de la figure 2.16 qui exprime qu’un client commande des produits chez un fournisseur. 6. Imaginons qu’un même client commande un même produit chez un même fournisseur plus d’unefois. Cette situation est-elle compatible avec le modèle ? 7. Proposez une amélioration de ce modèle. F??. 2.16 – Le type-association Commande lie les type-entités Produit, Client et Fournisseur. F??. 2.17 – Modélisation des résidences principales et secondaires d’un ensemble de personnes. 2.4.4 Comprenez-vous les type-associations n-aire? On désire créer une base de données sur les résidences principales et secondaires d’un échantillon de la population possédant exactement une résidence principale et une résidence secondaire. Dans cette base, si une personne ne peut posséder plus d’une résidence, une résidence peut très bien appartenir à plusieurs personnes. Pour modéliser cette situation, on vous propose le modèle de la figure 2.17. 8. Expliquez la cardinalité 1 ? 1 de l’une des pattes du type-association ternaire. 9. Critiquez cette solution. 10. Proposez un modèle corrigé. F??. 2.18 – Ces deux modélisations ne sont pas des alternatives. 2.4. TRAVAUXDIRIGÉS – MODÈLE ENTITÉS-ASSOCIATIONS {S2} 11. Les deux modèles de la figure 2.18 ne sont pas équivalents. Expliquez pourquoi. 2.4.5 Cas d’une bibliothèque (1re partie)Une petite bibliothèque souhaite informatiser la gestion de son fonds documentaire et de ses emprunts. Dans cette perspective, le bibliothécaire, qui n’est pas un informaticien, a rédigé le texte suivant : Grâce à cette informatisation, un abonné devra pouvoir retrouver un livre en connaissant son titre. Il doit aussi pouvoir connaître la liste des livres d’un auteur. Un abonné a le droit d’emprunter au maximum dix ouvrages simultanément. Les prêts sont accordés pour une durée de quinze jours. La gestion des prêts doit permettre de connaître, à tout moment, la liste des livres détenus par un abonné, et inversement, de retrouver le nom des abonnés détenant un livre absent des rayons. Un livre peut être écrit par plusieurs auteurs. Chaque livre est acheté en un ou plusieurs exemplaires. 13. Identifiez, dans le texte ci-dessus, les mots devant se concrétiser par des entités, des associationsou des attributs. 14. Proposez un modèle entités-associations permettant de modéliser la situation décrite ci-dessus. 2.5 Règles de bonne formation d’un modèle entités-associationsLa bonne formation d’un modèle entités-associations permet d’éviter une grande partie des sources d’incohérences et de redondance. Pour être bien formé, un modèle entités-associations doit respecter certaines règles et les type-entités et type-associations doivent être normalisées. Un bon principe de conception peut être formulé ainsi : « une seule place pour chaque fait ». 2.5.1 Règles portant sur les nomsRègle 2.21Dans un modèle entités-associations, le nom d’un type-entité, d’un type-association ou d’un attribut doit être unique. F??. 2.19 – La présence des deux type-entités Enseignant et Etudiant est symptomatique d’une modélisation inachevée. A terme, ces deux type-entités doivent être fusionnés en un unique type-entité Personne. Référez vous à la règle 2.25 pour plus de précisions concernant cette erreur de modélisation. F??. 2.20 – Ici, les attributs Adresse de facturation sont redondants. Cette situation doit être évitée à tout prix car elle entraîne un gaspillage d’espace mémoire mais aussi et surtout un grand risque d’incohérence. En effet, que faire si, dans le cadre d’une occurrence du type-association Correspondre, la valeurs des deux attributs Adresse de facturation diffèrent ? F??. 2.21 – Dans cette situation, les deux attributs Adresse doivent simplement être renommés en Adresse client et Adresse fournisseur. Il en va de même pour les deux attributs Nom. Lorsque des attributs portent le même nom, c’est parfois le signe d’une modélisation inachevée (figure 2.19) ou d’une redondance (figure 2.20). Sinon, il faut simplement ajouter au nom de l’attribut le nom du type-entité ou du type-association dans lequel il se trouve (figure 2.21). Il faut toutefois remarquer que le dernier cas décrit n’est pas rédhibitoire et que les SGDB Relationnel s’accommodent très bien de relations comportant des attributs de même nom. L’écriture des requêtes sera tout de même plus lisible si les attributs ont tous des noms différents. 2.5.2 Règles de normalisation des attributsRègle 2.22Il faut remplacer un attribut multiple en un type-association et un type-entité supplémentaires. F??. 2.22 – Remplacement des attributs multiples en un type-association et un type-entité et décomposition des attributs composites. Il est également intéressant de décomposer les attributs composites comme l’attribut Adresse par exemple. Il est en effet difficile d’écrire une requête portant sur la ville où habitent les employés si cette information est noyée dans un unique attribut Adresse. Règle 2.23Il ne faut jamais ajouter un attribut dérivé d’autres attributs, que ces autres attributs se trouvent dans le même type-entité ou pas. F??. 2.23 – Il faut supprimer l’attribut Montant total du type-entité Commande car on peut le calculer à partir des attributs Quantité du type association Contenir et Prix unitaire du type-entité Article. En effet, les attributs dérivés induisent un risque d’incohérence entre les valeurs des attributs de base et celles des attributs dérivés. La figure 2.23 illustre le cas d’un attribut Montant total dans un type-entité Commande qui peut être calculé à partir des attributs Quantité du type association Contenir et Prix unitaire du type-entité Article. Il faut donc supprimer l’attribut Montant total dans le type-entité Commande. D’autres attributs dérivés sont également à éviter comme l’âge, que l’on peut déduire de la date de naissance et de la date courante. Il faut cependant faire attention aux pièges : par exemple, le code postal ne détermine ni le numéro de département ni la Ville Comme nous l’avons déjà dit (cf. règle 2.12), les attributs d’un type-association doivent dépendre directement des identifiants de tous les type-entités de la collection du type-association. F??. 2.24 – Comme la cardinalité maximale du type-association Livrer est 1 du côté du type-entité Livraison, l’attribut Nom livreur de Livrer doit être déplacé dans Livraison. Règle 2.24Un attribut correspondant à un type énuméré est généralement avantageusement remplacé par un type-entité. Par exemple, sur la figure 2.25, l’attribut Type caractérise le type d’une émission et peut prendre des valeurs comme : actualité, culturelle, reportage, divertissement, etc. Remplacer cet attribut par un typeentité permet, d’une part, d’augmenter la cohérence (en s’affranchissant, par exemple, des variations du genre culturelle, culture, Culture, . . .) et d’autre part, si les cardinalités le permettent, de pouvoir affecter plusieurs types à une même entité (ex : actualité et culturelle) F??. 2.25 – Un attribut correspondant à un type énuméré est généralement avantageusement remplacé par un type-entité.. 2.5.3 Règles de fusion/suppression d’entités/associationsRègle 2.25Il faut factoriser les type-entités quand c’est possible. La spécialisation du type-entité obtenu peut se traduire par l’introduction d’un attribut supplémentaire dont l’ensemble des valeurs possibles est l’ensemble des noms des type-entités factorisés (figure 2.26). F??. 2.26 – Il faut factoriser les type-entités quand c’est possible, éventuellement en introduisant un nouvel attribut. Mais l’introduction d’un attribut supplémentaire n’est pas forcément nécessaire ou souhaitable. Par exemple, sur le modèle entités-associations final de la figure 2.27, on peut distinguer les entités qui correspondent à des écrivains ou des abonnés en fonction du type de l’association, Ecrire ou Emprunter, que l’entité en question entretient avec une entité du type Livre. Ne pas introduire d’attribut permet en outre de permettre à une personne d’être à la fois un Abonné et un Écrivain. Règle 2.26Il faut factoriser les type-associations quand c’est possible. Cette règles est le pendant pour les type-associations de la règle 2.25 qui concerne les type-entités. La spécialisationdutype-associationobtenupeutsetraduireparl’introductiond’unattributsupplémentaire dont l’ensemble des valeurs possibles est l’ensemble des noms des type-associations factorisés. La figure 2.28 montre un exemple de multiplication inutile de type-associations. Règle 2.27Un type-entité remplaçable par un type-association doit être remplacé. Parexemple,letype-entitéProjectiondelafigure2.11page25doitêtreremplacéparletype-association ternaire Projeter pour aboutir au schéma de la figure 2.10 page 24. Règle 2.28Lorsque les cardinalités d’un type-association sont toutes 1, 1 c’est que le type-association n’a pas lieu d’être. la commune La Feuillade, dont le code postal est 19600, est située dans le département de la Dordogne (24). Dans cette non correspondance entre code postal et département, il y a toute la Corse ! Il n’y a pas non plus de correspondance biunivoque entre le code postal et une ville. Une commune peut avoir plusieurs codes postaux, un code postal peut recouvrir plusieurs communes. F??. 2.28 – Un seul type-association suffit pour remplacer les quatre type-associations Jouer en tant que . . . Il faut aussi se poser la question de l’intérêt du type-association quand les cardinalités maximale sont toutes de 1. F??. 2.29 – Lorsque les cardinalités d’un type-association sont toutes 1, 1 c’est qu’il s’agit d’un typeassociation fantôme. Règle 2.29Il faut veiller à éviter les type-associations redondants. En effet, s’il existe deux chemins pour se rendre d’un type-entité à un autre, alors ces deux chemins doivent avoir deux significations ou deux durées de vie distinctes. Dans le cas contraire, il faut supprimer le chemin le plus court puisqu’il est déductible des autres chemins. Par exemple, dans le modèle représenté sur la figure 2.31, si un client ne peut pas régler la facture d’un autre client, alors le type-association Payer est redondant et doit purement et simplement être supprimé F??. 2.30 – Même si toutes les cardinalités maximale sont de 1, il vaut mieux conserver le type-association Etre. F??. 2.31 – Si un client ne peut pas régler la facture d’un autre client, alors le type-association Payer est inutile. F??. 2.32 – Solution au problème de la redondance du type-association de la figure 2.31. F??. 2.33 – Dans le modèle de la figure 2.31, si un client peut régler la facture d’un autre client, il faut remplacer le type-entité Règlement par un type-association Régler. du modèle (cf. figure 2.32). On pourra toujours retrouver le client qui a effectué un règlement en passant par la facture correspondante. Par contre, si un client peut régler la facture d’un autre client, alors c’est la règle 2.27 qu’il faut appliquer : on remplace le type-entité Règlement par un type-association Régler (cf. figure 2.33). 2.5.4 Normalisation des type-entités et type-associationsIntroductionLes formes normales sont différent stades de qualité qui permettent d’éviter la redondance, source d’anomalies. La normalisation peut être aussi bien effectuée sur un modèle entités-associations, où elle s’applique sur les type-entités et type-associations, que sur un modèle relationnel. Nous avons décidé de présenter deux fois cette théorie de la normalisation : – Une première fois, dans le cadre du modèle entités-associations (la présente section 2.5.4), enprivilégiant une approche plus intuitive qui n’introduit pas explicitement la notion de dépendance fonctionnelle (et encore moins les notions de dépendance multivaluée et de jointure). Nous nous arrêterons, dans cette section, à la forme normale de Boyce-Codd. – Puis une seconde fois, dans le cadre de modèle relationnel (section 3.2), en privilégiant une ap-proche plus formelle s’appuyant sur la définition des dépendances fonctionnelle, multivaluée et de jointure. Nous irons alors jusqu’à la cinquième forme normale. Première forme normale (1FN)Définition 2.30 -Première forme normale (1FN)-Un type-entité ou un type-association est en première forme normale si tous ses attributs sont élémentaires, c’est-à-dire non décomposables. Un attribut composite doit être décomposés en attributs élémentaires (comme l’attribut Adresse sur la figure 2.34) ou faire l’objet d’une entité supplémentaire (comme l’attribut Occupants sur la figure 2.34. L’élémentarité d’un attribut est toutefois fonction des choix de gestion. Par exemple, la propriété Adresse peut être considérée comme élémentaire si la gestion de ces adresses est globale. Par contre, s’il faut pouvoir considérer les codes postaux, les noms de rues, . . ., il convient d’éclater la propriété Adresse F??. 2.34 – Exemple de normalisation en première forme normale. en Adresse (au sens numéro d’appartement, numéro et nom de rue, . . .), Code postal et Ville. En cas de doute, il est préférable (car plus général) d’éclater une propriété que d’effectuer un regroupement. Définition 2.31 -Deuxième forme normale (2FN)-Un type-entité ou un type-association est en deuxième forme normale si, et seulement si, il est en première forme normale et si tout attribut n’appartenant pas à la clé dépend de la totalité de cette clé. Autrement dit, les attributs doivent dépendre de l’ensemble des attributs participant à la clé. Ainsi, si la clé est réduite à un seul attribut, ou si elle contient tous les attributs, le type-entité ou le type-association est, par définition, forcément en deuxième forme normale. La figure 2.35 montre un type-entité Article décrivant des produits provenant de différents fournisseurs. On suppose qu’un même fournisseur peut fournir plusieurs produits et qu’un même produit peut être fourni par différents fournisseurs. Dans ce cas, les attributs Produit ou Fournisseur ne peuvent constituer un identifiant du type-entité Article. Par contre, le couple Produit/Fournisseur constitue bien un identifiant du type-entité Article. Cependant, l’attribut Adresse fournisseur ne dépend maintenant que d’une partie de la clé (Fournisseur). Opter pour une nouvelle clé arbitraire réduite à un seul attribut N? article permet d’obtenir un type-entité Article en deuxième forme normale. On va voir dans ce qui suit que cette solution n’a fait que déplacer le problème. Troisième forme normale (3FN)Définition 2.32 -Troisième forme normale (3FN)-Un type-entité ou un type-association est en troisième forme normale si, et seulement si, il est en deuxième forme normale et si tous ses attributs dépendent directement de sa clé et pas d’autres attributs. Cette normalisation peut amener à désimbriquer des type-entités cachées comme le montre la figure 2.36. Un type-entité ou un type-association en deuxième forme normale avec au plus un attribut qui n’appartient pas à la clé est, par définition, forcément en troisième forme normale. Forme normale de Boyce-Codd (BCNF)F??. 2.37 – Exemple de normalisation en forme normale de Boyce-Codd. Définition 2.33 -Forme normale de Boyce-Codd (BCNF)-Un type-entité ou un type-association est en forme normale de Boyce-Codd si, et seulement si, il est en troisième forme normale et si aucun attribut faisant partie de la clé dépend d’un attribut ne faisant pas partie de la clé. Intéressons-nous, par exemple (cf. figure 2.37), à un type-entité Diplômé modélisant des personnes (Nom et Prénom) possédant un diplôme (Diplôme) d’une institution (Institution). On suppose qu’il n’y a pas d’homonyme, qu’une même personne ne possède pas deux fois le même diplôme mais qu’elle peut posséder plusieurs diplômes différents. Une institution ne délivre qu’un type de diplôme, mais un même diplôme peut être délivré par plusieurs institutions (par exemple, plusieurs écoles d’ingénieurs délivrent des diplômes d’ingénieur). Une clé possible pour le type-entité Diplômé est donc Nom,Prénom,Diplôme. Le type-entité obtenu est en troisième forme normale, mais une redondance subsiste car l’attribut Institution détermine l’attribut Diplôme. Le type-entité Diplômé n’est donc pas en forme normale de Boyce-Codd. Un modèle en forme normale de Boyce-Codd est considéré comme étant de qualité suffisante pour une implantation. Autres formes normalesIl existe d’autres formes normales. La quatrième et la cinquième forme normale sont présentées dans la section 3.2 dans le cadre du modèle relationnel. 2.6. ÉLABORATION D’UN MODÈLE ENTITÉS-ASSOCIATIONS Recueil des besoins – C’est une étape primordiale. Inventoriez l’ensemble des données à partir des documents de l’entreprise, d’un éventuel cahier des charges et plus généralement de tous les supports de l’information. N’hésitez pas à poser des questions. Tri de l’information – Faites le tri dans les données recueillies. Il faut faire attention, à ce niveau, aux problèmes de synonymie/polysémie. En effet, les attributs ne doivent pas être redondants. Par exemple, si dans le langage de l’entreprise on peut parler indifféremment de référence d’article ou de n? de produit pour désigner la même chose, cette caractéristique ne devra se concrétiser que par un unique attribut dans le modèle. Inversement, on peut parler d’adresse pour désigner l’adresse du fournisseur et l’adresse du client, le contexte permettant de lever l’ambiguïté. Par contre, dans le modèle, il faudra veiller à bien distinguer ces deux caractéristiques par deux attributs distincts. Un autre exemple est celui d’une entreprise de production fabricant des produits à destination d’une autre société du même groupe. Il se peut que dans ce cas, le prix de production (i.e. le coût de revient industriel) soit le même que prix de vente (aucune marge n’est réalisée). Même dans ce cas où les deux caractéristiques sont identiques pour chaque entité (prix de production égale prix de vente), il faut impérativement les scinder en deux attributs au niveau du type-entité Produit. Sinon, cette égalité factuelle deviendrait une contrainte imposée par le modèle, obligeant alors l’entreprise de production à revoir son système le jour où elle décidera de réaliser une marge (prix de production inférieure au prix de vente). Attention, un même concept du monde réel peut être représenté dans certains cas comme un attribut et dans d’autres cas comme un type-entité, selon qu’il a ou non une existence propre. Par exemple, la marque d’une automobile peut être vue comme un attribut du type-entité Véhicule de la base de données d’une préfecture mais aussi comme un type-entité Constructeur automobile dans la base de données du Ministère de l’Industrie. Lorsqu’on ne parvient pas à trouver d’identifiant pour un type-entité, il faut se demander s’il ne s’agit pas en fait d’un type-association. Si ce n’est pas le cas, un identifiant arbitraire numérique entier peut faire l’affaire. Identification des type-associations – Identifiez les type-associations reliant les type-entités du modèle. Le cas échéant, leur affecter les attributs correspondant. Il est parfois difficile de faire un choix entre un type-entité et un type-association. Par exemple, un mariage peut être considéré comme un type-association entre deux personnes ou comme un type-entité pour lequel on veut conserver un numéro, une date, un lieu, . . ., et que l’on souhaite manipuler en tant que tel. Étudiez également les cardinalités des type-associations retenus. Lorsque toutes les pattes d’un type-association portent la cardinalité 1, 1, il faut se demander si ce type-association et les typeentités liés ne décrivent pas en fait un seul type-entité (cf. règle 2.29). Vérification du modèle – Vérifiez que le modèle respecte bien les règles que nous avons énoncés et les définitions concernant la normalisation des type-entités et des type-associations. Le cas échéant, opérez les modifications nécessaires pour que le modèle soit bien formé. 2.6.2 Conseils diversConcernant le choix des nomsPour les type-entités, choisissez un nom commun décrivant le type-entité (ex : Étudiant, Enseignant, Matière). Certain préfèrent mettre le nom au pluriel (ex : Étudiants, Enseignants, Matières). Restez cependant cohérents, soit tous les noms de type-entité sont au pluriel, soit ils sont tous au singulier. Pour les type-association, choisissez un verbe à l’infinitif, éventuellement à la forme passive ou accompagné d’un adverbe (ex : Enseigner, Avoir lieu dans). Pour les attributs, utilisez un nom commun au singulier éventuellement accompagné du nom du type-entité ou du type-association dans lequel il se trouve (ex : nom de client, numéro d’article). Concernant le choix des identifiants des type-entitésÉvitez les identifiants composés de plusieurs attributs (comme, par exemple, un identifiant formé par les attributs nom et prénom d’un type-association Personne) car : – ils dégradent les performances du SGBD, – mais surtout l’unicité supposée par une telle démarche finit généralement, tôt ou tard, par êtredémentie ! Évitez les identifiants susceptibles de changer au cours du temps (comme la plaque d’immatriculation d’un véhicule). Évitez les identifiants du type chaîne de caractère. Bien distinguer les concepts de données et de traitementsLa modélisation conceptuelle de données exclut la représentation des traitements futurs sur ces données. Toutefois, elle nécessite la connaissance de ces traitements pour prévoir les données élémentaires indispensables à ceux-ci. En conséquence, il existe une confusion fréquente entre les concepts de données et de traitements. Par exemple, la facturation est un traitement qui nécessite de connaître toutes les caractéristiques d’une commande. Par contre, la facturation ne se traduit ni par un type-entité, ni par un type-association dans le schéma entités-associations. 2.7. TRAVAUXDIRIGÉS – MODÈLE ENTITÉS-ASSOCIATIONS {S3} 2.7 Travaux Dirigés – Modèle entités-associations (2epartie)2.7.1 Mais qui a fait cette modélisation? F??. 2.38 – Ce modèle entités-associations n’est pas en bonne forme ! Le modèle entités-associations de la figure 2.38 pose de nombreux problèmes. 1. Identifiez les erreurs de modélisation et les incohérences dont souffre ce modèle. Précisez à chaque fois la règle ou la définition enfreinte et réfléchissez à la correction à apporter au modèle. 2. Proposez un modèle corrigé bien formé. 2.7.2 Cas d’une bibliothèque (2e partie)Une petite bibliothèque souhaite informatiser la gestion de son fonds documentaire et de ses emprunts. Dans cette perspective, le bibliothécaire, qui n’est pas un informaticien, a rédigé le texte suivant : 15. Identifiez, dans le texte ci-dessus, les mots devant se concrétiser par des entités, des associationsou des attributs. 16. Proposez un modèle entités-associations bien formé permettant de modéliser la situation décrite ci-dessus. 2.7.3 Cas d’une entreprise de dépannageUne entreprise de dépannage possède plusieurs services spécialisés regroupant chacun un certain nombre d’employés. Les employés ne travaillent que dans un service, ils ont une fonction dans l’entreprise, éventuellement un supérieur et des subalternes. Leur salaire dépend de leur fonction et de leur ancienneté au sein de l’entreprise. En plus du petit outillage courant, l’entreprise de dépannage dispose de gros matériels demandant une qualification particulière aux salariés susceptibles de l’utiliser. Tous les salariés ne sont pas qualifiés pour l’utilisation de tout le matériel. Ce matériel est référencé au niveau de l’entreprise. Un matériel particulièrement complexe est référencé comme un tout et, le cas échéant, par composants, les composant étant eux-mêmes parfois décomposables. Une intervention de dépannage se fait toujours à la demande d’un client et sous la direction d’un responsable. Une intervention de dépannage se décompose en un certain nombre d’actes de dépannage faisant intervenir un employé. Chaque acte de dépannage comporte un coût. Lorsqu’un employé participe à un acte de dépannage, la date de début et de fin de la participation de l’employé est notée. Proposez un modèle entités-associations bien formé permettant de modéliser la situation décrite ci-dessus. Chapitre 3 Bases de données relationnelles3.1 Introduction au modèle relationnel3.1.1 PrésentationLe modèle relationnel a déjà été introduit dans la section 1.1.2. Le succès du modèle relationnel auprès des chercheurs, concepteurs et utilisateurs est dû à la puissance et à la simplicité de ses concepts. En outre, contrairement à certains autres modèles, il repose sur des bases théoriques solides, notamment la théorie des ensembles et la logique des prédicats du premier ordre. Les objectifs du modèle relationnel sont : – proposer des schémas de données faciles à utiliser ; – améliorer l’indépendance logique et physique (cf. section 1.2.2) ; – mettre à la disposition des utilisateurs des langages de haut niveau ; – optimiser les accès à la base de données ;– améliorer l’intégrité et la confidentialité ; – fournir une approche méthodologique dans la construction des schémas. De façon informelle, on peut définir le modèle relationnel de la manière suivante : – les données sont organisées sous forme de tables à deux dimensions, encore appelées relations,dont les lignes sont appelées n-uplet ou tuple en anglais ; – les données sont manipulées par des opérateurs de l’algèbre relationnelle ;– l’état cohérent de la base est défini par un ensemble de contraintes d’intégrité. Au modèle relationnel est associée a la théorie de la normalisation des relations qui permet de se débarrasser des incohérences au moment de la conception d’une base de données relationnelle. 3.1.2 Éléments du modèle relationnelDéfinition 3.1 -attribut-Un attribut est un identificateur (un nom) décrivant une information stockée dans une base. Exemples d’attribut : l’âge d’une personne, le nom d’une personne, le numéro de sécurité sociale. Définition 3.2 -Domaine-Le domaine d’un attribut est l’ensemble, fini ou infini, de ses valeurs possibles. 43 Définition 3.3 -relation-Une relation est un sous-ensemble du produit cartésien de n domaines d’attributs (n > 0). Une relation est représentée sous la forme d’un tableau à deux dimensions dans lequel les n attributs correspondent aux titres des n colonnes. Définition 3.4 -schéma de relation-Un schéma de relation précise le nom de la relation ainsi que la liste des attributs avec leurs domaines. Le tableau 3.1 montre un exemple de relation et précise son schéma.
T??. 3.1 – Exemple de relation de schéma Personne(N? sécu : Entier, Nom : Chaîne, Prénom : Chaîne) Définition 3.5 -degré-Le degré d’une relation est son nombre d’attributs. Définition 3.6 -occurrence ou n-uplets ou tuples-Une occurrence, ou n-uplets, ou tuples, est un élément de l’ensemble figuré par une relation. Autrement dit, une occurrence est une ligne du tableau qui représente la relation. Définition 3.7 -cardinalité-La cardinalité d’une relation est son nombre d’occurrences. Définition 3.8 -clé candidate-Une clé candidate d’une relation est un ensemble minimal des attributs de la relation dont les valeurs identifient à coup sûr une occurrence. La valeur d’une clé candidate est donc distincte pour toutes les tuples de la relation. La notion de clé candidate est essentielle dans le modèle relationnel. Règle 3.9Toute relation a au moins une clé candidate et peut en avoir plusieurs. Ainsi, il ne peut jamais y avoir deux tuples identiques au sein d’une relation. Les clés candidates d’une relation n’ont pas forcément le même nombre d’attributs. Une clé candidate peut être formée d’un attribut arbitraire, utilisé à cette seule fin. Définition 3.11 -clé étrangère-Une clé étrangère dans une relation est formée d’un ou plusieurs attributs qui constituent une clé primaire dans une autre relation. Définition 3.12 -schéma relationnel-Un schéma relationnel est constitué par l’ensemble des schémas de relation. Définition 3.13 -base de données relationnelle-Une base de données relationnelle est constituée par l’ensemble des n-uplets des différentes relations du schéma relationnel. 3.1. INTRODUCTION AU MODÈLE RELATIONNEL 3.1.3 Passage du modèle entités-associations au modèle relationnelRègles de passagePour traduire un schéma du modèle entités-associations vers le modèle relationnel, on peut appliquer les règles suivantes : 1. La normalisation devrait toujours être effectuée avant le passage au modèle relationnel (cf. section 2.5.4). Dans les faits, elle est parfois faite a posteriori (section 3.2), ce qui impose toujours une surcharge de travail importante. 2. Chaque type-entité donne naissance à une relation. Chaque attribut de ce type-entité devient unattribut de la relation. L’identifiant est conservé en tant que clé de la relation. 3. Chaque type-association dont aucune patte n’a pour cardinalité maximale 1 donne naissance à unerelation. Chaque attribut de ce type-association devient un attribut de la relation. L’identifiant, s’il est précisé, est conservé en tant que clé de la relation, sinon cette clé est formée par la concaténation des identifiants des type-entités qui interviennent dans le type-association. Cas particulier d’un type-assocuation du type 1 vers 1F??. 3.1 – Reprise de l’exemple de la figure 2.30 d’un type-association Etre où toutes les cardinalités maximales sont de 1. Dans l’exemple de la figure 3.1 toutes les cardinalités maximales du type-association Etre sont de 1. L’application des règles de passage du modèle entités-associations au modèle relationnel énoncées cidessus nous donnerait : – Citoyen(Num-Citoyen, Num-Candidat, Nom, Prénom, Adresse) – Candidat(Num-Candidat), Num-Citoyen, Parti) L’attribut Num-Candidat dans la relation Citoyen est une clé étrangère de la relation Candidat. L’attribut Num-Citoyen dans la relation Candidat est une clé étrangère de la relation Citoyen. Le type-association Etre étant du type 1 vers 1, il est entièrement matérialisé dans la relation Candidat par l’attribut Num-Citoyen. Il est donc inutile de la rematérialiser dans la relation Citoyen. L’attribut Num-Candidat dans la relation Citoyen doit donc être supprimé. D’autre part, dans la relation Candidat, l’attribut Num-Citoyen, en plus d’être une clé étrangère, constitue une clé candidate. On peut donc se passer de la clé Num-Candidat. Le schéma relationnel adéquat correspondant au modèle entités-associations de la figure 3.1 devient donc : – Citoyen(Num-Citoyen, Nom, Prénom, Adresse) – Candidat(Num-Citoyen, Parti) où Num-Citoyen, en plus d’être la clé de la relation Candidat, est une clé étrangère de la relation Citoyen. Cas particulier d’un type-entité sans attribut autre que sa cléLorsqu’un type-entité ne possède pas d’attribut en dehors de sa clé, il ne faut pas nécessairement en faire une relation. F??. 3.2 – Ici, le type-entité Date ne doit pas se matérialiser par une relation. – Exemplaire(Num-Exemplaire, date-achat) – Personne(Num-Personne, nom, prénom, adresse) – Emprunter(Num-Exemplaire, Num-Personne, Date, date-retour) Exemple completF??. 3.3 – Exemple très simplifié de modélisation entités-associations Comme exemple d’application, voici les relations déduites du schéma entités-associations de la figure 3.3 : – Patient(Num-Patient, Nom-Patient, Num-Mutuelle) – Mutuelle(Num-Mutuelle, Nom-Mutuelle) – Médecin(Num-Médecin, Nom-Médecin, Prénom-Médecin) – Affection(Num-Affection, Nom-Affection) – Hospitaliser(Num-Patient, Num-Affection, Num-Médecin, Date-Entrée,Chambre,Durée-Hospitalisation) 3.2 Normalisation3.2.1 IntroductionLes formes normales sont différents stades de qualité qui permettent d’éviter la redondance dans les bases de données relationnelles afin d’éviter ou de limiter : les pertes de données, les incohérences au sein des données, l’effondrement des performances des traitements. Le processus de normalisation consiste à remplacer une relation donnée par certaines projections afin que la jointure de ces projections permette de retrouver la relation initiale. En d’autres termes, le processus est réversible (i.e. sans perte d’information). Les notions de projection et de jointure seront respectivement définies dans les sections 3.4.3 et 3.4.8. Il existe une hiérarchie dans les règles de normalisation : une relation en 5e forme normale est forcément en 4e forme normale, une relation en 4e forme normale est forcément en forme normale de Boyce-Codd, etc. Il existe des méthodes systématiques pour normaliser une relation dans chacune des formes normales. Ces algorithmes de décomposition, associés à chacune des formes normales, sortent du cadre de ce cours et ne seront pas abordés. Contrairement à ce que nous avions fait dans la section 2.5.4 dans le cadre du modèle entitésassociations, nous abordons ici la normalisation en nous appuyant sur les notions de dépendance fonctionnelle, dépendance multivaluée et dépendance de jointure. Il est important de prendre conscience que la dépendance fonctionnelle, la dépendance multivaluée et la dépendance de jointure sont des notions sémantiques. Elles tirent leurs origines dans les contraintes du monde réel. Comme ces contraintes participent à la sémantique de la situation, elles doivent avoir une manifestation dans la base de données. Les dépendances doivent donc être spécifiées dans la définition de la base de données afin que le SGBD puisse les appliquer. Les concepts de normalisation fournissent en fait un moyen indirect de déclarer ces dépendances. Autrement dit, la normalisation d’une base de données est une manifestation observable des dépendances observées dans le monde réel. La dépendance fonctionnelle permet de définir les premières formes normales jusqu’à la forme normale de Boyce-Codd (1FN, 2FN, 3FN et BCNF). La dépendance multivaluée permet de définir la quatrième forme normale (4FN) et la dépendance de jointure la cinquième forme normale (5FN). 3.2.2 Dépendance fonctionnelle (DF)Définition 3.14 -dépendance fonctionnelle (DF)-Soit R(A1,A2, An) un schéma de relation, et X et Y des sous-ensembles de A1,A2, An. On dit que X détermine Y ou que Y dépend fonctionnellement de X si, et seulement si, des valeurs identiques de X impliquent des valeurs identiques de Y. On le note : X ? Y. Autrement dit, il existe une dépendance fonctionnelle entre un ensemble d’attributs X et un ensemble d’attributs Y, que l’on note X ? Y, si connaissant une occurrence de X on ne peut lui associer qu’une seule occurrence de Y. Définition 3.15 -dépendance fonctionnelle élémentaire-Une dépendance fonctionnelle élémentaire est une dépendance fonctionnelle de la forme X ? A, où A est un attribut unique n’appartenant pas à X et où il n’existe pas X0inclus au sens strict dans X (i.e. X0 ? X) tel que X0 ? A. Autrement dit, une dépendance fonctionnelle est élémentaire si la cible est un attribut unique et si la source ne comporte pas d’attributs superflus. La question sur l’élémentarité d’une dépendance fonctionnelle ne doit donc se poser que lorsque la partie gauche de la dépendance fonctionnelle comporte plusieurs attributs. Définition 3.16 -dépendance fonctionnelle directe-Une dépendance fonctionnelle X ? A est une dépendance fonctionnelle directe s’il n’existe aucun attribut B tel que l’on puisse avoir X ? B et B ? A. En d’autres termes, cela signifie que la dépendance entre X et A ne peut pas être obtenue par transitivité. 3.2.3 Première et deuxième forme normalePremière forme normaleDéfinition 3.17 -première forme normale (1FN)-Une relation est en première forme normale si, et seulement si, tout attribut contient une valeur atomique (non multiples, non composées). Par exemple, le pseudo schéma de relation Personne(num-personne, nom, prénom, rue-et-ville, prénoms- enfants) n’est pas en première forme normale. Il faut le décomposer en : – Personne(num-personne, nom, prénom, rue, ville) – Prénoms-enfants(num-personne, num-prénom) – Prénoms(num-prénom, prénom) Remarques sur la première forme normaleLa première forme normale impose que chaque ligne d’une relation ait une seule valeur pour chaque colonne (i.e. attribut), ce qui est justement la définition d’une table. Donc, une table est nécessairement en première forme normale au sens du modèle relationnel. Deuxième forme normaleDéfinition 3.18 -deuxième forme normale (2FN)-Une relation est en deuxième forme normale si, et seulement si, elle est en première forme normale et si toutes les dépendances fonctionnelles entre la clé et les autres attributs sont élémentaires. Autrement dit, une relation est en deuxième forme normale si, et seulement si, elle est en première forme normale et si tout attribut n’appartenant pas à la clé ne dépend pas que d’une partie de la clé. Une relation peut être en deuxième forme normale par rapport à une de ses clés candidates et ne pas l’être par rapport à une autre. Une relation avec une clé primaire réduite à un seul attribut est, par définition, forcément en deuxième forme normale. Soit, par exemple, le schéma de relation suivant : CommandeLivre(Num-Commande, Num-Client, Titre, Auteur, Quantité, Prix). Cette relation indique qu’un client (identifié par Num-Client) a passé une commande (identifiée par Num-Commande) de livre. Elle est bien en première forme normale. Par contre, les attributs Titre, Auteur, Quantité et Prix ne dépendent que de Num-Commande, et pas de Num-Client. Cette relation n’est donc pas en deuxième forme normale. Une solution simple pour la normaliser est de la remplacer par : CommandeLivre(Num-Commande, Num-Client, Titre, Auteur, Quantité, Prix). 3.2.4 Troisième forme normaleDéfinition 3.19 -troisième forme normale (3FN)-Une relation est en troisième forme normale si, et seulement si, elle est en deuxième forme normale et si toutes les dépendances fonctionnelles entre la clé et les autres attributs sont élémentaires et directes. Autrement dit, une relation est en troisième forme normale si, et seulement si, elle est en deuxième forme normale et si tout attribut n’appartenant pas à la clef ne dépend pas d’un attribut non-clé. Soit, par exemple, le schéma de relation suivant : CommandeLivre(Num-Commande, Num-Client, Titre, Auteur, Quantité, Prix). Comme nous l’avons vu plus haut, cette relation est bien en deuxième forme normale. Par contre, les attributs Auteur et Prix dépendent de l’attribut Titre. La relation n’est donc pas en troisième forme normale. Pour la normaliser, il faut la décomposer de la manière suivante : – CommandeLivre(Num-Commande, Num-Client, Num-Livre, Quantité) – Livre(Num-Livre, Titre, Auteur, Prix) Remarques importantesSoit les schémas de relation suivant : – Ville(Code-Postal, Nom, Population) – Personne(Nom, Prénom, Téléphone) Dans ces relations, on suppose les dépendances fonctionnelles directes suivante : – Code-Postal ? Nom – Code-Postal ? Population – Nom, Prénom ? Téléphone Il en va de même avec le schéma de relation Livre(Num-Livre, Titre, Auteur, Prix). Nous avons ici introduit un identifiant numérique arbitraire Num-Livre car l’identifiant naturel Titre, qui est une chaîne de caractères complexe, de taille non bornée et au format libre, ne constitue pas un bon identifiant dans la pratique. Pour justifier la troisième forme normale de cette relation, on peut imaginer que plusieurs livres peuvent porter le même titre. Il faut enfin noter que la normalisation n’est pas une fin en soit et qu’elle ne doit pas nécessairement être systématiquement appliquée (nous y reviendrons section 3.2.7). 3.2.5 Forme normale de BOYCE-CODDDéfinition 3.20 -forme normale de BOYCE-CODD (BCNF)-Une relation est en forme normale de BOYCE-CODD (BCNF) si, et seulement si, elle est en troisième forme normale et si les seules dépendances fonctionnelles élémentaires sont celles dans lesquelles une clé détermine un attribut. Cette forme normale permet de renforcer certaines lacunes de la troisième forme normale. Soit, par exemple, le schéma relationnel décrivant l’enseignement d’une matière donnée à une classe par un enseignant : – Matière(nom-matière) – Classe(num-classe) – Enseignant(nom-enseignant) – Enseignement(nom-enseignant, num-classe, nom-matière) Supposons, de plus, qu’une matière n’est enseignée qu’une seule fois dans une classe et que par un seul enseignant, et qu’un enseignant n’enseigne qu’une seule matière. Chacune des relations respecte bien la troisième forme normale. Cependant, dans la relation Enseignement, nous avons les dépendances fonctionnelles élémentaires suivantes : 1. nom-matière, num-classe ? nom-enseignant 2. nom-enseignant ? nom-matière Il existe donc des dépendances fonctionnelles élémentaires dont la source n’est pas la clé de la relation.
T??. 3.2 – Exemple de relation présentant une redondance due au non respect de la forme normale de BOYCE-CODD. Le non respect de la forme normale de BOYCE-CODD entraîne une redondance illustrée par la table 3.2 : pour chaque nom-enseignant identifiant un enseignant, il faut répéter le nom-matière identifiant la matière qu’il enseigne. Pour normaliser la relation Enseignement, il faut la décomposer pour aboutir au schéma relationnel suivant : – Matière(nom-matière) – Classe(num-classe) – Enseignant(nom-enseignant, nom-matière) – Enseigner(nom-enseignant, num-classe) Dans la pratique, la plupart des problèmes de conception peuvent être résolus en appliquant les concepts de troisième forme normale et de forme normale de BOYCE-CODD. Les quatrième et cinquième formes normales traitent encore d’autres cas de redondance, mais qui ne sont pas expliqués par des dépendances fonctionnelles. 3.2.6 Pour aller plus loin que le cours : quatrième et cinquième forme normaleDépendance multivaluée (DM)Définition 3.21 -dépendance multivaluée (DM)-Soit R(A1,A2, An) un schéma de relation contenant n propriétés, soit X, Y et Z des sous-ensembles de A1,A2, An et soit Xi, Yi et Zi des instances de ces sous-ensembles (i.e. une affectation de valeur à chacune des propriétés de ces sous-ensembles). Il existe une dépendance multivaluée (DM) entre les ensembles de propriétés X, Y lorsque : (X1,Y1,Z1) ? R et (X1,Y2,Z2) ? R ? (X1,Y1,Z2) ? R et (X1,Y2,Z1) ? R On la note XY, ce qui se lit X multidétermine Y. Remarque : XY ? XAi ? (X ? Y). – Employé(Nom-Employé) – Intervention(Type-Intervetion) – Constructeur(Marque) – Intervenir(Nom-Employé, Type-Intervetion, Marque) Supposons maintenant qu’un employé qui effectue un ensemble de types d’interventions pour un ensemble de marques de voiture, est capable d’effectuer chacun de ces types d’interventions sur chacune decesmarquesdevoitures.Danscecas,ilexistedesdépendancesmultivaluéesdanslarelationIntervenir: Nom-EmployéType-Intervetion et Nom-EmployéMarque. Quatrième forme normaleDéfinition 3.22 -quatrième forme normale (4FN)-Une relation est en quatrième forme normale (4FN) si, et seulement si, elle est en forme normale de BOYCE-CODD et si elle ne possède pas de dépendance multivaluée ou si, XY étant la dépendance multivaluée, il existe une propriété A telle que X ? A.
T??. 3.3 – Exemple de relation n’étant pas en quatrième forme normale. Pour normaliser la relation Intervenir, il faut la décomposer pour aboutir au schéma relationnel suivant : – Employé(Nom-Employé) – Intervention(Type-Intervetion) – Constructeur(Marque) – Etre-capable-de(Nom-Employé, Type-Intervetion) – Etre-capable-d’intervenir-sur(Nom-Employé, Marque) Dépendance de jointure (DJ)Jusqu’ici, nous avons pu résoudre une redondance dans une relation en la remplaçant par deux de ses projections. Il existe cependant des relations qui ne peuvent pas être décomposées sans perte d’information en deux projections, mais qui peuvent l’être en trois ou plus (ces cas sont assez rares en pratique). C’est ce que permet la normalisation en cinquième forme normale. Les dépendances de jointures font appel à des notions (projection et jointure) qui seront définies plus loin (cf. section 3.4). Définition 3.23 -dépendance de jointure (DJ)-Soient X1,X2, ,Xn des sous-ensembles d’un schéma de relation R. Il y a une dépendance de jointure, notée ?{X1,X2, ,Xn} dans la relation R, si : R = ?(X1)R ?(X2)R R Définition 3.24 -dépendance de jointure triviale-Une dépendance de jointure est triviale si une des parties, Xi, est l’ensemble de toutes les attributs de R. Cinquième forme normale (5FN)Définition 3.25 -cinquième forme normale (5FN)-Une relation R est en cinquième forme normale (5FN) si, pour toute dépendance de jointure non triviale ?{X1,X2, ,Xn} dans R, chacun des Xi contient une clé candidate de R. En d’autres termes, les seules décompositions qui préservent le contenu sont celles où chacune des tables de la décomposition contient une clé candidate de la table. Il est donc superflu de décomposer de ce point de vue.
T??. 3.4 – Exemple de relation n’étant pas en cinquième forme normale. Prenons, comme illustration, la relation Fournisseur (table 3.4) qui décrit les fournisseurs des organismes de la fonction publique. La fonction publique a des règles très particulières concernant les fournisseurs pour réduire le potentiel de conflit d’intérêt. Un fournisseur fournit un certain nombre d’articles (par exemple f1 fournit a1 et a2). Le même article peut être fourni par plusieurs fournisseurs (par exemple a1 est fourni par f1 et f2). Un fournisseur peut être attitré à plusieurs organismes (par exemple f1 est attitré à o1 et o2). Un organisme peut avoir plusieurs fournisseurs (par exemple o1 est servi par f1 et f2). Un organisme peut utiliser plusieurs articles (c’est-à-dire que o1 utilise a1 et a2) et un article peut être utilisé par plusieurs organismes (c’est-à-dire que a1 est utilisé par o1 et o2). La règle de la fonction publique est la suivante : – si un fournisseur fournit un certain article (comme f1 fournit a1), – le fournisseur est attitré à l’organisme (comme f1 est attitré à o1), et – l’organisme utilise un article (comme o1 utilise a1), – alors nécessairement, le fournisseur fournit l’article à l’organisme (f1 fournit a1 à o1). Le dernier fait est déductible des trois autres. Cette table contient de la redondance de données parce que certains faits sont répétés. Par exemple, le fait que f1 fournit a1 est répété à deux reprises, une fois parce qu’il fournit a1 à o1 et une autre fois parce qu’il fournit a1 à o2. Le fait que f1 est attitré à o1 est aussi répété à deux reprises. Il en est de même pour o1 qui utilise a1. La relation Fournisseur souffre d’une dépendance de jointure :
T??. 3.5 – Décomposition de la relation Fournisseur (table 3.4) pour obtenir des relations en cinquième forme normale. Il est important de se convaincre qu’aucune décomposition binaire de cette relation ne préserve le contenu de la relation initiale. Pour cela, il suffit de tenter de joindre deux tables parmi les trois précédentes. Aucune de ces jointures, ne produit la relation Fournisseur. 3.2.7 Remarques au sujet de la normalisationIl existe d’autres formes normales comme la forme normale domaine-clé (FNDC), la forme normale de restriction-union ou la sixième forme normale (6NF). Bien que l’objectif de la normalisation soit d’amener le concepteur à obtenir des relations en forme normale finale (i.e. en cinquième forme normale), cet objectif ne doit pas être interprété comme une loi. Il peut exister, très occasionnellement, de bonnes raisons pour passer outre les principes de la normalisation. De plus, un schéma en cinquième forme normale n’est pas nécessairement un schéma pleinement satisfaisant. D’autres facteurs sont à considérer dans le processus de conception d’une base de données et l’expérience et l’intuition jouent un rôle important. 3.3 Travaux Dirigés – Modèle relationnel3.3.1 Passage du modèle entités-associations au modèle relationnel1. Établissez un schéma relationnel à partir du petit diagramme entités-associations ci-dessus. 2. Quelles sont les clés primaires et les clés étrangères de chaque relation ? 4. Combien de schémas de relation doit contenir la traduction en schéma relationnel du petit diagramme entités associations ci-dessus ? 5. Établissez un schéma relationnel à partir du petit diagramme entités associations ci-dessus sanstenir compte de la spécificité de la cardinalité 1-1. 6. Proposez un petit exemple de base de données relationnelle correspondant au schéma relationnelétabli précédemment. 7. Expliquez pourquoi deux des relations doivent être fusionnées. 8. Donnez le schéma relationnel correct. 9. Quelles sont les clés primaires et les clés étrangères de chaque relation ? 10. A partir du MCD de la figure 3.4, établir le schéma relationnel. 3.3.2 NormalisationLa pièceLe schéma de relation Pièce permet de décrire des pièces employées dans un atelier de montage : Pièce (N?pièce, prix-unit, TVA, libellé, catégorie) Supposons les dépendances fonctionnelles suivantes : – N?pièce ? prix-unit – N?pièce ? TVA – N?pièce ? libellé – N?pièce ? catégorie – catégorie ? TVA 11. Proposez un identifiant pour ce schéma de relation. 12. Normalisez ce schéma de relation jusqu’à la forme normale de Boyce Codd. 3.3. TRAVAUXDIRIGÉS – MODÈLE RELATIONNEL {S4} F??. 3.4 – Exemple de MCD répondant à la question 2.7.1 du TD 2.7 La primeLe schéma de relation Prime donne la liste des primes attribuées au personnel technique en fonction des machines sur lesquelles il travaille : Prime (N?machine, atelier, N?technicien, montant-prime, nom-technicien) Supposons les dépendances fonctionnelles suivantes : – N?machine ? atelier – N?technicien ? nom-technicien – (N?machine, N?technicien) ? montant-prime 13. Proposez un identifiant pour ce schéma de relation. 14. Normalisez ce schéma de relation jusqu’à la forme normale de Boyce Codd. Enseignement (nom-étudiant, prénom-étudiant, matière, volume-horaire-matière, nom-enseignant, prénom-enseignant, salaire-enseignant) 15. Identifiez les dépendances fonctionnelles de ce schéma de relation. 16. Normalisez ce schéma de relation jusqu’en troisième forme normale. Supposons maintenant que les contraintes suivantes s’appliquent : – Chaque étudiant n’a qu’un enseignant par matière. – Un enseignant n’enseigne qu’une seule matière, mais une matière peut très bien être enseignée parplusieurs professeurs. 17. En tenant compte de ces nouvelles contraintes, identifiez les dépendances fonctionnelles du schémade relationnel en troisième forme normale que vous avez obtenu. 18. L’un des schémas de relation n’est pas en forme normale de Boyce Codd, lequel ? 19. A l’aide d’un exemple de relation, illustrez la redondance induite par ce schéma de relation. 20. Normalisez ce schéma de relation en forme normale de Boyce Codd. 21. Cette décomposition résout le problème de redondance, mais n’introduit-elle pas un autre problème ? 3.4 Algèbre relationnelle3.4.1 IntroductionL’algèbre relationnelle est un support mathématique cohérent sur lequel repose le modèle relationnel. L’objet de cette section est d’aborder l’algèbre relationnelle dans le but de décrire les opérations qu’il est possible d’appliquer sur des relations pour produire de nouvelles relations. L’approche suivie est donc plus opérationnelle que mathématique. On peut distinguer trois familles d’opérateurs relationnels : Les opérateurs unaires (Sélection, Projection) : ce sont les opérateurs les plus simples, ils permettent de produire une nouvelle table à partir d’une autre table. Les opérateurs binaires ou n-aires (Produit cartésien, Jointure, Division) : ils permettent de produire une nouvelle table à partir de deux ou plusieurs autres tables. Les notations ne sont pas standardisées en algèbre relationnelle. Ce cours utilise des notations courantes mais donc pas forcément universelles. 3.4.2 SélectionDéfinition 3.26 -sélection-La sélection (parfois appelée restriction) génère une relation regroupant exclusivement toutes les occurrences de la relation R qui satisfont l’expression logique E, on la note ?(E)R. Il s’agit d’une opération unaire essentielle dont la signature est : relation × expression logique ?? relation En d’autres termes, la sélection permet de choisir (i.e. sélectionner) des lignes dans le tableau. Le résultat de la sélection est donc une nouvelle relation qui a les mêmes attributs que R. Si R est vide (i.e. ne contient aucune occurrence), la relation qui résulte de la sélection est vide. Le tableau 3.7 montre un exemple de sélection.
T??. 3.6 – Exemple de relation Personne
T??. 3.7 – Exemple de sélection sur la relation Personne du tableau 3.6 : ?(Numero´ ?5)Personne 3.4.3 ProjectionDéfinition 3.27 -projection-La projection consiste à supprimer les attributs autres que A1, An d’une relation et à éliminer les n-uplets en double apparaissant dans la nouvelle relation; on la note ?(A1, An)R. Il s’agit d’une opération unaire essentielle dont la signature est : relation × liste d’attributs ?? relation Le tableau 3.8 montre un exemple de sélection.
T??. 3.8 – Exemple de projection sur la relation Personne du tableau 3.6 : ?NomPersonne 3.4.4 UnionDéfinition 3.28 -union-L’union est une opération portant sur deux relations R1et R2ayant le même schéma et construisant une troisième relation constituée des n-uplets appartenant à chacune des deux relations R1et R2sans doublon, on la note R1 ? R2. Il s’agit une opération binaire ensembliste commutative essentielle dont la signature est : relation × relation ?? relation Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs et si une même occurrence existe dans R1 et R2, elle n’apparaît qu’une seule fois dans le résultat de l’union. Le résultat de l’union est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 et R2 sont vides, la relation qui résulte de l’union est vide. Si R1 (respectivement R2) est vide, la relation qui résulte de l’union est identique à R2 (respectivement R1). Le tableau 3.9 montre un exemple d’union.
T??. 3.9 – Exemple d’union : R = R1?R2 3.4.5 IntersectionDéfinition 3.29 -intersection-L’intersection est une opération portant sur deux relations R1et R2ayant le même schéma et construisant une troisième relation dont les n-uplets sont constitués de ceux appartenant aux deux relations, on la note R1 ? R2. Il s’agit une opération binaire ensembliste commutative dont la signature est : Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs. Le résultat de l’intersection est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 ou R2 ou les deux sont vides, la relation qui résulte de l’intersection est vide. Le tableau 3.10 montre un exemple d’intersection.
T??. 3.10 – Exemple d’intersection : R = R1?R2 3.4.6 DifférenceDéfinition 3.30 -différence-La différence est une opération portant sur deux relations R1et R2ayant le même schéma et construisant une troisième relation dont les n-uplets sont constitués de ceux ne se trouvant que dans la relation R1; on la note R1 ? R2. Il s’agit une opération binaire ensembliste non commutative essentielle dont la signature est : relation × relation ?? relation Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs. Le résultat de la différence est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 est vide, la relation qui résulte de la différence est vide. Si R2 est vide, la relation qui résulte de la différence est identique à R1. Le tableau 3.11 montre un exemple de différence.
|