Agence de voyages

[45 min]

Une agence de voyage propose des prestations de logement de vacances de type hôtel, location ou gîte. Son catalogue est public et peut être consulté par tous les utilisateurs.

Elle dispose par ailleurs d'un fichier client avec les prestations effectivement vendues, à des prix éventuellement négociés en dessous du prix public. Ces données ne sont accessibles qu'à l'utilisateur "Gerant".

Le schéma UML ci-dessous représente le problème posé. On notera que Code et Numéro sont deux clés naturelles préalablement identifiées (mais il peut y avoir d'autres clés non encore identifiées). le niveau de prestation correspond au nombre d'étoiles de 1 à 5.

Schéma UML du système de l'agence de voyage

Question

Analyser la relation d'héritage et proposer la meilleure solution pour le passage au modèle relationnel.

Solution

L'héritage est complet et exclusif, la classe mère est abstraite. Les deux solutions de transformation par la classe mère et pas les classes filles sont possibles, mais l'héritage par la classe mère est beaucoup plus simple dans ce cas (solution à 3 tables seulement).

On choisit donc une transformation de l'héritage par la classe mère.

Question

Proposer un modèle logique relationnel.

Solution

1
Prestations (#code:integer, intitule:string, description:string, lieu:string,  niveau:integer, prixPublic:float, photo:BLOB, type:{H,L,G}) WITH type NOT NULL
2
Clients (#numero:integer, nom:string, prenom:string)
3
Achete (#client=>Clients(numero), #prestation=>Prestation(code), prix:float)
4
- Constraints : {niveau ≤ 5}

Question

Écrire en algèbre relationnelle la ou les vues induites par la transformation de la relation d'héritage.

Solution

1
vHotel = Restriction (Prestations, Prestations.type = 'H')
2
vLocation = Restriction (Prestations, Prestations.type = 'P')
3
vGite = Restriction (Prestations, Prestations.type = 'G')

Question

Noter que l'intitulé d'une prestation est unique et toujours renseigné d'une part ; et que le niveau de la prestation (nombre d'étoiles), le lieu ainsi le type de logement (hôtel, location ou gîte) permettent de déterminer le prix public d'autre part.

Préciser pourquoi le fait que les prix pratiqués peuvent être négociés en dessous du prix public permet d'écarter la DF Prestation → Prix dans la relation Achete.

Énoncer pour chaque relation du schéma la liste des clés et des DF[1].

Solution

Si les prix sont fixes pour tous les clients, alors Prestation → Prix dans la relation Achete. Mais comme les prix peuvent être négociés pour chaque client, il faut connaître la prestation et le client pour connaître le prix. Par exemple si le prix public est de 100 et que le client2 a eu 10% de remise alors on obtient dans la relation Achete : (client1, prestat1, 100) ; (client2, prestat1, 90).

1
Prestations : les clés sont (code) et (intitule)
2
{code → intitule, description, lieu, niveau, prixPublic, photo, type ; intitule → code, description, lieu, niveau, prixPublic, photo, type ; (niveau, lieu, type) → prixPublic}
3
Clients : La clé est (numero)
4
{numero → nom, prenom}
5
Achete : La clé est (client, prestation)
6
{(client, prestation) → prix}

Question

En quelle forme normale est le schéma relationnel ? Justifier avec précision. Le schéma est-il redondant ? Si oui donner un exemple, sinon expliquer pourquoi.

Solution

Le schéma est en 2NF.

Le schéma est en 1NF car toutes les relations ont au moins 1 clé et tous les attributs sont atomiques.

Le schéma est en 2NF car :

  • Pour Prestations et Clients, les clés (code), (intitule), (numero) sont composées d'un seul attribut

  • Pour Achete, prix ne dépend pas de Clients ou Prestations seulement (car le prix peut être négocié par le client pour chaque prestation donc Prestations ne détermine pas prix).

Le schéma n'est pas en 3NF car (niveau, lieu, type) → prix, donc il existe une DFE[2] vers un attribut n'appartenant pas à une clé qui n'est pas issue d'une clé.

La schéma est donc redondant, en effet on aura par exemple :

Exemple de données redondantes dans la relation Prestation

Question

Écrivez les instructions SQL LDD[3] permettant de créer la base de données PostgreSQL correspondant au schéma relationnel.

Indice

Pour la gestion des BLOB[4], on s'inspirera du code ci-dessous valide sous PostgreSQL :

1
CREATE TABLE Fruit (name CHAR(30), image OID);
2
INSERT INTO Fruit (name, image) VALUES ('peach', lo_import('/usr/images/peach.jpg'));

Solution

1
CREATE TABLE Prestations (
2
  pkCode INTEGER PRIMARY KEY,
3
  intitule VARCHAR UNIQUE NOT NULL,
4
  description VARCHAR,
5
  lieu VARCHAR,
6
  niveau INTEGER CHECK (niveau < 6),
7
  prix DECIMAL(4,2),
8
  photo OID,
9
  type CHAR(1) NOT NULL CHECK (type IN ('H','L','G'))
10
) ;
11
12
CREATE TABLE Clients (
13
  pkNumero INTEGER PRIMARY KEY,
14
  nom VARCHAR,
15
  prenom VARCHAR
16
) ;
17
18
CREATE TABLE Achete (
19
  fkClient INTEGER,
20
  fkPrestation INTEGER,
21
  prix DECIMAL (4,2),
22
  PRIMARY KEY (fkClient, fkPrestation),
23
  FOREIGN KEY (fkClient) REFERENCES Clients(pkNumero),
24
  FOREIGN KEY (fkPrestation) REFERENCES Prestations(pkCode)
25
) ;
Question
Afin d'assurer la confidentialité du système écrivez les instructions SQL LCD[5] adaptées.
Solution
1
GRANT SELECT ON Prestation TO PUBLIC;
2
3
GRANT ALL PRIVILEGES ON Prestation TO Gerant;
4
GRANT ALL PRIVILEGES ON Client TO Gerant;
5
GRANT ALL PRIVILEGES ON Achete TO Gerant;