Cours Base de données SQL

Cours Base de données SQL comment ça marche

...

Tous les systèmes de gestion de données utilisent SQL pour l'accès aux données ou pour communiquer avec un serveur de données. SQL (Standard Query Language) est né à la suite des travaux mathématiques de Codd, travaux qui ont fondé les bases de données relationnelles. SQL, défini d'abord chez IBM, a subi trois tentatives de normalisation en 86, 89 et 92 (SQL 2 ou SQL 92). Nous présentons trois raisons fondamentales qui justifient l'utilisation de SQL.

• D'une part, la structuration et la manipulation des données sont devenues très complexes. Pour une application de taille moyenne, la base de données contient fréquemment plus de trente tables fortement interconnectées. Il est donc hors de question de manipuler les données de façon algorithmique traditionnelle. Une requête SQL dans un langage logique simple remplace donc bien avantageusement plusieurs dizaines de lignes d'un langage de programmation tel C ou Cobol.
• D'autre part, l'architecture client-serveur est omniprésente. Tandis que la station client exécute le code de l'application en gérant, en particulier, l'interface graphique, le serveur optimise la manipulation et le contrôle des données. De plus, les applications doivent être portables et gérer des données virtuelles, c'est-à-dire émanant de n'importe quel serveur. Développer une application dans un environnement hétérogène n'est possible que parce que la communication entre l'applicatif client et le serveur est réalisée par des primitives SQL normalisées.
• Enfin, les applications à développer (même sur un PC) sont devenues de plus en plus complexes. Le profil du programmeur a fortement changé. Il doit maintenant traiter des données de plus en plus volumineuses, intégrer les techniques de manipulation des interfaces, maîtriser la logique événementielle et la programmation orientée objet, tout celà dans un contexte d'architecture client-serveur où se cotoient les systèmes d'exploitation et les protocoles de réseaux hétérogènes. L'accès et la manipulation des données ne sont que l'un des aspects de la conception et de la réalisation de programmes. On cherche donc à acquérir un environnement de développement performant qui prend en charge un grand nombre de tâches annexes. Des outils de développement sont apparus pour permettre au développeur de se concentrer sur l'application proprement dite : générateurs d'écrans, de rapports, de requêtes, d'aide à la conception de programme, de connexion à des bases de données distantes via des réseaux. Dans tous ces outils, la simplicité et la standardisation de SQL font que SQL est utilisé chaque fois qu'une définition, une manipulation, ou un contrôle de données est nécessaire. SQL est donc un élément central entre les divers composants d'un environnement de développement dans une architecture client-serveur.

.2 SQL dans l'architecture en couches des SGBD.

Dans la phase d'analyse de systèmes d'information, on considère différents niveaux d'abstraction du système d'information : le niveau conceptuel, le niveau logique ou organisationnel et enfin, le niveau physique ou opérationnel.

Nous allons considérer ici différents niveaux de perception d'une base de données relationnelle. Un SGBD est fréquemment décrit par une structure en couches correspondant à des perceptions différentes des données, associées à des tâches différentes pour différents acteurs.

Pour plus de simplicité, nous distinguerons trois types d'acteurs : les administateurs de la base de donnée, les développeurs et les utilisateurs. . Au niveau externe, proche de l'utilisateur, la perception est totalement indépendante du matériel et des techniques mises en oeuvre, tandis qu'au niveau le plus intérieur, se trouvent les détails de l'organisation sur disque et en mémoire.

Le schéma logique est l'ensemble de toutes les données pertinentes, toutes applications confondues. Il est rendu conforme à un modèle de représentation des données, et est totalement indépendant de la technologie utilisée. Nous choisissons le modèle relationnel. Ce niveau a un inconvénient : toutes les données sont accessibles à tout le monde. Cet ensemble vaste de données est trop touffu. Il est préférable de montrer à l'utilisateur (et au programmeur) une vue plus simple des données. On constitue ainsi des schémas externes. Par exemple, le gestionnaire du stock n'est concerné que par les données décrivant les articles en stock. S'il ne manipule que des bordereaux d'entrée, des bordereaux de sortie, et des fiches d'état du stock, ceux-ci constituent son schéma de perception externe. Le schéma interne fournit une perception plus technique des données, et enfin le schéma physique est dépendant du matériel et du logiciel de base.

Niveau externe.

Au niveau externe, les utilisateurs et développeurs d'application ont une perception limitée de la base de données. On parle de vue. Une vue peut être considérée comme une restriction du schéma logique à un type d'utilisateur. Ce niveau concerne les utilisateurs et les développeurs.

Niveau logique.

Traduction dans le modèle relationnel du schéma conceptuel. On précise à ce niveau les tables, les relations entre tables, les contraintes d'intégrité, les vues et les droits par groupe d'utilisateurs. Ce niveau concerne l'administrateur et les développeurs.

Niveau interne.

On définit les index et tous les éléments informatiques susceptibles d'optimiser les ressources et les accès aux données. Ce niveau concerne l'administrateur.

Niveau physique.

On y précise tout ce qui dépend du matériel et du système d'exploitation. Ce niveau concerne l'administrateur.

Cette découpe en niveaux présente les avantages suivants :

• Les applications développées sont indépendantes du niveau interne. Tout changement de stratégie d'accès, ou d'organisation des données entraîne une modification au niveau interne, mais le schéma logique reste inchangé. Par exemple, une requête SQL précise le QUOI sans se préoccuper du COMMENT.
• La distinction externe/logique assure (en partie) l'indépendance entre les applications et le niveau logique. Par exemple on peut enrichir le schéma logique sans modifier les applications existantes pour toutes les vues non concernées par les modifications apportées au schéma logique.
• La distinction logique/interne permet de modifier les optimisations d'accès aux données. Par exemple, si une application a des performances insuffisantes, il est possible d'optimiser les accès (en introduisant de nouveaux index, par exemple) et d'augmenter les performances sans modifier l'application.
• La distinction interne/physique permet une meilleure portabilité car seule la partie physique est dépendante du matériel et du système d'exploitation.

.3 Principes de base d'une Base de Données relationnelle

Le terme relationnel provient de la définition mathématique d'algèbre relationnelle (Codd 70). Une relation est un ensemble de tuples4 de données, on peut alors définir des opérations algébriques sur les relations . Nous parlerons dans la suite de table, et non pas de relation. Une table est un ensemble de tuples de données distincts deux à deux. Une table est constituée d'une clé primaire et de plusieurs attributs (ou colonnes) qui dépendent de cette clé. La clé primaire d'une table est un attribut ou un groupe d'attributs de la table qui détermine tous les autres de façon unique. Une table possède toujours une et une seule clé primaire. Par contre, une table peut présenter plusieurs clés candidates qui pourraient jouer ce rôle. Le domaine d'un attribut est l'ensemble des valeurs que peut prendre cet attribut. Le domaine est constitué d'un type, d'une longueur et de contraintes qui réduisent l'ensemble des valeurs permises. Une clé étrangère dans une table est une clé primaire ou candidate dans une autre table. Les contraintes d'intégrité font partie du schéma logique. Parmi celles-ci, on distingue

• les contraintes de domaine qui restreignent l'ensemble des valeurs que peut prendre un attribut dans une table,
• les contraintes d'intégrité d'entité qui précisent qu'une table doit toujours avoir une clé primaire et
• les contraintes d'intégrité référentielles qui précisent les conditions dans lesquelles peuvent être ajoutés ou supprimés des enregistrements lorsqu'il existe des associations entre tables par l'intermédiaire de clés étrangères.

Exemple 1  

CLIENTS (cltnum, cltnom, cltpnom, cltloc, cltca, clttype)

COMMANDES (cmdnum, cmdclt, cmddate, cmdvnd)

LIGCOMMANDES (ldccmd, ldcart, ldcqte)

ARTICLES (artnum, artnom, artpv, artcoul)

Les identifiants sont en gras, les clés étrangères en italique. Une contrainte d'intégrité référentielle est, par exemple, l'obligation de la présence d'un client pour une comande. C'est-à-dire encore qu'à un enregistrement dans la table COMMANDES doit correspondre un enregistrement de la table CLIENTS tel que

COMMANDES.cmdclt=CLIENTS.cltnum.

.4 Architecture client-serveur et communication par SQL

Nous allons d'abord revoir les différents types d'application possibles pour la gestion de données distantes en commençant par les trois formes les plus simples.

Monoposte.

La base de données se trouve sur un poste et n'est pas accessible en réseau. Il faut, dans ce cas, penser à la notion de sécurité si plusieurs utilisateurs peuvent interroger la base (suppression accidentelle d'enregistrements).

Multiposte, basée sur des terminaux

, liés à un site central. C'est l'informatique multiposte traditionnelle. La gestion des données est centralisée. Les applications ont été écrites par le service informatique et seules ces applications peuvent interroger le serveur.

Multiposte, basée sur un serveur de fichiers.

C'est la première forme (la plus simple) d'architecture client-serveur. Si l'applicatif sur un PC souhaite visualiser la liste des clients habitant Paris, tous les enregistrements du fichier CLIENT transitent sur le réseau, entre le serveur et le client, la sélection (des clients habitant Paris) est faite sur le PC. Le trafic sur le réseau est énorme et les performances se dégradent lorsque le nombre de clients augmente. Les serveurs de fichiers restent très utilisés comme serveurs d'images, de documents, d'archives.

De nouveaux besoins sont apparus :

• diminuer le trafic sur le réseau pour augmenter le nombre de postes sans nuire au fonctionnement,
• traiter des volumes de données de plus en plus grand,
• accéder de façon transparente à des données situées sur des serveurs différents,
• accéder aux données de façon ensembliste, même si les données sont distantes, afin de diminuer le travail du programmeur,
• adopter des interfaces graphiques de type Windows pour les applicatifs clients.

Bases de données en client-serveur

Dans une architecture client-serveur, un applicatif est constitué de trois parties : l'interface utilisateur, la logique des traitements et la gestion des données. Le client n'exécute que l'interface utilisateur et la logique des traitements, laissant au serveur de bases de données la gestion complète des manipulations de données.

• Le serveur de bases de données fournit des services aux processus clients. Les tâches qu'il doit prendre en compte sont : la gestion d'une mémoire cache, l'exécution de requêtes exprimées en SQL, exécuter des requêtes mémorisées, la gestion des transactions, la sécurité des données.
• Le client doit ouvrir une connexion pour pouvoir profiter des services du serveur. Il peut ouvrir plusieurs connexions simultanées sur plusieurs serveurs. Le client peut soumettre plusieurs requêtes simultanément au serveur et traiter les résultats de façon asynchrone.
• Communication entre le client et le serveur. Puisque l'application doit pouvoir se connecter à divers serveurs de façon transparente, le langage de communication SQL doit être compatible avec la syntaxe SQL de chaque serveur pressenti. Or, malgré les normes, les dialectes SQL sont nombreux et parfois source d'incompatibilité. La seule façon de permettre une communication plus large est d'adopter un langage SQL standardisé de communication. Une couche fonctionnelle du client traduit les requêtes du dialecte SQL client en SQL normalisé. La requête transformée est envoyée au serveur. Celui-ci traduit la requête dans le dialecte SQL-serveur et l'exécute. Le résultat de la requête suit le chemin inverse. Le langage de communication normalisé le plus fréquent est l'ODBC (Open DataBase Connectivity) de Microsoft. Signalons également IDAPI (Integrated Database Application Programming Interface) de Borland. De plus, ces programmes permettent d'interroger des bases de données autres que relationnelles.

Les serveurs de transactions

Une transaction correspond à une procédure SQL, i.e. un groupe d'instructions SQL. Il y a un seul échange requête/réponse pour la transaction. Le succès ou l'échec concerne l'ensemble des instructions SQL. Ces serveurs sont essentiellement utilisés pour l'informatique de production (ou opérationnelle) pour laquelle la rapidité des temps de réponse est importante sinon essentielle.

.5 Structure générale du langage SQL

Les instructions essentielles SQL se répartissent en trois familles fonctionnellement distinctes et trois formes d'utilisation :

Selon la norme SQL 92, le SQL interactif permet d'exécuter une requête et d'en obtenir immédiatement une réponse. Le SQL intégré ( ou module SQL) permet d'utiliser SQL dans un langage de troisième génération (C, Cobol, ...), les instructions permettant essentiellement de gérer les curseurs. Le SQL dynamique est une extension du SQL intégré qui permet d'exécuter des requêtes SQL non connues au moment de la compilation. Hors norme, SQL est utilisable sous la forme de librairies de fonctions API (exemple : ODBC). Nous nous limitons au SQL interactif.

Dans le SQL interactif, le LDD (Langage de Définition de données) permet la description de la structure de la base (tables, vues, index, attributs, ...). Le dictionnaire contient à tout moment le descriptif complet de la structure de données. Le LMD (Langage de Manipulation de Données) permet la manipulation des tables et des vues. Le LCD (Langage de Contrôle des Données) contient les primitives de gestion des transactions et des privilèges d'accès aux données. Le tableau ci-dessous vous donne les principales primitives SQL et leur classification. Nous nous intéresserons essentiellement au LMD et à la commande SELECT. Nous étudierons également quelques problèmes concernant les privilèges d'accès et les transactions.

SQL interactif

LDD LMD LCD

CREATE SELECT GRANT

DROP INSERT REVOKE

ALTER DELETE CONNECT

  UPDATE COMMIT

    ROLLBACK

    SET

.6 SQL : un langage algébrique

Pour bien comprendre le langage SQL, nous allons brièvement exposer les principes sur lesquels repose ce langage (algèbre relationnelle).

Une table (relation) est un ensemble de tuples. On peut donc appliquer à une table les opérateurs algébriques usuels. Le résultat d'une opération ou requête est une nouvelle table qui est exploitable à son tour dans une nouvelle opération. Tous les opérateurs peuvent être dérivés de cinq primitives de base : la PROJECTION, la SELECTION, l'UNION, la DIFFERENCE et le PRODUIT. L'opérateur de JOINTURE qui peut être déduit des cinq primitives de base est cependant fondamental.

La PROJECTION

permet de ne conserver que les attributs intéressants d'une table (sélection verticale). De plus, la projection élimine les répétitions de tuples résultant de cette sélection.

Exemple 2   CLIENTS2 = PROJECT CLIENTS OVER (cltnom, cltloc)

La SELECTION

permet de ne conserver que les tuples qui respectent une condition définie sur les valeurs des attributs (sélection horizontale).

Exemple 3   BONSCLIENTS = SELECT CLIENTS WHERE cltca>10000

L'UNION

réalise l'union de plusieurs tables.

Exemple 4   CLIENTS3 = SELECT CLIENTS WHERE cltloc = 'PARIS' UNION SELECT BONSCLIENTS WHERE cltloc='BRUXELLES'

La DIFFERENCE

consiste à prendre les tuples appartenant à une table mais pas à une autre.

Exemple 5   CLIENTS4 = SELECT CLIENTS WHERE cltloc = 'BRUXELLES' EXCEPT BONSCLIENTS

Le PRODUIT

réalise la juxtaposition de tous les tuples de la première table avec chaque tuple de la seconde. Celà signifie que, si les deux tables ont respectivement M et N tuples, la table résultante aura M× N tuples. Cette opération présente peu d'intérêt mais combinée avec une sélection, on obtient une opération fondamentale : la JOINTURE.

La JOINTURE

n'est possible que sur deux tables possédant un attribut de domaine commun. Elle consiste à juxtaposer les tuples dont la valeur d'un attribut est égal dans les deux tables. C'est une primitive dérivée car elle peut être définie à l'aide des primitives précédentes (exercice laissé au lecteur).

Exemple 6   CMDCLIENTS = COMMANDES JOIN CLIENTS ON cmdclt=cltnum

Les primitives peuvent être combinées pour constituer des requêtes plus élaborées.La séquence d'opérateurs permettant de réaliser une requête élaborée devient assez vite complexe. Le langage SQL permet (heureusement) d'exprimer globalement une requête sans faire apparaître les tables et les primitives intermédiaires. Ce sera le moteur SQL qui sera chargé d'optimiser la requête.

Exemple 7   une requête SQL qui permet de dresser la liste des noms des clients qui ont acheté des articles de moins de 200F est :

SELECT DISTINCT cltnom FROM CLIENTS, COMMANDES, LIGCOMMANDES, ARTICLES WHERE cltnum=cmdclt AND artnum=lcdart AND cmdnum=ldccmd AND artpv<200

Une combinaison de primitives permettant d'exécuter cette requête est :

TEMP1 = SELECT ARTICLES WHERE artpv<200

TEMP2 = PROJECT TEMP1 OVER (artnum)

TEMP3 = PROJECT CLIENTS OVER (cltnum,cltnom)

TEMP4 = PROJECT COMMANDES OVER (cmdnum, cmdclt)

TEMP5 = PROJECT LIGCOMMANDES OVER (ldccmd,ldcart)

TEMP6 = TEMP2 JOIN TEMP5 ON artnum=ldcart

TEMP7 = PROJECT TEMP6 OVER (ldccmd)

TEMP8 = TEMP3 JOIN TEMP4 ON cltnum=cmdclt

TEMP9 = PROJECT TEMP8 OVER (cltnom, cmdnum)

TEMP10 = TEMP7 JOIN TEMP9 ON ldccmd=cmdnum

RESULTAT = PROJECT TEMP10 OVER (cltnom)

Une telle séquence n'est, en général, pas unique. La séquence fournie est une des plus efficaces (le lecteur peut s'exercer à en trouver d'autres).

.7 Eléments de SQL

7.1 La commande SELECT

Syntaxe :

SELECT

attributs

expressions extraites

FROM expressions de tables

WHERE conditions de filtrage

GROUP BY critères de regroupement

HAVING conditions de filtrage sur les groupes

ORDER BY critères de tri

Les attributs

permettent de préciser si on souhaite inclure toutes les lignes (attribut ALL par défaut) ou seulement les lignes distinctes (attribut DISTINCT).

Exemple 8   SELECT cltloc FROM clients : on affiche les villes des clients avec répétition (il y a autant de lignes affichées que de clients).

SELECT DISTINCT cltloc FROM clients : on affiche les villes des clients sans répétition.

Les expressions extraites

sont des champs de table, des expressions calculées faisant intervenir les champs, ou des expressions faisant intervenir des fonctions de calcul. Les fonctions utilisées peuvent être des fonctions qui s'appliquent à la valeur d'un champ (upper, year, ...) ou des fonctions de groupe qui effectuent un calcul sur l'ensemble des valeurs d'un champ dans un groupe (max, min, sum, count, avg).

Exemple 9   SELECT cmdnum, year(cmddate) FROM commandes : on affiche les numéros de commande avec l'année correspondante.

SELECT cltnom + `` ``+ cltpnom FROM clients : on affiche la concaténation des nom et prénom des clients.

SELECT AVG(artpv) FROM articles : on affiche le prix de vente moyen des articles.

Les expressions de tables

se présentent sous la forme d'une table, d'une jointure entre tables, d'une liste de tables. Les opérateurs de jointure seront : INNER JOIN, LEFT JOIN ou RIGHT JOIN qui correspondent respectivement à la jointure, la jointure externe gauche et la jointure externe droite.

Exemple 10   SELECT cltnom,cmdnum FROM clients INNER JOIN commandes ON cltnum=cmdclt : on affiche les noms des clients ayant passé une commande avec le numéro de commande correspondant (autant de fois que de commandes).

SELECT cltnom,cmdnum FROM clients LEFT JOIN commandes ON cltnum=cmdclt : on affiche les noms des clients sans autre information s'ils n'ont pas passé de commandes et avec le numéro de commande correspondant (autant de fois que de commandes) sinon.

Les conditions de filtrage

correspondent à des sélections. On peut utiliser les opérateurs de comparaison usuels. On peut également utiliser les trois opérateurs BETWEEN, LIKE et IN.

remarque Les conditions de jointure peuvent être introduites à l'intérieur de la clause WHERE. Dans ce cas, l'expression de tables correspond à la liste des tables utilisées. L'écriture obtenue est en général plus simple, mais il faut savoir que l'exécution peut être moins efficace. Par exemple, on peut écrire SELECT cltnom, cmdnum FROM clients, commandes WHERE cltnum=cmdclt en lieu et place de SELECT cltnom,cmdnum FROM clients INNER JOIN commandes ON cltnum=cmdclt.

Exemple 11   SELECT cmdnum FROM commandes WHERE year(cmddate)=1995 : on affiche les numéros de commande de l'année 1995.

SELECT artnom FROM articles WHERE artpv BETWEEN 100 and 200 : on affiche les noms d'article dont le prix de vente est entre 100 et 200.

SELECT cltnom FROM clients WHERE cltloc IN ('PARIS', 'LILLE') : on affiche les noms des clients parisiens et lillois.

SELECT cltnom FROM clients WHERE cltnom LIKE 'M*' : on affiche la liste des clients dont le nom commence par M.

Les critères de regroupement.

Un groupe est un sous-ensemble des tuples d'une table (qui peut être le résultat d'une requête), pour lesquels les valeurs du champ de regroupement reste constante. Les groupes sont spécifiés par la clause GROUP BY suivie du nom du champ sur lequel on effectue le regroupement. Les fonctions de groupe usuelles sont : MAX, MIN, SUM, COUNT, AVG. La clause HAVING est l'équivalent du WHERE appliqué aux groupes. Les conditions de filtrage de la clause HAVING sont des conditions qui portent sur les fonctions de groupe. Les critères de la clause WHERE s'appliquent aux tuples, ceux de la clause HAVING s'appliquent aux groupes. Le moteur SQL élimine d'abord les tuples qui ne satisfont pas aux conditions du WHERE, puis il constitue les groupes et leur applique les conditions du HAVING.