polycopié sur l'utilisation de postgresql avec un exemple de base

Download Polycopié sur l'utilisation de PostgreSQL avec un exemple de base

Post on 05-Jan-2017

221 views

Category:

Documents

3 download

Embed Size (px)

TRANSCRIPT

  • 1

    Licence de MIDO - 3me anne

    Spcialits Informatique et Mathmatiques Appliques

    AANNNNEEEE 22001144 // 22001155

    Dsignation de lenseignement : Bases de donnes

    Nom du document : TP SQL sous PostgreSQL

    Rdacteur : Maude Manouvrier

    La reproduction de ce document par tout moyen que ce soit est interdite conformment

    aux articles L111-1 et L122-4 du code de la proprit intellectuelle

  • TP PostgreSQL

    Universit Paris-Dauphine - Maude Manouvrier Reproduction Interdite

    2

    TABLE DES MATIERES I. ENONCE DU TP SOUS POSTGRESSQL ................................................................................................. 4

    A. PREMIERE UTILISATION DE POSTGRESQL ET CONNEXION A LA BASE DE DONNEES .................................. 4 B. LANCER LINTERPRETEUR SQL ................................................................................................................ 4 C. CREATION DE LA BASE EXEMPLE .............................................................................................................. 4 D. INTERROGATION DE LA BASE DE DONNEES EXEMPLE ............................................................................... 5 E. MODIFICATION DU SCHEMA DE LA BASE EXEMPLE ....................................................................................... 6

    II. SCRIPT DE LA BASE DE DONNEES EXEMPLE .............................................................................. 7

    III. EXEMPLE DE FONCTIONS SQL, PL/PGSQL ET DE TRIGGER ................................................... 9

    A. FONCTIONS SQL ...................................................................................................................................... 9 B. FONCTION TRIGGER EN PL/PGSQL ........................................................................................................ 10

    1. Langage PL/pgSQL ............................................................................................................................... 10 2. Exemple de fonction utilise dans un dclencheur ................................................................................ 10

    C. TRIGGER ................................................................................................................................................ 11

    V. ANNEXE 1 : INTERFACE DE LOUTIL PGADMIN ....................................................................... 12

    A. LANCER PGADMIN ................................................................................................................................. 12 B. SE CONNECTER ....................................................................................................................................... 13 C. EXECUTER DES COMMANDES SQL ......................................................................................................... 14 D. VISUALISER LES RELATIONS DE LA BASE DE DONNEES ........................................................................... 16 E. ACCES A LAIDE ......................................................................................................................................... 16

    VI. ANNEXE 2 : EXEMPLES DE PROGRAMMES JDBC/ODBC ......................................................... 18

    A. EXEMPLE DE PROGRAMME JDBC ........................................................................................................... 18 B. CONNEXION DUNE SOURCE DE DONNEES A ODBC ............................................................................... 18

    VIII. ANNEXE 4 : INSERTION DES NUPLETS DE LA BASE EXEMPLE ........................................ 21

    VIII. ANNEXE 5 : INSTALLATION DE POSTGRESQL SOUS WINDOWS ...................................... 22

  • TP PostgreSQL

    Universit Paris-Dauphine - Maude Manouvrier Reproduction Interdite

    3

    PostgreSQL est un Systme de Gestion de Bases de Donnes Relationnel Objet, open source,

    successeur de Ingres, dvelopp par lUniversit de Californie de Berkeley. Pour plus

    dinformations sur PostgreSQL, vous pouvez regarder les sites suivants : http://www.postgresql.org

    http://www.grappa.univ-lille3.fr/polys/reseaux-2000/reseaux022.html

    Documentation en franais : http://docs.postgresqlfr.org/

    Site de la communaut franaise : http://www.postgresql.fr/

    Ce document a pour objectif de vous aider utiliser ce SGBD. Il contient le sujet du TP (voir

    Section I), le script de la base de donnes exemple (voir Section II), le scripts dinsertion des

    nuplets (voir Section VIII), des exemples de fonctions SQL, PL/pgSQL et de dclencheur

    (voir Section III), une description de linterface de loutil PgAdmin (voir Section V), un

    exemple de programme ODBC (voir Section VI) et quelques informations sur JDBC (voir

    Section VI). Il contient galement un petit guide dinstallation de PosgreSQL sous Windows

    (voir Section VIII).

    http://www.postgresql.org/http://www.grappa.univ-lille3.fr/polys/reseaux-2000/reseaux022.html

  • TP PostgreSQL

    Universit Paris-Dauphine - Maude Manouvrier Reproduction Interdite

    4

    I. ENONCE DU TP SOUS POSTGRESSQL

    Ce TP a pour objectif de vous faire manipuler le SGBD PostgreSQL et le langage SQL.

    Les scripts SQL de la base de donnes exemple peuvent tre retrouvs ladresse : http://www.lamsade.dauphine.fr/~manouvri/TPBD/

    Ils sont galement disponibles sur MyCourse : L3 Informatique_2014-2015_Bases

    de donnes relationnelles_Maude Manouvrier

    A. Premire utilisation de PostgreSQL et connexion la base de donnes

    1. Lancer un browser web et connecter vous ladresse : https://manager.crio.dauphine.fr/mes-projets/

    Faites create_project et retenez bien le nom et le mot de passe associ votre

    projet.

    2. Lancer - Voir Section V page 12 pour plus de dtails sur linterface.

    3. Connecter vous la base en cliquant sur le bouton reprsentant une prise de courant en haut gauche. Une fentre apparat. Voir copie dcran dans le REDAME sur MyCourse.

    4. Taper postgres.crio.dauphine.fr dans le champ Hte de la fentre: il sagit de ladresse du serveur sur lequel t install le SGBD PostgreSQL.

    5. Taper CrioUNIX dans le champ Nom : le bouton Valider nest plus gris.

    6. Taper le nom du projet cr ltape 1 dans le champ Base maintenance : la base de donnes, sur laquelle vous allez travailler, a le mme nom que votre nom dutilisateur.

    7. Taper le nom du projet cr ltape 1 dans le champ Nom Utilisateur et votre Mot de Passe dans le champ correspondant et cliquer sur Valider.

    8. Cliquer sur le symbole + situ cot du terme Groupes de Serveurs dans le menu gauche, puis sur le symbole + situ cot de CRIO UNIX, puis sur le symbole + situ

    cot de Base de donnes, puis sur le symbole correspondant votre nom de base de

    donnes cre ltape 1.

    B. Lancer linterprteur SQL

    Cliquer sur le bouton contenant le mot SQL situ dans la barre dicnes en haut de la fentre

    de PgAdmin3 pour lancer linterprteur de requtes SQL - Voir Section V page 12 pour

    plus de dtails sur linterface. Il sagit du principal outil que vous utiliserez pendant ce TP,

    lobjectif de ce TP tant le langage SQL.

    C. Cration de la base exemple

    1. Crer le schma de donnes de la base exemple en excutant le script BDExemple.sql1

    dans linterprteur de requtes SQL.

    1 http://www.lamsade.dauphine.fr/~manouvri/TPBD/BDExemple.sql ou sur

    MyCourse

    http://www.lamsade.dauphine.fr/~manouvri/BD/CoursBD_MM.htmlhttp://www.lamsade.dauphine.fr/~manouvri/BD/CoursBD_MM.htmlhttp://www.lamsade.dauphine.fr/~manouvri/TPBD/BDExemple.sql

  • TP PostgreSQL

    Universit Paris-Dauphine - Maude Manouvrier Reproduction Interdite

    5

    Il vous suffit de recopier le contenu du fichier dans la fentre du haut de linterprteur.

    Vous pouvez aussi sauvegarder le fichier dans votre rpertoire et louvrir dans

    linterprteur SQL.

    Attention : Linterface de la version de PgAdmin3, installe au CRIO UNIX, a un bug. Si

    vous ouvrez un fichier dans linterprteur SQL, rien ne se passe. IL faut ouvrir les fichiers

    dans un diteur de texte quelconque et copier le contenu dans linterprteur SQL.

    Une version papier du script de cration de la base exemple est donne dans Section II

    (voir page 7). Une partie de ce script vous a t dcrit en cours.

    Pour excuter le script SQL il suffit de cliquer sur le bouton reprsentant un triangle vert

    en haut de linterprteur SQL.

    2. Insrer les nuplets exemples, en excutant le script dinsertion BDInsertion.sql,2. Une version papier du script vous est donn dans la section VIII page 21.

    3. Excuter le script FonctionEtTrigger.sql3 (voir version papier la Section III

    page 9) pour crer la fonction FunctionTriggerReservation() ainsi que le

    dclencheur InsertionReservation.

    4. Afin de vous approprier le schma de la base et raliser plus facilement les requtes

    demandes dans la section suivante, insrer (par la commande SQL INSERT) les nuplets

    suivants :

    a. Un dpartement, b. Un enseignant dans le dpartement MIDO, c. Un tudiant, d. Une salle, e. Une rservation pour un enseignement existant, f. Une rservation qui chevauche une rservation existante (pour tester

    laffichage du dclencheur).

    NB : Si vous slectionnez plusieurs lignes avant de cliquer sur le bouton , seules les lignes

    slectionnes seront excutes.

    Pour mettre des lignes en commentaires, vous devez prcder chaque ligne par (deux tirets)

    ou placer les lignes mettre en commentaire entre /* et */.

    Vous pouvez reprendre et suivre le polycop