Exercice Corrigé SQL Requêtes en algèbre relationnelle SQL SGBD : Gestion de Spectacles

Un organisme de gestion de spectacles, de salles de concert et de vente de billets de spectacles gère une base de données dont le schéma relationnel est le suivant :

Spectacle(Spectacle_ID, Titre, DateDéb, Durée, Salle_ID, Chanteur)

Concert (Concert_ID, Date, Heure, Spectacle_ID)

Salle (Salle_ID, Nom, Adresse, Capacité)

Billet (Billet_ID, Concert_ID, Num_Place, Catégorie, Prix)

Vente (Vente_ID, Date_Vente, Billet_ID, MoyenPaiement)

Les attributs soulignés sont les attributs appartenant à la clé primaire. Ils sont de type entier.

L'attribut Salle_ID de la relation Spectacle est une clé étrangère qui fait référence à l'attribut de même nom de la relation Salle. L'attribut Spectacle_ID de la relation Concert est une clé étrangère qui fait référence à l'attribut de même nom de la relation Spectacle. L'attribut Concert_ID de la relation Billet est une clé étrangère qui fait référence à l'attribut de même nom de la relation Concert. L'attribut Billet_ID de la relation Vente est une clé étrangère qui fait référence à l'attribut de même nom de la relation Billet.

Exprimez, lorsque cela est possible, les requêtes suivantes en algèbre relationnelle, en calcul relationnel à variable nuplet et en SQL.

1. Quelles sont les dates du concert de Corneille au Zenith ?
2. Quels sont les noms des salles ayant la plus grande capacité ?
3. Quels sont les chanteurs n'ayant jamais réalisé de concert à la Cygale ?
4. Quels sont les chanteurs ayant réalisé au moins un concert dans toutes les salles ?
5. Quels sont les dates et les identicateurs des concerts pour lesquels il ne reste aucun billet invendu ?

----------------------------------------------------------------------------------------------------

Correction

----------------------------------------------------------------------------------------------------

1.    Quelles sont les dates du concert de Corneille au Zenith ?

En algèbre relationnelle : 

Cette requête comporte deux jointures naturelles. La première jointure, entre les relations Concert et Spectacle, associe les nuplets de Spectacle, correspondant aux spectacles du chanteur `Corneille' (puisqu'il y a une sélection avant), avec les nuplets de la relation Concert ayant la même valeur pour l'attribut Spectacle_ID. La jointure se fait naturellement sur l'attribut de même nom, Spectacle_ID. La deuxième jointure associe les nuplets résultats de la première jointure (donc les concerts des spectacles de 'Corneille') avec le nuplets correspondant à la salle du 'Zenith' (résultat de la requête de sélection σN om=0Zenith0(Salle)). La jointure se fait naturellement sur l'attribut commun Salle_ID. La projection nale se fait sur l'attribut Date.

En calcul relationnel :

{t.Date | Concert(t) ∧ [∃ u, v Spectacle(u) ∧ Salle(v) ∧ (u.Spectacle_ID = t.Spectacle_ID) ∧ (u.Chanteur =0    Corneille0) ∧ (v.N om =0    Zenith0) ∧ (u.Salle_ID = v.Salle_ID) ] }

La requête retourne les dates des concerts pour lesquels il existe un spectacle de 'Corneille' associé à la salle du 'Zenith'. Le résultat de la requête contient donc les valeurs de l'attribut Date des nuplets t de la relation Concert tels qu'il existe un nuplet u dans Spectacle, correspondant à un spectacle de 'Corneille' (c'est-à-dire dont l'attribut Chanteur a pour valeur 'Corneille'), avec la même valeur pour l'attribut    Spectacle_ID    que le nuplet  t et tels qu'il existe aussi un nuplet v dans la relation Salle, correspondant à la salle du 'Zenith' (dont l'attribut Nom a pour valeur 'Zenith'), avec la même valeur pour l'attribut Salle_ID que celle de l'attribut Salle_ID du nuplet u.

En SQL, par traduction immédiate de la requête en calcul à variable nuplet :

SELECT Date
  FROM Concert t, Spectacle u, Salle v
    WHERE t.Spectacle_ID = u.Spectacle_ID
      AND u.Chanteur = 'Corneille'
        AND u.Salle_ID = v.Salle_ID
           AND v.Nom = 'Zenith'

2.    Quels sont les noms des salles ayant la plus grande capacité ?

En algèbre relationnelle : Cette requête ne peut pas s'écrire en algèbre relationnelle non étendue. Il faut un opérateur maximum. Pour plus de détails sur l'algèbre relationnelle étendue.

En algèbre relationnelle étendue1, la requête s'exprime par: 

ΠNom(σ(Capacite>=CapaciteMax)ΠSalle_ID,CapacitMax[Salle×(ΠMAX(Capacite)→CapaciteM ax)(Salle))])

La requête ΠSalle_ID,CapacitM ax[Salle×(ΠM AX(Capacite)→CapaciteM ax)(Salle))] retourne une relation temporaire de deux colonnes, la pemière contenant les valeurs de l'attribut Salle_ID de la relation Salle et la deuxième colonne contenant une seule valeur (repétée pour toutes  les valeurs de Salle_ID) correspondant à la valeur maximale de l'attribut Capacité (calculée par la fonction d'agrégation MAX et renommée en CapaciteMAX). L'opérateur utilisé est le produit cartésien (×). Pour obtenir le nom des salles avec la plus grande capacité, il sut  donc de joindre à cette relation temporaire à la relation Salle et de sélectionner les nuplets ayant une valeur de Capacité superieure ou égale à celle de l'attribut CapaciteMax.

En calcul relationnel :
{t.N om | Salle(t) ∧ ¬[∃ u Salle(u) (u.Capacite >= t.Capacite) ] }

Cette requête retourne les valeurs de l'attribut Nom des nuplets t de la relation Salle pour lesquels il n'existe pas de nuplets u dans Salle avec une valeur de l'attribut Capacité supérieure ou égale.

En SQL:

Il est possible de traduire directement la requête exprimée en calcul relationnel, comme ci-dessous.

SELECT Nom
   FROM Salle t
    WHERE NOT EXISTS (SELECT *
      FROM Salle u
         WHERE u.Capacité >= t. Capacité)

Il est également possible d'utiliser l'opérateur d'agrégation M AX, comme pour la requête suivante.

SELECT Nom
   FROM Salle
     WHERE Capacité >= ( SELECT (MAX(Capacité)
       FROM Salle
)

Il est également possible d'utiliser le mot-clé ALL :

SELECT Nom
  FROM Salle
    WHERE Capacité >= ALL ( SELECT Capacité
       FROM Salle
)

3.    Quels sont les chanteurs n'ayant jamais réalisé de concert à la Cygale ?

En algèbre relationnelle : 

La requête ΠChanteur[Spectacle o σ(N om=0Cygale0)(Salle)] retourne les chanteurs ayant chanté au moins une fois dans la salle de la 'Cygale'. Le résultat de la requête fnale est obtenu en supprimant ces chanteurs de la liste de tous les chanteurs.

En calcul relationnel :

{t.Chanteur    |    Spectacle(t)    ∧ ¬[∃    u, v Spectacle(u)    ∧ Salle(v)    ∧    (v.Nom    ='Cygale') ∧ (u.Chanteur = t.Chanteur) ∧ (u.Salle_ID = v.Salle_ID) ] }

La requête retourne les valeurs de l'attribut Chanteur    des nuplets t de la relation Spectacle tels qu'il ne soit pas possible de trouver un spectacle de ce même chanteur à la 'Cygale' (i.e. de trouver un nuplet u dans Spectacle avec la même valeur pour l'attribut Chanteur et un nuplet v dans Salle avec 'Cygale' comme valeur de l'attribut Nom et avec la même valeur que u.Salle_ID pour l'attribut Salle_ID).

En SQL:

SELECT Chanteur
  FROM Spectacle
    WHERE Chanteur NOT IN (SELECT Chanteur
      FROM Spectacle u, Salle v
        WHERE u.Salle_ID=v.Salle_ID
           AND v.Nom='Cygale'

Cette requête peut aussi s'exprimer avec un NOT EXISTS en utilisant une variable nuplet t dans le premier F ROM , par une simple traduction du calcul relationnel :

SELECT Chanteur
  FROM Spectacle t
    WHERE Chanteur NOT EXISTS ( SELECT *
       FROM Spectacle u, Salle v
          WHERE u.Salle_ID=v.Salle_ID
             AND v.Nom='Cygale'
              AND t.CHanteur=u.Chanteur
)

4.    Quels sont les chanteurs ayant réalisé au moins un concert dans toutes les salles ?

En algèbre relationnelle : 

La requête ΠSalle_ID(Salle) retourne tous les identificateurs de salle.
La requête ΠChanteur,Salle_ID(Spectacle Salle) retourne une relation associant à chaque chanteur identificateur de la salle dans laquelle il a réalisé au moins un spectacle.
La division va donc retourner les chanteurs associés au moins une fois à toutes les salles de la base.

En calcul relationnel :

{t.Chanteur | Spectacle(t) ∧ [∀ u (Salle(u)) =⇒ (∃ v Spectacle(v) ∧ (v.Chanteur = t.Chanteur) ∧ (u.Salle_ID = v.Salle_ID) ) ] }

La requête retourne les valeurs de l'attribut Chanteur    des nuplets t de la relation Spectacle tels que pour quel que soit un nuplet, s'il s'agit d'une salle (donc un nuplet u pris dans Salle), alors il existe un spectacle de ce chanteur dans cette salle (donc il existe un nuplet v dans Spectacle correspondant à ce chanteur, avec v.Chanteur = t.Chanteur, et à cette salle, avec u.Salle_ID = v.Salle_ID).

On peut également écrire :
{t.Chanteur | Spectacle(t) ∧ [∀ u ¬(Salle(u)) ∨ (∃ v Spectacle(v) ∧ (v.Chanteur = t.Chanteur) ∧ (u.Salle_ID = v.Salle_ID) ) ] }

La requête retourne les valeurs de l'attribut Chanteur des nuplets t de la relation Spectacle tels que pour quel que soit un nuplet, soit il ne s'agit pas d'une salle (donc il ne s'agit pas d'un nuplet u de Salle), soit (implicitement il s'agit d'un nuplet u de Salle et) il existe un spectacle de ce chanteur dans cette salle (donc il existe un nuplet v dans Spectacle correspondant à ce chanteur, avec v.Chanteur = t.Chanteur, et à cette salle, avec u.Salle_ID = v.Salle_ID).

D'où dit de manière négative :

{t.Chanteur | Spectacle(t) ∧ ¬[∃ u Salle(u) ¬(∃ v Spectacle(v) ∧ (v.Chanteur = t.Chanteur) ∧ (u.Salle_ID = v.Salle_ID) ) ] }

En SQL:

SELECT Chanteur FROM Spectacle t WHERE NOT EXISTS
( SELECT * FROM Salle u WHERE NOT EXISTS
( SELECT * FROM Spectacle v
WHERE v.Chanteur = t. Chanteur AND u.Salle_ID = v.Salle_ID
)
)

5.    Quels sont les dates et les identificateurs des concerts pour lesquels il ne reste aucun billet invendu ?

En algèbre relationnelle : Cette requête étant complexe et ne peut pas s'exprimer à l'aide d'une division. Il est plus simple de l'écrire en la décomposant. Une première sous-requête R1va permettre de déterminer les billets invendus :

R1= ΠBillet_ID(Billet) − ΠBillet_ID(Vente)

La requête R1supprime de la liste des billets (ΠBillet_ID(Billet)), ceux qui ont été vendus  (ΠBillet_ID(Vente)). Pour obtenir les concerts auxquels appartiennent ces billets invendus, il faut faire une jointure avec la relation Billet (pour obtenir la valeur de l'attribut Concert_ID associé au billet) puis avec Concert (pour obtenir la date du concert associé), soit : 

Au final, on supprime la liste des identicateurs de concerts et de leur date associée au résultat de la requête R2, soit :

En calcul relationnel :

{t.Concert_ID, t.Date | Concert(t) ∧ [∀ u Billet(u) (u.Concert_ID = t.Concert_ID) (∃ v V ente(v) ∧ (v.Billet_ID = u.Billet_ID) ) ] }

La requête retourne les valeurs des attributs Concert_ID et Date des nuplets t de la relation Concert    tels que pour tous les billets de ce concert (donc pour tous les nuplets u dans Billet tels que u.Concert_ID = t.Concert_ID), il existe une vente de ce billet (donc il existe un nuplet v dans Vente correspondant à ce billet, i.e. tel que v.Billet_ID = u.Billet_ID).

D'où dit de manière négative :

{t.Concert_ID, t.Date    | Concert(t) ∧ ¬[∃    u Billet(u) (u.Concert_ID    =    t.Concert_ID) ¬(∃ v Vente(v) ∧ (v.Billet_ID = u.Billet_ID) ) ] }

En SQL:

SELECT Concert_ID, Date
  FROM Concert t
   WHERE NOT EXISTS (SELECT * FROM Billet u
      WHERE u.Concert_ID=t.Concert_ID
         AND NOT EXISTS (SELECT * FROM Vente v
           WHERE u.Billet_ID = v.Billet_ID
)
)