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
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
Question 2 : 2 points
Question 3 : 3 points (Q3.1 = 1 pt, Q3.2 = 1 pt, Q3.3 = 1 pt)
Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)
Question 5 : 4 points (Q5.1 = 1,5 pts, Q5.2 = 2,5 pts)
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)
La sémantique des diverses relations est la suivante :
CHAUFFEUR(NOCHAUFFEUR, nomch, prenomch) : un chauffeur est identifié de manière unique par un numéro et est décrit par son nom et son prénom,
CAMION(NOIMMAT, type, poidstransp) : un camion est identifié de manière unique par son numéro d’immatriculation et est décrit par son type (frigo, citerne, palette, plateau) et le poids total qu’il peut transporter,
CONDUIRE(NOIMMAT, NOCHAUFFEUR, jour) : un tuple dans cette relation signifie que le chauffeur identifié par son numéro a conduit le camion identifié par son numéro un jour donné (noimmat clé étrangère sur CAMION et nochauffeur clé étrangère sur CHAUFFEUR),
LOCALISATION(NOIMMAT, JOUR, lieumatin, lieusoir) : un tuple dans la relation LOCALISATION signifie que le camion identifié par son numéro d’immatriculation se trouve le jour désigné dans un lieu le matin et un autre lieu le soir (qui peuvent éventuellement être les mêmes). Cette relation permet de savoir où se trouve les camions pour pouvoir les affecter à des marchandises. Noimmat est clé étrangère sur CAMION,
MARCHANDISE(NOMARCH, nommarch, typem, poids, datetr, lieud, lieua, nomclient) : une marchandise est identifiée de manière unique par un numéro et est décrite par un nom, un type de camion requis pour son transport (même domaine que l’attribut type dans CAMION), un poids total à transporter (qui peut être supérieur à la capacité d’un camion, auquel cas il faudra plusieurs camions pour transporter la marchandise). Cette marchandise est possédée par un client (désigné par son nomclient) qui veut la faire transporter d’un lieu de départ (lieud) à un lieu d’arrivée (lieua) à une date donnée (datetr),
TRANSPORTE(NOIMMAT, NOMARCH, dateaff) : une ligne dans cette relation signifie qu’un client a proposé une marchandise (identifiée par son numéro, nomarch clé étrangère sur MARCHANDISE) à transporter à la date dateaff par le camion de numéro noimmat (noimmat clé étrangère sur CAMION). On considère que le transport s’effectue complètement sur la journée.
Question 1
Donner le schéma Entité-Association correspondant à ce schéma relationnel. Vous préciserez bien les entités, entités faibles (s’il y a lieu), associations, propriétés, clés et cardinalités des associations.
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.
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.
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.
(Q4.2) Donner le numéro des chauffeurs qui ont conduit tous les types de camions.
(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.
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;
(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.
(Q5.2) Donner la forme normale de ce schéma et s’il n’est pas en 3FN proposer une décomposition en 3FN.