Annales des examens

Examen blanc, janvier 2019

Le schéma de base de données suivant sera utilisé pour l’ensemble du sujet. Il permet de gérer une plateforme de vente de billets de spectacles en ligne :

  • Personne(id, nom, prénom, age)
  • Salle(id, nom, adresse, nbPlaces)
  • Spectacle (id, idArtiste, idSalle, dateSpectacle, nbBilletsVendus)
  • Billet (id, idSpectateur, idSpectacle, catégorie, prix)

Notez que les artistes et les spectateurs sont des personnes. Tous les attributs id sont des clés primaires, ceux commençant par id sont des clés étrangères.

Conception (5 points)

  • Donnez le modèle entité / association correspondant au schéma de notre base.
  • Pensez-vous qu’une association a été réifiée? Si oui, laquelle?
  • Donnez la commande de création de la table Spectacle
  • On veut qu’un même spectateur puisse avoir au plus un billet pour un spectacle. Quelle dépendance fonctionnelle cela ajoute-t-il? Que proposez-vous pour en tenir compte dans le schéma?

SQL (6 points)

Question de cours. Deux requêtes syntaxiquement différentes sont équivalentes si

  • Elles peuvent donner le même résultat
  • Elles donnent toujours le même résultat, quelle que soit la base

Indiquez la bonne réponse.

Donnez les expressions SQL des requêtes suivantes

  • Noms des artistes âgés d’au moins 70 ans qui ont donné au moins un spectacle dans la salle ‘Bercy’
  • Donnez le nom des artistes, des salles où ils se sont produits, triés sur le nombre de billets vendus
  • Quelles personnes n’ont jamais acheté de billet?
  • Donnez la liste des spectateurs ayant acheté au moins 10 billets, affichez le nom, le nombre de billets et le prix total.

Algèbre (5 points)

Voici 3 relations.

A B C D
1 0 1 2
4 1 2 2
6 2 6 3
7 1 1 3
1 0 1 1
1 1 1 1

Relation R

A B E
1 0 2
4 1 2
1 0 7
8 6 6

Relation S

A B E
1 0 2
4 2 2
1 0 7
8 6 5
8 5 6

Relation T

  • Pour quelle requête le résultat contient-il plus d’un nuplet,? Attention: souvenez-vous que l’opérateur de projection élimine les doublons.

    1. \(\pi_{A,C} (\sigma_{B=0}(R))\)
    2. \(\pi_{A,C} (\sigma_{D=0}(R))\)
    3. \(\pi_{A,C} (\sigma_{B=0}(R) \cup \sigma_{D=0}(R))\)
    4. \(\pi_{A,C} (\sigma_{A=C}(R)\)
  • Combien de nuplets retourne la requête \(\pi_{A,B,E} (S \Join_{A=A \land B=B} R)\)?

    1. 2
    2. 3
    3. 4
    4. 5
  • Combien de nuplets retourne la requête \(R \Join_{A=A \land B=B} (S \cup T)\)?

    1. 3
    2. 5
    3. 7
    4. 9
  • Donnez l’expression algébrique pour la requête “Noms et prénoms des spectateurs qui ont acheté au moins une fois un billet de plus de 500 euros.”

  • Donnez l’expression algébrique pour la requête “Id des personnes qui ne sont pas des artistes”

Transactions (4 points)

On prend le schéma donné au début de l’examen.

  • Question de cours. Quel est le principal inconvénient du mode serializable?

    1. Le système ne peut exécuter qu’une transaction à la fois
    2. Certaines transactions sont mises en attente avant de pouvoir finir
    3. Certaines transactions sont rejetées par le système

    Indiquez la bonne réponse

  • Exprimez au moins un critère de cohérence de cette base

  • On écrit une procédure de réservation d’un billet pour un spectacle.

    Reserver (v_idSpectateur, v_idSpectacle, v_categorie,v_ prix)
      # Requête SQL de mise à jour du spectacle
      requêteA
      # Requête SQL d'insertion du billet
     requêteB
    

Donnez les deux requêtes SQL, indiquez l’emplacement du commit et justifiez-les brièvement.

Correction de l’examen

Conception

Le schéma E/A est donné dans la Fig. 55. Notez que l’on ne représente pas les clés étrangères comme attributs des entités: les clés étrangères sont le mécanisme du modèle relationnel pour représenter les liens entre entités. Dans le modele EA, ces liens sont des associations, et il serait donc redondant de faire figure également les clés étrangères

Le nommage des assocations et des attributs est libre, l’important est de privilégier la clarté et la précision, notamment pour les associations.

_images/exam-blanc-19.png

Fig. 55 Le schéma E/A après rétro-conception

Le type d’entité Billet est issu d’une réification d’une association plusieurs-plusieurs. La principale différence est que les billets ont maintenant leur identifiant propre, alors que l’association plusieurs-plusieurs est identifiée par la paire (idSpectacle, idPersonne).

Voici la commande de création de la salle. On a choisi de mettre not null partout pour maximiser la qualité de la base, ce sont des contraintes que l’on peut facilement lever si nécessaire.

create table Spectacle (id integer not null,
       idArtiste integer not null,
       idSalle integer not null,
       dateSpectacle date not null,
       nbBilletsVendus integer not null,
       primary key (id),
       foreign key (idArtiste) references Personne (id),
       foreign key (idSalle) references Salle (id)
       )

Dans le schéma relationnel proposé, il est possible d’avoir plusieurs billets (avec des identifiants distincts) pour le même spectacle et le même spectateur. C’est un effet de la réification: on a perdu la contrainte d’unicité sur la paire (idSpectacle, idPersonne).

On peut se poser la question d’ajouter ou non cette contrainte. Si oui, alors (idSpectacle, idSpectateur) devient une clé secondaire, que l’on déclare avec la clause unique dans la commande de création de la table Billet.

create table Billet (id integer not null,
       idSpectacle integer not null,
       idSpectateur integer not null,
       catégorie varchar(4) integer not null,
       prix float non null,
       primary key (id),
       unique (idSpectacle, idSpectateur),
       foreign key (idSpectacle) references Spectacle (id),
       foreign key (idSpectateur) references Personne (id)
       )

SQL

Deux requêtes sont équivalentes si elles donnent toujours le même résultat, quelle que soit la base.

select p.nom
from Personne as p, Spectacle s, Salle l
where p.id = s.idArtiste AND s.idSalle = l.id
AND l.nom = 'Bercy' AND p.age >= 70
select p.nom as nomArtiste, s.nom as nomSalle, s.nbBilletsVendus
from Peronne as p, Spectacle as sp, Salle as s
where p.id = sp.idArtiste
and sp.idSalle = s.id
order by s.nbBilletsVendus
select prénom, nom
from Personne as p
where not exists (select '' from Billet where idSpectateur = p.id)
select p.nom, count(*), sum(prix)
from Personne as p, Billet as b
where p.id = b.idSpectateur
group by p.id, p.nom
having count(*) >= 10

Algèbre

  • La requête 4 renvoie 2 nuplets: (1,1) et (6,6)
  • La requête renvoie 3 nuplets: (1,0,1), (1,0,7) et (4,1,2)
  • La requête renvoie également 3 nuplets
  • \(\pi_{nom, prenom}(Personne \underset{id=idSpectateur}{\bowtie} \sigma_{prix > 500}(BILLET))\)
  • \(\pi_{id}(Personne) - \pi_{idArtiste} (Spectacle)\)

Transactions

Le mode sérialisable entraîne des rejets de transactions.

Le nombre de billets vendus (nbBilletsVendus dans Spectacle) pour un spectacle est égal au nombre de lignes dans Billet pour ce même spectacle.

Autre possibilité,: pas plus de billets vendus que de places dans la salle.

Un update de Spectacle pour incrémenter nbBilletsVendus,

update Spectacle set nbBilletsVendus=nbBilletsVendus+1 where idSpectacle=v_idSpectacle

et un insert dans Billet.

insert into Billet (id, idSpectateur, idSpectacle, catégorie, prix)
value (1234, v_idSpectateur, v_idSpectacle, v_catégorie, v_prix)

Le commit vient à la fin car c’est seulement à ce moment que la base est à nouveau dans un état cohérent.