Support de cours BD en PostgreSQL Philippe Durif 9 juillet 2010
Chapitre 1 Qu’est-ce qu’une base de donn´ ees 1.1
Qu’est-ce qu’une base de donn´ ees (BD)
Une base de donn´ees peut ˆetre vue comme le besoin de m´emoriser de fa¸con durable des donn´ees et de pouvoir exprimer le plus pr´ecis´ement possible les relations qu’entretiennent ces donn´ees. Une fois cette repr´esentation faite il est n´ecessaire d’associer des fonctionnalit´es (programmes et des requˆetes) `a cette base de donn´ees afin de pouvoir l’exploiter le plus facilement possible. Toutes les personnes exploitant la mˆeme base de donn´ees n’ont pas la mˆeme fonction et n’ont donc pas forc´ement besoin de voir les mˆemes informations ou d’appliquer les mˆemes actions `a la base de donn´ees. Les syst`emes des privil`eges, des vues et des programmes stock´es permettent de d´elimiter rigoureusement ces diff´erentes visions d’une mˆeme base de donn´ees (chaque vision est nomm´ee sch´ema externe). Enfin, plusieurs utilisateurs peuvent appliquer simultan´ement des modifications `a la mˆeme base de donn´ees, il est alors n´ecessaire d’utiliser des techniques d’isolation et de synchronisation afin de garantir la coh´erence de ces modifications.
1.2
Qu’est-ce qu’un syst` eme de gestion de base de donn´ ees (SGBD)
Un SGBD est la structure d’accueil d’une ou plusieurs bases de donn´ees : il offre les outils n´ecessaires `a la mise en place d’une base de donn´ees. On pourrait comparer le SGBD au syst`eme d’exploitation et la base de donn´ees `a un programme d’application utilisant les services du syst`eme. Voici quelques-unes des caract´eristiques d’un SGBD : – Capacit´e de g´erer des donn´ees persistantes et structur´ ees. – Capacit´e `a g´erer, autant que possible, la s´ emantique des donn´ees et `a garantir des propri´et´es (les contraintes, assertions, domaines des attributs, triggers et proc´edures stock´ees) – Pouvoir manipuler facilement et efficacement de tr`es grand volumes de donn´ees. – Permettre l’ex´ecution de transactions concurrentes par un ou plusieurs utilisateurs tout en conservant les propri´et´es de la BD. – Assurer la s´ ecurit´ e des donn´ees : – contrˆoler les acc`es en fonction de droits accord´es aux diff´erents utilisateurs. – tol´erer les pannes logicielles ou mat´erielles grˆace `a des proc´edures de reprise. – Procurer l’ind´ ependance physique : le SGBD permet de manipuler les donn´ees ind´ependemment de leurs implantations mat´erielles. 2
– Procurer l’ind´ ependance logique : chaque utilisateur ne voit de la base que les donn´ees qui lui sont n´ecessaires (sch´ema externe). – Le cœur d’un SGBD est le mod` ele de donn´ ees qu’il supporte, c’est `a dire la mani`ere d’organiser les donn´ees qu’il offre. Le mod`ele actuellement le plus utilis´e est le relationnel invent´e dans les ann´ees 1970 dont une belle qualit´e est probablement la sym´etrie naturelle qu’il offre dans les associations inter-donn´ees. Il existe d’autres mod`ele de donn´ees : hi´erarchique, r´eseau et objet, qui eux ne sont pas franchement sym´etriques. – Fournir un langage de haut niveau adapt´e au mod`ele : SQL pour le mod`ele relationnel, CODASYL pour le mod`ele r´eseau, OQL pour le mod`ele objet. – Exemples de SGBD relationnels : Oracle, PostgreSQL, MySQL, Access et plein d’autres !
1.3
Les mod` eles de donn´ ees
Un mod`ele de donn´ees est un formalisme permettant de : – d´ecrire les donn´ees (organisation, typage, ...) – manipuler ces donn´ees. Les deux principaux mod`eles : Mod` eles ` a acc` es purement associatif Ce sont : Relationnel ann´ees 1970, SQL1 1987, SQL2 1992 D´ eductif ann´ee 1980-1990, calcul des pr´edicats logiques du premier ordre, par exemple DATALOG (`a la Prolog) La manipulation des donn´ees est d´eclarative : le programmeur n’a pas `a se soucier du comment mais seulement du quoi, par exemple : je veux la liste des clients dont les soldes sont positifs, je n’ai pas `a dire comment faire pour obtenir cette liste, c’est le SGBD qui s’en charge. Mod` eles ` a acc` es Navigationnel Ce sont : Fichiers avec chaˆınage programme APOLLO 1965, Hi´ erarchique fin des ann´ees 1960, utilisation de pointeurs permettant la navigation R´ eseaux fin des ann´ees 1960, COSET Orient´ e Objet ann´ees 1980-1990 (O2) La manipulation des donn´ees est proc´edurale : en plus du quoi, le programmeur doit se pr´eoccuper du comment, par exemple : tant qu’il reste au moins un client, mettre le prochain client dans la liste si son solde est positif. Mod` eles hybrides On trouve des mod`eles hybrides qui disposent d’acc`es associatif et navigationnel : le relationnel-objet (SQL3 1999, Oracle, PostgreSQL).
1.4
Les niveaux d’abstraction
Pour assurer l’ind´ependance logique et l’ind´ependance physique, le groupe ANSI/X3/SPARC a d´efini en 1975 trois niveaux de description d’une base de donn´ees : – le sch´ ema conceptuel, `a ce niveau on d´efinit la structuration et le typage des donn´ees. C’est le domaine du concepteur de la base. – Des sch´ emas externes donnent diff´erentes vues d’un mˆeme sch´ema conceptuel, chacun ´etant appropri´e `a un type d’utilisateur (SQL introduit la notion de vue et de privil`ege). – le sch´ ema interne qui d´efinit les param`etres de stockage, les index favorisant certains acc`es. C’est le domaine de l’administrateur/optimiseur. Ce niveau est le dernier avant la repr´esentation physique des donn´ees sur disque et en m´emoire centrale et qui est `a la charge du SGBD. 3
1.5
Sch´ ema et instances
Dans une BD, il y a un sch´ema qui d´ecrit la structure des donn´ees et des donn´ees fournies y sont m´emoris´ees. Le sch´ema d’une BD est le r´esultat de la conception (par exemple le MCD de Merise) qui d´ecrit l’organisation des donn´ees. Un sch´ema n’est pas destin´e `a ˆetre modifi´e (ou bien rarement). Une instance d’un sch´ema correspond aux donn´ees stock´ees dans la base `a un moment donn´e. Les donn´ees d’une instance respectent ´evidemment l’organisation impos´ee par le sch´ema. Le contenu d’une BD est ´eminemment variable : chaque modification de la BD produit une nouvelle instance du sch´ema correspondant. Exemple : 1. soit le sch´ema relationnel : Personne (idp, Nom, Pr´ enom), et une instances possible de ce sch´ema : idp 1 2 3
Nom DURAND DUPOND LAGAFFE
Pr´enom Gaston Jules Gaston
Chaque ligne correspond `a une personne, le fait que idp est soulign´e indique que idp est la clef primaire (primary key) devant avoir une valeur unique pour chaque ligne de la table. 2. le mˆeme sch´ema qui cr´ee une table vide en SQL : create table Personne ( idp Serial, Nom Varchar (20), Prenom Varchar (20), constraint Personne_PK primary key (idp) ) ; Serial est un g´en´erateur d’entiers (integer) qui commence `a 1, idp est la clef primaire de la table : deux lignes diff´erentes de Personne ne pourront avoir la mˆeme valeur en idp.
1.6 1.6.1
Les diff´ erents langages cˆ ot´ e serveur DDL : Data Definition Language
Pour d´efinir/modifier les sch´emas externes et le sch´ema conceptuel – par exemple, pour le mod`ele relationnel, SQL propose la cr´eation de table : create table Diplome ( id Serial, mention Varchar (20), constraint Diplome_PK primary key (id) ) ; create table Etudiant ( id Serial, nom Varchar (20), prenom Varchar (20), constraint Etudiant_PK primary key (id) ) ; – il est possible de modifier le sch´ema qui ajoute une colonne et une contrainte `a la table Etudiant : 4
alter table Etudiant add column mon_diplome integer ; alter table Etudiant add constraint Etudiant_Diplome_FK foreign key (mon_diplome) references Diplome (id) ; Une foreign key est une r´ef´erence `a une ligne dans une autre table ou la mˆeme table (les colonnes de la table r´ef´erenc´ee doivent ˆetre la clef primaire (primary key) de cette table). La clef primaire de la table r´ef´erenc´ee doit exister (sinon erreur et aucune modification de la table r´ef´eren¸cante). Si on tente de cr´eer (avec insert) un ´etudiants avec un id de diplˆome qui n’existe pas, il y aura une erreur et l’´etudiant ne sera pas cr´e´e. – cr´eation d’une vue qui donne le nombre d’´etudiants portant le mˆeme nom : create view Nb_Homonymes (Nom, Nombre_D_Etudiants_Portant_Ce_Nom) as select e.nom, count (*) from Etudiant e group by e.nom ; Le group by cr´ee un groupe de ligne des ´etudiants ayant le mˆeme nom, le count (*) compte le nombre d’´etudiants de chaque groupe. – cr´eation d’une vue qui donne le nombre d’´etudiants inscrits dans chaque diplˆome : create view Effectifs (id, mention, nb_etudiants) as select d.id, d.mention, count (e.id) from Diplome d left outer join Etudiant e on e.mon_diplome = d.id group by d.id, d.mention ;
1.6.2
DML : Data Manipulation Language
Permet de modifier le contenu de la base (insertion, mises `a jour, suppression de donn´ees) et d’interroger la base (langage de requˆete). – par exemple, pour le mod`ele relationnel, SQL propose les instructions insert, update, delete et la requˆete select. – par exemple, pour le mod`ele objet, la norme ODMG propose OQL (Object Query Language) et OML (Object Manipulation Language).
1.6.3
DCL : Data Control Language
Pour g´erer les utilisateurs et leurs privil`eges. Par exemple en SQL Oracle : CREATE USER ... (cr´ ee un utilisateur avec des options donn´ ees) CREATE ROLE ... (cr´ ee un r^ ole avec des options donn´ ees) DROP USER ... (supprime un utilisateur) GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] Un rˆole peut concerner un ou plusieurs utilisateurs. 5
1.7
L’Architecture Client/Serveur
Tr`es souvent le SGBD tourne sur une machine serveur plus ou moins d´edi´ee, par contre les applicatifs client tournent sur d’autres machines et doivent se connecter au SGBD via le r´eseau. Il faut donc distinguer clairement entre ce qui doit tourner sur le serveur et ce qui doit tourner sur le client.
1.7.1
Le code ex´ ecut´ e par le SGBD (le serveur)
Les ordres SQL select, insert, update, delete, Les triggers r´eflexes d´eclench´es lors d’une modification des donn´ees, pour v´erifier des contraintes complexes, ou pour rendre la base de donn´ees plus autonome. (langage : PL/SQL d’Oracle, ou PLPGSQL de PostgreSQL qui ressemblent tous deux fortement `a Ada). Les proc´ edures stock´ ees pour ´ecrire des traitements complexes n’ayant de sens que s’ils sont men´es jusqu’`a leur terme, par exemple une op´eration de virement d’un compte `a un autre qui n´ecessite deux op´erations de mise `a jour successives (2 update) (langage : PL/SQL d’Oracle, ou PLPGSQL de Postgres qui ressemblent tous deux fortement a` Ada). Les SGBD proposent souvent leur propre langage de programmation : PL/SQL pour Oracle, plpgsql pour PostgreSQL et le langage de MySQL.
1.8
Le code applicatif ex´ ecut´ e cˆ ot´ e serveur et/ou client
Ce code est en g´en´eral ´ecrit dans un langage hˆote : ce sont des langages classiques (Cobol, C, Ada, Java, . . .) qui permettront d’´ecrire une application cliente compl`ete, ou du code destin´e `a ˆetre ex´ecut´e par le serveur. Il y a deux possibilit´es pour utiliser le SGBD `a partir d’un langage hˆote : API La premi`ere possibilit´e est de fournir une API plus ou moins sp´ecifique au SGBD (ODBC, JDBC pour Java, libpq pour C de Postgres, OCI pour Oracle, . . .), il suffit d’utiliser les primitives de l’API dans un programme traditionnel. SQL embarqu´ e La seconde, de loin la plus agr´eable, repose sur une extension du langage hˆote permettant d’´ecrire et d’exploiter tr`es naturellement des ordres du SGBD (des ordres SQL par exemple, et on parle alors de SQL embarqu´e ou embedded SQL). Le programme obtenu doit ˆetre trait´e par un pr´eprocesseur, en g´en´eral fourni par l’´editeur du SGBD, qui, entre autres choses, remplace les ordres embarqu´es par des appels `a une API sp´ecifique. Le nouveau programme obtenu est ´ecrit dans le langage hˆote d’origine et contient des appels `a une API, on est alors ramen´e `a la premi`ere possibilit´e. Exemples de pr´eprocesseurs : – Oracle : Pro*C/C++, Pro*COBOL, SQLJ, – Postgres : ecpg pour PostgreSQL ´ecrit en langage C, – le projet GNADE : SQL embarqu´e dans du Ada 95, avec des API ODBC, PostgreSQL et MySQL Avec le d´eveloppement de l’acc`es `a des bases de donn´ees via le r´eseau Internet, de nombreux environnements normalis´es ou non existent. Par exemple l’environnement Hibernate qui tend `a rendre transparent au programmeur la persistance des objets stock´es dans une base de donn´ees (http://www.hibernate.org/). 6
Chapitre 2 Introduction ` a la conception d’une base de donn´ ees (MCD) 2.1
Un MCD d’hˆ opital
Dans un MCD un rectangle est une entit´e et une ´eclipse ou un cercle est une association entre deux ou plusieurs entit´es. Une entit´e peut avoir plusieurs attributs qui d´ecrivent l’entit´ee. Une association est param´etr´ee par un nombre d’associations possible, une association peut poss´eder des attributs qui lui sont sp´ecifiques.
1 Service ids nom
1,n appartient n
Chambre idc nb_lits
1,n
lieu 1 numlit 1
soignant
Sejour idj date_entree
1
accueilli
0,n
date_sortie
Patient idp nom
Fig. 2.1 – Voici un exemple de MCD d´ecrivant la structure d’un hˆopital avec ses chambres et ses patients accueillis. Q.1 Y a-t-il de la redondance d’information dans le MCD de la figure 2.1 page 7
Une association qui a un 1 et un n sera implant´ee par une clef ´etrang`ere dans l’entit´e du cˆot´e 1. Voici une impl´ementation du MCD de l’hˆopital : create table Patient ( idp Serial, nom varchar (30), constraint Patient_PK primary key (idp) ) ; -- sequence "patient_idp_seq" create table Service ( ids Serial, nom varchar (30), constraint Service_PK primary key (ids) 7
) ; -- sequence "service_ids_seq" create table Chambre ( idc Serial, nb_lits Int4, service integer, constraint Chambre_PK primary key (idc), constraint Chambre_Service_FK foreign key (service) references Service (ids) ) ; -- sequence "chambre_idc_seq" create table Sejour ( idj Serial, date_entree Date, date_sortie Date, patient integer, service integer, chambre integer, numlit int2, constraint Sejour_PK primary key (idj), constraint Sejour_Patient_FK foreign key (patient) references Patient (idp), constraint Sejour_Service_FK foreign key (service) references Service (ids), constraint Sejour_Chambre_FK foreign key (chambre) references Chambre (idc) ) ; -- sequence "sejour_idj_seq" Remarquez que les tables r´ef´erenc´ees par une clef ´etrang`ere (foreign key) doivent exister avant que les tables r´ef´eren¸cantes soient cr´e´ees. De mˆeme si une clef ´etrang`ere est ins´er´ee dans une table r´ef´eren¸cante la ligne r´ef´erenc´ee doit exister (`a moins que la clef ´etrang`ere soit ind´efinie (is null). Un exemple o` u le patient est donn´e mais ni le service ni la chambre ne sont donn´es pour le s´ejour : insert into Patient (nom) values (’durif’) ; -- idp = 1 select * from patient ; idp | nom -----+------1 | durif (1 row) insert into Sejour (date_entree, date_sortie, patient) values (to_date(’23/10/2010’, ’DD/MM/YYYY’), to_date(’03/11/2010’, ’DD/MM/YYYY’), 1) ; select * from Sejour ; idj | date_entree | date_sortie | patient | service | chambre -----+-------------+-------------+---------+---------+--------1 | 2010-10-23 | 2010-11-03 | 1 | | (1 row) Le service et la chambre sont ind´efinis, le pr´edicat is null permet de le savoir : select * from Sejour where service is null and chambre is null; idj | date_entree | date_sortie | patient | service | chambre -----+-------------+-------------+---------+---------+--------1 | 2010-10-23 | 2010-11-03 | 1 | | (1 row) 8
´ Q.2 Ecrire une requˆete qui donne le nombre de patient `a une date donn´ee. Pour supprimer la base de donn´ees, il faut d’abord d´etruire les tables r´ef´eren¸cantes : drop drop drop drop
2.2
table table table table
Sejour ; Chambre ; Service ; Patient ;
Un MCD des diplˆ omes de l’universit´ e UE
Matiere idm libelle ects
1,n
Composition
1,n
idue semestre titre
1,n
Constitution
1,n
Parcours idp intitule valide
1
Delivre
1
Diplome idd nom
Fig. 2.2 – Voici un exemple de MCD d´ecrivant la structure des diplˆomes d’une universit´e. En revanche une association qui a un n et un n des deux cˆot´es sera implant´ee par une table dont chaque tuple aura deux clefs ´etrang`eres. Voici une implantation des diplˆomes : create table Diplome ( idd Serial, nom Varchar (20), constraint Diplome_PK primary key (idd) ) ; create table Parcours ( idp Serial, intitule Varchar (20), valide Boolean default FALSE not null, idd Integer, constraint Parcours_PK primary key (idp), constraint Parcours_Diplome_FK foreign key (idd) references Diplome (idd), constraint Parcours_Valide check (valide = ’n’ or valide = ’v’) ) ; create table Matiere ( idm Serial, libelle Varchar (20), ects integer, constraint Matiere_PK primary key (idm) ) ; create table UE ( idue Serial, semestre int4, titre Varchar (20), constraint UE_PK primary key (idue), constraint UE_semestre check (1 <= semestre and semestre <= 10) ) ; Le check garantit qu’un semestre est compris entre 1 et 10. Et par d´efaut un parcours est invalide.
9
Les associations en 1,n et 1,n : create table Composition ( idue integer, idm integer, constraint Composition_PK primary key (idue, idm), constraint Composition_UE_FK foreign key (idue) references UE (idue), constraint Composition_Matiere_FK foreign key (idm) references Matiere (idm) ) ; create table Constitution ( parcours integer, idue integer, constraint Constitution_PK primary key (parcours, idue), constraint Constitution_Parcours_FK foreign key (parcours) references Parcours (idp), constraint Constitution_UE_FK foreign key (idue) references UE (idue) ) ; Remarquez que les tables r´ef´erenc´ees par une clef ´etrang`ere (foreign key) doivent d´ej`a exister. Il faut d’abord d´etruire les tables r´ef´eren¸cantes (contenant des foreign key) : drop table Constitution ; drop table Composition ; drop table UE ; drop table Matiere ; drop table Parcours ; drop table Diplome ; ´ Q.3 Ecrire une requˆete qui permet de voir que certaines UE ont plus de 5 ECTS.
Exercice 1 Sur les courses Voici les trois tables : create table Coureur ( id_coureur Serial constraint Coureur_PK primary key, nom Varchar (20), age Int2 default 0 check (age >= 0) ) ; create table Course ( id_course Serial constraint Course_PK primary key, date_course Date, lieu Varchar (20), age_minimum Int2 default 0 check (age_minimum > 0), nbMaxCoureurs Int4 -- nombre maximum de coureurs ) ; create table Inscription ( id_course Integer, id_coureur Integer, constraint Inscription_PK primary key (id_course, id_coureur), constraint Inscription_Course_FK foreign key (id_course) references Course (id_course), constraint Inscription_Coureur_FK foreign key (id_coureur) references Coureur (id_coureur) ) ; Voici le garnissage des deux premi`eres tables : 10
insert insert insert insert insert select
into Coureur (nom, into Coureur (nom, into Coureur (nom, into Coureur (nom, into Coureur (nom, * from Coureur ;
age) age) age) age) age)
values values values values values
(’toto’, (’titi’, (’tete’, (’tata’, (’tutu’,
\set DateStyle ’DMY’ insert into Course (date_course, lieu, values (CAST(’03/09/2010’ as Date), insert into Course (date_course, lieu, values (CAST(’03/10/2010’ as Date), select * from Course ;
Q.1 Donner le MCD correspondant aux tables pr´ ec´edentes Q.2 Dans quel ordre peut-on d´ etruire les trois tables ?
Une solution pour ´eviter qu’une personne ne puisse s’inscrire `a une course si elle est trop jeune est de mettre en place un trigger qui d´etectera cette erreur en empˆechera l’inscription : CREATE or replace FUNCTION agerespecte() RETURNS TRIGGER AS $agerespecte$ DECLARE age_min Int2 ; age_courant Int2 ; BEGIN select age_minimum into age_min from Course where id_course = NEW.id_course ; select age into age_courant from Coureur where id_coureur = NEW.id_coureur ; if age_courant < age_min then RAISE EXCEPTION ’Trop jeune : age coureur % < age min %’, age_courant, age_min; end if; return NEW; END ; $agerespecte$ LANGUAGE plpgsql; CREATE TRIGGER agerespecte AFTER INSERT OR UPDATE ON Inscription FOR EACH ROW EXECUTE PROCEDURE agerespecte(); insert into Inscription values (1, 1); -insert into Inscription values (1, 2); -insert into Inscription values (1, 3); -ERROR: Trop jeune : age coureur 13 < age
OK OK ERREUR min 15
select * from Inscription ; id_course | id_coureur -----------+-----------1 | 1 1 | 2 On voit que le coureur num´ero 3 n’est pas inscrit car il est trop jeune. Le trigger traitera toute ligne ajout´ee (insert) ou modifi´ee (update).
11
Chapitre 3 Cr´ eation d’une table et ses contraintes d’int´ egrit´ e en SQL DDL = Data Definition Language D`es la d´eclaration d’une table on peut fixer un certain nombre de propri´et´es sur les valeurs que peuvent prendre les attributs.
3.1
Cr´ eation des tables create table ( ) ; create table Client ( idc Serial, nom Varchar (20) constraint Client_Nom_Defini not null, prenom Varchar (20), solde Numeric (6, 2) default 0.0, constraint Client_PK primary key (idc) ) ; insert into Client (solde) values (100.55) ; ERROR: null value in column "nom" violates not-null constraint -- le idc Serial a ´ et´ e incr´ ement´ e insert into Client (nom, solde) values (’Toto’, 100.55) ; insert into Client (nom) values (’Titi’) ; select * from client ; idc | nom | prenom | solde -----+------+--------+-------2 | Toto | | 100.55 3 | Titi | | 0.00
La clause default n’est pas une contrainte, elle provoque simplement l’introduction de la valeur par d´efaut lors d’un insert ne pr´ecisant pas de valeur explicite.
3.2
Les commentaires
Un commentaire est introduit par -- et se termine en fin de la ligne. 12
3.3
Les types de donn´ ees
3.3.1
Le type bool´ een
PostgreSQL dispose du type BOOLEAN avec les valeurs TRUE et FALSE not´ee dans l’affichage d’une requˆete par t ou f : select idc, nom, solde = 100.55 as a_ce_solde from Client ; idc | nom | a_ce_solde -----+------+-----------2 | Toto | t 3 | Titi | f En PostgreSQL c’est l’absence de valeur is null qui dit que la colonne n’a pas de valeur, on a aussi is not null qui est vrai si la colonne a une valeur. Les types d´efinis par la norme ne sont malheureusement pas toujours respect´es.
3.3.2
Types num´ eriques
Numeric : possiblement des entiers ou des r´ eels, et Real Real est un r´eel de 4 octets. Numeric (precision, scale) le nombre de chiffres d´ecimaux est donn´e par precision, scale correspond au nombre maximum de chiffre apr`es la virgule : – si scale est positif on a scale chiffres d´ecimaux apr`es la virgule, donc le nombre de chiffres avant la virgule est de precision - scale. chiffres d´ecimaux. – si scale vaut 0 ou est absent on a un entier de precision chiffres d´ecimaux. create table Bof (prix Numeric (5, 2)) ; -- un r´ eel avec 2 chiffre apr` es le point insert into Bof values (111.55) ; select * from Bof ; prix -------111.55 create table BofBof (prix Numeric (5, 0)) ; -- un entier insert into BofBof values (11155) ; select * from BofBof ; prix ------11155 create table BofBofBof (prix Numeric (5)) ; -- un entier insert into BofBofBof values (11155) ; select * from BofBofBof ; prix ------11155 insert into BofBofBof values (111555) ; -- trop de chiffres ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^5 for field with precision 5, scale 0. 13
– Numeric (p) nombre entier, qui signifie Numeric (p, 0) Int8, Int4, Int2, SmallInt, Integer, Bigint Il – – –
Serial Une colonne de table ayant le type Serial se voit g´en´erer un compteur qui sera incr´ement´e `a partir de 1 lors de chaque insertion (insert). Serial peut ˆetre bien pratique pour la g´en´eration de clef primaire : le type Serial correspond `a un compteur qui sera incr´ement´e `a chaque insertion, sa premi`ere valeur sera 1 : create table Jouet ( idj Serial, nom Varchar (20), constraint Jouet_PK primary key (idj) ) ; insert into Jouet (nom) values (’Cheval’) ; insert into Jouet (nom) values (’Cartes’) ; select * from Jouet ; idj | nom -----+-------1 | Cheval 2 | Cartes
3.3.3
Types chaˆınes de caract` eres
Pour PostgreSQL Varchar (n) chaˆınes de taille variable et de longueur inf´erieure ou ´egale `a n. Character (n) de taille forc´ement ´egale `a n ou remplie d’espace si elle est trop courte. Text une chaˆıne de longueur quelconque. Char Length () une fonction qui donne la longueur de la chaˆıne.
3.3.4
Types temporels
Pour PostgreSQL – Date = ann´ee-mois-jour On dispose des fonctions : – la fonction To date(text, text), par exemple to_date(’05 12 2010’, ’DD MM YYYY’) : select to_date(’05 12 2010’, ’DD MM YYYY’) from client where idc = 2; to_date -----------2010-12-05 14
– La fonction Current date donne la date courante du syst`eme. – La fonction Current time donne l’heure courante du syst`eme avec le type time.
3.4
Les contraintes
D´eclar´ees `a la cr´eation de la table, puis v´erifi´ees automatiquement par le SGBD : – programmation all´eg´ee – s´ecurit´e plus forte PostgreSQL v´erifie les contraintes lors de l’ex´ecution d’une instruction DML (de modification) compl`etement termin´ee, si elle n’est pas respect´ee la modification est annul´ee. Si une contrainte n’est pas v´erifi´ee en fin d’instruction DML, il y a annulation de la mise `a jour avec message d’erreur. Plus pr´ecis´ement, la table est remise dans l’´etat dans lequel elle ´etait avant le d´ebut de l’instruction DML (fonctionnement en tout ou rien).
3.4.1
Baptisez vos contraintes !
Chaque contrainte peut ˆetre baptis´ee (et on a toujours int´erˆet `a le faire), elle pourra ensuite ˆetre manipul´ee facilement par certaines commandes simplement en donnant sont nom. Le nom d’une contrainte est donn´e apr`es le mot-clef constraint : constraint La contrainte sera alors v´erifi´ee en fin de l’instruction modifiant la table (insert, update ou delete).
3.4.2
Aspects syntaxiques
SQL distingue deux syntaxes pour d´ecrire les contraintes : les contraintes de colonnes et les contraintes de table. Syntaxe contrainte de colonne
Syntaxe contrainte de table Une contrainte de table peut porter sur plusieurs colonnes, elle est indiqu´ee comme un ´el´ement de la liste des colonnes de la table :
Une contrainte de colonne porte sur exactement create table Commande ( produit Numeric (5), une colonne (par exemple la contrainte not client Numeric (5), null) est indiqu´ee au moment de la d´eclaration quantite Numeric (5) default 0, de la colonne et on peut en mettre plusieurs : constraint Commande_PK primary key (produit, client), create table Produit ( constraint Commande_Produit_FK id Serial foreign key(produit) references Produit(id), constraint Produit_PK primary key, constraint Commande_Client_FK nom Varchar (10), foreign key(client) references Client(idc), stock Numeric (5) default 0 constraint Quantite_Positive constraint Produit_stock_defini check (quantite >= 0)) ; not null, constraint Stock_Positif Le default introduit une valeur d’initialisation check (stock >= 0)) ; des colonnes stock de Produit et quantite de Deux contraintes portent sur la colonne stock. Commande quand on ne leur donne pas de valeur. Fig. 3.1 – Les deux mani`eres de d´eclarer des contraintes 15
La seule contrainte qui ne peut ˆetre d´ecrite qu’en tant que contrainte de colonne est not null car elle qualifie toujours une seule colonne. Une autre contrainte exprimable dans les deux syntaxes est primary key pouvant s’appliquer `a plusieurs colonnes. Chaque valeur de la clef primaire doit ˆetre unique et d´efinie dans la table contenant ces lignes. Lors de la cr´eation (insert) d’une commande il faut que le produit r´ef´erenc´e par la clef ´etrang`ere produit et le client r´ef´erenc´e par la clef ´etrang`ere client existent d´ej`a, sinon il y aura une erreur et l’insertion ne sera pas faite. Lors d’un update on aura le mˆeme comportement si produit ou client n’existe pas dans leur table.
3.4.3
Liste des contraintes
not null l’attribut doit toujours avoir une valeur d´efinie, c’est la seule contrainte qui ne peut s’´ecrire qu’en contrainte de colonne. default : donne la valeur de son expression `a la colonne si la valeur donn´ee est ind´efinie (is null). primary key Aucune des colonnes de la clef primaire ne peut ˆetre ind´efinie et chaque ligne doit avoir des valeurs diff´erentes (PostgreSQL cr´ee un index unique pour cette contrainte). unique Sur une colonne ou un groupe de colonnes dont la valeur, quand elle est d´efinie, doit ˆetre unique dans la table (PostgreSQL cr´ee un index unique pour cette contrainte). Des colonnes de unique peuvent ˆetre ind´efinies. Par exemple si on pose la contrainte unique (formation, rang) les deux couples (1, 23) et (1, 24) sont bien distincts, en revanche (1, null) et (1, null) seront consid´er´es par PostgreSQL comme des valeurs diff´erentes. L’unicit´e, par exemple (null, null) et (null, null) sont consid´er´es comme diff´erents. foreign key et une r´ef´erence sur un ´el´ement d’une autre table, les colonnes r´ef´erenc´ees doivent ˆetre soit une clef primaire soit unique. Si lors d’un insert (ajout d’une ligne) ou d’un update (mise `a jour d’une ligne existante la clef n’existe pas dans la table r´ef´erenc´ee, la modification ´echouera). check v´erifie que la condition donn´ee `a droite est vraie, sinon on a un ´echec de l’insert ou du update. La contrainte unique Cette contrainte admet que des colonnes uniques puissent ˆetre ind´efinies (is null). Si les colonnes sont d´efinies elles doivent ˆetre uniques dans la table, sinon une erreur sera provoqu´ee. La syntaxe est unique(nom_col_1{, nom_col_autre}). create table X ( a Numeric (5), b Numeric (5), c Numeric (5), constraint X_PK primary key (a), constraint Unicite Unique (b, c) ) ; En revanche si b et c sont ind´efinis sur plusieurs lignes, ces lignes seront accept´ees. Les autres contraintes peuvent ˆetre d´ecrites indiff´eremment en tant que contrainte de colonne ou contrainte de table ce sont unique, primary key, foreign key, check et not null. L’exemple de droite garantit qu’on ne pourra pas avoir deux fois les mˆemes valeurs de b et c `a cause de la contrainte unique.
– PostgreSQL respecte la norme SQL, c’est `a dire qu’il consid`ere (1, null) et (1, null) comme distincts. 16
create table T ( formation int4, rang int4, constraint Unicite ) ; insert into T values insert into T values insert into T values insert into T values insert into T values
select * from T ; formation | rang -----------+-----1 | 1 | | | | On a bien 5 lignes en postgreSQL. – check pr´edicat portant sur les colonnes d’un mˆeme nuplet check (qte >= 0) check (date_debut <= date_fin) check (couleur IN (’BLANC’, ’VERT’, ’ROUGE’)) create table Couleur ( c varchar (20), constraint autorise check (c in (’BLANC’, ’VERT’, ’ROUGE’)) ) ; insert into Couleur values (’VERT’) ; insert into Couleur values (’vert’) ; -- erreur, pas d’insertion insert into Couleur values (null) ; -- OK car peut ^ etre ind´ efini select * from Couleur ; c -----VERT (2 rows) Restrictions PostgreSQL : le pr´edicat doit porter uniquement sur la valeur de la ligne courante, on ne peut pas y mettre une sous-requˆete. Si la condition de check est vraie ou is null (pr´esomption d’innocence) la propri´et´e est consid´er´ee comme respect´ee et la mise `a jour est accept´ee. ` votre avis, le delete provoque-t-il la v´erification des contraintes not null et check ? Q.3 A Q.4 Ce mˆ eme delete a-t-il des v´erifications `a faire quand il y a des contraintes primary key et
unique et si la table est r´ef´erenc´ee par une clef ´etrang`ere (foreign key). Lesquelles ? Si la condition d’un check s’´evalue `a UNKNOWN alors la contrainte est consid´er´ee comme satisfaite. Par exemple : 17
check (salaire > 0 or (salaire = 0 and commission > 0))
Q.5 Montrer que si salaire is null la mise ` a jour est accept´ee quel que soit l’´etat de commission.
L’id´ee est qu’on ne peut pas empˆecher la cr´eation d’un nuplet en l’absence d’information (pr´esomption d’innocence). Q.6 Si commission n’est pas d´ efinie, le salaire peut-il ˆetre n´egatif ? Q.7 Corriger la contrainte pour garantir que le salaire et la commission ne sont jamais n´ egatifs
(une id´ee consiste `a utiliser l’op´erateur is null, une autre id´ee `a mettre plusieurs check). D´ efinition de nouveaux domaines en PostgreSQL En SQL2 et PostgreSQL oui, mais pas en Oracle : create domain Quantite Integer default 0 check (value >= 0) ; create table Q (qte_produit Quantite) ; insert into Q values (5) ; insert into Q values (-1) ; --erreur : non insertion ERROR: value for domain quantite violates check constraint "quantite_check" select * from Q ; qte_produit ------------5 Un exemple de domaine en PostgreSQL : create domain Couleurs_Additives as Text default ’bleu’ constraint Couleurs_Additives_CHK check (upper (value) in (’ROUGE’, ’VERT’, ’BLEU’)) ; -- liste des domaines avec la commande suivante : \dD create table T (id int primary key, c Couleurs_Additives) ; select * from T ; insert into T (id) values (1) ; insert into T (id, c) values (2, ’vert’) ; insert into T (id, c) values (3, ’ROUGE’) ; insert into T (id, c) values (4, ’verte’) ; -- echec select * from T ; id | c ----+------1 | bleu 2 | vert 3 | ROUGE
3.4.4
Contraintes d’int´ egrit´ e d’entit´ e : clef primaire
Il s’agit des clefs primaires create table Contient ( commande Numeric (4), produit Numeric (4), quantite Numeric (4), 18
constraint Contient_PK primary key (commande, produit), constraint Quantite_Positive check (quantite > 0) ) ; insert into contient values (1, 2, 0); ERROR: new row for relation "contient" violates check constraint "quantite_positive" Les colonnes de la clef primaire doivent ˆetre d´efinies et les clefs primaires forment un ensemble (unicit´e de chaque valeur de clef primaire qui peut-ˆetre constitu´ee de plusieurs colonnes). Sous PostgreSQL (et d’autres), un index unique est automatiquement cr´e´e sur la clef primaire, il prend le nom de la contrainte (Produit_PK dans l’exemple). Table sans clef primaire En th´eorie, une table sans clef primaire peut contenir la mˆeme valeur sur plusieurs lignes : En PostgreSQL il est possible de d´efinir une table sans clef : create table Sans_Clef (num Int4) ; insert into Sans_Clef values (1) ; insert into Sans_Clef values (1) ; select * from Sans_Clef ; num ----1 1 (2 rows) et on pourra y ins´erer plusieurs nuplets de mˆeme valeur.
3.4.5
Contraintes d’int´ egrit´ e r´ ef´ erentielle : clef ´ etrang` ere
create table Etudiant ( id Serial, nom Varchar (20), constraint Etudiant_PK primary key (id) ) ; create table Note ( note Numeric (5, 2) not null, etudiant Integer, constraint Note_Etud_FK foreign key (etudiant) references Etudiant (id) ) ; Le fait que la colonne Note.etudiant est une clef ´etrang`ere implique que la table Note d´epend de la table Etudiant. Autrement dit la table Note ne peut ˆetre cr´e´ee que quand la table Etudiant existe. Ensuite la table Etudiant ne pourra ˆetre d´etruite avec drop table car la table Note en d´epend. Consid´erons une ligne de la table Note : – si sa colonne etudiant est d´efinie, il doit exister exactement une ligne de Etudiant dont le id doit exister dans une ligne de Etudiant. L’unicit´e de Etudiant.id est garantie puisque c’est justement la clef primaire. – si sa colonne etudiant est ind´efinie (is null), c’est qu’elle ne r´ef´erence aucune ligne de Etudiant et l’insert ou l’update sera accept´e. La colonne Note.etudiant est alors appel´ee une clef ´etrang`ere, on peut aussi la comprendre comme un pointeur associatif qui n’est pas une adresse m´emoire mais une valeur permettant de retrouver la ligne d´esign´ee de la table Etudiant. Par exemple la table Etudiant ´etant vide : 19
insert into note values (12.5, 1); ERROR: insert or update on table "note" violates foreign key constraint "note_etud_fk" DETAIL: Key (etudiant)=(1) is not present in table "etudiant". Une cons´equence du exactement une ligne de la table Etudiant est que la colonne id doit garantir l’unicit´e des lignes de Etudiant : id doit soit ˆetre une clef primaire soit supporter une contrainte d’unicit´e (unique). Une clef ´etrang`ere peut-ˆetre constitu´ee de plusieurs colonnes : ces colonnes ne r´ef´erencent une ligne que si elles toutes d´efinies. Une table peut se r´ef´erencer elle-mˆeme : create table Employe ( id Numeric (4), nom Varchar (20) constraint nom_not_null not null, superieur Numeric (4), constraint Employe_PK primary key (id), constraint Employe_Superieur_FK foreign key (superieur) references Employe (id) ) ; insert into Employe values (1, ’Toto’, null); insert into Employe values (2, ’Titi’, 1) ; select * from Employe ; -- Les sup´ erieurs select chef.* from Employe e inner join Employe chef on chef.id = e.superieur ; id | nom | superieur ----+------+----------1 | Toto | -- Toto n’a pas de sup´ erieur Remarquez que dans select on a ´ecrit chef.* ce qui ne donne que les informations sur le sup´erieur. Voici ce qu’on obtiendrait si on avait not´e * : select * from Employe e inner join Employe chef on chef.id = e.superieur ; id | nom | superieur | id | nom | superieur ----+------+-----------+----+------+----------2 | Titi | 1 | 1 | Toto | Une clef ´etrang`ere doit r´ef´erencer une clef primaire. Suivent quelques manipulations dont certaines sont erron´ees. On peut noter un ´ etudiant non d´ efini ! insert into Note (etudiant) values (13) ; ERROR: null value in column "note" violates not-null constraint On ne peut pas noter un ´ etudiant qui n’existe pas insert into Note (note, etudiant) values (13, 111) ; 20
ERROR: insert or update on table "note" violates foreign key constraint "note_etud_fk" DETAIL: Key (etudiant)=(111) is not present in table "etudiant". On ne peut pas modifier la clef cible d’un ´ etudiant not´ e insert select insert select update ERROR:
into Etudiant (nom) values (’Dupont’) ; -- id=1 * from Etudiant ; into Note values (12.5, 1) ; * from Note ; Etudiant set id = 666 where nom = ’Dupont’ ; update or delete on "etudiant" violates foreign key constraint "note_etud_fk" on "note" DETAIL: Key (id)=(1) is still referenced from table "note". insert into Etudiant values (666, ’Grand’) ; select * from Etudiant ; id | nom -----+-------1 | Dupont 666 | Grand Cela posera un probl`eme car la valeur de Serial de id finira par atteindre la valeur 666. Modification de contrainte pour propager la mise ` a jour Possible en PostgreSQL. On ne peut pas supprimer un ´ etudiant not´ e delete from etudiant where id = 666 ; -- OK car 666 n’a pas de note select * from Etudiant ; delete from etudiant where id = 1 ; ERROR: update or delete on "etudiant" violates foreign key constraint "note_etudiant_fk" DETAIL: Key (id)=(1) is still referenced from table "note".
3.4.6
Clef ´ etrang` ere et modifications de la table maˆıtre
SQL permet de maintenir automatiquement la coh´erence des clefs ´etrang`eres lorsqu’on modifie la table r´ef´erenc´ee (ou table maˆıtre). Pour cela il propose un certain nombre de comportements, qui ne sont pas tous impl´ement´es par Oracle : SQL Commentaire Oracle (10.2) PostgreSQL on delete|update no action Modification interdite (´echec de par d´efaut par d´efaut (par d´efaut) l’instruction). on delete cascade Suppression propag´ee : les nuplets oui oui r´ef´eren¸cant sont supprim´es on update cascade Modification propag´ee. non oui on delete|update set null La r´ef´erence devient ind´efinie. oui oui on delete|update set default La r´ef´erence est remise `a sa valeur non oui par d´efaut. 21
Un tel comportement est indiqu´e lors de la d´eclaration d’une clef ´etrang`ere, ainsi on peut avoir des clefs ´etrang`eres ayant la mˆeme cible et n’ayant pas le mˆeme comportement. Ces comportements sont des compl´ements optionnels `a ajouter `a la d´efinition d’une clef ´etrang`ere. Red´ efinition de contrainte pour propager la suppression on delete cascade figure 3.1 alter table Note drop constraint Commande_Produit_FK ; alter table Note add (constraint Commande_Produit_FK foreign key (produit) references Produit (id) on delete cascade) ; select n.note, Coalesce (e.nom, ’anonyme’) as from Note n left outer join Etudiant e on n.etudiant NOTE NOM 13 Dupont 10 Dupont 13 anonyme delete from etudiant where e.nom is null ; -select n.note, Coalesce (e.nom, ’inconnu’) as from Note n left outer join Etudiant e on n.etudiant NOTE NOM 13 Dupont 10 Dupont
3.5
nom = e.id ;
OK nom = e.id ;
Modification du sch´ ema : alter table
alter table permet : ajouter/supprimer/modifier la d´efinition d’une colonne ajouter/supprimer des contraintes
3.5.1
Ajouter, Modifier ou Supprimer une colonne ou une contrainte : alter table
alter table add {, } ; alter table modify {, } ; alter table drop ; Ajouter une ou plusieurs colonnes et contraintes : add (...) create table Client (id Numeric (5)) ; alter table Client add nom Varchar (20) constraint nom_defini not null ; insert into client (id) values (1) ; ERROR: null value in column "nom" violates not-null constraint insert into client values (1, ’Toto’) ; select * from Client ; id | nom 1 | Toto
insert into Client (id, nom) values (1, ’Toto’) ; insert into Client (id, nom) values (1, ’Titi’) ; -- pas d’insertion : ERROR: duplicate key violates unique constraint "client_pk" select * from Client ; id | nom | tel | habitation | solde 1 | Toto | | Lille | Suppression de colonne et/ou contraintes : drop Suppression d’une contrainte nomm´ ee
Suppression d’une colonne select * from client; alter table Client drop constraint tel_unique ; id | nom | habitation | solde alter table Client drop column tel ; ----+------+------------+------1 | Toto | Lille |
3.5.2
Suppression d’une Table
Le probl`eme des d´ependances dues aux clef ´etrang`eres : create table Maitre ( idm Numeric (3) constraint Maitre_PK primary key) ; create table Esclave ( ide Numeric (3) constraint Esclave_PK primary key, maitre Numeric (3), constraint Esclave_Maitre_FK foreign key (maitre) references Maitre (idm) ) ; La suppression drop table Maitre ; -- ´ echec de destruction ! NOTICE: constraint esclave_maitre_fk on table esclave depends on table maitre ERROR: cannot drop table maitre because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. La contrainte de clef ´etrang`ere Esclave_Maitre_FK empˆeche la destruction de la table Maitre. La destruction de la table Maitre ´echoue si la table est r´ef´erenc´ee par des clefs ´etrang`eres (mˆeme si elle est vide). Voici la syntaxe : drop table Effets : – enl`eve la d´efinition de la table du dictionnaire, – tous les index et triggers associ´es sont d´etruits, – les vues qui utilisent la table d´etruites doivent ˆetre d´etruites avant la destruction de la table. – la place occup´ee par la table est restitu´ee. – Le drop table ´echoue si la table est r´ef´erenc´ee par une clef ´etrang`ere d’une autre table et son contenu est inchang´e.
3.5.3
drop table ... cascade
Remarquez que la suppression avec cascade d´etruit 23
drop table Maitre Cascade ; NOTICE: drop cascades to constraint Esclave_Maitre_FK on table esclave -- La table Maitre a disparue ainsi que la contrainte Esclave_Maitre_FK Un exemple de cr´ eation de table avec erreur d’insertion En PostgreSQL : create table Ville ( idv int4, nom Varchar (50), departement int4, population int4 default 0, constraint Ville_PK primary key (idv), constraint Ville_Dpt_Intervalle check (departement between 1 and 100), constraint Ville_Pop_Val check (0 <= population) ) ; insert into ville (idv, nom, departement) values (1, ’Paris’, 75); insert into ville (idv, nom, departement) values (1, ’Lyon’, 69); ERROR: duplicate key violates unique constraint "ville_pk" insert into ville (idv, nom, departement) values (2, ’Limoges’, 169); ERROR: new row for relation "ville" violates check constraint "ville_dpt_intervalle" select * from Ville ; idv | nom | departement | population 1 | Paris | 75 | 0 Cet ordre create cr´ee la table Ville dont le sch´ema, d´ecrit entre les parenth`eses, est compos´e de quatre attributs et comporte aussi des contraintes permettant de garantir les propri´et´es : – constraint Ville_PK primary key (idv) garantit que deux lignes de Ville auront toujours une valeur d´efinie et diff´erente pour la colonne idv. De fa¸con plus consise on dit que idv est la clef primaire de Ville. La tentative d’ajouter dans la table Ville une ville dont idv existe d´ej`a dans une ligne de Ville ´echouera et la valeur de Ville sera inchang´ee. – constraint Ville_Dpt_Intervalle check (departement between 1 and 100) garantit que la colonne departement aura une valeur comprise entre 1 et 100 si elle est d´efinie. La tentative d’ajouter dans la table Ville une ville dont departement vaut 169 ´echouera et la valeur de Ville sera inchang´ee. – default 0 donne par d´efaut la valeur 0 `a population. – constraint Ville_Pop_Val check (0 <= population) garantit que que la colonne population aura une valeur positive ou nulle quand elle est d´efinie : la tentative d’ajouter dans la table Ville une ville `a population n´egative ´echouera et la valeur de Ville sera inchang´ee. Une table SQL ressemble `a une variable relationnelle mais avec quelques diff´erences dont la premi`ere est importante : – la valeur d’une variable relationnelle ne peut pas comporter plusieurs fois le mˆeme n-uplet alors qu’une table — sauf si on pose explicitement une contrainte de clef primaire — peut comporter plusieurs lignes identiques, sauf les clefs primaires ! – un ´el´ement d’une relation s’appelle un n-uplet, alors qu’un ´element d’une table s’appelle une ligne (ou row en anglais). – il est possible en SQL qu’une colonne n’ait pas de valeur, on dit qu’elle est ind´efinie et cela se teste avec l’op´erateur bool´een is null. En revanche cela n’aurait pas de sens pour une relation car cela correspondrait `a un n-uplet auquel il manque un attribut, ce qui n’aurait pas de sens en th´eorie.
24
Chapitre 4 Implantation d’un MCD 4.1
Passage du mod` ele Entit´ e Association au mod` ele relationnel
Chaque entit´e est traduite par une table ayant les attributs et la clef de l’entit´e. Pour les associations, on a plusieurs cas : 1-1 Soit on fusionne les 2 entit´es en une seule table, soit on conserve deux tables en recopiant la clef d’une des deux dans l’autre en tant que clef ´etrang`ere ou de fa¸con plus sym´etrique chaque table re¸coit la clef de l’autre ; les attributs de l’association sont mis dans une des deux tables. 1-n On copie dans la table fille (celle qui participe `a au plus une association) la clef de la table parente en tant que clef ´etrang`ere ainsi que les attributs de l’association. La table parente peut ˆetre r´ef´erenc´ee par plus d’une fille. n-n L’association est traduite par une nouvelle table dont la clef est form´ee des clefs des diff´erentes entit´es li´ees par cette association. Cette table contient aussi les attributs ´eventuels de l’association. En r´esum´e, le MCD : Ville idv nom departement population
Client 1,n
Habite
1
idc
Commande 0,n
Passe
1
nom
idcom la_date
Produit 1,n
Pour quantité
0,n
idp nom prix
sera traduit dans les relations : Table Ville Table Client Table Produit Table Commande Association Pour
(idv, nom, departement, population) (idc, nom, ville →Ville.idv) (idp, nom, prix) (idcom, client →Client.idc,la date) (commande →Commande.idcom, produit →Produit.idp, quantite)
O` u: – c1, ...,ck est une clef primaire – ville; , client , commande et produit sont des clefs ´etrang`eres d´esignant un nuplet d’une autre table, par exemple la colonne client d’une commande doit soit ˆetre ´egale `a la colonne id d’au plus un des clients soit ˆetre ind´efinie. On peut comprendre une clef ´etrang`ere comme un pointeur associatif. Une clef ´etrang`ere peut bien entendu ˆetre constitu´ee de plusieurs colonnes, dans ce cas elle r´ef´erence autant de colonnes de la table r´ef´erenc´ee. Puisque pas plus d’une ligne de la 25
table r´ef´erenc´ee ne doit ˆetre d´esign´ee par une clef ´etrang`ere, il faut que les colonnes r´ef´erenc´ees par une clef ´etrang`eres garantissent l’unicit´e des lignes : elles doivent soit ˆetre la clef primaire de la table r´ef´erenc´ee soit ˆetre l’objet de la contrainte unique. Ces r`egles sont bien entendu appliqu´ees par Oracle et PostgreSQL et certainement beaucoup d’autres SGBD. Depuis sˆ urement assez longtemps MySQL accepte la syntaxe de d´eclaration de clef ´etrang`ere, il n’en assure la s´emantique que depuis sa version 6 et uniquement dans InnoDB. Ces deux notations se traduisent en SQL par des contraintes exprim´ees lors de la cr´eation des tables correspondant aux relations, par exemple : create table Ville ( idv Serial, nom Varchar (20), departement Numeric (3), population Numeric (10), constraint Ville_PK primary key (idv) ) ; create table Client ( idc Serial, nom Varchar (20), ville Numeric (3), constraint Client_PK primary key (idc), constraint Client_Ville_FK foreign key (ville) references Ville (idv) ) ; create table Commande ( idcom Serial, client Integer, la_date Date, constraint Commande_PK primary key (idcom), constraint Commande_Client_FK foreign key (client) references Client (idc) ) ; create table Produit ( idp Serial, nom Varchar (20), prix Numeric (10, 2), -- 2 indique 2 chiffres apr` es la virgule constraint Produit_PK primary key (idp) ) ; create table Pour ( commande Integer, produit Integer, quantite Numeric (5), constraint Pour_PK primary key (commande, produit), constraint Pour_Commande_FK foreign key (commande) references Commande (idcom), constraint Pour_Produit_FK foreign key (produit) references Produit (idp) ) ; L’ordre de cr´eation des tables n’est pas quelconque : une table r´ef´eren¸cante ne peut ˆetre cr´e´ee que si les tables qu’elle r´ef´erence l’ont d´ej`a ´et´e. Q.8 Combien aura-t-on de chiffres avant la virgule pour le prix d’un Produit. Q.9 Trouver un autre ordre de cr´ eation en SQL des cinq tables ?
´ Q.10 Ecrire une requˆete qui donne le nombre de commandes par client. 26
´ Q.11 Ecrire l’implantation en tables du MCD suivant :
0,n
0,1
Camion
Localite
cdl
0,n
0,1
ville dpt
0,n
Produit
Envoi
0,n
cdm
cdp 0,n quantité 0,n Client libellé cdc nom 0,1
Q.12 Voici le MCD du parc de voitures empruntables dans une entreprise, donnez-en une implan-
tation : Voiture idv marque nbPlaces
0,n
De
1
Réservation idr jourDebut jourFin
1
Reserve 0,n Employe
0,1 0,n
PrévuPar 1 Emprunt jourDebut jourFin
1
Emprunte
27
ide nom
1
Dans
1,n
Service ids libelle
Chapitre 5 SQL/DML les ordres de modification du contenu des tables : insert update delete SQL signifie Structured Query Language SQL = {DDL, DML, DCL} DML = Data Manipulation Language
5.1
insert : ajout de nouvelles lignes
Pour ajouter de nouvelles lignes. insert into [(col1, ..., coln)] values (val1, ..., valn) ; ou insert into [(col1, ..., coln)] ; [(col1, ..., coln)] est optionnel `a cause des crochets []. Les colonnes non mentionn´ees dans (col1, ..., coln) sont ind´efinies ou ont leur valeur par d´efaut ou sont ind´efinies (motclefis null). On peut donner l’ordre des colonnes de (col1, ..., coln) dans n’importe quel ordre mais `a condition de respecter cet ordre dans (val1, ..., valn). Exemple : – Insertion d’une ligne en explicitant la valeur de toutes les colonnes dans l’ordre de leurs d´eclarations : insert into Client values (4, ’Durif’, ’Philippe’, 300) ; On peut explicitement indiquer qu’une colonne n’est pas d´efinie (is null) en mettant null pour signifier l’absence de valeur. – Insertion d’une ligne en explicitant les valeurs d’un sous-ensemble des colonnes de la table : insert into Client (num_client, nom, prenom) values (5, ’Durif’, ’Pablo’) ; Les colonnes non mentionn´ees seront ind´efinies ou bien auront leur valeur par d´efaut ´eventuellement indiqu´ee lors de la cr´eation de la table (default). – Insertion dans Client du r´esultat d’une requˆete donnant des employ´es ayant un salaire > 1000 : insert into Client (num_client, nom, prenom) select ref, nom, prenom from Employe where salaire > 1000 ; 28
Le mot clef default peut ˆetre utilis´e en tant que valeur d’une colonne et indique que la colonne doit prendre sa valeur par d´efaut si on ne lui donne pas de valeur (voir create table section 3.1 page 12) ou ˆetre ind´efinie si elle n’a pas de valeur par d´efaut.
5.2
update : la mise ` a jour de lignes existantes
Pour modifier des lignes existantes. update set affectation {, affectation} [where condition] ; affectation ::= colonne = expression | (col1, ..., colp) = (sous-requ^ ete-1-ligne-p-colonnes) Attention : la sous-requˆete ´eventuelle ne doit pas porter sur la table en cours de modification sinon on aura une erreur de table mutante. Dans l’expression `a droite de =, solde a l’ancienne valeur du client. Exemple, augmentation du solde des clients ayant un num´ero inf´erieur `a 4 : update Client
set solde = solde + 100
where num_client < 4 ;
L’ancien solde des clients dont le num_client est inf´erieur `a 4 est utilis´e dans l’expression solde + 100. Exemple avec une liste de colonnes : create table Departement ( deptno Numeric (5) primary key, prefecture Varchar (10) not null unique ) ; create table Employe ( id Numeric (5) primary key, deptno Numeric (5), salaire Numeric (10, 2), commission Numeric (10, 2), constraint Employe_Departement_FK foreign key (deptno) references Departement (deptno) -- clef ´ etrang` ere ) ; On veut d´eplacer sur Paris les employ´es des d´epartements de Lille et Lyon en doublant leurs salaires et en leur accordant une commission de 500 euros. Voici un exemple : insert insert insert select insert insert
into Departement values into Departement values into Departement values * from Departement ; into Employe values (1, into Employe values (2,
On regarde le contenu complet de Employe : select * from Employe ; id | deptno | salaire | commission ----+--------+---------+-----------1 | 59 | 2000.00 | 0.00 2 | 69 | 3000.00 | 0.00 update Employe set salaire = 2 * Employe.salaire, commission = 500.0, deptno = (select d.deptno from Departement d where d.prefecture = ’Paris’) where deptno in(select deptno from Departement where prefecture in(’Lille’,’Lyon’)); select * from Employe ; id | deptno | salaire | commission ----+--------+---------+-----------1 | 75 | 4000.00 | 500.00 2 | 75 | 6000.00 | 500.00 drop table Employe ; drop table Departement ;
5.3
delete : suppression de lignes existantes delete from [where condition] ;
Supprime les lignes pour lesquelles le condition est vraie. Exemple suppression des clients ayant un num´ero num_client ´egal `a 2 ou 5 : delete from Client where num_client in (2, 5) ; Suppression de tous les clients : delete from Client ; -- vide la table Si un client est r´ef´erenc´e par une clef ´etrang`ere, la suppression du client ´echouera et tous les clients continueront d’exister.
30
Chapitre 6 Le mod` ele relationnel et SQL Invent´e par E.F. Codd en 1970, chez IBM. Ce mod`ele est li´e `a la th´eorie des ensembles (unicit´e des ´el´ements, sous-ensemble, produit cart´esien, . . .) Une de ses r´ealisations pratiques : SQL (Structured Query Language). Historique – 1970, Codd invente l’alg`ebre relationnelle, – 1972 `a 1975 IBM invente SEQUEL puis SEQUEL/2 en 1977 pour le prototype SYSTEM-R de SGBD relationnel – SEQUEL donne naissance `a SQL – Parall`element, Ingres d´eveloppe le langage QUEL en 1976 – D`es 1979, Oracle utilise SQL – 1981, IBM sort SQL/DS – 1983, IBM sort DB2 (h´eritier de SYSTEM-R) qui fournit SQL. – 1982, l’ANSI (organisme de normalisation am´ericain) commence la normalisation de SQL qui aboutit en 1986 et donne la norme ISO en 1987 – en 1986 PostgreSQL commence son d´eveloppement. – Une nouvelle norme SQL-89 – Puis la norme SQL-92 (ou SQL2) qui est la plus utilis´ee, – Puis la normalisation SQL-99 (ou SQL3) avec, entre-autres, les extensions relationnel-objet, qui n’est pas encore termin´ee !
6.1
Qu’est-ce qu’un ensemble
Un ensemble est une collection d’´el´ements de mˆeme nature. Par exemple l’ensemble des entiers n´egatifs, ensemble des caract`eres, des voyelles, des mots de la langue fran¸caises. D´efinition d’un ensemble : – par extension (ou ´enum´eration) : on explicite chaque ´el´ement, par exemple l’ensemble des voyelles : {a, e, i, o, u, y}. L’ordre des ´el´ements n’a aucune importance : {a, e, i} = {i, a, e}. Unicit´e de chaque ´el´ement apparaissant dans un ensemble, contre-exemple : {a, e, i, a} n’est pas un ensemble. L’ensemble vide : {} = ∅ – par intention (ou caract´erisation) : on d´efinit la ou les propri´et´es v´erifi´ees par chaque ´el´ement de l’ensemble et seulement les ´el´ements de l’ensemble. Par exemple l’ensemble des entiers naturels 31
pairs :{x|x = 2p, p ∈ N} En SQL on parle plutˆot de domaine que d’ensemble, par exemple Varchar (20) est l’ensemble de toutes les chaˆınes de caract`eres de longueurs inf´erieures ou ´egales `a 20 et, en Oracle, Number (5, 2) est l’ensemble des nombres positifs ou n´egatifs pouvant s’exprimer avec 5 chiffres d´ecimaux dont 2 apr`es la virgule.
6.2
Notion centrale : sch´ ema et valeur d’une relation
Le sch´ema d’une relation exprime comment est constitu´ee une relation : le nombre d’attributs par n-uplet, un nom diff´erent pour chaque attribut et, pour chaque attribut, le domaine dans lequel il prend ses valeurs. Par exemple : sch´ema : Etudiant (NumCarte : Entier ; Nom : Chaine ; Note : Entier) Le nombre d’attributs du sch´ema s’appelle son arit´e, le sch´ema Etudiant a une arit´e de 3. La valeur d’une relation est un sous-ensemble du produit cart´esien des domaines de son sch´ema (un domaine est un ensemble de valeurs, par exemple l’ensemble des chaˆınes de caract`eres, l’ensemble des couleurs primaires, l’ensemble des notes de 0 `a 20, l’ensemble des mentions de diplˆomes d´elivr´es par l’USTL, . . .). Voici un exemple de valeur d’une relation : NumCarte (122678555, (123678555, (213678555,
Nom ’Toto’, ’Truc’, ’Bidule’,
Note 12) 10) 15)
qui est bien un sous-ensemble du produit cart´esien : Entier × Chaˆıne × Entier. Chaque ligne de la relation est un n-uplet1 dont l’ordre des attributs est fix´e par le sch´ema. Dans l’exemple, la premi`ere valeur de chaque n-uplet est le num´ero de carte d’un ´etudiant, la deuxi`eme son nom, la troisi`eme sa note. Chaque n-uplet repr´esente un ´etudiant.
6.2.1
Sch´ ema ou intention d’une relation
Par exemple voici la relation Ville : sch´ema : Ville (Id : Entier, Nom : Chaine, Departement : 1..100, Population : Naturel)
6.2.2
Contenu ou instance ou extension d’une relation
L’extension d’une relation est un sous-ensemble du produit cart´esien D1 × D2 × . . . × Dk . Les membres (ou ´el´ements) d’une relation sont appel´es nuplets (k-uplets). SQL Plusieurs fa¸cons d’ajouter une ville dans la table Ville en PostgreSQL : – insert into Ville values (1, ’Lille’, 59, 222400) ; insert into Ville values (7, ’Dunkerque’, 59, 175000) ; Dans cette forme on doit donner une valeur `a chaque colonne dans l’ordre dans lequel sont d´eclar´ees les colonnes. – insert into Ville (id, Departement, Nom , Population) values (3, 75, ’Paris’, 2200000) ; Ici on voit qu’en explicitant les noms des colonnes on peut utiliser un autre ordre. 1
Ici on a affaire `a des 3-uplet.
32
– insert into Ville (Nom, id) values (’Paris-Texas’, 4) ; Enfin, en explicitant les colonnes `a initialiser on peut n’en donner qu’un sous-ensemble, les colonnes non mentionn´ees seront ind´efinies (is null) sauf celles qui ont une valeur par d´efaut (default) pour la colonne population. select * from Ville ; insert into Ville values (7, ’Dunkerque’, 59, 175000) ;
6.2.3
Sch´ ema et extension
Souvent on repr´esente par un seul tableau `a la fois le sch´ema et une instance possible de la relation : Id 1 2 3 4 5 6
Nom Lille Dunkerque Paris Paris-Texas Marseille Lyon
Departement 59 59 75 13 69
Population 222400 175000 2200000 0 880000 420000
On voit que le d´epartement de Paris-Texas n’est pas d´efini (le test est is null). Les colonnes blanches ou vides de Paris-Texas correspondent `a des colonnes ind´efinies. Q.13 Combien d’´ el´ements ou lignes contient le produit cart´esien du tableau pr´ec´edent avec lui-mˆeme ?
6.3
Clef d’une relation
Une clef candidate C d’une relation R est un sous-ensemble minimal d’attributs de R qui d´eterminent les autres attributs de R, c’est `a dire que pour une valeur donn´ee de C, les autres attributs ont exactement une valeur. Par exemple le num´ero de carte d’´etudiant d´etermine le nom de l’´etudiant et certainement d’autres informations. Autrement, dit une valeur de C apparaˆıt au plus une fois dans toute extension de R. Une relation peut poss´eder plusieurs colonnes servant de clef primaire (primary key). Par exemple : Etudiant (num_carte, num_insee, nom, pr´ enom, datenaiss) pourrait poss´eder deux clefs candidates : (num_carte) qui doit ˆetre diff´erent pour chaque ´etudiant et (num_insee) qui identifie la naissance d’une personne et est cens´ee ˆetre unique pour chaque personne n´ee en France. On peut choisir (num_carte) comme clef primaire. Q.14 Quel probl` eme se poserait si on choisissait (nom, pr´ enom) comme clef primaire d’un ´etudiant ? En SQL, la clef primaire fait l’objet d’une contrainte primary key, les autres clefs candidates peuvent faire l’objet d’une contrainte d’unicit´e (unique). En Oracle ainsi qu’en PostgreSQL, aucune des colonnes d’une clef primaire ne peut ˆetre ind´efinie (is null).
6.4
Clef ´ etrang` ere
Une clef ´etrang`ere est constitu´ee d’une ou plusieurs colonnes et permet de d´esigner au plus une ligne d’une autre table ou de la mˆeme table. 33
Une clef ´etrang`ere peut ˆetre interpr´et´ee comme un pointeur associatif vers une ligne d’une autre table ou de la mˆeme table. Les colonnes de l’autre table correspondant `a celles de la clef ´etrang`ere doivent ˆetre la clef primaire compl`ete de cette table ou constituer compl`etement les colonnes d’une contrainte d’unicit´e. Associatif signifie que pour retrouver la ligne r´ef´erenc´ee on recherche dans l’autre table la ligne dont les colonnes de la clef primaire ou de la contrainte d’unicit´e sont ´egales `a celles de la ligne r´ef´eren¸cante (cela peut heureusement se faire efficacement grˆace aux index associ´ees aux clefs primaires et contraintes d’unicit´e voir le chapitre 8 page 64). Par exemple une fˆete r´ef´erence la ville dans laquelle elle se passe en mentionnant en tant que clef ´etrang`ere le num´ero de d´epartement et le nom de la ville dans ce d´epartement (deux villes de deux d´epartements diff´erents pouvant porter le mˆeme nom) : create table Ville ( departement Int4,
create table Fete ( departement Int4, nom Varchar (20), id Int4, jour Date, constraint Fete_PK primary key (id), constraint Fete_Ville_FK foreign key (departement, nom) -| | -V V references Ville (departement, nom) )
Donner un nom `a chaque contrainte avec le mot clef constraint permet de rendre un message d’erreur plus clair car le nom de la contrainte apparaˆıt dans le message d’erreur. L’ordre des colonnes est bien entendu important dans la d´eclaration de la contrainte foreign key. Une clef ´etrang`ere comportant une colonne ind´efinie ne d´esigne aucune ligne, sinon le SGBD (Oracle, PostgreSQL et MySQL avec InnoDB) garantit que la ligne d´esign´ee existe, sinon l’ordre ´echoue. Par d´efaut, une ligne r´ef´erenc´ee par une clef ´etrang`ere ne peut pas ˆetre d´etruite, d’autres comportements peuvent ˆetre sp´ecifi´es grˆace `a des options de d´eclaration de clef ´etrang`ere, par exemple si une ligne r´ef´erenc´ee est d´etruite on peut demander que les lignes r´ef´eren¸cantes soient aussi d´etruites ou modifi´ees avec les options de clef ´etrang`ere : – on delete cascade qui indique que la ligne r´ef´eren¸cante sera elle aussi d´etruite. – set null rend les colonnes clefs ´etrang`eres ind´efinies. – on update cascade met `a jour les colonnes clefs ´etrang`eres.
6.5
L’alg` ebre relationnelle et le langage de requˆ etes SQL
Une requˆete permet de voir l’´etat partiel ou complet de une ou plusieurs tables (avec des inner join ou left outer join).
6.5.1
Pr´ eliminaire : l’identit´ e
En notation relationnelle, il suffit de mentionner le nom de la relation, par exemple R, et on a alors acc`es implicitement `a sa valeur (son extension), exactement comme lorsqu’on mentionne la variable 34
x dans une expression arithm´etique. En SQL il faut par contre ´ecrire la requˆete suivante pour voir le contenu complet d’une table : select * from Ville ; L’´etoile * indique d’afficher toutes les colonnes de la table Ville. Tous les nuplets de la table Ville sont alors affich´es. Ou, si on veut garantir l’unicit´e de chaque nuplet affich´e : select distinct * from Ville ; Le mot clef distinct garantie l’unicit´e des lignes affich´ees. Q.15 Si Ville poss` ede une clef primaire, le distinct est-il utile dans la requˆete pr´ec´edente ?
6.5.2
Les op´ erateurs de base
La projection : SELECT Pour ne conserver que certaines colonnes. ΠAp1 ,...,Apk (R) = {(xp1 , . . . , xpk ) | ∃(y1 , . . . , yn ) ∈ R, xpi = ypi ∀i ∈ [1, k]} Par exemple l’op´erateur ΠDpt,P opulation correspond `a une projection sur les deux colonnes Dpt et Population : select v.Departement as departement, v.Population as population from Ville v ; On peut donner un nom aux colonnes affich´ees avec as. Ce qui donne : departement 59 59 59 75
population 222400 175000 222400 2.200000
13 880000 69 420000 En SQL, c’est la clause select de la requˆete qui exprime la projection. Le qualificatif distinct permet d’obtenir l’unicit´e des lignes du r´esultat (distinct porte sur toutes les colonnes de la projection) : select distinct v.Departement, v.Population from Ville v ; Ce qui donne :
departement 59 59 75
population 222400 175000 2.200000
13 69
880000 420000
Si on ne met pas distinct, les doublons ´eventuels sont conserv´es (voir au dessus). 35
La restriction : WHERE qui ne conserve que les lignes v´ erifiant sa condition Pour ne conserver que les nuplets v´erifiant le pr´edicat P . σP (R) = {(x1 , . . . , xk ) | (x1 , . . . , xk ) ∈ R ∧ P (x1 , . . . , xk )} Par exemple, on veut les villes du nord : select * from Ville v where v.Departement = 59 ; On obtient : Id Nom 1 Lille 7 Dunkerque
Departement 59 59
Population 222400 175000
En SQL, c’est la clause where de la requˆete qui exprime la s´election des lignes (si sa condition est vraie). Si en revanche on veut toutes les villes qui ne sont pas dans le d´epartement du nord : select * from Ville v where v.Departement != 59 ; -- pr´ edicat de la restriction (ou <> en PostgreSQL) on ne voit pas les villes de d´epartement inconnu (ou non renseign´e ou is null) car le pr´edicat n’ayant pas de valeur dans ce cas, le nuplet est rejet´e par la requˆete.
L’union : UNION R et S sont deux relations de mˆeme sch´ema. R ∪ S = {(x1 , . . . , xk ) | (x1 , . . . , xk ) ∈ R ∨ (x1 , . . . , xk ) ∈ S} Une requˆete select peut ˆetre utilis´ee comme une table, on peut donc avoir des emboˆıtements de requˆetes. 1. La requˆete ensembliste (sans doublons) union : select nom, ’Etudiant’ as categorie from Etudiant Union select nom, ’Enseignant’ as categorie from Enseignant ; 2. La requˆete ensembliste qui conserve les doublons union all : select nom, ’Etudiant’ as categorie from Etudiant Union All select nom, ’Enseignant’ as categorie from Enseignant ; Lors d’une instruction insert il est possible d’ajouter 0, 1 ou plusieurs lignes d’un coup a` condition de remplacer la clause values par une requˆete, par exemple : create table Ville_Du_Nord ( id Serial, nom Varchar (50), departement Int4, constraint Ville_Du_Nord_PK primary key (id) ) ; insert into Ville_Du_Nord (departement, nom) select v.dpt, v.nom from Ville v where v.dpt = 59 ; 36
Le type Serial de id est en fait un compteur entier incr´ement´e `a chaque insertion, ce qui donne des valeurs diff´erentes pour la clef primaire. La diff´ erence : EXCEPT Except donne les lignes de la requˆete gauche qui n’apparaissent pas dans la requˆete droite. Les lignes sont uniques `a moins de mettre l’op´erateur Except all R et S sont deux relations de mˆeme sch´ema. R − S = {(x1 , . . . , xk ) | (x1 , . . . , xk ) ∈ R ∧ (x1 , . . . , xk ) 6∈ S} Les villes dont le d´epartement est connu : select * from Ville EXCEPT select * from Ville where Departement is null ; Ou autrement sans Except : select * from Ville where Departement is not null ; L’intersection : INTERSECT R et S sont deux relations de mˆeme sch´ema.
R∩S = {(x1 , . . . , xk ) | (x1 , . . . , xk ) ∈ R∧(x1 , . . . , xk ) ∈ S} Oracle ne propose pas d’op´erateur d’intersection, mais on peut la r´ealiser grˆace `a l’´egalit´e : R ∩ S = R − (R − S) Calcule l’intersection entre deux requˆete : select nom, departement from ville INTERSECT select nom, departement from ville where departement = 59 ; nom | departement -----------+----Dunkerque | 59 Lille | 59 La clause All est possible pour conserver les lignes identiques : select nom, dpt from ville INTERSECT all select nom, dpt from ville where dpt = 59 ; Nouveau jeu de donn´ ees (figure 6.1) Fig. 6.1 – Un exemple de valeur de table avec deux clefs ´etrang`eres etu et mat dans la table Note. Table nom Alfred Marc Julie
Etudiant ide 1 2 3
Table Note note mat →Matiere.idm 12 1 14 2 15 2
Etudiant.ide←etu
1 1 3
Table Matiere idm nom coeff 1 BD 3 2 CL 5
Dans la table Note etu et mat servent de clef primaire. ´ Q.16 Ecrire le MCD correspondant aux trois tables pr´ecedantes. Q.17 Donner la d´ eclaration de ces trois tables et remplissez les tables avec les donn´ees ci-dessus.
37
Le produit cart´ esien : CROSS JOIN Le produit cart´esien cross join est une fonction binaire dont les deux op´erandes sont des ensembles quelconques et la valeur est l’ensemble des couples form´es d’un ´el´ement du premier op´erande et d’un ´el´ement du second op´erande. Exemple : {b, f } × {e, i, o} = {(b, e), (b, i), (b, o), (f, e), (f, i), (f, o)}. Dans un couple (ou 2-uplet) l’ordre des ´el´ements est important : (b, e) 6= (e, b). Autre exemple : le produit cart´esien de l’ensemble des ´etudiants de licence GMI avec l’ensemble des UE de licence GMI. Etudiant×Matiere = {(e1 , . . . , eke , m1 , . . . , mkm ) | (e1 , . . . , eke ) ∈ Etudiant∧(m1 , . . . , mkm ) ∈ Matiere} Tous les couples ´etudiant, mati`ere (Oracle10, PostgreSQL, SQL92) : select * from Etudiant etu cross Join Matiere mat ; ide | nom | idm | nom | coeff -----+--------+-----+-----+------1 | Alfred | 1 | BD | 3 1 | Alfred | 2 | CL | 5 2 | Marc | 1 | BD | 3 2 | Marc | 2 | CL | 5 3 | Julie | 1 | BD | 3 3 | Julie | 2 | CL | 5 on obtient 3 × 2 lignes. En Oracle 10 et en PostgreSQL on peut ´ecrire (et en g´en´eral en SQL on peut ´ecrire) le produit cart´esien comme ceci : -- Oracle10, PostgreSQL, SQL92 select * from Etudiant, Matiere ; On obtient le mˆeme r´esultat que pr´ec´edemment. Si on ne veut afficher que la partie Etudiant de chaque ´el´ement du produit cart´esien, on peut pr´efixer * avec le nom de la table ou son alias : select etu.* from Etudiant etu cross Join Matiere mat ; etu est l’alias de la table Etudiant, mat est l’alias de la table Matiere. Q.18 Sous quelle condition les deux requˆ etes suivantes ont-elle la mˆeme valeur, sous quelle condition ont-elle des valeurs diff´erentes ? select * from Etudiant ; select distinct etu.* from Etudiant etu cross join Matiere mat ; ide | nom -----+-------1 | Alfred 2 | Marc 3 | Julie (3 rows) 38
6.5.3
Quelques op´ erateurs suppl´ ementaires
Ils peuvent s’exprimer grˆace aux op´erateurs de base vus pr´ec´edemment et ne sont donc th´eoriquement pas insdispensables, mais ils sont tellement pratiques qu’`a la fois le relationnel et SQL leur attribuent une identit´e. La jointure, produit cart´ esien et restriction : ... INNER JOIN ... ON Elle permet de ne conserver que les ´el´ements pertinents d’un produit cart´esien. R ⊲⊳P S = σP (R × S) o` u P exprime la condition de conservation d’un ´el´ement du produit cart´esien. Par exemple les couples (´etudiant, mati`ere) si l’´etudiant a une note dans cette mati`ere en se basant sur les contenu des tables de la figure 6.1 page 37 : select e.nom as from Etudiant cross join cross join where e.ide = and n.mat =
etudiant, m.nom as matiere, n.note as note e Note n Matiere m n.etu m.idm ;
SQL2, PostgreSQL et Oracle 10 (et d’autres bien entendu) disposent d’un op´erateur de jointure sp´ecifique
inner join
on . La requˆete pr´ec´edente peut alors ˆetre r´e´ecrite plus clairement en : select e.nom as from Etudiant inner join inner join
etudiant, m.nom as matiere, n.note as note e Note n on n.etu = e.ide Matiere m on m.idm = n.mat ;
etudiant | matiere | note ----------+---------+-----Alfred | BD | 12 Alfred | CL | 14 Julie | CL | 15 (3 rows) Q.19 Pourquoi Marc n’apparaˆıt-il pas dans le r´ esultat ?
Le mot clef inner permet de distinguer cette jointure de la jointure dite externe (voir la section 6.12 page 54) qui, elle, utilise le mot clef outer plutˆot que inner. L’op´erateur != signifie diff´erent et peut aussi se noter <> en PostgreSQL. On distingue plusieurs cas particuliers de jointures ´ ´ Equi-jointure Egalit´ e entre colonnes : c’est probablement la plus courante, tr`es souvent on teste l’´egalit´e entre la clef ´etrang`ere d’une table et la clef primaire d’une autre table. L’exemple pr´ec´edent 9 est une ´equi-jointure. ´ Jointure naturelle : attention danger Equi-jointure de R et S sur les colonnes de mˆemes noms. En SQL92 et PostgreSQL on ajoute le mot clef natural. La jointure naturelle est particuli`erement dangereuse : supposons une application qui utilise la jointure naturelle entre deux tables T1 et T2 . Si, plus tard, on ajoute `a T1 et `a T2 une colonne homonyme 39
et de mˆeme type alors ces deux colonnes participeront automatiquement `a cette jointure naturelle, ce qui n’est pas forc´ement ce que souhaite celui qui ajoute ces colonnes. create table Note_Naturelle ( ide Int4, idm Int4, note Int2, constraint Note_Naturelle_PK primary key (ide, idm), constraint Note_Naturelle_Etudiant_FK foreign key (ide) references Etudiant (ide), constraint Note_Naturelle_Matiere_FK foreign key (idm) references Matiere (idm) ) ; insert into Etudiant values (1, ’Alfred’) ; insert into Etudiant values (2, ’Marc’) ; insert into Etudiant values (3, ’Julie’) ; select * from Etudiant ; insert into Matiere values (1, ’BD’, 3) ; insert into Matiere values (2, ’CL’, 5) ; select * from Matiere ; insert into Note_Naturelle values (1, 1, 12) ; insert into Note_Naturelle values (1, 2, 14) ; insert into Note_Naturelle values (3, 2, 15) ; select * from Note_Naturelle ; select e.nom as etudiant, n.note as note from Etudiant e natural join Note_Naturelle n ; etudiant | note ----------+-----Alfred | 12 Alfred | 14 Julie | 15
Auto-jointure Jointure d’une relation avec elle-mˆeme. Par exemple, les employ´es qui sont chef d’au moins un autre employ´e : create table Employe ( id Serial, nom Varchar (20), mon_chef Int4, contraint Employe_PK primary key (id), contraint Employe_Chef_FK foreign key (mon_chef) reference Employe (id) ) ; select distinct chef.* from Employe emp inner join Employe chef on chef.id = emp.mon_chef ; 40
-- ´ equi-jointure
Non ´ equi-jointure Le pr´edicat de la clause on d’une jointure n’est pas forc´ement une ´egalit´e : toute condition peut convenir. Grˆace `a l’ordre alter, on ajoute l’attribut sexe aux ´etudiants : alter table Etudiant add sexe Varchar (1) default ’M’ -- valeur par d´ efaut (discutable !) check (sexe in (’M’, ’F’)) -- les 2 valeurs possibles not null ; -- ne peut ^ etre ind´ efini update Etudiant set sexe = ’F’ where ide = 3 ; select * from etudiant; id | nom | sexe ----+--------+-----1 | Alfred | M 2 | Marc | M 3 | Julie | F ´ Q.20 Ecrire la requˆete qui donne tous les binˆomes mixtes d’´etudiant et sans redondance : si on obtient le binˆome (Alfred, Julie) on ne doit pas obtenir aussi le binˆome (Marc, Julie) car Marc n’a pas de note. Q.21 Utiliser count() pour compter le nombre de mati` eres de chaque ´etudiant. Q.22 Pour la BD suivante, donner la requˆ ete fournissant les ´etudiants inscrits `a toutes les mati`eres
(clause having qui s´electionne un groupe). select e.ide, e.nom, e.sexe from Etudiant e inner join Note n on n.etu = e.ide group by e.ide, e.nom, e.sexe having count (*) = (select count(*) from Matiere) ; ide | nom | sexe -----+--------+-----1 | Alfred | M La clause having permet de conserver un groupe si sa condition vaut vraie. En particulier la contrainte primary key garantit que ses colonnes sont d´efinies et donc les colonnes clefs ´etrang`eres de Note sont forc´ement d´efinies. La clause having repr´esente une condition de conservation d’un groupe. Ici un groupe correspond au fait qu’un ´etudiant a une note dans toutes les mati`eres. Cette condition porte sur chaque ´etudiant s´epar´ement, ainsi l’expression count (*) repr´esente le nombre de mati`eres d’un mˆeme ´etudiant. Pour r´esumer : la condition du having porte sur le nombre de lignes d’un groupe (un ´etudiant identif´e par sa clef primaire) produite par la clause from et la condition du having porte sur chaque groupe construit par le group by.
6.6
Le cas des valeurs ind´ efinies : le test est is [not] null
Soit la table Client : 41
create table Client ( id Int4, nom Varchar (20), tel Varchar (30), constraint Client_PK primary key (id) ) ; Dans la pratique il est souhaitable de pouvoir m´emoriser une nouvelle ligne dans une table, mˆeme si certaines colonnes ne peuvent ˆetre renseign´ees du fait qu’on n’a pas forc´ement toute l’information. Par exemple je veux quand mˆeme pouvoir enregistrer un nouveau client mˆeme si je ne connais pas son num´ero de t´el´ephone. Par exemple voici deux ordres ´equivalents qui ne renseignent pas le t´el´ephone d’un nouveau client : Insert into Client (id, nom, tel) values (13, ’Tartampion’, null) ; Insert into Client (id, nom) values (14, ’Gidon’) ; Insert into Client (id, nom, tel) values (15, ’Guy’, ’03/20/18/18/18’) ; select * from client; id | nom | tel ----+------------+---------------13 | Tartampion | 14 | Gidon | 15 | Guy | 03/20/18/18/18 Et une mani`ere d’enregistrer le fait qu’on ne connaˆıt plus le nouveau num´ero du client 15 : update Client set tel = null where id = 15 ; select * from client; id | nom | tel ----+------------+----13 | Tartampion | 14 | Gidon | 15 | Guy | La colonne t´el´ephone sera alors dite ind´efinie : elle n’a pas de valeur. On pourra tester si une colonne (etplus g´en´eralement une expression) est d´efinie ou non avec le pr´edicat bool´een is [not] null : – is null vrai ssi est ind´efinie, faux ssi est d´efinie.
– is not null est ´equivalent `a not ( is null) Par exemple, les villes dont on ne connaˆıt pas le d´epartement : select v.nom from Ville v where v.departement is null ; Paris-Texas Q.23 Quelle ambigu¨ıt´ e y a-t-il dans la question : les villes du nord du tableau. (page 33) Q.24 Lister les villes qui ne sont pas dans le d´ epartement du Nord ou dont le d´epartement n’est pas renseign´e. SQL permet qu’une colonne soit is null `a condition qu’elle ne soit assujettie ni `a la contrainte not null ni `a primary key.
6.6.1
Noter l’absence de valeur
Un op´erande n’ayant pas de valeur peut se noter explicitement avec le mot clef null en PostgreSQL et Oracle, par exemple null + 5 donnera une valeur ind´efinie. 42
Attention : ne pas interpr´eter ce null comme le pointeur null des langages de programmation ni comme le z´ero des entiers !
6.6.2
Comportement des op´ erateurs et des fonctions ` a valeur non bool´ eenne
La plupart des op´erateurs et des fonctions `a valeur autre que bool´eenne sont ind´efinis si un de leurs op´erandes est ind´efini. Par exemple : a 1 is null 0 0 is null
b 2 2 0 is null is null
a+b 3 is null = true 0 is null = true is null = true
Par exemple : (1 + n.note) is null ⇔ n.note is null
6.6.3
Comportement des op´ erateurs relationnels
Les op´erateurs relationnels (=, <, <=, >, >= et x between a and b) sont `a valeur bool´eenne. Quand un de leurs op´erandes est ind´efini, il ont vraiment une valeur qui peut ˆetre test´ee avec is null en PostgreSQL ou vide en PostgreSQL. a b is not null is not null Au moins un des deux is null
a = b, a != b, a <= b, ... vrai (t) ou faux (f) is null = true
Par exemple, quel que soit l’´etat de la colonne nom, les expressions null=null et nom!=null valent une valeur ind´efinie (is null) en PostgreSQL.
6.6.4
Comportement des op´ erateurs logiques
Les op´erateurs logiques (not, or et and) travaillent donc en logique tri-valu´ee, c’est `a dire que leurs op´erandes ont des valeurs prises dans un ensemble de trois valeurs : {vrai, faux, is null}. Quand aucun des op´erandes n’est is null on a affaire `a la logique binaire habituelle. Pr´ecisons ce qui se passe quand un des op´erandes est is null : not vaut ´evidemment unknown ou vide. and vaut faux si l’autre op´erande vaut faux, sinon unknown ou vide. or vaut vrai si l’autre op´erande vaut vrai, sinon unknown ou vide. a is null is null is null
b not b is null is null ou vide faux vrai vrai faux
a and b is null ou vide faux is null ou vide
a or b is null ou vide unknown ou vide vrai
Q.25 Que donnerait le ou exclusif xor qui n’existe pas en Oracle et en PostgreSQL ? Q.26 Donner une d´ efinition du pr´edicat x between a and b en utilisant uniquement les op´erateurs
<= et and. Q.27 Que donnerait l’op´ erateur a between b and c si un de ses op´erandes est ind´efini ?
43
6.6.5
L’expression conditionnelle : case {when then} else
case when [when ... when [else end
then valeur1 then valeur2 then valeurN] valeurParD´ efaut ]
Le premier pr´edicat qui vaut vrai donne sa valeur au case, si aucun pr´edicat ne vaut vrai c’est la valeur par d´efaut du else qui est donn´e, s’il n’y a pas de else et que tous les pr´edicats sont faux la valeur est ind´efinie (is null). Par exemple : select v.nom as nom, case when v.population >= 100000 then ’Grande ville’ when v.population < 100000 then ’Petite ville’ else ’Je ne sais pas : la population est ind´ efinie’ end as categorie from Ville v ; nom | categorie -------------+------------------------------------------------Lille | Grande Ville Paris | Grande Ville Paris-Texas | Petite Ville Paris | Petite Ville Dunkerque | Petite Ville Montpellier | Je ne sais pas : la population est ind´ efinie ! Q.28 Donner une autre formulation ´ equivalente au case pr´ec´edant qui utilise is null.
6.6.6
Pr´ esomption d’innocence de la clause where
La clause where peut apparaˆıtre dans une requˆete (select) mais aussi dans une mise `a jour de lignes (update) ou une suppression de lignes (delete). Si la condition d’une clause where s’´evalue `a false ou unknown alors le nuplet correspondant n’est pas trait´e. Par exemple, pour le delete, l’id´ee est qu’on ne veut pas d´etruire un nuplet si on ne sait pas s’il v´erifie la condition de suppression (pr´esomption d’innocence). Q.29 La requˆ ete suivante, cens´ee lister les clients dont le nom n’est pas d´efini. select * from Client c where c.nom is null ;
6.7 6.7.1
Quelques op´ erateurs et fonctions scalaires de SQL/Oracle et PostgreSQL between a and b
Les expressions a et b peuvent ˆetre des nombres, des chaˆınes, des dates, tout type disposant d’un ordre. 44
select * from ville where departement between 59 and 75; id | nom | departement | population ----+-----------+-------------+-----------2 | Lille | 59 | 222400 3 | Paris | 75 | 2200000 4 | Dunkerque | 59 | 175000
6.7.2
La fonction coalesce de PostgreSQL
La fonction coalesce, `a au moins un param`etre ou plus de deux et vaut la premi`ere valeur d´efinie en partant de la gauche et est ind´efinie si tous ses param`etres le sont2 . select ’Bonjour ’||v.nom||’ ’|| coalesce(upper(v.departement),’pas de departement’) as coucou from Ville v ; coucou ---------------------------------------Bonjour Lille 59 Bonjour Paris 75 Bonjour Paris-Texas pas de departement Bonjour Paris 75 Bonjour Dunkerque 59 Bonjour Montpellier pas de departement ´ Q.30 Ecrire l’´equivalent de coalesce (a, b, c) en utilisant l’op´erateur case.
6.7.3
Manipuler les chaˆınes
Les fonctions de chaˆıne (upper, lower) select upper (nom) as majuscule from Etudiant; majuscule ----------ALFRED MARC JULIE select lower (nom) as minuscule from Etudiant; minuscule ----------alfred marc julie Concat´ enation : || et reconnaissance de mod` ele : like select ’Bonjour ’ || e.nom as bonjour from Etudiant e ; bonjour ---------------Bonjour Alfred 2
PostgreSQL propose aussi la fonction coalesce avec la mˆeme signification.
45
Bonjour Marc Bonjour Julie Je ne dis bonjour qu’aux ´etudiants dont le nom contient un r qui n’est pas la derni`ere lettre : Dans le mod`ele de like : – % correspond `a un nombre quelconque (´eventuellement nul) de n’importe quel caract`ere. – _ correspond `a exactement un caract`ere quelconque. Par exemple ’Alfred’ like ’%r_%’ est vrai et ’mer’ like ’%r_%’ est faux. select * from Etudiant where nom like ’%r_%’; id | nom | sexe ----+--------+-----1 | Alfred | M 2 | Marc | M ´ Q.31 Ecrire le mod`ele qui reconnaˆıt toute chaˆıne contenant le caract`ere x qui n’est ni le premier, ni le dernier de la chaˆıne. ´ Q.32 Ecrire le mod`ele qui reconnaˆıt toute chaˆıne contenant deux caract`eres x s´epar´es par au moins deux caract`eres. Q.33 Comment reconnaˆıtre les chaˆınes qui ont le caract` ere x en premi`ere et/ou en derni`ere position ?
La chaˆıne vide en PostgreSQL n’est pas ind´efinie. PostgreSQL est parfaitement coh´erent sur la notion de chaˆıne vide qui est bien entendu parfaitement d´efinie.
6.8
Les fonctions d’agr´ egation count, sum, avg, min, max
Ces fonctions effectuent un calcul synth´etique sur l’ensemble des nuplets fournis `a la projection (requˆete select). Par exemple sum calcule la somme des valeurs d´efinies que prend son expression pour chacun des nuplets et min en calcule la plus petite. Une requˆete dont la clause select comportant de telles fonctions dans ses expressions de projection fournit exactement une ligne (sauf si la requˆete est munie d’une clause group by, voir la section 6.11). sum, avg, min et max donnent un r´esultat ind´efini si l’expression argument n’est jamais d´efinie, c’est en particulier le cas quand aucun nuplet n’est s´electionn´e. En revanche count, qui compte le nombre de fois que son expression a une valeur d´efinie, a toujours une valeur d´efinie (´eventuellement la valeur z´ero si aucune ligne n’est trait´ee ou que toutes les valeurs sont ind´efinies). Par exemple count (e.id) donne le nombre de fois que l’attribut e.id est d´efini. Formes sp´eciales : – count (*) renvoie le nombre total de nuplets fournis. – count (distinct ) nombre de valeurs diff´erentes et d´efinies que prend l’expression. Q.34 Donner d’autres formes de count (*) qui soient ´ equivalentes. Enfin, on ne peut pas demander `a la clause select de fournir `a la fois une information synth´etique (exactement un nuplet) et une information individuelle (0, 1 ou plusieurs nuplets). Donc, d`es qu’une fonction d’agr´egation apparaˆıt dans la clause select, un nom de colonne ne peut apparaˆıtre que dans une expression argument d’une fonction d’agr´egation. La requˆete suivante fournira toujours exactement une ligne : 46
select count (distinct n.mat) as nb_matieres, avg (n.note) as moyenne, sum (n.note) / count (n.note) as autre_moyenne, max (n.note) as meilleure_note from Note n ; nb_matieres | moyenne | autre_moyenne | meilleure_note -------------+---------------------+---------------+---------------2 | 13.6666666666666667 | 13 | 15 Et voici un exemple incorrect car il m´elange information individuelle et information synth´etique : select e.nom as nom, -- incorrect ` a cause de count (*) count (*) as nb_etudiants from Etudiant e ; ERROR: syntax error at or near "count" LINE 2: count (*) as nb_etudiants, ^ Le tableau suivant r´esume les diff´erentes fonctions d’agr´egation count, sum, avg, min, max fonction
valeur
sum (expr) avg (expr) min (expr) max (expr) count (expr) count (distinct expr) count (*) count (1+2) count (’abc’) count (e.nom)
si expr est toujours ind´ efinie ou que aucune ligne ne lui est fournie somme des valeurs d´efinies de expr is null moyenne des valeurs d´efinies de expr is null min des valeurs d´efinies de expr is null max des valeurs d´efinies de expr is null nombre de valeurs d´efinies de expr 0 nombre de valeurs d´efinies et diff´erentes de expr 0 nombre de lignes 0 si aucune ligne nombre de lignes 0 si aucune ligne nombre de lignes 0 si aucune ligne nombre de e.nom is not null 0 si e.nom est toujours ind´efini
Q.35 Parmi les expressions de la figure 6.2 page 47, regrouper celles qui ont exactement le mˆ eme
(e.nom) (’coucou’) (upper (e.nom)) (e.nom is null)
count (55 + 2*3.14) sum (e.note) / count (*) sum (case when e.nom is null then 0 else 1 end) sum (e.note) / count (e.note)
Fig. 6.2 – Expressions `a classer.
6.8.1
´ Evaluation d’une requˆ ete synth´ etique (fonctions d’agr´ egation)
Une requˆete synth´etique produit toujours exactement une ligne (mˆeme si le from where ne produit aucune ligne) en utilisant les fonctions d’agr´egation dans sa clause select.
47
Une requˆete synth´etique produit toujours exactement une ligne (mˆeme si le from where ne produit aucune ligne) en utilisant les fonctions d’agr´egation dans sa clause Mˆeme si aucun produit n’a un prix sup´erieur `a 100 on aura quand mˆeme une ligne ´egale `a 0. Si la table Produit est vide ou qu’aucun prix n’est sup´erieur `a 100, on obtient : select count (*) as nb_trop_cher from Produit p where p.prix > 100 ; nb_trop_cher -------------0 On veut calculer la moyenne pond´er´ee par les coefficients de mati`ere de l’´etudiant Alfred. Voici la requˆete et, conceptuellement, comment elle va ˆetre ´evalu´ee (il est tr`es probable qu’un vrai moteur SQL ne fera pas l’´evaluation de cette mani`ere) : select Sum (n.note*m.coeff) / Sum (case when n.note is null then 0 else m.coeff end) as moy_alfred from Etudiant e inner join Note n on e.ide = n.etu inner join Matiere m on n.mat = m.idm where e.ide = 1 ; --
1) r´ esultat de la jointure et de la restriction where : NOTE| COEFF --------|-----12| 3 14| 5 -- 2) calcul des expressions en argument des fonctions d’agr´ egation : N.NOTE*M.COEFF| CASE ... --------------|-----36| 3 70| 5 --- 3) Calcul les sommes de chacune des deux colonnes : SUM(N.NOTE*M.COEFF)|SUM(CASE WHEN NOTE IS NULL THEN 0 ELSE M.COEFFEND) -------------------|---------------------------------------106| 8 -- 4) Enfin calcul de la moyenne d’Alfred (la division) impr´ ecis car entier: moy_alfred -----------13 Les expressions arguments des fonctions d’agr´egation sont donc ´evalu´ees s´epar´ement pour chaque nuplet et les expressions externes aux fonctions d’agr´egation sont calcul´ees en dernier. Pour avoir cette mˆeme moyenne pour chaque ´etudiant, on pourra utiliser la clause group by, voir la section 6.11. 48
6.9
Les sous-requˆ etes
6.9.1
sous-requˆ ete dans la clause from
Dans la clause from on peut ´ecrire un select entre parenth`eses `a la place du nom d’une table. Par exemple : les villes dont la population est sup´erieure ou ´egale `a la moyenne des populations : select v.nom as ville from Ville v cross join (select AVG (v.population) as moyenne from Ville v where v.population is not null) pop where v.population >= pop.moyenne ; ville ------Paris Ou encore, les villes dont la population est sup´erieure ou ´egale `a la population moyenne par ville de leur d´epartement : select v.nom as ville from Ville v inner join (select AVG (vv.population) as moyenne, vv.departement as departement from Ville vv group by vv.dpt) popParDpt on popParDpt.departement = v.departement where v.population >= popParDpt.moyenne ; ville ------Paris Lille Remarquer que la sous-requˆete calculant la moyenne de population par d´epartement est close (autonome) : elle ne d´epend en rien de la requˆete englobante. Une clause on ne peut mentionner que des alias de tables d´ej`a d´eclar´es. Une sous-requˆete dans la clause from ne peut pas mentionner des colonnes appartenant aux tables de la clause englobante : elle doit ˆetre close ou autonome (idem en PostgreSQL). Autrement dit : une sous-requˆete dans une clause from ne peut pas ˆetre corr´el´ee (ou d´ependante) avec une table ou une autre sous-requˆete de la mˆeme clause from. L’exemple suivant est refus´e par Oracle et PostgreSQL car la sous-requˆete n’est pas close : select v.nom as ville from Ville v inner join (select AVG (vv.population) as moyenne, max (vv.departement) as departement from Ville vv where vv.departement = v.departement) pop_par_dpt on v.departement = pop_par_dpt.departement where v.population >= pop_par_dpt.moyenne ;
49
ERROR: invalid reference to FROM-clause entry for table "v" HINT: There is an entry for table "v", but it cannot be referenced from this part of the query.
6.9.2
sous-requˆ etes dans les clauses where et select
En g´en´eral un op´erande dans une expression peut ˆetre une sous-requˆete entre parenth`eses. Si cette sous-requˆete produit : – exactement une ligne d’une colonne, elle peut ˆetre employ´ee avec un op´erateur scalaire correspondant au type de la colonne de la ligne courante. – un nombre quelconque de nuplets, elle devra ˆetre utilis´ee avec un op´erateur ensembliste appropri´e (exists (sous-requˆ ete) si une ligne existe dans le r´esultat de la requˆete, expression in (sousrequˆ ete) si la valeur de gauche apparaˆıt dans le requˆete de droite, expression not in (sousrequˆ ete) vrai si l’expression n’a pas sa valeur dans la sous-requˆete). Les requˆetes sont entre parenth`eses. Dans where et select une sous-requˆete peut ˆetre corr´el´ee si elle mentionne des colonnes appartenant `a des tables de la clause from de la requˆete englobante. sous-requˆ ete close, autonome ou non corr´ el´ ee C’est une sous-requˆete qui ne d´epend pas du nuplet courant de la requˆete englobante, une sousrequˆete non corr´el´ee donnera donc toujours le mˆeme r´esultat, l’optimiseur peut s’en rendre compte et ne l’´evaluer qu’une seule fois. Par exemple : les villes dont la population est sup´erieure ou ´egale `a la moyenne : select v.nom from Ville v where v.population >= (select AVG (vv.population) from Ville vv where vv.population is not null) ; nom ------Paris sous-requˆ ete corr´ el´ ee Le r´esultat d’une sous-requˆete corr´el´ee d´epend du nuplet courant de la requˆete principale car elle mentionne des colonnes de ce nuplet. Une corr´elation ne peut se faire que dans la clause select ou where, mais pas dans la clause from. Par exemple les villes dont la population est sup´erieure ou ´egale `a la moyenne de leur d´epartement : select v.nom from Ville v where v.population >= (select AVG (vl.population) from Ville vl where vl.departement = v.departement) ; Q.36 Lister les couples mati` ere, nom d’un ´etudiant ayant la meilleure note dans cette mati`ere avec
les deux techniques : sous-requˆete dans la clause from et sous-requˆete dans la condition. On a trois tables : Etudiant, Note et Matiere.
6.10
Ordonner le listing des nuplets : order by
Cette clause order by expressions [ASC — DESC] permet d’indiquer dans quel ordre croissant (par d´efaut ou avec ASC) ou d´ecroissant avec DESC on souhaite obtenir les nuplets produits par 50
la clause select. Obtenir les nuplets dans un certain ordre n’est utile que pour un lecteur humain (par exemple : lors d’un jury on aime bien avoir la liste des ´etudiants par moyenne d´ecroissante) ou pour un programme dont l’algorithme a besoin de r´ecup´erer les nuplets dans un ordre bien pr´ecis (par exemple si on veut v´erifier par programme que les num´eros d’´etudiants sont uniques et contig¨ us le plus simple est d’ouvrir un curseur sur les num´eros croissants). Cette clause d’ordre n’est donc utilisable que pour le select. Pour trier les villes par d´epartements croissants, puis populations d´ecroissantes, puis noms croissants : select * from Ville v order by v.Departement asc, v.Population desc, v.Nom; dpt | nom | population -----+-----------+-----------59 | Lille | 20000 59 | Dunkerque | 10000 75 | Paris | 222000 Par d´efaut l’ordre est asc (i.e. croissant), desc demande un ordre d´ecroissant. On n’est ´evidemment pas oblig´e d’ordonner sur toutes les colonnes et on peut trier sur le r´esultat d’une expression : select * from Ville v order by upper (v.Nom) ; -- On peut aussi ordonner sur une colonne de la projection du select : select upper (v.Nom) as nom_MAJ from Ville v order by nom_MAJ ; La clause order by est toujours la derni`ere d’une requˆete.
6.11
La formation de groupes : group by
L’ensemble des nuplets produits par les clauses from et where peut ˆetre partitionn´e en sousensembles ou groupes non vides et disjoints. La mani`ere de partitionner est indiqu´ee par les colonnes donn´ees apr`es la clause group by qu’on appellera clef de groupe : les nuplets ayant la mˆeme valeur pour la clef de groupe font partie du mˆeme groupe. Seules les expressions du group by peuvent figurer en direct dans la projection du select, toute autre expression ou nom de colonne ne peut figurer qu’en param`etre d’une fonction d’agr´egation : cette fonction s’appliquera donc aux nuplets de chaque groupes trait´es s´epar´ement. Par exemple pour calculer la moyenne de chaque ´etudiant on utilise la clef de groupe e.id, e.nom : select e.ide, e.nom, avg (n.note) as moyenne from Etudiant e inner join Note n on e.ide = n.etu group by e.ide, e.nom ; id | nom | moyenne ----+--------+---------1 | Alfred | 13 3 | Julie | 15 51
Une telle requˆete peut constituer un nombre quelconque de groupes (´eventuellement aucun groupe si aucun nuplet n’est retenu par le where qui doit ˆetre ´ecit avant le group by) et elle produira autant de nuplets qu’il y a de groupes. Une mani`ere de visualiser ce regroupement est de remplacer la clause group by par une clause order by dont la clef de tri est la clef de groupe : select e.ide as id, e.nom as nom, n.note as note from Etudiant e inner join Note n on e.ide = n.etu order by e.ide, e.nom ; id | nom | note ----+--------+-----1 | Alfred | 12 1 | Alfred | 14 3 | Julie | 15 Remarquer que dans ce cas on ne peut pas appliquer la fonction avg() sur les notes. Le regroupement devient int´eressant d`es qu’on veut obtenir une information synth´etique sur chaque groupe grˆace aux fonctions d’agr´egation (sinon on peut se contenter du qualificatif distinct de la clause select). Par exemple on souhaite connaˆıtre la moyenne de chaque ´etudiant : select e.ide, e.nom, n.note as note from Etudiant e inner join Note n on e.ide = n.etu ; id | nom | note ----+--------+-----1 | Alfred | 12 1 | Alfred | 14 3 | Julie | 15 select e.ide, e.nom, avg (n.note) as moyenne, count (*) as nb_notes from Etudiant e inner join Note n on e.ide = n.etu group by e.ide, e.nom ; id | nom | moyenne | nb_notes ----+--------+--------------------1 | Alfred | 13 | 2 3 | Julie | 15 | 1 Et encore une mani`ere de lister, pour chaque mati`ere, les ´etudiants qui ont la meilleure note. On remplace, dans la clause from, la table Matiere par la table (virtuelle) des notes maxi de chaque mati`ere : select m_max.nom as matiere, e.nom as nom from Etudiant e inner join Note n on e.ide = n.etu inner join (select m.idm as idm, m.nom as nom, Max (n.note) as note_max -- meilleure note de chaque mati` ere from Matiere m inner join Note n on m.idm = n.mat group by m.idm, m.nom) m_max on n.mat = m_max.idm where n.note = m_max.note_max ; matiere | nom 52
---------+-------BD | Alfred CL | Julie Q.37
En supposant que chaque mati`ere soit dot´ee d’un coefficient coeff, calculer la moyenne pond´er´ee de chaque ´etudiant. On supposera que toutes les notes et coefficients sont renseign´es (is not null). Q.38 Que se passe-t-il si le coefficient d’une mati` ere est ind´efini ? Q.39 Comment calculer une moyenne correcte pour l’´ etudiant si certaines notes ne sont pas ren-
seign´ees ? (si une note n’est pas renseign´ee, il faut ne pas la prendre en compte)
6.11.1
S´ electionner des groupes : la clause having
La sous-clause having s´electionne un groupe de group by est l’´equivalent pour un groupe de la clause where pour une ligne. Elle permet de ne traiter que les groupes qui v´erifient sa condition. Elle peut donc mentionner que des expressions de la clef du group by ou des fonctions d’agr´egation sur les autres colonnes. Par exemple la moyenne des ´etudiants ayant au moins deux notes : select e.ide, e.nom, AVG (n.note) as moyenne, count (*) as nb_notes from Etudiant e inner join Note n on e.ide = n.etu group by e.ide, e.nom having count (*) >= 2 ; id | nom | moyenne | nb_notes ----+--------+--------------------1 | Alfred | 13 | 2 Le having ne conserve que les groupes ayant au moins 2 lignes. Q.40 Moyenne pond´ er´ee des ´etudiants ayant une note renseign´ee dans chaque mati`ere. Q.41 Pour chaque ´ etudiant, nombre de mati`eres pour lesquelles il a une note d´efinie. Q.42 Quelle sera la valeur syst´ ematique d’une requˆete ayant un group by muni de la clause having
count (*) >= 1 ? Q.43 Donnez une nouvelle version de la requˆ ete listant les ´etudiants inscrits `a toutes les UE.
6.11.2
group by et informations ind´ efinies
Lors d’un group by sur une seule expression E, Oracle 10 consid`ere que toutes les lignes pour lesquelles E est ind´efinie (is null) font partie du mˆeme groupe (ce qui n’est pas plus ´evident que de consid´erer qu’elle forment autant de groupes diff´erents). PostgreSQL a la mˆeme attitude. Le mieux serait cependant d’expliciter la valeur ind´efnie : select coalesce (, ’inconnu’), ... ... group by coalesce (, ’inconnu’), ... Attention : ’inconnu’ doit ˆetre du mˆeme type que . create table X (N Int4) ; insert into X values (2) ; 53
insert insert insert insert insert
into into into into into
X X X X X
values values values values values
(null) ; (null) ; (null) ; (2) ; (3) ;
select coalesce (N, -1) as valeur, count (*) as cardinal from X group by coalesce (N, -1) ; valeur | cardinal --------+---------1 | 3 3 | 1 2 | 2 Q.44 Mettre en place une exp´ erience pour savoir comment se comporte votre SGBD favori dans ce
cas.
6.12
Les jointures externes : outer join
Dans l’exercice pr´ec´edent, le probl`eme est qu’on ne voit pas Marc car n’ayant pas de notes il ne fait pas partie de la jointure (figure 6.1 page 37). On peut r´esoudre ce probl`eme grˆace `a une jointure externe sur la table Etudiant (left outer join) : un ´etudiant n’ayant aucune note fera alors partie de la jointure mais toutes les colonnes relatives `a la partie Note seront ind´efinies (Oracle10, PostgreSQL92) : select e.ide, e.nom, n.note from Etudiant e left outer join Note n on e.ide = n.etu ; id | nom | note ----+--------+-----1 | Alfred | 12 1 | Alfred | 14 2 | Marc | <--nuplet suppl´ ementaire d^ u au left outer join (note ind´ efinie) 3 | Julie | 15 Si un nuplet Etudiant n’a pas de note, le left outer join le concat`ene quand mˆeme avec un nuplet Note dont toutes les colonnes sont ind´efinies (is null). Cette jointure externe est signal´ee par left outer join. Le left d´esigne la table dont on veut conserver tous les nuplets : celle de gauche, on peut utiliser right pour conserver les lignes de la table de droite ou full pour conserver les lignes des deux tables. Q.45 Dans la requˆ ete pr´ec´edente, qu’obtiendrait-on avec une jointure externe conservant les lignes de la table de droite : right outer join qui conserve les lignes de la table de droite ? La jointure externe n’est pas une primitive car on peut l’exprimer grˆace aux op´erateurs pr´ec´edents, voici l’´equivalent de la requˆete pr´ec´edente : select e.ide as id, e.nom as nom, n.note as note from Etudiant e inner join Note n on e.ide = n.etu union select e.ide as id, e.nom as nom, null as note from Etudiant e 54
where e.ide not in (select distinct n.etu from Note n) ; id | nom | note ----+--------+-----1 | Alfred | 12 1 | Alfred | 14 2 | Marc | 3 | Julie | 15 Il suffit de rajouter le group by pour obtenir des informations synth´etiques par ´etudiant (Oracle10, PostgreSQL, SQL92) : select e.ide, e.nom, count (n.etu) as nb_notes from Etudiant e left outer join Note n on e.ide = n.etu group by e.ide, e.nom ; id | nom | nb_notes ----+--------+---------1 | Alfred | 2 2 | Marc | <-- car n.etu est ind´ efini pour Marc 3 | Julie | 1 La fonction count (expression) compte le nombre de fois que expression est d´efinie. n.etu ´etant ind´efini pour Marc, son nombre de mati`eres vaut z´ero. La fonction AVG n’est d´efinie que si son expression est d´efinie au moins une fois pour les diff´erents nuplets du groupe. Elle est donc ind´efinie pour le groupe (2, Marc). Les jointures sont (Oracle10, PostgreSQL, SQL92) : inner join : jointure classique (interne) left outer join : jointure externe conservant les lignes de la table de gauche qui ne s’apparient avec aucune ligne de la table de droite, right outer join : comme ci-dessus mais ce sont les lignes de la table de droite qui sont conserv´ees, full outer join : pour une jointure externe compl`ete (conservation des lignes des tables de gauche et de droite) Et il est possible de pr´efixer chacune de ces possibilit´es par natural pour indiquer une ´equi-jointure, `a condition que les clefs ´etrang`eres aient le mˆeme nom que les clefs primaires qu’elles r´ef´erencent, la clause on ´etant alors interdite l’´egalit´e sera faite implicitement. Exemple : liste des couples ´etudiant, mati`ere, mˆeme pour les ´etudiants n’ayant aucune note et mati`ere est alors ind´efinie : select e.nom, coalesce (m.nom, ’aucune mati` ere’) from Etudiant e left outer join Note n on e.ide = n.etu left outer join Matiere m on n.mat = m.idm ; nom | coalesce --------+----------------Alfred | BD Alfred | CL 55
Julie Marc
6.13
| CL | aucune mati` ere
Contraintes sur l’usage des fonctions d’agr´ egation
Une clause on ne peut mentionner aucune fonction d’agr´egation, elle s’applique `a la construction d’une concat´enation de lignes. Une clause where ne peut mentionner aucune fonction d’agr´egation car elle s’applique `a exactement une ligne de la clause from. Cependant elle peut contenir une sous-requˆete utilisant des fonctions d’agr´egation car une sous-requˆete est un nouveau monde et n’a donc pas d’impact sur la clause where, par exemple pour avoir les notes des ´etudiants sup´erieures `a leurs moyennes : select e.ide as id, e.nom as nom, n.note as note from Etudiant e inner join Note n on n.etu = e.ide where n.note > (select Avg (n.note) as moyenne from Note n where n.etu = e.ide) ; id | nom | note ----+--------+-----1 | Alfred | 14 Une clause group by ne peut mentionner aucune fonction d’agr´egation. Une clause having peut mentionner des fonctions d’agr´egation. Les colonnes clef du group by peuvent apparaˆıtre en dehors ou `a l’int´erieur de fonctions d’agr´egation, les autres colonnes doivent absolument apparaˆıtre `a l’int´erieur de fonctions d’agr´egation. La clause select d’une requˆete R peut : 1. si R n’a pas de clause group by : – si R n’est pas une requˆete synth´etique aucune fonction d’agr´egation n’apparaˆıt, – si R est une requˆete synth´etique, toute colonne provenant de sa clause from doit apparaˆıtre dans une fonction d’agr´egation dont la profondeur est exactement de 1. En revanche des constantes ou des colonnes provenant d’une requˆete englobante peuvent apparaˆıtre en dehors des fonctions d’agr´egation, ou `a l’int´erieur, car elles ont une valeur constante pour l’´evaluation de R. 2. si R a une clause group by : – si R n’est pas une requˆete synth´etique alors toute colonne ne faisant pas partie de la clef de groupe doit apparaˆıtre dans une fonction d’agr´egation avec une profondeur de 1. Les colonnes clef de groupe peuvent apparaˆıtre `a l’ext´erieur ou `a l’int´erieur des fonctions d’agr´egation. – si R est une requˆete synth´etique alors toute colonne ne faisant pas partie de la clef de groupe doit apparaˆıtre dans un double emboˆıtement de fonctions d’agr´egation (profondeur de 2) Attention : PostgreSQL ne permet pas d’emboˆıter deux fonctions d’agr´egation (en revanche Oracle le permet). Les colonnes clef de groupe doivent apparaˆıtre `a une profondeur 1 ou 2 dans les fonctions d’agr´egation. En revanche des constantes ou des colonnes provenant d’une requˆete englobante peuvent apparaˆıtre en dehors des fonctions d’agr´egation, ou `a l’int´erieur, car elles ont une valeur constante pour l’´evaluation de R. Par exemple : select Sum (n.note*m.coeff) / Sum (m.coeff) as moyenne_promo from Note n 56
inner join Matiere m on m.idm = n.mat where n.mat = 2 and n.note is not null group by n.etu ; -- Sum porte sur toutes les notes d’un m^ eme ´ etudiant moyenne_promo --------------15 13
6.14
Emplacement des fonctions d’agr´ egation
Une fonction d’agr´egation ne peut ˆetre utilis´ee ni dans une clause on de jointure ni dans la clause where. Il est possible d’emboˆıter des fonctions d’agr´egation dans le select d’une requˆete munie d’une clause group by, mais sans d´epasser une profondeur d’emboˆıtement de deux. Dans ce cas la requˆete donne une information synth´etique des informations obtenues pour chaque groupe, par exemple la moyenne des moyennes des ´etudiants : select Sum (n.note*m.coeff) / Sum (m.coeff) as moyenne_promo from Etudiant e inner join Note n on e.ide = n.etu inner join Matiere m on m.idm = n.mat group by e.ide, e.nom ; moyenne_promo --------------13 15 Cette requˆete calcule la moyenne de chaque ´etudiant, puis la moyenne de ces moyennes. Il est aussi possible d’utiliser des fonctions d’agr´egation dans l’expression du having mais avec une profondeur d’emboˆıtement de un : donc on ne peut y emboˆıter deux fonctions d’agr´egation. Par exemple si on veut la moyenne des moyennes sup´erieures ou ´egales `a 10 : select Avg (n.note) as moyenne_promo from Etudiant e inner join Note n on e.ide = n.etu group by e.ide, e.nom having AVG (n.note) >= 10 ; moyenne_promo --------------13 15
6.15
Pour conclure
En conclusion, l’ex´ecution d’une requˆete se fait conceptuellement dans cet ordre : 1. from produit les nuplets (ou lignes) du produit cart´esien (´eventuellement la jointure pour ANSI SQL et Oracle 9, PostgreSQL), 2. where applique une condition de s´election aux lignes obtenues depuis la clause from, et conserve la ligne si sa condition est vraie. 57
3. group by construit des groupes avec sa clause optionnelle. 4. having applique sa condition au groupe et conserve le groupe si sa condition est vraie. 5. select produit la projection de chaque groupe de nuplets provenant du group by ou de chaque nuplet du where s’il n’y a pas de group by, 6. order by ordonne les nuplets provenant du select. On peut remarquer que l’ordre syntaxique et l’ordre conceptuel n’ont pas grand chose `a voir l’un avec l’autre ! En particulier, la clause select est la derni`ere `a ˆetre ex´ecut´ee.
58
Chapitre 7 Les vues 7.1
Syntaxe PostgreSQL de cr´ eation d’une vue CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query ;
– OR REPLACE permet de changer la requˆete (query). de la vue, il faut que les colonnes produites par la nouvelle requˆete aient le mˆeme sens que dans la requˆete remplac´ee. – name est le nom de la vue. – [ ( column_name \[, ...\] )\] est optionnel et si on le met donne un nom `a chaque colonne produite par la requˆete (query). Si on ne met pas cette liste de colonnes les colonnes ont les noms (as) de la requˆete. – Une vue qualifi´ee par TEMP ou TEMPORARY disparaˆıt lorsque l’utilisateur se d´econnecte de la base de donn´ees. Une vue PostgreSQL n’est pas modifiable.
7.2
Syntaxe PostgreSQL de destruction d’une vue DROP VIEW ;
7.3
Un exemple
En premi`ere approche, une vue est un objet qui associe un nom `a une requˆete. Une fois cr´e´ee, on pourra consulter cette vue comme si c’´etait une table : create table Client ( idc Serial, nom Varchar (20), solde Numeric (10, 2), constraint Client_PK primary key (idc) ) ; insert into Client (nom, solde) values (’Toto’, 55.2) ; insert into Client (nom, solde) values (’TresGold’, 5555.2) ; insert into Client (nom, solde) values (’Pasgold’, 4000.2) ; create view Bon_Client (idc, nom, solde) as select idc, nom, solde from Client 59
where solde > 1000 ; Dans une requˆete une vue est prise comme une table select * from Bon_Client ; idc | nom | solde -----+----------+--------2 | TresGold | 5555.20 3 | Pasgold | 4000.20 select * from Bon_Client where lower (nom) like ’%gold%’ ; idc | nom | solde -----+----------+--------2 | TresGold | 5555.20 3 | Pasgold | 4000.20 select Max (solde) as LeMax, Sum (solde) as LaSomme from Bon_Client ; lemax | lasomme ---------+--------5555.20 | 9555.40 select bc.idc as id_meilleur_client, bc.nom as nom_meilleur_client from Bon_Client bc where bc.solde = (select Max (solde) from Bon_Client) ; id_meilleur_client | nom_meilleur_client --------------------+--------------------2 | TresGold En g´en´eral, une utilisation particuli`ere d’une base de donn´ees ne n´ecessite pas de voir toutes les donn´ees de la base de donn´ees, ceci pour des raisons de confidentialit´e mais aussi tout simplement pour ne pas polluer l’utilisateur avec des informations qui ne le concernent pas. Par exemple les ´etudiants qui con¸coivent l’annuaire des anciens GMI ne peuvent pas voir le salaire individuel que certains anciens renseignent, mais il peuvent en obtenir une moyenne. Ainsi ces ´etudiants n’auront aucun droit sur la table Ancien mais disposeront d’une vue correspondant `a la table Ancien amput´ee de la colonne salaire et d’une vue calculant le salaire moyen. Pour mettre en place une vision limit´ee et appropri´ee `a la mission de l’utilisateur de la base de donn´ees, les vues sont un des outils majeurs (le syst`eme de privil`eges intervient lui aussi). Les vues constituant le cadre juste n´ecessaire `a une utilisation particuli`ere de la base de donn´ees sont un des outils permettant de r´ealiser un sch´ema externe. Quelques usages des vues : – Pour obtenir simplement une information synth´etique. – Pour ´eviter de divulguer certaines informations (nominative par exemple) : une vue peut restreindre le nombre de colonnes consultables, l’utilisateur concern´e pourra consulter la vue mais pas la ou les tables d’o` u elle tire sa valeur. – Pour assurer l’ind´ependance du sch´ema externe vis `a vis du sch´ema interne : on peut esp´erer qu’une 60
modification des tables qui implantent la base de donn´ees permettra de modifier les requˆetes des vues sans changer le sens des informations qu’elles fournissent. Une vue est ´evalu´ee `a chaque consultation. Oracle en d´efinit un grand nombre pour faciliter la consultation de son dictionnaire, par exemple : tab, user_objects, . . . Si le sch´ema externe d’une utilisation n’est constitu´e que de vues, on aurait tendance `a penser que cette utilisation est incapable de modifier la base, ce qui serait parfois tr`es embˆetant ! En fait, comme on le verra, Oracle et PostgreSQL disposent de moyens permettant de modifier la base de donn´ees via les vues d’un sch´ema externe.
7.3.1
Un exemple
Soit la base de donn´ees : create table Client ( id Number (5) primary key, nom Varchar2 (20), solde Number (6, 2) default 0.0) ; create table Commande ( client references Client (id), montant Number (6, 2) default 0.0) ; La vue qui donne la liste des clients avec le montant moyen des commandes qu’il a effectu´ees create view Client_Moyenne (id, nom, montant_moyen) as select Cl.id as id, Cl.Nom as nom, Avg (Co.montant) from Client Cl inner join Commande Co on Co.client = Cl.id group by Cl.nom with read only ; Comme une table, une vue peut ˆetre mentionn´ee dans la clause from d’une requˆete. Si une des tables utilis´ees par la vue est d´etruite, cette derni`ere devient inutilisable. Q.46 Les vues Bon Client et Client Moyenne sont-elles modifiables ?
7.4
Vue modifiable : elle est recalcul´ ee ` a chaque usage
Sans l’option with check option toute insertion est possible, mais ne sera pas forc´ement visible via la vue : create view Mauvaise_Vue (id, nom, solde) as select id, nom, solde from Client where solde > 1000 ; insert into Mauvaise_Vue values (45, ’dupont’,
500) ;
select * from Mauvaise_Vue ; -- on ne voit pas ’dupont’ update Mauvaise_Vue set solde = 300 where id = 45 ; -- aucune ligne mise ` a jour 61
-- OK
delete from Mauvaise_Vue where id = 45 ; -- aucune ligne supprim´ ee
7.5
Deux mots ` a propos de PostgreSQL
En PostgreSQL, on peut modifier les tables sous-jacentes aux vues en cr´eant une r`egle (create rule). Une r`egle permet d’ex´ecuter des commandes suppl´ementaires lorsqu’une commande donn´ee est ex´ecut´ee sur une table ou une vue donn´ee (also) ou `a la place de la commande (instead). create table Etudiant ( ide Serial, nom Varchar (20), constraint Etudiant_PK primary key (ide) ) ; create table Matiere ( idm Serial, nomMat Varchar (20), constraint Matiere_PK primary key (idm) ) ; create table Note ( etudiant Int4, matiere Int4, note Numeric (5, 2), constraint Note_PK primary key (etudiant, matiere), constraint Note_Etudiant_FK foreign key (etudiant) references Etudiant (ide), constraint Note_Matiere_FK foreign key (matiere) references Matiere (idm) ) ;
create view Moyenne (ide, nom, moyenne) as select e.ide, e.nom, coalesce (avg (n.note), -1) from Etudiant e left outer join Note n on e.ide = n.etudiant group by e.ide, e.nom ; select * from Moyenne ; ide | nom | moyenne -----+------+--------------------62
1 | toto | 13.3333333333333333 create rule Creer_Etudiant as on insert to Moyenne do instead Insert into Etudiant (nom) values (new.nom) ; create rule Modifier_Etudiant as on update to Moyenne do instead update Etudiant set nom = new.nom where ide = ide ; create rule Supprimer_Etudiant as on delete to Moyenne do instead (delete from Note where etudiant = old.ide ; delete from Etudiant where ide = old.ide ) ; update Moyenne set nom = ’titi’ where ide = 1; select * from Moyenne ; ide | nom | moyenne -----+------+--------------------1 | titi | 13.3333333333333333 insert into Moyenne (nom) values (’Durand’) ; select * from Etudiant ; ide | nom -----+-------1 | titi 2 | Durand delete from Moyenne where ide = 1 ; select * from Etudiant ; ide | nom -----+-------2 | Durand select * from Note ; etudiant | matiere | note ----------+---------+-----(0 rows) select * from Moyenne ; ide | nom | moyenne -----+--------+--------2 | Durand | -1
63
Chapitre 8 Optimisations (Clef primaire et unique) 8.1
Organisation physique d’un SGBD
La durabilit´e d’une base de donn´ees est assur´ee par son enregistrement sur un disque magn´etique (c’est probablement actuellement la technique la plus utilis´ee). L’unit´e atomique de lecture/´ecriture sur un disque est le secteur ou le bloc (plusieurs secteurs contigus). La taille d’un secteur peut ˆetre de 512 ou 1024 octets voire 4096. ´ Ecrire ou lire un secteur prend un temps ´enorme par rapport a` la mˆeme op´eration en m´emoire centrale. Cela est dˆ u principalement `a l’aspect m´ecanique de l’acc`es au secteur : 1. le bras supportant la tˆete de lecture/´ecriture doit d’abord ˆetre d´eplac´e radialement sur la piste du secteur 2. il faut ensuite attendre que le secteur se pr´esente sous le bras grˆace `a la rotation du disque, 3. enfin il faut lire ou ´ecrire le secteur, la dur´ee de cette op´eration d´epend elle aussi de la vitesse de rotation du disque.
Contenu Index Debut
d’acces :
de la recherche
B+−arbre
de la Table
Fig. 8.1 – Le principe d’utilisation d’un index permettant d’obtenir rapidement une lignre de la table. PostgreSQL organise ses acc`es au disque de la fa¸con suivante : – le bloc est la plus petite unit´e de l’´ecriture/´ecriture dont la taille est fix´ee par la constante BLOCK_SIZE, dont la valeur par d´efaut en PostgreSQL est de 8 kilo-octets, mais on peut la changer dans le fichier postgresql.conf. – l’extent est l’unit´e suivante. Un extent est constitu´e d’un certain nombre de blocs contigus, ce qui garantit un acc`es physique efficace. 64
– le segment est une collection d’extents qui constitue en g´en´eral un seul objet de la base, par exemple le segment de donn´ee d’une table ou le segment d’un index.
8.1.1
Un principe d’organisation d’un nœud du B+-arbre
Un nœud interne (ou aiguillage) ne contient que des clefs et des adresses d’autres nœuds du B+-arbre. Une adresse est en fait le num´ero de page du nœud ou de la feuille point´ee. Chaque nœud interne constitue un aiguillage permettant de trouver le chemin menant `a la feuille contenant la clef cherch´ee et sa valeur, voir la figure 8.2. Ce sont les primary key et unique qui provoquent la cr´eation d’un B+-arbre. C1 B1
C2 B2
C3 ........ B3
Cn Bn
espace libre
Bn+1
Fig. 8.2 – Nœud interne (page disque) constituant un aiguillage : on a C1 < C2 < . . . < Cn , l’´el´ement de clef C telle que Ci−1 < C ≤ Ci ne peut se trouver que dans le sous-arbre Bi . Si C ≤ C1 , C doit se trouver dans B1 . Si Cn < C, C doit se trouver dans Bn+1 . On remarque que ce nœud interne n’est pas satur´e et qu’il pourrait donc accueillir d’autres clefs et sous-arbres.
8.1.2
Un exemple de B+-arbre avec une clef atomique
Voici un exemple de B+-arbre associ´e `a une table ayant des lignes du genre (34, "nom") o` u 34 est la clef.
B+−ARBRE (ou INDEX)
4
10
30
30
31
44
44
55
66
TABLE (31, rr) (30, toto)
(10, oo) (4, bof)
(55, ii)
(66, ii)
(44, oo)
Fig. 8.3 – Chaque rectangle correspond `a un bloc du disque qui peut ˆetre de taille variable. Un autre algorithme plus efficace consiste `a ne faire exploser un nœud que quand c’est indispensable : dans ce cas les explosions se font en remontant le chemin vers la racine : la pile des nœuds p`eres satur´es est alors n´ecessaire dont le fond est le dernier nœud p`ere non satur´e rencontr´e s’il en existe un. Si tous les nœuds de la pile sont satur´es alors le nœud en fond de pile est forc´ement la racine, c’est le cas o` u le B+-arbre verra sa profondeur augmenter de 1 `a la racine : cela justifie le fait que les feuilles sont toujours `a la mˆeme profondeur. Q.47 Comment retrouver la feuille contenant la clef 44 ? ´ etiqueter les blocs lus avec une * Q.48 Comment retrouver les feuilles contenant les clefs de ∈ [25, 44] ? ´ etiqueter les blocs lus avec + Q.49 Dessiner le nouvel ´ etat apr`es insertion dans la table de (45, ”truc”) puis (7, ”truc”).
65
Chapitre 9 D´ ependances fonctionnelles et normalisation L’int´erˆet de la normalisation est d’´eviter les redondances d’information. Une relation universelle est l’unique relation form´ee de tous les attributs pertinents d’un probl`eme. A, B, C, D d´esignent des attributs. R, T, X, Y, Z d´esignent des ensembles d’attributs (´eventuellement vides). F un ensemble de d´ependances fonctionnelles (DF) On notera indiff´eremment X ∪ Y ou XY .
9.1
D´ ependances fonctionnelles
Une DF est not´ee X → Y et exprime que dans toute extension de X ∪ Y les valeurs des attributs de X d´eterminent de fa¸con unique celles des attributs de Y . Autrement dit : si on connaˆıt une valeur de X alors on connaˆıt la valeur de Y lui correspondant. Q.50 Soit la table (num´ ero-de-carte-´etudiant, nom), que peut-on faire de num´ero-de-carte-´etudiant ? X → Y est ´ el´ ementaire si X = {C1 C2 · · · Ck } et que pour tout 1 ≤ i ≤ k on n’a pas X − {Ci} → Y . X → Y est triviale ssi Y ⊆ X, y compris pour Y vide. {num´ero-insee} → {sexe, date-naissance} est ´el´ementaire, Exemple de {num´ero-insee, sexe} → {sexe, date-naissance} n’est pas ´el´ementaire, d´ependances {date-naissance, sexe} → {sexe} est triviale. fonctionnelles : Soit la relation universelle LDF qui d´ecrit une ligne d’une facture : LDF = {num_facture, la_date, qt´ e_vendue, num_client, nom_client, num_produit, produit, prix_produit, qt´ e_produit}. num_facture identifie la facture avec sa la_date, num_client identifie le nom_client et num_produit identifie le produit, prix_produit et qt´ e_produit. Une facture peut avoir plusieurs produits (i.e. plusieurs lignes). Un num_produit apparaˆıt dans une ligne d’une facture. num_facture, num_client et num_produit identifient qt´ e_vendue.Un client a au plus une facture par jour. Q.51 Donner l’ensemble des DF ´ el´ementaires de LDF. Q.52 Donner quelques DF triviales et quelques DF non triviales et non ´ el´ementaires de LDF. Q.53 Combien y a-t-il de d´ ependances triviales dont le d´eterminant (partie gauche de X → Y ) est
LDF ?
9.2
La n´ ecessit´ e de d´ ecomposer une relation en sous-relations
Motivation : ´eviter la r´ep´etition (redondance) d’information et l’impossibilit´e de repr´esenter certaines informations tout en essayant de conserver les d´ependances fonctionnelles. 66
Q.54 Donnez le MCD de LDF qui ´ evite des redondances d’information. Q.55 Que doit-on faire pour modifier le prix d’un produit.
On a donc souvent besoin de d´ecomposer (normaliser) une relation en plusieurs sous-relations afin d’´eviter ces anomalies. Q.56 Proposer une telle d´ ecomposition de la relation Ligne-de-Facture et conserver les d´ependances fonctionnelles qui sont conserv´ees par les sous-relations.
9.3
Axiomes de Armstrong
Axiomes de Armstrong Ils permettent de d´eduire de nouY ⊆X ⇒ X→Y velles d´ependances fonctionnelles `a par- (1) trivialit´e (2) augmentation X → Y ⇒ XZ → Y Z tir d’un ensemble F de d´ependances (3) transitivit´e X →Y ∧Y →Z ⇒ X →Z fonctionnelles. Q.57 De R = {A, B, C, D, E, F } muni de F = {AB → CD, B → F }, d´ eduire {DE → E, AB → C, ABD → ADF }. (l’axiome d’augmentation est pr´ecieux, ainsi que le fait que X ∪ X = X) F + est la clˆ oture de l’ensemble de DF F obtenue par application des axiomes de Armstrong. Q.58 Calculer la clˆ oture de F = {A → B} sur R = {A, B}. L’int´erˆet d’une telle clˆoture est qu’elle permet de d´efinir l’´equivalence entre deux ensembles de DF F1 et F2 portant sur la mˆeme relation universelle : F1 est ´equivalent `a F2 ssi F1 + = F2 +. Q.59 Sans passer par la clˆ oture, on veut montrer que sur R = {A, B, C}, F1 = {A → B, B → C} est ´equivalente `a F2 = {A → BC, B → C}. Comment peut-on montrer l’´equivalence de F1 et F2 ? 3 corollaires bien pratiques des axiomes de (4) union / d´ecomposition X →Y ∧X →Z (5) pseudo-transitivit´e X →Y ∧YZ →T (6) augmentation bis X →Y ∧Z →T
Armstrong ⇔ X →YZ ⇒ XZ → T ⇒ XZ → Y T
Q.60 Prouver ces corollaires ` a l’aide des axiomes et des corollaires d´ej`a prouv´es.
Soit R = {A, B, C, D, E, F } munie de : F = {{A, B} → C; {C, D} → {E, F }; {E} → {F, D}} Q.61 Montrer que si on supprime la DF {E} → {F } on perd une information. Q.62 En revanche si on supprime la DF {C, D} → {F } montrer qu’on ne perd rien.
9.4
Calculer les cl´ es candidates d’une relation
Une cl´ e candidate d’une relation R vis `a vis d’un ensemble de d´ependances fonctionnelles F , est un sous-ensemble minimal d’attributs de R qui d´etermine tous les attributs de R. Q.63 Quelles sont les cl´ es candidates de R munie de F = {} ? D´efinition : tout ensemble d’attributs incluant strictement ceux d’une cl´e candidate est peut-ˆetre une super-cl´ e. Cet algorithme d´etermine l’ensemble des cl´es candidates d’une relation R munie d’un ensemble de DF : 1. On construit le graphe des d´ependances, y compris les attributs n’apparaissant dans aucune d´ependance et sont donc des sommets isol´es dans le graphe. 67
2. Les sommets non cibles d’une fl`eche appartiennent `a toutes les cl´es, on les marque. 3. Tant qu’il existe un sommet S d´etermin´e par des sommets, marquer S comme non clef. 4. Effacer tous les sommets marqu´es et les fl`eches qui en partent. 5. Tant qu’il existe un sommet S non source d’une fl`eche, effacer S qui n’appartient `a aucune cl´e. 6. Les sommets restant sont forc´ement dans des cycles, consid´erer s´epar´ement chacun d’eux comme appartenant `a une des cl´es, le marquer puis recommencer en 3 7. S’il ne reste pas de sommet, supprimer toutes les cl´ es non minimales qui peuvent exister et c’est fini. Voici le graphe de C = {Ville, Rue, Zip, D} muni de F = {{Ville, Rue } → Zip, Zip → Ville}. D
Ville
Une cl´e non minimale est : {Ville, Rue, Zip, D}. Rue Les 2 cl´es candidates sont : {{Ville, Rue, D}, {Rue, Zip, D}} Q.64 Dessiner le graphe des d´ ependances de Ligne-de-Facture (voir question Q.51).
Zip
Q.65 Marquer les nœuds du graphe LDF d´ etermin´es directement ou indirectement par (num-facture,
num-client, num-produit) puis montrer qu’on obtient le mˆeme r´esultat en utilisant les DF et les axiomes et corollaires de Armstrong. Q.66 Donner les cl´ es candidates de Ligne-de-Facture.
la relation R = {A, B, C, D, E, F, G, H, I} Q.67 Donner R = {A, B, C, D, E, F, G} les cl´es de : R = {A, B, C, D, E} R = {A, B, C, D, E}
munie des d´ ependances fonctionnelles {A → BC, C → D, BDE → A, F → AG, G → H} {AC → B, B → C, C → DE, D → F, E → F, F → G} {A → DE, BC → A, E → B, D → C} {A → DE, B → AC → A, E → B, D → C}
D´ efinitions des formes normales : BCNF ⇒ 3NF ⇒ 2NF ⇒ 1NF Une forme normale permet de mesurer la qualit´e d’une relation munie de d´ependances fonctionnelles. Par exemple 2NF nous garantit que toutes les cl´es compl`etes sont n´ecessaires pour d´eterminer les attributs n’appartenant `a aucune cl´e : cela permettra d’´eviter des redondances. Par exemple Magasin = {P roduit, Date, P rix, P roducteur} muni de Regle = {{P roduit, Date} → P rix, {P roduit} → P roducteur} a comme cl´es C = {{P roduit, Date}}. Elle n’est donc pas 2NF. Q.68 Pourquoi Magasin n’est pas 2NF ? Donner un exemple de redondance sur Magasin. 1NF Si tout attribut a une valeur atomique. 2NF Une relation est en 2NF si elle est 1NF et que tout attribut n’appartenant `a aucune cl´e candidate est en d´ependance ´el´ementaire ou (irr´eductible) avec chacune des cl´es. (contre-exemple : {A, B, C}, {B → C}) 3NF Une relation est en 3NF si tout attribut A n’appartenant `a aucune cl´e X d´epend de chacune des cl´es par une d´ependance fonctionnelle ´el´ementaire ou directe. Une relation 3NF est aussi 2NF. (contre-exemple : {A, B, C, D}, {AB → C, C → D}, 2NF ? D´ecomposez-la en 3NF) BCNF : Boyce Codd Normal Form Une relation R est BCNF vis `a vis d’un ensemble de DF F , si toute DF non triviale de F + a comme d´eterminant une cl´e primaire ou candidate de R . Un sous-ensemble d’une clef ne doit pas d´ependre d’attributs non clefs. Chaque attribut de R ne faisant pas partie d’une clef doit d´ependre directement d’une clef. Lors d’une d´ecomposition on conserve les attributs constituant les clefs dans le reste des attributs qu’on veut d´ecomposer en BCNF. D´ecomposer {A, B, C, D}, {AB → C, C → D} en BCNF. Par exemple R = {cru, pays,r´egion, qualit´e} munie de {{cru, pays} → {r´egion, qualit´e}, {r´egion} → {pays}} n’est pas BCNF car {r´egion} n’est pas une cl´e. Est-elle 2NF ? 3NF ? Q.69
68
Q.70 Normalit´ e de LDF (voir Q.51) ? Q.71 Normalit´ e de R = {A, B, C, D} munie de F = {AB → CD, BC → D, CD → A} ? Q.72 Normalit´ e de R = {A, B, C, D} munie de F = {A → BC, B → C, C → B} ?
9.5
D´ ecomposer une relation sans perte d’information
Quand une relation ne satisfait pas la normalit´e souhait´ee, on la d´ecompose en deux sous-relations. Si cette d´ecomposition ne satisfait toujours pas la normalit´e souhait´ee on pourra `a nouveau les d´ecomposer : le processus de d´ecomposition est it´eratif. Cette technique presque m´ecanique de d´ecomposition risque de donner un r´esultat similaire `a celui obtenu par une approche plus intuitive comme par exemple la conception du MCD de Merise. Soient la relation R munie de F et R1 , R2 une d´ecomposition de R (i.e. R1 ∪ R2 = R et R1 ∩ R2 n’est pas vide). Cette d´ecomposition est sans perte d’information vis `a vis de F si toute extension r de R v´erifiant F est ´egale `a ΠR1 (r) ⊲⊳ ΠR2 (r) = r, cette jointure naturelle se fait par ´egalit´e sur les colonnes de R1 ∩ R2 . Soit R = {A, B, C} munie de F = {A → C}. Pour l’exemple d’extension donn´e `a droite, montrer que les d´ecompositions suivantes de R : Q.73 R1 = {A, C}, R2 = {A, B} ne perd pas d’information.
A B C a1 b1 c1 a1 b2 c1 a2 b2 c1 Q.74 R1 = {B, C}, R2 = {A, C} perd de l’information. Le principe de non perte d’information est ´evidemment incontournable lors d’une d´ecomposition ! D’o` u l’importance du th´eor`eme suivant. Th´ eor` eme de d´ ecomposition sans perte d’information Soient R = {A1 , A2 , . . . , An } un sch´ema relationnel, F un ensemble de d´ependances fonctionnelles et X, Y, Z une partition de R telle que X → Y ∈ F + et X → Z ∈ F +. Alors R1 = X ∪ Y, R2 = X ∪ Z est une d´ecomposition de R sans perte d’information1 . X, Y, Z est une partition de R ⇔ (X ∪ Y ∪ Z = R) ∧ (X ∩ Y = ⊘) ∧ (X ∩ Z = ⊘) ∧ (Y ∩ Z = ⊘) D´emonstration : Soit r une valeur quelconque de R et r1 = ΠR1 (r), r2 = ΠR2 (r). On montre d’abord que r1 ⊲⊳ r2 ⊆ r, pour cela on peut montrer que r1 ⊲⊳ r2 6⊆ r est une absurdit´e : supposons que (xi , yi ) ∈ r1 et (xi , zi ) ∈ r2 et que (xi , yi, zi ) 6∈ r, puisque (xi , yi ) ∈ r1 et (xi , zi ) ∈ r2 ont ´et´e obtenus par projection de r, c’est qu’il existe deux nuplets (xi , yi , zi′ ), (xi , yi′ , zi ) appartenant `a r, or X → Y on a donc yi = yi′ et donc (xi , yi , zi ) ∈ r. De la mˆeme mani`ere on montre que r ⊆ r1 ⊲⊳ r2 . Q.75 Montrer que la condition du th´ eor`eme est aussi n´ecessaire, c’est `a dire que si une d´ecomposition
est sans perte alors elle v´erifie n´ecessairement la condition du th´eor`eme. Suggestion : montrer que si on n’a ni R1 ∩ R2 → R1 ni R1 ∩ R2 → R2 alors la d´ecomposition est avec perte, un exemple suffit. Q.76 En SQL, ` a quelles contraintes serait soumis X dans les tables R1 et R2 ?
L’ensemble des DF de Ri est la projection ΠRi (F +) = {X → Y ∈ F + |X ∪ Y ⊆ Ri }. Une d´ecomposition sans perte d’information ne pr´eserve pas toujours les d´ependances fonctionnelles. R = {A, B, C, D} munie de {AB → C, C → D} X = {A, B} Y = {C} Z = {D} Exemple : R1 = {A, B, C} munie de {AB → C} R2 = {A, B, D} munie de {AB→ D} 1
Autrement dit : R1 , R2 est sans perte d’information ssi R = R1 ∪ R2 et (R1 ∩ R2 → R1 ou R1 ∩ R2 → R2 ).
69
mais la d´ependance {C → D} est perdue. On perd donc une contrainte d’int´egrit´e facilement exprimable par une contrainte d’unicit´e ou de cl´e primaire. Il faudra programmer pour garantir que cette d´ependance est pr´eserv´ee lors des modifications de table. Une bien meilleure solution ui conserve toutes les DF : R = {A, B, C, D} munie de {AB → C, C → D} X = {A, B} Y = {C} Z = {D} R1 = {A, B, C} munie de {AB → C} R2 = {C, D} munie de {C → D} Q.77 On d´ ecompose la relation R de la question Q.71 en R1 = {A, B, C}, R2 = {A, B, D}. Cette d´ecomposition est-elle sans perte ? Quelles sont les DF conserv´ees par cette d´ecomposition ? Q.78 D´ ecomposer LDF (voir Q.51) en sous-relations qui sont toutes BCNF, cette d´ecomposition
conserve-t-elle toutes les DF ? Remarque : pour un mˆeme probl`eme R muni de F il peut y avoir plusieurs d´ecompositions diff´erentes permettant d’obtenir des sous-relations v´erifiant une forme normale. Attention : une d´ecomposition BCNF sans perte d’information peut perdre des d´ependances fonctionnelles (ce n’est pas le cas de 3NF).
Application (emprunt´ e au poly de Mireille Clerbout) Soit la relation D = {d´epˆot, journal, titre, cat´egorie, tx com, prix, adr d´epˆot, jour, quantit´e} munie des d´ependances F : {d´epˆot} → {adr d´epˆot} {cat´egorie} → {tx com} {titre} → {journal} {d´epˆot, journal, jour} → {quantit´e} {journal} → {titre, prix, cat´egorie, tx com} Utilisez des diminutifs pour faire les questions, par exemple D pour d´epˆot, Jl pour journal, Jr pour jour . . .. Q.79 D´ eterminer les cl´es de D munie de F et montrer qu’elle n’est pas BCNF (section 9.4). Q.80
D´ecomposer D par ´etapes successives en sous-relations qui sont BCNF et qui conservent, globalement, toutes les DF de F (section 9.5).
Q.81 Dessiner le MCD de la d´ ecomposition obtenue.
´ Q.82 Ecrire les ordres SQL de cr´eation des tables BCNF et leurs garnissages `a partir d’une table D d´ej` a peupl´ee.
