BDBIO - Modélisation relationnelle - niveau physique Fabien Duchateau fabien.duchateau [at] univ-lyon1.fr Université Claude Bernard Lyon 1 2016 - 2017 http://liris.cnrs.fr/fabien.duchateau/ens/BDBIO/ Positionnement dans BDBIO BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 2 / 46 Rappels Du diagramme E/A (conceptuel)… … à un schéma relationnel (logique) serie(nomSerie) saison(idSaison, dateLancement, #nomSerie) episode(numero, #idSaison, titre) acteur(numINSEE, nom, prenom) joue(#numero, #idSaison, #numINSEE, salaire) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 3 / 46 Motivation Dernière étape de la modélisation (niveau physique) : I Implique le choix d’un SGBD pour stocker la BD I Besoin de traduire le modèle logique en modèle physique I I Dans notre cas, transformation du schéma relationnel en tables SQL Certains outils traduisent automatiquement (e.g., JMerise) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 4 / 46 Plan Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Syntaxe de création d’une base de données Dans un SGBD, les tables sont stockées dans une base de données, qu’il faut créer avec une requête SQL ou un client graphique (e.g., PHPMyAdmin) CREATE OR REPLACE DATABASE nom_bd ; I I Création d’une base de données nommée nom_bd L’option or replace permet de remplacer (détruire puis recréer) une base de données nom_bd existante http://mariadb.com/kb/en/mariadb/create-database/ BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 6 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de base de données 7 8 9 /* Creation d'une base de donnees Séries */ CREATE OR REPLACE DATABASE Series; USE Series; Création ou remplacement d’une base de données Series. La commande use permet de sélectionner (travailler) avec la base mentionnée http://mariadb.com/kb/en/mariadb/use/ BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 7 / 46 Plan Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Généralités Pour chaque relation du modèle relationnel, création de la table correspondante I I Avec une requête SQL ou un client graphique (e.g., PHPMyAdmin) Attention aux différences d’implémentation de SQL dans les SGBD (MariaDB pour cet enseignement) À la création d’une table (schéma), on indique : I I I Le nom des attributs Le type de chaque attribut De manière optionnelle : I I certaines contraintes d’intégrité des caractéristiques de stockage BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 9 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Syntaxe de création de table CREATE TABLE nom_table ( att1 type1 [ , att2 type2 , …] ); I I Création simple d’une table de nom nom_table, avec un attribut att1 de type type1 , un attribut att2 de type type2 , etc. Les crochets [. . . ] représentent des éléments optionnels http://mariadb.com/kb/en/mariadb/create-table/ BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 10 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Quelques types d’attributs I BOOLEAN ou TINYINT(1) I VARCHAR(longueur) I taille variable, pouvant contenir longueur caractères I TEXT I DOUBLE(longueur, échelle) I un nombre à virgule flottante (double précision) I INT ou INTEGER, TINYINT, MEDIUMINT, BIGINT (entier) I DATE, une date au jour près, stockée au format YYYY-MM-JJ I ENUM(’val1 ’, ’val2 ’, …) I définition d’une liste de valeurs autorisées pour un attribut http://mariadb.com/kb/en/mariadb/data-types/ BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 11 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables serie(nomSerie) 18 19 20 CREATE TABLE Serie( nomSerie VARCHAR (255) ); saison(idSaison, dateLancement, #nomSerie) 22 23 24 25 26 CREATE TABLE Saison( idSaison INT , dateLancement DATE , nomSerie VARCHAR (255) ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 12 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables (2) episode(numero, #idSaison, titre) 28 29 30 31 32 CREATE TABLE Episode( numero INT , idSaison INT , titre VARCHAR (255) ); acteur(numINSEE, nom, prenom) 34 35 36 37 38 CREATE TABLE Acteur( numINSEE INT , nom VARCHAR (255) , prenom VARCHAR (255) ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 13 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables (3) joue(#numero, #idSaison, #numINSEE, salaire) 40 41 42 43 44 45 CREATE TABLE Joue( numero INT , idSaison INT , numINSEE INT , salaire DOUBLE ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 14 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Visualisation du schéma d’une table I Avec un client graphique (e.g., PHPMyAdmin) I En ligne de commande, avec l’instruction desc : I noms d’attributs, types, certaines contraintes d’intégrité DESC nom_table ; Affichage des informations de la table Joue dans un terminal BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 15 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution En résumé I Création (du schéma) d’une table avec create table I Définition de chaque attribut par son nom et son type BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 16 / 46 Plan Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Généralités Le schéma de la table est créé, mais il n’y a aucune donnée ! SQL (comme LMD) permet la création (CRUD) d’instances Deux méthodes pour ajouter des instances en SQL : I I Insertion d’un seul n-uplet (valeur pour chaque attribut du n-uplet) Copie de données existantes http://mariadb.com/kb/en/mariadb/insert-commands/ Ces deux méthodes sont aussi réalisables via un client graphique (e.g., PHPMyAdmin) ou un langage de programmation BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 18 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Insertion d’un seul n-uplet INSERT INTO nom_table(att1 , …, attn ) VALUES(val1 , …, valn ); I I I Insertion d’un n-uplet dans la table nom_table Chaque attribut de att1 , …, attn aura la valeur val1 , …, valn correspondante (i.e., vali pour l’attribut atti ) Si un attribut de la table nom_table n’apparaît pas dans att1 , …, attn , alors la valeur du n-uplet pour cet attribut est null BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 19 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Insertion d’un seul n-uplet (2) INSERT INTO nom_table VALUES(val1 , …, valn ); I I I Insertion simplifiée d’un n-uplet dans la table nom_table (sans spécifier les attributs) Obligation de donner une valeur à chaque attribut de nom_table L’ordre des valeurs val1 , …, valn est l’ordre des attributs dans la définition de la table nom_table BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 20 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple d’insertion d’un seul n-uplet 52 53 INSERT INTO Serie VALUES('The Big Bang Theory '); INSERT INTO Serie VALUES('Game of Thrones '); 54 55 56 INSERT INTO Saison VALUES (1, '2011 -09 -22', 'The Big Bang Theory '); INSERT INTO Saison VALUES (2, '2012 -09 -27', 'The Big Bang Theory '); INSERT INTO Saison VALUES (3, '2011 -04 -17', 'Game of Thones '); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 21 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Copie de données existantes INSERT INTO nom_table(att1 , . . . , attn ) SELECT e1 , . . . , en FROM . . . ; I I I Insertion de n-uplets dans la table nom_table à partir de données récupérées par la requête select … from La requête ne peut pas contenir de order by (le SGBD détermine l’ordre de stockage des n-uplets) Le nom des attributs dans le résultat de la requête n’est pas important : c’est l’ordre des expressions qui compte BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 22 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de copie de données existantes 81 82 83 84 INSERT INTO Serie(nomSerie) SELECT nomSerie FROM Saison WHERE idSaison = 2; I Quel résultat obtient-on ? I Comment visualiser le résultat en SQL ? I Pourquoi peut-on avoir deux instances The Big Bang Theory dans la table Serie ? BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 23 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution En résumé I Insertion d’un n-uplet (insert into … values …) I I une version compacte de cette requête permet d’insérer plusieurs n-uplets en même temps (cf ”export” sous MariaDB) Insertion d’un n-uplet (insert into … select …) Démo avec MariaDB (script SQL en ligne) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 24 / 46 Plan Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Généralités Dans l’exemple précédent, aucune contrainte sur les tables : il est donc possible de créer des épisodes identiques, d’avoir des épisodes sans actrice, d’insérer des salaires négatifs, etc. Besoin de spécifier des contraintes d’intégrité ! BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 26 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Généralités (2) Le SGBD vérifie les contraintes lors des insertions ou mises à jour de n-uplets Quand créer les contraintes ? I Lors de la création de la table I Lors d’une évolution ultérieure du schéma de la table Quels types de contraintes ? I Unicité I Autorisation des valeurs nulles I Clé primaire (PK, pour Primary Key) I Clé étrangère (FK, pour Foreign Key) I Valeurs autorisées (check) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 27 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Syntaxe de création de contrainte CREATE TABLE nom_table ( att1 type1 , [. . . ] CONSTRAINT [nom1 ] def _contrainte1 [. . . ] ); I Le mot-clé constraint permet de spécifier une contrainte I nom1 est le nom de la contrainte (optionnel) I def _contrainte1 définit la contrainte (selon son type) I Certaines contraintes ont une syntaxe ”raccourcie” (contrainte indiquée après l’attribut concerné) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 28 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte unique CONSTRAINT nomc UNIQUE(atti , attj , …) I I I Avec la contrainte unique, chaque n-uplet doit avoir une combinaison de valeurs différente pour les attributs atti , attj , … Il est par contre possible d’avoir deux fois la même valeur pour un attribut atti Si une des valeurs pour atti , attj , … est null, la contrainte ne s’applique pas sur le n-uplet concerné BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 29 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de contrainte unique 103 104 105 106 107 CREATE TABLE TestUnique1( att1 INTEGER , att2 INTEGER , CONSTRAINT UNIQUE(att1 , att2) ); Création d’une table TestUnique1 avec deux attributs, et une contrainte d’unicité sur ces deux attributs 109 110 111 CREATE TABLE TestUnique2( att1 INTEGER UNIQUE ); Création d’une table TestUnique2 avec un seul attribut unique (raccourci d’écriture) Le raccourci d’écriture n’est valable que lorsque la contrainte porte sur un seul attribut BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 30 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte not null att1 type1 [ NOT ] NULL I I I Contrainte [not] null uniquement spécifiée lors de la définition d’un attribut Indique que l’attribut peut / ne peut pas recevoir de valeurs nulles Par défaut, les attributs acceptent les valeurs nulles BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 31 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de contrainte not null 113 114 115 116 CREATE TABLE TestNotNull( att1 INTEGER NOT NULL , att2 INTEGER NOT NULL ); Création d’une table TestNotNull avec deux attributs dont les valeurs ne peuvent pas être nulles BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 32 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte de clé primaire CONSTRAINT nomc PRIMARY KEY(atti , attj , …) I I I I Une contrainte de clé primaire indique que l’ensemble d’attributs (atti , attj , …) sert d’identifiant principal pour les n-uplets de la table La valeur de la clé primaire permet donc de retrouver un n-uplet de manière non ambigüe Implique not null et unique sur chacun des (atti , attj , …) Au maximum une contrainte primary key par table BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 33 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte de clé primaire - auto-incrément Il est souvent préférable d’utiliser un entier (sans signification) en clé primaire plutôt qu’un attribut (e.g., le nom de la série, les nom/prénom d’une actrice) I I I I Un SGBD peut gérer automatiquement ces clés primaires par auto-incrément (auto_increment pour MySQL, serial pour PostgreSQL, etc.) À l’insertion d’un n-uplet, l’attribut en clé primaire reçoit une valeur null Le SGBD affecte automatiquement à la clé primaire la plus grande valeur positive de la colonne +1 La première valeur d’un attribut auto-incrémenté est 1 BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 34 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de contrainte de clé primaire 118 119 120 121 122 CREATE TABLE TestPK1( att1 INTEGER , att2 INTEGER , CONSTRAINT pk_TestPK1 PRIMARY KEY(att1 , att2) ); Création d’une table TestPK1 avec deux attributs qui forment la clé primaire de la table 124 125 126 CREATE TABLE TestPK2( att1 INTEGER AUTO_INCREMENT PRIMARY KEY ); Création d’une table TestPK2 avec un seul attribut qui est clé primaire (raccourci d’écriture), et dont les valeurs sont gérées par le SGBD (auto-increment) Le raccourci d’écriture n’est valable que lorsque la contrainte porte sur un seul attribut BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 35 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte de clé étrangère CONSTRAINT nomc FOREIGN KEY(att1 , …, attk ) REFERENCES table_cible(att10 ,…,attk0 ) I I Une clé étrangère est une référence vers la clé primaire d’une autre table Les valeurs pour (att1 , …, attk ) doivent correspondre aux valeurs d’un des n-uplets de table_cible pour ses attributs (att10 ,…,attk0 ) Certains moteurs de stockage ne gèrent pas les clés étrangères (e.g., MyISAM) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 36 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de contrainte de clé étrangère 128 129 130 131 132 CREATE TABLE TestFK1( att1 INTEGER , att2 INTEGER , CONSTRAINT fk_TestFK1_att1 FOREIGN KEY(att1) REFERENCES TestPK1(att1) ); Création d’une table TestFK1 avec deux attributs, parmi lesquels le premier est clé étrangère. Il ne peut donc avoir que des valeurs existantes dans l’attribut référencé (ici att1 de la table TestPK1) 134 135 136 CREATE TABLE TestFK2( att1 INTEGER REFERENCES TestPK2(att1) ); Création d’une table TestFK2 avec un attribut qui est clé étrangère (raccourci d’écriture). Il ne peut donc avoir que des valeurs existantes dans l’attribut référencé (ici att1 de la table TestPK2) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 37 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Contrainte check CONSTRAINT nomc CHECK(condition) I I La contrainte check permet de spécifier une condition (booléenne) qui sera vérifiée lors d’insertions ou modification de n-uplets La forme check(att in (’val1 ’, ’val2 ’, …)), utilisée pour les types énumérés est un cas particulier de cette contrainte Dans MariaDB/MySQL, les contraintes check ne sont pas vérifiées BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 38 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de contrainte check 139 140 141 142 143 CREATE TABLE TestCheck1( att1 INTEGER , att2 INTEGER , CONSTRAINT CHECK(att1 > 0) ); Création d’une table TestCheck1 avec deux attributs. Le premier a une contrainte qui force ses valeurs à être positives 145 146 147 CREATE TABLE TestCheck2( att1 INTEGER CHECK(att1 > 0) ); Création d’une table TestCheck2 avec un seul attribut. Celui-ci a une contrainte qui force ses valeurs à être positives (raccourci d’écriture) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 39 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables avec contraintes serie(nomSerie) 150 151 152 CREATE TABLE Serie( nomSerie VARCHAR (255) PRIMARY KEY ); saison(idSaison, dateLancement, #nomSerie) 154 155 156 157 158 CREATE TABLE Saison( idSaison INT PRIMARY KEY , dateLancement DATE , nomSerie VARCHAR (255) REFERENCES Serie(nomSerie) ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 40 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables avec contraintes (2) episode(numero, #idSaison, titre) 160 161 162 163 164 165 CREATE TABLE Episode( numero INT , idSaison INT REFERENCES Saison(idSaison), titre VARCHAR (255) , PRIMARY KEY(numero , idSaison) ); acteur(numINSEE, nom, prenom) 167 168 169 170 171 CREATE TABLE Acteur( numINSEE INT PRIMARY KEY , nom VARCHAR (255) , prenom VARCHAR (255) ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 41 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Exemple de création de tables avec contraintes (3) joue(#numero, #idSaison, #numINSEE, salaire) 173 174 175 176 177 178 179 180 CREATE TABLE Joue( numero INT REFERENCES Episode(numero), idSaison INT REFERENCES Saison(idSaison), numINSEE INT REFERENCES Acteur(numINSEE), salaire DOUBLE , CONSTRAINT pk_Joue PRIMARY KEY(numero , idSaison , numINSEE), CONSTRAINT check_salaire_positif CHECK(salaire > 0.0) ); BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 42 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution En résumé I I Contraintes de clés (primaires et étrangères) Contraintes sur les valeurs autorisées (unique, [not] null, et check) Démo avec MariaDB (script SQL en ligne) BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 43 / 46 Plan Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Motivation Une base de données est amenée à évoluer : I I Contraintes non implémentables à la création (e.g., deux tables qui se référencent) Nouveaux besoins ou fonctionnalités, qui nécessitent une modification de l’application et de son modèle de données Opérations fréquentes : I Ajout d’information ou modification du schéma d’une table I Mise à jour (CRUD) et suppression (CRUD) d’instance BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 45 / 46 Création d’une BD Création de tables Insertion de n-uplets Contraintes d’intégrité Évolution Commandes SQL pour l’évolution I Base de données (alter database, drop database) I Table (alter table, drop table) I Attribut (alter table) I Contrainte (alter table) I N-uplet (update, delete) http://mariadb.com/kb/en/mariadb/alter/ http://mariadb.com/kb/en/mariadb/drop/ http://mariadb.com/kb/en/mariadb/update/ http://mariadb.com/kb/en/mariadb/delete/ BDBIO - Base de données pour la bioinformatique // Modélisation relationnelle - niveau physique UCBL Lyon 1 46 / 46