\modenormal

\chapterimage{bobbytables}

\chapter{SQL: la révision}

%\modeexercices

\section{Modèle relationnel}


Tout  a commencé  avec cet  homme  qui reçut  le prix  \textsc{Turing} en  1981,
notamment suite à son travail initié par un court article publié en 1970:
\href{A Relational Model of Data for Large Shared Data Banks}{http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf}

 

%\begin{figure}
\begin{center}
  \includegraphics[height=6cm]{codd}
\end{center}
%\end{figure}


\subsection{Table, relation}

Une relation (une table) peut être représentée  par un tableau à deux entrées où
les lignes sont des $n$-uplets et les colonnes des attributs.

Considérons  une table  \texttt{ALIEN}  constituée  des attributs  \texttt{Nom},
\texttt{Sexe}, \texttt{Planète}, \texttt{NoCabine}.


Par exemple:


%\begin{center}
\medskip

{\ttfamily
\begin{multicols}{2}
  \begin{tabularx}{1.3\linewidth}{Y{2}}
\whline
Nom&Sexe&Planète&NoCabine\\
\whline
Zorglub&M&Trantor&1\\
\hline
Blorx&M&Euterpe&2\\
\hline
Urxiz&F&Aurora&3\\
\hline
Zbleurdite&F&Trantor&4\\
\hline
Darneurane&M&Trantor&5\\
\hline
Mulzo&M&Helicon&6\\
\hline
Zzzzz&F&Aurora&7\\
\hline
Arggh&M&Nexon&8\\
\hline
Joranum&F&Euterpe&9\\
\whline
  \end{tabularx}

\hfill
  \begin{tabularx}{0.6\linewidth}{Y{0}}
  \whline
  NoCabine&NoAllee\\
\whline
1&1\\
\hline
2&1\\
\hline
3&1\\
\hline
4&1\\
\hline
5&1\\
\hline
6&2\\
\hline
7&2\\
\hline
8&2\\
\hline
9&2\\
\whline
  \end{tabularx}
\end{multicols}
}
%\end{center}
\medskip

La relation \texttt{ALIEN} est de degré 4 et de cardinalité 9. On peut choisir comme clé
primaire le nom de l'Alien. On aurait pu choisir le numéro de la cabine qui est une
autre clé candidate.
La relation \texttt{CABINE}  a pour clé primaire \texttt{NoCabine}  qui est donc
une clé étrangère de \texttt{ALIEN}.

Point de vue vocabulaire selon les différents domaines:


\begin{center}

\begin{tabularx}{0.6\linewidth}{Y{1}}
  \whline
Maths&BDD&SGBD\\
\whline
Relation&Table&Fichier\\
\hline
$n$-uplet&Ligne&Enregistrement\\
\hline
Attribut&Colonne&Champ\\
\whline
\end{tabularx}


\end{center}


Il y  a de nombreuses  régles d'intégrité  assurant la sécurité  et l'efficacité
d'une BDD mais nous ne les étudierons pas.


\subsection{opérateurs relationnels}

\begin{description}
\item[Opérateurs ensemblistes classiques] Union, Intersection, Différence, 
  Produit cartésien;
\item[Opérateurs relationnels spécifiques] Sélection (Restriction), Projection, Jointure;
\item[Opérateurs dérivés] Division, jointure externe, etc.
\end{description}

La  description   de  la  construction   d'une  relation  avec   les  opérateurs
relationnels est appelée \textit{requête}.


Quelles  sont   les  contraintes   sur  les   tables  sur   lesquelles  l'union,
l'intersection ou la différence peuvent opérer?


La \textbf{sélection}  permet d'extraire d'une  relation les $n$-uplets  satisfaisant une
condition donnée:

$$\mathtt{T = S (critere) R}$$

La nouvelle relation \texttt{T} a le même en-tête que \texttt{R}. Par exemple:

$$\mathtt{ALIEN\_TRANTOR=S (Planete="Trantor") ALIEN}$$

donne:

\begin{center}
 \begin{tabularx}{0.7\linewidth}{Y{2}}
\whline
Nom&Sexe&Planète&NoCabine\\
\whline
Zorglub&M&Trantor&1\\
\hline
Zbleurdite&F&Trantor&4\\
\hline
Darneurane&M&Trantor&5\\
\whline
  \end{tabularx}
\end{center}

La \textbf{projection}  permet de  définir une  nouvelle relation  consistant en
l'ensemble de tous  les $n$-uplets de la relation de  départ dans laquelle seuls
les attributs de projection sont conservés.

$$\mathtt{ALIEN\_PLANETE= [NomAlien, Planete] ALIEN}$$

donne?

Une  \textbf{jointure} consiste  à  construire une  nouvelle  relation dont  les
$n$-uplets sont la concaténation d'un  $n$-uplet d'une première relation et d'un
$n$-uplet d'une seconde relation vérifiant une condition de jointure.

Les deux relations doivent avoir au moins une colonne commune sémantiquement.

En fait,  une jointure  est un  produit cartésien suivi  d'une sélection  sur la
condition de jointure.

$$\mathtt{R  [Att  =  Att']  R'  =   \bigl\{ligne  \in  R×R'  \mid  ligne.Att  =
  ligne.Att'\bigr\} = S [Att = Att'] (R×R')}$$


Considérons la  table \texttt{RESPONSABLE} qui donne  le nom du responsable  de chaque
allée:

{\ttfamily
\begin{center}
\begin{tabularx}{0.4\linewidth}{Y{0}}
  \whline
NoAllee&Nom\\
\whline
1&Seldon\\
\hline
2&Pelorat\\
\whline
\end{tabularx}
\end{center}
}

Nous allons  créer la jointure symétrique  entre la table \texttt{CABINE}  et la
table     \texttt{RESPONSABLE}    avec     comme    condition     de    jointure
\texttt{CABINE.NoAllee = RESPONSABLE.NoAllee}:


$$\mathtt{CAB\_RES = CABINE [CABINE.NoAllee = RESPONSABLE.NoAllee] RESPONSABLE}$$



{\ttfamily
\begin{center}
   \begin{tabularx}{0.6\linewidth}{Y{2}}
  \whline
  NoCabine&NoAllee&NoAllee&Nom\\
\whline
1&1&1&Seldon\\
\hline
2&1&1&Seldon\\
\hline
3&1&1&Seldon\\
\hline
4&1&1&Seldon\\
\hline
5&1&1&Seldon\\
\hline
6&2&2&Pelorat\\
\hline
7&2&2&Pelorat\\
\hline
8&2&2&Pelorat\\
\hline
9&2&2&Pelorat\\
\whline
  \end{tabularx}
\end{center}
}

Il y a redondance de l'attribut de  jointure. On règle le problème en effectuant
une projection. Par exemple: \texttt{[NoCabine, Nom] CAB\_RES}.



{\ttfamily
\begin{center}
   \begin{tabularx}{0.4\linewidth}{Y{0}}
  \whline
  NoCabine&Nom\\
\whline
1&Seldon\\
\hline
2&Seldon\\
\hline
3&Seldon\\
\hline
4&Seldon\\
\hline
5&Seldon\\
\hline
6&Pelorat\\
\hline
7&Pelorat\\
\hline
8&Pelorat\\
\hline
9&Pelorat\\
\whline
  \end{tabularx}
\end{center}
}



La \textbf{division  cartésienne} entre  deux relations est  le pendant  pour le
produit cartésien de la division euclidienne pour le produit des entiers.

Si \texttt{R} et \texttt{S} sont deux relations, alors \texttt{Q=R Div S} est la
plus  grande  relation au  sens  de  l'inclusion  vérifiant $Q×S  \subseteq  R$:
\textit{tous les n-uplets de Q concaténés à ceux de S apparaissent dans R}.

C'est   l'opération  qui   permet  de   répondre   à  des   questions  du   type
\textit{quelque soit x, trouver y}.

Par  exemple, supposons  que  l'on dispose  de  la table  \texttt{PLANETE\_ALLEE}
associant planète et numéro d'allée de l'Alien orifginaire de la planète.

Comment obtient-on cette table?




{\ttfamily
\begin{center}
   \begin{tabularx}{0.4\linewidth}{Y{0}}
  \whline
  Planete&NoAllee\\
\whline
Trantor&1\\
\hline
Euterpe&1\\
\hline
Aurora&1\\
\hline
Helicon&2\\
\hline
Aurora&2\\
\hline
Nexon&2\\
\hline
Euterpe&2\\
\whline
  \end{tabularx}
\end{center}
}




 si on  cherche le  nom des planètes  apparaissant dans  toutes les
allées:

$$\mathtt{PLANETE\_ALLEE [PLANETE\_ALLEE.NoAllee / CABINE.NoAllee] CABINE}$$


{\ttfamily
\begin{center}
   \begin{tabular}{|c|}
  \hline
  Planete\\
\hline
Euterpe\\
\hline
Aurora\\
\hline
  \end{tabular}
\end{center}
}



\section{Le langage SQL}

SQL signifie \textit{Structered  Query Language} et a été conçu  dans les années
1970 par \textsc{Ibm}. La version étudiée est celle introduite en 1992: SQL2.


L'obtention de données se fait exclusivement avec la clause
\mintinline{sql}{SELECT} qui  possède six  clauses différentes dont  seules les
deux premières sont obligatoires:

\begin{sqlcode}
SELECT Liste des attributs à extraire
FROM Liste des tables utilisées
WHERE critère de sélection des n-uplets
GROUP BY liste des attributs qui définissent le regroupement
HAVING critère qui détermine les groupes qui sont sélectionnés
ORDER BY liste des attributs permettant d'ordonner la présentation des résultats
\end{sqlcode}



\subsection{SELECT}


\mintinline{sql}{SELECT} correspond à l'opérateur de projection.


Par exemple $\mathtt{ALIEN\_PLANETE= [NomAlien, Planete] ALIEN}$ donne en SQL:

\begin{sqlcode}
SELECT ALIEN.NomAlien, ALIEN.Planete
FROM ALIEN
\end{sqlcode}

On peut omettre le nom de la table lorsqu'il n'y a pas ambiguité:


\begin{sqlcode}
SELECT NomAlien, Planete
FROM ALIEN
\end{sqlcode}



Il  y  a  une  différence  cependant  entre la  requête  SQL  et  la  projection
relationnelle. Ainsi

\begin{sqlcode}
SELECT Planete
FROM ALIEN
\end{sqlcode}

donnera:

  \texttt{Trantor, Euterpe,  Aurora, Trantor,  Trantor, Helicon,  Aurora,
  Nexon, Euterpe}.  

En  effet  les   doublons  ne  sont   pas  automatiquement
éliminés. Pour cela, il faut utiliser le mot réservé \mintinline{sql}{DISTINCT}:

 

\begin{sqlcode}
SELECT DISTINCT Planete
FROM ALIEN
\end{sqlcode}

qui donnera \texttt{Trantor, Euterpe, Aurora, Helicon, Nexon}. 

Il ne faut cependant pas en abuser car cela ralentit le traitement des données!


Ce n'est  pas explicitement  au programme des  concours mais  c'était nécessaire
pour répondre à une question de l'épreuve de Centrale 2015...

Le mot  réservé \mintinline{sql}{*}  permet de  sélectionner tous  les attributs
d'une table.


\begin{sqlcode}
SELECT *
FROM ALIEN
\end{sqlcode}

retourne toutes les données de la table \texttt{ALIEN}.


Le mot réservé \mintinline{sql}{AS} permet de renommer les résultats.

\begin{sqlcode}
SELECT Planete AS pl
FROM ALIEN
\end{sqlcode}




Pour  réaliser  des produits  cartésiens,  on  met  la  liste des  tables  après
\mintinline{sql}{FROM}:

\begin{sqlcode}
SELECT *
FROM RESPONSABLE, ALIEN
\end{sqlcode}

Parfois, on  doit effectuer le produit  cartésien d'une table par  elle-même: on
utilise alors des alias pour éviter les confusions:

\begin{sqlcode}
SELECT A1.Nom, A2.Planete
FROM ALIEN AS A1, ALIEN AS A2
\end{sqlcode}

\subsection{WHERE}


\mintinline{sql}{WHERE} permet de spécifier des critères de sélection.

\begin{sqlcode}
SELECT Nom
FROM ALIEN
WHERE planete = 'Trantor'
\end{sqlcode}


Les critères peuvent s'exprimer en utilisant:
\begin{itemize}
\item des opérateurs de comparaison: \mintinline{sql}{<, >, <=, >=, =, <>}
\item des opérateurs logiques: \mintinline{sql}{AND, NOT, OR}
\item   des   prédicats    définissant   des   ensembles:   \mintinline{sql}{IN,    BETWEEN ... AND ..., LIKE}
\item la comparaison avec la valeur \mintinline{sql}{NULL}: \mintinline{sql}{IS NULL, IS NOT NULL} 
\item des  caractères génériques: \mintinline{sql}{_} qui  remplace un caractère
  et \mintinline{sql}{%} qui remplace une chaîne de caractères.
\end{itemize}



Pour sélectionner les Aliens de Trantor ou Euterpe, on peut écrire:

\begin{sqlcode}
SELECT Nom
FROM ALIEN
WHERE planete = 'Trantor' OR planete = 'Euterpe'
\end{sqlcode}

ou bien


\begin{sqlcode}
SELECT Nom
FROM ALIEN
WHERE planete IN ('Trantor', 'Euterpe')
\end{sqlcode}


\subsection{Les jointures}

On   peut   utiliser   la    clause   \mintinline{sql}{WHERE}   ou   l'opérateur
\mintinline{sql}{JOIN}.

Reprenons la jointure 
$\mathtt{CABINE [CABINE.NoAllee = RESPONSABLE.NoAllee] RESPONSABLE}$.

\begin{sqlcode}
SELECT *
FROM CABINE, RESPONSABLE
WHERE CABINE.NoAllee = RESPONSABLE.NoAllee
\end{sqlcode}

ou bien 

\begin{sqlcode}
SELECT *
FROM CABINE JOIN RESPONSABLE OΝ CABINE.NoAllee = RESPONSABLE.NoAllee
\end{sqlcode}

\subsection{Les fonctions de groupes (ou d'agrégation)}

Les fonctions de groupe permettent d'obtenir des informations sur un ensemble de
$n$-uplets en travaillant sur les \textbf{colonnes} et  non pas sur les lignes comme avec
\mintinline{sql}{WHERE}.

Les fonctions disponibles sont:

\begin{itemize}
\item \mintinline{sql}{AVG}: calcule la moyenne d'une colonne
\item \mintinline{sql}{SUM}: calcule la somme d'une colonne
\item \mintinline{sql}{MIN}: calcule le minimum d'un colonne
\item \mintinline{sql}{MAX}: le max
\item \mintinline{sql}{VARIANCE}:...
\item \mintinline{sql}{STDDEV}: l'écart-type
\item \mintinline{sql}{COUNT}: nombre de lignes de la colonne
\item \mintinline{sql}{EVERY}: vrai si toutes les occurrences de la colonne sont
  vraies
\item \mintinline{sql}{ANY | SOME}: vrai si au moins une occurrence de la colonne est
  vraie
\end{itemize}


Que donne:

\begin{sqlcode}
SELECT COUNT(Planete) AS NbPlanetes
FROM ALIEN
WHERE Planete = 'Trantor'
\end{sqlcode}

Que donne:


\begin{sqlcode}
SELECT COUNT(DISTINCT Planete) AS NbPetitesPlanetes
FROM ALIEN
WHERE NoCabine < 5
\end{sqlcode}


Que donne:


\begin{sqlcode}
SELECT COUNT(*)
FROM ALIEN
\end{sqlcode}



\subsection{GROUP BY}

Attention: c'est une clause souvent utilisée aux concours mais dangereuse...

Elle permet de subdiviser une table en groupes mais les valeurs des attributs de
regroupement  peuvent ne  plus être  atomiques  ce qui  limitera les  opérations
réalisables.


Par exemple:

\begin{sqlcode}
SELECT Planete, Nom
FROM ALIEN
GROUP BY Planete
\end{sqlcode}

donne:


\begin{center}
{\ttfamily
  \begin{tabularx}{0.7\linewidth}{Y{0}}
\whline
Planète&Nom\\
\whline
Trantor&Zorglub\\
\hline
& Zbleurdite\\
\hline
&Darneurane\\
\whline
Euterpe&Blorx\\
\hline
&Joranum\\
\whline
Aurora&Urxiz\\
\hline
&Zzzzz\\
\whline
Helicon&Mulzo\\
\whline
Nexon&Arggh\\
\whline
  \end{tabularx}
}
\end{center}


On peut appliquer des fonctions de groupe. Par exemple:

\begin{sqlcode}
SELECT Planete, COUNT(Nom) as NbPlanetes
FROM ALIEN
GROUP BY Planete
\end{sqlcode}

donne:


\begin{center}
{\ttfamily
  \begin{tabularx}{0.7\linewidth}{Y{0}}
\whline
Planète&NbPlanètes\\
\whline
Trantor&3\\
\hline
Euterpe&2\\
\hline
Aurora&2\\
\hline
Helicon&1\\
\hline
Nexon&1\\
\whline
  \end{tabularx}
}
\end{center}

\subsection{HAVING}

\mintinline{sql}{HAVING} permet  de faire  des sélection  a posteriori,  en fait
après un \mintinline{sql}{GROUP BY}.

 
\begin{sqlcode}
SELECT Planete
FROM ALIEN
GROUP BY Planete
HAVING COUNT(Nom) > 1
\end{sqlcode}

donne:


\begin{center}
{\ttfamily
  \begin{tabular}{|c|}
\hline
Planète\\
\hline
Trantor\\
\hline
Euterpe\\
\hline
Aurora\\
\hline
  \end{tabular}
}
\end{center}


\subsection{ORDER BY}

La  clause  \mintinline{sql}{ORDER}  permet  de préciser  dans  quel  ordre  les
$n$-uplets sélectionnés seront donnés. 

Cette clause ne modifie pas l'ensemble des données retournées par la requête, mais simplement leur
ordre de présentation. Les expressions sont généralement des noms d’attributs. On peut trier par
ordre croissant par  défaut, ou par ordre décroissant, en  utilisant dans ce cas
le mot réservé \mintinline{sql}{DESC}.


\begin{sqlcode}
SELECT Planete, COUNT(Nom) AS NbPlanetes
FROM ALIEN
GROUP BY Planete
ORDER BY Planete
\end{sqlcode}


\begin{center}
{\ttfamily
  \begin{tabularx}{0.7\linewidth}{Y{0}}
\whline
Planète&NbPlanètes\\
\whline
Aurora&2\\
\hline
Euterpe&2\\
\hline
Helicon&1\\
\hline
Nexon&1\\
\hline
Trantor&3\\
\whline
  \end{tabularx}
}
\end{center}

\newpage

\thispagestyle{empty}
\includepdf[openright=false,pages={4},pagecommand={}]{Centrale2015}


\newpage

\begin{sqlcode}
SELECT masse FROM CORPS
\end{sqlcode}

\begin{sqlcode}
SELECT COUNT(DISTINCT id_corps)
FROM ETAT
WHERE datem < tmin()
\end{sqlcode}


\begin{sqlcode}
SELECT id_corps, MAX(datem) 
FROM ETAT
WHERE datem < tmin()
GROUP BY id_corps
\end{sqlcode}


\begin{sqlcode}
SELECT masse, x, y, z, vx, vy, vz 
FROM (CORPS JOIN ETAT ON CORPS.id_corps = ETAT.id_corps)
     JOIN date_mesure 
          ON (datem = date_der AND ETAT.id_corps = date_mesure.id_corps)
WHERE masse > masse_min() AND ABS(x) < arete()/2 AND ABS(y) < arete()/2
                          AND ABS(z) < arete()/2
ORDER BY x*x + y*y + z*z
\end{sqlcode}
%%% Local Variables: 
%%% TeX-master: "PolyIPT2015_16"
%%% End: 
