Controle by cuiliqing

VIEWS: 15 PAGES: 13

									Université Paris 5 – Mathématiques Informatique – BDAV 2008           Page 1 of 13


TD n° 1 – SQL*Plus 1/3
Université René Descartes – Paris 5,
UFR de mathématiques Informatique
Master 1 BDAV Année 2008 – Durée 1H30.
Particularité : Travail en Groupe.



TD n° 1 – SQL*Plus / Modelisation / Vues DBA
O. Hirsch, olivier.hirsch@oracle.com 26/04/2008

Aide-mémoire SQL*Plus
Connexion Nom utilisateur : MGUx (x € {1,2,3,4,5})
Mot de passe : MGUx (x € {1,2,3,4,5})

Description des champs d’une table DESC[RIBE] Nom_Table
Liste des tables créées SELECT * FROM TAB;
Paramètres dans les requêtes SQL & (ex. INSERT INTO Nom_Table
VALUES(&param_nb, ‘&param_ch’);)
Ré-exécuter la commande précédente /
Édition d’un fichier de commandes EDIT Nom_Fichier (ajout automatique de
l’extension .sql)
Changer l’éditeur par défaut Menu Éditer/Éditeur/Définir éditeur
Commentaires -- Commentaire
Exécution d’un fichier de commandes START Nom_Fichier[.sql] ou
@Nom_Fichier[.sql]
Sortie de SQL*Plus EXIT, QUIT ou menu Fichier/Quitter
Rediriger la sortie vers un fichier : spool nom_de_fichier
Fermer la sortie de fichier : spool off.




    Les réponses sont à mettre dans un fichier spool nommé
                           MGUx.lst
     Chaque fichier MGUx comprendra la liste des etudiants
                    appartenant a ce groupe.
       et à envoyer à mon adresse email avant 11 H29…
                        Bon Courage !!
          Université Paris 5 – Mathématiques Informatique – BDAV 2008         Page 2 of 13



          Modèle de données et implantation dans la base Oracle

          Voici une représentation du modèle EPID utilise pour un projet
          informatique d’etude epidemiologique.


              ZONE

              #ID_ZONE                        PAYS
              *SUPERFICIE
              *POPULATION                                                   CONTINENT
                                              #ID_PAYS
              *TYPE_CLIMAT                    *SYTEME POLITIQUE
                                                                            #ID_CONT
                                              *LANGUE PRINCIPALE
                                                                            *NOM CONT
                                              *INDIC_ECO
                                              *INDIC_SANITAIRE              DESCR CONT




                     APP_ZONE
                     # ID_ZONE
                     #ID_PERSON
                     MOTIF
 STATUT              DEBUT
 #ID_STATUT          FIN
 LIBELLE
 DESCR
 DATE                                                   PERSONNE

                                                        #ID_PERSONNE
                         OBJET_ETUDE                    *AGE
                         #ID_PERSON                     *SEXE
                         #NOM_EPID                      O RISQUE_SAN_MAJ
                         ID_STATUT                      O CATSP
                         DATE_DEBUT                     O RACE
                         DATE_FIN                       O ESPECE
                                                        O ID_SYMPTOME




                           EPIDEMIE                                        APP_POP
                                                      SYMPTOME             #ID_POP
                           #INOM_EPID                                      #ID_PERSONNE
                           *DEBUT                     #ID_SYMTOME          DEBUT
                           O FIN                      DESC                 FIN
                           *ID_SYMPTOME               LIBELLE
                           O Niveau Gravité
                           ID_ZONE



MALADIE                    MALADIE_EPID                 TYPE               POPULATION
#ID_MALADIE                #ID_EPID                     # ID_TYPE          #ID_POP
NOM                        #ID_MALADIE                  *LIB               *LIBELLE
DESCR                      DATE_ORIG                    *DESCR             *DESCR
Université Paris 5 – Mathématiques Informatique – BDAV 2008      Page 3 of 13




       Modelisation de données (5points)
   1. Quel peut bien être le sujet de ce projet informatique.

   Le sujet de ce projet informatique concerne une etude statistique
   sur les populations atteintes d’une maladié referenceé comme
   faisant partie des epidemies recensées.
   L’etude vise a correler l’appartenance d’une population donnée a
   une zone geographique. Les sujets de l’étude sont anonymes. Ils
   peuvent etre etudies dans le cadre de recherche sur plusieurs
   maladies.

   2. A quelle classe de modèle le modele présenté ci dessus
      appartient il ? Commentez.

   Ce modele appartient a la classe des modeles Logiques de données.
   On y trouve des relations relies entre elles par des liens de cles
   etrangeres. Cependant certaines cles etrangeres semblent
   manquer. Il s’agit peut etre d’un oubli. Ce ne peux être un modeme
   physique car les types de donnees et leur tailles ne sont pas
   indiqués ni un modele conceptuel car les associations ne sont pas
   representées.

   3. Dans le mode le modele relationnel EPID identifiez les
      types d'Associations, les types d'entites.

     Pour obtenir les entites et les associations du modele EPID il faut
faire un reverse engeenering du modele relationel. Certains outils le
permettent. Le principe appliqué pour retrouve les entites se base sur
le presence des cles etrangeres. Lorsqu’une cle etrangere est portee
dans la relation alors il y a une association de cardianlite 1 :n entre les
table, si 2 ou plus cles etrangeres sont portees dans une relation alors
on a une association n-aire avec une cardianlite n :n generalement.


    Dans le modele EPID, les entites sont :
      Continent,
      Pays,
      Zone,
      Personne,
      Statut,
      population,
Université Paris 5 – Mathématiques Informatique – BDAV 2008       Page 4 of 13


      type population,
      symptome,
      Epidemie,
      maladie.
    Dans le modele EPID, les associations sont :
      Continent Compose de
      Pays compose de
      Personne appartient Zone
      Personne Objet d’etude
      Personne appartient Population
      Maladie concerne Epidemie
      Population appartient type population
      Symptome observe a personne
      Symptome representatif d’epidemie

Note : Dans le model relationnel EPID il semble manqué a la relation
ZONE la clé etrangere sur PAYS.



   4. Quelles sont les tables qui ne seront vraisemblablement
      pas ou peu mises a jour.

       Les table pas ou peu mises a jour seront les tables de type
       catalogue. Ici les tables qui serontportees par les relations
       CONTINENT, PAYS, ZONE, STATUT, TYPE, POPULATION,
       SYMPTOME, MALADIE, EPIDEMIE, MALADIE_EPID. Ces tables
       etant peu mises a jour pourront être administees de maniere
       differentes et demanderont moins de surveillance.

   5. comment seront implementes les identifiants des
      relations dans Oracle pour le modele EPID principalement.

       En règle generale il existe 3 facons d’implémenter des
       identifiants.Soit on utilise des identifiants semantiques tels que
       le nom d’une personne ou d’un pays, ou encore le numero de
       securite sociale, soit on utilise des identifiants informatiques qui
       seront des numéros générés de maniere automatique mais sans
       aucun sens semantique par exemple « XD4R-Z23Q », ou encore
       des numeros unique généré a partir des objets sequence dans
       Oracle. .

       Dans le cas du modele EPID le plus vraisemblable sera d’utiliser
       des identifiants en séquence, sauf pour les associations n-aire
Université Paris 5 – Mathématiques Informatique – BDAV 2008     Page 5 of 13


       qui porteront la concatenation des identifiant de séquences des
       relations qu’ils relient entre elles. Par exemple app_zone aura
       pour identifiant (id_zone,id_personne). En effet le modele EPID
       mentionne de manière explicite que ID_ZONE sera l’identifiant
       avec le symbole # ce qui ne laisse pas d’autre choix que
       l’identifiant informatique ou la sequence.


   6. Quelle sont les tables qui feront l'objet d'une attention
      particuliere quant a leur dimensionement

       Les tables devant faire l’objet d’une attention particuliere quant
       a leur dimensionement seront les autres tables que celles
       mentionees à la reponse 4 a savoir OBJET_ETU, PERSONNE,
       APP_ZONE, APP_POP. En effet ces tables seront volumineuses.
       Car ce sont les associations hors catalogues pour la plupart
       d’entre elles exception faite de la table personne. La table
       Personne sera la plus dimensionante de toute mais pas la plus
       grosse. En effet chaque occurrence de la table personne aura
       potentiellement une incidence surla table OVBJET_ETU,
       APP_ZONE et APP_POP. La table OBJET_ETU devrait être la plus
       grosse et faire l’objet d’une attention toute particuliere en
       production.




Mise à jour de la base de données (7
points)
   1. tapez desc APP_ZONE. a qui appartient cette table ?

       DESC est l’ordre SQL*PLUS qui permet d’obtenir une description
       d’un objet. Ici APP_ZONE etantune table on obtiendra la
       description des colonnes et des types de donnée aisin que la
       taille de celles ci pour la table APP_ZONE. Il s’agit donc d’une
       vue directe sur le modele physique implementé pour la relation
       APP_ZONE du modele EPID. Pour savoir qui est le propriétaire
       de cette table il faut faire SELECT OWNER FROM ALL_TABLES
       WHERE TABLE_NAME = ‘APP_ZONE’ ; dans SQL*PLUS.
Université Paris 5 – Mathématiques Informatique – BDAV 2008        Page 6 of 13


       La requete renvoie alors OMS. C’est donc que la Table
       APP_ZONE appartient au schéma OMS de la base de données
       ORCL.


   2. Combien de ligne y a t il dans APP_ZONE ?

       Select count (*) from APP_ZONE revoie le nombre de ligne.



   3. créez une table APP_ZONE vide sans la colonne date_fin
      dans votre schema oracle. de la meme maniere cree une
      table Zone dans votre schema.

       Create table MGUX.APP_ZONE as select
       id_zone,id_person,motif,debut from APP_ZONE where rownum <
       1;


       Create table MGUX.ZONE as select * from ZONE where rownum
       <1;

   4. creez une trentaine de ligne dans la table ZONE.

   Pour ce faire il faut une trentaine d’ordre insert. Afin de s’assurer
   que l’on insert dans son schema on doit prefixer le nom de la tabel
   du nom du schema dans le quel on travaille. Car sinon le syninyme
   actuellement en place va aller créer les lignes dans la table
   OMS.ZONE. On sait qu(il y a un synonyme en place a cause dela
   question 1.

       Insert   into   MGUX.ZONE      (1, 10000, 100, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (2, 1000, 200, ‘ARIDE’ ) ;
       Insert   into   MGUX.ZONE      (3, 50, 300, ‘TEMPERE’ ) ;
       Insert   into   MGUX.ZONE      (4, 5000, 100, ‘CONTINENTAL’ ) ;
       Insert   into   MGUX.ZONE      (5, 500, 200, ‘TROPICAL’ ) ;
       Insert   into   MGUX.ZONE      (6, 7000, 300, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (7, 567, 2000, ‘ARIDE’ ) ;
       Insert   into   MGUX.ZONE      (8, 5000, 20, ‘CONTINENTAL’ ) ;
       Insert   into   MGUX.ZONE      (9, 5000, 200, ‘TROPICAL’ ) ;
       Insert   into   MGUX.ZONE      (10, 5000, 700, ‘TEMPERE’) ;
       Insert   into   MGUX.ZONE      (11, 5000, 100, ‘CONTINENTAL’) ;
       Insert   into   MGUX.ZONE      (12, 5000, 200, ‘ARIDE’) ;
Université Paris 5 – Mathématiques Informatique – BDAV 2008          Page 7 of 13


       Insert   into   MGUX.ZONE      (13,   5000, 800, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (14,   5000, 30, ‘CONTINENTAL’) ;
       Insert   into   MGUX.ZONE      (15,   5000, 2200, ‘TEMPERE’) ;
       Insert   into   MGUX.ZONE      (16,   5000, 2500, ‘TROPICAL’) ;
       Insert   into   MGUX.ZONE      (17,   5000, 1200, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (18,   5000, 200, ‘TROPICAL’) ;
       Insert   into   MGUX.ZONE      (19,   5000, 500, ‘ARIDE’) ;
       Insert   into   MGUX.ZONE      (20,   5000, 600, ‘TROPICAL’) ;
       Insert   into   MGUX.ZONE      (21,   5200, 2700,‘CONTINENTAL’) ;
       Insert   into   MGUX.ZONE      (22,   5400, 4500, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (23,   450, 1300,‘CONTINENTAL’) ;
       Insert   into   MGUX.ZONE      (24,   500, 10, ‘ARIDE’) ;
       Insert   into   MGUX.ZONE      (25,   15000, 900, ‘TEMPERE’) ;
       Insert   into   MGUX.ZONE      (26,   6000, 600, ‘TEMPERE’) ;
       Insert   into   MGUX.ZONE      (27,   7000, 450, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (28,   800, 980, ‘TROPICAL’) ;
       Insert   into   MGUX.ZONE      (29,   90000, 110, ‘HUMIDE’ ) ;
       Insert   into   MGUX.ZONE      (30,   300, 650, ‘CONTINENTAL’) ;


   5. creez egalement une table personne dans votre schema.

    Pour créer une table personne dans notre schema nous devons
   faire dans SQL*Plus :

   CREATE table MGUX.PERSONNE as select * from PERSONNE where
   rownum < 1;


   6. recuperer 10000 lignes de la table personne et creez les
      dans votre schema.

   Par contre la table personne etant deja volumineuse il faut s’assurer
   qu’il y a sufisamment de place pour pouvoir inserer les 10000 lignes
   par la commande :

   Insert into MGUX.PERSONNE SELECT * FROM PERSONNE WHERE
   ROWNUM < 10000;

   7. modifier la table personne de sorte que
      risque_sanitaire_majeur soit de type caractere.

           a. On recree une table personne2 vide.
Université Paris 5 – Mathématiques Informatique – BDAV 2008        Page 8 of 13


           CREATE TABLE MGUX.PERSONNE2 as select * from
           MGUX.PERSONNE where rownum <1 ;

           b. On modifie la table personne2 avec alter table

           ALTER TABLE MGUX.PERSONNE2 MODIFY
           (RISQUE_SANITAIRE_MAJEUR CHAR(2)) ;

           c. On insere dans personne2 a partir de personne en
              beneficiant de la conversion implicite entre l’entier et le
              caractere.
            INSERT INTO MGUX.PERSONNE2 SELECT * FROM
           MGUX.PERSONNE ;

           d. On supprime ma table personne

           DROP TABLE MGUX.PERSONNE ;

           e. On renomme personne2 en personne

          RENAME MGUX.PERSONNE2 TO MGUX.PERSONNE ;

   8. Vous aller maintenant faire appartenir les 10000
      personnes a des zones de maniere aleatoire en nourissant
      la table app_zone.

       Il faut realiser un programme PL/SQL pour faire ce chargement.
       Le programme utilisera une fonction aleatoire RAND. Pour
       chaque occurrence d’identifiant de la table personne on va
       associer un identifiant aleatoire de la table ZONE. Voici sa
       structure :

       CREATE PROCEDURE CHARGE_APP_ZONE AS
       DECLARE
        CURSOR CUR_PERS IS SELECT ID_PERS FROM
       MGUX.PERSONNE;
       Nombre_total_zone integer :=30;
       MY_ID_Zone integer :=0;
       V_person integer :=0;
       BEGIN

       OPEN cur_pers;
       LOOP
         FETCH cur_pers INTO v_person
Université Paris 5 – Mathématiques Informatique – BDAV 2008   Page 9 of 13


           EXIT WHEN cur_pers%NOTFOUND;
          MY_ID_ZONE=CEIL(RAND(1)*30);
         INSERT INTO MGUX.APP_ZONE VALUES ( my_id_zone,
          v_person, ‘AUCUN’, SYSDATE , NULL) ;
        END LOOP;
       close cur_pers;
       END;



   9. vous aller creer une table etudiant (nom , prenom, note )
      dans votre shema.

       CREATE TABLE MGUX.ETUDIANT (NOM VARCHAR (50) , PRENOM
       Varchar (50), NOTE number ) ;

   10.     pour chaque etudiant de votre groupe vous inserer
     une ligne.

       INSERT INTO MGUX.ETUDIANT VALUES ( ‘NOM1’ , ‘PRENOM1’ ,
       ‘0’) ;


   11.    Pouvez vous faire en sorte que cette table ne puisse
   pas contenir plus de 10 lignes. Si oui comment ?

   A l’aide d’un trigger sur insertion
       sur insertion dans table etudiant
       Debut
         vérifier le nombre de ligne de la table etudiant.
         si nombre_ligne > 10 alors
                                 Rollback ;
                                 Retourner une erreur avec un message ;
         sinon
                                 Ne rien faire.
         Fin si
       fin
Université Paris 5 – Mathématiques Informatique – BDAV 2008   Page 10 of 13




Interrogation de la base de données (3 points)
   1. Dans le modèle Epidémie A partir du schema OMS creer
      une table RHO contenant la liste des personnes de type
      RH O ayant eu une épidémie de statut " inconnu " en
      janvier 2007 sur le continent Européen.


       On n’a que les identifiants des personnes dans le modele EPID
       donc on prendra la liste des identifiants ; cependant comme une
       meme personne peut avoir plusieurs epidemies de statu inconu
       on fera une restriction avec la clause DISTINCT. La requete
       devra comprendre en tout 9 jointures et trois projections. On
       prendra soin de prefixer tous les noms des tables par OMS.

       Create table MGUX as select distinct id_person FROM
       OMS.PERSONNE A,
       OMS.APP_ZONE B,
       OMS.ZONE C,
       PAYS D,
       OMS.CONTINENT E,
       OMS.APP_POP F,
       OMS.POP G,
       OMS.TYPE H,
       OMS.OBJET_ETU I,
       OMS.STATUT K
       WHERE
       A.ID_PERSON=B.ID_PERSON AND
       B.ID_ZONE=C.ID_ZONE AND
       C.ID_PAYS=D.ID_PAYS AND
       D.ID_CONT=E.ID_CONT AND
       NOM_CONT=’Européen’ AND
       I.ID_PERSON=A.ID_PERSON AND
       I.ID_STATUT=K.ID_STATUT AND
       K.LIBELLE=’inconnu’ AND
       A.ID_PERSON=F.ID_PERSON AND
       F.ID_POP=G.ID_POP AND
       H.ID_TYPE=G.ID_TYPE AND
Université Paris 5 – Mathématiques Informatique – BDAV 2008     Page 11 of 13


       H.LIB=’RHO’ ;


   2. Combien de lignes retourne elle ?

       Elle ne retourne aucune ligne.

   3. Pourquoi ?

       Parc ce que la table APP_ZONE et la table ZONE sont vides. Si on
       utilisait le travail effectue dans la partie precedente pour remplir
       ces deux tables du schema OMS a partir de celles crees dans
       notre schema MGUX, alors la requete ramenerait des lignes.




Administration Oracle (5 points)
   1. retrouvez en vous servant du dictionaire de donnees
      Oracle le script de la contrainte d'integrite referentielle
      entre Personne et APP_POP.

       Il existe bien une contrainte d’ingrité referentielle entre la la
       table Personne et la table APP_POP. En effet Id_person dans la
       table app_pop doit exister dans la table table personne. Pour
       retrouver dansle dicrionnaire de donees cetteinformation il faut :

           a. chercher dans quelle vue chercher :
                 i. Select view_namle from user_views where
                     view_name like ‘%CON%’ ;
           b. Chercher les tables qui sont referencees dans la vue des
              contraintes :
                 i. Select table_name, constraint_type from
                     user_constraints where table_name like
                     ‘%APP%Z%’;
           c. Verifier les colonnes faisant partie de la contrainte
                 i. Select * from user_cons_columns where table_name
                     =’APP_ZONE’ and constraint_type =’R’ ;
Université Paris 5 – Mathématiques Informatique – BDAV 2008       Page 12 of 13


   2. Donnez le droit aux autres groupe de lire et d'inserer
      dans la table cree en 3 [RHO].

       GRANT SELECT, INSERT ON MGUX.RHO TO PUBLIC ;

       Ici X doit etre different de notre schema car on veut inserer dans
       un schema different du notre. La valuer importe peu puisqu’il n’y
       a pas de contrainte sur cette table et que par ailleurs elle est
       vide. Il faut cependant que ce soit un nombre.

       INSERT INTO MGUX.RHO VALUES (123456) ;


   3. Validez votre insertion

       COMMIT ; Sans quoi les autres ne voient pas l’information.

   4. verifier que vous parvenez a lire la ligne ainsi que d'autre
      groupe.

       SELECT * FROM MGUX.APP_ZONE; Si on ne parvient pas a la
       lire et a lire celle que les atres aurait fait dans notre schema
       c’est que le commit n’a pas ete fait.

   5. supprimez maintenant le droit d'acces a votre table RHO

       REVOKE ALL ON RHO FROM PUBLIC ;

   6. donnez la taille en MB de la table PERSONNE du modele
      EPID.

       Avec la requete suivante, on recupere le nombre de blocks
       Oracle alloué a la table et on le multiplie par la taille par defaut
       d’un bloc Oracle (8K) en suite on divise 2 fois par 1024 pour
       obtenir un resultat en Mega Octets.

       SELECT BLOCKS*8192/1024/1024 || ‘MB’ FROM ALL_TABLES
       WHERE TABLE_NAME = ‘PERSONNE’ AND OWNER=’OMS’

   7. Comment interroger la base pour savoir si elle est en
      mode archive log ?

       Le mode Archive log est un mode dans lequel les journaux sont
       archives ce qui permet une restauration jusqu’au moment de la
Université Paris 5 – Mathématiques Informatique – BDAV 2008     Page 13 of 13


       pane sans perte de données. Pour identifierl’etat de
       fonctionement de l’instance on interoge une vue dynamique V$ :

       SELECT LOG_MODE FROM V$DATABASE ;

   8. Comment interroger la base pour connaitre nom
      d’instance de l’instance ?

       Il peut être utile de savoir sur quelle instance on est connecte.
       Pour ce faire on utilise une autre vue dynalmique v$ :

       SELECT INSTANCE_NAME FROM V$INSTANCE;


   9. Combien de process la base est elle en train de servir ?

       On devra cette fois ci interroger la vue dynamique V$PROCESS
       ou a la rigueur V$Session.

       Select count (*) from v$session;
       Select count (*) from v$process;

       Dans Unix on peut egalement utiliser la commande suivante:

       Ps –ef | grep ORCL | wc –l

								
To top