Corrigé Examen Final Bases de Données (2010/2011)

Ecole Supérieure de la Statistique et de l’Analyse de l’Information
‫اﻟﻣدرﺳﺔ اﻟﻌﻠﯾﺎ ﻟﻺﺣﺻﺎء وﺗﺣﻠﯾل اﻟﻣﻌﻠوﻣﺎت ﺑﺗوﻧس‬
Corrigé Examen Final
Bases de Données (2010/2011)
Questions de cours :
Répondre, brièvement mais précisément, aux questions suivantes :
Q1) A travers un exemple, décrire quels sont les inconvénients de schémas de BD non normalisés ?
redondances => anomalies lors des mises à jour
Exemple : Fournisseur (NF, NomF, TelF, Nprod,libellé)
Q2) Quels sont les différents types et utilités des Contraintes d’Intégrité que l’on peut ajouter à un
schéma SQL de définition de données ?
Contraintes de domaine, d'unicité, de clé primaire, de clé étrangère, etc..
Q3) Parmi les propositions suivantes, déterminer celles qui sont incorrectes (justifiez votre réponse)
:
a)- DECLARE
V_id CHAR(20) ;
correct
b)- DECLARE
v1, v2, v3
INTEGER ;
incorrect : une seule variable par ligne
Corrigé Examen Bases de Données (2011/2012)- F. CHAKER
Page 1
c)- DECLARE
date_naissance
DATE NOT NULL ;
incorrect : une valeur NOT NULL doit être initialisée
d)- DECLARE
stock
BOOLEAN :=1 ;
incorrect : 1 n’est pas une valeur booléenne
e)- Dans un bloc PL/SQL, il est possible d’exécuter des instructions du SQL DDL (Data
Definition Language), soit les instructions ALTER, CREATE et DROP.
incorrect : Dans un bloc PL/SQL, il n’est pas possible d’exécuter des instructions du SQL
DDL (Data Definition Language), soit les instructions ALTER, CREATE et DROP.
Exercice 1 : Normalisation
Etudier la relation suivante et proposer un schéma en 3ème forme normale en justifiant les
décompositions proposées :
Présentation (N°Conférencier, Nom_Conférence, Date_Conférence ,
Laboratoire_affiliation, Lieu_Conférence, Sujet_Présenté)
NomConférencier,
Sachant que :
 Une conférence est identifiée par son nom et la date à laquelle elle aura lieu
 Un conférencier ne présente qu’un seul sujet par conférence
 Un conférencier est affilié à un seul laboratoire de recherche
La clé candidate est composée de (N°Conférencier, Nom_Conférence, Date_Conférence)
N°Conférencier, Nom_Conférence, Date_Conférence  NomConférencier
N°Conférencier, Nom_Conférence, Date_Conférence  Laboratoire_affiliation
N°Conférencier, Nom_Conférence, Date_Conférence  Lieu_Conférence
N°Conférencier, Nom_Conférence, Date_Conférence  Sujet_Présenté
Présentation est en 1FN
Or on peut déduire :
N°Conférencier  NomConférencier
N°Conférencier  Laboratoire_affiliation
Nom_Conférence, Date_Conférence  Lieu_Conférence
Corrigé Examen Bases de Données (2011/2012)- F. CHAKER
Page 2
+   est une DFE
+  est une DFE
+  est une DFE
D’où Présentation n’est pas en 2FN
 Conférencier (N°Conférencier ,NomConférencier, Laboratoire_affiliation)
Conférence (Nom_Conférence, Date_Conférence,Lieu_Conférence)
Présentation (N°Conférencier, Nom_Conférence, Date_Conférence, Sujet_Présenté)
Ces 3 relations sont en 3FN
Exercice 2 :
Soit le schéma relationnel suivant :
Salle (NomS, Horaire, capacité, Titre*)
Film (Titre, réalisateur, durée, acteur)
Producteur (Producteur, Titre*)
Vu (Spectateur, Titre*)
Aime (Amateur, Titre*)
Sachant que :

Un film peut être produit par plusieurs producteurs

La relation Vu décrit les titres des films vus par les spectateurs. Evidemment un
spectateur peut voir plusieurs films

La relation Aime décrit les films appréciés par les spectateurs (amateurs). Evidemment,
un spectateur peut aimer plusieurs films.
1- Exprimez en Algèbre relationnelle ET en SQL les requêtes suivantes :
a)- Où et à quel horaire peut on voir le film « Ma belle Tunisie » ?
AR :
R1= Selection(Salle, Titre=’Ma belle Tunisie’
Corrigé Examen Bases de Données (2011/2012)- F. CHAKER
Page 3
R2= Projection (R1, Horaire, NomS)
SQL :
SELECT NomS, Horaire
FROM Salle
WHERE Titre=’Ma belle Tunisie’ ;
b)- Quels sont les acteurs qui jouent dans tous les films ?
AR :
R1= Projection(Film, Titre)
R2= Projection(Film, Titre, acteur)
R3= Division(R2,R1)
SQL :
SELECT acteur
FROM Film
GROUP BY acteur
HAVING COUNT(Titre)=(SELECT COUNT(*)
FROM Film);
c)- Quels sont les spectateurs qui aiment tous les films qu’ils voient ?
AR :
R1= Regrouper_et_calculer(Vu, Spectateur, N1=comptage(Titre))
R2= Regrouper_et_calculer(Aime, Amateur, N2=comptage(Titre))
R3= jointure(R1,R2, Spectateur=Amateur)
R4= selection (R3, N1=N2)
R5= projection(R4, Spectateur)
Corrigé Examen Bases de Données (2011/2012)- F. CHAKER
Page 4
SQL:
SELECT spectateur
FROM Vu
GROUP BY spectateur
HAVING COUNT(Titre)=(SELECT COUNT(Titre)
FROM Aime
GROUP BY Amateur)
2- Ecrire un bloc PL/SQL permettant d’afficher le nombre de films réalisés par chaque réalisateur
sous la forme (Le réalisateur : Foulen à réalisé XX films) .
Traiter le cas où la table Film est vide comme une Exception. Dans ce cas afficher un message de la
forme « Pas de films disponibles !! » (4,5 pts)
DECLARE
N
integer ;
Film_vide
EXCEPTION ;
CURSOR Film_Realisateur IS SELECT réalisateur, COUNT(Titre) AS NB FROM Film GROUP
BY réalisateur;
Un_enreg Film_Realisateur%ROWTYPE;
BEGIN
SELECT COUNT(Titre) INTO N FROM Film ;
If (N=0) THEN RAISE Film_vide;
End if;
Open Film_Realisateur;
FETCH Film_Realisateur INTO Un_enreg
WHILE Film_Realisateur%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(‘Le réalisateur’|| Un_enreg.réalisateur||’a realize’ || Un_enreg.NB
|| ‘films’);
FETCH Film_Realisateur INTO Un_enreg
End loop;
Close Film_Realisateur;
EXCEPTION
WHEN Film_vide THEN DBMS_OUTPUT.PUT_LINE(‘Pas de films disponibles !!’) ;
END ;
Corrigé Examen Bases de Données (2011/2012)- F. CHAKER
Page 5