SQL IG2

Système de Gestion de Base de Données Relationnelle
Une base de données relationnelle repose sur des concepts simples :
1. des tables à deux dimensions qui contiennent des données :
• un nombre statique de colonnes qui représentent les attributs
(la valeur d’un attribut peut être “ nul ”),
• un nombre dynamique de lignes (n-uplets ou tuples) qui représentent les instances
Nom de lattribut
nom de la relation
Annuaire
ID
Nom
Prénom
Telephone
QW01Z
Dupont
Paul
1122334455
QE05Z
ABDI
Hamed
1829364656
CW45B
Gamma
Erich
1922336415
HP81A
Pree
Wolfgang
2132536455
Clé primaire
instance
(tuple)
Donnée (valeur d'un attribut pour une
instance donnée)
Figure 05 Exemple de représentation tabulaire d'une relation
2. une algèbre simple, composé d’un ensemble d’opérateurs qui facilitent la manipulation des relations :
opérations élémentaires, opérations sur des ensembles (union, différence, produit cartésien, etc.),
opérations supplémentaires (inclusion, intersection, division) ;
3. des méthodes d’accès pour optimiser les performances : index d’arbre balancé, tables de hachage, etc.
4. la théorie des formes normales qui nécessite le respect d’un effort de conception sur le modèle de base de
données (éviter et éliminer les données redondantes et acquérir une meilleure compréhension des relations
sémantiques entre les données : formes normales)
5. des règles d’intégrité :
• intégrité de l’identité : unicité de la clé primaire (une par table),
• intégrité des références : cohérence entre clé étrangère et clé primaire ;
• intégrité de la cohérence : définie par le concepteur pour garantir la cohérence des données,
6. un Langage de Définition des Données (LDD) qui permet d’administrer des tables, attributs, vues, index,
… (CREATE, DROP, ALTER)
7. un Langage de Manipulation des Données (LMD) qui permet de manipuler les données via des
méthodes connues sous l’abréviation CRUD.
8. un Langage de Contrôle des Données (LCD) permet la gestion des transactions (COMMIT et
ROLLBACK), privilèges d’accès (GRANT et REVOKE) et connexion à un serveur.
1
La norme SQL 2 (dit aussi SQL-92) qui fait suite à la norme SQL-89 et maintenant reconnue, souvent que
partiellement, par l'ensemble des systèmes de gestion de bases de données.
Quelques définitions :
Une table, ou relation, est un ensemble de données relatives à un même sujet
La clé primaire d'une relation est un attribut ou groupe d'attribut de la relation qui détermine univoquement tous
les autres.
Tout attribut est associé à un domaine.
Un domaine est l'ensemble de valeurs que peut prendre un attribut.
Un domaine est constitué d'un type d'une longueur et de contraintes (dites contraintes de domaines) qui réduisent
l'ensemble des valeurs permises (restriction).
Une clé étrangère dans une table est un attribut (ou ensemble d'attribut) de cette table dont la valeur est valeur
de clé primaire ou candidate dans une autre table.
Structure générale du langage
• Les instructions SQL se répartissent en trois familles fonctionnellement distinctes (LDD, LMD et LCD)
LDD : permet la description de la structure de la base : table, vue, index, attributs...
CREATE, DROP, ALTER
LMD : les quatre opérations fondamentales sur les données
SELECT, INSERT, DELETE, UPDATE
LCD : pour le contrôle de données : contient
COMMIT, ROLLBACK : Les primitives de gestion des transactions.
Une transaction est un ensemble indivisible d'opérations.
GRANT, REVOKE : privilèges d'accès au données
CONNECT : connexion au serveur
SET : permet de paramétrer la session
Trois formes d'utilisation :
• SQL interactif (requête avec résultat immédiat),
• Module SQL (SQL intégré dans un langage de troisième génération C, Cobol...)
• SQL dynamique qui est une extension du SQL intégré permettant des requêtes SQL non connues a
priori, c'est à dire au moment de la compilation.
Exemple d'API : ODBC (Open DataBase Connectivity), sous forme de bibliothèques de fonction.
JDBC (Java DataBase Connectivity), permettre à des applications Java, via des pilotes JDBC,
d'accéder à des sources de données communues.
2
1 contraintes déclaratives
Il existe 4 types de contraintes déclaratives :
1.
2.
3.
4.
Contraintes de domaine (sur les colonnes définissant les valeurs prises par un attribut)
Contraintes d'intégrité d'entité : Déclaration d'une clé primaire
Contraintes d'intégrité référentielle : Assure la liaison entre clés étrangères et clés primaires.
Les Assertions, qui permettent de définir des contraintes sur des colonnes quelconques.
Soit le MLD suivant.
Créons un ordre de création de la table Imprimante :
CREATE TABLE Imprimante
(
RefImprimante
CodeType
DesImprimante
)
VARCHAR2(10),
CHAR(1),
VARCHAR2(40),
Puis étudions le type de contrainte que nous pourrions lui appliquer.
CONTRAINTES DE DOMAINE
Une contrainte qui permet de restreindre les valeurs d'une colonne.
NOT NULL : Interdire l'utilisation de NULL dans une colonne.
En SQL; NULL est une valeur de donnée compatible avec tout type de donnée.
Interprétation possible de la présence d'une valeur NULL : manquante, inconnue, quelconque ou
inapplicable (non-pertinente).
Ne pas confondre avec les valeurs : "", (chaîne vide), " " (espace), 0, 0.0,
DEFAULT : SI Le système n'a pas de valeur EXPLICITE à placer dans la colonne ET que la contrainte DEFAULT
est renseignée ALORS il insérera la valeur correspondante.
Exemple de valeurs par défaut selon le domaine :
DATE, TIME, DATETIME :
Date système
Numérique :
Zéro
Chaîne :
'Inconnu', 'même adresse', …
UNIQUE
Indique qu'il ne peut y avoir de valeurs dupliquées dans la colonne.
La contrainte UNIQUE peut s'appliquer à plusieurs colonnes d'une même table contrairement à la
clause PRIMARY KEY.
Peut accepter une ET UNE SEULE valeur NULL dans la colonne (à moins d'avoir spécifié la contrainte
NOT NULL bien entendu).
3
CHECK( )
La clause CHECK (expression logique) spécifie une contrainte sur les tuples d'une table. L'expression
logique est aussi appelée condition de recherche. Est rejetée toute ligne pour laquelle la condition de
recherche rendra FALSE, mais acceptera les lignes sur lesquelles la condition de recherche rendra
TRUE ou UNKNOWN.
Exemple :
CHECK (nom_pays IN (SELECT * FROM pays)) CONSTRAINT NOMPAYS_CONSTRAINT
CHECK ( (x, y) VALUES (IN ((1,2),(2,1),(1,3),(3,1))) CONSTRAINT COORD_CONSTRAINT
CRÉATION DE DOMAINE
Un même domaine peut être partagé par différents attributs dans différentes tables. Il est alors utile de
le spécifier en définissant un domaine particulier accompagné des ses contraintes. Exemple.
CREATE DOMAIN TypeQte INTEGER DEFAULT
CHECK (VALUE >= 0);
0
CREATE DOMAIN Sexe CHAR(1) DEFAULT 'M'
CHECK (VALUE IN ('M', 'F'));
CREATE DOMAIN Base10 INTEGER DEFAULT 0
CHECK (VALUE BETWEEN 0 AND 9);
CREATE DOMAIN TypePays VARCHAR(20)
CHECK (VALUE IN (SELECT nom_pays FROM pays));
CREATE DOMAIN OUI_NON VARCHAR(20)
DEFAULT 'NON'
CONSTRAINT CTR_LISTE_VALEUR CHECK (UPPER(VALUE) IN ('OUI', 'NON'))
CONTRAINTE D'INTÉGRITÉ D'ENTITÉ : CLÉ PRIMAIRE
PRIMARY KEY Indique qu'il ne peut y avoir de valeurs dupliquées dans la clé et que la contrainte NOT NULL est
automatiquement appliquée.
La contrainte PRIMARY KEY peut s'appliquer à plusieurs colonnes d'une même table (qui doivent
être déclarées NOT NULL).
CREATE TABLE Imprimante
(
RefImprimante
VARCHAR2(10) NOT NULL,
CodeType
CHAR(1) NOT NULL,
DesImprimante
VARCHAR2(40) DEFAULT 'Inconnue',
CONSTRAINT PK_IMPRIMANTE PRIMARY KEY (RefImprimante)
)
Une clé définie sur plusieurs colonnes.
CREATE TABLE Accepter
(
RefCartouche VARCHAR2(12)
NOT NULL,
RefImprimante VARCHAR2(10)
NOT NULL,
CONSTRAINT PK_ACCEPTER PRIMARY KEY (RefCartouche, RefImprimante)
)
4
CONTRAINTE D'INTÉGRITÉ RÉFÉRENTIELLE
Le schéma est
Table
Référencée
ID_TR
attribut
Table qui
référence
<référence
ID_TQR
att. clé étrangère
attribut
Clauses
FOREIGN KEY
REFERENCES
ON UPDATE
ON DELETE
Vue de la table qui référence
Clause REFERENCES
C'est la façon la plus simple de définir une contrainte relationnelle. La syntaxe est :
[CONSTRAINT nom de la contrainte]
REFERENCES <nom de la table référencée> [(Colonne de référence)]
Exemple.
CREATE TABLE Imprimante
(
RefImprimante
VARCHAR2(10) NOT NULL,
CodeType
CHAR(1) NOT NULL REFERENCES Type (CodeType),
DesImprimante
VARCHAR2(40) DEFAULT 'Inconnue',
CONSTRAINT PK_IMPRIMANTE PRIMARY KEY (RefImprimante)
)
Clause FOREIGN KEY
Si la clé étrangère est composée de plusieurs attributs il faut utiliser la clause FOREIGN KEY.
CREATE TABLE lig_liv
(
llv_liv
INTEGER NOT NULL REFERENCES livraisons,
llv_art
INTEGER NOT NULL REFERENCES articles,
llv_qte
INTEGER NOT NULL,
llv_cmd
INTEGER NOT NULL,
PRIMARY KEY (llv_liv, llv_art),
FOREIGN KEY (llv_cmd, llv_art) REFERENCES lig_cmd
);
Remarque : La clause FOREIGN KEY peut s'utiliser même si la clé étrangère est atomique.
ALTER TABLE Imprimante
ADD CONSTRAINT FK_IMPRIMAN_RATTACHER_TYPE
FOREIGN KEY (CodeType) REFERENCES Type (CodeType)
5
Vue de la table référencée
Que se passe-t-il si une valeur de clé primaire est modifiée ou supprimée (suppression de la ligne) ?
Les tables dont les lignes ont des attributs qui référencent la clé primaire modifiée ou supprimée doivent être mises
à jour. Ce sont ces dernières, c'est à dire les tables référençant qui décident de l'action à entreprendre en cas de
mis à jour ou de suppression d'une ligne référencée.
Des ACTIONS sont possibles sur les événements ON UPDATE et ONDELETE de tuples référençant, ces actions
sont
NO ACTION
Le moteur de la base de données déclenche une erreur et la suppression/modification de la ligne dans la
table parente est annulée. Valeur par défaut.
CASCADE
Les lignes correspondantes sont supprimées/modifiées de la table de référence si cette ligne est
supprimée/modifiée de la table parente.
SET NULL
Toutes les valeurs qui composent la clé étrangère sont NULL si la ligne correspondante dans la table parente
est supprimée/modifiée. Pour que cette contrainte soit exécutée, les colonnes de clé étrangère doivent
accepter les valeurs NULL.
SET DEFAULT
Toutes les valeurs qui composent la clé étrangère sont celles par défaut si la ligne correspondante dans la
table parente est supprimée/modifiée. Pour que cette contrainte soit exécutée, toutes les colonnes de clé
étrangère doivent avoir des définitions par défaut. Si une colonne peut avoir une valeur NULL et qu'il n'existe
aucun ensemble de valeurs par défaut explicite, NULL devient la valeur par défaut implicite de la colonne.
Exemple :
-- table référençant
CREATE TABLE Imprimante
(
RefImprimante
VARCHAR2(10) NOT NULL,
CodeType
CHAR(1) NOT NULL
REFERENCES Type (CodeType)
ON UPDATE CASCADE
ON DELETE SET NULL
CONSTRAINT TYPE_IMPRIMANTE,
DesImprimante
VARCHAR2(40) DEFAULT 'Inconnue',
CONSTRAINT PK_IMPRIMANTE PRIMARY KEY (RefImprimante)
)
6
contraintes déclaratives – suite et fin – Assertion
Nous avons vu 3 des 4 types de contraintes déclaratives :
Contraintes de domaine (sur les colonnes définissant les valeurs prises par un attribut)
Contraintes d'intégrité d'entité : Déclaration d'une clé primaire
Contraintes d'intégrité référentielle : Assure la liaison entre clés étrangères et clés primaires.
Le quatrième type de contrainte déclarative est l'Assertion, qui permet de définir des contraintes non rattachées à
une table en particulier.
LES ASSERTIONS
Prenons le cas où nous ayons deux tables articles de type différent (articleA et articleB).
On ne souhaite pas que la valeur globale du stock n'excède 1000€.
Une première ébauche nous amène à écrire la contrainte de niveau table sur la table articleA :
CREATE TABLE articleA
(
numArtA . . .
. . .
CHECK
(SUM(artA_pa * artA_stock) +
(SELECT SUM(artB_pa * artB_stock) FROM articleB) < 1000
CONSTRAINT VAL_GLOB_STOCK
)
Suffisant ? Certainement pas, car si la table ArticleA reste désespérément vide la valeur du stock de la table articleB
peut s'envoler allègrement !
Une solution consiste à placer cette même contrainte (enfin presque) dans la table articleB.
Une autre solution bien meilleure est de déplacer la portée de cette contrainte au niveau du schéma (de la base de
données). Pour cela on utilise le mot clé ASSERTION.
Une assertion déclarée indépendamment de toute table doit toujours être vérifiée.
Exemple.
CREATE ASSERTION VAL_GLOB_STOCK
CHECK ( (SELECT SUM(artA_pa * artA_stock) FROM articleA) +
(SELECT SUM(artB_pa * artB_stock) FROM articleB) < 1000)
7
Conflit de contraintes
Prenons le cas classique suivant.
CLIENT
IDClient
ARTICLE
1.N
COMMANDE
0.N
IDArticle
QteCmd
On constate que :
On ne peut créer un client que s'il a passé une commande
On ne peut créer une commande que si le client existe
Conclusion : On ne peut créer ni l'un ni l'autre !
Solution : Il faut différer la vérification des contraintes
Une contrainte peut être marquée différable ou immédiate. Par défaut une contrainte est immédiate.
On spécifie le mode d'une contrainte par l'instruction suivante :
SET CONSTRAINTS <liste de contraintes | ALL> <DEFERRED | IMMEDIATE>
La clause IMMEDIATE signifie que la contrainte sera vérifiée à la fin de chaque requête SQL
La clause DEFERRED signifie que la contrainte sera vérifiée à la fin de la transaction en cours.
exemple :
SET CONSTRAINTS FK_CLIENT_CMD DEFERRED
Remarque : Si une assertion ne peut être vérifier après une opération, l'opération est annulée par un rollback.
8
Exercices
Exercice 1 : Soit deux entités : VOITURE et CAMION.
VOITURE
VoitNum
VoitMarque
CAMION
CamNum
CamMarque
CamChargeUtile
Donner l'ordre de création SQL des tables CAMION et VOITURE. Vous devez spécifier le fait qu'une clé
doit être unique pour l'ensemble des deux tables. Un véhicule est soit une voiture soit un camion mais pas les deux.
CREATE TABLE VOITURE (
VoitNum INTEGER PRIMARY KEY,
VoitMarque CHAR(20) NOT NULL)
);
CREATE TABLE CAMION (
CamNum INTEGER PRIMARY KEY,
CamMarque CHAR(20) NOT NULL),
CamChargeUtile INTEGER NOT NULL,
CHECK ( CamNum NOT IN (SELECT VoitNum FROM VOITURE))
);
variante
CREATE TABLE CAMION (
CamNum INTEGER PRIMARY KEY,
CamMarque CHAR(20) NOT NULL);
CamChargeUtile INTEGER NOT NULL,
CHECK ( CamNum <> ANY (SELECT VoitNum FROM VOITURE));
)
Puis
ALTER TABLE VOITURE
ADD CONSTRAINT UNIQUE_TYPE_VEHICULE
CHECK ( VoitNum <> ANY (SELECT CamNum FROM CAMION));
Ou bien, créer simplement une unique contrainte :
CREATE ASSERTION UNIQUE_TYPE_VEHICULE
CHECK ( NOT EXISTS (
(SELECT VoitNum FROM VOITURE
WHERE VoitNum IN (SELECT CamNum FROM CAMION))
);
variante :
CREATE ASSERTION UNIQUE_TYPE_VEHICULE
CHECK ( UNIQUE (
SELECT VoitNum FROM VOITURE
UNION
SELECT CamNum FROM CAMION))
);
9
Exercice 2 : Soit l'entité Option suivante :
OPTION
OptA
OptB
Créer la table correspondante, de sorte que les attributs OptA et OptB ne puissent prendre que les valeurs 0 ou 1.
solution 1 :
CREATE DOMAIN ZEROUUN SMALLINT DEFAULT 0
CHECK (VALUE BETWEEN 0 AND 1);
CREATE TABLE OPTION (
OptA ZEROUUN NOT NULL,
OptB ZEROUUN NOT NULL,
)
=> De plus, si un champ est nul, l'autre doit l'être aussi.
solution 2 :
CREATE TABLE OPTION (
OptA SMALLINT,
OptB SMALLINT,
CHECK ((OptA, OptB) IN (VALUES (0,0) (0,1) (1,0) (1,1) (NULL, NULL))
)
10