UIN

*****

Niveau : L2 INFO/Telecoms Enseignant : ALMOU Bassirou

Module de Base de données : cas de PostgreSQL

PostgreSQL est un Système de Gestion de Bases de Données Relationnelles Objet (SGBDRO ou ORDBMS en
Anglais) sous licence GPL, disponible sur l’ensemble des plates-formes Unix,  PostgreSQL est très complet:
- Il supporte le transactionnel
- Il supporte les procédures stockées
- Il permet à l’utilisateur de définir ses types et ses fonctions
- Il permet de poser des verrous
- Il permet les select imbriqués
- Il permet la réplication
PostgreSQL utilise les commandes standards de SQL
Quelques commandes usuelles
- sudo apt update; apt-get install Installation en ligne du server postgreSQ, client, et pgadmin,
postgresql utilitaire à l’image de phpMyAdmin
- sudo apt-get install postgresql-client
- sudo apt-get install pgadmin3
sudo apt-get install postgresql-contrib postgresql-contrib permet d’installer les extensions reconnues par la
communauté POSTGRES
Remarque : Sous Windows, vous pouvez installer d’autres module grâce à STACKBUILDER (C:\Program
Files\PostgreSQL\9.6\bin\[Link])

Le site [Link] héberge de nombreux projets développés par des équipes indépendantes :
- connecteurs pour les différents langages ;
- langages procéduraux ;
- outils d'aide à l'administration ;
- logiciels pour la haute disponibilité (réplication, gestion des connexions, etc.).
Citons :
- pgFouine création de rapports à partir du journal d'activité de PostgreSQL
- PGCluster réplication synchrone multi-maîtres
- pgpool gestion des connexions (limitation, réplication, répartition, parallélisation)
- pg-toolbox un ensemble de scripts pour l'aide à l'administration D'autres projets existent :
- Slony [Link] réplication maître vers plusieurs esclaves
- phpPgAdmin [Link] interface Web d'administration
- pgAdmin [Link] client d'administration

Vérifier le type de connexion

grep -v '^#' /etc/postgresql/9.4/main/pg_hba.conf|grep 'peer'
Pour se connecter au serveur :
- sudo su postgres
- sudo -u postgres psql
/etc/postgresql/9.4/main/[Link] Editer ce fichier pour permettre à d’autres machines de se connecter
au serveur PostgreSQL. Pour ce faire, localisez la ligne
#listen_addresses = 'localhost' et changez-la en : listen_addresses =
'*'

Pour permettre aux machines utilisant la connexion IPV4 ou IPv6, remplacer 'localhost' par '
'::
Postgresql écoute sur le port 5432 par défaut
Soit le Modèle Conceptuel de Données de la gestion des notes
1
 Remarque : Nous utilisons ce MCD pour répondre aux questions
1. Déduire le MLD issu du MCD ci-dessus
Comment se connecter pour la premiere fois au serveur postgres ?
$su postgres
#psql [nom de la base]
#

Hiérarchie des objets de PostgreSQL server

Source : extrait du livre Learning PostgreSQL
CREATE DATABASE nom
[ [ WITH ] [ OWNER [=] nom_utilisateur ]
[ TEMPLATE [=] modèle ]
[ ENCODING [=] codage ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] limite_connexion ] ]

Remarque :
- Le nom du modèle squelette de la nouvelle base de données ou DEFAULT pour le modèle par défaut
(template1).
- Pour avoir l’aide sur la syntaxe de creation de la base de donnees :
\h create database ;
Create database scolarite Création de la base de données scolarite
Créer une base de données ventes possédée par l'utilisateur ali utilisant le tablespace espace_ali comme espace par
défaut :
CREATE DATABASE ventes OWNER ali TABLESPACE espace_ali;
Créer une base de données ventes qui supporte le jeu de caractères ISO-8859-1
CREATE DATABASE ventes ENCODING 'LATIN1' TEMPLATE template0;
2. Créer la base de données note tout en respectant les paramètres suivants :
• Propriétaire postgres
• Template=template1
• Tablespace=default
• Connection limit = 4

2
psql scolarite Accéder à la base de données scolarite.
 Si vous n'indiquez pas le nom de la base, alors psql utilisera par
défaut le nom de votre compte utilisateur.
\h create database Fournit l’aide sur la syntaxe complète de création de base de
données
\h create user ; sur la syntaxe complète de création d’un utilisateur

CREATE USER username

[ WITH
[ SYSID uid ]
[ PASSWORD 'password' ] ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ IN GROUP groupname [, ...] ]
[ VALID UNTIL 'abstime' ]
Exemple:
#CREATE USER ibrahim WITH PASSWORD 'fantastic' VALID UNTIL 'Jan 1, 2018';
# CREATE USER techonthenet WITH PASSWORD 'fantastic' VALID UNTIL 'infinity';

3. Créer l’utilisateur adnane tout en respectant les paramètres suivants:

• SYSID 2525
• PASSWORD 'tresor17'
• CREATEDB
• IN GROUP postgres
4. Créer l’utilisateur ben tout en respectant les paramètres suivants:
• SYSID 2545
• PASSWORD 'pouvoir15'
• NOCREATEDB
• IN GROUP postgres
ALTER USER username
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'abstime' ]
drop user iro ; Supprimer l’utilisateur iro
5. Modifier le compte de adnane pour lui attribuer la possibilité de créer d’utilisateurs avec CREATEUSER
Gestion de groupe d’utilisateurs

Syntaxe :
ALTER GROUP name ADD USER
username [, ... ]
ALTER GROUP name DROP USER
username [, ... ]

Exemple
- ALTER GROUP ventes ADD USER rabi, ali;
- ALTER GROUP ventes DROP USER rabi;
6. Créer le groupe super avec la possibilité de créer une base de données
7. Mettre l’utilisateur adnane dans le groupe super
Quelques commandes usuelles
drop database Détruire une base
pg_dump Extraire une base dans un script
pg_dumpall Extraire toutes les bases dans un script
Postgres Activer postgres en mode simple utilisateur
3
 postgresql-dump Permettre une mise à jour de la base de données
Postmaster Le serveur multi-utilisateurs
psql -U toto Se connecter avec le compte toto
psql -c requete Indiquer un c=fichier contenant des requêtes
psql -o fichier Indiquer un fichier de sortie
psql -p port Spécifier le port du serveur, par defaut 5432
psql – H Provoquer une sortie HTML
psql – W Provoque la demande de mot de passe
\dS Affiche la liste des tables système
\dt Affiche les tables de la base courante
\d Affiche la liste de toutes les tables
\z Afficher les autorisations
\!cmde Exécute une commande via shell
\df Afficher les fonctions
\q Quitter
\l Affiche la liste des bases de données
\h create table Donne de l’aide sur la syntaxe de création d’une table

-- ligne en commentaire
/* paragraphe */

Création de la base de données « create database bourseetudes

bourseetudes »
Suppression de la base de données « Drop database bourseetudes
bourseetudes »
Accéder à la base de données « psql bourseetudes
bourseetudes »
Le programme psql dispose d'un certain nombre de commandes internes qui ne sont pas des commandes SQL. Elles
commencent avec le caractère antislash
Par exemple, vous pouvez obtenir de l'aide sur \h
la syntaxe de nombreuses commandes SQL de
PostgreSQL
Pour sortir de psql \q
Pour plus de commandes internes \?
Select current_user \g
\d+ Donne la liste des tables de la base de données
\d nomTable ; Description de la table nomTable
\x Affichage étendu
\df Liste des fonctions
Gestion des utilisateurs
\h create user ;
Définir le nombre de connexion
a) SELECT datconnlimit FROM pg_database WHERE datname='postgres';
b) ALTER DATABASE postgres CONNECTION LIMIT 1;
c) SELECT datconnlimit FROM pg_database WHERE datname= 'postgres';

Role

4
 Les attributs des roles
Source : extrait du livre Learning PostgreSQL
CREATE ROLE financier LOGIN;
CREATE DATABASE finances ENCODING 'UTF-8' LC_COLLATE 'en_US.UTF-8' LC_ CTYPE
'en_US.UTF-8' TEMPLATE template0 OWNER financier;
Gestion des privilèges
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
Les differents privileges: SELECT, INSERT, UPDATE, DELETE, RULE ,ALL, object [ tables, views, sequences]

Retirer les privilèges

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

5
6
Bloquer la table durant une transaction :
LOCK [ TABLE ] name
LOCK [ TABLE ] name IN lock_mode Lock_mode:
ˉ ACCESS SHARE MODE : automatiquement bloquée pour le select,update ou delete. Il bloque
egalement le mode ACCESS EXCLUSIVE ˉ ROW SHARE MODE :
ˉ ROW EXCLUSIVE MODE : bloque ALTER TABLE, DROP TABLE, VACUUM, et CREATE
INDEX ˉ SHARE MODE
ˉ EXCLUSIVE MODE
ˉ SHARE ROW EXCLUSIVE MODE
ˉ ACCESS EXCLUSIVE MODE
Supprimer une table : DROP TABLE name [, ...] Creation
d’une vue
CREATE VIEW view AS query

10. Créer une vue qui donne la liste des étudiants qui une moyenne générale supérieure ou égale a 10

case 11. Sélectionner les étudiants qui vont

when condition then ‘traitement1’ ‘REDOUBLER’ lorsque la moyenne <10,
when condition then ‘traitement2’ ‘PASSER’ lorsque la moyenne >=10,
end EXCLUS lorsque la moyenne <5
Creation de sequence
CREATE SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE nextval(’name’)
Exemple:
Create sequence suite; Select
nextval(‘suite’);
currval(’name’) Select currval(‘suite’);
setval(’name’, newval) Select setval(‘suite’,100);
12. Créer une séquence qui incrémente l’identifiant de chaque étudiant. Idem pour les autres tables
Type de donnees

Backup:
a) pg_dump Exemple:
pg_dump -h localhost -p 5432 -U utilisateur -F c -b -v -f [Link] mydb
b) pg_dumpall
c) pg_restore
d) pg_basebackup

Modification de la structure de la table

ALTER TABLE table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT default value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD CONSTRAINT newconstraint definition
ALTER TABLE table
OWNER TO newowner
a. Ajouter la

7
b. Creation des contraintes
- Ajouter une clé primaire à la table NOTER
- Ajouter les clés étrangères dans la table NOTER
- Créer la contrainte qui oblige la saisie des notes comprises entre 0 et 20 - Créer une contrainte qui
oblige la saisie d’une date de naissance >=17 et <=45
- Définir la nationalité ‘nigerienne’ comme nationalite par défaut

Création de domaine

CREATE DOMAIN nom [AS] type_donnee

[ COLLATE collation ]
[ DEFAULT expression ]
[ contrainte [ ... ] ]

où contrainte est :

[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL | CHECK (expression) }

Les domaines permettent d'extraire des contraintes communes à plusieurs tables et de les regrouper en un seul
emplacement, ce qui en facilite la maintenance.
Exemple :
CREATE DOMAIN code_postal AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE courrier(
id_adresse SERIAL PRIMARY KEY,
rue1 TEXT NOT NULL,
rue2 TEXT,
rue3 TEXT,
ville TEXT NOT NULL,
code_postal code_postal_us NOT NULL
);

8
Creation de TRIGER Syntaxe
:
CREATE EVENT TRIGGER nom
ON evenement
[ WHEN variable_filtre IN (valeur_filtre [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE nom_fonction()
Remarque:
CREATE EVENT TRIGGER crée un nouveau trigger sur événement. À chaque fois que l'événement désigné
intervient et que la condition WHEN associée au trigger est satisfaite, la fonction du trigger est exécutée
- evenement : Le nom de l'événement qui déclenche un appel à la fonction donnée
- variable_filtre: Le nom d'une variable utilisée pour filtrer les événements. Ceci rend possible de restreindre
l'exécution du trigger sur un sous-ensemble des cas dans lesquels ceci est supporté
- nom_fonction: Une fonction fournie par un utilisateur, déclarée ne prendre aucun argument et renvoyant le
type de données event_trigger Exemple:

CREATE OR REPLACE FUNCTION annule_toute_commande()

RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION 'la commande % est désactivée', tg_tag;
END;
$$;

CREATE EVENT TRIGGER annule_ddl ON ddl_command_start

EXECUTE PROCEDURE annule_toute_commande();

CREATE TRIGGER virement

BEFORE UPDATE
ON compte
FOR EACH ROW
EXECUTE PROCEDURE calculSolde();

Exemple
EXISTS SELECT * FROM etudiant WHERE EXISTS (SELECT * FROM
etudiant WHERE ide=1); ou
SELECT * FROM etudiant WHERE EXISTS (SELECT 1 FROM
etudiant WHERE ide=1);
UNION SELECT ide,nom,prenom FROM etudiant WHERE
UNION ALL
INTERSECT
EXCEPT
ANY
ALL
TRIM
||
Type de donnees
il y a 3 grandes catégories de types de données :
- numeric
- chaine de caracteres
- date
NUMERIC
Taille Plage

9
smallint Equivalent à int2 en 2octets -32768 à +32767
SQL
Int Equivalent à int4 en 4 octets -2147483648 à +2147483647
SQL
Bigint Equivalent à int8 en 8 octets -9223372036854775808 à +9223372036854775807
SQL
Numeric ou Pas de différence varie Jusqu’à 131072 dans la partie entière et 16383 dans la
decimal partie décimale
real 4 octets
Remarque : il est conseillé d’utiliser les types NUMERIC er DECIMAL pour stocker des données monétaires
CARACTERE
Longueur
char Equivalent à char(1) 1
name Propre à PostgreSQL 64
Char(n) 1à 10485760
Varchar(n) 1 à 10485760
text Illimité
DATE
Taille
Timestamp Equivalent à timestamp 8 octets
without time
zone
Timestamp
with time
zone
Date
Time without
time zone

Time with time

zone
interval
LOGIQUE
boolean boolean, true ou false
GEOMETRIQUE
POINT
LSEG ligne
PATH Liste des points
BOX rectangle
CERCLE

POLYGON
RESEAU
INET Adresse IP avec
masque du réseau
MACADDR Adresse MAC

10
 Exemple 1 :
SET timezone TO 'Africa/Niger';
Show timezone;
SELECT now();
Exemple 2
\x
SELECT now(), now()::timestamp, now() AT TIME ZONE 'CST', now()::timestamp AT TIME ZONE 'CST';
TABLEAU
Exemple:
create Table adresse(
coordoneesGPS interger[5], telephone integer[8][8],
region varchar[][][]); Insertion de données dans la table adresse: insert into adresse
values('{45,70,52}','{{82546987},{20456978}}','{{Maradi},{Tahoua},{Zinder}}'); Selection de
donnees :
SELECT telephone[2][1] FROM adresse ;

Création de table à partir du resulat d’une requete

a. table temporaire
SELECT * temporary etudiantTMP FROM etudiant ;
b. table ordinaire:
Exemple de création des tables

CREATE TABLE client ( idc

SERIAL PRIMARY KEY, nom
TEXT NOT NULL, prenom TEXT
NOT NULL, email TEXT NOT
NULL UNIQUE, motpasse TEXT
NOT NULL, CHECK(nom !~ '\s'
AND prenom !~ ' \s'),
CHECK (email ~* '^\w+@\w+[.]\w+$'),
CHECK
(char_length(motpasse)>=8)
);
CREATE TABLE ventes ( idv
SERIAL PRIMARY KEY,
idc INT UNIQUE NOT NULL
REFERENCES account(idc), nbr INT
DEFAULT 0,
achat float, total_achat
float
);
VUE
Syntaxe:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ (
column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Exemple:
CREATE OR REPLACE VIEW information(nom,prenom,email) AS SELECT * client;
VUE MATERIALISEE

11
CREATE MATERIALIZED
[ (column_name [, ...] ) ] VIEW table_name
[ WITH ( storage_parameter [= value] [, ... ] )
[ TABLESPACE tablespace_name ]
AS query ]

12
13
CREATE index nomIndex ON
nomTable(colonne ASC/DESC)
Creation d’une function:

CREATE [ OR REPLACE ] FUNCTION

nom ( [ [ modearg ] [ nomarg ] typearg [ { DEFAULT | = } expression_par_defaut ] [, ...] ] ) ] )
[ RETURNS type_ret
| RETURNS TABLE ( nom_colonne type_colonne [, ...] ) ]
{ LANGUAGE nom_lang
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| COST cout_execution
| ROWS nb_lignes_resultat
| SET parametre { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'fichier_obj', 'symbole_lien'
} ...
[ WITH ( attribut [, ...] ) ]
- modearg; Le mode d'un argument : IN, OUT, INOUT ou VARIADIC. En cas d'omission, la valeur par
défaut est IN. Seuls des arguments OUT peuvent suivre un argument VARIADIC. Par ailleurs, des
arguments OUT et INOUT ne peuvent pas être utilisés en même temps que la notation RETURNS TABLE.
- argtype : Le(s) type(s) de données des arguments de la fonction (éventuellement qualifié du nom du
schéma), s'il y en a. Les types des arguments peuvent être basiques, composites ou de domaines, ou faire
référence au type d'une colonne.
- expression_par_defaut : Une expression à utiliser en tant que valeur par défaut si le paramètre n'est pas
spécifié.
- type_ret: Le type de données en retour (éventuellement qualifié du nom du schéma). Le type de retour peut
être un type basique, composite ou de domaine, ou faire référence au type d'une colonne existante.
Les requêtes
Syntaxe :
SELECT [DISTINCT | ALL]
<expression>[[AS] <output_name>][, …] [FROM
<table>[, <table>… | <JOIN clause>…]
[WHERE <condition>]
[GROUP BY <expression>|<output_name>|<output_number>
[,…]]
[HAVING <condition>]
[ORDER BY <expression>|<output_name>|<output_number>
[ASC | DESC] [NULLS FIRST | LAST] [,…]]
[OFFSET <expression>]
[LIMIT <expression>];
CASE
Syntaxe:

CASE WHEN <condition1> THEN <expression1> [WHEN <condition2> THEN

<expression2> ...] [ELSE <expression n>] END
Exemple SELECT

CASE
WHEN now() > date_trunc('day', now()) +interval '12 hours' THEN 'PM' ELSE 'AM'
END;

14
Les opérateurs multi-lignes
Les opérateurs multi-lignes sont les suivants :
• IN compare un élément à une donnée quelconque d’une liste ramenée par la sous-interrogation. Cet
opérateur est utilisé pour les équijointures ou autojointures. L’opérateur NOT IN sera employé pour les
jointures externes. • ANY compare l’élément à chaque donnée ramenée par la sous-interrogation.
L’opérateur « =ANY » équivaut à IN . L’opérateur « <ANY » signifie « inférieur à au moins une des valeurs
» donc « inférieur au

maximum ». L’opérateur « >ANY » signifie « supérieur à au moins une des valeurs » donc « supérieur au
minimum ».
• ALL compare l’élément à tous ceux ramenés par la sous-interrogation. L’opérateur « <ALL » signifie «
inférieur au minimum » et « >ALL » signifie « supérieur au maximum ». Les variables de substitution

NULLIF (a, b)
TRUNCATE TABLE nomTable;
Nettoye et analyse une base de donnees
ˉ VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
ˉ VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
Verifier les parametres AUTOVCUUM

SELECT *FROM pg_settings WHERE name LIKE 'autovacuum%'

Etude de cas : gestion des bourses d’études

15
Objectif : Créer une base de données PostgreSQL sous Linux pour la gestion des bourses d’études. a)
Dictionnaire de données

Champ Type du champ contrainte Explication

ide int Clé primaire Clé primaire qui s’auto incrémente à chaque
nouvel enregistrement
nom varchar Not null nom de l’étudiant
Taille maximale=25
Pren varchar Not null Prénoms de l’étudiant
Taille maximale=25
datenaiss date Not null format Date de naissance de l’étudiant
yyyy-mm-dd
lieunaiss varchar Not null Lieu de naissance de l’étudiant
Taille maximale=25
Genre char M : masculin Genre de l’étudiant
F : féminin
Taille=1
idb intege Clé primaire Clé primaire qui s’auto incrémente à chaque
nouvel enregistrement
nomb varchar Not null Nom de la bourse (exemple : nationale, belge,
Taille maximale=150 coopération canadienne)
mnt decimal Not null Montant de la bouse
Format decimal(7,3)
nbr int Not null Le nombre des bourses octroyées
dated date Not null format Date de début de la bourse
yyyy-mm-dd
datefin date Not null format Date à laquelle la bourse prend fin
yyyy-mm-dd
diplm varchar Not null Le nom du diplôme avec lequel l’étudiant
Taille maximale=50 obtient la bourse
diplr varchar Not null Le diplôme que l’étudiant peut avoir à la fin de
Taille maximale=50 la formation
univ varchar Not null L’université d’accueil après avoir eu la bourse
Taille maximale=50
pays varchar Not null Le pays où se trouve l’université où l’étudiant
Taille maximale=25 va étudier

b) Le Modèle Conceptuel de Données(MCD)

c) Modèle Logique de Données

ETUDIANT(ide, nom, pren, datenaiss, lieunaiss)
BOURSE(idb, nomb, mnt, nbr, dated, datefin)
OBTENIR(ido, #ide, #idb, diplm, diplr, univ, pays)

16
 Création de la table « ETUDIANT »
CREATE TABLE ETUDIANT (ide int primary key, nom varchar(50) NOT NULL, pren varchar(50) NOT NULL,
datenaiss varchar(10) NOT NULL, lieunaiss varchar(50) NOT NULL, genre char(1) CONSTRAINT ck_genre
CHECK(genre IN(‘F’,’M’))

Création de la table « BOURSE »

create table BOURSE(idb int primary key, nomb varchar(25) not null, mnt int(11) not null, nbr int(5) not null, dateb
date not null, datefin date not null
)
Création de la table « OBTENIR »
Create table OBTENIR (
Ido int primary key, ide foreign key fk_etud references etudiant(ide), idb foreign key fk_brse references
bourse(idb), Univ varchar(50) NOT NULL , pays varchar(25) NOT NULL DEFAULT ‘Niger’,diplr varchar(50),
diplm varchar(50) NOT NULL
)

Table ETUDIANT
ide Nom pren datenaiss lieunaiss
1 Ali Iro 1987-02-12 Goure
2 Oussou Mani 1984-08-17 Gaya
3 Ide Kalla 1991-05-21 Bilma
4 Sani Moudi 1995-02-01 Tessaoua
5 Garba Hamza 1988-07-11 Timia
6 Hadi Hassia 1999-12-21 Ngal
7 Issa Noura 1992-02-26 Madaoua
8 Balki Moussa 1990-12-05 Bilma
9 Ibra Sani 1995-11-02 Torodi
10 Nadia Haro 1982-02-06 Birni Konni
11 Saadatou Baba 1985-05-05 Maradi
12 Dan Tanin Kaka 1979-11-23 Timia
13 Bermo Habsou 1987-04-07 Bouza
14 Amina Sala 1988-08-05 Magaria

Table BOURSE
idb nomb Mnt nbr dated datefin

1 Co Fr 150000 52 2015-12-11 2018-12-11

2 Co Blg 25000 102 2015-01-10 2018-11-02

3 Alg 74500 79 2014-10-01 2018-09-24

4 Maroc 53200 42 2013-12-01 2018-08-16

5 Ng1 35000 2500 2014-05-15 2018-07-08

6 Ng2 75000 1500 2015-01-08 2018-05-30

7 Ng3 125000 500 2013-08-09 2018-04-21

8 Chi 132050 13 2015-11-08 2018-03-13

9 Can 257500 10 2015-12-01 2019-01-15
10 Lux 258600 5 2015-12-24 2019-11-19
11 Turk 45900 16 2016-01-16 2018-01-03
12 Ind 35200 25 2016-02-08 2019-02-25

17
 13 Ngria 38200 200 2016-03-02 2020-04-18
14 Suiss 320000 12 2016-03-25 2021-06-10

Table OBTENIR
ido idb ide diplm diplr univ pays
1 1 1 Licence Master UCAD Senegal
2 2 2 Licence Master UNICAN Niger
3 7 3 BTS Master UAC Benin
4 5 4 Bac C Licence Ouaga1 Burkina
5 12 5 Bac A Licence UAM Niger
6 11 6 Bac A8 Licence UAM Niger
7 2 7 Licence Master UAM Niger
8 3 8 Bac D Licence Libre Tunisie
9 8 9 Bac D Licence UAM Niger
10 6 10 Bac E Licence Maradi Niger
11 4 11 Bac A Master Zinder Niger
12 4 12 Master Doctorat Tahoua Niger
13 12 13 Licence Master Agadez Niger
14 11 14 Bac A Licence Rabat Maroc
15 12 15 Bac A Licence Alger Algerie

Insertion des données dans les tables

Insertion de données dans la table INSERT INTO etudiant(nom,pren,datenaiss,
« etudiant »(respect de l’ordre des colonnes, lieunaiss,genre)
expLicenceitement) VALUES (‘Ali’,’Iro’,’ 1987-02-12’,’Goure’, ’M’)
Insertion de données dans la table INSERT INTO etudiant VALUES (‘Ali’,’Iro’,’ 1987-02-
« etudiant »(respect de l’ordre des colonnes) 12’,’Goure’, ’M’)
Insertion de données dans la table « etudiant » INSERT INTO etudiant(datenaiss, genre,lieunaiss,
(Dans un ordre différent des colonnes) nom,pren)
VALUES (’ 1987-02-12 ’, ’M’, ’Goure’,‘Ali’,’Iro’)
Copier des données depuis/vers un fichier vers /depuis une table

Syntaxe :
COPY nom_table [ ( nom_colonne [, ...] ) ]
FROM { 'nom_fichier' | PROGRAM 'commande' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
----------------------------------------------------------------------
-------------------------------------------------------//--------
COPY { nom_table [ ( nom_colonne [, ...] ) ] | ( requête ) }
TO { 'nom_fichier' | PROGRAM 'commande' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
ostgreSQL et les fichiers du système de fichiers standard
- COPY transfère des données entre les tables de P
- COPY TO copie le contenu d'une table vers un fichier er vers une table (ajoutant les données à celles déjà dans la
- COPY FROM copie des données depuis un fichi table).
- COPY TO peut aussi copier le résultat d'une requête
SELECT Remarque :
COPY ne peut être utilisé qu'avec des tables réelles, pas avec des
vues. Néanmoins, vous pouvez écrire COPY (SELECT ROM * F nom_vue) TO ....

La commande COPY pour insérer les données dans COPY etudiant FROM ‘/home/iat/[Link]’
la table « etudiant » à partir du fichier [Link]

18
Copier l’ide, nom, prenom des étudiant dans le fichier Copy (select ide, nom, pren FROM ETUDIANT) TO
[Link] ‘/home/iat/[Link]
COPY TO copie le contenu d'une table vers un fichier tandis que COPY FROM copie des données depuis un fichier
vers une table (ajoutant les données à celles déjà dans la table). COPY TO peut aussi copier le résultat d'une requête
SELECT.
Si une liste de colonnes est précisée, COPY ne copie que les données des colonnes spécifiées vers ou depuis le
fichier. COPY FROM insère les valeurs par défaut des colonnes qui ne sont pas précisées dans la liste.
Sélectionner tous les étudiants SELECT * FROM etudiant
Sélectionner nom, prénom, genre des étudiants SELECT nom,pren,genre FROM etudiant
Rendre les colonnes plus compréhensibles à SELECT nom NOM,pren PRENOM,genre GENRE FROM
l’utilisateur etudiant
Sélectionner nom, prénom, genre des étudiants SELECT nom NOM,pren PRENOM,genre GENRE FROM
femmes etudiant WHERE genre=‘F’

Mise à jour des données

Mettre à jour le montant de la bourse du UPDATE bourse SET mnt=45000 WHERE nomb=‘Ng1’;
premier cycle du Niger à 45000
Mettre à jour le nom de l’Université d’accueil UPDATE obtenir SET univ=‘Ouaga2’ Where univ=‘Ouaga1’ ;
Ouaga1 du burkina à Ouaga2
Suppression

Supprimer l’étudiant Dan Tanin Kaka né le DELETE FROM etudiant WHRE nom=‘Dan Tanin’ AND
1979-11-23 pren=‘Kaka’ AND datenaiss=‘1979-11-23’;

Creation de vue
Créer une vue qui affiche la liste des étudiants de CREATE OR REPLACE VIEW etudiantMaradi AS
l’Université de Maradi, Afficher le nom, prénom, SELECT nom,pren,datenaiss,lieunaiss, diplr FROM etudiant
date de naissance, lieu de naissance, diplôme à e, obtenir o WHERE [Link]=[Link] AND univ=‘Maradi’;
préparer
Créer une vue qui donne le montant total annuel de CREATE or REPLACE VIEW totalmntbouse AS
la bourse par nom de la bourse SELECT nomb , SUM(mnt*nbr*12) total
FROM bourse b, obtenir o WHERE [Link]=[Link] GROUP BY
nomb;

Requêtes
Sélectionner les étudiants qui sont nés à Goure SELECT * FROM etudiant WHERE lieunaiss=‘Goure’
Sélectionner les étudiants dont les noms SELECT * FROM etudiant WHERE nom LIKE ‘Ga%’
commencent par ‘Ga’
Sélectionner les 30 premiers enregistrements de la SELECT * from recyclage limit 30;
table recyclage
Sélectionner les30 premiers enregistrements de la SELECT * from etudiant limit 30;
table etudiant,
Faire une comparaison de résultat avec la table
etudiants
Exécuter cette requête et comparer le résultat SELECT * from ONLY etudiant limit 30;
Sélectionner les étudiants dont les prénoms se SELECT * FROM etudiant WHERE pren like ‘%lla’ AND
terminent par ‘lla’ et lieu de naissance commence lieunaiss LIKE ‘bil%’
par ‘bil’
Sélectionner le nom, prénom, date de naissance des SELECT nom,pren,datenaiss FROM etudiant WHERE
étudiants dont la date de naissance est comprise datenaiss BETWEEN ‘1984-08-01’ AND ‘1990-01-01’
entre ‘1984-08-01’ et ‘’1990-01-01’

19
 Sélectionner le nombre des étudiants dont la date SELECT COUNT(*) FROM etudiant WHERE datenaiss
de naissance est comprise entre ‘1984-08-01’ et BETWEEN ‘1984-08-01’ AND ‘1990-01-01’
‘’1990-01-01’
Sélectionner les bourses dont le mont dépasse SELECT nomb FROM bourse WHRE mnt >150000
150000
Sélectionner le montant total de la bourse de SELECT SUM(mnt*nbr) MONTANT FROM bourse
coopération chinoise entre 01 janvier 2013 et 31 WHERE dated BETWEEN ‘2013-01-01’ AND ‘2015-12-31’
décembre 2015 AND nomb=‘chin’;
Sélectionner les différents pays d’études et les trier SELECT DISTINCT pays FROM obtenir ORDER BY pays
par ordre décroissant DESC
Sélectionner les étudiants (nom,pren,lieunaiss) qui SELECT nom,pren,lieunaiss FROM etudiant e, bourse b,
ont eu la bourse de coopération française obtenir o
WHERE [Link]=[Link] AND [Link]=[Link] AND nomb=‘Co
Fr’;
Utiliser les requêtes imbriquées pour le même résultat SELECT nom,pren,lieunaiss FROM etudiant e
WHERE [Link] = (SELECT [Link] FROM obtenir o, bourse b
WHERE nomb=‘Co Fr’ AND [Link]=[Link])
Sélectionner les étudiants (nom,pren,lieunaiss) nés à SELECT nom,pren,lieunaiss FROM etudiant e, bourse b
Bilma qui ont eu la bourse nationale du premier cycle WHERE [Link]=[Link] AND [Link]=[Link] AND nomb=‘Ng1’
AND lieunaiss=‘Bilma’
Sélectionner les étudiants dont la bourse prend fin au SELECT nom,pren,lieunaiss,datef FROM etudiant e, obtenir o,
plus tard le 31 décembre 2015 bourse b
WHERE [Link]=[Link] AND [Link]=[Link] AND datefin <=‘2015-
12-31’
Sélectionner les étudiants qui ont eu la bourse pour SELECT nom,pren,datenaiss FROM etudiant e, obtenir o
étudier à l’UCAD WHERE [Link]=[Link] AND univ=‘UCAD’
Sélectionner les étudiants qui ont eu la bourse pour SELECT nom,pren,datenaiss FROM etudiant e, obtenir o
étudier à l’université de Maradi et l’université de WHERE [Link]=[Link] AND univ IN (‘’Maradi,’Tahoua’) AND
Tahoua et qui ont eu le Bac C diplm=‘Bac C’;
Sélectionner les étudiants qui vont étudier hors du SELECT nom NOM,pren PRENOM,datenaiss ‘DATE DE
Niger et qui doivent revenir avec le diplôme de NAISSANCE’ FROM etudiant e, obtenir o WHERE
Master [Link]=[Link] AND pays <>’Niger’ and diplr=‘Master’;
Sélectionner le montant total par bourse entre le SELECT SUM(mnt*nbr) TOTAL, nomb FROM bourse b,
2014-01-01 et 2015-12-31, montant total par mois obtenir o WHERE [Link]=[Link] AND dated BETWEEN
‘2014-01-01’ AND ‘2015-12-31’
GROUP BY nomb;
Sélectionner les donateurs extérieurs de la bourse SELECT nomb, SUM(mnt*nbr*12) total FROM etudiant e,
annuelle supérieure à 30900000 bourse b, obtenir o WHERE WHERE [Link]=[Link] AND
[Link]=[Link] AND nomb NOT IN (Ng1, Ng2, Ng3)
GROUP BY nomb
HAVING SUM(mnt*nbr*12) >30900000;

Function de fenêtrage
 Utiliser la Fonctions de fenêtrage pour SELECT nom, pren, SUM(mnt*12) OVER (PARTITION BY [Link])
déterminer le montant total de la bourse FROM etudiant e, obtenir o, bourse b
par étudiant du second cycle par année WHERE [Link]=[Link] AND [Link]=[Link] AND nomb=‘Ng2’ ;
 Utiliser la Fonctions de fenêtrage pour SELECT nomb, mnt, rank() OVER (PARTITION BY nomb
déterminer le montant total de la bourse ORDER BY mnt DESC)
annuelle par nom de la bourse et donner FROM obtenir o, bourse b
aussi le rang en fonction du montant total WHRE [Link]=[Link];

Ajout d’une colonne à une table ALTER TABLE etudiant ADD photo text;
Modifier une colonne ALTER TABLE etudiant ALTER COLUMN photo varchar(255);
Supprimer la colonne ALTER TABLE etudiant DROP COLUMN photo;
20
 Ajouter une clé étrangère ALTER TABLE obtenir ADD FOREIGN KEY(idb) REFERENCES
bourse(idb);
------------------------------------ou-------------------------------------
ALTER TABLE obtenir ADD CONSTRAINT fk_brs FOREIGN KEY(idb)
REFERENCES bourse(idb);
Supprimer la clé étrangère ALTER TABLE obtenir DROP CONSTRAINT fk_etud;
Ajouter une contrainte CHECK ALTER TABLE bourse ADD CONSTRAINT ck_nbr CHECK(nbr >0);
Supprimer la contrainte CHECK ALTER TABLE bourse DROP CONSTRAINT ck_nbr;
Supprimer la table recyclage DROP TABLE recyclage;

TD numero 1 Dictionnaire de données

PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION
Idc Int(11) Identifiant du client
Nom Varchar(50) Not_null Nom du client
Prenom Varchar(50) Not_null Prénom du client
Tel Int(8) Not_null Numéro de téléphone du client
Residence Varchar(50) Not_null Le quartier de résidence du client

PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION

Idmdl Int(11) Identifiant du modèle
NumModel Int(11) Not_null Numéro du modèle
#idc Int(11) Identifiant du client

PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION

Idm Int(11) Identifiant de la mesure
Epaule Int(2) NOT NULL Mesure de l’épaule
Poitrine Int(3) NOT NULL Mesure de la poitrine
Bassin Int(3) Mesure du bassin
Hanche Int(3) Mesure de la hanche
Ceinture Int(3) Mesure de la ceinture
Cuisse Int(3) Mesure de la cuisse
Longueurchemise Int(3) NOT NULL Longueur de la chemise
Tourcou Int(3) NOT NULL Mesure du tourcou
Manche ENUM(‘1’ ,’0’) NOT NULL Mesure de la manche
tourManche Int(2) Mesure du tourmanche
MancheTroisquart Int(2) Mesure manche troisquart
Lgtroisquart Int(3) Longueur trois quart
LgPantJupe Int() Longueur du pantalon ou du jupe

PROPRIETE TYPE et CONTRAINTE DESCRIPTION

TAILLE
Idt Int(11) Identifiant de la mesure

MURNA
EMPRUNTER

COMPTE EMPRUNT
CLIENT 1,N numEmprunt
1,N 1,1 numCompte
numClient dateEmprunt
AVOIR compte 1,1
client montantEmprunt
type
tel tauxInteret
dateCreation
residence dateEcheance 21
photo 1,N
 Type Varchar(50) NOT NULL

PROPRIETE TYPE et CONTRAINTE DESCRIPTION

TAILLE
Jour Int(11) Le jour ou la mesure a était prise
Prix Int() Le prix de la couture
dateR Date La date du rendez-vous
heureR Time L’heure du rendez-vous
#idc Int(11)
#idm Int(11)
Gestion de comptes bancaires

 Voici un extrait du Modèle Conceptuel de Données de gestion de comptes bancaires de la banque

1,N
1,N
MOUVEMENT
idMouvement EFFECTUER
dateTransaction REMBOURSEMENT
1,1
credit dateRembouse
debit montantVerse

champ Description Type(taille) Contraintes/Forma t

numClient Numéro du client SERIAL PRIMARY KEY
client Le nom du client VARCHAR(50) NOT NULL
tel Numéro de téléphone du client NUMERIC(8) NOT NULL, UNIQUE
residence Résidence d client VARCHAR(50) NOT NULL
photo Photo du client VARCHAR(50) NOT NULL
numCompte Numéro du compte NUMERIC PRIMARY KEY
compte Le nom du compte VARCHAR(50) NOT NULL
type Le type du compte NUMERIC(1) 0 = ‘epargne’
1 = ‘courant’
dateCreation Date de création du compte. TIMESTAMP DEFAULT
(Date système par défaut) CURRENT_TIMESTAMP
numEmprunt Numéro d’emprunt NUMERIC PRIMARY KEY
dateEmprunt Date d’emprunt TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
montantEmprunt Montant emprunté NUMERIC(8) NOT NULL
tauxInteret Le taux d’intérêt NUMERIC(2) NOT NULL

dateEcheance Date échéance de remboursement DATE NOT NULL

idMouvement Identifiant du mouvement SERIAL PRIMARY KEY
dateTransaction Date de transaction TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
credit Le montant crédité NUMERIC(8) DEFAULT 0
debit Le montant débité NUMERIC(8) DEFAULT 0
dateRembouse Date de versement TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
montantVerse Montant versé NUMERIC(8) NOT NULL

 Voici le Modèle Logique de Données

COMPTE(numCompte,compte,type,dateCreation,#numClient)
22
CLIENT(numClient,client,tel,residence,photo)
MOUVEMENT(idMouvement,dateTransaction,credit,debit,#numCompte)
EMPRUNT(numEmprunt,dateEmprunt,montantEmprunt,#numCompte,tauxInteret,dateEcheance)
REMBOURSEMENT(numRembourse,dateRembouse,montantVerse,#numCompte,#numEmprunt)

Travail demandé
1. Donner le nombre de comptes créés par an, de 2010 à 2020
2. Donner le nombre de comptes d’épargne créés par mois en 2020
3. Donner le nombre de comptes d’épargne créés par trimestre en 2020
4. Donner le nombre de comptes d’épargne créés par semaine au mois de mars 2021
5. Donner le nombre de comptes créés par type en 2021
6. Donner le solde de chaque client
7. Donner le nombre de comptes qui n’ont pas effectué de transaction depuis la date de leur création
8. Donner le nombre de client qui n’ont pas remboursé leurs emprunts
9. Donner le montant total entré et le montant sorti par mois en 2020
10.

TD numéro 2
Gestion de transport des voyageurs et messagerie

Attribut Type ContrainTes Explication

idp NUMBER(11) PRIMARY KEY Clé primaire de l’entité PERSONNE.
titre CHAR(3) CHECK(titre La valeur doit être ‘M’ pour Monsieur ou ‘Mme’ pour
IN(‘Mme’,’Mr’) Madame
nom VARCHAR2(50) NOT NULL Le nom du client sur 50 caractères
prenom VARCHAR2(50) NOT NULL Le prénom du client sur 50 caractères
tel NUMBER(8) NOT NULL Le numéro de téléphone sur 8 chiffres
piece La pièce d’identité
idb NUMBER (11) PRIMARY KEY Clé primaire de l’entité BAGAGE.
nbrSacV NUMBER (3)
nbrValV NUMBER (3)
23
 nbrSacGYH NUMBER (3)
nbrColis NUMBER (3)
idbus NUMBER (11) PRIMARY KEY Clé primaire de l’entité BUS.
numbus NUMBER (5) NOT NULL Le numéro du bus
clim NUMBER (1) NOT NULL 1 si le bus est climatisé, 0 sinon
nbrsiege NUMBER (3) NOT NULL Le nombre de sièges du bus
numticket CHAR(11) NOT NULL Le numéro du billet du voyage
Hdepart NUMBER (5) NOT NULL Heure de départ. Format hh :mm
vdepart VARCHAR2(50) NOT NULL La ville de départ
datedep date NOT NULL La date de départ
vdest VARCHAR2(50) NOT NULL La Ville de destination
mnt NUMBER (6) NOT NULL Le montant du voyage
Siege NUMBER (3) NOT NULL Le numéro du siège où un voyageur doit s’assoir
Type NUMBER (1) NOT NULL 0 pour national et 1 pour international c’est-à-dire à
l’extérieur du Niger
Idcol PRIMARY KEY Clé primaire de l’entité COLIS
Valeur La valeur du colis. C’est le montant remboursé au
client en cas de perte du colis
Montant NUMBER (6) NOT NULL Le montant donné par le client au service de
messagerie
Decrire BLOB La description du colis

TD 3
[Link] est un opérateur de téléphonie mobile

Le service courrier de l’entreprise [Link] reçoit ou envoie des courriers aux partenaires. Dans l’ensemble il y
a deux catégories de courriers : Enveloppe et Colis. Et une fois les courriers enregistrés, Ils sont soumis à la Direction
concernée qui les transmet au service destinataire.
Langage de programmation : PHP ; Editeur : Sublime, SGBD : MySQL, Framework : Bootstrap Voici
le Modèle Conceptuel de Données(MCD)

Renseignements sur les attribues des entités

Attribut Contraint Type Explication
cat NOT NULL INT(2) Catégorie du courrier (Enveloppe ou Colis)
jour NOT NULL DATE Jour d’entrée ou de sortie du courrier
type NOT NULL INT(1) 0 pour l’entrée ; 1 pour la sortie

24
 objet NOT NULL TEXT Objet du courrier
numero NOT NULL VARCHAR(20) Numéro du courrier
idd NOT NULL INT(2) Identifiant de la Direction
direction NOT NULL VARCHAR(80) Le nom de la Direction
idp PRIMARY INT Identifiant du partenaire
KEY
nom NOT NULL VARCHAR(50) Nom du partenaire
telp NOT NULL INT(8) Téléphone du partenaire
idservice PRIMARY INT Identifiant du service
KEY
service NOT NULL VARCHAR(50) Nom du service

TD 4
Exercice 2 (10 points) Gestion des comptes bancaires

Soit le schéma de base de données relationnel suivant :

AGENCE (numAgence, nomAgence, villeAgence, Actif)
CLIENT (numCompte, nomClient, prenomClient, dateNaiss villeClient,telClient,fermer)
MOUVEMENT(#numAgence, # numCompte, entree, sortie,typecompte, statut)
EMPRUNT (numEmprunt,#numAgence, #numCompte, montant, datedebut, datefin,tauxInteret)
REMBOURSEMENT(montantRembouse,dateRemb,# numEmprunt)

Ecrire les requêtes suivantes en SQL :

1. Clients ayant fait des emprunts de plus de cinq ans

2. Clients ayant un compte et n’ayant pas fait d’emprunt
3. Clients ayant des comptes courants et n’ayant pas effectué de mouvements il y a plus de trois mois
4. Donner le Solde des comptes le solde dépassant “5000000”
 Solde=la somme des entrées moins la somme des sorties
5. Nombre de clients pour chacune des villes suivantes : “Niamey”, “Maradi”, “Agadez”
6. Lister les clients qui ont remboursé plus de 75% de leurs emprunts
7. Lister les clients n’ayant pas remboursé leurs emprunts
8. Donner la liste des clients dont la date limite de remboursement d’emprunt est dans 30 jours
9. Si la pénalité est de 1500Fcfa par jour, quel est montant de la pénalité à la date d’aujourd’hui pour
chaque client ayant fait des emprunts mais il n’a pas remboursé à temps?
10. Créer l’utilisateur ali avec le mot de passe de votre choix
11. Retirer le privilège de modification sur les colonnes ‘entree’ et ‘sortie’ de la table mouvement à
l’utilisateur ali
12. Créer le synonyme COMPTE de la table CLIENT ;
13. Créer le rôle CONTROLEUR
14. Attribuer les privilèges SELECT, CREATE SESSION au rôle CONTROLEUR
15. Attribuer le rôle CONTROLEUR à ali
16. Créer une transaction pour le virement d’une somme de 77500000 dans le compte 3578520
17. Apres avoir effectuer ce virement, créer un point de restauration nommé POINTvir3578520
18. Apres vérification, on a constat, le chef d’agence a confirmé le virement. Alors faire une
confirmation de cette transaction dans la base de données au point POINTvir3578520
19. Créer une séquence nommée SEQrembour qui commence par 1
20. Sélectionner les contraintes de la table MOUVEMENT

25
TD 5 Transfert d’argent
Propriété Type Contraintes Explication
numAgence NUMBER PRIMARY KEY Numéro de de l’agence
nomAgence VARCHAR2(50) UNIQUE, NOT NULL Nom de l’agence de transfert d’argent
localite VARCHAR2(50) NOT NULL La situation géographique de l’agence
region VARCHAR2(25) NOT NULL La région ou se trouve l’agence
numRecu NUMBER Le numéro du reçu
nomExp VARCHAR2(50) NOT NULL Nom de l’expéditeur
prenomExp VARCHAR2(50) NOT NULL Prénom de l’expéditeur
telExp NUMBER(8) NOT NULL Téléphone de l’expéditeur
montant NUMBER >5000 Montant à envoyer
nomDest VARCHAR2(50) NOT NULL Nom du destinataire
prenomDest VARCHAR2(50) NOT NULL Prénom du destinataire
telDest NUMBER(8) NOT NULL Téléphone du destinataire
motPasse VARCHAR2(15) Mot de passe
pieceDest VARCHAR2(12) Obligatoire à la réception de Numéro de la pièce d’identité
l’argent
destination VARCHAR2(50) NOT NULL
codeEnvoi NUMBER PRIMARY KEY Code d’envoi
matAgent VARCHAR2(10) UNIQUE, NOT NULL Matricule de l’agent
nomAgent VARCHAR2(50) Nom de l’agent
prenomAgent VARCHAR2(50) Prénom de l’agent
compte VARCHAR2(25) UNIQUE, NOT NULL Compte de l’agent
motpasse VARCHAR2(15) NOT NULL Mot de passe de l’agent
profile NUMBER(1) DEFAULT 1 Si 0 alors administrateur, 1 pour agent
dateEnvoi TIMESTAMP NOT NULL, Date d’envoi
Format: ‘YYYY-MM-DD
HH:MM:SS’
dateReception TIMESTAMP NOT NULL, Date de réception
Format: ‘YYYY-MM-DD
HH:MM:SS’
Type NUMBER(1) 1 si réception, 0 si envoi

26
 AGENT
HAGENCE matAgent
1,N POSSEDER 1,1 nomAgent
numAgence
nomAgence prenomAgent
localite compte
region motpasse
TRANSFERT
codeEnvoi 1,N
numRecu
nomExp
prenomExp 1,1
GERER
telExp
montant
nomDest
prenomDest
telDest
motPasse
pieceDest
destination
dateEnvoi
dateReception
type

Travail demandé :
I) Déduire le Modèle Logique de Données de la partie pointillée du MCD ci-dessus. Cette partie comprend
les entités VACATAIRE, MATIERE et FILIERE II) Définition de données
1. Créer les tables CYCLE et FILIERE tout en respectant les contraintes d’intégrité
2. Créer la clef étrangère dans la table MATIERE
3. Créer la contrainte qui oblige la saisie d’une date de fin de vacation supérieure à la date de début de vacation
4. Créer un index sur la colonne ‘tel’ de la table VACATAIRE
5. Créer la contrainte UNIQUE sur la colonne ‘filiere’ de la table FILIERE
6. Supposons que vous avez oublié de créer la colonne ’ pieceIdent’ de la table VACATAIRE, donner une
instruction SQL qui permet de l’ajouter
7. Renommer la colonne ‘tel’ en ‘telephone‘ dans la table VACATAIRE
III) INTERROGATION DE DONNEES
1. Sélectionner les vacataires le nombre d'heures ont effectuées par chaque vacataire qui enseigne les matières
‘Algorithme’, ‘Base de donnees’, ‘Linux’, au premier trimestre de l’année 2017
2. Sélectionner les vacataires qui ont effectuée 100% du volume horaire au entre mars et mai 2017
3. Sélectionner les vacataires qui n'ont pas effectué 80% du mois de mai 2017 au 15 juillet 2017
4. Sélectionner le montant total à remettre à chaque vacataire entre au deuxième trimestre de l'année 2017
 Sachant que montant total= (somme de nombre d'heures effectuées) x taux horaire x 0.95
5. Donner le montant total des vacataires qui ont été payés entre juin et juillet 2017
IV) MANIPULATION DE DONNEES
1. Quelle est l’instruction SQL qui permet d’insérer les données suivantes dans la table cycle ?
idc cycle
1 Premier
2. Quelle est l’instruction qui permet de modifier la valeur ‘premier’ en ‘Second’ dans la table cycle ?
3. Quelle est l’instruction qui permet de supprimer l’enregistrement dont le idc=2 de la table cycle ?
4. Quelle instruction permet de rendre la table VACATAIRE accessible uniquement en lecture
27
5. Quelle instruction permet de créer la table SAUVEGARDE_VACATAIRE qui reçoit les vacataires qui ont
été payés entre avril et juillet 2017

TD 2

EXERCICE 1 : Gestion des vacations

COLONNE TYPE(TAILLE) CONTRAINTE DESCRIPTION

idvac NUMERIC(5) PRIMARY KEY Identifiant du vacataire
nom VARCHAR(50) NOT NULL Nom du vacataire
prenom VARCHAR(50) NOT NULL Prénom du vacataire
tel NUMERIC(8) NOT NULL, UNIQUE Téléphone du vacataire
pieceIdent VARCHAR(15) NOT NULL, UNIQUE Pièce d’identité du vacataire
codeMat NUMERIC(6) PRIMARY KEY Code de la matière
matiere VARCHAR(50) NOT NULL Nom de la matière
idf NUMERIC(5) PRIMARY KEY Identifiant de la filière
filiere VARCHAR(50) NOT NULL, UNIQUE Nom de la filière
idc NUMERIC(1) PRIMARY KEY Identifiant du cycle
cycle VARCHAR(25) NOT NULL Nom du cycle
nbreH NUMERIC(2) NOT NULL Nombre d’heures effectuées par le
vacataire à un jour donné
jour DATE NOT NULL ; jour <= sysdate Date à laquelle le vacataire a dispensé
le nombre d’heures qu’il a effectuées
tauxH NUMERIC(5) NOT NULL ; tauxH>=2000 Les frais de vacations par heure
annee NUMERIC(4) NOT NULL L’année à laquelle la matière est
affectée au vacataire
volumeH NUMERIC(3) NOT NULL ; volumeH >=10 Le volume horaire que le vacataire
dispensera pour la matière qu’on lui a
affectée
dateDebut DATE NOT NULL ; dateDebut >= Date de début des cours de la matière
sysdate affectée au vacataire
dateFin DATE NOT NULL ; dateFin > Date de début des cours de la matière
dateDebut affectée au vacataire

28
 payer NUMERIC(1) La valeur zéro par défaut Pour savoir si le vacataire est payé. Si
la valeur est 1 alors il est payé

Travail demandé :
V) Déduire le Modèle Logique de Données du MCD ci-dessus.
VI) Définition de données
8. Lister les bases de données présentes sur votre serveur : SELECT datname FROM pg_database;
Rappel : Les tablespaces dans PostgreSQL permettent aux administrateurs de bases de données de
définir l'emplacement dans le système de fichiers où seront stockés les fichiers représentant les objets de
la base de données.
 Cet emplacement ne doit pas être amovible ou volatile, sinon l'instance pourrait cesser de fonctionner si
le tablespace venait à manquer ou être perdu.
#create tablespace EPN2018 location [chemin du repertoire] ;
9. Vérifier les tablespaces de votre serveur
a. #SELECT spcname FROM pg_tablespace;
10. Créer un schéma EPN
11. Créer les tables de la base de données ‘scolarite’ sur le schema EPN UPDATE pg_database SET
datistemplate=true WHERE datname=' scolarite;
12. Créer les tables tout en respectant les contraintes d’intégrité
13. Créer la contrainte qui oblige la saisie d’une date de fin de vacation supérieure à la date de début de
vacation
14. Créer un index sur la colonne ‘tel’ de la table VACATAIRE
15. Créer la contrainte UNIQUE sur la colonne ‘filiere’ de la table FILIERE
16. Renommer la colonne ‘tel’ en ‘telephone‘ dans la table VACATAIRE
17. Ajouter la colonne ‘coeff’ dans la table MATIERE
18. Ajouter la colonne ‘genre dans la table VACATAIRE
19. Définir ‘H’ comme valeur par défaut dans la colonne ‘genre de la table VACATAIRE
20. Définir une contrainte qui oblige que le total des heures dispensées soit inférieur ou égal au volume
horaire attribué à un enseignant pour une matière
21. Renommer la base de données en scolariteEPN
22. Le service de la scolarité veut élargir la base de données pour gérer les notes.

Pour cela, il vous demande de :

a. Créer les tables issues du complément du MCD ci-dessus tout en respectant toutes les contraintes :
i. nigerienne comme ‘nationalite’ par défaut dans la colonne ‘nationalite’ ii. Les
dates du jour de la notation doivent être inférieures ou égales à la date du jour iii. Le
coefficient doit être compris entre 1 et 10
iv. La note doit être comprise entre 0 et 20
VII) INTERROGATION DE DONNEES
6. Charger les données pour chacune des tables. Ci-joints les fichiers

29
7. Sélectionner le nombre d'heures effectuées par chaque vacataire qui enseignent les matières
‘Algorithme’, ‘Base de donnees’, ‘Linux’, au premier trimestre de l’année 2017
8. Sélectionner les vacataires qui ont effectuée 100% du volume horaire au entre mars et mai 2017
9. Lister les filières ayant effectué 100% de leurs volumes horaires au premier trimestre de l’année 2017
10. Sélectionner les vacataires qui n'ont pas effectué 80% du mois de mai 2017 au 15 juillet 2017
11. Sélectionner le montant total à remettre à chaque vacataire entre au deuxième trimestre de l'année 2017
12. Sélectionner le nombre d’enseignants par trimestre qui ont effectué 100% du volume horaire octroyé
 Sachant que montant total= (somme de nombre d'heures effectuées) x taux horaire x 0.95
13. Donner le montant total des vacataires qui ont été payés entre juin et juillet 2017
14. quel est le nombre total des étudiantes par filière
15. donner la liste des étudiants qui ont des notes comprises entre 12 et 17,5
16. lister les étudiants qui ont des moyennes inférieures à celle de la classe
17. donner le premier de la classe
18. Donner la plus grande et la plus petite note en ‘Securite reseaux’
19. Créer une table EXCELLENT qui reçoit la moyenne générale de chaque
etudiant(idEtudiant,nom,prenom,moyenne) supérieure ou égale a 13
20. Lister les étudiants qui redoublent. C’est-à-dire ceux ayant la moyenne inférieure à 10 sachant que la
moyenne=SUM(note x coeff)/SUM(coeff).
21. Lister les étudiants qui ont la même note en informatique
22. Lister les matières où les étudiants ont une note inférieure à 5
23. Créer la colonne date_inscription, avec la contrainte date_inscription > datenaiss et la date_inscription
correspond à la date du jour c’est-à-dire current_date
24. Afficher l’ide, nom, prénom et statut (redouble ou passe) et trier par ordre croissant de statut
25. Afficher les étudiants qui ont 25 ans et qui redouble
26. En une seule instruction, insérer trois nouveaux étudiants de votre choix
27. Ecrie une requête qui fait moins cinq points à la note d’informatique aux étudiants dont les ide sont : 1, 5
et 2
28. Créer une vue nommée ‘ADMIS’ qui affiche la liste des étudiants qui passent en classe supérieure
29. Ajouter une colonne tel, le numéro du téléphone de l’étudiant. Créer un indexe sur la colonne tel
30. Afficher les mentions ‘très bien’, ‘bien’, ‘assez bien’, ‘passable’, ‘travail insuffisant’ pour les étudiants
qui ont respectivement les moyennes >=15 ; >=14 et <15 ; <14 et >=12,5 ; <12,5 et >=10 ; <10
31. Supprimer l’index sur la colonne tel
32. Modifier le type de la colonne note en NUMERIC(2,2)
33. Supprimer tous les étudiants qui n’ont pas la moyenne et dont l’âge est supérieur ou égale à 25 ans
34. Donner le pourcentage des étudiants qui passent en classe supérieure
35. Donner le pourcentage des filles qui redoublent
36. Donner le pourcentage des filles qui redoublent par an
37. Donner l’année où on a plus d’admis
38. Comparer les pourcentage d’admission sur les deux dernières années
39. lister les 10 premiers enregistrements de la table sauvegarde_etudiant
40. modifier le nom et prénom de l’étudiant dont l’ide‘ est 5 en nom=’MAHAMAN SANI’ et prenom=
‘Moussa’ et datenaiss=‘1990-02-24’
41. chercher le nombre d’étudiants qui ont la moyenne de passage par tranche d’âge : [20-24],[25-29],
[3034] et [35-40]
42. chercher les étudiants de Master 1 ‘Genie Logiciel’ qui ont des notes en Base de données, supérieures à
chaque note de l’étudiant de Master 1 ‘Réseaux’ en 2017
43. chercher l’étudiant de Master 1 ‘Réseaux’ qui a une note superieure à celle de tous les étudiants de 1
‘Genie Logiciel’ dans la matière ‘Administration de base de données’ en 2017

30
 44. créer la fonction AFFEXCELLENT qui donne la liste des étudiants excellents d’une filière. Cette
fonction prend en paramètre le nom de la filière
45. écrire la requête de calcul de moyenne dans un fichier et exécuter cette requête depuis le fichier.

VIII) MANIPULATION DE DONNEES

6. Quelle est l’instruction qui permet de modifier la valeur ‘premier’ en ‘Second’ dans la table cycle ?
7. Quelle est l’instruction qui permet de supprimer l’enregistrement dont le idc=2 de la table cycle ?
8. Quelle instruction permet de rendre la table VACATAIRE accessible uniquement en lecture
9. Quelle instruction permet de créer la table SAUVEGARDE_VACATAIRE qui reçoit les vacataires qui
ont été payés entre avril et juillet 2017
IX) CONTROLE
1. Créer un utilisateur ‘ali’ ayant les droits de créer une base de données, des utilisateurs avec une durée
de vie illimitée
2. Créer un super utilisateur ‘ben’
3. Créer un rôle avec les options : SUPERUSER, CREATEDB, CREATEROLE, CONNECTION
LIMIT 2, ENCRYPTED PASSWORD, LOGIN
4. Créer un utilisateur ‘sani’ n’ayant pas les droits de créer une base de données, pas de droits de créer
des utilisateurs et le compte expirera le ‘2018-03-30 00:00’
5. attribuer les droits d’insertion, modification à l’utilisateur ‘ali’ sur la table NOTER
6. attribuer les droits de sélection de note à tout le monde
7. créer un groupe ‘directeur’ avec les droits suivants :

NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOCREATEUSER
NOLOGIN
INHERIT
VALID UNTIL ‘2018-06-15 00:00’
8. créer un utilisateur alassane que vous intégrez dans le groupe ‘directeur’
9. attribuer le role sani a ali
10. confirmer l’attribution des privileges => SET role sani ;
X) ADMINISTRATION DU SERVEUR DE BASES DE DONNEES
1. Le fichier [Link] permet de configurer par exemple l’allocation de la mémoire, le
stockage par défaut pour une base de données nouvellement créée
2. Le fichier pg_hba.conf contrôle la sécurité de la base de données, il indique quel utilisateur peut
accéder a la base de données, quelle adresse IP peut se connecter au serveur de base de données
3. pg_ident.conf permet de mapper les comptes des utilisateurs système et ceux de la base de
données

XI) Apres avoir vérifié là où se trouvent ces fichiers, étudier les paramètres de chacun de ces fichiers
a. SELECT name, setting
FROM pg_settings
WHERE category = 'File Locations';
b. Etudier le contenu
2. Vérification des paramètres via une requête :

31
 SELECT name, context , unit , setting , boot_val , reset_val
FROM pg_settings WHERE
name
in('listen_addresses','max_connections','shared_buffers','effective_cache_size',
'work_mem', 'maintenance_work_mem')
ORDER BY context,name;

4. Si context = ‘postmaster’ alors cela nécessitera le redémarrage du serveur PostureSQL et si

context=’user’ a alors cela nécessitera un reload uniquement
5. unit : indique l’unique de mesure de la mémoire
6. boot_val est le paramètre par défaut
7. reset_val est la nouvelle valeur si on redémarre ou on fait un reload
3. donner la description detaillee de la vue système pg_tables
4. vérifier les détails de la table ‘ETUDIANT’ dans la vue système pg_tables
5. recharger la configuration > SELECT pg_reload_conf();
6. voir la liste des utilisateurs connectes au serveur ainsi que les bases de données qu’ils utilisent :
a. SELECT * FROM pg_stat_activity;
7. Annuler toutes les requêtes actives sur la onnexion :
b. SELECT pg_cancel_backend(procid); 8. Terminer toutes
les connexions:
c. SELECT pg_terminate_backend(procid)

XII) Gestion des privilèges

1. Attribuer les droits de modification à l’utilisateur sani
XIII) Backup
1. Faire le backup de la base de donnees scolarite
2. Archiver la base de données tout etant compressee en .tar pg_dump -U utilisateur -W -F t
basedonnees > le chemin
3. Faire un backup depuis un serveur distant
=> pg_dump -u postgres -h [Link] -F c –f [Link] donnees
4. Faire le dump de toutes les bases de donnees de votre serveur => pg_dumpall –g
5. Pour sauvegarder l’integralite des base de donnees du cluster y compris les roles, tablespaces,
databases,schemas, tables, indexes, triggers, functions, constraints, views, ownership et
privileges,
5.1 Tous les schema : pg_dumpall -s > c:\pgdump\[Link]

XIV) Fonctions
Pour avoir l’aide sur la syntaxe de création de fonction => \h create function

Pour avoir de l’aide sur la syntaxe complète de création d’une fonction : saisir \h create function
1. Créons la tale eleve :
Create table eleve(id serial,nom varchar(50) not null,prenom varchar(50) not null) ;
2. Créer la function inserer
create or replace function inserer(nom varchar,prenom varchar)
returns integer as
$$
INSERT INTO eleve(nom,prenom)
VALUES($1,$2) returning id;
$$
language 'sql' volatile;
32
3. Créons la function maj
create or replace function maj (id integer,nom varchar,prenom varchar)
returns void as
$$
UPDATE eleve SET nom=$2,prenom=$3 WHERE id=$1
$$
language 'sql' volatile;
create or replace function maj1 (id integer,nom varchar,prenom varchar)
returns void as
$$
UPDATE eleve SET nom=nom,prenom=prenom WHERE
$$ id=id
language 'sql' volatile;
Recherche
create or replace function afficher(id integer)
returns table(id integer,nom varchar,prenom varchar)as $$
SELECT * FROM eleve WHERE id=$1;
$$
language 'sql' volatile;

create or replace function afficher2(id integer)

returns SETOF eleve as
$$
SELECT * FROM eleve WHERE id=$1; $$
Language 'sql' stable;

PLPGSQL
create or replace function afficherTOUT(id integer)
returns table(id int,nom varchar,prenom varchar) as
$$
begin
return query
SELECT * FROM eleve WHERE id=$1;
end;
$$
language 'plpgsql' stable;
SELECT * FROM afficherTOUT();

TRIGGER
create or replace function fonctTrigger1()
returns trigger as
$$
begin
[Link]:=upper([Link]);
return NEW;
end;
$$
language 'plpgsql' volatile;
create trigger verificer
before INSERT or UPDATE OF nom,prenom on eleve
FOR EACH ROW
execute procedure fonctTrigger1();

33
 Remarque: pour supprimer une function, on ecrit ses parametres
DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

Attribuer le droit d’exécution de la fonction afficherTOUT()

Exemple : fonction de calcul de moyenne

create or replace function moyenne() returns
table(idEtudiant int,moy real)as
$$ begin return query select
[Link](coeff*note)/sum(coeff) as moy
FROM etudiant e,noter n,matiere m
WHERE [Link]=[Link] AND [Link]=[Link]
GROUP by [Link];
end;
$$
language 'plpgsql' stable;

TD3: Gestion des ventes de matériels informatiques

Travail demandé :
i. DEFINITION DE DONNEES
1. Traduire le Diagramme de Classe ci-dessus en Modèle Relationnel Quelques renseignements :
Champ Description Type [taille] Contrainte
34
numClient Numéro du client SERIAL PRIMARY KEY
nomC Nom du client VARCHAR(50) NOT NULL
tel Téléphone du client INTEGER NOT NULL
email L’adresse mail du client VARCHAR(60)
fax Faxe du client INTEGER
ville Ville du client VARCHAR(25)
pays Pays du client VARCHAR(25)
numCom Numéro de la commande BIGSERIAL PRIMARY KEY
etat Etat de la commande {‘En VARCHAR(12)
attente’|’Traitee’}
qteCom Quantité commandée INTEGER NOT NULL
prixU Prix unitaire MONEY NOT NULL
marque Marque du matériel VARCHAR(30) NOT NULL
numF Numero du fournisseur SMALLSERIAL PRIMARY KEY
nomF Nom du fournisseur VARCHAR(50) NOT NULL
telF Téléphone du fournisseur INTEGER NOT NULL
poids Le poids du matériel INTEGER NOT NULL
Pour la classe ORDINATEUR
ecran Pouce de l’écran (exple : 13,3 NUMERIC(2,2) NOT NULL
")
processeur Le type du processeur VARCHAR(15)
frequenceProc Fréquence du microprocesseur INTEGER
sysOS Système d’exploitation VARCHAR(25) NOT NULL
RAM La mémoire RAM INTEGER NOT NULL
espaceDisk Espace du disque dur INTEGER NOT NULL
Pour la classe IMPRIMANTE
nbrPageMin Nombre de page à imprimer par INTEGER NOT NULL
minute
capBacPapier Nombre de papiers qu’on peut INTEGER NOT NULL
introduire dans le bac papiers
JetEncre Imprimante à jet d’encre. ‘O’ CHAR(1) NOT NULL
pour Oui et ‘N’ pour Non
2. Créer le type simple ‘statut’ qui énumère les états de la commande : ‘en attente’, ‘traitee’, ‘annulee’
d. create type etat as enum(‘en attente’, ‘traitee’, ‘annulee’) ;
3. créer un type ‘localiser’ constitué de boitePostale, telephoneBureau, email
e. create type localiser as (boitePostale INTEGER, telBureau INTEGER, email
VARCHAR(50)) ;
4. ajouter la colonne adresse de type ‘localiser’ dans la table FOURNISSEUR
f. alter table fournisseur add adresse localiser ;
5. Créer l’utilisateur ‘ben’ avec mot de passe crypté
6. Créer le tablespace ‘informatique’ appartenant à l’utilisateur ‘ben’
7. Créer la base de données ‘stock’
OWNER ben
TABLESPACE informatique
8. Créer le schéma ‘business’
9. Définir l’utilisateur ‘postgres’ comme propriétaire du schéma ‘business’
10. Créer les tables issues du Modèle Relationnel sur le schéma ‘business’ tout en respectant les
contraintes d’intégrité.
Remarque 1: les tables ORDINATEUR, IMPRIMANTE héritent de la table ‘MATERIEL’
35
 Remarque 2 : PostgreSQL nous offre la possibilte de créer simplement les cles etrangeres Exemple
: create table mere(idp int primary key);
create table fille(idf int primary key, id int references mere on delete cascade);
Renommer la colonne ‘telF’ en ‘telephone’
11. Définir le Niger comme pays par défaut dans la table ‘CLIENT’
12. Créer l’index idxTel sur le champ tel de la table CLIENT
13. La valeur du poids du matériel est comprise entre 5 et 25
14. Renommer l’index idxTel du champ tel de la table CLIENT en ‘indexTel’
15. Renommer le schéma ‘business’ en ‘haraka’
16. Ajouter la colonne NIF dans la table FOURNISSEUR de type integer
17. Vérifier les paramètres de la base de données
g. show all ;
18. définir l’unicité des valeurs des champs suivants :
Champ Table
tel CLIENT
email
fax
telF FOURNISSEUR
19. fixer la longueur du numéro de téléphone à 8 chiffres

i. MANIPULATION DE DONNEES
1. Charger le contenu des fichiers dans les tables respectives.
ii. INTERROGATION DE DONNEES
1. Ecrire une requête qui catégorise chaque client en fonction du chiffre d’affaires qu’il a généré au
deuxième trimestre de l’année 2017:
Catégorie Tranche
DIAMANT >= 4500000
OR < 4500000 et >=2800000
ARGENT <2800000 et >=1000000
BRONZE <1000000 et >=300000
AUTRE <300000

2. Ecrire dans le fichier ‘[Link]’, la requête qui donne la liste des clients qui ont génère 500000 FCFA
de chiffre d’affaires au deuxième trimestre de l’année 2017
3. Ecrire l’instruction qui permet d’exécuter la requête contenu dans le fichier ‘[Link]’
4. Lister les commandes (numCom,montantHT) qui sont en attente aujourd’hui sachant que
montantHT=SUM(qteCom x prixU - qteCom x prixU x 0.19)
5. Donner le chiffre d’affaires Toute Taxe comprise, généré par les imprimantes au dernier trimestre
de l’année 2017
6. Donner le chiffre d’affaires Toute Taxe comprise, généré par les ordinateurs au premier trimestre de
l’année 2017
7. Créer une fonction qui permet d’insérer un fournisseur
8. Créer une fonction qui donne le chiffre d’affaires Toute Taxe comprise, généré par les imprimantes
pendant une période donnée
9. Créer une fonction qui permet de calculer la quantité restant des imprimantes
10. Ecrire une instruction SQL qui permet de créer une table commandeClone qui reçoit toutes les
données de la table COMMADE
11. Analyser toutes tables avec la commande VACUUM ANALYSE`

36
 iii. CONTROLE DE DONNES iv. SECURITE
 Crypter le mot de passe des groupes et utilisateurs à créer
1. Créer le groupe ‘vente’
2. Créer le groupe ‘marketing’
3. Créer le groupe ‘finance’
4. Créer le groupe ‘it’
5. Créer l’utilisateur ‘idi’
6. Créer l’utilisateur ‘iro’
7. Insérer l’utilisateur ‘idi’ dans le groupe vente
8. Renommer le groupe ‘it’ à ‘adjointDBA’ avec les options suivantes :
NOCREATEDB
CREATEUSER
VALID UNTIL ‘2018-03-30’
9. Ajouter ‘iro’ dans le groupe ‘adjointDBA’
10. Supprimer l’utilisateur ‘tanko’ du groupe ‘marketing’
11. Renommer ‘iro’ à ‘ibrah’
12. Donner les privilèges sur les objets aux utilisateurs suivants :
Utilisateur Objets Privilèges
idi COMMANDE, SELECT, INSERT
FOURNISSEUR,
MATERIEL
tanko COMMANDE, SELECT
MATERIEL
adjointDBA COMMANDE, SELECT,UPDATE,DELETE,CREATE
MATERIEL
v. TRANSACTION
1. Ecrire une transaction pour la réduction du montant HT des achats effectués par chaque client au
dernier trimestre de l’année 2017 a condition qu’il génère plus de 500000FCFA.
2. Vérifier si la transaction a réussi. Si oui, il faut la valider sinon il faut l’annuler
3. Ecrire la transaction avec START TRANSACTION ;
vi. SAUVEGARDE
1. Faire la copie du contenu de la table COMMANDE dans un fichier
2. Sauvegarder toute la base de données dans un fichier
3. Utiliser pg_dump pour exporter :
a. le schéma et la base de données
h. pg_dump -U utilsateur base_donnees > [chemin]/[Link]
b. uniquement le schema
i. pg_dump -U utilsateur -s base_donnees > [chemin]/[Link]
c. uniquement les données
j. pg_dump -U utilsateur -a base_donnees > [chemin]/[Link]
4. créer un RULE qui permet de sauvegarder dans table sauveCOMMANDE tous les
enregistrements supprimés de la table COMMANDE
k. creation de la table sauveCOMMANDE

37