INFORMATIQUE IUT G.E.A. ¨Deuxième Année¨

Institut Universitaire de Technologie de Poitiers Institut Universitaire de Technologie de Poitiers Gestion des Entreprises et AdministrationsGestion des Entreprises et Administrations

8, rue des Carmes8, rue des Carmes86000 86000 PoitiersPoitiers

INFORMATIQUE INFORMATIQUE IUT G.E.A. ¨Deuxième Année¨IUT G.E.A. ¨Deuxième Année¨

Jean louis Martineau

2

Plan du coursPlan du cours

1. Introduction aux bases de données

2. Conception d’un système d’information

3. Interrogation d’une base de données

3

Plan du coursPlan du cours

Chap. 1- introduction

4

1. Organisation Systémique1. Organisation Systémique

Le système de gestion :

système de pilotage (cerveau de l'entreprise) :

il régule et contrôle le système opérant en décidant de son comportement en fonction des objectifs fixés

5


Le système d’informations :

Partie la moins visible, mais la plus fondamentale

Interface entre le système de gestion et le système opérant

Ensemble des informations utiles à la vie de l’entreprise

6


Système d'information

Informations traitées

Système de gestion

Système opérant

Informations de décision

Informations de représentation

Informations d'interactions

Informations externes Informations vers l'extérieur

Produits achetés

règlements desclients

Produits vendus

règlements desfournisseurs



Chap. 2 - Conception d’un système Chap. 2 - Conception d’un système d’informationd’information

1. Le modèle Entité/Association (E/A)

2. Méthode de construction du modèle E/A

3. Le modèle logique des données

8

Objectif du chapitreObjectif du chapitre

Méthode de conception d’un schéma de base de données :

- éviter les redondances de données

- démarche méthodique

- support graphique

9

2. Méthode de construction du 2. Méthode de construction du modèle E/Amodèle E/A

Construction d’un modèle Entité/Association depuis un problème réel :approche descendante

- beaucoup d’entités et d’associations- tâche trop complexe pour être effectuée sans méthode

Autre méthode de conception envisageable :

- approche ascendante (MERISE) : - des données détaillées vers le modèle E/A - regroupement pour constituer les entités et les associations

Des erreurs dans le modèle

10

2. Méthode de construction du 2. Méthode de construction du modèle E/Amodèle E/A

Dictionnaire des données

Analyse des dépendances entre les données

Constitution des entités et des associations (par regroupement)

Établissement schéma Entité-Association final

Méthode ascendante :(approche MERISE)

Des outils (4)Des règles d'utilisation des outils

11

2.1. Le dictionnaire des données2.1. Le dictionnaire des données

Objectif : recenser toutes les données qui seront stockées dans le système d’information

Pour chaque donnée il existe :

- un nom- un type (calculé ou non calculé)- un domaine de définition- une règle de gestion (si le type est calculé)

Regroupement de ces informations dans un tableau appelé :le dictionnaire des données

12

2.1. Le dictionnaire des données2.1. Le dictionnaire des données

Nom de ladonnée

Signification Type Domaine dedéfinition

Règle de gestion

NUMFAC Numéro de lafacture

Non Calculé Entier

MONTHT Montant HT dela facture

Non Calculé Monnaie

TVA Taux de TVAappliqué

Non Calculé Entier

MONTTTC Montant TTC dela facture

Calculé Monnaie R1

R1 : MONTTC=MONTHT*(1+TVA/100)

Exemple de dictionnaire des données

R1 est une règle de gestion permettant de calculer l’attributMONTTTC depuis d’autres données contenues dans le dictionnaire

13

2.2. Les dépendances fonctionnelles2.2. Les dépendances fonctionnelles

Exemples de D.F. entre deux attributs :

• Un numéro de sécurité social implique un, et un seul assuré social

• Une référence de produit implique unprix

NomAssuréNumSecu

PrixPdt

Par contre l’inverse n’est pas vrai car plusieurs produits peuventavoir le même prix

Par contre l’inverse n’est pas vrai car des personnes homonymes n’ont pas le même numéro de sécu.

14


Exemples de D.F. avec plusieurs attributs :

Jour,Mois,Année Date

NuméroProduit,BonCommande QuantitéCommandée

NuméroProduit,NumEntrepot QuantitéStock

Une valeur d’un identifiant détermine une et une seule occurrence de l’entité

15


Notation : Soient A,B et C des attributs

A B

CAOn peut écrire de façon équivalente

A B,C

Exemple :

NuméroClient NomClient, PrénomClient

Les dépendances fonctionnelles portent sur des ensembles d’attributs

16


Construction du graphe des dépendances fonctionnelles :

Etape 1 :

utiliser tous les attributs non calculés du dictionnaire des données

Etape 2 :

déterminer les D.F. simples et composées

17

2.3. Construction du schéma E/A2.3. Construction du schéma E/A

Construction du schéma E/A depuis le graphe des D.F.

- 3 règles permettent d’établir une esquisse du schéma E/A

Présentation des règles sur un exemple :Numéro commande

Référence produit

Prix unitaire

désignation

Qté

Numéro client

Adresse clientNom client

Date commandeCode représentant

Nom représentant

18


Règle 1 : Les dépendances fonctionnelles sans successeur sont regroupées en entités. Les origines des arcs désignent les attributs identifiants de ces entités.

Application sur l’exemple :

Numéro commande

Référence produit

Prix unitaire

désignation

Qté

Numéro client

Adresse clientNom client

Date commandeCode représentant

Nom représentant

19


Les deux autres règles gèrent les associations et les attributs isolés :

Règle 2 : Les arcs restants mettent en évidence les associations. Les attributs restants dans le graphe des dépendances fonctionnelles sont les attributs des associations.

Règle 3 : les attributs isolés constituent des entités isolées. Les règles de gestion permettent de trouver les cardinalités.

20


Résultat de l’application de la règle 1 sur l’exemple

Commande

Numéro Commande

Date commande

Produit

Référence produitDésignationPrix unitaire

Client

Numéro ClientNom client

Adresse client

Représentant

Code représentantNom représentant

Qté

21

2.3. Le modèle Entité/Association2.3. Le modèle Entité/Association

Définition L'information permet de représenter des connaissances.

Une information se décompose en trois niveaux :

- l'entité concernée par l'information. C'est l'objet, le concept ou l'individu dont il est question.

- les attributs (ou propriétés): ce sont les propriétés et les caractéristiques utilisées pour décrire l'entité.

- les valeurs d'un attribut permettent de définir une entité donnée.

22


Définition : l'occurrence d'une entité est l'ensemble des valeurs des attributs de cette entité.

Entité Facture

« Définition des informations »Format d’une facture

« occurrences d’une entité »liste des factures (valeurs)

Attributs :NomQté, DésignationMontantDate

23

2.3 Le modèle Entité/Association2.3 Le modèle Entité/Association

Définition : Une entité est un concept, un objet ou une personne du monde réel. Elle possède :

- une existence propre- des occurrences multiples- au moins un attribut identifiant (souligné).

Nom Entité

Attribut Identifiant 1….

Attribut Identifiant nAttribut 1

…Attribut m

Représentation graphique d’uneentité dans le modèle E/A

24


Exemple d’entité : Un fournisseur

Fournisseur

NuméroNom FournisseurAdresse FournisseurNom ContactNuméro TéléphoneNuméro FaxAdresse e-mail

Attributs : liste des informationsutiles à la gestion d’un fournisseur

Attribut identifiant : Numéro- code créé par l’entreprise- détermine un et un seul fournisseur

25


Concept d’Association : toutes les informations n’ont pas d’existence intrinsèque

Elles ne peuvent pas être regroupées dans une entité

Exemple : affectation d’un salarié dans un service

Salariés

Numéro salariéNomFonctionRémunération

Service

Numéro serviceNomLocalisation

Affectation

26


Affectation d’un salarié dans un service :

Problème : action dépendant de deux entités :- salarié- service

Exemple (suite)

La notion d’association va permettre de représenter les dépendances entre les entités

27


Définition : Une association regroupe les informations relatives à une ou plusieurs entités définies par :

- absence d'existence intrinsèque,- au moins une occurrence,- le nombre d'entités associées,- un nom traduisant généralement une action.

Nom AssociationAttribut 1

…Attribut m

Nom Association

Représentation graphique des associations

28


Exemple :

Salariés


Service


Travaille dans

L'identifiant d'une association est implicitement constitué par tous les identifiants des entités qui participent à l'association.

Participation de l’entité à l’association

29


Règles de construction du modèle Entité-Association : -Deux entités ne sont jamais reliées ensembles,-Deux associations ne sont jamais reliées ensembles.

Nom Entité 2

Identifiant 2Attribut 2.1

Attribut 2.m

Nom Entité 1

Identifiant 1Attribut 1.1

Attribut 1.m

Nom Associatio

n

Nom Associatio

n

Participations interdites

Participation Autorisée

30


Salariés


Service


Travailler dans

Hiérarchique de

Exemple (suite) : modélisation de la hiérarchie entre salariés

Rôle de la participation:explication pour leverles ambiguïtés

Est le chef de Est dirigé par

31


de représenter le lien entre les entités

Permet Permet pas

Connaître le nombred’occurrencesconcernées par l’association

Une association

Solution : indiquer le nombre d’occurrences de l’associationconcernées par la participation

Les cardinalités

32


Définition : CardinalitéLa Cardinalité précise pour chaque arc du modèle E/A - le nombre minimum d'occurrences d'une association liées à une occurrence d'entité.- le nombre maximum d'occurrences d'une association liées à une occurrence d'entité.

Remarque : si le nombre n'est pas connu précisément, une variable est utilisée (par exemple N ou M).

Entité

Association

Min,MaxReprésentationgraphique

33


Exemple : commande de produits par des clients

Client

NuméroNomAdresse

Produit

RéférenceDésignationPrix unitaire HT

Commander

Qté

0,N 0,N

Combien de commandes au minimum peut faire un client donné : 0Combien de commandes au maximum peut faire un client donné : N

Combien de commandes au minimum peut contenir un produit donné : 0Combien de commandes au maximum peut contenir un produit donné : N

34


Salariés


Encadre

Est dirigé par

Est le chef de

(0,1)

(0,N)

Exemple 2:

Participation Cardinalités SignificationsSalarié - Encadre(est dirigé par)

(0;1) Un salarié n'a pas de hiérarchique et un employé a aumaximum un chef

Salarié - Encadre(est le chef de)

(0,N) Un salarié peut encadrer de 0 à N autres salariés

Explications des cardinalités dans un tableau séparé du modèle E/A

35


Client

NumClientNom

Adresse

Dépôt

NumDépôtAdresse

LivrerNb colis

(0,N) (0,N)

Exemple 3 :

Participation Cardinalités SignificationsClient - Livrer (0;N) Un client est livré par 0 à N dépôtsDépôt - Livrer (0,N) Un dépôt livre de 0 à N clients

36


Définition : Contrainte d'Intégrité Fonctionnelle (CIF)Une cardinalité (1;1) est appelée une Contrainte d'Intégrité Fonctionnelle

Salariés


Service


Travailler dans

1,1 1,N

CIF

A chaque occurrence de « salarié » correspond une, et une seule, occurrence de « travailler dans »

37


Commande

Numéro CommandeDate commande

Produit

Référence produitDésignationPrix unitaire

Client

Numéro ClientNom client

Adresse client

Représentant

Code représentantNom représentant

ComposeQté

Obtient Passer0,N

0,N1,N

1,11,1

0,N

Résultat de l’application des règles 2 et 3 sur l’exemple :

38

2. Le modèle Entité/Association2. Le modèle Entité/Association

Produits

NumProdDésignation

PrixHT

Etat Stock

NumProdQté Stock

Stock Mini

Stocker

Exercice : Compléter les cardinalités

Atelier

NumAtelNom

Mission

Produit

NumProdDésignation

Stocker

(?,?)1,1 (?,?)1,1

(?,?)0,N (?,?)0,N

39


Produit

NumProd

Machine

NumMachDésignationMaintenance

Fabrique

Formation

NumFourDésignation

Date

Salarié

NumSalNom

Adresse

Participe

Exercice (suite) :

(?,?)1,N (?,?)1,N

(?,?)0,N (?,?)0,N

40


Exercice (suite) :

Matière

NumMatLibelléAnnée

Profs

NumProfNom

Spécialité

EnseigneDateSalle

Etudiant

NumEtdNomAge

(?,?)1,N (?,?)1,N

(?,?)1,N

41


Proposer VinPrix

RESTAURANT

CodeNom

Adresse

VIN

NomRécolteRégion

JOUR

NomOuvrirHoraire

MidiHoraire Soir

COULEUR

Nom

Est

Proposer PlatPrix

PLAT

NomPlaceRepas

Recommander

Accompagner

Guide Gastronomique

Complétez les Cardinalités (?,?)1,N

(?,?)0,N

(?,?)1,N(?,?)0,N

(?,?)0,1

(?,?)0,1

(?,?)0,N

(?,?)0,N

(?,?)1,N (?,?)0,N(?,?)1,1

(?,?)0,N

42


AVION

NuméroDateRévision

Qualifier

TYPE AVION

TypeCapacité

Rayon Action

PILOTE

NuméroDateRévision

DESC. VOLS

NuméroHoraire

VilleDépartVilleArrivée

Vols

Est un

Compagnie aérienne

a) Complétez les Cardinalités

b) modifiez ce schémapour tenir compte desescales

(?,?)1,1 (?,?)1,N

(?,?)1,N

(?,?)0,N

(?,?)1,1(?,?)0,N

(?,?)0,N

43


Règle de Gestion :

Une réparation concerne une interventionUne réparation peut concerner plusieurs salariésUne intervention peut nécessiter plusieurs réparationsUn salarié est responsable d’une interventionUn salarié est affecté à un atelier et éventuellement à plusieurs services

Entreprise de Maintenance

44


Atelier

NumAtelierLibelAtelier

Service

NumServiceNbEmploySpécialité

Client

NumClientNomClientAdresseCliCpClient

VilleClient

Matériel

NumMatNomMatTypeMat

Salarié

NumSalNomSal

AdresseSalCpSal

VilleSalTélSal

Fonction

Réparation

NumRepaCoutRepa

Intervention

NumInterNatureDate

Demande

Responsable

Travaille

Regroupe

Chef de

Utilise

Concerne

EffectueDate

Compose

Entreprise de Maintenance

Complétez lescardinalités

(?,?)1,N

(?,?)1,1

(?,?)0,N

(?,?)1,N

(?,?)0,N(?,?)0,N

(?,?)1,1

(?,?)0,N

(?,?)0,N

(?,?)1,1

(?,?)1,N(?,?)1,1

(?,?)0,N

(?,?)1,N

(?,?)0,N (?,?)1,N

(?,?)0,N

(?,?)0,N

45

2.4. Le modèle logique des données2.4. Le modèle logique des données

Dictionnaire des données

Etablissement schéma Entité-Association final

Modèle Logique des données

indépendant de l’organisation informatique

des données

Choix d’une organisation(relationnelle)

46


Le modèle logique des données relationnel (Codd, 1970):

Les données des entités et des associations sont mémorisées dans des relations :

RELATION(Attribut 1, …, Attribut n)

Nom de la relationAttribut identifiant

attribut

47


schéma Entité/Association

Modèle logique de données relationnel

4 règles de passage

Principe de transformation des entités et des associations :

48


Règle 1 : Transformation des entités- Toutes les entités deviennent des relations ; - Les identifiants deviennent des clés primaires.

Règle 2 : Transformation des associations binaires (?;1) - (?;N)Placer l'attribut clé de la relation (?;N) dans la relation (?;1), ainsi que tous les attributs de l'association.

49


Salarié

NumSalNomFonction

Service

NumServSpécialité

RegoupeDateArrivée

(1,N)(1,1)

Exemple d’application des règles 1 et 2 :

Application règle 1 :

SERVICE(NumServ, Spécialité)SALARIE(NumSal,Nom,Fonction, NumServ)

)

Application règle 2 :

50


Règle 3 : Transformation les associations n-aires (?;N) - (?;N)Transformer l'association en une relation contenant :- les clés primaires des deux entités reliées par l'association les attributs de l'association

Commande

NumCmdeDateCmde

Article

RefArticleLibelléPrixUnitaire

ConcernerQté Cmdée

(0,N)(1,N)

COMMANDE(NumCmde, DateCmde)ARTICLE(RefArticle,Libellé, PrixUnitaire)

Règle 1

CONCERNER(NumCmde,RefArticle,Qté Cmdée) Règle 3

51


Règle 4 : Transformation des associations réflexivesUne association réflexive, si elle ne correspond pas avec la règle 2, se transforme en une relation avec deux attributs qui sont la duplicationde la clé de l'entité, et toutes les deux renommées. Les attributs de l'association deviennent des attributs de la relation ainsi créée.

Personne

NumPersNomPrénom

Est Parent

(0,N)

(0,N)

PERSONNE(NumPers, Nom, Prénom)ESTPARENT(NumParent,NumEnfant)

Règle 1Règle 4

Parent

Enfant

52

Tables RelationnellesTables Relationnelles

Illustration : Création d’une table avec Access

Nom Attribut

Domainede l’attribut

Clé

53

Tables RelationnellesTables Relationnelles

Visualisationdu contenud’unetable Access

54

Cohérence des donnéesCohérence des données

Représentation des liens avec Access (attention : lien=relation)

Liensdessinésavec la souris

55


Avec Access : double clic sur un lien pour définir les contraintes

Contraintes

56


Contrainte d’entité avec Access : à la création de la table

Null Interdit

Attribut clésélectionné

57


Contrainte de domaine avec Access : à la création de la table

Assertion logiquedéfinissant une contrainte de domaine

Attributsélectionné



Chap. 3 - Langages de requêtesChap. 3 - Langages de requêtes

2. QBE

3. SQL

4. Les transactions

59

3. Introduction3. Introduction

Langages de requêtes : partie d ’un SGBD permettant d’interroger une base de

données

Principales Caractéristiques : simplicité : accessible à des non informaticiens complet : doit permettre toutes les interrogations et

manipulation de la base efficace : fournir les résultats dans un temps

raisonnable

60

3. Introduction3. Introduction

Principaux langages pour les SGBD relationnels SQL : Structured Query Language

QBE : Query By Example

- Ecriture des requêtes sous la forme d’un texte en anglais- la référence dans le domaine

- Support graphique sous la forme de tableau- présentation des requêtes en construisant la relation résultat- moins complet que SQL

61

3. QBE3. QBE

QBE : Query By Example(Interroger par un exemple)

Formulation de la requête en spécifiant la forme de la réponse

- Formulation graphique (à la souris)

- supporté par ACCESS

62

3. QBE3. QBE

Ecrire requête QBE

Requêteéquivalente

SQL

Traduction automatique

Exécution

QBE est une sur couche graphique à SQL

Relationrésultat

SGBD avec QBEVu par l’utilisateur

63

3. QBE3. QBE

Relations utilisées(FROM)

Attributs durésultat (SELECT)

Attributs avecune condition(WHERE)

SELECT Nom,Prénom FROM Client WHERE NumClient<100

64

3. QBE3. QBE

Expression des jointures :

Jointure entre lestables Clientet Facture

65

3. SQL3. SQL

SQL : Structured Query Language

Langage Normalisé : supporté par de nombreux SGBD

Normes 1989 et 1992

Access supporte partiellement ces deux normes

66

3. Éléments de base3. Éléments de base

Les valeurs logiques peuvent prendre trois états différents :

- VRAI (TRUE)- FAUX (FALSE)- INCONNUE (NULL)

AND VRAI FAUX NULLVRAI VRAI FAUX NULLFAUX FAUX FAUX FAUXNULL NULL FAUX NULL

Incidence sur les tables de vérité

Exemple : l’opérateur ET logique

67


OR VRAI FAUX NULLVRAI VRAI VRAI VRAIFAUX VRAI FAUX NULLNULL VRAI NULL NULL

Opérateur OU logique :

Opérateur Complémentation :

NOT VRAI FAUX NULLFAUX VRAI NULL

(Opérateurs logiques ...suite)

68


Types de requêtes SQL :

- Recherche de données : clause SELECT...FROM...WHERE

- modification de données : clause UPDATE

- Ajout de données : clause INSERT

- ...

69

3. Recherche de données3. Recherche de données

Requête de recherche de données :

- Manipule des tables ou des relations (résultats d’autres requêtes)

- résultat : relation

Table Relation

Enregistrement permanentdans la base de données

Enregistrement temporaire

70


Structure générale d’une requête de recherche de données :

Exemple :

SELECT NOMCLIENTFROM FACTURE

NOMCLIENTMr RobertMr Marcel...

Exécution

Texte de la Requête Relation résultat

71


• Sélectionner tous les attributs des relations utilisées dans la clauseFROM

SELECT *

• Sélectionner tous les attributs d’une relation de la clause FROM

SELECT NomRelation.*

• Sélectionner un attribut d’une relation de la clause FROM

SELECT NomRelation.NomAttribut

72


SELECT Relation.Attribut....

Relation.Attribut

Renommer le nom d’un colonne du résultat (clause AS) :

SELECT Relation.Attribut AS MonAttribut....

MonAttribut

73


A) PROJECTION (éliminer des colonnes du résultat)

SELECT <liste des attributs à présenter dans la relation résultat>FROM <Nom de la relation utilisée>

Exemple :

en SQL : SELECT Nom,CouleurFROM PeintureVoiture

74


B) SELECTION (éliminer des lignes du résultat)

SELECT *FROM <Nom de la relation>WHERE <conditions>

Exemple :

SELECT *FROM CouleurVoitureWHERE Moteur="Essence"

SQL

75


B1) SELECTION dans un texte

"*cice" mot se terminant par "cice"."exer* " mot commençant par "exer""ex*ce" mot débutant par "ex" et se terminant par "ce".

Recherche du type :

Utilisation de la clause LIKE (comme)

SELECT NomClientFROM ClientWHERE NomClient LIKE "DUR*"

76


B2) SELECTION dans une liste

Vérifier l’appartenance à un mot d’une liste : Clause IN

Exemple :

WHERE Attribut IN ( <liste de valeurs>)

SELECT Modele,Moteur,CouleurFROM VoitureWHERE Couleur IN ("Verte","Grise")

77


B3) SELECTION dans un intervalle

Vérifier l’appartenance à un intervalle de valeurs: Clause BETWEEN

WHERE attribut BETWEEN val_debut AND Val_fin

Exemple :

SELECT NomClient, MontantFROM ClientWHERE Montant BETWEEN 10 AND 1000

78


C) Produit Cartésien

SQL SELECT *FROM Voiture,OptionPeinture

SELECT *FROM R1,R2,....,Rn

Produit cartésien : R1R2 ... Rn

Exemple :

79


D) Jointure

C’est une sélection sur le produit cartésien :

SELECT *FROM R1,R2WHERE <condition rapprochement R1.Attribut = R2.attribut>

Exemple :

SELECT *FROM Fournisseur,HistoriqueWHERE Fournisseur.Four=Historique.Four

80


La norme SQL2 (1992) apporte une nouvelle formulation

SELECT *FROM R1 INNER JOIN R2 ON R1.attribut=R2.Attribut

Les jointures sont exprimées dans la clause FROM

SELECT *FROM Fournisseur INNER JOIN Historique ON Fournisseur.Four=Historique.Four

Exemple précédent :

81


E) Requête d’UNION de deux relations

SELECT … FROM … WHEREUNIONSELECT … FROM … WHERE

Formulation générale d’une requête UNION :

SELECT * FROM Client1UNIONSELECT * FROM Client2

SQL

82


F) Trier les lignes du résultat

Clause ORDER BY <liste ordonnée attributs > [ASC;DESC]

Ordre croissantOrdre décroissant

SELECT ...FROM ...WHERE ...ORDER BY .... [ASC;DESC]

Extension de la clause select from where :

83


Exemple : liste des noms des clients par ordre alphabétique

SELECT Nom,PrénomFROM ClientORDER BY Nom,Prénom ASC

Exemple : même question dans l’ordre inverse

SELECT Nom,PrénomFROM ClientORDER BY Nom,Prénom DESC

84


Exemples de requêtes :

Modèle relationnel et contrainte d’intégrité référentielle:

Client(numcli,nom,prenom,rue,CodePostal,Ville)

Facture(numfac,numcli,montant,date)

Requête : Liste des clients (Nom,Prenom)

SELECT nom,prenomFROM Client

85


Liste de numéro des montants des factures du client DUPONT :

SELECT numfac,montantFROM FACTURE,CLIENTWHERE FACTURE.numcli=CLIENT.numcli

Autre formulation (SQL2-1992) :

SELECT numfac,montantFROM FACTURE INNER JOIN CLIENT ON FACTURE.numcli=CLIENT.numcli

86


Liste des factures d’un montant supérieur à 20000F

SELECT *FROM FactureWHERE montant > 20000

Même question avec les noms des clients dans la liste résultat :

SELECT Client.nom, Facture.*FROM Facture INNER JOIN Client ON Client.numcli=Facture.numcliWHERE montant > 20000

87


Même question pour les clients de la vienne et par ordre alphabétique

SELECT Client.nom, Facture.*FROM Facture INNER JOIN Client ON Client.numcli=Facture.numcliWHERE montant > 20000 AND CodePostal LIKE "86* »ORDER BY Client.nom ASC

88

3. Fonctions et agrégats3. Fonctions et agrégats

Dans ce qui précède Select From Where... permet :

- Extraction de données

- mais pas de calculs

Deux types de calculs sont envisageables :

- en ligne : utilisation de fonctions pour créer des colonnes

- en colonne : agrégats pour regrouper des lignes et faire des calculs

89


A) Fonctions (calculs en ligne)

Exemple :

MONTANT_TTC=MONTANT_HT 20.6

Colonnes existantesNouvelle colonne liée au calcul

Solution SQL :

SELECT MONTANT_HT*TVA AS MONTANT_TTC FROM ...

Constante

90


Exemple : Compléter la relation en calculant le montant TTC

Facture(numfac,numcli,montant_ht)

Solution :

SELECT *, montant_ht*20.6 AS montant_ttcFROM Facture

91


B) Agrégats (calculs en colonne)

Agrégats : Ensemble d’attributs d’une relation, utilisé pour

regrouper des enregistrements durant des calculs sur les colonnes

n’appartenant pas à cet ensemble.

Exemple : calcul de la somme des montants des factures par client

Regroupement client 1

Regroupement client 2

Facture ... Numcli Montant 1 10001 20002 4000

92


Facture Numcli Montant 1 30002 4000

Après regroupement par numéro de client et cumul des montants

Il faut donc préciser dans la requête SQL :

- les attributs utilisés pour le regroupement (agrégats)

- les opérations utilisés

93


Préciser les agrégats : clause GROUP BY

SELECT A1,A2,A3,fonctions de calculFROM ....GROUP BY A1,A2,A3

Listes identiques d’attributs

94


Les fonctions de calculs sur les attributs non agrégats:

Descriptif SQL

SOMME(Attribut) SUM (Attribut)MOYENNE(Attribut) AVG (Attribut)MNIMUM(Attribut) MIN(Attribut)MAXIMUM(Attribut) MAX(Attribut)COMPTE COUNT(*)

Exemple :

SELECT numcli,SUM(Montant)FROM FactureGROUP BY numcli

95


a) sélection avant regroupement (clause Where)

b) sélection après regroupement (clause Having)

SELECT ...FROM ...WHERE <sélection avant regroupement>GROUP BY ...

SELECT ...FROM ... GROUP BY ...HAVING <sélection après regroupement>

96


SELECT numcli,SUM(Montant)FROM FactureWHERE montant>2000GROUP BY numcli

Exemples : a) clients avec cumuls des factures de plus de 2000 F

b) clients avec un cumul de facture de plus de 100000F

SELECT numcli,SUM(Montant)FROM FactureGROUP BY numcliHAVING SUM(Montant)>100000

97

3. Synthèse sur SELECT FROM WHERE3. Synthèse sur SELECT FROM WHERE

SELECT <liste d’attributs>,fonctionsFROM relations et jointure INNER JOIN ... ON condition rapprochementWHERE <sélection avant regroupement>GROUP BY <attributs agrégats>HAVING <sélection après regroupement>ORDER BY <attributs pour le tri>

La clause SELECT permet de tout combiner !

C’est très puissant - mais il faut réfléchir

98

3. Synthèse sur SELECT FROM WHERE3. Synthèse sur SELECT FROM WHERE

Enregistrement de la relation résultat dans une nouvelle table(si elle existe déjà elle sera effacée !)

SELECT <liste d’attributs>,fonctions INTO NomTableFROM relations et jointure INNER JOIN ... ON condition rapprochementWHERE <sélection avant regroupement>GROUP BY <attributs agrégats>HAVING <sélection après regroupement>ORDER BY <attributs pour le tri> À ajouter à la

fin du SELECT

99

3. Les sous-requêtes3. Les sous-requêtes

SQL autorise l’imbrication des requêtes SELECT

- après un comparateur =,<,>,>=,<=- dans une expression IN

- dans une expression EXISTS

Avoir des écritures très concises pour traiter des requêtes complexes.

Intérêt :

La sous-requête est unerelation avec une colonne(liste de valeurs possibles)

Sous-requête quelconque

100


a) Sous-requête après un comparateur

La clause WHERE s’écrit alors de la façon suivante :

Comparateur (< requête SELECT … FROM … WHERE > )

Exemple :requête qui retourne de le nom d'un produit et son prix, parmi les produits ayant un prix de produit du Marché numéro 1

SELECT NomProduit, Prix FROM ProduitsWHERE Prix = (SELECT PrixUnitaire FROM Produit WHERE NumMarche = 1)

Sous-requête

101


b) Sous-requête après l'instruction IN

La clause WHERE s'écrit de la façon suivante:

IN (< requête SELECT … FROM … WHERE > )

Exemple : recherche les produits dont les couleurs appartiennent à la relation NouvelleCouleur et sont du type 'Nacré'.

SELECT NumProduit, NomProduit FROM ProduitWHERE Couleur IN (SELECT Couleur FROM NouvelleCouleur WHERE TypeCouleur='Nacré')

102


c) Sous-requête après l'instruction EXISTS

But : Tester l’existence (on non) d’enregistrements dans le résultat de la sous-requête

WHERE [NOT] EXISTS (< requête SELECT … FROM … WHERE > )

Remarques

• Le NOT permet de tester l’inexistence• Il doit exister une jointure entre les relations de la requête principal et la sous-requête

103


Exemple : la liste des vendeurs de l'entreprise n'ayant pas passé des commandes.

SELECT Prénom, Nom FROM VendeurWHERE NOT EXISTS (SELECT NumCmde FROM CommandesWHERE Commandes.NumVendeur= Vendeur.NumVendeur)

Liens entre les deux requêtes

104

3. Sous-requêtes3. Sous-requêtes

Sous-Requête et Jointure :

SELECT R1.* FROM R1,R2WHERE R1.a=R2.b

Jointure

SELECT R1.* FROM R1WHERE EXISTS (SELECT * FROM R2 WHERE R1.a=R2.b)

Sous-Requête

Ces deux requêtes ont le même résultat

105

3. Jointures externes3. Jointures externes

Dans une jointure classique :

- tous les enregistrements ne vérifiant pas la condition derapprochement ne sont pas dans la relation résultat.

• Une jointure externe va permettre de conserver tout ou partie de ces informations. Les informations manquantes seront représentées par la valeur NULL

• Lorsque la condition de rapprochement est vérifiée, l’opérationest identique à la jointure classique

106


RESULTAT- -JOINTURE EXTERNEFour Société Adresse Facture Four Montant Date1 Soc 1 Adr 1 1 1 2000 F 20/10/982 Soc 2 Adr 2 2 2 1000 F 20/10/983 Soc 3 Adr 3 NULL NULL NULL NULL4 Soc 4 Adr 4 NULL NULL NULL NULL

FournisseurFour Société Adresse1 Soc 1 Adr 12 Soc 2 Adr 23 Soc 3 Adr 34 Soc 4 Adr 4

HistoriqueFacture Four Montant Date1 1 2000 F 20/10/982 2 1000 F 20/10/98

Fournisseur.Four = Historique.FourLa condition de rapprochement

est vérifiée pour chaque couple d’enregistrements des deux relations

107


• jointure externe à gauche :

Syntaxe dans la clause FROM :

Relation1 LEFT JOIN Relation2 ON condition_rapprochement

Résultat :

les enregistrements de Relation1 sans correspondance dans Relation2sont conservés

108


• Exemple de jointure externe à gauche :

Table1 Table 2NumFacture Fournisseur Montant NumFour NomFour1 1 1000 1 TotoSA2 2 5000

Select * From Table1 LEFT JOIN Table 2 ON Fournisseur=NumFour

NumFacture Fournisseur Montant NumFour NomFour1 1 1000 1 TotoSA2 2 5000 NULL NULL

109


• jointure externe à droite :

Syntaxe dans la clause FROM :

Relation1 RIGHT JOIN Relation2 ON condition_rapprochement

Résultat :

les enregistrements de Relation2 sans correspondance dans Relation1sont conservés

110


• Exemple de jointure externe à gauche :

Table1 Table2NumFacture Fournisseur Montant NumFour NomFour1 1 1000 1 TotoSA2 2 5000

Select * From Table2 RIGHT JOIN Table1 ON Fournisseur=NumFour

NumFour NomFour NumFacture Fournisseur Montant 1 TotoSA 1 1 1000NULL NULL 2 2 5000

111


• Tester la valeur NULL dans une relation :

Clause IS NULL ou bien IS NOT NULL dans la clause WHERE

Exemple : trouver les factures sans fournisseur

SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Fournisseur=NumFourWHERE Table2.NumFour IS NULL

112


Jointure externe avec ACCESS :

Dans l’assistant double-clic sur la jointure

LEFTJOIN

RIGHTJOIN

113

3. Requête INSERT3. Requête INSERT

Insertion d’enregistrement dans une table : clause INSERT

L ’exécution de la requête va insérer des enregistrements depuis :

• le résultat d’une requête SELECT...FROM...WHERE

• la liste des valeurs d’un enregistrement

114


Insertion depuis la liste des valeurs des attributs :

INSERT INTO <nom de la table>(<Attribut1,…, Attribut n)VALUES (Attribut1,…, Attribut n)

Eventuellement sous-ensemble des attributs de la table

Même ordre dans leslistes des attributs

115


Exemple :

Avant

Client Nom Prénom Adr

A a 1 B b 2

Client Nom Prénom Adr

Après

INSERT INTO Client (Nom,Prénom,Adr)VALUES (C,c,3)

A a 1 B b 2

C c 3

116


Insertion depuis une requête SELECT :

INSERT INTO <nom de la relation R1>SELECT <liste des attributs renommés comme dans la relation R1>FROM <liste des relations utilisées>WHERE <conditions>

Exemple : insertion des nouveaux clients

INSERT INTO Clients SELECT * FROM ClientsNouveaux

117

3. Requête UPDATE3. Requête UPDATE

Requête de mise-à-jour (modification) d’enregistrements

UPDATE <nom de la relation>SET <Attribut> = <expression>WHERE <condition>

Exemple : augmentation de 15% des produits de luxe et baisse de500 F des coûts d’expédition

UPDATE ProduitSET PrixVente = PrixVente * 1.15 , Expedition=Expedition-500WHERE TypeProduit = 'LUXE';

118

3. Requête DELETE3. Requête DELETE

Suppression d’enregistrements dans une table

DELETE FROM <nom de la table>WHERE <condition de recherche>

Exemple : Suppression des factures antérieures à 1990

DELETE FROM HistoriqueFactureWHERE Annee<1990

Documents

INFORMATIQUE IUT G.E.A. ¨Deuxième Année¨