Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle pour les EM 97/98
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 1h30 Date : 10/06/98
Coordonnateur :
Mr Defude
____________________________________________________
Question 1
Donner le schéma Entité-Association correspondant à ce schéma relationnel. Vous préciserez bien les entités, entités faibles (s'il y a lieu), associations, propriétés, clés et cardinalités des associations.
Le formalisme utilisé importe peu s'il est lisible et précis. Epreuves est faible par rapport à Concours car la clé de Epreuves comprend celle de Concours. Pour une entité faible non seulement il faut indiquer qu'elle est faible (au moyen d'un rectangle double ici), mais aussi par rapport à qui elle est faible (en indiquant l'association supportant cette propriété par un double losange ici). Il est évident que les attributs de type clé étrangère (par exemple nomconc dans Ecoles) au niveau du schéma relationnel n'ont pas à être représentés sur le schéma Entité-Association comme attributs d'une relation (ils sont présents implicitement dans l'association qu'ils représentent). C'est une erreur très grave de faire cela.
Question 2
Donner la définition complète de la relation ECOLES dans le langage SQL d'Oracle. Par définition complète, on entend la définition des attributs et de leur domaine, mais aussi la définition de toutes les contraintes d'intégrité associées.
CREATE TABLE ECOLES (
NOMECOLE CHAR(20) PRIMARY KEY,
ADRESSE CHAR(100),
NBPLACES INTEGER CONSTRAINT CNBP
(CHECK NBPLACES BETWEEN 50 AND 200),
NOMCONC CHAR(20) CONSTRAINT CCONC
FOREIGN KEY ON CONCOURS (NOMCONC)
)
On aurait pu également rajouter une contrainte de non nullité sur l'attribut ADRESSE.
Question 3
Exprimer chacune des 3 questions suivantes en algèbre relationnelle.
(Q3.1) Donner le nom des candidats inscrits au concours lié à l'école de nom "INT Management".
(Q3.2) Donner le nom des épreuves du concours lié à l'école de nom "Telecom INT".
(Q3.3) Donner le nom des concours où il n'y a aucun inscrit.
Question 4
Exprimer les trois requêtes suivantes en SQL. Attention, la requête 3 est en fait une vue relationnelle.
(Q4.1) Donner la note maximale et la note minimale obtenues à l'épreuve de nom "informatique" du concours de nom "bon concours".
SELECT MIN(note), MAX(note)
FROM RESULTATS
WHERE nomconc='Bon concours' AND nomepreuve='informatique'
Il n'y a pas besoin de GROUP BY puisque l'on veut la réponse non pas pour toutes les épreuves ou tous les concours, mais pour un couple donné de concours et d'épreuve. On peut également utiliser plusieurs agrégats dans un même SELECT (ici MIN et MAX).
(Q4.2) Donner le numéro et le nom des candidats inscrits à tous les concours.
Deux solutions possibles selon que l'on utilise la double négation ou la solution par comptage.
Solution par double négation
SELECT C.nocand, C.nom
FROM CANDIDATS C
WHERE NOT EXISTS
(SELECT *
FROM CONCOURS CO
WHERE NOT EXISTS
(SELECT *
FROM INSCRITS I
WHERE I.nocand =C.nocand
AND I.nomconc=CO.nomconc
)
)
Solution par comptage :
SELECT C.nocand, C.nom
FROM CANDIDATS C, INSCRITS I
WHERE I.nocand=C.nocand
GROUP BY C.nocand, C.nom
HAVING COUNT(*) = (SELECT COUNT(*) FROM CONCOURS)
Il faut penser à grouper par nocand (clé de CANDIDATS) et par nom (puisque l'on veut le sélectionner). Chaque partition contient donc le nombre d'inscriptions d'un candidat que l'on va comparer au nombre total de CONCOURS.
(Q4.3) Donner la définition de la vue MOYENNE(nomconc, nocand, nom, note_moyenne) qui donne pour chaque concours (désigné par son nom) et chaque candidat (désigné par son numéro et son nom) la moyenne des notes obtenues sur toutes les épreuves du concours.
CREATE VIEW MOYENNE(nomconc, nocand, nom, note_moyenne)
AS
SELECT R.nomconc, C.nocand, C.nom, avg(note)
FROM RESULTATS R, CANDIDATS C
WHERE R.nocand=C.nocand
GROUP BY R.nomconc, C.nocand, C.NOM
Penser à rajouter le nom du candidat dans les critères de groupement.
Question 5
Soit le schéma relationnel M(NE, A, NB, NC, P, NP, C).
(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.
NE dérive A, NB, NC, P
NC dérive P
NP dérive C
Seuls NE et NP ne sont pas en partie droite des DF donc ils appartiennent forcément à la clé. Ici c'est même la clé puisqu'ils dérivent tous les autres.
Le schéma a donc une clé qui est le couple d'attributs (NE, NP)
Attention, ce n'est pas la même chose de dire que (NE, NP) est une clé à deux attributs par rapport à NE clé et NP clé (donc deux clés) !
(Q5.2) Donner la forme normale de ce schéma et s'il n'est pas en 3FN proposer une décomposition en 3FN.
Le schéma est forcément en 1FN. Il est en 2FN si les DF de type clé vers attribut non clé sont élémentaires. Ce n'est pas le cas ici puisque la clé est (NE, NP) et donc par exemple la DF NE donne A n'est pas élémentaire.
S'il avait été en 2FN, il serait en 3FN si les attributs non clés ne dépendent pas d'autres attributs non clés. Ici les attributs clés sont NE et NP (attention on appelle attribut clé un attribut qui appartient à une clé et non pas un attribut qui est complètement une clé !). Il y a une dépendance entre NC et P qui ne sont pas des attributs clés donc le critère de 3FN n'est pas rempli.
En conclusion le schéma est en 1FN.
On peut mettre en oeuvre l'algorithme de décomposition pour obtenir un schéma en 3FN : (attention la solution n'est pas unique)
- on décompose à travers la DF : NC -> P
on obtient R1(NC, P) il reste R(NE, A, NB, NC, NP, C)
- on décompose selon la DF : NP -> C
on obtient R2(NP, C) il reste R(NE, A, NB, NC, NP)
- on décompose selon la DF : NE -> A, NB, NC
on obtient R3(NE, A, NB, NC) et il reste R(NE, NP)
R(NE, NP) n'a pas d'intérêt puisqu'il n'y a pas de lien entre ces deux attributs. Il nous reste donc les relations R1, R2 et R3.
La clé de R1 est NC, celle de R2 est NP et celle de R3 est NE. Chacune est en 3FN donc le schéma est en 3FN.