====== Correction du partiel de base de donnée 2008-2009 ======

===== 1. Manipulation d'une base de donnée relationnelle =====

==== 1.1 Définition du schéma des relations ====

Cette question revient à se demander si tous les tuples ont des clés primaires différentes.\\

Ici ce n'est pas le cas, mais si d'autres tuples d'une autre table avait été mentionnés, il aurait fallu vérifier que les clés secondaire aient bien été définies en tant que clé primaire dans la table en relation.\\

La relation VISITE possède 2 clés primaires qui sont : \\ \\


C1=(n°visite)\\
C2=(jour,horaire,n°employé)\\ \\


Ainsi nous devons vérifier que les valeurs pour les champs C1 et C2 soient bien différents entre
chacun des tuples.\\ \\

On observe que pour la clé C1, il n'y a pas de problèmes puisqu'ils ont tous un n° de visite différent.

En revanche, pour la clé C2, on remarque que $t_i$ et $t_k$ ont un clé primaire C2 de même valeur, ce qui pose problème lors de l'insertion en base. En effet ici le couple (lu,10,231) est présent dans ces 2 tuples.

==== 1.2 Définition du schéma des relations ====

=== a) Liste des numéros de salles vues par chaque visit. Exprimez cette requête en SQL 89 sans select imbriqué et en SQL92  ===

Déjà on veut les numéros de salles dans notre requête, on va donc chercher là où les tables faisant référence à cette information.\\

On trouve une seule table qui s'appelle OEUVRE et qui contient les numéros de salles. Le début de la requête commencera donc par : SELECT n°salle\\

Il nous faut lier les numéros de salles par rapport au visite, donc on cherche dans quel table on peut obtenir les visites.\\

La table définissant les visites est la table VISITE. En revanche, comme cette table contient 2 clés, il faut voir comment faire pour pouvoir relier\\

la table VISITE à la table OEUVRE. Pour cela il faut passer par la table CONTENU_VISITE qui fait l'association entre une visite et une oeuvre et c'est\\

exactement ce que nous cherchons.\\

Normalement il suffit donc d'écrire : 

//SQL89// : \\
**SELECT** n°salle \\
**FROM** OEUVRE,CONTENU_VISITE \\
**WHERE** OEUVRE.n°ouvre=CONTENU_VISITE.n°oeuvre;\\ \\

//SQL92// : \\
**SELECT** n°salle \\
**FROM** OEUVRE \\
**NATURAL JOIN** CONTENU_VISITE;

=== b) n°visite,n°oeuvre, nom_oeuvre et auteur des oeuvres visitées successivement par les visites du type "decouverteEnfant" classées par visite et par ordre. Exprimez cette requête en SQL 89 sans select imbriqué et en SQL92 ===

Commençons par ce qui est le plus simple, à savoir les contraintes. En effet nous avons une contrainte sur le type qui est "decouverteEnfant". Voyons dans quel table nous avons cet attribut. Il s'agit de la table VISITE. Nous aurons donc à inclure cette table dans notre FROM. 

Voyons donc aussi le tri afin de ne plus avoir à s'en soucier. Pour cela il faut utiliser le mots clé ORDER BY. Le tri se faisant d'abord sur les visites puis par ordre, nous avons donc : ORDER BY n°visite,n°ordre. Pour pouvoir faire cela, il va nous falloir également inclure la table CONTENU_VISITE dans notre FROM qui est la seule à contenir l'attribut n°ordre.

Maintenant intéressons nous au SELECT. Il nous faut les attributs : n°visite,n°oeuvre, nom_oeuvre et auteur. Il nous faut donc les tables : CONTENU_VISITE et OEUVRE. Du coup si on fait le bilan des tables il nous faudrait : VISITE,CONTENU_VISITE et OEUVRE. Pour les jointures, as-t-on besoin de la table GUIDE ? Cette table ne nous sert à rien dans notre requête.

Reste à joindre ces tables. En vu des 2 flêches, nous allons utiliser les attributs n°visite et n°oeuvre pour joindre ces 3 tables de la façon suivante :

//SQL89// :\\
**SELECT** n°visite,n°oeuvre,nom_oeuvre,auteur\\
**FROM** VISITE,CONTENU_VISITE,OEUVRE\\
**WHERE** VISITE.n°visite = CONTENU_VISITE.n°visite\\
**AND** OEUVRE.n°oeuvre = CONTENU_VISITE.n°oeuvre\\
**ORDER BY** n°visite,n°ordre;

Les contraintes de clés sur les tables étant définies, nous avons en SQL92

//SQL92// :\\
**SELECT** n°visite,n°oeuvre,nom_oeuvre,auteur\\
**FROM** VISITE
**NATURAL JOIN** CONTENU_VISITE\\
**NATURAL JOIN** OEUVRE\\
**WHERE** VISITE.n°visite = CONTENU_VISITE.n°visite\\
**AND** OEUVRE.n°oeuvre = CONTENU_VISITE.n°oeuvre\\
**ORDER BY** n°visite,n°ordre;

=== c) Liste des guides (numéro, identité, qualification). Exprimez cette requête en SQL 89 et en SQL92 ===

On remarque déjà une chose dans l'énoncé qui change des 2 précédents, il n'y a pas marqué sans SELECT imbriqué. Cela veut probablement dire qu'il est intéressant d'utiliser les SELECT imbriqués dans cet requête et c'est ce que nous allons faire.\\ \\

En effet nous voulons des tuples contenus dans GUIDE qui n'appartient pas à un certain ensemble de tuples (ceux qui sont affectés à une visite). On va donc commencer par récupérer par un SELECT les GUIDE (ou du moins des 3 attributs qui le compose) qui possèdent au moins une visite.

Pour savoir si un GUIDE est attribué à une VISITE, il suffit de joindre ces tables sur l'attribut n°empl. Comme rien nous dit que l'on ne va pas avoir des tuples en double (car les 3 attributs de GUIDE ne forment par une clé), il faut rajouter le mots clé DISTINCT après le SELECT. Nous avons donc :

//SQL89// : \\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE,VISITE\\
**WHERE** GUIDE.n°empl = VISITE.n°empl;\\ \\

//SQL92// : \\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE 
**NATURAL JOIN** VISITE\\

Il ne reste maintenant plus qu'a sélectionner tous les tuples de guide, aussi de façon distincte, (basé sur les 3 attributs précédents) qui ne figurent par dans l'ensemble généré par le SELECT DISTINCT que l'on vient de réaliser précédemment. Cela donne donc :

//SQL89// : \\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE\\
**WHERE NOT IN** (\\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE,VISITE\\
**WHERE** GUIDE.n°empl = VISITE.n°empl;\\
)

//SQL92// : \\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE\\
**WHERE NOT IN** (\\
**SELECT DISTINCT** n°empl,emp_identité,qualification\\
**FROM** GUIDE 
**NATURAL JOIN** VISITE\\
)

=== d) Nombre de visites prévues pour chaque guide. ===

Cette fois-ci on rentre dans le calcul. En effet, il va falloir, pour un GUIDE donnée compter le nombre de VISITES associées à ce guide.
Pour cela on va utiliser l'agrégat COUNT que l'on regroupera par visite afin de toutes les sommer. Il nous faudra donc les tables GUIDE et VISITE pour
faire la jointure ce qui nous donne :

**SELECT** n°empl,COUNT(*)\\
**FROM** GUIDE,VISITE\\
**WHERE** GUIDE.n°empl=VISITE.n°empl\\
**GROUP BY** n°visite;

Voir l'[[http://sql.toutestfacile.com/maitriser_les_bases_de_donnees/count_et_group_by_1.php5|Exemple illustré]] pour mieux comprendre GROUP BY