Année universitaire 2019/2020
Site :
Luminy
St-Charles
St-Jérôme
Cht-Gombert
Sujet session de : 1er semestre - 2ème semestre - Session 2
Examen de : L1/L2/L3 - M1/M2 - LP - DU
Code Apogée du module : SIN3U04T
Aix-Montperrin
Aubagne-SATIS
Durée de l’épreuve : 2h
Nom diplôme : Licence d’informatique
Libellé du module : Bases de données relationnelles
Document autorisé : OUI - NON
Calculatrices autorisées : OUI - NON
EXAMEN
Exercice 1
On s'intéresse à une base de données qui décrit la gestion de projets relatifs au développement de logiciels.
Les schémas suivants sont utilisés pour regrouper les différentes données :
DEVELOPPEUR (NumDev, NomDev, PrenomDev, AdrDev, EmailDev, TelDev)
PROJET (NumProj, TitreProj, DateDeb, DateFin, ChefProjet)
LOGICIEL (CodLog, NomLog, PrixLog, NumProj)
REALISATION (CodLog, NumDev)
PARTICIPE (NumProj, NumDev)
La relation DEVELOPPEUR donne pour chaque développeur son numéro, nom, prénom, adresse postale,
adresse mail (EmailDev) et son téléphone (TelDev). La relation PROJET donne le numéro d’un projet, son
titre, une date de début et une date de fin ainsi que le numéro du développeur qui est le chef du projet. La
relation LOGICIEL donne le code d’un logiciel (CodLog), son nom, son prix et le numéro du projet auquel il
appartient. La relation REALISATION renseigne le logiciel ainsi que le développeur qui l’a réalisé. La
relation PARTICIPE donne le numéro du projet ainsi que le développeur qui participe à ce projet.
Remarques :
- L’absence de valeur est interdite sur tous les attributs.
- Les attributs ayant même nom qu'une clef primaire référencent cette clef (conformément au principe
d'intégrité de référence).
Question 1 (3 points) : Algèbre relationnelle
En utilisant les opérateurs relationnels, exprimez les requêtes suivantes :
1) Donner les numéros et les titres des projets.
REPONSE = PROJET [numProj, TitreProj]
(projection)
2) Donner le numéro des développeurs qui ont réalisé le logiciel numéro 101.
R1 = (REALISATION : codeLog=101)
(sélection)
REPONSE = R1[numDev]
(projection)
3) Donner les codes des logiciels dont le prix est supérieur à 200 et appartement au projet numéro 10.
R1 = (LOGICIEL : PrixLog > 200 et NumProj=10)
(sélection)
REPONSE = R1[codLog]
(projection)
4) Donner les caractéristiques des développeurs qui ne participent à aucun projet.
R1 = PARTICIPE[NumDev]
(projection)
R2 = DEVELOPPEUR[NumDev] - R1
(différence)
REPONSE = DEVELOPPEUR * R2
(produit)
5) Donner les noms des logiciels qui ont été réalisés par le développeur 'D20'.
R1 = (REALISATION : numDev= 'D20')
(sélection)
R2= [CodLog]
(projection)
R3 = LOGICIEL * R2
(produit)
REPONSE = R3[NomLog]
(projection)
Question 2 (7 points) : Requêtes SQL
Exprimez en SQL les requêtes suivantes :
1) Les codes des logiciels appartenant au projet numéro 101.
SELECT CodeLog
FROM LOGICIEL
WHERE NumProj=101 ;
2) Le nombre de logiciels réalisés par le développeur «D20»
SELECT count(CodLog) as nbreP
FROM REALISATION
WHERE numDev='D20';
3) Les numéros des développeurs qui n’ont pas réalisé de logiciels
SELECT numDev
FROM DEVELOPPEUR
WHERE numDev NOT IN (SELECT DISTINCT numDev FROM REALISATION);
4) La durée des projets gérés par le développeur numéro «D20»
SELECT NumProj, (dateFin- DateDeb) as Durée
FROM Projet
WHERE ChefProjet=’D20’;
5) Le total des prix des logiciels du projet numéro 101. Lors de l’affichage, le titre de la colonne sera «
cout total du projet »
SELECT SUM (PrixLog) as "cout total du projet"
FROM Logiciel
WHERE NumPRoj =101;
6) Les noms et les prix des logiciels appartenant au projet ayant comme titre « gestion de commandes »,
triés dans l’ordre décroissant des prix.
SELECT L.NomLog , L.PrixLog
FROM Logiciel L INNER JOIN Projet P
ON L.NumProj =P.NumProj
WHERE P.TitreProj ='gestion de commandes'
ORDER BY L.PrixLog DESC
7) Les numéros et titres des projets qui ont plus que 5 logiciels
SELECT P.NumProj, TitreProj
FROM PRojet P INNER JOIN Logiciel L ON P.NumProj=L.NumProj
GROUP BY P.NumProj
HAVING count(*)>5
8) Les numéros et noms des développeurs qui ont participé à tous les projets.
SELECT D.NumDev , NomDev
FROM Developpeur D INNER JOIN Participe R ON D.NumDev =R.NumDev
GROUP BY D.NumDev
HAVING count(*)=( SELECT COUNT (*) FROM Projet )
Question 3 (5 points) : Modélisation conceptuelle
Donnez le MCD (modèle conceptuel des données) de ce domaine. Vous prendrez comme dictionnaire
l’ensemble des attributs.
Exercice 2 (5 points) : Normalité et Décomposition
Soit le schéma SR({A, B, C, D, E, F, G, H, I, J }, F ) où F est l’ensemble de dépendances fonctionnelles
suivant :
F = { A,B->C; A->D,E; B->F; F->G,H; D->I,J }
Question 1 : Trouvez la clef de ce schéma.
Clef : {A,B}
Question 2 : Indiquez la normalité de SR. Justifiez votre réponse.
SR n’est pas en 2NF car A -> D, E et A est partie de clef et D est non-clef
Question 3 : Montrez que la DF A,F->J est déductible de F .
On calcule la fermeture de {AF}+F = {A,F,D,E,I,J} et cette fermeture contient bien {J}. Donc A,F->J est
déductible de F
Question 4: Donnez une décomposition qui élimine le plus possible les redondances tout en préservant les DF.
Indiquez la normalité des composantes de cette décomposition. Précisez les clefs de chaque relation obtenue.
L’ensemble des DF est une couverture minimale.
On obtient les schémas suivants :
SR1({A,B,C}, { A,B->C })=> BCNF
SR2({A,D,E}, { A->D,E })=> BCNF
SR3({B,F}, { B->F })=> BCNF
SR4({F,G,H}, { F->G,H })=> BCNF
SR5({D,I,J}, { D->I,J })=> BCNF
