Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle pour les EM 96/97
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 1h30 Date : 04/06/97
Documents autorisés : Coordonnateur :
ceux distribués en cours Mr Defude
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barême indicatif
Question 1 : 2 points
Question 2 : 2 points
Question 3 : 6 points (Q3.1 = 1 pt, Q3.2 = 1 pt, Q3.3 = 2 pts, Q3.4 = 2pts)
Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)
Question 5 : 4 points (Q5.1 = 2 pts, Q5.2 = 2 pt)
Question 1
Compléter la définition de ce schéma en rajoutant toutes les contraintes d'intégrité qui vous paraissent souhaitables pour améliorer la qualité des données. Ces contraintes seront décrites en français.
On peut définir les contraintes d'intégrité référentielle, les contraintes de domaine (l'attribut tour n'a que deux valeurs par exemple). D'autres contraintes sont plus sémantiques :
- un candidat ne peut être présent au deuxième tour s'il n'est pas présent au premier
- le total des voix obtenues dans une circonscription par l'ensemble des candidats d'un tour donné est inférieur ou égal au nombre d'inscrits
- on ne peut obtenir de résultats à un tour où on ne se présente pas
Question 2
Soit le schéma entité-association suivant extrait du cours :
Proposer une traduction de ce schéma avec attribut multi-valué vers un schéma entité-association sans attribut multi-valué (type MCD Merise par exemple). Est ce que cette traduction préserve la sémantique initiale ? Si non, qu'est ce qui est perdu ?
La sémantique initiale est préservée, le seul élément qui n'est pas fixé est la cardinalité entre Localisation et Departement. A priori on peut fixer 1,1 ce qui veut dire qu'une localisation n'est pas partagée (c'est le cas dans la modélisation initiale).
Question 3
Exprimer chacune des 4 questions suivantes en algèbre relationnelle.
(Q3.1) Donner le nom et le nombre de voix obtenues au premier tour par les candidats du parti de nom "INT demain".
R = RESULTATS C = CANDIDATS P = PARTIS
E = ELECTEURS
(Q3.2) Donner les noms des élus sortants qui se représentent au premier tour dans la circonscription dont ils sont les élus.
CA = CANDIDATS E = ELECTEURS CI = CIRCONSCRIPTIONS
(Q3.3) Donner le numéro d'électeur du candidat en tête des résultats du premier tour dans la circonscription de numéro 10.
RESULTAT1:=
R = RESULTATS E = ELECTEURS
RESULTAT1 représente le numéro d'électeur et le nombre de voix obtenues au premier tour par les candidats de la 10ème circonscription.
RESULTAT2 := RESULTAT1
idem pour RESULTAT2
Le sous arbre de gauche représente l'ensemble des candidats du premier tour dans la 10ème circonscription. Le sous arbre de droite calcule l'ensemble des candidats qui ont eu un nombre de voix inférieur à un autre candidat (ce qui est exprimé dans la sélection qui suit le produit cartésien). La différence entre les deux sous arbres calcule donc bien le (ou les) candidat qui a eu le plus de voix.
(Q3.4) Donner le nom des partis qui présentent un candidat au premier tour dans toutes les circonscriptions.
C = CANDIDATS P = PARTIS E = ELECTEURS
CI = CIRCONSCRIPTIONS
Il s'agit ici d'une question impliquant une division. Le diviseur (sous arbre de droite) représente l'ensemble des circonscriptions. Le sous arbre de gauche calcule les couples nom de parti, numéro de circonscriptions tels qu'un candidat de ce parti se présente au premier tour dans cette circonscription.
Question 4
Exprimer les trois vues suivantes en SQL
(Q4.1) Vue qui donne pour chaque numéro et nom de circonscription, le nombre de candidats au premier tour.
CREATE VIEW NBCANDIDAT(numéro, nom, nombre) AS
SELECT CI.nocirc, CI.nomcirc, count(*)
FROM CANDIDATS CA, CIRCONSCRIPTIONS CI, ELECTEURS E
WHERE CA.noelecteur=E.noelecteur AND E.nocirc=CI.nocirc AND tour=1
GROUP BY CI.nocirc, CI.nomcirc
(Q4.2) Vue qui donne pour chaque numéro et nom de parti le plus grand nombre de voix obtenues dans une circonscription et le plus mauvais (pour le premier tour de scrutin seulement).
CREATE VIEW RESEXTREME(numéro, nom, minimum, maximum) AS
SELECT P.noparti, P.nomparti, min(nbvoix), max(nbvoix)
FROM PARTIS P, CANDIDATS CA, RESULTATS R
WHERE P.noparti=CA.noparti AND R.noelecteur=CA.noelecteur AND tour=1
GROUP BY P.noparti, P.nomparti
(Q4.3) Vue qui donne pour chaque numéro et nom de parti le pourcentage total de voix obtenues au premier tour de scrutin. Par pourcentage total on entend le rapport entre le nombre total de voix obtenues et le nombre total d'inscrits. On ne considère ici que les partis qui présentent un candidat dans plus de 50 circonscriptions.
CREATE VIEW POURCENTAGE(numéro, nom, pourcent) AS
SELECT P.noparti, P.nomparti, sum(nbvoix)/sum(nbinscrits)
FROM PARTIS P, CANDIDATS CA, RESULTATS R,
CIRCONSCRIPTIONS CI, ELECTEURS E
WHERE P.noparti=CA.noparti AND R.noelecteur=CA.noelecteur AND
CA.noelecteur=E.noelecteur AND E.nocirc=CI.nocirc AND tour=1
GROUP BY P.noparti, P.nomparti
HAVING count(*) > 50
Question 5
Soit le schéma relationnel M(V1, N, R1, V2, C, R2, Q) avec les dépendances fonctionnelles suivantes (il s'agit de la fermeture transitive) :
{ V1 -> N; V1 -> R1; V2 -> C; V2 -> R2;
V1, V2 -> Q; C -> R2}
(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.
V1, V2 (le couple) est la clé de ce schéma. V1 et V2 appartiennent forcément à la clé puisqu'ils ne sont jamais en partie droite d'une DF. Comme V1, V2 est déjà clé, elle est unique.
(Q5.2) Donner la forme normale de ce schéma et s'il n'est pas en 3FN proposer une décomposition en 3FN.
- ce schéma est en 1FN par définition
- il est en 2FN s'il est en 1FN et que tous les attributs non clés dépendent pleinement des clés (DF entre clé et attribut non clé est élémentaire) :
attributs clés : V1, V2
attributs non clés : N, R1, C, R2, Q
N par exemple ne dépend que de V1 donc la DF V1, V2 donne N n'est pas élémentaire. Le schéma n'est donc pas en 2FN, a fortiori il n'est pas en 3FN et il est donc en 1FN.
On peut proposer une décomposition de ce schéma qui soit en 3FN :
R1(V1, N, R1) avec V1 donne N, R1 donc clé et schéma en 3FN
R2(V1, V2, Q) avec V1, V2 clé et V1, V2 donne Q et schéma en 3FN
R3(V2, C) avec V2 clé et V2 donne C et schéma en 3FN
R4(C, R2) avec C clé et C donne R2 et schéma en 3FN.