doc

MSBM 2002-2003
TD 5 : Conception d'une base de données de A à Z
Ce TD a pour objectif de vous faire concevoir une base de données de A à Z selon des spécifications données.
Pour cela, vous aurez à :
 modéliser l'organisation selon les spécifications imposées
 convertir le schéma entité-association en schéma relationnel
 rédiger le script de création du schéma de la base de données
 écrire le script d'insertion des tuples
 modifier le schéma en ajoutant des contraintes
 interroger la base de données en traduisant les requêtes dans le langage demandé
Le langage SQL (Structured Query Langage) peut être décomposé en 3 parties :
 le langage de définition des données (LDD) qui permet de définir le schéma de la base de données,
c'est-à-dire que ce langage regroupe les instructions de création des relations, vues et contraintes
d'intégrité,
 le langage de manipulation des données (LMD) qui permet d'interroger les données
 le langage de contrôle des données (LCD) qui permet de gérer les accès aux données c'est-à-dire les
droits des utilisateurs de la base de données
Rq : Vous trouverez sur la page http://www-lsr.imag.fr/Les.Personnes/Herve.Martin/HTML/SQL.htm une
présentation concise et bien faite des trois parties du langage SQL.
Vous trouverez sur ma page personnelle (http://www.lirmm.fr/~berasalu puis enseignements puis MSBM-
2003) des liens vers d'autres cours sur les bases de données ainsi qu'un petit aide-mémoire sur le langage SQL.
1 SPECIFICATIONS
Le but est de décrire de manière très simplifiée le mode de fonctionnement d'un ensemble de sociétés
pharmaceutiques.
" Une société pharmaceutique a des employés. Certains de ces employés sont des
attachés de recherche clinique (ARC) qui ont pour mission de tester les médicaments,
développés par la société qui les emploie et agissant sur des pathologies, avant leur
mise sur le marché.
Une société est identifiée par son code siret, a un nom, un siège social (lieu) et un
PDG.
Un employé est identifié, au sein de son entreprise, par un matricule, porte un nom, a
une date de naissance, a été embauché a une certaine date, perçoit un salaire et est
dirigé par un supérieur .
Un ARC travaille sur des médicaments appartenant à un certain secteur médical.
Un médicament est identifié par un numéro au sein de la société qui le développe,
porte un nom, a un type de présentation et contient un principe actif.
Un principe actif est une molécule identifiée par son nom, a une formule.
Une pathologie est identifiée par un libellé et possède une description.
Un principe actif agit potentiellement sur plusieurs pathologies. "

MSBM 2002-2003
2 MODELE ENTITE-ASSOCIATION
Construisez le modèle entité-association de cette organisation grâce aux spécifications indiquées ci-dessus.
Pour cela, déterminez :
 les types d'entités nécessaires
 les types d'association
 les cardinalités caractérisant les types d'association
3 MODELE RELATIONNEL
Convertissez le modèle entité-association précédent en modèle relationnel.
o Que deviennent les types d'entité ?
o Que deviennent les types d'association ?
o Indiquer les types des attributs impliqués dans les relations que vous avez créées.
o Indiquer les dépendances fonctionnelles ainsi que les contraintes d'intégrité référentielles ?
Dans le langage de définition des données standard, on crée une table grâce aux instructions suivantes :
CREATE TABLE nom_table ( att1 type_att1, att2 type_att2, ..... , atti type_atti, ..... , attn val_attn);
Les principaux types de données qui sont disponibles dans les bases de données standard sont :
NUMBER[(longueur,[précision])
Ce type de données permet de stocker des données numériques à la fois entières et réelles dont la valeur
est comprise entre 10^-130 et 10^125/ avec une précision de 38 chiffres.
longueur
précise le nombre maximum de chiffres significatifs stockés (par défaut 38),
précision
donne le nombre maximum de chiffres après la virgule (par défaut 38), sa valeur peut être comprise
entre -84 et 127. Une valeur négative signifie que le nombre est arrondi à gauche de la virgule.
CHAR(longueur)
Ce type de données permet de stocker des chaînes de caractères de longueur fixe. Longueur doit être
inférieur à 255, sa valeur par défaut est 1.
VARCHAR(longueur)
Ce type de données permet de stocker des chaînes de caractères de longueur variable. Longueur doit
être inférieur à 2000, il n'y a pas de valeur par défaut.
DATE Ce type de données permet de stocker des données constituées d'une date et d'une heure.
RAW(longueur)
Ce type de données permet de stocker des caractères non imprimables.
LONG Ce type de données permet des stocker des chaînes de caractères de longueur variable et inférieure à
2^31 -1. Les colonnes de ce type sont soumises à certaines restrictions ;
 une table ne peut pas contenir plus d'une colonne de ce type ;
 les colonnes de ce type ne peuvent pas apparaître dans des contraintes d'intégrité ;
 les colonnes de ce type ne peuvent pas être indexées ;
 les colonnes de ce type ne peuvent pas apparaître dans des clauses : WHERE, GROUP BY, ORDER BY
ou CONNECT BY ainsi que dans un DISTINCT.

MSBM 2002-2003
Pour créer une contrainte d'intégrité, il existe plusieurs syntaxes possibles :
1. Soit vous indiquez la contrainte quand vous définissez l'attribut de la relation
2. Soit vous indiquez la contrainte après la définition des attributs de la relation
3. Soit vous modifier le schéma de la relation
Comme nous l'avons vu, il existe différentes contraintes d'intégrité :
a. Dépendances fonctionnelles (en particulier, clé primaire)
b. Contraintes de domaines
c. Contraintes d'intégrité référentielles (clés référentielles)
Syntaxe :
a. création d'une contrainte primaire
1.
CREATE TABLE nom_table
( att1 type_att1 PRIMARY KEY,
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn);
2.
CREATE TABLE nom_table
( att1 type_att1,
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn
CONSTRAINT pk_nom_table PRIMARY KEY);
3.
ALTER TABLE nom_table ADD CONSTRAINT pk_nom_table PRIMARY KEY att1;
b. création d'une contrainte de domaine
1.
CREATE TABLE nom_table
( att1 type_att1 CHECK(att1 > 0),
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn);
2.
CREATE TABLE nom_table
( att1 type_att1,
att2 type_att2,

MSBM 2002-2003
..... ,
atti type_atti,
..... ,
attn val_attn
CONSTRAINT chk_nom_table_att1 CHECK(att1 > 0));
3.
ALTER TABLE nom_table ADD CONSTRAINT chk_nom_table_att1 CHECK(att1 > 0);
ALTER TABLE nom_table ADD CONSTRAINT pk_nom_table PRIMARY KEY att1;
c. création d'une contrainte d'intégrité référentielle
1.
CREATE TABLE nom_table
( att1 type_att1 REFERENCES table_bidule(att_machin ),
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn);
2.
CREATE TABLE nom_table
( att1 type_att1,
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn
CONSTRAINT fk_nom_table_att1 REFERENCES
table_bidule(att_machin ),
3.
ALTER TABLE nom_table ADD CONSTRAINT fk_nom_table_att1 REFERENCES
table_bidule(att_machin );
4 MISE A JOUR DU SCHEMA RELATIONNEL
Nous allons modifier le schéma relationnel établi en ajoutant des contraintes à ce schéma. Pour chaque
contrainte que vous devez ajouter indiquer à quel type de contrainte elle correspond.
 Les salaires des employés sont supérieur ou égal au SMIC (on admet que tout employé travaille à temps
complet et donc touche au moins le SMIC).
 Les secteurs auxquels appartiennent les ARCs sont pris parmi l'ensemble suivant : cancérologie,
rhumatologie, cardiologie, dermatologie.
 Un employé doit avoir plus de 18 ans et moins de 65 ans.
Dans le langage de définition des données, l'ajout de contraintes se fait d'après la syntaxe que nous avons vu
dans la partie 3. Servez-vous de l'instruction qui permet d'altérer une relation.

MSBM 2002-2003
Comme nous l'avons sous ACCESS, le langage de définition des données permet d'activer ou de désactiver une
contrainte. Ceci se fait de la façon suivante :
ACTIVATION : ALTER TABLE nom_table ENABLE CONSTRAINT nom_contrainte;
DESACTIVATION : ALTER TABLE nom_table DISABLE CONSTRAINT nom_contrainte;
5 MISE A JOUR DES DONNEES
Maintenant que nous avons créé le schéma de la base, il faut y insérer des données, les mettre à jour et,
pourquoi pas en supprimer. Pour cela, vous allez créer un script fait d'une suite de d'instructions du type suivant
:
 instruction d'insertion
INSERT INTO nom_table VALUES (val_att1, val_att2, ......, val_atti, ......., val_attn);
 instruction de modification
UPDATE nom_table SET atti = new_val WHERE attj = une_valeur;
 instruction de suppression
DELETE FROM nom_table WHERE attj = une_valeur;
Pour vous inspirer, vous trouverez à l'adresse
http://www.lirmm.fr/~berasalu/enseignements/MSBM/TP/TP3_ACCESS/
des exemples de tuples à insérer.
Quelle instruction (SQL) feriez-vous pour augmenter le salaire de tous les employés agés de plus de 50 ans de
10% ?
Quelle instruction (SQL) feriez-vous pour supprimer les sociétés dont le siège social se trouvent à Paris ? Quel
sera le résultat de l'exécution de cette instruction ?
6 INTERROGATION DE LA BASE
Nous avons déjà vu ensemble des exemples de requêtes SQL dans les TD 3 et 4.
Nous allons maintenant interroger la base de données construite. Les requêtes suivantes exprimées en français
sont à traduire en algèbre relationnelle (lorsque c'est possible) puis en SQL.
1. Donner les noms et matricules des employés de la société de code Siret 5.
2. Donner les noms et matricules des employés de la société Bayer Pharma.
3. Donner les noms et matricules des employés des sociétés Bayer Pharma, Pierre Fabre, Biogalénique et
Sanofi Synthelabo.
4. Lister les ARCs (noms et matricules) spécialisés en cardiologie.
5. Lister les ARCs (noms et matricules) qui ont été embauchés entre 1990 et 1992.
6. Donner la liste des pathologies triée par ordre alphabétique.
7. Donner les sociétés dont le siège social se trouve à Paris.
8. Donner le nombre de médicaments décrits dans la base de données.
9. Donner pour chaque société le nombre de molécules actives mises au point.
10. Donner les sociétés qui ont mis au point au moins deux molécules actives.
11. Quelle est la société qui a le plus de molécules actives à son actif ?
12. Quelles sont les molécules actives qui ont un rôle thérapeutique dans l'hypertension artérielle ?
13. Combien de molécules actives ont un rôle dans l'hypertension artérielle?
14. Quels sont les médicaments qui sont impliqués dans le traitement de l'hypertension artérielle ?
15. Quelles sont les molécules qui ont un rôle thérapeutique dans au moins deux pathologies ?
16. Quelles sont les molécules qui agissent à la fois sur la grippe et sur le syndrome de Kawasaki ?
17. Quelles sont les pathologies qui peuvent être soulagées par plusieurs molécules actives ?