Cours Bd Complet

1

Bases de données

M. RAMDANI

2

PLAN

1. Introduction sur les BD et SGBD 2. Modélisation des données 3. Modèle relationnel 4. Algèbre Relationnel 5. SQL 6. Modèle relationnel Normalisation

3

Bibliographie

Ouvrages en Français– Carrez C., Des Structures aux Bases de Données,

Masson– Delobel A. et M. Adiba, Bases de données et Systèmes

Relationnels, Dunod Informatique– Gardarin G., Maîtriser les Bases de Données : Modèles

et langages, Eyrolles Ouvrages en anglais

– Ullman J.D., Principles of Database and Knowledge-Base Systems, volumes 2, Computer Sciences Press

– Dates C. J., an Introduction to Database Systemes, Addison-Wesley

– Abiteboul S., Hull R., Vianu V. Foundations of Databases, Addison-Weslay

4

Exemples d’applications

1. Classiques– Gestion (salaires, stock, …)– Transactionnel (comptes, centrales d’achat,….)– Réservations (avions, trains,…)

2. Multimédia / Mobilité– Documentation (bibliothèques, journaux,…)– Bureautique (textes, images, son), C.A.O – Géographique (cartes routières, thématiques,…), SIG

3. Bases de connaissances– Systèmes experts, knowledge management (KM ou GC)– Bases de Données Déductives, Datamaning …

5

SGF vers SGBD

Historique– La notion de Base de données date des années

60– Applications gérant de grandes quantités de

données : Données dans des fichiers fortran, PL1, Pascal ou Cobol Méthodes d’accès standards

– Développement des techniques d’accès aux fichiers et enregistrements

– Intégration des fichiers et applications ?– Représentation des liens plus complexes?

6

Redondance et incohérence des données

noCompte nomClient adresseClient noTéléphone dateOuverture solde100 Hugh Paycheck Ottawa (999)999-9999 5/05/1999 1000.00200 Dollard Cash Montréal (888)888-8888 10/10/1999 2000.00300 Hugh Paycheck Québec (555)555-5555 10/10/1999 1000.00400 Ye San Le Su Montréal (777)777-7777 20/7/2000 5.00600 Ye San Le Su Montréal (777)777-7777 15/10/2000 10.00

noPrêt nomClient adresseClient

noTéléphone montantPrêt

dateDébut tauxIntérêt

fréquencePaiement

1000 HughPaycheck

Alma (444)444-4444 10000.00 10/6/2000 10 12

2000 Ye San LeSu

Montréal (777)777-7777 20000.00 20/7/2000 12 52

3000 HughPaycheck

Ottawa (999)999-9999 5000.00 15/8/2000 12 12

Modifications anarchiques

7

BD et SGBD

Données Bases de Données (B.D.)– Une B.D. est un GROS ENSEMBLE d’informations

STRUCTUREES mémorisées sur un support PERMANENT, accessible pour satisfaire simultanément plusieurs utilisateurs en un temps opportun

LOGICIEL SGBD– Un Système de gestion de Bases de Donnés (SGBD) est

un logiciel de Haut Niveau qui permet de manipuler ces informations.

– Objectifs d’un SGBDLiens entre les données, cohérences des données,

souplessed’accès aux données, sécurité, Partage des données,Indépendance des données

8

Intégrité sémantique

Contrainte d'intégrité sémantique (semantic integrity constraint)– ou simplement contrainte d'intégrité

– une règle qui doit toujours être respectée par les données de la BD

– ex: “ le solde d'un compte ne peut être négatif ”

Supporté par SGBD (pas SGF)

9

Fiabilité

Exemple de panne SGBD : mécanismes de

récupération Revenir à un état cohérent suite à

une panne– journal des transactions

10

Sécurité

SGF : – lecture, écriture au niveau fichier

SGBD : contrôle plus fin– types de données– enregistrements– champs– type d ’opération– cryptage

11

Gestion des transactions

Transaction

– séquence d'opérations de manipulation de données exécutées par un programme

– délimitées par une opération de début et une opération de fin de transaction

12

Indépendances des Données

Indépendance Physique– On peut modifier l’implantation physique sans

modifier les programmes d’application Indépendance logique

– On peut modifier les programmes d’application sans toucher à l’implantation

13

Modèle de données

– Les Systèmes de gestion de fichiers ne permettent pas de définir et de manipuler des liens complexes entre les données.

– SGBD doit être fondé sur un modèle de données (Structuration des données). Façon de représenter les informations du monde réel

dans le SGBD– entité-relation,– Réseau (CODASYL) (SOCRATE),– Hiérarchique IMS d’IBM, – Relationnel,– Orienté-objet,– Modèles sémantiques,– ..

14

Entité-Relation

véhicule personnepropriétaire

N°VEH.

Marque

TypeCouleur

N°CIN NomPrénomDate

15

Modèles de SGBD

Hiérarchique– Pointeurs entre les

différents enregistrements

Réseau– Pointeurs– Arborescence

modifiée

16

Modèles de SGBD

Relationnel (SGBDR)– Tables à deux dimensions– Relations

Déductifs– Tables– Prédicats

Objets– Classes et instances

17

Où et comment sont stockées les données

Nature du support: bandes, disques magnétiques, disque optique

Organisations des fichiers : index, arbres-B, hachage,…

Sur quel site : centralisé, distribué, sur d’autres bases accessibles par réseau

18

Hachage

•Fichier dans lequel les articles sont placés dans des paquets dont l’adresse est calculée à l’aide d’une fonction de hachage appliquée à la clé:

•La clé d’un article est une donnée dans l’articlefichier d’étudiantÉtudiant (N°, Nom, prénom, Adresse, résultats)

•Le fichier est divisé en p paquet de longueur L•Parmi les fonctions de hachage : le modulo•Soit un fichier de 47 paquets et des articles de clé numérique. Ainsi l’article de clé 100 sera placé dans le paquet 6

19

Index

Table permettant d’associer à chaque clé d’article l’adresse relative de cet article

0 4 7 12 18

a10a3a57a2a5

0 4 7 12 182 6 8

a5 a2 a57 a3 a10

20

Architecture d’un SGBD: ANSI-SPARC (1975)

Niveau Externe

Niveau Logique

Niveau Physique

21

Fonctionnalité d’un SGBD

Niveau Physique – Gestion sur mémoire secondaire (fichier) de données,

du Schéma, des index– Partage de données et gestion de la concurrence

d’accès– Reprise sur pannes (fiabilité)– Distribution des données et interopérabilité (accès

aux réseaux) Concerne l’administrateur

Chaque niveau du SGBD remplit (réalise) un certain nombre de fonctions :

22

Niveau Logique– Définition de la structure de données : Langage de

description de données (LDD)– Consultation et Mise à jour des données : Langages

de Requêtes (LR) et Langage de Manipulation de Données (LMD)

– Gestion de la confidentialité (Sécurité)– Maintien de l’intégrité

Concerne l’administrateur et le développeur


23


Niveau externe– Vues– Environnement de programmation (intégration

avec un langage de programmation– Interfaces conviviales et Langages de 4e

Génération– Outils d’aides (e.g. conception de Schémas)– Outils de saisie, d’impression d’états– Débogueurs– Passerelles (réseaux, autres SGBD, etc.….)

Concerne l’utilisateur et le développeur

24

Le Facteur Humain

L’éditeur (le constructeur) du SGBD

L’administrateur de la base Rôle de l’administrateur

– discute avec les différents utilisateurs– conception d’un schéma logique (différentes vues)– conception du schéma physique– installation de la base et réglages fins (tuning)– gère l’évolution de la base (nouveaux besoins, utilisateurs)

Outils à sa disposition fournis par l’éditeur du SGBD

25

Le Facteur Humain

Utilisateur expert: informaticien connaissant langages programmation et langages BD

Concepteur et programmeur d’applicationà partir : besoins des différents utilisateurs, écrit l’application pour des utilisateurs “naïfs”

Utilisateur naïf: du non spécialiste des SGBD au non informaticien.

26

Mise en œuvre d’un SGBD

Monde réel

Schéma conceptuelLDD<>MD

Modélisation Modèle réseauModèle relationnel

.

.

.

Schéma physique

schéma externe n

schéma externe 1LDD

27

En résumé, on veut Gérer

Un Gros volume d’informations– Persistantes (années) et fiables(protection

sur pannes)– Partageables (utilisateurs, programmes)– Manipulées indépendamment de leur

représentation physique

28

Perspective historique

Modèle de définition de données (“ data definition model ”)

– nature des types de données supportés par le SGBD.

1ère génération (années 70): réseau, hiérarchique (CODASYL, IMS,...)– LMD navigationnel

2ième génération (années 80) :relationnel (Oracle, Ingres, DB2, SQL server,…)

– LMD non procédural 3ième génération (années 90)

– objet (Object Store, Versant,…)– relationnel objet (Oracle8, DB2 UDS, Informix US)

4ième génération ?– déductif (« Datalog »), entrepôt de données, data mining, support

du WEB (XML, XMLQL) Modèles pour conception

– E/R, UML

29

Modélisation des donnéesProcessus de conception d'une BD

Monde réel

Spécifications de la BD

Schéma conceptuel

(MCD)

Schéma logique

(Schéma relationnel)

Schéma interne (tables)

Analyse

Conception

Transformation en modèle logique

Conception physique

Indépendant d'un SGBD

Spécifique à un SGBD

30

Modélisation des donnéesAnalyse

Avant de concevoir il faut réfléchir au problème et l'analyser

Validation par les utilisateurs avant la mise en œuvre du système concret

Méthodes d'analyse: MERISE

31

Modélisation des données Conception: MCD

MCD (modèle conceptuel de données):– Représentation de l'information d'une

manière compréhensible aux différents services de l'entreprise.

– Description statique du système d'informations à l'aide d'entités et d'associations

32

Modélisation des données Entités et Attributs

Entité:– Représentation dans le

système d'information d'un objet, matériel ou immatériel ayant une existence propre et conforme aux choix de l'entreprise.(ex: une personne, une voiture, un client, un projet)

Attribut: – Propriété de l'entité (ex: date

de début de projet, couleur de voiture, note d'étudiant)

Client

Nom Prénom

33

Attribut composé– Subdivisé en

attributs

Attribut dérivé– Valeur calculée

Modélisation des donnéesEntités et Attributs

Nom

Prénom

Prénom

Prénom

Prénom

Âge

34

Modélisation des donnéesEntités et Attributs

Client

Rue VilleCode postal

Nom jj/mm/aaPrénom

Âge

- Exemple d’entité avec ses attributs -

35

Type d’entité: ex. CLIENT Occurrences du type CLIENT:

– Albert DUPONT– James WEST– Marie MARTIN– Gaston DURAND– …

Modélisation des donnéesTypes et occurrences

36

Type d’attribut– Nom et Prénom: chaînes de caractères– Date de naissance: Date– Âge: nombre entier

Occurrences d’attributs– Ex.: Bleu, rouge sont les occurrences d’un

attribut Couleur

Modélisation des donnéesTypes et occurrences

37

Modélisation des donnéesIdentifiant

Nom Prénom Date de naissance

Etc …

DUPONT Albert 01/06/70 …

WEST James 03/09/63 …

MARTIN Marie 05/06/78 …

DUPONT Albert 03/05/82 …

Liste des clients

Problème: Comment distinguer les Dupont ?

38

Solution: Ajouter un attribut N°client


N° client Nom Prénom Date de naissance

Etc …

1 DUPONT Albert 01/06/70 …

2 WEST James 03/09/63 …

3 MARTIN Marie 05/06/78 …

4 DUPONT Albert 03/05/82 …

39

Le numéro de client est un identifiant. Un identifiant caractérise de façon unique les occurrences d’un type d’entité

Notation graphique:


CLIENT Numéro

40

Association: liaison perçue entre les Entités– Ex. Les clients commandent des produits

Nbre d’entités participantes=degré de l’association

Modélisation des donnéesAssociations et cardinalités

CLIENT PRODUITCommande

41

Associations récursives et rôles– Association récursive

Une même occurrence d’entité peut jouer plusieurs rôles dans la même association (ex. employés et supérieur hiérarchiques)

– Rôle Fonction de chaque type d’entité

participant


EMPLOYES Hiérarchie

Est supérieur de

Est subalterne de

42

Associations 1-1– Ex. un client donné ne commande qu’un seul

produit. Un produit donné n’est commandé que par un seul client

– X,Y: X cardinalité mini et Y cardinalité maxi– Lire un client commande X à Y produits


CLIENT PRODUITCommande1,1 1,1

43

Associations 1-N– Ex. un client donné commande plusieurs

produits. Un produit donné n’est commandé que par un seul client.

– La cardinalité « un à plusieurs » (1-N) peut aussi être « 0 à plusieurs » (0-N).


CLIENT PRODUITCommande1,N 1,1

44

Associations M-N– EX. un client donné commande plusieurs

produits. Un produit donné est commandé par plusieurs clients.


CLIENT PRODUITCommande1,N 1,N

45

Attributs d’associations– Dans une association M-N il est possible de

caractériser l’association par des attributs.– Ex. une commande est passée à une Date

données et concerne une Quantité de produit fixée.


CLIENT PRODUITCommande1,N 1,N

Date Quantité

46

Spécifications– Les clients sont caractérisés par un numéro

de client, leur nom, prénom, date de naissance, rue, code postal et ville.

– Ils commandent des produits à une date donnée et dans une quantité donnée.

– Les produits sont caractérisés par un numéro de produit, leur désignation et leur prix unitaire.

Modélisation des donnéesExemple complet

47

Spécifications (suite)– Chaque produit est fourni par un

fournisseur unique (mais un fournisseur peut fournir plusieurs produits).

– Les fournisseurs sont caractérisés par un numéro de fournisseur et leur raison sociale


48

Marche à suivre pour produire le MCD1) identifier les données2) identifier les associations entre entités3) identifier les attributs de chaque entité et

de chaque association4) Évaluer les cardinalités des associations


49


Client ProduitCommande 1,N

QuantitéN° clientNom

Prénom

Date denaissance

Rue VilleCode postal

Date

N° produit

Désignation

Prix unitaire

fourni

FournisseurRaison soc.

N° fourn.

1,1

1,N

1,N

50

Une relation R est un ensemble d’attributs {A1,A2,…,An}– Ex. La relation Produit est l’ensemble des

attributs {NumProd, Désignation,Prix unitaire}

Modèle relationnelRelations et attributs

51

Le modèle relationnel est un modèle associé aux SGBD relationnels

Objectifs– Indépendance physique– Traitement de la redondance– Langages faciles à utiliser

Chaque attribut Ai prend ses valeurs dans un domaine dom(Ai)– Ex. PU ]0, 10000]

Modèle relationnelGénéralités

52

Un tuple est un ensemble de valeurs t=<V1,V2,…,Vn> où Vi dom(Ai)– Ex. <112, Raquette de tennis, 300> est un

tuple de la relation produit

Notation: R(A1,A2,…,An)– Ex. PRODUIT(N°produit,désignation,PU)

Modèle relationnelRelations, attributs et tuples

53

Clé primaire: – ensemble d’attributs dont les valeurs

permettent de distinguer les tuples les uns des autres (identifiant)

– Ex. N°produit clé primaire de la relation PRODUIT

Notation: clef primaire

Modèle relationnelContraintes d’intégrité

54

Clé étrangère:– Attribut qui est clé primaire d’une autre

relation

– Ex. connaître le fournisseur de chaque produit ajout de l’attribut N°fournisseur à la relation PRODUIT

Notation: Clef étrangère


55

Notation– Clef primaire– Clef secondaire– Ex. PRODUIT(N°produit,

désignation,PU,N°fournisseur)

Contraintes de domaine– PU 0 et PU 10000


56

Chaque Entité devient une Relation– Les attributs de l’entité deviennent les

attributs de la relation. Seuls les attributs simple des attributs composés sont inclus.

– L’identifiant de l’entité devient clé primaire de la relation.

Ex. CLIENT(N°client,nom,prénom,date de naissance,rue,CP,ville)

Modèle relationnelTraduction MCD - relationnel

57

Chaque association 1-1 est prise en compte en incluant la clef primaire d’une des relations comme clef étrangère dans l’autre relation

– Ex. Si un client peut posséder un compte COMPTE(N°compte,Solde) CLIENT(N°client,…,N°compte)


58

Chaque association 1-N est prise en compte en incluant la clef primaire de la relation dont la cardinalité maximale est N comme clef étrangère dans l’autre relation– Exemple:

PRODUIT(N°produit, dési,…,N°fournisseur)

FOURNISSEUR(N°fournisseur, raison sociale)


59

Chaque relation M-N est prise en compte en créant une nouvelle relation dont la clef primaire est la concaténation des clefs primaires des relations participantes. Les attributs de l’association sont insérés dans cette nouvelle relation

– Ex. COMMANDE(N°client,N°produit,Date,Quantité)


60

Schéma relationnel complet

– CLIENT(N°client,nom,prénom,Date de naissance,Rue,CP,Ville)

– PRODUIT(N°produit,désignation,PU,N°fournisseur)

– FOURNISSEUR(N°fournisseur,raison sociale)

– COMMANDE(N°client,N°produit,Date,Quantité)


61

Modèle Relationnel:Définitions

Un Domaine est un ensemble de valeurs. Exemples : {0,1}, N, l’ensemble des chaînes de caractères, l’ensemble des chaînes de caractères de longueur 10.

Un ATTRIBUT prend ses valeurs dans un domaine. Plusieurs attributs peuvent avoir le même domaine.

Un NUPLET est une liste de n valeurs (v1,…,vn) où chaque valeur vi est la valeur d’un attribut Ai de domaine Di : vi є Di

Le PRODUIT CARTÉSIEN D1x…xDn entre des domainesD1,…,Dn est l’ensemble de tous les nuplets (v1,…,vn) où vi є Di

.

62

Définition (suite 1)

RELATION : soit D1,…,Dn les domaines respectifs des attributs A1,…,An.

Une relation R définie sur les attributs A1,…,An est un sous-ensemble fini du produit cartésien D1x…xDn est un ensemble de nuplets.

Une relation est représentée sous forme d’une table. L’ordre des colonnes ou des lignes n’a pas d’importance. Les colonnes sont distinguées par les noms d’attributs et chaque ligne représente un élément de l’ensemble, (un nuplet).

Un attribut peut apparaître dans plusieurs relations.

Une BASE DE DONNÉES est un ensemble de relations.

63


L’UNIVERS D’ATTRIBUTS D’UNE BASE DE DONNÉES est l’ensemble de tous les attributs des relations de la base.

Le SCHÉMA D’UNE RELATION R est défini par le nom de la relation et la liste des attributs avec pour chaque attribut son domaine. Notation :R(A1:D1,…,An:Dn) ou plus simplement : R(A1,…,An)

Exemple :VEHICULE(NOM:CHAR(20), TYPE:CHAR(10), ANNEE:ENTIER)

64


Si la relation a n attributs (n colonnes), n est appelé ARITÉ de la relation.

La relation VEHICULE est d’arité 3

Le SCHÉMA D’UNE BASE DE DONNÉES est l’ensemble des schémas de ses relations.

65

Exemple de Base de Données

SCHÉMA :

FOURNISSEURS (FNOM:CHAR(20), FADRESSE:CHAR(30))

FOURNITURE (FNOM:CHAR(20), PNOM:CHAR(10), PRIX:ENTIER))

COMMANDES (NUM_COMDE:ENTIER, NOM:CHAR(20), PNOM:CHAR(10), QUANTITE;ENTIER))

CLIENTS (NOM: CHAR(20), CADRESSE:CHAR(30), BALANCE:RELATIF)

66

Exemple de B.D. (1)

UNIVERS D’ATTRIBUTS :U={FNOM, PNOM, NOM, FADRESSE, CADRESSE, PRIX, NUM_CODE, QUANTITE, BALANCE}

RELATION UNIVERSELLE :FPCC(FNOM, PNOM, NOM, FADRESSE, CADRESSE, PRIX, NUM_CODE, QUANTITE, BALANCE)

67

Modèle Relationnelle : Opérations et Langages

LANGAGE DE DÉFINITION DES DONNÉES (définition et MAJ du schéma) :– Création et destruction d’une relation ou d’une base– Ajout, suppression d’un attribut

LANGAGE DE MANIPULATION DES DONNÉES– Saisie des nuplets d’une relation– Affichage d’une relation– Modification d’une relation : insertion, suppression et maj des nuplets– Requêtes : consultation d’une relation ou calcul d’une nouvelle relation

GESTION DES TRANSACTIONS

GESTION DES VUES

68

Langages de Requêtes Relationnels

POUVOIR D’EXPRESSION : Qu’est-ce qu’on peut calculer ? Quelles opérations peut-on faire ?

Les langages de requête relationnels utilisent deux approches :- calcul relationnel- algèbre relationnelle

Les deux approches ont même pouvoir d’expression.

69

Algèbre Relationnelle

Une opération prend en entrée une ou deux relationsle résultat est toujours une relation

5 Opérations de base pour exprimer toutes les requêtes :

– Opérations unaires : sélection, projection

– Opérations binaires : union, différence, produit cartésien

– Autres opérations qui s’expriment en fonction des 5 opérations de base : jointure (naturelle, θ-jointure), intersection, division

70

Projection

LA PROJECTION “ÉLIMINE” UNE OU PLUSIEURS COLONNES D’UNE RELATION.

Notation : (A1,A2,…An) (R)

71

Projection: Exemples

a) On élimine la colonne C dans la relation R

R A,B(R)A B C A B a b c < a bd a b d ac b d c ba b e < e ee e a

Le nuplet (a,b) n’apparaît qu’une fois dans la relation R bien qu’il existe deux nuplets (a,b,c) et (a,b,e) dans R.

72

Projection: Exemples

b) On élimine la colonne B dans la relation R

R A,C(R)A B C A C a b c < a cd a b d bc b d c da b e < a ee e a e e

73

Sélection

Sélection sur la condition C :

On garde les nuplets qui satisfont C. NOTATION :

σC ( R )

74

Sélection: Exemples

a) On sélectionne les nuplets dans la relation R tels que l’attribut B vaut “b” :

R A B C A B Ca b 1 σB= ˝b˝( R ) a b 1

d a 2 c b 3c b 3 a b 4a b 4e e 5

75


b) On sélectionne les nuplets :

(A=˝a˝ v B= ˝a˝) Λ C3 R: A B C A B C

a b 1 σ(A=˝a˝v B=˝a˝) Λ C3( R ) a b 1d a 2 d

a 2c b 3a b 4e e 5

76


c) On sélectionne les nuplets tels que la 1ère et la 2ème colonne sont identiques :R: A B C A B C

a b 1 σA= B( R ) e e 5 d a 2 c b 3 a b 4 e e 5

77

Condition de Sélection

La condition C d’une sélection peut être une formule logique quelconque avec des et () et des ou () entre termes de la forme Ai θ Aj et Ai θ a Où :

Ai et Aj sont des attributs, a est un élément (une valeur) du domaine de

Ai, θ est l’un de =,<,<=,>,>=,!=…

78

Expressions de l’Algèbre Relationnelle

le résultat d’une opération est une relation sur cette relation, on peut faire une autre

opération de l’algèbre

=>Les opérations peuvent être composées pour former des expressions de l’algèbre relationnelle.

79

Expressions de l’Algèbre Relationnelle

EXEMPLE : COMMANDES (NOM, PNOM, NUM, QTE)

R’’= PNOM ( σNOM=˝Jean˝ (COMMANDES) ) ------------------------------

R’ La relation R’(NOM, PNOM, NUM, QTE)

contient les nuplets dont l’attribut NOM a la valeur ‘’Jean’’

La relation R’’(PNOM) contient tous les produits commandés par Jean.

80

Produit Cartésien

NOTATION : R×S ARGUMENTS : 2 relations quelconques:

R(A1,..,An) S(B1,…Bk)

SCHÉMA DE T=R×S : T(A1,..,An, B1,…Bk) VALEUR DE T=R×S : ensemble de tous les

nuplets ayant n+k composants (attributs)– dont les premiers composants forment un nuplet de

R– et les derniers composants forment un nuplet de S

81

Exemple de Produit Cartésien

A B

113

124

C D E

aab

bba

aca

R SA B C D E

1 1 a b a

1 1 a b c

1 1 b a a

1 2 a b a

1 2 a b c

1 2 b a a

3 4 a b a

3 4 a b c

3 4 b a a

RxS

|R|

|S||R|x |S|

82

Opération renommer ()

“Quels sont les Clients qui ont le même numéro de téléphone? ”Table Client

noClient nomClient noTéléphone 10 Hugh Paycheck (999)999-9999 20 Dollard Cash (888)888-8888 30 Ye San Le Sou (777)777-7777 40 Le Comte Hasek (666)666-6666 50 Hafedh Lajoie (555)555-5555 60 Comtesse Hasek (666)666-6666 70 Coco McPoulet (444)444-4419 80 Dollard Cash (333)333-3333

Client2 (Client)

Table Client2 noClient nomClient noTéléphone 10 Hugh Paycheck (999)999-9999 20 Dollard Cash (888)888-8888 30 Ye San Le Sou (777)777-7777 40 Le Comte Hasek (666)666-6666 50 Hafedh Lajoie (555)555-5555 60 Comtesse Hasek (666)666-6666 70 Coco McPoulet (444)444-4419 80 Dollard Cash (333)333-3333

Client.noClient, Client2.noClient, ( Client.noTéléphone = Client2.noTéléphone (Client Client2 (Client)))

Client. noClient Client2.noClient40 4040 6060 4060 60

83

Renommer les colonnes “Quels sont les Clients qui ont le même numéro de

téléphone? ” Table Client noClient nomClient noTéléphone 10 Hugh Paycheck (999)999-9999 20 Dollard Cash (888)888-8888 30 Ye San Le Sou (777)777-7777 40 Le Comte Hasek (666)666-6666 50 Hafedh Lajoie (555)555-5555 60 Comtesse Hasek (666)666-6666 70 Coco McPoulet (444)444-4419 80 Dollard Cash (333)333-3333

Client2(noClient2, nomClient2,

noTéléphone) (Client)

Table Client2 noClient2 nomClient2 noTéléphone 10 Hugh Paycheck (999)999-9999 20 Dollard Cash (888)888-8888 30 Ye San Le Sou (777)777-7777 40 Le Comte Hasek (666)666-6666 50 Hafedh Lajoie (555)555-5555 60 Comtesse Hasek (666)666-6666 70 Coco McPoulet (444)444-4419 80 Dollard Cash (333)333-3333

noClient, noClient2 (Client Client2(noClient2, nomClient2,

noTéléphone) (Client))Client. noClient Client2.noClient40 4040 6060 4060 60

84

NOTATION : RS

ARGUMENTS : 2 relations quelconques:

R(A1,..,Am ,X1,..,Xk) S(B1,…Bn ,X1,..,Xk)

SCHÉMA DE T=R S : T(A1,..,Am, B1,…Bn ,X1,..,Xk)

VALEUR DE T=R S : ensemble de tous les nuplets ayant m+n+k composants (attributs)

Dont les m premiers et les k derniers composants forment un nuplet de R

et les n+k derniers composants forment un nuplet de S

Jointure Naturelle

85

A B C

a b c

d b c

b b f

c a d

B C D

b c d

b c e

a d b

A B C D

a b c d

a b c e

d b c d

d b c e

c a d b

S R RS

Jointure Naturelle : Exemple

86

Soit U={A1,..,Am, B1,…Bn ,X1,..,Xk} l’ensemble des attributs de relations et V={X1,..,Xk} l’ensemble des attributs en commun

RS =U(AV :R.A=S.A(Rx S))

Notation : R.A veut dire l’attribut A de la relation R

Jointure Naturelle

87

A B

1 a

1 b

4 a

A B D

1 a b

2 c b

4 a a

R.A

R.B

S.A

1 a 1

1 a 2

1 a 4

1 b 1

1 b 2

1 b 4

4 a 1

4 a 2

4 a 4

S.B D

a b

c b

a a

a b

c b

a a

a b

c b

a a

Jointure Naturelle :Exemple

R S

RxS

88

RS =R.A,R.B,D(R.A=S.AR.B=S.B(Rx S))

A B D

1 a b

4 a a

RS

Jointure Naturelle : Exemple

89

Pour chaque a nuplet dans R et pour chaque b nuplet dans S:

• On concatène a et b et on obtient un nuplet qui a pour attributs

A1,..,Am, X1,..,Xk,B1,…Bn ,X1,..,Xk

• On ne garde que si chaque attribut Xi de a est égal à l’attribut Xi de b: i=1..K a.Xi=b.Xi

• On élimine les valeurs (les colonnes) dupliquées:

A1,..,Am,B1,…Bn ,X1,..,Xk

a b

Jointure Naturelle : Algorithme

90

NOTATION : RAiBiS , {=, , , , <, >}


R(A1,..,Am ) S(B1,…Bn )

SCHÉMA DE T= RAiBiS : T(A1,..,Am, B1,…Bn )

VALEUR DE T= RAiBiS :T=AiBi(RxS))

Equijointure : est l’égalité

jointure

91

A B

1 a

1 b

3 a

C D E

1 b a

2 b c

4 a a

A B C D E

1 a 1 b a

1 a 2 b c

1 a 4 a a

1 b 1 b a

1 b 2 b c

1 b 4 a a

3 a 1 b a

3 a 2 b c

3 a 4 a a

R S

RxS

Jointure : Exemple

92

A B C D E

1 a 1 b a

1 a 2 b c

1 a 4 a a

1 b 1 b a

1 b 2 b c

1 b 4 a a

3 a 4 a a

T=AC(RxS) =R AC S

A B C D E

1 a 4 a a

1 b 1 b a

1 b 2 b c

3 a 4 a a

T=B=D(RxS) =R B=D S

Equijointure

Jointure : Exemple

93

IMMEUBLE(ADI,NBETAGES,AGES,DATESC,PROP)APPIM(ADI,NAP, OCCUP, ETAGE)

•Nom du propriétaire de l’immeuble où est situé l’appartement occupé par Durand :PROP(IMMEUBLE OCCUP="Durand "(APPIM))

•Appartements occupés par des propriétaires d’immeuble :ADI,NAP,ETAGE (IMMEUBLE OCCUP=PROPAPPIM)

94

Autre Exemple de REQUÊTE : Nom et adresse des clients qui ont commandé des parpaings:

Schéma Relationnel :

COMMANDES (PNOM,CNOM,NUM_COM,QTE)

CLIENTS(CNOM,CADRESSE,BALANCE)CNOM,CADRESSZ(CLIENTS PNOM="parpaing"(COMMANDES))

95

NOTATION : RS ARGUMENTS : 2 relations quelconques:

R(A1,..,Am) S(A1,…Am) SCHÉMA DE T= RS : T(A1,..,Am) VALEUR DE T: union ensembliste sur D1x…

xDm

T={t| tR t S}

UNION

96

A B

1 a

1 b

3 a

A B

1 a

1 d

3 e

A B

1 a

1 b

3 a

1 d

3 e

R SRS

UNION : Exemple

97

NOTATION : R-S ARGUMENTS : 2 relations quelconques:

R(A1,..,Am) S(A1,…Am) SCHÉMA DE T= R-S : T(A1,..,Am) VALEUR DE T: différence ensembliste sur

D1x…xDm

T={t| tR t S}

Différence

98

A B

1 a

1 b

3 a

A B

1 a

1 d

3 e

A B

1 b

3 a

R

SR-S

Différence : Exemple

99

NOTATION : RS


R(A1,..,Am) S(A1,…Am)

SCHÉMA DE T= R S : T(A1,..,Am)

VALEUR DE T: intersection ensembliste sur D1x…xDm

T={t| t R t S}

INTERSECTION

100

A B

1 a

1 b

3 a

A B

1 a

1 d

3 e

A B

1 AR S

RS

RS = R-(R-S)

INTERSECTION: Exemple

101

NOTATION : RS


R(A1,..,Am ,X1,..,Xk) S(B1,…Bn ,X1,..,Xk)

SCHÉMA DE T=R S : T(A1,..,Am ,X1,..,Xk)

VALEUR DE T=R S :Projection sur les attributs de R de la jointure naturelle entre S et R

Semijointure

102

La semijointure correspond à une sélection où la condition de sélection est définie par le biais d’une autre relation.

Soit U={A1,..,Am} l’ensemble des attributs de R

R S =URS

Semijointure

103

A B C

a b c

d b c

b b f

c a d

B C D

b c d

b c e

a d b

S R

A B C

a b c

d b c

c a d

R S =A,B,CRS

Semijointure : Exemple

104

NUM

NOM PNOM QTE

123456

Jean Jean JeanPaul Paul Vincent

briques ciment parpaing briquesParpaingparpaing

1002 2200 33

Division: Exemple

REQUÊTE : Clients qui commandent tous les produits:

COMM

105

R=NOM,PNOM(COMM)

NOM PNOMJean Jean JeanPaul Paul Vincent

briques ciment parpaing briquesParpaingparpaing

PNOMbriques ciment parpaing

R PROD

NOM

Jean RPROD

Division: Exemple

106

A B C Daaa bbcccdd

bbbc deeeaa

xyz x xxyz zy

mno o mmno pp

C Dxyz

mno

A Bac

b e

R SRS

Division: Exemple

107

NOTATION : R S

ARGUMENTS : 2 relations:

R(A1,..,Am ,X1,..,Xk) S( X1,..,Xk)

Où tous les attributs de S sont des attributs de R

SCHÉMA DE T=R S : T(A1,..,Am )

VALEUR DE T=R S :

R S={(a1,..,am)| (x1,…,xk) S :(a1,..,am, x1,…,xk) R}

Division

108

La division s’exprime en fonction du produit cartésien, de la projection et de la différence :

R S=R1-R2 où

R1= A1,..,Am (R) et R2= A1,..,Am ((R1xS)-R)

Division

109

SQL présentation Générale

110

Principe

SQL (Structured Query Language) est le Langage de Requêtes standard pour les SGBD relationnels (Standard développé par ANSI et adopté par ISO/IEC 1986)

Expression d’une requête par un bloc SELECT FROM WHERE

SELECT <liste des attributs a projeter>

FROM <liste des relations argument>

WHERE <conditions sur un ou plusieurs attributs>

Dans les requêtes simples, la correspondance avec l’algèbre relationnelle est facile à mettre en évidence.

111

EXPRESSION DE BASE

112

Projection

Soit le schéma de relation COMMANDES (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Information sur toutes les commandes

SQL:

SELECT NUM,CNOM,PNOM,QUANTITE

FROM COMMANDES

ou

SELECT *

FROM COMMANDES

113

Soit le schéma de relation COMMANDES(NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Produits commandés

SELECT PNOM

FROM COMMANDES

NOTE: Contrairement à l’algèbre relationnelle, SQL n’élimine pas les dupliqués. Pour les éliminer on utilise DISTINCT :

SELECT DISTINCT PNOM

FROM COMMANDES

Le DISTINCT peut être remplacé par la clause UNIQUE dans certains systèmes

Projection : Distinct

114

Sélection


REQUÊTE: Produits commandés par Jean

PNOM (CNOM=‘’JEAN’’(COMMANDES))

SQL:

SELECT PNOM

FROM COMMANDES

WHERE CNOM = ’JEAN’

REQUÊTE : produits commandés par Jean en quantité supérieur à 100

SELECT PNOM

FROM COMMANDES

WHERE CNOM = ’JEAN’ AND QUANTITE > 100

115

Les conditions de base sont exprimées de deux façons:

1. attribut comparateur valeur

2. attribut comparateur attribut

Où comparateur est =,<, >, <>,…,

Soit le schéma de relation FOURNITURE(PNOM,FNOM,PRIX)

REQUÊTE: Produits de prix supérieur à 200F

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX > 2000

Conditions de sélection en SQL : Conditions simples

116


REQUÊTE: Produits dont le nom est celui du fournisseur

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PNOM = FNOM

117

Le comparateur est BETWEEN, LIKE, IS NULL, IN


REQUÊTE: Produits avec un coût entre 1000F et 2000F

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX BETWEEN 1000 AND 2000

NOTE: La condition y BETWEEN x AND z est équivalente à y <=z AND x <= y (NOT BETWEEN)

Conditions de sélection en SQL : Suite

118


REQUÊTE: Clients dont le nom commence par "C"

SQL:

SELECT CNOM

FROM COMMANDES

WHERE CNOM LIKE ’C%’

NOTE: Le littéral qui suit LIKE doit être une chaîne de caractères éventuellement avec des caractères jokers (_, %). Pas exprimable avec l’algèbre relationnelle.


119

Soit le schéma de relation FOURNISSEUR(FNOM,STATUT,VILLE)

REQUÊTE: Les Fournisseurs de Paris.

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE VILLE = ’Paris’

On ne trouve pas les fournisseurs avec VILLE = NULL !


120


REQUÊTE: Produits avec un coût de 100F, de 200F ou de 300F

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX IN {100,200,300}


121

Soit le schéma de relations

COMMANDES(NUM,CNOM,PNOM,QUANTITE)

FOURNITURE(PNOM,FNOM,PRIX)

REQUÊTE: Nom, Coût, Fournisseur des Produits commandés par Jean

SQL :

SELECT COMMANDES.PNOM, PRIX, FNOM

FROM COMMANDES, FOURNITURE

WHERE CNOM = ’JEAN’ AND

COMMANDES.PNOM = FOURNITURE.PNOM

NOTE: Cette requête est équivalente à une jointure naturelle. Noter qu’il faut toujours expliciter les attributs de jointure.

NOTE: SELECT COMMANDES.PNOM, PRIX, FNOM FROM COMMANDES,

FOURNITURE équivaut à un produit cartésien des deux relations, suivi d’une projection.

Jointure

122

Opération de jointure

SQL2 opération Algèbre

R1 CROSS JOIN R2R1 JOIN R2 ON R1.A < R2.BR1 NATURAL JOIN R2

produit cartésienthéta-jointureJointure naturelle

R1R2R1 R1.A < R2.B R2

R1 R2

123

Soit le schéma de relation FOURNISSEUR(FNOM,STATUT,VILLE)

REQUÊTE: Fournisseurs qui habitent deux à deux dans la même ville

SQL:

SELECT PREM.FNOM, SECOND.FNOM

FROM FOURNISSEUR PREM, FOURNISSEUR SECOND

WHERE PREM.VILLE = SECOND.VILLE AND

PREM.FNOM < SECOND.FNOM

La deuxième condition permet

1. l’élimination des paires (x,x)

2. d’éviter d’obtenir au résultat à la fois (x,y) et (y,x)

NOTE: PREM représente une instance de FOURNISSEUR, SECOND une autre instance de FOURNISSEUR.

124

Soit le schéma de relation EMPLOYE(EMPNO,ENOM,DEPNO,SAL)

REQUÊTE: Nom et Salaire des Employés gagnant plus que l’employé de numéro 12546

SQL:

SELECT E1.ENOM, E1.SAL

FROM EMPLOYE E1, EMPLOYE E2

WHERE E2.EMPNO = 12546 AND

E1.SAL > E2.SAL

125

Union



REQUÊTE: Produits qui coûtent plus que 1000F ou ceux qui sont commandés par Jean

126

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX >= 1000

UNION

SELECT PNOM

FROM COMMANDES

WHERE CNOM = ’Jean’

NOTE: L’union élimine les dupliqués. Pour garder les dupliqués on utilise l’opération UNION ALL

127

Intersection

L’intersection ne fait pas partie du standard.

EMPLOYE(EMPNO,ENOM,DEPTNO,SAL)

DEPARTEMENT(DEPTNO,DNOM,LOC)

REQUÊTE: Départements ayant des employés qui gagnent plus que 20000F et qui se trouvent à Paris

128

SQL:

SELECT DEPTNO

FROM DEPARTEMENT

WHERE LOC = ’Paris’

INTERSECT

SELECT DEPTNO

FROM EMPLOYE

WHERE SAL > 20000;

NOTE: L’intersection élimine les dupliqués. Pour garder les dupliqués on utilise l’opération INTERSECT ALL

129

La Jointure s’exprime par deux blocs SFW imbriqués




REQUÊTE: Nom, prix et fournisseurs des Produits commandés par Jean

ALGÈBRE:

PNOM,PRIX,FNOM (CNOM=‘’JEAN’’(COMMANDES FOURNITURE))

Requêtes imbriquées simples

130

SQL:

SELECT PNOM,PRIX,FNOM

FROM FOURNITURE

WHERE PNOM IN(SELECT PNOM

FROM COMMANDES

WHERE CNOM = ’JEAN’)

ou

SELECT FOURNITURE.PNOM,PRIX,FNOM

FROM FOURNITURE,COMMANDES

WHERE FOURNITURE.PNOM = COMMANDES.PNOM

AND CNOM = ‘‘JEAN’’


131

La Différence s’exprime aussi par deux blocs SFW imbriqués


EMPLOYE(EMPNO,ENOM,DEPNO,SAL)


REQUÊTE: Départements sans employés

ALGÈBRE:

DEPTNO (DEPARTEMENT )- DEPTNO (EMPLOYE)


132

SELECT DEPTNO

FROM DEPARTEMENT

WHERE DETPNO NOT IN (SELECT DISTINCT DEPTNO

FROM EMPLOYE)

ou

SELECT DEPTNO

FROM DEPARTEMENT

EXCEPT

SELECT DISTINCT DEPTNO

FROM EMPLOYE


133


REQUÊTE: Fournisseurs des Briques à un coût inférieur au coût maximum des Ardoises

SQL : SELECT FNOM

FROM FOURNITURE

WHERE PNOM = ’Brique’

AND PRIX < ANY (SELECT PRIX

FROM FOURNITURE

WHERE PNOM = ’Ardoise’)

Requêtes imbriquées plus complexes : ANY - ALL

134

le schéma de relations

COMMANDE(NUM,CNOM,PNOM,QUANTITE)


REQUÊTE: Nom, Coût et Fournisseur des Produits commandés par Jean

SQL:

SELECT PNOM, PRIX, FNOM

FROM FOURNITURE

WHERE PNOM = ANY (SELECT PNOM

FROM COMMANDE


NOTE: Les prédicats IN et = ANY sont utilisés de façon équivalente.

Requêtes imbriquées plus complexes : ANY - ALL

135

Soit le schéma de relation COMMANDE(NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Client ayant commandé la plus petite quantité de Briques

SQL:

SELECT CNOM

FROM COMMANDE

WHERE PNOM = ’Brique’ AND

QUANTITE <= ALL (SELECT QUANTITE

FROM COMMANDE

WHERE PNOM = ’Brique’)

NOTE: La condition ALL (SELECT F FROM . . . ) est vraie ssi la comparaison v est vraie pour toutes les valeurs v du résultat du bloc (SELECT F FROM . . . ).

136


EMPLOYE(EMPNO,ENOM,DEPNO,SAL)


REQUÊTE: Départements sans employés

SQL:

SELECT DEPTNO

FROM DEPARTEMENT

WHERE DETPNO NOT = ALL (SELECT DISTINCT DEPTNO

FROM EMPLOYE)

NOTE: Les prédicats NOT IN et NOT = ALL sont utilisés de façon équivalente.

137


FOURNISSEUR(FNOM,STATUS,VILLE)


REQUÊTE: Fournisseurs qui fournissent au moins un produit

SQL : SELECT FNOM

FROM FOURNISSEUR

WHERE EXISTS (SELECT *

FROM FOURNITURE

WHERE FNOM = FOURNISSEUR.FNOM)

NOTE: La condition EXISTS (SELECT * FROM . . . ) est vraie ssi le résultat du bloc (SELECT F FROM . . . ) n’est pas vide.

Requêtes imbriquées plus complexes :EXISTS

138


FOURNISSEUR(FNOM,STATUS,VILLE)


REQUÊTE: Fournisseurs qui ne fournissent aucun produit

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE NOT EXISTS (SELECT *

FROM FOURNITURE

WHERE FNOM = FOURNISSEUR.FNOM)

NOTE: La condition NOT EXISTS (SELECT * FROM . . . ) est vraie ssi le résultat du bloc (SELECT F FROM . . . ) est vide.

139

Si est un des opérateurs de comparaison <, =, >…

•La condition x ANY (SELECT Ri.y FROM R1,..,Rn WHERE (p) est équivalente à

EXISTS (SELECT * FROM R1,..,Rn WHERE p AND x Ri.y)

•La condition x ALL (SELECT Ri.y FROM R1,..,Rn WHERE p) est équivalente à

NOT EXISTS (SELECT * FROM R1,..,Rn WHERE (p) AND NOT (x Ri.y))

Formes Équivalentes de Quantification

140


COMMANDE(NUM,CNOM,PNOM,QUANTITE)


REQUÊTE: Nom, prix et fournisseur des produits commandés par Jean

SELECT PNOM, PRIX, FNOM FROM FOURNITURE

WHERE EXISTS (SELECT * FROM COMMANDE

WHERE CNOM = ’JEAN’

AND PNOM = FOURNITURE.PNOM)

SELECT PNOM, PRIX, FNOM FROM FOURNITURE

WHERE PNOM = ANY (SELECT PNOM FROM COMMANDE


141


REQUÊTE: Fournisseurs qui fournissent au moins un produit avec un coût supérieur au coût des produits fournis par Jean

SQL:

SELECT DISTINCT P1.FNOM

FROM FOURNITURE P1

WHERE NOT EXISTS (SELECT * FROM FOURNITURE P2

WHERE P2.FNOM = ’JEAN’

AND P1.PRIX <= P2.PRIX)

SELECT DISTINCT FNOM FROM FOURNITURE

WHERE PRIX > ALL (SELECT PRIX FROM FOURNITURE

WHERE FNOM = ’JEAN’)

142


FOURNITURE(FNUM,PNUM,QUANTITE)

PRODUIT(PNUM,PNOM,PRIX)

FOURNISSEUR(FNUM,FNOM,STATUS,VILLE)

REQUÊTE: Fournisseurs qui fournissent tous les produits

Division

143

SELECT FNOM

FROM FOURNISSEUR

WHERE NOT EXISTS

(SELECT *

FROM PRODUIT

WHERE NOT EXISTS

(SELECT *

FROM FOURNITURE

WHERE FOURNITURE.FNUM = FOURNISSEUR.FNUM

AND FOURNITURE.PNUM = PRODUIT.PNUM))

R1=PNOM,,FNOM (FOURNITURE) PNOM (PRODUIT)

R2= FNOM (FOURNISSEURR1))

144

Fonctions de calcul

145

REQUÊTE: Nombre de Fournisseurs de Paris

SELECT COUNT(*) FROM FOURNISSEUR

WHERE VILLE = ’Paris’

REQUÊTE: Nombre de Fournisseurs qui fournissent actuellement des produits

SELECT COUNT(DISTINCT FNOM) FROM FOURNITURE

NOTE: La fonction COUNT(*) compte le nombre des n-uplets du résultat d’une requête sans élimination des dupliqués ni vérification des valeurs nulles. Dans le cas contraire on utilise la clause COUNT(UNIQUE . . . ).

COUNT, SUM, AVG, MIN, MAX

146

REQUÊTE: Quantité totale de Briques commandées

SELECT SUM (QUANTITE)

FROM COMMANDES

WHERE PNOM = ’Brique’

REQUÊTE: Coût moyen de Briques fournies

SELECT AVG (PRIX) SELECT SUM (PRIX)/COUNT(PRIX)

FROM FOURNITURE ou FROM FOURNITURE

WHERE PNOM = ’Brique’ WHERE PNOM = ’Brique’

147

REQUÊTE: Le prix des briques qui sont le plus chères.

SELECT MAX (PRIX)

FROM FOURNITURE

WHERE PNOM = ’Briques’;

REQUÊTE: Fournisseurs des Briques au coût moyen des Briques

SELECT FNOM

FROM FOURNITURE

WHERE PNOM = ’Brique’ AND

PRIX < (SELECT AVG(PRIX)

FROM FOURNITURE

WHERE PNOM = ’Brique’)

148

Opérations d’Agrégation

149

REQUÊTE: Nombre de fournisseurs par ville

SELECT VILLE, COUNT(FNOM)

FROM FOURNISSEUR

GROUP BY VILLE

GROUP BY

150

NOTE: La clause GROUP BY permet de préciser les attributs de partitionnement des relations déclarées dans la clause FROM. Par exemple on regroupe les fournisseurs par ville.

VILLE FNOM

PARIS TOTO

PARIS DUPOND

LYON DURAND

LYON LUCIEN

LYON REMI

VILLE COUNT(FNOM)

PARIS 2

LYON 3

LA BASELE RESULTAT :

GROUP BY

151

REQUÊTE: Donner pour chaque produit fourni son coût moyen

SELECT PNOM, AVG (PRIX)

FROM FOURNITURE

GROUP BY PNOM

NOTE: Les fonctions de calcul appliquées au résultat de regroupement sont directement indiquées dans la clause SELECT. Par exemple le calcul de la moyenne se fait par produit obtenu au résultat après le regroupement.

PNOM AVG(PRIX)

Briques 10.5

ardoise 8.9

152

REQUÊTE: Produits fournis par deux ou plusieurs fournisseurs avec un coût supérieur de 100

SELECT PNOM

FROM FOURNITURE

WHERE PRIX > 100

GROUP BY PNOM

HAVING COUNT(*) >= 2

HAVING

153

PNOM FNOM PRIX

BRIQUEARDOISEARDOISE

TOTOLUCIENDURAND

105110120

PNOM FNOM PRIX

ARDOISEARDOISE

LUCIENDURAND

110120

NOTE: La clause HAVING permet d’éliminer des partitionnements, comme la clause WHERE élimine des n-uplets du résultat d’une requête. Par exemple on garde les produits dont le nombre des fournisseurs est >= 2. De cette façon des conditions de sélection peuvent être appliquées avant le calcul d’agrégat (clause WHERE) mais aussi après (clause HAVING).

Avant la clause Having Après la clause Having

HAVING

154

REQUÊTE: Produits fournis et leur coût moyen pour les fournisseurs dont le siège est à Paris seulement si le coût minimum du produit est supérieur à 1000F

SELECT PNOM, AVG(PRIX)

FROM FOURNITURE, FOURNISSEUR

WHERE VILLE = ’Paris’ AND

FOURNITURE.FNOM = FOURNISSEUR.FNOM

GROUP BY PNOM

HAVING MIN(PRIX) > 1000

155

En général, le résultat d’une requête SQL n’est pas trié. Pour trier le résultat par rapport aux valeurs d’un ou de plusieurs attributs, on utilise la clause ORDER BY :

SELECT VILLE, FNOM, PNOM

FROM FOURNITURE, FOURNISSEUR

WHERE FOURNITURE.FNOM = FOURNISSEUR.FNOM

ORDER BY VILLE, FNOM DESC

Le résultat est trié par les villes (ASC) et le noms des fournisseur dans l’ordre inverse (DESC).

ORDER BY

156

Normalisation et formes normales :

Une relation normalisée est une relation qui présente :

- le moins possible de redondance d’informations

-le moins possible d’anomalies de stockage

- Exemple :la relation ENSEIGNANT.

NOM FONCTION SALAIRE ADRESSE

Toumani PA 2000 Aubière

Lopes PA 2000 Versailles

Quilliot Professeur 3500 Clermont

Schneider Professeur 3500 Cournon

Jaudouin Professeur 3500 Clermont

De Marchi ATER 1100 Bert

157


- le couple (Professeur, 3500) est répété 3 fois.- Le couple (Maître de conférence,2000) est répété deux fois.

•Anomalie d’ajout•si on veut ajouter une nouvelle fonction (par exemple « Moniteur »)

•Anomalie de modification :•si le salaire d’un professeur (en général) change,

•Anomalie de suppression•on supprime l’ATER De Marchi, alors on perd l’information qui dit qu’un ATER gagne 1100.

Conclusion :La relation n’organise pas les informations sous une forme normalisée : elle n’est pas normalisée.

158


Solution :Décomposer ENSEIGNANT en 2 relations ENSEIGNANT et REMUNERATION dont une particulière (REMUNERATION) relative à la DF FONCTION SALAIRE.

ENSEIGNANT(Nom,Fonction, salaire)REMUNIRATION(Fonction,salaire)

•Sous cette forme il y a moins de redondance d’informations•On peut ajouter (Moniteur, 1200) dans REMUNERATION,•On peut modifier un salaire en une seule opération•On supprime l’ATER De Marchi, on ne perd pas l’information qui dit que les ATER gagnent 1100.

159

Dépendance fonctionnelle (DF)

Soit R(A,B,C,D) une relation On a :

A B (t1,t2)R2, t1(A) = t2(A) t1(B) = t2(B)

On a A B si et seulement si quel que soit le couple de tuple (t1,t2) de la relation, quand on a t1 et t2 qui ont la même valeur pour l’attribut A, alors t1 et t2 ont la même valeur pour l’attribut B.La notion de dépendance fonctionnelle s’applique aussi à un ensemble d’attributs. : A,B C

160


VILLE FONCTI NOM B_PUBLIC DATE_DE_CONt1 Casa maire Sajid mairie 1678t2 Casa préfet jalal préfecture 1854t3 Rabat maire Bahraoui mairie 1784t4 Rabat préfet Amrani préfecture 926t5 Rabat adjoint faycal mairie 1784t6 Fès maire kabaj mairie 1872

Relation PERSONNALITES

On a la DF FONCTION BATIMENT_PUBLIC

On a la DF (VILLE,BATIMENT_PUBLIC) DATE_DE_CONSTRUCTION.

161


Soit une relation R et A un attribut ou ensemble d’attribut de R.(A est clé primaire de R) ( l’attribut B de R (différent ou non de A), on a la DF A B)

162


Propriétés des dépendances fonctionnelles :

REFLEXIVITE : si un ensemble d’attributs Y est inclus dans un autre ensemble d’attributs X, alors X YExemple : {A,B,C} {A,C}, ou A AAUGMENTATION : si X Y et l’ens. des attributs W est inclus dans l’ens. des attributs Z, alors X,Z Y,WExemple : si F G alors F,A,B,C G,B,CTRANSITIVITE : si X Y et Y Z alors X ZPSEUDO-TRANSITIVITE : si X Y et Y,Z T alors X,Z TDECOMPOSITION : si X Y,Z alors X Y et X ZREUNION : si X Y et X Z alors X Y,Z

163

1ère forme normale (1NF)

relation n’ayant que des attributs atomiques, c’est-à-dire ayant au plus une valeur par tuple pour un attribut (pas plusieurs valeurs à l’intersection d’une ligne et d’une colonne), est 1NF.)

N°ISBN AUTEURS TITRE DATE_PARUTION3452986 Frédérique Amblard la route sinueuse1985

Alphonse Rebutand 3334689 Eric Germante Ludivine 1998

Léontine Truchet l’extravertieLa relation LIVRE n’est pas en 1NF car pour certains tuples, il peut y avoir plusieurs valeurs pour l’attribut AUTEURS.

164

2ème forme normale (2NF)

Une relation R(X,Y), avec X et Y des ensembles d’attributs et X clé primaire, est en 2NF si et seulement si :• elle est en 1NF,• quel que soit l’ensemble d’attribut X’ inclus dans X mais différent de X, et quel que soit l’attribut ou ensemble d’attributs Z n’appartenant pas à X (donc appartenant à Y), on n’a PAS X’Z.(autre formulation : aucune sous-partie stricte de la clé primaire n’implique un attribut qui n’est pas dans la clé)(autre formulation 2 : il n’y a aucune DF entre une sous-partie stricte de la clé et un autre attribut de la relation)

165

2ème forme normale (2NF)Décomposition

•Quand on veut mettre une relation en 2NF, c’est qu’elle n’est pas déjà en 2NF, ce qui signifie qu’il existe une sous-partie stricte X’ de la clé X qui implique un attribut ou ensemble d’attributs Z de la relation qui n’est pas dans la clé (c’est-à-dire qu’il y a une DF X’ Z) .

•Pour décomposer la relation en un ensemble de relations en 2NF, on crée une nouvelle relation, que l’on nomme comme on veut (réfléchir à un nom pertinent), et qui possède comme attributs X’ et Z, avec X’ la clé primaire de la nouvelle relation, et on enlève de R, la relation initiale, l’attribut ou ensemble d’attributs Z.

166

2ème forme normale (2NF)Décomposition Exemple

Soit la relation

COMMANDE(NOM_FOURNISSEUR, ADRESSE_FOURNISSEUR, ARTICLE, QITE, PRIX).

La clé primaire est (NOM_FOURNISSEUR, ARTICLE).

NOM_FOURNISSEUR ADRESSE_FOURNISSEUR.

COMMANDE n’est pas en 2NF

COMMANDE(NOM_FOURNISSEUR, ADRESSE_FOURNISSEUR, ARTICLE, QITE, PRIX)

COMMANDE_bis(NOM_FOURNISSEUR, ARTICLE, QITE, PRIX)FOURNISSEUR(NOM_FOURNISSEUR, ADRESSE_FOURNISSEUR)

décomposition en 2NF

167

3ème forme normale (3NF)

Une relation R(X,Y), avec X et Y des ensembles d’attributs et X clé primaire, est en 2NF si et seulement si :

- elle est en 2NF,

- il n’y a aucune DF entre deux attributs qui ne sont pas dans la clé, c’est-à-dire entre deux attributs qui appartiennent à Y.

168

3ème forme normale (3NF) Décomposition Exemple

VOITURE(N°IMMATRICULATION, COULEUR, MARQUE, MODELE).

MODELE MARQUE

VOITURE_bis(N°IMMATRICULATION, COULEUR, MODELE)

TYPE(MODELE, MARQUE)

Décomposition correspondante Pour mettre une relation en 3NF:On décompose comme d’habitude en créant une nouvelle relation à partir des attributs de la DF, avec la partie gauche de la DF comme clé primaire, puis en enlevant la partie droite de la DF de la relation initiale.

(modèle Clio Fabriquè par marque Renault)

VOITURE n’est pas en 3NF

Documents

Cours Bd Complet