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.
- Quelles sont les dates du concert de Corneille au Zenith ?
- Quels sont les noms des salles ayant la plus grande capacité ?
- Quels sont les chanteurs n'ayant jamais réalisé de concert à la Cygale ?
- Quels sont les chanteurs ayant réalisé au moins un concert dans toutes les salles ?
- 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:
| 1234567 | SELECT DateFROM 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.
| 1234567 | SELECT NomFROM Salle tWHERE 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.
| 12345 | SELECT NomFROM SalleWHERE Capacité >= ( SELECT (MAX(Capacité)FROM Salle) |
Il est également possible d'utiliser le mot-clé ALL :
| 12345 | SELECT NomFROM SalleWHERE 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:
| 123456789 | SELECT ChanteurFROM SpectacleWHERE 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 :
| 12345678 | SELECT ChanteurFROM Spectacle tWHERE 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:
| 123456789 | 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:
| 1234567891011 | SELECT Concert_ID, DateFROM Concert tWHERE 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 ) ) |