informatique. cours td n°4 : bases de données...

GeneralitesCreation et modification en langage SQL

Algebre relationnelleRequetes SQL

Agregats ou fonctions

Informatique. Cours TD N◦4 :Bases de donnees relationnelles

M. GOUMI

Lycee Edmond PERRIER

M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles




Fonctionnalites d’une base de donnees relationnelle

Tout le monde utilise tous les jours les bases de donneesrelationnelles : en consultant ses mails ou son comptebancaire, en utilisant un site marchand d’internet, etc ;Les fonctionnalites d’une base de donnees relationnelles :





L’efficacite d’une base de donnees relationnelle

Elle decoule de l’architecture ANSI-SPARC qui intercale entrel’utilisateur final et lestockage physique des donnees une couche logicielle : le SGBD ;

celui-ci recoit les requetes de l’utilisateur, les communique auxserveurs de donnees et gere le retour des donnees attenduesvers l’utilisateur ;l’acces des utilisateurs est simple et l’independance logiqueentre le stockage et sa gestion est garanti.





Modele relationnel

La notion de modele relationnel a ete inventee par EdgarFranck Codd en 1970 ;

cette theorie est adossee a la theorie mathematique desensembles ;

l’implementation informatique de cette theorie est assure parle langage SQL (Structured Query Language, en francaislangage de requete structuree).





Relations

Une relation est un tableau (une ”table”) a deux dimensionsdont les colonnes correspondent a un attribut prenant desvaleurs dans un domaine, chaque ligne de ce tableau etant unn-uplet, plus volontiers appele tuple dans ce contexte.

Par exemple, pour un eleve de Terminale S du lycee :

LVI Langue Salle Professeur

Anglais 402 Mme Plazanet-DavidAnglais 112 Mme Druliolle

Espagnol 112 Mme BrandaoRusse 7 Mme Astrakhan

Allemand 402 M Ciet





Exemples de relations

LVI Langue Salle Professeur

Anglais 402 Mme Plazanet-DavidAnglais 112 Mme Druliolle

Espagnol 112 Mme BrandaoRusse 7 Mme Astrakhan

Allemand 213 M Ciet

Eleves noCode Nom Prenom ProfesseurLV1

133 Nobel Alfred Mme Plazanet-David98 Fields John Charles Mme Brandao

213 Abel Niels Mme Brandao

Salles noSalle Discipline

7 Langues112 Langues213 Langues402 Langues403 Physique





Cle

Une cle est un groupe minimal d’attributs qui determine achaque instant un unique tuple de la relation.

Quelles sont les cles des trois relations precedentes ?

Une premiere contrainte d’integrite : une relation doitposseder une cle renseignee.





Schema

Le schema d’une relation decrit :

son nom ;la liste de ses attributs et des domaines associes ;la liste des attributs composants sa cle (ils sont soulignes) ;

Exemple : Eleves(noCode:entier, Nom:chaıne,Prenom:chaıne, ProfesseurLV1:chaıne)

Le schema d’une relation est aussi appele intention de larelation ; l’ensemble des tuples qui la composent est sonextension.

Le schema d’une banque de donnees relationnelle estl’ensemble des schemas de ses relations.





Creation et mise a jour d’une relation

On jette ici un coup d’œil sur SQL en tant que langage dedefinition de donnees ;

Creation minimale :CREATE TABLE Eleves ( num INTEGER ,nom CHAR(40),

prenom CHAR(30));

Mise a jour :Ajout d’un attribut : ALTER TABLE Eleves ADD profLV1 CHAR(30) ;

Suppression : DROP TABLE Eleves ;





Domaines de base

Numeriques :Entier : INTEGER, SMALLINTDecimal : DECIMAL (m,n), NUMBER(m,n)Reel flottant : FLOAT, REAL

Chaıne de caracteres : CHAR (n), VARCHAR(n)

Temporel : DATE

Chaque SGBD possede d’autres domaines qui lui sont propres

Valeur NULL : l’absence de valeur ne veut pas dire zero!





Insertion et suppression de tuples en SQL

On passe a SQL en tant que langage de gestion dedonnees ;

Insertion de tuples : INSERT INTO ... VALUES (...);

Exemple : INSERT INTO Eleves VALUES (718, ’MARTIN’,Paul, BRANDAO) ;

Suppression de tuples : DELETE FROM ... WHERE (...);

Exemple : DELETE FROM Eleves WHERE num = 203;(supprime l’eleve numero 203 et tous les tuples des autrestables qui referencent cet eleve.)





Algebre relationnelle : le contexte

On dispose d’une base de donnees relationnelles, constituee deplusieurs relations.





Restrictions ou selections

But :

Un restriction (ou selection) selectionne des tuples enspecifiant une condition (par exemple, pour la relation”Salles” : etage inferieur ou egal a 2) ;elle cree donc une nouvelle relation reduite verticalement ;la notation textuelle d’une restriction est : T← σcond(R) ;la notation graphique est :

R

cond.

T

.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles




Projections

But :

Une projection selectionne des attributs en specifiant uneliste d’attributs (par exemple, pour la relation ”Eleves” :nom,LV1) ;elle cree donc une nouvelle relation reduite horizontalement ;la notation textuelle d’une restriction est : T← Πattributs(R) ;la notation graphique est :

R

attributs

T





Union

L’union permet de fusionner 2 relations ayant le memeschema (par exemple, les relations ”ElevesTermS” et”ElevesPremS”) ;elle cree donc une nouvelle relation contenant tous les tuplesappartenant a au moins l’une des relations, un tuple donne nepouvant apparaıtre qu’une fois et une seule ;la notation textuelle d’une reunion est : T← R ∪ S ;la notation graphique est :

R S

T

U





Intersection

L’intersection permet d’obtenir l’ensemble des tuplescommuns a 2 relations ayant le meme schema (par exemple,les relations ”ProfsPrincipaux” et ”ProfsLangues”) ;la encore, conformement a la theorie ensembliste, un tupledonne ne peut apparaıtre qu’une fois et une seule dans larelation resultat ;la notation textuelle d’une intersection est : T← R ∩ S ;la notation graphique est :

R S

T

U





Difference

La difference permet d’obtenir l’ensemble des tuplesappartenant a une relation sans appartenir a l’autre (parexemple, la difference entre les relations ”ProfsPrincipaux” et”ProfsLangues” donnent les profs principaux qui ne sont pasprofs de langues) ;la encore, les relations doivent avoir le meme schema ;la notation textuelle d’une difference est : T← R \ S ;la notation graphique est :

R S

T





Jointure

La jointure permet d’etablir le lien semantique entre lesrelations au travers d’une ou plusieurs conditions (parexemple, former une relation a partir des relations”ElevesTermS” et ”ElevesPremS” avec la condition”T.LV1=P.LV1”) ;

la notation textuelle d’une jointure est : T← R��S ;

la notation graphique est :

R S

T

Condition





Division

La division permet de repondre aux requetes de type ”tousles” ;si T = R ÷ S , un tuple t est dans l’extension de la relation Tsi, et seulement si, pour tout tuple s de S, le tuple t+s estdans l’extension de R ;Schema du resultat : si R(a1, a2, . . . , an, b1, b2, . . . , bp),S(b1, b2, . . . , bp) et T = R ÷ S , alors T (a1, a2, . . . , an) ;la notation textuelle d’une division est : T← R÷ S ;la notation graphique est :

R S

T





Exemple de division

Quels sont les eleves etudiant toutes les langues vivantesenseignees au lycee ?

¿ on construit, a l’aide d’une projection sur la relationInscritsLycee, la relation R avec les informations dont on abesoin : attributs numEleve et codeLangue ;

on construit, a l’aide d’une projection sur la relationprofsLangues, la relation S avec les informations dont on abesoin : attributs codeLangue ;

la division de R par S donne les numEleve des elevesrepondant a la requete.

la verification se fait en associant a un tuple trouve dans Tn’importe quel tuple de S pour s’assurer que leurconcatenation est dans R.





Exemple de requetes en algebre relationnelle

Base de donnees exemple : les vins.Vins(num, cru, annee, degre) VRecoltes(nvin, nprod, quantite) RProducteurs(num, nom, prenom, region) PClients(num, nom, prenom, ville) ClCommandes(ncde, date, ncli, nvin, qte) CLivraisons(ncde, noOrdre, qteLivree) L

Quels ”liens” entre ces informations ?





Exemple de requetes en algebre relationnelle

A quelle requete repond-on ?





Quels sont les noms des producteurs de Muscadet ?





Numeros des vins ne faisant l’objet d’aucune commande





Syntaxe generale d’une requete

Une requete SQL comporte trois parties :SELECT....FROM....WHERE....;

on commence par ”remplir” le FROM par les relations ou onpeut trouver les tuples recherches, ainsi que par les conditionsde jointure avec la syntaxe

Relation1 JOIN Relation2 ON conditionjointure ;

on remplit ensuite le WHERE par les conditions derestrictions imposees par la requete ;

on termine en remplissant le SELECT par la projectionfinale : les attributs demandes par la requete ;

enfin, une requete SQL se termine toujours par unpoint-virgule.





Donner les vins de cru Chablis (restriction, puis projection)

On attend le numero, l’annee et le degre des vins de cru Chablis

SELECT num,annee,degre

FROM Vins

WHERE cru=’Chablis’;

on obtiendra le resultat sous forme d’une relation(schema+extension) :

num annee degre

273 2012 12

128 2010 12.5

57 2013 12





Autres exemples de projections

”Donner tous les vins” : on attend le numero, le cru, l’anneeet le degre de tous les vins.

SELECT *

FROM Vins;

”Donner la liste de tous les crus, avec elimination desdoublons”.

SELECT DISTINCT cru

FROM Vins;





Autres exemples de restrictions

”Donner les vins de degre compris entre 8 et 12” : on attendle numero, le cru, l’annee et le degre de tous les vins.

SELECT *

FROM Vins

WHERE degre >=8 AND degre <=12 ;

SELECT *

FROM Vins

WHERE degre BETWEEN 8 AND 12 ;

SELECT *

FROM Vins

WHERE degre IN (8, 9, 10, 11, 12);





Exemples de jointure

”Donner les noms des producteurs de Pommard” : on attendleurs noms.

les Pommard sont dans Vins, les producteurs dans Producteurset le lien entre les deux est assure par la relation Recoltes ;

SELECT P.nom

FROM Vins V JOIN Recoltes R ON (V.num = R.nvin) JOINProducteurs P ON (R.nprod=P.num)

WHERE V.cru=’Pommard’ ;

noter le renommage des relations pour raccourcir le prefixagedes attributs.





Operateurs ensemblistes

”Donner les noms de l’ensemble des producteurs et desclients” ;

SELECT num FROM Producteurs UNION SELECT numFROM Clients ;

”Donner les noms portes a la fois par des producteurs et desclients” ;

SELECT num FROM Producteurs INTERSECT SELECTnum FROM Clients ;

”Donner les noms de producteurs qui ne sont pas des noms declients” ;

SELECT num FROM Producteurs MINUS SELECT numFROM Clients ;





SQL, c’est un peu plus que de l’algebre relationnelle

Vins num cru degre annee

1 Pomerol 12.5 20102 Chablis 12 20133 Pomerol 13 20084 Chablis 11.5 20115 Chablis 11 2010

NbVins 5

nbCrus 2

nbAnnees 4

degreMoyen 12

degreMin 11

degreMax 13





Fonctions

5 fonctions predefinies : COUNT, SUM, MIN, MAX, AVG ;

elles s’appliquent a l’ensemble des valeurs d’un attribut d’unerelation et produit une valeur unique ;

elles se positionnent uniquement dans le SELECT, jamaisdans le WHERE ;

attention a ne pas melanger dans le SELECT les fonctions etles attributs simples : il y aurait alors un conflit entre lamultiplicite potentielle des tuples satisfaisants aux conditionssur les attributs et l’unicite du resultat d’une fonction :

Par exemple, SELECT num, AVG(degre) FROM Vins ;ne peut pas donner un resultat coherent.





Group by

En revanche, on peut parfois melanger dans le SELECT lesfonctions et les attributs simples a condition d’utiliser lacommande GROUP BY ;

Par exemple, si on veut obtenir le nombre de crus pourchaque annee de production, on peut faire :

SELECT count(DISTINCT cru), annee FROM Vins GROUPBY annee ;





Exemples avec des fonctions

”Donner la moyenne des degres de tous les vins”

SELECT AVG(degre) FROM Vins ;

”Donner la quantite totale commandee par le client de nomGoumi”

SELECT SUM(qte)FROM Clients C JOIN Commandes Co ON(C.num=Co.nCli)WHERE C.nom=’Goumi’;

”Nombre de crus differents”

SELECT COUNT(DISTINCT cru)FROM Vins ;





Requetes imbriquees

”Vins dont le degre est superieur a la moyenne des degres desvins”

SELECT *FROM Vins WHERE degre>(SELECT AVG (degre)

FROM Vins);

”Numeros de commande ou la quantite commandee a etetotalement livree”

SELECT ncdeFROM Commandes CWHERE C.qte=(SELECT SUM (L.qteLivree)

FROM Livraisons LWHERE L.ncde=C.ncde);

remarquer que l’alias C est visible dans le bloc imbrique.


informatique. cours td n°4 : bases de données...

Documents