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

- Consulter la page l'examen.

- 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';