Table des matières
~~stoggle_buttons~~
Exercices avec les bases relationnelles
Introduction
Ces exercices introduisent les notions suivantes :
- les formes simples de requêtes SELECT.
- clé primaire ;
- clé étrangère ;
- première forme normale (1FN) ;
Pour vous aider dans les exercices, consultez les tutoriaux SQL du site developpez.com. Et notamment :
- Le site de SQLite :
Des logiciels pour créer un dictionnaire des données, dessiner des MCD et générer automatiquement les modèles logiques, physiques et les ordres SQL de création des tables. Ces deux logiciels demandent que Java (machine virtuelle) soit installée sur votre poste :
- AnalyseSI : https://launchpad.net/analysesi/+download
Voici un lien vers un document PDF intéressant qui remet en perspective tout le vocabulaire abordé Lundi :
- du niveau conceptuel (à partir de la page 10) ;
- au niveau physique (page 2 à 8) ;
- en passant par le modèle relationnel, les dépendances fonctionnelles (page 34) et toute la théorie mathématique sous-jacente au niveau des langages de requêtes :
- l'algèbre relationnelle (à partir de la page 41) ;
- les formes normales (à partir de la page 41) et vous présente des exemples de requêtes SELECT.
- Ce lien (et les autres fichiers de l'auteur) vous présente un résumé très succint et pratique du modèle relationnel, des dépendances fonctionnelles et des formes normales.
- Ces deux derniers documents vous présentent des requêtes utilisant uniquement l'algèbre relationnelle et des notions sur UML et la conception des programmes informatiques et des bases de données en mettant en oeuvre le "paradigme objet".
Création de tables
Exercice 1
Voici le MCD correspondant :
Créez la table Livre0
livre0(titre, auteur, editeur, annee_parution, isbn, prix)
| Colonne | Type |
|---|---|
| titre | varchar(100) |
| auteur | varchar(300) |
| editeur | varchar(100) |
| annee_parution | INTEGER (INT en SQLite) |
| prix | DOUBLE (REAL en SQLite) |
| isbn | varchar(20) |
Vous pouvez recréer une base ou utiliser la base existante. <spoiler|Solution>
F:\>cd __2012_2013\_PREPAS\jeudi\sqlite3\ F:\__2012_2013\_PREPAS\jeudi\sqlite3>sqlite3 base_livres.sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables sqlite>
CREATE TABLE livre0(titre VARCHAR(100) NOT NULL, isbn VARCHAR(100), auteur VARCHAR(300), annee_parution INT, prix DOUBLE, editeur VARCHAR(100));
sqlite> .tables livre0 sqlite>
</spoiler>
Insérez des données
Utilisez un script SQL avec une vingtaine d'ouvrages d'éditeurs et d'auteurs différents mais aussi avec "doublons" ou "triplés" sur les dates, éditeurs, auteurs.
Quelques données: Design Patterns: Elements of Reusable Object-Oriented Software; Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides;1994;Addison-Wesley Professional;ISBN10 : 0201634988 UML 2 en action , De l'analyse des besoins à la conception ;Pascal Roques, Franck Vallée;2007;Eyrolles;ISBN10 : 2212121040 UML en action , De l'analyse des besoins à la conception;Pascal Roques, Franck Vallée;2000;Eyrolles;ISBN-10 : 2212091273 UML pour les décideurs;Franck Vallée;2005;Eyrolles;ISBN-10 : 2212116217 <spoiler|Solution : >
INSERT INTO livre0 VALUES('UML 2 en action ,De l''analyse des besoins à la conception', '2212121040','Pascal Roques, Franck Vallée', 2007, 42.95, 'Eyrolles');
Lorsqu'on désire ne renseigner que quelques colonnes, on indique leurs noms à la suite du nom de la table
INSERT INTO livre0(titre,isbn) VALUES('Design Patterns: Elements of Reusable Object-Oriented Software','0201634988');
</spoiler>
Ajoutez un livre ou deux livres dont vous ne connaissez que le titre et l'auteur. <spoiler|Solution : >
INSERT INTO livre0(titre,auteur) VALUES('Le Seigneur des Anneaux','Tolkien'); INSERT INTO livre0(titre,auteur) VALUES('Cryptonomicon','Neal Stephenson');
</spoiler>
Exécutez des requêtes SELECT
Projection
N'afficher que quelques colonnes d'une table : titre et auteur par exemple. <spoiler|Solution : >
SELECT titre, auteur FROM livre0;
</spoiler>
Sélection (restriction)
N'afficher que certaines lignes d'une requête (table ou jointure) : liste des titres de 2007. <spoiler|Solution >
- Afficher toutes les colonnes (symbole *) d'une sélection
SELECT * FROM livre0 WHERE annee_parution=2007;
- Même requête de sélection avec une projection
SELECT titre, auteur, editeur FROM livre0 WHERE annee_parution=2007;
</spoiler> Affichez les livres dont l'éditeur ou l'année de parution ou le prix n'ont pas été renseignés. <spoiler|Solution >
SELECT * FROM livre0 WHERE editeur IS NULL; Le Seigneur des Anneaux||Tolkien|||SELECT titre, auteur FROM livre0 WHERE annee_parution IS NULL; SELECT titre, auteur FROM livre0 WHERE prix IS NULL;
</spoiler>
Opérateurs logiques
AND, OR, NOT, LIKE, …
Exemples de requêtes :
- liste de tous les livres de l'éditeur
Eyrolles, parus en 2007 ; - les livres dont le titre contient
Python; - les livres de l'éditeur Eyrolles dont le titre contient Python,
- nom des auteurs qui ont publié en 2007.
- liste des livres dont l'éditeur ou l'année de parution ou le prix n'ont pas été renseignés.
<spoiler|Solution >
SELECT titre, auteur FROM livre0 WHERE editeur IS NULL OR annee_parution IS NULL OR prix IS NULL;
</spoiler>
- …
Opérations sur les regroupements
- Afficher le nombre total de livres ;
- Afficher le nombre de livres par éditeur ;
- Afficher le prix moyen d'un livre par éditeur.
Questions à propos du modèle relationnel
Cette table est elle en 1ère Forme Normale, dans les différents cas évoqués ci-dessous :
- si dans votre sujet, on vous dit que, dans le champ auteur, il ne peut y avoir qu'un seul nom ?
- sans précisions particulières sur le sujet traité ?
- si dans votre sujet, on vous dit que, dans le champ auteur, il peut y avoir plusieurs noms d'auteur ?
Solutions : que devez vous faire dans chacun de ces 3 cas ?
Exercice 2
Voici le MCD correspondant : 
Il s'agit, ici de vous familiariser avec les notions de clés primaires et étrangères et de jointure entre tables dans les requêtes SQL.
Créez les tables
- Créez la table editeur :
editeur(num_editeur, nom_editeur)
Attention, il s'agit de la colonne : num_editeur.
- Créez la table livre :
livre(num, titre, auteur, fk_num_editeur#, annee_parution, prix)
num signifie que la colonne est clé primaire.
fk_num_editeur# : Le caractère dièse (#) signifie que fk_num_editeur (le numéro éditeur) est une clé étrangère.
Vous chercherez dans la documentation de SQLite3 comment déclarer une clé étrangère : http://www.sqlite.org/foreignkeys.html et trouverez ici : des liens vers de la documentation SQLite.
Remarque : SQLite ne respecte pas entièrement le santard SQL92 : http://stackoverflow.com/questions/1884818/how-do-i-add-a-foreign-key-to-an-existing-sqlite-3-6-21-table
| Colonne | Type | Commentaires |
|---|---|---|
| num_editeur | type compteur auto-incrémenté | |
| num | type compteur auto-incrémenté | |
| titre | varchar(100) NOT NULL | valeur obligatoire |
| nom_editeur | varchar(100) NOT NULL | valeur obligatoire |
| auteur | varchar(100) | |
| fk_num_editeur | entier | INTEGER ou INT (en Sqlite3) |
| annee_parution | entier | INTEGER ou INT (en Sqlite3) |
| prix | réel | DOUBLE |
<spoiler |Solution>
F:\>cd __2012_2013\_PREPAS\jeudi\sqlite3\ F:\__2012_2013\_PREPAS\jeudi\sqlite3>sqlite3 base_livres.sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables editeur livre sqlite> .schema
Affichage de
CREATE TABLE editeur( num_editeur INTEGER PRIMARY KEY , nom_editeur VARCHAR(100) NOT NULL ); CREATE TABLE livre(num_livre INTEGER PRIMARY KEY , titre VARCHAR(100) NOT NULL, isbn VARCHAR(100) NOT NULL, auteur VARCHAR(300), annee_parution INT, prix DOUBLE, fk_num_editeur INTEGER, FOREIGN KEY (fk_num_editeur) REFERENCES editeur(num_editeur)); sqlite>
</spoiler>
Insérez des lignes dans les tables
Avec un script SQL
Vous pouvez copier et modifier votre script de l'exercice précédent.
Attention : veillez à respecter la cohérence entre les tables.
Vous pouvez, aussi, faire un INSERT à partir d'un SELECT sur la table livre0.
INSERT …. INTO table1 .. SELECT ….. FROM table2 WHERE …..;
\\Attention : veillez à respecter la cohérence entre les tables. Voir ici : http://www.sqlite.org/lang_insert.html
Remarque pour les clés étrangères : pour SQLIte, il est impératif de préciser en début de session :
sqlite>PRAGMA foreign_keys = ON;
Sinon les contraintes de clés étrangères ne seront pas vérifiées.
<spoiler | Solution>
SELECT * INTO livre FROM livre0; ou CREATE TABLE livre AS SELECT * FROM livre0
</spoiler>
<spoiler | Un exemple plus complexe >
sqlite> SELECT * FROM livre; 1|Python 2|222222|BBBBB|2007|22.225|2 2|Python et PyQT|444444|DDDDD|2007|22.22|2 3|Python 3|333333|CCCCC|2010|33.33|1 sqlite> SELECT * FROM livre0; UML 2 en action ,De l'analyse des besoins à la conception|2212121040|Pascal Roques, Franck Vallée|2007|42.95|Eyrolles Le Seigneur des Anneaux||Tolkien||| sqlite> INSERT INTO livre0(titre,auteur) VALUES('Cryptonomicon','Neal Stephenson'); sqlite> insert into livre0 select titre, isbn, auteur, annee_parution, prix, nom_editeur f rom livre INNER JOIN editeur ON editeur.num_editeur = livre.fk_num_editeur; sqlite> select * from livre0; UML 2 en action ,De l'analyse des besoins à la conception|2212121040|Pascal Roques, Franck Vallée|2007|42.95|Eyrolles Le Seigneur des Anneaux||Tolkien||| Cryptonomicon||Neal Stephenson||| Python 2|222222|BBBBB|2007|22.225|Dunod Python et PyQT|444444|DDDDD|2007|22.22|Dunod Python 3|333333|CCCCC|2010|33.33|Eyrolles sqlite>
</spoiler>
En mode interactif
Utilisez directement l'ordre INSERT en "ligne de commandes". Evitez les accents pour pouvoir réutiliser votre base sous Windows et en Python.
Essayez de :
- provoquer des erreurs de cohérence entre les tables ;
- respecter l'intégrité de la base en insérant des données valides.
Requêtes avec jointures
Effectuez les mêmes requêtes que dans l'exercice 1, mais en prenant en compte "l'aspect jointure". Conseil : ajoutez un éditeur qui n'a pas encore de livre stocké dans votre bibliothèque et essayez des jointures externes : gauche, droite et complète.
Requêtes : Affichez le nombre de livres par éditeur : afficher le nom de l'éditeur et le nombre de lignes. Affichez le prix moyen d'un livre par éditeur : afficher le nom de l'éditeur et le prix moyen.
Exercice 3
Créez une table auteur et liez la à la table livre.
Posez les contraintes d'intégrité (clés primaires et étrangères).
Insérez des données qui respectent et/ou ne respectent pas les contraintes d'intégrité.
Attention : le MCD fait apparaître une association (un lien) entre ces deux tables, mais les cardinalités sont (1,N) de chaque côté.
Vous pouvez maintenant passer à l'Écriture de requêtes sur une base existante.