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;
NC, NI -> J; NC, 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.
Posté le 4 septembre 2009