informatique. cours td n°4 : bases de données...
Post on 10-Sep-2018
215 Views
Preview:
TRANSCRIPT
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
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 :
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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).
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 ;
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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!
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.)
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
Algebre relationnelle : le contexte
On dispose d’une base de donnees relationnelles, constituee deplusieurs relations.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
.M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 ?
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
Exemple de requetes en algebre relationnelle
A quelle requete repond-on ?
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
Quels sont les noms des producteurs de Muscadet ?
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
Numeros des vins ne faisant l’objet d’aucune commande
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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;
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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);
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 ;
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 ;
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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 ;
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
GeneralitesCreation et modification en langage SQL
Algebre relationnelleRequetes SQL
Agregats ou fonctions
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.
M. GOUMI Informatique. Cours TD N◦4 : Bases de donnees relationnelles
top related