Business Intelligence - Bases de données...

28
Système de gestion de bases de données Langage de requêtes Conception de schémas Business Intelligence - Bases de données relationnelles Olivier Schwander <[email protected]> UPMC 1 / 28

Transcript of Business Intelligence - Bases de données...

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Business Intelligence - Bases de donnesrelationnelles

    Olivier Schwander

    UPMC

    1 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    SGBD - Systmes de Gestion de Bases de DonnesServeur

    I Stocke les donnesI Reoit et interprte les requtes des clientsI Gre le contrle daccsI Gre les accs concurrents

    ClientI Application qui utilise la base de donne

    Application

    I Programme classique, contenant un clientI Bibliothques dans des langages de programmationI Fournit une interface avec la base

    2 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    Abstraction du stockage physique

    Serveur de bases de donnesI Reoit les requtes par le rseau

    Moteur de base de donnesI Stockage concret des donnesI Indexation pour acclrer les requtesI Journalisation des critures

    3 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    Langage standardis et normalis

    ThorieI Une application conue pour un SGBD doit marcher avec un

    autre

    Pratique

    I Dlicat. . .

    Pourquoi ?

    I Extensions de la syntaxeI Parties de la norme pas implantesI Performances, passage lchelle

    4 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    12 rgles de Codd

    Tentative de dfinition dun SGBD soit relationnel

    Rgle 1 - UnicitChaque information est reprsente dune seule manire.

    Rgle 2 - Accs non-ambigu(cl primaire, base/table/colonne) donne une valeur

    Rgle 3 - Traitement des valeurs manquantesValeur NULL distincte de toutes les autres valeurs (y compris deNULL)

    Rgle 7 - Insertion, mise jour, et effacement de haut niveauTraitement par lots

    5 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    12 rgles de Codd - Indpendances

    Rgle 8 - Indpendance physiqueOn peut changer la faon de stocker les donnes

    Rgle 9 - Indpendance logiqueOn peut changer la structure de la base

    Rgle 10 - Indpendance dintgritOn peut exprimer des contraintes sur les donnes indpendemmentde lapplication

    Rgle 11 - Indpendance de distributionDistribution sur plusieurs machines transparente

    6 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    Groupes doprations

    Exemple

    I tape 1 : dbiter un compte bancaireI tape 2 : crditer un autre compte

    Que se passe-t-il si le systme plante entre les tapes 1 et 2 ?

    TransactionI Marquer une suite dopration comme atomiqueI Pouvoir revenir ltape antrieur en cas de problme

    7 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    ACID

    8 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    ArchitectureRgles de CoddTransactions

    ACID

    AtomicitI Une transaction se fait compltement ou pas du tout

    CohrenceI Le systme passe toujours dun tat valide un autre

    IsolationI Indpendance entre les transactions

    DurabilitI Une transaction effectue lest de faon durable

    9 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Algbre relationnelle

    MotivationI Formaliser les oprations sur les bases de donnesI Bas sur la thorie des ensembles

    Optimisation des requtes

    I Transformer des requtes en requtes quivalentesI Choisir la plus efficace

    Loptimisation des requtes est un point critique pour laperformance dun SGBD.

    10 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Oprations ensemblistesUnion

    R S = {t : t R ou t S}

    Intersection

    R S = {t : t R et t S}

    Diffrence

    R S = {t : t R et t 6 S}

    Produit cartsien

    R S = {(r , s) : r R et s S}11 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Oprations relationnelles

    Slection

    F (R) = {r R|F (r)}

    Projection

    A(R)

    lements de R en ne considrant que les attributs lists dans A.

    Renommage

    a/b(R)

    Attribut b rebaptis en a.

    12 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Tables

    Tableaux deux dimensionsI Attributs lmentsI Une ligne nest pas forcment prsente une et une seule fois

    Cl primaire

    I Garantir lunicitI Un attribut ou plusieurs attributs naturellement uniquesI Un attribut rajout artificiellement dans ce but

    13 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Jointures

    Rfrences entre tablesI Produit cartsienI Slection

    Cl trangre

    I Attribut identifiant un lment dune autre tableI Cl primaire de lautre table

    14 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Slection et projection

    SELECT * FROM table WHERE ...;

    ProjectionSELECT a,d,e FROM table;

    FiltreSELECT * FROM table WHERE table.name = machin;

    Produit cartsienSELECT t1.a, t2.a, t2.b FROM table1 t1, table2 t2;

    15 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Jointure

    Deux oprations

    I Produit cartsienI Slection

    Personnesid (cl primaire)nomprenomageadresse_id (cl trangre)

    Adressesid (cl primaire)rueville

    SQLSELECT * FROM Personnes, AdressesWHERE Personnes.adresse_id = Adresses.id

    16 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Oprations non-ensemblistes

    TriSELECT * FROM Personnes ORDER BY nom;

    Aggrgation

    I Comptage : SELECT count(*) FROM table;I Somme SELECT sum(age) FROM personnes;I Autre : max, min, avg, fonctions statistiques

    Suppression de rponses identiques

    I SELECT DISTINCT

    17 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Algbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Contraintes

    Proprits sur les colonnes

    I Strictement positifI Pas NULLI etc

    Intgrit rfrentielle

    I Garantir que les cls trangres correspondent des lmentsexistants

    18 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Description du schma

    Bibliothque universitaire

    I Des tudiantsI Des livresI Des emprunts

    tudiantsI Empruntent des livresI 0 ou plusI Nom, prnom, numro

    unique dtudiant

    LivresI Sont empruntsI 0 ou plusI Titre, auteur, et numro

    ISBN unique

    19 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Entit-Association

    (0, n) (0, n)

    Etudiant

    Nom

    Prenom

    Numero

    Livre

    Titre

    Auteur

    ISBN

    Emprunter

    20 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Tables

    tudiantI Cl primaireI Attributs

    LivreI Cl primaireI Attributs

    Lien tudiant-LivreI Cl trangre vers tudiantI Cl trangre vers Livre

    21 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Exemple de requte

    Liste des livres emprunts par un tudiant

    SELECT etudiant.nom,livre.titreFROM etudiant,livre,emprunter_etudiant_livre emprunterWHERE etudiant.numero = emprunter.id_etudiantAND livre.isbn = emprunter.id_livreAND etudiant.numero = 123513223;

    22 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Types numriques

    http://docs.postgresql.fr/9.3/datatype.html#datatype-numeric

    boolean 1 octet tat vrai ou fauxsmallint 2 octets entier de faible tendueinteger 4 octets entier habituelbigint 8 octets grand entierdecimal variable valeur exactenumeric variable valeur exactereal 4 octets valeur inexactedouble precision 8 octets valeur inexacte

    23 / 28

    http://docs.postgresql.fr/9.3/datatype.html##datatype-numerichttp://docs.postgresql.fr/9.3/datatype.html##datatype-numeric

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Types caractres

    http://docs.postgresql.fr/9.3/datatype-character.html

    varchar(n) Longueur variable avec limitechar(n) longueur fixe, complt par des espacestext longueur variable illimite

    24 / 28

    http://docs.postgresql.fr/9.3/datatype-character.html

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Types date/heure

    http://docs.postgresql.fr/9.3/datatype-datetime.html

    timestamp 8 octets date et heure (sans fuseau horaire)timestamp with time zone 8 octets date et heure, avec fuseau horairedate 4 octets date seule (pas dheure)time 8 octets heure seule (pas de date)time with time zone 12 octets heure seule, avec fuseau horaireinterval 16 octets intervalles de temps

    I 04:05:06, 04:05:06-8I 1999-01-08 04:05:06 -8:00, January 8 04:05:06 1999

    PST

    25 / 28

    http://docs.postgresql.fr/9.3/datatype-datetime.html

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Autres types

    http://docs.postgresql.fr/9.3/datatype.html

    I Donnes binairesI Types gomtriquesI Types personnalissI IntervallesI numrations

    26 / 28

    http://docs.postgresql.fr/9.3/datatype.html

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Cration de tablesCREATE TABLE etudiant (

    numero char(8) PRIMARY KEY,nom varchar(40),prenom varchar(40),

    );

    CREATE TABLE emprunter (id_etudiant char(8),id_livre char(13),CONSTRAINT pk_emprunter

    PRIMARY KEY (id_etudiant, id_livre),CONSTRAINT fk_emprunter_etudiant

    FOREIGN KEY (id_etudiant) REFERENCES Etudiant(numero),CONSTRAINT fk_emprunter_livre

    FOREIGN KEY (id_livre) REFERENCES Livre(isbn),);

    27 / 28

  • Systme de gestion de bases de donnesLangage de requtes

    Conception de schmas

    Schmas entit-associationTypes de donnesSyntaxe

    Autres oprations

    Suppression

    I DROP

    ModificationI ALTER

    28 / 28

    Systme de gestion de bases de donnesArchitectureRgles de CoddTransactions

    Langage de requtesAlgbre relationnelleSQL - Structured Query LanguagePlus que des ensembles

    Conception de schmasSchmas entit-associationTypes de donnesSyntaxe