Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle pour les EM 99/00
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GIG20
Durée : 1h30 Date : 21/06/99
Documents autorisés : ceux distribués en cours
Coordonnateur : Mr Defude
Une entreprise de transport routier nous demande de mettre en place son système d’informations gérant son frêt. Le schéma relationnel suivant décrit la base de données (les clés sont en majuscules et les clés étrangères sont en gras) :
CHAUFFEUR(NOCHAUFFEUR, nomch, prenomch)
CAMION(NOIMMAT, type, poidstransp)
CONDUIRE(NOIMMAT, NOCHAUFFEUR, jour)
LOCALISATION(NOIMMAT, JOUR, lieumatin, lieusoir)
MARCHANDISE(NOMARCH, nommarch, typem, poids, datetr, lieud, lieua, nomclient)
TRANSPORTE(NOIMMAT, NOMARCH, dateaff)
Question 2
Donner la définition complète de la relation MARCHANDISE 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 MARCHANDISE (
NOMARCH INTEGER PRIMARY KEY,
NOMMARCH CHAR(80),
TYPEM CHAR(8) CONSTRAINT CTYPE
CHECK TYPEM IN ('frigo', 'citerne', 'palette', 'plateau'),
POIDS INTEGER,
DATETR DATE,
LIEUD CHAR(40),
LIEUA CHAR(40),
NOMCLIENT CHAR(60) NOT NULL
)
Question 3
Exprimer chacune des 3 questions suivantes en algèbre relationnelle (sous forme d’arbres algébriques).
(Q3.1) Donner le numéro d’immatriculation des camions qui ont transporté des marchandises pour le compte du client de nom ‘Martin’ après le 01.04.2000.
(Q3.2) Donner le nom et le numéro des chauffeurs qui ont conduit des camions de type ‘citerne’ à la date du 01.03.2000.
(Q3.3) Donner le numéro d’immatriculation et le type des camions qui n’ont rien transporté le 01.05.2000.
On fait la différence entre l’ensemble des camions et ceux qui ont transporté quelque chose le 01052000. Bien pensez que pour utiliser un opérateur ensembliste il faut que les schémas soient homogènes (c’est pourquoi on fait la jointure CAMION, TRANSPORTE avant de projeter sur noimmat, type).
Question 4
Exprimer les trois requêtes suivantes en SQL. Attention, la requête 4.3 est en fait une vue relationnelle.
(Q4.1) Donner le poids total des marchandises transportées pour le compte du client de nom ‘Durand’ entre Paris et Grenoble.
SELECT SUM(M.poids)
FROM MARCHANDISE M, TRANSPORTE T
WHERE M.lieud='Paris' AND M.lieua='Grenoble' AND M.nomclient='Durand' AND M.nomarch=T.nomarch
Les marchandises transportées sont celles qui apparaissent dans TRANSPORTE (c’est pourquoi on fait la jointure).
(Q4.2) Donner le numéro des chauffeurs qui ont conduit tous les types de camions.
SELECT CO.nochauffeur
FROM CONDUIRE CO, CAMION CA
WHERE CO.noimmat=CA.noimmat
GROUP BY CO.nochauffeur
HAVING COUNT(distinct CA.type)= (SELECT COUNT(distinct type) FROM CAMION)
Pour avoir à la fois le numéro du chauffeur et le type de camion conduit, il faut faire la jointure entre CONDUIRE et CAMION. Ensuite on groupe par chauffeur pour avoir dans un groupe toutes les conduites faites par un chauffeur. Il nous reste ensuite à comparer les types de camions conduits par rapport à l’ensemble des types de camion. Comme on sait que les types de camions conduits sont forcément des types de camion, il suffit de comparer la taille des deux ensembles. Attention ici il faut utiliser distinct dans le COUNT car un chauffeur peut conduire deux camions du même type.
(Q4.3) Donner la définition de la vue NBCONDUITE(nochauffeur, nomch, nbcond) qui donne pour chauffeur (numéro et nom) le nombre de conduites effectuées.
CREATE VIEW NBCONDUITE(nochauffeur, nomch, nbcond)
AS
SELECT CH.nochauffeur, CH.nomch, COUNT(*)
FROM CONDUITE CO, CHAUFFEUR CH
WHERE CO.nochauffeur=CH.nochauffeur
GROUP BY CH.nochauffeur, CH.nomch
Question 5
Soit le schéma relationnel M(NC, NI, N, P, T, J) avec les dépendances fonctionnelles suivantes (il s’agit de la fermeture transitive) :
NC -> N; NC -> P; NC, NI -> N; NC, NI -> P; NI -> T;NC, NI -> J; NC, NI -> T
(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.
De l’étude de la fermeture transitive on voit qu’aucun attribut tout seul ne dérive tous les autres. Il faut prendre deux attributs pour dériver tous les autres qui sont NC et NI (ce sont d’ailleurs deux attributs qui ne sont jamais en partie droite des DF). La clé est donc le couple (NC, NI)
(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 en 1FN par définition. Il est en 2FN si aucun attribut non clé ne dépend d’une partie de la clé. Ici les attributs clés sont NC et NI et les attributs non clés sont N, P, T et J.
Ici on a NC qui donne N qui est un exemple d’attribut non clé dépendant partiellement d’une clé. Donc le schéma n’est pas en 2FN donc il est en 1FN.
Une décomposition possible du schéma (mais non l’unique) est la suivante :
R1(NC, N, P) en utilisant la DF : NC donne N, P avec NC clé
R2(NI, T) en utilisant la DF : NI donne T avec NI clé
R3(NC, NI, J) en utilisant la DF : NC, NI donne J avec NC, NI clé.