Cours BD

Base de Donnes et langage SQL
(IUT, dpartement informatique, 1re anne)
Laurent AUDIBERT
Institut Universitaire de Technologie de Villetaneuse Dpartement Informatique Avenue Jean-Baptiste Clment 93430 Villetaneuse Adresse lectronique : laurent[dot]audibert[at]iutv[dot]univ-paris13[dot]fr
Avant-propos
Aujourdhui, la disponibilit de systmes de gestion de base de donnes ables permet aux organisations de toutes tailles de grer des donnes ecacement, de dployer des applications utilisant ces donnes et de les stocker. Les bases de donnes sont actuellement au cur du systme dinformation des entreprises. Ce cours est consacr aux bases de donnes relationnelles, cest--dire aux bases conues suivant le modle relationnel et manipules en utilisant lalgbre relationnelle. Il sagit, ce jour, de la mthode la plus courante pour organiser et accder des ensembles de donnes. Ce cours constitue galement une bonne introduction au langage SQL (Structured Query Language) qui peut tre considr comme le langage daccs normalis aux bases de donnes relationnel. Le langage SQL est support par la plupart des systmes de gestion de bases de donnes commerciaux (comme Oracle) et du domaine libre (comme PostgreSQL). Dans la premire partie, aprs une description concise du modle entit-association, nous abordons le modle relationnel, le passage du modle entit-association au modle relationnel et enn lalgbre relationnel. La seconde partie est entirement consacr au langage SQL. Nous y dtaillons les instructions du langage de dnition de donnes et celles du langage de manipulation de donnes. Dirents exercices de travaux dirigs et de travaux pratiques ponctuent ce cours. Des exemples de corrections de certains des exercices sont regroups dans la dernire partie du document. Ce document constitue le support du cours Base de Donnes et langage SQL dispens aux tudiants du dpartement dinformatique de linstitut universitaire de technologie de Villetaneuse en semestre dcal. Ce support a t ralis en utilisant les ouvrages cits en bibliographie. Vous trouverez ce document en ligne (pour avoir la dernire version par exemple) ladresse suivante : http://www-lipn.univ-paris13.fr/~audibert/pages/enseignement/cours.htm
Table des matires

1 Bases de donnes relationnelles 1.1 Introduction aux bases de donnes {S1} . . . . . . . . . . . . . . . . 1.1.1 Notion de base de donnes . . . . . . . . . . . . . . . . . . . 1.1.2 Modle de base de donnes . . . . . . . . . . . . . . . . . . . 1.2 Systme de gestion de base de donnes {S1} . . . . . . . . . . . . . . 1.2.1 Principes de fonctionnement . . . . . . . . . . . . . . . . . . 1.2.2 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.3 Niveaux de description des donnes . . . . . . . . . . . . . . 1.2.4 Quelques SGBD connus et utiliss . . . . . . . . . . . . . . . 1.3 Rappels sur le modle entit-association {S1} . . . . . . . . . . . . . 1.3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.3.2 Modle entit-association . . . . . . . . . . . . . . . . . . . . 1.4 Modle relationnel {S1} . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.2 Dnitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.3 Normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.4 Passage du modle entit-association au modle relationnel 1.5 Travaux Dirigs Modle relationnel {S1} . . . . . . . . . . . . . . . 1.6 Algbre relationnelle {S2} . . . . . . . . . . . . . . . . . . . . . . . . 1.6.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.2 Slection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.3 Projection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.4 Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.5 Intersection . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.6 Dirence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.7 Produit cartsien . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.8 Jointure, theta-jointure, equi-jointure, jointure naturelle . . . 1.6.9 Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.7 Travaux Dirigs algbre relationnelle {S2} . . . . . . . . . . . . . . 1.7.1 Exercices de comprhension de requtes . . . . . . . . . . . 1.7.2 Trouver la bonne requte . . . . . . . . . . . . . . . . . . . . SQL 2.1 Introduction {S3} . . . . . . . . . . . . . . . . . . . . . . . 2.1.1 Prsentation gnrale . . . . . . . . . . . . . . . . 2.1.2 Catgories dinstructions . . . . . . . . . . . . . . 2.1.3 PostgreSQL . . . . . . . . . . . . . . . . . . . . . . 2.2 Dnir une base Langage de dnition de donnes {S3} 2.2.1 Introduction aux contraintes dintgrit . . . . . . 2.2.2 Crer une table : CREATE TABLE . . . . . . . . . . . 2.2.3 Contraintes dintgrit . . . . . . . . . . . . . . . . 2.2.4 Supprimer une table : DROP TABLE . . . . . . . . . 2.2.5 Modier une table : ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 9 9 10 11 11 11 12 12 13 13 13 15 15 16 17 18 20 21 21 21 21 22 22 23 23 23 25 26 26 28 29 29 29 30 31 32 32 32 33 35 35
TABLE DES MATIRES
Modier une base Langage de manipulation de donnes {S3} . . . . . . . . . . . . . 2.3.1 Insertion de n-uplets : INSERT INTO . . . . . . . . . . . . . . . . . . . . . . . . 2.3.2 Modication de n-uplets : UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . 2.3.3 Suppression de n-uplets : DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . 2.4 Travaux Pratiques SQL : Premire base de donnes {S3} . . . . . . . . . . . . . . . . 2.4.1 Informations pratiques concernant PostgreSQL . . . . . . . . . . . . . . . . . 2.4.2 Premire base de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5 Interroger une base Langage de manipulation de donnes : SELECT (1re partie) {S4} 2.5.1 Introduction la commande SELECT . . . . . . . . . . . . . . . . . . . . . . . . 2.5.2 Traduction des oprateurs de lalgbre relationnelle (1re partie) . . . . . . . . 2.5.3 Syntaxe gnrale de la commande SELECT . . . . . . . . . . . . . . . . . . . . . 2.5.4 La clause SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5.5 La clause FROM (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5.6 La clause ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5.7 La clause WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5.8 Les expressions rgulires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.6 Travaux Pratiques SQL : Premires requtes {S4-5} . . . . . . . . . . . . . . . . . . . 2.6.1 Premires requtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.6.2 Requtes dj rsolues en utilisant lalgbre relationnelle . . . . . . . . . . . . 2.6.3 Utilisation des expressions rgulires . . . . . . . . . . . . . . . . . . . . . . . 2.7 Interroger une base Langage de manipulation de donnes : SELECT (2e partie) {S6} 2.7.1 La clause FROM (2e partie) : les jointures . . . . . . . . . . . . . . . . . . . . . . 2.7.2 Les clauses GROUP BY et HAVING et les fonctions dagrgation . . . . . . . . . . 2.7.3 Oprateurs ensemblistes : UNION, INTERSECT et EXCEPT . . . . . . . . . . . . . 2.7.4 Traduction des oprateurs de lalgbre relationnelle (2e partie) . . . . . . . . . 2.8 Travaux Pratiques SQL : Requtes avances {S6-7} . . . . . . . . . . . . . . . . . . . 2.8.1 Prix de GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.8.2 Requtes dj rsolues en utilisant lalgbre relationnelle . . . . . . . . . . . . 2.8.3 GROUP toujours ! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.9 Nouveaux objets Langage de dnition de donnes (LDD) {S8} . . . . . . . . . . . 2.9.1 Squences (CREATE SEQUENCE) et type SERIAL . . . . . . . . . . . . . . . . . . 2.9.2 Rgles (CREATE RULE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.9.3 Vues (CREATE VIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.9.4 Schmas (CREATE SCHEMA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.10 Travaux Pratiques SQL : Nouveaux objets {S8} . . . . . . . . . . . . . . . . . . . . . 2.10.1 Squences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.10.2 Schma et vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.10.3 Rgles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.10.4 Toujours des requtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11 SQL intgr {S9} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.2 Connexion au serveur de bases de donnes . . . . . . . . . . . . . . . . . . . . 2.11.3 Excuter des commandes SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.4 Les variables htes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.5 Variables indicateur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.6 Gestion des erreurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.7 Curseurs pour rsultats lignes multiples . . . . . . . . . . . . . . . . . . . . 2.11.8 Prcompilation et compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.11.9 Exemple complet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.3
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
35 35 36 36 37 37 38 40 40 41 41 42 43 43 44 45 48 48 48 49 50 50 54 56 56 59 59 59 59 60 60 61 62 64 65 65 65 65 65 66 66 66 67 67 69 70 71 72 73
TABLE DES MATIRES
Corrections 3.1 Travaux Dirigs 1.5 Modle relationnel . . . . . . . . . . . . . . . . . . . . 3.2 Travaux Dirigs 1.7 Algbre relationnelle . . . . . . . . . . . . . . . . . . 3.2.1 Exercices de conprhension de requtes . . . . . . . . . . . . . . . . 3.2.2 Trouver la bonne requte . . . . . . . . . . . . . . . . . . . . . . . . 3.3 Travaux Pratiques 2.4 PostgreSQL : Premire base de donnes . . . . . . 3.4 Travaux Pratiques 2.6 PostgreSQL : Premires requtes . . . . . . . . . . 3.4.1 Premires requtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.4.2 Requtes dj rsolues en utilisant lalgbre relationnelle . . . . . . 3.4.3 Utilisation des expressions rgulires . . . . . . . . . . . . . . . . . 3.5 Travaux Pratiques 2.8 PostgreSQL : Requtes avances . . . . . . . . . . 3.5.1 Prix de GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.5.2 Requtes dj rsolues en utilisant lalgbre relationnelle . . . . . . 3.5.3 GROUP toujours ! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.6 Travaux Pratiques 2.10 PostgreSQL : Manipulation des nouveaux objets 3.6.1 Squences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.6.2 Schma et vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.6.3 Rgles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.6.4 Toujours des requtes . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
75 76 78 78 78 80 81 81 82 83 84 84 84 85 88 88 88 88 89 91
Bibliographie
TABLE DES MATIRES
Chapitre 1
Bases de donnes relationnelles

1.1
1.1.1
Introduction aux bases de donnes

Notion de base de donnes
Description gnrale Il est dicile de donner une dnition exacte de la notion de base de donnes. Une dnition trs gnrale pourrait tre : Un ensemble organis dinformations avec un objectif commun. Peu importe le support utilis pour rassembler et stocker les donnes (papier, chiers, etc.), ds lors que des donnes sont rassembles et stockes dune manire organise dans un but spcique, on parle de base de donnes. Plus prcisment, on appelle base de donnes un ensemble structur et organis permettant le stockage de grandes quantits dinformations an den faciliter lexploitation (ajout, mise jour, recherche de donnes). Bien entendu, dans le cadre de ce cours, nous nous intressons aux bases de donnes informatises. Base de donnes informatise Le rsultat de la conception dune base de donnes informatise est une description des donnes. Par description on entend dnir les proprits densembles dobjets modliss dans la base de donnes et non pas dobjets particuliers. Les objets particuliers sont crs par des programmes dapplications ou des langages de manipulation lors des insertions et des mises jour des donnes. Cette description des donnes est ralise en utilisant un modle de donnes1 . Ce dernier est un outil formel utilis pour comprendre lorganisation logique des donnes. La gestion et laccs une base de donnes sont assurs par un ensemble de programmes qui constituent le Systme de gestion de base de donnes (SGBD). Nous y reviendrons dans la section 1.2. Un SGBD est caractris par le modle de description des donnes quil supporte (hirarchique, rseau, relationnel, objet : cf. section 1.1.2). Les donnes sont dcrites sous la forme de ce modle, grce un Langage de Description des Donnes (LDD). Cette description est appele schma. Une fois la base de donnes spcie, on peut y insrer des donnes, les rcuprer, les modier et les dtruire. Cest ce quon appelle manipuler les donnes. Les donnes peuvent tre manipules non seulement par un Langage spcique de Manipulation des Donnes (LMD) mais aussi par des langages de programmation classiques.
1 cf. section 1.1.2 pour une prsentation gnrale de plusieurs modles de donnes. Le modle entit-association est prsent dans la section 1.3 et le modle relationnel dans la section 1.4
10
CHAPITRE 1. BASES DE DONNES RELATIONNELLES
Enjeux Les bases de donnes ont pris une place importante en informatique, et particulirement dans le domaine de la gestion. Ltude des bases de donnes conduit au dveloppement de concepts, mthodes et algorithmes spciques, notamment pour grer les donnes en mmoire secondaire (i.e. disques durs)2 . En eet, ds lorigine de la discipline, les informaticiens ont observ que la taille de la RAM ne permettait pas de charger lensemble dune base de donnes en mmoire. Cette hypothse est toujours vrie car le volume des donnes ne cesse de saccrotre sous la pousse des nouvelles technologies du WEB. Ainsi, les bases de donnes de demain devront tre capables de grer plusieurs dizaines de traoctets de donnes, gographiquement distribues lchelle dInternet, par plusieurs dizaines de milliers dutilisateurs dans un contexte dexploitation changeant (on ne sait pas trs bien matriser ou prdire les dbits de communication entre sites) voire sur des nuds volatiles. En physique des hautes nergies, on prdit quune seule exprience produira de lordre du pta-octets de donnes par an. Comme il est peu probable de disposer dune technologie de disque permettant de stocker sur un unique disque cette quantit dinformations, les bases de donnes se sont orientes vers des architectures distribues ce qui permet, par exemple, dexcuter potentiellement plusieurs instructions dentre/sortie en mme temps sur des disques dirents et donc de diviser le temps total dexcution par un ordre de grandeur.
1.1.2
Modle de base de donnes
Modle hirarchique Une base de donnes hirarchique est une forme de systme de gestion de base de donnes qui lie des enregistrements dans une structure arborescente de faon ce que chaque enregistrement nait quun seul possesseur (par exemple, une paire de chaussures nappartient qu une seule personne). Les structures de donnes hirarchiques ont t largement utilises dans les premiers systmes de gestion de bases de donnes conus pour la gestion des donnes du programme Apollo de la NASA. Cependant, cause de leurs limitations internes, elles ne peuvent pas souvent tre utilises pour dcrire des structures existantes dans le monde rel. Les liens hirarchiques entre les dirents types de donnes peuvent rendre trs simple la rponse certaines questions, mais trs dicile la rponse dautres formes de questions. Si le principe de relation 1 vers N nest pas respect (par exemple, un malade peut avoir plusieurs mdecins et un mdecin a, a priori, plusieurs patients), alors la hirarchie se transforme en un rseau. Modle rseau Le modle rseau est en mesure de lever de nombreuses dicults du modle hirarchique grce la possibilit dtablir des liaisons de type n-n, les liens entre objets pouvant exister sans restriction. Pour retrouver une donne dans une telle modlisation, il faut connatre le chemin daccs (les liens) ce qui rend les programmes dpendants de la structure de donnes Ce modle de bases de donnes a t invent par C.W. Bachman. Pour son modle, il reut en 1973 le prix Turing. Modle relationnel Une base de donnes relationnelle est une base de donnes structure suivant les principes de lalgbre relationnelle. Le pre des bases de donnes relationnelles est Edgar Frank Codd. Chercheur chez IBM la n des anne 1960, il tudiait alors de nouvelles mthodes pour grer de grandes quantits de donnes car les modles et les logiciels de lpoque ne le satisfaisait pas. Mathmaticien de formation, il tait persuad quil pourrait utiliser des branches spciques des mathmatiques (la thorie des ensembles
2 Il faut savoir que les temps daccs des disques durs sont dun ordre de grandeur suprieur (disons 1000 fois suprieur) aux temps daccs la mmoire RAM. Tout gestionnaire de base de donnes doit donc traiter de manire particulire les accs aux disques.
1.2. SYSTME DE GESTION DE BASE DE DONNES {S1}
11
et la logique des prdicats du premier ordre) pour rsoudre des dicults telles que la redondance des donnes, lintgrit des donnes ou lindpendance de la structure de la base de donnes avec sa mise en uvre physique. En 1970, Codd (1970) publia un article o il proposait de stocker des donnes htrognes dans des tables, permettant dtablir des relations entre elles. De nos jours, ce modle est extrmement rpandu, mais en 1970, cette ide tait considre comme une curiosit intellectuelle. On doutait que les tables puissent tre jamais gres de manire ecace par un ordinateur. Ce scepticisme na cependant pas empch Codd de poursuivre ses recherches. Un premier prototype de Systme de gestion de bases de donnes relationnelles (SGBDR) a t construit dans les laboratoires dIBM. Depuis les annes 80, cette technologie a mri et a t adopte par lindustrie. En 1987, le langage SQL, qui tend lalgbre relationnelle, a t standardis. Cest dans ce type de modle que se situe ce cours de base de donnes. Modle objet La notion de bases de donnes objet ou relationnel-objet est plus rcente et encore en phase de recherche et de dveloppement. Elle sera trs probablement ajoute au modle relationnel.
1.2
1.2.1
Systme de gestion de base de donnes (SGBD)

Principes de fonctionnement
La gestion et laccs une base de donnes sont assurs par un ensemble de programmes qui constituent le Systme de gestion de base de donnes (SGBD). Un SGBD doit permettre lajout, la modication et la recherche de donnes. Un systme de gestion de bases de donnes hberge gnralement plusieurs bases de donnes, qui sont destines des logiciels ou des thmatiques dirents. Actuellement, la plupart des SGBD fonctionne selon un mode client/serveur. Le serveur (sous entendu la machine qui stocke les donnes) reoit des requtes de plusieurs clients et ceci de manire concurrente. Le serveur analyse la requte, la traite et retourne le rsultat au client. Le modle client/serveur est assez souvent implment au moyen de linterface des sockets (voir le cours de rseau) ; le rseau tant Internet. Une variante de ce modle est le modle ASP (Application Service Provider). Dans ce modle, le client sadresse un mandataire (broker) qui le met en relation avec un SGBD capable de rsoudre la requte. La requte est ensuite directement envoye au SGBD slectionn qui rsout et retourne le rsultat directement au client. Quelque soit le modle, un des problmes fondamentaux prendre en compte est la cohrence des donnes. En eet, dans un environnement o plusieurs utilisateurs peuvent accder concurremment une colonne dune table par exemple pour la lire ou pour lcrire, il faut saccorder sur la politique dcriture. Cette politique peut tre : les lectures concurrentes sont autorises mais ds quil y a une criture dans une colonne, lensemble de la colonne est envoye aux autres utilisateurs layant lue pour quelle soit rafrachie.
1.2.2
Objectifs
Des objectifs principaux ont t xs aux SGBD ds lorigine de ceux-ci et ce, an de rsoudre les problmes causs par la dmarche classique. Ces objectifs sont les suivants : Indpendance physique : La faon dont les donnes sont dnies doit tre indpendante des structures de stockage utilises. Indpendance logique : Un mme ensemble de donnes peut tre vu diremment par des utilisateurs dirents. Toutes ces visions personnelles des donnes doivent tre intgres dans une vision globale. Accs aux donnes : Laccs aux donnes se fait par lintermdiaire dun Langage de Manipulation de Donnes (LMD). Il est crucial que ce langage permette dobtenir des rponses aux requtes en un
12
temps raisonnable . Le LMD doit donc tre optimis, minimiser le nombre daccs disques, et tout cela de faon totalement transparente pour lutilisateur. Administration centralise des donnes (intgration) : Toutes les donnes doivent tre centralises dans un rservoir unique commun toutes les applications. En eet, des visions direntes des donnes (entre autres) se rsolvent plus facilement si les donnes sont administres de faon centralise. Non redondance des donnes : An dviter les problmes lors des mises jour, chaque donne ne doit tre prsente quune seule fois dans la base. Cohrence des donnes : Les donnes sont soumises un certain nombre de contraintes dintgrit qui dnissent un tat cohrent de la base. Elles doivent pouvoir tre exprimes simplement et vries automatiquement chaque insertion, modication ou suppression des donnes. Les contraintes dintgrit sont dcrites dans le Langage de Description de Donnes (LDD). Partage des donnes : Il sagit de permettre plusieurs utilisateurs daccder aux mmes donnes au mme moment de manire transparente. Si ce problme est simple rsoudre quand il sagit uniquement dinterrogations et quand on est dans un contexte mono-utilisateur, cela ne lest plus quand il sagit de modications dans un contexte multi-utilisateurs car il faut : permettre deux (ou plus) utilisateurs de modier la mme donne en mme temps et assurer un rsultat dinterrogation cohrent pour un utilisateur consultant une table pendant quun autre la modie. Scurit des donnes : Les donnes doivent pouvoir tre protges contre les accs non autoriss. Pour cela, il faut pouvoir associer chaque utilisateur des droits daccs aux donnes. Rsistance aux pannes : Que se passe-t-il si une panne survient au milieu dune modication, si certains chiers contenant les donnes deviennent illisibles ? Il faut pouvoir rcuprer une base dans un tat sain . Ainsi, aprs une panne intervenant au milieu dune modication deux solutions sont possibles : soit rcuprer les donnes dans ltat dans lequel elles taient avant la modication, soit terminer lopration interrompue.
1.2.3
Niveaux de description des donnes
Pour atteindre certains de ces objectifs (surtout les deux premiers), trois niveaux de description des donnes ont t dnis par la norme ANSI/SPARC. Le niveau interne spcie comment les donnes seront stockes au niveau des units de stockage, des chiers, etc. On appelle cette description le schma interne. Le niveau conceptuel dcrit la structure de toutes les donnes de la base, leurs proprits (i.e. les relations qui existent entre elles : leur smantique inhrente), sans se soucier de limplmentation physique ni de la faon dont chaque groupe de travail voudra sen servir. On appelle cette description le schma conceptuel. Le niveau externe correspond la perception de tout ou partie de la base par un groupe donn dutilisateurs, indpendamment des autres. On appelle cette description le schma externe ou vue. Le niveau externe assure lanalyse et linterprtation des requtes en primitives de plus bas niveau et se charge galement de convertir ventuellement les donnes brutes, issues de la rponse la requte, dans un format souhait par lutilisateur.
1.2.4
Quelques SGBD connus et utiliss
Il existe de nombreux systmes de gestion de bases de donnes, en voici une liste non exhaustive : PostgreSQL : http ://www.postgresql.org/ dans le domaine public ; MySQL : http ://www.mysql.org/ dans le domaine public ; Oracle : http ://www.oracle.com/ de Oracle Corporation ; IBM DB2 : http ://www-306.ibm.com/software/data/db2/ Microsoft SQL : http ://www.microsoft.com/sql/ Sybase : http ://www.sybase.com/linux Informix : http ://www-306.ibm.com/software/data/informix/
1.3. RAPPELS SUR LE MODLE ENTIT-ASSOCIATION {S1}
13
1.3
1.3.1
Rappels sur le modle entit-association

Introduction
Il est dicile de modliser un domaine sous une forme directement utilisable par un SGBD. Une ou plusieurs modlisations intermdiaires sont donc utiles (une telle modlisation est appele Modle Conceptuel de Donnes ou MCD), le modle entit-association constitue lune des premires et des plus courantes. Ce modle, prsent par Chen (1976), permet une description naturelle du monde rel partir des concepts dentit et de relation. Bas sur la thorie des ensembles et des relations, ce modle se veut universel et rpond lobjectif dindpendance donnes-programmes.
1.3.2
Entit
Modle entit-association
Une entit est un objet, une chose concrte ou abstraite qui peut tre reconnue distinctement et qui est caractrise par son unicit. Exemples dentit : Jean Dupont, Pierre Bertrand, un livre que je tiens entre les mains, etc. Les entits ne sont gnralement pas reprsentes graphiquement. Un type-entit est un ensemble dentits qui possdent les mmes caractristiques. Exemples de type-entit : personne, livre, etc. La gure 1.1 montre un exemple de reprsentation graphique dun type-entit. Par abus de langage, on utilise souvent le mot entit en lieu et place du mot type-entit, il faut cependant prendre garde ne pas confondre les deux concepts.
F. 1.1 Exemple de reprsentation graphique dun type-entit
Attribut ou proprit, valeur Un attribut (ou une proprit) est une caractristique associe un type-entit ou un typeassociation. Exemples dattribut : lge dune personne, le code dun fournisseur, le numro dun produit. Au niveau du type-entit ou du type-association, chaque attribut possde un domaine qui dnit lensemble des valeurs possibles qui peuvent tre choisies pour lui (entier, chane de caractres, boolen, etc.). Au niveau de lentit, chaque attribut possde une valeur compatible avec son domaine. La gure 1.2 montre un exemple de reprsentation graphique dun type-entit avec deux attributs.
F. 1.2 Exemple de reprsentation graphique dun type-entit avec deux attributs
Association ou relation Une association (ou une relation) est un lien entre plusieurs entits. Les associations ne sont gnralement pas reprsentes graphiquement. Exemples dassociation : lemprunt par ltudiant Tanidute le 3e exemplaire du livre Matrisez SQL . Un type-association (ou un type-relation) est un ensemble de relations qui possdent les mmes caractristiques. Le type-association dcrit un lien entre plusieurs types-entits. Les associations de ce type-association lient des entits de ces types-entits. Comme les types-entits, les types-associations sont dnis laide dattributs qui prennent leur valeur dans les associations. Un type-association peut
14
ne pas possder dattribut explicite et cela est relativement frquent, mais on verra quil possde au moins des attributs implicites. Exemples de type-association : lemprunt dun livre la bibliothque. Par abus de langage, on utilise souvent le mot association en lieu et place du mot type-association, il faut cependant prendre garde ne pas confondre les deux concepts. La gure 1.3 montre un exemple de reprsentation graphique dun type-association.
F. 1.3 Exemple de reprsentation graphique dun type-association liant deux types-entits
Identiant ou cl Un identiant (ou cl) dun type-entit ou dun type-association est constitu par un ou plusieurs de ses attributs qui doivent avoir une valeur unique pour chaque entit ou association de ce type. Exemples didentiant : le numro dimmatriculation dune voiture, le code ISBN dun livre pour un livre (pas pour un exemplaire). Chaque type-entit possde au moins un identiant, ventuellement form de plusieurs attributs. Chaque type-entit possde au moins un attribut qui, sil est seul, est donc forcment lidentiant. Lidentiant, quil soit explicite ou non, dun type-association doit tre la concatnation des identiants des types-entits lis. On admet cependant un identiant plus naturel, condition quil ne soit quun moyen dexprimer plus simplement cette concatnation. Dans la reprsentation graphique, les attributs dun type-entit ou dun type-relation qui constituent lidentiant sont souligns (cf. gure 1.4).
F. 1.4 Exemple de reprsentation graphique dun type-entit avec trois attributs dont un est un identiant
Cardinalit La cardinalit dune patte reliant un type-association et un type-entit est le nombre de fois minimal et maximal dintervention dune entit du type-entit dans une association du type-association. La cardinalit minimale doit tre infrieure ou gale la cardinalit maximale. Exemple de cardinalit : une personne peut tre propritaire de 0 n appartements, un appartement peut avoir de 1 n propritaires (cf. gure 1.5). Lexpression de la cardinalit est obligatoire pour chaque patte dun type-association (cf. gure 1.5). La cardinalit minimale peut-tre : 0 : Cela signie quune entit peut exister tout en tant implique dans aucune association. 1 : Cela signie quune entit ne peut exister que si elle est implique dans au moins une association. n : Cela signie quune entit ne peut exister que si elle est implique dans plusieurs associations. Attention, le cas n est rare et pose problme. Il est prudent de lviter. La cardinalit maximale peut-tre : 0 : Cela signie quune entit ne peut pas tre implique dans une association. En toute logique, le cas 0 ne doit pas exister : il dmontre un problme de conception puisque le type-entit est inutile au type-association. Il faut alors reconsidrer la cardinalit ou retirer la liaison entre le type-entit et le type-association.
1.4. MODLE RELATIONNEL {S1}
15
1 : Cela signie quune entit peut tre implique dans au maximum une association. n : Cela signie quune entit peut tre implique dans plusieurs associations.
F. 1.5 Exemple de reprsentation graphique de cardinalit
Remarques diverses Une entit ou une association est souvent nomme occurrence ou instance de son type. Un attribut ne peut en aucun cas tre partag par plusieurs types-entits ou types-associations. Il est parfois dicile de faire un choix entre un type-entit et un type-association. Par exemple, un mariage peut tre considr comme un type-association entre deux personnes ou comme un type-entit pour lequel on veut conserver un numro, une date, un lieu, . . ., et que lon souhaite manipuler en tant que tel. Un mme concept du monde rel peut tre reprsent dans certains cas comme un attribut et dans dautres cas comme une entit, selon quil a ou non une existence propre. Par exemple, la marque dune automobile peut tre vue comme un attribut de lentit vhicule de la base de donnes dune prfecture mais aussi comme une entit constructeur automobile dans la base de donnes du Ministre de lIndustrie. Lorsquon ne parvient pas trouver didentiant pour un type-entit, il faut se demander sil ne sagit pas en fait dun type-association. Si ce nest pas le cas, un identiant arbitraire numrique entier peut faire laaire. Lorsque toutes les pattes dun type-association portent la cardinalit 1, 1, il faut se demander si ce type-association et les types-entits lis ne dcrivent pas en fait un seul type-entit. Pour faciliter la lecture du schma, il est assez courant de ne pas y faire gurer les attributs ou de ne conserver que ceux qui font partie des identiants. Les attributs cachs doivent alors absolument tre spcis dans un document part.
1.4
1.4.1
Modle relationnel
Introduction
Le modle relationnel a dj t introduit dans la section 1.1.2. Dans ce modle, les donnes sont stockes dans des tables, sans prjuger de la faon dont les informations sont stockes dans la machine. Le succs du modle relationnel auprs des chercheurs, concepteurs et utilisateurs est d la puissance et la simplicit de ses concepts. En outre, contrairement certains autres modles, il repose sur des bases thoriques solides, notamment la thorie des ensembles et la logique des prdicats du premier ordre. Les objectifs du modle relationnel sont : proposer des schmas de donnes faciles utiliser ; amliorer lindpendance logique et physique (cf. section 1.2.2) ; mettre la disposition des utilisateurs des langages de haut niveau ; optimiser les accs la base de donnes ; amliorer lintgrit et la condentialit ; fournir une approche mthodologique dans la construction des schmas. De faon informelle, on peut dnir le modle relationnel de la manire suivante : les donnes sont organises sous forme de tables deux dimensions, encore appeles relations, dont les lignes sont appeles n-uplet ou tuple en anglais ; les donnes sont manipules par des oprateurs de lalgbre relationnelle ; ltat cohrent de la base est dni par un ensemble de contraintes dintgrit.
16
Au modle relationnel est associe la thorie de la normalisation des relations qui permet de se dbarrasser des incohrences au moment de la conception dune base de donnes.
1.4.2
Dnitions
Attribut Un attribut est un identicateur (un nom) dcrivant une information stocke dans une base. Exemples dattribut : lge dune personne, le nom dune personne, le numro de scurit sociale. Domaine Le domaine dun attribut est lensemble, ni ou inni, de ses valeurs possibles. Par exemple, lattribut numro de scurit sociale a pour domaine lensemble des combinaisons de quinze chires et nom a pour domaine lensemble des combinaisons de lettres (une combinaison comme cette dernire est gnralement appele chane de caractres ou, plus simplement, chane). Relation, schma de relation Une relation est un sous-ensemble du produit cartsien de n domaines dattributs (n > 0). Une relation est reprsente sous la forme dun tableau deux dimensions dans lequel les n attributs correspondent aux titres des n colonnes. Un schma de relation prcise le nom de la relation ainsi que la liste des attributs avec leurs domaines. Le tableau 1.1 montre un exemple de relation et prcise son schma. N Scu 354338532195874 345353545435811 173354684513546 973564213535435 Nom Durand Dubois Dupont Dubois Prnom Caroline Jacques Lisa Rose-Marie
T. 1.1 Exemple de relation de schma Personne(N scu : Entier, Nom : Chane, Prnom : Chane)
Degr Le degr dune relation est son nombre dattributs. Occurrence Une occurrence, ou n-uplets, ou tuples, est un lment de lensemble gur par une relation. Autrement dit, une occurrence est une ligne du tableau qui reprsente la relation. Cardinalit La cardinalit dune relation est son nombre doccurrences. Cl candidate Une cl candidate dune relation est un ensemble minimal des attributs de la relation dont les valeurs identient coup sr une occurrence. La valeur dune cl candidate est donc distincte pour toutes les occurrences. La notion de cl candidate est essentielle dans le modle relationnel. Toute relation a au moins une cl candidate et peut en avoir plusieurs. Ainsi, il ne peut jamais y avoir deux occurrences identiques au sein dune relation. Les cls candidates dune relation nont pas forcment le mme nombre dattributs. Une cl candidate peut tre forme dun attribut arbitraire, utilis cette seule n.
17
Cl primaire La cl primaire dune relation est une de ses cls candidates. Pour signaler la cl primaire, ses attributs sont gnralement souligns. Cl trangre Une cl trangre dune relation est forme dun ou plusieurs de ses attributs qui constituent une cl primaire dans une autre relation. Schma relationnel Un schma relationnel est constitu par lensemble des schmas de relation. Base de donnes relationnelle Une base de donnes relationnelle est constitue par lensemble des n-uplets des direntes relations du schma relationnel.
1.4.3
Normalisation
Pourquoi normaliser ? La normalisation est utile pour : limiter les redondances de donnes ; limiter les pertes de donnes ; limiter les incohrences au sein des donnes ; amliorer les performances des traitements. Dpendance fonctionnelle Soit R(A1 , A2 , . . . An ) un schma de relation, et X et Y des sous-ensembles de A1 , A2 , . . . An . On dit que X dtermine Y ou que Y dpend fonctionnellement de X si, et seulement si, des valeurs identiques de X impliquent des valeurs identiques de Y. On le note : X Y. Autrement dit, il existe une dpendance fonctionnelle entre un ensemble dattributs X et un ensemble dattributs Y, que lon note X Y, si connaissant une occurrence de X on ne peut lui associer quune seule occurrence de Y. Il est essentiel de noter quune dpendance fonctionnelle est une assertion sur toutes les valeurs possibles et non sur les valeurs actuelles : elle caractrise une intention et non une extension de la relation. Une dpendance fonctionnelle lmentaire est une dpendance fonctionnelle de la forme X A, o A est un attribut unique nappartenant pas X et o il nexiste pas X inclus au sens strict dans X (i.e. X X) tel que X A. Autrement dit, une dpendance fonctionnelle est lmentaire si la cible est un attribut unique et si la source ne comporte pas dattributs superus. La question sur llmentarit dune dpendance fonctionnelle ne doit donc se poser que lorsque la partie gauche de la dpendance fonctionnelle comporte plusieurs attributs. On dit que la dpendance fonctionnelle X A est une dpendance fonctionnelle directe sil nexiste aucun attribut B tel que lon puisse avoir X B et B A. En dautres termes, cela signie que la dpendance entre X et A ne peut pas tre obtenue par transitivit. Premire forme normale Une relation est en premire forme normale si, et seulement si, tout attribut contient une valeur atomique (non multiples, non composes).
18
Par exemple, la relation Personne(nom, prnom, rue_et_ville, prnoms_enfants) nest pas en premire forme normale. Deuxime forme normale Une relation est en deuxime forme normale si, et seulement si, elle est en premire forme normale et si tout attribut nappartenant pas une cl ne dpend pas que dune partie de cette cl. De manire quivalente, on dit quune relation est en deuxime forme normale si, et seulement si, elle est en premire forme normale et si toutes les dpendances fonctionnelles entre la cl et les autres attributs sont lmentaires. Une relation peut tre en deuxime forme normale par rapport une de ses cls candidates et ne pas ltre par rapport une autre. Une relation avec une cl primaire rduite un seul attribut est, par dnition, forcment en deuxime forme normale. Troisime forme normale Une relation est en troisime forme normale si, et seulement si, elle est en deuxime forme normale et si tout attribut nappartenant pas une cl ne dpend pas dun attribut non cl. De manire quivalente, on dit quune relation est en troisime forme normale si, et seulement si, elle est en deuxime forme normale et si toutes les dpendances fonctionnelles entre la cl et les autres attributs sont lmentaires et directes. Une relation peut tre en troisime forme normale par rapport une de ses cls candidates et ne pas ltre par rapport une autre. Une relation en deuxime forme normale avec au plus un attribut qui nappartient pas la cl primaire est, par dnition, forcment en troisime forme normale. Forme normale de BOYCE-CODD Une relation est en forme normale de BOYCE-CODD (BCNF) si, et seulement si, les seules dpendances fonctionnelles lmentaires sont celles dans lesquelles une cl dtermine un attribut. Autres formes normales Il existe dautres types de dpendance dont la prise en compte peut encore amliorer la qualit des relations. Notamment, les dpendances multivalues ont permis de dnir la quatrime forme normale et les dpendances de jointure ont donn naissance la cinquime forme normale. Leur usage reste cependant plus marginal.
1.4.4
Passage du modle entit-association au modle relationnel
Pour traduire un schma du modle entit-association vers le modle relationnel, on peut appliquer les rgles suivantes : 1. La normalisation (cf. section 1.4.3) devrait toujours tre eectue avant le passage au modle relationnel. Dans les faits, elle est parfois faite a posteriori, ce qui impose toujours une surcharge de travail importante. 2. Chaque type-entit donne naissance une relation. Chaque attribut de ce type-entit devient un attribut de la relation. Lidentiant est conserv en tant que cl de la relation. 3. Chaque type-association dont aucune patte na pour cardinalit maximale 1 donne naissance une relation. Chaque attribut de ce type-association devient un attribut de la relation. Lidentiant, sil est prcis, est conserv en tant que cl de la relation, sinon cette cl est forme par la concatnation des identiants des types-entits qui interviennent dans le type-association. 4. Un type-association dont au moins une patte a une cardinalit maximale 1 (ce type-association devrait tre binaire et na gnralement pas dattribut) ne devient pas une relation. Il dcrit en eet une dpendance fonctionnelle (cf. section 1.4.3). La relation correspondant au type-entit dont
19
la patte vers le type-association a une cardinalit maximale valant 1, se voit simplement ajouter comme attribut lidentiant de lautre type-entit.
F. 1.6 Exemple trs simpli de modlisation entit-association Comme exemple dapplication, voici les relations dduites du schma entit-association de la gure 1.6 : Patient(Num-Pat, Nom, Num-Mut) Mutuelle(Num-Mut, Nom) Mdecin(Num-Med, Nom) Aection(Num-A, Nom) Hospitalisation(Num-Pat, Num-A, Num-Med, Date-Entre, Date-Sortie)
20
1.5
Travaux Dirigs Modle relationnel
F. 1.7 Exemple simple de MCD
Exercices 1. Indiquer les cardinalits sur les pattes des associations du MCD de la gure 1.7. 2. A partir du MCD de la gure 1.7, tablir le schma relationnel. 3. Quelles sont les dpendances fonctionnelles ? 4. Quelles sont les cls primaires et les cls trangres de chaque relation ?
1.6. ALGBRE RELATIONNELLE {S2}
21
1.6
1.6.1
Algbre relationnelle
Introduction
Lalgbre relationnelle est un support mathmatique cohrent sur lequel repose le modle relationnel. Lobjet de cette section est daborder lalgbre relationnelle dans le but de dcrire les oprations quil est possible dappliquer sur des relations pour produire de nouvelles relations. Lapproche suivie est donc plus oprationnelle que mathmatique. On peut distinguer trois familles doprateurs relationnels : Les oprateurs unaires (Slection, Projection) : ce sont les oprateurs les plus simples, il permettent de produire une nouvelle table partir dune autre table. Les oprateurs binaires ensemblistes (Union, Intersection Dirence) : ces oprateurs permettent de produire une nouvelle relation partir de deux relations de mme degr et de mme domaine. Les oprateurs binaires ou n-aires (Produit cartsien, Jointure, Division) : il permettent de produire une nouvelle table partir de deux ou plusieurs autres tables. Les notations ne sont pas standardises en algbre relationnelle. Ce cours utilise des notations courantes mais donc pas forcment universelles.
1.6.2
Slection
La slection (parfois appele restriction) gnre une relation regroupant exclusivement toutes les occurrences de la relation R qui satisfont lexpression logique E, on la note (E) R. Il sagit dune opration unaire essentielle dont la signature est : relation expression logique relation En dautres termes, la slection permet de choisir (i.e. slectionner) des lignes dans le tableau. Le rsultat de la slection est donc une nouvelle relation qui a les mmes attributs que R. Si R est vide (i.e. ne contient aucune occurrence), la relation qui rsulte de la slection est vide. Le tableau 1.3 montre un exemple de slection. Numro 5 1 12 3 Nom Durand Germain Dupont Germain Prnom Caroline Stan Lisa Rose-Marie
T. 1.2 Exemple de relation Personne
Numro 5 12
Nom Durand Dupont
Prnom Caroline Lisa
T. 1.3 Exemple de slection sur la relation Personne du tableau 1.2 : (Numro5) Personne e
1.6.3
Projection
La projection consiste supprimer les attributs autres que A1 , . . . An dune relation et liminer les n-uplets en double apparaissant dans la nouvelle relation ; on la note (A1 ,...An ) R. Il sagit dune opration unaire essentielle dont la signature est :
22
relation liste dattributs relation En dautres termes, la projection permet de choisir des colonnes dans le tableau. Si R est vide, la relation qui rsulte de la projection est vide, mais pas forcment quivalente (elle contient gnralement moins dattributs). Le tableau 1.4 montre un exemple de slection. Nom Durand Germain Dupont T. 1.4 Exemple de projection sur la relation Personne du tableau 1.2 : Nom R
1.6.4
Union
Lunion est une opration portant sur deux relations R1 et R2 ayant le mme schma et construisant une troisime relation constitue des n-uplets appartenant chacune des deux relations R1 et R2 sans doublon, on la note R1 R2 . Il sagit une opration binaire ensembliste commutative essentielle dont la signature est : relation relation relation Comme nous lavons dj dit, R1 et R2 doivent avoir les mmes attributs et si une mme occurrence existe dans R1 et R2 , elle napparat quune seule fois dans le rsultat de lunion. Le rsultat de lunion est une nouvelle relation qui a les mmes attributs que R1 et R2 . Si R1 et R2 sont vides, la relation qui rsulte de lunion est vide. Si R1 (respectivement R2 ) est vide, la relation qui rsulte de lunion est identique R2 (respectivement R1 ). Le tableau 1.5 montre un exemple dunion. Relation R1 Nom Prnom Durand Caroline Germain Stan Dupont Lisa Germain Rose-Marie Relation R2 Nom Prnom Dupont Lisa Juny Carole Fourt Lisa Relation R Nom Prnom Durand Caroline Germain Stan Dupont Lisa Germain Rose-Marie Juny Carole Fourt Lisa
T. 1.5 Exemple dunion : R = R1 R2
1.6.5
Intersection
Lintersection est une opration portant sur deux relations R1 et R2 ayant le mme schma et construisant une troisime relation dont les n-uplets sont constitus de ceux appartenant aux deux relations, on la note R1 R2 . Il sagit une opration binaire ensembliste commutative dont la signature est : relation relation relation Comme nous lavons dj dit, R1 et R2 doivent avoir les mmes attributs. Le rsultat de lintersection est une nouvelle relation qui a les mmes attributs que R1 et R2 . Si R1 ou R2 ou les deux sont vides, la relation qui rsulte de lintersection est vide. Le tableau 1.6 montre un exemple dintersection.
23
Relation R1 Nom Prnom Durand Caroline Germain Stan Dupont Lisa Germain Rose-Marie Juny Carole
Relation R2 Nom Prnom Dupont Lisa Juny Carole Fourt Lisa Durand Caroline
Relation R Nom Prnom Durand Caroline Dupont Lisa Juny Carole
T. 1.6 Exemple dintersection : R = R1 R2
1.6.6
Dirence
La dirence est une opration portant sur deux relations R1 et R2 ayant le mme schma et construisant une troisime relation dont les n-uplets sont constitus de ceux ne se trouvant que dans la relation R1 ; on la note R1 R2 . Il sagit une opration binaire ensembliste non commutative essentielle dont la signature est : relation relation relation Comme nous lavons dj dit, R1 et R2 doivent avoir les mmes attributs. Le rsultat de la dirence est une nouvelle relation qui a les mmes attributs que R1 et R2 . Si R1 est vide, la relation qui rsulte de la dirence est vide. Si R2 est vide, la relation qui rsulte de la dirence est identique R1 . Le tableau 1.7 montre un exemple de dirence. Relation R1 Nom Prnom Durand Caroline Germain Stan Dupont Lisa Germain Rose-Marie Juny Carole Relation R2 Nom Prnom Dupont Lisa Juny Carole Fourt Lisa Durand Caroline Relation R Nom Prnom Germain Stan Germain Rose-Marie
T. 1.7 Exemple de dirence : R = R1 R2
1.6.7
Produit cartsien
Le produit cartsien est une opration portant sur deux relations R1 et R2 et qui construit une troisime relation regroupant exclusivement toutes les possibilits de combinaison des occurrences des relations R1 et R2 , on la note R1 R2 . Il sagit une opration binaire commutative essentielle dont la signature est : relation relation relation Le rsultat du produit cartsien est une nouvelle relation qui a tous les attributs de R1 et tous ceux de R2 . Si R1 ou R2 ou les deux sont vides, la relation qui rsulte du produit cartsien est vide. Le nombre doccurrences de la relation qui rsulte du produit cartsien est le nombre doccurrences de R1 multipli par le nombre doccurrences de R2 . Le tableau 1.8 montre un exemple de produit cartsien.
1.6.8
Jointure, theta-jointure, equi-jointure, jointure naturelle
Jointure La jointure est une opration portant sur deux relations R1 et R2 qui construit une troisime relation regroupant exclusivement toutes les possibilits de combinaison des occurrences des relations R1 et R2
24
Relation Amie Nom Prnom Fourt Lisa Juny Carole
Relation Cadeau Article Prix livre 45 poupe 25 montre 87
Nom Fourt Fourt Fourt Juny Juny Juny
Relation R Prnom Article Lisa livre Lisa poupe Lisa montre Carole livre Carole poupe Carole montre
Prix 45 25 87 45 25 87
T. 1.8 Exemple de produit cartsien : R = Amie Cadeau
qui satisfont lexpression logique E. La jointure est note R1 E R2 . Il sagit dune opration binaire commutative dont la signature est : relation relation expression logique relation Si R1 ou R2 ou les deux sont vides, la relation qui rsulte de la jointure est vide. En fait, la jointure nest rien dautre quun produit cartsien suivi dune slection : R1 E R2 = E (R1 R2 ) Le tableau 1.9 montre un exemple de jointure. Relation Famille Nom Prnom Age Fourt Lisa 6 Juny Carole 42 Fidus Laure 16 Relation Cadeau AgeC Article Prix 99 livre 30 6 poupe 60 20 baladeur 45 10 dguisement 15 Relation R Age AgeC Article 6 99 livre 6 20 baladeur 6 10 dguisement 42 99 livre 16 99 livre 16 20 baladeur
Nom Fourt Fourt Fourt Juny Fidus Fidus
Prnom Lisa Lisa Lisa Carole Laure Laure
Prix 30 45 15 30 30 45
T. 1.9 Exemple de jointure : R = Famille ((AgeAgeC)(Prix<50)) Cadeau
Theta-jointure Une theta-jointure est une jointure dans laquelle lexpression logique E est une simple comparaison entre un attribut A1 de la relation R1 et un attribut A2 de la relation R2 . La theta-jointure est note R1 E R2 . Equi-jointure Une equi-jointure est une theta-jointure dans laquelle lexpression logique E est un test dgalit entre un attribut A1 de la relation R1 et un attribut A2 de la relation R2 . Lequi-jointure est note R1 A1 ,A2 R2 . Jointure naturelle Une jointure naturelle est une equi-jointure dans laquelle les attributs des relations R1 et R2 portent le mme nom A. Dans la relation construite, lattribut A nest pas dupliqu mais fusionn en un seul R attribut. La jointure naturelle est note R1 2 . Le rsultat de la jointure naturelle est une nouvelle relation qui a tous les attributs de R1 et tous ceux de R2 sauf A. Il est en fait indirent dliminer lattribut A de la relation R1 ou R2 . Le tableau 1.10 montre un exemple de jointure naturelle.
25
Relation Famille Nom Prnom Age Fourt Lisa 6 Juny Carole 40 Fidus Laure 20 Choupy Emma 6
Relation Cadeau Age Article Prix 40 livre 45 6 poupe 25 20 montre 87
Nom Fourt Juny Fidus Choupy
Relation R Prnom Age Lisa 6 Carole 40 Laure 20 Emma 6
Article poupe livre montre poupe
Prix 25 45 87 25
T. 1.10 Exemple de jointure naturelle : R = Famille Cadeau
1.6.9
Division
La division est une opration portant sur deux relations R1 et R2 , telles que le schma de R2 est strictement inclus dans celui de R1 , qui gnre une troisime relation regroupant toutes les parties doccurrences de la relation R1 qui sont associes toutes les occurrences de la relation R2 ; on la note R1 R2 . Il sagit dune opration binaire non commutative dont la signature est : relation relation relation Autrement dit, la division de R1 par R2 (R1 R2 ) gnre une relation qui regroupe tous les n-uplets qui, concatns chacun des n-uplets de R2 , donne toujours un n-uplet de R1 . La relation R2 ne peut pas tre vide. Tous les attributs de R2 doivent tre prsents dans R1 et R1 doit possder au moins un attribut de plus que R2 (inclusion stricte). Le rsultat de la division est une nouvelle relation qui a tous les attributs de R1 sans aucun de ceux de R2 . Si R1 est vide, la relation qui rsulte de la division est vide. Le tableau 1.11 montre un exemple de division. Relation Enseignement Enseignant Etudiant Germain Dubois Fidus Pascal Robert Dubois Germain Pascal Fidus Dubois Germain Durand Robert Durand Relation Etudiant Nom Dubois Pascal Relation R Enseignant Germain Fidus
T. 1.11 Exemple de division : R = Enseignement Etudiant. La relation R contient donc tous les enseignants de la relation Enseignement qui enseignent tous les tudiants de la relation Etudiant.
26
1.7
Travaux Dirigs algbre relationnelle
Soit le schma relationnel suivant : Individu(Num-Ind, Nom, Prnom) Jouer(Num-Ind, Num-Film, Rle) Film(Num-Film Num-Ind, Titre, Genre, Anne) Projection(Num-Cin, Num-Film, Date) Cinma(Num-Cin, Nom, Adresse) Le tableau 1.12 donne une instance de ce schma relationnel.
1.7.1
Exercices de comprhension de requtes
Dans les exercices qui suivent, donnez, sous forme de relation, le rsultat des requtes formules en algbre relationnelle. Slection, et un peu de logique . . . 1. (Anne<1996) Film e 2. (Anne<2000Genre=Drame) Film e 3. (Anne<1990Genre=Drame) Film e 4. ((Anne>2000Genre=Policier)) Film e 5. ((Anne>2000)) (Genre=Drame) Film e Projection 6. (Titre,Genre,Anne) Film e 7. (Genre) Film 8. (Genre) (Anne<2000) Film e Union 9. ((Nom,Prnom) (Prnom=John) Individu) ((Nom,Prnom) (Prnom=Paul) Individu) e e e e Intersection 10. ((Prnom) Individu) ((Role) Jouer) e Dirence 11. ((Nom) (Nom[TW]) Individu) ((Nom) (Prnom=John) Individu)3 e Produit cartsien 12. ((Titre,Genre) (Anne1985) Film) ((Nom) Cinma) e e Jointure 13. (Titre,Nom,Prnom) (Film NumInd Individu) e
3 est un oprateur de comparaison indiquant que llment qui suit nest pas une chane de caractres mais une expression rgulire.
1.7. TRAVAUX DIRIGS ALGBRE RELATIONNELLE {S2}
27
Relation Individu Num-Ind Nom Prnom 01 Kidman Nicole 02 Bettany Paul 03 Watson Emily 04 Skarsgard Stellan 05 Travolta John 06 L. Jackson Samuel 07 Willis Bruce 08 Irons Jeremy 09 Spader James 10 Hunter Holly 11 Arquette Rosanna 12 Wayne John 13 von Trier Lars 14 Tarantino Quentin 15 Cronenberg David 16 Mazursky Paul 17 Jones Grace 18 Glen John Num-Film 05 04 03 02 01 06 07 Num-Ind 01 02 03 04 05 06 07 08 09 10 11 04 16 Num-Ind 13 13 14 15 15 12 18
Relation Projection Num-Cin Num-Film Date 02 05 01/05/2002 02 05 02/05/2002 02 05 03/05/2002 02 04 02/12/1996 01 01 07/05/1996 02 07 09/05/1985 01 04 02/08/1996 04 03 08/04/1994 03 06 02/12/1990 02 02 25/09/1990 03 03 05/11/1994 04 03 06/11/1994 01 06 05/07/1980 02 04 02/09/1996 04 06 01/08/2002 03 06 09/11/1960 01 02 12/03/1988
Relation Film Titre Dogville Breaking the waves Pulp Fiction Faux-Semblants Crash Alamo Dangereusement vtre Num-Cin 02 01 03 04
Genre Drame Drame Policier Epouvante Drame Western Espionnage
Anne 2002 1996 1994 1988 1996 1960 1985
Relation Jouer Num-Film Rle 05 Grace 05 Tom Edison 04 Bess 04 Jan 03 Vincent Vega 03 Jules Winneld 03 Butch Coolidge 02 Beverly & Elliot Mantle 01 James Ballard 01 Helen Remington 01 Gabrielle 05 Chuck 07 May Day
Relation Cinma e Nom Adresse Le Fontenelle 78160 Marly-le-Roi Le Renoir 13100 Aix-en-Provence Gaumont Wilson 31000 Toulouse Espace Cin 93800 Epinay-sur-Seine
T. 1.12 Exemple dinstance de schma relationnel
28
Division 14. ((Nom,Prnom,Titre) (Film NumFilm Jouer NumInd Individu)) e /((Titre) (Film NumInd ((Prnom=Lars) Individu)) e
1.7.2
Trouver la bonne requte
15. Quels sont les titres des lms dont le genre est Drame ? 16. Quels lms sont projets au cinma Le Fontenelle ? 17. Quels sont les noms et prnoms des ralisateurs ? 18. Quels sont les noms et prnoms des acteurs ? 19. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? 20. Quels lms (titres) ont t projets en 2002 ? 21. Donnez le titre des lms raliss par Lars von Trier. 22. Quels sont les ralisateurs qui ont ralis des lms dpouvante et des lms dramatiques ? 23. Quels sont les titres des lms o Nicole Kidman a jou un rle et qui ont t projets au cinma Le Fontenelle ? 24. Quels sont les acteurs qui nont pas jou dans des lms dramatiques ? 25. Quels sont les noms et prnoms des individus dont le prnom est la fois celui dun acteur et celui dun ralisateur sans quil sagisse de la mme personne ? 26. Quels acteurs a-t-on pu voir au cinma Le Fontenelle depuis lan 2000 ? 27. Quels sont les lms qui ont encore t lache 5 annes aprs leur sortie ? 28. Quels sont les cinmas qui ont projet tous les lms ? 29. Quels sont les acteurs que lon a pu voir dans toutes les salles ?
Chapitre 2
SQL
2.1
2.1.1
Introduction
Prsentation gnrale
Introduction Le langage SQL (Structured Query Language) peut tre considr comme le langage daccs normalis aux bases de donnes. Il est aujourdhui support par la plupart des produits commerciaux que ce soit par les systmes de gestion de bases de donnes micro tel que Access ou par les produits plus professionnels tels que Oracle. Il a fait lobjet de plusieurs normes ANSI/ISO dont la plus rpandue aujourdhui est la norme SQL2 qui a t dnie en 1992. Le succs du langage SQL est d essentiellement sa simplicit et au fait quil sappuie sur le schma conceptuel pour noncer des requtes en laissant le SGBD responsable de la stratgie dexcution. Le langage SQL propose un langage de requtes ensembliste et assertionnel. Nanmoins, le langage SQL ne possde pas la puissance dun langage de programmation : entres/sorties, instructions conditionnelles, boucles et aectations. Pour certains traitements il est donc ncessaire de coupler le langage SQL avec un langage de programmation plus complet. De manire synthtique, on peut dire que SQL est un langage relationnel, il manipule donc des tables (i.e. des relations, cest--dire des ensembles) par lintermdiaire de requtes qui produisent galement des tables. Historique rapide En 1970, E.F. CODD, directeur de recherche du centre IBM de San Jos, invente le modle relationnel qui repose sur une algbre relationnelle. Ce modle provoque une rvolution dans lapproche des bases des donnes. En 1977, cration du langage SEQUEL (Structured English Query Language) et mise en place du Systme R, prototype de base de donnes reposant sur la thorie de CODD. SEQUEL continue de senrichir pour devenir SQL (Structured Query Language). En 1981, la socit ORACLE CORP lance la premire version de son systme de gestion de base de donnes relationnelle (SGBDR), IBM sort SQL/DS et RTI lance INGRES. En 1982, IBM sort SQL/DS pour son environnement VM/CMS et lANSI (American National Standard Institute) lance un projet de normalisation dun langage relationnel. En 1983, IBM lance DB2 pour lenvironnement MVS. En 1986, la socite SYBASE lance son SGBDR conu selon le modle Client-Serveur. La premire norme SQL (SQL-1) de lISO (International Standard Organisation) apparat. Il existe dsormais plusieurs dizaines de produits proposant le langage SQL et tournant sur des machines allant des micros aux gros systmes. Depuis, les dirents produits phares ont volu, la norme SQL est passe SQL-2, puis SQL-3. SQL est dsormais un langage incontournable pour tout SGBD moderne. Par contre, bien quune
29
30
CHAPITRE 2. SQL
norme existe, on assiste une prolifration de dialectes propres chaque produit : soit des sousensembles de la norme (certaines fonctionnalits ntant pas implantes), soit des sur-ensembles (ajout de certaines fonctionnalits, propres chaque produit). Oracle et Informix dominent le march actuel, SQL-Server (de Microsoft) tente de simposer dans le monde des PC sous NT. ct des ces produits, trs chers, existent heureusement des systmes libres et gratuits : MySQL et PostgreSQL sont les plus connus. Bien que ces SGBDR naient pas la puissance des produits commerciaux, certains sen approchent de plus en plus. Les dirences notables concernent principalement les environnements de dveloppement qui sont de vritables ateliers logiciels sous Oracle et qui sont rduits des interfaces de programmation C, Python, Perl sous PostgreSQL. Il en va de mme pour les interfaces utilisateurs : il en existe pour PostgreSQL, mais ils nont certainement pas la puissance de leurs quivalents commerciaux. Terminologie Modle relationnel Franais Anglais Relation Relation Domaine Domain Attribut Attribute n-uplet tuple Cl primaire Primary key Standard SQL Table Domaine Colonne Ligne Primary key
2.1.2
Catgories dinstructions
Les instructions SQL sont regroupes en catgories en fonction de leur utilit et des entits manipules. Nous pouvons distinguer cinq catgories, qui permettent : 1. la dnition des lments dune base de donnes (tables, colonnes, clefs, index, contraintes, . . .), 2. la manipulation des donnes (insertion, suppression, modication, extraction, . . .), 3. la gestion des droits daccs aux donnes (acquisition et rvocation des droits), 4. la gestion des transactions, 5. et enn le SQL intgr. Langage de dnition de donnes Le langage de dnition de donnes (LDD, ou Data Denition Language, soit DDL en anglais) est un langage orient au niveau de la structure de la base de donnes. Le LDD permet de crer, modier, supprimer des objets. Il permet galement de dnir le domaine des donnes (nombre, chane de caractres, date, boolen, . . .) et dajouter des contraintes de valeur sur les donnes. Il permet enn dautoriser ou dinterdire laccs aux donnes et dactiver ou de dsactiver laudit pour un utilisateur donn. Les instructions du LDD sont : CREATE, ALTER, DROP, AUDIT, NOAUDIT, ANALYZE, RENAME, TRUNCATE. Langage de manipulation de donnes Le langage de manipulation de donnes (LMD, ou Data Manipulation Language, soit DML en anglais) est lensemble des commandes concernant la manipulation des donnes dans une base de donne. Le LMD permet lajout, la suppression et la modication de lignes, la visualisation du contenu des tables et leur verrouillage. Les instructions du LMD sont : INSERT, UPDATE, DELETE, SELECT, EXPLAIN, PLAN, LOCK TABLE. Ces lments doivent tre valids par une transaction pour quils soient pris en compte.
2.1. INTRODUCTION {S3}
31
Langage de protections daccs Le langage de protections daccs (ou Data Control Language, soit DCL en anglais) soccupe de grer les droits daccs aux tables. Les instructions du DCL sont : GRANT, REVOKE.
Langage de contrle de transaction Le langage de contrle de transaction (ou Transaction Control Language, soit TCL en anglais) gre les modications faites par le LMD, cest--dire les caractristiques des transactions et la validation et lannulation des modications. Les instructions du TCL sont : COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION
SQL intgr Le SQL intgr (Embedded SQL) permet dutiliser SQL dans un langage de troisime gnration (C, Java, Cobol, etc.) : dclaration dobjets ou dinstructions ; excution dinstructions ; gestion des variables et des curseurs ; traitement des erreurs. Les instructions du SQL intgr sont : DECLARE, TYPE, DESCRIBE, VAR, CONNECT, PREPARE, EXECUTE, OPEN, FETCH, CLOSE, WHENEVER.
2.1.3
PostgreSQL
Les systmes traditionnels de gestion de bases de donnes relationnelles (SGBDR) orent un modle de donnes compos dune collection de relations contenant des attributs relevant chacun dun type spcique. Les systmes commerciaux grent par exemple les nombres dcimaux, les entiers, les chanes de caractres, les monnaies et les dates. Il est communment admis que ce modle est inadquat pour les applications de traitement de donnes de lavenir car, si le modle relationnel a remplac avec succs les modles prcdents en partie grce sa simplicit spartiate , cette dernire complique cependant limplmentation de certaines applications. PostgreSQL apporte une puissance additionnelle substantielle en incorporant les quatre concepts de base suivants an que les utilisateurs puissent facilement tendre le systme : classes, hritage, types, fonctions. Dautres fonctionnalits accroissent la puissance et la souplesse : contraintes, dclencheurs, rgles, intgrit des transactions. Ces fonctionnalits placent PostgreSQL dans la catgorie des bases de donnes relationnel-objet. Ne confondez pas cette catgorie avec celle des serveurs dobjets qui ne tolre pas aussi bien les langages traditionnels daccs aux SGBDR. Ainsi, bien que PostgreSQL possde certaines fonctionnalits orientes objet, il appartient avant tout au monde des SGBDR. Cest essentiellement laspect SGBDR de PostgreSQL que nous aborderons dans ce cours. Lune des principales qualits de PostgreSQL est dtre un logiciel libre, cest--dire gratuit et dont les sources sont disponibles. Il est possible de linstaller sur les systmes Unix/Linux et Win32. PostgreSQL fonctionne selon une architecture client/serveur, il est ainsi constitu : dune partie serveur, cest--dire une application fonctionnant sur la machine hbergeant la base de donnes (le serveur de bases de donnes) capable de traiter les requtes des clients ; il sagit dans le cas de PostgreSQL dun programme rsident en mmoire appel postmaster ; dune partie client (psql) devant tre installe sur toutes les machines ncessitant daccder au serveur de base de donnes (un client peut ventuellement fonctionner sur le serveur lui-mme). Les clients (les machines sur lesquelles le client PostgreSQL est install) peuvent interroger le serveur de bases de donnes laide de requtes SQL.
32
CHAPITRE 2. SQL
2.2
2.2.1
Dnir une base Langage de dnition de donnes (LDD)

Introduction aux contraintes dintgrit
Soit le schma relationnel minimaliste suivant : Acteur(Num-Act, Nom, Prnom) Jouer(Num-Act, Num-Film) Film(Num-Film, Titre, Anne) Contrainte dintgrit de domaine Toute comparaison dattributs nest accepte que si ces attributs sont dnis sur le mme domaine. Le SGBD doit donc constamment sassurer de la validit des valeurs dun attribut. Cest pourquoi la commande de cration de table doit prciser, en plus du nom, le type de chaque colonne. Par exemple, pour la table Film, on prcisera que le Titre est une chane de caractres et lAnne une date. Lors de linsertion de n-uplets dans cette table, le systme sassurera que les dirents champs du n-uplet satisfont les contraintes dintgrit de domaine des attributs prcises lors de la cration de la base. Si les contraintes ne sont pas satisfaites, le n-uplet nest, tout simplement, pas insr dans la table. Contrainte dintgrit de relation (ou dentit) Lors de linsertion de n-uplets dans une table (i.e. une relation), il arrive quun attribut soit inconnu ou non dni. On introduit alors une valeur conventionnelle note NULL et appele valeur nulle. Cependant, une cl primaire ne peut avoir une valeur nulle. De la mme manire, une cl primaire doit toujours tre unique dans une table. Cette contrainte forte qui porte sur la cl primaire est appele contrainte dintgrit de relation. Tout SGBD relationnel doit vrier lunicit et le caractre dni (NOT NULL) des valeurs de la cl primaire. Contrainte dintgrit de rfrence Dans tout schma relationnel, il existe deux types de relation : les relations qui reprsentent des entits de lunivers modlis ; elles sont qualies de statiques, ou dindpendantes ; les relations Acteur et Film en sont des exemples ; les relations dont lexistence des n-uplets dpend des valeurs dattributs situes dans dautres relations ; il sagit de relations dynamiques ou dpendantes ; la relation Jouer en est un exemple. Lors de linsertion dun n-uplet dans la relation Jouer, le SGBD doit vrier que les valeurs Num-Act et Num-Film correspondent bien, respectivement, une valeur de Num-Act existant dans la relation Acteur et une valeur Num-Film existant dans la relation Film. Lors de la suppression dun n-uplet dans la relation Acteur, le SGBD doit vrier quaucun n-uplet de la relation Jouer ne fait rfrence, par lintermdiaire de lattribut Num-Act, au n-uplet que lon cherche supprimer. Le cas chant, cest--dire si une, ou plusieurs, valeur correspondante de Num-Act existe dans Jouer, quatre possibilits sont envisageables : interdire la supprssion ; supprimer galement les n-uplets concerns dans Jouer ; avertir lutilisateur dune incohrence ; mettre les valeurs des attributs concerns une valeur nulle dans la table Jouer, si lopration est possible (ce qui nest pas le cas si ces valeurs interviennent dans une cl primaire) ;
2.2.2
Crer une table : CREATE TABLE
Introduction Une table est un ensemble de lignes et de colonnes. La cration consiste dnir (en fonction de lanalyse) le nom de ces colonnes, leur format (type), la valeur par dfaut la cration de la ligne (DEFAULT) et les rgles de gestion sappliquant la colonne (CONSTRAINT).
2.2. DFINIR UNE BASE LANGAGE DE DFINITION DE DONNES {S3}
33
Cration simple La commande de cration de table la plus simple ne comportera que le nom et le type de chaque colonne de la table. A la cration, la table sera vide, mais un certain espace lui sera allou. La syntaxe est la suivante : CREATE TABLE nom_table (nom_col1 TYPE1, nom_col2 TYPE2, ...) Quand on cre une table, il faut dnir les contraintes dintgrit que devront respecter les donnes que lon mettra dans la table (cf. section 2.2.3). Les types de donnes Les types de donnes peuvent tre : INTEGER : Ce type permet de stocker des entiers signs cods sur 4 octets. BIGINT : Ce type permet de stocker des entiers signs cods sur 8 octets. REAL : Ce type permet de stocker des rels comportant 6 chires signicatifs cods sur 4 octets. DOUBLE PRECISION : Ce type permet de stocker des rels comportant 15 chires signicatifs cods sur 8 octets. NUMERIC[(prcision, [longueur])] : Ce type de donnes permet de stocker des donnes numriques la fois entires et relles avec une prcision de 1000 chires signicatifs. longueur prcise le nombre maximum de chires signicatifs stocks et prcision donne le nombre maximum de chires aprs la virgule. CHAR(longueur) : Ce type de donnes permet de stocker des chanes de caractres de longueur xe. longueur doit tre infrieur 255, sa valeur par dfaut est 1. VARCHAR(longueur) : Ce type de donnes permet de stocker des chanes de caractres de longueur variable. longueur doit tre infrieur 2000, il ny a pas de valeur par dfaut. DATE : Ce type de donnes permet de stocker des donnes constitues dune date. TIMESTAMP : Ce type de donnes permet de stocker des donnes constitues dune date et dune heure. BOOLEAN : Ce type de donnes permet de stocker des valeurs Boolenne. MONEY : Ce type de donnes permet de stocker des valeurs montaires. TEXT : Ce type de donnes permet des stocker des chanes de caractres de longueur variable. Cration avec Insertion de donnes On peut insrer des donnes dans une table lors de sa cration par la commande suivante : CREATE TABLE nom_table [(nom_col1, nom_col2, ...)] AS SELECT ... On peut ainsi, en un seul ordre SQL crer une table et la remplir avec des donnes provenant du rsultat dun SELECT (cf. section 2.5 et 2.7). Si les types des colonnes ne sont pas spcis, ils correspondront ceux du SELECT. Il en va de mme pour les noms des colonnes. Le SELECT peut contenir des fonctions de groupes mais pas dORDER BY (cf. section 2.7.2 et 2.5.6) car les lignes dune table ne peuvent pas tre classes.
2.2.3
Contraintes dintgrit
Syntaxe A la cration dune table, les contraintes dintgrit se dclarent de la faon suivante :
34
CHAPITRE 2. SQL
CREATE TABLE nom_table ( nom_col_1 type_1 [CONSTRAINT nom_1_1] contrainte_de_colonne_1_1 [CONSTRAINT nom_1_2] contrainte_de_colonne_1_2 ... ... [CONSTRAINT nom_1_m] contrainte_de_colonne_2_m, nom_col_2 type_2 [CONSTRAINT nom_2_1] contrainte_de_colonne_2_1 [CONSTRAINT nom_2_2] contrainte_de_colonne_2_2 ... ... [CONSTRAINT nom_2_m] contrainte_de_colonne_2_m, ... nom_col_n type_n [CONSTRAINT nom_n_1] contrainte_de_colonne_n_1 [CONSTRAINT nom_n_2] contrainte_de_colonne_n_2 ... ... [CONSTRAINT nom_n_m] contrainte_de_colonne_n_m, [CONSTRAINT nom_1] contrainte_de_table_1, [CONSTRAINT nom_2] contrainte_de_table_2, ... ... [CONSTRAINT nom_p] contrainte_de_table_p ) Contraintes de colonne Les direntes contraintes de colonne que lon peut dclarer sont les suivantes : NOT NULL ou NULL : Interdit (NOT NULL) ou autorise (NULL) linsertion de valeur NULL pour cet attribut. UNIQUE : Dsigne lattribut comme cl secondaire de la table. Deux n-uplets ne peuvent recevoir des valeurs identiques pour cet attribut, mais linsertion de valeur NULL est toutefois autorise. Cette contrainte peut apparatre plusieurs fois dans linstruction. PRIMARY KEY : Dsigne lattribut comme cl primaire de la table. La cl primaire tant unique, cette contrainte ne peut apparatre quune seule fois dans linstruction. La dnition dune cl primaire compose se fait par lintermdiaire dune contrainte de table. En fait, la contrainte PRIMARY KEY est totalement quivalente la contraite UNIQUE NOT NULL. REFERENCES table [(colonne)] [ON DELETE CASCADE] : Contrainte dintgrit rfrentielle pour lattribut de la table en cours de dnition. Les valeurs prises par cet attribut doivent exister dans lattribut colonne qui possde une contrainte PRIMARY KEY ou UNIQUE dans la table table. En labsence de prcision dattribut colonne, lattribut retenu est celui correspondant la cl primaire de la table table spcie. CHECK (condition) : Vrie lors de linsertion de n-uplets que lattribut ralise la condition condition. DEFAULT valeur : Permet de spcier la valeur par dfaut de lattribut. Contraintes de table Les direntes contraintes de table que lon peut dclarer sont les suivantes : PRIMARY KEY (colonne, ...) : Dsigne la concatnation des attributs cits comme cl primaire de la table. Cette contrainte ne peut apparatre quune seule fois dans linstruction. UNIQUE (colonne, ...) : Dsigne la concatnation des attributs cits comme cl secondaire de la table. Dans ce cas, au moins une des colonnes participant cette cl secondaire doit permettre de distinguer le n-uplet. Cette contrainte peut apparatre plusieurs fois dans linstruction. FOREIGN KEY (colonne, ...) REFERENCES table [(colonne, ...)] [ON DELETE CASCADE | SET NULL] : Contrainte dintgrit rfrentielle pour un ensemble dattributs de la table en cours de dnition. Les valeurs prises par ces attributs doivent exister dans lensemble dattributs spci et possder une contrainte PRIMARY KEY ou UNIQUE dans la table table.
2.3. MODIFIER UNE BASE LANGAGE DE MANIPULATION DE DONNES {S3}
35
CHECK (condition) : Cette contrainte permet dexprimer une condition qui doit exister entre plusieurs attributs de la ligne. Les contraintes de tables portent sur plusieurs attributs de la table sur laquelle elles sont dnies. Il nest pas possible de dnir une contrainte dintgrit utilisant des attributs provenant de deux ou plusieurs tables. Ce type de contrainte sera mis en uvre par lintermdiaire de dclencheurs de base de donnes (trigger). Complment sur les contraintes ON DELETE CASCADE : Demande la suppression des n-uplets dpendants, dans la table en cours de dnition, quand le n-uplet contenant la cl primaire rfrence est supprim dans la table matre. ON DELETE SET NULL : Demande la mise NULL des attributs constituant la cl trangre qui font rfrence au n-uplet supprim dans la table matre. La suppression dun n-uplet dans la table matre pourra tre impossible sil existe des n-uplets dans dautres tables rfrenant cette valeur de cl primaire et ne spciant pas lune de ces deux options.
2.2.4
Supprimer une table : DROP TABLE
Supprimer une table revient liminer sa structure et toutes les donnes quelle contient. Les index associs sont galement supprims. La syntaxe est la suivante : DROP TABLE nom_table
2.2.5
Modier une table : ALTER TABLE
Ajout ou modication de colonnes ALTER TABLE nom_table {ADD/MODIFY} ([nom_colonne type [contrainte], ...]) Ajout dune contrainte de table ALTER TABLE nom_table ADD [CONSTRAINT nom_contrainte] contrainte La syntaxe de dclaration de contrainte est identique celle vue lors de la cration de table. Si des donnes sont dj prsentes dans la table au moment o la contrainte dintgrit est ajoute, toutes les lignes doivent vrier la contrainte. Dans le cas contraire, la contrainte nest pas pose sur la table. Renommer une colonne ALTER TABLE nom_table RENAME COLUMN ancien_nom TO nouveau_nom Renommer une table ALTER TABLE nom_table RENAME TO nouveau_nom
2.3
2.3.1
Modier une base Langage de manipulation de donnes (LMD)

Insertion de n-uplets : INSERT INTO
La commande INSERT permet dinsrer une ligne dans une table en spciant les valeurs insrer. La syntaxe est la suivante :
36
CHAPITRE 2. SQL
INSERT INTO nom_table(nom_col_1, nom_col_2, ...) VALUES (val_1, val_2, ...) La liste des noms de colonne est optionnelle. Si elle est omise, la liste des colonnes sera par dfaut la liste de lensemble des colonnes de la table dans lordre de la cration de la table. Si une liste de colonnes est spcie, les colonnes ne gurant pas dans la liste auront la valeur NULL. Il est possible dinsrer dans une table des lignes provenant dune autre table. La syntaxe est la suivante : INSERT INTO nom_table(nom_col1, nom_col2, ...) SELECT ... Le SELECT (cf. section 2.5 et 2.7) peut contenir nimporte quelle clause sauf un ORDER BY (cf. section 2.5.6).
2.3.2
Modication de n-uplets : UPDATE
La commande UPDATE permet de modier les valeurs dune ou plusieurs colonnes, dans une ou plusieurs lignes existantes dune table. La syntaxe est la suivante : UPDATE nom_table SET nom_col_1 = {expression_1 | ( SELECT ...) }, nom_col_2 = {expression_2 | ( SELECT ...) }, ... nom_col_n = {expression_n | ( SELECT ...) } WHERE predicat Les valeurs des colonnes nom_col_1, nom_col_2, ..., nom_col_n sont modies dans toutes les lignes qui satisfont le prdicat predicat. En labsence dune clause WHERE, toutes les lignes sont mises jour. Les expressions expression_1, expression_2, ..., expression_n peuvent faire rfrence aux anciennes valeurs de la ligne.
2.3.3
Suppression de n-uplets : DELETE
La commande DELETE permet de supprimer des lignes dune table. La syntaxe est la suivante : DELETE FROM nom_table WHERE predicat Toutes les lignes pour lesquelles predicat est valu vrai sont supprimes. En labsence de clause WHERE, toutes les lignes de la table sont supprimes.
2.4. TRAVAUX PRATIQUES SQL : PREMIRE BASE DE DONNES {S3}
37
2.4
2.4.1
Travaux Pratiques PostgreSQL : Premire base de donnes

Informations pratiques concernant PostgreSQL
Initialisation et dmarrage de PostgreSQL Linitialisation de PostgreSQL consiste crer un cluster de bases de donnes de la manire suivante : /rpertoire_des_binaires/initdb -D /rpertoire_choisi_pour_la_base Il faut ensuite lancer le serveur PostgreSQL : /rpertoire_des_binaires/postmaster -D /rpertoire_choisi_pour_la_base Une meilleure solution consiste lancer le serveur PostgreSQL en tche de fond et diriger son ux de sortie vers un chier (logfile) : /rpertoire_des_binaires/postmaster -D /rpertoire_choisi_pour_la_base > logfile 2>&1 &
La cration proprement dite dune base de donnes dans le cluster se fait de la manire suivante : createdb nom_de_la_nouvelle_base Nous pouvons enn utiliser linterface en ligne de commande de PostgreSQL en dmarrant un client : psql nom_de_la_nouvelle_base Remarque concernant SELinux Attention, il y a des incompatibilits entre PostgreSQL et SELinux. Si vous rencontrez des problmes, essayez de dsactiver temporairement SELinux (setenforce 0). Cette solution nest pas la meilleure. Si elle marche, essayez de corriger le problme plus nement et de manire dnitive. Par exemple, sous une installation standard de Fedora Core 3, pour corriger le problme, procdez de la manire suivante : Cliquer sur : Menu principal > Paramtres de systme > Niveau de scurit ; Cliquer sur longlet SELinux puis dvelopper SELinux Service Protection et cocher la case Disable SELinux protection for postgresql daemon et valider. PostgreSQL lIUT lIUT, un seul cluster de base de donnes est cr et disponible pour tous les utilisateurs de PostgreSQL. Une seule base de donnes est aecte chaque utilisateur ; son nom tant lidentiant de lutilisateur (i.e. nom de login). Pour crer la base de donnes, il faut : ouvrir internet Galeon, puis cliquer sur Etat de votre base de donnes PostgreSQL et enn sur Crer la base de donnes . Le dmarrage du client se fait de la manire suivante : psql -h nom_serveur -p num_port ma_base identifiant En salle de TP, nom_serveur est aquanux ; les champs num_port, ma_base et identifiant sont optionnels et inutiles, pour information : num_port : 5432 ; ma_base : votre identiant ; identifiant : votre identiant. criture des commandes sous PostgreSQL Toutes les lignes de commandes SQL doivent se terminer par un ; ! Ce nest, par contre, pas le cas des mta-commandes dont il est question ci-dessous.
38
CHAPITRE 2. SQL
Mta-commandes sous PostgreSQL Mta-commandes \? \h \h nom_commande \df \cd nom_repertoire \! nom_commande \i nom_fichier \d \d nom_table \copy nom_table from nom_fichier Description Acher toutes les mta-commandes Acher toutes les commandes SQL Aide concernant une commande SQL particulire Acher toutes les fonctions postgresql Changer de rpertoire courant Excuter une commande shell Lire et excuter un script SQL Acher la liste des tables cres Information concernant une table cre Remplissage dune table partir dun chier texte
2.4.2
Premire base de donnes
1. Crez votre base de donnes en utilisant internet Galeon. 2. Dmarrez un client (psql -h aquanux) pour vous connecter PostgreSQL. 3. Tapez \ ? pour acher la liste des mta-commandes. 4. Tapez \h CREATE TABLE pour connatre la syntaxe de la commande SQL de cration de table. 5. Crez les tables du schma relationnel vu en travaux dirigs section 1.7. Schma relationnel : film (num_film, num_realisateur, titre, genre, annee) cinema (num_cinema, nom, adresse) individu (num_individu, nom prenom) jouer (num_acteur, num_film, role) projection (num_cinema, num_film, jour) Noubliez surtout pas : de choisir correctement le domaine de dnition (i.e. le type) de chacun des attributs ; de bien prciser la cl primaire de chaque relation ; les contraintes dintgrit rfrentielles (i.e. les clefs trangres). 6. Achez la liste des tables cres (\d). 7. Remplissez la main , cest--dire en utilisant la commande INSERT INTO, la table cinema en utilisant le tableau 1.12. 8. Remplissez les tables jouer, film, projection et individu laide des chiers fournis (jouer.txt, film.txt, projection.txt et individu.txt) en utilisant la mta-commande adquate (\copy nom_table from nom_fichier). Devez-vous respecter un ordre de remplissage des tables ? Pourquoi ? 9. Crez un chier cinema.txt permettant de remplir la table cinema en respectant le format des chiers qui vous ont t fournis. 10. Crez un script SQL (GenBDCine.sql) permettant de rgnrer votre base de donne. Ce chier, compos de trois parties, doit permettre de : (a) eacer chacune des tables ; (b) crer chacune des tables comme dans lexercice 5 ; (c) remplir chacune des tables. 11. Restaurez votre base de donnes en utilisant le chier GenBDCine.sql. 12. Vous voulez eacer lacteur John Travolta de la base. Quelles oprations sont ncessaires pour mener bien cet suppression ? Ralisez cette suppression.
2.4. TRAVAUX PRATIQUES SQL : PREMIRE BASE DE DONNES {S3}
39
Remarque Pour acher lensemble des n-uplets dune table, vous pouvez utiliser la commande SQL : SELECT * FROM nom_table.
40
CHAPITRE 2. SQL
2.5
2.5.1
Interroger une base Langage de manipulation de donnes (LMD) : SELECT (1re partie)
Introduction la commande SELECT
Introduction La commande SELECT constitue, elle seule, le langage permettant dinterroger une base de donnes. Elle permet de : slectionner certaines colonnes dune table (projection) ; slectionner certaines lignes dune table en fonction de leur contenu (slection) ; combiner des informations venant de plusieurs tables (jointure, union, intersection, dirence et division) ; combiner entre elles ces direntes oprations. Une requte (i.e. une interrogation) est une combinaison doprations portant sur des tables (relations) et dont le rsultat est lui-mme une table dont lexistence est phmre (le temps de la requte). Syntaxe simplie de la commande SELECT Une requte se prsente gnralement sous la forme : SELECT [ ALL | DISTINCT ] { * | attribut [, ...] } FROM nom_table [, ...] [ WHERE condition ] la clause SELECT permet de spcier les attributs que lon dsire voir apparatre dans le rsultat de la requte ; le caractre toile (*) rcupre tous les attributs de la table gnre par la clause FROM de la requte ; la clause FROM spcie les tables sur lesquelles porte la requte ; la clause WHERE, qui est facultative, nonce une condition que doivent respecter les n-uplets slectionns. Par exemple, pour acher lensemble des n-uplets de la table film, vous pouvez utiliser la requte : SELECT * FROM film De manire synthtique, on peut dire que la clause SELECT permet de raliser la projection, la clause FROM le produit cartsien et la clause WHERE la slection (cf. section 2.5.2). Dlimiteurs : apostrophes simples et doubles Pour spcier littralement une chane de caractres, il faut lentourer dapostrophes (i.e. guillemets simples). Par exemple, pour slectionner les lms policiers, on utilise la requte : SELECT * FROM film WHERE genre=Policier Les date doivent galement tre entoure dapostrophes (ex : 01/01/2005). Comme lapostrophe est utilise pour dlimiter les chanes de caractres, pour la reprsenter dans une chane, il faut la ddoubler (exemple : larbre), ou la faire prcder dun antislash (exemple : l\arbre). Lorsque le nom dun lment dune base de donnes (un nom de table ou de colonne par exemple) est identique un mot clef du SQL, il convient de lentourer dapostrophes doubles. Par exemple, si la table achat possde un attribut date, on pourra crire : SELECT date FROM achat Bien entendu, les mots rservs du SQL sont dconseills pour nommer de tels objets. Les apostrophes doubles sont galement ncessaires lorsque le nom (dune colonne ou dune table) est compos de caractres particuliers tels que les blancs ou autres, ce qui est videmment dconseill.
2.5. INTERROGER UNE BASE LANGAGE DE MANIPULATION DE DONNES : SELECT (1re PARTIE) {S4}41
2.5.2
Traduction des oprateurs de projection, slection, produit cartsien et quijointure de lalgbre relationnelle (1re partie)
Traduction de loprateur de projection Loprateur de projection (A1 ,...An ) (relation) se traduit tout simplement en SQL par la requte : SELECT DISTINCT A_1, ..., A_n FROM relation DISTINCT permet de ne retenir quune occurence de n-uplet dans le cas o une requte produit plusieurs n-uplets identiques (cf. section 2.5.4). Traduction de loprateur de slection Loprateur de slection (prdicat) (relation) se traduit tout simplement en SQL par la requte : e SELECT * FROM relation WHERE prdicat De manire simplie, un prdicat est une expression logique sur des comparaisons. Reportez-vous la section 2.5.7 pour une description plus complte. Traduction de loprateur de produit cartsien Loprateur de produit cartsien relation1 relation2 se traduit en SQL par la requte : SELECT * FROM relation_1, relation_2 Nous reviendrons sur le produit cartsien dans les sections 2.5.5 et 2.7.1. Traduction de loprateur dqui-jointure Loprateur dqui-jointure relation1 A1 ,A2 relation2 se traduit en SQL par la requte : SELECT * FROM relation_1, relation_2 WHERE relation_1.A_1 = relation_2.A_2 Nous reviendrons sur les dirents types de jointure dans la section 2.7.1.
2.5.3
Syntaxe gnrale de la commande SELECT
Voici la syntaxe gnrale dune commande SELECT : SELECT [ ALL | DISTINCT ] { * | expression [ AS nom_affich ] } [, ...] FROM nom_table [ [ AS ] alias ] [, ...] [ WHERE prdicat ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ {UNION | INTERSECT | EXCEPT [ALL]} requte ] [ ORDER BY expression [ ASC | DESC ] [, ...] ] En fait lordre SQL SELECT est compos de 7 clauses dont 5 sont optionnelles : SELECT : Cette clause permet de spcier les attributs que lon dsire voir apparatre dans le rsultat de la requte (cf. section 2.5.4). FROM : Cette clause spcie les tables sur lesquelles porte la requte (cf. section 2.5.5 et 2.7.1). WHERE : Cette clause permet de ltrer les n-uplets en imposant une condition remplir pour quils soient prsents dans le rsultat de la requte (cf. section 2.5.7). GROUP BY : Cette clause permet de dnir des groupes (i.e. sous-ensemble ; cf. section 2.7.2). HAVING : Cette clause permet de spcier un ltre (condition de regroupement des n-uplets) portant sur les rsultats (cf. section 2.7.2). UNION, INTERSECT et EXCEPT : Cette clause permet deectuer des oprations ensemblistes entre plusieurs rsultats de requte (i.e. entre plusieurs SELECT) (cf. section 2.7.3). ORDER BY : Cette clause permet de trier les n-uplets du rsultat (cf. section 2.5.6).
42
CHAPITRE 2. SQL
2.5.4
La clause SELECT
Introduction Comme nous lavons dj dit, la clause SELECT permet de spcier les attributs que lon dsire voir apparatre dans le rsultat de la requte. Pour prciser explicitement les attributs que lon dsire conserver, il faut les lister en les sparant par une virgule. Cela revient en fait oprer une projection de la table intermdiaire gnre par le reste de la requte. Nous verrons dans cette section que la clause SELECT permet daller plus loin que la simple opration de projection. En eet, cette clause permet galement de renommer des colonnes, voire den crer de nouvelles partir des colonnes existantes. Pour illustrer par des exemples les sections qui suivent, nous utiliserons une table dont le schma est le suivant : employee(id_employee, surname, name, salary) Cette table contient respectivement lidentiant, le nom, le prnom et le salaire mensuel des employs dune compagnie. Loprateur toile (*) Le caractre toile (*) permet de rcuprer automatiquement tous les attributs de la table gnre par la clause FROM de la requte. Pour acher la table employee on peut utiliser la requte : SELECT * FROM employee Les oprateurs DISTINCT et ALL Lorsque le SGBD construit la rponse dune requte, il rapatrie toutes les lignes qui satisfont la requte, gnralement dans lordre ou il les trouve, mme si ces dernires sont en double (comportement ALL par dfaut). Cest pourquoi il est souvent ncessaire dutiliser le mot clef DISTINCT qui permet dliminer les doublons dans la rponse. Par exemple, pour acher la liste des prnoms, sans doublon, des employs de la compagnie, il faut utiliser la requte : SELECT DISTINCT name FROM employee Les oprations mathmatiques de base Il est possible dutiliser les oprateurs mathmatiques de base (i.e. +, -, * et /) pour gnrer de nouvelles colonnes partir, en gnrale, dune ou plusieurs colonnes existantes. Pour acher le nom, le prnom et le salaire annuel des employs, on peut utiliser la requte : SELECT surname, name, salary*12 FROM employee Loprateur AS Le mot clef AS permet de renommer une colonne, ou de nommer une colonne cre dans la requte. Pour acher le nom, le prnom et le salaire annuel des employs, on peut utiliser la requte : SELECT surname AS nom, name AS prnom, salary*12 AS salaire FROM employee Loprateur de concatnation Loprateur || (double barre verticale) permet de concatner des champs de type caractres. Pour acher le nom et le prnom sur une colonne, puis le salaire annuel des employs, on peut utiliser la requte : SELECT surname || || name AS nom, salary*12 AS salaire FROM employee
2.5.5
La clause FROM (1re partie)
Comportement Comme nous lavons dj dit, la clause FROM spcie les tables sur lesquelles porte la requte. Plus exactement, cette clause construit la table intermdiaire (i.e. virtuelle), partir dune ou de plusieurs tables, sur laquelle des modications seront apportes par les clauses WHERE, GROUP BY et HAVING pour gnrer la table nale rsultat de la requte. Quand plusieurs tables, spares par des virgules, sont numres dans la clause FROM, la table intermdiaire est le rsultat du produit cartsien de toutes les tables numres. Loprateur AS Le mot clef AS permet de renommer une table, ou de nommer une table cre dans la requte (cest dire une sous-requte) an de pouvoir ensuite y faire rfrence. Le renommage du nom dune table se fait de lune des deux manires suivantes : FROM nom_de_table AS nouveau_nom FROM nom_de_table nouveau_nom Une application typique du renommage de table est de simplier les noms trop long : SELECT * FROM nom_de_table_1 AS t1, nom_de_table_1 AS t2 WHERE t1.A_1 = t2.A_2 Attention, le nouveau nom remplace compltement lancien nom de la table dans la requte. Ainsi, quand une table a t renomme, il nest plus possible dy faire rfrence en utilisant son ancien nom. La requte suivante nest donc pas valide : SELECT * FROM nom_table AS t WHERE nom_table.a > 5 Sous-requte Les tables mentionnes dans la clause FROM peuvent trs bien correspondre des tables rsultant dune requte, spcie entre parenthses, plutt qu des tables existantes dans la base de donnes. Il faut toujours nommer les tables correspondant des sous-requtes en utilisant loprateur AS. Par exemple, les deux requtes suivantes sont quivalentes : SELECT * FROM table_1, table_2 SELECT * FROM (SELECT * FROM table_1) AS t1, table_2 Les jointures Nous traiterons cet aspect de la clause FROM dans la section 2.7.1.
2.5.6
La clause ORDER BY
Comme nous lavons dj dit, la clause ORDER BY permet de trier les n-uplets du rsultat et sa syntaxe est la suivante : ORDER BY expression [ ASC | DESC ] [, ...] expression dsigne soit une colonne, soit une opration mathmatique de base (nous avons abord ce type doprations dans la section 2.5.4 sur La clause SELECT ) sur les colonnes. ASC spcie lordre ascendant et DESC lordre descendant du tri. En labsence de prcision ASC ou DESC, cest lordre ascendant qui est utilis par dfaut. Quand plusieurs expressions, ou colonnes sont mentionnes, le tri se fait dabord selon les premires, puis suivant les suivantes pour les n-uplet qui sont gaux selon les premires. Le tri est un tri interne sur le rsultat nal de la requte, il ne faut donc placer dans cette clause que les noms des colonnes mentionns dans la clause SELECT.
44
CHAPITRE 2. SQL
La clause ORDER BY permet de trier le rsultat nal de la requte, elle est donc la dernire clause de tout ordre SQL et ne doit gurer quune seule fois dans le SELECT, mme sil existe des requtes imbriques ou un jeu de requtes ensemblistes (cf. section 2.7.3). En labsence de clause ORDER BY, lordre des n-uplet est alatoire et non garanti. Souvent, le fait de placer le mot clef DISTINCT sut tablir un tri puisque le SGBD doit se livrer une comparaison des lignes, mais ce mcanisme nest pas garanti car ce tri seectue dans un ordre non contrlable qui peut varier dun serveur lautre.
2.5.7
La clause WHERE
Comportement Comme nous lavons dj dit, la clause WHERE permet de ltrer les n-uplets en imposant une condition remplir pour quils soient prsents dans le rsultat de la requte ; sa syntaxe est la suivante : WHERE prdicat Concrtement, aprs que la table intermdiaire (i.e. virtuelle) de la clause FROM a t construite, chaque ligne de la table est confronte au prdicat prdicat an de vrier si la ligne satisfait (i.e. le prdicat est vrai pour cette ligne) ou ne satisfait pas (i.e. le prdicat est faux ou NULL pour cette ligne) le prdicat. Les lignes qui ne satisfont pas le prdicat sont suprimes de la table intermdiaire. Le prdicat nest rien dautre quune expression logique. En principe, celle-ci fait intervenir une ou plusieurs lignes de la table gnre par la clause FROM, cela nest pas impratif mais, dans le cas contraire, lutilit de la clause WHERE serait nulle. Expression simple Une expression simple peut tre une variable dsigne par un nom de colonne ou une constante. Si la variable dsigne un nom de colonne, la valeur de la variable sera la valeur situe dans la table lintersection de la colonne et de la ligne dont le SGBD cherche vrier si elle satisfait le prdicat de la clause WHERE. Les expressions simples peuvent tre de trois types : numrique, chane de caractres ou date. Une expression simple peut galement tre le rsultat dune sous-requte, spcie entre parenthses, qui retourne une table ne contenant quune seule ligne et quune seule colonne (i.e. une sous-requte retournant une valeur unique). Prdicat simple Un prdicat simple peut tre le rsultat de la comparaison de deux expressions simples au moyen de lun des oprateurs suivants : = != < <= > >= gal dirent strictement infrieur infrieur ou gal strictement suprieur suprieur ou gal
Dans ce cas, les trois types dexpressions (numrique, chane de caractres et date) peuvent tre compars. Pour les types date, la relation dordre est lordre chronologique. Pour les caractres, la relation dordre est lordre lexicographique. Un prdicat simple peut galement correspondre un test de description dune chane de caractres par une expression rgulire : ~ ~* !~ !~* dcrit par lexpression rgulire comme LIKE mais sans tenir compte de la casse non dcrit par lexpression rgulire comme NOT LIKE mais sans tenir compte de la casse
Dans ce cas, la chane de caractres faisant lobjet du test est gauche et correspond une expression simple du type chane de caractres, il sagit gnralement dun nom de colonne. Lexpression rgulire, qui scrit entre apostrophe simple, comme une chane de caractres, est situe droite de loprateur. La section 2.5.8 donne une description dtaille du formalisme des expressions rgulires. Un prdicat simple peut enn correspondre lun des tests suivants : test sur lindtermination de expr comparaison de expr une liste de valeurs test dabsence dune liste de valeurs mme chose, mais la liste de valeurs est le rsultat dune sous-requte qui doit imprativement retourner une table ne contenant quune colonne EXIST (requte) vraie si la sous-requte retourne au moins un n-uplet vraie si au moins un n-uplet de la sous-requte vrie la expr operateur ANY (requte) comparaison expr oprateur n-uplet ; la sous-requte doit imprativement retourner une table ne contenant quune colonne ; IN est quivalent = ANY vraie si tous les n-uplets de la sous-requte vrient la expr operateur ALL (requte) comparaison expr oprateur n-uplet ; la sous-requte doit imprativement retourner une table ne contenant quune colonne expr IS NULL expr IN (expr_1 [, ...]) expr NOT IN (expr_1 [, ...]) expr IN (requte) expr NOT IN (requte) Dans ce tableau, expr dsigne une expression simple et requte une sous-requte. Prdicat compos Les prdicats simples peuvent tre combins au sein dexpression logiques en utilisant les oprateurs logiques AND (et logique), OR (ou logique) et NOT (ngation logique).
2.5.8
Les expressions rgulires
Introduction Le terme expression rgulire est issu de la thorie informatique et fait rfrence un ensemble de rgles permettant de dnir un ensemble de chanes de caractres. Une expression rgulire constitue donc une manire compacte de dnir un ensemble de chanes de caractres. Nous dirons quune chane de caractres est dcrite par une expression rgulire si cette chane est un lment de lensemble de chanes de caractres dni par lexpression rgulire. PostgreSQL dispose de trois oprateurs de description par une expression rgulire : 1. LIKE ou ~~ 2. ~ 3. SIMILAR TO La syntaxe et le pouvoir expressif des expressions rgulires dirent pour ces trois oprateurs. Nous ne dcrirons ici que la syntaxe du formalisme le plus standard et le plus puissant, celui que lon retrouve sous Unix avec les commandes egrep, sed et awk. Ce formalisme est celui associ loprateur ~. Avec PostgreSQL, le test dgalit avec une chane de caractres scrit : expression=chaine De manire quivalente, le test de description par une expression rgulire scrit : expression~expression_rgulire Loprateur de description ~ est sensible la casse, loprateur de description insensible la casse est ~*. Loprateur de non description sensible la casse est !~, son quivalent insensible la casse se note !~*.
46
CHAPITRE 2. SQL
Formalisme Comme nous allons le voir, dans une expression rgulire, certains symboles ont une signication spciale. Dans ce qui suit, expreg, expreg_1, expreg_2 dsignent des expressions rgulires, caractre un caractre quelconque et liste_de_caractres une liste de caractres quelconque. caractre : un caractre est une expression rgulire qui dsigne le caractre lui-mme, except pour les caractres ., ?, +, *, {, |, (, ), ^, $, \, [, ]. Ces derniers sont des mta-caractres et ont une signication spciale. Pour dsigner ces mta-caractres, il faut les faire prcder dun antislash (\., \?, \+, \*, \{, \|, $, $, \^, \$, \\, \[, \]). [liste_de_caractres] : est une expression rgulire qui dcrit lun des caractres de la liste de caractres, par exemple [abcdf] dcrit le caractre a, le b, le c, le d ou le f ; le caractre - permet de dcrire des ensembles de caractres conscutifs, par exemple [a-df] est quivalent [abcdf] ; la plupart des mta-caractres perdent leur signication spciale dans une liste, pour insrer un ] dans une liste, il faut le mettre en tte de liste, pour inclure un ^, il faut le mettre nimporte o sauf en tte de liste, enn un - se place la n de la liste. [liste_de_caractres] : est une expression rgulire qui dcrit les caractres qui ne sont pas dans la liste de caractres. [ :alnum :] : lintrieur dune liste, dcrit un caractre alpha-numrique ([[:alnum:]] est quivalent [0-9A-Za-z]) ; sur le mme principe, on a galement [:alpha:], [:cntrl:], [:digit:], [:graph:], [:lower:], [:print:], [:punct:], [:space:], [:upper:] et [:xdigit:]. . : est une expression rgulire et un mta-caractre qui dsigne nimporte quel caractre. : est une expression rgulire et un mta-caractre qui dsigne le dbut dune chane de caractres. $ : est une expression rgulire et un mta-caractre qui dsigne la n dune chane de caractres. expreg ? : est une expression rgulire qui dcrit zro ou une fois expreg. expreg* : est une expression rgulire qui dcrit expreg un nombre quelconque de fois, zro compris. expreg+ : est une expression rgulire qui dcrit expreg au moins une fois. expreg{n} : est une expression rgulire qui dcrit expreg n fois. expreg{n,} : est une expression rgulire qui dcrit expreg au moins n fois. expreg{n,m} : dcrit expreg au moins n fois et au plus m fois. expreg_1expreg_2 : est une expression rgulire qui dcrit une chane constitue de la concatnation de deux sous-chanes respectivement dcrites par expreg_1 et expreg_2. expreg_1|expreg_2 : est une expression rgulire qui dcrit toute chane dcrite par expreg_1 ou par expreg_2. (expreg) : est une expression rgulire qui dcrit ce que dcrit expreg. \n : o n est un chire, est une expression rgulire qui dcrit la sous-chane dcrite par la ne sousexpression parenthse de lexpression rgulire. Remarque : la concatnation de deux expressions rgulires (expreg_1expreg_2) est une opration prioritaire sur lunion (expreg_1|expreg_2). Exemples Un caractre, qui nest pas un mta-caractre, se dcrit lui-mme. Ce qui signie que si vous cherchez une chane qui contient voiture , vous devez utiliser lexpression rgulire voiture. Si vous ne cherchez que les motifs situs en dbut de ligne, utilisez le symbole ^. Pour chercher toutes les chanes qui commencent par voiture , utilisez ^voiture. Le signe $ (dollar) indique que vous souhaitez trouver les motifs en n de ligne. Ainsi : voiture$ permet de trouver toutes les chanes nissant par voiture . Le symbole . (point) remplace nimporte quel caractre. Pour trouver toutes les occurrences du motif compos des lettres vo, de trois lettres quelconques, et de la lettre e, utilisez : vo...e. Cette commande permet de trouver des chanes comme : voyagent, voyage, voyager, voyageur, vous e.
Vous pouvez aussi dnir un ensemble de lettres en les insrant entre crochets [ ]. Pour chercher toutes les chanes qui contiennent les lettres P ou p suivies de rince, utilisez :[Pp]rince. Si vous voulez spcier un intervalle de caractres, servez-vous dun trait dunion pour dlimiter le dbut et la n de lintervalle. Vous pouvez aussi dnir plusieurs intervalles simultanment. Par exemple [A-Za-z] dsigne toutes les lettres de lalphabet, hormis les caractres accentus, quelque soit la casse. Notez bien quun intervalle ne correspond qu un caractre dans le texte. Le symbole * est utilis pour dnir zro ou plusieurs occurrences du motif prcdent. Par exemple, lexpression rgulire ^Pa(pa)*$ dcrit les chanes : Pa, Papa, Papapa, Papapapapapapa, . . . Si vous souhaitez quun symbole soit interprt littralement, il faut le prxer par un \. Pour trouver toutes les lignes qui contiennent le symbole $, utilisez : \$
48
CHAPITRE 2. SQL
2.6
Travaux Pratiques PostgreSQL : Premires requtes
Dans les exercices de cette section, lobjectif est de trouver les requtes SQL permettant de rpondre aux problmes poss. Nous utilisons ici la base de donnes sur les lms (cf. sance de travaux pratiques 2.4).
2.6.1
Premires requtes
1. Quel est le contenu de la table individu ? 2. Quels sont les prnoms des individus en conservant les doublons ? 3. Quels sont les prnoms des individus en conservant les doublons, mais en les classant par ordre alphabtique ? 4. Quels sont les prnoms des individus sans doublons ? Observez le rsultat en eectuant un classement alphabtique et sans eectuer de classement. 5. Quels sont les individus dont le prnom est John ? 6. Quel est le nom des individus dont le prnom est John ? 7. Dressez la liste de toutes les associations possibles entre un individu et un lm (il ny a pas ncessairement de lien entre lindividu et le lm quon lui associe). Observez le nombre de lignes retounes. tait-il prvisible ? 8. Quels sont les individus qui sont des acteurs ? 9. Dressez la liste de toutes les associations possibles entre un acteur et un lm (il ny a pas ncessairement de lien entre lacteur et le lm quon lui associe). Observez le nombre de lignes retounes. 10. Dressez la liste de toutes les interptations, en prcisant le rle, dacteur, dont on prcisera le nom et le prnom, ayant jou dans des lms dont on prcisera le titre. Le rsultat sera de la forme : prenom | nom | role | titre --------+----------+--------------+-------------Nicole | Kidman | Grace | Dogville Paul | Bettany | Tom Edison | Dogville 11. Mme question que la prcdente, mais en formattant le rsultat de la manire suivante : listing -----------------------------------------------------------------Nicole Kidman a jou le rle de Grace dans le film Dogville Paul Bettany a jou le rle de Tom Edison dans le film Dogville
2.6.2
Requtes dj rsolues en utilisant lalgbre relationnelle (cf. travaux dirigs section 1.7.2)
12. Quels sont les titres des lms dont le genre est Drame ? 13. Quels lms (titres) ont t projets en 2002 ? 14. Donnez le titre des lms raliss par Lars von Trier. 15. Quels lms sont projets au cinma Le Fontenelle ? 16. Quels sont les noms et prnoms des ralisateurs ? 17. Quels sont les noms et prnoms des acteurs ? 18. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : vous ne pouvez utiliser le mot clef INTERSECT puisque nous ne lavons pas encore vu. 19. Quels acteurs a-t-on pu voir au cinma Le Fontenelle depuis lan 2000 ? 20. Quels sont les titres des lms o Nicole Kidman a jou un rle et qui ont t projets au cinma Le Fontenelle ?
2.6. TRAVAUX PRATIQUES SQL : PREMIRES REQUTES {S4-5}
49
2.6.3
Utilisation des expressions rgulires
21. Quels sont les prnoms des individus qui contiennent la lettre s ? 22. Mme question que la prcdente mais sans distinguer les lettres en fonction de la casse. 23. Quels sont les prnoms des individus dont le prnom commence par la lettre s sans tenir compte de la casse ? 24. Quels sont les prnoms des individus dont le prnom se termine par la lettre s sans tenir compte de la casse ? 25. Quels sont les prnoms des individus dont le prnom contient la lettre e sans commencer ou nir par cette lettre et sans tenir compte de la casse ? 26. Quels sont les prnoms des individus qui ne contiennent pas la lettre e ? 27. Quels sont les prnoms des individus qui contiennent les lettres a et l dans un ordre quelconque et sans tenir compte de la casse ? 28. Quels sont les noms des individus qui contiennent la chane an ou la chane on ? Rpondez en utilisant : (a) loprateur | des expressions rgulires ; (b) les listes de caractres des expressions rgulires ; (c) loprateur OR de la clause WHERE. 29. Quels sont les titres des lms qui contiennent au moins trois e ?
50
CHAPITRE 2. SQL
2.7
Interroger une base Langage de manipulation de donnes (LMD) : SELECT (2e partie)
La clause FROM (2e partie) : les jointures
2.7.1
Recommandation Dans la mesure du possible, et contrairement ce que nous avons fait jusqu prsent, il est prfrable dutiliser un oprateur de jointure normalis SQL2 (mot-clef JOIN) pour eectuer une jointure. En eet, les jointures faites dans la clause WHERE (ancienne syntaxe datant de 1986) ne permettent pas de faire la distinction, de prime abord, entre ce qui relve de la slection et ce qui relve de la jointure puisque tout est regroup dans une seule clause (la clause WHERE). La lisibilit des requtes est plus grande en utilisant la syntaxe de loprateur JOIN qui permet disoler les conditions de slections (clause WHERE) de celles de jointures (clauses JOIN), et qui permet galement de cloisonner les conditions de jointures entre chaque couples de table. De plus, loptimisation dexcution de la requte est souvent plus pointue lorsque lon utilise loprateur JOIN. Enn, lorsque lon utilise lancienne syntaxe, la suppression de la clause WHERE des ns de tests pose videmment des problmes.
Le produit cartsien Prenons une opration de jointure entre deux tables R1 et R2 selon une expression logique E. En algbre relationnelle, cette opration se note : R1 E R2 Dans la section 1.6.8, nous avons vu que la jointure nest rien dautre quun produit cartsien suivi dune slection : R1 E R2 = E (R1 R2 ) On peut galement dire que le produit cartsien nest rien dautre quune jointure dans laquelle lexpression logique E est toujours vraie : R1 R2 = R1 true R2 Nous avons vu section 2.5.5 que le produit cartsien entre deux tables table_1 et table_2 peut scrire en SQL : SELECT * FROM table_1, table_2 Il peut galement scrire en utilisant le mot-cl JOIN ddi aux jointures de la manire suivante : SELECT * FROM table_1 CROSS JOIN table_2 En fait, sous PostgreSQL, les quatre critures suivantes sont quivalentes : SELECT SELECT SELECT SELECT * * * * FROM FROM FROM FROM table_1, table_2 table_1 CROSS JOIN table_2 table_1 JOIN table_2 ON TRUE table_1 INNER JOIN table_2 ON TRUE
Les deux dernires critures prendront un sens dans les sections qui suivent.
2.7. INTERROGER UNE BASE LANGAGE DE MANIPULATION DE DONNES : SELECT (2e PARTIE) {S6}51
Syntaxe gnrale des jointures Sans compter loprateur CROSS JOIN, voici les trois syntaxes possibles de lexpression dune jointure dans la clause FROM en SQL : table_1 { [INNER] { LEFT | RIGHT | FULL } [OUTER] } JOIN table_2 ON predicat [...] table_1 { [INNER] { LEFT | RIGHT | FULL } [OUTER] } JOIN table_2 USING (colonnes) [...] table_1 NATURAL { [INNER] { LEFT | RIGHT | FULL } [OUTER] } JOIN table_2 [...] Ces trois syntaxes dirent par la condition de jointure spcie par les clause ON ou USING, ou implicite dans le cas dune jointure naturelle introduite par le mot-cl NATURAL. ON : La clause ON correspond la condition de jointure la plus gnrale. Le prdicat predicat est une expression logique de la mme nature que celle de la clause WHERE dcrite dans la section 2.5.7. USING : La clause USING est une notation abrge correspondant un cas particulier de la clause ON. Les deux tables, sur lesquelles portent la jointure, doivent possder toutes les colonnes qui sont mentionnes, en les sparant par des virgules, dans la liste spcie entre parenthses juste aprs le mot-cl USING. La condition de jointure sera lgalit des colonnes au sein de chacune des paires de colonnes. De plus, les paires de colonnes seront fusionnes en une colonne unique dans la table rsultat de la jointure. Par rapport une jointure classique, la table rsultat comportera autant de colonnes de moins que de colonnes spcies dans la liste de la clause USING. NATURAL : Il sagit dune notation abrge de la clause USING dans laquelle la liste de colonnes est implicite et correspond la liste des colonnes communes aux deux tables participant la jointure. Tout comme dans le cas de la clause USING, les colonnes communes napparaissent quune fois dans la table rsultat. INNER et OUTER : Les mots-cl INNER et OUTER permettent de prciser sil sagit dune jointure interne ou externe. INNER et OUTER sont toujours optionnels. En eet, le comportement par dfaut est celui de la jointure interne (INNER) et les mots clefs LEFT, RIGHT et FULL impliquent forcment une jointure externe (OUTER). INNER JOIN : La table rsultat est constitue de toutes les juxtapositions possibles dune ligne de la table table_1 avec une ligne de la table table_2 qui satisfont la condition de jointure. LEFT OUTER JOIN : Dans un premier temps, une jointure interne (i.e. de type INNER JOIN) est eectue. Ensuite, chacune des lignes de la table table_1 qui ne satisfait pas la condition de jointure avec aucune des lignes de la table table_2 (i.e. les lignes de table_1 qui napparaissent pas dans la table rsultat de la jointure interne) est ajoute la table rsultats. Les attributs correspondant la table table_2, pour cette ligne, sont aects de la valeur NULL. Ainsi, la table rsultat contient au moins autant de lignes que la table table_1. RIGHT OUTER JOIN : Mme scnario que pour lopration de jointure de type LEFT OUTER JOIN, mais en inversant les rles des tables table_1 et table_2. FULL OUTER JOIN : La jointure externe bilatrale est la combinaison des deux oprations prcdentes (LEFT OUTER JOIN et RIGHT OUTER JOIN) an que la table rsultat contienne au moins une occurrence de chacune des lignes des deux tables impliques dans lopration de jointure. La jointure externe droite peut tre obtenue par une jointure externe gauche dans laquelle on inverse lordre des tables (et vice-versa). La jointure externe bilatrale peut tre obtenue par la combinaison de deux jointures externes unilatrales avec loprateur ensembliste UNION que nous verrons dans la section 2.7.3. Des jointures de nimporte quel type peuvent tre chanes les unes derrires les autres. Les jointures peuvent galement tre imbriques tant donn que les tables table_1 et table_2 peuvent trs bien tre elles-mmes le rsultat de jointures de nimporte quel type. Les oprations de jointures peuvent tre parenthses an de prciser lordre dans lequel elles sont eectues. En labsence de parenthses, les jointures seectuent de gauche droite.
52
CHAPITRE 2. SQL
Dnition de deux tables pour les exemples qui suivent An dillustrer les oprations de jointure, considrons les tables realisateur et film dnies de la manire suivante : create table realisateur ( id_real integer primary key, nom varchar(16), prenom varchar(16) ); create table film ( num_film integer primary key, id_real integer, titre varchar(32) ); On notera que dans la table film, lattribut id_real correspond une clef trangre et aurait d tre dni de la manire suivante : id_real integer references realisateur. Nous ne lavons pas fait dans le but dintroduire des lms dont le ralisateur nexiste pas dans la table realisateur an dillustrer les direntes facettes des oprations de jointure. La table realisateur contient les lignes suivantes : id_real | nom | prenom ---------+-----------+--------1 | von Trier | Lars 4 | Tarantino | Quentin 3 | Eastwood | Clint 2 | Parker | Alan La table film contient les lignes suivantes : id_film | id_real | titre ---------+---------+---------------------------1 | 1 | Dogville 2 | 1 | Breaking the waves 3 | 5 | Faux-Semblants 4 | 5 | Crash 5 | 3 | Chasseur blanc, coeur noir Exemples de jointures internes La jointure naturelle entre les tables film et ralisateur peut scrire indiremment de lune des manires suivante : SELECT SELECT SELECT SELECT * * * * FROM FROM FROM FROM film film film film NATURAL JOIN realisateur NATURAL INNER JOIN realisateur; JOIN realisateur USING (id_real); INNER JOIN realisateur USING (id_real);
pour produire le rsultat suivant : id_real | id_film | titre | nom | prenom ---------+---------+----------------------------+-----------+-------1 | 1 | Dogville | von Trier | Lars 1 | 2 | Breaking the waves | von Trier | Lars 3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint Nous aurions galement pu eectuer une qui-jointure en crivant :
SELECT * FROM film, realisateur WHERE film.id_real = realisateur.id_real; SELECT * FROM film JOIN realisateur ON film.id_real = realisateur.id_real; SELECT * FROM film INNER JOIN realisateur ON film.id_real = realisateur.id_real; Mais la colonne id_real aurait t duplique : id_film | id_real | titre | id_real | nom | prenom ---------+---------+----------------------------+---------+-----------+-------1 | 1 | Dogville | 1 | von Trier | Lars 2 | 1 | Breaking the waves | 1 | von Trier | Lars 5 | 3 | Chasseur blanc, coeur noir | 3 | Eastwood | Clint Exemples de jointures externes gauches La jointure externe gauche entre les tables film et ralisateur permet de conserver, dans la table rsultat, une trace des lms dont le ralisateur napparat pas dans la table realisateur. Une telle jointure peut scrire indiremment comme suit : SELECT SELECT SELECT SELECT * * * * FROM FROM FROM FROM film film film film NATURAL LEFT JOIN realisateur; NATURAL LEFT OUTER JOIN realisateur; LEFT JOIN realisateur USING (id_real); LEFT OUTER JOIN realisateur USING (id_real);
Elle produit le rsultat suivant : id_real | id_film | titre | nom | prenom ---------+---------+----------------------------+-----------+-------1 | 1 | Dogville | von Trier | Lars 1 | 2 | Breaking the waves | von Trier | Lars 5 | 3 | Faux-Semblants | | 5 | 4 | Crash | | 3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint Naturellement, en crivant : SELECT * FROM film LEFT JOIN realisateur ON film.id_real = realisateur.id_real; SELECT * FROM film LEFT OUTER JOIN realisateur ON film.id_real = realisateur.id_real; la colonne id_real serait duplique : id_film | id_real | titre | id_real | nom | prenom ---------+---------+----------------------------+---------+-----------+-------1 | 1 | Dogville | 1 | von Trier | Lars 2 | 1 | Breaking the waves | 1 | von Trier | Lars 3 | 5 | Faux-Semblants | | | 4 | 5 | Crash | | | 5 | 3 | Chasseur blanc, coeur noir | 3 | Eastwood | Clint Exemples de jointures externes droites La jointure externe droite entre les tables film et ralisateur permet de conserver, dans la table rsultat, une trace des ralisateurs dont aucun lm napparat dans la table film. Une telle jointure peut scrire indiremment comme suit : SELECT SELECT SELECT SELECT * * * * FROM FROM FROM FROM film film film film NATURAL RIGHT JOIN realisateur; NATURAL RIGHT OUTER JOIN realisateur; RIGHT JOIN realisateur USING (id_real); RIGHT OUTER JOIN realisateur USING (id_real);
54
CHAPITRE 2. SQL
Elle produit le rsultat suivant : id_real | id_film | titre | nom | prenom ---------+---------+----------------------------+-----------+--------1 | 1 | Dogville | von Trier | Lars 1 | 2 | Breaking the waves | von Trier | Lars 2 | | | Parker | Alan 3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint 4 | | | Tarantino | Quentin Exemples de jointures externes bilatrales La jointure externe bilatrale entre les tables film et ralisateur permet de conserver, dans la table rsultat, une trace de tous les ralisateurs et de tous les lms. Une telle jointure peut indiremment scrire : SELECT SELECT SELECT SELECT * * * * FROM FROM FROM FROM film film film film NATURAL FULL JOIN realisateur; NATURAL FULL OUTER JOIN realisateur; FULL JOIN realisateur USING (id_real); FULL OUTER JOIN realisateur USING (id_real);
Elle produit le rsultat suivant : id_real | id_film | titre | nom | prenom ---------+---------+----------------------------+-----------+--------1 | 1 | Dogville | von Trier | Lars 1 | 2 | Breaking the waves | von Trier | Lars 2 | | | Parker | Alan 3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint 4 | | | Tarantino | Quentin 5 | 3 | Faux-Semblants | | 5 | 4 | Crash | |
2.7.2
Les clauses GROUP BY et HAVING et les fonctions dagrgation
Syntaxe La syntaxe dune requte faisant ventuellement intervenir des fonctions dagrgation, une clause GROUP BY et une clause HAVING est la suivante : SELECT expression_1, [...,] expression_N [, fonction_agrgation [, ...] ] FROM nom_table [ [ AS ] alias ] [, ...] [ WHERE prdicat ] [ GROUP BY expression_1, [...,] expression_N ] [ HAVING condition_regroupement ] La clause GROUP BY La commande GROUP BY permet de dnir des regroupements (i.e. des agrgats) qui sont projets dans la table rsultat (un regroupement correspond une ligne) et deectuer des calculs statistiques, dnis par les expressions fonction_agrgation [, ...], pour chacun des regroupements. La liste dexpressions expression_1, [...,] expression_N correspond gnralement une liste de colonnes colonne_1, [...,] colonne_N. La liste de colonnes spcie derrire la commande SELECT doit tre identique la liste de colonnes de regroupement spcie derrire la commande GROUP BY. A la place des noms de colonne il est possible de spcier des oprations mathmatiques de base sur les colonnes (comme dnies dans la section 2.5.4). Dans ce cas, les regroupements doivent porter sur les mmes expressions.
Si les regroupements sont eectus selon une expression unique, les groupes sont dnis par les ensembles de lignes pour lesquelles cette expression prend la mme valeur. Si plusieurs expressions sont spcies (expression_1, expression_2, . . .) les groupes sont dnis de la faon suivante : parmi toutes les lignes pour lesquelles expression_1 prend la mme valeur, on regroupe celles ayant expression_2 identique, etc. Un SELECT avec une clause GROUP BY produit une table rsultat comportant une ligne pour chaque groupe. Les fonctions dagrgation AVG( [ DISTINCT | ALL ] expression ) : Calcule la moyenne des valeurs de lexpression expression. COUNT( * | [DISTINCT | ALL] expression ) : Dnombre le nombre de lignes du rsultat de la requte. Si expression est prsent, on ne compte que les lignes pour lesquelles cette expression nest pas NULL. MAX( [ DISTINCT | ALL ] expression ) : Retourne la plus petite des valeurs de lexpression expression. MIN([ DISTINCT | ALL ] expression ) : Retourne la plus grande des valeurs de lexpression expression. STDDEV([ DISTINCT | ALL ] expression) : Calcule lcart-type des valeurs de lexpression expression. SUM([ DISTINCT | ALL ] expression) : Calcule la somme des valeurs de lexpression expression. VARIANCE([ DISTINCT | ALL ] expression) : Calcule la variance des valeurs de lexpression expression. DISTINCT indique la fonction de groupe de ne prendre en compte que des valeurs distinctes. ALL indique la fonction de groupe de prendre en compte toutes les valeurs, cest la valeur par dfaut. Aucune des fonctions de groupe ne tient compte des valeurs NULL lexception de COUNT(*). Ainsi, SUM(col) est la somme des valeurs non NULL de la colonne col. De mme AVG est la somme des valeurs non NULL divise par le nombre de valeurs non NULL. Il est tout fait possible dutiliser des fonctions dagrgation sans clause GROUP BY. Dans ce cas, la clause SELECT ne doit comporter que des fonctions dagrgation et aucun nom de colonne. Le rsultat dune telle requte ne contient quune ligne. Exemples Reprenons la base de donnes de la sance de travaux pratiques 2.4 dont le schma relationnel tait : film (num_film, num_realisateur, titre, genre, annee) cinema (num_cinema, nom, adresse) individu (num_individu, nom prenom) jouer (num_acteur, num_film, role) projection (num_cinema, num_film, jour) Pour connatre le nombre de fois que chacun des lms a t projet on utilise la requte : SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection GROUP BY num_film, titre; Si lon veut galement connatre la date de la premire et de la dernire projection, on utilise : SELECT num_film, titre, COUNT(*), MIN(jour), MAX(jour) FROM film NATURAL JOIN projection GROUP BY num_film, titre; Pour connatre le nombre total de lms projets au cinma Le Fontenelle, ainsi que la date de la premire et de la dernire projection dans ce cinma, la requte ne contient pas de clause GROUP BY mais elle contient des fonctions dagrgation : SELECT COUNT(*), MIN(jour), MAX(jour) FROM film NATURAL JOIN projection NATURAL JOIN cinema WHERE cinema.nom = Le Fontenelle;
56
CHAPITRE 2. SQL
La clause HAVING De la mme faon quil est possible de slectionner certaines lignes au moyen de la clause WHERE, il est possible, dans un SELECT comportant une fonction de groupe, de slectionner certains groupes par la clause HAVING. Celle-ci se place aprs la clause GROUP BY. Le prdicat dans la clause HAVING suit les mmes rgles de syntaxe quun prdicat gurant dans une clause WHERE. Cependant, il ne peut porter que sur des caractristiques du groupe : fonction dagrgation ou expression gurant dans la clause GROUP BY. Une requte de groupe (i.e. comportant une clause GROUP BY) peut contenir la fois une clause WHERE et une clause HAVING. La clause WHERE sera dabord applique pour slectionner les lignes, puis les groupes seront constitus partir des lignes slectionnes, les fonctions de groupe seront ensuite values et la clause HAVING sera enn applique pour slectionner les groupes. Exemples Pour connatre le nombre de fois que chacun des lms a t projet en ne sintressant quaux lms projets plus de 2 fois, on utilise la requte : SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection GROUP BY num_film, titre HAVING COUNT(*)>2; Si en plus, on ne sintresse quaux lms projets au cinma Le Fontenelle, il faut ajouter une clause WHERE : SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection NATURAL JOIN cinema WHERE cinema.nom = Le Fontenelle GROUP BY num_film, titre HAVING COUNT(*)>2;
2.7.3
Oprateurs ensemblistes : UNION, INTERSECT et EXCEPT
Les rsultats de deux requtes peuvent tre combins en utilisant les oprateurs ensemblistes dunion (UNION), dintersection (INTERSECT) et de dirence (EXCEPT). La syntaxe dune telle requte est la suivante : requte_1 { UNION | INTERSECT | EXCEPT } [ALL] requte_2 [...] Pour que lopration ensembliste soit possible, il faut que requte_1 et requte_2 aient le mme schma, cest dire le mme nombre de colonnes respectivement du mme type. Les noms de colonnes (titres) sont ceux de la premire requte (requte_1). Il est tout fait possible de chaner plusieurs oprations ensemblistes. Dans ce cas, lexpresion est value de gauche droite, mais on peut modier lordre dvaluation en utilisant des parenthses. Dans une requte on ne peut trouver quune seule instruction ORDER BY. Si elle est prsente, elle doit tre place dans la dernire requte (cf. section 2.5.6). La clause ORDER BY ne peut faire rfrence quaux numros des colonnes (la premire portant le numero 1), et non pas leurs noms, car les noms peuvent tre dirents dans chacune des requtes sur lesquelles porte le ou les oprateurs ensemblistes. Les oprateurs UNION et INTERSECT sont commutatifs. Contrairement la commande SELECT, le comportement par dfaut des oprateurs ensemblistes limine les doublons. Pour les conserver, il faut utiliser le mot-clef ALL. Attention, il sagit bien doprateurs portant sur des tables gnres par des requtes. On ne peut pas faire directement lunion de deux tables de la base de donnes.
2.7.4
Traduction des oprateurs dunion, dintersection, de dirence et de division de lalgbre relationnelle (2e partie)
Traduction de loprateur dunion Loprateur dunion relation1 relation2 se traduit tout simplement en SQL par la requte :
SELECT * FROM relation_1 UNION SELECT * FROM relation_2 Traduction de loprateur dintersection Loprateur dintersection R1 R2 se traduit tout simplement en SQL par la requte : SELECT * FROM relation_1 INTERSECT SELECT * FROM relation_2 Traduction de loprateur de dirence Loprateur de dirence R1 R2 se traduit tout simplement en SQL par la requte : SELECT * FROM relation_1 EXCEPT SELECT * FROM relation_2 Traduction de loprateur de division Il nexiste pas de commande SQL permettant de raliser directement une division. Prenons la requte : Quels sont les acteurs qui ont jou dans tous les lms de Lars von Trier ? Cela peut se reformuler par : Quels sont les acteurs qui vrient : quel que soit un lm de Lars von Trier, lacteur a jou dans ce lm. Malheureusement, le quanticateur universel () nexiste pas en SQL. Par contre, le quanticateur existentiel () existe : EXISTS. Or, la logique des prdicats nous donne lquivalence suivante : xP(x) = xP(x) On peut donc reformuler le problme de la manire suivante : Quels sont les acteurs qui vrient : il est faux quil existe un lm de Lars von Trier dans lequel lacteur na pas jou. Ce qui correspond la requte SQL : SELECT DISTINCT nom, prenom FROM individu AS acteur_tous_lars WHERE NOT EXISTS ( SELECT * FROM ( film JOIN individu ON num_realisateur = num_individu AND nom = von Trier AND prenom = Lars ) AS film_lars WHERE NOT EXISTS ( SELECT * FROM individu JOIN jouer ON num_individu = num_acteur AND num_individu = acteur_tous_lars.num_individu AND num_film = film_lars.num_film ) ); En prenant le problme dun autre point de vue, on peut le reformuler de la manire suivante : Quels sont les acteurs qui vrient : le nombre de lms raliss par Lars von Trier dans lequel lacteur jou est gal au nombre de lms raliss par Lars von Trier. Ce qui peut se traduire en SQL indiremment par lune des deux requtes suivantes : SELECT acteur.nom, acteur.prenom FROM individu AS acteur JOIN jouer ON acteur.num_individu = jouer.num_acteur JOIN film ON jouer.num_film = film.num_film JOIN individu AS realisateur ON film.num_realisateur = realisateur.num_individu WHERE realisateur.nom = von Trier AND realisateur.prenom = Lars GROUP BY acteur.nom, acteur.prenom
58
CHAPITRE 2. SQL
HAVING COUNT (DISTINCT film.num_film) = ( SELECT DISTINCT COUNT(*) FROM film JOIN individu ON num_realisateur = num_individu WHERE nom = von Trier AND prenom = Lars ); SELECT DISTINCT acteur_tous_lars.nom, acteur_tous_lars.prenom FROM individu AS acteur_tous_lars WHERE ( SELECT DISTINCT COUNT(*) FROM jouer JOIN film ON jouer.num_film = film.num_film JOIN individu ON num_realisateur = num_individu WHERE nom = von Trier AND prenom = Lars AND jouer.num_acteur = acteur_tous_lars.num_individu ) = ( SELECT DISTINCT COUNT(*) FROM film JOIN individu ON num_realisateur = num_individu WHERE nom = von Trier AND prenom = Lars );
2.8. TRAVAUX PRATIQUES SQL : REQUTES AVANCES {S6-7}
59
2.8
Travaux Pratiques PostgreSQL : Requtes avances
Dans les exercices de cette section, lobjectif est de trouver les requtes SQL permettant de rpondre aux problmes poss. Nous utilisons la base de donnes sur le cinma (cf. sance de travaux pratiques 2.4). Contrairement la sance de travaux pratiques 2.6, nous utilisons maintenant la commande JOIN pour toutes les jointures des requtes.
2.8.1
Prix de GROUP
1. Dressez la liste de toutes les interptations, en prcisant le rle, dacteur, dont on prcisera le nom et le prnom, ayant jou dans des lms dont on prcisera le titre. 2. On dsire connatre le nom et le prnom des acteurs et le nombre de lms dans lesquels ils ont jou. 3. On dsire connatre le nom et le prnom des acteurs, le nombre de lms dans lequel ils ont jou ainsi que lanne du lm de leur premier et de leur dernier rle. 4. On dsire connatre le nom et le prnom des acteurs et le nombre de lms dans lesquels ils ont jou pour les acteurs ayant jou dans strictement plus dun lm. 5. On dsire connatre le nom et le prnom des acteurs et le nombre de drames dans lesquels ils ont jou.
2.8.2
6. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : vous devez utiliser le mot clef INTERSECT puisque nous lavons maintenant vu. 7. Quels sont les ralisateurs qui ont ralis des lms dpouvante et des lms dramatiques ? 8. Quels sont les acteurs qui nont pas jou dans des lms dramatiques ? 9. Quels sont les cinmas qui ont projet tous les lms ? 10. Quels sont les acteurs que lon a pu voir dans toutes les cinmas ?
2.8.3 GROUP toujours !

11. Quel est le nombre de lms raliss par chacun des ralisateurs ? 12. Combien de lms ralis le ralisateur qui en a le plus raliss ? 13. Quel sont les ralisateurs (il peut y en avoir un ou plusieurs execo) ayant ralis le plus de lms ? Comment serait-il possible de simplier cette requte ? 14. Quel est le nombre de lms raliss par les ralisateurs, dont on dsire connatre le nom et le prnom, ayant ralis au moins un lm du mme genre que lun des lms raliss par David Cronenberg ? 15. On suppose que les ttes dache dun lm sont les acteurs recenss pour ce lm dans la base de donnes. Quel est le nombre de ttes dache et le ralisateur de chacun des lms ?
16. En supposant quun lm cote 1000000 plus 200000 par tte dache, donnez le prix moyen des lms raliss par chacun des ralisateurs.
60
CHAPITRE 2. SQL
2.9
2.9.1
Nouveaux objets Langage de dnition de donnes (LDD)

Squences (CREATE SEQUENCE) et type SERIAL
Cration dune squence Une squence est en fait une table spciale contenant une seule ligne. Cet objet est utilis pour crer une suite de nombres entiers dont lvolution, gnralement croissante, est rgie par un certain nombre de paramtres. Voici la syntaxe de cration dune squence : CREATE SEQUENCE nom [ INCREMENT [ BY ] incrment ] [ MINVALUE valeurmin ] [ MAXVALUE valeurmax ] [ START [ WITH ] dbut ] [ [ NO ] CYCLE ] La commande CREATE SEQUENCE cre un nouveau gnrateur de nombre. Ceci implique la cration et linitialisation dune nouvelle table portant le nom nom. INCREMENT BY : La clause optionnelle INCREMENT BY incrment spcie la valeur ajoute la valeur de la squence courante pour crer une nouvelle valeur. Une valeur positive crera une squence ascendante, une ngative en crera une descendante. La valeur par dfaut est 1. MINVALUE : La clause optionnelle MINVALUE valeurmin prcise la valeur minimale quune squence peut gnrer. Si cette clause nest pas fournie, alors les valeurs par dfaut seront utilises. Les valeurs par dfaut sont 1 et 263 1 pour les squences respectivement ascendantes et descendantes. MAXVALUE : La clause optionnelle MAXVALUE valeurmax prcise la valeur maximale pour la squence. Si cette clause nest pas fournie, alors les valeurs par dfaut seront utilises. Les valeurs par dfaut sont 263 1 et 1 pour les squences respectivement ascendantes et descendantes. START WITH : La clause optionnelle START WITH dbut prcise la valeur dinitialisation de la squence. La valeur de dbut par dfaut est valeurmin pour les squences ascendantes et valeurmax pour les squences descendantes. [ NO ] CYCLE : Loption CYCLE autorise la squence recommencer au dbut lorsque valeurmax ou valeurmin a t atteinte par une squence respectivement ascendante ou descendante. Si la limite est atteinte, le prochain nombre gnr sera respectivement valeurmin ou valeurmax. Si NO CYCLE est spci, tout appel nextval aprs que la squence a atteint la valeur minimale renverra une erreur. NO CYCLE est le comportement par dfaut. Utilisation dune squence Bien que vous ne pouvez pas mettre jour directement une squence, vous pouvez toujours utiliser une requte comme : SELECT * FROM nom_sequence; Aprs la cration dune squence, il faut utiliser les fonctions nextval(), currval() et setval() pour la manipuler. nextval(nom_sequence) : incrmente la valeur courante de la squence nom_sequence (except la premire fois) et retourne cette valeur. currval(nom_sequence) : retourne la valeur courante de la squence nom_sequence ; cette fonction ne peut tre appele que si nextval() la t au moins une fois. setval(nom_sequence, nombre) : Initialise la valeur courante de la squence nom_sequence nombre.
2.9. NOUVEAUX OBJETS LANGAGE DE DFINITION DE DONNES (LDD) {S8}
61
Vous pouvez appeler ces direntes fonctions de la manire suivante : SELECT nextval(nom_sequence); SELECT currval(nom_sequence); SELECT setval(nom_sequence, nombre); Utilisez DROP SEQUENCE pour supprimer une squence. Type SERIAL Le type de donne SERIAL nest pas un vrai type, mais plutt un raccourci de notation pour dcrire une colonne didentiants uniques. Ainsi, la commande CREATE TABLE nom_de_table ( nom_de_colonne SERIAL ); est quivalente la commande : CREATE SEQUENCE nom_de_sequence; CREATE TABLE nom_de_table ( nom_de_colonne integer DEFAULT nextval(nom_de_sequence) NOT NULL ); Ainsi, nous avons cr une colonne dentiers et fait en sorte que ses valeurs par dfaut soient assignes par un gnrateur de squence. Une contrainte NOT NULL est ajoute pour sassurer quune valeur nulle ne puisse pas tre explicitement insre. Dans la plupart des cas, on ajoute galement une contrainte UNIQUE ou PRIMARY KEY pour interdire que des doublons soient crs par accident. Pour insrer la valeur suivante de la squence dans la colonne de type SERIAL, il faut faire en sorte dutiliser la valeur par dfaut de la colonne. Cela peut se faire de deux faons : soit en excluant cette colonne de la liste des colonnes de la commande INSERT, ou en utilisant le mot cl DEFAULT.
2.9.2
Rgles (CREATE RULE)
Description Le systme de rgles autorise la dnition dactions alternatives raliser sur les insertions, mises jour ou suppressions dans les tables de la base de donnes. Concrtement, une rgle permet dexcuter des commandes supplmentaires lorsquune commande donne est excute sur une table donne. Autrement dit, une rgle peut remplacer une commande donne par une autre ou faire quune commande ne soit pas excute. Les rgles sont aussi utilises pour implmenter les vues de tables (cf. section 2.9.3). Syntaxe de dnition Voici la syntaxe de cration dune rgle : CREATE [ OR REPLACE ] RULE nom AS ON vnement TO table [ WHERE condition ] DO [ INSTEAD ] { NOTHING | commande | ( commande ; commande ... ) } CREATE RULE : dnit une nouvelle rgle sappliquant une table ou une vue. CREATE OR REPLACE RULE : dnit une nouvelle rgle, ou, le cas chant, remplace une rgle existante du mme nom pour la mme table. nom : dsigne le nom dune rgle crer. Elle doit tre distincte du nom de toute autre rgle sur la mme table. Lorsque plusieurs rgles portent sur la mme table et le mme type dvnement, elles sont appliques dans lordre alphabtique de leur nom.
62
CHAPITRE 2. SQL
vnement : SELECT, INSERT, UPDATE ou DELETE. Les rgles qui sont dnies sur INSERT, UPDATE ou DELETE sont appeles des rgles de mise jour. Les rgles dnies sur SELECT permettent la cration de vues (cf. section 2.9.3). table : Le nom (pouvant tre quali par le nom du schma) de la table ou de la vue o sapplique la rgle. condition : Toute expression SQL conditionnelle (i.e. de type boolean). Lexpression de condition ne peut pas rfrer une table autre que NEW et OLD et ne peut pas contenir de fonction dagrgat. commande : Zone de spcication des commandes ralisant laction de la rgle. Les commandes valides sont SELECT, INSERT, UPDATE, DELETE ou NOTIFY. Le mot-cl NOTHING permet de spcier que lon ne veut rien faire. INSTEAD : Si ce mot-cl est utilis, la ou les commandes sont xcutes la place de la requte dclenchante. En labsence de INSTEAD, la ou les commandes sont xcutes aprs la requte dclanchante dans le cas ON INSERT (pour permettre aux commandes de voir les lignes insres) et avant dans le cas ON UPDATE ou ON DELETE (pour permettre aux commandes de voir les lignes mettre jour ou supprimer). lintrieur dune condition et dune commande, deux tables spciales, NEW et OLD, peuvent tre utilises pour se rfrer la table sur laquelle porte la rgle. NEW est valide dans les rgles ON INSERT et ON UPDATE pour dsigner la nouvelle ligne en cours dinsertion ou de mise jour. OLD est valide dans les rgles ON UPDATE et ON DELETE pour dsigner la ligne existante en cours de modication ou de suppression. Syntaxe de suppression DROP RULE nom ON relation [ CASCADE | RESTRICT ] DROP RULE : Supprime une rgle de rcriture. nom : Le nom de la rgle supprimer. relation : Le nom (quali ou non du nom du schma) de la table ou vue o sapplique la rgle. CASCADE : Supprime automatiquement les objets dpendant de la rgle. RESTRICT : Refuse de supprimer la rgle si un objet en dpend. Ceci est la valeur par dfaut.
2.9.3
Vues (CREATE VIEW)
Description Les vues sont des tables virtuelles qui contiennent le rsultat dune requte SELECT. Lun des intrts de lutilisation des vues vient du fait que la vue ne stocke pas les donnes, mais fait rfrence une ou plusieurs tables dorigine travers une requte SELECT, requte qui est excute chaque fois que la vue est rfrence. De ce fait, toute modication de donnes dans les tables dorigine est immdiatement visible dans la vue ds que celle-ci est nouveau rfrence dans une requte. Les utilisations possibles dune vue sont multiples : Cacher aux utilisateurs certaines colonnes ou certaines lignes en mettant leur disposition des vues de projection ou de slection. Ceci permet de fournir un niveau de condentialit et de scurit supplmentaire. Simplier lutilisation de tables comportant de nombreuses colonnes, de nombreuses lignes ou des noms complexes, en crant des vues avec des structures plus simples et des noms plus intelligibles. Nommer des requtes frquemment utilises pour simplier et acclrer lcriture de requte y faisant rfrence.
2.9. NOUVEAUX OBJETS LANGAGE DE DFINITION DE DONNES (LDD) {S8}
63
Syntaxe de dnition Voici la syntaxe de cration dune vue : CREATE [ OR REPLACE ] VIEW nom [ ( nom_colonne [, ...] ) ] AS requte CREATE VIEW : dnit une nouvelle vue. CREATE OR REPLACE VIEW : dnit une nouvelle vue, ou la remplace si une vue du mme nom existe dj. Vous pouvez seulement remplacer une vue avec une nouvelle requte qui gnre un ensemble de colonnes identiques. nom : Le nom de la vue crer (quali ou non du nom du schma). Si un nom de schma (cf. section 2.9.4) est donn (par exemple CREATE VIEW monschema.mavue ...), alors la vue est cre dans le schma donn. Dans les autres cas, elle est cre dans le schma courant. Le nom de la vue doit tre dirent du nom des autres vues, tables, squences ou index du mme schma. nom_colonne : Une liste optionnelle de noms utiliser pour les colonnes de la vue. Si elle nest pas donne, le nom des colonnes sera dduit de la requte. requte : Une requte (cest--dire une instruction SELECT) qui dnit les colonnes et les lignes de la vue. La norme SQL propose un ensemble important de restrictions pour la modication ou linsertion ou la modication des donnes dans les vues. Les systmes de gestion de base de donnes ont aussi chacun leur implantation de ce concept et chacun leurs contraintes et restrictions. En particulier, peu doprations sont autorises ds quune vue porte sur plusieurs tables ; aucune nest possible si la vue comporte des oprateurs dagrgation. Avec PostgreSQL les vues ne sont que consultables par des instructions SELECT (i.e. lecture seule). Aucune autre opration nest possible (insertion, mise jour ou suppression de lignes). Par contre, la notion de rgles permet, avec PostgreSQL, dimplmenter ces fonctionnalits. Cette notion savre plus souple et puissante que les restrictions communment appliques aux SGBD classiques. Implmentation interne Avec PostgreSQL, les vues sont implmentes en utilisant le systme de rgles. En fait, il ny aucune dirence entre CREATE VIEW mavue AS SELECT * FROM matable; et ces deux commandes CREATE TABLE mavue (liste de colonnes identique celle de matable); CREATE RULE "_RETURN" AS ON SELECT TO mavue DO INSTEAD SELECT * FROM matable; parce que cest exactement ce que fait la commande CREATE VIEW en interne. Ainsi, pour lanalyseur, il ny a aucune dirence entre une table et une vue : il sagit de relations. Syntaxe de suppression DROP VIEW nom [, ...] [ CASCADE | RESTRICT ] DROP VIEW : Supprime une vue existante. nom : Le nom de la vue supprimer (quali ou non du nom du schma). CASCADE : Supprime automatiquement les objets qui dpendent de la vue (comme par exemple dautres vues). RESTRICT : Refuse de supprimer la vue si un objet en dpend. Ceci est la valeur par dfaut.
64
CHAPITRE 2. SQL
2.9.4
Schmas (CREATE SCHEMA)
Description Les schmas sont des espaces dans lesquels sont rfrencs des lments (tables, vues, index...). La notion de schma est trs lie la notion dutilisateur ou de groupe dutilisateurs. Syntaxe de dnition CREATE SCHEMA nom_schma CREATE SCHEMA cre un nouveau schma dans la base de donnes en cours. Le nom du schma doit tre distinct du nom des dirents schmas existants dans la base de donnes en cours. Le paramtre nom_schma est le nom du schma crer. Accs aux tables Lorquune table nom_table est dans un schma nom_schema, pour la dsigner, il faut faire prcder son nom par le nom du schma qui la contient de la manire suivante : nom_schema.nom_table. Cest ce que lon appel un nom quali. Le Chemin de Recherche de Schma Les noms qualis sont pnibles crire et il est, de toute faon, prfrable de ne pas coder un nom de schma dans une application. Donc, les tables sont souvent appeles par des noms non qualis (i.e. nom de la table lui mme). Le systme dtermine quelle table est appele en suivant un chemin de recherche qui est une liste de schmas regarder. La premire table correspondante est considre comme la table voulue. Sil ny a pas de correspondance, une erreur est leve, mme si des noms de table correspondants existent dans dautres schmas dans la base. Le premier schma dans le chemin de recherche est appel le schma courant. En plus dtre le premier schma parcouru, il est aussi le schma dans lequel de nouvelles tables seront cres si la commande CREATE TABLE ne prcise pas de nom de schma. Pour voir le chemin de recherche courant, utilisez la commande suivante : SHOW search_path; Pour ajouter un nouveau schma mon_schema dans le chemin tout en conservant dans ce chemin le schma par dfaut (public), nous utilisons la commande : SET search_path TO mon_schema,public; Syntaxe de suppression La commande DROP SCHEMA permet de supprimer des schmas de la base de donnes. La syntaxe de la commande est la suivante : DROP SCHEMA nom [, ...] [ CASCADE | RESTRICT ] Un schma peut seulement tre supprim par son propritaire ou par un super utilisateur. nom : Le nom du schma CASCADE : Supprime automatiquement les objets (tables, fonctions, etc.) contenus dans le schma. RESTRICT : Refuse de supprimer le schma sil contient un objet. Ceci est la valeur par dfaut.
2.10. TRAVAUX PRATIQUES SQL : NOUVEAUX OBJETS {S8}
65
2.10
2.10.1
Travaux Pratiques PostgreSQL : Manipulation des nouveaux objets

Squences
1. Crez une squence test_sequence cyclique commenant 10 de pas dincrment 2 et de valeur maximum 20. 2. Testez cette squence (avec la fonction nextval) et observez son comportement. Le cycle recommencet-il 10 ? Pourquoi ? 3. Testez galement les fonctions currval et setval. Eacez la squence de la table. 4. Modiez votre chier GenBDCine.sql an que la colonne num_individu de la table individu soit du type serial. Rechargez votre base. 5. Tentez dinsrer un nouvel individu sans prciser son num_individu. Quel est le problme ? Comment pouvez-vous y remdier ?
2.10.2
Schma et vues
6. Crez un schma vue. 7. Dans ce schma, crez deux vues, lune correspondant la liste des acteurs, lautre la liste des ralisateurs. Les schmas respectifs de ces relations seront : acteur(num_acteur, nom, prenom) ; realisateur(num_realisateur, nom, prenom).
2.10.3
Rgles
8. Crez une rgle insertion_acteur qui insre un individu dans la table individu la place de linsrer dans la table vue.acteur quand on tente de linsrer dans la table vue.acteur. 9. Quel est le problme de cette rgle ? 10. Crez une nouvelle ligne dans la table film. Il sagit dun lm ctif : num_film 0 num_realisateur titre 0 NULL genre NULL annee NULL
Quel problme rencontrez-vous ? Trouvez une solution. 11. Ainsi, quand un nouvel acteur est insr, il est possible de mettre jour la table jouer en faisant rfrence ce lm ctif. Corrigez votre rgle insertion_acteur pour mettre en uvre cette nouvelle logique. Vriez quun nouvel acteur insr dans la vue vue.acteur apparraisse bien dans cette vue une fois lopration eectue.
2.10.4
Toujours des requtes
Dans les exercices qui suivent, pour rpondre, utilisez les vues vue.acteur et vue.realisateur quand cela permet de simplier lcriture des requtes. 12. Quels sont les individus qui ne sont ni des acteurs, ni des ralisateurs. 13. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : cette requte a dj t rsolue en utilisant lalgbre relationnelle (cf. travaux dirigs section 1.7.2) et le langage SQL (cf. travaux pratiques 2.6 et 2.8) : 14. Quels sont les noms et prnoms des individus dont le prnom est la fois celui dun acteur et celui dun ralisateur sans quil sagisse de la mme personne ? Remarque : cette requte a dj t rsolue en utilisant lalgbre relationnelle (cf. travaux dirigs section 1.7.2).
66
CHAPITRE 2. SQL
2.11
2.11.1
SQL intgr
Introduction
Ce chapitre dcrit le pacquage SQL embarqu pour PostgreSQL ECPG. Il est compatible avec les langages C et C++ et a t dvelopp par Linus Tolke et Michael Meskes. Un programme SQL embarqu est en fait un programme ordinaire, dans notre cas un programme en langage C, dans lequel nous insrons des commandes SQL incluses dans des sections spcialement marques. Ainsi les instructions Embedded SQL commencent par les mots EXEC SQL et se terminent par un point-virgule ( ; ). Pour gnrer lexcutable, le code source est dabord traduit par le prprocesseur SQL qui convertit les sections SQL en code source C ou C++, aprs quoi il peut tre compil de manire classique. Le SQL embarqu prsente des avantages par rapport dautres mthodes pour prendre en compte des commandes SQL dans du code C. Par exemple, le passage des informations de et vers les variables du programme C est entirement pris en charge. Ensuite, le code SQL du programme est vri syntaxiquement au moment de la prcompilation. Enn, le SQL embarqu en C est spci dans le standard SQL et support par de nombreux systmes de bases de donnes SQL. Limplmentation PostgreSQL est conue pour correspondre ce standard autant que possible, an de rendre le code facilement portable vers des SGBD autre que PostgreSQL. Comme alternative au SQL intgr, on peut citer lutilisation dune API (Application Programming Interface) permettant au programme de communiquer directement avec le SGBD via des fonctions fournies par lAPI. Dans ce cas de gure, il ny a pas de prcompilation eectuer. Se rfrer la documentation PostgreSQL (The PostgreSQL Global Development Group, 2005) pour plus dinformation ce sujet : Chapitre 27. libpq Bibliothque C.
2.11.2
Connexion au serveur de bases de donnes
Introduction Quelque soit le langage utilis (C, Java, PHP, etc.), pour pouvoir eectuer un traitement sur une base de donnes, il faut respecter les tapes suivantes : 1. tablir une connexion avec la base de donnes ; 2. rcuperer les informations relatives la connexion ; 3. eectuer les traitements dsirs (requtes ou autres commandes SQL) ; 4. fermer la connexion avec la base de donnes. Nous allons voir dans cette section comment ouvrir et fermer une connexion, et nous verrons dans les sections suivantes comment eectuer des traitements. Ouverture de connexion La connexion une base de donnes se fait en utilisant linstruction suivante : EXEC SQL CONNECT TO cible [AS nom_connexion] [USER utilisateur]; La cible cible peut tre spcie de lune des faons suivantes : nom_base[@nom_hte ][:port] ; tcp:postgresql://nom_hte [:port ] [/nom_base][? options] ; unix:postgresql://nom_hte[: port][/nom_base ][? options] ; une chane SQL littrale contenant une des formes ci-dessus ; une rfrence une variable contenant une des formes ci-dessus ; DEFAULT. En pratique, utiliser une chane littrale (entre guillemets simples) ou une variable de rfrence gnre moins derreurs. La cible de connexion DEFAULT initie une connexion sur la base de donnes par dfaut
2.11. SQL INTGR {S9}
67
avec lutilisateur par dfaut. Aucun nom dutilisateur ou nom de connexion ne pourrait tre spci isolment dans ce cas. Il existe galement direntes faons de prciser lutilisateur utilisateur : nom_utilisateur nom_utilisateur/ mot_de_passe nom_utilisateur IDENTIFIED BY mot_de_passe nom_utilisateur USING mot_de_passe nom_utilisateur et mot_de_passe peuvent tre un identicateur SQL, une chane SQL littrale ou une rfrence une variable de type caractre. nom_connexion est utilis pour grer plusieurs connexions dans un mme programme. Il peut tre omis si un programme nutilise quune seule connexion. La dernire connexion ouverte devient la connexion courante, utilise par dfaut lorsquune instruction SQL est excuter. Fermeture de connexion Pour fermer une connexion, utilisez linstruction suivante : EXEC SQL DISCONNECT [connexion]; Le paramtre connexion peut prendre lune des valeurs suivantes : nom_connexion DEFAULT CURRENT ALL Si aucun nom de connexion nest spci, cest la connexion courante qui est ferme. Il est prfrable de toujours fermer explicitement chaque connexion ouverte.
2.11.3
Excuter des commandes SQL
Toute commande SQL, incluse dans des sections spcialement marques, peut tre excute lintrieur dune application SQL embarqu. Ces sections se prsentent toujours de la manire suivante : EXEC SQL instructions_SQL ; Dans le mode par dfaut, les instructions ne sont valides que lorsque EXEC SQL COMMIT est excut. Linterface SQL embarqu supporte aussi la validation automatique des transactions via linstruction EXEC SQL SET AUTOCOMMIT TO ON. Dans ce cas, chaque commande est automatiquement valide. Ce mode peut tre explicitement dsactiv en utilisant EXEC SQL SET AUTOCOMMIT TO OFF. Voici un exemple permettant de crer une table : EXEC SQL create table individu ( num_individu integer primary key, nom varchar(64), prenom varchar(64) ); EXEC SQL COMMIT;
2.11.4
Les variables htes
Introduction aux variables htes La transmission de donnes entre le programme C et le serveur de base de donnes est particulirement simple en SQL embarqu. En eet, il est possible dutiliser une variable C, dans une instruction SQL, simplement en la prxant par le caractre deux-points ( : ). Par exemple, pour insrer une ligne dans la table individu on peut crire : EXEC SQL INSERT INTO individu VALUES (:var_num, Poustopol, :var_prenom); Cette instruction fait rfrence deux variables C nommes var_num et var_prenom et utilise aussi une chane littrale SQL (Poustopol) pour illustrer que vous ntes pas restreint utiliser un type de donnes plutt quun autre. Dans lenvironemment SQL, nous appelons les rfrences des variables C des variables htes.
68
CHAPITRE 2. SQL
Dclaration des variables htes Les variables htes sont des variables de langage C identies auprs du prprocesseur SQL. Ainsi, pour tre dnies, les variables htes doivent tre places dans une section de dclaration, comme suit : EXEC SQL BEGIN DECLARE SECTION; declarations_des_variables_C EXEC SQL END DECLARE SECTION; Vous pouvez avoir autant de sections de dclaration dans un programme que vous le souhaitez. Les variables htes peuvent remplacer les constantes dans nimporte quelle instruction SQL. Lorsque le serveur de base de donnes excute la commande, il utilise la valeur de la variable hte. Notez toutefois quune variable hte ne peut pas remplacer un nom de table ou de colonne. Comme nous lavons dj dit, dans une instruction SQL, le nom de la variable est prcd du signe deux-points ( : ) pour le distinguer dautres identicateurs admis dans linstruction. Les initialisations sur les variables sont admises dans une section de dclaration. Les sections de dclarations sont traites comme des variables C normales dans le chier de sortie du prcompilatuer. Il ne faut donc pas les rednir en dehors des sections de dclaration. Les variables qui nont pas pour but dtre utilises dans des commandes SQL peuvent tre normalement dclares en dehors des sections de dclaration. Les variables en langage C ont leur porte normale au sein du bloc dans lequel elles sont dnies. Toutefois, le prprocesseur SQL nanalyse pas le code en langage C. Par consquent, il ne respecte pas les blocs C. Aussi, pour le prprocesseur SQL, les variables htes sont globales : il nest pas possible que deux de ces variables portent le mme nom. Types des variables htes Seul un nombre limit de types de donnes du langage C est support pour les variables htes. En outre, certains types de variable hte nont pas de type correspondant en langage C. Dans ce cas, des macros prdnies peuvent tre utilises pour dclarer les variables htes. Par exemple, le type prdini VARCHAR est la structure adquate pour interfacer des donnes SQL de type varchar. Une dclaration comme VARCHAR var[180]; est en fait convertie par le prprocesseur en une structure : struct varchar_var { int len; char arr[180]; } var; Utilisation dune variable hte : clause INTO Dans le cas dune requte de ligne unique, cest dire qui nextrait pas plus dune ligne de la base de donnes, les valeurs renvoyes peuvent tre stockes directement dans des variables htes. Cependant, contrairement au langage C ou C++, le SQL est un langage ensembliste : une requte peut trs bien retourner plus dune ligne. Dans ce cas, il faut faire appel la notion de curseur que nous abordons dans la section 2.11.7. Dans le cas dune requte de ligne unique, une nouvelle clause INTO est intercalle entre la clause SELECT et la clause FROM. La clause INTO contient une liste de variables htes destine recevoir la valeur de chacune des colonnes mentionnes dans la clause SELECT. Le nombre de variables htes doit tre identique au nombre de colonnes de la clause SELECT. Les variables htes peuvent tre accompagnes de variables indicateur an de prendre en compte les rsultats NULL (cf. section 2.11.5). Lors de lexcution de linstruction SELECT, le serveur de base de donnes rcupre les rsultats et les place dans les variables htes. Si le rsultat de la requte contient plusieurs lignes, le serveur renvoie une erreur. Si la requte naboutit pas la slection dune ligne, un avertissement est renvoy. Les erreurs et les avertissements sont renvoys dans la structure SQLCA, comme dcrit dans la section 2.11.6. Par exemple, en reprenons la base de donnes de la sance de travaux pratiques 2.4 et une requte que nous avons dj rencontre section 2.7.2 : nombre de fois que chacun des lms a t projet . Nous
69
pouvons rcuprer les rsultats de cette requte de ligne unique dans des variables htes de la manire suivante : EXEC SQL BEGIN DECLARE SECTION; VARCHAR titre[128]; int id_film; int nb_proj; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT num_film, titre, COUNT(*) INTO :id_film, :titre, :nb_proj FROM film NATURAL JOIN projection GROUP BY num_film, titre;
2.11.5
Variables indicateur
Prsentation Les variables indicateur sont des variables en langage C qui fournissent des informations complmentaires pour les oprations de lecture ou dinsertion de donnes. Il existe plusieurs types dutilisation pour ces variables. Valeurs NULL : Pour permettre aux applications de grer les valeurs NULL. Troncature de chanes : Pour permettre aux applications de grer les cas o les valeurs lues doivent tre tronques pour tenir dans les variables htes. Erreurs de conversion : Pour stocker les informations relatives aux erreurs. Une variable indicateur est une variable hte de type int suivant immdiatement une variable hte normale dans une instruction SQL. Utilisation de variables indicateur Dans les donnes SQL, la valeur NULL reprsente un attribut inconnu ou une information non applicable. Il ne faut pas confondre la valeur NULL de SQL avec la constante du langage C qui porte le mme nom (NULL). Cette dernire reprsente un pointeur non initialis, incorrect ou ne pointant pas vers un contenu valide de zone mmoire. La valeur NULL nquivaut aucune autre valeur du type dni pour les colonnes. Ainsi, si une valeur NULL est lue dans la base de donnes et quaucune variable indicateur nest fournie, une erreur est gnre (SQLE_NO_INDICATOR). Pour transmettre des valeurs NULL la base de donnes ou en recevoir des rsultats NULL, des variables htes dun type particulier sont requises : les variables indicateur. Par exemple, dans lexemple prcdent, une erreur est gnre si, pour une raison quelconque, le titre du lm nexiste pas et que sa valeur est NULL. Pour saranchir de ce problme, on utilise une variable indicateur de la manire suivante : EXEC SQL BEGIN DECLARE SECTION; VARCHAR titre[128]; int id_film; int nb_proj; int val_ind; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT num_film, titre, COUNT(*) INTO :id_film, :titre :val_ind, :nb_proj FROM film NATURAL JOIN projection GROUP BY num_film, titre; Dans cet exemple, la variable indicateur val_ind vaudra zro si la valeur retourne nest pas NULL et elle sera ngative si la valeur est NULL. Si la valeur de lindicateur est positive, cela signie que la valeur retourne nest pas NULL mais que la chane a t tronque pour tenir dans la variable hte.
70
CHAPITRE 2. SQL
2.11.6
Gestion des erreurs
Congurer des rappels : instruction WHENEVER Linstruction WHENEVER est une mthode simple pour intercepter les erreurs, les avertissements et les conditions exceptionnelles rencontrs par la base de donnes lors du traitement dinstructions SQL. Elle consiste congurer une action spcique excuter chaque fois quune condition particulire survient. Cette opration seectue de la manire suivante : EXEC SQL WHENEVER condition action; Le paramtre condition peut prendre une des valeurs suivantes : SQLERROR : Laction spcie est appele lorsquune erreur survient pendant lexcution dune instruction SQL. SQLWARNING : Laction spcie est appele lorsquun avertissement survient pendant lexcution dune instruction SQL. NOT FOUND : Laction spcie est appele lorsquune instruction ne rcupre ou naecte aucune ligne. Le paramtre action peut avoir une des valeurs suivantes : CONTINUE : Signie eectivement que la condition est ignore. Cest laction par dfaut. SQLPRINT : Ache un message sur la sortie standard. Ceci est utile pour des programmes simples ou lors dun prototypage. Les dtails du message ne peuvent pas tre congurs. STOP : Appel de exit(1), ce qui terminera le programme. BREAK : Excute linstruction C break. Cette action est utile dans des boucles ou dans des instructions switch. GOTO label et GO TO label : Saute au label spci (en utilisant une instruction C goto). CALL nom (args) et DO nom (args) : Appelle les fonctions C spcies avec les arguments spcis. Le standard SQL ne dnit que les actions CONTINUE et GOTO ou GO TO. Linstruction WHENEVER peut tre insre en un endroit quelconque dun programme SQL embarqu. Cette instruction indique au prprocesseur de gnrer du code aprs chaque instruction SQL. Leet de cette instruction reste actif pour toutes les instructions en SQL embarqu situes entre la ligne de linstruction WHENEVER et linstruction WHENEVER suivante contenant la mme condition condition derreur, ou jusqu la n du chier source. Les conditions derreur sont fonction du positionnement dans le chier source de langage C et non du moment o linstruction est excute. Cette instruction est fournie pour vous faciliter le dveloppement de programmes simples. Il est plus rigoureux de contrler les conditions derreur en vriant directement le champ sqlcode de la zone SQLCA (cf. section suivante). Dans ce cas, linstruction WHENEVER est inutile. En fait, linstruction WHENEVER se contente de demander au prprocesseur de gnrer un test if ( SQLCODE ) aprs chaque instruction SQL. Zone de communication SQL (SQLCA) La zone de communication SQL (SQLCA) est une zone de mmoire qui permet, pour chaque demande adresse la base de donnes, de communiquer des statistiques et de signaler des erreurs. En consultant la zone SQLCA, vous pouvez tester un code derreur spcique. Un code derreur sache dans les champs sqlcode et sqlstate lorsquune requte adresse la base de donnes provoque une erreur. Une variable SQLCA globale (sqlca) est dnie dans la bibliothque dinterface, elle a la structure suivante : struct char long long { sqlcaid[8]; sqlabc; sqlcode;
71
struct { int sqlerrml; char sqlerrmc[70]; } sqlerrm; char sqlerrp[8]; long sqlerrd[6]; char sqlwarn[8]; char sqlstate[5]; } sqlca; SQLCA couvre la fois les avertissements et les erreurs. Si plusieurs avertissements ou erreurs surviennent lors de lexcution dune instruction, alors sqlca ne contient que les informations relatives la dernire. Si aucune erreur ne survient dans la dernire instruction SQL, sqlca.sqlcode vaut 0 et sqlca.sqlstate vaut "00000". Si un avertissement ou une erreur a eu lieu, alors sqlca.sqlcode sera ngatif et sqlca.sqlstate sera dirent de "00000". Les champs sqlca.sqlstate et sqlca.sqlcode sont deux schmas dirents fournissant des codes derreur. Les deux sont spcis dans le standard SQL mais sqlcode est indiqu comme obsolte dans ldition de 1992 du standard et a t supprim dans celle de 1999. Du coup, les nouvelles applications sont fortement encourages utiliser sqlstate.
2.11.7
Curseurs pour rsultats lignes multiples
Prsentation Lorsque vous excutez une requte dans une application, le jeu de rsultats est constitu dun certain nombre de lignes. En gnral, vous ne connaissez pas le nombre de lignes que lapplication recevra avant dexcuter la requte. Les curseurs constituent un moyen de grer les jeux de rsultats dune requte lignes multiples. Les curseurs vous permettent de naviguer dans les rsultats dune requte et deectuer des insertions, des mises jour et des suppressions de donnes sous-jacentes en tout point dun jeu de rsultats. Pour grer un curseur vous devez respecter les tapes suivantes : 1. Dclarer un curseur pour une instruction SELECT donne laide de linstruction DECLARE : EXEC SQL DECLARE nom_curseur CURSOR FOR requte_select ; 2. Ouvrir le curseur laide de linstruction OPEN : EXEC SQL OPEN nom_curseur ; 3. Rcuprer une par une les lignes du curseur laide de linstruction FETCH : FETCH [ [ NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE } nombre ] { FROM | IN } ] nom_curseur INTO liste_variables NEXT : Rcupre la ligne suivante. Ceci est la valeur par dfaut. PRIOR : Rcupre la ligne prcdente. FIRST : Rcupre la premire ligne de la requte (identique ABSOLUTE 1). LAST : Rcupre la dernire ligne de la requte (identique ABSOLUTE -1). ABSOLUTE nombre : Rcupre la nombree ligne de la requte ou la abs(nombre)e ligne partir de la n si nombre est ngatif. La position avant la premire ligne ou aprs la dernire si nombre est en-dehors de lchelle ; en particulier, ABSOLUTE 0 se positionne avant la premire ligne. RELATIVE nombre : Rcupre la nombree ligne ou la abs(nombre)e ligne avant si nombre est ngatif. RELATIVE 0 rcupre de nouveau la ligne actuelle si elle existe. nom_curseur : Le nom dun curseur ouvert.
72
CHAPITRE 2. SQL
liste_variables : La liste des variables htes destines recevoir la valeur de chacun des attributs de la ligne courante. Le nombre de variables htes doit tre identique au nombre de colonnes de la table rsultat. 4. Continuez lextraction des lignes tant quil y en a. 5. Fermer le curseur laide de linstruction CLOSE : CLOSE nom_curseur Lors de son ouverture, un curseur est plac avant la premire ligne. Par dfaut, les curseurs sont automatiquement referms la n dune transaction. Voici un exemple utilisant la commande FETCH : EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION; ... EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test; EXEC SQL OPEN foo; while (...) { EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; ... }
2.11.8
Prcompilation et compilation
Inclusion de chiers Pour inclure un chier externe SQL embarqu dans votre programme, utilisez la commande : EXEC SQL INCLUDE nom_fichier; Cette commande indique au prprocesseur du SQL embarqu de chercher un chier nomm nom_fichier.h, de traiter et de linclure dans le chier C gnr. Du coup, les instructions SQL embarqu du chier inclus sont gres correctement. En utilisant la directive classique #include <nom_fichier.h> le chier nom_fichier.h ne serait pas sujet au pr-traitement des commandes SQL. Naturellement, vous pouvez continuer utiliser la directive #include pour inclure dautres chiers den-tte. Prcompilation et compilation La premire tape consiste traduire les sections SQL embarqu en code source C, cest--dire en appels de fonctions de la librairie libecpg. Cette tape est assure par le prprocesseur appel ecpg qui est inclus dans une installation standard de PostgreSQL. Les programmes SQL embarqu sont nomms typiquement avec une extension .pgc. Si vous avez un chier programme nomm prog.pgc, vous pouvez le passer au prprocesseur par la simple commande : ecpg prog1.pgc Cette tape permet de crer le chier prog.c. Si vos chiers en entre ne suivent pas le modle de nommage suggr, vous pouvez spcier le chier de sortie explicitement en utilisant loption -o. Le chier trait par le prprocesseur peut alors tre compil de faon classique, par exemple : cc -c prog.c
73
Cette tape permet de crer le chier prog.o. Les chiers sources en C gnrs incluent les chiers dentte provenant de linstallation de PostgreSQL. Si vous avez install PostgreSQL un emplacement qui nest pas parcouru par dfaut, vous devez ajouter une option comme -I/usr/local/pgsql/include sur la ligne de commande de la compilation. Vous devez enn lier le programme avec la bibliothque libecpg qui contient les fonctions ncessaires. Ces fonctions rcuprent linformation provenant des arguments, excutent la commande SQL en utilisant linterface libpq et placent le rsultat dans les arguments spcis pour la sortie. Pour lier un programme SQL embarqu, vous devez donc inclure la bibliothque libecpg : cc -o monprog prog.o -lecpg De nouveau, vous pourriez avoir besoin dajouter une option comme -L/usr/local/pgsql/lib sur la ligne de commande.
2.11.9
Exemple complet
Voici un exemple complet qui eectue les oprations suivantes : connexion la base ; vrication de la russite de la connexion ; achage du contenu de la table individu en utilisant un curseur ; fermeture de la connexion. #include <stdio.h> // ____ pour grer les erreurs EXEC SQL INCLUDE sqlca; // ____ Dfinition des variables htes EXEC SQL BEGIN DECLARE SECTION; char var_nom[256]; char var_prenom[256]; int var_num; EXEC SQL END DECLARE SECTION; int main(void){ // ____ Ouverture de la connexion la base de donnes EXEC SQL CONNECT TO nom_base@aquanux; if(sqlca.sqlcode) { printf("erreur %s\n",sqlca.sqlerrm.sqlerrmc); exit(0); } printf(" connexion russie \n"); // ____ Utilisation dun curseur pour afficher le contenu de la table individu EXEC SQL DECLARE curseur_individu CURSOR FOR SELECT num_individu, nom, prenom FROM individu; EXEC SQL OPEN curseur_individu; // Boucle daffichage while(SQLCODE==0) { EXEC SQL FETCH FROM curseur_individu INTO :var_num, :var_nom, :var_prenom; printf("Li-ndividu %d est %s %s\n", var_num, var_prenom, var_nom); } EXEC SQL CLOSE curseur_individu; // ____ Fermeture de connexion printf(" Dconnexion \n"); EXEC SQL DISCONNECT; return 0; }
74
CHAPITRE 2. SQL
En supposant que ce programme est enregistr dans un chier nomm prog.pgc, lexcutable est obtenu de la manire suivante : ecpg prog.pgc cc -c prog.c cc -o prog prog.o -lecpg
Chapitre 3
Corrections
75
76
CHAPITRE 3. CORRECTIONS
3.1
Travaux Dirigs 1.5 Modle relationnel
F. 3.1 Exemple simple de MCD
1. Indiquer les cardinalits sur les pattes des associations du MCD de la gure 1.7. Cf. gure 3.1 2. A partir du MCD de la gure 1.7, tablir le schma relationnel. Individu(Num-Ind, Nom, Prnom) Jouer(Num-Ind, Num-Film, Rle) Film(Num-Film, Num-Ind, Titre, Genre, Anne) Projection(Num-Cin, Num-Film, Date) Cinma(Num-Cin, Nom, Adresse) 3. Quelles sont les dpendances fonctionnelles ? Individu : Num-Ind Nom Num-Ind Prnom Jouer : Num-Ind, Num-Film Rle Film : Num-Film Num-Ind Num-Film Titre Num-Film Genre Num-Film Anne
Cinma : Num-Cin Nom Num-Cin Adresse 4. Quelles sont les cls primaires et les cls trangres de chaque relation ? Cls primaires Individu : Num-Ind Jouer : (Num-Ind, Num-Film) Film : Num-Film Projection : (Num-Cin, Num-Film, Date)
3.1. TRAVAUX DIRIGS ?? MODLE RELATIONNEL
77
Cinma : Num-Cin Cls trangres Jouer : Num-Ind, Num-Film Film : Num-Ind Projection : Num-Cin, Num-Film
78
3.2
3.2.1
Travaux Dirigs 1.7 Algbre relationnelle

Exercices de conprhension de requtes
Non corrig.
3.2.2
Trouver la bonne requte

(Titre) (Titre=Drame) Film
15. Quels sont les titres des lms dont le genre est Drame ? 16. Quels lms sont projets au cinma Le Fontenelle ? (Titre,Genre) Film NumFilm Pro jection NumCin (Nom=LeFontenelle) Cinma e e 17. Quels sont les noms et prnoms des ralisateurs ? (Nom,Prnom) Individu NumInd Film e 18. Quels sont les noms et prnoms des acteurs ? (Nom,Prnom) Individu NumInd Jouer e 19. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? R1 = (Nom,Prnom) Individu NumInd Jouer e R2 = (Nom,Prnom) Individu NumInd Film e R = R1 R2 20. Quels lms (titres) ont t projets en 2002 ? (Titre,Genre) Film NumFilm (Date01/01/2002Date<01/01/2003) Pro jection 21. Donnez le titre des lms raliss par Lars von Trier. (Titre) (Nom=von TrierPrnom=Lars) Film NumInd Individu e 22. Quels sont les ralisateurs qui ont ralis des lms dpouvante et des lms dramatiques ? R1 = (NumInd) (Genre=Drame) Film R2 = (NumInd) (Genre=Epouvante) Film R = Individu NumInd (R1 R2 ) 23. Quels sont les titres des lms o Nicole Kidman a jou un rle et qui ont t projets au cinma Le Fontenelle ? R1 = ((Nom=KidmanPrnom=Nicole Individu) NumInd Jouer e R2 = (Cinma.nom=LeFontenelle) Cinma e e R3 = Film NumFilm Pro jection NumCin R2 NumFilm R1 e R = (Titre) R3 24. Quels sont les acteurs qui nont pas jou dans des lms dramatiques ? R1 = Individu NumInd Jouer Acteurs : R2 = (NumInd,Nom,Prnom) R1 e Films dramatiques : R3 = Genre=Drame Film Acteurs dans lms dramatiques : R4 = (NumInd,Nom,Prnom) (R1 NumFilm R3 ) e R = R2 R4 25. Quels sont les noms et prnoms des individus dont le prnom est la fois celui dun acteur et celui dun ralisateur sans quil sagisse de la mme personne ? Acteurs : Act = (NumInd,Nom,Prnom) Individu NumInd Jouer e Ralisateurs : Ral = (NumInd,Nom,Prnom) Individu NumInd Film e e R1 = ((Act.prnom=Ral.prnom)(Act.NumInd Ral.NumInd)) (Act Ral) e e e e e ou R1 = (Act ((Act.prnom=Ral.prnom)(Act.NumInd Ral.NumInd)) Ral) e e e e e R = (Act.prnom,Act.nom) R1 (Ral.prnom,Ral.nom) R1 e e e e
3.2. TRAVAUX DIRIGS ?? ALGBRE RELATIONNELLE
79
26. Quels acteurs a-t-on pu voir au cinma Le Fontenelle depuis lan 2000 ? R1 = ((Cinma.nom=LeFontenelle) Cinma) NumFilm ((Date01/01/2000) Pro jection)) e e R = (Nom,Prnom) (R1 NumFilm Jouer NumInd Individu) e 27. Quels sont les lms qui ont encore t lache 5 annes aprs leur sortie ? (Titre) ((Anne+5Date.Anne) (Film NumFilm Pro jection)) e e 28. Quels sont les cinmas qui ont projet tous les lms ? ((NumCin,Nom,NumFilm) (Pro jection Cinma))/((NumFilm) (Film)) e e 29. Quels sont les acteurs que lon a pu voir dans toutes les cinmas ? R1 = (NumCin) (Cinma) e e R2 = (NumInd,Nom,Prnom,NumCin) Individu Jouer Pro jection e e R = R2 /R1
80
3.3
drop drop drop drop drop
Travaux Pratiques 2.4 PostgreSQL : Premire base de donnes

table table table table table jouer; projection; film; individu; cinema;
Correction partielle correspondant au contenu du chier GenBDCine.sql :
create table individu ( num_individu integer primary key, nom varchar(64), prenom varchar(64)); create table film ( num_film integer primary key, num_realisateur integer references individu, titre varchar(128), genre varchar(64), annee date); create table cinema ( num_cinema integer primary key, nom varchar(64), adresse varchar(128)); create table jouer ( num_acteur integer references individu, num_film integer references film, role varchar(128), primary key (num_acteur, num_film)); create table projection ( num_cinema integer references cinema, num_film integer references film, jour date, primary key (num_cinema, num_film, jour)); \copy \copy \copy \copy \copy individu from individu.txt cinema from cinema.txt film from film.txt jouer from jouer.txt projection from projection.txt
3.4. TRAVAUX PRATIQUES ?? POSTGRESQL : PREMIRES REQUTES
81
3.4
3.4.1
Travaux Pratiques 2.6 PostgreSQL : Premires requtes

Premires requtes
SELECT * FROM individu;
1. Quel est le contenu de la table individu ?
2. Quels sont les prnoms des individus en conservant les doublons ? SELECT prenom FROM individu; 3. Quels sont les prnoms des individus en conservant les doublons, mais en les classant par ordre alphabtique ? SELECT prenom FROM individu ORDER BY prenom; 4. Quels sont les prnoms des individus sans doublons ? SELECT DISTINCT prenom FROM individu; 5. Quels sont les individus dont le prnom est John ? SELECT * FROM individu WHERE prenom=John; 6. Quel est le nom des individus dont le prnom est John ? SELECT DISTINCT nom FROM individu WHERE prenom=John; 7. Dressez la liste de toutes les associations possibles entre un individu et un lm (il ny a pas ncessairement de lien entre lindividu et le lm quon lui associe). SELECT DISTINCT * FROM individu, film; 8. Quels sont les individus qui sont des acteurs ? SELECT DISTINCT individu.* FROM individu, jouer WHERE individu.num_individu = jouer.num_acteur; 9. Dressez la liste de toutes les associations possibles entre un acteur et un lm (il ny a pas ncessairement de lien entre lacteur et le lm quon lui associe). SELECT DISTINCT * FROM (SELECT DISTINCT individu.* FROM individu, jouer WHERE individu.num_individu = jouer.num_acteur) AS acteur, film; 10. Dressez la liste de toutes les interptations, en prcisant le rle, dacteur, dont on prcisera le nom et le prnom, ayant jou dans des lms dont on prcisera le titre. SELECT individu.prenom, individu.nom, jouer.role, film.titre FROM individu, jouer, film WHERE individu.num_individu = jouer.num_acteur AND jouer.num_film = film.num_film; 11. Mme question que la prcdente, mais en formattant le rsultat de la manire suivante : listing -----------------------------------------------------------------Nicole Kidman a jou le rle de Grace dans le film Dogville Paul Bettany a jou le rle de Tom Edison dans le film Dogville SELECT individu.prenom || || individu.nom || a jou le rle de || jouer.role || dans le film || film.titre AS listing FROM individu, jouer, film WHERE individu.num_individu = jouer.num_acteur AND jouer.num_film = film.num_film;
82
3.4.2
SELECT DISTINCT titre FROM film WHERE genre=Drame;
12. Quels sont les titres des lms dont le genre est Drame ?
13. Quels lms (titres) ont t projets en 2002 ? SELECT DISTINCT film.titre FROM film, projection WHERE film.num_film = projection.num_film AND projection.jour < 01/01/2003 AND projection.jour >= 01/01/2002; 14. Donnez le titre des lms raliss par Lars von Trier. SELECT DISTINCT film.titre FROM film, individu WHERE individu.num_individu = film.num_realisateur AND individu.nom=von Trier AND individu.prenom = Lars; 15. Quels lms sont projets au cinma Le Fontenelle ? SELECT DISTINCT titre, genre FROM film,projection,cinema WHERE film.num_film = projection.num_film AND projection.num_cinema = cinema.num_cinema AND cinema.nom = Le Fontenelle; 16. Quels sont les noms et prnoms des ralisateurs ? SELECT DISTINCT nom, prenom FROM individu, film WHERE individu.num_individu = film.num_realisateur; 17. Quels sont les noms et prnoms des acteurs ? SELECT DISTINCT nom, prenom FROM individu, jouer WHERE individu.num_individu = jouer.num_acteur; 18. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : vous ne pouvez utiliser le mot clef UNION puisque nous ne lavons pas encore vu. SELECT DISTINCT acteur.nom, acteur.prenom FROM (SELECT DISTINCT num_individu, nom, prenom FROM individu, jouer WHERE individu.num_individu = jouer.num_acteur) AS acteur, (SELECT DISTINCT num_individu, nom, prenom FROM individu, film WHERE individu.num_individu = film.num_realisateur) AS realisateur WHERE acteur.num_individu=realisateur.num_individu; 19. Quels acteurs a-t-on pu voir au cinma Le Fontenelle depuis lan 2000 ? SELECT DISTINCT individu.* FROM individu, jouer, projection, cinema WHERE individu.num_individu = jouer.num_acteur AND jouer.num_film = projection.num_film AND projection.num_cinema = cinema.num_cinema AND cinema.nom=Le Fontenelle AND projection.jour >= 01/01/2000; 20. Quels sont les titres des lms o Nicole Kidman a jou un rle et qui ont t projets au cinma Le Fontenelle ? SELECT DISTINCT film.titre FROM individu, jouer, film, projection, cinema WHERE individu.num_individu = jouer.num_acteur
3.4. TRAVAUX PRATIQUES ?? POSTGRESQL : PREMIRES REQUTES
83
AND AND AND AND AND AND
jouer.num_film = film.num_film film.num_film = projection.num_film projection.num_cinema = cinema.num_cinema individu.nom=Kidman individu.prenom=Nicole cinema.nom=Le Fontenelle;
3.4.3
Utilisation des expressions rgulires

SELECT prenom FROM individu WHERE prenom ~ s;
21. Quels sont les prnoms des individus qui contiennent la lettre s ?
22. Mme question que la prcdente mais sans distinguer les lettres en fonction de la casse. SELECT prenom FROM individu WHERE prenom ~* s; 23. Quels sont les prnoms des individus dont le prnom commence par la lettre s sans tenir compte de la casse ? SELECT prenom FROM individu WHERE prenom ~* ^s; 24. Quels sont les prnoms des individus dont le prnom se termine par la lettre s sans tenir compte de la casse ? Attention aux espaces la n de la chane ! SELECT prenom FROM individu WHERE prenom ~* s *$; 25. Quels sont les prnoms des individus dont le prnom contient la lettre e sans commencer ou nir par cette lettre et sans tenir compte de la casse ? Attention aux espaces la n de la chane ! SELECT prenom FROM individu WHERE prenom ~* ^[ê].*e.*[ê] *$; 26. Quels sont les prnoms des individus qui ne contiennent pas la lettre e ? SELECT prenom FROM individu WHERE prenom ~* ^[ê]*$; 27. Quels sont les prnoms des individus qui contiennent les lettres a et l dans un ordre quelconque et sans tenir compte de la casse ? SELECT prenom FROM individu WHERE prenom ~* (a.*l)|(l.*a); 28. Quels sont les noms des individus qui contiennent la chane an ou la chane on ? Rpondez en utilisant : (a) loprateur | des expressions rgulires ; (b) les listes de caractres des expressions rgulires ; (c) loprateur OR de la clause WHERE. SELECT nom FROM individu WHERE nom ~* an|on; SELECT nom FROM individu WHERE nom ~* [ao]n; SELECT nom FROM individu WHERE nom ~* an OR nom ~* on; 29. Quels sont les titres des lms qui contiennent au moins trois e ? SELECT titre FROM film WHERE titre ~* e.*e.*e;
84
3.5
Travaux Pratiques 2.8 PostgreSQL : Requtes avances
Dans les exercices de cette section, lobjectif est de trouver les requtes SQL permettant de rpondre aux problmes poss. Nous utilisons la base de donnes sur le cinma (cf. sance de travaux pratiques 2.4).
3.5.1
Prix de GROUP
1. Dressez la liste de toutes les interptations, en prcisant le rle, dacteur, dont on prcisera le nom et le prnom, ayant jou dans des lms dont on prcisera le titre. Contrairement la sance de travaux pratiques 2.6, on utilisera cette fois la commande JOIN dans la requte. SELECT individu.prenom, individu.nom, jouer.role, film.titre FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur JOIN film ON jouer.num_film = film.num_film; 2. On dsire connatre le nom et le prnom des acteurs et le nombre de lms dans lesquels ils ont jou. SELECT individu.prenom, individu.nom, COUNT(*) FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur GROUP BY individu.prenom, individu.nom; 3. On dsire connatre le nom et le prnom des acteurs, le nombre de lms dans lequel ils ont jou ainsi que lanne du lm de leur premier et de leur dernier rle. SELECT individu.prenom, individu.nom, COUNT(*), MIN(film.annee), MAX(film.annee) FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur JOIN film ON jouer.num_film = film.num_film GROUP BY individu.prenom, individu.nom; 4. On dsire connatre le nom et le prnom des acteurs et le nombre de lms dans lesquels ils ont jou pour les acteurs ayant jou dans strictement plus dun lm. SELECT individu.prenom, individu.nom, COUNT(*) FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur GROUP BY individu.prenom, individu.nom HAVING COUNT(*) > 1; 5. On dsire connatre le nom et le prnom des acteurs et le nombre de drames dans lesquels ils ont jou. SELECT individu.prenom, individu.nom, COUNT(*) FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur JOIN film USING (num_film) WHERE film.genre=Drame GROUP BY individu.prenom, individu.nom;
3.5.2
6. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : vous devez utiliser le mot clef INTERSECT puisque nous lavons maintenant vu. SELECT DISTINCT nom, prenom FROM individu JOIN film ON individu.num_individu = film.num_realisateur INTERSECT SELECT DISTINCT nom, prenom FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur;
3.5. TRAVAUX PRATIQUES ?? POSTGRESQL : REQUTES AVANCES
85
7. Quels sont les ralisateurs qui ont ralis des lms dpouvante et des lms dramatiques ? SELECT DISTINCT individu.* FROM individu JOIN film ON individu.num_individu = film.num_realisateur WHERE film.genre=Drame INTERSECT SELECT DISTINCT individu.* FROM individu JOIN film ON individu.num_individu = film.num_realisateur WHERE film.genre=Epouvante; 8. Quels sont les acteurs qui nont pas jou dans des lms dramatiques ? SELECT DISTINCT individu.* FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur EXCEPT SELECT DISTINCT individu.* FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur JOIN film USING (num_film) WHERE film.genre=Drame; 9. Quels sont les cinmas qui ont projet tous les lms ? SELECT cinema.nom FROM cinema NATURAL JOIN projection GROUP BY cinema.nom HAVING COUNT (DISTINCT projection.num_film) = (SELECT COUNT(*) FROM film); 10. Quels sont les acteurs que lon a pu voir dans toutes les cinmas ? SELECT individu.nom, individu.prenom FROM individu JOIN jouer ON individu.num_individu = jouer.num_acteur JOIN projection USING (num_film) GROUP BY individu.nom, individu.prenom HAVING COUNT (DISTINCT projection.num_cinema) = (SELECT COUNT(*) FROM cinema);
3.5.3 GROUP toujours !

11. Quel est le nombre de lms raliss par chacun des ralisateurs ? SELECT individu.prenom, individu.nom, COUNT(*) AS nombre FROM individu JOIN film ON individu.num_individu = film.num_realisateur GROUP BY individu.prenom, individu.nom; 12. Combien de lms ralis le ralisateur qui en a le plus raliss ? SELECT MAX(nombre) FROM ( SELECT individu.prenom, individu.nom, COUNT(*) AS nombre FROM individu JOIN film ON individu.num_individu = film.num_realisateur GROUP BY individu.prenom, individu.nom ) AS realisateur; 13. Quel sont les ralisateurs (il peut y en avoir un ou plusieurs execo) ayant ralis le plus de lms ? SELECT real_1.prenom, real_1.nom FROM ( SELECT individu.prenom, individu.nom, COUNT(*) AS nb_1 FROM individu JOIN film ON individu.num_individu = film.num_realisateur GROUP BY individu.prenom, individu.nom ) AS real_1 WHERE real_1.nb_1 = (
86
SELECT MAX(nb_2) FROM ( SELECT individu.prenom, individu.nom, COUNT(*) AS nb_2 FROM individu JOIN film ON individu.num_individu = film.num_realisateur GROUP BY individu.prenom, individu.nom ) AS real_2 ); Comment serait-il possible de simplier cette requte ? Il faudrait dcomposer la requte en deux requtes distinctes et pouvoir donner un nom la premire pour y faire rfrence dans la deuxime. 14. Quel est le nombre de lms raliss par les ralisateurs, dont on dsire connatre le nom et le prnom, ayant ralis au moins un lm du mme genre que lun des lms raliss par David Cronenberg ? SELECT num_individu, nom, prenom, COUNT(*) AS nombre_de_film FROM film JOIN ( SELECT DISTINCT num_individu, nom, prenom FROM ( SELECT individu.*, genre FROM individu JOIN film ON num_individu = num_realisateur ) AS a NATURAL JOIN ( SELECT genre FROM individu JOIN film ON num_individu = num_realisateur WHERE individu.nom=Cronenberg AND individu.prenom=David ) AS b ) AS realisateur ON film.num_realisateur = realisateur.num_individu GROUP BY num_individu, nom, prenom ; 15. On suppose que les ttes dache dun lm sont les acteurs recenss pour ce lm dans la base de donnes. Quel est le nombre de ttes dache et le ralisateur de chacun des lms ? SELECT realisateur.num_individu, realisateur.nom, realisateur.prenom, COUNT(acteur.num_individu) FROM individu AS realisateur JOIN film ON realisateur.num_individu = film.num_realisateur JOIN jouer ON film.num_film = jouer.num_film JOIN individu AS acteur ON jouer.num_acteur = acteur.num_individu GROUP BY realisateur.num_individu, realisateur.nom, realisateur.prenom; 16. En supposant quun lm cote 1000000 plus 200000 par tte dache, donnez le prix moyen des lms raliss par chacun des ralisateurs. SELECT *, (nombre_film*1000000+200000*nombre_acteur)/nombre_film AS prix_moyen_film FROM ( SELECT individu.num_individu, individu.prenom, individu.nom, COUNT(*) AS nombre_film FROM individu JOIN film ON individu.num_individu = film.num_realisateur GROUP BY individu.num_individu, individu.prenom, individu.nom ) AS nb_film FULL JOIN ( SELECT realisateur.num_individu, realisateur.nom, realisateur.prenom, COUNT(acteur.num_individu) AS nombre_acteur FROM individu AS realisateur JOIN film ON realisateur.num_individu = film.num_realisateur JOIN jouer ON film.num_film = jouer.num_film JOIN individu AS acteur ON jouer.num_acteur = acteur.num_individu GROUP BY realisateur.num_individu, realisateur.nom, realisateur.prenom ) AS nb_acteur
3.5. TRAVAUX PRATIQUES ?? POSTGRESQL : REQUTES AVANCES
87
USING(num_individu, nom, prenom);
88
3.6
3.6.1
Travaux Pratiques 2.10 PostgreSQL : Manipulation des nouveaux objets

Squences
1. Crez une squence test_sequence cyclique commenant 10 de pas dincrment 2 et de valeur maximum 20. CREATE SEQUENCE test_sequence INCREMENT BY 2 MAXVALUE 20 START WITH 10 CYCLE; 2. Testez cette squence (avec la fonction nextval) et observez son comportement. Le cycle recommencet-il 10 ? Pourquoi ? 3. Testez galement les fonctions currval et setval. Eacez la squence de la table. DROP SEQUENCE test_sequence; 4. Modiez votre chier GenBDCine.sql an que la colonne num_individu de la table individu soit du type serial. Rechargez votre base. 5. Tentez dinsrer un nouvel individu sans prciser son num_individu. Quel est le problme ? Comment pouvez-vous y remdier ? La squence commence 1 alors que la table individu contient dj un certain nombre dindividus. Il faut donc rajouter dans le chier GenBDCine.sql la commande SELECT setval(individu_num_individu_seq, 100); aprs avoir observ le nom par dfaut donn la squence.
3.6.2
Schma et vues
CREATE SCHEMA vue;
6. Crez un schma vue. 7. Dans ce schma, crez deux vues, lune correspondant la liste des acteurs, lautre la liste des ralisateurs. Les schmas respectifs de ces relations seront : acteur(num_acteur, nom, prenom) ; realisateur(num_realisateur, nom, prenom). CREATE VIEW vue.acteur AS SELECT DISTINCT num_individu AS num_acteur, nom, prenom FROM individu JOIN jouer ON num_individu = num_acteur ; CREATE VIEW vue.realisateur AS SELECT DISTINCT num_individu AS num_realisateur, nom, prenom FROM individu JOIN film ON num_individu = num_realisateur ;
3.6.3
Rgles
8. Crez une rgle insertion_acteur qui insre un individu dans la table individu la place de linsrer dans la table vue.acteur quand on tente de linsrer dans la table vue.acteur. CREATE RULE insertion_acteur AS ON INSERT TO vue.acteur DO INSTEAD INSERT INTO individu (nom, prenom) VALUES (NEW.nom, NEW.prenom); 9. Quel est le problme de cette rgle ? Par la suite, il ny a pas de moyen de savoir quil sagit dun acteur car la table jouer nest pas mise jour. Lindividu napparat donc pas dans la vue vue.acteur !
3.6. TRAVAUX PRATIQUES ?? POSTGRESQL : MANIPULATION DES NOUVEAUX OBJETS
89
10. Crez une nouvelle ligne dans la table film. Il sagit dun lm ctif : num_film 0 num_realisateur titre 0 NULL genre NULL annee NULL
Quel problme rencontrez-vous ? Trouvez une solution. INSERT INTO individu VALUES(0); INSERT INTO film VALUES(0,0); 11. Ainsi, quand un nouvel acteur est insr, il est possible de mettre jour la table jouer en faisant rfrence ce lm ctif. Corrigez votre rgle insertion_acteur pour mettre en uvre cette nouvelle logique. Vriez quun nouvel acteur insr dans la vue vue.acteur apparraisse bien dans cette vue une fois lopration eectue. CREATE RULE insertion_acteur AS ON INSERT TO vue.acteur DO INSTEAD ( INSERT INTO individu (nom, prenom) VALUES (NEW.nom, NEW.prenom) ; INSERT INTO jouer VALUES (currval(individu_num_individu_seq), 0) ; );
3.6.4
Toujours des requtes
Dans les exercices qui suivent, pour rpondre, utilisez les vues vue.acteur et vue.realisateur quand cela permet de simplier lcriture des requtes. 12. Quels sont les individus qui ne sont ni des acteurs, ni des ralisateurs. SELECT * FROM individu EXCEPT SELECT * FROM vue.acteur EXCEPT SELECT * FROM vue.realisateur; 13. Quels sont les noms et prnoms des acteurs qui sont galement ralisateurs ? Remarque : cette requte a dj t rsolue en utilisant lalgbre relationnelle (cf. travaux dirigs section 1.7.2) et le langage SQL (cf. travaux pratiques 2.6 et 2.8) : SELECT * FROM vue.acteur INTERSECT SELECT * FROM vue.realisateur; 14. Quels sont les noms et prnoms des individus dont le prnom est la fois celui dun acteur et celui dun ralisateur sans quil sagisse de la mme personne ? Remarque : cette requte a dj t rsolue en utilisant lalgbre relationnelle (cf. travaux dirigs section 1.7.2). SELECT vue.acteur.nom, vue.acteur.prenom FROM vue.acteur JOIN vue.realisateur ON num_acteur != num_realisateur AND vue.acteur.prenom = vue.realisateur.prenom UNION SELECT vue.realisateur.nom, vue.realisateur.prenom FROM vue.acteur JOIN vue.realisateur ON num_acteur != num_realisateur AND vue.acteur.prenom = vue.realisateur.prenom;
90
Bibliographie
Articles en ligne sur Developpez.com. (2005a). LE SQL de A Z : Le simple ( ?) SELECT et les fonctions SQL. (http ://sql.developpez.com/sqlaz/select). Articles en ligne sur Developpez.com. (2005b). LE SQL de A Z : Les jointures, ou comment interroger plusieurs tables. (http ://sql.developpez.com/sqlaz/jointures). Bourda, Y. (2005a). Le langage SQL. (http ://wwwlsi.supelec.fr/www/yb/poly_bd/sql/tdm_sql.html). Bourda, Y. (2005b). Systmes de Gestion de (http ://wwwlsi.supelec.fr/www/yb/poly_bd/poly.html). Bases de Donnes Relationnelles.
Chen, P. (1976, March). The Entity-Relationship Model : Toward a Unied View of Data. ACM Transactions on Database Systems, 1(1), 936. Codd, E. F. (1970, June). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 377387. Crescenzo, P. (2005). Un support de cours (http ://www.crescenzo.nom.fr/CMBasesDeDonnees). magistraux de Bases de donnes.
Encyclopdie Wikipdia. (2005). Articles en ligne sur Wikipdia. (http ://fr.wikipedia.org). Gabillaud, J. (2004). SQL et algbre relationnelle - Notions de base. ENI. Hernandez, M. J. & Viescas, J. L. (2001). Introduction aux requtes SQL. Eyrolles. Kauman, J., Matsik, B. & Spencer, K. (2001). Matrisez SQL (Wrox Press, Ed.). CampusPress. Marre, D. (1996, January). Introduction aux systmes de gestion de bases de donnes. Support de cours. SQL Anywhere Studio. (2005a). ASA - Guide de programmation : Programmation avec Embedded SQL. (http ://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/fr/html/dbpgfr9/00000171.htm). SQL Anywhere Studio. (2005b). ASA - Guide de programmation : Utilisation de SQL dans les applications. (http ://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/fr/html/dbpgfr9/00000018.htm). Szulman, S. (2005). Base de donnes et SGBD. Prsentation projette. The PostgreSQL Global Development Group. (2005). (http ://traduc.postgresqlfr.org/pgsql-fr/index.html). Documentation PostgreSQL 7.4.7.
91
92
Bibliographie

Cours BD

Transféré par

Droits d'auteur :

Formats disponibles

Cours BD

Transféré par

Informations du document

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Cours BD

Transféré par

Droits d'auteur :

Formats disponibles

Base de Donnes et langage SQL

(IUT, dpartement informatique, 1re anne)

Table des matires

TABLE DES MATIRES

TABLE DES MATIRES

TABLE DES MATIRES

Bases de donnes relationnelles

Introduction aux bases de donnes

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Modle de base de donnes

1.2. SYSTME DE GESTION DE BASE DE DONNES {S1}

Systme de gestion de base de donnes (SGBD)

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Niveaux de description des donnes

Quelques SGBD connus et utiliss

1.3. RAPPELS SUR LE MODLE ENTIT-ASSOCIATION {S1}

Rappels sur le modle entit-association

F. 1.1 Exemple de reprsentation graphique dun type-entit

F. 1.2 Exemple de reprsentation graphique dun type-entit avec deux attributs

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

F. 1.3 Exemple de reprsentation graphique dun type-association liant deux types-entits

1.4. MODLE RELATIONNEL {S1}

F. 1.5 Exemple de reprsentation graphique de cardinalit

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

1.4. MODLE RELATIONNEL {S1}

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Passage du modle entit-association au modle relationnel

1.4. MODLE RELATIONNEL {S1}

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Travaux Dirigs Modle relationnel

F. 1.7 Exemple simple de MCD

1.6. ALGBRE RELATIONNELLE {S2}

T. 1.2 Exemple de relation Personne

Nom Durand Dupont

Prnom Caroline Lisa

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

T. 1.5 Exemple dunion : R = R1 R2

1.6. ALGBRE RELATIONNELLE {S2}

Relation R Nom Prnom Durand Caroline Dupont Lisa Juny Carole

T. 1.6 Exemple dintersection : R = R1 R2

T. 1.7 Exemple de dirence : R = R1 R2

Jointure, theta-jointure, equi-jointure, jointure naturelle

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Relation Amie Nom Prnom Fourt Lisa Juny Carole

Relation Cadeau Article Prix livre 45 poupe 25 montre 87

Nom Fourt Fourt Fourt Juny Juny Juny

T. 1.8 Exemple de produit cartsien : R = Amie Cadeau

Nom Fourt Fourt Fourt Juny Fidus Fidus

Prnom Lisa Lisa Lisa Carole Laure Laure

T. 1.9 Exemple de jointure : R = Famille ((AgeAgeC)(Prix<50)) Cadeau

1.6. ALGBRE RELATIONNELLE {S2}

Relation Cadeau Age Article Prix 40 livre 45 6 poupe 25 20 montre 87

Nom Fourt Juny Fidus Choupy

Relation R Prnom Age Lisa 6 Carole 40 Laure 20 Emma 6

Article poupe livre montre poupe

T. 1.10 Exemple de jointure naturelle : R = Famille Cadeau

CHAPITRE 1. BASES DE DONNES RELATIONNELLES

Travaux Dirigs algbre relationnelle

Exercices de comprhension de requtes