Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction aux bases de donnéesFormation continue
Idir AIT SADOUNE
[email protected]École Supérieure d’Électricité
Département InformatiqueGif sur Yvette
2012/2013
1/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
2/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
2/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
2/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
2/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
2/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
Données, Bases de données et SGBD
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
3/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
Données, Bases de données et SGBD
Données et bases de données
DonnéeUne donnée est une information quelconque
Ex. "Cette personne s’appelle Jean"
Une donnée est une relation entre des informationEx. "Jean enseigne les mathématiques"
Base de donnée (BD)Une BD est un ensemble volumineux d’information mémorisé de manièrepermanente
Une BD est un gros ensemble d’informations structurées mémorisées sur unsupport permanent.
4/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
Données, Bases de données et SGBD
Données et bases de données
DonnéeUne donnée est une information quelconque
Ex. "Cette personne s’appelle Jean"
Une donnée est une relation entre des informationEx. "Jean enseigne les mathématiques"
Base de donnée (BD)Une BD est un ensemble volumineux d’information mémorisé de manièrepermanente
Une BD est un gros ensemble d’informations structurées mémorisées sur unsupport permanent.
4/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
Données, Bases de données et SGBD
Données et bases de données
DonnéeUne donnée est une information quelconque
Ex. "Cette personne s’appelle Jean"
Une donnée est une relation entre des informationEx. "Jean enseigne les mathématiques"
Base de donnée (BD)Une BD est un ensemble volumineux d’information mémorisé de manièrepermanente
Une BD est un gros ensemble d’informations structurées mémorisées sur unsupport permanent.
4/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
Données, Bases de données et SGBD
Données et bases de données
DonnéeUne donnée est une information quelconque
Ex. "Cette personne s’appelle Jean"
Une donnée est une relation entre des informationEx. "Jean enseigne les mathématiques"
Base de donnée (BD)Une BD est un ensemble volumineux d’information mémorisé de manièrepermanente
Une BD est un gros ensemble d’informations structurées mémorisées sur unsupport permanent.
4/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
5/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Système de Gestion de Bases de Données (SGBD)
Gestion d’une BDUne organisation basée basée sur des fichiers ?
lourdeur d’accès aux donnéesmanque de sécuritéabsence de contrôle de concurrence
SolutionUn système chargé de gérer les fichiers constituant une base de données, de prendreen charge les fonctionnalités de protection et de sécurité et de fournir les différentstypes d’interface nécessaires à l’accès aux données
Système de Gestion de Bases de Données (SGBD)Un Système de Gestion de Bases de Données (SGBD) est un logiciel de haut niveauqui permet de manipuler les informations stockées dans une base de données.
6/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Système de Gestion de Bases de Données (SGBD)
Gestion d’une BDUne organisation basée basée sur des fichiers ?
lourdeur d’accès aux donnéesmanque de sécuritéabsence de contrôle de concurrence
SolutionUn système chargé de gérer les fichiers constituant une base de données, de prendreen charge les fonctionnalités de protection et de sécurité et de fournir les différentstypes d’interface nécessaires à l’accès aux données
Système de Gestion de Bases de Données (SGBD)Un Système de Gestion de Bases de Données (SGBD) est un logiciel de haut niveauqui permet de manipuler les informations stockées dans une base de données.
6/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Système de Gestion de Bases de Données (SGBD)
Gestion d’une BDUne organisation basée basée sur des fichiers ?
lourdeur d’accès aux donnéesmanque de sécuritéabsence de contrôle de concurrence
SolutionUn système chargé de gérer les fichiers constituant une base de données, de prendreen charge les fonctionnalités de protection et de sécurité et de fournir les différentstypes d’interface nécessaires à l’accès aux données
Système de Gestion de Bases de Données (SGBD)Un Système de Gestion de Bases de Données (SGBD) est un logiciel de haut niveauqui permet de manipuler les informations stockées dans une base de données.
6/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Système de Gestion de Bases de Données (SGBD)
Gestion d’une BDUne organisation basée basée sur des fichiers ?
lourdeur d’accès aux donnéesmanque de sécuritéabsence de contrôle de concurrence
SolutionUn système chargé de gérer les fichiers constituant une base de données, de prendreen charge les fonctionnalités de protection et de sécurité et de fournir les différentstypes d’interface nécessaires à l’accès aux données
Système de Gestion de Bases de Données (SGBD)Un Système de Gestion de Bases de Données (SGBD) est un logiciel de haut niveauqui permet de manipuler les informations stockées dans une base de données.
6/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Utilisation d’un SGBD
Éléments essentiels pour un SGBDDéfinition du schéma de données en utilisant les modèles de données du SGBD
Le modèle conceptuel : la description du système d’information (Modèleentité association)Le modèle logique : interface avec le SGBD (définition des données et desopérations sur les données)Le modèle physique : fichiers.
Opérations sur les données : recherche, mises-à-jour, etc.Création, modification, destruction et recherche.
Partager les données entre plusieurs utilisateurs. (Mécanisme de transaction).Gérer les conflits si les deux font des mises-à-jour.Offrir un mécanisme de retour en arrièreDonner une image cohérente des données si l’un fait des requêtes etl’autre des mises-à-jour.
Optimiser les performances, par le réglage de l’organisation physique desdonnées.
L’organisation physique des données7/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Utilisation d’un SGBD
Éléments essentiels pour un SGBDDéfinition du schéma de données en utilisant les modèles de données du SGBD
Le modèle conceptuel : la description du système d’information (Modèleentité association)Le modèle logique : interface avec le SGBD (définition des données et desopérations sur les données)Le modèle physique : fichiers.
Opérations sur les données : recherche, mises-à-jour, etc.Création, modification, destruction et recherche.
Partager les données entre plusieurs utilisateurs. (Mécanisme de transaction).Gérer les conflits si les deux font des mises-à-jour.Offrir un mécanisme de retour en arrièreDonner une image cohérente des données si l’un fait des requêtes etl’autre des mises-à-jour.
Optimiser les performances, par le réglage de l’organisation physique desdonnées.
L’organisation physique des données7/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Utilisation d’un SGBD
Éléments essentiels pour un SGBDDéfinition du schéma de données en utilisant les modèles de données du SGBD
Le modèle conceptuel : la description du système d’information (Modèleentité association)Le modèle logique : interface avec le SGBD (définition des données et desopérations sur les données)Le modèle physique : fichiers.
Opérations sur les données : recherche, mises-à-jour, etc.Création, modification, destruction et recherche.
Partager les données entre plusieurs utilisateurs. (Mécanisme de transaction).Gérer les conflits si les deux font des mises-à-jour.Offrir un mécanisme de retour en arrièreDonner une image cohérente des données si l’un fait des requêtes etl’autre des mises-à-jour.
Optimiser les performances, par le réglage de l’organisation physique desdonnées.
L’organisation physique des données7/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Utilisation d’un SGBD
Éléments essentiels pour un SGBDDéfinition du schéma de données en utilisant les modèles de données du SGBD
Le modèle conceptuel : la description du système d’information (Modèleentité association)Le modèle logique : interface avec le SGBD (définition des données et desopérations sur les données)Le modèle physique : fichiers.
Opérations sur les données : recherche, mises-à-jour, etc.Création, modification, destruction et recherche.
Partager les données entre plusieurs utilisateurs. (Mécanisme de transaction).Gérer les conflits si les deux font des mises-à-jour.Offrir un mécanisme de retour en arrièreDonner une image cohérente des données si l’un fait des requêtes etl’autre des mises-à-jour.
Optimiser les performances, par le réglage de l’organisation physique desdonnées.
L’organisation physique des données7/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Introduction
SGBD
Utilisation d’un SGBD
Éléments essentiels pour un SGBDDéfinition du schéma de données en utilisant les modèles de données du SGBD
Le modèle conceptuel : la description du système d’information (Modèleentité association)Le modèle logique : interface avec le SGBD (définition des données et desopérations sur les données)Le modèle physique : fichiers.
Opérations sur les données : recherche, mises-à-jour, etc.Création, modification, destruction et recherche.
Partager les données entre plusieurs utilisateurs. (Mécanisme de transaction).Gérer les conflits si les deux font des mises-à-jour.Offrir un mécanisme de retour en arrièreDonner une image cohérente des données si l’un fait des requêtes etl’autre des mises-à-jour.
Optimiser les performances, par le réglage de l’organisation physique desdonnées.
L’organisation physique des données7/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Principes généraux
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
8/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Principes généraux
Introduction
le modèle Entité/Association (E/A) est utilisé pour la conception de bases dedonnées.
le modèle E/A a pour caractéristiques d’être simple et puissant pour représenterdes structures relationnelles (représentation graphique).
le schéma E/A décrit l’application visée (une abstraction d’un domaine d’étude)
une abstraction consiste à choisir certains aspects de la réalité perçue (et doncà éliminer les autres)
9/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Principes généraux
Introduction
le modèle Entité/Association (E/A) est utilisé pour la conception de bases dedonnées.
le modèle E/A a pour caractéristiques d’être simple et puissant pour représenterdes structures relationnelles (représentation graphique).
le schéma E/A décrit l’application visée (une abstraction d’un domaine d’étude)
une abstraction consiste à choisir certains aspects de la réalité perçue (et doncà éliminer les autres)
9/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Principes généraux
Présentation informelle
des entités, représentées par des rectangles,
des associations entre entités représentées par des liens entre ces rectangles.
10/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
11/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Entités, attributs et identifiants
on désigne par entité tout objet identifiable et pertinent pour l’application.
les entités sont caractérisées par des propriétés (attributs).un attribut est désigné par un nom et prend ses valeurs dans un domaineénumérable comme les entiers, les chaînes de caractères, les dates, etc.
Définition formelle d’une entitéLe type d’une entité est composé des éléments suivants :
son nom ;la liste de ses attributs avec, optionnellement le domaine où l’attribut prendses valeurs ;l’indication du (ou des) attribut(s) permettant d’identifier l’entité.
12/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Entités, attributs et identifiants
on désigne par entité tout objet identifiable et pertinent pour l’application.
les entités sont caractérisées par des propriétés (attributs).un attribut est désigné par un nom et prend ses valeurs dans un domaineénumérable comme les entiers, les chaînes de caractères, les dates, etc.
Définition formelle d’une entitéLe type d’une entité est composé des éléments suivants :
son nom ;la liste de ses attributs avec, optionnellement le domaine où l’attribut prendses valeurs ;l’indication du (ou des) attribut(s) permettant d’identifier l’entité.
12/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Identifiants
Définition formelle d’une cléSoit E un type d’entité et A l’ensemble des attributs de E . Une clé de E est unsous-ensemble minimal de A permettant d’identifier de manière unique une entitéparmi n’importe quelle extension de E
Il est possible d’avoir plusieurs clés pour un même ensemble d’entités. Dans ce cas onen choisit une comme clé primaire, et les autres comme clés secondaires.
Les caractéristiques d’une bonne clé primaire sont les suivantes :sa valeur est connue pour toute entité ;on ne doit jamais avoir besoin de la modifier ;enfin, pour des raisons de performance, sa taille de stockage doit être laplus petite possible.
13/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Identifiants
Définition formelle d’une cléSoit E un type d’entité et A l’ensemble des attributs de E . Une clé de E est unsous-ensemble minimal de A permettant d’identifier de manière unique une entitéparmi n’importe quelle extension de E
Il est possible d’avoir plusieurs clés pour un même ensemble d’entités. Dans ce cas onen choisit une comme clé primaire, et les autres comme clés secondaires.
Les caractéristiques d’une bonne clé primaire sont les suivantes :sa valeur est connue pour toute entité ;on ne doit jamais avoir besoin de la modifier ;enfin, pour des raisons de performance, sa taille de stockage doit être laplus petite possible.
13/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations binaires
La représentation (et le stockage) d’entités indépendantes les unes des autres est depeu d’utilité
Prenons l’exemple de l’association représentant le fait qu’un réalisateur met en scènedes films.
certains réalisateurs mettent en scène plusieurs films ;
inversement, un film est mis en scène par au plus un réalisateur.
14/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations binaires
La représentation (et le stockage) d’entités indépendantes les unes des autres est depeu d’utilité
Prenons l’exemple de l’association représentant le fait qu’un réalisateur met en scènedes films.
certains réalisateurs mettent en scène plusieurs films ;
inversement, un film est mis en scène par au plus un réalisateur.
14/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations binaires
DéfinitionsUne association binaire entre les ensembles d’entités E1 et E2, est un ensemblede couples (e1, e2), avec e1 ∈ E1 et e2 ∈ E2.
Soit une association (E1,E2) entre deux types d’entités. La cardinalité del’association pour Ei , i ∈ {1, 2}, est une paire [min,max ] telle que :
Le symbole max (min) désigne le nombre maximal (minimal) de fois oùune une entité ei de E1 peut intervenir dans l’association.
La clé d’une association (binaire) entre un type d’entité E1 et un type d’entité E2est le couple constitué de la clé c1 de E1 et de la clé c2 de E2 .
15/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations binaires
DéfinitionsUne association binaire entre les ensembles d’entités E1 et E2, est un ensemblede couples (e1, e2), avec e1 ∈ E1 et e2 ∈ E2.
Soit une association (E1,E2) entre deux types d’entités. La cardinalité del’association pour Ei , i ∈ {1, 2}, est une paire [min,max ] telle que :
Le symbole max (min) désigne le nombre maximal (minimal) de fois oùune une entité ei de E1 peut intervenir dans l’association.
La clé d’une association (binaire) entre un type d’entité E1 et un type d’entité E2est le couple constitué de la clé c1 de E1 et de la clé c2 de E2 .
15/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations binaires
DéfinitionsUne association binaire entre les ensembles d’entités E1 et E2, est un ensemblede couples (e1, e2), avec e1 ∈ E1 et e2 ∈ E2.
Soit une association (E1,E2) entre deux types d’entités. La cardinalité del’association pour Ei , i ∈ {1, 2}, est une paire [min,max ] telle que :
Le symbole max (min) désigne le nombre maximal (minimal) de fois oùune une entité ei de E1 peut intervenir dans l’association.
La clé d’une association (binaire) entre un type d’entité E1 et un type d’entité E2est le couple constitué de la clé c1 de E1 et de la clé c2 de E2 .
15/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Le modèle
Associations généralisées
DéfinitionsUne association n-aire entre n types d’entités E1,E2, ...,En est un ensemble den-uplets (e1, e2, ..., en) où chaque ei appartient à Ei
16/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Avantages et inconvénients
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
17/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Avantages et inconvénients
Avantages et inconvénients du modèle E/A
AvantagesLe modèle Entité/Association est simple et pratique.
Il n’y a que 3 concepts : entités, associations et attributs.
Il est approprié à une représentation graphique intuitive, même s’il existebeaucoup de conventions.
Il permet de modéliser rapidement des structures pas trop complexes.
Inconvénientsnon-déterminisme : il n’y a pas de règle absolue pour déterminer ce qui estentité, attribut ou relation.
il est difficile d’exprimer des contraintes d’intégrité, des structures complexes.
il ne propose pas d’opérations sur les données.
18/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle Entité/Association
Avantages et inconvénients
Avantages et inconvénients du modèle E/A
AvantagesLe modèle Entité/Association est simple et pratique.
Il n’y a que 3 concepts : entités, associations et attributs.
Il est approprié à une représentation graphique intuitive, même s’il existebeaucoup de conventions.
Il permet de modéliser rapidement des structures pas trop complexes.
Inconvénientsnon-déterminisme : il n’y a pas de règle absolue pour déterminer ce qui estentité, attribut ou relation.
il est difficile d’exprimer des contraintes d’intégrité, des structures complexes.
il ne propose pas d’opérations sur les données.
18/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
19/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Introduction
Un modèle de données définit un mode de représentation de l’information selon troiscomposantes :
Des structures de données (Langage de Définition de Données (DDL)).
Des contraintes qui permettent de spécifier les règles que doit respecter unebase de données (Langage de Définition de Données (DDL))
Des opérations pour manipuler les données, en interrogation et en mise à jour(Langage de Manipulation de Données (DML))
Un modèle de données doit d’être indépendant de la représentation physique.
Le modèle relationnel offre une totale indépendance entre les représentationslogique et physique.
20/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Introduction
Un modèle de données définit un mode de représentation de l’information selon troiscomposantes :
Des structures de données (Langage de Définition de Données (DDL)).
Des contraintes qui permettent de spécifier les règles que doit respecter unebase de données (Langage de Définition de Données (DDL))
Des opérations pour manipuler les données, en interrogation et en mise à jour(Langage de Manipulation de Données (DML))
Un modèle de données doit d’être indépendant de la représentation physique.
Le modèle relationnel offre une totale indépendance entre les représentationslogique et physique.
20/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Définition d’un schéma relationnel
Le modèle relationnel est un modèle très simple basé sur une seule structure, larelation.
Un schéma de relation est simplement un nom suivi de la liste des attributs,chaque attribut étant associé à son domaine.
La syntaxe est : R(A1 : D1,A2 : D2, ...,An : Dn) où les Ai sont les nomsd’attributs et les Di les domaines.
L’arité d’une relation est le nombre de ses attributs
Une instance d’une relation R, ou simplement relation se définitmathématiquement comme un sous ensemble fini du produit cartésien desdomaines des attributs de R.
La clé d’une relation est le plus petit sous-ensemble des attributs qui permetd’identifier chaque ligne de manière unique.
Un tuple est une liste de n valeurs (v1, v2, ..., vn) où chaque valeur vi est lavaleur d’un attribut Ai de domaine Di : vi ∈ Di .
21/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Définition d’un schéma relationnel
Le modèle relationnel est un modèle très simple basé sur une seule structure, larelation.
Un schéma de relation est simplement un nom suivi de la liste des attributs,chaque attribut étant associé à son domaine.
La syntaxe est : R(A1 : D1,A2 : D2, ...,An : Dn) où les Ai sont les nomsd’attributs et les Di les domaines.
L’arité d’une relation est le nombre de ses attributs
Une instance d’une relation R, ou simplement relation se définitmathématiquement comme un sous ensemble fini du produit cartésien desdomaines des attributs de R.
La clé d’une relation est le plus petit sous-ensemble des attributs qui permetd’identifier chaque ligne de manière unique.
Un tuple est une liste de n valeurs (v1, v2, ..., vn) où chaque valeur vi est lavaleur d’un attribut Ai de domaine Di : vi ∈ Di .
21/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Définition
Définition d’un schéma relationnel
Film (titre : string, année : number, genre : string)(’Alien’, 1979, ’Science-Fiction’)(’Vertigo’, 1958, ’Suspense’)(’Volte-face’, 1997, ’Thriller’)(’Pulp Fiction’, 1995, ’Policier’)
22/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
23/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Principe
On passe d’un modèle disposant de deux structures (entités et associations) à unmodèle disposant d’une seule structure (relations).
24/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
EntitésOn crée une relation de même nom que l’entité.
Chaque propriété de l’entité, y compris l’identifiant, devient un attribut de larelation.
Les attributs de l’identifiant constituent la clé de la relation.
Film (idFilm, titre, année, genre, résumé)Artiste (idArtiste, nom, prénom, annéeNaissance)Internaute (email, nom, prénom, région)Pays (code, nom, langue)
25/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
EntitésOn crée une relation de même nom que l’entité.
Chaque propriété de l’entité, y compris l’identifiant, devient un attribut de larelation.
Les attributs de l’identifiant constituent la clé de la relation.
Film (idFilm, titre, année, genre, résumé)Artiste (idArtiste, nom, prénom, annéeNaissance)Internaute (email, nom, prénom, région)Pays (code, nom, langue)
25/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
Associations de un à plusieursSoit une association de un à plusieurs 1 entre A et B. Le passage au modèle logiquesuit les règles suivantes :
On crée les relations RA et RB correspondant respectivement aux entités A et B.
L’identifiant de B devient un attribut de RA.
Film (idFilm, titre, année, genre, résumé, idArtiste, codePays)Artiste (idArtiste, nom, prénom, annéeNaissance)Pays (codePays, nom, langue)
26/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
Associations de un à plusieursSoit une association de un à plusieurs 1 entre A et B. Le passage au modèle logiquesuit les règles suivantes :
On crée les relations RA et RB correspondant respectivement aux entités A et B.
L’identifiant de B devient un attribut de RA.
Film (idFilm, titre, année, genre, résumé, idArtiste, codePays)Artiste (idArtiste, nom, prénom, annéeNaissance)Pays (codePays, nom, langue)
26/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
Associations de plusieurs à plusieursSoit une association n-m entre A et B.
On crée les relations RA et RB correspondant respectivement aux entités A et B.
On crée une relation RA−B pour l’association.
La clé de RA et la clé de RB deviennent des attributs de RA−B .
La clé de cette relation est la concaténation des clés des relations RA et RB .
Les propriétés de l’association deviennent des attributs de RA−B
Film (idFilm, titre, année, genre, résumé, idMES, codePays)Artiste (idArtiste, nom, prénom, annéeNaissance)Role (idFilm, idArtiste, nomRôle)
27/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
Associations de plusieurs à plusieursSoit une association n-m entre A et B.
On crée les relations RA et RB correspondant respectivement aux entités A et B.
On crée une relation RA−B pour l’association.
La clé de RA et la clé de RB deviennent des attributs de RA−B .
La clé de cette relation est la concaténation des clés des relations RA et RB .
Les propriétés de l’association deviennent des attributs de RA−B
Film (idFilm, titre, année, genre, résumé, idMES, codePays)Artiste (idArtiste, nom, prénom, annéeNaissance)Role (idFilm, idArtiste, nomRôle)
27/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Passage d’un schéma E/A à un schéma relationnel
Règles générales
Points non abordésDépendances fonctionnelles
Normalisation du schéma relationnel
Différentes formes normales (1 FN, 2 FN, 3 FN, ...)
28/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
29/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Les types SQL
30/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Création des tables/relations
CREATE TABLECREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20),motDePasse VARCHAR (60) NOT NULL,anneeNaiss DECIMAL (4))
CREATE TABLE Cinéma (nom VARCHAR (50) NOT NULL,adresse VARCHAR (50) DEFAULT ’Inconnue’)
31/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Création des tables/relations
CREATE TABLECREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20),motDePasse VARCHAR (60) NOT NULL,anneeNaiss DECIMAL (4))
CREATE TABLE Cinéma (nom VARCHAR (50) NOT NULL,adresse VARCHAR (50) DEFAULT ’Inconnue’)
31/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Les règles : contraintes d’intégritéUn attribut doit toujours avoir une valeur.
Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation.
Un attribut dans une table est liée à la clé primaire d’une autre table (intégritéréférentielle).
La valeur d’un attribut doit être unique au sein de la relation.
Enfin toute règle s’appliquant à la valeur d’un attribut.
32/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Clé primaire : PRIMARY KEYCREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20),motDePasse VARCHAR (60) NOT NULL,anneeNaiss DECIMAL (4),PRIMARY KEY (email))
Clé secondaire : UNIQUECREATE TABLE Artiste (id INTEGER NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20) NOT NULL,anneeNaiss INTEGER,PRIMARY KEY (id),UNIQUE (nom, prenom))
CREATE TABLE Cinema ( nom VARCHAR (20) NOT NULL,adresse VARCHAR (50) UNIQUE,PRIMARY KEY (nom))
33/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Clé primaire : PRIMARY KEYCREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20),motDePasse VARCHAR (60) NOT NULL,anneeNaiss DECIMAL (4),PRIMARY KEY (email))
Clé secondaire : UNIQUECREATE TABLE Artiste (id INTEGER NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20) NOT NULL,anneeNaiss INTEGER,PRIMARY KEY (id),UNIQUE (nom, prenom))
CREATE TABLE Cinema ( nom VARCHAR (20) NOT NULL,adresse VARCHAR (50) UNIQUE,PRIMARY KEY (nom))
33/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Clé primaire : PRIMARY KEYCREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20),motDePasse VARCHAR (60) NOT NULL,anneeNaiss DECIMAL (4),PRIMARY KEY (email))
Clé secondaire : UNIQUECREATE TABLE Artiste (id INTEGER NOT NULL,
nom VARCHAR (20) NOT NULL,prenom VARCHAR (20) NOT NULL,anneeNaiss INTEGER,PRIMARY KEY (id),UNIQUE (nom, prenom))
CREATE TABLE Cinema ( nom VARCHAR (20) NOT NULL,adresse VARCHAR (50) UNIQUE,PRIMARY KEY (nom))
33/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Clé étrangère : FOREIGN KEYCREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,annee INTEGER NOT NULL,idMES INTEGER,codePays INTEGER,PRIMARY KEY (idFilm),FOREIGN KEY (idMES) REFERENCES Artiste,FOREIGN KEY (codePays) REFERENCES Pays)
CREATE TABLE Film (idFilm INTEGER NOT NULL,titre VARCHAR (50) NOT NULL,annee INTEGER NOT NULL,idMES INTEGER,codePays INTEGER,PRIMARY KEY (idFilm),FOREIGN KEY (idMES) REFERENCES Artiste
ON DELETE SET NULL,FOREIGN KEY (codePays) REFERENCES Pays)
34/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Clé étrangère : FOREIGN KEYCREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,annee INTEGER NOT NULL,idMES INTEGER,codePays INTEGER,PRIMARY KEY (idFilm),FOREIGN KEY (idMES) REFERENCES Artiste,FOREIGN KEY (codePays) REFERENCES Pays)
CREATE TABLE Film (idFilm INTEGER NOT NULL,titre VARCHAR (50) NOT NULL,annee INTEGER NOT NULL,idMES INTEGER,codePays INTEGER,PRIMARY KEY (idFilm),FOREIGN KEY (idMES) REFERENCES Artiste
ON DELETE SET NULL,FOREIGN KEY (codePays) REFERENCES Pays)
34/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
CREATE TABLE Salle (nomCinema VARCHAR (30) NOT NULL,no INTEGER NOT NULL,capacite INTEGER NOT NULL,PRIMARY KEY (nomCinema, no),FOREIGN KEY (nomCinema) REFERENCES CinemaON DELETE CASCADEON UPDATE CASCADE)
35/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
CHECK (condition)CREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,annee INTEGER NOT NULL
CHECK (annee BETWEEN 1890 AND 2000) NOT NULL,genre VARCHAR (10)
CHECK (genre IN (’Histoire’,’Western’,’Drame’)),idMES INTEGER,codePays INTEGER,PRIMARY KEY (idFilm),FOREIGN KEY (idMES) REFERENCES Artiste,FOREIGN KEY (codePays) REFERENCES Pays)
36/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
Contraintes
Les points non abordés : Modification du schémaModification des attributs
Création d’index
...
37/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
TD : Gestion (simplifiée) d’un vidéo-club
Le propriétaire d’un vidéo-club désire conserver toutes les informations nécessaires àla bonne gestion de celui-ci. Vous l’avez interrogé, et voici les notes que vous avezprises :– Les films peuvent être sur différents supports (Blu_ray, DVD, . . .).– Un même film peut être présent en plusieurs exemplaires.– Les clients connaissent parfois le titre du film qu’ils veulent emprunter.– Les clients demandent souvent les films disponibles de tel acteur, tel réalisateur, ouportant sur tel thème.– Les films sont classés par catégories (policier, comédies, etc.).– Les clients sont connus par leur nom, prénom, adresse.– Chaque exemplaire d’un film a un numéro d’inventaire.– Les clients achètent des crédits avant de pouvoir emprunter.– La facturation dépend de la durée de l’emprunt et intervient à la restitution de l’objetloué. Le tarif est de 1 crédit par tranche de 3h pour un Blu_ray et de 2 crédits partranche de 3h pour les DVD.– Les clients doivent pouvoir être fidélisés par un système de bonus.– Le vidéo-club achète régulièrement des films soit parce qu’ils viennent de sortir, soitparce qu’ils sont empruntés souvent.– Le vidéo-club aime bien sortir régulièrement des statistiques sur les films les plusempruntés du mois, les clients qui empruntent le plus, . . .
38/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le modèle relationnel
Langage de définition de données (LDD) : SQL2
TD : Gestion (simplifiée) d’un vidéo-club
Travail à exécuter- Modéliser les données en utilisant un modèle entité/association.- Donner les relations correspondantes- Créer les tables correspondantes en précisant les contraintes d’intégrité
39/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
40/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Introduction
DéfinitionsUne requête est une expression algébrique qui s’applique à un ensemble derelations (la base de données) et produit une relation finale (le résultat de larequête).
On peut voir l’algèbre relationnelle comme un langage de programmation quipermet d’exprimer des requêtes sur une base de données relationnelle.
Les opérateurs de l’algèbre relationnelle1 la sélection, dénotée σ ;2 la projection, dénotée π ;3 le produit cartésien, dénoté × ;4 l’union, dénoté ∪ ;5 la différence, dénotée − ;6 la jointure, dénotée on
41/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Introduction
DéfinitionsUne requête est une expression algébrique qui s’applique à un ensemble derelations (la base de données) et produit une relation finale (le résultat de larequête).
On peut voir l’algèbre relationnelle comme un langage de programmation quipermet d’exprimer des requêtes sur une base de données relationnelle.
Les opérateurs de l’algèbre relationnelle1 la sélection, dénotée σ ;2 la projection, dénotée π ;3 le produit cartésien, dénoté × ;4 l’union, dénoté ∪ ;5 la différence, dénotée − ;6 la jointure, dénotée on
41/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Introduction
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
42/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La sélection, σLa sélection σF (R) s’applique à une relation R et extrait de cette relation les tuples quisatisfont un critère de sélection, F . Ce critère peut être :
La comparaison entre un attribut de la relation, A, et une constante a.
La comparaison entre deux attributs A1 et A2.
σregion=′Antilles′ (Station)
43/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La sélection, σLa sélection σF (R) s’applique à une relation R et extrait de cette relation les tuples quisatisfont un critère de sélection, F . Ce critère peut être :
La comparaison entre un attribut de la relation, A, et une constante a.
La comparaison entre deux attributs A1 et A2.
σregion=′Antilles′ (Station)
43/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La sélection, σLa sélection σF (R) s’applique à une relation R et extrait de cette relation les tuples quisatisfont un critère de sélection, F . Ce critère peut être :
La comparaison entre un attribut de la relation, A, et une constante a.
La comparaison entre deux attributs A1 et A2.
σregion=′Antilles′ (Station)
43/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La projection, πLa projection πA1,A2,...,An (R) s’applique à une relation R et ne garde que les attributsA1,A2, ...,An. Donc, contrairement à la sélection, on ne supprime pas des lignes maisdes colonnes.
πnomStation,region(Station)
44/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La projection, πLa projection πA1,A2,...,An (R) s’applique à une relation R et ne garde que les attributsA1,A2, ...,An. Donc, contrairement à la sélection, on ne supprime pas des lignes maisdes colonnes.
πnomStation,region(Station)
44/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La projection, πLa projection πA1,A2,...,An (R) s’applique à une relation R et ne garde que les attributsA1,A2, ...,An. Donc, contrairement à la sélection, on ne supprime pas des lignes maisdes colonnes.
πnomStation,region(Station)
44/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Le produit cartésien, ×Le produit cartésien entre deux relations R et S se note R × S, et permet de créer unenouvelle relation où chaque tuple de R est associé à chaque tuple de S.
45/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Le produit cartésien, ×Le produit cartésien entre deux relations R et S se note R × S, et permet de créer unenouvelle relation où chaque tuple de R est associé à chaque tuple de S.
45/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Station × Activite
46/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
σS.nomStation=A.nomStation(Station × Activite)
47/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
σS.nomStation=A.nomStation(Station × Activite)
47/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La jointure, onToutes les requêtes exprimables avec l’algèbre relationnelle peuvent seconstruire avec les 5 opérateurs présentés.
En pratique, il existe d’autres opérations, très couramment utilisées, qui peuventse construire par composition des opérations de base. La plus importante est lajointure.
Une jointure R onF S peut être définie étant équivalent à σF (R × S). Le critère derapprochement F peut être n’importe quelle opération de comparaison liant un attributde R à un attribut de F .
48/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
La jointure, onToutes les requêtes exprimables avec l’algèbre relationnelle peuvent seconstruire avec les 5 opérateurs présentés.
En pratique, il existe d’autres opérations, très couramment utilisées, qui peuventse construire par composition des opérations de base. La plus importante est lajointure.
Une jointure R onF S peut être définie étant équivalent à σF (R × S). Le critère derapprochement F peut être n’importe quelle opération de comparaison liant un attributde R à un attribut de F .
48/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Station onS.nomStation=A.nomStation Activite = σS.nomStation=A.nomStation(Station × Activite)
49/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
L’union, ∪L’expression R ∪ S crée une relation comprenant tous les tuples existant dans l’une oul’autre des relations R et S. Il existe une condition impérative : les deux relationsdoivent avoir le même schéma, c’est-à-dire même nombre d’attributs, mêmes noms etmêmes types.
La différence, −La différence s’applique à deux relations qui ont le même schéma. L’expression R − Sa alors pour résultat tous les tuples de R qui ne sont pas dans S.
50/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
L’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
Les opérateurs de l’algèbre relationnelle
L’union, ∪L’expression R ∪ S crée une relation comprenant tous les tuples existant dans l’une oul’autre des relations R et S. Il existe une condition impérative : les deux relationsdoivent avoir le même schéma, c’est-à-dire même nombre d’attributs, mêmes noms etmêmes types.
La différence, −La différence s’applique à deux relations qui ont le même schéma. L’expression R − Sa alors pour résultat tous les tuples de R qui ne sont pas dans S.
50/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
51/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
La structure de baseUne requête SQL de base contient trois clauses :
1 SELECT indique la liste des attributs constituant le résultat.2 FROM indique la (ou les) tables dans lesquelles on trouve les attributs utiles à la
requête.3 WHERE indique les conditions que doivent satisfaire les n-uplets de la base pour
faire partie du résultat.
SELECT nomStationFROM StationWHERE region = ’Antilles’
52/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
La structure de baseUne requête SQL de base contient trois clauses :
1 SELECT indique la liste des attributs constituant le résultat.2 FROM indique la (ou les) tables dans lesquelles on trouve les attributs utiles à la
requête.3 WHERE indique les conditions que doivent satisfaire les n-uplets de la base pour
faire partie du résultat.
SELECT nomStationFROM StationWHERE region = ’Antilles’
52/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
La structure de baseDans une requête SQL, on peut :
1 Renommer les attributs.2 Appliquer des fonctions aux valeurs de chaque tuple.3 Introduire des constantes.
53/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
Renommage
SELECT libelle, (prix / 6.56 AS prixEnEuros), ’Cours de l’euro = ’, 6.56 AS coursFROM ActiviteWHERE nomStation = ’Santalba’
54/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
Doublons
SELECT DISTINCT libelleFROM Activite
55/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
Tri du résultat
SELECT *FROM ActiviteORDER BY Prix
SELECT *FROM ActiviteORDER BY Prix DESC
56/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
Sélections simples
Tri du résultat
SELECT *FROM ActiviteORDER BY Prix
SELECT *FROM ActiviteORDER BY Prix DESC
56/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Conditions
SELECT nomStation, libelleFROM ActiviteWHERE nomStation = ’Santalba’AND (prix > 50 AND prix < 120)
SELECT nomStation, libelleFROM ActiviteWHERE nomStation = ’Santalba’AND prix BETWEEN 50 AND 120
57/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Conditions
SELECT nomStation, libelleFROM ActiviteWHERE nomStation = ’Santalba’AND (prix > 50 AND prix < 120)
SELECT nomStation, libelleFROM ActiviteWHERE nomStation = ’Santalba’AND prix BETWEEN 50 AND 120
57/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Chaînes de caractèresIl existe des motifs de recherches à l’aide de la clause LIKE. Le caractère ′_′ désignen’importe quel caractère, et le ′%′ n’importe quelle chaîne de caractères.
SELECT nomStationFROM StationWHERE nomStation LIKE ’%a’
SELECT nomStationFROM StationWHERE nomStation LIKE ’V______’
DatesUne date est spécifiée par le mot-clé DATE et d’une chaîne de caractères au format’aaaa-mm-jj’.SELECT idClientFROM SejourWHERE debut BETWEEN DATE ’1998-07-01’ AND DATE ’1998-07-31’
58/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Chaînes de caractèresIl existe des motifs de recherches à l’aide de la clause LIKE. Le caractère ′_′ désignen’importe quel caractère, et le ′%′ n’importe quelle chaîne de caractères.
SELECT nomStationFROM StationWHERE nomStation LIKE ’%a’
SELECT nomStationFROM StationWHERE nomStation LIKE ’V______’
DatesUne date est spécifiée par le mot-clé DATE et d’une chaîne de caractères au format’aaaa-mm-jj’.SELECT idClientFROM SejourWHERE debut BETWEEN DATE ’1998-07-01’ AND DATE ’1998-07-31’
58/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Chaînes de caractèresIl existe des motifs de recherches à l’aide de la clause LIKE. Le caractère ′_′ désignen’importe quel caractère, et le ′%′ n’importe quelle chaîne de caractères.
SELECT nomStationFROM StationWHERE nomStation LIKE ’%a’
SELECT nomStationFROM StationWHERE nomStation LIKE ’V______’
DatesUne date est spécifiée par le mot-clé DATE et d’une chaîne de caractères au format’aaaa-mm-jj’.SELECT idClientFROM SejourWHERE debut BETWEEN DATE ’1998-07-01’ AND DATE ’1998-07-31’
58/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes simples SQL
La clause WHERE
Chaînes de caractèresIl existe des motifs de recherches à l’aide de la clause LIKE. Le caractère ′_′ désignen’importe quel caractère, et le ′%′ n’importe quelle chaîne de caractères.
SELECT nomStationFROM StationWHERE nomStation LIKE ’%a’
SELECT nomStationFROM StationWHERE nomStation LIKE ’V______’
DatesUne date est spécifiée par le mot-clé DATE et d’une chaîne de caractères au format’aaaa-mm-jj’.SELECT idClientFROM SejourWHERE debut BETWEEN DATE ’1998-07-01’ AND DATE ’1998-07-31’
58/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
59/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
DéfinitionLa jointure permet d’exprimer des requêtes portant sur des données répartiesdans plusieurs tables.
La syntaxe pour exprimer des jointures est une extension directe de celleétudiée précédemment dans le cas des sélections simples.
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
donner le nom des clients avec le nom des stations où ils ont séjourné.SELECT nom, stationFROM Client, SejourWHERE id = idClientLe nom du client est dans la table Client, l’information sur le lien client/station dans latable Sejour
60/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
DéfinitionLa jointure permet d’exprimer des requêtes portant sur des données répartiesdans plusieurs tables.
La syntaxe pour exprimer des jointures est une extension directe de celleétudiée précédemment dans le cas des sélections simples.
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
donner le nom des clients avec le nom des stations où ils ont séjourné.SELECT nom, stationFROM Client, SejourWHERE id = idClientLe nom du client est dans la table Client, l’information sur le lien client/station dans latable Sejour
60/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
DéfinitionLa jointure permet d’exprimer des requêtes portant sur des données répartiesdans plusieurs tables.
La syntaxe pour exprimer des jointures est une extension directe de celleétudiée précédemment dans le cas des sélections simples.
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
donner le nom des clients avec le nom des stations où ils ont séjourné.SELECT nom, stationFROM Client, SejourWHERE id = idClientLe nom du client est dans la table Client, l’information sur le lien client/station dans latable Sejour
60/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
DéfinitionLa jointure permet d’exprimer des requêtes portant sur des données répartiesdans plusieurs tables.
La syntaxe pour exprimer des jointures est une extension directe de celleétudiée précédemment dans le cas des sélections simples.
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
donner le nom des clients avec le nom des stations où ils ont séjourné.SELECT nom, stationFROM Client, SejourWHERE id = idClientLe nom du client est dans la table Client, l’information sur le lien client/station dans latable Sejour
60/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
DéfinitionLa jointure permet d’exprimer des requêtes portant sur des données répartiesdans plusieurs tables.
La syntaxe pour exprimer des jointures est une extension directe de celleétudiée précédemment dans le cas des sélections simples.
BD d’un organisme de voyageStation (nomStation, capacité, lieu, région, tarif)Activite (nomStation, libellé, prix)Client (id, nom, prénom, ville, région, solde)Séjour (idClient, station, début, nbPlaces)
donner le nom des clients avec le nom des stations où ils ont séjourné.SELECT nom, stationFROM Client, SejourWHERE id = idClientLe nom du client est dans la table Client, l’information sur le lien client/station dans latable Sejour
60/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
Gestion des ambiguïtésafficher le nom d’une station, son tarif hebdomadaire, ses activités et leurs prix.
SELECT nomStation, tarif, libelle, prixFROM Station, ActiviteWHERE Station.nomStation = Activite.nomStation
SELECT S.nomStation, tarif, libelle, prixFROM Station S, Activite AWHERE S.nomStation = A.nomStation
61/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
Gestion des ambiguïtésafficher le nom d’une station, son tarif hebdomadaire, ses activités et leurs prix.
SELECT nomStation, tarif, libelle, prixFROM Station, ActiviteWHERE Station.nomStation = Activite.nomStation
SELECT S.nomStation, tarif, libelle, prixFROM Station S, Activite AWHERE S.nomStation = A.nomStation
61/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Jointures
Gestion des ambiguïtésafficher le nom d’une station, son tarif hebdomadaire, ses activités et leurs prix.
SELECT nomStation, tarif, libelle, prixFROM Station, ActiviteWHERE Station.nomStation = Activite.nomStation
SELECT S.nomStation, tarif, libelle, prixFROM Station S, Activite AWHERE S.nomStation = A.nomStation
61/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Union, intersection et différence
SELECT region FROM StationUNIONSELECT region FROM Client
SELECT region FROM StationINTERSECTSELECT region FROM Client
SELECT region FROM StationEXCEPTSELECT region FROM Client
62/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Union, intersection et différence
SELECT region FROM StationUNIONSELECT region FROM Client
SELECT region FROM StationINTERSECTSELECT region FROM Client
SELECT region FROM StationEXCEPTSELECT region FROM Client
62/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes sur plusieurs tables
Union, intersection et différence
SELECT region FROM StationUNIONSELECT region FROM Client
SELECT region FROM StationINTERSECTSELECT region FROM Client
SELECT region FROM StationEXCEPTSELECT region FROM Client
62/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes imbriquées
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
63/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes imbriquées
Conditions portant sur des relations
SELECT nom, prenomFROM ClientWHERE region = (SELECT region FROM Station WHERE nomStation = ’Santalba’)
Voici les conditions que l’on peut exprimer sur une relation R construite avec unerequête imbriquée.
EXISTS R. Renvoie TRUE si R n’est pas vide.
t IN R, où t est un tuple dont le type est celui de R.
v cmp ANY R, où cmp est un comparateur.
v cmp ALL R, où cmp est un comparateur.
De plus, toutes ces expressions peuvent être préfixées par NOT pour obtenir lanégation
64/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes imbriquées
Conditions portant sur des relations
SELECT nom, prenomFROM ClientWHERE region = (SELECT region FROM Station WHERE nomStation = ’Santalba’)
Voici les conditions que l’on peut exprimer sur une relation R construite avec unerequête imbriquée.
EXISTS R. Renvoie TRUE si R n’est pas vide.
t IN R, où t est un tuple dont le type est celui de R.
v cmp ANY R, où cmp est un comparateur.
v cmp ALL R, où cmp est un comparateur.
De plus, toutes ces expressions peuvent être préfixées par NOT pour obtenir lanégation
64/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes imbriquées
Conditions portant sur des relations
SELECT nom, prenomFROM ClientWHERE region = (SELECT region FROM Station WHERE nomStation = ’Santalba’)
Voici les conditions que l’on peut exprimer sur une relation R construite avec unerequête imbriquée.
EXISTS R. Renvoie TRUE si R n’est pas vide.
t IN R, où t est un tuple dont le type est celui de R.
v cmp ANY R, où cmp est un comparateur.
v cmp ALL R, où cmp est un comparateur.
De plus, toutes ces expressions peuvent être préfixées par NOT pour obtenir lanégation
64/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Requêtes imbriquées
Conditions portant sur des relations
SELECT nom, prenomFROM ClientWHERE region = (SELECT region FROM Station WHERE nomStation = ’Santalba’)
Voici les conditions que l’on peut exprimer sur une relation R construite avec unerequête imbriquée.
EXISTS R. Renvoie TRUE si R n’est pas vide.
t IN R, où t est un tuple dont le type est celui de R.
v cmp ANY R, où cmp est un comparateur.
v cmp ALL R, où cmp est un comparateur.
De plus, toutes ces expressions peuvent être préfixées par NOT pour obtenir lanégation
64/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
65/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
Fonctions d’agrégation
DéfinitionCes fonctions s’appliquent à une colonne, en général de type numérique. Ce sont :
COUNT qui compte le nombre de valeurs non nulles.
MAX et MIN.
AVG qui calcule la moyenne des valeurs de la colonne.
SUM qui effectue le cumul.
SELECT COUNT(nomStation), AVG(tarif), MIN(tarif)FROM Station
Combien de places a réservé Mr Kerouac pour l’ensemble des séjours ?.SELECT SUM (nbPlaces)FROM Client, SejourWHERE nom = ’Kerouac’AND id = idClient
66/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
Fonctions d’agrégation
DéfinitionCes fonctions s’appliquent à une colonne, en général de type numérique. Ce sont :
COUNT qui compte le nombre de valeurs non nulles.
MAX et MIN.
AVG qui calcule la moyenne des valeurs de la colonne.
SUM qui effectue le cumul.
SELECT COUNT(nomStation), AVG(tarif), MIN(tarif)FROM Station
Combien de places a réservé Mr Kerouac pour l’ensemble des séjours ?.SELECT SUM (nbPlaces)FROM Client, SejourWHERE nom = ’Kerouac’AND id = idClient
66/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
Fonctions d’agrégation
DéfinitionCes fonctions s’appliquent à une colonne, en général de type numérique. Ce sont :
COUNT qui compte le nombre de valeurs non nulles.
MAX et MIN.
AVG qui calcule la moyenne des valeurs de la colonne.
SUM qui effectue le cumul.
SELECT COUNT(nomStation), AVG(tarif), MIN(tarif)FROM Station
Combien de places a réservé Mr Kerouac pour l’ensemble des séjours ?.SELECT SUM (nbPlaces)FROM Client, SejourWHERE nom = ’Kerouac’AND id = idClient
66/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
Fonctions d’agrégation
DéfinitionCes fonctions s’appliquent à une colonne, en général de type numérique. Ce sont :
COUNT qui compte le nombre de valeurs non nulles.
MAX et MIN.
AVG qui calcule la moyenne des valeurs de la colonne.
SUM qui effectue le cumul.
SELECT COUNT(nomStation), AVG(tarif), MIN(tarif)FROM Station
Combien de places a réservé Mr Kerouac pour l’ensemble des séjours ?.SELECT SUM (nbPlaces)FROM Client, SejourWHERE nom = ’Kerouac’AND id = idClient
66/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause GROUP BY
DéfinitionDans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble durésultat d’une requête. Une fonctionnalité complémentaire consiste à partitioner cerésultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe.
afficher les régions avec le nombre de stationsSELECT region, COUNT(nomStation)FROM StationGROUP BY region
On souhaite consulter le nombre de places réservées, par client.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY id, nom
67/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause GROUP BY
DéfinitionDans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble durésultat d’une requête. Une fonctionnalité complémentaire consiste à partitioner cerésultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe.
afficher les régions avec le nombre de stationsSELECT region, COUNT(nomStation)FROM StationGROUP BY region
On souhaite consulter le nombre de places réservées, par client.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY id, nom
67/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause GROUP BY
DéfinitionDans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble durésultat d’une requête. Une fonctionnalité complémentaire consiste à partitioner cerésultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe.
afficher les régions avec le nombre de stationsSELECT region, COUNT(nomStation)FROM StationGROUP BY region
On souhaite consulter le nombre de places réservées, par client.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY id, nom
67/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause GROUP BY
DéfinitionDans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble durésultat d’une requête. Une fonctionnalité complémentaire consiste à partitioner cerésultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe.
afficher les régions avec le nombre de stationsSELECT region, COUNT(nomStation)FROM StationGROUP BY region
On souhaite consulter le nombre de places réservées, par client.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY id, nom
67/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause GROUP BY
DéfinitionDans les requêtes précédentes, on appliquait la fonction d’agrégation à l’ensemble durésultat d’une requête. Une fonctionnalité complémentaire consiste à partitioner cerésultat en groupes, et à appliquer la ou les fonction(s) à chaque groupe.
afficher les régions avec le nombre de stationsSELECT region, COUNT(nomStation)FROM StationGROUP BY region
On souhaite consulter le nombre de places réservées, par client.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY id, nom
67/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause HAVING
DéfinitionOn peut faire porter des conditions sur les groupes avec la clause HAVING. La clauseWHERE ne peut exprimer des conditions que sur les tuples pris un à un.
On souhaite consulter le nombre de places réservées, par client, pour les clients ayantréservé plus de 10 places.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY nomHAVING SUM(nbPlaces) >= 10
68/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause HAVING
DéfinitionOn peut faire porter des conditions sur les groupes avec la clause HAVING. La clauseWHERE ne peut exprimer des conditions que sur les tuples pris un à un.
On souhaite consulter le nombre de places réservées, par client, pour les clients ayantréservé plus de 10 places.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY nomHAVING SUM(nbPlaces) >= 10
68/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Agrégation
La clause HAVING
DéfinitionOn peut faire porter des conditions sur les groupes avec la clause HAVING. La clauseWHERE ne peut exprimer des conditions que sur les tuples pris un à un.
On souhaite consulter le nombre de places réservées, par client, pour les clients ayantréservé plus de 10 places.SELECT nom, SUM(nbPlaces)FROM Client, SejourWHERE id = idClientGROUP BY nomHAVING SUM(nbPlaces) >= 10
68/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Plan1 Introduction
Données, Bases de données et SGBDSGBD
2 Le modèle Entité/AssociationPrincipes générauxLe modèleAvantages et inconvénients
3 Le modèle relationnelDéfinitionPassage d’un schéma E/A à un schéma relationnelLangage de définition de données (LDD) : SQL2
4 L’algèbre relationnelleLes opérateurs de l’algèbre relationnelle
5 Le langage SQLRequêtes simples SQLRequêtes sur plusieurs tablesRequêtes imbriquéesAgrégationMises-à-jour
69/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Insertion
DéfinitionL’insertion s’effectue avec la commande INSERT dont la syntaxe est la suivante :
INSERT INTO R(A1,A2, ...An) VALUES (v1, v2, ...vn)
INSERT INTO Client (id, nom, prenom)VALUES (40, ’Ait Sadoune’, ’Idir’)
INSERT INTO Sites (lieu, region)SELECT lieu, region FROM Station
70/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Insertion
DéfinitionL’insertion s’effectue avec la commande INSERT dont la syntaxe est la suivante :
INSERT INTO R(A1,A2, ...An) VALUES (v1, v2, ...vn)
INSERT INTO Client (id, nom, prenom)VALUES (40, ’Ait Sadoune’, ’Idir’)
INSERT INTO Sites (lieu, region)SELECT lieu, region FROM Station
70/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Insertion
DéfinitionL’insertion s’effectue avec la commande INSERT dont la syntaxe est la suivante :
INSERT INTO R(A1,A2, ...An) VALUES (v1, v2, ...vn)
INSERT INTO Client (id, nom, prenom)VALUES (40, ’Ait Sadoune’, ’Idir’)
INSERT INTO Sites (lieu, region)SELECT lieu, region FROM Station
70/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Destruction
DéfinitionLa destruction s’effectue avec la clause DELETE dont la syntaxe est :
DELETE FROM R WHERE condition
Destruction de tous les clients dont le nom commence par ’M’.DELETE FROM ClientWHERE nom LIKE ’M%’
71/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Destruction
DéfinitionLa destruction s’effectue avec la clause DELETE dont la syntaxe est :
DELETE FROM R WHERE condition
Destruction de tous les clients dont le nom commence par ’M’.DELETE FROM ClientWHERE nom LIKE ’M%’
71/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Destruction
DéfinitionLa destruction s’effectue avec la clause DELETE dont la syntaxe est :
DELETE FROM R WHERE condition
Destruction de tous les clients dont le nom commence par ’M’.DELETE FROM ClientWHERE nom LIKE ’M%’
71/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Modification
DéfinitionLa modification s’effectue avec la clause UPDATE.
UPDATE R SET A1 = v1, ...,An = vn
WHERE condition
augmenter le prix des activités de la station ’Passac’ de 10%.UPDATE ActiviteSET prix = prix ∗ 1.1WHERE nomStation = ’Passac’
72/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Modification
DéfinitionLa modification s’effectue avec la clause UPDATE.
UPDATE R SET A1 = v1, ...,An = vn
WHERE condition
augmenter le prix des activités de la station ’Passac’ de 10%.UPDATE ActiviteSET prix = prix ∗ 1.1WHERE nomStation = ’Passac’
72/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
Modification
DéfinitionLa modification s’effectue avec la clause UPDATE.
UPDATE R SET A1 = v1, ...,An = vn
WHERE condition
augmenter le prix des activités de la station ’Passac’ de 10%.UPDATE ActiviteSET prix = prix ∗ 1.1WHERE nomStation = ’Passac’
72/73
Cours bases de données – <[email protected]> – Formation continue – 2012/2013
Le langage SQL
Mises-à-jour
TD : Gestion (simplifiée) d’un vidéo-club
* Lancer le programme "SQLiteSpy.exe"* Chargez le fichier de la base de données "Videoclub.db3" (menu File > OpenDatabase...)1 - A partir des tables, retrouvez le schéma des relations.Avez-vous des commentaires sur la façon dont sont enregistrées les identités desacteurs et des réalisateurs ?
2 - trouvez les requêtes SQL permettant de répondre aux questions suivantes* Quels sont les films plus récents que 2008 ?* Quels sont les films dans lesquels joue Belmondo ?* Quel est de réalisateur du film "Gran Torino" ?* Quels sont les films réalisés par "Clint Eastwood" ?* Dans quels films peut-on voir Alain Delon et Simone Signoret ?* Quels sont les exemplaires actuellement en location ?* Quels sont les films de Claude Chabrol actuellement disponibles à la location ?* Quels sont les 10 Films possédant le plus d’exemplaires
73/73