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 ?