INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD21
Durée : 1h30 Date : 20/12/2000
Documents autorisés : ceux distribués en cours
Coordonnateurs : Mme Carpentier, 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 : 5 points (Q1 = 5 pts)
Question 2 : 4 points (Q2.1 = 2 pts, Q2.2 = 2 pts)
Question 3 : 6 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pts)
Question 4 : 3 points (Q4.1 = 1,5 pts, Q4.2 = 1,5 pts)
Question 5 : 2 points (Q5 = 2pts)
Le conseil général
souhaiterait disposer d’une vue globale de ses transports urbains. Pour
cela, il vous demande de gérer une base dont le schéma est le suivant :
COMPAGNIEBUS(raisonSociale, adresse)
LIGNE(noLigne, compagnie, nbArrets, départ, arrivée)
AFFECTATION(plaqueImmat, noLigne, compagnie, dateAffectation)
MECANICIEN(noMécanicien, nom)
BUS(plaqueImmat, marque, nbPlacesAssis, nbPlacesDebout, mecaResponsable)
Les clés sont soulignées et les clés étrangères sont en gras.
Question 1
Schéma Entité-Association résultat de la rétro-conception de ce schéma relationnel.
Question 2
Exprimer chacune des 2 questions suivantes, d’une part en algèbre relationnelle sous la forme d’un arbre algébrique, et d’autre part en SQL.
(Q2.1) Donner les lignes de bus (numéro, départ et arrivée) des compagnies de bus basées à ‘Evry’.
SELECT L.noligne, L.départ, L.arrivée
FROM COMPAGNIEBUS C, LIGNE L
WHERE L.compagnie=L.raisonsociale AND adresse=’Evry’
(Q2.2)
Donner la plaque d’immatriculation et la capacité d’accueil (places
assises et debout) des bus de la ligne 404 de la compagnie de raison
sociale ‘TICE’.
SELECT B.plaqueImmat, B.nbPlacesAssis, B.nbPlacesDebout
FROM BUS B, AFFECTATION A
WHERE A.plaqueImmat=B.plaqueImmat AND A.noLigne=404 AND A.compagnie=’TICE’
Dans cette requête on ne tient pas compte de la date d’affectation.
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Donner le nombre de lignes qui partent de ou arrivent à la station ‘Evry-Courcouronnes’.
SELECT COUNT(*)
FROM LIGNE
WHERE depart=’Evry-Courcouronnes’ OR arrivee=’Evry-Courcouronnes’
(Q3.2)
Donner pour chaque compagnie de bus, le nombre de ligne qu’elle possède
et le nombre moyen d’arrêts par ligne en ne considérant que les lignes
qui possèdent au moins 10 arrêts.
SELECT compagnie, COUNT(*), AVG(nbArrets)
FROM LIGNE
WHERE nbArrets >= 10
GROUP BY compagnie
Attention ici le filtre porte sur
une ligne (il faut qu’elle comporte plus de 10 arrêts) et non pas sur
une compagnie. Le filtre s’exprime donc simplement par un WHERE et pas
par un HAVING.
(Q3.3) Donner la raison sociale et l’adresse de la compagnie possédant le plus grand nombre de lignes.
SELECT C.raisonsociale, C.adresse
FROM COMPAGNIEBUS C, LIGNE L
WHERE C.raisonsociale=L.compagnie
GROUP BY C.raisonsociale, C.adresse
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM LIGNE
GROUP BY compagnie)
On groupe par raisonsociale ET
adresse parce qu’il faut sélectionner adresse (adresse ne sert à rien
dans le GROUP BY). Autre solution pour le HAVING, dire que COUNT(*)
>= ALL (SELECT COUNT(*) FROM LIGNE GROUP BY compagnie).
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Donner le nom des mécaniciens qui ne sont responsables d’aucun bus.
SELECT nom FROM MECANICIEN
WHERE noMecanicien IN
(SELECT noMécanicien
FROM MECANICIEN
MINUS
SELECT mecaResponsable
FROM BUS)
(Q4.2) Donner le nom des mécaniciens responsables d’au moins un bus de chaque marque.
SELECT M.Nom
FROM BUS B, MECANICIEN M
WHERE B.mecaResponsable=M.noMécanicien
GROUP BY M.noMécanicien, M.nom
HAVING COUNT(DISTINCT marque) =
(SELECT COUNT(DISTINCT marque) FROM BUS)
Question 5
<appli>
<entete>
<cgi> http://mica.int-evry.fr/cgi-bin/mowi_sql.cgi</cgi>
<methode>POST</methode>
<uid> applibus/applibus@MICA</uid>
</entete>
<liste_form>
<formulaire>
<nom_table>BUS</nom_table>
<mode><mode_ins></mode_ins></mode>
</formulaire>
<formulaire>
<nom_table>LIGNE</nom_table>
<mode><mode_nor>
<attribut>départ</attribut>
</mode_nor>
</mode>
</formulaire>
<formulaire>
<nom_table>BUS</nom_table>
<mode><mode_sup>
<attribut>plaqueImmat</attribut>
</mode_sup></mode>
</formulaire>
</liste-form>
</appli>
Posté le 4 septembre 2009