Exercice Langage SQL : Gestion de spectacles

Enoncé de l'exercice

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 identificateurs des concerts pour lesquels il ne reste aucun billet invendu ?

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

En algèbre relationnelle : 
?Date[Concert x ?Chanteur='Corneille'(Spectacle) x ?Nom='Zenith'(Salle)]

En calcul relationnel :
{t.Date|Concert(t)?[?u,vSpectacle(u)?Salle(v)?(u.Spectacle_ID =t.Spectacle_ID)? (u.Chanteur =? Corneille?) ? (v.Nom =? Zenith?) ? (u.Salle_ID = v.Salle_ID) ] }

En SQL:

1
2
3
4
5
6
7

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 calcul relationnel :
{t.Nom | Salle(t) ? ¬[? u Salle(u) (u.Capacite >=t.Capacite) ] }

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

1
2
3
4
5
6
7

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 MAX, comme pour la requêtesuivante.

1
2
3
4
5

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

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

1
2
3
4
5

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 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) ] }

En SQL:

1
2
3
4
5
6
7
8
9

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 tdans le premier FROM, par une simple traduction du calcul relationnel :

1
2
3
4
5
6
7
8

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 : 
?Chanteur,Salle_ID(Spectacle x Salle)÷?Salle_ID(Salle)

En calcul relationnel :
{t.Chanteur|Spectacle(t)?[?u(Salle(u))=?(?vSpectacle(v)?(v.Chanteur =t.Chanteur)? (u.Salle_ID = v.Salle_ID) ) ] }

En SQL:

1
2
3
4
5
6
7
8
9

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 identi cateurs des concerts pour lesquels il ne resteaucun billet invendu ?

En SQL:

1
2
3
4
5
6
7
8
9
10
11

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
                              )
                )