sgbd et sql

7/30/2019 SGBD ET SQL

1/13

1

&SQL

Not ions de basesDf in i t ion int ui t ive dune BD (1)

Dfinition intuitive : on peu considrer une Base deDonnes (BD) comme: Grande quantit de donnes (ou ensemble dinformations), Centralises (ou non) l i l i li il i l i li i , Interrogeables et modif iables par un groupe duti lisateurs

travaillant en parallle.

Exemples dapplication Annuaire lectronique Catalogue lectronique dune bibliothque

Not ions de basesDf in i t ion int ui t ive dune BD (2)

Dfinition ; une base de donn es est un ensemble structur dedonnes (1) enregistr es sur des suppor ts (2) pour satisfairesimultanment plusieurs utilisateurs (3) de manire slective (4) en untemps opportun (5).

(1) : Organisation et description de donnes (2) : Stockage sur disque (3) : Partage des donnes (4) : Confidentialit (5) : Performance

SGBD (1)

Dfinition : Le logiciel qui permet dinteragir avec une BDest Systme de Gestion de Base de Donnes (SGBD) ouDBMS

BDSGBD

SGBD (2)Un SGBD est un intermdiaire entre les utilisateurs et lesfichiers physiques

Un SGBD facilite La gestion de donnes, avec une reprsentation intuitive simplesous forme de table par exemple

La manipulation de donnes. On peut insrer, modifier lesdonnes et les structures sans modifier les programmes quimanipulent la base de donnes

Programme 1

Programme 2

Programme 3

SGBD

Fichier 1

Fichier 2

Fichier 3

Base de donnes

Object i fs des SGBD (1)

Fac i l i t e r la reprsenta t ion e t ladescr ip t ion de donnesIndpendance physique (1) :

Plus besoin de travailler directement sur les fichiers physiques (telsquils sont enregistrs sur disque).

i l l li ll erme e crire les onn es e les liens en re elles une aonlogique sans se soucier du comment cela va se faire physiquementdans les fichiers.

On parle alors dimage logique de la base de donnes,

Fichiers

physiques

Image physiqueImage logique


2/13

2


Indpendance physique (2) :

La manipulation des donnes se fait :

Directement sur le schma logique. On peut insrer, supprimer, modifierdes donnes directement sur limage logique.

Le SGBD va soccuper de faire le travail sur les fichiers physiques.


Indpendance logique : Un mme ensemble de donnes est vu diffremment par des utilisateurs

ii . Toutes ces visions personnelles des donnes doivent tre intgrs dansune vision globale par le SGBD.

Manipulations des donnes par des non informaticiens. I l fau t pouvo ir accder aux donnes sans savo ir programmer ce quisignifie des langages quasi naturels .


Efficacit des accs aux donnes :

Ces lan a es doivent ermettre dobtenir des r onses auxinterrogations en un temps raisonnable .

Il doivent donc tre optimiss (minimiser le nombre daccs disques) Dune faon compltement transparente pour lutilisateur.

Administration centralise des donnes :

Des visions diffrentes des donnes se rsolvent plus facilementsi lesdonnes sont administres de faon centralise.


Cohrence des donnes.

Les donnes sont soumises un certain nombre de contrainte dintgrit quidfinissent un tat cohrent de la base.

Elles doivent pouvoir tre exprimes simplement et vrifiesautomatiquement chaque insertion, modif icat ion ou suppression de

donnes, par exemple : lge dune personne suprieur zro Salaire suprieur zro Etc

Ds que lon essaie de saisir une valeur qui ne respecte pas cettecontrainte, le SGBD le refuse.


Non redondance des donnes :

Afin dvi ter les problmes lors des mises jour, chaque donne ne doi t treprsente quune seule fois dansla base.

Parta eabilit des donnes :

Plusieurs utilisateurs daccder aux mmes donnesau mme moment. Simple en cas dinterrogations et mono-utilisateur, Difficile lors de modifications dans un contexte multi-utilisateurs. Il sagit alorsde pouvoir :

Permett re deux (ou plus) uti li sa teurs de modif ie r la mme donne en mmetemps ; Assurer un rsultat dinterrogation cohrent pour un utilisateur consultant une tablependant quun autre la modifie.


Scurit des donnes.

Les donnes doivent pouvoir tre protges contre les accs non autoriss. Pourcela, il fautpouvoir associer chaque utilisateur desdroits daccs auxdonnes.

Rsistance aux pannes :

Prvoir de rcuprer une base dans un tat sain . Deux solutions sont possibles :

Rcuprer les donnes dans lta t dans leque l e lles ta ient avant lamodification,

Soit terminer lopration interrompue.


3/13

3

Trois Fonct ions dun SGBD

Description des donnes :

Codification structuration, grce un Langage de Description de Donnes(LDD)

Manipulation et restitution des donnes (insertion, mise jour, interrogation) Mise en uvre laide dun Langage de Manipulation de Donnes

(LMD) S.Q.L. (Structures Query Langage) : Langage standard

Contrle (partage, intgrit, confidentialit, scurit)

Dfin i t ion e t descr ip t ion des donnes3 n iveaux de descr ip t ion

Interview

Documents

Schma physique

Schma conceptuel

Modlisation

TT

Schma

Externe1

Schma

Externe2

Df in i t ion e t desc r ip t ion des donnesniveau log ique (conceptue l )

Permet la description

Des objets : exemple OUVRAGES, ETUDIANTS i i l ii i l i

OUVRAGES Des liens entre les objets : un OUVRAGE peut tre

emprunt par un ETUDIANT

Des contraintes : le nombre dexemplaires dun OUVRAGEest suprieur zro

Cette description est faite selon un modle de donnes.

Dfin i t ion e t descr ip t ion des donnesniveau physique

Description informatique des donnes et de leurorganisation : en terme de fichiers, dindex, demthodes daccs,

Passage du modle logique au modle physiquetend tre assist par le SGBD : transparent et/ou

semi-automatique

Objectifs : optimiser les performances

Df in i t ion e t desc r ip t ion des donnesniveau ex terne

Description des donnes vues par un utilisateur ( ouun groupe dutilisateurs)

Ob ectifs : simplification, confidentialiti i li i i i i li

Exemple : CIEL, SAGE,

Manipu la t ion e t res t i tu t ion desdonnes

Afin de raliser les oprations suivantes

Insertion : saisir des donnes Supprimer Modifier Interroger : rechercher des donnes via des requtes

La manipulation des donnes est mise en uvre laidedun Langage de manipulation de Donnes (LMD). SQL(Structured Query Language) est le langage standard demanipulation de BD


4/13

4

Contr les ral iss par le SGBD

Partage de donnes :

accs la mme information par plusieurs utilisateurs en mme temps. Le SGBD inclut un mcanisme de contrle de la concurrence bas sur des

techniques de verrouillage des donnes Pour viter par exemple quon puisse lire uneinformation quon est en train

demettre jour

Intgrit des donnes

Le SGBD vei lle ce que toutes les contraintes soient vrif ies chaqueinsertion, suppression, ou modification dune donne.

Contr les ral iss pa r le SGBD

Confidentialit : Plusieurs utilisateurs peuvent utiliser en mme temps une base de donnes

Se pose le problme de la confidentialit des donnes.

i i l i l ii i l , i l , i j ,cration; qui permettent daffiner.

Scurit : Une base de donnes est souvent vi ta le dans le fonctionnement d une

organisation,

Il nest pas tolrable quune panne puisse remettre en cause sonfonctionnement de manire durable.

LesSGBD fournissent des mcanismes pourassurer cette scurit.

Quelque s SGBD

Quelques SGBD (relationnels du march)

Micro : ACCESS, Paradox, Dbase, PostSQL, MySQL, Gros systme : DB2, ORACLE, SYBASE,

Archi t ec t ure des SGBD Architecture centralise

programme d'application et SGBD sur mme machine (mme site) premiers systmes

rc ec ure u ype c en-serveur c en -serverarchitecture)

programme d'application = client interface ( GUI ) + traitement du domaine d application

SGBD = serveur (de donnes data server ) machines (sites) diffrentes deux couches, niveaux, strates(two tier)

Arch i tec ture des SGBD

Base sur une architecture Client-Serveur

Donnes sur le serveur partages entre N clients Interfaces raphi ues sur la station de travail personnellei l i il ll Communication par des protocoles standardiss Clients et serveurs communiquant par des requtes avec

rponses

Arch i t ect ure c l ien t / serveur

Rseau

BD

Logicielintermdiaire

Pilote detlcommunication

Programmed'application

Logicielintermdiaire


SGBD

Client Serveur


5/13

5

Arch i tec tu re 3 t i e rs

RseauRseau

BD

Logiciel

intermdiaire


Application

Logiciel

intermdiaire


SGBD

Serveurd'application

Serveur dedonnes

Logiciel

intermdiaire


Interface

Clientmince

Base de donnes d is t r ibues

RseauRseau

BD

locale

BD

locale

Logiciel

intermdiaire


SGBD rparti

Serveur dedonnes

Logiciel

intermdiaire


Programmed'application

Client

Logiciel

intermdiaire


SGBD rparti

Serveur dedonnes

Base de donnes paral l les

Unit dei

Unit dei

Unit dei

Mmoire vive

Disque Disque Disque Disque

i i i

Disque

Entrept de donnes

BD

oprationnelle(OLTP)

Entrept de donnes( data wharehouse )

Autresource de

donnes

Extraction :filtrage,

synthse,transformation,

fusion

BD

oprationnelle(OLTP)

Analyse(OLAP),

prospection

Entrept de donnes Base de donnes oprationnelle

traitement des donnes quotidienneset rcentes OLTP ( On LineTransactionProcessing).

Entrept de donnes(data wharehouse)

grand volume de donnes historiques extraites de bases oprationnellespour le support la prise de dcision

OLAP ( On LineAnalytical Processing) Prospection de donnes , ou forage, fouille, exploration de

donnes, ou dcouverte de connaissances dans les BD(datamining, analysis, dredging, archeology, knowledge discovery indatabases - KDD)

extractionnon trivialed'informationsimplicites, inconnueset utiles apprentissagemachine , statistiques

Langage SQL


6/13

6

plan

Introduction Interrogation Dfinition des donnes Contrle des donnes

In t roduct ion au langage SQL

SQL : Structured Query Language

Un langage ANSI American National Standards

Institute standard de gestion des bases de donnesrelationnelles

Conu par IBM dans les annes 70

In t roduct ion au langage SQLSQL est un langage de:

Dfinition des donnes DDL : create, drop, alter / tables ,procdures et vues

an pu a on es onn es : inser , dee e, upda e /tables

Interrogation des donnes DQL : select / tables Contrle de donnes DCL : grant, revoke / tables ,

procdures et vues Contrle de transactions : commit, rollback / requtes


Il existe plusieurs versions de SQL malgr quil soit ANSI :PL/SQL, Transact-SQL, OQL,

bases et les traitent de la mme manire:

Select, update, delete, insert , where,

Bas sur le calcul relationnel Algbre relationnel (Bool)

In t roduct ion au langage SQL Le langage SQL est utilis directement partir dun diteur

Indirectement depuis un langage de programmation telsque Java, C++, par le biais dun outil d interfaage :ODBC, JDBC,

Sous forme de procdures stockes ou fonctionsenregistres dans la base de donnes elle-mme

Avec des triggers (dclencheurs) : gestion desvnements lis aux modifications du contenu de la basede donnes


Le langage SQL est un langage dclaratif : dcrit lersultat escompt et non la manire de lobtenir

Gnralement quip dun optimiseur de requtes pourrduire la complexit algorithmique


7/13

7

In t roduct ion au langage SQL Une relation du schma relationnel est converti vers une

table dans la base de donnes

Une table est compose de lignes et de colonnes

Lignes : enregistrements (occurrences) Colonnes: champs (attributs)

Toutes les donnes dune colonnes doivent avoir le mmetype dfini au pralable


Exemple de table Etudiant :

Nom Prnom CNE note Email

Nadiri Ahmed 1 32 54 2 1 5 Nadiri@ ahoo.fr

Zerwal Bouchra 4 56 88 4 1 2 [email protected]

Moutawaki l Marwan 8 78 95 5 1 0 [email protected]

Moutawaki l Nidal 6 85 49 9 1 4 [email protected]

In t roduc t ions SQL / Types

Numriques:

Nombres entiers: SMALLINT(2 octets) ; INTEGER(4octets)

, , 456.12 : p=5; d=2

A virgule flottante : REAL (p >=7) ; FLOAT, DOUBLEPRECISION (p >=15) Chane de bits :

BIT (n) BIT VARYING (n)

TY PE DE DONNES

40

Type de donnes

Types numriques

Types pour les chanes de caractres

T es tem orels dates heures minutes , , ,

Types numr iques

Nombres entiers : SMALLINT sur 2 octets INTEGER sur 4 octets

vir ule flottante : REAL DOUBLE PRECISION (ou FLOAT)

Constantes : 253.8, -10, 1.3E-5


8/13

8

Types Chane de Carac t res

CHAR(longueur) :chanes de caractres avec unnombre fixe de Caractres

VARCHAR (longueurMaximum)

chanes de caractres avec un nombre variable de

caractres (mais un nombre maximum de caractres)

CHAR(5) : chane de 5 caractres

VARCHAR(20) : chane de 20 caractres au plus

Temporels:

DATE (2,2,4) : our les dates

Types Tempore ls

TIME (H, M, S) : pour les heures, minutes et secondes TIMESTAMP (date + heure) pour un moment prcis INTERVAL ( intervalle de temps)

In te r roga t ions s imp les

45

Interrogation / SELECT

Base essentiellement sur linstruction SELECT

Syntaxe :

_ , , _

FROM table_1 , , table_n

WHERE Prdicat ========================

SELECT *

FROM table_1 , , table_n

WHERE Prdicat

Int errog at ion / SELECT

SELECT : retourne les donnes spcifies dans la clause exp_1, , exp_n depuis les tables table_1, , table_n et vrifiant les conditions dcrites dans Prdicat

ex 1 , , ex n : colonnes de tables ou ex ressions_ _bases dessus (constantes, )

FROM : spcifies les tables de slections

table_1 , , table_n : (tables, vues, )

WHERE : introduit des contraintes logiques vrifier

Prdicat : contraintes logiques vrif ier qui peuvent treassez complexes

SELECT nom , CNE FROM Etudiant ========================

In ter rogat ion / Exemples


Nadiri Ahmed 1 32 54 2 1 5 [email protected]




Nom CNE

Nadiri 132542

Zerwal 456884

Moutawakil 878955

Moutawakil 685499


9/13

9

SELECT nom , CNE , note + 2 FROM Etudiant ========================







Nom CNE note

Nadiri 1 32 54 2 1 7

Zerwal 4 56 88 4 1 4

Moutawakil 8 78 95 5 1 2

Moutawakil 6 85 49 9 1 6


SELECT *

FROM Etudiant ========================Nom Prnom CNE note Email





SELECT Nom, CNE FROM Etudiant


========================Nom Prnom CNE note Email





Nom CNE

Moutawakil 878955

Moutawakil 685499


SELECT *

FROM Etudiant = =

Ahmed

========================Nom Prnom CNE note EmailNadiri Ahmed 132542 15 [email protected]

In terrogat ion / Oprateurslogiques

Oprateur description

= Egale

> Strictement suprieur

>= Suprieur ou gal

< Strictement infrieur


10/13

10

Interrogation / SELECTDISTINCT

Exemple : SELECT DISTINCT nom

========================Nom

Nadiri

Zerwal

Moutawakil

Crat ion d une table

(LDD)

56

CREATE TABLE table (colonne1 type1,

colonne2 type2,

. . .

Option not null si la colonne doitobligatoirement tre renseigne

SQL/ Df in i t ion des donnes

. . .)

Exemple :create table article (ref char(5) not null,

nom varchar(20), prix numeric(9,2), dateMAJ date);

Manipu la t ion des Donnes (LMD)

58

SQL/ Manipulat ion des donnes

Commandes de manipulation des donnes:

INSERT pour ajouter des lignespour mo er es gnes

DELETE pour supprimer des lignes


INSERT INTO table [(colonne1, colonne2,)]

VALUES (valeur1, valeur2,)

Exemplesi ii i

values (10, 'Finance', 'Paris');insert into dept (lieu, nomD, dept)

values ('Grenoble', 'Recherche', 20);


11/13

11


UPDATE table

SET colonne1 = expr1, colonne2 = expr2,

[ WHERE prdicat ]

ouToutes leslignespardfaut

Exempleupdate empset dept = 10where nomE = 'Martin';

UPDATE table [ synonyme ]

SET (colonne1, colonne2, ) = (select )

[ WHERE prdicat ]

Ne doitrenvoyerqu'uneseule ligne

Exempleupdate empset sal = sal * 1.1where poste = 'Commercial';


DELETE FROM table

[ WHERE prdicat ]

Attention ! s'il n'y a pas de clause WHERE, touteses gnes son supprm es

Exemple :

delete from empwhere dept = 10;

SQL/ Exer cic es

Soit le modle relationnel suivant relatif une base de donnes sur desreprsentations musicales :

REPRESENTATION (nreprsentation, titre_reprsentation, lieu)MUSICIEN (nom, nreprsentation*)PROGRAMMER (date, nreprsentation*, tarif)NB : les cls primaires sont soulignes et les cls trangres sont marques par *

1. Donner la liste des titres des reprsentations.

SELECT titre_reprsentationFROM REPRESENTATION ;

SQL/ Exer cic es



2. Donner la liste des titres des reprsentations ayant lieu l'opra PARIS.

SELECT titre_reprsentationFROM REPRESENTATIONWHERE lieu="Opra PARIS"

SQL/ Exer cic es



3. Donner la liste des noms des musiciens et des titres des reprsentationsauxquelles ils participent.

SELECT nom, titre_reprsentationFROM MUSICIEN, REPRESENTATIONWHERE MUSICIEN.nreprsentation =REPRESENTATION.nreprsentation ;

SQL/ Exer cic es



4. Donner la liste des titres des reprsentations, les lieux et les tarifs pour lajourne du 14/09/96

SELECT titre_reprsentation, lieu, tarifFROM REPRESENTATION, PROGRAMMERWHERE PROGRAMMER.nreprsentation =REPRESENTATION.nreprsentationAND date='14/06/96' ;


12/13

12

SQL/ Exer cic es

Soit le modle relationnel suivant relatif la gestion des notes annuelles d'unepromotion d'tudiants :ETUDIANT(NEtudiant, Nom, Prnom)MATIERE(CodeMat, LibellMat, CoeffMat)EVALUER(NEtudiant*, CodeMat*, Date, Note)

l l i i li l l: les cls primaires sont soulignes et les cls trang res sont marques par

1 - Quel est le nombre total d'tudiants ?

SELECT COUNT(*)FROM ETUDIANT ;

SQL/ Exer cic es



2. Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plusbasse ?

SELECT MIN(Note), MAX(Note)FROM EVALUER ;

SQL/ Exer cic es



3. Quelles sont les moyennes de chaque tudiant dans chacune des matires ?

SELECT ETUDIANT.NEtudiant, Nom, Prnom, LibellMat, CoeffMat,AVG(Note) AS MoyEtuMatFROM EVALUER, MATIERE, ETUDIANTWHERE EVALUER.CodeMat = MATIERE.CodeMat ANDEVALUER.NEtudiant = ETUDIANT.NEtudiantGROUP BY ETUDIANT.NEtudiant, Nom, Prnom, LibellMat,CoeffMat;

SQL/ Exer cic es

Le modle relationnel correspondant :REPRESENTANT (Num_rep, Nom_rep, Ad_rep, CP_rep, Vil_rep, Age_rep)COUVRIR (#Num_rep, #Code_dep)DEPARTEMENT (Code_dep, nom_dep, c hef secteur)CLIENT (Code_clt, Nom_clt, #Num-rep,#Num-cat)

i_ , _ , i

Ecrire les requtes suivantes

1) Afficher la liste des clients appartenant la catgorie tarifaire n1, classe par ordrealphabtique

R1) SELECT *FROM ClientWHERE Num-cat =1ORDER BY Nom-clt ASC

SQL/ Exer cic es

Le modle relationnel correspondant :REPRESENTANT (Num_rep, Nom_rep, Ad_rep, CP_rep, Vil_rep, Age_rep)COUVRIR (#Num_rep, #Code_dep)DEPARTEMENT (Code_dep, nom_dep, chef secteur)CLIENT (Code_clt, Nom_clt, #Num-rep,#Num-cat)CATEGORIE TARIFAIRE Num cat Nom cat Remise _ , _ , i


2) Afficher la liste des clients (code, nom de client) rattachs au reprsentant HINAUD

R2) SELECT Client.code-clt, Client.Nom-cltFROM Client, ReprsentantWHERE Reprsentant.Nom-rep = HinaudAND Client.Num-rep =Rerpsentant.numrep

SQL/ Exer cic es

Le modle relationnel correspondant :REPRESENTANT (Num_rep, Nom_rep, Ad_rep, CP_rep, Vil_rep, Age_rep)COUVRIR (#Num_rep, #Code_dep)DEPARTEMENT (Code_dep, nom_dep, c hef secteur)CLIENT (Code_clt, Nom_clt, #Num-rep,#Num-cat)CATEGORIE TARIFAIRE Num cat Nom cat Remise _ , _ , i


3) Afficher la liste des clients bnficiant d'une remise de 10%

R3) SELECT Client.*FROM Client, Catgorie tarifaireWHERE Catgorie tarifaire.remise = 10%AND Client.Num-cat = Catgorietarifaire.Num-cat


13/13

13

SQL/ Exer cic es

Le modle relationnel correspondant :REPRESENTANT (Num_rep, Nom_rep, Ad_rep, CP_rep, Vil_rep, Age_rep)COUVRIR (#Num_rep, #Code_dep)DEPARTEMENT (Code_dep, nom_dep, chef secteur)CLIENT (Code_clt, Nom_clt, #Num-rep,#Num-cat)

i_ , _ , i


4) Afficher la liste des reprsentants (Numro et nom) dpendant du chef de secteur PONS

R4) SELECT Reprsentant.num-rep, Reprsentant.Nom-repFROM Reprsentant, Couvrir, DpartementWhere Dpartement.chef secteur = Pons AND Reprsentant.Num-rep = Couvrir.Num-repAND Couvrir.code-dep = Dpartement.code-dep