Juin 1999

Corrigé du

Contrôle des Connaissances en Bases de Données

1e Partie

Q1) Quels sont les inconvénients de schémas de BD non normalisés ?

redondances => anomalies lors des mises à jour

Q2) Quels sont les intérêts de stocker des procédures SQL à côté des données dans une BD relationnelle ?

stockage du plan d’exécution optimisé

codage et vérification des contraintes d’intégrité sur les données

extension du modèle relationnel vers le modèle objet

(procédures = méthodes, éventuellement avec limitation de droits)

Q3) Quels sont les services rendus par le sous-système transactionnel d’un SGBD ?

possibilité d’exécuter des transactions

(suites de requêtes, terminée par commit/rollback, atomique et consistante)

service de gestion de la concurrence

service de reprise sur défaillance (à chaud / à froid)

plus, si intégré, service de cache du disque

2e Partie

Q4) Quelles régions produisent plus de 12 crus différents de vin ?

Select REGION
From VINS
Group by REGION
Having count(distinct CRU) > 12 ;

Q5) Quels sont les vins (V#) qu’aucun buveur n’a jamais encore acheté ?

Select V# from VINS
Minus
Select V# from ACHATS ;

/* ou */

Select V# from VINS
where V# not in (select V# from ACHATS) ;

/* ou */

Select V# from VINS V
where V# not exists (select * from ACHATS A where V.V#= A.V#) ;

Q6) Quels buveurs (B#) n’ont acheté que des vins qu’ils aiment ?

Proj B# ( ACHATS) minus Proj B# (Proj B#, V# (ACHATS) minus GOUTS)

se traduit en SQL par :

Select B# from ACHATS minus Select B# from ACHATS A where not exists
(Select * from GOUTS G where A.B# = G.B# and A.V# = G.V#) ;

Q7) Quels buveurs (B#) ont acheté tous les vins qu’ils aiment ?

Proj B# ( ACHATS) minus Proj B# (GOUTS minus Proj B#, V# (ACHATS))

se traduit en SQL par :

Select B# from ACHATS minus Select B# from GOUTS G where not exists
(Select * from ACHATS A where A.B# = G.B# and A.V# = G.V#) ;

Q8) Ecrire en SQL la définition de la vue CONSOMMATIONS.

Create view CONSOMMATIONS as
Select B#, V#, DATE, QUANTITE from ACHATS A where exists
(select * from GOUTS G where G.B# = A.B# and G.V# = A.V#);

ou, plus simplement,

Create view CONSOMMATIONS as
Select A.B#, A.V#, DATE, QUANTITE
from ACHATS A, GOUTS G
where G.B# = A.B# and G.V# = A.V# ;

Q9) Pour chaque buveur donnez la quantité totale achetée de tous les vins qu’il aime.

Select B#, sum(QUANTITE) from CONSOMMATIONS group by B#;

Q9bis)

Select A.B#, sum(QUANTITE) from ACHATS A, GOUTS G
where G.B# = A.B# and G.V# = A.V#
group by A.B#;

3e Partie

Q10) Donner le schéma sous la forme d'une collection de relations normalisées dont on précisera le type (2NF, 3NF, BCNF, ou plus) et on soulignera les clés.

3FN ou BCNF : 

LABORATOIRE(NUMLAB, NOMLAB, ADRESSE, TEL)

MEDICAMENT(NOMMED, TABLEAU, TAUXSS, NUMLAB)

PRODUIT(NOMMED, FORME, PRIX)

COMPOSITION(NOMMED, DCI)

THERAPIE(DCI , ACTION)

et (facultatif) :

PRINCIPEACTIF(DCI)

Q11) Donnez aussi le schéma sous la forme Entité-Association (Entity-Relationship).

Q12) Liste des noms de médicament ayant une action anticoagulante.

Select NOMMED from COMPOSITION C, THERAPIE T
Where T.DCI = C.DCI and T.ACTION = ’anticoagulante’ ;

Q13) Liste des produits du laboratoire ‘EcoSanté’ vendus pour moins de 3 Euros.

Select NOMMED from LABORATOIRE L, MEDICAMENT M, PRODUIT P
Where L.NUMLAB = M.NUMLAB and M.NOMED = P. NOMMED
And L.NOMLAB = ’EcoSanté’ and P.PRIX < 3 ;