\chapter{Bases de données}

\enlargethispage*{\baselineskip}
\begin{exointro}{\null}
Une base de données a la structure relationnelle suivante:

\Par

{\ttfamily

famille (id\_prenom,prenom)

genres (id\_genre,designation)

livres (id\_livre,titre,auteur,id\_genre,id\_prenom)
}

\begin{itemize}
\item La table \og famille \fg{} désigne l'ensemble des membres de la famille Numos;

\item La table \og genres \fg{} représente l'ensemble des genres des livres que possède la famille (policier, science-fiction, etc.);

\item La table \og livres \fg{} désigne l'ensemble des livres de la famille Numos.\linebreak L'attribut \og id\_prenom \fg{} sera vide si le livre n'est pas en la possession d'un membre de la famille.
\end{itemize}

Les attributs des tables sont explicites.

\begin{questions}
\item Pour chaque table, quel attribut peut être une clé primaire ?
\item Pour la table \og livres \fg, existe-t-il un attribut qui peut être défini comme clé étrangère ?

\item La famille Numos est composée de Mathilde, François, Nathan,\linebreak Ludovic et Jeanne.

\'Ecrire une requête SQL permettant d'insérer une de ces informations dans la table \og famille \fg.

\item On suppose qu'un membre de la famille peut être en possession de\linebreak plusieurs livres en même temps.

\'Ecrire une requête SQL permettant de trouver tous les livres en\linebreak possession de Mathilde.

\item \'Ecrire une requête SQL permettant de calculer le nombre total de livres indisponibles.
\end{questions}
\end{exointro}

\newpage

\begin{problematique}
Comment stocker, manipuler et partager un volume d'informations toujours plus important ?
\end{problematique}

\section{Systèmes de Gestion de Bases de Données (SGBD)}

\subsection{Introduction}

\index{Base de données!SGBD}
\begin{defin}
Un Système de Gestion de Bases de Données (SGBD) est un outil informatique permettant la sauvegarde, l'interrogation, la recherche et la mise en forme de données.
\end{defin}

Un SGBD est donc un ensemble de logiciels systèmes qui permettent aux programmateurs d'insérer, de modifier et de rechercher des données spécifiques dans une grande masse d'informations partagées par plusieurs utilisateurs.

Ces informations sont généralement enregistrées sur des disques magnétiques (par exemple, des serveurs).

Un SGBD donne l'impression à chaque utilisateur qu'il est le seul à travailler avec les données.

Oracle Database, 4D, Microsoft SQL Server, SQLite ou MySQL sont des exemples de SGBD très répandus. SQLite et MySQL sont des logiciels libres (open source), et sont par conséquent très utilisés, aussi bien par le grand public que par les professionnels.

\subsection{Fonctions d'un SGBD}

En plus des fonctions primaires citées dans la définition précédente, un SGBD:
\begin{itemize}
\item assure le partage des données;
\item vérifie qu'une opération s'effectue entièrement ou pas du tout : c'est ce que l'on nomme l'\textit{atomicité};
\item protège les données contre tout incident (détérioration, suppression accidentelle, etc.);
\item optimise les performances (temps de recherche minimisé par exemple).
\end{itemize}

Les SGBD permettent la description des données (définition des types par des noms, formats, ...) de manière séparée de leur utilisation (mise à jour et recherche). Ils permettent aussi de retrouver les caractéristiques d'un type de données à partir de son nom (par exemple, comment est décrit un produit).

\newpage

\subsection{Les différents modèles de SGBD}

Il existe cinq modèles de SGBD :

\begin{itemize}
\item \textit{le modèle hiérarchique}:  les données sont classées hiérarchiquement, selon une arborescence descendante (arbre). Ce modèle utilise des pointeurs entre les\linebreak différents enregistrements. Il s'agit du premier modèle de SGBD.
\Par
\begin{figure}
\begin{center}
\includegraphics{225_06-sgbd-hierarchique}
\figcaption{Modèle hiérarchique d'un SGBD}
\end{center}
\end{figure}

\Par

\item \textit{le modèle réseau} : ce modèle ressemble au modèle hiérarchique à ceci près qu'il n'est plus nécessairement descendant.
\Par
\begin{figure}
\begin{center}
\includegraphics{225_06-sgbd-reseau}
\figcaption{Modèle réseau d'un SGBD}
\end{center}
\end{figure}

\Par

\item \textit{le modèle relationnel (SGBDR)} : les données sont enregistrées dans des tableaux à deux dimensions (lignes et colonnes);

\item \textit{le modèle déductif} : similaire au modèle relationnel, mais la manipulation des tables se fait différemment (nous ne donnerons pas de détails sur les calculs utilisés car hors programme);

\item \textit{le modèle objet (SGBDO)}: les données sont stockées sous forme de classes.
\end{itemize}


Depuis la fin des années 1990, le modèle relationnel est le plus répandu (environ trois quarts des bases de données utilisent ce modèle). Dans la suite de cet ouvrage, nous ne nous intéresserons qu'à ce modèle.


\section{Le modèle relationnel}

\subsection{Historique}

Le modèle relationnel a été imaginé dans les années 1970 par l'informaticien\linebreak britannique Edgar Frank Codd. Avant cela, les modèles de bases de données ne permettaient pas de décrire de façon satisfaisante les relations entre deux données à l'aide de pointeurs logiques. Dix années de recherches ont été nécessaires avant de publier l'article \og A Relational Model of Data for Large Shared Data Banks \fg (\textit{un modèle de données relationnel pour de grandes banques de données\linebreak partagées}), CACM 13, No. 6, June 1970.

\subsection{Représentations}

Comme nous l'avons dit dans la section précédente, le modèle relationnel consiste à représenter les données dans des tableaux, que l'on appelle \textit{tables}. Chaque table est une sorte de classe, qui admet donc des attributs.

\Par

Prenons l'exemple d'un site internet, qui demande à ses utilisateurs trois données afin de pouvoir leur créer un compte : nom, prénom et email. On peut alors imaginer une classe \textit{user}, représentant un utilisateur ou une utilisatrice, classe comportant les trois attributs : surname, name et email. Cette classe est alors représentée par la table :

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{user}\\
\tabulartitle\hline
surname & name & email\\
\hline
\end{tabular}
\tabcaption{Structure de la table représentant les utilisateurs}
\end{center}
\end{figure}

\Par

Une fois la structure de la table connue, nous pouvons y insérer les données, par exemple:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{user}\\
\tabulartitle\hline
surname & name & email\\
\hline
Dupont 	& Jean & jean.dupont@free.fr\\
\hline
Durand & Anne & anne.dur@orange.fr\\
\hline
$\vdots$ & $\vdots$ & $\vdots$\\
\hline
\end{tabular}
\tabcaption{Insertion de données dans la table représentant les utilisateurs}
\label{table-1}
\end{center}
\end{figure}

\subsection{Définitions}

\begin{defins}

\begin{itemize}
\item Dans une table, chaque ligne constitue un \textit{enregistrement}.
\index{Base de données!Enregistrement}

\item Les \textit{attributs} d'une table sont les noms de ses colonnes.
\index{Base de données!Attributs}

\item Le \textit{degré} d'une table est le nombre de ses attributs.
\index{Base de données!Degré d'une table}

\item Le \textit{domaine} d'un attribut est son type : entier, flottant, chaîne de caractères, booléen, ...
\index{Base de données!Domaine d'un attribut}

\item La \textit{clé primaire} d'une table est l'attribut qui permet d'identifier de manière unique (sans aucun risque de doublon) un enregistrement de cette table.
\index{Base de données!Clé primaire}

\item Une \textit{clé étrangère} est un attribut d'une table qui fait référence à la clé primaire d'une autre table. Une clé étrangère permet de mettre en relation un enregistrement d'une table (appelée \textit{table fille}) qui la contient avec un enregistrement de la table référencée (appelée \textit{table parent}).
\index{Base de données!Clé étrangère}
\end{itemize}

\end{defins}

\begin{Exemple}
reprenons la table \ref{table-1} précédente et ajoutons-y une clé primaire \textit{id}:
\Par
\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zc|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{user}\\
\tabulartitle\hline
id & surname & name & email\\
\hline
1 & Dupont 	& Jean & jean.dupont@free.fr\\
\hline
2 & Durand & Anne & anne.dur@orange.fr\\
\hline
$\vdots$ & $\vdots$ & $\vdots$ & $\vdots$\\
\hline
\end{tabular}
\tabcaption{Table avec clé primaire}
\end{center}
\end{figure}
\Par
Les attributs de cette table sont donc : \textit{id}, \textit{surname}, \textit{name} et \textit{email}.

Le degré de la table est donc égal à 4 car il y a quatre attributs.

Le domaine de l'attribut \textit{id} est : entier, et celui des autres attributs est : chaîne de caractères.

\Par

Imaginons maintenant que ce site internet propose plusieurs abonnements : \og Free \fg, \og Basic \fg{} et \og Premium \fg.

On peut alors créer une seconde table nommée \og abonnement \fg{}  représentant les abonnements:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*2{Zc|}}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{abonnement}\\
\tabulartitle\hline
id\_abo & type \\
\hline
1 & Free \\
\hline
2 & Basic \\
\hline
3 & Premium\\
\hline
\end{tabular}
\tabcaption{Table des abonnements}
\end{center}
\end{figure}

\Break

On peut alors mettre en relation les tables \og user \fg{} et \og abonnement \fg{} en insérant une clé étrangère dans la première table:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*4{Zc|}}
\tabulartitle\hline
\multicolumn{5}{|Zc|}{user}\\
\tabulartitle\hline
id & surname & name & email & id\_abo\\
\hline
1 & Dupont 	& Jean & jean.dupont@free.fr & 3\\
\hline
2 & Durand & Anne & anne.dur@orange.fr & 1\\
\hline
$\vdots$ & $\vdots$ & $\vdots$ & $\vdots$ & \\
\hline
\end{tabular}
\tabcaption{Table des utilisateurs avec clé étrangère}
\end{center}
\end{figure}
\Par
On met ici en relation les deux tables en considérant que l'attribut \textit{id}\_\textit{abo} de la table \og user \fg{} correspond à l'attribut \textit{id}\_\textit{abo} de la table \og abonnement \fg.

Ainsi, si l'on regarde le premier enregistrement de la table \og user \fg, on peut voir que Jean Dupont a souscrit à un abonnement Premium, c'est-à-dire à l'enregistrement de la table \og abonnement \fg{}  dont l'attribut \textit{id}\_\textit{abo} est 3.
\end{Exemple}

\subsection{Schématisation}

Pour schématiser la relation qu'il peut y avoir entre deux tables, on peut utiliser la représentation suivante (en reprenant les deux tables de l'exemple précédent):

\Par

\begin{figure}
\begin{center}
\includegraphics{228_6-relation}
\figcaption{Schématisation de la relation entre deux tables}
\end{center}
\end{figure}

\Par

La relation entre les deux tables est indiquée par un trait qui les lie.

Dans chacune des tables, on peut indiquer la présence d'une clé primaire en précédant l'attribut d'un astérisque (*), et la présence d'une clé étrangère en précédant l'attribut d'un dièse (\#). 

On peut aussi, pour plus de clarté, séparer les clés des autres attributs.

\begin{defin}
Une \textit{base de données} est un ensemble de tables, dont certaines peuvent être mises en relation.
\end{defin}

\newpage

\section{Algèbre relationnelle}

\index{Base de données!Algèbre relationnelle}

\subsection{Opérateurs ensemblistes}

L'idée ici est de reprendre ce qui se fait en mathématiques, et plus précisément en \textit{théorie des ensembles}.


\subsubsection{Union}
\index{Base de données!Algèbre relationnelle!Union}

\vspace*{-1em}
\begin{defin}
On considère deux tables $T_1$ et $T_2$ d'une base de données.

L'\textit{union} des deux tables $T_1$ et $T_2$ est l'ensemble des éléments qui sont dans $T_1$ ou dans $T_2$ :
\[
T_1 \cup T_2 = \{ e \in T_1 \text{ ou } e \in T_2 \}.
\]
\end{defin}

\begin{Exemple}
nous avons représenté ci-dessous deux tables $T_1$ et $T_2$ ainsi que leur union.
\Par
\begin{figure}
\begin{center}
\begin{tabular}{|Zc|}
\tabulartitle\hline
$T_1$\\
\tabulartitle\hline
nom \\
\hline
Dupont \\
\hline
Durand \\
\hline
Dufon \\
\hline
Dupain\\
\hline
\end{tabular}
\quad
\begin{tabular}{|Zc|}
\tabulartitle\hline
$T_2$\\
\tabulartitle\hline
nom \\
\hline
Dugont \\
\hline
Dufon \\
\hline
Dulong \\
\hline
Dupont\\
\hline
\end{tabular}
\quad
\begin{tabular}{|Zc|}
\tabulartitle\hline
$T_1 \cup T_2$\\
\tabulartitle\hline
nom \\
\hline
Dupont \\
\hline
Durand \\
\hline
Dufon \\
\hline
Dupain\\
\hline
Dugont\\
\hline
Dulong\\
\hline
\end{tabular}
\end{center}
\tabcaption{Deux tables et leur union}\label{table:deux-tables}
\end{figure}
\end{Exemple}


\subsubsection{Intersection}

\index{Base de données!Algèbre relationnelle!Intersection}

\vspace*{-1em}
\begin{defin}
On considère deux tables $T_1$ et $T_2$ d'une base de données.

L'\textit{intersection} des deux tables $T_1$ et $T_2$ est l'ensemble des éléments qui sont dans $T_1$ et dans $T_2$ :
\[
T_1 \cap T_2 = \{ e \in T_1 \text{ et } e\in T_2 \}.
\]
\end{defin}


\begin{minipage}{\dimexpr\linewidth-2cm}
\begin{Exemple}
en reprenant les tables $T_1$ et $T_2$ de l'exemple précédent (tableau \ref{table:deux-tables}), l'intersection est la table ci-contre:

\vspace*{1cm}~
\end{Exemple}
\end{minipage}
\begin{minipage}{2cm}
\begin{flushright}
\begin{tabular}{|Zc|}
\tabulartitle\hline
$T_1 \cap T_2$\\
\tabulartitle\hline
nom \\
\hline
Dupont \\
\hline
Dufon \\
\hline
\end{tabular}
\end{flushright}
\end{minipage}

\subsubsection{Différence}

\index{Base de données!Algèbre relationnelle!Différence}

\vspace*{-1em}
\begin{defin}
On considère deux tables $T_1$ et $T_2$ d'une base de données.

La \textit{différence} $T_1-T_2$ est l'ensemble des éléments qui sont dans $T_1$ mais pas dans $T_2$ :
\[
T_1 - T_2 = \{ e \in T_1 \text{ et } e\not\in T_2 \}.
\]
\end{defin}

\begin{Exemple}
en reprenant les tables $T_1$ et $T_2$ du tableau \ref{table:deux-tables} page \pageref{table:deux-tables}, la différence est la table suivante:

\Par

\begin{center}
\begin{tabular}{|Zc|}
\tabulartitle\hline
$T_1 - T_2$\\
\tabulartitle\hline
nom \\
\hline
\hline
Durand \\
\hline
Dupain\\
\hline
\end{tabular}
\end{center}
\end{Exemple}

\subsubsection{Produit cartésien}
\index{Base de données!Algèbre relationnelle!Produit cartésien}

\vspace*{-1em}
\begin{defin}
On considère deux tables $T_1$ et $T_2$ d'une base de données.

Le \textit{produit cartésien} $T_1 \times T_2$ est l'ensemble:
\[
T_1 \times T_2 = \big\{ (e_1,e_2)~\vert~ e_1 \in T_1 \text{ et } e_2 \in T_2 \big\}.
\]
\end{defin}

\begin{Exemple}
si l'on considère les deux tables suivantes,

\Par

\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\begin{tabular}{|L|Zl|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{$T_1$}\\
\tabulartitle\hline
id & surname \\
\hline
1 & Dupont \\
\hline
2 & Durand \\
\hline
\end{tabular}
\tabcaption{Table 1}
\end{center}
\end{figure}
\end{minipage}
\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\begin{tabular}{|L|Zl|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{$T_2$}\\
\tabulartitle\hline
stockage & price \\
\hline
201 & 15 \\
\hline
41 & 7 \\
\hline
\end{tabular}
\tabcaption{Table 2}
\end{center}
\end{figure}
\end{minipage}

\Par\Par

le produit cartésien de ces deux tables est:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zl|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{$T_1 \times T_2$}\\
\tabulartitle\hline
id & surname & stockage & price \\
\hline
1 & Dupont & 201 & 15 \\
\hline
1 & Dupont & 41 & 7\\
\hline
2 & Durand & 201 & 15\\
\hline
2 & Durand & 41 & 7 \\
\hline
\end{tabular}
\tabcaption{Produit cartésien}
\end{center}
\end{figure}
\end{Exemple}

\subsection{Opérations}

\subsubsection{Sélection}

\begin{defin}
On considère une table $T$.

La \textit{sélection} est l'opération qui consiste à ne retenir que les enregistrements de $T$ qui vérifient une condition donnée.
\end{defin}


\begin{Exemple}
étant donnée la table suivante,

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zl|}}
\tabulartitle\hline
id & surname & name & genre \\
\hline
1 & Dupont & Jean & Homme \\
\hline
2 & Dupont & Jeanne & Femme\\
\hline
3 & Durand & Camille & Femme\\
\hline
4 & Dufon & Pierre & Homme \\
\hline
\end{tabular}
\tabcaption{Table d'utilisateurs}
\label{table:users}
\end{center}
\end{figure}

\Par

on ne souhaite que les enregistrements dont l'attribut \og genre \fg{}  est \og Homme \fg. 

Le résultat est alors :

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zl|}}
\tabulartitle\hline
id & surname & name & genre \\
\hline
1 & Dupont & Jean & Homme \\
\hline
4 & Dufon & Pierre & Homme \\
\hline
\end{tabular}
\tabcaption{Résultat de la requête : \og genre =  Homme \fg}
\end{center}
\end{figure}
\end{Exemple}

\subsubsection{Projection}

\index{Base de données!Projection}
\vspace*{-1em}
\begin{defin}
On considère une table $T$.

La \textit{projection} est l'opération qui consiste à ne retenir que certains attributs de $T$.
\end{defin}

\begin{Exemple}
reprenons la table \ref{table:users} de l'exemple précédent. La projection de cette table sur les attributs \og id \fg{} et \og name \fg{} est :

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|Zl|}
\tabulartitle\hline
id  & name  \\
\hline
1  & Jean  \\
\hline
2  & Jeanne \\
\hline
3  & Camille \\
\hline
4  & Pierre  \\
\hline
\end{tabular}
\end{center}
\tabcaption{Projection de la table sur \og id \fg{} et \og name \fg{}  }
\end{figure}
\end{Exemple}


\subsubsection{Jointures}
\index{Base de données!Jointures}

Une jointure permet de créer une liaison entre deux ou plusieurs tables pour pouvoir récupérer un résultat bien précis. Le résultat est en quelque sorte une fusion virtuelle de plusieurs tables. Chaque enregistrement de chaque table est retourné dans le résultat en fonction de la jointure utilisée... car il existe plusieurs types de jointures.

\begin{defin}[jointure interne]
Soient $T_1$ et $T_2$ deux tables. La \textit{jointure interne} de ces deux tables sur un attribut donné $A$ est une table formée en ne prenant que les enregistrements de $T_1$ et $T_2$ qui ont une valeur de $A$ commune.
\end{defin}


On peut représenter cette jointure par le schéma suivant:

\Par

\begin{figure}
\begin{center}
\includegraphics{232_6-crs-jointure-interne}
\figcaption{Schématisation d'une jointure interne}
\end{center}
\end{figure}

\begin{Exemple}
en considérant les deux tables suivantes,

\Par

\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|C|*3{Zc|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{user}\\
\tabulartitle\hline
id & id\_town & surname & name\\
\hline
1 & 1 & Dupont & Jean\\
\hline
2 & 2 & Durand & Agathe\\
\hline
3 & 1 & Dufon & Jérôme\\
\hline
\end{tabular}
\tabcaption{Table $T_1$}
\label{jointure-table1}
\end{center}
\end{figure}
\end{minipage}
\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|C|Zc|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{town}\\
\tabulartitle\hline
id\_town & name\_town \\
\hline
1 & Bordeaux \\
\hline
3 & Paris \\
\hline
\end{tabular}
\tabcaption{Table $T_2$}
\label{jointure-table2}
\end{center}
\end{figure}
\end{minipage}

\Par\Par

leur jointure sur l'attribut \og id\_town \fg{} est:

\Par

\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|L|*3{Zc|}|*2{Zc|}}
\tabulartitle\hline
id & id\_town & surname & name & id\_town & name\_town\\
\hline
1 & 1 & Dupont & Jean & 1 & Bordeaux\\
\hline
3 & 1 & Dufon & Jérôme & 1 & Bordeaux\\
\hline
\end{tabular}
\tabcaption{Jointure interne de $T_1$ et $T_2$}
\end{center}
\end{figure}
\end{Exemple}

\newpage

\begin{defin}[jointure externe gauche]
\'Etant données deux tables $T_1$ et $T_2$, la \textit{jointure externe gauche} de $T_1$ avec $T_2$ est une table réunissant
\begin{itemize}
\item les enregistrements de $T_1$ et $T_2$ qui ont une valeur de $A$ commune (comme pour une jointure interne);
\item les enregistrements de $T_1$ (table de gauche) pour lesquels l'attribut $A$ de $T_1$ ne correspond avec aucun attribut $A$ de $T_2$.
\end{itemize}
\end{defin}

\begin{defin}[jointure externe droite]
\'Etant données deux tables $T_1$ et $T_2$, la \textit{jointure externe gauche} de $T_1$ avec $T_2$ est une table réunissant
\begin{itemize}
\item les enregistrements de $T_1$ et $T_2$ qui ont une valeur de $A$ commune (comme pour une jointure interne);
\item les enregistrements de $T_2$ (table de droite) pour lesquels l'attribut $A$ de $T_2$ ne correspond avec aucun attribut $A$ de $T_1$.
\end{itemize}
\end{defin}



\begin{figure}
\begin{center}
\includegraphics{233_6-crs-jointures-externes}
\figcaption{Représentation des jointures externes}
\end{center}
\end{figure}

\begin{Exemple}
reprenons les tables \ref{jointure-table1} et \ref{jointure-table2} de l'exemple précédent. 

Alors, la jointure externe gauche donnera:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zc|}|*2{Zc|}}
\tabulartitle\hline
id & id\_town & surname & name & id\_town &  name\_town\\
\hline
1 & 1 & Dupont & Jean & 1 & Bordeaux\\
\hline
3 & 1 & Dufon & Jérôme & 1 & Bordeaux\\
\hline
2 & 2 & Durand & Agathe & NULL & NULL\\
\hline
\end{tabular}
\tabcaption{Jointure externe gauche de $T_1$ et $T_2$}
\end{center}
\end{figure}

\Par

Dans cette jointure gauche, nous conservons, par définition, tous les enregistrements de la table $T_1$ (gauche). Or l'enregistrement où apparaît Agathe comporte un \textit{id}\_\textit{town} qui ne
correspond à aucun \textit{id}\_\textit{town} dans la table $T_2$. Le SGBD utilise alors la valeur NULL pour exprimer l'absence de correspondance dans $T_2$.

\Break

La jointure externe droite, quant à elle, donnera la table suivante:

\Par


\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zc|}|*2{Zc|}}
\tabulartitle\hline
id & id\_town & surname & name & id\_town & name\_town\\
\hline
1 & 1 & Dupont & Jean & 1 & Bordeaux\\
\hline
3 & 1 & Dufon & Jérôme & 1 & Bordeaux\\
\hline
NULL & NULL & NULL & NULL & 3 & Paris\\
\hline
\end{tabular}
\tabcaption{Jointure externe droite de $T_1$ et $T_2$}
\end{center}
\end{figure}

\Par

Dans la jointure droite, nous conservons tous les enregistrements de  la table $T_2$, et comme aucun enregistrement de la table $T_1$ ne fait référence à la valeur de \textit{id\_town} de \og Paris \fg, les valeurs \og NULL \fg{} sont données aux attributs \textit{id}, \textit{id}\_\textit{town}, \textit{surname} et \textit{name}.
\end{Exemple}



\section{Le langage SQL}

%\begin{problematique}
%Comment formaliser de manière lexicale des opérations à exécuter dans une base de données ?
%\end{problematique}


\subsection{Introduction}

Développé par IBM dans les années 1970, le langage SQL (pour \og Structured Query Language \fg, ou \og langage de requête structurée \fg{} en français) est rapidement devenu le standard des langages de bases de données relationnelles. Il permet de manipuler la structure d'une base de données et d'y rechercher, insérer, modifier ou supprimer les données à l'aide d'une vingtaine d'instructions dont la syntaxe ressemble à celle de phrases ordinaires en anglais. SQL peut s'utiliser de manière interactive, ou à l'intérieur d'un langage hôte tel que C, Fortran, ou Cobol, par exemple.


\subsection{Environnements nécessaire à l'utilisation de SQL}

\subsubsection{Généralités}

Pour travailler sur des bases de données avec SQL, plusieurs outils sont nécessaires :

\begin{itemize}
\item un serveur pour héberger la base,
\item un SGBD pour la manipuler.
\end{itemize}

Si l'on souhaite utiliser SQL de manière interactive, il nous faudra un logiciel d'édition ou une interface graphique pour entrer manuellement les requêtes.

Au Lycée, vous aurez principalement affaire à SQLite et MyPHP, dont nous allons rapidement étudier les environnements.

\newpage

\subsubsection{Environnement SQLite}
\index{SQLite}
\index{Base de données!SQLite}

SQLite est une solution légère et gratuite, ce qui en fait la plus utilisée au monde. Quelque soit votre système d'exploitation, vous pouvez télécharger les outils nécessaires sur :
\Par
\begin{center}
\nathanlive{web}{https://www.sqlite.org/download.html}
\end{center}
\Par
Par exemple, sous Windows 10, vous pouvez télécharger le fichier :
\Par
\begin{center}
\texttt{sqlite-tools-win32-x86-xxxxxxx.zip}
\end{center}
\Par
qui contient trois exécutables : \texttt{sqlite3}, \texttt{sqldiff} et \texttt{sqlite3}\_\texttt{analyser}.

Lorsque le programme principal sqlite3 est exécuté, une console permet de lancer les requêtes SQL nécessaires à la manipulation des bases de données. 

\Par

\begin{center}
\includegraphics{235_SQL-fenetre}
\end{center}

\Par

Cependant, cette console n'étant pas toujours très pratique, on peut aussi utiliser une interface graphique telle que DB Browser ou SQLiteSpy.

\subsubsection{Environnement MySQL}
\index{Bases de données!MySQL}
\index{MySQL}

MySQL étant un logiciel libre, il s'intègre parfaitement dans un environnement composé du système d'exploitation Linux, du serveur Apache et du langage PHP. Cet environnement est appelé \textit{LAMP}, acronyme de \og Linux - Apache - MySQL - PHP \fg.

Sous Windows, l'environnement correspondant s'appelle \textit{WAMP} (W pour Windows), et sous Mac \textit{OS MAMP} (M pour Mac).

Notons que le langage PHP est le complément idéal à MySQL pour la création de pages Web dynamiques via un serveur HTTPS.

\textit{PhpMyAdmin} est l'interface la plus connue pour utiliser MySQL.

\Par

Une fois l'environnement installé avec MySQL ou SQLite, nous sommes prêts à manipuler des bases de données avec SQL.

\enlargethispage*{5em}

\subsection{Les requêtes SQL}
\index{Base de données!Requêtes SQL}
\nathanlive{video}{Retrouvez une explication des requêtes SQL en vidéo avec Nathan Live.}
\label{video:ch7-1}

\subsubsection{Fonctions de base}

La syntaxe des fonctions de base en SQL est très simple.

\newpage
\begin{itemize}
\item Création d'une table nommée \og matable \fg{} dans la base de données:
\begin{listing}[language=SQL]{}{07-01.txt}\end{listing}

\Par

\item Suppression de la table:
\begin{listing}[language=SQL]{}{07-02.txt}\end{listing}

\Par

\item Insertion d'un enregistrement dans une table:
\begin{listing}[language=SQL]{}{07-03.txt}\end{listing}

\Par


\item Modification d'une ou plusieurs valeurs d'attributs:
\begin{listing}[language=SQL]{}{07-04.txt}\end{listing}

\Par


\item Suppression d'un enregistrement dans une table:
\begin{listing}[language=SQL]{}{07-05.txt}\end{listing}

\newpage
\item Sélection d'un ou plusieurs attributs dans une table:
\begin{listing}[language=SQL]{}{07-06.txt}\end{listing}

\Par

\item Classification des données selon un ou plusieurs attributs:
\begin{listing}[language=SQL]{}{07-07.txt}\end{listing}
\Par
À noter que \og * \fg{} désigne la totalité des attributs.

\Par

\item Suppression des doublons sur un attribut:
\begin{listing}[language=SQL]{}{07-08.txt}\end{listing}
\end{itemize}

\begin{Exemple} 
à l'aide d'une interface graphique (DB Browser pour SQLite ou\linebreak PhpMyAdmin pour MySQL), il est facile de créer une base de données nommée\linebreak \og gestion \fg{} ainsi que deux tables :
\begin{itemize}
\item[$\to$] \og restaurants \fg d'attributs : id, nom, rue, cp, ville, email, tel et id\_gerant;
\item[$\to$] \og gerants \fg d'attributs : id, nom, prenom, email et tel.
\end{itemize}
\end{Exemple}

\begin{itemize}
\item Pour changer l'adresse email de l'enregistrement dont l'id est 1, on écrira:
\begin{listing}[language=SQL]{}{07-09.txt}\end{listing}

\newpage
\item Pour supprimer tous les enregistrements concernant la ville de Nantes dans la table \og restaurants \fg, on écrira:
\begin{listing}[language=SQL]{}{07-10.txt}\end{listing}
\end{itemize}


\subsubsection{Jointures}
\index{Base de données!Jointures (SQL)}
\index{Jointures (bases de données)}


Il existe trois types de jointures : INNER (jointure interne), OUTER (jointure externe) et CROSS (produit cartésien).

\paragraph{Jointure interne}

La syntaxe pour une jointure interne est la suivante :

\begin{listing}[language=SQL]{}{07-11.txt}\end{listing}

\Par

Cette requête joint les deux tables \texttt{table1} et \texttt{table2} et on choisit un critère de sélection avec \og ON \fg. La condition qui vient après peut être une égalité ou autre chose.


\paragraph{Jointures externes}

La syntaxe pour une jointure externe est la suivante :

\begin{listing}[language=SQL]{}{07-12.txt}\end{listing}

\Par

Si aucune correspondance n'est trouvée pour un attribut, la valeur \og NULL \fg{} est donnée pour l'attribut. Ainsi, si l'on dispose de deux tables \texttt{clients} (avec les\linebreak attributs \textit{nom\_client} et \textit{ville\_client}) et \texttt{distributeurs} (avec les attributs\linebreak \textit{nom\_distrib} et \textit{ville\_distrib}), la requête:

\begin{listing}[language=SQL]{}{07-13.txt}\end{listing}

\Par

donne une table comme ci-dessous:

\Par


\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|*3{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries nom\_client & \bfseries nom\_distrib & \bfseries ville\_client\\
\hline
Jean Naimart & CARREFOUR & BORDEAUX\\
Anne Onime & LECLERC & BORDEAUX\\
Paul Tronc & AUCHAN & PARIS\\
Chloé Opieu & NULL & MARSEILLE\\
\hline
\end{tabular}
\end{center}

\Par

La dernière ligne signifie que l'enregistrement correspondant à  Chloé Opieu n'est mis en relation avec aucun enregistrement de la table \texttt{distributeurs}.


Maintenant, si on teste la requête:

\begin{listing}[language=SQL]{}{07-14.txt}\end{listing}

\Par

s'afficheront uniquement les entrées de la seconde table qui satisfont la condition, avec éventuellement des \og NULL \fg{} si certains enregistrements de la table \texttt{distributeurs} ne sont  mis en relation avec aucun enregistrement de la table \og clients \fg.

\Par

\textit{Astuce} : quand les noms des tables sont \og longs \fg, on peut créer des raccourcis. Par exemple, la requête précédente peut s'écrire:

\begin{listing}[language=SQL]{}{07-15.txt}\end{listing}


\Par

Remarquez que nous avons mis un raccourci tout de suite après avoir fait appel à une table : \og distributeurs D \fg{} (donc ici, la lettre \texttt{D} est le raccourci pour cette table) et \og clients C \fg.


\paragraph{Produit cartésien}


Pour effectuer un produit cartésien, on utilisera une syntaxe telle que:
\begin{listing}[language=SQL]{}{07-16.txt}\end{listing}

\Par

Le CROSS JOIN met en relation \textit{tous} les enregistrements de la table de gauche avec tous ceux de la table de droite sans rechercher aucune égalité ni logique particulière.


\subsubsection{Opérations d'agrégation}
\index{Base de données!Opérations d'agrégation (SQL)}

\vspace*{-3mm}
\paragraph{Ajouter}
\index{Bases de données!SUM}

Dans certains cas, il est nécessaire d'ajouter toutes les entrées d'une colonne (d'un attribut). On pourra le faire à l'aide de la fonction d'agrégation SUM.
\enlargethispage*{5em}

\vspace*{-3mm}
\begin{Exemple}
considérons la table \og commandes \fg{} suivante:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*{3}{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{commandes}\\
\tabulartitle\hline
id\_commande & id\_client & total\\
\hline
1 & 12 & 125\\
\hline
2 & 7 & 35\\
\hline
3 & 8 & 50\\
\hline
\end{tabular}
\tabcaption{Table \og commandes \fg{} }
\label{table-commandes-sum}
\end{center}
\end{figure}

\Break

La requête :

\begin{listing}[language=SQL]{}{07-17.txt}\end{listing}

\Par

retourne:

\Par

\begin{center}
\begin{tabular}{|*3{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries somme \\
\hline
210\\
\hline
\end{tabular}
\end{center}

\Par

où \og 210 \fg{} représente le total des valeurs de l'attribut \texttt{montant} des commandes.

Il n'est pas indispensable de spécifier d'alias (\texttt{AS}), mais c'est assez pratique dans les requêtes plus complexes. Dans notre exemple, nous avons choisi l'alias \og somme \fg{} pour désigner la somme obtenue.
\end{Exemple}

\paragraph{Compter}
\index{Bases de données!COUNT}

Dans certains cas, savoir combien une colonne comporte d'entrées s'avère utile. On le fera avec la fonction d'agrégation COUNT.

\begin{Exemple}
prenons à nouveau la table \og commandes \fg{}  du tableau \ref{table-commandes-sum} de l'exemple précédent.
\end{Exemple}

Si on considère la requête suivante:\enlargethispage*{\baselineskip}
\begin{listing}[language=SQL]{}{07-18.txt}\end{listing}
\Par
alors, elle affiche:

\Par

\begin{center}
\begin{tabular}{|*1{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries nb \\
\hline
2\\
\hline
\end{tabular}
\end{center}

\Par

car il y a deux enregistrements où la valeur de l'attribut \og montant \fg{} est strictement inférieure à 100.

\paragraph{Effectuer une moyenne}
\index{Bases de données!AVG (Moyenne)}
\enlargethispage*{5em}
Cela se fait à l'aide de la fonction d'agrégation AVG (abréviation de \textit{average}, qui signifie \og moyenne \fg en anglais).

\begin{Exemple}
en reprenant la table représentée dans le tableau \ref{table-commandes-sum}, la requête :
\Par
\begin{minipage}{0.45\linewidth}
\begin{listing}[language=SQL]{}{07-19.txt}\end{listing}
\end{minipage}
\hfill
\begin{minipage}{0.5\linewidth}
affiche:
\Par
\begin{center}
\raisebox{1em}{\begin{tabular}{|*1{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries moy \\
\hline
66.6667\\
\hline
\end{tabular}}
\end{center}
\end{minipage}
\Par
où \og 66,6667 \fg{} représente donc la moyenne des commandes.
\end{Exemple}

\newpage
\paragraph{Maximum et minimum}
\index{Bases de données!MAX (Maximum)}
\index{Bases de données!MIN (Minimum)}
Connaître le maximum et le minimum des valeurs d'un attribut se fait avec les fonctions d'agrégation MAX et MIN.

\begin{Exemple}
toujours avec la table représentée par le tableau \ref{table-commandes-sum}, la requête :
\begin{listing}[language=SQL]{}{07-20.txt}\end{listing}

\Par

affiche :

\Par

\begin{center}
\begin{tabular}{|*2{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries minimum & \bfseries maximum \\
\hline
25 & 125\\
\hline
\end{tabular}
\end{center}
\end{Exemple}

\subsubsection{Complément : créer une vue}
\index{Bases de données!Vue}

Il sera de temps en temps utile de nommer la table obtenue à l'issue d'une requête; c'est ce que l'on appelle \textit{créer une vue} de la requête. Prenons par exemple la requête précédente qui permet de calculer le maximum et le minimum, et créons une vue que l'on nomme \og mavue \fg{} \enlargethispage*{4em}
\begin{listing}[language=SQL]{}{07-21.txt}\end{listing}
\Par
On peut ainsi utiliser la table (la vue) dans une autre requête, par exemple pour calculer l'écart entre le maximum et le minimum:
\begin{listing}[language=SQL]{}{07-22.txt}\end{listing}



\section{Manipuler les bases de données avec Python}
\index{Base de données!Python}
\index{sqlite3 (Python)}
\index{mysql.connector (Python)}

Pour manipuler des bases de données avec Python, on fera appel au module \texttt{sqlite3} ou au module \texttt{mysql.connector} selon le serveur SQL que l'on aura installé.

\newpage
\subsection{Avec SQLite 3}
\label{bddPython}
\vspace*{-2mm}
\nathanlive{video}{Retrouvez une mise en pratique de SQLite 3 en Python en vidéo avec Nathan Live.}
\label{video:ch7-2}

\vspace*{-5mm}
\subsubsection{Préliminaires}

Il faut d'abord faire appel au module \texttt{sqlite3}:

\begin{listing}{}{07-23.txt}\end{listing}

\Par

Ensuite, il faut établir une connexion à une base de données:

\begin{listing}{}{07-24.txt}\end{listing}

\Par

Ici, on donne le nom \og conn \fg{} à notre connexion, et on donne le nom \og gestion.db \fg{} à notre base de données. À ce stade, cette dernière n'existe pas encore. Pour la créer, on écrit:

\begin{listing}{}{07-25.txt}\end{listing}

\Par

On donne en quelque sorte le curseur à notre base.

Cette opération provoque la création d'un fichier \texttt{gestion.db} dans le répertoire qui contient le script Python. C'est dans ce fichier que l'on va stocker les tables créées ainsi que leurs enregistrements. 

\vspace*{-1mm}
\begin{attention}
Le format du fichier est propre à Sqlite (mélange de binaire et de texte) et ne doit pas être manipulé directement via un éditeur de texte. C'est le SGBD qui maintient ce fichier à jour en y exécutant les requêtes qui lui sont transmises (ici par Python).
\end{attention}

\subsubsection{Passer une requête SQL}


Pour exécuter une requête SQL, on utilisera la commande \texttt{cursor.execute} :
\begin{listing}{}{07-26.txt}\end{listing}

\enlargethispage*{5em}

\vspace*{-1mm}
\begin{Exemples}
\begin{itemize}
\item Pour créer une table:
\begin{listing}{}{07-27.py}\end{listing}
\end{itemize}
\end{Exemples}

\begin{itemize}
\item Pour insérer un enregistrement:
\begin{listing}{}{07-28.py}
\end{listing}
\end{itemize}

\subsubsection{Insérer un enregistrement à partir d'un dictionnaire}

On utilise toujours la méthode \texttt{execute} mais la syntaxe est légèrement différente, comme le montre l'exemple de la page suivante.

\vspace*{-1.5em}
\begin{Exemple}
\begin{listing}{}{07-29.py}\end{listing}
\end{Exemple}

\subsubsection{Insérer plusieurs enregistrements à partir d'une liste}

On utilise ici la méthode \texttt{executemany} comme dans l'exemple suivant.\enlargethispage*{4em}

\vspace*{-1em}
\begin{Exemple}
\begin{listing}{}{07-30.py}\end{listing}\end{Exemple}

\subsubsection{Afficher les enregistrements}

On pourra utiliser une syntaxe comme celle de l'exemple suivant.

\vspace*{-1em}
\begin{Exemple}
\begin{listing}{}{07-31.py}\end{listing}
\end{Exemple}

\subsubsection{Enregistrements avec la clause WHERE}

Quand on souhaite afficher les enregistrements en fonction de la valeur d'un\linebreak attribut avec la clause WHERE, la syntaxe est un peu spéciale, comme le montre l'exemple page ci-contre.

\newpage
\begin{Exemple}
\begin{listing}{}{07-32.txt}\end{listing}
\end{Exemple}

\subsubsection{Valider les modifications}

Avant de clore la connexion, il faut s'assurer que les éventuelles modifications ou insertions soient prises en compte. On utilise alors la méthode \texttt{commit()}:

\begin{listing}{}{07-33.txt}\end{listing}


\subsubsection{Clôture de la connexion}

Quand on a fini de se servir de la base de données, il ne faut pas oublier de clore la connexion.

\begin{listing}{}{07-34.txt}\end{listing}

\Par

\nathanlive{prog}{Télécharger le programme Python complet sur sqlite3.}% 07-35.py

\Par

Pour plus d'informations sur le module \texttt{sqlite3}, n'hésitez pas à vous servir de la commande \textit{help}:

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
\pychevrons import sqlite3\\
\pychevrons help(sqlite3)
\end{tabularx}

\subsection{Avec mysql.connector}

Par défaut, ce module (auquel on doit faire appel si l'on utilise MySQL) n'est pas installé. Il faudra donc se renseigner pour l'installer sur votre distribution Python (avec la commande \texttt{pip} ou, sous Anaconda, la commande \texttt{conda}).

Si l'on part de la base de données \og gestion \fg{}  définie précédemment, voici un exemple.\enlargethispage*{5em}

\nathanlive{progmargin}{}%07-36.py
\begin{listing}{}{07-36-1.txt}\end{listing}
\newpage
\begin{listing}{}{07-36-2.txt}\end{listing}
%Là encore, il ne faudra pas hésiter à regarder la documentation:
%\Par
%\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
%\pychevrons import mysql.connector\\
%\pychevrons help(mysql.connector)
%\end{tabularx}

\enlargethispage*{5em}
\begin{solintro}
\begin{questions}
\item Pour la table \og famille \fg, l'attribut \og id\_prenom \fg{}  peut être une clé primaire. En effet, une clé primaire est un attribut dont les valeurs sont toutes distinctes. Dans notre situation, pour la table \og famille \fg, tous les enregistrements ont un id\_prenom différent.

Il en est de même pour la table \og genres \fg{} : \og id\_genre \fg{} peut être une clé primaire.

Pour la table \og livres \fg, l'attribut \og id\_livres \fg{}  peut être une clé primaire.


\item Pour la table \og livres \fg, l'attribut \og id\_prenom \fg{} est lié à la table \og famille \fg{} donc il peut être défini comme clé étrangère. Mais \og id\_genre \fg{} est lié à la table \og genres \fg{} donc peut aussi être une clé étrangère.


\item Une requête SQL permettant d'insérer une des informations dans la table \og famille \fg est :

\begin{listing}[language=SQL]{}{07-37.txt}\end{listing}

\Par

Pour insérer tous les enregistrements, il suffit de répéter quatre autres fois cette requête en changeant bien entendu le prénom.

On peut aussi, plus simplement, faire la requête suivante :

\begin{listing}[language=SQL]{}{07-38.txt}\end{listing}
\Break
\item Dans cette question, la difficulté réside dans le fait que dans la table \og films \fg, les prénoms n'apparaissent pas; seuls les identifiants sont\linebreak enregistrés. Il faut donc avant tout rechercher l'identifiant de Mathilde à l'aide de la requête :

\begin{listing}[language=SQL]{}{07-39.txt}\end{listing}

\Par

Cette dernière requête renvoyant l'identifiant, il faut s'en servir dans la requête SQL permettant de trouver tous les livres en possession de Mathilde. On obtient alors:

\begin{listing}[language=SQL]{}{07-40.txt}\end{listing}

\Par

\textit{Autre possibilité (avec une jointure) :}
\begin{listing}[language=SQL]{}{07-41.txt}\end{listing}


\item Une requête SQL permettant de calculer le nombre total de livres\linebreak indisponibles est celle proposée page suivante.

\Par

Il s'agit ici de compter tous les enregistrements de la table \og livres \fg{} dont la valeur de l'attribut \og id\_prenom \fg n'est pas vide (ce qui signifie qu'un membre de la famille possède le livre correspondant, et donc que ce dernier est indisponible).
\vspace*{-5mm}
\BreakWithoutItem




\begin{listing}[language=SQL,morekeywords={IS}]{}{07-42.txt}\end{listing}

\Par

L'attribut \og id\_prenom \fg{} étant par définition un entier, sa valeur sera nécessairement \og NULL \fg{} si aucune valeur ne lui a été attribuée. 
\end{questions}
\end{solintro}
