Bases de données - Cahier de Prépa

Lycée Carnot
PC
2016-2017
TP no 4 d’Informatique
Bases de données
Objectif du TP
Dans de nombreux domaines, le stockage de l’information sous forme de structures plates n’est pas efficace.
Ce genre de structures a un certain nombre d’inconvénient, parmi lesquels on trouve la redondance nécessaire
de données, qui engendre un risque d’incohérences et une utilisation excessive des capacités de stockage.
L’utilisation de bases de données relationnelles permet alors d’améliorer le stockage des données, mais
nécessite cependant une réflexion plus importante lors de la conception de la structure et des méthodes
d’écriture.
Le but de ce TP est de rappeler les principaux aspects à connaître des bases de données relationnelles, vues
en première année.
1
Rappel : Théorie des bases de données relationnelles
1.1
Vocabulaire
–
–
–
–
–
–
Relation/table : ensemble de p-uplets (t1 , . . . ,tn ), noté R
Attribut/champ : nom d’une colonne dans la table noté Ai
Domaine : domaine de valeurs d’un attribut Ai noté Di
P-uplet/enregistrement : ligne dans la table
Base : plusieurs tables
Clé : sous-ensemble minimal d’attributs permettant d’identifier chaque résultat de façon unique. La clé
est dite primaire si elle sert principalement à l’identification. On utilise en général un champ spécifique,
non sémantique et immuable, souvent appelé identifiant.
– Clé étrangère : champ d’une table qui est aussi par définition une clé primaire d’une autre table
– Schéma d’une relation/table/base : la liste des attributs et des domaines d’une table/base
1.2
1.2.1
Algèbre relationnelle et langage SQL
Sélection
Opération qui permet de sélectionner des lignes vérifiant une propriété F au sein d’une table.
On note σF : R 7→ σF (R) avec F (t) = (fonction des attributs)
En SQL, SELECT * FROM table WHERE condition
1.2.2
Projection
Opération qui permet de projeter les résultats dans un sous-espace : on ne récupère qu’une partie des champs.
Q
Q
On note (Ai ,··· ,Ai ) : R 7→ (Ai ,··· ,Ai ) (R) = {(ti1 , · · · ,tik ) pour t ∈ R}
1
1
k
k
En SQL, SELECT champ1, champ2, ... FROM table
1.2.3
Renommage
On peut changer le nom d’un attribut/champ, par exemple pour améliorer la compréhension de l’opération.
On note ρ(Ai1 →A′i ,··· ,Ai →A′i )
k
1
k
En SQL, SELECT champ1 AS nouveaunomdechamp, ... FROM table
ou SELECT champ1, ... FROM table AS nouveaunomdetable
TP no 4 d’Informatique - Bases de données
1/4
Lycée Carnot
1.2.4
PC
2016-2017
Union, différence, intersection
Ces trois opérations ensemblistes correspondent à réaliser l’union/la différence/l’intersection des ensembles
de résultats obtenus.
On note
– R1 ,R2 7→ R1 ∪ R2 = {t | t ∈ R1 ou t ∈ R2 }
– R1 ,R2 7→ R1 \ R2 = {t | t ∈ R1 et t ∈
/ R2 }
– R1 ,R2 7→ R1 ∩ R2 = {t | t ∈ R1 et t ∈ R2 }
En SQL, les mots-clés UNION, MINUS et INTERSECT sont à placer respectivement entre deux requêtes SELECT.
1.2.5
Jointure
C’est la principale opération à connaître : il s’agit de la construction d’une table plus importante en nombre
d’attributs, en croisant plusieurs tables et en ne gardant que les lignes qui ont un « sens », qui vérifient une
propriété reliant les enregistrements des tables jointées. La plupart du temps, la propriété est l’identification
de clés étrangère/primaire. C’est la composition du produit cartésien et de la sélection suivant la propriété.
On note R1 ,R2 7→ R1 ⊲⊳F R2 = σF (R1 × R2 ) = {t | t(R1 ) ∈ R1 et t(R2 ) ∈ R2 et F (t) est vraie}
En SQL, SELECT * FROM table1 JOIN table2 ON condition (WHERE selection)
Très souvent, la condition est du genre table1.champ = table2.id
2
2.1
Autres rappels de SQL
Mots-clés
SELECT (champs|*) FROM table (WHERE condition) ORDER BY champ (ASC|DESC) (LIMIT n)
L’ordre des instructions est obligatoire (JOIN, ON, WHERE, ORDER, LIMIT). Par défaut, l’ordre est descendant.
Dans les conditions, on peut utiliser les mots-clés AND, OR et NOT. Il est possible de rechercher des égalités
sur des chaînes de caractère avec =, mais aussi de rechercher des sous-chaînes de caractères. Il faut alors
remplacer = par l’opérateur LIKE, et utiliser le métacaractère (joker) %.
Le mot-clé DISTINCT placé juste avant un champ de projection permet de supprimer les lignes faisant doublon
sur ce champ.
2.2
Agrégation
L’agrégation de résultats est, après la jointure, le deuxième concept important à connaître : il est possible
de regrouper des enregistrements en un seul. La syntaxe SQL est GROUP BY champ, obligatoirement à la fin
de la requête. Si les valeurs d’un champ sont différentes, l’une d’entre elle sera choisie aléatoirement, ce qui
n’est pas un comportement souhaité.
Il faut donc la plupart du temps utiliser des fonctions d’agrégation, qui vont faire un calcul sur plusieurs
enregistrements :
MIN(), MAX(), AVG(), SUM(), COUNT().
Il est possible d’utiliser une fonction d’agrégation sans , GROUP BY : automatiquement un seul résultat sera
renvoyé.
TP no 4 d’Informatique - Bases de données
2/4
Lycée Carnot
3
PC
2016-2017
Récupération des données du TP
Nous allons travailler avec une base de données de notes de colles, contenant des élèves, des matières, des
colleurs. Cette base provient en partie de cahier-de-prepa.fr. Elle est vide : il va falloir commencer par la
peupler, de façon automatique si possible.
1. Récupérer les fichiers TP4-notes-original.db, TP4-remplissage.py et sqliteman.exe. Après analyse du
fichier Python (donné en annexe), lancer l’exécution.
2. Ouvrir la base de données avec le logiciel SQLiteman (portable, libre, multi-plateforme et gratuit).
3. Vérifier que le nombre de notes entrées est correct (noter comment on fait !).
4. Vérifier que les notes de la première semaine de colles sont correctes.
4
Entraînement aux requêtes
On écrira en même temps l’expression de l’algèbre relationnelle correspondante à toutes ses requêtes.
1. Lister les élèves.
2. Rechercher les élèves qui ont les lettres « en » à la suite dans leur nom.
3. Lister les colleurs et leur matière.
4. Lister les notes mises par un colleur particulier dont on donne le nom.
5. Faire la moyenne des notes mises par ce colleur.
6. Lister chaque colleur, leur matière, leur moyenne.
7. Lister chaque élève et sa moyenne globale.
8. Lister chaque élève et sa moyenne globale, dans l’ordre des moyennes (meilleur en premier).
9. Lister chaque élève et sa moyenne dans chaque matière. Il faudra neleves × nmatieres lignes.
10. Lister les élèves ayant eu des notes supérieures à 18 au moins une fois.
11. Lister les élèves n’ayant jamais eu de note supérieure à 18.
TP no 4 d’Informatique - Bases de données
3/4
Lycée Carnot
PC
2016-2017
Annexe : script de peuplement de la table notes
import sqlite3
import random
# Connexion à la base de données (ouverture du fichier)
connexion = sqlite3.connect("TP4-notes.db")
connexion.row_factory = sqlite3.Row
c = connexion.cursor()
# Nettoyage éventuellement nécessaire si la table notes est non vide
c.execute('DELETE FROM notes')
connexion.commit()
# Récupération des matières, des semaines, des élèves
matieres, semaines, eleves = [], [], []
c.execute("SELECT * FROM matieres")
for ligne in c:
matieres.append([ligne['id'],ligne['nom']])
c.execute("SELECT id FROM semaines WHERE colle = 1")
for ligne in c:
semaines.append(ligne['id'])
c.execute("SELECT id FROM eleves")
for ligne in c:
eleves.append(ligne['id'])
# Peuplement de la table notes, matière par matière
for matiere in matieres:
print("Remplissage des notes de la matière",matiere[1],end=' ')
m = matiere[0]
# Récupération des colleurs de la matière
c.execute("SELECT * FROM colleurs WHERE matiere = ?",(m,))
colleurs = []
for ligne in c:
colleurs.append(ligne['id'])
nc = len(colleurs)
# Mise des notes : itération sur les semaines
for i in range(len(semaines)):
print('*',end='')
for j in range(len(eleves)):
# Pour les matières 1 et 2, l'élève est noté si son trinome (par ex.
# j//3) est de la même parité que les semaines, inverse sinon.
if m < 3 and (j//3)%2 != i%2 or m > 2 and (j//3)%2 == i%2:
continue
# Ajout d'une nouvelle note avec roulement des colleurs
c.execute("INSERT INTO notes (semaine, colleur, eleve, note) VALUES
(?,?,?,?)",(semaines[i], colleurs[(j//6+i//2)%nc], eleves[j],
random.randint(5,18)))
connexion.commit()
print()
connexion.close()
TP no 4 d’Informatique - Bases de données
4/4