SQL (LDD)
publicité
Conception d’une base de données Cours “Bases de données” 1. Un modèle conceptuel : le modèle entité- association 2. Le modèle relationnel 2.1. Définition 3° année (MISI) 2.2. Règles de traduction Antoine Cornuéjols 2.3. Dépendances entre données et formes normales 2.4. Le LDD SQL2 www.lri.fr/~antoine [email protected] Le modèle relationnel Langage de Définition de Données SQL2 SQL (Structured Query Language) Interface de communication avec les SGBD relationnels Langage non procédural. Le modèle relationnel Langage de Définition de Données SQL2 SQL n’est pas un langage de programmation complet SQL permet : de définir le schéma de la base de données (LDD) de charger les tables relationnelles (LMD) de manipuler les données stockées (LMD) Plusieurs versions de SQL de gérer la base de données (LDD) : sécurité, organisation physique 1989 : plus ancien standard 1992 : SQL-92 ou SQL-2 1999 : SQL-99 ou SQL-3 ((un peu) orienté objet) 2003 : SQL-2003 (fonctions pour XML) Ici : aperçu de la partie LDD Plus loin : le LMD 3 4 Le modèle relationnel Langage de Définition de Données SQL2 Types SQL Le modèle relationnel Langage de Définition de Données SQL2 Création des tables Commande : CREATE TABLE CREATE TABLE Internaute (email VARCHAR (50) NOT NULL, nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4)) 5 Le modèle relationnel Langage de Définition de Données SQL2 Les contraintes 6 Le modèle relationnel Langage de Définition de Données SQL2 Les clés Règles permettant d’assurer une certaine intégrité des données : 1. Un attribut doit toujours avoir une valeur. (Contrainte NOT NULL) 2. Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation 3. Un attribut dans une table est liée à la clé primaire d’une autre table (intégrité référentielle) 4. La valeur d’un attribut doit être unique au sein de la relation Clé Un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique un tuple d’une relation. Clé primaire Il peut y avoir plusieurs clés, mais l’une d’entre elles doit être choisie comme clé primaire. Choix capital : la clé primaire est la clé utilisée pour référencer une ligne et une seule à partir d’autres tables. Est spécifiée avec l’option PRIMARY KEY 5. Autres règles s’appliquant à la valeur d’un attribut (e.g. min et max) 7 8 Le modèle relationnel Langage de Définition de Données SQL2 Clés Commande : PRIMARY KEY CREATE TABLE Internaute (email VARCHAR (50) NOT NULL, Le modèle relationnel Langage de Définition de Données SQL2 Clé secondaire Commande : UNIQUE On spécifie que la valeur d’un attribut est unique pour l’ensemble de la colonne. nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4), CREATE TABLE Artiste (id INTEGER NOT NULL, nom VARCHAR (30) NOT NULL, PRIMARY KEY (email)) prenom VARCHAR (30) NOT NULL, anneeNaiss INTEGER, Clé constituée de plusieurs attributs : PRIMARY KEY (ID), CREATE TABLE Notation (idFilm INTEGER NOT NULL, UNIQUE (nom, prenom)); email VARCHAR (50) NOT NULL, note INTEGER DEFAULT 0, PRIMARY KEY (titre, email)) 9 Le modèle relationnel Langage de Définition de Données SQL2 Clé étrangère Commande : FOREIGN KEY Attributs qui font référence à une ligne dans une autre table. CREATE TABLE Film (idFilm INTEGER NOT NULL, nom VARCHAR (50) NOT NULL, année INTEGER NOT NULL, idMES INTEGER, codePays INTEGER, PRIMARY KEY (ideFilm), FOREIGN KEY (idMES) REFERENCE Artiste, FOREIGN KEY (codePays) REFERENCE Pays); Référence la clé primaire de la table Artiste. Le SGBD vérifiera, pour toute modification pouvant affecter le lien entre les deux tables, que la valeur de idMES correspond bien à une ligne de Artiste. 11 10 Le modèle relationnel Langage de Définition de Données SQL2 Clé étrangère Modifications contrôlées : 1. l’insertion dans Film avec une valeur inconnue pour idMES 2. la destruction d’un artiste 3. la modification de id dans Artiste ou de idMES dans Film. Que se passe-t-il si une violation d’une contrainte d’intégrité est détectée ? Par défaut, la mise à jour est rejetée On peut demander la répercussion de cette mise à jour de manière à ce que la contrainte soit respectée grâce à UPDATE et ON DELETE 12 Le modèle relationnel Langage de Définition de Données SQL2 Clé étrangère Le modèle relationnel Langage de Définition de Données SQL2 Énumération des valeurs possibles CREATE TABLE Film (idFilm VARCHAR NOT NULL, année INTEGER NOT NULL, idMES INTEGER, codePays INTEGER, PRIMARY KEY (ideFilm), FOREIGN KEY (idMES) REFERENCE Artiste, ON DELETE SET NULL, FOREIGN KEY (codePays) REFERENCE Pays); La destruction d’un metteur en scène déclenche la mise à NULL de la clé étrangère idMES pour tous les films qu’il a réalisés. CREATE TABLE Film (idFilm VARCHAR NOT NULL, année INTEGER CHECK (année BETWEEN 1890 AND 2000) NOT NULL, genre VARCHAR (10) CHECK (genre IN (‘Histoire’, ‘Western’, ‘Drame’)), idMES INTEGER, codePays INTEGER, PRIMARY KEY (ideFilm), FOREIGN KEY (idMES) REFERENCE Artiste, ON DELETE SET NULL, FOREIGN KEY (codePays) REFERENCE Pays); 13 Le modèle relationnel Langage de Définition de Données SQL2 Commande : CHECK 14 Le modèle relationnel Langage de Définition de Données SQL2 Modification du schéma Modification du schéma Commande : ALTER TABLE nomTable ACTION description Commande : ALTER TABLE nomTable ACTION description où ACTION peut être : Différents types d’altérations sont possibles : ADD, MODIFY, DROP ou RENAME Ajout d’une colonne (ADD COLUMN) Modification des attributs Modification de la définition d’une colonne (MODIFY COLUMN) ALTER TABLE Internaute ADD region VARCHAR(10); Suppression d’une colonne (DROP COLUMN) ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL; Modification du nom de la table ou d’une colonne (RENAME TO, RENAM COLUMN) ALTER TABLE Internaute ALTER region SET DEFAULT ‘PACA’; ALTER TABLE Internaute DROP region; 15 16 Le modèle relationnel Langage de Définition de Données SQL2 Commande : ALTER TABLE nomTable ACTION description Exemples : Le modèle relationnel Langage de Définition de Données SQL2 Suppression d’une table Commande : SQL DROP TABLE ALTER TABLE Livre RENAME TO Livre2; ALTER TABLE Livre RENAME COLUMN Titre TO Titre2; ALTER TABLE Livre Exemple : DROP TABLE Livre [CASCADE CONSTRAINT]; ADD COLUMN Data_Achat DATE, MODIFY Auteur VARCHAR2(30) NOT NULL; Remarque : L’option cascade constraint permet de supprimer la table même si des contraintes d’intégrité référentielle portent sur des colonnes de cette table. 17 Le modèle relationnel Langage de Définition de Données SQL2 18 Le modèle relationnel Langage de Définition de Données SQL2 Insertion d’un tuple dans une table Insertion d’un tuple dans une table Commande : INSERT INTO Commande : INSERT INTO Exemples : Exemples : INSERT INTO Livre VALUES (‘HUGO’, ‘HERNANT’, 1830, ‘THEATRE’, 120.00); INSERT INTO Livre (Auteur, Titre, Année, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, ‘Roman’); Insère l’ensemble des valeurs associées à un tuple de la table. Insère un nouveau tuple sans initialiser le prix. INSERT INTO Livre (Auteur, Titre, Année, Prix, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, 148.5, ‘Roman’); Insère un nouveau tuple en utilisant un ordre différent de l’ordre de de définition. 19 20 Le modèle relationnel Langage de Définition de Données SQL2 Création d’index Le modèle relationnel Langage de Définition de Données SQL2 Création d’index Un index offre un chemin d’accès très rapide aux lignes d’une table. Les SGBD créent systématiquement un index sur la clé primaire. Un index est également créé pour chaque clause UNIQUE On peut de plus créer d’autres index CREATE INDEX idxGenre ON Film (genre); Cet index permet d’exécuter très rapidement des requêtes SQQL ayant comme critère de recherche le genre d’un film. CREATE [UNIQUE] INDEX nomIndex ON nomTable (attribut1 [, ...]) SELECT * FROM Film CREATE UNIQUE INDEX idxNom ON Artiste (nom, prenom); Crée un index de idxNom sur les attributs nom et prenom de la table Artiste. WHERE genre = ‘Western’ ATTENTION : les index ont un impact négatif sur les commandes d’insertion et de destruction. 21 Langage de manipulation des données 22 L’algèbre relationnelle Bases [Codd, 1970, “A relational model for large shared shared data banks”, Communications of the ACM, vol.13, No.6, pp.377-387] 1. L’algèbre relationnelle Collection d’opérations formelles agissant sur des relations et produisent des relations 1.1. Opérateurs ensemblistes 1.2. Opérateurs relationnels 1.3. Expression de requêtes 2. Le langage SQL Opérations ensemblistes (binaires) Union, différence, intersection, produit cartésien Opérations spécifiques Projection, sélection (restriction), jointure Opérations dérivées Intersection, θ-jointure, jointure naturelle, division 24 L’algèbre relationnelle L’algèbre relationnelle Bases Bases Un domaine D est un ensemble de valeurs Une relation est un sous-ensemble du produit cartésien d’une liste de domaines Exemples : Exemple : à partir de D1 et D2, on peut construire la relation R booléen = {0,1} couleur = {rouge, vert, bleu} Le produit cartésien d’un ensemble de domaines D1, D2, ..., Dn est l’ensemble des n-uplets (ou tuples) <v1, v2, ..., vn> tels que vi appartient à Di; Exemple : le produit cartésien de D1 = {0, 1} et D2 = {rouge, vert, bleu} est : rouge 0 rouge 1 vert 0 vert 1 bleu 0 bleu 1 Bases A2 1 vert 0 vert 1 bleu 0 L’ensemble des tuples est une extension possible de R Le schéma de la table est composé du nom de la relation et de la liste des couples (attribut, domaine) Exemple : R(A1 : couleur, A2 : booléen) Une base de données relationnelle est un ensemble de schémas relationnels 25 L’algèbre relationnelle A1 rouge Les colonnes sont les attributs de la table 26 L’algèbre relationnelle Opérateurs ensemblistes Une table ou relation est un ensemble de tuples représenté sous forme tabulaire et ayant les propriétés suivantes : L’union Deux tables sont compatibles avec l’union si elles satisfont les conditions suivantes : Elles contiennent le même nombre d’attributs Les attributs correspondants ont une forme identique 1. Chaque table porte un nom unique 2. A l’intérieur d’une table, le nom de chaque attribut est unique et désigne une colonne avec des propriétés spécifiques 3. Une table peut contenir un nombre quelconque d’attributs, l’ordre des colonnes étant indifférent 4. L’un des attributs ou une combinaison d’attributs identifie de façon unique chaque tuple dans la table et sera la clé primaire 5. Une table peut contenir un nombre quelconque de tuples, leur ordre dans la table étant indifférent. 27 CLUB DE SPORT E E1 E7 E19 Nom Meier Humbert Savoy Rue rue Faucigny route des Alpes avenue de la Gare Ville de domicile Fribourg Bulle Romont CLUB DE PHOTO E E4 E7 Membre Brodard Humbert Rue rue du Tilleul route des Alpes 28 Ville de domicile Fribourg Bulle L’algèbre relationnelle L’algèbre relationnelle Opérateurs ensemblistes Opérateurs ensemblistes L’union L’intersection CLUB DE SPORT U E E1 E7 E19 E4 CLUB DE PHOTO Nom Meier Humbert Savoy Brodard CLUB DE SPORT Rue rue Faucigny route des Alpes avenue de la Gare rue du Tilleul Ville de domicile ∩ E Nom E7 Fribourg Bulle Romont Fribourg CLUB DE PHOTO Rue Humbert route des Alpes 29 30 L’algèbre relationnelle L’algèbre relationnelle Opérateurs ensemblistes Opérateurs ensemblistes La différence R\S Le produit cartésien R x S Ensemble de toutes les combinaisons possibles des tuples de R avec ceux de S S’obtient en éliminant de R toutes les occurences aussi présentes dans S CLUB DE SPORT \ CLUB DE PHOTO E E1 E19 Nom Meier Savoy Ville de domicile Bulle CLUB DE SPORT Rue rue Faucigny avenue de la Gare 31 Ville de domicile Fribourg Romont x CLUB DE PHOTO E Nom Rue E1 E7 E19 Meier Humbert Savoy rue Faucigny route des Alpes avenue de la Gare Ville de domicile Fribourg Bulle Romont 32 E Membre E4 E7 E4 E7 Brodard Humbert Brodard Humbert Rue rue du Tilleul route des Alpes rue du Tilleul route des Alpes Ville Fribourg Bulle Fribourg Bulle L’algèbre relationnelle L’algèbre relationnelle Opérateurs relationnels La projection Opérateurs relationnels La sélection πM (R) Construit, à partir de la table R, une sous-table dont les noms d’attributs sont définis dans M σF (R) Permet la sélection des tuples d’une table R d’après une formule F La formule F contient un nombre déterminé de noms d’attributs ou de constantes liés entre eux par des opérateurs de comparaison (<, >, =, ...) ou par des opérateurs CLUB DE SPORT E E1 E7 E19 logiques (AND, OR, NOT). Nom Meier Humbert Savoy Rue rue Faucigny route des Alpes avenue de la Gare Ville de domicile Fribourg Bulle Romont πNom (CLUB DE SPORT) E E1 E7 E19 Nom Meier Humbert Savoy 33 L’algèbre relationnelle Opérateurs relationnels La jointure 34 L’algèbre relationnelle Opérateurs relationnels La division R !"P S Permet de composer deux tables en une seule R÷S Possible à condition que S soit un sous-table contenue dans R. Combinaison de tous les tuples de R avec ceux de S qui satisfont le prédicat de Produit une sous-table R’ à partir de R, telle que toutes les combinaisons de tuples r’ jointure P. dans R’ avec les tuples s dans S sont contenues dans la table R. En d’autres termes, le produit cartésien R’ x S doit être contenu dans la table R. R !"P S = σP (R × S) 35 36
