Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle pour les EM 00/01
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.
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) =
(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
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é.