Download Équations différentielles and more Study Guides, Projects, Research Differential Equations in PDF only on Docsity!
CHAPITRE 4. LE MODÈLE RELATIONNEL 33
Chapitre 4
Le modèle relationnel
Sommaire
4.1 Définition d’un schéma relationnel........................... 33 4.2 Passage d’un schéma E/A à un schéma relationnel................... 35 4.2.1 Règles générales.................................. 36 4.2.2 Retour sur le choix des identifiants........................ 41 4.2.3 Dénormalisation du modèle logique........................ 41 4.3 Le langage de définition de données SQL2....................... 43 4.3.1 Types SQL.................................... 43 4.3.2 Création des tables................................ 44 4.3.3 Contraintes.................................... 45 4.3.4 Modification du schéma.............................. 48 4.4 Exercices......................................... 50
Un modèle de données définit un mode de représentation de l’information selon trois composantes :
- Des structures de données.
- Des contraintes qui permettent de spécifier les règles que doit respecter une base de données.
- Des opérations pour manipuler les données, en interrogation et en mise à jour.
Les deux premières composantes relèvent du Langage de Définition de Données (DDL) dans un SGBD. Le DDL est utilisé pour décrire le schéma d’une base de données. La troisième composante (opérations) est la base du Langage de Manipulation de Données (DML) dont le représentant le plus célèbre est SQL. Dans le contexte des bases de données, la principale qualité d’un modèle de données est d’être indépen- dant de la représentation physique. Cette indépendance permet de séparer totalement les tâches respectives des administrateurs de la base, chargés de l’optimisation de ses performances, et des développeurs d’ap- plication ou utilisateurs finaux qui n’ont pas à se soucier de la manière dont le système satisfait leurs demandes. Le modèle relationnel, venant après les modèles hiérarchique et réseau, offre une totale indépendance entre les représentations logique et physique. Ce chapitre présente la partie du modèle relative à la définition et à la création des tables, ce qui constitue l’essentiel du schéma.
4.1 Définition d’un schéma relationnel
Un des grands avantages du modèle relationnel est sa très grande simplicité. Il n’existe en effet qu’une seule structure, la relation. Une relation peut simplement être représentée sous forme de table , comme sur la figure 4.1. Une relation a donc un nom ( Film ) et se compose d’un ensemble de colonnes désignées par
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
4.1. DÉFINITION D’UN SCHÉMA RELATIONNEL 34
titre année genre Alien 1979 Science-Fiction Vertigo 1958 Suspense Volte-face 1997 Thriller Pulp Fiction 1995 Policier
FIG. 4.1 – Une relation
un nom d’attribut. Dans chaque colonne on trouve des valeurs d’un certain domaine (chaînes de caractères, nombres). Enfin on constate que chaque ligne (ou tuple ) correspond à une entité (ici des films). Un schéma relationnel est constitué d’un ensemble de schémas de relations qui décrivent, à l’aide des élements présentés informellement ci-dessus (domaines, attributs, noms de relation) le contenu d’une relation. Le schéma de la relation de la figure 4.1 est donc :
Film (titre: string, année: number, genre : string)
Il existe un langage de définition pour créer une relation dans un SGBDR (voir section 4.3), mais nous nous contenterons pour l’instant de la description ci-dessus. Voici maintenant quelques précisions sur la terminologie introduite ci-dessus.
Domaines
Un domaine de valeurs est un ensemble d’instances d’un type élémentaire. Exemple : les entiers, les réels, les chaînes de caractères, etc. La notion de ’type élémentaire’ s’oppose à celle de type structuré : il est interdit en relationnel de manipuler des valeurs instances de graphes, de listes, d’enregistrements, etc. En d’autres termes le système de types est figé et fourni par le système.
Attributs
Les attributs nomment les colonnes d’une relation. Il servent à la fois à indiquer le contenu de cette colonne, et à la référencer quand on effectue des opérations. Un attribut est toujours associé à un domaine. Le nom d’un attribut peut apparaître dans plusieurs schémas de relations.
Schéma de relation
Un schéma de relation est simplement un nom suivi de la liste des attributs, chaque attribut étant associé à son domaine. La syntaxe est donc :
où les
sont les noms d’attributs et les les domaines. L’arité d’une relation est le nombre de ses attributs. On peut trouver dans un schéma de relation plusieurs fois le même domaine, mais une seule fois un nom d’attribut. Le domaine peut être omis en phase de définition.
Instance d’une relation
Une instance d’une relation
, ou simplement relation se définit mathématiquement comme un sous- ensemble fini du produit cartésien des domaines des attributs de
. Rappelons que le produit cartésien entre des domaines est l’ensemble de tous les tuples
où
. Un des fondements du modèle relationnel est la théorie des ensembles et la notion de relation dans le modèle correspond strictement au concept mathématique dans cette théorie. Une relation se représente sous forme de table , et on emploie le plus souvent ces deux termes comme des synonymes.
4.2. PASSAGE D’UN SCHÉMA E/A À UN SCHÉMA RELATIONNEL 36
Pays
nom langue
code
Salle capacité climatisée
Artiste
nom
email
Internaute
Film
note
id^ Donne une note Réalise
motDePpasse annéeNaissance
annéeNaissance
Joue
no Horaire
heureDébut heureFin
id
Cinéma
adresse
résumé
genre
année
id
0..*
0..*
0..*
0..
nom
titre
prénom prénom
nom
rôle
FIG. 4.2 – Le schéma de la base de données Films
4.2.1 Règles générales
Nous allons considérer dans un premier temps que la clé est définie, pour chaque type d’entité E , par un identifiant abstrait, idE.
Règles de passage : entités
Rappel : le schéma d’une relation est constitué du nom de la relation, suivi de la liste des attributs. Alors, pour chaque entité du schéma E/A:
- On crée une relation de même nom que l’entité.
- Chaque propriété de l’entité, y compris l’identifiant, devient un attribut de la relation.
- Les attributs de l’identifiant constituent la clé de la relation.
Exemple 1 À partir du schéma E/A Officiel des spectacles, à l’exception des entités concernant les ciné- mas, les salles et les horaires, on obtient les relations suivantes :
- Film ( idFilm _, titre, année, genre, résumé)
- Artiste (_ idArtiste _, nom, prénom, annéeNaissance)
- Internaute (_ email _, nom, prénom, région)
- Pays (_ code , nom, langue)
On peut remarquer que l’on a perdu pour l’instant tout lien entre les relations.
CHAPITRE 4. LE MODÈLE RELATIONNEL 37
Règles de passage : associations de un à plusieurs
Soit une association de un à plusieurs 1 entre
et
. Le passage au modèle logique suit les règles suivantes :
- On crée les relations
et
correspondant respectivement aux entités
et
.
- L’identifiant de
devient un attribut de
.
L’idée est qu’une occurence de
référence l’occurence de qui lui est associée à l’aide d’une clé étrangère. Cette référence se fait de manière unique et suffisante à l’aide de l’identifiant.
Exemple 2 Voici le schéma obtenu pour représenter l’association entre les types d’entité Film , Artiste et Pays_. Les clés étrangères sont en italiques._
- Film ( idFilm _, titre, année, genre, résumé, idArtiste, codePays)
- Artiste (_ idArtiste _, nom, prénom, annéeNaissance)
- Pays (_ code , nom, langue)
Le rôle précis tenu par l’artiste dans l’association disparaît. L’artiste dans Film a un rôle de metteur en scène, mais il pourrait tout aussi bien s’agir du décorateur ou de l’accessoiriste. Rien n’empêche cependant de donner un nom plus explicite à l’attribut. Il n’est pas du tout obligatoire en fait que les attributs consti- tuant une clé étrangères aient le même nom que ceux de le clé primaire auxquels ils se réfèrent. Voici le schéma de la table Film , dans lequel la clé étrangère pour le metteur en scène est nommée idMES.
Film ( idFilm , titre, année, genre, résumé, idMES )
Les tables ci-dessous montrent un exemple de la représentation des associations entre Film et Artiste d’une part, Film et Pays d’autre part (on a omis le résumé du film). Noter que si on ne peut avoir qu’un artiste dont l’ id est 2 dans la table Artiste , en revanche rien n’empêche cet artiste 2 de figurer plusieurs fois dans la colonne idMES de la table Film. On a donc bien l’équivalent de l’association un à plusieurs élaborée dans le schéma E/A.
idFilm titre année genre idMES codePays 100 Alien 1979 Science Fiction 1 US 101 Vertigo 1958 Suspense 2 US 102 Psychose 1960 Suspense 2 US 103 Kagemusha 1980 Drame 3 JP 104 Volte-face 1997 Action 4 US 105 Van Gogh 1991 Drame 8 FR 106 Titanic 1997 Drame 6 US 107 Sacrifice 1986 Drame 7 FR La table Film
idArtiste nom prénom annéeNaiss 1 Scott Ridley 1943 2 Hitchcock Alfred 1899 3 Kurosawa Akira 1910 4 Woo John 1946 6 Cameron James 1954 7 Tarkovski Andrei 1932 8 Pialat Maurice 1925 La table Artiste
code nom langue US Etats Unis anglais FR France français JP Japon japonais La table Pays
- Il s’agit ici des cardinalités maximales qui déterminent pour l’essentiel le schéma relationnel obtenu.
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
CHAPITRE 4. LE MODÈLE RELATIONNEL 39
idArtiste nom prénom annéeNaiss 100 Eastwood Clint 1930 101 Hackman Gene 1930 102 Scott Tony 1930 103 Smith Will 1968 La table Artiste
idFilm idActeur rôle 20 100 William Munny 20 101 Little Bill 21 101 Bril 21 103 Robert Dean La table Rôle
On peut remarquer finalement que chaque partie de la clé de la table Rôle est elle-même une clé étran- gère qui fait référence à une ligne dans une autre table :
- l’attribut idFilm fait référence à une ligne de la table Film (un film) ;
- l’attribut idActeur fait référence à une ligne de la table Artiste (un acteur) ;
Le même principe de référencement et d’identification des tables s’applique à la table Notation dont un exemple est donné ci-dessous. Il faut bien noter que, par choix de conception, on a interdit qu’un internaute puisse noter plusieurs fois le même film, de même qu’un acteur ne peut pas jouer plusieurs fois dans un même film. Ces contraintes ne constituent pas des limitations, mais des décisions prises au moment de la conception sur ce qui est autorisé, et sur ce qui ne l’est pas.
idFilm titre année genre idMES codePays 100 Alien 1979 Science Fiction 1 USA 101 Vertigo 1958 Suspense 2 USA 102 Psychose 1960 Suspense 2 USA 103 Kagemusha 1980 Drame 3 JP 104 Volte-face 1997 Action 4 USA 105 Van Gogh 1991 Drame 8 FR 106 Titanic 1997 Drame 6 USA 107 Sacrifice 1986 Drame 7 FR La table Film
email nom prénom annéeNaiss [email protected] Doe John 1945 [email protected] Fogg Phileas 1965 La table Internaute
idFilm email note 100 [email protected] 4 104 [email protected] 3 100 [email protected] 5 107 [email protected] 2 106 [email protected] 5 La table Notation
Le processus de conception détaillé ci-dessus permet de décomposer toutes les informations d’une base de données en plusieurs tables dont chacune stocke un des ensembles d’entités gérés par l’application. Les liens sont définis par un mécanisme de référencement basé sur les clés primaires et les clés étrangères. Il est important de bien comprendre ce mécanisme pour maîtriser la construction de bases de données qui ne nécessiteront par de réorganisation – nécessairement douloureuse – par la suite.
Associations ternaires
Dans le cas d’associations impliquant plus de deux entités, on atteint une des limites du modèle En- tité/Association en matière de spécification de contraintes. En première approche, on peut appliquer la règle énoncée précédemment pour les associations binaires et la généraliser. On obtiendrait alors, pour l’association Séance :
- Cinéma ( nomCinéma , numéro, rue, ville)
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
4.2. PASSAGE D’UN SCHÉMA E/A À UN SCHÉMA RELATIONNEL 40
idCinéma no capacité Le Rex 1 200 Kino 1 130 Le Rex 2 180 La table Salle
idHoraire heureDébut heureFin 1 14 16 2 16 18 La table Horaire
idFilm titre année genre idMES codePays 100 Alien 1979 Science Fiction 1 USA 101 Vertigo 1958 Suspense 2 USA 102 Psychose 1960 Suspense 2 USA 103 Kagemusha 1980 Drame 3 JP 104 Volte-face 1997 Action 4 USA 105 Van Gogh 1991 Drame 8 FR 106 Titanic 1997 Drame 6 USA 107 Sacrifice 1986 Drame 7 FR La table Film
idFilm nomCinéma noSalle idHoraire tarif 103 Le Rex 2 1 23 103 Le Rex 2 2 56 100 Kino 1 2 45 102 Le Rex 2 1 50 La table Séance
FIG. 4.3 – Représentation d’une association ternaire : Séance
- Salle ( nomCinéma , no , capacité)
- Film ( idFilm , titre, année, genre, résumé, idMES , codePays )
- Horaire ( idHoraire , heureDébut, heureFin)
- Séance ( idFilm , nomCinéma , noSalle , idHoraire , tarif)
Donc, la relation Séance a pour clé la concaténation des identifiants de chacune des entités composantes, ce qui donne une clé d’une taille assez importante. On autorise alors une base comme celle de la figure 4.3. On ne peut pas trouver deux fois le même triplet constituant la clé.
Maintenant on s’aperçoit que la même salle présente deux films différents au même horaire. Si on souhaite éviter cette situation, la clé devient (nomCinéma, noSalle, idHoraire) , et on ne respecte plus la règle de passage du schéma E/A vers le schéma relationnel.
En d’autres termes, en cas d’association entre plus de 2 entités, la clé de la table représentant l’asso- ciation est un sous-ensemble de la concaténation des clés. Il faut se poser soigneusement la question de la (ou des) clé(s) au moment de la création de la table car elle(s) ne peu(ven)t plus être déduite(s) du schéma E/A. On parle parfois de clé candidate. Ces clés peuvent être spécifiées avec la clause UNIQUE du langage SQL2.
4.2. PASSAGE D’UN SCHÉMA E/A À UN SCHÉMA RELATIONNEL 42
Suppression de relations
On peut supprimer les entités qui portent peu d’attributs en les déplaçant vers une autre relation. Exemple : si le schéma de Cinema est simplement Cinéma ( nom , adresse), on peut supprimer la relation et placer l’adresse dans Salle.
Salle ( nomCinéma , noSalle , adresse) L’adresse d’un cinéma est dupliquée autant de fois qu’il y a de salles. Cette option implique une perte de place due à la redondance, un effort de saisie supplémentaire, et des risques d’incohérences. Elle ne peut être valable que tant qu’il n’y a pas d’attrbuts à ajouter pour qualifier un cinéma. Quand ce sera le cas, il faudra finalement se décider à (1) créer la relation Cinéma et (2) supprimer les attributs mal placés dans Salle. Autre exemple : il peut paraître inutile de créer Horaire pour gérer un couple d’heures. On peut alors placer l’horaire dans la relation Séance. Afin de permettre l’existence de plusieurs lignes avec le même film et la même salle, il faut introduire l’attribut heureDébut dans la clé. On obtient le schéma suivant.
Séance ( idFilm , nomCinema , noSalle , heureDébut , heureFin, tarif) Cette variante présente peu d’inconvénients. On peut tout juste citer le fait qu’il y a duplication de certains horaires, et que la gestion contraintes (heure début heure fin) doit être gérée pour plus de lignes. En fait la création d’un type d’entité Horaire ou Date , même si elle se justifie en théorie, présente plus d’inconvénients que d’avantages. En pratique, on place toujours un attribut horaire ou date dans le schéma de la relation.
Introduction de redondance
En principe il faut éviter les redondances dans une base de données. Donc une information est repré- sentée soit explicitement (elle figure une fois et une seule), soit implicitement (elle peut être déduite ou calculée). L’accès à une information peut cependant être long et/ou compliqué, et justifier l’introduction de re- dondances. Par exemple :
- à partir de la table Séance , il faut consulter Salle puis Cinéma pour connaître l’adresse du cinéma. ;
- il faut faire un calcul pour obtenir le nombre de salles d’un cinéma. En pratique on peut être amené à introduire (prudemment) des redondances. Les problèmes précédents pourraient ainsi se résoudre de la manière suivante :
- ajout de l’adresse du cinéma dans la table Séance.
Séance ( idFilm , nomCinema , noSalle , idHoraire , adresse)
Le risque peut être considéré comme mineur car l’adresse d’un cinéma change rarement.
- ajout du nombre de salles dans la relation Cinéma.
Cinéma ( nomCinema , numéro, rue, ville, nbSalles).
Il faut mettre à jour nbSalles quand une salle est ajoutée ou supprimée d’un cinéma. On peut considérer que cela arrive rarement, et que la redondance est donc sans grand danger.
- ajout du nombre de rôles tenus dans la table Artiste.
Artiste ( idArtiste , nom, prénom, annéeNaissance, nbRôles)
Il faut mettre à jour nbRôles quand un artiste obtient un nouveau rôle. Cela arrive fréquemment, et le risque induit par la redondance est alors important. L’introduction de redondances présente principalement le danger d’introduire des incohérences dans la base. On peut utiliser le mécanisme des triggers pour effectuer automatiquement la répercussion de la modification d’une donnée sur ses autres versions présentes dans la base.
CHAPITRE 4. LE MODÈLE RELATIONNEL 43
4.3 Le langage de définition de données SQL
Ce chapitre présente le langage de définition de données (LDD) qui permet de spécifier le schéma d’une base de données relationnelle. Ce langage correspond à une partie de la norme SQL ( structured query language ), l’autre partie étant relative à la manipulation des données (LMD). La définition d’un schéma logique comprend essentiellement deux parties : d’une part la description des tables et de leur contenu, d’autre part les contraintes qui portent sur les données de la base. La spé- cification des contraintes est souvent placée au second plan bien qu’elle soit en fait très importante : elle permet d’assurer, au niveau de la base des contrôles sur l’intégrité des donnés qui s’imposent à toutes les applications accédant à cette base. Un dernier aspect de la définition d’un schéma, rapidement survolé ici, est la description de la représentation physique. Il existe plusieurs versions de SQL. Le plus ancien standard date de 1989. Il a été révisé de manière importante en 1992 : la norme résultant de cette révision est SQL-92 ou SQL2. Une extension (SQL3) com- prenant l’introduction de caractéristiques orientées-objet est en cours de discussion depuis très longtemps, et certains systèmes ont déjà anticipé en proposant de nouvelles fonctionnalités. Le matériel présenté dans ce cours est essentiellement SQL2, sauf pour quelques nouveautés explicitement indiquées.
4.3.1 Types SQL
La norme SQL ANSI propose un ensemble de types qui sont donnés dans le tableau 4.1. Ce tableau présente également la taille, en octets, des instances de chaque type, cette taille n’étant ici qu’à titre indicatif car elle peut varier selon les systèmes.
Type Description Taille INTEGER Type des entiers relatifs 4 octets SMALLINT Idem. 2 octets BIGINT Idem. 8 octets FLOAT Flottants simple précision 4 octets DOUBLE PRECISION Flottants double précision 8 octets REAL Synonyme de FLOAT 4 octets NUMERIC ( M , D ) Numérique avec précision fixe. M octets DECIMAL ( M , D ) Idem. M octets CHAR( M ) Chaînes de longueur fixe M octets VARCHAR( M ) Chaînes de longueur variable L+1 avec L M BIT VARYING Chaînes d’octets Longueur de la chaîne. DATE Date (jour, mois, an) env. 4 octets TIME Horaire (heure, minutes, secondes) env. 4 octets DATETIME Date et heure 8 octets YEAR Année 2 octets
TAB. 4.1 – Types SQL ANSI
Types numériques exacts
La norme SQL ANSI distingue deux catégories d’attributs numériques : les numériques exacts , et les numériques flottants. Les types de la première catégorie (essentiellement INTEGER et DECIMAL) per- mettent de spécifier la précision souhaitée pour un attribut numérique, et donc de représenter une valeur exacte. Les numériques flottants correspondent aux types couramment utilisés en programmation (FLOAT, DOUBLE) et ne représentent une valeur qu’avec une précision limitée. Le type INTEGER permet de stocker des entiers, sur 4 octets en général, mais la taille du stockage n’est pas spécifiée par la norme. Il existe deux variantes du type INTEGER : SMALLINT et BIGINT. Ces types différent par la taille utilisée pour le stockage : voir le tableau 4.1.
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
CHAPITRE 4. LE MODÈLE RELATIONNEL 45
Il s’agit d’une différence importante entre la pratique et la théorie : on admet que certains attributs peuvent ne pas avoir de valeur, ce qui est très différent d’une chaîne vide ou de 0. Quand on parle de valeur NULL en SQL2, il s’agit en fait d’une absence de valeur. En conséquence :
- on ne peut pas faire d’opération incluant un NULL ;
- on ne peut pas faire de comparaison avec un NULL.
L’option NOT NULL oblige à toujours indiquer une valeur. L’option suivante permet ainsi de garantir que tout internaute a un mot de passe.
motDePasse VARCHAR(60) NOT NULL
Le SGBD rejettera alors toute tentative d’insérer une ligne dans Internaute sans donner de mot de passe. Si les valeurs à NULL sont autorisées, il faudra en tenir compte quand on interroge la base. Cela peut compliquer les choses, voire donner des résultats surprenants : il est préférable de forcer les attributs important à avoir une valeur. Une autre manière de forcer un attribut à toujours prendre une valeur est de spécifier une valeur par défaut avec l’option DEFAULT.
CREATE TABLE Cinéma (nom VARCHAR (50) NOT NULL, adresse VARCHAR (50) DEFAULT ’Inconnue’)
Quand on insérera une ligne dans la table Cinéma sans indiquer d’adresse, le système affectera au- tomatiquement la valeur ’Inconnu’ à cet attribut. En général on utilise comme valeur par défaut une constante, sauf pour quelques variables fournies par le système (par exemple SYSDATE qui peut indiquer la date du jour).
4.3.3 Contraintes
La création d’une table telle qu’on l’a vue précédemment est extrêmement sommaire car elle n’indique que le contenu de la table sans spécifier les contraintes que doit respecter ce contenu. Or il y a toujours des contraintes et il est indispensable de les inclure dans le schéma pour assurer (dans la mesure du possible) l’intégrité de la base. Voici les règles (ou contraintes d’intégrité ) que l’on peut demander au système de garantir :
- Un attribut doit toujours avoir une valeur. C’est la contrainte NOT NULL vue précédemment.
- Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation.
- Un attribut dans une table est liée à la clé primaire d’une autre table ( intégrité référentielle).
- La valeur d’un attribut doit être unique au sein de la relation.
- Enfin toute règle s’appliquant à la valeur d’un attribut (min et max par exemple).
Les contraintes sur les clés doivent être systématiquement spécifiées. La dernière (clause CHECK) s’ap- puie en grande partie sur la connaissance du langage d’interrogation de SQL et sera vue ultérieurement.
Clés d’une table
Une clé est un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique un tuple d’une relation. Il peut y avoir plusieurs clés mais l’une d’entre elles doit être choisie comme clé primaire. Ce choix est important : la clé primaire est la clé utilisée pour référencer une ligne et une seule à partir d’autres tables. Il est donc assez délicat de la remettre en cause après coup. En revanche les clés secondaires peuvent être créées ou supprimées beaucoup plus facilement. La clé primaire est spécifiée avec l’option PRIMARY KEY.
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
4.3. LE LANGAGE DE DÉFINITION DE DONNÉES SQL2 46
nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss INTEGER, PRIMARY KEY (email)) Il devrait toujours y avoir une PRIMARY KEY dans une table pour ne pas risquer d’insérer involontai- rement deux lignes strictement identiques. Une clé peut être constituée de plusieurs attributs :
CREATE TABLE Notation (idFilm INTEGER NOT NULL, email VARCHAR (50) NOT NULL, note INTEGER DEFAULT 0, PRIMARY KEY (titre, email))
Tous les attributs figurant dans une clé doivent être déclarés NOT NULL. Cela n’a pas vraiment de sens en effet d’identifier des lignes par des valeurs absentes. On peut également spécifier que la valeur d’un attribut est unique pour l’ensemble de la colonne. Cela permet d’indiquer des clés secondaires. On peut par exemple indiquer que deux artistes ne peuvent avoir les mêmes nom et prénom avec l’option UNIQUE.
CREATE TABLE Artiste(id INTEGER NOT NULL, nom VARCHAR (30) NOT NULL, prenom VARCHAR (30) NOT NULL, anneeNaiss INTEGER, PRIMARY KEY (id), UNIQUE (nom, prenom)); Il est facile de supprimer cette contrainte de clé secondaire par la suite. Ce serait beaucoup plus difficile si on avait utilisé la paire (nom, prenom) comme clé primaire puisqu’elle serait alors utilisée pour référencer un artiste dans d’autres tables. Voici un autre exemple d’utilisation d’une clé secondaire : on indique ci-dessous qu’on ne peut pas trouver deux cinémas à la même adresse. Ce deuxième exemple montre que l’on peut placer une contrainte comme UNIQUE sur la ligne de l’attribut auquel elle se rapporte. Ce n’est bien entendu possible que quand cette contrainte ne concerne qu’un seul attribut.
CREATE TABLE Cinema (nom VARCHAR (30) NOT NULL, adresse VARCHAR(50) UNIQUE, PRIMARY KEY (nomCinema)) La clause UNIQUE ne s’applique pas aux valeurs NULL : il peut y avoir plusieurs cinémas d’adresse inconnue. En revanche le nom du cinéma est obligatoire (clause NOT NULL) et il est unique (clause PRIMARY KEY).
Clés étrangères
La norme SQL ANSI permet d’indiquer quelles sont les clés étrangères dans une table, autrement dit, quels sont les attributs qui font référence à une ligne dans une autre table. On peut spécifier les clés étrangères avec l’option FOREIGN KEY.
CREATE TABLE Film (idFilm INTEGER NOT NULL, titre VARCHAR (50) NOT NULL, annee INTEGER NOT NULL, idMES INTEGER, codePays INTEGER, PRIMARY KEY (idFilm), FOREIGN KEY (idMES) REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
4.3. LE LANGAGE DE DÉFINITION DE DONNÉES SQL2 48
Énumération des valeurs possibles avec CHECK
La norme SQL ANSI comprend une option CHECK ( condition ) pour exprimer des contraintes por- tant soit sur un attribut, soit sur une ligne. La condition elle-même peut être toute expression suivant la clause WHERE dans une requête SQL. Les contraintes les plus courantes sont celles consistant à restreindre un attribut à un ensemble de valeurs, comme expliqué ci-dessous. On peut trouver des contraintes arbitrai- rement complexes, faisant référence à d’autres relations. Nous reviendrons sur cet aspect après avoir étudié le langage d’interrogation SQL. Voici un exemple simple qui restreint les valeurs possibles des attributs annee et genre dans la table Film.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL, annee INTEGER CHECK (annee BETWEEN 1890 AND 2000) NOT NULL, genre VARCHAR (10) CHECK (genre IN (’Histoire’,’Western’,’Drame’)), idMES INTEGER, codePays INTEGER, PRIMARY KEY (titre), FOREIGN KEY (idMES) REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
Au moment d’une insertion dans la table Film , ou d’une modification de l’attribut annee ou genre=, le SGBD vérifie que la valeur insérée dans genre appartient à l’ensemble énuméré défini par la clause CHECK. Une autre manière de définir, dans la base, l’ensemble des valeurs autorisées pour un attribut – en d’autres termes, une codification imposée – consiste à placer ces valeurs dans une table et la lier à l’attribut par une contrainte de clé étrangère. C’est ce que nous pouvons faire par exemple pour la table Pays.
CREATE TABLE Pays (code VARCHAR (4) DEFAULT 0 NOT NULL, nom VARCHAR (30) NOT NULL, langue VARCHAR (30) NOT NULL, PRIMARY KEY (code))
INSERT INTO Pays VALUES (0, ’Inconnu’, ’Inconnue’); INSERT INTO Pays VALUES (1, ’France’, ’Français’); INSERT INTO Pays VALUES (2, ’USA’, ’Anglais’); INSERT INTO Pays VALUES (3, ’Allemagne’, ’Allemand’); INSERT INTO Pays VALUES (4, ’Angleterre’, ’Anglais’); ...
Si on ne fait pas de vérification automatique, soit avec CHECK, soit avec la commande FOREIGN KEY, il faut faire cette vérification dans l’application, ce qui est plus lourd à gérer.
4.3.4 Modification du schéma
La création d’un schéma n’est qu’une première étape dans la vie d’une base de données. On est toujours amené par la suite à créer de nouvelles tables, à ajouter des attributs ou à en modifier la définition. La forme générale de la commande permettant de modifier une table est :
ALTER TABLE nomTable ACTION description
où ACTION peut être principalement ADD, MODIFY, DROP ou RENAME, et description est la com- mande de modification associée à ACTION. La modification d’une table peut poser des problèmes si elle est incompatible avec le contenu existant. Par exemple passer un attribut à NOT NULL implique que cet attribut a déjà des valeurs pour toutes les lignes de la table.
CHAPITRE 4. LE MODÈLE RELATIONNEL 49
Modification des attributs
Voici quelques exemples d’ajout et de modification d’attributs. On peut ajouter un attribut region à la table Internaute avec la commande :
ALTER TABLE Internaute ADD region VARCHAR(10); S’il existe déjà des données dans la table, la valeur sera à NULL ou à la valeur par défaut. La taille de region étant certainement insuffisante, on peut l’agrandir avec MODIFY, et la déclarer NOT NULL par la même occasion :
ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL; Il est également possible de diminuer la taille d’une colonne, avec le risque d’une perte d’information pour les données existantes. On peut même changer son type, pour passer par exemple de VARCHAR à INTEGER, avec un résultat imprévisible.
L’option ALTER TABLE permet d’ajouter une valeur par défaut. ALTER TABLE Internaute ALTER region SET DEFAULT ’PACA’; Enfin on peut détruire un attribut avec DROP. ALTER TABLE Internaute DROP region;
Création d’index
Pour compléter le schéma d’une table, on peut définir des index. Un index offre un chemin d’accès aux lignes d’une table qui est considérablement plus rapide que le balayage de cette table – du moins quand le nombre de lignes est très élevé. Les SGBDL créent systématiquement un index sur la clé primaire de chaque table. Il y a deux raisons à cela ;
- l’index permet de vérifier rapidement, au moment d’une insertion, que la clé n’existe pas déjà ;
- beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables ( jointures ), se basent sur les clés des tables pour reconstruire les liens. L’index peut alors être utilisé pour améliorer les temps de réponse. Un index est également créé pour chaque clause UNIQUE utilisée dans la création de la table. On peut de plus créer d’autres index, sur un ou plusieurs attributs, si l’application utilise des critères de recherche autres que les clés primaire ou secondaires. La commande pour créer un index est la suivante : CREATE [UNIQUE] INDEX nomIndex ON nomTable (attribut1 [, ...]) La clause UNIQUE indique qu’on ne peut pas trouver deux fois la même clé. La commande ci-dessous crée un index de nom idxNom sur les attributs nom et prenom de la table Artiste. Cet index a donc une fonction équivalente à la clause UNIQUE déjà utilisée dans la création de la table.
CREATE UNIQUE INDEX idxNom ON Artiste (nom, prenom);
On peut créer un index, cette fois non unique, sur l’attribut genre de la table Film.
CREATE INDEX idxGenre ON Film (genre); Cet index permettra d’exécuter très rapidement des requêtes SQL ayant comme critère de recherche le genre d’un film.
SELECT * FROM Film WHERE genre = ’Western’ Cela dit il ne faut pas créer des index à tort et à travers, car ils ont un impact négatif sur les commandes d’insertion et de destruction. À chaque fois, il faut en effet mettre à jour tous les index portant sur la table, ce qui représente un coût certain.
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
CHAPITRE 4. LE MODÈLE RELATIONNEL 51
Exercice 7 On trouve dans un SGBD relationnel les relations ci-dessous. Les clés primaires sont en gras, les clés étrangères ne sont pas signalées.
- Immeuble ( nom _, adresse, nbEtages, annéeConstruction, nomGérant)
- Appart (_ nomImm, noApp _, type, superficie, étage)
- Personne (_ nom _, prenom, age, codeProfession)
- Occupant (_ nomImm, noApp, nomOccupant _, annéeArrivée)
- Propriété (_ nomImm, nomPropriétaire _, quotePart)
- TypeAppart (_ code _, libellé)
- Profession (_ code , libellé)
Identifier les clés étrangères dans chaque relation, et reconstruire le schéma E/A.
Philippe Rigaux ([email protected]), Cours de bases de données, 2003
