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

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.

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;

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;

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
)

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'Exemple illustré pour mieux comprendre GROUP BY