1 Structure logicielle

Informatique
Cours S2.7
Bases de données
Les principales capacités développées sont :
• utiliser une application offrant une interface graphique pour créer une base de données et l’alimenter,
• utiliser une application offrant une interface graphique pour lancer des requêtes sur une base de
données,
• distinguer les rôles respectifs des machines client, serveur, et éventuellement serveur de données,
• traduire dans le langage de l’algèbre relationnelle des requêtes écrites en langage courant,
• concevoir une base constituée de plusieurs tables, et utiliser les jointures symétriques pour effectuer
des requêtes croisées.
1 Structure logicielle
Les bases de données sont largement répandues : pages dynamiques sur internet, catalogues en ligne,
données personnelles…
1.1 Structure client/serveur
La base de données (database) est connectée au réseau afin d'être plus largement accessible. L'accès est
contrôlé grâce à un serveur (SGBD : Système de Gestion de Base de Données).
L'utilisateur qui souhaite interroger ou compléter la base de données utilise un logiciel client.
Les instructions émises par le client à la base de données sont appelées des requêtes. En réponse, le
serveur renvoie les données correspondant à la requête (valeurs enregistrées ou collectées).
Les requêtes peuvent être écrites dans un Langage de reQuête Structurée et normalisée SQL (Structured
Query Language).
Client : lance les requêtes
Serveur : stocke les informations et transmet les réponses aux requêtes
[1]
Figure 1 : Relation client-serveur pour une base de données locale (serveur de fichiers iaca du lycée par exemple)
1.2 Structure 3 tiers
La structure 3 tiers repose sur la présence d'une couche intermédiaire (application) entre le client et la base
de données.
Figure 2 : Structure logicielle 3 tiers pour l'interrogation d'une base de données[2].
La structure 3 tiers apporte plusieurs avantages :
- permettre une modification du traitement des données sans modifier la base de données,
- s'adapter à des plateformes hétérogènes (systèmes d'exploitation différents),
- alléger le traitement du client,
- sécuriser la base de données,
- permettre de répartir les charges entre serveurs d'applications.
Lycée Jules Ferry Cannes
Page 1 sur 6
TSI1
Informatique
Cours S2.7
Bases de données
Figure 3 : Relation client-serveur pour des requêtes par internet (demande d'information météo par exemple)
2 Structure des bases de données
2.1 Table
Les bases de données sont structurées sous la forme de tables (ou relations) dont les colonnes
représentent les attributs (ou champs).
Chaque ligne de ces tables représentent les enregistrements successifs de la table. Ces enregistrements
sont appelées les instances.
Exemple la table suivante est composée :
o de 7 attributs (num_sta, nom_usuel…)
o de 3 enregistrements apparents (7005, 7015 et 7020).
Table RADOME
Figure 4 : Table RADOME (coordonnées GPS et altitude des différentes stations de Météo en France)
2.2 Domaine des données
On appelle domaine d'une donnée les contraintes qu'elle doit respecter : type, nombre entre 0 et 1, listes
de valeurs ou de caractère par exemple.
Pour un enregistrement, chaque attribut peut être d'un type différent.
Les types de variables sont nombreux mais peuvent être classés en 5 affinités (sorte de typage
dynamique) [3]:
- TEXT : chaine de caractères,
- INTEGER : entier,
- REAL : nombre à virgule,
- NONE (ou BLOB): du type de ce qui a été saisi,
- NULL : absence de valeur pour l'attribut correspondant
Exemple : num_sta est un INTEGER; nom_usuel est un TEXT, latitude est un REAL.
2.3 Clef primaire
Afin de distinguer chaque enregistrement, on utilise une clef primaire. Il s'agit d'un attribut dont la valeur est
différente pour chaque enregistrement. La clef primaire est généralement un entier positif que l'on
incrémente à chaque nouvel enregistrement.
La clef primaire peut aussi être un texte dont le SGDB s'assurera qu'il n'y a pas de répétition.
Lycée Jules Ferry Cannes
Page 2 sur 6
TSI1
Informatique
Cours S2.7
Bases de données
2.4 Association entre table
Dans une base de données, il existe des liens entre les tables.
Pour indiquer les liens entre tables on peut tracer le schéma relationnel suivant :
clef
primaire
clef
étrangère
Figure 5 : Association des tables RADOME et SYNOP par l'attribut primaire de RADOME
L'association se fait ici par une clef étrangère à SYNOP. Dans SYNOP apparait ainsi l'attribut
RADOME_num_sta. On peut aussi donner le même nom aux attributs qui créent le lien entre 2 tables (ici
ce serait num_sta mais ce n'est pas obligatoire).
Remarque sur l'exemple précédent :
- un enregistrement n'est possible dans SYNOP que pour une station de numéro défini dans
RANDOME; inversement on ne peut supprimer un numéro de station que s'il n'existe aucun
enregistrement dans SYNOP,
- RADOME_num_sta n'est pas une clef primaire car la même station aura plusieurs enregistrements
météorologiques,
- les icônes présents devant les attributs ne sont pas à connaitre et ne sont pas normalisés.
3 Algèbre relationnel et requête SQL
Les commandes SQL qui permettent d'interroger la base de données sont des instructions normalisées.
Les instructions SQL sont insensibles à la casse (pas de distinction entre les minuscules et les
majuscules). Par contre, certains domaines peuvent être paramétrés pour contenir des enregistrements
avec un type sensible à la casse.
Dans la suite de ce cours on écrira les instructions et les tables en majuscules et les attributs en
minuscules mais cela n'a que pour but d'améliorer la lisibilité des instructions.
3.1 Opérateurs relationnels
Projection : Sélection de certaines colonnes (ou élimination d’attributs).
- algèbre relationnel :
←
,
,… ( 1 )
-
requête SQL :
←SELECT
,
,. .. FROM
Exemples : « Quelle est la liste complète des enregistrements de la table RADOME ? »
o
o
!"#)
SQL : SELECT * FROM RADOME ;
Algebre relationnelle :
Lycée Jules Ferry Cannes
∗ (
Page 3 sur 6
TSI1
Informatique
Cours S2.7
Bases de données
« Quels sont les numéros de station et les communes correspondantes ? »
o
o
!"#)
SQL : SELECT num_sta, commune FROM RADOME ;
Algebre relationnelle :
$%&_()*,+,&&%$- (
L'instruction SELECT DISTINCT permet d'éliminer les enregistrements identiques lors de l'affichage.
Exemple : « Quels sont les communes équipés d'au moins une station météo? »
o SQL : SELECT DISTINCT commune FROM RADOME
Sélection (restriction) : Sélection de certaines lignes qui vérifient une condition.
- algèbre relationnel :
←.
/012345/523( 1 )
- requête SQL :
←SELECT ∗FROM
WHERE attribute == condition ;
Exemple : « Quels sont les numéros de station et les villes correspondantes situées dans le var ? »
$%&_()*,+,&&%$- 678é:*;)-&-$) 0<= (>?@ABC)D
o
algèbre relationnel :
o
requête SQL : SELECT
num_sta, commune FROM RADOME
WHERE département == 83 ;
Remarques sur l'écriture des conditions :
- les conditions utilisent les mêmes symboles de comparaison qu'en python : == !=
- les opérations standards + /
*
%
AND OR
NOT
- la concaténation de liste par contre se fait par les symboles
||
>=
<=
Jointure symétrique : Combiner deux tables en une seule table suivant un attribut commun.
- algèbre relationnel : E ← .F .
/0G .
/ ( 1 × R2 )
- requête SQL :
←SELECT ∗FROM
JOIN
ON R1.attribute1 == R2.attribute2 ;
Exemple : « Quelles sont les villes où la vitesse du vent moyen a dépassé les 20 m/s ? »
o algèbre relationnel :
+,&&%$- 67 K.$%&_()*0>L.MNO_PQR (>?@ABC ×
STUQVPPV_TVMQW X (YZ[A\))D
o requête SQL :
SELECT RADOME.commune
FROM RADOME JOIN SYNOP
ON RADOME.num_sta == SYNOP.RADOME_num_sta
WHERE SYNOP.vitesse_vent >20 ;
RADOME.num_sta peut aussi s'écrire num_sta en absence de confusion possible avec un attribut de la
table SYNOP.
De même pour tous les préfixes de la requête ce qui donne :
SELECT commune
FROM RADOME JOIN SYNOP
ON num_sta == RADOME_num_sta
WHERE vitesse_vent >20 ;
On peut changer le nom d'un attribut dans la requête, par exemple RADOME_num_sta en num :
SELECT commune, RADOME_num_sta as num FROM RADOME JOIN SYNOP
WHERE vitesse_vent >20 ON num_sta == num;
Lycée Jules Ferry Cannes
Page 4 sur 6
TSI1
Informatique
Cours S2.7
Bases de données
Fonctions d'agrégation : Ces fonctions qui ne sont pas couvertes par l’algèbre relationnelle classique,
permettent d’effectuer des calculs statistiques basiques sur les valeurs :
MIN, MAX, SUM (somme), AVG(moyenne), COUNT(comptage du nombre de lignes).
La clause GROUP BY permet de rassembler les enregistrements ayant l'attribut spécifié en commun.
La clause HAVING permet d'imposer une condition sur le résultat de l'agrégation (valeur minimum …)
La clause ORDER BY permet de trier l'affichage selon les attributs spécifiés.
Exemple : « Quelle est la ville et la valeur de la pression où la plus haute valeur a été mesurée ? »
o algèbre relationnel :
+,&&%$- ,]" ^(:;-((_,$) 67 K.$%&_()*0>L.MNO_PQR (>?@ABC × YZ[A\)D
o requête SQL :
SELECT RADOME.commune , MAX(SYNOP.pression)
FROM RADOME JOIN SYNOP ON RADOME.num_sta == SYNOP.RADOME_num_sta
ORDER BY RADOME.commune, SYNOP.pression
Bilan : formalisme général d'une jointure symétrique
SELECT <liste d’expressions>
FROM <liste de tables> ON <conditions de jointure>
WHERE <conditions>
GROUP BY <liste d’attributs>
HAVING <conditions>
ORDER BY <liste attributs>
3.2 Opérateurs ensemblistes
Union : Relation contenant les attributs appartenant à R1 ou à R2. Il y a suppression des lignes identiques.
- algèbre relationnel : R3 ← R1 ⋃ R2 :
- Syntaxe SQL : R3 ←R1 UNION R2
Exemple : « Quels sont les numéros de station situés dans le var et celles situées dans les alpesmaritimes ? »
o algèbre relationnel :
$%&_()* 678é:*;)-&-$)0<= (>?@ABC) ⋃ 78é:*;)-&-$) 0bc (>?@ABC)D
o
requête SQL :
SELECT num_sta FROM RADOME WHERE départment=83
UNION SELECT num_sta FROM RADOME WHERE départment=06
Intersection : Relation contenant les lignes appartenant à R1 et à R2.
- algèbre relationnel : R3 ← R1 ⋂ R2 :
- Syntaxe SQL : R3 ←R1 INTERSECT R2
Exemple : « Quels sont les numéros de station situés dans le var et à une altitude supérieure à 70
m? »
o algèbre relationnel :
$%&_()* 678é:*;)-&-$)0<= (>?@ABC) ∩
o
7*f)_)%8-WgX (>?@ABC)D
requête SQL :
SELECT num_sta FROM RADOME WHERE départment=83
INTERSECT SELECT num_sta FROM RADOME WHERE altitude>70
Lycée Jules Ferry Cannes
Page 5 sur 6
TSI1
Informatique
Cours S2.7
Bases de données
Différence : Relation contenant les lignes appartenant à R1 moins celles appartenant à R2.
- algèbre relationnel : R3 ← R1 − R2 :
- Syntaxe SQL : R3 ←R1 EXCEPT R2
Exemple : « Quels sont les numéros de station situés dans le var et qui ne sont pas à une altitude
supérieure à 70 m ? »
o algèbre relationnel :
$%&_()* 678é:*;)-&-$)=<= (>?@ABC)
o requête SQL :
− 7*f)_)%8->kb (>?@ABC )D
SELECT num_sta FROM RADOME WHERE départment=83
EXCEPT SELECT num_sta FROM RADOME WHERE altitude>70
Produit cartésien : R3 ← R1 × R2 : Relation contenant l’ensemble des possibilités d’association entre une
valeur de R1 et une valeur de R2.
On l’utilise notamment pour définir l’opérateur relationnel de type ≪ jointure symétrique≫ (voir partie
précédente).
Références :
[1] P. Beynet, « Informatique ». UPSTI.
[2] « Architecture trois tiers », Wikipédia. 26-févr-2016.
[3] « Query Language Understood by SQLite ». [En ligne]. Disponible sur: http://www.sqlite.org/lang.html.
[Consulté le: 02-mai-2016].
Lycée Jules Ferry Cannes
Page 6 sur 6
TSI1