basesdedonnées licenceprofessionnelle logiciels libres · 3 modélisationetmodèles ......
TRANSCRIPT
Bases de donnéesLicence professionnelle
Logiciels Libres
Pierre DesportSupport inspiré de Frédéric Lardeux
Université d’Angers
5 septembre 2016
Bases de données – Licence pro Informatique 1
Bases de données
Cours (15h) / TD (5h) / TP (20h)
Modèle relationnel.Données (attribut, clé, etc.).Opérations (algèbre relationnelle).
Utilisation de SGBD relationnels : MySQL, PostgreSQL etOracle.
Bases de données – Licence pro Informatique 2
Bases de données
Cours (15h) / TD (5h) / TP (20h)Modèle relationnel.
Données (attribut, clé, etc.).Opérations (algèbre relationnelle).
Utilisation de SGBD relationnels : MySQL, PostgreSQL etOracle.
Bases de données – Licence pro Informatique 2
Chapitre I
Bases de données
1 Introduction2 Fonctions d’un SGBD3 Modélisation et modèles
Bases de données – Licence pro Informatique Bases de données 3
Introduction
Chapitre I
Bases de données
1 Introduction2 Fonctions d’un SGBD3 Modélisation et modèles
Bases de données – Licence pro Informatique Bases de données 4
Introduction
Introduction
La plupart des applications « réelles » manipulent de grandesquantités de données.Les langages de programmation classiques n’offrent pas de facilitéspour la gestion de données.
Défauts de la gestion des données directement dans uneapplication :
Non indépendance des données.Chaque programme utilise son format de stockage desdonnées.Indépendance physique : insensibilité des applications à deschangements matériels de la base de données. Une modification del’organisation physique des données n’implique pas de modificationdes programmesIndépendance logique : insensibilité des applications à l’ajoutd’informations à certaines classes d’objets.
Bases de données – Licence pro Informatique Bases de données 5
Introduction
Introduction
La plupart des applications « réelles » manipulent de grandesquantités de données.Les langages de programmation classiques n’offrent pas de facilitéspour la gestion de données.Défauts de la gestion des données directement dans uneapplication :
Non indépendance des données.Chaque programme utilise son format de stockage desdonnées.
Indépendance physique : insensibilité des applications à deschangements matériels de la base de données. Une modification del’organisation physique des données n’implique pas de modificationdes programmesIndépendance logique : insensibilité des applications à l’ajoutd’informations à certaines classes d’objets.
Bases de données – Licence pro Informatique Bases de données 5
Introduction
Introduction
La plupart des applications « réelles » manipulent de grandesquantités de données.Les langages de programmation classiques n’offrent pas de facilitéspour la gestion de données.Défauts de la gestion des données directement dans uneapplication :
Non indépendance des données.Chaque programme utilise son format de stockage desdonnées.Indépendance physique : insensibilité des applications à deschangements matériels de la base de données. Une modification del’organisation physique des données n’implique pas de modificationdes programmes
Indépendance logique : insensibilité des applications à l’ajoutd’informations à certaines classes d’objets.
Bases de données – Licence pro Informatique Bases de données 5
Introduction
Introduction
La plupart des applications « réelles » manipulent de grandesquantités de données.Les langages de programmation classiques n’offrent pas de facilitéspour la gestion de données.Défauts de la gestion des données directement dans uneapplication :
Non indépendance des données.Chaque programme utilise son format de stockage desdonnées.Indépendance physique : insensibilité des applications à deschangements matériels de la base de données. Une modification del’organisation physique des données n’implique pas de modificationdes programmesIndépendance logique : insensibilité des applications à l’ajoutd’informations à certaines classes d’objets.
Bases de données – Licence pro Informatique Bases de données 5
Introduction
Gestion de données dans une application
Redondance de données.Chaque logiciel utilise ses fichiers. Certaines informationsdoivent être utilisées par plusieurs logiciels : elles sontdupliquées → gestion plus complexe, mise à jour plus difficile.
Risque de problèmes d’intégrité.Les données peuvent être incohérentes.
⇒ Multiplication de routines de gestion de données peuréutilisables (coût, résultat peu satisfaisant).⇒ Pas de mise en commun des données utilisées par plusieurslogiciels (saisies multiples, risques d’incohérences).⇒ Utilisations de représentations et d’opérations peu sûres et peuefficaces.
Bases de données – Licence pro Informatique Bases de données 6
Introduction
Gestion de données dans une application
Redondance de données.Chaque logiciel utilise ses fichiers. Certaines informationsdoivent être utilisées par plusieurs logiciels : elles sontdupliquées → gestion plus complexe, mise à jour plus difficile.Risque de problèmes d’intégrité.Les données peuvent être incohérentes.
⇒ Multiplication de routines de gestion de données peuréutilisables (coût, résultat peu satisfaisant).⇒ Pas de mise en commun des données utilisées par plusieurslogiciels (saisies multiples, risques d’incohérences).⇒ Utilisations de représentations et d’opérations peu sûres et peuefficaces.
Bases de données – Licence pro Informatique Bases de données 6
Introduction
Gestion de données dans une application
Redondance de données.Chaque logiciel utilise ses fichiers. Certaines informationsdoivent être utilisées par plusieurs logiciels : elles sontdupliquées → gestion plus complexe, mise à jour plus difficile.Risque de problèmes d’intégrité.Les données peuvent être incohérentes.
⇒ Multiplication de routines de gestion de données peuréutilisables (coût, résultat peu satisfaisant).
⇒ Pas de mise en commun des données utilisées par plusieurslogiciels (saisies multiples, risques d’incohérences).⇒ Utilisations de représentations et d’opérations peu sûres et peuefficaces.
Bases de données – Licence pro Informatique Bases de données 6
Introduction
Gestion de données dans une application
Redondance de données.Chaque logiciel utilise ses fichiers. Certaines informationsdoivent être utilisées par plusieurs logiciels : elles sontdupliquées → gestion plus complexe, mise à jour plus difficile.Risque de problèmes d’intégrité.Les données peuvent être incohérentes.
⇒ Multiplication de routines de gestion de données peuréutilisables (coût, résultat peu satisfaisant).⇒ Pas de mise en commun des données utilisées par plusieurslogiciels (saisies multiples, risques d’incohérences).
⇒ Utilisations de représentations et d’opérations peu sûres et peuefficaces.
Bases de données – Licence pro Informatique Bases de données 6
Introduction
Gestion de données dans une application
Redondance de données.Chaque logiciel utilise ses fichiers. Certaines informationsdoivent être utilisées par plusieurs logiciels : elles sontdupliquées → gestion plus complexe, mise à jour plus difficile.Risque de problèmes d’intégrité.Les données peuvent être incohérentes.
⇒ Multiplication de routines de gestion de données peuréutilisables (coût, résultat peu satisfaisant).⇒ Pas de mise en commun des données utilisées par plusieurslogiciels (saisies multiples, risques d’incohérences).⇒ Utilisations de représentations et d’opérations peu sûres et peuefficaces.
Bases de données – Licence pro Informatique Bases de données 6
Introduction
Système de gestion de bases de données (SGBD)
Un ensemble de routines permettant une gestion efficace et sûre detous types de données.
Regroupement de données pour former une base de données.possibilité de formuler des requêtes faisant intervenir toutes les donnéesde la base.
AvantagesIndépendance logique et physique. Pas de redondance,Meilleure intégrité.Gestion de tous types de données adaptable à tout typed’application.Interface avec des langages hôtes.
Bases de données – Licence pro Informatique Bases de données 7
Introduction
Système de gestion de bases de données (SGBD)
Un ensemble de routines permettant une gestion efficace et sûre detous types de données.Regroupement de données pour former une base de données.possibilité de formuler des requêtes faisant intervenir toutes les donnéesde la base.
AvantagesIndépendance logique et physique. Pas de redondance,Meilleure intégrité.Gestion de tous types de données adaptable à tout typed’application.Interface avec des langages hôtes.
Bases de données – Licence pro Informatique Bases de données 7
Introduction
Système de gestion de bases de données (SGBD)
Un ensemble de routines permettant une gestion efficace et sûre detous types de données.Regroupement de données pour former une base de données.possibilité de formuler des requêtes faisant intervenir toutes les donnéesde la base.
AvantagesIndépendance logique et physique. Pas de redondance,Meilleure intégrité.
Gestion de tous types de données adaptable à tout typed’application.Interface avec des langages hôtes.
Bases de données – Licence pro Informatique Bases de données 7
Introduction
Système de gestion de bases de données (SGBD)
Un ensemble de routines permettant une gestion efficace et sûre detous types de données.Regroupement de données pour former une base de données.possibilité de formuler des requêtes faisant intervenir toutes les donnéesde la base.
AvantagesIndépendance logique et physique. Pas de redondance,Meilleure intégrité.Gestion de tous types de données adaptable à tout typed’application.
Interface avec des langages hôtes.
Bases de données – Licence pro Informatique Bases de données 7
Introduction
Système de gestion de bases de données (SGBD)
Un ensemble de routines permettant une gestion efficace et sûre detous types de données.Regroupement de données pour former une base de données.possibilité de formuler des requêtes faisant intervenir toutes les donnéesde la base.
AvantagesIndépendance logique et physique. Pas de redondance,Meilleure intégrité.Gestion de tous types de données adaptable à tout typed’application.Interface avec des langages hôtes.
Bases de données – Licence pro Informatique Bases de données 7
Fonctions d’un SGBD
Chapitre I
Bases de données
1 Introduction2 Fonctions d’un SGBD3 Modélisation et modèles
Bases de données – Licence pro Informatique Bases de données 8
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).
Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.
Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.
LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).
Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.
Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).
Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).
Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Fonctions d’un SGBD
Gérer de grandes quantités de données persistantes (HDD,Serveur . . . ).Accéder efficacement à ces données.Supporter un modèle de données abstrait qui est la façon dontl’utilisateur perçoit les données.LDD : Langage de définition des données.LMD : Langage de manipulation de données (de requêtes).Permettre un accès concurrent aux données.Vérifier les accès des utilisateurs (sécurité).Conserver la validité des données (intégrité).Récupérer les données après panne.
Bases de données – Licence pro Informatique Bases de données 9
Fonctions d’un SGBD
Langage de définition des données
Description de la nature des données et de leurs liens logiques.Permet la définition du schéma conceptuel.Description des schémas externes.
Permet d’exprimer des contraintes d’intégrité sur les données.Valeurs permises pour certaines données.ex : prix_vente > prix_achat.Définition de clés.Définition des droits d’accès aux données.. . .
Bases de données – Licence pro Informatique Bases de données 10
Fonctions d’un SGBD
Langage de définition des données
Description de la nature des données et de leurs liens logiques.Permet la définition du schéma conceptuel.Description des schémas externes.
Permet d’exprimer des contraintes d’intégrité sur les données.Valeurs permises pour certaines données.ex : prix_vente > prix_achat.
Définition de clés.Définition des droits d’accès aux données.. . .
Bases de données – Licence pro Informatique Bases de données 10
Fonctions d’un SGBD
Langage de définition des données
Description de la nature des données et de leurs liens logiques.Permet la définition du schéma conceptuel.Description des schémas externes.
Permet d’exprimer des contraintes d’intégrité sur les données.Valeurs permises pour certaines données.ex : prix_vente > prix_achat.Définition de clés.Définition des droits d’accès aux données.. . .
Bases de données – Licence pro Informatique Bases de données 10
Fonctions d’un SGBD
Langage de manipulation de données
Manipulation, modification, ajout, suppression des données.
SGBD avec langage hôteLe LMD n’est pas un langage en soi, mais est une« sur-couche » d’un langage de programmation tel que JAVA.
SGBD autonomeInterpréteur (et/ou compilateur) fourni avec le SGBD quiexécute des ordres rédigés dans un langage propre au SGBD(ex : SQL).
Bases de données – Licence pro Informatique Bases de données 11
Fonctions d’un SGBD
Langage de manipulation de données
Manipulation, modification, ajout, suppression des données.
SGBD avec langage hôteLe LMD n’est pas un langage en soi, mais est une« sur-couche » d’un langage de programmation tel que JAVA.SGBD autonomeInterpréteur (et/ou compilateur) fourni avec le SGBD quiexécute des ordres rédigés dans un langage propre au SGBD(ex : SQL).
Bases de données – Licence pro Informatique Bases de données 11
Fonctions d’un SGBD
Module de contrôle d’accès aux données
Vérification des droits (lecture, modification, ajout)
Gestion des accès concurrents.Risque d’une perte d’intégrité.Techniques de verrouillage des données.
SGBD et SEHabituellement un SGBD est construit au dessus d’un SE, etréutilise son système de gestion de fichiers (même si l’utilisateur duSGBD ne verra pas les fichiers), sa gestion de la mémoire, desexclusions mutuelles.
Bases de données – Licence pro Informatique Bases de données 12
Fonctions d’un SGBD
Module de contrôle d’accès aux données
Vérification des droits (lecture, modification, ajout)Gestion des accès concurrents.Risque d’une perte d’intégrité.Techniques de verrouillage des données.
SGBD et SEHabituellement un SGBD est construit au dessus d’un SE, etréutilise son système de gestion de fichiers (même si l’utilisateur duSGBD ne verra pas les fichiers), sa gestion de la mémoire, desexclusions mutuelles.
Bases de données – Licence pro Informatique Bases de données 12
Fonctions d’un SGBD
Module de contrôle d’accès aux données
Vérification des droits (lecture, modification, ajout)Gestion des accès concurrents.Risque d’une perte d’intégrité.Techniques de verrouillage des données.
SGBD et SEHabituellement un SGBD est construit au dessus d’un SE, etréutilise son système de gestion de fichiers (même si l’utilisateur duSGBD ne verra pas les fichiers), sa gestion de la mémoire, desexclusions mutuelles.
Bases de données – Licence pro Informatique Bases de données 12
Fonctions d’un SGBD
Modules d’administration
Initialisation de la base (à vide, par importation d’une baseexistante).
Copie / Sauvegarde / Restauration.Gestion des journaux d’historique.
Identification des programmes, utilisateurs, postes.Charge du serveur.. . .
Réorganisation de la base (niveau physique).. . .
Bases de données – Licence pro Informatique Bases de données 13
Fonctions d’un SGBD
Modules d’administration
Initialisation de la base (à vide, par importation d’une baseexistante).Copie / Sauvegarde / Restauration.
Gestion des journaux d’historique.Identification des programmes, utilisateurs, postes.Charge du serveur.. . .
Réorganisation de la base (niveau physique).. . .
Bases de données – Licence pro Informatique Bases de données 13
Fonctions d’un SGBD
Modules d’administration
Initialisation de la base (à vide, par importation d’une baseexistante).Copie / Sauvegarde / Restauration.Gestion des journaux d’historique.
Identification des programmes, utilisateurs, postes.Charge du serveur.. . .
Réorganisation de la base (niveau physique).. . .
Bases de données – Licence pro Informatique Bases de données 13
Fonctions d’un SGBD
Modules d’administration
Initialisation de la base (à vide, par importation d’une baseexistante).Copie / Sauvegarde / Restauration.Gestion des journaux d’historique.
Identification des programmes, utilisateurs, postes.Charge du serveur.. . .
Réorganisation de la base (niveau physique).. . .
Bases de données – Licence pro Informatique Bases de données 13
Modélisation et modèles
Chapitre I
Bases de données
1 Introduction2 Fonctions d’un SGBD3 Modélisation et modèles
Bases de données – Licence pro Informatique Bases de données 14
Modélisation et modèles
Modélisation et modèles
Définition (Modelisation)Expression du monde réel observé utilisant les concepts d’unmodèle de représentation.
Représentation de la partie du monde « utile » à l’application.Par exemple, la modélisation d’une « personne » sera différentedans une application gérant l’inscription d’étudiants à desdiplômes, et dans la BD de la sécurité sociale.Les données choisies pour la modélisation (d’une personne)différent.Il existe plusieurs modèles de représentation.
1960 Modèle hiérarchique, modèle réseau.1970 Modèle relationnel1990 Modèle objet
Bases de données – Licence pro Informatique Bases de données 15
Modélisation et modèles
Modélisation et modèles
Définition (Modelisation)Expression du monde réel observé utilisant les concepts d’unmodèle de représentation.
Représentation de la partie du monde « utile » à l’application.
Par exemple, la modélisation d’une « personne » sera différentedans une application gérant l’inscription d’étudiants à desdiplômes, et dans la BD de la sécurité sociale.Les données choisies pour la modélisation (d’une personne)différent.Il existe plusieurs modèles de représentation.
1960 Modèle hiérarchique, modèle réseau.1970 Modèle relationnel1990 Modèle objet
Bases de données – Licence pro Informatique Bases de données 15
Modélisation et modèles
Modélisation et modèles
Définition (Modelisation)Expression du monde réel observé utilisant les concepts d’unmodèle de représentation.
Représentation de la partie du monde « utile » à l’application.Par exemple, la modélisation d’une « personne » sera différentedans une application gérant l’inscription d’étudiants à desdiplômes, et dans la BD de la sécurité sociale.Les données choisies pour la modélisation (d’une personne)différent.
Il existe plusieurs modèles de représentation.1960 Modèle hiérarchique, modèle réseau.1970 Modèle relationnel1990 Modèle objet
Bases de données – Licence pro Informatique Bases de données 15
Modélisation et modèles
Modélisation et modèles
Définition (Modelisation)Expression du monde réel observé utilisant les concepts d’unmodèle de représentation.
Représentation de la partie du monde « utile » à l’application.Par exemple, la modélisation d’une « personne » sera différentedans une application gérant l’inscription d’étudiants à desdiplômes, et dans la BD de la sécurité sociale.Les données choisies pour la modélisation (d’une personne)différent.Il existe plusieurs modèles de représentation.
1960 Modèle hiérarchique, modèle réseau.1970 Modèle relationnel1990 Modèle objet
Bases de données – Licence pro Informatique Bases de données 15
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Bases de données – Licence pro Informatique Modèle relationnel 16
Modèle relationnel
Fondé sur le concept mathématique de relation (sous-ensemble duproduit cartésien de différents domaines).
Le modèle le plus utilisé dans les bases de données (SGBDR) :fondements mathématiques.simple à comprendre.muni de langages de manipulation.homogène : représentation de toutes les informations sous unemême forme.
Bases de données – Licence pro Informatique Modèle relationnel 17
Modèle relationnel
Fondé sur le concept mathématique de relation (sous-ensemble duproduit cartésien de différents domaines).
Le modèle le plus utilisé dans les bases de données (SGBDR) :fondements mathématiques.
simple à comprendre.muni de langages de manipulation.homogène : représentation de toutes les informations sous unemême forme.
Bases de données – Licence pro Informatique Modèle relationnel 17
Modèle relationnel
Fondé sur le concept mathématique de relation (sous-ensemble duproduit cartésien de différents domaines).
Le modèle le plus utilisé dans les bases de données (SGBDR) :fondements mathématiques.simple à comprendre.
muni de langages de manipulation.homogène : représentation de toutes les informations sous unemême forme.
Bases de données – Licence pro Informatique Modèle relationnel 17
Modèle relationnel
Fondé sur le concept mathématique de relation (sous-ensemble duproduit cartésien de différents domaines).
Le modèle le plus utilisé dans les bases de données (SGBDR) :fondements mathématiques.simple à comprendre.muni de langages de manipulation.
homogène : représentation de toutes les informations sous unemême forme.
Bases de données – Licence pro Informatique Modèle relationnel 17
Modèle relationnel
Fondé sur le concept mathématique de relation (sous-ensemble duproduit cartésien de différents domaines).
Le modèle le plus utilisé dans les bases de données (SGBDR) :fondements mathématiques.simple à comprendre.muni de langages de manipulation.homogène : représentation de toutes les informations sous unemême forme.
Bases de données – Licence pro Informatique Modèle relationnel 17
Données
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Bases de données – Licence pro Informatique Modèle relationnel 18
Données
Données
Définition (Domaine)D : Ensemble d’éléments de même type.
Exemple (Domaine)D1 = {RedHat Linux 9.0,Windows 2000,Windows xp}D2 = {RedHat,Microsoft corp}D3 = ND4 = Dates
Bases de données – Licence pro Informatique Modèle relationnel 19
Données
Données
Définition (Domaine)D : Ensemble d’éléments de même type.
Exemple (Domaine)D1 = {RedHat Linux 9.0,Windows 2000,Windows xp}D2 = {RedHat,Microsoft corp}D3 = ND4 = Dates
Bases de données – Licence pro Informatique Modèle relationnel 19
Données
Données
Définition (Relation)R : sous-ensemble du produit cartésien de n domaines Di (nonnécessairement distincts).R ⊆ D1 × D2 × . . .Dn.
Exemple (Relation)Systèmes ⊆ D1 × D2 × D3 × D4
Relation n-aire ou d’arité n ou de degré n.Systèmes est d’arité 4.
Bases de données – Licence pro Informatique Modèle relationnel 20
Données
Données
Définition (Relation)R : sous-ensemble du produit cartésien de n domaines Di (nonnécessairement distincts).R ⊆ D1 × D2 × . . .Dn.
Exemple (Relation)Systèmes ⊆ D1 × D2 × D3 × D4
Relation n-aire ou d’arité n ou de degré n.Systèmes est d’arité 4.
Bases de données – Licence pro Informatique Modèle relationnel 20
Données
Données
Définition (Relation)R : sous-ensemble du produit cartésien de n domaines Di (nonnécessairement distincts).R ⊆ D1 × D2 × . . .Dn.
Exemple (Relation)Systèmes ⊆ D1 × D2 × D3 × D4
Relation n-aire ou d’arité n ou de degré n.Systèmes est d’arité 4.
Bases de données – Licence pro Informatique Modèle relationnel 20
Données
Données
Définition (Attribut)nom donné à une composante de la relation.indispensable si un même domaine est utilisé plusieurs fois.
Exemple (Schéma conceptuel)Systèmes(version, éditeur, prix, date_install)Systèmes ⊆ D1 × D2 × D3 × D4Systèmes(version, éditeur, prix, date_install)
Définition (Tuple (ou n-uplet))élément de la relation. t ∈ R
Exemple (Tuple)(d1, d2, . . . dn) ∈ R.t1 = (RedHat Linux 9.0, RedHat, 0, 23/10/2005)
Bases de données – Licence pro Informatique Modèle relationnel 21
Données
Données
Définition (Attribut)nom donné à une composante de la relation.indispensable si un même domaine est utilisé plusieurs fois.
Exemple (Schéma conceptuel)Systèmes(version, éditeur, prix, date_install)Systèmes ⊆ D1 × D2 × D3 × D4Systèmes(version, éditeur, prix, date_install)
Définition (Tuple (ou n-uplet))élément de la relation. t ∈ R
Exemple (Tuple)(d1, d2, . . . dn) ∈ R.t1 = (RedHat Linux 9.0, RedHat, 0, 23/10/2005)
Bases de données – Licence pro Informatique Modèle relationnel 21
Données
Données
Définition (Attribut)nom donné à une composante de la relation.indispensable si un même domaine est utilisé plusieurs fois.
Exemple (Schéma conceptuel)Systèmes(version, éditeur, prix, date_install)Systèmes ⊆ D1 × D2 × D3 × D4Systèmes(version, éditeur, prix, date_install)
Définition (Tuple (ou n-uplet))élément de la relation. t ∈ R
Exemple (Tuple)(d1, d2, . . . dn) ∈ R.t1 = (RedHat Linux 9.0, RedHat, 0, 23/10/2005)
Bases de données – Licence pro Informatique Modèle relationnel 21
Données
Données
Définition (Attribut)nom donné à une composante de la relation.indispensable si un même domaine est utilisé plusieurs fois.
Exemple (Schéma conceptuel)Systèmes(version, éditeur, prix, date_install)Systèmes ⊆ D1 × D2 × D3 × D4Systèmes(version, éditeur, prix, date_install)
Définition (Tuple (ou n-uplet))élément de la relation. t ∈ R
Exemple (Tuple)(d1, d2, . . . dn) ∈ R.t1 = (RedHat Linux 9.0, RedHat, 0, 23/10/2005)
Bases de données – Licence pro Informatique Modèle relationnel 21
Données
Données
NotationSoit A un attribut d’une relation R et t un tuple de R on note t.Ala valeur de l’attribut A du tuple t.t1.éditeur = RedHat
Soit R une relation, a(R) est l’ensemble des attributs de R.a(Systèmes) = {version, éditeur, prix, date_install}
Bases de données – Licence pro Informatique Modèle relationnel 22
Données
Données
NotationSoit A un attribut d’une relation R et t un tuple de R on note t.Ala valeur de l’attribut A du tuple t.t1.éditeur = RedHatSoit R une relation, a(R) est l’ensemble des attributs de R.a(Systèmes) = {version, éditeur, prix, date_install}
Bases de données – Licence pro Informatique Modèle relationnel 22
Données
Données
Définition (Cardinalité d’une relation)nombre de tuples de la relation.
Définition (Instance du schéma)Un ensemble de tuples. (définition extensionnelle de la relation)
Exemple (Instance d’un schéma)version éditeur prix date_installRedHat Linux 9.0 RedHat 0 23/10/2005Windows 2000 Microsoft corp 350 10/05/2005
Bases de données – Licence pro Informatique Modèle relationnel 23
Données
Données
Définition (Cardinalité d’une relation)nombre de tuples de la relation.
Définition (Instance du schéma)Un ensemble de tuples. (définition extensionnelle de la relation)
Exemple (Instance d’un schéma)version éditeur prix date_installRedHat Linux 9.0 RedHat 0 23/10/2005Windows 2000 Microsoft corp 350 10/05/2005
Bases de données – Licence pro Informatique Modèle relationnel 23
Données
Clé
Il est souvent nécessaire de faire référence dans une relation à destuples d’une autre relation.
Définition (Clé Primaire)attribut ou ensemble d’attributs dont les valeurs identifient demanière unique chaque tuple de la relation.
Exempleversion est la clé primaire de Systèmes.
Définition (Clé Étrangère)Les clés étrangères ont pour fonction principale la vérification del’intégrité de votre base. Elles font références à une clé primaired’une autre relation.
Bases de données – Licence pro Informatique Modèle relationnel 24
Données
Clé
Il est souvent nécessaire de faire référence dans une relation à destuples d’une autre relation.
Définition (Clé Primaire)attribut ou ensemble d’attributs dont les valeurs identifient demanière unique chaque tuple de la relation.
Exempleversion est la clé primaire de Systèmes.
Définition (Clé Étrangère)Les clés étrangères ont pour fonction principale la vérification del’intégrité de votre base. Elles font références à une clé primaired’une autre relation.
Bases de données – Licence pro Informatique Modèle relationnel 24
Données
Clé
Il est souvent nécessaire de faire référence dans une relation à destuples d’une autre relation.
Définition (Clé Primaire)attribut ou ensemble d’attributs dont les valeurs identifient demanière unique chaque tuple de la relation.
Exempleversion est la clé primaire de Systèmes.
Définition (Clé Étrangère)Les clés étrangères ont pour fonction principale la vérification del’intégrité de votre base. Elles font références à une clé primaired’une autre relation.
Bases de données – Licence pro Informatique Modèle relationnel 24
Données
Exemple
ExempleReprésentation de commandes effectuées par des clients, deproduits achetés chez des fournisseurs.
Fournisseurs(f_nom, adresse)Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom)Clients(cli_nom, adresse)Contenu_commandes(cmd_numéro, p_nom, quantité)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}Contenu_commandes = {(23, 500N, 2), (23, Cartouche HP500, 1)}
Bases de données – Licence pro Informatique Modèle relationnel 25
Données
Exemple
ExempleReprésentation de commandes effectuées par des clients, deproduits achetés chez des fournisseurs.
Fournisseurs(f_nom, adresse)Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom)Clients(cli_nom, adresse)Contenu_commandes(cmd_numéro, p_nom, quantité)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}Contenu_commandes = {(23, 500N, 2), (23, Cartouche HP500, 1)}
Bases de données – Licence pro Informatique Modèle relationnel 25
Données
Exemple
ExempleReprésentation de commandes effectuées par des clients, deproduits achetés chez des fournisseurs.
Fournisseurs(f_nom, adresse)Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom)Clients(cli_nom, adresse)Contenu_commandes(cmd_numéro, p_nom, quantité)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}Contenu_commandes = {(23, 500N, 2), (23, Cartouche HP500, 1)}
Bases de données – Licence pro Informatique Modèle relationnel 25
Langages de manipulation
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Bases de données – Licence pro Informatique Modèle relationnel 26
Langages de manipulation
Langages de manipulation
Le modèle relationnel ne définit pas qu’une méthode dereprésentation, mais aussi des langages permettant la manipulationdes relations.
Ces langages ne sont pas procéduraux, et sont plus simplesd’utilisation que les langages de programmation. L’utilisateur n’apas à exprimer comment les résultats doivent être obtenus(parcours des relations, etc.), il lui suffit d’indiquer le résultat quil’intéresse (quoi).
Langage algébrique : combinaison de relations en utilisantdes opérateurs. Les relations sont considérées comme desensembles de tuples → opérations ensemblistes.Langage logique : spécification de relations par des formuleslogiques.
Bases de données – Licence pro Informatique Modèle relationnel 27
Langages de manipulation
Langages de manipulation
Le modèle relationnel ne définit pas qu’une méthode dereprésentation, mais aussi des langages permettant la manipulationdes relations.Ces langages ne sont pas procéduraux, et sont plus simplesd’utilisation que les langages de programmation. L’utilisateur n’apas à exprimer comment les résultats doivent être obtenus(parcours des relations, etc.), il lui suffit d’indiquer le résultat quil’intéresse (quoi).
Langage algébrique : combinaison de relations en utilisantdes opérateurs. Les relations sont considérées comme desensembles de tuples → opérations ensemblistes.Langage logique : spécification de relations par des formuleslogiques.
Bases de données – Licence pro Informatique Modèle relationnel 27
Langages de manipulation
Langages de manipulation
Le modèle relationnel ne définit pas qu’une méthode dereprésentation, mais aussi des langages permettant la manipulationdes relations.Ces langages ne sont pas procéduraux, et sont plus simplesd’utilisation que les langages de programmation. L’utilisateur n’apas à exprimer comment les résultats doivent être obtenus(parcours des relations, etc.), il lui suffit d’indiquer le résultat quil’intéresse (quoi).
Langage algébrique : combinaison de relations en utilisantdes opérateurs. Les relations sont considérées comme desensembles de tuples → opérations ensemblistes.
Langage logique : spécification de relations par des formuleslogiques.
Bases de données – Licence pro Informatique Modèle relationnel 27
Langages de manipulation
Langages de manipulation
Le modèle relationnel ne définit pas qu’une méthode dereprésentation, mais aussi des langages permettant la manipulationdes relations.Ces langages ne sont pas procéduraux, et sont plus simplesd’utilisation que les langages de programmation. L’utilisateur n’apas à exprimer comment les résultats doivent être obtenus(parcours des relations, etc.), il lui suffit d’indiquer le résultat quil’intéresse (quoi).
Langage algébrique : combinaison de relations en utilisantdes opérateurs. Les relations sont considérées comme desensembles de tuples → opérations ensemblistes.Langage logique : spécification de relations par des formuleslogiques.
Bases de données – Licence pro Informatique Modèle relationnel 27
Algèbre relationnelle
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Opérations de baseAutres opérationsL’algèbre relationnelle comme langage de requêtes
Bases de données – Licence pro Informatique Modèle relationnel 28
Algèbre relationnelle Opérations de base
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Opérations de baseAutres opérationsL’algèbre relationnelle comme langage de requêtes
Bases de données – Licence pro Informatique Modèle relationnel 28
Algèbre relationnelle Opérations de base
Algèbre relationnelleOpérations de base
5 opérateurs primitifs à partir desquels les autres peuvent êtredérivés.
Définition (Union et Différence)R et S sont définies sur le même ensemble d’attributsA1,A2, . . .An.U = R ∪ S = {t ∈ A1 × A2 × . . .× An/t ∈ R ou t ∈ S}D = R − S = {t ∈ A1 × A2 × . . .× An/t ∈ R et t /∈ S}
Définition (Produit cartésien)R est définie sur l’ensemble d’attributs A1,A2, . . .An. S est définiesur l’ensemble d’attributs B1,B2, . . .Bm.P = R × S = {(a1, . . . , an, b1, . . . , bm) ∈A1× . . .×An×B1× . . .×Bm/(a1, . . . , an) ∈ R et (b1, . . . , bm) ∈ S}
Bases de données – Licence pro Informatique Modèle relationnel 29
Algèbre relationnelle Opérations de base
Algèbre relationnelleOpérations de base
5 opérateurs primitifs à partir desquels les autres peuvent êtredérivés.
Définition (Union et Différence)R et S sont définies sur le même ensemble d’attributsA1,A2, . . .An.U = R ∪ S = {t ∈ A1 × A2 × . . .× An/t ∈ R ou t ∈ S}D = R − S = {t ∈ A1 × A2 × . . .× An/t ∈ R et t /∈ S}
Définition (Produit cartésien)R est définie sur l’ensemble d’attributs A1,A2, . . .An. S est définiesur l’ensemble d’attributs B1,B2, . . .Bm.P = R × S = {(a1, . . . , an, b1, . . . , bm) ∈A1× . . .×An×B1× . . .×Bm/(a1, . . . , an) ∈ R et (b1, . . . , bm) ∈ S}
Bases de données – Licence pro Informatique Modèle relationnel 29
Algèbre relationnelle Opérations de base
Algèbre relationnelleOpérations de base
5 opérateurs primitifs à partir desquels les autres peuvent êtredérivés.
Définition (Union et Différence)R et S sont définies sur le même ensemble d’attributsA1,A2, . . .An.U = R ∪ S = {t ∈ A1 × A2 × . . .× An/t ∈ R ou t ∈ S}D = R − S = {t ∈ A1 × A2 × . . .× An/t ∈ R et t /∈ S}
Définition (Produit cartésien)R est définie sur l’ensemble d’attributs A1,A2, . . .An. S est définiesur l’ensemble d’attributs B1,B2, . . .Bm.P = R × S = {(a1, . . . , an, b1, . . . , bm) ∈A1× . . .×An×B1× . . .×Bm/(a1, . . . , an) ∈ R et (b1, . . . , bm) ∈ S}
Bases de données – Licence pro Informatique Modèle relationnel 29
Algèbre relationnelle Opérations de base
Définition (Projection (sur un ensemble d’attributs))R est définie sur l’ensemble d’attributs A1,A2, . . .An.ΠAi1 ...Aim (R) = {t ∈ Ai1 × . . .Aim/∃t ′ ∈ R avec t ′.Ai1 =t.Ai1 et . . . et t ′.Aim = t.Aim}
« découpage vertical », « suppression de certaines colonnes ».
ExempleEDT ΠSalle(EDT)Salle Matière JourA116 BD LundiA115 Objets LundiH007 BD MardiA116 Réseaux Mardi
SalleA116A115H007
Bases de données – Licence pro Informatique Modèle relationnel 30
Algèbre relationnelle Opérations de base
Définition (Projection (sur un ensemble d’attributs))R est définie sur l’ensemble d’attributs A1,A2, . . .An.ΠAi1 ...Aim (R) = {t ∈ Ai1 × . . .Aim/∃t ′ ∈ R avec t ′.Ai1 =t.Ai1 et . . . et t ′.Aim = t.Aim}
« découpage vertical », « suppression de certaines colonnes ».
ExempleEDT ΠSalle(EDT)Salle Matière JourA116 BD LundiA115 Objets LundiH007 BD MardiA116 Réseaux Mardi
SalleA116A115H007
Bases de données – Licence pro Informatique Modèle relationnel 30
Algèbre relationnelle Opérations de base
Définition (Projection (sur un ensemble d’attributs))R est définie sur l’ensemble d’attributs A1,A2, . . .An.ΠAi1 ...Aim (R) = {t ∈ Ai1 × . . .Aim/∃t ′ ∈ R avec t ′.Ai1 =t.Ai1 et . . . et t ′.Aim = t.Aim}
« découpage vertical », « suppression de certaines colonnes ».
ExempleEDT ΠSalle(EDT)Salle Matière JourA116 BD LundiA115 Objets LundiH007 BD MardiA116 Réseaux Mardi
SalleA116A115H007
Bases de données – Licence pro Informatique Modèle relationnel 30
Algèbre relationnelle Opérations de base
Définition (Sélection)Soit C une formule logique utilisant des attributs de R.σC (R) = {t ∈ R/C(t) = vrai}
« découpage horizontal », Caractérisation d’un sous-ensemble de larelation. Possibilité d’utiliser des opérateurs selon le type desattributs.
ExempleσMatière = BD(EDT) σ(Salle = A116)∧(Jour = Lundi)(EDT)
Salle Matière JourA116 BD LundiH007 BD Mardi
Salle Matière JourA116 BD Lundi
Bases de données – Licence pro Informatique Modèle relationnel 31
Algèbre relationnelle Opérations de base
Définition (Sélection)Soit C une formule logique utilisant des attributs de R.σC (R) = {t ∈ R/C(t) = vrai}
« découpage horizontal », Caractérisation d’un sous-ensemble de larelation. Possibilité d’utiliser des opérateurs selon le type desattributs.ExempleσMatière = BD(EDT) σ(Salle = A116)∧(Jour = Lundi)(EDT)
Salle Matière JourA116 BD LundiH007 BD Mardi
Salle Matière JourA116 BD Lundi
Bases de données – Licence pro Informatique Modèle relationnel 31
Algèbre relationnelle Autres opérations
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Opérations de baseAutres opérationsL’algèbre relationnelle comme langage de requêtes
Bases de données – Licence pro Informatique Modèle relationnel 31
Algèbre relationnelle Autres opérations
Autres opérations
Trois nouveaux opérateurs qui peuvent être définis à partir des 5opérateurs de base.
Définition (Intersection)R et S sont définies sur le même ensemble d’attributsA1,A2, . . .An.I = R ∩ S = {t ∈ A1 × A2 × . . .× An/t ∈ R et t ∈ S}
R ∩ S = R − (R − S)
Bases de données – Licence pro Informatique Modèle relationnel 32
Algèbre relationnelle Autres opérations
Autres opérations
Trois nouveaux opérateurs qui peuvent être définis à partir des 5opérateurs de base.
Définition (Intersection)R et S sont définies sur le même ensemble d’attributsA1,A2, . . .An.I = R ∩ S = {t ∈ A1 × A2 × . . .× An/t ∈ R et t ∈ S}
R ∩ S = R − (R − S)
Bases de données – Licence pro Informatique Modèle relationnel 32
Algèbre relationnelle Autres opérations
Jointure
Définition (Jointure)Soit R et S deux relations définies sur des ensembles d’attributs àl’intersection non vide a(R) ∩ a(S) = {A1 . . .An}.
R on S est une relation définie sur l’union des attributs de R et Scontenant tous les tuples composés d’un tuple de R et un tuple deS qui coïncident sur A1 . . .An.
Expression de on à partir de Π, σ et ×Soit a(R) ∩ a(S) = {A1, . . .An}.a(R) = {A1, . . .An,B1, . . .Bk} et a(S) = {A1, . . .An,C1, . . .Cl}.
R on S =∏
A1,...,An,B1,...,Bk ,C1,...,Cl
(σ∧i∈[1,n](R.Ai=S.Ai )
(R × S))
Bases de données – Licence pro Informatique Modèle relationnel 33
Algèbre relationnelle Autres opérations
Jointure
Définition (Jointure)Soit R et S deux relations définies sur des ensembles d’attributs àl’intersection non vide a(R) ∩ a(S) = {A1 . . .An}.R on S est une relation définie sur l’union des attributs de R et Scontenant tous les tuples composés d’un tuple de R et un tuple deS qui coïncident sur A1 . . .An.
Expression de on à partir de Π, σ et ×Soit a(R) ∩ a(S) = {A1, . . .An}.a(R) = {A1, . . .An,B1, . . .Bk} et a(S) = {A1, . . .An,C1, . . .Cl}.
R on S =∏
A1,...,An,B1,...,Bk ,C1,...,Cl
(σ∧i∈[1,n](R.Ai=S.Ai )
(R × S))
Bases de données – Licence pro Informatique Modèle relationnel 33
Algèbre relationnelle Autres opérations
Jointure
Définition (Jointure)Soit R et S deux relations définies sur des ensembles d’attributs àl’intersection non vide a(R) ∩ a(S) = {A1 . . .An}.R on S est une relation définie sur l’union des attributs de R et Scontenant tous les tuples composés d’un tuple de R et un tuple deS qui coïncident sur A1 . . .An.
Expression de on à partir de Π, σ et ×Soit a(R) ∩ a(S) = {A1, . . .An}.a(R) = {A1, . . .An,B1, . . .Bk} et a(S) = {A1, . . .An,C1, . . .Cl}.
R on S =∏
A1,...,An,B1,...,Bk ,C1,...,Cl
(σ∧i∈[1,n](R.Ai=S.Ai )
(R × S))
Bases de données – Licence pro Informatique Modèle relationnel 33
Algèbre relationnelle Autres opérations
Jointure
Définition (Jointure)Soit R et S deux relations définies sur des ensembles d’attributs àl’intersection non vide a(R) ∩ a(S) = {A1 . . .An}.R on S est une relation définie sur l’union des attributs de R et Scontenant tous les tuples composés d’un tuple de R et un tuple deS qui coïncident sur A1 . . .An.
Expression de on à partir de Π, σ et ×Soit a(R) ∩ a(S) = {A1, . . .An}.a(R) = {A1, . . .An,B1, . . .Bk} et a(S) = {A1, . . .An,C1, . . .Cl}.
R on S =∏
A1,...,An,B1,...,Bk ,C1,...,Cl
(σ∧i∈[1,n](R.Ai=S.Ai )
(R × S))
Bases de données – Licence pro Informatique Modèle relationnel 33
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients
(cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)
Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients =
{(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}
Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs
(p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)
Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs =
{(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Exemple (Jointure)Fournisseurs(f_nom, adresse) Produits(p_nom, f_nom, prix)Commandes(cmd_numéro, date, cli_nom) Clients(cli_nom, adresse)
Fournisseurs = {(Canson, 1 rue de la Paix), (Pelikan, 3 boulevard Foch)}Produits = {(500N, Canson, 12), (Cartouche HP500, Pelikan, 20), (1000C,Canson, 25)}Commandes = {(23, 21/01/2002, Rudoux)}Clients = {(Rudoux, Place de l’horloge), (Loucondre, Rue des directions)}
Commandes on Clients (cmd_numéro, date, cli_nom, adresse)Commandes on Clients = {(23, 21/01/2002, Rudoux, Place de l’horloge)}Produits on Fournisseurs (p_nom, f_nom, prix, adresse)Produits on Fournisseurs = {(500N, Canson, 12, 1 rue de la Paix), (CartoucheHP500, Pelikan, 20, 3 boulevard Foch), (1000C, Canson, 25, 1 rue de la Paix)}
Bases de données – Licence pro Informatique Modèle relationnel 34
Algèbre relationnelle Autres opérations
Quotient
Définition (Quotient)Soit R et S deux relations telles que a(S) ⊆ a(R).a(S) = {A1, . . . ,An} et a(R) = a(S) ∪ {B1, . . . ,Bp}.
Q = R ÷ S est la relation définie sur {B1, . . . ,Bp} et composéedes tuples (b1, . . . , bp) tels que pour tout tuple (a1, . . . , an) ∈ S,(a1, . . . , an, b1, . . . , bp) ∈ R.
Plus formellement...1 Q ⊆
∏a(R)−a(S)(R)
2 Q × S ⊆ R
Expression de ÷ à partir de Π, × et −R ÷ S = Πa(R)−a(S)(R)−Πa(R)−a(S)((Πa(R)−a(S)(R)× S)− R)
Bases de données – Licence pro Informatique Modèle relationnel 35
Algèbre relationnelle Autres opérations
Quotient
Définition (Quotient)Soit R et S deux relations telles que a(S) ⊆ a(R).a(S) = {A1, . . . ,An} et a(R) = a(S) ∪ {B1, . . . ,Bp}.
Q = R ÷ S est la relation définie sur {B1, . . . ,Bp} et composéedes tuples (b1, . . . , bp) tels que pour tout tuple (a1, . . . , an) ∈ S,(a1, . . . , an, b1, . . . , bp) ∈ R.
Plus formellement...1 Q ⊆
∏a(R)−a(S)(R)
2 Q × S ⊆ R
Expression de ÷ à partir de Π, × et −R ÷ S = Πa(R)−a(S)(R)−Πa(R)−a(S)((Πa(R)−a(S)(R)× S)− R)
Bases de données – Licence pro Informatique Modèle relationnel 35
Algèbre relationnelle Autres opérations
Quotient
Définition (Quotient)Soit R et S deux relations telles que a(S) ⊆ a(R).a(S) = {A1, . . . ,An} et a(R) = a(S) ∪ {B1, . . . ,Bp}.
Q = R ÷ S est la relation définie sur {B1, . . . ,Bp} et composéedes tuples (b1, . . . , bp) tels que pour tout tuple (a1, . . . , an) ∈ S,(a1, . . . , an, b1, . . . , bp) ∈ R.
Plus formellement...1 Q ⊆
∏a(R)−a(S)(R)
2 Q × S ⊆ R
Expression de ÷ à partir de Π, × et −R ÷ S = Πa(R)−a(S)(R)−Πa(R)−a(S)((Πa(R)−a(S)(R)× S)− R)
Bases de données – Licence pro Informatique Modèle relationnel 35
Algèbre relationnelle Autres opérations
Quotient
Définition (Quotient)Soit R et S deux relations telles que a(S) ⊆ a(R).a(S) = {A1, . . . ,An} et a(R) = a(S) ∪ {B1, . . . ,Bp}.
Q = R ÷ S est la relation définie sur {B1, . . . ,Bp} et composéedes tuples (b1, . . . , bp) tels que pour tout tuple (a1, . . . , an) ∈ S,(a1, . . . , an, b1, . . . , bp) ∈ R.
Plus formellement...1 Q ⊆
∏a(R)−a(S)(R)
2 Q × S ⊆ R
Expression de ÷ à partir de Π, × et −R ÷ S = Πa(R)−a(S)(R)−Πa(R)−a(S)((Πa(R)−a(S)(R)× S)− R)
Bases de données – Licence pro Informatique Modèle relationnel 35
Algèbre relationnelle Autres opérations
Exemple (Quotient)R
Nom Sport NiveauJean Hauteur 2Pierre 100 m 1Jean 100 m 2Pierre Perche 1Pierre Longueur 1Jean Longueur 1
SSport Niveau100 m 1Perche 1Longueur 1
R ÷ SNomPierre
Bases de données – Licence pro Informatique Modèle relationnel 36
Algèbre relationnelle Autres opérations
Exemple (Quotient)R
Nom Sport NiveauJean Hauteur 2Pierre 100 m 1Jean 100 m 2Pierre Perche 1Pierre Longueur 1Jean Longueur 1
SSport Niveau100 m 1Perche 1Longueur 1
R ÷ SNomPierre
Bases de données – Licence pro Informatique Modèle relationnel 36
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes (cli_nom, adresse)Clients n Commandes = {(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes (cli_nom, adresse)Clients n Commandes = {(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes
(cli_nom, adresse)Clients n Commandes = {(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes (cli_nom, adresse)
Clients n Commandes = {(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes (cli_nom, adresse)Clients n Commandes =
{(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
Semi-jointure
Définition (Semi-jointure)R n S =
∏a(R)(R on S)
Attention : R on S = S on R mais R n S 6= S n R(sauf si a(R) = a(S))
ExempleClients n Commandes (cli_nom, adresse)Clients n Commandes = {(Rudoux, Place de l’horloge)}
Bases de données – Licence pro Informatique Modèle relationnel 37
Algèbre relationnelle Autres opérations
θ-jointure
Définition (θ-jointure)
R onR.A θ S.B S = σR.A θ S.B(R × S)
avec θ ∈ {=, 6=, <,>,≤,≥}.
Bases de données – Licence pro Informatique Modèle relationnel 38
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
Chapitre II
Modèle relationnel
1 Données2 Langages de manipulation3 Algèbre relationnelle
Opérations de baseAutres opérationsL’algèbre relationnelle comme langage de requêtes
Bases de données – Licence pro Informatique Modèle relationnel 38
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.Πadresse(Clients on (σcmd_numéro=23Commandes)){(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.
Πadresse(Clients on (σcmd_numéro=23Commandes)){(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.Πadresse(Clients on (σcmd_numéro=23Commandes))
{(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.Πadresse(Clients on (σcmd_numéro=23Commandes)){(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.Πadresse(Clients on (σcmd_numéro=23Commandes)){(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.
Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Algèbre relationnelle L’algèbre relationnelle comme langage de requêtes
L’algèbre relationnelle comme langage de requêtes
Les opérateurs de l’algèbre relationnelle permettent d’exprimertoute requête non récursive. On peut donc utiliser l’algèbrerelationnelle pour exprimer une requête.
ExempleL’adresse du client de la facture 23.Πadresse(Clients on (σcmd_numéro=23Commandes)){(Place de l’horloge)}
cependant...Langage pas très facile : l’utilisateur doit connaître le schéma de labase pour naviguer dans les relations.Ce n’est pas exacement un langage déclaratif (quoi), puisqu’uneexpression décrit comment la requête doit être résolue.
Bases de données – Licence pro Informatique Modèle relationnel 39
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 40
Présentation de SQL
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 41
Présentation de SQL
Présentation de SQL
SQL = Structured Query Language = langage d’interrogationstructuré Langage de gestion de bases de données relationnellespour
interrogermettre à jour (LMD ; Langage de Manipulation des Données)définir les données (LDD ; Langage de Définition des Donnéesgérées)contrôler l’accès aux données (LCD ; Langage de Contrôle del’accès aux Données)
Bases de données – Licence pro Informatique SQL 42
Présentation de SQL
Définition (Norme SQL92 (ou SQL2))Norme adoptée en 1992 par l ’ISO (International Organisation forStandardization) Presque complètement implémentée par lesprincipaux SGBD : Oracle, DB2, Informix, MySQL, PostgreSQL,Access, SQL Server,. . .
Depuis, de nouvelle normes (SQL99 (ou SQL3), SQL:2003 etSQL:2008) ont été proposées. Elles ajoutent certainesfonctionnalités mais elles ne sont pas toutes implémentées danstous les SGBD.
Bases de données – Licence pro Informatique SQL 43
Mysql
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 44
Mysql
SGBDR (Relationnel) qui utilise le langage SQLLangage procédural suivant la norme SQL:2003
Nombreux programmes utilitairesmysql : SQL interactif en ligne de commandesmysql workbench : saisir/voir des données, créer/administrerdes serveurs, ...phpmyadmin pour l’interface avec le Web...
Bases de données – Licence pro Informatique SQL 45
Mysql
Identificateurs pour les objets manipulés30 caractères au plus n lettres, chiffres, _, $ ou # (commencepar une lettre)pas un mot clef
Quelques mots clefs : ASSERT, ASSIGN, AUDIT, COMMENT,DATE, DECIMAL, DEFINITION, FILE, FORMAT, INDEX, LIST,MODE, OPTION, PARTITION, PRIVILEGES, PUBLIC, SELECT,SESSION, SET,TABLE.
Bases de données – Licence pro Informatique SQL 46
Mysql
Relations stockées sous forme de tables composées de lignes et decolonnesSQL92 : le nom d’une table est précédé du nom d’un schéma (pourréunir tous les objets liés à un même thème) Par défaut, le schémaest le nom de l’utilisateur connecté
Bases de données – Licence pro Informatique SQL 47
Mysql
Exemple (Exemple de table : DEPT)
Dept NomD Lieu10 Finances Paris20 Recherche Grenoble30 Ventes Lyon
Exemple (Exemple de table : EMP)
Matr Nom Sal Com Sup Dept1200 Dupond 2500 300 2200 102200 Durand 3000 500 101780 Boisier 2500 2200 20
Bases de données – Licence pro Informatique SQL 48
Mysql
Toutes les données d’une colonne sont d’un même typeIdentificateur unique pour les colonnes d’une table, mais 2colonnes dans 2 tables différentes peuvent avoir le même nomLe nom complet d’une colonne comprend le nom de la table àlaquelle elle appartient (obligatoire en cas d’ambiguïté) :DEPT.dept, BERNARD.DEPT.dept
Bases de données – Licence pro Informatique SQL 49
Types de données
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 50
Types de données
Types numériquesTypes pour les chaînes de caractèresTypes temporels (dates, heures, minutes,. . . )Types fichiersSQL ne permet pas à l’utilisateur de créer ses propres types
Bases de données – Licence pro Informatique SQL 51
Types de données
Nombres entiers :SMALLINT sur 2 octetsINTEGER sur 4 octets
À virgule flottante :REALDOUBLE PRECISION (ou FLOAT)
Constantes : 253.8, -10, 1.3E-5
Bases de données – Licence pro Informatique SQL 52
Types de données
Nombres décimaux à nombre fixe de décimales :DECIMAL(nbChiffres, nbDécimales)NUMERIC(nbChiffres, nbDécimales)Les deux sont identiques en MySQL
NUMERIC(8, 2) ou DECIMAL(8, 2) : 6 chiffres avant lavirgule et 2 aprèsConstantes : 253.8, -10
Bases de données – Licence pro Informatique SQL 53
Types de données
CHAR(longueur) chaînes de caractères avec un nombre fixe decaractèresVARCHAR(longueurMaximum) chaînes de caractères avec unnombre variable de caractères (mais un nombre maximum decaractères)CHAR(5) : chaîne de 5 caractèresVARCHAR(20) : chaîne de 20 caractères au plusConstante : ’Comptabilité’, ’Aujourd”hui’
Bases de données – Licence pro Informatique SQL 54
Types de données
DATE pour les datesTIME pour les heures, minutes et secondesTIMESTAMP pour un moment précis : date et heures,minutes et secondes, avec une précision jusqu’à lamicroseconde (un millionième de seconde)
Bases de données – Licence pro Informatique SQL 55
Types de données
BIT permet d’enregistrer un bitExemples : BIT(1), BIT(4)
Bases de données – Licence pro Informatique SQL 56
Types de données
Une valeur de type BLOB est un objet binaire de grande tailleTINYBLOB, BLOB, MEDIUMBLOB, et LONGBLOB nedifférent que par la taille maximale de données qu’ils peuventstocker
TINYTEXT, TEXT, MEDIUMTEXT, et LONGTEXTcorrespondent aux types BLOBdifférences aux niveau des tris et comparaisons : une valeurTEXT est une valeur BLOB insensible à la casse.
Bases de données – Licence pro Informatique SQL 57
Interrogations simples
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 58
Interrogations simples
Interrogations simples
Définition (Select simple)SELECT expression1, expression2, . . .FROM tableWHERE prédicat
Exempleselect nomE, poste from emp;select * from dept;select nomE, sal + comm from emp;select matr, nomE, sal * 1.15 from emp where sal + comm >=12500;
Bases de données – Licence pro Informatique SQL 59
Interrogations simples
Expressions
Ces expressions se trouvent à la suite du select ou du where ; ellespeuvent comporter des
noms de colonnesconstantesopérateurs arithmétiquesconcaténations de chaînes de caractères (||)calculs sur les dates (+ et -)fonctions
Bases de données – Licence pro Informatique SQL 60
Interrogations simples
Valeur NULL
Valeur attribuée aux attributs qui n’ont pas reçu de valeurLes expressions qui contiennent la valeur NULL ont la valeurNULL :comm + sal est NULL si comm est NULLSauf la fonction COALESCE
Bases de données – Licence pro Informatique SQL 61
Interrogations simples
COALESCE
COALESCE(expr1, expr2,. . . )retourne la valeur de la 1ère expression non NULL parmi lesexpressions expr1, expr2,. . .COALESCE(comm, 0)COALESCE(comm, salaire, 0)COALESCE(poste, ’simple soldat’)
Bases de données – Licence pro Informatique SQL 62
Interrogations simples
NVL
NVL(expression, valeur)renvoie la valeur de l’expression si elle n’est pas NULL, etvaleur sinonExemple : NVL(comm, 0)
Bases de données – Licence pro Informatique SQL 63
Interrogations simples
NULLIF
Permet de récupérer des données d’une BD qui n’utilisait pasla valeur NULLExemple : select nomE, NULLIF(comm, -1) from emp
Bases de données – Licence pro Informatique SQL 64
Interrogations simples
Signification de NULL
NULL signifie qu’une donnée est manquante et qu’on neconnaît donc pas sa valeur Cependant, dans la pratique il peutsignifier (entre autres) :
pas applicable (seuls les commerciaux touchent unecommission) ; à éviter, mais possible pour simplifier le schémade la basevaleur 0 : on peut considérer que la valeur NULL correspond àla valeur 0 ; à éviter !
Bases de données – Licence pro Informatique SQL 65
Interrogations simples
Logique à 3 valeurs
La valeur NULL implique une logique à 3 valeurs :vraifauxon ne sait pas si c’est vrai ou faux
Par exemple, la condition « salaire > 1000 » pour un employépeut être
vrai, si le salaire est 1200faux, si le salaire est 700on ne sait pas, si le salaire est NULL
Bases de données – Licence pro Informatique SQL 66
Interrogations simples
Implication de cette logique
Soit une liste L qui contient NULL : (1, 8, NULL, 78, 7)Est-ce que 1 appartient à L ?Évidemment ouiEst-ce que 10 appartient à L ?On ne sait pas !Est-ce que 10 n’appartient pas à L ?On ne sait pas !
Bases de données – Licence pro Informatique SQL 67
Création d’une table (LDD)
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)
Contraintes d’intégritéDictionnaire des données
6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 68
Création d’une table (LDD)
Création d’une table
DéfinitionCréation d’une table CREATE TABLE table (colonne1 type1,colonne2 type2,......)
Exemplecreate table article (ref char(5) not null,nom varchar(20),prix numeric(9,2),dateMAJ date);
Option not null si la colonne doit obligatoirement être renseignée.Bases de données – Licence pro Informatique SQL 69
Création d’une table (LDD)
Valeur par défaut
On peut donner une valeur par défaut pour une colonne :
Exemplecreate table dept (numDept integer not null,nomDept varchar(20),ville varchar(30) default ’Nice’);
On peut aussi donner une fonction comme valeur par défaut ; parexemple, default sysdate
Bases de données – Licence pro Informatique SQL 70
Création d’une table (LDD)
DESCRIBE
Cette commande est à la base développée pour Oracle mais estmaintenant intégrée dans MySQL. Elle affiche une description descolonnes d’une table :ExempleSQL > describe article;Name Null ? TypeREF Not null CHAR(5)NOM VARCHAR(20)PRIX NUMBER(9,2)DATEMAJ DATE
Bases de données – Licence pro Informatique SQL 71
Création d’une table (LDD) Contraintes d’intégrité
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)
Contraintes d’intégritéDictionnaire des données
6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 71
Création d’une table (LDD) Contraintes d’intégrité
Contraintes d’intégrité
DéfinitionContraintes d’intégrité
Une contrainte d’intégrité est une contrainte que doiventvérifier les données d’une tableUne commande est annulée par le SGBD si son exécutionviole une des contraintes
Bases de données – Licence pro Informatique SQL 72
Création d’une table (LDD) Contraintes d’intégrité
Types de contraintes d’intégrité
PRIMARY KEY : clé primaireFOREIGN KEY . . . REFERENCES : clé étrangèreUNIQUE : 2 lignes ne peuvent avoir la même valeur pour lescolonnes spécifiéesCHECK : contrainte de domaine, ou autre ; porte sur uneseule ligne
Bases de données – Licence pro Informatique SQL 73
Création d’une table (LDD) Contraintes d’intégrité
Types de contraintes d’intégrité
2 types de contraintes :contrainte de colonne (concerne une seule colonne)contrainte de table
Bases de données – Licence pro Informatique SQL 74
Création d’une table (LDD) Contraintes d’intégrité
Définition des contraintes
Les contraintes sont définies dans les commandes CREATE(ou ALTER) TABLE
à l’intérieur des définitions de colonnes pour les contraintes decolonneau même niveau que les définitions de colonnes pour lescontraintes de table
Pour les contraintes de table : CONSTRAINTnomContrainte définitionContrainte
Bases de données – Licence pro Informatique SQL 75
Création d’une table (LDD) Contraintes d’intégrité
Clé primaire
Si la clé primaire n’est formée que d’une seule colonne, le plussimple est d’ajouter une contrainte de colonne :
Exemplecreate table emp (matr integer primary key,...
Sinon, il faut ajouter une contrainte de table :
Exemplecreate table participation (matr integer,codeP integer,...,constraint pkpar primary key(matr, codeP))
Bases de données – Licence pro Informatique SQL 76
Création d’une table (LDD) Contraintes d’intégrité
Une erreur à ne pas faire
Si une table a une clé primaire formée de 2 colonnes, il ne fautpas déclarer 2 contraintes de colonneIl faut déclarer une seule contrainte de table portant sur les 2colonnes :constraint pkpar primary key(matr, codeP)Aucune des colonnes de la clé primaire ne peut avoir la valeurnull
Bases de données – Licence pro Informatique SQL 77
Création d’une table (LDD) Contraintes d’intégrité
Contrainte UNIQUE
2 lignes de la table ne pourront avoir la même valeur (saufNULL)Correspond à un identificateur (clé candidate si minimal), sion ajoute une contrainte NOT NULL
Bases de données – Licence pro Informatique SQL 78
Création d’une table (LDD) Contraintes d’intégrité
Clé étrangère
Si une seule colonne forme la clé étrangère, le plus simple estd’utiliser une contrainte de colonne :Exemplecreate table emp (...,dept integer references dept(dept))
(dept) : Optionnel si la colonne référencée est clé primaire
Bases de données – Licence pro Informatique SQL 79
Création d’une table (LDD) Contraintes d’intégrité
Clé étrangère
Peut être une contrainte de table :DéfinitionFOREIGN KEY (colonne1, colonne2,. . . )REFERENCES table-ref [(col1, col2,. . . )]
Exemplecreate table emp (...,dept integer,constraint r_dept foreign key(dept) references dept(dept))
Bases de données – Licence pro Informatique SQL 80
Création d’une table (LDD) Contraintes d’intégrité
Clé étrangère
Les colonnes de l’autre table référencées (col1, col2,. . . ) doiventavoir la contrainte PRIMARY KEY ou UNIQUEconstraint r_dept references dept(dept)dept doit être clé primaire, ou unique
Bases de données – Licence pro Informatique SQL 81
Création d’une table (LDD) Contraintes d’intégrité
Option ON DELETE CASCADE (sans)
Exemplecreate table emp (...dept integer references dept)
ouExemplecreate table emp (...dept integerconstraint r_dept foreign key (dept) references dept)
Attention On ne peut supprimer un département s’il est référencépar une ligne de la table emp
Bases de données – Licence pro Informatique SQL 82
Création d’une table (LDD) Contraintes d’intégrité
Option ON DELETE CASCADE (avec)
Exemplecreate table emp (...dept decimal(2,0),constraint r_dept foreign key (dept) references dept on deletecascade)
Attention La suppression d’un département entraîneautomatiquement la suppression de toutes les lignes de la tableemp qui référencent ce département.
Bases de données – Licence pro Informatique SQL 83
Création d’une table (LDD) Contraintes d’intégrité
Autres options pour les clés étrangères
on delete set nullon delete set defaulton update cascadeon update set nullon update set default
Bases de données – Licence pro Informatique SQL 84
Création d’une table (LDD) Contraintes d’intégrité
Exemples divers de contraintes
Exemplecreate table emp (matr integer primary key,nomE varchar(30) unique,dept smallint references dept check (dept in (10, 20, 30, 40)),constraint nom_unique check (nomE = upper(nomE)));
Exemplecreate table participation (matr integer references emp,codeP varchar(5) references projet,. . . ,constraint pkpart primary key(matr, codeP));
Bases de données – Licence pro Informatique SQL 85
Création d’une table (LDD) Contraintes d’intégrité
Modification des contraintes
ExempleALTER TABLE empDROP CONSTRAINT nom_uniqueADD (CONSTRAINT sal_min check(sal + coalesce(comm, 0) >5000))RENAME CONSTRAINT truc TO machin;
Attention On ne peut ajouter que des contraintes de table.
Bases de données – Licence pro Informatique SQL 86
Création d’une table (LDD) Contraintes d’intégrité
Vérification des contraintes
En fonctionnement normal les contraintes sont vérifiées àchaque requête SQL.Cette vérification peut être gênante, en particulier lors del’ajout de plusieurs lignes de données.Exemple : si on a cette contrainte sur la colonne SUP de latable EMP : constraint sup_ref_emp foreign key (SUP)references EMPLa contrainte oblige à ajouter les supérieurs en premier.
Bases de données – Licence pro Informatique SQL 87
Création d’une table (LDD) Dictionnaire des données
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)
Contraintes d’intégritéDictionnaire des données
6 Langage de manipulation des données (LMD)7 Interrogation de la base
Bases de données – Licence pro Informatique SQL 87
Création d’une table (LDD) Dictionnaire des données
Dictionnaire des données
DéfinitionTables qui stockent les descriptions des objets de la baseTenues à jour automatiquement par le SGBDPeuvent être consultées au moyen du langage
Bases de données – Licence pro Informatique SQL 88
Création d’une table (LDD) Dictionnaire des données
INFORMATION_SCHEMA
Ce dictionnaire est stocké dans la baseINFORMATION_SCHEMA
ExempleSELECT table_name, table_type, engine FROMinformation_schema.tables;
Il est aussi possible d’utiliser la commande SHOW (ex : showtables)
Bases de données – Licence pro Informatique SQL 89
Création d’une table (LDD) Dictionnaire des données
Tables de INFORMATION_SCHEMA :SCHEMATA : informations sur les bases de donnéesTABLES : informations sur les tablesCOLUMNS : informations sur les colonnes dans les tablesUSER_PRIVILEGES : informations sur les droits globauxSCHEMA_PRIVILEGES : informations sur les droits desschémasTABLE_PRIVILEGES : informations sur les droits des tablesCOLUMN_PRIVILEGES : informations sur les droits reliésaux colonnesTABLE_CONSTRAINTS : informations sur les tables qui ontdes contraintesKEY_COLUMN_USAGE : descriptions des contraintes sur lescolonnesROUTINES : informations sur les procédures stockéesVIEWS : informations sur les vuesBases de données – Licence pro Informatique SQL 90
Langage de manipulation des données (LMD)
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)
INSERT/UPDATE/DELETETransactions
7 Interrogation de la baseBases de données – Licence pro Informatique SQL 91
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)
INSERT/UPDATE/DELETETransactions
7 Interrogation de la baseBases de données – Licence pro Informatique SQL 91
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Langage de manipulation des données (LMD)
Commandes de manipulation des données :INSERT pour ajouter des lignesUPDATE pour modifier des lignesDELETE pour supprimer des lignes
Bases de données – Licence pro Informatique SQL 92
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Insertion
DéfinitionINSERT INTO table [(colonne1, colonne2,...)] VALUES (valeur1,valeur2,...)
ouDéfinitionINSERT INTO table [(colonne1, colonne2,...)] select ...
La liste des colonnes est optionnelle ; par défaut, toutes lescolonnes sont dans l’ordre donné lors de la création de la table.Si la commande comporte une liste, les colonnes qui ne sont pasdans la liste auront la valeur NULLDans les programmes, il faut toujours donner la liste des colonnesdont on donne les valeurs pour faciliter la maintenance del’application
Bases de données – Licence pro Informatique SQL 93
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Insertion
Exempleinsert into dept values (10, ’Finance’, ’Paris’);insert into dept (lieu, nomD, dept) values (’Grenoble’, ’Recherche’,20);insert into emp (matr, nomE, dept, sal)select matr + 100, nomE, 60, sal * 0.15from empwhere dept = 10;
Bases de données – Licence pro Informatique SQL 94
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Modification
DéfinitionUPDATE table SET colonne1 = expr1, colonne2 = expr2, . . . [WHERE prédicat ]
DéfinitionUPDATE table [ synonyme ] SET (colonne1, colonne2, . . . ) =(select . . . ) [ WHERE prédicat ]
Le select ne doit renvoyer qu’une seule ligne
Bases de données – Licence pro Informatique SQL 95
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Exemples
Exempleupdate emp set dept = 10 where nomE = ’Martin’;update emp set sal = sal * 1.1 where poste = ’Commercial’;
Exempleupdate emp set sal = (select avg(sal) * 1.1 from emp where poste= ’Secrétaire’) where nomE = ’Clément’;update emp E set (sal, comm) = (select avg(sal), avg(comm) fromemp where dept = E.dept) where poste = ’Secrétaire’;
Bases de données – Licence pro Informatique SQL 96
Langage de manipulation des données (LMD) INSERT/UPDATE/DELETE
Suppressions
DéfinitionDELETE FROM table [ WHERE prédicat ]
Attention S’il n’y a pas de clause WHERE, toutes les lignes sontsupprimées.
Exempledelete from emp where dept = 10;
Bases de données – Licence pro Informatique SQL 97
Langage de manipulation des données (LMD) Transactions
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)
INSERT/UPDATE/DELETETransactions
7 Interrogation de la baseBases de données – Licence pro Informatique SQL 97
Langage de manipulation des données (LMD) Transactions
Transaction
Une requête forme un tout indivisibleSi une erreur survient pendant l’exécution d’une requête SQL,toutes les modifications déjà effectuées par la requête sontannulées automatiquement.On peut généraliser ce comportement à un ensemble derequêtes SQL en utilisant les transactionsEnsemble de modifications de la base qui forment un toutindivisible : à la fin de la transaction, toutes les modificationseffectuées pendant la transaction sont sauvegardées ouannulées
Bases de données – Licence pro Informatique SQL 98
Langage de manipulation des données (LMD) Transactions
Début d’une transaction
Dans la norme SQL2 toute modification appartient à unetransactionPar défaut, MySQL est lancé en mode autocommit(transaction effectuée après chaque opération)Une transaction démarre automatiquement après la fin d’unetransaction (pas de commande pour démarrer une transaction)Une transaction démarre par START TRANSACTIONLa structure des transactions est « plate » : les transactionsne peuvent se chevaucher
Bases de données – Licence pro Informatique SQL 99
Langage de manipulation des données (LMD) Transactions
Terminer une transaction
Pour terminer une transaction on peutvalider la transaction (COMMIT) : toutes les modificationsdeviennent effectivesannuler la transaction (ROLLBACK) : toutes les modificationssont annulées
Les ordres DDL (create table par exemple) provoquent unCOMMIT automatique
Bases de données – Licence pro Informatique SQL 100
Langage de manipulation des données (LMD) Transactions
Propriétés des transactions - ACID
Atomicité : un tout indivisibleCohérence : une transaction doit laisser la base dans un étatcohérent ; elle ne doit pas mettre les données dans un état «anormal »Isolation : une transaction est isolée des autres transactions(dans une certaine mesure. . . )Durabilité : le SGBD doit garantir que les modifications d’unetransaction validée seront conservées, même en cas de panne
Bases de données – Licence pro Informatique SQL 101
Langage de manipulation des données (LMD) Transactions
Propriétés des transactions - ACID
AID est du ressort du système transactionnel du SGBDC est du ressort de l’utilisateur mais il est aidé
par I, car il n’a pas à considérer les interactions avec les autrestransactionspar la vérification automatique des contraintes d’intégrité parle SGBD
I est assuré par le système de contrôle de la concurrence duSGBD et AD sont supportés par les procédures de repriseaprès panne du SGBD
Bases de données – Licence pro Informatique SQL 102
Langage de manipulation des données (LMD) Transactions
Transaction
Exemple d’annulation d’une transaction
Exemplestart transaction;insert into dept values (10, ’Finance’, ’Paris’);delete from emp;rollback;
Bases de données – Licence pro Informatique SQL 103
Langage de manipulation des données (LMD) Transactions
Isolation des transactions
En fonctionnement standard les modifications effectuées par unetransaction T ne sont connues par les autres transactions qu’aprèsvalidation de T En fait, il existe plusieurs niveaux d’isolation (voircours sur la concurrence)
Bases de données – Licence pro Informatique SQL 104
Langage de manipulation des données (LMD) Transactions
Transactions longues
Exemple : organisation par une agence de voyage d’un voyageNice – Wuhan (Chine)Nécessite la réservation de plusieurs billets d’avion : Nice –Paris ; Paris – Beijing ; Beijing – WuhanOn commence par réserver les 2 premiers mais si on ne peuttrouver de Beijing – Wuhan, il faut tout annulerOn met donc toutes ces réservations dans une transaction ; çapeut être long si l’agence discute avec le client pendant latransaction
Bases de données – Licence pro Informatique SQL 105
Langage de manipulation des données (LMD) Transactions
Problèmes avec les transactions longues
Manque de souplesse : si on ne trouve pas de voyage Beijing –Wuhan, on annule toutOn aurait pu garder le Nice – Paris et essayer de passer parShanghai pour aller à Wuhan, en annulant seulement le Paris– BeijingAutre problème : le contrôle de la concurrence effectue desblocages sur les tables et les lignes qui ne sont relâchés qu’à lafin de la transactionUn problème de communication peut provoquer l’annulationdes premières réservations alors qu’on pourrait simplementréessayer le lendemain
Bases de données – Licence pro Informatique SQL 106
Langage de manipulation des données (LMD) Transactions
Transactions emboîtées
Extension de la notion de transaction « plate »Évite les annulations complètes de transactionsApporte plus de souplesse dans les transactions longues etmulti-sitesPermet de limiter la durée des blocages des ressources système
Bases de données – Licence pro Informatique SQL 107
Langage de manipulation des données (LMD) Transactions
Définition des transactions emboîtées
Une transaction globale (mère) peut contenir dessoustransactions filles qui, elles-mêmes, peuvent avoir des fillesL’annulation d’une transaction n’annule pas nécessairement latransaction mère ; celle-ci peut
décider d’un traitement substitutifreprendre la transaction annulées’annulerou même ignorer l’annulation (traitement pas indispensable)
L’annulation d’une transaction provoque l’annulationautomatique de toutes ses transactions filles
Bases de données – Licence pro Informatique SQL 108
Langage de manipulation des données (LMD) Transactions
Points de reprise
Sans passer au modèle des transactions emboîtées, on peutassouplir le modèle des transactions platesDésigner des points de reprise dans une transaction :savepoint nomPointPossible d’annuler toutes les modifications effectuées depuisun point de reprise : rollback to nomPointÉvite d’annuler toute la transaction et permet d’essayer depallier le problème si c’est possible
Bases de données – Licence pro Informatique SQL 109
Langage de manipulation des données (LMD) Transactions
Points de reprise
Exemplestart transaction;insert into ...;savepoint p1;delete from ...;update ...;savepoint p2;insert into ...; – Problème !rollback to p2;insert into ...; – on essaie autre chosecommit;
Bases de données – Licence pro Informatique SQL 110
Interrogation de la base
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Fonctions de groupe
Bases de données – Licence pro Informatique SQL 111
Interrogation de la base
Syntaxe générale
DéfinitionSELECT ... FROM ...WHERE ...GROUP BY ...HAVING ...ORDER BY ...
L’ordre des clauses est imposéSELECT et FROM sont obligatoires
Bases de données – Licence pro Informatique SQL 112
Interrogation de la base
Clause SELECT
Définitionselect [distinct] expression1 [ [AS] nom1], expression2 [ [AS]nom2], . . .
nom1 :en-tête de la colonne (entre guillemets si mot-clef ou sicontient plusieurs mots)alias pour désigner la colonne dans une autre partie du select
select * : Toutes les colonnesExempleselect distinct poste from emp;select nomE, sal + coalesce(comm, 0) as "Salaire Total" from emp;
Bases de données – Licence pro Informatique SQL 113
Interrogation de la base
select dans une expression
Exempleselect nomE, sal / (select sum(sal) from emp) * 100 from emp
Le select de l’expression peut être synchronisé avec le selectprincipal :
Exempleselect nomE, (select count(*) from emp where sal > e1.sal) + 1 asrang from emp e1
e1 est un synonyme de emp pour lever l’ambiguïté dans le selectinterne
Bases de données – Licence pro Informatique SQL 114
Interrogation de la base
select dans une expression
On peut utiliser l’alias pour trier par ordre décroissant des salaires :
Exempleselect nomE, (select count(*) from emp where sal > e1.sal) + 1 asrang from emp e1 order by rang
Bases de données – Licence pro Informatique SQL 115
Interrogation de la base
Clause FROM
DéfinitionFROM table1 [synonyme1], table2 [synonyme2], ...
Produit cartésien des tables s’il y en a plusieursPossible de se restreindre à un sous-ensemble du produitcartésien (voir jointure)
Bases de données – Licence pro Informatique SQL 116
Interrogation de la base
Clause FROM
ExempleDEPT NOMD
select B.dept, A.nomD 10 VENTESfrom dept A, dept B 20 RECHERCHE
30 FINANCE
DEPT NOMD30 VENTES20 VENTES10 VENTES30 RECHERCHE20 RECHERCHE10 RECHERCHE30 FINANCE20 FINANCE10 FINANCE
Bases de données – Licence pro Informatique SQL 117
Interrogation de la base
Clause FROM
Certains SGBDs (et la norme SQL-2) permettent l’utilisation d’unSELECT à la place du nom d’une table :
Exempleselect nomE, sal,sal / total * 100 Pourcentagefrom emp,(select sum(sal) as total from emp);
Bases de données – Licence pro Informatique SQL 118
Interrogation de la base
Clause WHERE
La clause WHERE comporte de nombreuses possibilités :opérateurs de comparaisonopérateurs logiquesjointuressous-interrogations
Bases de données – Licence pro Informatique SQL 119
Interrogation de la base
Opérateurs de comparaison
=, !=, <, >, <=, >=, BETWEEN, LIKE, NOT LIKE, IN,NOT IN, IS NULL, IS NOT NULLLIKE permet d’utiliser des jokers :
% pour une chaîne de caractères de longueur quelconque_ pour un seul caractère
Attention, expression = NULL n’est jamais vrai, il faut utiliserexpression IS NULL
Bases de données – Licence pro Informatique SQL 120
Interrogation de la base
Opérateurs de comparaison
Exempleselect * from emp where poste = ’Secrétaire’;select * from emp where sal between 10000 and 15000;select * from emp where dept in (10, 30);select * from emp where comm is not null;select * from emp where nomE like ’%A%’;
Bases de données – Licence pro Informatique SQL 121
Interrogation de la base
Opérateurs logiques
AND, OR, NOT
Exempleselect nomE from empwhere dept = 30and (sal > 10000 or comm is null);
Exempleselect * from empwhere not (poste = ’Directeur’or poste = ’Secrétaire’);
Bases de données – Licence pro Informatique SQL 122
Interrogation de la base
Jointures
Traduction de l’équi-jointure « emp on dept » :
Exempleselect nomE, nomDfrom emp, deptwhere emp.dept = dept.dept
Autre syntaxe :
Exempleselect nomE, nomDfrom emp JOIN deptON emp.dept = dept.dept
Bases de données – Licence pro Informatique SQL 123
Interrogation de la base
Jointure de plus de 2 tables
Exempleselect nomE, nomPfrom emp, participation, projetwhere emp.matr = participation.matrand participation.codeP = projet.codeP
Autre syntaxe :
Exempleselect nome, nompfrom empjoin participationon emp.matr = participation.matrjoin projeton participation.codep = projet.codep
Bases de données – Licence pro Informatique SQL 124
Interrogation de la base
Jointure naturelle
La jointure s’effectue sur toutes les colonnes qui ont le mêmenom dans les 2 tables ; ces colonnes ne sont pas répétées dansla jointureLes colonnes qui participent à la jointure ne doivent êtrepréfixées par un nom de table
Exempleselect nomE, nomD, dept from emp NATURAL JOIN dept;select nome, nomp from emp NATURAL JOIN participationNATURAL JOIN projet;
Bases de données – Licence pro Informatique SQL 125
Interrogation de la base
Jointure d’une table avec elle-même
Alias indispensable pour le nom de la table afin de leverl’ambiguïté sur les colonnes :
Exempleselect emp.nomE "Employé",supe.nomE "Supérieur"from emp join emp supeon emp.sup = supe.matr
Bases de données – Licence pro Informatique SQL 126
Interrogation de la base
Jointures « non équi »
Les jointures « non équi » peuvent être traduites comme leséqui-jointures, en utilisant d’autres opérateurs de comparaison
Exempleselect emp1.nomE, emp2.nomEfrom emp emp1join emp emp2on emp1.sal < emp2.sal
Bases de données – Licence pro Informatique SQL 127
Interrogation de la base
Jointure externe
Dans une jointure n’apparaissent que les lignes qui ont uneligne correspondante dans l’autre tableDans l’exemple suivant, un département qui n’a pas d’employén’apparaîtra pas :
Exempleselect nomE, nomD from emp join dept on emp.dept = dept.dept
Si on veut qu’il apparaisse, on doit utiliser une jointure externe
Bases de données – Licence pro Informatique SQL 128
Interrogation de la base
Syntaxe de la jointure externe
Exempleselect nomE, nomDfrom emp RIGHT OUTER JOIN deptON emp.dept = dept.dept
RIGHT indique que l’on veut afficher toutes les lignes de latable de droite (dept)Ça revient à ajouter une « ligne fictive » dans l’autre tableempCette ligne fictive aura toutes ses colonnes à null, sauf lacolonne de jointureIl existe de même LEFT OUTER JOIN et FULL OUTER JOIN
Bases de données – Licence pro Informatique SQL 129
Interrogation de la base
Sous-interrogations
Une clause WHERE peut comporter un ordre SELECTemboîté :
Exempleselect nomE from empwhere poste = (select poste from emp where nomE = ’Martin’);
Cette sous-interrogation doit ramener une seule ligne et uneseule colonneRemplace le select interne par NULL s’il ne renvoie aucuneligne (ou erreur, suivant les SGBD)
Bases de données – Licence pro Informatique SQL 130
Interrogation de la base
Sous-interrogations
Des variantes de sous-interrogations ramènent plusieurs colonnesou plusieurs lignes
Bases de données – Licence pro Informatique SQL 131
Interrogation de la base
Sous-interrogation ramenant 1 ligne, 1 colonne
DéfinitionWHERE expression op (SELECT ...)
où op est un des opérateurs de comparaison =,!=, <, >, <=, >=
Exempleselect nomE from empwhere sal >=(select sal from empwhere nomE = ’Mercier’)
Bases de données – Licence pro Informatique SQL 132
Interrogation de la base
Sous-interrogation ramenant plusieurs lignes
DéfinitionWHERE expression op ANY (SELECT ...)WHERE expression op ALL (SELECT ...)WHERE expression IN (SELECT ...)WHERE expression NOT IN (SELECT ...)
où op est un des opérateurs de comparaison =,!=, <, >, <=, >=ANY : vrai si la comparaison est vraie pour au moins une desvaleurs ramenées par le SELECTALL : vrai si la comparaison est vraie pour toutes les valeursramenées par le SELECT
Bases de données – Licence pro Informatique SQL 133
Interrogation de la base
Remarque
=ANY est équivalent à IN!=ALL est équivalent à NOT IN
Bases de données – Licence pro Informatique SQL 134
Interrogation de la base
Exempleselect nomE, sal from empwhere sal > all (select sal from emp where dept = 30);select nomE, sal from empwhere sal > all (select salfrom empwhere dept = 38888)
Bases de données – Licence pro Informatique SQL 135
Interrogation de la base
Réflexion sur ALL
Quand « x > all (x1, x2, ..., xn) » est faux ?Quand ∃ un xi tel que xi >= xSi 6 ∃ un xi tel que xi >= x, l’expression est vraieDonc si la liste (x1, x2, ..., xn) est vide, l’expression esttoujours vraie
Bases de données – Licence pro Informatique SQL 136
Interrogation de la base
Retour sur NULL
La condition expression not in (expr1, expr2, null) n’estjamais vérifiée. Pourquoi ?Est-ce que la condition expression in (expr1, expr2, null)peut être vérifiée ?Rappel : la logique de SQL n’utilise pas seulement vrai et fauxmais aussi « je ne sais pas », représenté par NULLUtile à savoir pour les sous interrogations qui renvoient NULLpour une des lignes
Bases de données – Licence pro Informatique SQL 137
Interrogation de la base
Sous-interrogations ; optimisation
Soit un select qui comporte une sous-interrogation :
Exempleselect nom from empwhere dept in(select dept from deptwhere lieu = ‘NICE’)
Pour chaque employé, le select peut lancer lasousinterrogation pour savoir si l’employé est dans undépartement qui se trouve à NiceEn fait, le moteur de recherche du SGBD va optimiser enlançant d’abord la sous-interrogation et en conservant enmémoire les départements de Nice
Bases de données – Licence pro Informatique SQL 138
Interrogation de la base
Sous-interrogations synchronisées
Cette optimisation n’est pas possible quand lasous-interrogation utilise une des valeurs ramenées parl’interrogation principaleOn dit que la sous-interrogation est synchronisée avecl’interrogation principaleNotation pointée utilisée pour se référer dans lasous-interrogation à une colonne de l’interrogation principale :sous-interrogation
Exempleselect nomE from emp Ewhere dept != (select dept from empwhere matr = E.sup);
Bases de données – Licence pro Informatique SQL 139
Interrogation de la base
Sous-interrogation ramenant 1 ligne de plusieurs colonnes
DéfinitionWHERE (expr1, expr2,...) op (SELECT ...)
où op est = ou != (mais pas <, >, <=, >=) et où le selectrenvoie 1 seule ligne
Exempleselect nomE from empwhere (poste, sal) =(select poste, sal from empwhere nomE = ’Mercier’);
Bases de données – Licence pro Informatique SQL 140
Interrogation de la base
Relation d’ordre
Il n’y a pas de relation d’ordre totale « naturelle » sur lestuples5 > 3, 12 > 7On ne peut comparer (5, 7) et (3, 12)
Bases de données – Licence pro Informatique SQL 141
Interrogation de la base
Sous-interrogation ramenant plusieurs lignes de plusieurscolonnes
DéfinitionWHERE (expr1, expr2,...) op ANY (SELECT ...)WHERE (expr1, expr2,...) op ALL (SELECT ...)WHERE (expr1, expr2,...) IN (SELECT ...)WHERE (expr1, expr2,...) NOT IN (SELECT ...)
où op est = ou != (mais pas <, >, <=, >=)
Exempleselect nomE from empwhere (poste, sal) in(select poste, sal from empwhere dept = 10);
Bases de données – Licence pro Informatique SQL 142
Interrogation de la base
EXISTS
La clause « EXISTS(select ...) » est vraie si le select renvoieau moins une ligneLa sous-interrogation est le plus souvent synchronisée :
Exempleselect nomE from emp Ewhere exists(select null from empwhere sup = E.matr);
Bases de données – Licence pro Informatique SQL 143
Interrogation de la base
EXERCICE : Division avec not exist
Bases de données – Licence pro Informatique SQL 144
Interrogation de la base Fonctions de groupe
Chapitre III
SQL
1 Présentation de SQL2 Mysql3 Types de données4 Interrogations simples5 Création d’une table (LDD)6 Langage de manipulation des données (LMD)7 Interrogation de la base
Fonctions de groupe
Bases de données – Licence pro Informatique SQL 144
Interrogation de la base Fonctions de groupe
Fonctions de groupe
Les fonctions de groupe peuvent apparaître dans une expression duselect ou du having :
AVG moyenneSUM sommeMIN plus petite valeurMAX plus grande valeurVARIANCE varianceSTDDEV écart typeCOUNT(*) nombre de lignesCOUNT(col) nombre de valeurs non NULL dans la colonneCOUNT(DISTINCT col) nombre de valeurs distinctes
Bases de données – Licence pro Informatique SQL 145
Interrogation de la base Fonctions de groupe
Fonctions de groupe
Exempleselect count(*) from emp;select count(comm) from emp where dept = 10;select sum(sal) from emp where dept = 10;select max(sal) from emp where poste = ’INGENIEUR’;
Bases de données – Licence pro Informatique SQL 146
Interrogation de la base Fonctions de groupe
Niveaux de regroupement
A un niveau de profondeur (relativement aux sousinterrogations) d’un SELECT, les fonctions de groupe et lescolonnes doivent être toutes du même niveau de regroupementPar exemple, si on veut le nom et le salaire des employés quigagnent le plus dans l’entreprise, la requête suivanteprovoquera une erreur :
Exempleselect nome, sal from empwhere sal = max(sal)
Solution :Exempleselect nome, sal from empwhere sal = (select max(sal) from emp)
Bases de données – Licence pro Informatique SQL 147
Interrogation de la base Fonctions de groupe
Clause GROUP BY
Permet de regrouper des lignes qui ont les mêmes valeurs pour desexpressions :
DéfinitionGROUP BY expression1, expression2,...
Il n’est affiché qu’une seule ligne par regroupement de lignes
Exempleselect dept, count(*) from emp group by dept;
Bases de données – Licence pro Informatique SQL 148
Interrogation de la base Fonctions de groupe
Clause GROUP BY
Exempleselect dept, poste, count(*) from emp group by dept, poste;select dept, count(comm) from emp group by dept;
Schéma à retenir pour obtenir des optima sur des regroupements :Exempleselect nome, dept, sal from empwhere (dept, sal) in (select dept, max(sal) from emp group bydept);
Sans doute moins performant :Exempleselect nome, dept, sal from emp Ewhere sal = (select max(sal) from empwhere dept = E.dept);
Bases de données – Licence pro Informatique SQL 149
Interrogation de la base Fonctions de groupe
Clause GROUP BY
Exempleselect nomD, avg(sal) from emp natural join dept group by nomD;
Exempleselect dept, count(*) from emp where poste = ’SECRETAIRE’group by dept;
Exempleselect count(*) "Nbre employés", count(comm) "Nbrecommissions", count(comm) / count(*) "Ratio" from emp;
Bases de données – Licence pro Informatique SQL 150
Interrogation de la base Fonctions de groupe
Restrictions pour les expressions
Dans la liste des expressions du select ne peuvent figurer que descaractéristiques liées aux groupes :
des fonctions de groupesdes expressions figurant dans le GROUP BY
Exemple (Ceci est interdit)select dept, nomE, salfrom empgroup by dept;
Bases de données – Licence pro Informatique SQL 151
Interrogation de la base Fonctions de groupe
Restrictions pour les expressions
Ceci ne fonctionne pas :
Exempleselect nomd, sum(sal)from emp natural join deptgroup by dept.dept
Il aurait fallu écrire :Exempleselect nomd, sum(sal)from emp natural join deptgroup by nomd
Bases de données – Licence pro Informatique SQL 152
Interrogation de la base Fonctions de groupe
Clause HAVING
Cette clause sert à sélectionner des groupes : HAVING prédicatLe prédicat ne peut porter que sur des caractéristiques degroupe
Bases de données – Licence pro Informatique SQL 153
Interrogation de la base Fonctions de groupe
Clause HAVING
Exempleselect dept, count(*) from empwhere poste = ’Secrétaire’group by depthaving count(*) > 1;
Exempleselect nomd "Département",count(*) "Nombre de secrétaires"from emp natural join deptwhere poste = ’Secrétaire’group by nomdhaving count(*) = (select max(count(*)) from empwhere poste = ’Secrétaire’group by dept);
Bases de données – Licence pro Informatique SQL 154
Interrogation de la base Fonctions de groupe
Clause HAVING
Exempleselect dept, count(*) from emp group by dept having count(*) =max(count(*));
Interdit ! car pas le même niveau de regroupement que le reste duselectExempleselect dept, count(*) from empgroup by depthaving count(*) = (select max(count(*)) from empgroup by dept);
Bases de données – Licence pro Informatique SQL 155
Interrogation de la base Fonctions de groupe
Clause ORDER BY
La clause ORDER BY précise l’ordre des lignes d’un SELECT :
DéfinitionORDER BY expr1 [DESC], expr2 [DESC], ...
On peut aussi donner le numéro de la colonne qui servira de clé detriExempleselect dept, nomD from dept order by nomD;select dept, nomD from dept order by 2;
Bases de données – Licence pro Informatique SQL 156
Interrogation de la base Fonctions de groupe
Clause ORDER BY
Exempleselect nome, poste from emp order by dept, sal desc;
select dept, sum(sal) "Total salaires" from emp group by deptorder by 2;
select dept, sum(sal) "Total salaires" from emp group by deptorder by sum(sal);
Bases de données – Licence pro Informatique SQL 157
Interrogation de la base Fonctions de groupe
Fonctions
Fonctions arithmétiques : abs(n), mod(m, n), power(n, e),round(n, p), trunc(n, p), sign(n), sqrt(n), greatest(n1,n2,. . . ), least(n1, n2,. . . )Conversions nombre - chaîne de caractères : to_char(n,format), number(chaîne)Fonctions date : round(date, précision), trunc(date, précision),sysdateConversions date - chaîne de caractères : to_char(date,format), to_date(chaîne, format)
Bases de données – Licence pro Informatique SQL 158
Interrogation de la base Fonctions de groupe
Fonctions
Fonctions sur les chaînes de caractères : length(chaîne),substr(chaîne, position, longueur), locate(sous-chaîne,chaîne), upper(chaîne), lower(chaîne), lpad(chaîne, long, car),rpad(chaîne, long, car), ltrim(chaîne, car), rtrim(chaîne, car),translate(chaîne, lesCar1, lesCar2), replace(chaîne, ancienne,nouvelle)
Bases de données – Licence pro Informatique SQL 159
Interrogation de la base Fonctions de groupe
Fonctions
Exempleselect nomE, to_char(datemb, ’DD/MM/YYYY’)from empwhere round(sysdate – datemb) > 3;
Bases de données – Licence pro Informatique SQL 160
Interrogation de la base Fonctions de groupe
Conditionnelle
2 variantes :DéfinitionCASEWHEN condition1 THEN resultat1WHEN condition2 THEN resultat2ELSE resultat3END
DéfinitionCASE expressionWHEN valeur1 THEN resultat1WHEN valeur2 THEN resultat2ELSE resultat3END
Bases de données – Licence pro Informatique SQL 161
Interrogation de la base Fonctions de groupe
Conditionnelle
ExempleSELECT nome, poste FROM emporder byCASE posteWHEN ’Président’ THEN 1WHEN ’Directeur’ THEN 2ELSE 3 END;
ExempleSELECT nome, poste FROM emporder byCASEWHEN poste = ’Président’ THEN 1WHEN poste = ’Directeur’ THEN 2ELSE 3END;
Bases de données – Licence pro Informatique SQL 162
Interrogation de la base Fonctions de groupe
Opérateurs ensemblistes
On peut effectuer des opérations sur plusieurs selectconsidérés comme des ensembles de lignes :
Définitionselect ... UNION select ...select ... INTERSECT select ...select ... MINUS select ...
Ensembles au sens mathématique : si 2 lignes des 2 selectd’une union ont les mêmes valeurs, l’union n’aura qu’uneseule ligne avec ces valeurs
Bases de données – Licence pro Informatique SQL 163
Interrogation de la base Fonctions de groupe
Exemple d’opérateur ensembliste
Exemple (PostgreSQL)select dept from deptminusselect dept from emp;
Exemple (Mysql)select dept from deptnot in (select dept from emp);
Exempleselect nomE, ’salaire’ as TYPE, sal as MONTANT from emp unionselect nomE, ’commission’, comm from emp where comm is notnull;
nomE TYPE MONTANTToto salaire 1200Bibi salaire 5000Toto commission 240
Bases de données – Licence pro Informatique SQL 164
Interrogation de la base Fonctions de groupe
Opérateurs ensembliste
UNION ALL : Pour conserver les doublons avec UNIONOpérateurs ensembliste et order by : Seul le dernier selectpeut recevoir une clause order by
Bases de données – Licence pro Informatique SQL 165
Interrogation de la base Fonctions de groupe
Limiter le nombre de lignes
Problème de portabilité si on ne veut afficher qu’une partie deslignes récupérées par un select
ExempleSELECT matr, nomEFROM empLIMIT 10
Bases de données – Licence pro Informatique SQL 166
Chapitre IV
Langage de définition des données avancé
Bases de données – Licence pro Informatique Langage de définition des données avancé 167
Création de table par copie
DéfinitionCREATE TABLE table (col1 type, ...) AS select ...
Exemplecreate table dept2(cle integer, nom varchar(20))as select dept, nomd from dept;create table dept10as select * from empwhere dept = 10;
Bases de données – Licence pro Informatique Langage de définition des données avancé 168
Modifier la définition d’une table
DéfinitionALTER TABLE table ADD (col1 type1, col2 type2,...)ALTER TABLE table MODIFY (col1 type1, col2 type2,...)ALTER TABLE table DROP COLUMN colonne;
Bases de données – Licence pro Informatique Langage de définition des données avancé 169
Modifier la définition d’une table
On ne peut modifier une colonne que si la colonne ne contient quedes valeurs null ou si la nouvelle définition est compatible avec lesvaleurs déjà entrées dans cette colonne.
Exemplealter table emp add (situ_famille char(1), nbEnfants smallint);alter table emp modify (situ_famille char(2));
Bases de données – Licence pro Informatique Langage de définition des données avancé 170
Supprimer une colonne
Tous les SGBDs ne permettent pas de supprimer une colonned’une table (Mysql l’accepte)Si c’est impossible, on peut mettre toutes les valeurs de lacolonne à null pour gagner de la placeOn peut aussi
utiliser create table as pour transférer les autres données dansune nouvelle table qui n’a pas cette colonnesupprimer la 1ère tablerenommer la nouvelle table
Bases de données – Licence pro Informatique Langage de définition des données avancé 171
Supprimer une colonne
DéfinitionALTER TABLE emp DROP COLUMN commIl n’est pas possible de supprimer une colonne
référencée par une clé étrangèresur laquelle un index a été construit
Exemplealter table emp drop column situ_famille;
Bases de données – Licence pro Informatique Langage de définition des données avancé 172
Modifier la définition d’une table
Supprimer une table : DROP TABLE tableRenommer une colonne :ALTER TABLE table RENAMECOLUMN ancienNom TO nouveauNomRenommer une table : RENAME ancienNom TO nouveauNomCommande équivalente pour renommer une table : ALTERTABLE ancienNom RENAME TO nouveauNom
Bases de données – Licence pro Informatique Langage de définition des données avancé 173
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 174
Vues
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 175
Vues
Vues
Une vue est une vision virtuelle partielle ou particulière desdonnées d’une ou plusieurs tablesLa définition d’une vue est donnée par un select : les donnéesde la vue sont celles retournées par le selectLes utilisateurs peuvent consulter ou modifier la base à traversla vue comme si c’était une table réelle
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 176
Vues
Création et suppression d’une vue
DéfinitionCREATE VIEW vue [(col1, col2,...)]AS select ... [WITH CHECK OPTION]
Le select peut contenir toutes les clauses d’un select sauf order by
Exemplecreate view emp10 asselect * from empwhere dept = 10;DROP VIEW vue
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 177
Vues
Exemple de création de vues
Exemplecreate viewdeptStat (nom, inf, moy, max, total)asselect nomd,min(sal), avg(sal), max(sal), sum(sal)from emp natural join deptgroup by dept.nomd;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 178
Vues
Vue de vue
Il est possible de créer une vue de vue :
Exemplecreate view stat2 asselect nom, maxfrom deptstat;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 179
Vues
Utilisation des vues dans un select
Dans un select on peut utiliser une vue à la place d’une table
Exempleselect * from emp10;
select nom, totalfrom deptStatwhere total > 100000;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 180
Vues
Suppression avec une vue
On peut effectuer des delete à travers une vue, sous les conditionssuivantes sur le select qui définit la vue :
une seule tablepas de group bypas de distinctpas de fonction de groupe
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 181
Vues
Modification avec une vue
On peut effectuer des update à travers une vue, sous les conditionssuivantes :
une seule tablepas de group bypas de distinctpas de fonction de groupeles colonnes modifiées sont des colonnes réelles de la table(pas des expressions)
Exempleupdate emp10 set sal = sal * 1.1;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 182
Vues
Insertion avec une vue
On peut effectuer des insert à travers une vue sous les conditionssuivantes :
une seule tablepas de group bypas de distinctpas de fonction de groupeles colonnes modifiées sont des colonnes réelles de la table(pas des expressions)toute colonne « not null » de la table représentée par la vueest présente dans la vue
Exempleinsert into emp10 (matr, nome, ...) values (1200, ’DUBOIS’, ...);
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 183
Vues
Option CHECK
Si la vue a été créée avec WITH CHECK OPTION, toutemodification au travers de la vue doit donner des données quipeuvent être affichées par la vue.
Exemple (Ceci est interdit)update emp10 set dept = 20;
La mise à jour est interdite car la vue ne contient que les employésdu departement 10.
Exemple (Rappel)create view emp10 asselect * from empwhere dept = 10;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 184
Vues
Utilité des vues
Dissocierla façon dont les utilisateurs voient les donnéesdu découpage en tables
On favorise ainsi l’indépendance entre les programmes et lesdonnéesSi un programme utilise des vues, on peut, par exemple,remplacer une table par 2 tables sans modifier le programmede consultation des données ; il suffit de modifier la définitiondes vues
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 185
Vues
Utilité des vues (2)
Simplifier la consultation de la base en enregistrant des selectcomplexesProtéger des données :
on peut donner accès à une vue, sans donner accès à la tablesous-jacenteune vue peut ne donner accès qu’à certaines colonnes ou lignesd’une tableles modifications des données peuvent être restreintes avec laclause WITH CHECK OPTION
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 186
Index
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 187
Index
Index
Un index utilise des techniques informatiques pour rendre trèsrapides les accès aux valeurs d’une colonne
Exempleselect * from empwhere nomE = ’Dupond’
Cet exemple est très long si la table emp contient des millionsde lignesUn index bien construit permet d’obtenir l’emplacement desinformations sur Dupond en quelques accès disques (pas plusde 4, même s’il y a des millions de lignes dans la table EMP)
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 188
Index
Création d’un index
DéfinitionCREATE [UNIQUE] INDEX nomIndexON table (col1, col2,...)
Exemplecreate index nomE on emp(nomE);
Le nom choisi doit être unique parmi tous les index de toutes lestables
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 189
Index
Utilisation d’un index
Après sa création un index est géré automatiquement par leSGBDIl est transparent pour l’utilisateur : celui-ci interroge la basede la même façon que si l’index n’existait pasLe SGBD peut utiliser un index s’il pense que la requête seraaccéléréeLes index ralentissent les modifications des données
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 190
Index
Suppression d’un index
DéfinitionDROP INDEX nomIndex
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 191
Génération de clés
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 192
Génération de clés
Génération de clés
Utilité :Les identifiants de lignes non significatifs sont préférablesLe plus simple est d’avoir des clés qui sont des nombres entiersLe problème : générer des entiers sans que 2 lignes puissentavoir le même identifiant, même en situation de concurrenceentre plusieurs transactions
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 193
Génération de clés
Une mauvaise solution
Prendre le plus grand nombre déjà utilisé dans la table commeidentifiant et ajouter 1
Exemple (En pseudo code)lock table;val = select max(cle) from table;insert into table values (val + 1,. . . );commit;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 194
Génération de clés
Inconvénients de la solution 1
Cette solution n’est pas performante :nécessite un accès à la baseil faut trouver le plus grandnécessite un blocage de la table pour éviter que 2 transactionsn’obtiennent la même valeur
Si on veut garder un historique, on peut se retrouver avec deslignes qui ont le même identificateur...... (si la ligne de plus grand identificateur est supprimée)
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 195
Génération de clés
Solution 2
Une table contient la prochaine clé à attribuer. La valeur estincrémentée à chaque nouvelle clé.Variantes
Variante 1 : Une seule table contient une seule valeur utiliséepour les identifiants de toutes les tablesVariante 2 : Une table par cléVariante 3 : Une seule table qui contient une ligne par tablequi a besoin d’une clé Quelle colonnes dans la table des clés ?
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 196
Génération de clés
Pseudo-code de la variante 1
Exemplelock table_cle;update table_cle set cle = cle + 1;val = select cle from table_cle;commit;insert into table values (val,...);commit;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 197
Génération de clés
Inconvénients de l’utilisation d’une table
Cette solution n’est pas très performante :nécessite un accès à la base (mais cette petite table estconservée en mémoire centrale dans le cache du SGBD)il est nécessaire de bloquer l’accès à la valeur
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 198
Génération de clés
Les séquences
Les versions actuelles des SGBD offrent des solutions qui nenécessitent pas d’accès aux données de la baseInconvénient : pas les mêmes solutions dans tous les SGBDsLes séquences sont disponibles avec Oracle, DB2 etPostgreSQL
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 199
Génération de clés
Créer une séquence
DéfinitionCREATE SEQUENCE nom_séquence [INCREMENT BY entier1][START WITH entier2]
Exemplecreate sequence seqdeptincrement by 10start with 10
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 200
Génération de clés
Utilisation des séquences
Deux pseudo-colonnes permettent d’utiliser les séquences :CURRVAL retourne la valeur couranteNEXTVAL incrémente la séquence et retourne la nouvellevaleur
Exempleinsert into dept(dept, nomd)values (seqdept.nextval, ’Finances’)
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 201
Génération de clés
CURRVAL et NEXTVAL
On ne peut utiliser CURRVAL qu’après avoir utiliséNEXTVAL au moins une fois dans la session de travailNEXTVAL modifie immédiatement la valeur future pour lesautres transactions, même s’il est lancé dans une transactionnon validéeLa valeur de CURRVAL ne dépend que des NEXTVAL lancésdans la même transaction
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 202
Génération de clés
Modification des séquences
DéfinitionALTER SEQUENCE nom_séquenceINCREMENT BY entier1alter sequence seqdeptincrement by 5
On ne peut modifier la valeur de départ
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 203
Génération de clés
Récupérer plusieurs identificateurs
Mettre un incrément supérieur à 1 permet d’obtenir plusieursidentificateurs en un seul appel pour obtenir de meilleuresperformancesPar exemple, si on veut des identificateurs pour des lignes defactures, on en a souvent besoin de plusieurs en même temps
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 204
Génération de clés
Informations sur les séquences pour Oracle
Afficher la valeur d’une séquence : select seqdept.currval fromdualTables du dictionnaire des données : USER_SEQUENCES etALL_SEQUENCES
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 205
Génération de clés
Autres solutions
DB2 et SQL Server ont une clause « IDENTITY » pour direqu’une colonne est un identifiant, avec une valeur qui estgénérée automatiquement par le SGBDMySQL permet de déclarer une colonneAUTO_INCREMENT
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 206
Génération de clés
Comparaison séquences et autres solutions
Les séquences sont souvent plus souples si on veut récupérerla valeur des identifiants pendant l’enregistrement des donnéesExemple : dans l’enregistrement d’une facture avec ses lignesde factures, on doit disposer de l’identifiant de la facture pourle mettre en clé étrangère dans les lignes de facture
Exemplefacture(id, nom_client, date_facture,...)ligne_facture(id_facture, nb_ligne, quantite,id_article)
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 207
Injection de code SQL
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 208
Injection de code SQL
Injection de code SQL
Tous les langages de programmation permettent de lancer desrequêtes SQL pour interagir avec une base de donnéesIl est possible de construire une requête SQL dont le textecontient une partie entrée par l’utilisateur (par concaténationde chaînes de caractères)Attention alors à l’injection de code SQL
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 209
Injection de code SQL
Exemple d’injection de code SQL
Un programme demande son nom et son mot de passe à unutilisateur, et les range dans 2 variables nom et mdpIl lance cette requête et accepte l’utilisateur si elle renvoiebien une ligne "select * from utilisateur where nom = ’" +nom + "’ and mdp = ’" + mdp + "’"Quel est le problème ?
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 210
Injection de code SQL
Le problème
Un pirate sait qu’un des utilisateurs autorisés s’appelleDupondIl saisit « Dupond’ – » pour le nom et « a » pour le mot depasseLa requête devient :select * from utilisateur where nom = ’Dupond’ –’ and mdp= ’a’Mais « – » indique un commentaire avec le SGBD utilisé ;donc la requête exécutée sera :select * from utilisateur where nom = ’Dupond’
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 211
Injection de code SQL
Les parades
Toujours vérifier la saisie d’un utilisateur avant de s’en servirpour construire une requête SQLPour l’exemple, il aurait suffit d’interdire le caractère « ’ » oude le doublerLes API fournies avec les langages pour accéder à une base dedonnées offrent des facilités pour se protéger contre l’injectionde code SQLAvec JDBC par exemple, il suffit d’utiliser des paramètres etPreparedStatement ; les caractères spéciaux comme « ’ » sontalors traités comme des caractères ordinaires
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 212
Procédures stockées
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 213
Procédures stockées
Procédures stockées
Les SGBD modernes fonctionnent en client/serveurChaque requête issue d’un client
transite sur le réseauest « compilée » lorsqu’elle arrive au serveur : celuici rechercheen particulier la meilleure façon de répondre à la requête
La compilation peut être une étape complexe et longue àtraiter et coûteuse en ressourcesLes procédures stockées sont des requêtes précompilées etstockées sur le serveur
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 214
Procédures stockées
3 étapes
1 Écriture du code de la procédure2 Compilation et enregistrement sur le serveur ; on donne un
nom à la procédure stockée3 Exécution de la procédure en passant au serveur le nom de la
procédure
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 215
Procédures stockées
Langages des procédures stockées
Les procédures stockées peuvent incluredes variables, des boucles et des testsplusieurs requêtes SQL
Le plus souvent, elles sont écrites dans un langage spécialPL/SQL pour OraclePSM pour la norme SQL (PL/SQL lui ressemble mais n’estpas vraiment compatible)Java pour les dernières versions d’Oracle
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 216
Procédures stockées
Avantages et inconvénients des procédures stockées
Améliorent les performances et diminuent le trafic sur leréseauEncapsulent les processus métier (boîtes noires)Mais les langages d’écriture et les appels de ces procédures nesont pas normalisésOn perd donc de la portabilité si on utilise des procéduresstockées
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 217
Procédures stockées
Syntaxe Officielle
DéfinitionCREATE PROCEDURE sp_name ([parameter[,...]])[characteristic ...] routine_body
CREATE FUNCTION sp_name ([parameter[,...]]) [RETURNStype][characteristic ...] routine_body
paramètre : [ IN | OUT | INOUT ] param_name typetype : Any valid MySQL data typecharacteristic: LANGUAGE SQL | [NOT] DETERMINISTIC |SQL SECURITY DEFINER | INVOKER | COMMENTstringroutine_body : Commande(s) SQL valide(s)
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 218
Procédures stockées
Exemple (1/2)
Exempledelimiter | – changement de délimiteurcreate procedure augmentation(in unDept integer, in pourcentage numeric, out cout numeric)beginselect sum(sal) * pourcentage / 100 into coutfrom EMPwhere dept = unDept;update EMP set sal = sal * (1 + pourcentage / 100)where dept = unDept;end|
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 219
Procédures stockées
Exemple (2/2)
Exempledelimiter ; – changement de délimiteurset @b=10; – initialisation d’une variablecall augmentation(@b,50,@a); – appel de la procédure stockéeselect @a;
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 220
Procédures stockées
Langage de programmation (1/2)
CALL : Appelle une procédure stockée précédemment définie,qui a été créée à l’aide de l’instruction CREATEPROCEDURE.BEGIN . . . END : Contient un groupe de plusieursinstructions en vue de leur exécution.DECLARE : Sert à définir des variables locales, desconditions, des routines de gestion et des curseurs.SET : sert à modifier les valeurs des variables locales et desvariables serveur globales.SELECT . . . INTO : Sert à stocker les colonnes indiquéesdirectement dans des variables.OPEN : Sert à ouvrir un curseur.FETCH : Extrait la ligne suivante à l’aide du curseur spécifiéet avance le curseur d’une ligne.CLOSE : Sert à fermer un curseur ouvert.Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 221
Procédures stockées
Langage de programmation (2/2)
IF : instruction conditionnelle IF-THEN-ELSE-END IF.CASE . . . : construction conditionnelle d’une instructionCASE.LOOP : Structure de boucle simple; la sortie se fait à l’aide del’instruction LEAVE.LEAVE : Sert à sortir des instructions IF, CASE, LOOP,REPEAT et WHILE.ITERATE : Utilisée dans les boucles pour recommencer audébut de la boucle.REPEAT : Boucle avec le test conditionnel à la fin.WHILE : Boucle avec le test conditionnel au début.RETURNS : Renvoie une valeur d’une fonction stockée.
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 222
Procédures stockées
Utilisation d’une procédure stockée
Depuis un langage de 3ème génération (C, Java,. . . ) on les appellepresque comme des procédures ou des fonctions du langage
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 223
Procédures stockées
Supprimer une procédure
DéfinitionDROP PROCEDURE nomProcédure
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 224
Procédures stockées
Compilation d’une procédure
Une procédure stockée est compilée dès sa créationLes erreurs éventuelles sont montrées par la commandeSHOW ERRORSSi le schéma de la base ou la répartition des données a changé,il faut recompiler une procédure pour optimiser son exécution :
DéfinitionALTER PROCEDURE nomProcédure COMPILE
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 225
Procédures stockées
Fonctions
Comme les procédures stockées mais elles renvoient une valeurElles peuvent être utilisées dans les requêtes SQL comme lesfonctions prédéfinies
Exemplecreate function euro_to_fr(somme numeric) returns numberbeginreturn somme * 6.55957;end|
Exempleselect nome, sal, euro_to_fr(sal) from emp
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 226
Triggers
Chapitre V
Vues, Index, Procédures stockées, ...
1 Vues2 Index3 Génération de clés4 Injection de code SQL5 Procédures stockées6 Triggers
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 227
Triggers
Triggers (déclencheurs)
Ils complètent les contraintes d’intégrité en permettant descontrôles et des traitements plus complexesCompilés et stockés sur le serveurSouvent écrits dans le même langage que les procéduresstockéesNormalisés en SQL3
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 228
Triggers
Trigger (déclencheurs)
DéfinitionCREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW trigger_stmt
La table associée doit être une table permanente (pas de vue)trigger_time est le moment d’action du trigger. Il peut êtreBEFORE (avant) ou AFTER (après)trigger_event indique le type de commande qui active letrigger. Il peut valoir INSERT, UPDATE ou DELETE. Il nepeut pas y avoir deux déclencheurs pour une même table avecles mêmes configurations de moment et de commande.trigger_stmt est la commande a exécuter lorsque ledéclencheur s’active. Si vous voulez utiliser plusieurscommandes, il faut utiliser BEGIN ... END. Cela vous permetaussi d’utiliser les mêmes codes que ceux utilisés dans desprocédures stockées.Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 229
Triggers
Exemple de trigger
ExempleCREATE TRIGGER totalSalaireAFTER UPDATE ON EMPFOR EACH ROWbeginif (NEW.sal != OLD.sal)thenupdate cumul set augmentation = augmentation + NEW.sal -OLD.salwhere cumul.matricule = NEW.emp.matr;end if;end|
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 230
Triggers
Utilisation de OLD et NEW
OLD.col_name faire référence à une colonne d’une ligneexistante avant sa modification ou son effacement.NEW.col_name faire référence à une colonne d’une ligneaprès insertion ou modification.
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 231
Triggers
Clause WHEN
ExempleCREATE TRIGGER totalSalaireAFTER UPDATE ON EMPFOR EACH ROWwhen (NEW.sal != OLD.sal)beginupdate cumul set augmentation = augmentation + NEW.sal -OLD.salwhere cumul.matricule = NEW.emp.matr;end|
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 232
Triggers
Restriction
Un trigger ne peut modifier par une requête SQL (update,insert, delete) les données de la table qui l’a déclenchéMais un trigger « for each row » peut modifier la ligne de latable concernée ; par exemple begin new.version = old.version+ 1; end
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 233
Triggers
Suppression de trigger
DéfinitionDROP TRIGGER tbl_name.trigger_name
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 234
Triggers
Ordres interdits
Les ordres COMMIT et ROLLBACK sont interdits dans untrigger
Bases de données – Licence pro Informatique Vues, Index, Procédures stockées, ... 235
Chapitre VI
Sécurité
1 Contrôle de l’accès à la base2 Gestion des accès concurrents
Bases de données – Licence pro Informatique Sécurité 236
Contrôle de l’accès à la base
Chapitre VI
Sécurité
1 Contrôle de l’accès à la base2 Gestion des accès concurrents
Bases de données – Licence pro Informatique Sécurité 237
Contrôle de l’accès à la base
Contrôle de l’accès à la base
Le contrôle de l’accès à la base est effectué en associant àchaque utilisateur
un nom de loginun mot de passe
Chaque utilisateur a des privilèges d’accès à la base : droit ounon
de créer des tables ou des vuesde lire ou de modifier des tables ou des vues...
Bases de données – Licence pro Informatique Sécurité 238
Contrôle de l’accès à la base
Propriétaire des données
Une table (et les données qu’elle contient) appartient à celuiqui l’a crééLe propriétaire d’une table peut donner à d’autres le droit detravailler avec sa tableLes vues permettent d’affiner les droits que l’on donne sur sespropres données : on peut donner des droits sur des vues etpas sur les tables sous-jacentes
Bases de données – Licence pro Informatique Sécurité 239
Contrôle de l’accès à la base
Accorder des droits sur des objets
DéfinitionGRANT privilège [(colonne,...)]ON table/vueTO utilisateur[WITH GRANT OPTION]
L’utilisateur qui reçoit le privilège pourra le donner à d’autresutilisateursLes privilèges :
SELECTINSERTUPDATE [(col1, col2,. . . )]DELETEALTERALL PRIVILEGES
Bases de données – Licence pro Informatique Sécurité 240
Contrôle de l’accès à la base
Accorder des droits (exemples)
Exemplegrant select on emp to clement;grant select, update on emp to clement, chatel;grant select on emp to public;
Changer son mot de passe :
Exemplegrant connect to bibi identified by motDePasse;
Bases de données – Licence pro Informatique Sécurité 241
Contrôle de l’accès à la base
Reprendre les droits
DéfinitionREVOKE privilège ON table/vue FROM utilisateur
Bases de données – Licence pro Informatique Sécurité 242
Gestion des accès concurrents
Chapitre VI
Sécurité
1 Contrôle de l’accès à la base2 Gestion des accès concurrents
Bases de données – Licence pro Informatique Sécurité 243
Gestion des accès concurrents
Gestion des accès concurrents
Un SGBD est prévu pour travailler avec de nombreuxutilisateurs/transactions... qui peuvent consulter et modifier en même temps lesmêmes données
Bases de données – Licence pro Informatique Sécurité 244
Gestion des accès concurrents
Quelques questions
A quel moment les modifications sont-elles vues par les autrestransactions ?Que se passe-t-il lorsque plusieurs transactions veulentmodifier les mêmes données ?Un ordre SQL (moyenne des salaires, par exemple) tient-ilcompte des modifications apportées par les autres transactionsvalidées pendant son exécution ?
Bases de données – Licence pro Informatique Sécurité 245
Gestion des accès concurrents
Problèmes liés aux accès concurrents
Il peut se produire des pertes de données quand plusieursprocessus veulent modifier les mêmes données en même tempsLes principaux cas d’école sont :
mise à jour perduelecture inconsistantelecture non reproductibleligne fantôme
Bases de données – Licence pro Informatique Sécurité 246
Gestion des accès concurrents
Mise à jour perdue
S = 500
TempsTransaction T1 Transaction T2s = Lire S
s = Lire S
Bases de données – Licence pro Informatique Sécurité 247
Gestion des accès concurrents
Mise à jour perdue
S = 1500
TempsTransaction T1 Transaction T2s = Lire S
s = Lire Ss = s + 1000
Bases de données – Licence pro Informatique Sécurité 248
Gestion des accès concurrents
Mise à jour perdue
S = 2500
TempsTransaction T1 Transaction T2s = Lire S
s = Lire Ss = s + 1000
s = s + 2000
Bases de données – Licence pro Informatique Sécurité 249
Gestion des accès concurrents
Mise à jour perdue
S = 1500
TempsTransaction T1 Transaction T2s = Lire S
s = Lire Ss = s + 1000
s = s + 2000Enregistrer s
Bases de données – Licence pro Informatique Sécurité 250
Gestion des accès concurrents
Mise à jour perdue
S = 2500
TempsTransaction T1 Transaction T2s = Lire S
s = Lire Ss = s + 1000
s = s + 2000Enregistrer s
Enregistrer s
Bases de données – Licence pro Informatique Sécurité 251
Gestion des accès concurrents
Éviter les mises à jour perdues
TempsTransaction T1 Transaction T2Bloquer Ss = Lire S
s = Lire S; attente ...s = s + 1000Enregistrer sDébloquer S ... s = Lire S
s = s + 2000Enregistrer s
Bases de données – Licence pro Informatique Sécurité 252
Gestion des accès concurrents
Problèmes liés aux blocages : interblocage
TempsTransaction T1 Transaction T2Bloquer A
Bloquer BBloquer B; Attente ...
Bloquer A; Attente ...
Bases de données – Licence pro Informatique Sécurité 253
Gestion des accès concurrents
Lecture inconsistante
TempsTransaction T1 Transaction T2V = 100
v = Lire V (100)ROLLBACK
Travaille avec v=100
Ce cas n’arrive pas si les modifications ne sont visibles par lesautres qu’après un COMMIT.
Bases de données – Licence pro Informatique Sécurité 254
Gestion des accès concurrents
Lecture inconsistante
TempsTransaction T1 Transaction T2Lire V
V = V + 100COMMIT
Lire V
Pour éviter cela, T1 devrait bloquer les données lues (V)
Bases de données – Licence pro Informatique Sécurité 255
Gestion des accès concurrents
Lignes fantômes
Ce problème survient quand une transaction n’a pas perçu lacréation d’une ligne par une autre transactionPar exemple, une transaction lit d’abord le nombre d’employéset lance ensuite la lecture d’informations sur chacun de cesemployésSi une autre transaction a ajouté des employés, le nombre luauparavant ne correspond plus à la réalité
Bases de données – Licence pro Informatique Sécurité 256
Gestion des accès concurrents
Lignes fantômes
TempsTransaction T1 Transaction T2Récupère le nombred’employés du dept 10 (35)
Ajoute un employéau dept 10COMMIT
Récupère les informationssur les employés dudept 10 (36 lignes)
Pour éviter cela, T1 devrait bloquer la table des employés au débutde la transaction (pas possible de bloquer des lignes qui n’existentpas !)
Bases de données – Licence pro Informatique Sécurité 257
Gestion des accès concurrents
Transactions sérialisables
On vient de voir que l’exécution de transactions entrelacéespeut provoquer des pertes de données ou de cohérencePour éviter ces problèmes, le SGBD doit s’arranger pour quel’exécution de plusieurs transactions entrelacées fournisse lesmêmes résultats que l’exécution des mêmes transactions lesunes à la suite des autres (dans un ordre quelconque)
Bases de données – Licence pro Informatique Sécurité 258
Gestion des accès concurrents
Moyens de sérialiser
Le moyen le plus courant s’appelle le verrouillage à 2 phases:
on doit bloquer un objet avant d’agir sur lui (lecture ouécriture)on ne peut plus faire de blocage après avoir débloquer un objet
On a donc 2 phases :1 acquisitions des verrous2 abandons des verrous (en pratique, au COMMIT ou
ROLLBACK)
Bases de données – Licence pro Informatique Sécurité 259
Gestion des accès concurrents
Inter-blocage avec le verrouillage à 2 phases
Les situations d’entrelacement des transactions qui auraientprovoqué des problèmes vont se traduire par des attentes oudes inter-blocagesEn cas d’inter-blocage, des transactions sont annulées etredémarrées
Bases de données – Licence pro Informatique Sécurité 260
Gestion des accès concurrents
Estampillage
L’estampillage est une autre stratégie que le verrouillage à 2phases pour assurer la sérialisation des transactionsL’ancienneté des transactions est repérée par une estampilledonnée à la création de la transaction (un nombre incrémentéà chaque attribution)Chaque donnée accédée par une transaction reçoit l’estampillede cette transactionLes algorithmes qui utilisent l’estampillage assurent quel’exécution concurrente des transactions sera équivalente àl’exécution séquentielle de ces transactions dans l’ordre de leurestampille
Bases de données – Licence pro Informatique Sécurité 261
Gestion des accès concurrents
Idée pour les algorithmes d’estampillage
Une transaction T ne peut accéder à une donnée si cettedonnée a déjà été accédée par une transaction plus jeune(donc d’estampille plus grande que celle de T)La transaction « trop vieille » T est annulée si elle s’est faitedoubler par une transaction plus jeuneElle est relancée avec une estampille plus grande ; plus jeune,elle a plus de chance de pouvoir accéder à la donnée enpassant « après »
Bases de données – Licence pro Informatique Sécurité 262
Gestion des accès concurrents
Résultat de l’estampillage
Cet algorithme est trop restrictif et ne va autoriser que peud’exécutions parallèles des transactionsLa transaction redémarrée va peut-être entrer en conflit avecune autre transaction plus jeuneD’autres algorithmes d’estampillage moins simplistespermettent d’améliorer le parallélisme, en particulier endistinguant les accès en lecture et les accès en écritureMalgré tout, ce type de traitement est souvent trop restrictifet nuit à la concurrence
Bases de données – Licence pro Informatique Sécurité 263
Gestion des accès concurrents
Autres stratégies
Pour améliorer les performances dans des situations de forteconcurrence, les SGBD offrent la possibilité d’être pluspermissif et de ne pas sérialiser les transactions
Bases de données – Licence pro Informatique Sécurité 264
Gestion des accès concurrents
Niveaux d’isolation des transactions (InnoDB)
DéfinitionSET TRANSACTION ISOLATION LEVELSERIALIZABLE | READ COMMITED | READ-UNCOMMITTED |READ-COMMITTED | REPEATABLE-READ
SERIALIZABLE : les transactions s’exécutent totalement isoléesdes autres transactions et comme si elles s’exécutaient les unesaprès les autres
Bases de données – Licence pro Informatique Sécurité 265
Gestion des accès concurrents
Niveaux d’isolation des transactions (InnoDB)
DéfinitionSET TRANSACTION ISOLATION LEVELSERIALIZABLE | READ COMMITED | READ-UNCOMMITTED |READ-COMMITTED | REPEATABLE-READ
READ COMMITED : les transactions ne voient les modificationsdes autres transactions qu’après les commit ; empêche les lecturesinconsistantes mais pas les lectures non répétitives ni les lignesfantômes C’est l’isolation par défaut d’Oracle et de beaucoup deSGBD car c’est un bon compromis entre sécurité et performances
Bases de données – Licence pro Informatique Sécurité 266
Gestion des accès concurrents
Niveaux d’isolation des transactions (InnoDB)
DéfinitionSET TRANSACTION ISOLATION LEVELSERIALIZABLE | READ COMMITED | READ-UNCOMMITTED |READ-COMMITTED | REPEATABLE-READ
READ UNCOMMITED : les transactions voient les modificationsavant même le commit
Bases de données – Licence pro Informatique Sécurité 267
Gestion des accès concurrents
Niveaux d’isolation des transactions (InnoDB)
DéfinitionSET TRANSACTION ISOLATION LEVELSERIALIZABLE | READ COMMITED | READ-UNCOMMITTED |READ-COMMITTED | REPEATABLE-READ
REPEATABLE READ : empêche les lectures non répétitives maispas les lignes fantômes
Bases de données – Licence pro Informatique Sécurité 268
Gestion des accès concurrents
En résumé...
Niveaux par isolation décroissante :SERIALIZABLE (souhaitable, mais coûteux car provoque desblocages de tables)REPEATABLE READREAD COMMITED (souvent un bon compromis)READ UNCOMMITED (très rarement utilisé)
Bases de données – Licence pro Informatique Sécurité 269
Gestion des accès concurrents
Traitement des accès concurrents par les SGBD
Le but : favoriser au maximum les accès concurrents pourpermettre l’exécution du plus grand nombre de transactionsdans un temps donné (argument commercial important)Tous les SGBDs n’ont pas les mêmes solutions pour atteindrece but
Bases de données – Licence pro Informatique Sécurité 270
Gestion des accès concurrents
Durée des blocages
Un blocage n’a lieu que le temps d’une transaction
Bases de données – Licence pro Informatique Sécurité 271
Gestion des accès concurrents
Accès concurrents
Pas de verrou pour effectuer une lectureLes lectures ne sont pas bloquées par les écritures, etvice-versaUne transaction est bloquée si elle veut modifier des donnéesqui sont en cours de modification par une autre transactionLa granularité minimum de blocage est la ligne (dépand dumoteur (InnoBD, MyISAM, ...)
Bases de données – Licence pro Informatique Sécurité 272
Gestion des accès concurrents
Autre façon de traiter les accès concurrents
Beaucoup de SGBD bloquent les données luesce blocage n’interdit pas leur lecture par d’autres transactionsmais interdit leur modification
Une écriture bloque les données modifiéeselles ne peuvent être modifiées par d’autres transactions(comme avec Oracle)mais elles ne peuvent pas non plus être lues par d’autrestransactions
Bases de données – Licence pro Informatique Sécurité 273
Gestion des accès concurrents
Blocages explicites et implicites
Des blocages sont effectués implicitement par certainescommandes (en particulier UPDATE, INSERT et DELETE)Si le comportement par défaut du SGBD ne convient pas pourun traitement, on peut effectuer des blocages explicites deslignes ou des tablesLes inter-blocages sont détectés par le SGBD qui annule undes ordres qui a provoqué l’inter-blocage
Bases de données – Licence pro Informatique Sécurité 274
Gestion des accès concurrents
Granularité des blocages
2 types de blocages :au niveau d’une tableau niveau d’une ligne
Dans certains SGBDs le blocage d’une ligne implique leblocage de toute la page/bloc (plusieurs lignes) qui contientla ligneCertains SGBD transforment de trop nombreux blocages delignes d’une table en un blocage de toute la table
Bases de données – Licence pro Informatique Sécurité 275
Gestion des accès concurrents
Réponses à quelques questions
A quel moment les modifications sont-elles vues par les autrestransactions ?après la validation de la transaction (COMMIT)Que se passe-t-il lorsque plusieurs transactions veulentmodifier les mêmes données ?blocages implicites du SGBD ⇒ attente
Bases de données – Licence pro Informatique Sécurité 276
Gestion des accès concurrents
Réponses à quelques questions
Quand un ordre SQL tient compte de plusieurs lignes(moyenne des salaires, par exemple), cet ordre tient-il comptedes modifications apportées par les autres transactionspendant l’exécution de l’ordre ?Ca dépend du SGBD. Avec Mysql, non
Bases de données – Licence pro Informatique Sécurité 277