________ _______ ________ ________ __ ________
| | | \ | | | | | | | |
| __ | | __ | | __ | | _____| | | | _____|
| | | | | |__| _| | |__| | | | | | | |___
| |__| | | __ \ | __ | | |_____ | |_____ | ___|_
| | | | | | | | | | | | | | | |
|________| |__| |__| |__| |__| |________| |________| |________|
COUR DE BASE DE DONNEES AVEC ORACLE
bd.txt 12/09/2005
maregeneration
Definition:
Le SQL est un langage de requêtes descriptif, standard pour toutes les bases de données qui suivent le modèle relationnel. Ce langage permet toutes les opérations sur les données dans tous les cas d'utilisation de la base. Avec Oracle, on peut associer au SQL un langage procédural, le PL/SQL, qui ajoute de nombreuses possibilités dans la manipulation des données.
CHAPITRE 1 - Modèle relationnel
1.1. La gestion des données
Il existe 4 types de fichiers de données:
- fichiers permanents (ex: fiches de clients, catalogues d'articles)
- fichiers de mouvement (ex: commandes clients)
- fichiers de travail créés par des applications (informations)
- fichiers historiques contenant les données archivées.
L'organisation des données sont:
- le séquentiel (caractères délimiteurs ou tailles fixes, bit à bit et lent)
- le séquentiel indexé (fichier - table d'index triée contenant les adresses)
- les bases de données navigationnelles (sécurisé, très gourmant)
- les bases de données relationnelles
1.2. Le modèle relationnel
Une base de données relationnelle est une collection de données mises en relation dans des tables logiques; une table étant un ensemble de lignes et de colonnes. Le SGBDR (Système de Gestion de Bases de Données Relationnelles, RDBMS en anglais) gère indépendamment le niveau logique, et le niveau physique, ce qui procure une simplicité dans la gestion des données. Le concept de la SGBDR est un langage assertionnel basé sur des ensembles.
a) Domaine: C'est un ensemble de valeurs caractérisé par un nom. Le nombre d'éléments d'un domaine est le Cardinal.
b) Produit cartésien: P = D1 x D2 x ... x Dn (D Domaine)
Codes Taux Codes Taux Codes = {1,2,3}
----- x ----- = ---------- Taux = {0,8}
1 0 1 0
2 8 1 8 P = Codes x Taux
3 2 0 = {(1,0),(1,8),(2,0),(2,8),(3,0),(3,8)}
2 8
3 0
3 8
c) Relation: C'est un sous-ensemble du produit cartésien caractérisé par un nom. Le nom d'une colonne de la relation est un attribut, et le degré est le nombre d'attributs.
TVA CODE VALEUR
ex: pour associer un seul taux par code: ------------------
1 0
2 8
Principales règles de la SGBDR
- Cohérence: toute valeur d'un attribut appartient à son domaine de définition.
- Unicité: Tous les éléments d'une relation doivent être distincts.
- Identifiant: permet de caractériser chaque élément.
- Clé primaire: identifiant minimum d'une relation.
- Clés secondaires: autres identifiants de la relation.
- Intégrité référentielle: un attribut apparait comme clé primaire ailleur.
- Clé étrangère: un attribut qui apparait comme clé primaire ailleur.
- Valeur nulle: valeur représentant une information inconnue dans une colonne.
- Contrainte d'entité Toute valeur participant à une clé primaire est non NULL.
1.3. L'algèbre relationnelle
a) Union: Rx = R1 U R2; CLIENTS = CLIOUEST U CLICENTRE
CLIOUEST______________ CLICENTRE_____________ CLIENTS_______________
NOCLI NOM ADRESSE NOCLI NOM ADRESSE NOCLI NOM ADRESSE
_____________________ _____________________ _____________________
15 Dupont Nantes 20 Labiche Paris 15 Dupont Nantes
35 Dubois Nantes 138 Dubois Tours 35 Dubois Nantes
152 Laroche Le Mans 152 Laroche Le Mans 152 Laroche Le Mans
20 Labiche Paris
concatenation 138 Dubois Tours
b) Intersection: Rx = R1 (U à l'enver) R2; CLICOM = CLIOUEST U CLICENTRE
_____________________________
CLICOM NOCLI NOM ADRESSE
_____________________________ lignes communes
152 Laroche Le Mans
c) Différence: Rx = R1 - R2; CLIOS = CLIO - CLIC
____________________________
CLIOS NOCLI NOM ADRESSE
____________________________ on enlève les doublons
15 Dupont Nantes
35 Dubois Nantes
d) Restriction: Rx = phi(condition) R1; CLI44= phi (ADRESSE="Nantes")CLIOUEST
____________________________
CLI44 NOCLI NOM ADRESSE [NON] [(] attribut opérateur valeur [)]
____________________________ [{ET/OU}condition]
15 Dupont Nantes
35 Dubois Nantes opérateurs: =, <>, >, <, >=, <=
e) projection: Rx = Pi R(A1, A2,.. An); CLICDE1 = Pi COMMANDES(NOCLI)
_____________________________ ______________ _____________________
COMMANDES NOCDE NOCLI ETATCDE CLICDE1 NOCLI CLICDE2 NOCLI ETATCDE
_____________________________ ______________ _____________________
1210 15 SO 15 15 SO
1230 35 SO 35 35 SO
1301 15 EC 20 15 EC
1280 20 LI 20 LI
1150 15 SO CLICDE2= Pi COMMANDES( 35 EC
1250 35 EC NOCLI, ETATCDE)
f) produit cartésien: Rx = S1 x S2; INVENTAIRE = DEPOT x ART2
__________________ _____________________________________
DEPOTS CODE NOM INVENTAIRE CODE NOM REFART DESIG
__________________ _____________________________________
NW Nantes NW Nantes AB Tapis
SE Nice NW Nantes CD Hifi
P1 Paris I SE Nice AB Tapis
_________________ SE Nice CD Hifi
ART2 REFART DESIG P1 Paris I AB Tapis
_________________ P1 Paris I CD Hifi
AB Tapis
CD Hifi
g) jointures: Rx = S1 JOIN (condition) S2
_________________ _________________________________
CDEEC NOCDE NOCLI LIGNESCDE NOCDE2 NOLIG REFART QTE
_________________ _________________________________
1301 15 1210 1 AB10 3
1250 35 1210 2 CD50 4
1230 1 AB10 1
LIGCDEEC = CDEEC JOIN (CDEEC.NOCDE = 1301 1 AB03 3
LIGNESCDE.NOCDE2)LIGNESCDE 1301 2 AB22 1
____________________________________________ 1250 1 CD50 5
LIGCDEEC NOCDE NOCLI NOCDE2 NOLIG REFART QTE 1280 1 AB10 15
____________________________________________ 1150 1 AB03 7
1301 15 1301 1 AB03 3 1150 2 AB22 5
1301 15 1301 2 AB22 1 1150 3 AA00 1
1250 35 1250 1 CD50 5
- Theta-jointure: la condition est une comparaison entre deux attributs.
- Equi-jointure: la condition porte sur l'égalité entre deux attributs.
- Jointure naturelle: equi-jointure entre les attributs portant le même nom.
h) projection avec calcul: Rx = Pi R(A1, A2,.. N1='')
____________________________________ _____________________________________
LIGCDE NOCDE NOLIG REFART QTE PRIXHT LIGCDEVALO NOCDE NOLIG REFART VALOLIG
____________________________________ _____________________________________
1301 1 AB03 3 150.00 1301 1 AB03 450.00
1301 2 AB22 1 1250.10 1301 2 AB22 1250.10
1250 1 CD50 5 735.40 1250 1 CD50 3677.00
LIGCDEVALO = Pi LIGCDE(NOCDE,NOLIG,REFART,VALOLIG=QTE*PRIXHT)
i) calcul d'agrégats
COUNT(*) nb lignes NBCLI = Pi LIGCDEVALO(N=COUNT(*))
COUNT(attribut) nb valeurs CDEVALO = Pi LIGCDEVALO(NOCDE,TOT=SUM(VALOLIG))
SUM(attribut) somme ________ _____________________
AVG(attribut) moyenne NBCLI N CDEVALO NOCDE TOT
MAX(attribut) maximum ________ _____________________
MIN(attribut) minimum 6 1301 1700.10
1250 3677.00
1.4. résolution d'un problème
Il faut écrire le résultat à atteindre, trouver les attributs et les relations necessaires. trouver les calculs élémentaire, les calculs d'agregat. Creer une vue en classifiant les attributs.
CLIENTS(NOCLI,NOMCLI,ADR) COMMANDES(NOCDE,NOCLI,DATE,ETATCDE) ARTICLES(REFART,DES,PRIXHT) LIGNESCDE(NOCDE,NOLIG,REFART,QTE)
RESULTAT(NOCDE,DATE,NOMCLI,ADR,REFART,DES,PRIHT,QTE,MTHT,TOTHT)
- T1 = phi(NOCDE=1301) COMMANDES T1(NOCDE,NOCLI,DATE,ETATCDE)
- T2 = phi(NOCDE=1301) LIGNESCDE T2(NOCDE,NOLIG,REFART,QTE)
- T3 = T1 JOIN (T1.NOCDE=T2.NOCDE) T2 T3(NOCDE,NOCLI,DATE,ETATCDE,NOLIG,REFART,QTE)
- T4 = T3 JOIN (T3.NOCLI=CLIENTS.NOCLI) CLIENTS T4(NOCDE,NOCLI,DATE,ETATCDE,NOLIG,REFART,QTE,NOMCLI,ADR)
- T5 = T4 JOIN (T4.REFART=ARTICLES.REFART) ARTICLES T5(NOCDE,NOCLI,DATE,ETATCDE,NOLIG,REFART,QTE,NOMCLI,ADR,DES,PRIHT)
- T6 = Pi T5(NOCDE,DATE,NOMCLI,ADR,REFART,DES,PRIHT,QTE,MTHT=PRIHT*QTECDE) T6(NOCDE,DATE,NOMCLI,ADR,REFART,DES,PRIHT,QTE,MTHT)
- T7 = Pi T6(NOCDE,TOTHT=SUM(MTHT)) T7(NOCDE,TOTHT)
- RESULTAT = T6 JOIN (T7.NOCDE=T6.NOCDE) T7
CHAPITRE 2 - SQL
1.1. Généralités
Le SQL (Structured Query Language) permet la manipulation des bases de données relationnelles. Il est aux normes ANSI, ISO, et FIPS. La plupart des éditeurs de SGBDR, intègrent le SQL. Les données, requêtes et applications sont donc assez facilement portables d'une base à l'autre. Le SQL peut être utilisé à tous les niveaux (administration, développement)...
1.2. Description des objets
Une table est un ensemble de lignes et de colonnes.
Types
- CHAR(n) chaine de longueur fixe n (n <= 2000 en octets)
- VARCHAR2(n) chaine de longueur variable dont la taille max est n (<4000)
- NCHAR(n) CHAR(n) avec jeux de caractères national
- NVARCHAR2(n) VARCHAR2(n) avec jeux de caractères national
- NUMBER(p,s) numéro de prècision p dont s décimales
- DATE date au format '01/01/95'
- BLOB données binaires (max 4Go)
- CLOB chaine de longueur variable (max 4Go)
- NCLOB CLOB avec jeux de caractères national
- BFILE données binaires stockées dans des fichiers ectérieurs
Contraintes de colonne:
- NULL/NOT NULL autorise ou interdit la valeur NULL
- PRIMARY KEY désigne l'attribut comme clé primaire
- UNIQUE désigne l'attribut comme clé secondaire
- REFERENCES se réfere à une colonne d'une autre table
- CHECK (cond°) vérifie la valeur rentrée dans une colonne
CREATE TABLE client ( CREATE TABLE OR (
noclient NUMBER(5) PRIMARY KEY, noOR NUMBER(7),
nomclient VARCHAR2(30) NOT NULL, dateOR DATE,
adresse VARCHAR2(40), panne VARCHAR2(25),
sexe DEFAULT 'M' NOT NULL, noclient NUMBER(5) REFERENCES
age NUMBER(2) client(noclient) ON DELETE CASCADE,
); RefApp NUMBER(7) );
Contraintes de table:
- PRIMARY KEY() désigne la colonne spécifiée comme clé primaire
- UNIQUE() désigne la colonne spécifiée comme clé secondaire
- CHECK (cond°) vérifie une condition entre plusieurs attributs de la ligne
- FOREIGN KEY() désigne la colonne spécifiée comme clé etrangère cet attribut est une PRIMARY KEY d'une autre table
CREATE TABLE necessite ( CREATE TABLE OR (
noOR NUMBER(10), noOR NUMBER(7) PRIMARY KEY,
refPiece NUMBER(15), noclient NUMBER(5),
qte NUMBER(10,2), FOREIGN KEY (noclient) REFERENCES
PRIMARY KEY (noOR, refPiece) client(noclient)
); );
Les contraintes peuvent être nommés afin d'être plus facilement manipulées ultérieurement. La convention de nommage est Table_Colonne_TypeDeContrainte
mnémonique TypeDeContrainte :
- PK Clé primaire CREATE TABLE OR (
- UQ Unique noOR NUMBER(7) OR_PK PRIMARY KEY,
- NN Not Null noclient NUMBER(5)
- CK Check );
- RF Références
- FK Clé étrangère
1.3. Opérations
Pour effectuer des opération SQL, il faut se placer dans l'interpreteur de commandes SQL.
SQL> CONNECT login/login@domaine STRUCTURE d'un script de création de table:
SQL> disconnect -- création des tables sans aucune contrainte
SQL> exit -- création des contraintes de clé primaire
SQL> select * from client -- création des contraintes de clé étrangère
SQL> / -- réexecute le buffer -- création des contraintes de validation
SQL> start script1.sql
SQL> @script1.sql
SQL> describe user_tables
Operations sur les tables et les index
TRUNCATE TABLE eleves; -- efface toutes les lignes
DROP TABLE eleves [CASCADE CONSTRAINTS];
RENAME client TO clients;
CREATE INDEX idx_client ON client(nomclient, prenom);
DROP INDEX idx_client; -- afficher la table user_indexes
Operations sur les contraintes
ALTER TABLE client DISABLE client_codcateg_FK;
ALTER TABLE client ENABLE NOVALIDATE client_codcateg_FK; -- pour les suivantes
ALTER TABLE client ENABLE VALIDATE client_codcateg_FK; -- pour toutes
ALTER TABLE client DROP client_codcateg_FK;
ALTER TABLE client DROP CONSTRAINT client_codcateg_FK; -- UNIQUE(), PRIMARY KEY
- pour visualiser les contraintes:
DESC user_constraints SELECT TABLE name FROM user_tables;
SELECT constraint_name, constraint_type, search_condition FROM user_conditraint;
SELECT constraint_name, column_name FROM user_cons_columns
Operations sur les colonnes
ALTER TABLE client ADD(codpostal NUMBER(5) CONSTRAINT client_codpostal_CK
CHECK (codpostal > 1000 AND codpostal < 99999), ville VARCHAR2(30));
ALTER TABLE client MODIFY (age NUMBER(3));
ALTER TABLE client ADD CONSTRAINT client_codcateg_FK FOREIGN KEY(codcateg)
REFERENCES categories(codcateg);
ALTER TABLE client DROP COLUMN codpostal;
ALTER TABLE client DROP (codpostal, age);
ALTER TABLE client SET UNUSED (codpostal, age); -- plus rapide
ALTER TABLE client DROP UNUSED COLUMNS CHECKPOINT 200; -- sans saturations
Operations sur les lignes
INSERT INTO client (noclient, nom) VALUES (104, 'Duchamp');
INSERT INTO client VALUES (104, 'Duchamp', 'Bordeaux','33000');
DELETE FROM client WHERE age < 18;
UPDATE client SET age=0 WHERE age > 100;
UPDATE client SET nom=UPPER(nom),adresse=LOWER(adresse);
1.4. Manipulation des données
Expressions
'chaine'; '15-JAN-94'; -123.459; -1.26e+6; + - / * (); 'Monsieur' || nom; " != <> < <= > >=; IN (a,b,c); BETWEEN a and b; LIKE 'j_ur%'; NOT AND OR;
Fonctions mathématiques
ABS(n) valeur absolue de n CEIL(n) premier entier supérieur ou égal à n COS(n) cosinus de n COSH(n) cosinus hyperbolique de n EXP(n) e puissance n (e=2.71828183...) FLOOR(n) premier entier inférieur ou égal à n LN(n) logarithme népérien de n LOG(n1,n2) logarithme de n2 base n1 MOD(n1,n2) reste de n1/n2 POWER(n1,n2) n1 exposant n2 ROUND(n1[,n2]) n1 arrondi à n2 positions décimales SIGN(n) -1 si n < 0; 0 si n = 0; +1 si n > 0 SIN(n) sinus de n SINH(n) sinus hyperbolique de n SQRT(n) racine carrée de n TAN(n) tangente de n TANH(n) tangente hyperbolique de n TO_CHAR(n) conversion numérique en chaîne TRUNC(n1[,n2]) n1 tronqué à n2 positions décimales
Fonctions chaîne de caractères
ASCII(s) code ASCII du premier caractère de s
CHR(n) caractère ASCII du code n
CONCAT(s1,s2) concaténation de s1 et s2
INITCAP(s) premières lettres des mots en majuscule
INSTR(s1,s2[,n1,n2]) position de la n2ieme occurrence s2 dans s1 à partir de n1
INSTRB INSTR avec n1 et n2 en octets
LENGTH(s) nombre de caractères de s
LENGTH(s) nombre d'octets de s
LOWER(s) renvoie s en minuscules
LPAD(s1,n[,s2]) renvoie s1 sur une longueur n complété à gauche par s2 ' '
LTRIM(s1[,s2]) renvoie s1 avec suppression à gauche de tous les s2 ' '
NLS_INITCAP(s,'') INITCAP avec code pays
NLS_LOWER(s,'') LOWER avec code pays
NLSSORT(s) code du caractère s dans la table NLS
NLS_UPPER(s,'') UPPER avec code pays
REPLACE(s1,s2,s3) remplace dans s1 tous les s2 par s3 (s3 nul ou pas)
RPAD(s1,n[,s2]) renvoie s1 sur une longueur n complété à droite avec s2
RTRIM(s1[,s2]) renvoie s1 avec suppression à droite de tous les s2 ' '
SOUNDEX(s) représentation phonétique de s
SUBSTR(s,n1[,n2]) renvoie la chaîne de n2 caractères de s à partir de n1
SUBSTRB(s,n1[,n2]) SUBSTR avec n1 et n2 en octets
TO_NUMBER(s) conversion d'une chaîne en numérique
TRANSLATE(s1,s2,s3) remplace dans s1 tous les s2 par s3 (s3 non nul)
TRIM(s1 FROM s2) tous s1 en tête et en fin de chaîne sont supprimé de s2
en spécifiant LEADING,TRAILING,BOTH: TRIM(BOTH s1 FROM s2)
UPPER(s) renvoie s en majuscules
Fonctions dates
ADD_MONTHS(d,n) date d plus n mois LAST_DAY(d) date du dernier jour du mois de d MONTHS_BETWEEN(d1,d2) différence entre les deux dates NEW_TIME(d,z1,z2) la date d de la zone z1 est convertie en zone z2 NEXT_DAY(d,j) date du prochain jour j à partir de d ROUND(d,format) arrondit la date au début suivant le format SYSDATE date système TRUNC(d,format) tronque la date au début suivant le format TO_CHAR(d,format) conversion d'une date en chaîne TO_DATE(s,format) conversion d'une chaîne en date D DD DDD jour de la semaine, dans le mois, dans l'année DAY DY nom du jour sur 9 caractères, nom abrégé HH HH12 HH24 heure du jour de 1 à 12, de 0 à 23 MI SS SSSS minute, secondes, secondes depuis minuit WW IW W numéro de la semaine dans l'année, iso, dans le mois MM MON MONTH numéro du mois, nom du mois abrégé, nom du mois sur 9c RM Q numéro du mois en chiffre romain, numéro du trimestre YYYY YYY YY Y année sur 4, 3, 2, ou 1 chiffre(s)
Fonctions scalaires diverses
DECODE(col,v1,r1,..) si colonne à v1, la forme sera r1, si col2 à v2, r2
DUMP(exp[,format]) format: 8 octal, 10 déc, 16 hexa, 17 caractère
GREATEST(exp[,..]) renvoie la plus grande expression
LEAST(exp[,..]) renvoie la dernière des expressions
NVL(exp1,exp2) si exp1 est NULL renvoie exp2
NVL2(exp1,e2,e3) si exp1 est NULL renvoie e2 sinon e3
UID numéro identificateur de l'utilisateur
USER nom de l'utilisateur
USERENV(opt) renvoie des caractéristiques de l'environnement
ENTRY id; SESSION id; TERMINAL; LANGUAGE
VSIZE(exp) nombre d'octets stockés pour exp
1.5. Traduction de l'algèbre relationnelle
Restrictions p65
SELECT * FROM client WHERE ville='Bordeaux';
SELECT * FROM client WHERE nomcli LIKE 'B%';
SELECT * FROM notes WHERE note BETWEEN 10 AND 15;
SELECT * FROM notes WHERE matiere IN('Francais','Anglais');
SELECT * FROM notes WHERE matiere='Francais' OR matiere='Anglais');
Projection (Pi)
SELECT TABLE_NAME FROM user_table
Calcul elementaire des projections
SELECT refart, design, prix * qtestk [AS] qteval FROM articles;
AS: renome la colonne []: facultatif
SELECT UPPER(nom),LOWER(prenom) Prenommin FROM eleves; p52 liste math SQL
SELECT ville FROM clients; SELECT DISTINCT ville FROM client
Calcul d'agregat
AVG(col) moyenne des valeurs de colonne
COUNT(col|*) nb de lignes regroupées ou la colonne est non NULL
MAX(col) valeur maximum de la colonne pour chaque regroupement
MIN(col) valeur minimum de la colonne pour chaque regroupement
STDDEV(col) écart TYPE des valeurs de colonne
SUM(col) somme des valeurs de colonne
VARIANCE(col) variance des valeurs de colonne
........
CHAPITRE 3 - Architechture Client Serveur C/S
A quel moment faut-il passer d’un SGBD micro de type serveur de fichier comme dBase, Paradox, FoxPro ou Access, à un poids lourd du client/serveur comme Oracle, Sybase, SQL Server, Informix, DB2 ou Firebird ? L’ensemble des techniques du C/S est dédié à minimiser le trafic des données sur un réseau et assurer une plus grande intégrité lors du traitement des données… Alors que dans un système à base de fichiers, c’est chaque poste de travail qui traite localement les données, dans un SGBD C/S, tous les traitements sont, en principe, effectués sur le serveur par le biais de requêtes.
3.1. Modes de verrouillage
Verrouillage pessimiste:
Lorsque qu’un utilisateur désire modifier une donnée, le SGBD tente de poser un verrou en écriture. Soit l’opération est effective (et donc le verrou est posé), soit l’opération échoue parce qu’un autre utilisateur a déjà posé un verrou. Si l’opération est positive, pendant toute la durée de la modification, le verrou est actif pour cet utilisateur jusqu’à ce que l’utilisateur valide ses modifications ou les annule. Dans ces deux cas le verrou est libéré.
Verrouillage optimiste
Lorsque qu’un utilisateur désire modifier une donnée, le SGBD relève le numéro de version de l’enregistrement et ne pose aucun verrou. Plusieurs utilisateurs peuvent faire de même sans aucune limite. Le premier utilisateur qui valide ses modifications, incrémente le numéro de version de l’enregistrement. Tous les autres se voient refuser leur modification, car le numéro de version qu’ils ont relevé au début de la session de modification des données n’est pas le même que celui qu’ils peuvent lire à la fin de cette session.
Lorsque l’on utilise la technique du verrouillage pessimiste, le SGBD effectue une boucle d’attente en tentant de poser le verrou pendant n secondes. C’est comme cela que fonctionne la quasi-totalité des SGBD à base de fichiers (dBase, Paradox, FoxPro, Access…).
Effectuer une boucle d’attente qui interroge un fichier, monopolise dramatiquement le réseau. C’est pourquoi la quasi-totalité des SGBD C/S fonctionne en verrouillage optimiste, car dans ce mode, l’échange de données entre la base et le poste client est rapide et bref…
De plus si nous prenons en compte l’intégrité référentielle et que la structure de la base permet de modifier des clefs existantes en répercutant leurs valeurs dans l’ensemble des tables filles, alors dans le cas d’un SGBD fichier ont peut obtenir un trafic réseau important, tandis que dans le cas du client serveur, l’opération sera menée au sein du SGBD donc sans aucune influence sur la charge du réseau.
3.2. Exécution du SQL
Soit une table de nom CLIENT contient 50 000 enregistrements de 200 octets chacun. La requête est la suivante :
SELECT * FROM CLIENT WHERE (CLI_NAME LIKE ‘%CLINTON%’)
Dans un SGBD de type fichier, voici le flot de données :
- Le PC rapatrie depuis le serveur le fichier contenant la table CLIENT (Soit au minimum 50 000 x 200 octets (sans compter les index et le reste [1])
- Le PC traite localement la requête
- Le PC affiche le résultat (soit par exemple 5 lignes de 200 octets)
Dans un SGBD de type C/S, voici le flot de données :
- Le PC envoie au serveur le fichier texte de la requête (soit environ 50 oct)
- Le serveur exécute la requête sur la table CLIENT
- Le serveur renvoie au PC les données répondant à la requête (5 lignes de 200)
- Le PC affiche le résultat
Bilan de ces opérations :
- SGBD fichier : environ 10 001 000 octets ont été véhiculé sur le réseau
- SGBD C/S : environ 1 050 octets ont été véhiculés sur le réseau
Dans ce cas, le rapport est de près de 1/10000 en faveur du C/S…
3.3. Le transactionnel
Les SGBD C/S disposent d’un mécanisme qui n’est généralement pas disponible dans les SGBD fichier, le transactionnel.
Le transactionnel permet de regrouper de multiples commandes SQL comme s’il s’agissait d’une instruction de type atomique, c’est à dire s’exécutant en totalité ou aucunement.
Cela suppose de donner un point de départ à la transaction un point d’arrivée et de contrôler si l’ensemble des commandes SQL ont été réalisées avec succès. Dans le cas ou l’une des commandes SQL n’a pas été traitée correctement, alors le point final de la transaction permet de revenir en arrière sur l’ensemble des opérations déjà effectuées. La transaction se présente alors comme une seule instruction de type ‘tout ou rien’.
Cela n’est possible que sur des serveurs capables d’enregistrer l’ensemble des insertions, modifications et suppressions effectuées sur les enregistrements des tables d’une base (historisation des modifications).
Supposons que les commerciaux sont dotés de PC portables et que, chaque semaine, ils viennent au siège de la société pour remplir la base centrale des nouveaux clients et des commandes qu’ils ont saisies sur leur portable.
Pour réaliser ce recollement d’information, l’informaticien a réalisé la transaction suivante :
BEGIN TRANSACTION
INSERT INTO CENTRAL_CLIENT (NO_CLI, NOM_CLI, ADRESSE_CLI)
SELECT PC.NO_CLI, PC.NOM_CLI, PC.ADRESSE_CLI
FROM PORTABLE_CLIENT PC
INSERT INTO CENTRAL_COMMANDE (NO_CLI, NO_COM, MONTANT_COM)
SELECT PC.NO_CLI, PC.NO_COM, PC.MONTANT_COM
FROM PORTABLE_COMMANDE PC
IF ERROR
THEN
ROLLBACK
ELSE
COMMIT
ENDIF
S’il n’avait fait qu’enchaîner les deux requêtes, il aurait été possible que les clients soit insérés sans que les commandes le soit, et vice versa. Mais tous les bons SGBD, qu’ils soient C/S ou fichier, savent gérer les intégrités référentielles et auraient empêché la moindre insertion dans la table des commandes sans avoir la référence du client préalablement insérée dans la table des clients…
3.4. Les triggers, spécialités du C/S
Les triggers font correspondre aux événements d’un SGBD du code.
Soit une table CLIENT liée à 3 tables filles (FACT, LIGNFACT, BONLIV). On veut modifier les tables filles lors de la suppression d’un enregistrement de CLIENT:
Dans les SGBD à base de fichiers il faut rajouter du code dans tous les éléments qui effectuent la suppression dans la table mère…
Dans une base de données C/S, avec un trigger, le traitement s’effectue directement sur le serveur et il ne nécessite aucun appel à aucune fonction d’aucune sorte. C’est en effet la base elle-même qui va se rendre compte que l’événement de suppression de la table client nécessite un traitement et va l’exécuter.
Dans un tel exemple, le code pourrait être le suivant :
CREATE TRIGGER DELETE_CASCADE_CLIENT (CLI_ID_CLI INTEGER) BOOLEAN
BEFORE DELETE
DELETE FROM FACT WHERE (ID_CLI = CLI_ID_CLI)
DELETE FROM LIGNFACT WHERE (ID_CLI = CLI_ID_CLI)
DELETE FROM BONLIV WHERE (ID_CLI = CLI_ID_CLI)
IF ERROR
THEN
RETURN FALSE
ENDIF
RETURN TRUE
Ce trigger comportant différentes commandes SQL devra être appelé au sein d’une transaction plus globale incluant la suppression de l’enregistrement dans la table mère. Les commandes SQL de ce trigger réclament la suppression dans les tables FACT, LIGNFACT et BONLIV de tous les enregistrements faisant référence à l’identifiant du client transmis en paramètre. Si au moins, une des requêtes échoue, le trigger renvoi False à la procédure l’ayant appelée et l’ensemble des requêtes sera annulé. Sinon, si tout va bien, le trigger renvoi True et l’ensemble des modifications sera rendu effectif par une validation (COMMIT).
Ce trigger est stocké au sein même de la définition de la base de données et s’exécute, sur le serveur, à chaque fois qu’un ordre de suppression arrive dans la table CLIENT. Il n’y a donc aucun trafic sur le réseau pour effectuer ce traitement…
3.5. Les procédures stockées, régals du C/S
En complément aux triggers, les procédures stockées permettent de stocker un traitement (opérant en général uniquement sur les données de la base) au sein même de la base de données, qui s’exécutera, par appel de la procédure au moment voulu, sur le serveur.
Exemple : une application réclame un traitement de modification des prix de vente des produits en fonction de la variation d’un certain nombre d’indices boursiers. Cette opération ne peut être déclenchée que manuellement par le responsable du service commercial…
Le code pourrait en être le suivant :
CREATE PROCEDURE AUGMENTATION_PRIX
VAR
INDICE REAL
ENDVAR
; calcul du nouvel indice d’augmentation
; moyenne de la différence entre les anciens et les nouveaux indices
SELECT AVG(DERNIER_INDICE – INDICE_PRECEDENT) AS :INDICE
FROM INDICE_BOURSE
; teste s’il s’agit bien d’une augmentation et non d’une diminution
; autrement dit un indice positif et non négatif
IF INDICE < 0
THEN
RETURN
ENDIF
; effectue l’augmentation
UPDATE PRODUITS
SET PRIX_VENTE_HT = (PRIX_VENTE_HT * (1 + (:INDICE)))
IF ERROR
THEN
RETURN FALSE
ENDIF
RETURN TRUE
Dès lors cette procédure peut être appelée n’importe quand, par n’importe quel poste client… Elle s’exécutera sur le serveur et ne nécessitera pratiquement aucun trafic réseau. On peut aussi imaginer qu’elle soit couplée avec un trigger ou encore exécutée à une heure différée, par exemple la nuit afin de ne pas ralentir les accès aux données plus fréquents en général dans la journée…
3.6. Le journal, particularité des SGBD C/S
En plus des triggers et des procédures stockées, la plupart des SGBD C/S proposent d’historiser la « vie » des données, dans ce que l’on appelle un journal des transactions. Celui-ci conserve une trace temporelle de toutes les insertions, suppressions, modifications intervenues dans la base de données depuis son origine. Ce journal permet de revenir en arrière de manière partielle sur les données. L’intérêt essentiel réside dans le fait de pouvoir, en cas de crash, revenir à un état des données satisfaisant.
Pour revenir à cet état stable, le système lit le journal en débutant par la fin, et reprend toutes les transactions qui ont été un succès, sans que la mise à jour physique des données ait été effectuée.
3.7. Mise en place
Il est nécessaire de se munir d’un serveur puissant; un SGBDR en C/S nécessite au minimum 128 Mo de RAM même avec un nombre très restreint d’utilisateurs). Il faut avoir des disques à accès très rapide (SCSI UW par exemple) et ne pas hésiter à investir dans un contrôleur RAID éventuellement en « hot plug ».
20 millisecondes