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