Chapitre 7 Schémas relationnels

CHAPITRE 7. SCHÉMAS RELATIONNELS 79
Chapitre 7
Schémas relationnels
Sommaire
7.1 Schémas.......................................... 80
7.1.1 Définition d‘un schéma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
7.1.2 Utilisateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
7.2 Contraintes et assertions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
7.3 Vues............................................ 83
7.3.1 Création et interrogation d’une vue . . . . . . . . . . . . . . . . . . . . . . . . 83
7.3.2 Mise à jour d’une vue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
7.4 Triggers .......................................... 85
7.4.1 Principes des triggers ............................... 85
7.4.2 Syntaxe ...................................... 86
7.5 Exercices ......................................... 87
Ce chapitre présente l’environnementd‘un utilisateur travaillant avec un SGBD relationnel. On se place
donc dans la situation d’un informaticien connecté à une machine sur laquelle tourne un SGBD gérant une
base de données.
Le principal élément d’un tel environnement est le schéma consistant principalement en un ensemble
de tables relatives à une même application. Il peut y avoir plusieurs schémas dans une même base: par
exemple on peut très bien faire coexister le schéma Officiel des spectacles et le schéma Agence de
voyage . En toute rigueur il faudrait donc distinguer l’instance de schéma de la base de données qui
est un sur-ensemble. Comme on se situe en général dans un et un seul schéma, on peut utiliser les deux
termes de manière équivalente.
Le schéma est le principal concept étudié dans ce chapitre. Outre les tables, un schéma peut comprendre
des vues, des contraintes de différents types, des triggers ( reflexes ) qui sont des procédures déclenchées
par certains évènements, enfin des spécifications de stockage et/ou d’organisation physique (comme les
index) qui ne sont pas traitées ici.
Dans tout ce chapitre, on basera les exemples sur le schéma Officiel qui est rappelé ci-dessous.
– Cinéma (nomCinéma, numéro, rue, ville)
– Salle (nomCinéma, no, capacité, climatisée)
– Horaire (idHoraire, heureDébut, heureFin)
– Séance (idFilm,nomCinéma,noSalle,idHoraire, tarif)
– Film (idFilm, titre, année, genre, résumé, idMES)
– Artiste (id, nom, prénom, annéeNaissance)
– Rôle (idActeur,idFilm, nomRôle)
Philippe Rigaux ([email protected]), Cours de bases de données, 2003

7.1. SCHÉMAS 80
7.1 Schémas
Cette section décrit la notion de schéma au sens SQL2, ainsi que le système de droits d’accès à un
schéma destiné à garantir la sécurité de la base.
7.1.1 Définition d‘un schéma
Un schéma est l’ensemble des déclarations décrivant une base de données au niveau logique: tables,
vues, domaines, etc. On crée un schéma en lui donnant un nom puis en donnant les listes des commandes
(de type CREATE en général) créant les éléments du schéma. Voici par exemple le squelette de la com-
mande de création du schéma Agence de voyage .
CREATE SCHEMA agence_de_voyage
CREATE TABLE Station (...
CREATE TABLE Client (...
...
CREATE VIEW ....
...
CREATE ASSERTION ...
...
CREATE TRIGGER ...
...
Deux schémas différents sont indépendants: on peut créer deux tables ayant le même nom. Bien en-
tendu on peut modifier un schéma en ajoutant ou en supprimant des éléments. La modification a lieu dans le
schéma courant que l’on peut modifier avec la commande SET SCHEMA. Par exemple, avant de modifier
la table FILM, on exécute:
SET SCHEMA officiel_des_spectacles
Quand on souhaite accéderà une table qui n’est pas dans le schéma courant (par exempledans un ordre
SQL), il faut préfixer le nom de la table par le nom du schéma.
SELECT * FROM officiel_des_spectacles.film
La norme SQL2 définit deux niveaux supérieurs au schéma: les catalogues et les groupes (cluster). Ils
correspondent respectivement aux niveaux d’unicité de nom de schéma, et d’accessibilité à une table dans
une requête. Donc un utilisateur ne peut spécifier dans sa requête que les tables du cluster courant.
7.1.2 Utilisateurs
L’accès à une base de données est restreint, pour des raisons évidentes de sécurité, à des utilisateurs
connus du SGBD et identifiés par un nom et un mot de passe. Chaque utilisateur se voit attribuer certains
droits sur les schémas et les tables de chaque schéma.
La connexion se fait soit dans le cadre d’un programme, soit interactivement par une commande du
type:
CONNECT utilisateur
Suivie de l’entrée du mot de passe demandée par le système. Une session est alors ouverte pour laquelle
le SGBD connaît l’ID de l’utilisateur courant. Les droits de cet utilisateur sont alors les suivants:
1. Tous les droits surles éléments du schéma commeles tables ou les vues desschémas que l’utilisateur
a lui-même créé. Ces droits concernent aussi bien la manipulation des données que la modification
ou la destruction des éléments du schéma.

CHAPITRE 7. SCHÉMAS RELATIONNELS 81
2. Les droits sur les éléments d’un schéma dont on n’est pas propriétaire sont accordés par le proprié-
taire du schéma. Par défaut, on n’a aucun droit.
En tant que propriétaire d’un schéma, on peut donc accorder des droits à d’autres utilisateurs sur ce
schéma ou sur des éléments de ce schéma. SQL2 définit 6 types de droits. Les quatre premiers portent sur
le contenu d’une table, et se comprennentaisément.
1. Insertion (INSERT).
2. Modification (UPDATE).
3. Recherche (SELECT).
4. Destruction (DELETE).
Il existe deux autres droits:
1. REFERENCES donne le droit à un utilisateur non propriétaire du schéma de faire référence à une
table dans une contrainte d’intégrité.
2. USAGE permet à un utilisateur non propriétaire du schéma d’utiliser une définition (autre qu’une
table ou une assertion) du schéma.
Les droits sont accordés par la commande GRANT dont voici la syntaxe:
GRANT <privilege>
ON <element du schema>
TO <utilisateur>
[WITH GRANT OPTION]
Bien entendu, pour accorder un privilège, il faut en avoir le droit, soit parce que l’on est propriétaire de
l’élément du schéma, soit parce que le droit a été accordé par la commande WITH GRANT OPTION.
Voici la commande permettant à Marc de consuter les films.
GRANT SELECT ON Film TO Marc;
On peut désigner tous les utilisateurs avec le mot-clé PUBLIC, et tous les privilèges avec l’expression
ALL PRIVILEGES.
GRANT ALL PRIVILEGES ON Film TO PUBLIC
On supprime un droit avec la commande REVOKE dont la syntaxe est semblable à celle de GRANT.
REVOKE SELECT ON Film FROM Marc
7.2 Contraintes et assertions
Nous revenons maintenant de manière plus approfondie sur les contraintes portant sur le contenu des
tables. Il est très important de spécifier les contraintes dans le schéma, afin que le SGBD puisse les prendre
en charge. Cela évite d’effectuer des contrôles de manière répétitive (et souvent lacunaire) dans les appli-
cations qui accèdent à la base.
Les contraintes (essentielles) portant sur les clés simples, primaires et étrangères ont déjà été vues dans
le chapitre 4. Les contraintes décrites ici sont moins spécifiques et portent sur les valeurs des attributs ou
plus globalement sur le contenu d’une ou plusieurs relations.
La commande
CHECK (condition)
Philippe Rigaux ([email protected]), Cours de bases de données, 2003

7.2. CONTRAINTES ET ASSERTIONS 82
permet d’exprimer toute contrainte portant soit sur un attribut, soit sur un tuple. La condition elle-même
peut être toute expression suivant la clause WHERE dans une requête SQL. Les contraintes les plus cou-
rantes sont celles consistant à restreindre un attribut à un ensemble de valeurs, mais on peut trouver des
contraintes arbitrairement complexes, faisant référence à d’autres relations. Dans ce cas, on doit obligatoi-
rement utiliser des sous-requêtes.
Exemple simple: on restreint les valeurs possibles des attributs capacité et climatisée dans la
table Salle.
CREATE TABLE Salle
(nomCinema VARCHAR (30) NOT NULLL,
no INTEGER,
capacite INTEGER CHECK (capacite 300),
climatisee CHAR(1) CHECK (climatisee IN (’O’,’N’)),
PRIMARY KEY (nomCinema, no),
FOREIGN KEY nomCinema REFERENCES Cinema)
Il s’agit de contraintes portant sur des valeurs d’attributs. A chaque insertion d’un tuple, ou mise-à-jour
de ce tuple affectant un des attributs contraints, le contrôle sera effectué. La règle est que la condition ne
doit pas s’évaluer à FALSE (donc la valeur UNKNOWN est acceptée).
Voici un autre exemple illustrant l’utilisation d’une sous-requête: on souhaite remplacer la contrainte
FOREIGN KEY par une clause CHECK.
CREATE TABLE Salle
(nomCinema VARCHAR (30) NOT NULLL,
no INTEGER,
capacite INTEGER CHECK (capacite 300),
climatisee CHAR(1) CHECK (climatisee IN (’O’,’N’)),
PRIMARY KEY (nomCinema, no),
CHECK (nomCinéma IN (SELECT nom FROM Cinema)))
Il s’agit d’une illustration simple d’une clause CHECK avec sous-requête, mais elle est incorrecte pour
garantir l’intégrité référentielle. Pourquoi? (penser aux événements qui déclenchent respectivement les
contrôles des clauses CHECK et FOREIGN KEY).
Au lieu d’associer une contrainte à un attribut particulier, on peut la définir globalement. Dans ce cas
la contrainte peut faire référence à n’importe quel attribut de la table et est testée tuple à tuple.
Exemple: toute salle de plus de 300 places doit être climatisée:
CREATE TABLE Salle
(nomCinema VARCHAR (30) NOT NULLL,
no INTEGER,
capacite INTEGER,
climatisee CHAR(1),
PRIMARY KEY (nomCinema, no),
FOREIGN KEY nomCinema REFERENCES Cinema,
CHECK (capacité 300 OR Climatisé = ’O’))
L’utilisation des sous-requêtes n’est pas recommandée, à cause du problème souligné précédemment:
la contrainte peut être satisfaite au moment de l’insertion du tuple, et ne plus l’être après.
Exemple: la grande salle du Rex doit rester la plus grande!
CREATE TABLE Salle
(nomCinema VARCHAR (30) NOT NULLL,
no INTEGER,

CHAPITRE 7. SCHÉMAS RELATIONNELS 83
capacite INTEGER,
climatisee CHAR(1),
PRIMARY KEY (nomCinema, no),
FOREIGN KEY nomCinema REFERENCES Cinema,
CHECK (capacité (SELECT Max(capacite) FROM Salle
WHERE nomCinema = ’Rex’)))
Problème: si on diminue la taille de la salle du Rex, la contrainte peut ne plus être respectée. Il est donc
préférable de ne pas utiliser la clause CHECK pour des contraintes impliquant d’autres tables.
Il est possible, et recommandé, de donner un nom aux contraintes avec la clause CONSTRAINT.
CONSTRAINT clim CHECK (capacite < 300 OR climatisee = ’O’)
Cela facilite la compréhension des messages, et permet de modifier ou de détruire une contrainte:
ALTER TABLE Salle DROP CONSTRAINT clim
7.3 Vues
Cette section est consacrée à l’une des fonctionnalités les plus remarquables des SGBD relationnels:
les vues.
Comme nous l’avons vu dans la partie consacrée à SQL, une requête produit toujours une relation. Cela
suggère la possibilité d’ajouter au schéma des tables ’virtuelles’ qui ne sont rien d’autres que le résultat
de requêtes stockées. De telles tables sont nommées des vues dans la terminologie relationnelle. On peut
interroger des vues comme des tables stockées.
Une vue n’induit aucun stockage puisqu’elle n’existe pas physiquement, et permet d’obtenir une repré-
sentation différente des tables sur lesquelles elle est basée.
7.3.1 Création et interrogation d’une vue
Une vue est tout point comparable à une table: en particulier on peut l’interroger par SQL. La grande
différence est qu’une vue est le résultat d’une requête, avec la caractéristique essentielle que ce résultat est
réévalué à chaque fois que l’on accède à la vue. En d’autre termes une vue est dynamique: elle donne une
représentation fidèle de la base au moment de l’évaluation de la requête.
Une vue est essentiellemnt une requête à laquelle on a donné un nom. La syntaxe de création d’une vue
est très simple:
CREATE VIEW <nom-vue>
AS <requête>
[WITH CHECK OPTION]
Exemple: on peut créer une vue qui ne contient que les cinémas parisiens:
CREATE VIEW ParisCinemas
AS SELECT * FROM Cinema WHERE ville = ’Paris’
On peut aussi en profiter pour restreindre la vision des cinémas parisiens à leur nom et à leur nombre
de salles.
CREATE VIEW SimpleParisCinemas
AS SELECT nom, COUNT(*) AS nbSalles
FROM Cinema c, Salle s
WHERE ville = ’Paris’
AND c.nom = s.nomCinema
GROUP BY c.nom
Philippe Rigaux ([email protected]), Cours de bases de données, 2003