Controle de connaissance
Juin 1999

1^e Partie

Répondre, brièvement mais précisément, aux trois questions suivantes :

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

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

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

2^e Partie

On considère le schéma relationnel suivant – légèrement différent de celui du TP :

BUVEURS(B#, NOM, ADRESSE, TYPE), où la clé primaire est (B#)

VINS(V#, REGION, CRU, MILLESIME, DEGRE), où la clé primaire est (V#)

ACHATS(V#, B#, DATE, QUANTITE), où la clé primaire est (V#, B#, DATE)

GOUTS(B#, V#), où la clé primaire est (B#, V#)

Ecrire en SQL les requêtes suivantes :

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

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

Ecrire en SQL et en algèbre relationnelle (arbre d’exécution) les requêtes suivantes :

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

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

On veut créer une vue CONSOMMATIONS de schéma (B#, V#, DATE, QUANTITE) pour les seuls achats conformes aux goûts des buveurs qui les ont effectués.

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

Exprimez en SQL, en utilisant cette vue, la requête suivante :

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

Q9bis) traduisez cette expression en expression sur les tables elles-mêmes (par substitution de la définition de la vue au nom de la vue)

3^e Partie

On désire constituer le schéma d'une base de données relationnelles pharmaceutiques. On dispose des informations suivantes : 

NUMLAB : numéro de laboratoire fabricant

NOMLAB : nom de laboratoire

ADRESSE : adresse de laboratoire

TEL : téléphone d'un laboratoire

NOMMED : nom d'un médicament

DCI : principe actif (Dénomination Commune Internationale)

ACTION : action thérapeutique

TABLEAU : catégorie de liberté de vente d'un médicament (libre, sur ordonnance,...)

TAUXSS : taux de remboursement Sécurité Sociale pour un médicament

FORME : présentation d'un médicament (comprimé, suppositoire etc..)

PRIX : prix d'un médicament

 

On précise que :

- Un laboratoire est identifié par son numéro, un médicament est identifié par son nom.

- Un médicament est produit par un seul laboratoire.

- La Sécurité Sociale définit pour chaque médicament une catégorie de vente – c’est son inscription au " tableau " - et un taux de remboursement

- Un même médicament peut avoir plusieurs présentations vendues éventuellement chacune à un prix différent.

- Un principe actif est une caractérisation chimique. Un médicament utilise un ou plusieurs principes actifs. Un principe actif est utilisé par un ou plusieurs médicaments.

- Une thérapie peut être vue comme l'association d'un ou plusieurs principes actifs et de une ou plusieurs actions.(par exemple le principe actif "acide nicotinique" a une action antalgique et une action anti-inflammatoire).

 

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.

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

(On utilisera au choix la méthode bottom-up – des dépendances fonctionnelles au schéma relationnel puis au schéma ER, ou la méthode top-down – d’un schéma ER, à sa traduction relationnelle puis à la vérification des dépendances fonctionnelles)

Exprimez, à titre de vérification de votre bonne conception, en SQL, les deux requêtes suivantes :

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

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

DUREE : 90 mn (= un tour de terre en satellite à basse altitude !)

TOUS DOCUMENTS AUTORISES

 

BON COURAGE !