Bases de donn©es relationnelles - SQL - M2 ISF - Actuariat

download Bases de donn©es relationnelles - SQL - M2 ISF - Actuariat

of 48

  • date post

    05-Jan-2017
  • Category

    Documents

  • view

    223
  • download

    7

Embed Size (px)

Transcript of Bases de donn©es relationnelles - SQL - M2 ISF - Actuariat

  • Bases de donnes relationnelles

    - SQL

    2017-2018

    NEGRE Elsa

    Sources : G. Gardarin, R. Grin, M. Manouvrier, T-L. Nguyen

  • SGBD

    Principaux composants : Systme de gestion de fichiers

    Gestionnaire de requtes

    Gestionnaire de transactions

    Principales fonctionnalits : Contrle de la redondance dinformation

    Partage des donnes (car plusieurs utilisateurs en mme temps)

    Gestion des autorisations daccs

    Vrifications des contraintes dintgrit

    Scurit et reprise sur panne

    2

  • Abstraction des donnes (3 niveaux)

    Niveau interne ou physique :

    plus bas niveau

    indique comment (avec quelles structures de donnes) sont stockes

    physiquement les donnes

    Niveau logique ou conceptuel : dcrit par un schma conceptuel ou logique

    indique quelles sont les donnes stockes et quelles sont leurs relations

    indpendamment de limplantation physique

    Niveau externe ou vue : propre chaque utilisateur

    dcrit par un ou plusieurs schmas externes

    3

  • SQL Structured Query Language (normalis en 1986)

    SQL2/SQL92 : standard adopt en 1992

    SQL3/SQL99 : extension de SQL2 avec "gestion" dobjets, dclencheurs

    SQL2003: auto-incrmentation des cls, colonne calcule, prise en compte

    de XML,

    SQL2008: correction de certains dfauts et manques (fonctions, types,

    curseurs)

    SQL :

    Langage de Manipulation de Donnes (DML) : interroger et modifier les

    donnes de la base

    Langage de Dfinition de Donnes (DDL) : dfinir le schma de la base

    de donnes

    Langage de contrle daccs aux donnes (DCL) : pour dfinir les

    privilges daccs des utilisateurs

    Langage de contrle des transactions (TCL) : pour grer les transactions.

    4

  • Bibliographie SQL2 - Application Oracle, Access et RDB

    Pierre DELMAL, 2me Edition, De Boeck Universit, 1998

    BU: 005.74 SQL

    SQL Pour Oracle (avec exercices corrigs)

    Christian Soutou, Eyrolles, 2005 BU: 005.72 SOU

    Initiation SQL (cours et exercices corrigs)

    Philip J. Pratt, Eyrolles,2001 BU : 005.72 SQL

    SQL (cours et exercices corrigs)

    Frdric Brouad et Christian Soutou, Coll. Synthex, Pearson Education, 2012 BU : 005.72 SQL

    Oracle PL/SQL - Prcis & concis

    Steven Feuerstein, Bill Pribyl et Chip Dawes, O Reilly, 2000

    5

  • DML

    6

  • 7

    Select : forme gnrale

    SELECT

    FROM

    [WHERE AND ]

    [GROUP BY ]

    [HAVING ]

    Restrictions

    arithmtique (=, , )

    textuelle (LIKE)

    sur intervalle (BETWEEN) ou sur liste (IN)

    Possibilit de blocs imbriqus par : IN, EXISTS, NOT EXISTS, ALL, SOME, ANY

  • 8

    Projection Syntaxe SQL :

    SELECT [UNIQUE1] liste_attributs2 FROM Table ;

    quivalent AR :

    liste_attributs R(Table)

    1 Permet d'liminer les doublons (on trouvera aussi DISTINCT) 2 On peut mettre une toile * pour demander tous les attributs

    On peut renommer un attribut en ajoutant AS NomAttribut

  • 9

    Projection - Exemples

    Soit la relation tudiants(#num, nom, prnom, ge, ville, CodePostal)

    Donner les noms, les prnoms et les ges de tous les tudiants.

    Afficher toute la relation tudiant.

    SELECT nom, prnom, age FROM tudiants;

    SELECT * FROM tudiants;

    Donner les numros des tudiants dans une colonne nomme Numro. SELECT #num AS Numro FROM tudiants;

  • 10

    Slection Syntaxe SQL :

    SELECT * FROM table WHERE condition;

    quivalent AR :

    condition R(Table)

    La condition peut forme sur des noms d'attributs ou des constantes avec

    des oprateurs de comparaison : =, >,

  • 11

    Slection Exemples

    Quels sont tous les tudiants gs de 20 ans ou plus ?

    SELECT * FROM tudiants WHERE (Age >= 20);

    Quels sont tous les tudiants gs de 19 23 ans ?

    SELECT * FROM tudiants WHERE Age IN (19, 20, 21, 22, 23);

    SELECT * FROM tudiants WHERE Age BETWEEN 19 AND 23;

    Quels sont tous les tudiants habitant dans les Vosges ?

    SELECT * FROM tudiant WHERE CodePostal LIKE '88%' ;

    Quels sont tous les tudiants dont la ville est inconnue/connue ?

    SELECT * FROM tudiants WHERE Ville IS NULL ;

    SELECT * FROM tudiants WHERE Ville IS NOT NULL ;

    Sur la relation tudiants(#Num, Nom, Prnom, Age, Ville, CodePostal)

  • Prdicats du WHERE exp1 = exp2 exp op ANY/SOME (SELECT )

    exp1 != exp2 exp op ALL (SELECT )

    exp1 > exp2 avec op tel que =, !=, ...

    exp1 < exp2

    exp1 = exp2 exp NOT IN (SELECT )

    exp1 BETWEEN exp2 AND exp3

    exp1 LIKE exp2

    exp1 IN (exp2, exp3, )

    exp1 NOT IN (exp2, exp3, )

    exp1 IS NULL

    exp1 IS NOT NULL

    12

  • Relation

    Enseignant :

    Rsultat de la slection SELECT * FROM Enseignant WHERE Grade=MCF :

    Rsultat de la projection

    SELECT Nom, Prenom FROM

    Enseignant :

    Rsultat de la requte

    SELECT Nom, Prenom

    FROM Enseignant

    WHERE Grade=MCF :

    13

    Exemples de rsultats

  • Clause EXISTS :

    Retourne VRAI si au moins un nuplet est renvoy par la requte

    FAUX si aucun nuplet nest retourn.

    La valeur NULL na aucun effet sur le boolen rsultat

    SELECT Nom, Prnom

    FROM Enseignant E

    WHERE NOT EXISTS

    ( SELECT *

    FROM Reservation_Salle S

    WHERE S.Enseignant_ID = E.Enseignant_ID

    );

    14

    Prdicats du WHERE (2)

  • 15

    Produit Cartsien Syntaxe SQL :

    SELECT *

    FROM table1 [Alias1], ..., tablen [Aliasn],

    quivalent AR :

    Table1 ... Table n

  • 16

    -Jointure Syntaxe SQL :

    SELECT *

    FROM table1 [Alias1], ..., tablen [Aliasn],

    WHERE condition;

    quivalent AR :

    Table1 ... Table n

    Autre Syntaxe :

    SELECT * FROM table1 INNER JOIN table2 ON condition;

    Possibilit de Renommage des tables

  • La relation Enseignant :

    La relation Departement :

    SELECT * FROM Enseignant e, Departement d

    WHERE e.Departement_ID=d.Departement_ID :

    17

    Exemples de Jointure

  • Nom_Employ Ville

    Tom Marseille

    Jerry Paris

    Alex Limoges

    Marthe Perpignan

    Nom_Employ Filiale Salaire

    Tom SUD_EST 10000

    Jerry IDF 25000

    Sophie IDF 15000

    Marthe SUD_OUEST 12000

    Nom_Employ Ville Filiale Salaire

    Tom Marseille SUD_EST 10000

    Jerry Paris IDF 25000

    Alex Limoges NULL NULL

    Marthe Perpignan SUD_OUEST 12000

    Nom_Employ Ville Filiale Salaire

    Tom Marseille SUD_EST 10000

    Jerry Paris IDF 25000

    Sophie NULL IDF 15000

    Marthe Perpignan SUD_OUEST 12000

    Personnel Employ

    Personnel

    ]

    Employ

    Personnel

    [

    Employ

    18

  • 19

    Jointures par requtes imbriques

    Une jointure peut aussi tre effectue l'aide d'une

    sous-requte.

    SELECT *

    FROM Stock

    WHERE #prod IN ( SELECT #prod

    FROM Produit)

    Principe : Le mot-clef "IN" permet ici de slectionner

    les tuples #prod appartenant la sous-requte.

    o La sous-requte ne doit retourner qu'une colonne !

    o Les tables de sous-requtes ne sont pas visibles depuis

    l'extrieur

    Sous-requte imbrique

    !

  • 20

    Union, Intersection et Diffrence

    Table1 Table2 : SELECT liste_attributs FROM table1

    UNION

    SELECT liste_attributs FROM table2 ;

    Table1 Table2 : SELECT liste_attributs FROM table1

    INTERSECT

    SELECT liste_attributs FROM table2 ;

    Table1 - Table2 : SELECT liste_attributs FROM table1

    EXCEPT

    SELECT liste_attributs FROM table2 ;

  • SELECT Nom, Prenom FROM Enseignant

    UNION

    SELECT Nom, Prenom FROM Etudiant :

    SELECT Nom, Prenom FROM Enseignant

    EXCEPT

    SELECT Nom, Prenom FROM Etudiant :

    21

    Union, Intersection et Diffrence -

    Exemples

  • SELECT Nom, Prenom FROM Enseignant

    INTERSECT

    SELECT Nom, Prenom FROM Etudiant

    22

  • Livre(ISBN, Titre, Editeur)

    Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID)

    Etudiant(EtudiantID,Nom, Prenom)

    Quels livres ont t emprunts par tous les tudiants?

    {t.Titre / Livre(t) [ u ( Etudiant (u) ) v ( v Emprunt(v)

    (v.Etudiant_ID=u.Etudiant_ID)

    (v.ISBN=t.ISBN )

    )

    ]

    }

    SELECT t.Titre FROM Livre t WHERE NOT EXISTS

    ( SELECT * FROM Etudiant u WHERE NOT EXISTS

    ( SELECT * FROM Emprunt v

    WHERE u.EtudiantID=v.EtudiantID AND v.ISBN=t.ISBN

    )

    ) ;

    {t.Titre / Livre(t) [ u Etudiant (u)

    ( v Emprunt(v)

    (v.Etudiant_ID=u.Etudiant_ID)

    (v.ISBN=t.ISBN )

    )

    ]

    }

    Il ny a pas de mot-cl

    "quel que soit " en SQL2

    23

    Division

  • La r