Tutoriel de Bases de Données Relationnelles

Corrigé du contrôle pour les EI 00/01

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>