Vertigo/CNAM, Paris 1

BasesdeDonnéesRelationnellesCours SGBD- Module 6A - ENSTA

M. Scholl,B. Amann

http://cedric.cnam.fr/ ver tig o/C our s/EN STA

Septembre 2001

Vertigo/CNAM, Paris 2

INTRODUCTION

Vertigo/CNAM, Paris 3

OBJECTIF DU COURS?

OBJECTIF : Comprendreetmaitriserla technologiedessystèmesdebasesdedonnées

relationnelle:

– Modélisation desdonnées

– Interr ogation desdonnées:évaluationdesrequêtes

– Mises-à-jour desdonnées:concurrenceet transactions

– Intégration desdonnées:entrepôtsdedonnées,médiateurs

– Ar chitecturesetapplicationsnouvelles:Web,portails,...

Et toutça en21 heures?

Vertigo/CNAM, Paris 4

PLAN

1. Introduction

2. ModèleRelationnel

3. CalculetAlgèbreRelationnel

4. SQL

5. OrganisationPhysique,Index

6. AlgorithmesdeJointure

7. Optimisationdesrequêtes:l’exempled’ORACLE.

Vertigo/CNAM, Paris 5

BIBLIOGRAPHIE

Ouvragesen français

1. DateC.J,IntroductionauxBasesdeDonnées, Vuibert,970Pages,Janvier 2001

2. CarrezC., DesStructuresauxBasesdeDonnées, Masson

Ouvragesenanglais

1. R. RamakrishnanetJ.Gehrke,DATABASEMANAGEMENTSYSTEMS, MacGraw

Hill

2. R. Elmasri,S.B.Navathe,Fundamentalsof databasesystems, 3eédition,1007

pages,2000,AddisonWesley

3. Ullman J.D.andWidomJ.A First Coursein DatabaseSystems, PrenticeHall, 1997

4. Garcia-MolinaH., Ullman J.andWidomJ.,Implementationof DatabaseSystems,

PrenticeHall, 1999

Vertigo/CNAM, Paris 6

5. Ullman J.D.,Principlesof DatabaseandKnowledge-BaseSystems, 2 volumes,

ComputerSciencePress

6. AbiteboulS.,Hull R., VianuV., Foundationsof Databases, Addison-Wesley

Le standard SQL

1. DateC.J.,A Guideto theSQLStandard, Addison-Wesley

TroisSystèmes

1. DateC.J.,A Guideto DB2, Addison-Wesley

2. DateC.J.,A Guideto Ingres, Addison-Wesley

3. ORACLE version7 ServerConceptsManual1992Oracle

Vertigo/CNAM, Paris 7

Applications “SGBD”

1. CLASSIQUES:

– DonnéesdeGestion(salaires,stocks,réservationsd’avions)

– ApplicationesTransactionnelles(gestiondecomptesbancaires,centrales

d’achat)

2. MOINS CLASSIQUES:

– Documentsélectroniques

– DonnéesSpatiales

3. A LA MODE:

– DonnéesduWeb:HTML, XML

– Entrepôtsdedonnées

– Portailsd’entreprise

Vertigo/CNAM, Paris 8

CommentStocker et Manipuler lesDonnées?

DONNÉES � BASE DE DONNÉES (B.D.)

– UneB.D. estunGROSENSEMBLEd’informationsSTRUCTURÉESmémorisées

surun supportPERMANENT.

LOGICIEL � SYSTÈME deGESTION deB.D. (S.G.B.D)

– Un SystèmedeGestiondeBasesdeDonnées(SGBD)estun logiciel deHAUT

NIVEAU qui permetdemanipulercesinformations.

Vertigo/CNAM, Paris 9

Diversité � Complexité

Diversité desutilisateurs, desinterfaceset desarchitectures:

1. utilisateurs : administrateurs,programmeurs,noninformaticiens,. . .

2. interfaces: langagesdeprogrammation,saisiededonnées,générationderapports,

autresSGBD. . .

3. architectures: donnéescentralisées,distribuées,hétérogènes

Vertigo/CNAM, Paris 10

En Résumé,un SGBD...

... estunOUTIL GÉNÉRIQUE qui doit répondreàdesbesoinstrèsdiversdegestion

deun GROS VOLUME D’INFORMA TIONS

– persistantes(années)et fiables(protectionsurpannes)

– partageables(utilisateurs,programmes)

– manipuléesindépendammentdeleurorganisationphysique

Vertigo/CNAM, Paris 11

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

vue 1 vue 2

NIVEAU EXTERNE

vue 3

NIVEAU LOGIQUE

NIVEAU PHYSIQUE

Vertigo/CNAM, Paris 12

FONCTIONN ALITÉS d’un SGBD

Chaqueniveaudu SGBDréaliseuncertainnombredefonctions:

NIVEA U PHYSIQUE

– Accèsauxdonnéessurmémoiresecondaire(disques),index, ...

– partagededonnéesetgestiondela concurrenced’accès

– reprisesurpannes(fiabilité)

– distributionet interopérabilité

Vertigo/CNAM, Paris 13

NIVEA U LOGIQ UE

– Définitiondela structurededonnées: LangagedeDescriptiondeDonnées(LDD)

– Consultationet MiseàJourdesdonnées: LangagesdeRequêtes(LR) etLangagede

ManipulationdeDonnées(LMD)

Vertigo/CNAM, Paris 14

NIVEA U EXTERNE : VuesUtilisateurs

1. Vue de la planification dessalles: pourchaquecours

– NomdeProf

– Horaireset salles

2. Vue de la paye: unensembledeProf

(nom,prénom,adresse,indice,nombred’heures.. . )

3. Vue du service descolarité (suivi desélèves): . . .

Vertigo/CNAM, Paris 15

Intégration de cesVues

1. On laissechaqueusageravecsavisiondumonde

2. PASSAGE DU NIVEAU EXTERNEAU NIVEAU LOGIQUE:

On“intègre” l’ensembledecesvuesenunedescriptionunique: SCHÉMA

LOGIQUE

Vertigo/CNAM, Paris 16

Modélisation desdonnées

Vertigo/CNAM, Paris 17

Modèlesdedonnées

Un modèlededonnéesestcaractérisépar :

– unestructuration desinformationset

– desopérationssurcesstructures

Vertigo/CNAM, Paris 18

Dansun SGBD, il existeplusieursmodèlesplus ou moinsabstraits desmêmesobjets,e.g. :

– le modèleconceptuel: la descriptiondu systèmed’information

– le modèlelogique: interfaceavecle SGBD

– le modèlephysique: fichiers

� cesdifférentsmodèlescorrespondentauxniveauxdansl’architectured’un SGBD.

Vertigo/CNAM, Paris 19

ModèleConceptuel:ExempleEntité-Relation

– Modèletrèsabstrait,pratiquepour:

– l’analysedumonderéel

– la conceptiondusystèmed’information

– la communicationentredifférentsacteursdel’entreprise

– Mais n’est pasassociéà un langage.

DONC UNE STRUCTURE

MAIS PAS

D’OPÉRATIONS

Vertigo/CNAM, Paris 20

Modèle logique

1. Langagede définition dedonnées(LDD) pourdécrirela structure.

2. Langagede manipulation de données(LMD) pourappliquerdesopérationsaux

données.

Ceslangagessontabstraits :

1. Le LDD estindépendantdela représentationphysiquedesdonnées.

2. Le LMD estindépendantdel’implantationdesopérations.

Vertigo/CNAM, Paris 21

Les avantagesde l’abstraction

1. Simplicité d’accès:lesstructureset leslangagessontplussimples,doncplusfaciles

pourl’usagernonexpert.

2. INDÉPENDANCE PHYSIQUE: onpeutmodifierl’implantationphysique(index,

stockage,...) sansmodifierlesprogrammesd’application

3. INDÉPENDANCE LOGIQ UE: on peutmodifierlesprogrammesd’application

sanstoucherà l’implantation.

Vertigo/CNAM, Paris 22

HISTORIQUE DESSGBD

À chaquegénérationcorrespondun modèlelogique

Lespremiersétaientpeuabstraits (navigationnels)

� 60 S.G.F. (e.g.COBOL)

mi-60 HIÉRARCHIQUE IMS (IBM) navigationnel

RÉSEAU (CODASYL) navigationnel

73-80 RELATIONNEL déclaratif

mi-80 RELATIONNEL explosionsurmicro

Fin 80 RELATIONNEL ETENDU nouvellesapplications

DATALOG (SGBDdéductifs) pasencoredemarché

ORIENTÉ-OBJET navig. + déclaratif

Fin 90 HIÉRARCHIQUE (XML) nouvellesapplicationsWeb

Vertigo/CNAM, Paris 23

Opérationssur lesdonnées

Vertigo/CNAM, Paris 24

Exemplesd’opérations

Insérer lesinformationspour l’employéJean

Augmenterle salairedeJeande10%

SupprimerlesinformationsdeJean

Chercher lesemployéscadres

Chercher lesemployésdudépartementcomptabilité

Chercher le salairemoyendesemployéscomptables,avecdeuxenfants,nésavant1960

et travaillant à Paris

Vertigo/CNAM, Paris 25

Quelstypesd’opérations?

4 typesd’opérationsclassiquesdansun SGBD:

1. La création (ou insertion) dedonnées.

2. La modification dedonnées.

3. La destruction dedonnées.

4. La recherchededonnées.

Cesopérationscorrespondentà desmanipulationsdedonnées(LMD) et sontappelésdes

requêtes. La pluscomplexe estla rechercheenraisondela variétédescritères.

Vertigo/CNAM, Paris 26

Le Traitement d’une Requête

– ANALYSE SYNTAXIQ UE

– OPTIMISA TIONGénération(parle SGBD)d’un programmeoptimiséàpartir dela connaissancede

la structuredesdonnées,del’existenced’index, destatistiquessurlesdonnées.

– EXÉCUTION POUR OBTENIR LE RÉSULTAT.

NB : ondoit tenir comptedu fait qued’autresutilisateurssontpeut-êtreentrain de

modifierlesdonnéesqu’on interroge!

Vertigo/CNAM, Paris 27

Concurrenced’accès

ArchitectureClient-Serveur:plusieursclients(utilisateurs,applications)doiventpouvoir

accéderenmêmetempsauxmêmesdonnées.

Le SGBDdoit savoir :

– Gérerlesconflitssi lesdeuxfont desmises-à-joursurlesmêmesdonnées.

– Offrir unmécanismederetourenarrièresi ondécided’annulerdesmodificationsen

cours.

– Donneruneimagecohérentedesdonnéessi l’un fait desrequêteset l’autredes

mises-à-jour.

Le but : éviterlesblocages,toutenempêchantdesmodifications“anarchiques”.

Vertigo/CNAM, Paris 28

Concurrenced’accès:Exemple

Deuxclients(A et B) partagentuncomptebancaire(X): client A retire100eurosdu

compteX et clientB dépose20 eurossurle mêmecompte:

compteX clientA client B

1 500 U=read(X)

2 500 V=read(X)

3 500 write(X,U-100)

4 400 write(X,V+20)

5 520

Qu’est-cequi s’estpassé?

Solutions:verrouillage,estampillage

Vertigo/CNAM, Paris 29

Revenonsaux Utilisateurs d’un SGBD

– L’administrateur de la base

Rôledel’administrateur/concepteur

– discuteaveclesdifférentsutilisateurs

– conceptiond’un schémalogique(etdesdifférentesvues)

– conceptionduschémaphysique

– installationdela baseet réglagesfins (tuning)

– gèrel’évolutiondela base(nouveauxbesoins,utilisateurs)

Outilsà sadispositionfournisparl’éditeurduSGBD

Vertigo/CNAM, Paris 30

– Utilisateur expert: informaticienconnaissantlangagesprogrammationet langages

BD

– Concepteuret programmeur d’applicationécrit lesapplicationspourdesutilisateurs“naïfs”

– Utilisateur naïf: dunon-spécialistedesSGBDaunon-informaticien.

Vertigo/CNAM, Paris 31

LE MODÈLE RELATIONNEL

PrésentationGénérale

Vertigo/CNAM, Paris 32

ExempledeRelation

Loic

Nadia

Loic

Julien

Marie

Espace

Espace

R5

R25

ZX

1988

1989

1978

1989

1993

VEHICULE Proprietaire Type Annee

n-uplet

Nom de la Relation Nom d’Attribut

Vertigo/CNAM, Paris 33

Le ModèleRelationnel sur un Exemple

SchémaEntités-Relations(ER):

PRIX

FOURNITFOURNISSEURS PRODUITS

QUANTITE BALANCE

CLIENTSCOMMANDE

C_ADRESSE

FADRESSE

FNOM PNOM

NUM_COMDE NOM

Vertigo/CNAM, Paris 34

FOURNISSEUR FNOM FADRESSE

Abounayan 92190Meudon

Cima 75010Paris

Preblocs 92230Gennevilliers

Sarnaco 75116Paris

FOURNITURE FNOM PNOM PRIX

Abounayan sable 300

Abounayan briques 1500

Preblocs parpaing 1200

Sarnaco parpaing 1150

Sarnaco ciment 125

Vertigo/CNAM, Paris 35

COMMANDES NUM_COMDE NOM PNOM QUANTITE

1 Jean briques 5

1 Jean ciment 10

3 Paul briques 3

4 Paul parpaing 9

5 Vincent parpaing 7

CLIENTS NOM CADRESSE BALANCE

Jean 75006Paris -12000

Paul 75003Paris 0

Vincent 94200Ivry 3000

Pierre 92400Courbevoie 7000

Vertigo/CNAM, Paris 36

Terminologieet Définitions

Vertigo/CNAM, Paris 37

BasedeDonnées:Domaine,Nuplet, Relation

– Un DOMAINE estunensemble(fini ou infini) devaleurs.

Exemples: � ����� � ������� , ������������� ����� �!�#" �!�%$&�#" $&�('�'�')� , *+�,��-.�/� l’ensemble

deschaînesdecaractères,*+�,��-.�/�102�3�546 l’ensembledeschaînesdecaractèresde

longueur10.

– Un NUPLETestuneliste devaleurs 087:9;��'('�'<�=7?>@4 .Exemples:0BADC EF�;GH�JILK&IL�AM�NAPO3IQEF�8�RAB���R�54 , 02�!�SAUTV�3IW�XAY�NADEZ��-\[ G]� ORA8�_^!4

– Le PRODUIT CARTÉSIEN ` 9 a '�'(' a ` > entredesdomaines 9 �('�'�'<�N` > est

l’ensembledetous lesn-uplets 0U7 9 ��'('�'<�=7 > 4 où 7cbed ` b .– UneRELATION estunsous-ensemblefini d’un produitcartésien 9 a '�'(' a ` > .

– UneBASEDE DONNÉESestun ensemblederelations.

Vertigo/CNAM, Paris 38

SchémadeBasesdeDonnées

– Un ATTRIBUT estdéfini parun nometundomaine.

Exemples:f g h i�jlk,m�nporq , s t u f v wWv x i#w�o�k�y�q�y�m .

– Le SCHÉMAD’UNE RELATION z estdéfiniparunnometunelisted’attributs.

Notation:

z {Bu | i5} |(~��(���<~Nu � i5} ���ouplussimplement:

z {Bu | ~��(���<~Nu � �– Un SCHÉMA DE BASESDE DONNÉESestunensembledeschémasderelation.

Remarque:unerelationnepeutpasavoir deuxattributsdumêmenomet unattribut est

toujoursidentifiéà traversunerelation.Ainsi, deuxrelationspeuventavoir desattributs

demêmenomsavecdestypesdifférents(variableslocales).

Vertigo/CNAM, Paris 39

Instancesd’un Schéma

– INSTANCE D’UN SCHÉMADE RELATION : Uneinstance� d’un schémade

rélation � �B� � �5� �R���(���<�N� � �5� ��� estunsous-ensemblefini duproduitcartésien� � � �(����� � � : l’ensemble� estunerelation.

– INSTANCE D’UN SCHÉMADE BD: Uneinstance� d’un schémadebasesde

donnéesestunensembled’instancesdesesschémasderelation: � estunebasede

données.

Remarque:unerelation � estreprésentéesousformed’unetable. L’ordredescolonnes

(attributs)oudeslignesn’a pasd’importance(ensemble).

Vertigo/CNAM, Paris 40

Exemplede BasedeDonné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)

Vertigo/CNAM, Paris 41

Exemplede BasedeDonnées

UNIVERSD’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)

Vertigo/CNAM, Paris 42

Revenonsà l’exempledu début

Loic

Nadia

Loic

Julien

Marie

Espace

Espace

R5

R25

ZX

1988

1989

1978

1989

1993

VEHICULE Proprietaire Type Annee

n-uplet

Nom de la Relation Nom d’Attribut

– Traduction:Schémaconceptuel(ER) � Schémalogique(relationnel)

– Plusprécisémment:Descriptiondumonderéel � Schémaconceptuel� Schémalogique

Objectif:Descriptiondumonderéel � Schémaconceptuel� Schémalogique?

Vertigo/CNAM, Paris 43

Contraintes d’integrité

Objectif:Schémalogique+ contraintes� Descriptiondumonderéel

– FOURNISSEUR(FNOM,FADRESSE)

– FOURNITURE(FNOM,PNOM,PRIX)

– COMMANDE(NUM_CMDE, NOM, PNOM,QUANTITÉ)

Exemplesdecontraintes:

1. Chaquefournisseuràunnomdifférent.

2. Chaquefournisseuràuneseuleadresse.

3. Chaquefournisseurfournit un produità unseulprix.

4. Chaquecommandeestfaiteparunseulclient.

Vertigo/CNAM, Paris 44

– Contraintes1 et2: FNOM estuneclédansFOURNISSEUR

– Contraintes1 et3: (FNOM, PNOM) estuneclédansFOURNITURE

– Contrainte4: NUM_CMDE “détermine”NOM dansCOMMANDE :

1. deuxnupletsavecla mêmevaleurpourNUM_CMDE ont la mêmevaleurpour

NOM.

2. notation:NUM_CMDE � NOM (dépendancefonctionnelle)

Attention:NUM_CMDE n’estpasuneclé.

Remarque:lesdépendancesfonctionnellesneréprésententqu’un typedecontraintesqui

peuventexisterdansunschémalogique(dépendanced’inclusion,dépendances

multi-valuées,...).

Vertigo/CNAM, Paris 45

Opérationset Langages

Vertigo/CNAM, Paris 46

Opérationssur uneBasedeDonnéesRelationnelle

– LANGAGE DE DÉFINITION DESDONNÉES(définitionet MAJ du schéma):

– Créationetdestructiond’un schémaderelation(et desoninstance).

– Modificationduschéma:ajout,suppressiond’un attribut

– Définitiondecontraintes.

Vertigo/CNAM, Paris 47

– LANGAGE DE MANIPULATION DESDONNÉES

– Saisiedesnupletsd’unerelation

– Affichaged’unerelation

– Modificationd’unerelation: insertion,suppressionet majdesnuplets

– Requêtes: consultationd’unerelationoucalculd’unenouvelle relation

– GESTIONDESTRANSACTIONS

– GESTIONDESVUES

Vertigo/CNAM, Paris 48

LangagesdeRequêtesRelationnels

POUVOIR D’EXPRESSION: Qu’est-cequ’onpeutcalculer? Quellesopérations

peut-onfaire?

Leslangagesderequêterelationnelsutilisentdeuxapproches:

– calculrelationnel

– algèbrerelationnelle

Lesdeuxapprochesontmêmepouvoir d’expression.

Vertigo/CNAM, Paris 49

LES DÉPENDANCES FONCTIONNELLES

Vertigo/CNAM, Paris 50

COURS

ETUDIANT

PROF

enseigne

suit

0,n

0,n

0,n

1,1

Nom-Cours

Nom-Etudiant

Nom-Prof

Vertigo/CNAM, Paris 51

Exemples: Relation PERSONNE

PERSONNE NOSS NOM VILLE

123 toto Paris

324 mimi Paris

574 toto Marseilles

Qu’est-cequ’onpeutdiresurla tablePERSONNE?

– “Connaissantle numérodesécuritésocialeNOSS, je connaisle NOM”

– L’attribut NOSSdéterminel’attribut NOM

– L’attribut NOMdépendfonctionnellementdel’attribut NOSS

– Attention: Connaissantle NOMon neconnaîtpasle NOSS: NOM�� NOSS

NOTATION : NOSS� NOM

Vertigo/CNAM, Paris 52

Relation COURS

COURS NOM-COURS PROF ETUDIANT

BDPI MATHELOT 123

BDPI MATHELOT 324

BDPI MATHELOT 574

IPA HARDIN 123

– “Un Coursa unseulProfesseur”

– NOM-COURS� PROF

– 2 nupletsqui ontmêmevaleurpourNOM-COURSontmêmevaleurpourPROF

Vertigo/CNAM, Paris 53

Clés,Supercléset Clésétrangères

Relation:PERSONNE(NOSS, NOM, PRENOM,ADRESSE)

NOSS� NOM, NOSS� PRENOM, NOSS� ADRESSE

– Le NOSSestuneclé.

NOSS NOM� PRENOM

NOSS NOMestunesuperclé (NOMestredondant)

Relation:COURS(NOM-COURS, PROF, ETUDIANT)

Si � estunevaleurdel’attribut ETUDIANTdansla tableCours , alors � estuneclédans

la tablepersonne.

– ETUDIANTestunecléétrangère(dépendanced’inclusion)entrela tableCOURSet

la tablePERSONNE.

Vertigo/CNAM, Paris 54

Définitions

a) Dépendancefonctionnelle

Soit R(U) unschémaderelation,r unerelationdeschémaR, X � U, Y � Udeux

sous-ensemblesd’attributsdeR. La dépendancefonctionnelle

X � Y

estvrai dansr , ssi(si et seulementsi) touslesnupletsder qui ontmêmevaleur

pour(tous)lesattributsdeX, ontmêmevaleurpour(tous)lesattributsdeY.

Exemple:

ADRESSE(RUE, NUMERO,VILLE, CODE-P)

X = CODE-P

Y = VILLE

Vertigo/CNAM, Paris 55

b) Superclé

Soit R(U) unschémaetX � Uunsousensembled’attributs.

X estunesupercléder deschémaR, si X � U.

Exemple:

NOSS NOM� NOSS NOMPRENOMADRESSE

NOSS NOMestunesuperclé

c) Clé

X estuneclé,si :

1. X estunesuperclé: X � U

2. il n’existepasY � X, tel queY � U

Exemple:

RUE NUMEROVILLE � RUE NUMEROVILLE CODE-P

RUE NUMEROVILLE estuneclé (pourquoi?)

Vertigo/CNAM, Paris 56

Calcul d’une Clé: Exemple

COURS(Nomc,Heure,Salle,Prof)� �N   HS, HS   P

Nouspouvonsprouver àpartir de�

quesi onconnaîtle nomdecours,onconnaîtaussile nomduprofesseur(Nomcestuneclé):

1. N   HS: deuxnupletsqui partagentle nomdecourspartagentégalementl’horaireet la salle

2. HS   P : deuxnupletsqui partagentl’horaireet la sallepartagentégalementlenomduprofesseur

3. DF 1 et 2 impliquent,quedeuxnupletsqui partagentle nomdecourspartagentégalementle nomdu prof : N   P

Onpeutdéfinir despropriétés(règles)surlesDF qui permettentdedéduired’autresDF.

Vertigo/CNAM, Paris 57

PropriétésdesDépendancesFonctionnelles

(Axiomesd’Armstrong)

1) Réflexivité

si X ¡ Y alorsY ¢ X (pourtouslesensemblesd’attributsX et Y)

Exemple:

NOMVILLE ¢ NOM

Trivial : “Deux personnesqui ont le mêmenomethabitentla mêmeville, ont le

mêmenom.”

Vertigo/CNAM, Paris 58

2) Transitivité

si X £ Y et Y £ Z, alorsX £ Z

Exemple :

R(NOSS, CODE-P, VILLE)¤NOSS £ CODE-P, CODE-P £ VILLE ¥ ¦ NOSS £ VILLE

“Si onconnaîtle codepostaleàpartir dunumérodesécuritésocialeet la ville à

partir ducodepostale,on connaîtla ville à partir dunumérodesécuritésociale.”

3) Augmentation

X £ Y ¦ XZ £ YZ

Exemple:

NOSS £ CODE-P ¦ NOSS VILLE £ CODE-P VILLE

Vertigo/CNAM, Paris 59

4) Union et décomposition§X ¨ A, X ¨ B ©«ª X ¨ AB

5) Pseudotransitivité§X ¨ Y, WY ¨ Z © ¬ WX ¨ Z

REMARQ UE : Union,décompositionet pseudotransitivité peuventêtredéduitesdes

autresaxiomes

Vertigo/CNAM, Paris 60

Clôtur e d’un ensemblededépendancesfonctionnelles

De l’ensembledesdépendancesfonctionnellesdonnéesparl’analysedumonderéel,en

utilisantlespropriétésci-dessus,appeléesAxiomesd’Armstrong, onpeutendéduire

d’autres:

Exemples:

1) R(A,B,C,D)­= ® A ¯ B, B ¯ C °

Par transitivité, on déduitA ¯ C

Notation:

­ ± ²A ¯ C

Vertigo/CNAM, Paris 61

2) R(Cours, Prof, Heure,Salle,Eleve,Note)³= ´ C µ P, HS µ C, HP µ S, CE µ N, HE µ S ¶³ · ¸

HS µ P, HE µ C,P,N

DoncHEestuneclé. Pourquoi?

L’union de³

etdetouteslesdépendancesainsidéduitesestappeléeclôture, ou

couvertur e de³

etnotée³ ¹

.

Vertigo/CNAM, Paris 62

Nouvelle définition d’une Superclé

Soit R(U) unschémaderelation, º unensemblededépendancesfonctionnellesetX »Uun ensembled’attributs.X estunesuperclédeRsi pourtoutA ¼ U

º ½ ¾ X ¿ A

ouencoresi:

X ¿ A ¼ º ÀCalculerº À àpartir de º peutêtretrèslong.

Montrerque º ½ ¾ X ¿ A estplusfacile.

Vertigo/CNAM, Paris 63

Calcul de la cléd’une relation

Soit R(U) unschémaderelationet X Á Uunensembled’attributs.

– Ondéfinit  à commeensembledesattributsA telsque Ä Å Æ X Ç A

– Si A appartientà Â Ã , alorspardéfinition Ä Å Æ X Ç A

– Â Ã estl’ensembledesattributsfonctionnellementdépendantsdeX.

Pourcalculerunecléonutilise l’algorithmesuivant:

1. OnchercheunX tel que  à = U È X estunesuperclé

2. X estuneclé,s’il n’existepasY É X tel que Ê Ã = U

Vertigo/CNAM, Paris 64

Calcul de Ë Ì

Étape 1: On partdeX

PourtouteY Í A, telle queY Î X, Y Í A Ï Ð , on rajouteA àX.

Onobtient Ñ Ò .Étape i: Onpartde Ñ ÓBÔ Ò

PourtouteY Í A, telle queY Î Ñ ÓBÔ Ò , Y Í A Ï Ð , on rajouteA à Ñ ÓBÔ Ò .Onobtient Ñ Ó .

Ons’arrêtequandonnetrouve plusdenouvelle DF (point fixe):

Ñ Õ = Ñ ÓÖÕ Ò = Ñ Ó

Vertigo/CNAM, Paris 65

Exemples

1. MontrerqueHEestuneclépourR(CHENSP)avecl’ensemble× deDF donné

ci-dessus

2. Pourla relationADRESSE(VILLE, RUE, NUMERO,CODE_P)ci-dessus,

montrerqueVILLE RUE NUMEROestuneclé.Quelleestl’autre?

Vertigo/CNAM, Paris 66

Contraintes d’intégrité

La listedesattributsestinsuffisantepourdécrirela sémantiquedumonderéel.

Il existeplusieurstypesdecontraintessurlesnuplets:

1. dépendances(fonctionnelles,d’inclusion,multivaluées,etc.)

2. contraintesqui dépendentdu domained’un attribut : Taille Ø 2m10,annéeØ 2000

3. etc.

Cesontlesdépendancesqui permettentla conceptiond’un bonschéma.

Vertigo/CNAM, Paris 67

ANOMALIES DE MISE À JOUR

Vertigo/CNAM, Paris 68

Exemple

Soit le schémaS1:

FOURNISSEUR(FNOM,FADRESSE)

FOURNITURE(FNOM,PNOM, PRIX)

et l’ensembledeDF: Ù Ú Û FNOM Ü FADRESSE, (FNOM PNOM) Ü PRIX ÝSupposonsqu’on remplaceS1parle schémaS2:

R(FNOM, FADRESSE, PNOM, PRIX)

Vertigo/CNAM, Paris 69

Anomalies

R (FNOM, FADRESSE, PNOM, PRIX)Þ ß àFNOM á FADRESSE, (NOM PNOM) á PRIX â

Quelleestla clédeR?

TOTO LYON BRIQUES 1000

DUPONT ROUEN BRIQUES 900

TOTO LYON BETON 400

1) REDONDANCE : l’adressed’unepersonneapparaîtplusieursfois.

2) MAJ : si on modifiel’adressedansunnuplet,il fautle fairedanslesautres.

Vertigo/CNAM, Paris 70

3) SUPPRESSION: si DUPONTnefournit plusdeBRIQUES, onsupprimele 2e

nuplet,onperdtouteinfo surDUPONT

4) INSERTION : onnepeutinsérerunnouveaufournisseuret sonadresse,si onne

connaîtpasaumoinsunproduitqu’il fournit

TOTO PARIS BRIQUES 1000

TOTO LYON BETON 400

DURAND NICE

ãä LE SCHÉMAINITIAL S1EST“MEILLEUR”

Vertigo/CNAM, Paris 71

Qualitésd’un bon schéma

1. Éviter lesanomaliesåæ décomposition

2. La décompositiondoit conserver la mêmeinformationLa jointured’unerelationf1 deschémaFOURNISSEUR(FNOM,FADRESSE)

etd’unerelationf2 deschémaFOURNITURE(FNOM,PNOM, PRIX)

obtenuespardécompositiond’unerelationr deschémaR(FNOM, FADRESSE, PNOM, PRIX)

doit redonnerr .

3. La décompositiondoit conserver lesmêmescontraintes(DF). La décompositiondeRenR1(FNOM, FADRESSE, PRIX) et R2(PNOM, PRIX) nepréserve paslesDF. Pourquoi?

Vertigo/CNAM, Paris 72

FORMES NORMALES ET DÉCOMPOSITION

Vertigo/CNAM, Paris 73

Relation en première forme normale (1FN)

– Touslesattributssontatomiques(élémentaires)

– Relationstellequ’on lesconnaît.

– Relation non normalisée,non en 1eFN :

Certainsattributssontdesensemblesdevaleurs,desrelationsellesmême

Vertigo/CNAM, Paris 74

Relation 1FN

NOTES ( COURS ETUDIANT NOTE )

BDB Toto 15

BDB Lulu 17

BDB Lili 0

ARCHI Lili 20

ARCHI Toto 0

Relation N1FN

NOTES ( COURS PERF (ETUDIANT NOTE) )

BDB Toto 15

Lulu 17

Lili 0

ARCHI Lili 20

Toto 0

Vertigo/CNAM, Paris 75

Relation en3eforme normale

– 2eforme normale :

Purementhistorique

– 3eforme normale: 3FN

– évitela plupartdesanomalies

le but du jeu : décomposerunerelation(1FN) enunensemblederelations3FN

Vertigo/CNAM, Paris 76

3FN: Définition

Définition : UnerelationResten3FN,si quellequesoit la DF X ç A de è é où

– A estunseulattribut et

– A n’estpasl’un desattributsdeX,

– soit X estunesuperclé (contientuneclé),

– soit A appartientà l’une desclés.

En fait, il n’estpasnécessairedevérifier touteslesDF de è é .

Il suffit devérifiercellesde è !

Vertigo/CNAM, Paris 77

Exemples:3eForme Normale

1) Poste(Ville, Rue,Code)ê= ë VR ì C, C ì V í

Clés: {V,R} et {R,C}

Resten3FN.

2) FOURNITURE(NOMF , ADR, NOMP, PRIX)ê= ë NOMF ì ADR, NOMF, NOMP ì PRIX í

Clé: {NOMF, NOMP}

FOURNITUREn’estpasen3FN.

Vertigo/CNAM, Paris 78

3) PLANNING(Cours, Heure,Salle)î= ï SH ð C, C ð S ñ

Clés: {S,H} et {C,H}

PLANNINGesten3FN.

4) R(A, B, C, D)î= ï AB ð C, B ð D, BC ð A ñ

Clés: {A, B}, {B, C}

Rn’estpasen3FN.

Vertigo/CNAM, Paris 79

Décompositionsansperte d’inf ormation (SPI)

Exemple

R ( A B C )

a b c

a b a

c b d

et ò = ó A ô B õDécomposonsen:

R1 ( A B ) R2 ( B C )

a b b c

c b b a

b d

Vertigo/CNAM, Paris 80

R1 = ör÷+øúù (R)

R2 = ö ùûøýü (R)

R’ = R1 þ R2 ÿ� R:

R’ ( A B C )

a b c

a b a

a b d

c b c

c b a

c b d

La décompositiondeRenR1 etR2 estavecperted’informations.

La jointurecréedesnupletsqui n’existaientpasdansR.

Vertigo/CNAM, Paris 81

DécomposonsR’ maintenanten:

R1’( A B ) R2’ ( A C )

a b a c

c b a a

c d

R” = R1’�

R2’ = R’ :

R’’ ( A B C )

a b c

a b a

c b d

Cettedécompositionestsansperted’information(SPI)

Il fautqu’aprèsla jointure,on retrouve la mêmeinformationqu’avantla décomposition.

Vertigo/CNAM, Paris 82

Définition

UnedécompositiondeRenR1, R2, ..., Rk parrapportà unensembledeDF �estSPI(sansperted’information),ssiquellequesoit r deschémaRsatisfaisant� , ona:

r = ����� (r ) � ��� (r ). . . � ����� (r )

Théorème:

Si (R1, R2) estunedécompositiondeRet � un ensembledeDF, alors(R1, R2)

estSPIparrapportà � , ssi:

R1 R2 � R1 - R2

ou

R1 R2 � R2 - R1

estunedépendancede � � .

Vertigo/CNAM, Paris 83

Exemples

R (A, B, C)�= � A � B �

1) R1(A, B), R2(B, C)

AB � BC= B

AB - BC= A

BC- AB= C

Il n’y ani la DF B � A, ni la DF B � Cdans� �� Décompositionavecperte d’inf ormation

Vertigo/CNAM, Paris 84

2) R1(A, B), R3(A, C)

AB � AC= A

AB - AC= B

A � B estdans� ( � � ).� Décompositionsansperte d’inf ormation

Vertigo/CNAM, Paris 85

Décompositionqui préserve lesdépendancesfonctionnelles

Définitions

1. Projectiond’un ensemblededépendancessurZ � U��� ( � ) = "! # $ % � & '(! $ ) * +Exemple: R(A,B,C,D) , � = AB # C, C # A, A # D +�-,�.0/ ( � ) = AB # C, C # A +

2. Décompositionqui préserve lesDF de �Soit 1 = (R1,...,Rk) unedécomposition,et � unensembledeDF.1 préserve lesDF de � , si onpeutretrouver touteslesDF de � & à partir de

l’union 2 detouteslesDF projetéesde � dans�43�5 ( � ) , . . . , ��3�6 ( � ) :27& = � &

Vertigo/CNAM, Paris 86

Exemples

R(A,B,C,D)8= 9 AB : C, C : A, A : D ;<= (ABC, BD) nepréserve paslesDF de

8<

= (ABC, AD) préserve lesDF de

8R(A,B,C)8

= 9 A : B, B : A, A : C ;<= (AB, BC) préserve lesDF de

8

Vertigo/CNAM, Paris 87

R(A, B, C, D)== > A ? B, B ? C, AB ? D @

La décomposition

R1(AC)

R2(AB)

R3(CD)

nepréserve paslesDF de=

. Pourquoi?

Vertigo/CNAM, Paris 88

Décompositiond’une relation en relations3FN

Étantdonnéun schéma(R, F) nonen3FN, i.e. avecdesanomalies,onveutune

décompositiondeR:

1. enrelations3FN

2. qui soit SPI

3. qui préserve lesDF de A

Vertigo/CNAM, Paris 89

Remarque:

– unedécompositionSPInepréserve pasforcémentlesDF et inversement

– le résultatnedonnepasforcémentdesrelations3FN

Théorème: Touterelationen1FNpossèdeunedécompositionenrelations3FN qui soit

SPIetpréserve lesdépendancesfonctionnelles.

Vertigo/CNAM, Paris 90

Algorithme de décomposition

Onsupposeque B estunecouvertureminimale

1. PourchaqueX C A D B , créerunerelationdeschéma(XA) .

2. Si aucunedesclésn’estcontenuedansl’un desschémascréésdansl’étape1,

rajouterunerelationdeschéma(Y) , où Y estuneclé.

3. Si aprèsl’étape1, il existeunerelationR1 dontle schéma(X1A1) estcontenudans

le schéma(X2A2) d’uneautrerelationR2, supprimerla relationR1.

4. Remplacerlesrelations(XA1),..., (XAk) (correspondantà desdépendances

ayantmêmemembregauche)parunerelationunique: (XA1...Ak) .

Vertigo/CNAM, Paris 91

Exemples

1) R(A,B,C,D)E= F AB G C, B G D, C G A H

Clés: AB, BC

– Étape1: R1(ABC) R2(BD) R3(CA)

– Étape2: Pasla peinederajouterunerelationdeschémala cléAB:

ABestcontenuedansR1

– Étape3: SupprimerR3 : CA I ABC

Bonnedécomposition: R1(ABC) R2(BD)

OnpeutvérifierqueR1etR2sonten3eFN.

Vertigo/CNAM, Paris 92

2) R(A,B,C,D,E)J= K AB L C, C L D, C L A M Clés: ABE, BCE

– Étape1: R1(ABC) R2(CD) R3(CA)

– Étape2: Onrajouteunerelationdeschémapourla cléABE: R4(ABE)

– Étape3: SupprimerR3 : CA N ABC

Bonnedécomposition: R1(ABC) R2(CD) R4(ABE)

R3n’a pasdedépendance.Quellessontlesdépendancesdesautres?

Autr e solution:

– Étape4: OnremplaceR2et R3del’étape1 parunerelationdeschéma(CAD)

Autr e bonnedécomposition: R1(ABC) R2’(CAD) R4(ABE)

Quesepasse-t-ilsi onavait choisila cléCBE?

Vertigo/CNAM, Paris 93

3) R(A,B,C,D)O= P AB Q C, C Q D, C Q A, AB Q D R

Clés: BA, BC

La relationn’estpasen3eFormeNormale.Pourquoi?

– Étape1: R1(ABC) R2(CD) R3(CA) R4(ABD)

– Étape2: onnerajoutepasderelation: cléAB S R1(ABC)

– Étape3: SupprimerR3 : CA S ABC

– Étape4: OnremplaceR1 etR4 parR5(ABCD) T onpeutsupprimerR2.

Décomposition: R5(ABCD)

Cettedécompositionn’estpasen3eFormeNormale.Oùestle problème?

Vertigo/CNAM, Paris 94

Forme Normale Boyce-Codd(BCNF)

Desanomaliessubsistenten3FN.

Exemple: Poste(Ville,Rue,Code) , U = V VR W C, C W V XClés: VR, RC

Poste ( Ville Rue Code )

Paris St Michel 75005

Paris Champollion 75005Y Redondanceentrele codeet la ville.

Vertigo/CNAM, Paris 95

Définition : UnerelationestenformenormaledeBoyce-Codd(BCNF),si quellequesoit

la dépendancede Z , le membredegaucheestuneclé.

Intérêt : On aéliminétouteslesanomalies

Remarque: TouterelationBCNF esten3FN

Malheureusement, il n’existepastoujoursunedécompositionenrelationsBCNF

– qui soit SPI

– qui préserve lesDF

Vertigo/CNAM, Paris 96

L’exemplede la poste

Poste(Ville,Rue,Code) , [ = \ VR ] C, C ] V ^Clés: VR, RC

Rest3FNmaisn’estpasBCNF(dansC ] V, Cn’estpasuneclé)

Poste ( Ville Rue Code )

Sevres de Gaulle 92310

Chaville de Gaulle 92370

Vertigo/CNAM, Paris 97

La décompositionR1(Ville,Code), R2(Rue,Code) évitela redondance

Ville,Code , elle estSPI,maiselle nepréserve pasla dépendanceVR _ C

R1 ( Ville Code ) R2 ( Rue Code )

Sevres 92310 de Gaulle 92310

Chaville 92370 de Gaulle 92370

L’insertionSevres de Gaulle 92190 , c.a.d.Sevres 92190 et de Gaulle

92190 respecteC _ V maisnerespecteplusVR _ C

R1 ( Ville Code ) R2 ( Rue Code )

Sevres 92310 de Gaulle 92310

Chaville 92370 de Gaulle 92370

Sevres 92190 de Gaulle 92190

Vertigo/CNAM, Paris 98

ALGÈBRE RELATIONNELLE

Vertigo/CNAM, Paris 99

Algèbre Relationnelle

– uneopérationprendenentréeuneoudeuxrelations

– le résultatesttoujoursunerelation

Vertigo/CNAM, Paris 100

Opérationsde l’Algèbr e Relationnelle

5 Opérationsdebasepourexprimertouteslesrequêtes:

– Opérationsunaires: sélection,projection

– Opérationsbinaires: union,différence,produitcartésien

– Autresopérationsqui s’exprimentenfonctiondes5 opérationsdebase: jointure

(naturelle, -jointure),intersection,division

Vertigo/CNAM, Paris 101

Projection

LA PROJECTION“ÉLIMINE” UNE OU PLUSIEURSCOLONNESD’UNE

RELATION.

Notation:

a b c bedfdfdgb

Vertigo/CNAM, Paris 102

Projection: Exemples

a)Onéliminela colonneh dansla relation i :

R A B Cj a b c

d a b

c b dj a b e

e e a

kl m npo q r

A B

a b

d a

c b

e e

Le nuplet sut npvxw n’apparaîtqu’une fois dansla relation y-z�{}| s~i w, bienqu’il existedeux

nuplets sut n�v�n���w et s�t n�v(n���w dansi .

Vertigo/CNAM, Paris 103

Projection: Exemples

b) On éliminela colonne� dansla relation � (ongarde � et � ) :

R A B C

a b c

d a b

c b d

a b e

e e a

�� � �p� � � A C

a c

d b

c d

a e

e a

Vertigo/CNAM, Paris 104

Sélection

Sélectionsurla condition � :

Ongardelesnupletsqui satisfont� .

NOTATION :

Vertigo/CNAM, Paris 105

Sélection:Exemples

a)Onsélectionnelesnupletsdansla relation � telsquel’attribut � vaut“b” :

R A B C

a b 1

d a 2

c b 3

a b 4

e e 5

�� � � ����������� � A B C

a b 1

c b 3

a b 4

Vertigo/CNAM, Paris 106

Sélection:Exemples

b) On sélectionnelesnupletstelsque� � ���  ¡���£¢ � ���  £�¤�¦¥ § ¨:

R A B C

a b 1

d a 2

c b 3

a b 4

e e 5

© ª�«¤¬®­°¯±¯³²�¯´¯¤µ·¶0­�¯´¯³²�¯´¯¹¸»º½¼¿¾4ÀÁu Ã

A B C

a b 1

d a 2

Vertigo/CNAM, Paris 107

Sélection:Exemples

c) Onsélectionnelesnupletstelsquela 1reet la 2ecolonnesontidentiques:

R A B C

a b 1

d a 2

c b 3

a b 4

e e 5

Ä Å Æ Ç È É Ê A B C

e e 5

Vertigo/CNAM, Paris 108

Condition deSélection

La condition Ë d’unesélectionpeutêtreuneformule logiquequelconqueavecdeset ( Ì )etdesou ( Í ) entretermesdela forme Î ÏuÐÑÎ¡Ò et Î Ï�ÐÔÓ où

– Î Ï et Î Ò sontdesattributs,

– Ó estun élément(unevaleur)dudomainede Î Ï ,– Ð estl’un de Õ ÖØ× ÖÚÙ ÖØÛ ÖÚÜ Ö�ÝÕ .

Vertigo/CNAM, Paris 109

Expressionsde l’Algèbr e Relationnelle

– le résultatd’uneopérationestunerelation

– surcetterelation,onpeutfaireuneautre opération del’algèbre

Lesopérationspeuventêtre composéespour formerdesexpressionsdel’algèbre

relationnelle.

Vertigo/CNAM, Paris 110

Expressionsde l’Algèbr e Relationnelle

EXEMPLE: Þ ß à à á â ã ä åçæuâ ß à è�é â ß à èêâ ë à è�ì í ä îï ð�ðòñ ó�ô°õ¡ö0÷ æ

øùú ûxü ýþ õ£ö�÷ ÿ ù±ù��������Ñù´ù æ�Þ ß à à á â ã ä å î îLa relation

ï ð æuâ ß à è�é â ß à èêâ ë à è�ì í ä î contientlesnupletsdontl’attribut â ß àa la valeur

ð�ð����� ®ð�ð. La relation

ï ð�ð æ~é â ß à î contienttouslesproduitscommandéspar

Jean.

Vertigo/CNAM, Paris 111

Produit Cartésien

– NOTATION : � � �– ARGUMENTS: 2 relationsquelconques:

� ��� ����� ������������� "! �#�%$ ���&$ �'�������(��$ )*!– SCHÉMADE + , � � � : + �-� ����� ������������� .�&$ ���&$ �*����������$ )�!– VALEUR DE + , � � � : ensembledetouslesnupletsayant/ 0 1 composants

(attributs)

– dontles / premierscomposantsformentunnupletde �– et les 1 dernierscomposantsformentunnupletde �

Vertigo/CNAM, Paris 112

ExempledeProduit Cartésien

R A B

1 12'3 21 2

3 4

S C D E

a b a254 2a b c

b a a

6

Vertigo/CNAM, Paris 113

R 7 S A B C D E

1 1 a b a

1 1 a b c

1 1 b a a

1 2 a b a8'9 8 7 85: 81 2 a b c

1 2 b a a

3 4 a b a

3 4 a b c

3 4 b a a

Vertigo/CNAM, Paris 114

Jointur e Naturelle

– NOTATION : ; < =– ARGUMENTS: 2 relationsquelconques:

; >-? @�A�B�B�B�A�? C AED @�A�B�B�B�AED F�G =#>%H @�A�B�B�B�A�H IJAKD @�A�B�B�B(AED F�Goù D @ A�B�B�B�AED F sontlesattributsencommun.

– SCHÉMADE L M ; < = : L >-? @�A�B�B�B�A�? C A�H @�A�B�B�B�A&H INAKD @�A�B�B�B�AKD F�G– VALEUR DE L M ; < = : ensembledetouslesnupletsayantO P Q P R attributs

dontles O premierset R dernierscomposantsformentunnupletde ; et les Q P Rdernierscomposantsformentunnupletde = .

Vertigo/CNAM, Paris 115

Jointur e Naturelle: Exemple

R A B C

a b c

d b c

b b f

c a d

S B C D

b c d

b c e

a d b

S

R T S A B C D

a b c d

a b c e

d b c d

d b c e

c a d b

Vertigo/CNAM, Paris 116

Jointur e Naturelle

Soit U V WYX Z�[�\�\�\�[�X ] [&^ Z�[�\�\�\�[&^ _J[E` Z�[�\�\�\�[E` acb l’ensembledesattributsdes2 relations

et d V W�` Z [�\�\�\�[K` a b l’ensembledesattributsencommun.

e f g V hjilk�mon*prqtsvuxwryxpjz|{�yxp}k e ~ g����

NOTATION :e \�X veutdire “l’attrib ut X dela relation

e”.

Vertigo/CNAM, Paris 117

Jointur e Naturelle: Exemple

R A B

1 a

1 b

4 a

S A B D

1 a b

2 c b

4 a a

�

R � S R.A R.B S.A S.B D

1 a 1 a b� 1 a 2 c b� 1 a 4 a a� 1 b 1 a b� 1 b 2 c b� 1 b 4 a a� 4 a 1 a b� 4 a 2 c b

4 a 4 a a

�

Vertigo/CNAM, Paris 118

R � S A B D

1 a b

4 a a

� ������������������� ���������v�������v���������v����� ���   ¡ ¢�¢

Vertigo/CNAM, Paris 119

Jointur e Naturelle: Algorithme

Pourchaquenuplet £ dans¤ et pourchaquenuplet ¥ dans¦ :

1. onconcatène£ et ¥ eton obtientunnupletqui apourattributs

§¨ ©�ª «¬ ­�®�¯�¯�¯�®�¬ ° ®E± ­�®�¯�¯�¯�®K± ²³® ´¨ ©(ª «µ ­�®�¯�¯�¯�® µ ¶ ®E± ­�®�¯�¯�¯�®K± ²2. onnele gardequesi chaqueattribut

± ·de £ estégal à l’attribut

± ·de ¥ :¸ ·º¹»­�¼½¼�² £ ¯E± ·¿¾ ¥ ¯K± ·

.

3. onéliminelesvaleurs(colonnes)dupliquées: onobtientun nupletqui a pour

attributs §¨ ©�ª «¬ ­ ®�¯�¯�¯�®�¬ ° ® ´¨ ©�ª «µ ­ ®�¯�¯�¯�® µ ° ® §³ÀÂÁ ´¨ ©(ª «± ­ ®�¯�¯�¯�®K± ²

Vertigo/CNAM, Paris 120

Ã-Jointur e

– ARGUMENTS: 2 relationsquelconques:

Ä Å�Æ Ç�È�É�É�É�È�Æ Ê Ë Ì#Å-Í Ç�È�É�É�É�È&Í ÎÏË– NOTATION :

Ä Ð}ÑjÒ�ÓÕÔ×Ö Ì, Ø Ù Ú*Û ÈÝÜÛ ÈÏÞ Èoß ÈÏà Èoá â

– SCHÉMADE ã Û Ä Ð ÑÝä�ÓÕÔ¿å Ì: ã Å-Æ Ç È�É�É�É�È�Æ Ê È&Í Ç È�É�É�É�È�Í Î Ë

– VALEUR DE ã Û Ä Ð Ñ|ä�ÓÕÔ¿å Ì: ã Û æ Ñ Ò ÓÕÔ ÖçÅ�Ä è Ì�Ë�Ë

– ÉQUIJOINTURE: Ø estl’ égalité.

Vertigo/CNAM, Paris 121

é-Jointur e: Exemple

R A B

1 a

1 b

3 a

S C D E

1 b a

2 b c

4 a a

Vertigo/CNAM, Paris 122

T := R ê S 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

ï

ðòñNóÏô (T) A B C D E

= R õ ñNóÏô S 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

Vertigo/CNAM, Paris 123

Équijointur e: Exemple

R A B

1 a

1 b

3 a

S C D E

1 b a

2 b c

4 a a

Vertigo/CNAM, Paris 124

T := R ö S 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

üýÿþ���� (T) A B C D E

=R� þ���� S 1 a 4 a a

1 b 1 b a

1 b 2 b c

3 a 4 a a

Vertigo/CNAM, Paris 125

Équijointur e vs.Jointur e Naturelle

��� � � � ��� � ����� � � � � ����� � � � ��� � � � �� � � ��� ��� � ����� � � �!� � � � �� � � � �

1. Nomdu propriétairedel’immeubleoù estsituél’appartementoccupéparDurand:

"�#%$'&(# �)+*-,/.1032547698;:<0=254>6@?A?B6C DFE G��� � � � H I &(JKJMLN#KOQPRPASTLN$NU 8 S PRP �V� � � ��� �W�

2. Appartementsoccupéspardespropriétairesd’immeuble:

" U'SYX[Z 8 U'#\Z^]Q_�U'`K]a6Wb-25,dce*-,/.10=2+476C D[E G��� � � ��� H &(JKJfLg#%OQ#K$'&(# ��� � � �Y �@�

Vertigo/CNAM, Paris 126

AutreExempledeREQUÊTE: Nometadressedesclientsqui ont commandédes

parpaings:

– SchémaRelationnel:

h i j j k l m n o pVq l i j r!h l i j r�l s j_h j m n rut v n w

h xzy{n l v o|p}h l i j ruh k m ~ n ozo�n ru� k x k l h n w– RequêteRelationnelle:

�����f��� �����\�Q�����1�+�������K�1� � �T� � ��� �f��� ����� � �\� � �� 9�f¡\�R� �V� ¢ £ £ ¤¥� ¦ � �Q§¨§

Vertigo/CNAM, Paris 127

Union

– ARGUMENTS: 2 relationsdemêmeschéma:

© ª�« ¬®­°¯°¯±¯[­�« ² ³ ´ ª�« ¬µ­°¯°¯±¯[­�« ² ³

– NOTATION :© ¶ ´

– SCHÉMADE · ¸ © ¶ ´ : · ª�« ¬µ­°¯°¯±¯[­�« ² ³– VALEUR DE · : Unionensemblistesur ¹ ¬ º ¯°¯±¯ º ¹ ² :

· ¸ »°¼ ½µ¼¿¾ © À ¼¿¾ ´¥Á

Vertigo/CNAM, Paris 128

Union: Exemple

R A B

a b

a c

d e

S A B

a b

a e

d e

f g

Â

R Ã S A BÄ a b

a cÄ d e

a e

f g

Vertigo/CNAM, Paris 129

Différ ence

– ARGUMENTS: 2 relationsdemêmeschéma:

Å ÆVÇ ÈµÉ°Ê±Ê°Ê[É�Ç Ë Ì Í|Æ�Ç ÈÎɱʰʰÊ[É�Ç Ë Ì

– NOTATION :Å Ï Í

– SCHÉMADE Ð Ñ Å Ï Í : Ð ÆVÇ È É°Ê±Ê°Ê[É�Ç Ë Ì– VALEUR DE Ð : Différenceensemblistesur Ò È Ó Ê±Ê°Ê Ó Ò Ë :

Ð Ñ Ô°Õ ÖµÕ¿× Å Ø Õ Ù× Í¥Ú

Vertigo/CNAM, Paris 130

Différ ence:Exemple

Vertigo/CNAM, Paris 131

R A BÛ a b

a cÛ d e

S A BÛ a b

a eÛ d e

f g

R - S A B

a c

S - R A B

a e

f g

Vertigo/CNAM, Paris 132

Intersection

– ARGUMENTS: 2 relationsdemêmeschéma:

Ü Ý�Þ ß®à°á°á±á[à�Þ â ã ä Ý�Þ ßµà°á°á±á[à�Þ â ã

– NOTATION :Ü å ä

– SCHÉMADE æ ç Ü å ä : æ ÝVÞ ßµà°á±á°á[à�Þ â ã– VALEUR DE æ : Intersectionensemblistesur è ß é á±á°á é è â :

æ ç ê°ë ìµë¿í Ü î ë¿í ä¥ï

Vertigo/CNAM, Paris 133

Intersection: Exemple

R A Bð a b

a cð d e

S A Bð a b

a eð d e

f g

R - S A B

a c

R ñ S = R - (R - S) A B

a b

d e

Vertigo/CNAM, Paris 134

Semijointure

– ARGUMENTS: 2 relationsquelconques:

ò ó�ô õµö°÷°÷±÷[ö�ô ø öRù õÎö°÷±÷°÷[öRù úüûþý óVÿ õÎö°÷°÷±÷Föuÿ ��öRù õ±ö°÷±÷°÷FöRù úüûoùù õÎö±÷°÷±÷[ö�ù ú

sontlesattributsencommun.

– NOTATION :ò ��� ý

– SCHÉMADE � � ò ��� ý: � óVô õµö°÷±÷°÷[ö�ô ø ö�ù õÎö±÷°÷°÷[öRù ú1û

– VALEUR DE � � ò ��� ý: Projectionsurlesattributsde

òdela jointurenaturelle

entreò

etý

.

Vertigo/CNAM, Paris 135

Semijointure

La semijointurecorrespondàunesélectionoù la conditiondesélectionestdéfinieparle

biaisd’uneautrerelation.

Soit � � � � ���������� � � l’ensembledesattributsde � .

� ��� � � ������� � �!

Vertigo/CNAM, Paris 136

Semijointure: Exemple

R A B C

a b c

d b c

b b f

c a d

S B C D

b c d

b c e

a d b

" #%$'&)(*&,+.-0/ 1 2!3!"

R 4�5 S A B C

a b c

d b c

c a d

Vertigo/CNAM, Paris 137

Division: Exemple

REQUÊTE: Clientsqui commandenttouslesproduits:

COMM NUM NOM PNOM QTE

1 Jean briques 100

2 Jean ciment 2

3 Jean parpaing 2

4 Paul briques 200

5 Paul parpaing 3

6 Vincent parpaing 3

Vertigo/CNAM, Paris 138

6 7 8%9;:'< =,>?9�:'< @BA C D D E:

R NOM PNOM

Jean briques

Jean ciment

Jean parpaing

Paul briques

Paul parpaing

Vincent parpaing

PROD PNOM

briques

ciment

parpaing

F6 G H 6 C I

NOM

Jean

Vertigo/CNAM, Paris 139

Division: Exemple

b

e

a

c

x

y

z

m

n

o

A B C DR

a

a

a

b

b

c

c

c

d

d

b

b

b

c

d

e

e

e

a

a

x

y

z

x

x

x

y

z

z

y

m

n

o

o

m

m

n

o

p

m

A BR \ SS C D

Vertigo/CNAM, Paris 140

Division: Exemple

Vertigo/CNAM, Paris 141

R A B C D E

1 a 1 b a

1 a 2 b a

1 a 4 a a

1 b 1 b a

1 b 2 b c

1 b 4 a a

2 a 1 b c

2 a 4 a c

3 c 1 b b

3 c 2 b b

3 c 4 a b

3 c 3 c b

S C D

1 b

2 b

4 a

J R K S A B E

1 a a

3 c b

Vertigo/CNAM, Paris 142

Division

– ARGUMENTS: 2 relations:

L M�N OP�Q�Q�Q�PRN S PUT OVP�Q�Q�Q�PUT WYX Z.M[T O�P�Q�Q�Q�PUT W\Xoù tous lesattributsde

Zsontdesattributsde

L.

– NOTATION :L ] Z

– SCHÉMADE ^ _ L ] Z: ^ M0N O P�Q�Q�Q�P�N S X

– VALEUR DE ^ _ L ] Z:

` a b c dfe[gihkjmlnlnlojUgfprqtsnuvexwyhzjolnlnlnj{w}|Vq�~ b ��e[gihkjolmlnlnjUgfp j{wyhkjnlmlnloj{w}|q'~ ` �

Vertigo/CNAM, Paris 143

Division

La divisions’exprimeenfonctionduproduitcartésien,dela projectionetdela

différence: � � � � � �!� � � où

� ��� ���������������{��� ��� �����'� � � �%�?�����������{��� ���0� � � �!��� � �

Vertigo/CNAM, Paris 144

Renommage

– NOTATION : �– ARGUMENTS: 1 relation: � �0  ¡ ¢�£�£�£�¢   ¤}¥– SCHÉMADE ¦ § �©¨�ª¬« ­vª

�: ¦

�0  ¡ ¢�£�£�£�¢   ® ¯ ¡ ¢�° ® ¢   ®x±�¡ ¢�£�£�£�¢   ¤²¥– VALEUR DE ¦ § �©¨ ª « ­ ª

�: ¦ §

�. La valeurdeR estinchangée.Seulle nomde

l’attribut

  ®aétéremplacépar °

®

Vertigo/CNAM, Paris 145

Et si lesattrib uts sont connuspar leur rang?

Onpeutdanslesopérationsdel’algèbreindiquerunattribut parsonrangaulieu deson

nom.

Danscecasl’ordre desattributsdansunerelationa del’importance.

L’exempledela sélection:³µ´·¶¹¸»º½¼¿¾�À�¼»¾�ÁoÂ�Ã?ºÅÄ Æ Ç Ç È É Ê Ë Ì aulieu de³ ¼¿Â;Í'Î ¸ º ¼»¾�À�¼»¾�ÁoÂ;à ºÏÄ Æ Ç Ç È É Ê Ë ÌLe premierattribut Ð Ñ corresponda l’attribut Ò É Æ Ç

Vertigo/CNAM, Paris 146

CALCUL RELATIONNEL

Vertigo/CNAM, Paris 147

Exemple: la basededonnéesCINÉMA

Films Titre MetteurenScene Acteur

Juleset Jim F. Truffaut J.Moreau

Juleset Jim F. Truffaut O. Werner

LesQuatreCentCoups F. Truffaut J.P. Leaud

Metropolis F. Lang B. Helm

Chimesat Midnight O. Welles J.Moreau

Vertigo/CNAM, Paris 148

Lieu Cinema Adresse No-Telephone

Rex Bd Poissonniere 4236 8393

Champo R. desEcoles 4354 5160

Cinoche R. deConde 4633 1082

Pariscope Cinema Titre Heure

Rex Juleset Jim 18

Rex Juleset Jim 20

Cinoche Juleset Jim 20

Champo Metropolis 18

Vertigo/CNAM, Paris 149

ExempledeRequête

Requête:Qui a dirigé le film Metr opolis?

– Oncherchela valeurde l’attrib ut Metteur_en_Scene detouslesn-upletsdans

la relation(table)Films dont l’attrib ut Titre estégal à la constante

’Metropolis’ .

– Calcul relationnel Ó -uplet

Ô}Õ Ö ×ÙØÚØ�שÛtÜ_שÝ

_Þ ß ×©Ýà× á�â ãmäæå çéèêÕìërí Õ Öïî ã�Ø�Üµ× ð ñ ×ÙØ�ܵòôóõòöäæãzç÷ñùø– Oncherchex.Metteur_en_Scene detouslesn-upletsú dansla relation(table)

Films tel quex.Titre = ’Metropolis’ .

Vertigo/CNAM, Paris 150

Syntaxe

Vertigo/CNAM, Paris 151

Symboles(c.a.d.l’alphabet du langage)

Unerequêteducalculrelationnelestcomposéededifférentssymboles:

– Constantes:’F. Truffaut’, ’Juleset Jim’, 18,43545160,.. .

– Prédicats(relations):Movies,Location, . . .

– Variables(n-uplets,attributs):x, y, z, ...

– Comparateursarithmétiques:=, û , ü , û ý , ...

– Connecteurslogiques: þ (ou), ÿ (et), � (non)

– Quantificateurs:�

(il existe), � (pourtous)

– Parenthèses: “(“, “)”

Vertigo/CNAM, Paris 152

Formules atomiques(atomes)et variables libr es

Le calculrelationnelestfondésurla logiquedu premierordreet utilisedesformules

logiques.Lesformuleslesplussimplessontappelésatomes:

– p(x1,.. . ,xn) estunatomeoùp estunsymboledeprédicat,et xi, i � [1,n] estsoit une

variable,soit uneconstante.

Exemples:

– Films (’Juleset Jim’, ’F.Truffaut’, ’J. Moreau’)

– Films (x, ’Truffaut’, y)

– x � y estunatomeoùx ety sontsoit desvariablessoit desconstanteset � estl’un des

6 comparateursarithmétiques.

Exemples: 1 <3, x <y, x <8

– Touteslesoccurrencesdevariablesapparaissantdansuneformuleatomiquesont

diteslibr es.

Vertigo/CNAM, Paris 153

Formulesbien formées

– si F estuneformuleavec � parmisesvariableslibres,alors( � x)F et (� x)F sontdesformules.Touteslesoccurrencesdex dansF sontalorsditesliées.

Exemples:

– ����� ��� � ���– ������� ��� � ���– ����� ��� � ���

– si F1 etF2 sontdesformules,alorsF1 � F2,F1 � F2 et � F1 sontdesformules(lesoccurrencesdevariablesdecesnouvellesformulessontlibresou liéessi ellesle sontdansF1,F2).Exemples:

– ��� � � ����� ��� � ���– ����� ��� � � � � � ���

– si F estuneformule,alors(F) estuneformule;

Vertigo/CNAM, Paris 154

Requêtes

Requête: "!$#&%'!)(*%,+-+-+-%'!/. 021 3 oùF estuneformuleavecvariableslibres, x1,x2,.. . ,xn.

ExemplesdeRequêtes:

– 4! 05! 6 73– 4! 081 9;:=< >@?�ACB):�D5E D FGAC9< ?H%I?KJ LMBON�NQP�BRFS?@%'!�TU3– 4! 0V>WYX�TZ1 9;:=< >@?�[ D FZL]\_^/\M:=9`E4?a%'!$%'X�Tb3

Vertigo/CNAM, Paris 155

FormesAbrégés

– Formesabrégéesdequantificateurs:

1. cdfe�g'd)h*g-i,i-ijgkd/l estuneabréviationde cd$e5cd)hmi-i-iUcYdRl ,

2. n�dfe�g'd)h*g-i,i-ijgkd/l estuneabréviationde n�d$e_n�d)hmi-i-ionQdRl .

– Ordredeprécédence(priorité) entrelesconnecteursetquantificateurs(du plusau

moinsprioritaire):

1. pqgrnsgtc ,

2. u ,

3. v .

Parexemple, w x y{z | wjx }�~ y � � w,~ y � � w�� y estcompriscomme

wVw x yOw�z | wjx }�~ y�yVy � w�� w,~ y � � w4� y�y��

Vertigo/CNAM, Paris 156

Sémantique

Vertigo/CNAM, Paris 157

Inter prétation: Exemple

Lesformuleset requêtessont“interprétées”:

– Formule: � �;��� �@��� � ���&�_�/�M�����"�H�I�������G�����*�H��� � �¢¡ �]�£�C¤¥�8��¦b§– Interpretation“naturelle”: “Est-cequeM. Brandoa jouédansunfilm Metropolis

dirigéparuncertainF. Lang?”

– Interpretationbasededonnées:“Est-cequ’il existeun n’uplet (’Metropolis’, ’F.

Lang’, ’M. Brando’)dansla table � �;�=� ?”

Vertigo/CNAM, Paris 158

– Formule: ¨©ª�«S¬ ­;®�¯ ¨@°�± ² ³�´&µ_¶/µM®�­�·"°H¸I° ¬º¹�»G¼�½Q¾Y°¿¸'ª�«bÀ– Interpretation“naturelle”: “Est-cequ’il existeunacteurqui a jouédansunfilm

Metropolisdirigéparun certainF. Lang?”ou “Est-cequeF. Langàdirigé le film

Metropolis(avecaumoinsunacteur)?”

– Interpretationbasededonnées:“Est-cequ’il existeuneconstanteÁ tel que

(’Metropolis’, ’F. Lang’, c) estunn’upletdansla table ¬ ­;®=¯ ?

Vertigo/CNAM, Paris 159

– Requête:Â4à Ä*ÅÇÆÈ�ÉZÊ Ë;Ì=Í Å@Î�Ï Ð ÑZÒ]Ó_Ô/ÓMÌ=Ë`Õ4ÎaÖ'È$Ö'Ã�Éb×– Interpretation“naturelle”: “je cherchele nomdetouslesacteursdansle(s)

film(s) avecle titre Metropolis” (je nem’intéressepaspariculièrementau(x)

metteur(s)enscène).

– Interpretation“basededonnées”:“je cherchetouteslesconstantesØ tel qu’il

existepourchacuneaumoinsuneconstanteÙ tel que(’Metropolis’, d, c) estun

n-upletdansla tableFilm.

Vertigo/CNAM, Paris 160

Inter prétation: atomesavecconstantes

Lesformulessontinterprêtesparrapportà unebasededonnéesÚ . Le résultat(la

réponse)del’interprétationd’uneformuleestvrai ou faux:

– L’atomep(x1,.. . ,xn) où p estunsymboledeprédicat(nomderelation)etoù lesxi iÛ [1,n] sontdesconstantes, estvrai dansÚ ( Ú satisfait p(x1,.. . ,xn)) si (x1,.. . ,xn) est

un Ü -upletdela relationp dansÚ .Exemple: Ý Þ;ß=à á@â�ã�äQß�å5æ å çèã�Þaà â@éIâëê ì8äOíîí�ï�äRçðâ�é�â ñ ò5ì&å4ï£ä�â�ó estvrai ssi

á¿â�ã�ä)ß�å5æ å çGã�Þaà â¿éIâôê ì8äOíîí�ï�äRçðâ�éIâõñ ò5ì�å4ï�äQâ�ó estunn-upletdansla table Ý Þ;ß=à .

– L’interprétationdex ö y estnaturelle(indépendantededela basededonnées).

Exemple: ÷ ø ù estvrai ssi3 estinférieurà5.

Vertigo/CNAM, Paris 161

Inter prétation: variables libr eset substitution

– Substitution:Si ú estunevariablelibre dansuneformule û onnote û ürú�ý�þUÿ la

formulequ’onobtientparun remplacementde ú par þ .– Exemple:

– Formule: û = Film(’Metropolis’, x, y)

– Substitutiondevariableslibres:

1. û ü ú)ý � û�� ����� � ÿ = Film(’Metropolis’, ’F. Lang’, y)

2. û ü ú)ý ��� �� ���ðþ��Rþ��Mþ�� � ÿ = Film(’Metropolis’, ’A. Hitchcock’,y)

3. û ü ú)ý � û�� ����� � ÿZü���ý ��� �� ����� � ÿ = Film(’Metropolis’, ’F. Lang’, ’B. Helm’)

4. û ü���ý ��� �� ����� � ÿZürú�ý � û�� ����� � ÿ = Film(’Metropolis’, ’F. Lang’, ’B. Helm’)

Vertigo/CNAM, Paris 162

Inter prétation: quantificateurs

– La formule( x)F estvraie dans! s’il existeunesubstition "$#&%('*) de # telleque+ "�#&%('*) estvraie dans! .Parexemple ,- .#&/ + 02143 5 ,6#87:9<; =?>A@B@DC�>FEG967:9�H IJ=LK�C�>9�/ estvraieparceque+ 02143 5 ,6#87:9<; =?>A@B@DCM>NEG9O7:9PH IQ=LK�C�>9P/R"$#&%S9OTU> 1 K 5 KVE*T 0-3 9W)&X+ 02143 5 ,Y9OTU> 1 K 5 KVE*T 0Z3 9Y7:9W; =?>A@B@DC�>FEG967:9�H IJ=LK�C�>9�/ estvraie.

– L’interprétationdeF1[ F2. \ F,.. .estl’interprétationhabituelle: F1[ F2 estvraiesi

F1 estvraieou F2 estvraie.

Note: La disjonctionet la quantificationuniversellepeuventêtreexpriméesenutilisantla

negation:

1. F1 [ F2 ] \^,_\ F1 `a\ F2),

2. (b x) F ] ( \a x) \ F.

Vertigo/CNAM, Paris 163

Inter prétation d’une Requête

Requête: cedgfihjdlkmhRnononohjdqp rts u oùF estuneformuleaveclesvariableslibres

dgfihvdkwhononRn�hvdFp .

Le résultatdel’interprétationd’unerequêteestensembledesp -uplets(unerelation!)xzy fih y kmhRnonRnVh y pl{ telsque s | x d8fJ| y fihvdk}| y kmhRnononohjdqp~| y p{ estvraie.

– c�d r?s �2�4� ���O�U�l�6�J� �V���U�-� ��h:��� �S�A�&� y �F����hjd&�*u retournetouslesacteursdufilm ’Jules

et Jim’ deTruffaut: c x � ��n�� �Q�L� y � � {-h x ��� n�� �o�Sp��o�t{�u .– c�d rw�-������s �2�4� �O�Dh � � �?�A�B� y �N� � hvdl��u retournetouslesacteursqui ont tournéavec

Truffaut.

Vertigo/CNAM, Paris 164

Algèbre relationnellevsCalcul relationnel sain

L’algèbrerelationnelleet le calculrelationnelsainont le mêmepouvoir d’expression.

Théorême1 : Touterequêteexprimabledansl’algèbrerelationnelleestexprimabledansle calculrelationnel.

Formule saine: Formuledontle résultatestfini (le nbde � -upletsqui satisfontlaformuleestfini).

Exempledeformulenonsaine:  e¡£¢v¤¥g¦6¡l§�¨ . Le resultatcontienttouslesnupletsqui nesontpasdansla relation¥ .

Deux solutions: interdirelesformulesnon-saineou interprétationparrapportaudomaineactif dela basededonnées(exemple: touteslesconstantesdans© qui nesontpasdans¥ ).

Théorême2 : Touterequêtedu calculrelationnelsainestexprimableenalgèbrerelationnelle.

Vertigo/CNAM, Paris 165

Calcul relationneldomaine/ª -uplet

Le calculrelationnelprécédentestappelécalcul relationneldomaine.

Pourobtenirle calcul relationnel « -uplet, on remplace:

– x1,x2,.. . ,xn (où xi estunevariabledomaine)parla variable« -uplett.

– L’attribut A derangi (xi dansle calculdomaine)estnotét.A (voir exemples

ci-dessous).

Theorême3 : Le calculrelationnel« -upletsaina le mêmepouvoir d’expressionque

l’algèbrerelationnelle.

Vertigo/CNAM, Paris 166

ExemplesdeRequêtes

Vertigo/CNAM, Paris 167

QuelquesRequêtes

1. Qui dirigeMetropolis?

2. AdresseetnumérodetéléphoneduStudio?

3. Sallesoù onpeutvoir un film deTruffaut?

4. Adressesdescinémasmontrantunfilm deTruffaut?

5. Quelsfilms deTruffautnepassentpasencemoment?

Vertigo/CNAM, Paris 168

Requête1: Qui a dirigé le film Metr opolis?

SQL

select Metteur-en-Scene

from Films

where Titre = ’Metropolis’;

Calcul relationnel ¬ -uplet

­¯® ° ±8²�²w±g³ ´_±gµ

_ ¶ ±8µ ± · ¸L¹�º »½¼o® ¾�¿ ® ° ¸?²�´À± Á  ±8²M´�ÃÅÄ Ã�¹Æ¸M»�ÂÈÇ

Calcul relationneldomaine

­ÊÉ · ¼(Ë ® ¾ ¸L¹�º »½¼  ±~²M´�ÃÅÄ Ã�¹�¸�» ÂÆÌ É Ì ® ¾qÇ

Vertigo/CNAM, Paris 169

Vertigo/CNAM, Paris 170

Requête2: Adr esseet numéro de téléphonedu Studio?

Calcul relationnel Í -uplet

Î¯Ï Ð ÑÓÒÀÔ¯ÕÖÕ¯ÔÀ×ØÏ Ð Ù Ú Ô8ÒÜÛ_

Ô£Ý Þ ß à�Ô8Ù áoÏ â ãÏ Ð àSä ÔgÚ å æ ç è�Ù ÑÓàiÛéçëê

Calcul relationneldomaine

Îìå Ñ ×jí î Þïß à�Ô8Ù á ç èMÙ ÑÓàðÛ ç ×6å Ñ ×jí î âqê

Vertigo/CNAM, Paris 171

Requête3: Sallesoù on peut voir un film deTruffaut ?

Calcul relationnel ñ -uplet

òôó õ÷ö øúùÓû�ü ý þ ÿ������}ÿ�� ý�Fø� ������ûAÿGó���� � ø�� ü Bÿ������ó õ�� ø����Åû ��õ�� ø����Åû!���õ û"�#�Rû%$&�

_û�ù

_' �}û.ù�û ()� �!$+*,* ý$-�#(.�0/

Calcul relationneldomaine

ò1 þ ÿ��&2-�3�!4Wý5�6�74��Vø��8�Dû9�}ÿ:� ý�Nø8 �7�0��ûUÿ�;4��Vø<���Åû=4>2?�@�;�A�� ø�� ü BÿB�Vø<���Åû=4C(>� �D$+*E*ïý$-��(F4Wý5�G�A�A�0/

Vertigo/CNAM, Paris 172

Requête4: Adr essesdescinémasmontrant un Truffaut ?

SQL

select L.Adresse

from Films F, Lieu L, Pariscope P

where F.Metteur-en-Scene = ’Truffaut’

and P.Titre = F.Titre

and L.Cinema = P.Cinema;

Calcul relationnel H -uplet

IDJLKNM O=PRQ!S3S Q T U�V�W XZY�[�U�\ ]�^F_ S=U�W�[�` a bcP!]8S d7e0fgQcUhY�[i` j ]�Q%k U�Jc[A`W K Q"l#lmQ%k&P

_Qon

_p d�Q%nqQ r sut P!k+v,v�bk-l#sw`W K�t ]�l8PRQ r YgK�t ]�l�PxQ ` YgKzy ]:nqQ%_ b r JLKzy ]:nqQ%_ b{[0|

Vertigo/CNAM, Paris 173

Requête4: Adr essesdescinémasmontrant un Truffaut ?

Calcul relationneldomaine

}D~ � ���)�7�������������;�����@�������q��~5�6������� ���F� �=���7�:�8� �!�+�,��~�-�”��~5�G�A�A�

� ~�D�8� �7���+�c�����:�q�=��� ���3�i� ¡ ���%� �����:�q�=��~¢���m�@���m�£�¢�A�0¤

Vertigo/CNAM, Paris 174

Requête5: Quelsfilms deTruffaut ne passentpas?

SQL

select Titre

from Films F

where F.Metteur en Scene = ’Truffaut’

and not exists ( select *

from Pariscope P

where P.Titre = F.Titre )

Calcul relationnel ¥ -uplet

¦3§ ¨�© ª�«8¬R­ ® ¯�° ª�±F² ³=¯h§�´�µ § ¨ ­¶«�«m­o·&¬_­%¸

_¹ º ­%¸q­ » ¼5© ¬!·+½,½�¾·-«#¼.´Aµ¯�¿ À�Á�´�¯: ¾c¬!ª8³ º7Ã0Ä ­c¯hÁ�´�µ Ág¨Å© ª�«8¬R­ » § ¨�© ª<«�¬x­!´0Æ

Vertigo/CNAM, Paris 175

Requête5: Quelsfilms deTruffaut ne passentpas?

Calcul relationneldomaine

Ç3È É�Ê�Ë�Ì�ÍÏÎ Ð�ÑFÒ Ó=ÊhÈ Ô:Õ�Ö ×!Ø+Ù,Ù�ÚØ-Û”ÔZÌ�ÍiÜ Ê�Ý ËLÞ�ÔZß Í�à Ú×DÐ�Ó á�â�ã+äÊ�ÞLÔZÈ ÔZß Í0å

ouÇ3È É�Ê�Ë�Ì�ÍÏÎ Ð�ÑFÒ Ó=ÊhÈ Ô:Õ�Ö ×!Ø+Ù,Ù�ÚØ-Û

”ÔZÌ�ÍiÜ Êçæ�ÞLÔZß ÍAÝ à Ú×DÐ8Ó á7â�ã+äcÊ�ÞLÔZÈ ÔZß Í�å

Vertigo/CNAM, Paris 176

SQL

Vertigo/CNAM, Paris 177

Principe

– SQL(StructuredQueryLanguage)estle LangagedeRequêtesstandardpourles

SGBDrelationnels

– Expressiond’unerequêteparunbloc SELECTFROM WHERE

SELECT è listedesattributsaprojeteréFROM è listedesrelationsargumentséWHERE è conditionssurunou plusieursattributsé

– Danslesrequêtessimples,la correspondanceavecl’algèbrerelationnelleestfacileà

mettreenévidence.

Vertigo/CNAM, Paris 178

Historique

SQL86 - SQL89 ou SQL1 La référencedebase:

– Requêtescompiléespuisexécutéesdepuisunprogrammed’application.

– Typesdedonnéessimples(entiers,réels,chaînesdecaractèresdetaille fixe)

– Opérationsensemblistesrestreintes(UNION).

SQL91 ou SQL2 Standardactuel:

– Requêtesdynamiques:exécutiondifféréeou immédiate

– Typesdedonnéesplusriches(intervalles,dates,chaînesdecaractèresdetaillevariable)

– Différentstypesdejointures:jointurenaturelle,jointureexterne

– Opérationsensemblistes:différence(EXCEPT),intersection(INTERSECT)

– Renommagedesattributsdansla clauseSELECT

Vertigo/CNAM, Paris 179

SQL3 (encours): SQLdevient un langagedeprogrammation:

– Extensionsorientées-objet

– Opérateurdefermeturetransitive (recursion)

– ...

Vertigo/CNAM, Paris 180

ExpressionsdeBase

Vertigo/CNAM, Paris 181

Projection

Soit le schémaderelationCOMMANDES (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Informationsur touteslescommandes

SQL:

SELECT NUM,CNOM,PNOM,QUANTITE

FROM COMMANDES

ou

SELECT *

FROM COMMANDES

Vertigo/CNAM, Paris 182

Projection: Distinct

Soit le schémaderelationCOMMANDES (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Produitscommandés

SELECT PNOM

FROM COMMANDES

NOTE: Contrairementà l’algèbrerelationnelle,SQLn’éliminepaslesdupliqués.Pour

lesélimineron utiliseDISTINCT:

SELECT DISTINCT PNOM

FROM COMMANDES

Le DISTINCT peutêtreremplacéparla clauseUNIQUE danscertainssystèmes

Vertigo/CNAM, Paris 183

Sélection

Soit le schémaderelationCOMMANDES (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Produitscommandéspar Jean

ALGÈBRE: ê;ë5ì,í�î ïwðRñ5ì,í�î ò=ó�ô6õ5öcì÷óøïúù û ü ü ý þ ÿ � �����SQL:

SELECT PNOM

FROM COMMANDES

WHERE CNOM= ’JEAN’

Vertigo/CNAM, Paris 184

REQUÊTE: Produitscommandéspar Jeanenquantitésupérieure à 100

SQL:

SELECT PNOM

FROM COMMANDES

WHERE CNOM= ’JEAN’

AND QUANTITE > 100

Vertigo/CNAM, Paris 185

Conditions desélectionenSQL : Conditions simples

Lesconditionsdebasesontexpriméesdedeuxfaçons:

1. attribut comparateurvaleur

2. attribut comparateurattribut

où ���� ��� ��������� est � ��� ��� ��� � ���������Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Produitsdeprix supérieurà 200F

Vertigo/CNAM, Paris 186

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX > 2000

Vertigo/CNAM, Paris 187

Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Produitsdontle nomestcelui du fournisseur

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PNOM= FNOM

Vertigo/CNAM, Paris 188

Conditions desélectionen SQL : Suite

Le �� ! "�#�$�#�%�&�'�$ estBETWEEN,LIKE, IS NULL, IN

Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Produitsavecun coûtentre 1000Fet 2000F

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX BETWEEN1000 AND 2000

NOTE: La condition ( BETWEEN ) AND * estéquivalenteà ( + , * AND

) + , ( .

Vertigo/CNAM, Paris 189

Soit le schémaderelationCOMMANDES (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Clientsdontle nomcommencepar "C"

SQL:

SELECT CNOM

FROM COMMANDES

WHERE CNOMLIKE ’C%’

NOTE: Le littéral qui suit LIKE doit êtreunechaînedecaractèreséventuellementavec

descaractèresjokers(_, %). Pasexprimableavecl’algèbrerelationnelle.

Vertigo/CNAM, Paris 190

La valeur NULL (*)

La valeurNULL estunevaleur“spéciale”qui représenteunevaleur(information)

inconnue.

1. - . / estinconnu (ni vrai, ni faux)si la valeurde - ou/et / estNULL ( . estl’un

de 0 1�2 143 1�5 146 1870 ).

2. - 9;: / estNULL si la valeurde - ou/et / estNULL ( 9<: estl’un de = 1�> 1@? 1BA ).

Vertigo/CNAM, Paris 191

Soit le schémaderelationFOURNISSEUR(FNOM,STATUT,VILLE)

REQUÊTE: LesFournisseurs deParis.

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE VILLE = ’Paris’

Onnetrouve paslesfournisseursavecVILLE = NULL !

Vertigo/CNAM, Paris 192

Soit le schémaderelationFOURNISSEUR(FNOM,STATUT,VILLE)

REQUÊTE: Fournisseurs dontl’adresseestinconnu.

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE VILLE IS NULL

NOTE: Le prédicatIS NULL (ou IS NOT NULL) n’estpasexprimableenalgèbre

relationnelle.

Vertigo/CNAM, Paris 193

Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Produitsavecun coûtde100F, de200Fou de300F

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX IN {100,200,300}

NOTE: La condition C IN DE�FHGIF�J�J�J�FLKNM estéquivalenteà C O E OR C O G OR J�J�J OR

C O K .

Vertigo/CNAM, Paris 194

Jointur e

Soit le schémaderelations

COMMANDES (NUM,CNOM,PNOM,QUANTITE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Nom,Coût,FournisseurdesProduitscommandéspar Jean

ALGÈBRE:

PRQTSVUXW YZQ\[8]_^VYa`bScUXW dfe�gTSVUXW hji�kmlTn8Soi�dqp r s s t u v w x�y{z d}| r ~ � u ��� ~ � w y�y

Vertigo/CNAM, Paris 195

SQL:

SELECT COMMANDES.PNOM,PRIX, FNOM

FROM COMMANDES,FOURNITURE

WHERE CNOM= ’JEAN’ AND

COMMANDES.PNOM= FOURNITURE.PNOM

NOTE: Cetterequêteestéquivalenteà unejointurenaturelle.Noterqu’il fauttoujours

expliciter lesattributsdejointure.

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

FOURNITUREéquivautàunproduitcartésiendesdeuxrelations,suivi d’uneprojection.

Vertigo/CNAM, Paris 196

Soit le schémaderelationFOURNISSEUR(FNOM,STATUT,VILLE)

REQUÊTE: Fournisseurs qui habitentdeuxà deuxdansla mêmeville

SQL:

SELECT PREM.FNOM, SECOND.FNOM

FROM FOURNISSEURPREM, FOURNISSEURSECOND

WHERE PREM.VILLE = SECOND.VILLE AND

PREM.FNOM< SECOND.FNOM

La deuxièmeconditionpermet

1. l’élimination despaires(x,x)

2. d’éviterd’obteniraurésultatà la fois (x,y) et (y,x)

NOTE: PREMreprésenteuneinstancedeFOURNISSEUR,SECONDuneautre

instancedeFOURNISSEUR.

Vertigo/CNAM, Paris 197

Soit le schémaderelationEMPLOYE(EMPNO,ENOM,DEPNO,SAL)

REQUÊTE: NometSalairedesEmployésgagnantplusquel’employédenuméro 12546

ALGÈBRE:

R1:= �j�N�8�X�}����� �T�V���������������f� � � �¡  ¢ � £�£R2:= � �¤�V�X� ¥Z�¤� �\�4�T¦��¨§©�N�8� ���f� � � �¡  ¢ � £«ª �¤� �T�¬�T¦­�®§Z�N�8��¯±°X�_§©�N�8� �f² ³I£�£

SQL:

SELECT E1.ENOM, E1.SAL

FROM EMPLOYEE1, EMPLOYEE2

WHERE E2.EMPNO = 12546 AND

E1.SAL > E2.SAL

Vertigo/CNAM, Paris 198

TroisValeursde Vérité (*)

Trois valeursdevérité:vrai, faux et inconnu

1. vrai AND inconnu = inconnu

2. faux AND inconnu = faux

3. inconnu AND inconnu = inconnu

4. vrai OR inconnu = vrai

5. faux OR inconnu = inconnu

6. inconnu OR inconnu = inconnu

7. NOT inconnu = inconnu

Vertigo/CNAM, Paris 199

Exemple(*)

Soit le schémaderelationEMPLOYE(EMPNO,ENOM,DEPNO,SAL)

SQL:

SELECT E1.ENOM

FROMEMPLOYEE1, EMPLOYEE2

WHEREE1.SAL > 20000 OR

E1.SAL <= 20000

Est-cequ’on trouvelesnomsdetouslesemployéss’il y a desemployésavecunsalaire

inconnu?

Vertigo/CNAM, Paris 200

Jointur esdansSQL2 (*)

Vertigo/CNAM, Paris 201

OpérationsdeJointur e (*)

SQL2 opération Algèbre

R1CROSSJOINR2 produitcartesien ´ µ ¶ ´ ·R1JOINR2ON R1.A <R2.B théta-jointure ´ µ ¸º¹X»�¼¾½X¿j¹8À�¼ZÁ ´ ·

R1NATURAL JOINR2 jointurenaturelle ´ µ ¸ ´ ·

Vertigo/CNAM, Paris 202

Jointur e Naturelle: Exemple(*)

SCHEMA:EMPLOYE(EMPNO,ENOM,DEPNO,SAL),DEPT(DEPNO,DNOM)

REQUÊTE: Nomsdesdépartementsaveclesnomsdeleurs employés.

SQL:

SELECT DNOM, ENOM

FROM DEPT NATURALJOIN EMP

Note: Commedansla définitionalgébrique,l’expression

DEPT NATURALJOIN EMPfait la jointurenaturelle(surl’attribut DEPNO) et

l’attribut DEPNOn’apparaîtqu’uneseulefois dansle schémadu résultat.

Vertigo/CNAM, Paris 203

Theta-Jointur e: Exemple(*)

Soit le schémaderelationEMPLOYE(EMPNO,ENOM,DEPNO,SAL)

REQUÊTE: Nometsalairedesemployésgagnantplusquel’employé12546

SQL:

SELECT E1.ENOM, E1.SAL

FROM EMPLOYEE1 JOIN EMPLOYEE2 ON E1.SAL > E2.SAL

WHERE E2.EMPNO = 12546

Vertigo/CNAM, Paris 204

Jointur e Externe (*)

EMP EMPNO DEPNO SAL

Tom 1 10000

Jim 2 20000

Karin 3 15000

DEPT DEPNO DNOM

1 Comm.

2 Adm.

4 Tech.

Jointur e: lesn-upletsqui nepeuventpasêtrejoints sontéliminés:

EMP NATURAL JOIN DEPT

Tom 1 10000 Comm.

Jim 2 20000 Adm.

Vertigo/CNAM, Paris 205

Jointur e Externe (*)

Jointur e externe: lesn-upletsqui nepeuventpasêtrejoints nesontpaséliminés.

– Ongardetouslesn-upletsdesdeuxrélations:

EMP NATURAL FULL OUTER JOIN DEPT

Tom 1 10000 Comm.

Jim 2 20000 Adm.

Karin 3 15000 NULL

NULL 4 NULL Tech.

Vertigo/CNAM, Paris 206

– Ongardetouslesn-upletsdela premièrerélation(gauche):

EMP NATURAL LEFT OUTER JOIN DEPT

Tom 1 10000 Comm.

Jim 2 20000 Adm.

Karin 3 15000 NULL

– Ongardetouslesn-upletsdela deuxièmerélation(droite):

EMP NATURAL RIGHT OUTER JOIN DEPT

Tom 1 10000 Comm.

Jim 2 20000 Adm.

NULL 4 NULL Tech.

Vertigo/CNAM, Paris 207

Jointur esExternesdansSQL2 (*)

– R1NATURAL FULL OUTERJOINR2: RemplirR1.* etR2.*

– R1NATURAL LEFT OUTERJOINR2: RemplirR2.*

– R1NATURAL RIGHT OUTERJOINR2: RemplirR1.*

avecNULL quandnécessaire.

D’unemanièresimilaireonpeutdéfinir desthéta-jointuresexternes:

– R1 (FULL|LEFT|RIGHT)OUTERJOINR2ON prédicat

Vertigo/CNAM, Paris 208

ExpressionsEnsemblistes

Vertigo/CNAM, Paris 209

Union

COMMANDES (NUM,CNOM,PNOM,QUANTITE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Produitsqui coûtentplusque1000Fou ceuxqui sontcommandéspar Jean

ALGÈBRE:

Â Ã Ä Å Æ ÇÉÈ Ã Ê ËNÌ Í ÎÐÏ�Ï�ÏXÇ Ñ Ò4ÒÓ

Â Ã Ä Å Æ ÇÉÈ Ô Ä Å Æ Õ ÖØ×�ÙÉÚÜÛ8Ö Ç Ò4Ò

Vertigo/CNAM, Paris 210

SQL:

SELECT PNOM

FROM FOURNITURE

WHERE PRIX >= 1000

UNION

SELECT PNOM

FROM COMMANDES

WHERE CNOM= ’Jean’

NOTE: L’union éliminelesdupliqués.Pourgarderlesdupliquéson utilise l’opération

UNION ALL : le résultatcontientchaquen-uplet Ý Þ ß fois, où Ý et ß estle nombre

d’occurrencesdun-upletdansla premièreet la deuxièmerequête.

Vertigo/CNAM, Paris 211

Différ ence

La différencenefait paspartiedu standard.

EMPLOYE(EMPNO,ENOM,DEPTNO,SAL)DEPARTEMENT (DEPTNO,DNOM,LOC)

REQUÊTE: Départementssansemployés

ALGÈBRE: àâá{ã¤ä8åâæcçéè}ê ë ì í î ï ë ð ë ñ ï òTó àâá{ã¤ä8åâæVçéèfë ð ì ô¡õ ö ë òSQL:

SELECT DEPTNO

FROM DEPARTEMENT

EXCEPT

SELECT DEPTNO

FROM EMPLOYE

NOTE: La différenceéliminelesdupliqués.Pourgarderlesdupliquésonutilise

Vertigo/CNAM, Paris 212

l’opérationEXCEPT ALL : le résultatcontientchaquen-uplet ÷ ø ù fois, où ÷ et ù estle

nombred’occurrencesdun-upletdansla premièreet la deuxièmerequête.

Vertigo/CNAM, Paris 213

Intersection

L’intersectionnefait paspartiedu standard.

EMPLOYE(EMPNO,ENOM,DEPTNO,SAL)

DEPARTEMENT (DEPTNO,DNOM,LOC)

REQUÊTE: Départementsayantdesemployésqui gagnentplusque20000Fet qui se

trouventà Paris

ALGÈBRE:

ú û ü ý þ ÿ � ��� ��� � � ��ý � ������� ����

ú û ü ý þ ÿ � ��� ��� ��� ����������� �!�

Vertigo/CNAM, Paris 214

SQL:

SELECT DEPTNO

FROM DEPARTEMENT

WHERE LOC = ’Paris’

INTERSECT

SELECT DEPTNO

FROM EMPLOYE

WHERE SAL > 20000

NOTE: L’intersectionéliminelesdupliqués.Pourgarderlesdupliquésonutilise

l’opérationINTERSECT ALL : le résultatcontientchaquen-uplet " #%$'&)(+*-,/. fois, où ( et

, estle nombred’occurrencesdu n-upletdansla premièreet la deuxièmerequête.

Vertigo/CNAM, Paris 215

Imbrication desRequêtesenSQL

Vertigo/CNAM, Paris 216

Requêtesimbriquéessimples

La Jointures’exprimepardeuxblocsSFWimbriqués

Soit le schémaderelations

COMMANDES (NUM,CNOM,PNOM,QUANTITE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Nom,prix et fournisseursdesProduitscommandéspar Jean

ALGÈBRE:

021436587 9:1<;>=@?69BAC3D587 EGFIH436587 JLKNMPO4Q>3RKSEUT V W W X Y Z [ \^]`_ Eba V c d Y eSf c d [ ]g]

Vertigo/CNAM, Paris 217

SQL:

SELECT PNOM,PRIX,FNOM

FROM FOURNITURE

WHERE PNOMIN (SELECT PNOM

FROM COMMANDES

WHERE CNOM= ’JEAN’)

ou

SELECT FOURNITURE.PNOM,PRIX,FNOM

FROM FOURNITURE,COMMANDES

WHERE FOURNITURE.PNOM= COMMANDES.PNOM

AND CNOM= ‘‘JEAN’’

Vertigo/CNAM, Paris 218

La Différences’exprimeaussipardeuxblocsSFWimbriqués

Soit le schémaderelations

EMPLOYE(EMPNO,ENOM,DEPNO,SAL)

DEPARTEMENT (DEPTNO,DNOM,LOC)

REQUÊTE: Départementssansemployés

ALGÈBRE:

hji`kml>njo6p qbr s t u v w s x s y w z4{ h2i|k4lCnjo6p�qbs x t }R~ � s z

Vertigo/CNAM, Paris 219

SQL:

SELECT DEPTNO

FROM DEPARTEMENT

WHERE DETPNONOT IN (SELECT DISTINCT DEPTNO

FROM EMPLOYE)

ou

SELECT DEPTNO

FROMDEPARTEMENT

EXCEPT

SELECT DISTINCT DEPTNO

FROMEMPLOYE

Vertigo/CNAM, Paris 220

Requêtesimbriquéesplus complexes: ANY - ALL

Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Fournisseurs desBriquesà uncoûtinférieurau coûtmaximumdesArdoises

SQL : SELECT FNOM

FROM FOURNITURE

WHERE PNOM= ’Brique’

AND PRIX < ANY (SELECT PRIX

FROM FOURNITURE

WHERE PNOM= ’Ardoise’)

NOTE: La condition � � ANY (SELECTF FROM . . . ) estvraiessila comparaison�����estvraieaumoinspourunevaleur � du résultatdu bloc (SELECTF FROM . . . ).

Vertigo/CNAM, Paris 221

Soit le schémaderelations

COMMANDE (NUM,CNOM,PNOM,QUANTITE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Nom,CoûtetFournisseurdesProduitscommandéspar Jean

SQL:

SELECT PNOM, PRIX, FNOM

FROM FOURNITURE

WHERE PNOM= ANY (SELECT PNOM

FROM COMMANDE

WHERE CNOM= ’JEAN’)

NOTE: LesprédicatsIN et= ANY sontutilisésdefaçonéquivalente.

Vertigo/CNAM, Paris 222

Soit le schémaderelationCOMMANDE (NUM,CNOM,PNOM,QUANTITE)

REQUÊTE: Clientayantcommandéla pluspetitequantitédeBriques

SQL:

SELECT CNOM

FROM COMMANDE

WHERE PNOM= ’Brique’ AND

QUANTITE <= ALL (SELECT QUANTITE

FROM COMMANDE

WHERE PNOM= ’Brique’)

NOTE: La condition � � ALL (SELECTF FROM . . . ) estvraiessila comparaison�I���estvraiepourtouteslesvaleurs� du résultatdubloc (SELECTF FROM . . . ).

Vertigo/CNAM, Paris 223

Soit le schémaderelations

EMPLOYE(EMPNO,ENOM,DEPNO,SAL)

DEPARTEMENT (DEPTNO,DNOM,LOC)

REQUÊTE: Départementssansemployés

SQL:

SELECT DEPTNO

FROM DEPARTEMENT

WHERE DETPNONOT = ALL (SELECT DISTINCT DEPTNO

FROM EMPLOYE)

NOTE: LesprédicatsNOT IN etNOT = ALL sontutilisésdefaçonéquivalente.

Vertigo/CNAM, Paris 224

Requêtesimbriquéesplus complexes: EXISTS

Soit le schémaderelations

FOURNISSEUR(FNOM,STATUS,VILLE)FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Fournisseurs qui fournissentaumoinsunproduit

SQL : SELECT FNOM

FROM FOURNISSEUR

WHERE EXISTS (SELECT *

FROM FOURNITURE

WHERE FNOM= FOURNISSEUR.FNOM)

NOTE: La conditionEXISTS(SELECT* FROM . . . ) estvraiessile résultatdubloc(SELECTF FROM . . . ) n’estpasvide.

Vertigo/CNAM, Paris 225

Soit le schémaderelations

FOURNISSEUR(FNOM,STATUS,VILLE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Fournisseurs qui nefournissentaucunproduit

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE NOT EXISTS (SELECT *

FROM FOURNITURE

WHERE FNOM= FOURNISSEUR.FNOM)

NOTE: La conditionNOT EXISTS(SELECT* FROM . . . ) estvraiessile résultatdu

bloc (SELECTF FROM . . . ) estvide.

Vertigo/CNAM, Paris 226

FormesÉquivalentesdeQuantification

Si � estundesopérateursdecomparaison� ��� ��� ���N���– La conditionx � ANY (SELECTRi.y FROM R1, �N��� RnWHEREp) estéquivalente

à

EXISTS(SELECT* FROM R1, ���N� RnWHEREp AND x � Ri.y)

– La conditionx � ALL (SELECTRi.y FROM R1, ���N� RnWHEREp) estéquivalente

à

NOT EXISTS(SELECT* FROM R1, ���N� RnWHERE(p) AND NOT (x � Ri.y))

Vertigo/CNAM, Paris 227

Soit le schémaderelations

COMMANDE (NUM,CNOM,PNOM,QUANTITE)

FOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Nom,prix et fournisseurdesproduitscommandéspar Jean

SELECT PNOM, PRIX, FNOM FROM FOURNITURE

WHERE EXISTS (SELECT * FROM COMMANDE

WHERECNOM= ’JEAN’

AND PNOM= FOURNITURE.PNOM)

SELECT PNOM, PRIX, FNOMFROM FOURNITURE

WHERE PNOM= ANY (SELECT PNOM FROM COMMANDE

WHERE CNOM= ’JEAN’)

Vertigo/CNAM, Paris 228

Soit le schémaderelationFOURNITURE (PNOM,FNOM,PRIX)

REQUÊTE: Fournisseurs qui fournissentaumoinsunproduit avecuncoûtsupérieurau

coûtdesproduitsfournispar Jean

SQL:

SELECT DISTINCT P1.FNOM

FROM FOURNITUREP1

WHERE NOT EXISTS (SELECT * FROM FOURNITUREP2

WHERE P2.FNOM = ’JEAN’

AND P1.PRIX <= P2.PRIX)

SELECT DISTINCT FNOMFROM FOURNITURE

WHERE PRIX > ALL (SELECT PRIX FROM FOURNITURE

WHEREFNOM= ’JEAN’)

Vertigo/CNAM, Paris 229

Division

Soit le schémaderelations

FOURNITURE (FNUM,PNUM,QUANTITE)

PRODUIT (PNUM,PNOM,PRIX)

FOURNISSEUR(FNUM,FNOM,STATUS,VILLE)

REQUÊTE: Fournisseurs qui fournissenttouslesproduits

Vertigo/CNAM, Paris 230

ALGÈBRE:

R1:= �2�C� ��� �B�<� ��� �G� � � � � �S  � � ¡ ¢�£ �j�<� ��� �G¤ � � ¥ � �S  ¢R2:= � �C�6¦8� �b� � � � � �!§`§�¡ � � ¨ � ©�¢

SQL:

SELECT FNOM

FROM FOURNISSEUR

WHERE NOT EXISTS

(SELECT *

FROM PRODUIT

WHERENOT EXISTS

(SELECT *

FROM FOURNITURE

WHEREFOURNITURE.FNUM= FOURNISSEUR.FNUM

AND FOURNITURE.PNUM= PRODUIT.PNUM))

Vertigo/CNAM, Paris 231

Fonctionsde Calcul

Vertigo/CNAM, Paris 232

COUNT, SUM, AVG, MIN, MAX

REQUÊTE: NombredeFournisseurs deParis

SELECT COUNT(*) FROM FOURNISSEUR

WHERE VILLE = ’Paris’

REQUÊTE: NombredeFournisseurs qui fournissentactuellementdesproduits

SELECT COUNT(DISTINCT FNOM) FROM FOURNITURE

NOTE: La fonctionCOUNT(*) comptele nombredesª -upletsdu résultatd’unerequête

sanséliminationdesdupliquésni vérificationdesvaleursnulles.Dansle cascontraireon

utilise la clauseCOUNT(UNIQUE . . . ).

Vertigo/CNAM, Paris 233

REQUÊTE: QuantitétotaledeBriquescommandées

SELECT SUM (QUANTITE)

FROM COMMANDES

WHERE PNOM= ’Brique’

REQUÊTE: CoûtmoyendeBriquesfournies

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

FROM FOURNITURE ou FROMFOURNITURE

WHERE PNOM= ’Brique’ WHEREPNOM= ’Brique’

Vertigo/CNAM, Paris 234

REQUÊTE: Leprix desbriquesqui sontle pluschères.

SELECT MAX (PRIX)

FROM FOURNITURE

WHERE PNOM= ’Briques’;

Vertigo/CNAM, Paris 235

REQUÊTE: Fournisseurs desBriquesaucoûtmoyendesBriques

SELECT FNOM

FROM FOURNITURE

WHERE PNOM= ’Brique’ AND

PRIX < (SELECT AVG(PRIX)

FROM FOURNITURE

WHEREPNOM= ’Brique’)

Vertigo/CNAM, Paris 236

Opérationsd’Agrégation

Vertigo/CNAM, Paris 237

GROUP BY

REQUÊTE: Nombredefournisseurspar ville

SELECT VILLE, COUNT(FNOM)

FROM FOURNISSEUR

GROUPBY VILLE

Vertigo/CNAM, Paris 238

LA BASEET LE RESULTAT :

VILLE FNOM

PARIS TOTO

PARIS DUPOND

LYON DURAND

LYON LUCIEN

LYON REMI

VILLE COUNT(FNOM)

PARIS 2

LYON 3

NOTE: La clauseGROUPBY permetdepréciserlesattributsdepartitionnementdesrelationsdeclaréesdansla clauseFROM. Parexempleon regroupelesfournisseursparville.

Vertigo/CNAM, Paris 239

REQUÊTE: Donnerpourchaqueproduit fourni soncoûtmoyen

SELECT PNOM, AVG (PRIX)

FROM FOURNITURE

GROUPBY PNOM

RÉSULTAT:

PNOM AVG (PRIX)

BRIQUE 10.5

ARDOISE 9.8

NOTE: Lesfonctionsdecalculappliquéesaurésultatderégroupementsontdirectement

indiquéesdansla clauseSELECT. Parexemplele calculdela moyennesefait par

produitobtenuaurésultataprèsle regroupement.

Vertigo/CNAM, Paris 240

HAVING

REQUÊTE: Produitsfournispar deuxou plusieurs fournisseurs avecuncoûtsupérieur

de100

SELECT PNOM

FROM FOURNITURE

WHERE PRIX > 100

GROUPBY PNOM

HAVING COUNT(*) >= 2

Vertigo/CNAM, Paris 241

AVANT LA CLAUSEHAVING

PNOM FNOM PRIX

BRIQUE TOTO 105

ARDOISE LUCIEN 110

ARDOISE DURAND 120

APRÈSLA CLAUSEHAVING

PNOM FNOM PRIX

ARDOISE LUCIEN 110

ARDOISE DURAND 120

NOTE: La clauseHAVING permetd’éliminer despartitionnements,commela clause

WHEREéliminedes« -upletsdu résultatd’unerequête.Parexempleongardeles

produitsdontle nombredesfournisseursest ¬ ­ 2. Decettefaçondesconditionsde

sélectionpeuventêtreappliquéesavantle calculd’agrégat (clauseWHERE)maisaussi

après(clauseHAVING).

Vertigo/CNAM, Paris 242

REQUÊTE: Produitsfourniset leur coûtmoyenpour lesfournisseurs dontle siège està

Paris seulementsi le coûtminimumduproduit estsupérieurà 1000F

SELECT PNOM, AVG(PRIX)

FROM FOURNITURE, FOURNISSEUR

WHERE VILLE = ’Paris’ AND

FOURNITURE.FNOM= FOURNISSEUR.FNOM

GROUPBY PNOM

HAVING MIN(PRIX) > 1000

Vertigo/CNAM, Paris 243

ORDER BY

Engénéral,le résultatd’unerequêteSQL n’estpastrié. Pourtrier le résultatparrapport

auxvaleursd’un ou deplusieursattributs,onutilise la clauseORDERBY:

SELECT VILLE, FNOM, PNOM

FROMFOURNITURE, FOURNISSEUR

WHEREFOURNITURE.FNOM= FOURNISSEUR.FNOM

ORDERBY VILLE, FNOMDESC

Le résultatesttrié parlesvilles (ASC) et le nomsdesfournisseurdansl’ordre inverse

(DESC).

Vertigo/CNAM, Paris 244

RécursiondansSQL3 (*)

Vertigo/CNAM, Paris 245

Enfants (*)

Soit le schémaderelationENFANT (NOMPAR,NOMENF)

REQUÊTE: LesenfantsdeCharlemagne

SQL:

SELECT NOMENF

FROMENFANT

WHERENOMPAR=’Charlemagne’ ;

Vertigo/CNAM, Paris 246

Descendants(*)

Soit le schémaderelationENFANT (NOMPAR,NOMENF).

REQUÊTE: LesdescendantsdeCharlemagne

SQL:

WITH RECURSIVE DESCENDANT(NOMANC,NOMDESC)AS

(SELECT NOMPAR,NOMENFFROMENFANT)

UNION

(SELECT R1.NOMANC, R2.NOMDESC

FROMDESCENDANTR1, DESCENDANTR2

WHERER1.NOMDESC=R2.NOMANC)

SELECT NOMDESCFROMDESCENDANT

WHERENOMANC=’Charlemagne’ ;

Vertigo/CNAM, Paris 247

Misesà jour avecSQL

Vertigo/CNAM, Paris 248

Création de Tables

On créeunetableavecla commandeCREATE TABLE :

CREATE TABLE Produit(pnom VARCHAR(20),

prix INTEGER,

PRIMARY KEY (pnom));

CREATE TABLE Fournisseur(fnom VARCHAR(20) PRIMARY KEY,

ville VARCHAR(16));

CREATE TABLE Fourniture (pnom VARCHAR(20) NOT NULL,

fnom VARCHAR(20) NOT NULL,

FOREIGN KEY (pnom) REFERENCESProduit,

FOREIGN KEY (fnom) REFERENCESFournisseur);

Vertigo/CNAM, Paris 249

Destruction deTables

On détruitunetableavecla commandeDROP TABLE :

DROPTABLE Fourniture;

DROPTABLE Produit;

DROPTABLE Fournisseur;

Vertigo/CNAM, Paris 250

Insertion de n-uplets

On insèredansunetableavecla commandeINSERT dontvoici la syntaxe.

INSERT INT O ® ¯G° ±�²³° ´�²�µ�µNµ¶²³° ·¹¸ VALUES ¯»º¼±�²½º�´�²�µNµ�µ¾º�·¹¸Doncondonnedeuxlistes: cellesdesattributs(les ° ¿ ) dela tableet celledesvaleurs

respectivesdechaqueattribut (les º�¿ ).1. Bienentendu,chaque° ¿ doit êtreunattribut de ®2. Lesattributsnon-indiquésrestentàNULL ouà leurvaleurpardéfaut.

3. Ondoit toujoursindiquerunevaleurpourunattribut déclaréNOT NULL

Vertigo/CNAM, Paris 251

Insertion : exemples

Insertiond’uneligne dansProduit:

INSERT INT O Produit (pnom,prix)

VALUES (’Ojax’, 15)

Insertiondedeuxfournisseurs:

INSERT INT O Fournisseur(fnom,ville)

VALUES (’BHV’, ’Paris’), (’Casto’, ’Paris’)

Il estpossibled’insérerplusieurslignesenutilisantSELECT

INSERT INT O NomsProd (pnom)

SELECT DISTINCT pnomFROM Produit

Vertigo/CNAM, Paris 252

Modification

Onmodifieunetableavecla commandeUPDATE dontvoici la syntaxe.

UPDATE À SET Á Â6à Ä�Â�ÅÆÁ Ç Ã Ä�Ç�Å�ÈNÈ�ȶÅÆÁ É Ã Ä�ÉWHERE condition

Contrairementà INSERT, UPDATE s’appliqueà unensembledelignes.

1. Onénumèrelesattributsquel’on veutmodifier.

2. On indiqueà chaquefois la nouvelle valeur.

3. La clauseWHERE conditionpermetdespécifierleslignesauxquelless’appliquela

miseà jour. Elle estidentiqueauWHERE du SELECT

Bienentendu,onnepeutpasvioler lescontraintessurla table.

Vertigo/CNAM, Paris 253

Modification : exemples

Miseà jour du prix d’Ojax:

UPDATE ProduitSET prix=17

WHERE pnom= ’Ojax’

Augmenterlesprix detouslesproduitsfournisparBHV par20%:

UPDATE ProduitSET prix = prix*1.2

WHERE pnomin (SELECT pnom

FROM Fourniture

WHERE fnom= ’BHV’ )

Vertigo/CNAM, Paris 254

Destruction

Ondétruituneouplusieurslignesdansunetableavecla commandeDELETE

DELETE FROM ÊWHERE condition

C’estla plussimpledescommandesdemise-à-jourpuisqueelles’appliqueàdeslignes

etpasàdesattributs.Commeprécédemment,la clauseWHERE conditionest

indentiqueauWHERE duSELECT

Vertigo/CNAM, Paris 255

Destruction: exemples

Destructiondesproduitsfournit parle BHV :

DELETE FROM Produit

WHERE pnomin (SELECT pnom

FROM Fourniture

WHERE fnom= ’BHV)

Destructiondu BHV :

DELETE FROM Fournisseur

WHERE fnom= ’BHV’