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.
Posté le 4 septembre 2009