Base de donnes Sance 5 Algbre relationnelle introduction

Base de données Séance 5 Algèbre relationnelle : introduction Langage de manipulation de données : SELECT

ALGEBRE RELATIONNELLE 1. Introduction 2. Opérations de base (opérateurs unaires) n n Projection Restriction 3. Requête et arbre algébrique

Relation o Partie du produit cartésien d’une liste de domaines (ensembles de valeurs) d’attributs domaine de A 1 domaine de A 2 a 11 a 21 a 12. . . a 22. . . a 1 i a 2 j card(A 1)=i card(A 2)=j domaine de An An an 1 . . . an 2. . . ank card(An)=k A 1 x A 2 x. . . x An (a 11, a 21, …, an 1) (a 11, a 21, …, an 2). . . (a 11, a 21, …, ank). . . (a 11, a 22, …, an 1). . . (a 11, a 22, …, ank). . . (a 1 i, a 2 j, …, ank) card(A 1 x A 2 x. . . x An) = i x j x. . . x k

Relation R en extension (table) o Soit R la relation définie sur les ensembles A 1, A 2, …, An R A 1 x A 2 x … x An A 1 A 2 … An a 11 a 21 … an 2 a 11 a 21 … ank … … a 12 a 2 j … ank (a 11, a 21, …, ank) est un n-uplet (ou tuple)

Schéma de la relation (MLD) o Description de la relation en intension R(A 1: D 1, A 2: D 2, …, An: Dn) avec : - R: nom de la relation - A 1, A 2, …, An : nom des attributs de la relation - D 1, D 2, …, Dn : domaine des attributs

Exemple de schéma o Schéma = format de la table o Nom de la relation, liste des attributs avec domaines plus liste des clés d'une relation o Exemple n Vins(nv : INT, cru : TEXT, mill : INT, region : TEXT) Buveurs(nb : INT, nom : TEXT, prenom : TEXT) Abus(nb : INT, nv : INT, date : INT, quantite : INT) n Clés étrangères : Abus. nv référence Vins. nv Abus. nb référence Buveurs. nb

MCD Vins nv cru mill région 0, n Abus date quantité 0, n Buveurs nb nom prénom

Exemple de relation VINS Cru CHENAS TOKAY TAVEL CHABLIS ST-EMILION Mill Région 1983 1980 1986 1987 BEAUJOLAIS ALSACE RHONE BOURGOGNE BORDELAIS L'abus d'alcool est dangereux pour la santé. A consommer avec modération

Principe des langages algébriques o L’information résultat peut être mise sous forme de relation. o La relation résultat est obtenue en appliquant successivement des opérateurs binaires ou unaires sur les relations de la base ou sur des relations de travail intermédiaires.

Projection o Opération sur une relation R consistant à composer une relation résultat R 1 n en enlevant à la relation initiale R tous les attributs non mentionnés en opérandes et n en éliminant les enregistrements en double qui sont conservés une seule fois. Résultat A 1, A 2 … An Relation Résultat = PA 1, A 2. . . An(Relation)

Exemple VINS Cru VOLNAY Mill 1983 1979 CHENAS 1983 JULIENAS 1986 Région BOURGOGNE BEAUJOLAIS Pcru, région P(VINS) Cru VOLNAY Région BOURGOGNE VOLNAY BOURGOGNE CHENAS BEAUJOLAIS JULIENAS BEAUJOLAIS

Restriction o Opération sur une relation R produisant une relation résultat R 1 de même schéma, mais comportant les seuls enregistrements qui vérifient la condition précisée en argument. Résultat Ai op Valeur Résultat = s. Ai op Valeur(Relation) Relation o Critère de qualification avec op dans {=, <, ≤, >, ≥, ≠} o Combinaison de critères simples : "and", "or" et "not"

Exemple VINS Cru Mill Région 1983 1979 BOURGOGNE CHENAS 1983 JULIENAS 1986 BEAUJOLAIS VOLNAY MILL>1983 (VINS) Cru Mill JULIENAS 1986 Région BEAUJOLAIS

Tri o Opération sur une relation R produisant une relation R 1 de même schéma contenant tous les enregistrements de R réordonnancés suivant les critères en argument. Résultat Ai , Aj Résultat = Tri(Relation, Ai , Aj ) Relation o Par défaut : ordre croissant

Exemple VINS Cru Mill Région 1983 1979 BOURGOGNE CHENAS 1983 JULIENAS 1986 BEAUJOLAIS VOLNAY Tri(VINS, année , cru ) Tri(VINS) Cru Mill Région JULIENAS 1986 CHENAS 1983 BEAUJOLAIS VOLNAY BOURGOGNE 1983 1979

Arbre algébrique o Arbre représentant une question dont n les nœuds terminaux représentent les relations, n les nœuds intermédiaires des opérations de l’algèbre relationnelle, n le nœud racine le résultat d’une question, n et les arcs les flux de données entre les opérations.

Opérateurs algébriques RESTRICTION V. CRU = V PROJECTION "BEAUJOLAIS" V. NV, V. CRU TRI V. CRU, V. MILL V V

Arbre algébrique : exemple o Donner les degrés des vins de crus Morgon et de millésime 1978 n R 1 = σcru = ‘Morgon’ (VINS) R 2 = σmill = 1978 (R 1) Résultat = Pdegré(R 2) n Résultat = Pdegré (smill = 1978 and cru = ‘Morgon’ (VINS))

Arbre algébrique : exemple Résultat degré mill = 1978 cru = 'Morgon' Vins mill = 1978 and Cru = 'Morgon' Vins

SQL : le Langage de Manipulation des Données (LMD) o Le LDD permettait d’effectuer des opérations sur les structures o Le LMD permet d’effectuer des opérations sur les données o Le LMD concerne le cycle de vie des données dans une application o Le LMD implémente le modèle «CRUDE» (Create, Read, Update, Delete). 20

L’implémentation du CRUDE en SQL o Le CRUDE en SQL est constitué des quatre ordres : n n INSERT : insertion d’enregistrement(s) UPDATE : modification d’enregistrement(s) DELETE : suppression d’enregistrement(s) SELECT : accès aux enregistrement(s) 21

Extraction de données avec SELECT o L’ordre SELECT (1ère partie) n n Sur une table La clause WHERE Opérateurs et fonctions de base Tri 22

L’ordre SELECT sur une table o Récupérer les informations stockées dans la base de manière structurée o Syntaxe générale : SELECT { <col>[, <col>… ] | *} FROM <nom_table> [WHERE <expr>] 23

Forme simple du SELECT o Récupérer tous les enregistrements de la table 'ma_table' SELECT * FROM ma_table; o Equivalent algèbre relationnelle : toutes les occurrences de ‘ma_table’ 24

Projection avec un SELECT o Récupérer seulement les colonnes c 1 et c 2 : SELECT c 1, c 2 FROM ma_table; o Garde les éventuels doublons (non ensembliste) 25

Unicité avec DISTINCT o Projection en éliminant les doublons: SELECT DISTINCT c 1, c 2 FROM ma_table; o Algèbre relationnelle : PC 1, C 2 (ma_table) 26

Restriction avec la clause WHERE o Ajout d’un prédicat avec WHERE : SELECT * FROM ma_table WHERE <predicat>; o Exemple : SELECT * FROM ma_table WHERE c 1 = 600; o Algèbre relationnelle : s. C 1=600(ma_table) 27

Les formes de la clause WHERE o Opérateurs de comparaison : = , <> , <= , >= , < , > o Opérateurs logiques AND, OR, NOT o Exemple : SELECT * FROM ma_table WHERE c 1 <> 12 AND c 2 = 15; 28

Le cas particulier de la nullité o Cas particulier des champs ‘NULL’ : Il sont testés avec le prédicat IS NULL ou IS NOT NULL. o Exemple : SELECT * FROM ma_table WHERE c 2 IS NULL; 29

L’opérateur LIKE o L’opérateur LIKE permet de tester les chaînes de caractères avec l’expression d’un motif (pattern) o Les méta-caractères sont : _ : 1 seul caractère % : 0 ou plusieurs caractères 30

L’opérateur LIKE : Exemples o Toutes les personnes dont le nom commence par 'A' : SELECT * FROM personne WHERE nom LIKE 'A%' ; o Tous les prénoms dont le nom contient un 'X' en deuxième position. SELECT prenom FROM personne WHERE nom LIKE '_X%' ; 31

Autres opérateurs de caractères o Concaténation : || ou CONCAT SELECT first_name || ' ' || last_name FROM … SELECT CONCAT(first_name, ' ', last_name) FROM … o Sous-chaînes : SUBSTR(chaîne, pos, long) ATTENTION : En SQL les chaînes de caractères sont indicées à partir de 1 32

Conversions de caractères o En majuscule : UPPER(chaîne) o En minuscule : LOWER(chaîne) o En nombre : TO_NUMBER(chaîne) 33

Conversion nombre/texte o Nombre => chaîne : TO_CHAR(nb, masque) o Le masque définit des règles de transformation : o o o 9 : chiffre représenté sauf 0 non significatif 0 : chiffre représenté D : séparateur décimal apparent (, ou. ) G : séparateur de groupe (. ou , ) C : symbole ISO de monnaie locale o Exemple : TO_CHAR(1555444. 2, '999 G 999 D 99 C') => 1. 555. 444, 20 EUR 34

Conversion de date o o chaîne => date : TO_DATE(chaîne, format) date => chaîne : TO_CHAR(date, format) Le format définit les parties utilisées et leur ordre Exemple : lundi 17 novembre 2008 n Année : YYYY, YEAR o 2008, TWO THOUSAND EIGHT (non traduit) n Mois : MM, Month o 11, Novembre n Semaine : WW (année), W (mois) o 46, 3 n Jour : DDD (année), DD (mois), D (semaine), DAY, DY o 322, 17, 1, Lundi, LU 35

Conversion de date o Exemple : 14 H 53’ 45 n Heures : HH, HH 12, AM, PM o 14, 2, pm n Minutes : MM o 53 n Secondes : SS o 45 o Séparateurs n - /. , ; : espace n "a" : entre quotes pour les autres caractères n Exemple : 'HH"h"MM: SS' => 14 h 53: 45 36

Tris o SELECT. . . FROM ma_table [WHERE. . . ] ORDER BY COL 1 [ASC|DESC] [, COL 2 [ASC|DESC]* o Exemple : tri de personne par année décroissante puis par nom dans l’ordre alphabétique n SELECT * FROM PERSONNE ORDER BY ANNEE DESC, NOM o Algèbre relationnelle : ANNEE , NOM PERSONNE 37