144
Cours bases de données – <[email protected]> – Formation continue – 2012/2013 Introduction aux bases de données Formation continue Idir AIT SADOUNE [email protected] École Supérieure d’Électricité Département Informatique Gif sur Yvette 2012/2013 1/73

Introduction aux bases de données - Formation continue · 1 Introduction Données, Bases de données et SGBD SGBD 2 Le modèle Entité/Association Principes généraux ... Un Système

  • Upload
    lamminh

  • View
    220

  • Download
    2

Embed Size (px)

Citation preview

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