INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 2h00 Date : 20/06/01
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 : 5 points (Q4.1 = 1,5 pts, Q4.2 = 1,5 pts, Q4.3 = 2 pts)
Question 5 : 3 points (Q5.1 = 1 pt, Q5.2 = 2 pts)
Question 6 : 2 points (Q6.1 = 1 pt, Q6.2 = 1pt)
Un hébergeur de sites de
commerce électronique vous demande de gérer le système d’informations
permettant de mettre des catalogues de produits en ligne (les clés sont en gras et les clés étrangères sont en italique) :
CATALOGUE(nocatalogue, libcatalogue, entreprise, idffamille)
FAMILLE(idffamille, libfamille, idffamillepère)
PRODUIT(idffamille, idfproduit, libproduit, prix, caractéristiques, imagep)
CLIENT(idfclient, nom, adresse, email)
COMMANDE(idfcommande, datecommande, modepaiement, idfclient)
LIGNECOMMANDE(idfcommande, noligne, idffamille, idfproduit, qté)
La sémantique des diverses relations est la suivante :
CATALOGUE(nocatalogue, libcatalogue, entreprise, idffamille)
: un catalogue est identifié de manière unique par un numéro et est
décrit par son libellé, le nom de l ‘entreprise proposant le catalogue
en ligne et la famille de produits racine du catalogue (idffamille clé
étrangère sur FAMILLE),
FAMILLE(idffamille, libfamille, idffamillepère)
: une famille de produits est identifiée de manière unique par son
numéro d’immatriculation et est décrit par son libellé. Les familles de
produits sont hiérarchisées et idffamillepère représente l’identifiant
de la famille de produits de niveau supérieur dans la hiérarchie
(idffamillepère clé étrangère de la relation FAMILLE sur elle même).
Par convention, une famille de produits racine d’un catalogue a un
idffamillepère qui vaut 0,
PRODUIT(idffamille, idfproduit,
libproduit, prix, caractéristiques, imagep) : un produit identifié par
son identifiant appartient à une famille de produits (désignée par son
identifiant idffamille clé étrangère sur FAMILLE). Cette famille sera
appelée par la suite famille de rattachement du produit. Un produit est défini par son libellé, son prix, ses caractéristiques et une image le représentant,
CLIENT(idfclient, nom,
adresse, email) : un client est identifié de manière unique par son
identifiant et est caractérisé par son nom, son adresse et son mél,
COMMANDE(idfcommande, datecommande, modepaiement, idfclient)
: une commande est identifiée de manière unique par son identifiant et
est décrite par une date et un mode de paiement (carte bancaire, à la
livraison). Une commande est faite par un client (idfclient clé
étrangère sur CLIENT),
LIGNECOMMANDE(idfcommande, noligne, idffamille, idfproduit,
qté) : une ligne de commande est rattachée à une commande (idfcommande
clé étrangère sur COMMANDE) et est identifiée par son numéro. Une ligne
de commande porte sur un produit (idffamille et idfproduit clé
étrangère sur PRODUIT) commandé en une certaine quantité.
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 COMMANDE 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 les clients (idfclient,
nom) ayant commandé au moins un produit appartenant à la famille de
rattachement d’identifiant FA003.
(Q3.2) Donner les produits (idffamille, idfproduit, libproduit) commandés par le client d’identifiant CL010 avant le 20/06/2001.
(Q3.3) Donner les produits (idffamille et idfproduit) qui n’ont fait l’objet d’aucune commande.
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 la famille de rattachement (idffamille et libfamille) qui comprend le plus de produits.
(Q4.2) Donner les clients (idfclient
et nom) qui ont commandé au moins une fois tous les produits de la
famille de rattachement d’identifiant FA009.
(Q4.3) Donner la définition de la vue
COMMANDEGLOBALE(idffamille, idfproduit, idfclient, nom, qtétotale) qui
donne pour chaque produit (idffamille et idfproduit) et pour chaque
client (idfclient et nom) la quantité totale commandée.
Question 5
Soit le schéma relationnel M(C, O, L, F, P, N, A, D, Q) avec les dépendances fonctionnelles suivantes (il s’agit de la fermeture transitive) :
C -> N; C -> A; O, L -> F, P; O, L -> Q; O -> D;
O -> C ; O -> N ; O -> A
(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.
Question 6
(Q6.1)
Vous avez créé le schéma de base de données défini dans la question 1
sous Oracle en étant connecté comme utilisateur U1 mot de passe P1.
L’utilisateur U2 souhaite pouvoir interroger votre relation CLIENT.
Donner l’instruction SQL complète que vous devez exécuter pour lui
donner cette possibilité.
(Q6.2)
On suppose que l’on dispose d’une relation COMPTE(nocompte, solde) qui
donne pour chaque compte bancaire (identifié par son numéro) le solde
de ce compte. On suppose que cette relation contient les données
suivantes :
|
1 |
100 |
|
2 |
120 |
|
3 |
330 |
|
4 |
200 |
|
5 |
120 |
Soit T la transaction suivante :
UPDATE COMPTE SET SOLDE = SOLDE+50 WHERE NOCOMPTE>3 ;
ROLLBACK ;
Quelle est le nouveau contenu de la relation COMPTE après l’exécution de la transaction T ?
Posté le 4 septembre 2009