Examen Corrigé Base de données SGBD et Analyse Merise SQL et algèbre relationnelle

- Consulter la page de l'examen.

- Correction de l'examen.

Exercice 1. Modèle conceptuel   (5 points)

2. Décrire le domaine de chaque attribut (date, chaînes de caractères, nombre entier, ...) (1)

Sauf précision les propriétés des attributs sont valeurs nulles non admises, monovalué et atomique.

3. Matérialiser les clés des types d'entité en soulignant dans le graphe le nom du ou des attributs qui composent ces clés ; (1)

4. Donner la cardinalité de chaque type d'association sous la forme habituelle d'un couple (minimum, maximum). Justifier en quelques lignes les cardinalités de chaque type d'association. (1)

 Exercice 2. Schéma relationnel (5 points)

1. Réaliser le passage à un schéma relationnel en détaillant les étapes ;

Étape 1 :

SALLES (NumS, NomSalle, NbPlaces, Surface)
MANIFESTATION (NumM, NomM, Type, Durée)
CLIENT (NumC, NomC, NomS, Tél, Adresse, PréC)

Étape 2 : Aucune

Étape 3 : Aucune

Étape 4 :

RESERVEPOUR (NumS, NumM)
BILLET (NumC, NumM, DateB, Type)

Étape 5 :

 
EQUIPEMENT (NumS, Equipement)
DATE (NumM, Date)

2 Indiquer et justifier les clés. (2)

 
• SALLES : NumS est clé car
NumS -> NbPlaces, Surfaces, NomSalles, Equipement est un DEF par définition de l'attribut NumS

• MANIFESTATION : NumM est clé car
NumM -> NomM, Date, Type, Durée est une DFE par définition de l'attribut NumM

• CLIENT : NumC est clé car
NumC -> NomC, PréC, Adresse, Tél, NomS est une DFE par définition de l'attribut NumC

• RESERVEPOUR : NumS, NumM est clé car
NumS, NumM -> ?  est une DFE car on n'a pas

1. NumS -> NumM, plusieurs manifestations peuvent avoir lieu dans la même salle (à des
dates différentes)
2. NumM -> NumS, une manifestation peut avoir lieu dans plusieurs salles

• BILLET : NumC, NumM est clé car
NumC, NumM ->DateB, Type est une DFE car on n'a pas
1. NumC -> NumM, DateB, Type, un client peut avoir des billets pour plusieurs
manifestations
2. NumM -> NumC, DateB, Type, des billets pour une manifestation peuvent être vendus à
plusieurs clients

• EQUIPEMENT : NumS, Equipement est clé car
NumS, Equipement -> ?  est une DFE car on n'a pas

1. NumS -> Equipement, une salle peut être équipée de plusieurs équipements,
2. Equipement -> NumS, un même type d'équipement peut se trouver dans plusieurs salles

Exercice 3. Requêtes en algèbre relationnelle (5 points)

1. Donner le nom des hôtels et leur catégorie dans lesquels le client Dupont a effectué une réservation. (1)

(CLIENT:NomC= « Dupont ») * RESERVATION * HOTEL [NomHotel]

2. Donner les types d'activités proposées pour chaque hôtel 4 étoiles par la station où il se trouve. (1)

(HOTEL:Cat=4) * STATION * ACTIVITE [TypeActivité]

3. Donner les types d'activités proposées par toutes les stations. (1)

(ACTIVITE / STATION[NomStation])[TypeActivité]

4. Donner le nom des stations proposant les activités tennis et piscine. (1)

((ACTIVITE1:TypeActivité= « Piscine ») x
(ACTIVITE2:TypeActivité= « Tennis »)  : ACTIVITE1.NumStation =
ACTIVITE2.NumStation) * STATION[NomStation]

5. Donner le nom des hôtels n'ayant aucun client pour la date du 1er  janvier.  (1)

HOTEL [NomHotel] -
(RESERVATION : Date < 1/1 et Nb + Date > 1/1)[NomHotel] 

Exercice 4. Requêtes en SQL (5 points)

 Les requêtes suivantes font référence à la même base de données que celle décrite dans l'exercice 3. Le but de l'exercice est d'écrire les requêtes suivantes dans le language SQL.

1. Donner le nom des stations ne disposant pas d'hôtel 5 étoiles. (1)

(select NomStation
from STATION)
minus
(select NomStation
from STATION, HOTEL
where STATION.NumStation = HOTEL.NumStation
and Cat = 5)

ou bien

select NomStation
from STATION, HOTEL
where STATION.NumStation = HOTEL.NumStation
and Cat <> 1
and Cat <> 2
and Cat <> 3
and Cat <> 4

2. Donner pour chaque client (donné par son nom) le nom de l'hôtel, le nom de la station et les dates de ses
séjours. (1)

select NomClient, NomHotel, NomStation, Date, Date+NbJours
from STATION S, HOTEL H, RESERVATION R, CLIENT C
where S.NumStation = H.NumStation
and H.NumHotel = R.NumHotel
and R.NumClient = C.NumClient

3. Donner l'altitude moyenne des stations accessibles depuis la gare d'Alberville. (1)

select avg (Altitude)
from  STATION
where G are = « Albertville »

4. Donner pour chaque station le nombre de chambres disponibles. Préciser le nom de la station. (1)

select NomStation, sum (NbChamb)
from  HOTEL, STATION
where  HOTEL.NumStation = STATION.NumStation
group by  NumStation

5. Donner le nombre d'activités que peut effectuer le client Dupont pour chacune des stations où il effectue un
séjour. Préciser le nom de la station. (1)

select NomStation, count (TypeActivité)
from  ACTIVITE A, CLIENT C, RESERVATION R, HOTEL H, STATIN S
where  A.NumStation = H.NumStation
and R.NumHotel = H.NumHotel
and R.NumClient = C.NumClient
and NomClient = « Dupont »
group by NumHotel, Date