mercredi 23 mars 2011

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.

-------------------------------------------------------------------------

- Correction de l'examen.

Exercice 1 Cours (2 pts)

a) Expliquer les instructions Commit et Rollback en SQL.

Commit  : permet de valider l’ensemble des instructions effectuées sur une Base de Données.

Rollback : permet d’annuler toutes les instructions effectuées sur une Base de Données jusqu’au dernier commit ou bien jusqu’à la dernière création d’une table.

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

IP signifie INTERNET PROTOCOL ; il permet l’adressage (routage) des informations sur le réseau grâce à un numéro IP qui permet d’identifier de manière unique une machine sur le réseau.

 
Exercice 2 Normalisation (4 pts)


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

Tous les attributs de la relation RESTAURANT sont non-décomposables, elle est donc en 1FN. Par transitivité, NuméroMenu ? NomMenu,

NuméroPlat, NomPlat, TypePlat, donc tous les attributs dépendent de la clé primaire, donc
la relation RESTAURANT est en 2FN. Il existe des DF entre attributs non clé primaire, donc la relation RESTAURANT n’est pas en 3FN.

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

La décomposition MENU (NuméroMenu, NomMenu, NuméroPlat) PLAT (NuméroPlat, NomPlat, TypePlat) est en 3FN et préserve les DF.


 Exercice 3 Algèbre relationnelle (4 pts)

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 :

? NOS(? NOC=’C1’(Commande))

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

? NOS(? NOC=’C1’(? NOP=’P1’(Commande))

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

? NOS(? Couleur=’Ivoire’? NOC=’C1’(Pièce ?? Commande))

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)

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 !)

create table personnel (nom varchar(20), role varchar(20),
constraint personnel_priprimary key(nom));
create table numero (titre varchar(30), nature varchar(20),
responsable varchar(20), constraint numero_pri primary
key(titre), constraint numero_etr foreign key(responsable)
references personnel(nom));
create table accessoire (nom varchar(30), couleur
varchar(10), volume number(4,1), ratelier number(2), camion
number(1), constraint accessoire_pri primary key(nom));
create table utilisation (titre varchar(30), utilisateur
varchar(20), accessoire varchar(30), constraint
utilisation_pri primary key(titre, utilisateur,
accessoire), constraint utilisation_et1 foreign key(titre)
references
numero(titre), constraint utilisation_et2 foreign
key(utilisateur) references personnel(nom), constraint
utilisation_et3 foreign key(accessoire) references
accessoire(nom));

b) Nature de tous les numéros avec suppression des doubles
select distinct nature from numero;

c) Camions et volumes des accessoires, par ordre croissant des camions et, pour chaque camion, par ordre décroissant des volumes.
select camion, volume from accessoire order by camion,
volume desc;


d) Accessoires de volume supérieur ou égal à 0,3 ou de couleur rouge
select nom from accessoire where volume>=0.3 or
couleur='Rouge';


e) Accessoires de volume compris entre 0,3 et 0,6
select nom from accessoire where volume between 0.3 and 0.6;

f) Accessoires rangés dans un râtelier dont le numéro est égal à son numéro de camion
select nom from accessoire where ratelier=camion;

g) Substitution d’un trapèze pour lion à la chaise longue de lion
update utilisation set accessoire='Trapèze pour lion' where
accessoire='Chaise longue de lion';


h) Ajouter 10 au volume de la cage à lions
update accessoire set volume=volume+10 where nom='Cage à lions';

i) Supprimer Crinière, qui démissionne du cirque
delete from utilisation where nom='Crinière';
delete from numero where nom='Crinière';
delete from personnel where nom='Crinière';


j) Accessoires servant aux jongleurs
select distinct accessoire from utilisation, personnel
where nom=utilisateur and role='Jongleur';

0 commentaires:

Enregistrer un commentaire

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites

 

IP