Examen Corrige SQL et base de données SGBD et Analyse Merise la normalisation et Algèbre relationnelle – Calcul relationnel

Objectifs : - Appliquer les différentes méthodes  de normalisation.

                  - Algèbre relationnelle 

                  - Calcul relationnel 

                  - Utiliser le langage SQL.

 
 Exercice 1 : Questions de Cours (2 pts)

a) Expliquer les instructions Commit et Rollback en SQL.


b) Que signifie l’acronyme « IP » et à quoi correspond-t-il ?

Exercice 2 Normalisation (4 pts)

Soit la relation RESTAURANT (NuméroMenu, NomMenu, NuméroPlat, NomPlat, TypePlat)
et l’ensemble de Dépendances Fonctionnelles D = { NuméroMenu ? NomMenu, NuméroMenu
? NuméroPlat, NuméroPlat
? NomPlat, NuméroPlat ? TypePlat }.

a) Sous quelle Forme Normale se trouve la relation RESTAURANT ? Justifier.

b) Proposer une décomposition de la relation RESTAURANT en 3FN sans perte d’information.

 (Barème : 1 point pour la 1FN (si justification correcte), 1 point pour la 2FN (si justification correcte), 1 point pour la 3FN (si justification correcte)., 0,5 point si une justification est approximative, 0 point si seule la réponse globale est donnée, 1 point pour la décomposition (0,5 par relation ; enlever 0,5 point si oubli de la clé étrangère, de MENU ou d’un attribut).

Exercice 3 Algèbre relationnelle (4 pts)

Soit la base de données FABRICATION suivante :
CLIENT (NOC, NOM, ADRESSE)
SERVICE (NOS, INTITULE, LOCALISATION)
PIECE (NOP, DESIGNATION, COULEUR, POIDS)
COMMANDE (NOP, NOS, NOC, QUANTITE)


Proposer, en algèbre relationnelle, une formulation des requêtes suivantes.

a) Donner les valeurs de NOS pour les services qui ont commandé pour le client C1 :

b) Donner les valeurs de NOS pour les services qui ont commandé une pièce P1 pour le client C1.


c) Donner les valeurs de NOS pour les services qui ont commandé une pièce de couleur ivoire pour le client C1.

d) Donner les valeurs de NOC pour les clients qui ont en commande au moins toutes les pièces commandées par S1.

Exercice 4 Cirque – SQL (5 pts)

Soit la base de données CIRQUE1 dont l’extension est donnée ci-dessous :
1 E. Pichat, R. Bodin, « Ingénierie des données », Masson 1990

Donner, au format SQL, les requêtes correspondantes aux questions suivantes :

a) Créer la structure des quatre tables de la base en intégrant les contraintes d’intégrité nécessaires (ne pas oublier de clé étrangère !).

b) Nature de tous les numéros avec suppression des doubles.

c) Camions et volumes des accessoires, par ordre croissant des camions et, pour chaque camion, par ordre décroissant des volumes.

d) Accessoires de volume supérieur ou égal à 0,3 ou de couleur rouge.

e) Accessoires de volume compris entre 0,3 et 0,6.

f) Accessoires rangés dans un râtelier dont le numéro est égal à son numéro de camion.

g) Substitution d’un trapèze pour lion à la chaise longue de lion.

h) Ajouter 10 au volume de la cage à lions.

i) Supprimer Crinière, qui démissionne du cirque.

j) Accessoires servant aux jongleurs.

Exercice 5 Algèbre relationnelle – Calcul relationnel

Considérons la base de données relationnelle suivante : 

Employé(nom-employé, rue, ville)
Travaille(nom-employé, nom-entreprise, salaire)
Entreprise(nom-entreprise, ville)

Soit la requête suivante :

Trouver les noms, les rues, et les villes de tous les employés qui travaillent pour FBC et qui gagnent plus de 20000 euros par année. On supposera que chaque personne travaille pour une entreprise au plus.

a) Donnez une expression en (i) algèbre relationnelle, (ii) calcul relationnel tuple, (iii) calcul relationnel domaine pour cette requête.

b) Donnez deux arbres syntaxiques équivalents (si possible) pour cette requête.

-------------------------------------------------------------------------
Corrigé