CSI : première séance de travaux dirigés

Un Système d'Information pour la vente de billets de spectacle en ligne

Vous devez concevoir un système d'information pour gérer la vente de billets pour des spectacles. Dans ces premières séances de TD, nous nous concentrons sur les données à stocker. Nous allons décrire le SI à l'aide du modèle Entité-Association. Après cette étape, vous traduirez le schéma que vous avez conçu en un schéma relationnel que nous pourrons alors implanter dans un SGBD pendant la séance de TP de la semaine suivante.

Voici un descriptif simplifié des besoins du maître d'ouvrage :

Notre bureau de vente virtuel en ligne vend des billets pour des spectacles, des représentations musicales et des concerts. Un spectacle a lieu à un endroit donné, à une date et à une heure spécifique. Le lieu du spectacle a une certaine capacité (nombre de place assises ou debout selon les cas). Ce nombre correspond au nombre maximum de billets disponibles pour un spectacle. Chaque spectacle a un artiste principal et éventuellement un ou plusieurs artistes additionnels (notamment les « premières parties » de concerts. Les artistes ont une nationalité et correspondent à un genre. Un spectacle a un prix. Quand les clients achètent des billets, leur commande est enregistrée avec la date de commande, le nom du client, et le nombre de billets achetés. Un client peut annuler une commande mais seulement avant les 24h précédant le spectacle.

Travail demandé

Pendant la première séance de TD, il est demandé de :

1. Donner une représentation du système d'information de ce bureau de vente virtuel à l'aide du modèle Entité-Association.

   Après avoir consulté votre proposition de modèle, le maître d'ouvrage s'aperçoit que cela ne suffit pas pour ses besoins. Il voudrait que les genres des artistes soient organisés en hiérarchie (par exemple, le punk est un sous-genre du rock) et veut établir un programme de fidélité pour ses clients. Chaque commande est alors associée à un client enregistré avec son nom et son adresse et un indicateur spécifiant si le client possède une carte de fidélité.

2. Modifier le modèle E-A pour tenir compte de ces changements.

La suite du travail, dans les séances suivantes, sera organisée comme suit :

1. Traduire le modèle E-A en schéma relationnel.
2. Utiliser l'algèbre relationnel pour exprimer formellement des requêtes sur ce modèle.
3. En TP : construire ce schéma à l'aide de l'outil MySQL Workbench.

Ce qui suit n'est pas à faire en TD ou TP, il s'agit de questions bonus pour ceux qui veulent aller plus loin ou pour réviser.

1. Donner les requêtes en SQL correspondant aux demandes suivantes exprimées en français :
   1. Nous voudrions savoir le nombre de spectacles disponibles, selon leur genre et pour chaque lieu.
   2. Pour détecter des conflits entre spectacles, nous voudrions connaitre la liste des spectacles ayant lieu simultanément au même endroit.
   3. Donner la liste des spectacles disponible au Fil correspondant au genre que le client ‘Mr Watchashaw' préfère.
2. Quelles opérations doivent être faites sur la base de données au moment des événements suivants ?
   1. Quand quelqu'un commande des billets.
   2. Quand quelqu'un annule une commande.

Correction possible

Je tiens à rappeler que dans tout problème concret en entreprise, toute solution peut être sujette à discussion. Ici, je ne propose qu'une solution parmi d'autres et je ne prétends pas qu'elle est la meilleure.

1. La figure qui suit donne une solution possible pour la spécification décrite ci-dessus. Il existe d'autres solutions envisageables.

   

2. Pour la seconde question, l'ajout du programme de fidélité peut se faire en ajoutant un attribut numéro_de_fidélité à l'entité Client; en autorisant cette valeur à être absente (avec le mot-clé NULL). Pour avoir une hiérarchie de genres, on peut ajouter une entitété Genre et retirer l'attribut genre de l'entité Artiste. Puis, il faut ajouter une association entre l'entité Artiste et l'entité Genre (par exemple a pour genre) associant un artiste à son genre. Pour simplifier, on suppose qu'un artiste appartient à un seul genre, mais on pourrait également lui en attribuer plusieurs. Enfin, pour la hiérarchie de genre proprement dite, on ajoute une association reliant Genre à lui-même (est sous-genre de). Le diagramme ci-dessous représente le modèle correspondant. On suppose qu'il n'y a pas "d'héritage multiple".

   

1. Voici la traduction du modèle précédent en schéma relationnel, en utilisant simplement les 3 règles présentées dans le cours :
   • Artiste(id_artiste: integer, nom_artiste: string, nationalite: string, id_genre: integer)
   • ArtisteSupplementaire(id_artiste: integer, id_spectacle: integer)
   • Client(id_client: integer, nom_client: string, email: string, code_fidelite: string)
   • Commande(id_commande: integer, date_commande: datetime, annulee: boolean, id_client: integer)
   • Genre(id_genre: integer, nom_genre: string, id_sur-genre: integer)
   • Lieu(id_lieu: integer, nom_lieu: string, adresse_lieu: string, capacite_debout: integer, capacite_assis: integer)
   • Spectacle(id_spectacle: integer, date_spectacle: date, id_lieu: integer, id_artiste_principal: integer)
   • Billet(id_billet: integer, prix: float, id_spectacle: integer, id_commande: integer)
2. À faire en TP.
3. Voici des requêtes pouvant répondre aux questions :

   1. Ici, on définit le genre d'un spectacle par le genre de l'artiste principal.

      SELECT COUNT(*) AS nombre_de_spectacles
FROM Spectacle, Artiste, Genre, Lieu
WHERE (Spectacle.id_lieu = Lieu.id_lieu)
AND (Spectacle.id_artiste_principal = Artiste.id_artiste)
AND (Artiste.id_genre = Genre.id_genre)
GROUP BY id_genre, id_spectacle

   2. Nous sélectionnons les pairs de spectacles qui ont lieu au même endroit et à la même date, mais qui ont des identifiants distincts. Si on avait la date et l'heure du spectacle dans une méme valeur de type DATETIME, il faudrait extraire le jour, le mois et l'année en utilisant les fonction SQL DAY(), MONTH() et YEAR() qui prennent en argument une expression de type DATETIME.

      SELECT s1.id_spectacle, s1.id_lieu, s1.date_spectacle
FROM Spectacle AS s1, Spectacle AS s2
WHERE (s1.id_lieu = s2.id_lieu)
AND (s1.date_spectacle = s2.date_spectacle)
AND (s1.id_spectacle = s2.id_spectacle)

   3. Nous sélectionnons les commandes que Mr Watchashaw a faites, obtenons leurs genres depuis l'artiste principal correspondant, utilisons un agrégat pour otenir le nombre de commandes faites pour ces genres, puis on ne garde que le genre ayant le plus de billets correspondants. Il suffit ensuite de sélectionner les spectacles correspondants à ce genre qui ont lieu au Fil.

      SELECT Spectacle.id_spectacle
FROM Spectacle, Genre, Artiste, Lieu
WHERE (Spectacle.id_lieu = Spectacle.id_lieu)
AND (Spectacle.id_artiste_principal = Artiste.id_artiste)
AND (Artiste.id_genre = Genre.id_genre)
AND (Genre.id_genre = (
SELECT table_auxiliaire.id_genre
FROM (
SELECT id_genre, COUNT(id_billet) AS compte
FROM Genre, Spectacle, Artiste, Commande, Billet
WHERE (Spectacle.id_spectacle = Billet.id_spectacle)
AND (Spectacle.id_artiste_principal = Artiste.id_artiste)
AND (Artiste.id_genre = Genre.id_genre)
AND (Billet.id_commande = Commande.id_commande)
GROUP BY id_genre
) AS table_auxiliaire
WHERE compte = (SELECT MAX(compte) FROM table_auxiliaire)
)
4. On peut envisager les opérations suivantes :

   1. Vérifier s'il y a toujours des billets disponibles pour le spectacle. Les billets disponibles pour un spectacle dont l'identifiant est i sont des billets dont la valeur de l'attribut id_spectacle est i et dont l'identifiant de commande est NULL. On retrouve les billets disponibles pour le spectacle i avec la requête suivante :

      SELECT id_billet
FROM Billet
WHERE (id_spectacle = i)
AND (id_commande IS NULL)

      S'il en existe, il faut créer une commande dans la table Commande et la relier au(x) billet(s) correspondant(s) en mettant à jour la valeur de id_commande du (ou des) billet(s) achetés. Pour le paiement, il faut tenir compte de la réduction qu'entraine l'existence d'une carte de fidélité.

   2. On met à jour le champ annule pour qu'il soit true. Ensuite, si l'on veut remettre en vente les billets de la commande, il faut réinitialisé la valeur de l'attribut id_commande à NULL.