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
____________________________________________________
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é)
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.
CREATE TABLE COMMANDE (
Idfcommande integer PRIMARY KEY,
Datecommande date NOT NULL,
Modepaiement char(20) CONSTRAINT Cmode CHECK modepaiement
IN ('carte bancaire', 'à la livraison'),
Idfclient integer);
ALTER TABLE COMMANDE ADD CONSTRAINT REFCLIENT
FOREIGN KEY (idfclient) REFERENCES CLIENT(idfclient)
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.
SELECT F.idffamille, F.libfamille
FROM PRODUIT P, FAMILLE F
WHERE P.idffamille=F.idffamille
GROUP BY F.idffamille, F.libfamille
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM PRODUIT
GROUP BY idffamille)
Ou bien
SELECT F.idffamille, F.libfamille
FROM PRODUIT P, FAMILLE F
WHERE P.idffamille=F.idffamille
GROUP BY F.idffamille, F.libfamille
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PRODUIT
GROUP BY idffamille)
(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.
SELECT C.idfclient, C.nom
FROM CLIENT C, LIGNECOMMANDE L, COMMANDE CO
WHERE C.idfclient=CO.idfclient AND L.idffamille='FA009'
AND L.idfcommande=CO.idfcommande
GROUP BY C.idfclient, C.nom
HAVING COUNT(DISTINCT L.idfproduit) =
(SELECT COUNT(*) FROM PRODUIT WHERE idffamille='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.
CREATE VIEW COMMANDEGLOBALE(idffamille, idfproduit,
idfclient, nom, qtétotale) AS
SELECT L.idffamille, L.idfproduit, C.idfclient,
C.nom, SUM(L.qté)
FROM LIGNECOMMANDE L, CLIENT C, COMMANDE CO
WHERE C.idfclient=CO.idfclient AND L.idfcommande=CO.idfcommande
GROUP BY L.idffamille, L.idfproduit, C.idfclient,
C.nom
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; O, L ->
P; O, L -> Q; O
-> D;
O ->
C ; O -> N ; O
-> A
(Q5.1) Donner la(les) clé(s) de ce
schéma relationnel.
Une clé est un attribut (ou un ensemble d'attributs)
qui dérive tous les autres. O, L dérive tous les autres et
est minimal (O ne dérive pas tous les autres et L non plus). O,
L est donc une clé, c'est même la seule car O et L ne sont
jamais en partie droite des DF donc appartiennent forcément à
toutes les clés.
(Q5.2) Donner la forme normale de ce schéma
et s’il n’est pas en 3FN proposer une décomposition en 3FN.
Ce schéma est en 1ère forme
normale par définition du modèle relationnel. Pour calculer
les formes normales il faut segmenter les attributs en attributs clés
(qui appartiennent à au moins une clé) et les attributs non
clés (les autres). Les attributs clés sont O et L et les
attributs non clés C, F, P, N, A, D, Q .
Le schéma est en 2ème forme
normale si les DF de type clé vers attributs non clés sont
toutes élémentaires. La clé est O, L mais C par exemple
dépend également de O seulement, donc la DF O, L ->
C n'est pas élémentaire.
Ce schéma est donc en 1ère
forme normale.
Une décomposition possible de ce schéma
en 3ème forme normale est la suivante :
R1(C, N, A)
R2(O, C, D)
R3(O, L, F, P, Q)
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é.
Il s'agit d'un problème de confidentialité, puisque par
défaut une relation créée par un utilisateur n'est
pas accessible des autres utilisateurs. Il faut donc que U1 donne explicitement
le droit SELECT sur la relation CLIENT à l'utilisateur U2 :
GRANT SELECT ON CLIENT TO U2;
(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 ?
Une transaction est une séquence d'instructions
sur une base de données qui se termine soit en demandant sa validation
(COMMIT), soit en demandant son annulation (ROLLBACK). Ici elle se termine
par un ROLLBACK donc la base de données reste dans le même
état que celui avant l'exécution de cette transaction et
donc le contenu de la relation COMPTE est inchangé.
Posté le 4 septembre 2009