doc
publicité
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 MSBM2003) 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 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, att1; 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 ALTER TABLE nom_table ADD CONSTRAINT pk_nom_table CHECK(att1 PRIMARY KEY > 0); 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 table_bidule(att_machin ), REFERENCES 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 : DESACTIVATION : ALTER TABLE nom_table ENABLE CONSTRAINT nom_contrainte; 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 ? MSBM 2002-2003 18. Quelles sont les noms de sociétés qui ont mis au point des molécules actives à l'origine de médicaments appartenant la famille des analgésiques ? 19. Quelles sont les molecules qui n'agissent que sur la grippe ? 20. Quelles sont les molécules qui agissent sur la grippe mais qui n'agissent pas sur l'hypertension artérielle ? 21. Quels sont les molécules actives qui agissent dans toutes les pathologies ?
