TP1

Informatique
TP1 Chapitre 5 : CREATION D'UNE BASE DE DONNEES
Objectifs :
- Prendre en main le SGBD SQLite, et l'interface SQLite Manager de Firefox ;
- Manipuler le langage de description de données SQL pour la création d'une petite base très simple ;
- Insérer quelques données et illustrer le mécanisme de vérification de contraintes d'intégrité du SGBD.
I/ CREATION D'UNE BASE DE DONNEES DE 3 RELATIONS
1/ Lancement
Dans Firefox, taper "SQLite Manager" dans le barre de recherche.
Installer le module.
Redémarrer, puis lancer "Outils / SQLite Manager".
2/ Création de la base
Faire "Nouvelle base de données" et la nommer "Musique_Votre_Nom".
Cliquez sur l'onglet "Exécuter le SQL" : on vous propose un champ de texte pour entrer vos requêtes SQL et un bouton pour les
exécuter.
Créer la base de données "Musique" en SQL selon le schéma relationnel suivant :
Artistes(nom, nationalite)
Albums(titre, artiste, annee, style)
en définissant artiste comme clé étrangère pour référencer Artistes(nom)
Pistes(titreAlbum, artisteAlbum, numeroPiste, titrePiste) en définissant (titreAlbum, artisteAlbum) comme clé
étrangère pour référencer Albums(titre, artiste)
Rq :
- en cas d'erreur sur une table, le plus simple est de la supprimer avant de la recréer.
Vérifier dans le panneau latéral que les tables sont bien créées.
II/ ALIMENTATION DE LA BASE : INSERTION DE DONNEES ET VERIFICATION DE L'INTEGRITE
Insérez un artiste "A" de votre choix (par exemple Twenty One Pilots) avec sa nationalité (Etats-Unis) dans la table Artistes.
Cliquez sur l'onglet "Parcourir & rechercher" pour vérifier que le n-uplet a bien été inséré dans la table.
Renouvelez la même opération avec le même artiste, mais une nationalité différente. Que se passe-t-il ?
Retenter l'opération avec "Parcourir & rechercher / Ajouter une nouvelle entrée". Pourquoi est-ce impossible ? (Comprendre le
message d'erreur).
Activez le support des clefs étrangères en exécutant la requête SQL suivante : PRAGMA foreign_keys = ON
Insérez dans la table Albums un album de votre choix de l'artiste "A" (Par exemple : Blurryface, Twenty One Pilots, 2015, Electro
Alternatif). Cliquez sur l'onglet "Parcourir & Rechercher" pour vérifier que le n-uplet a bien été inséré dans la table.
Renouvelez la même opération avec un artiste qui n'existe pas dans la relation Artistes. Que se passe-t-il ? Retenter l'opération avec
"Parcourir & rechercher / Ajouter une nouvelle entrée". Pourquoi est-ce impossible ? (Comprendre le message d'erreur).
Essayez de supprimer dans la table Artistes l'artiste "A". Que se passe-t-il ? (Comprendre le message d'erreur).
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-1-
Informatique
III/ ALIMENTATION DE LA BASE PAR IMPORTATION DE DONNEES
Pour alimenter la table Pistes, il est trop fastidieux de saisir les données une à une. On peut alors constituer un fichier .csv (coma
separated values) à partir d'une source quelconque (internet) avec le bloc-notes, puis l'importer avec SQLite Manager.
er
Constituer le fichier .csv du 1 album apparaissant dans votre base (essayer de trouver des méthodes efficaces !), puis l'importer
dans la table Pistes. Attention aux espaces en trop dans les chaînes de caractères.
Schéma relationnel en exemple :
ANNEXE : REQUETES SQL DE CREATION / ALIMENTATION
D'UNE BASE DE DONNEES
Notes(nom, prenom, cours, note)
Eleves(nom, prenom, email, classe)
Cours(intitule, …)
Création d'une table
• CREATE TABLE permet de fabriquer une table, avec la description précise de tous ses champs (type, valeur par
défaut, …).
Voici la requête SQL de création d'une table Notes(nom, prenom, cours, note) :
CREATE TABLE Notes (nom VARCHAR (80), prenom VARCHAR (80), cours VARCHAR (80), note INT,
PRIMARY KEY(nom, prenom, cours),
FOREIGN KEY(nom, prenom ) REFERENCES Eleves(nom, prenom),
FOREIGN KEY(cours) REFERENCES Cours(intitule))
• Les types de données (pour les attributs) sont :
CHAR(n), VARCHAR(n) : chaîne de caractères fixe ou variable, de longueur maximale n ;
INT ou INTEGER : entiers
FLOAT : réels (flottants)
Rq : On évite les accents comme d'habitude.
DATE, TIME, TIMESTAMP : "estampilles" temporelles.
• Les clés sont définies de la manière suivante :
PRIMARY KEY définit la clé primaire (1 seule par table !), FOREIGN KEY définit une clé étrangère, qui sert à relier 2
tables entre elles (ici, (nom, prénom) est une clé étrangère qui relie la table Notes et la table Eleves, cours est une clé
étrangère qui relie la table Notes et la table Cours).
Destruction d'une table
DROP TABLE R supprime la table R.
Ajout d'une colonne
ALTER TABLE R(Ai type_de_Ai) ajoute l'attribut Ai.
On ne peut pas supprimer directement une colonne, il faut définir une nouvelle table.
Alimentation d'une table
• On insère un n-uplet de valeurs des attributs A1, A2, … de la manière suivante :
INSERT INTO Nom_de_la_table VALUES (valeur_de_A1, valeur_de_A2, …)
Par exemple :
INSERT INTO Notes VALUES ("Vador ", "Dark ", "Pilotage", 20)
-- L'élève Dark Vador a eu 20/20 en pilotage
• On supprime un n-uplet avec :
DELETE FROM Nom_de_la_table WHERE A1 = valeur_de_A1 AND A2 = valeur_de_A2
Par exemple :
DELETE FROM Notes WHERE nom = "Vador" AND prenom = "Dark"
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-2-
-- supprime toutes les notes de Dark Vador