Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Cours “Bases de données” 3° année (MISI) Antoine Cornuéjols www.lri.fr/~antoine

Cours “Bases de données” 3° année (MISI) Antoine Cornuéjols www.lri.fr/~antoine antoine.cornuejols@agroparistech.fr Conception d’une base de données 1. Un modèle conceptuel : le modèle entité- association 2. Le modèle relationnel 2.1. Déﬁnition 2.2. Règles de traduction 2.3. Dépendances entre données et formes normales 2.4. Le LDD SQL2 Le modèle relationnel 3 Langage de Déﬁnition de Données SQL2 Plusieurs versions de SQL 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) SQL (Structured Query Language) Interface de communication avec les SGBD relationnels Langage non procédural. Le modèle relationnel 4 Langage de Déﬁnition de Données SQL2 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) de gérer la base de données (LDD) : sécurité, organisation physique Ici : aperçu de la partie LDD Plus loin : le LMD SQL n’est pas un langage de programmation complet Le modèle relationnel 5 Langage de Déﬁnition de Données SQL2 Types SQL Le modèle relationnel 6 Langage de Déﬁnition 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)) Le modèle relationnel 7 Langage de Déﬁnition de Données SQL2 Les contraintes 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 5. Autres règles s’appliquant à la valeur d’un attribut (e.g. min et max) Le modèle relationnel 8 Langage de Déﬁnition de Données SQL2 Les clés 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 Le modèle relationnel 9 Langage de Déﬁnition de Données SQL2 Clés Commande : PRIMARY KEY CREATE TABLE Internaute (email VARCHAR (50) NOT NULL, nom VARCHAR (20) NOT NULL, prenom VARCHAR (20), motDePasse VARCHAR (60) NOT NULL, anneeNaiss DECIMAL (4), PRIMARY KEY (email)) CREATE TABLE Notation (idFilm INTEGER NOT NULL, email VARCHAR (50) NOT NULL, note INTEGER DEFAULT 0, PRIMARY KEY (titre, email)) Clé constituée de plusieurs attributs : Le modèle relationnel 10 Langage de Déﬁnition 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. 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)); Le modèle relationnel 11 Langage de Déﬁnition 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. Le modèle relationnel 12 Langage de Déﬁnition 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 Le modèle relationnel 13 Langage de Déﬁnition de Données SQL2 Clé étrangère 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); Le modèle relationnel 14 Langage de Déﬁnition de Données SQL2 Énumération des valeurs possibles Commande : CHECK Modification des attributs ALTER TABLE Internaute ADD region VARCHAR(10); ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL; ALTER TABLE Internaute ALTER region SET DEFAULT ‘PACA’; ALTER TABLE Internaute DROP region; Le modèle relationnel 15 Langage de Déﬁnition de Données SQL2 Modification du schéma où ACTION peut être : ADD, MODIFY, DROP ou RENAME Commande : ALTER TABLE nomTable ACTION description Le modèle relationnel 16 Langage de Déﬁnition de Données SQL2 Modification du schéma Commande : ALTER TABLE nomTable ACTION description Différents types d’altérations sont possibles : Ajout d’une colonne (ADD COLUMN) Modification de la définition d’une colonne (MODIFY COLUMN) Suppression d’une colonne (DROP COLUMN) Modification du nom de la table ou d’une colonne (RENAME TO, RENAM COLUMN) Le modèle relationnel 17 Langage de Déﬁnition de Données SQL2 Commande : ALTER TABLE nomTable ACTION description Exemples : ALTER TABLE Livre RENAME TO Livre2; ALTER TABLE Livre RENAME COLUMN Titre TO Titre2; ALTER TABLE Livre ADD COLUMN Data_Achat DATE, MODIFY Auteur VARCHAR2(30) NOT NULL; DROP TABLE Livre [CASCADE CONSTRAINT]; Le modèle relationnel 18 Langage de Déﬁnition de Données SQL2 Suppression d’une table Commande : SQL DROP TABLE Exemple : 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. INSERT INTO Livre (Auteur, Titre, Année, Prix, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, 148.5, ‘Roman’); Le modèle relationnel 19 Langage de Déﬁnition de Données SQL2 Insertion d’un tuple dans une table Commande : INSERT INTO Exemples : Insère l’ensemble des valeurs associées à un tuple de la table. INSERT INTO Livre VALUES (‘HUGO’, ‘HERNANT’, 1830, ‘THEATRE’, 120.00); Insère un nouveau tuple en utilisant un ordre différent de l’ordre de de définition. INSERT INTO Livre (Auteur, Titre, Année, Genre) VALUES (‘Balzac, ‘Le père Goriot’, 1834, ‘Roman’); Le modèle relationnel 20 Langage de Déﬁnition de Données SQL2 Insertion d’un tuple dans une table Commande : INSERT INTO Exemples : Insère un nouveau tuple sans initialiser le prix. Le modèle relationnel 21 Langage de Déﬁnition de Données SQL2 Création d’index CREATE [UNIQUE] INDEX nomIndex ON nomTable (attribut1 [, ...]) 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 UNIQUE INDEX idxNom ON Artiste (nom, prenom); Crée un index de idxNom sur les attributs nom et prenom de la table Artiste. Le modèle relationnel 22 Langage de Déﬁnition de Données SQL2 Création d’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. SELECT * FROM Film WHERE genre = ‘Western’ ATTENTION : les index ont un impact négatif sur les commandes d’insertion et de destruction. Langage de manipulation des données 1. L’algèbre relationnelle 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 L’algèbre relationnelle 24 Bases [Codd, 1970, “A relational model for large shared shared data banks”, Communications of the ACM, vol.13, No.6, pp.377-387] Collection d’opérations formelles agissant sur des relations et produisent des relations θ L’algèbre relationnelle 25 Bases Un domaine D est un ensemble de valeurs Exemples : 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 L’algèbre relationnelle 26 Bases Une relation est un sous-ensemble du produit cartésien d’une liste de domaines Exemple : à partir de D1 et D2, on peut construire la relation R A1 A2 rouge 1 vert 0 vert 1 bleu 0 Les colonnes sont les attributs de la table L’ensemble des tuples est une extension possible de R Le schéma de la table est composé uploads/Litterature/ sql-ldd-compress.pdf