INFO 10 Bases de données relationnelles

INFO 10 Bases de données relationnelles
Introduction au modèle relationnel et requêtes élémentaires en SQL.
10 Bases de données relationnelles ......................................................... 1
1 Les systèmes de gestion de bases de données. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1 Architecture client-serveur. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2 Architecturetrois-tiers....................................................... 4
1.3 Gestion et interrogation d’une base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2 Lemodèlerelationnel .............................................................. 4
2.1 Schémarelationnel.......................................................... 5
2.2 Trouver « le bon »modèlerelationnel ......................................... 5
3 Création et alimentation d’une base de données relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.1 Création par import de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.2 Créationex-nihilo........................................................... 10
4 L’algèbrerelationnelle.............................................................. 11
4.1 Opérations sur les relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
4.2 Groupes et fonctions d’agrégation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
4.3 Exemples de requêtes sur la base de données prenoms . . . . . . . . . . . . . . . . . . . . . . . . . 14
4.4 Exemples de requêtes sur la base de données Biblio . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5 LelangageSQL .................................................................... 17
5.1 Traduction en SQL des opérations de l’algèbre relationnelle . . . . . . . . . . . . . . . . . . . . 17
5.2 Création et alimentation d’une base de données en SQL . . . . . . . . . . . . . . . . . . . . . . . 20
5.3 Requêtes depuis une application de gestion de SGBD . . . . . . . . . . . . . . . . . . . . . . . . . . 21
5.4 Requêtes depuis un interpréteur Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

PCSI2\2014-2015 Laurent Kaczmarek
LES ordinateurs ont été initialement créés pour calculer. Mais, depuis l’invention des disques durs
en 1956, les ordinateurs permettent aussi de collecter,classer et stocker de grandes quantités
d’informations. Le terme database (base de données) est apparu en 1964 pour désigner une col-
lection d’informations partagées par différents utilisateurs d’un même réseau. Les premières utilisations
des bases de données furent militaires. Elles sont de nos jours très largement utilisées, de l’échelle la
plus petite (accès local, par les employés d’une entreprise par exemple) à la plus grande (accès global,
par n’importe quel internaute par exemple).
Deux points fondamentaux découlent des objectifs pratiques des bases de données :
.Le partage à grande échelle de données implique certaines précautions (pour garantir la pérennité
des données par exemple).
.Pour traiter un grand nombre de données, il faut également réfléchir à la meilleure manière de les
structurer afin de faciliter les algorithmes de recherche.
Partager des données
La Bibliothèque Nationale de France posséde un catalogue impressionnant d’ouvrages enregistrés dans
une base de données. Elle peut être consultée par les utilisateurs qui peuvent par exemple effectuer des
recherches thématiques : quelles sont les ouvrages disponibles écrits par tel écrivain ? quelles sont les
éditions disponibles ? quelle est la date d’acquisition de telle édition ? etc. Les bibliothécaires ont un
droit supplémentaire : modifier les données (ajouter des données, en supprimer, les altérer).
En achetant sur Amazon, un client consulte une base de donées constituée des descriptions des biens
vendus, des prix, des vendeurs, des stocks et des commentaires des acheteurs. Un achat modifie auto-
matiquement ces données (l’état du stock par exemple). Les acheteurs ont également accès aux données
et peuvent modifier certaines données (prix, stocks, etc).
Les données des stations méteorologiques sont enregistrées dans des bases de données puis traitées par
les ingénieurs de Méteo-France.
Partager des données implique des règles de précautions :
.Les données doivent être facilement accessibles par un grand nombre de clients et il faut garantir une
certaine pérennité des données.
.Afin de protéger les données, il convient de respecter les points suivants :
† Les utilisateurs ne manipulent pas directement les fichiers.
† Les utilisateurs ne s’occupent pas de la façon dont sont stockées les informations, ni où elles le sont.
.Les utilisateurs doivent pouvoir collecter, fournir ou modifier des données sans programmer. Ceci
impose l’utilisation d’un langage de description de requêtes.
Structurer des données
Les structures « plates » que nous avons jusqu’à présant étudiées ont leur limite.
.Considérons le problème suivant : créer un catalogue des œuvres exposées dans les musées de la
RMN.
.Les œuvres et les musées ont des attributs propres : nom et auteur pour les œuvres, nom et ville pour
le musée.
.On peut représenter le catalogue par une liste de musées contenant chacune une liste d’œuvres :
cat=[["Louvre","paris",[["La Joconde","De Vinci"],["Gilles","Watteau"],...]],
["Palais des beaux-arts","Lille",[["Les vieilles","Goya"],...]],...]
.Il est facile de trouver la liste des œuvres d’un musée donné au moyen d’un parcours en temps linéaire
de la liste cat.
LLG \PCSI2INFO 10 \2

PCSI2\2014-2015 Laurent Kaczmarek
.La détermination de la liste de toutes les œuvres exposées dans les musées de la RMN d’un peintre
donnée est un peu plus longue à obtenir car nécessite un parcours linéaire de cat au sein duquel il faut
parcourir linéaire la liste des œuvres de chacun des musées (ce que l’on peut réaliser au moyen de deux
boucles for imbriquées).
.La situation serait encore plus délicate dans le cas de la recherche des œuvres portant le même nom.
.La manière dont est structuré cat induit une disymétrie dans le traitement des données : l’apparte-
nance d’une œuvre à un musée est privilégiée au lien entre l’œuvre et sont auteur.
.On aurait pu bien sûr structurer inversement : une liste d’auteurs contenant une liste d’œuvre avec un
attribut musée. Dans ce cas, les difficultés sont inversées, il est plus difficile d’obtenir la liste des œuvres
d’un musée que la liste des œuvres d’un artiste.
.Les bases de données permettent de simplifier et d’améliorer l’exploration de données en les struc-
turant de manières à ne privilégier aucun lien.
Mais alors, comment structurer les données ?
Depuis les années soixante, plusieurs modèles ont été crées. Ils correspondent à des manières diffé-
rentes de structurer des données.
1. Les systèmes de gestion de bases de données
La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui consti-
tuent un système de gestion de bases de données (SGBD).
.Un SGBD doit permettre l’ajout, la modification et la recherche de données.
.Un SGBD doit masquer la représentation physique des données et assurer la cohérence et la protec-
tion des données dans le contexte d’un partage des données entre un grand nombre d’utilisateurs.
Exemple 10.1. Principaux SGBD.
Les principaux acteurs dans le secteur des SGBD commerciaux sont Oracle Corporation (Oracle), IBM
(DB2) et Mycrosoft (SQL-Server).
La réponse apportée à cette double contrainte liée au partage et à la structure des données caractérise
un SGBD.
.Les SBGD sont conçus selon l’architecture Ansi/Sparc développée par Bachman vers 1965. Elle repose
sur une division en trois niveaux : physique (là où sont stockées les données), logique (la manière dont
elles sont structurées) et externe (l’interface avec l’utilisateur).
.Un SGBD est caractérisé par le modèle de description des données qu’il supporte.
Rapide historique des modèles :
.le modèle hiérarchique, vers 1960.
.le modèle relationnel créé par Edgar F.Codd, ingénieur chez IBM, en 1970.
.le modèle objet-relationnel au début des années 90.
Le modèle relationnel est très largement utilisé de nos jours et figure au programme.
1.1. Architecture client-serveur
.Les clients accèdent directement à la base de don-
nées (ce sont le plus souvent des programmes que des
opérateurs humains, cf. les sites de vente en ligne).
.Les communications passent le plus souvent par
l’intermédiaire d’un réseau (par exemple Internet).
.L’inconvénient de cette architecture est le lien direct
des utilisateurs aux données (bas niveau de sécurité).
LLG \PCSI2INFO 10 \3

PCSI2\2014-2015 Laurent Kaczmarek
1.2. Architecture trois-tiers
La terminologie est un anglicisme, il faut comprendre architecture à trois étages (3-tier in english) :
.Un client équipé d’une interface
chargée de la présentation.
.Un serveur d’application (appelé
middleware) qui fournit la ressource,
mais en faisant appel à un autre serveur.
.Un serveur de données qui fournit au
middleware les données requises pour
répondre au client.
Cette architecture renforce la sécurité des données en supprimant le lien avec le client. Le serveur ap-
plicatif a un rôle de traducteur, de gendarme et de filtre.
1.3. Gestion et interrogation d’une base de données
La création, l’administration et l’interrogation d’une base de données de taille modeste peut se faire via
une application. Il existe de nombreuses applications MySql,PhpMyAdmin, etc. Afin d’illustrer ce cours,
nous utiliserons Sqliteman, application gratuite.
Ouvrons par exemple une base de données disponible sur le site www.data.gouv.fr, la liste des prénoms
donnés à Paris de 2004 à 2013.
Les données sont structurées en quatre colonnes : prénom,nombre,sexe et année. L’utilisateur peut
modifier et/ou interroger la base en programmant en SQL dans une des fenêtres.
Dans la pratique, il est beaucoup plus facile de modifier la base de données en utilisant les différents
boutons, on n’utilise le langage SQL que pour formuler des requêtes.
2. Le modèle relationnel
Conformément au programme, nous nous limiterons au modèle relationnel. Nous illustrerons ce para-
graphe par deux exemples :
LLG \PCSI2INFO 10 \4

PCSI2\2014-2015 Laurent Kaczmarek
.Un exemple élémentaire, la liste des prénoms donnés à Paris entre 2004 et 2013. Les données sont
authentiques et ont été trouvées sur le site www.data.gouv.fr sous la forme d’un fichier .csv qui a
ensuite été transformé en une base de donnée. Elles sont structurées en quatre colonnes : P (prénom),
Nb (nombre), S (sexe) et A (année).
.Un exemple plus complexe, la gestion par une bibliothèque des prêts d’enregistrements sur CD
d’œuvres lyriques. Dans ce cas, on se posera en plus la question de la conception d’une base de don-
née (le modèle de conception par Entités/Associations n’est pas au programme, nous nous conten-
terons d’énoncer quelques principes généraux).
2.1. Schéma relationnel
Le vocabulaire est spécifique (relation au lieu de tableau, attribut au lieu de colonne, enregistrement au
lieu de ligne, etc).
Définition 10.2. Relation, enregistrements, attributs, domaine, schéma relationnel
Une base de données est structurée en un nombre fini de relations :
.Une relation est un tableau comportant des lignes distinctes (pas de doublons) et des colonnes nom-
mées.
.Les enregistrements d’une relation sont les lignes de cette relation.
.Les attributs d’une relation sont les noms donnés aux colonnes de cette relation. Si deux relations Ret
R0ont un attribut en commun A, on les distinguera en les notant R.A et R0.A.
.Le domaine d’un attribut est l’ensemble des valeurs admissibles de cet attribut.
.Une base de données est décrite au moyen d’un schéma relationnel, ie la donnée d’un ensemble fini de
relations, chacune étant décrites par ses attributs et leurs domaines.
Exemple 10.3. La relation prenom
Par exemple, les données sur les prénoms déclarés à Paris entre 2004 et 2013 décrites ci-dessus se
concrétisent par une base de donnnée Prepar à une seule table prenom dont on trouvera un extrait
ci-dessus :
P Nb S A
Liz 5 F 2012
Lohan 9 M 2012
Lohan 9 M 2012
est un enregistrement de la relation prenom.
Les attributs de la relation prenom sont P,Nb,
S,A. Pour la relation Prepar, les domaines des
attributs P,Nb,Set Asont respectivement : l’en-
semble des chaînes de caractères, l’ensemble
des entiers naturels, l’ensemble des chaînes de
caractères et l’ensemble des entiers naturels.
Voici le schéma relationnel de prenom :
prenom
PChaîne de caractères
Nb Entier naturel
SChaîne de caractères
AEntier naturel
2.2. Trouver « le bon » modèle relationnel
Considérons une bibliothèque souhaitant créer une base de données pour gérer le prêt de CD d’opéras.
.Une bibliothèque souhaite répertorier les différents enregistrements d’œuvres lyriques sur CD mises
au prêt.
.Il faut pouvoir intéroger cette relation afin d’obtenir les réponses à des questions telles que : combien
de fois tel CD a-t-il été emprunté ? Quel est le compositeur dont les œuvres sont les plus empruntées ?
Quel est le client qui emprunte le plus sur une période donnée ? Quel est le numéro de téléphone du
dernier client ayant emprunté tel CD ? etc.
LLG \PCSI2INFO 10 \5