concepts de bases des bases de données...

Université de Versailles Saint-Quentin-en-Yvelines IUT de Vélizy

Licence ISDRN

Concepts de bases

des bases de données relationnelles

Support de cours

Fait par : Karine ZEITOUNI

Edition 2005

TABLE DES MATIERES

RAPPELS SUR LA GESTION DE FICHIERS ............................................................................................................ 2

INTRODUCTION AUX SGBD .................................................................................................................................... 7

PROBLÈME A RÉSOUDRE............................................................................................................................................. 8

SOLUTION........................................................................................................................................................................ 9

OBJECTIFS DES SYSTÈMES DE BASES DE DONNÉES.......................................................................................... 10

DÉFINITIONS................................................................................................................................................................. 11

HISTORIQUE ET ÉVOLUTION .................................................................................................................................... 12

LE MODELE ENTITE / ASSOCIATION.................................................................................................................. 13

MODELE ENTITÉ / ASSOCIATION ............................................................................................................................. 14

LE MODÈLE RELATIONNEL ................................................................................................................................. 17

DESCRIPTION DES DONNEES .............................................................................................................................................. 18

REGLES DE TRADUCTION DU MODELE E/A AU MODELE RELATIONNEL .............................................................................. 20

MANIPULATION DES DONNEES .......................................................................................................................................... 21

LE LANGAGE SQL ..................................................................................................................................................... 29

PRESENTATION ................................................................................................................................................................. 30

LANGAGE DE DESCRIPTION DE DONNEES (LDD) ............................................................................................................... 31

LANGAGE DE MANIPULATION DE DONNEES (LMD) ........................................................................................................... 33

VUES, INTÉGRITÉ....................................................................................................................................................... 41

DÉFINITION ET UTILITÉ DES VUES.......................................................................................................................... 42

INTÉGRITÉ SÉMANTIQUE.......................................................................................................................................... 46

RAPPELS SUR LA GESTION DE FICHIERS

Rappels sur la gestion de fichiers 3

Définitions

Fichier Récipient d'information caractérisé par un nom, permettant d'écrire des programmes d'application indépendants des mémoires secondaires.

Un fichier peut être de différents types : ascii (ex: fichiers textes en C), exécutable, répertoire,

ou encore fichier de données. Il se caractérise plus particulièrement par :

- un nom

- un créateur

- une date de création

- un ou plusieurs types d'article

- un emplacement en mémoire secondaire

- une organisation

Article Elément composant d'un fichier correspondant à l'unité de traitement par les programmes d'application.

Fichier_de_données = { Articles }

Exemple

FICHIERS = { VINS (NV,CRU,QUALITE) }

1, Beaujolais, Excellente

2, Chenas,Mediocre

3, Julienas, Mediocre

5, Beaujolais, Bonne

7, Chenas, Excellente

14, Chenas, Bonne

…

Système de gestion de fichier (SGF) Système offrant à l’utilisateur des fonctions de haut niveau pour manipuler des fichiers. Il ne traite pas le contenu des articles.


Types d’accès

Organisation de fichier Structure interne du fichier, dont dépendent les algorithmes de lecture/écriture du SGF. On distingue les organisations suivantes : séquentielle, indexée, relative, aléatoire.

Méthode d'accès Manière dont l’utilisateur peut manipuler le fichier pour sélectionner des articles. Elle dépend de l’organisation. Il peut y avoir plusieurs méthodes d’accès pour un même fichier (ex: en organisation indexée, cf. exemple plus loin).

Organisation séquentielle Les articles sont placés les uns à la suite des autres, lors des ajouts. La figure ci-dessous donne une vue d’un tel fichier.

Article1 Article2 Articlei…

Début de Fichier (DF)

Fin de Fichier (FF)

Pointeur de Lecture Ecriture (PLE)

Procédures d’accès aux fichiers séquentiels

• Ouvrir (Nom_fichier, Usage, S: Code_rep)

Usage = Lecture ou Ecriture [ou parfois Extension]

Code_rep = OK, fichier déjà ouvert, fichier inexistant, ouvert en lecture mais vide, ouvert en écriture mais plein.

• Fermer (Nom_fichier, S: Code_rep)

Code_rep = OK, fichier non ouvert, ou fichier inexistant.

• Lire (Nom_fichier, E/S : Var_article, S: Code_rep)

Var_article = Variable du même type que l’article correspondant à la zone en mémoire où sera transféré l’article lu.

Code_rep = OK, fichier non ouvert en lecture, article trop long, article trop court.

Remarque : Le PLE (Pointeur de Lecture) est géré par le SGF, qui l’avance à l’article suivant

après chaque lecture. Deux lectures consécutives concerneront 2 articles qui se suivent dans le

fichier.

• Ecrire (Nom_fichier, E : Var_article, S: Code_rep)

Var_article = Variable du même type que l’article correspondant à la zone en mémoire qui sera transférée dans le nouvel article en fin de fichier.

Code_rep = OK, fichier non ouvert en écriture, espace disque saturé (rarement).


Organisation indexée (ou séquentielle indexée) Organisation comprenant :

• une partie pour les données où les articles sont placés les uns à la suite des autres, lors des ajouts.

• une ou plusieurs parties pour l’index (ou les index)

Clé d'article

Un champ ou une combinaison de champs, dont la valeur permet de sélectionner un article (généralement unique) dans un fichier. Il peut y avoir plus d’une clé par fichier.

Index

Une table ou une hiérarchie de tables associant à chaque valeur de clé l’adresse du (ou des) article correspondant. L’index est dit unique si la clé est unique et dupliqué si la clé l’est.

Intérêt

L’intérêt du séquentiel indexé est de permettre l’accès direct aux articles selon leur valeur de

clé. Une seconde méthode d’accès à ces fichiers est le séquentiel trié selon la clé de l’index.

On peut également combiner les deux : se placer sur un article par accès direct, puis lire

séquentiellement les articles suivants. Il est en plus performant (rapide).

Remarque : Il existe plusieurs implémentations d’index : statique (ISAM) ou dynamique

(arbre-B et arbre-B+) et plusieurs variantes pour une même méthodes d’accès.

Procédures d’accès aux fichiers indexés

• Ouvrir (Nom_fichier, Usage, Mode_d_Accès, S: Code_rep)

Usage = Lecture , Ecriture , Lecture et Ecriture

Mode_d_Acces = Sequentiel, Direct, Dynamique (c.à.d. Seq + Direct).

Accès direct aux fichiers indexés :

• Lire_Dir (Nom_fichier, Nom_index, Val_clé, E/S : Var_article, S: Code_rep)

Code_rep = OK, fichier non ouvert, article ayant Val_clé non trouvé, plus d’un article trouvé (index dupliqué)

• Ecrire_Dir (Nom_fichier, E : Var_article, S: Code_rep)

Insère un nouvel article dans le fichier et dans les index (au milieu) déclarés pour ce fichier.

Code_rep = OK, fichier non ouvert, clé dupliquée et index unique (erreur).

• Modifier_Dir (Nom_fichier, E : Var_article, Nom_index, Val_clé, S: Code_rep)

Modifie un article donné en accédant par sa valeur de clé.

Code_rep = OK, fichier non ouvert, article ayant Val_clé non trouvé, clé dupliquée après modification et index unique (erreur).


• Supprimer_Dir (Nom_fichier, Nom_index, Val_clé, S: Code_rep)

Supprime un article donné en accédant par sa valeur de clé et le supprime du ou des index.

Code_rep = OK, fichier non ouvert, article ayant Val_clé non trouvé.

Accès séquentiel aux fichiers indexés :

Selon l’index utilisé par la dernière commande d’accès direct ou par une commande

“Positionner”, on peut lire dans l’ordre de la clé de cet index les articles suivants.

• Positionner (Nom_fichier, Nom_index, comparateur, Val_clé, S: Code_rep)

On ne lit pas mais on positionne simplement le PLE au premier article =, < ou ≤ à Val_clé.

Comparateur = “≥“, “<“, “=“

Code_rep = OK, fichier non ouvert, article ayant Val_clé non trouvé …

• Lire_Seq (Nom_fichier, E/S : Var_article, S: Code_rep)

Lit l’article désigné par le PLE et avance ce PLE à l’article suivant dans l’ordre de l’index courant.

• Ecrire_Seq (Nom_fichier, E : Var_article, S: Code_rep)

Idem pour l’écriture. Sert au chargement.

Organisation relative et aléatoire Organisation séquentielle où les articles (de taille fixe) sont accédés directement par numéro d’ordre (à l’image d’un tableau accédé par indice). L’adressage est relatif, car c’est un déplacement par rapport au début du fichier.

Accès direct par valeur de clé d’un champs. Les articles sont non triés. L’organisation est dite aléatoire, car elle utilise une méthode de hachage basée sur une fonction aléatoire, qui attribut à la clé un numéro d’ordre servant à son placement dans le fichier.

INTRODUCTION AUX SGBD

Introduction aux SGBD 8

Gestion, de manière cohérente et efficace, d'une grande masse de données reliées entre elles

⇒ Applications de gestion

Exemple

ENTREPRISE

SERVICE COMMERCIAL

SERVICE LIVRAISON

SERVICE GESTION

DES STOCKS

SERVICE DU PERSONNEL

AGENTS_EMP CLIENTS

COMMANDES PROD_VENDUS

VEHICULES COM_LIVREES

ENTREPOTS PRODUITSEMPLOYES

DO

NN

EE

SA

PP

LIC

AT

ION

S

Comment représenter les liens entre les données d’une même application ou d’applications différentes ?

CLIENT COMMERCIALCOMMANDE à

NUNCLI

NOM

ADRESSE

NUMEMP

COMMISSIONSALAIRE

NUMCOM

DATE

NOM

Liens entre CLIENT et

COMMERCIAL par

COMMANDE

Insuffisance des systèmes de gestion de fichiers

Les premières solutions ont consisté à utiliser un environnement de type gestion de fichiers

basé sur un langage de programmation

↓↓↓↓ Chaque application possède:

son équipe de programmation

ses supports physiques

ses fichiers

sa norme

son langage (ex: cobol, PASCAL, fortran)

↓ ↓ ↓ ↓ L'organisation des données dépend des traitements à effectuer par application

PROBLÈME A RÉSOUDRE


Conséquences

↓ ↓ ↓ ↓ multiplication des supports informatiques et redondances (c.à.d. duplication) anarchiques

des fichiers

⇒ incohérences des versions et non maintenabilité

↓ ↓ ↓ ↓ multiplication des saisies et des tâches de programmation

↓ ↓ ↓ ↓ non portabilité des traitements

⇒⇒⇒⇒ multiplication des coûts

↓ ↓ ↓ ↓ difficultés d’accès aux données

↓ ↓ ↓ ↓ problèmes de sécurité, confidentialité et de fiabilité

↓ ↓ ↓ ↓ problèmes de partage des données.

...Problèmes humains

↓ ↓ ↓ ↓ coût en personnel qualifiés et en formation

↓ ↓ ↓ ↓ remise des pouvoirs de décision entre les main de spécialistes informaticiens

↓ ↓ ↓ ↓ non circulation de l'information

↓ ↓ ↓ ↓ tâches de maintenance : plus de 65%

• dépendance de l'environnement matériel et logiciel

• dépendance des données

⇒ baisse de productivité

→→ Administration unique et centralisée des données à l’échelle de l'entreprise

⇒ notion de BASE DE DONNEES

SOLUTION


• Etablissement d'une structure canonique des données indépendante des programmes.

(cf. modèle plus loin)

• Définition de plusieurs niveaux d’abstraction : un niveau externe donnant plusieurs vues

partielles aux applications, un niveau logique global de la base de données et un niveau

physique de stockage.

• Centralisation de l’administration de base de données. Une personne appelée DBA (Data

Base Administrator) organise les données (au niveau logique et au niveau du stockage

physique) et intègre plusieurs applications.

• Constitution d'une équipe de programmeurs d’applications. Celle ci peut opérer sur une vue

partielle de la BD.

• Indépendance logique et physique, ce qui permet au programmeur et au DBA de se

concentrer sur le niveau d’abstraction -externe, logique ou physique- qu’ils manipulent.

• Utilisation d'outils de haut niveau accessibles aux non spécialistes : les utilisateurs accèdent

directement aux données ou via les applications.

• Garantie de la sécurité, de la confidentialité et de l’intégrité des données.

• Contrôle de la redondance des données, par élimination des duplications anarchiques qui

mènent aux incohérences.

• Partage des données simultanément par plusieurs utilisateurs, en gérant les conflits d’accès.

• Optimisation des temps d’accès aux données et de leurs traitements (par le système ou par

des outils du systèmes offerts aux développeurs) afin d’assurer un haut débit de transactions.

SQL

optimiseur de requêtes

opérateurs

relationnels

COMPILATION

EXECUTION

ACCES

Stockage

Gérant des données

Métabase

Architecture type d’un SGBD Relationnel

OBJECTIFS DES SYSTÈMES DE BASES DE DONNÉES


Base de Données (BD)

C’est un ensemble de données,

logiquement reliées entre elles,

permettant l’évolution indépendante des programmes et des données.

Une base de données est mise en œuvre au moyen d'un logiciel spécialisé : un SGBD

Système de Gestion de Bases de Données (SGBD)

C’est un logiciel permettant aux utilisateurs de :

mettre en forme, sauvegarder, interroger et mettre à jour une base de données,

tout en garantissant l’intégrité, la confidentialité et la sécurité des données.

L’indépendance des données par rapport aux traitements nécessite une description des données

en elles-mêmes. La description des données peut être effectuée directement par les utilisateurs

à partir d'une vision abstraite de la réalité ⇒ nécessité d'un modèle de données

Modèle

Outil ou mode de perception et de description du monde réel.

Il décrit les données, les liens entre ces données et les contraintes sur ces données

→→ le formalisme de description est souvent textuel ou graphique

Exemples de modèles de données : • entité / association

• relationnel

• hiérarchique

• réseau

Schéma

Un schéma est l'application d'un modèle à des données particulières.

Les schémas sont stockés dans le dictionnaire des données (ou métabase).

DÉFINITIONS


1960 : Génération 0 → Systèmes de gestion de fichiers

→→ sans modèle de données,

→→ sans liens entre les données dans les fichiers

1965-1975 : 1° génération → définition de liens entre les données

• modèle hiérarchique

→→ liens père - fils entre les données

• modèle réseau

→→ liens de type graphe entre les données

→→ langages navigationnels d’accès aux données

commet

produit

région

reçoit

emet

provoque

producteur vins

buveurs abus

com

- Find

- GetProgramme

1970 (labo)-1980 (sur le marché) : 2° génération → modèle relationnel • transparence des liens entre les données et modèles à trois niveaux

→→ des tables (relations) associent les données

→→ un langage de haut niveau permet d'extraire des données d'une table ou d'une association

de plusieurs tables

millcru degrénv

10

20

30

julienas

chenas

tokay

1978

1975

1981

11.8

11.2

11.1

VINS

nb nom prénom type nv date qténb

100

200

300

gros

gros

petit

Pierre

Thomas

Durand

Martin

Dupont100

200 10

10 10

2024.12.90

31.12.90

BUVEUR ABUS

Paul

1985 (labo) -1992 (sur le marché) : 3° génération → SGBD avancés

• modèle orienté objet et relationnel extensible →→ documents, images, CAO,

multimédia

• bases de données actives →→ des faits (= données) + des règles (= comportement)

• systèmes répartis →→ base de données : client/serveur, multi-serveurs…

• systèmes parallèles →→ base de données localement distribuée sur des

mémoires de processeurs de machines parallèles

HISTORIQUE ET ÉVOLUTION

LE MODELE ENTITE / ASSOCIATION

Le modèle Entité/Association 14

Attribut ( ou donnée élémentaire )

Niveau d'abstraction le plus fin pris en considération pour une modélisation particulière

→ A un ensemble de données de même nature est associé un type élémentaire. Une donnée

particulière est une occurrence (= exemplaire) d'un type

Entité

Un élément du monde réel, composé d’attributs et possédant un nom.

Association

Relation, association, correspondance existant entre les entités du monde réel

Propriétés d'une association :

elle possède un nom

elle peut avoir des attributs

elle peut lier plusieurs entités à la fois (n-aire)

elle est caractérisée par une cardinalité (contrainte entre 2 entités) :

1 - 1 : bijection ou injection

1 - N : surjection ou application

N-M: correspondance quelconque

Graphisme :

NOM

RESERVE

ATTRIBUT

ASSOCIATION

ENTITE

CARDINALITES:

1

N

N 1

1

M

POSSEDE

CONDUIT

MANAGE

VOL

MODELE ENTITÉ / ASSOCIATION


Exemple :

POSSEDE PERSONNE VOITURE

NOM

PRENOMN°SSDATE

ACHAT PRIX

ACHAT TYPE

N°VEHICULE

MARQUE PUISSANCE

COULEUR

N M VENTE VENDEUR ACHETEUR

PRODUIT

P

EXEMPLE D'ASSOCIATION N-AIRE

Extensions

Attributs composés ou multi-valués

Précise les cardinalités (card_min, card_max) de chaque côté

Précise les rôles de chaque côté

Avantages/inconvénients

Utilise comme modèle de conception du schéma d'une BD

Utilise un graphisme clair qui sert de support de réflexion

Est très proche de la façon de penser d'un utilisateur non-informaticien

Relations réciproques supposées 1-1

Ne représente pas les contraintes sémantiques et de dépendance.

Pas de notion spécialisation d’entités (classes / sous-classes)

Remarque

Aucune implantation mais il existe des outils de traduction dans le modèle -implanté-

relationnel.

Ce modèle reste une référence dans la phase de conception de bases de données, car il permet

une bonne abstraction du monde réel. Il est notamment utilisé dans les méthodes de génie

logiciel, comme MERISE.


BASE DE DONNEES COMPAGNIE AERIENNE

NOM ADR. TEL.

PASSAGER

RESERVE

DATE NUM SIEGE

NOM ADR. SAL.

MB_EQUIPAGE EMP. NUM.EMBARQ. DEPARTDATE

INSTANCEEST-UN

PILOTENUM. LIC

NB H. VOL

AGREE

VOL NUM H.ARR.

ORIG. DEST. H.DEP.

AVION TYPE

LE MODÈLE RELATIONNEL (Inventé par T. CODD - ECOLE IBM San José en 1970)

Le modèle relationnel 18

Domaine

Ensemble des valeurs que peut prendre une donnée élémentaire.

Exemple :

COULEURS = { BLEU, BLANC, ROUGE }

BOOLÉEN = { VRAI, FAUX }

SALAIRE = [ 4 000 , 100 000 ]

ENTIER, RÉEL, CHAINES

Produit cartésien

Le produit cartésien de D1, D2, … , Dn

noté : D1 X D2 X … X Dn

est l'ensemble des tuples ( ou n-uplets)

< v1, v2, … , vn > tels que vi ∈ Di

Exemple :

D1 = { BLEU, BLANC, ROUGE }

D2 = { VRAI, FAUX }

D1 X D2 D1 D2

BLEU

BLEU

BLANC

BLANC

ROUGE

ROUGE

VRAI

FAUX

VRAI

FAUX

VRAI

FAUX

DESCRIPTION DES DONNEES


Relation ou Table

Sous-ensemble du produit cartésien d'une liste de domaines caractérisé par un nom

→→ une relation peut être vue comme une table où une ligne représente implicitement une entité du monde réel, et une colonne correspond aux valeurs d’un champ (dit attribut) particulier des entités.

→→ Pourquoi relation ? relation n-aire (entre n attributs)

Attribut

Nom donné à une colonne d'une relation

→→ Les attributs permettent de faire abstraction de l'ordre des colonnes.

Exemple de relation

VINS CRU ANNEE DEGRE

POMMARD

FLEURY

SAVIGNY

GRAVES

1988

1989

1984

1981

11,5

11

13

12,5

Clé d’une relation

Un attribut ou une combinaison d’attributs dont la valeur identifie chaque tuple de la relation.

→→ Il est recommandé de définir une clé courte, généralement un numéro ou un code.

Exemple : N° d’étudiant au lieu du (NOM,PRENOM)

Schéma d'une relation

Nom de la relation suivi du nom (et éventuellement du domaine) de ses attributs

Exemple

VINS ( CRU, ANNEE, DEGRE ) ou VINS ( CRU: TEXTE, ANNEE : DATE, DEGRE : REEL )

→→ Le schéma d'une base de données relationnelle est l'ensemble des schémas des relations composantes.

Remarque très importante : Les liens entre les données (provenant de plusieurs relations) sont représentés, dans le schéma de la BD, par la création d’attributs supplémentaires servant à la mise en correspondance des tuples. Ceci se fait par le report de clés formant ce qu’on appelle “clé étrangère ou de jointure”. Le mécanisme d’association avec des clés étrangères est décrit ci-dessous.


Traduction d’une entité :

→ Relation définie par les attributs de l'entité dont une partie est clé

Traduction d’une associations :

• binaire de cardinalité N-M :

→ Relation ayant comme attributs les clés des deux entités participantes plus ceux de

l'association s'il y en a. La clé de cette relation est formée de la composition des clés des

entités.

• binaire de cardinalité 1-N :

→ Pas de nouvelle relation, mais report de la clé de l'entité (côté du 1) dans la relation

correspondant à l'entité (côté du N) et des attributs de l'association s'il y en a. Ce report définit

ce que l'on appelle "clé étrangère"

• N-aire :

→ Relation ayant comme attributs les clés de toutes les entités participantes plus ceux de

l'association s'il y en a. La clé de cette relation est formée de la composition des clés des

entités.

Exemple

POSSEDE PERSONNE VOITURE

NOM

PRENOMN°SSDATE

ACHAT PRIX

ACHAT TYPE

N°VEHICULE

MARQUE PUISSANCE

COULEUR

• Si l’on considère un seul propriétaire (à qui appartient) par voiture (cardinalité 1-N)

VOITURE ( N°VEHICULE, MARQUE, TYPE, PUISSANCE, COULEUR, N°SS, DATE, PRIX)

PERSONNE ( N°SS, NOM, PRENOM)

• Si l’on s’intéresse à l’historique de propriété d’une voiture (à qui elle a appartenu) (cardinalité N-M)

VOITURE ( N°VEHICULE, MARQUE, TYPE, PUISSANCE, COULEUR)

PERSONNE ( N°SS, NOM, PRENOM)

POSSESSION ( N°SS, N°VEHICULE, DATE, PRIX)

Remarque: Nous verrons une méthode de conception plus rigoureuse basée sur des formes normales.

REGLES DE TRADUCTION DU MODELE E/A AU MODELE RELATIONNEL


Algèbre relationnelle

Une formalisation d'un ensemble d’opérateurs de base sur des relations permettant d'exprimer toutes les questions.

Ces opérateurs sont appelés : Opérateurs algébriques.

Ces opérateurs de base sont :

• Ensemblistes :

union, intersection, différence (2 relations en entrée de même schéma que la relation résultat),

produit cartésien (entre 2 relations quelconques)

• Relationnels :

restriction, projection (sur une relation)

jointure, division (sur 2 relations)

Union

R1 U R2 → R3

Intersection

R1 ∩ R2 → R3

Différence

R1 − R2 → R3

Produit cartésien

R1 (A1,A2,…,An) X R2 (B1,B2,…,Bm) → R3 (A1,A2,…,An, B1, B2,…,Bm)

MANIPULATION DES DONNEES


Restriction ou Sélection

σc (R1) → R2

La relation résultat ne contient que des tuples ayant certaines valeurs d'attributs : les valeurs vérifiant le critère de sélection “c”.

Notation:

σc (R) ou bien : Rest (R, c) Tel que: c = Critère

Exemple :

VOITURE N°VEH MARQUE TYPE PUISSANCE COULEUR

872RH78

700AB74

686HK78

720CD67

100XY92

RENAULT

PEUGEOT

RENAULT

PEUGEOT

RENAULT

R21

204

R18

205

R19

8

6

9

8

7

ROUGE

BLEUE

VERTE

ROUGE

BLEUE

σCOULEUR = ROUGE (VOITURE) ?

σMARQUE = RENAULT et COULEUR = ROUGE (VOITURE) ?

σCOULEUR = ROUGE ou PUISSANCE>8 (VOITURE) ?

Projection

ΠA1 A2 … Ap (R1) → R2 (A1,A2,...,Ap)

la relation résultat ne contient que des colonnes (attributs) désirées. les tuples en doubles engendres sont supprimés.

Notation: S = ΠA1 A2 … Ap (R) ou bien Proj (R, A1,A2,...,Ap)

Exemple:

Π COULEUR (VOITURE) =

VOITURE COULEUR

ROUGE

BLEUE

VERTE


Jointure

R1 (A1,A2,…,An) ıXı C R2 (B1,B2,…,Bm) → R3 (A1,A2,…,An, B1, B2,…,Bm)

Restriction, du produit cartésien de R1 et R2, aux seuls tuples vérifiant le critère “C” de comparaison d’attributs provenant de R1 et de R2 (ex. Ai=Bj). “C” est dit critère de jointure.

Remarque :

→ La jointure est une opération de mise en correspondance essentielle en relationnel. Elle resulte directement du report de clés (dites clés de jointures) nécessaire au moment de la modélisation.

Notation :

R1 ıXı C R2 OU Join (R1,R2, c) Tel que : c = critère de jointure

Exemple :

PLAGES REGION NOM

BRETAGNE

BRETAGNE

CALIFORNIE

NORMANDIE

CARNAC

BENODET

LONG BEACH

DEAUVILLE

LOCALISATION REGION PAYS

BRETAGNE

CALIFORNIE

NORMANDIE

FRANCE

USA

FRANCE

PLAGES ıXı LOCALISATION = LOC_PLAGES

LOC_PLAGES

REGION NOM PAYS

BRETAGRET

CARNAC

BENH

DEAUVILE

FRANCE

FRANCE

USAFRANC

Différents types de jointures :

Equi-jointure : la comparaison est une égalité. Jointure naturelle : équi-jointure sur les attributs de même nom des deux relations, en gardant une seule fois les colonnes correspondantes. Elle est notée simplement : Join (R1, R2)

ou R1 ıXı R2. C’est la plus courante;

Inéqui-jointure : inégalité; Semi-jointure : jointure où on ne garde que les attributs de la première relation R1,

notée: R1 ıX C R2; Auto-jointure : dans la même relation;


Jointure externe : jointure complétée par les tuples de R1, puis ceux de R2, ne vérifiant pas le critère de jointure. L’autre partie de la composition est complétée par des valeurs nulles. On parle de jointure externe gauche, droite et complète, selon qu’on complète par les tuples de R1, R2 ou des deux.

Extensions de l’algèbre relationnelle

Objectif :

Elles concernent l’introduction de facilitées pour des calculs, tels que les calculs

arithmétiques. On distingue les calculs tuple par tuple et ceux sur un ensemble de tuples

(agrégats).

Expression d’attributs

Calculs et/ou fonctions faisant intervenir des attributs d’un tuple et/ou des constantes.

=> Peut être utilisée à la place des attributs dans les critères et en projection

Exemple :

Proj (Join(C,V, C.NV=V.NV), QTE*DEG/100)

Notion d’agrégat

Partitionnement horizontal d’une relation en fonction d’un groupe d’attributs, suivi d’un regroupement par application d’une fonction de calcul sur un ensemble.

=> La fonction est appelée : fonction agrégat. Le résultat est formé des attributs de

regroupements éventuels et du résultat du calcul.

Exemple :

* Le cumul des quantités commandées par buveur :

-> Agrégat (C, NB, SUM (QTE))

* La moyenne des degrés de vins:

-> Agrégat (V, , MOY (DEG))

Valeur nulle

Valeur conventionnelle introduite dans une relation pour représenter une information inconnue ou inapplicable.

=> Sert, par exemple, pour constituer la jointure externe (cf. Types de jointures ci-dessus).

Elle peut être utilisée dans les critères (ex: Rest (B, PRENOM IS NULL))


Remarque :

Puisque ces opérateurs forment une algèbre, ils peuvent être composés ou imbriqués en restant

valables. Il a été démontré qu’ils permettent d’exprimer toutes les questions. Donc, l’algèbre

relationnelle est complète.

Langage algébrique

Composition de plusieurs opérateurs algébriques pour exprimer une question (dite requête).

Il constitue le langage interne d'un SGBD relationnel.

Base exemple : BD COOPÉRATIVE

VINS (NV,CRU,MIL, DEG)

VITICULTEURS (NVT,NOM,PRENOM,VILLE)

PRODUCTIONS (NV,NVT)

BUVEURS (NB,NOM,PRENOM,VILLE)

COMMANDES (NC,DATE,NV,QTE,NB)

EXPEDITIONS (NC,DATE,QTE)

SYNONYMES EMPLOYÉS -> VINS = V

-> VITICULTEURS = VT

-> PRODUCTIONS = P

-> BUVEURS = B

-> COMMANDES = C

-> EXPEDITIONS = E

Exemple 1 : Donner les numéros des vins de millésime 1975

1) TEMP <- Rest (V, MIL=1975)

2) RESU <- Proj (TEMP, NV)

qui s'écrit aussi :

RESU <- Proj( Rest(V, MIL=1975), NV)

Exemple 2 : Donner les noms des viticulteurs qui produisent du Muscadet

1) T1 <- Rest (V, CRU="Muscadet")

2) T2 <- Join (T1, P, T1.NV=P.NV)

3) T3 <- Join (T2, VT, T2.NVT = VT.NVT)

4) R <- Proj (T3, NOM)


R <- Proj (Join(Join(Rest(V,CRU="Muscadet"), P, NV=NV), VT, NVT=NVT), NOM )


Exemple 3 : Insertion d'un vin jurançon 1979 de degré 12 avec le numéro 150

V <- V ∪ { (150, "Jurançon", 1979, 12) }

Exemple 4 : Suppression des vins de millésime 1980

V <- V - Rest (V, MIL = 1980)

Exemple 5 : Donner les noms et les prénoms des buveurs habitant Paris qui ont commande du

Mâcon 1977 avant le 15/05/88

1) T1 <- Rest (B, Ville="Paris")

2) T2 <- Rest (C, DATE < 15/10/88)

3) T3 <- Rest (V, CRU="Macôn")

4) T4 <- Join (T1, T2, T1.NB=T2.NB)

5) T5 <- Join (T4, T3, T3.NV=T4.NV)

6) R <- Proj (T5, NOM, PRENOM)


R <- Proj ( Join ( Join ( Rest(B, Ville="Paris"), Rest(C, DATE < 15/10/88), NB ),

Rest(V, CRU="Macôn"), NV ),

NOM, PRENOM)

Arbre algébrique

Un arbre algébrique est un arbre représentant une requête où :

-> les noeuds feuilles représentent les relations de base

-> les noeuds intermédiaires représentent les opérateurs

-> le noeud racine représente le résultat

-> les arcs représentent un flux de données

Il constitue une description graphique du langage algébrique plus claire que la description textuelle.

Notations

V

V.NOM

PROJECTION

RES

V

V.CRU =

"Gamay"

RESTRICTION

V P

V.NV P.NV

=

JOINTURE

RESRES

Exemple : “Donner les noms et les prénoms des buveurs habitant Paris qui ont commandé du

Mâcon 1977 avant le 15/05/88"

On peut répondre de diverses façons :


B C V

VILLE="Paris"

DATE <

"15/05/88"

CRU="Mâcon"

MIL=1977

NB NB

=

=

NV NV

NOM

PRENOM

R

REPONSE 1

B C V

VILLE

="Paris"

DATE

<"15/05/88"

CRU="Mâcon"

MIL=1977

=

NV NV

=

NB NB

NOM

PRENOM

R

REPONSE 2


B C V

=

NB NB

=

NV NV

CRU="Mâcon"

MIL=1977

VILLE="Paris"

DATE < "15/05/88"

NOM

PRENOM

R

REPONSE 3

B C V

VILLE

="Paris"

DATE

<

"15/05/88"

CRU="Mâcon"

MIL=1977

NB

NOM

PRENOM

NV

NB

NV

=

NB NB

NV

NOM

PRENOM=

NV NV

NOM

PRENOM

R

REPONSE 4

LE LANGAGE SQL

Annexe 30

• SQL (Structured Query Language) est un langage normalisé de programmation de BD

Les standards successifs sont: SQL1 [ANSI-86, ISO-89], SQL2 [ISO-91], SQL3 [ISO-99].

• LMD (Langage de Manipulation de Données) équivalent à l’algèbre relationnelle

Requête d'interrogation <==> une suite d’opérations relationnelles

• Langage déclaratif ou assertionnel simple

• Propose dans tous les SGBD relationnels :

ORACLE, INGRES, SYBASE, INFORMIX, DB2, SQL/DS, RDB,

ACCESS, DBASE, …

=> Portabilité, facilité de communication, interfaçage avec d'autres outils

• Permet une manipulation logique

Notations

Mots clés → en MAJUSCULE GRAS

Paramètres → entre <chevrons>

Catégorie de mots clés → en MAJUSCULE

Optionnel → entre [crochets]

Alternatives de syntaxes → entre { accolades séparés par | barres}

PRESENTATION

Annexe 31

La table est la structure de base contenant les données des utilisateurs. Quand on crée une

table, on peut spécifier les informations suivantes :

la définition des colonnes, les contraintes d'intégrité,

les caractéristiques de stockage, La tablespace contenant la table, le cluster contenant la table.

Création de table (syntaxe simplifiée)

A la création, la table sera vide mais un certain espace lui sera alloué. La syntaxe est la

suivante :

CREATE TABLE <nom de table>

( <nom d'attribut 1> TYPE1 [NOT NULL],

<nom d'attribut 2> TYPE2 [NOT NULL],

… )

L'option NOT NULL assure qu'ORACLE interdit lors d'un INSERT ou d'un UPDATE que

cette colonne contienne la valeur NULL, par défaut elle est autorisée.

Les types dans ORACLE

Malgré la normalisation SQL 92, chaque SGBD a gardé ses spécificités dans le typage des

données. Les éditeurs précisent néanmoins la correspondance avec la norme.

NUMBER[(longueur,[précision])

permet de stocker des données numériques à la fois entières et réelles dont la valeur est

comprise entre 10^-130 et 10^125 avec une précision de 38 chiffres.

Longueur : précise le nombre maximum de chiffres significatifs stockés (par défaut 38),

Précision : donne le nombre maximum de chiffres après la virgule (par défaut 38), sa valeur

peut être comprise entre -84 et 127. Une valeur négative signifie que le nombre est arrondi.

CHAR(longueur)

permet de stocker des chaînes de caractères de longueur fixe. Longueur doit être inférieur à

255. Sa valeur par défaut est 1.

VARCHAR2(longueur)

permet de stocker des chaînes de caractères de longueur variable. Longueur doit être inférieur

à 255, il n'y a pas de valeur par défaut.

DATE

LANGAGE DE DESCRIPTION DE DONNEES (LDD)

Annexe 32

permet de stocker des données constituées d'une date et d'une heure.

RAW(longueur)

permet de stocker des caractères non imprimables. Longueur doit être inférieur à 255.

LONG

permet des stocker des chaînes de caractères de longueur variable et inférieure à 2^31 -1.

LONGRAW

Permet de stocker des données binaires de taille <= 65 535 octets, donné en Hexadécimal.

Les attributs de type LONG ou LONGRAW ne peuvent pas apparaître plus d'une fois dans une

table, ni dans des contraintes d'intégrité, ni dans un index, ni dans des clauses: WHERE,

GROUP BY, ORDER BY ou avec un DISTINCT.

Exemple :

Création de la table des vins

CREATE TABLE VINS

( NV NUMBER (4) NOT NULL,

CRU CHAR (20),

MIL DATE,

DEG NUMBER (3,1) )

Création / Suppression d'un index

CREATE [UNIQUE] INDEX <nom_index>

ON <relation (att1 [ASC | DESC] [, att2 [ASC | DESC],…]) >

→ Un index permet d'accélérer l'accès selon certains attributs, ceux mentionnés entre

parenthèses. L'ordre ASC (par défaut) ou DESC indique si ceux là sont pris dans l'ordre

croissant ou décroissants. La clause "UNIQUE" est optionnelle et indique que la combinaison

d'attributs indexés forme une clé de la relation. L'index peut, à tout moment, être supprimé ou

recréé. Ceci n'affecte pas le contenu (les tuples existants) de la relation indexée.

Exemples :

CREATE UNIQUE INDEX IDX_V ON VIN (NV) /* Vérifie aussi l'unicité de clé */

CREATE INDEX IDX_B ON BUVEUR (NOM,PRENOM) /* clé composée et non unique */

Création de Synonyme (Propre à Oracle)

Utilisation de V à la place de VINS par l'utilisateur courant (s'il a le droit d’accès à VINS)

CREATE SYNONYM V FOR VINS

Annexe 33

Utilisation, par tous les utilisateurs, de B à la place de la table BUVEURS de

l'utilisateur "COMPTE". commande autorisée pour DBA (voir DBA plus loin)

CREATE PUBLIC SYNONYM B FOR COMPTE.BUVEURS

Renommage d'une table :

RENAME <ancien nom de table> TO <nouveau nom de table>

Modification de schéma d'une table

ALTER TABLE <nom de table>

{MODIFY | ADD} ( <nom d'attribut 1> TYPE [NOT NULL | NULL],

<nom d'attribut 2> TYPE [NOT NULL | NULL], ...)

ADD permet l'ajout de nouvelles colonnes,

MODIFY change les paramètres d'attributs existants.

Exemples :

Changement de précision pour degré

ALTER TABLE VINS

MODIFY ( DEG NUMBER (4,2) )

Ajout d'un attribut nom_producteur obligatoire

ALTER TABLE VINS

ADD ( NOM_PRODUCTEUR CHAR (30) NOT NULL )

Suppression de table/ vue/ index/synonyme

DROP {TABLE | VIEW | INDEX | SYNONYM} <nom de l'objet>

Exemple :

Suppression de la table vins

DROP TABLE VINS

Syntaxe partielle de l’interrogation

SELECT <liste d'attributs projetés> /* quoi retourner */

FROM <liste de relations à joindre> /* de quelles relations */

LANGAGE DE MANIPULATION DE DONNEES (LMD)

Annexe 34

[ WHERE <liste de critères> ] /* sous quelles conditions */

Exemples : Recherche mono-relation

Lister tous les vins

SELECT *

FROM VINS

Donner les vins de CRU chablis

SELECT *

FROM VINS

WHERE CRU = 'Chablis' / * Restriction avec critère CRU = 'Chablis' */

Donner les CRUS des vins de millésime 1976 et de 12 degré

SELECT CRU /* Projection sur CRU */

FROM VINS

WHERE MIL = 1976 AND DEG = 12 /* Critère de restriction */

autre solution utilisant INTERSECT (cf. plus loin)

Recherche multi-relation

Quels sont les noms des viticulteurs qui produisent du Muscadet

SELECT NOM /* Attributs de projection */

FROM VITICULTEURS, PRODUCTIONS, VINS /* relations à joindre */

WHERE VITICULTEURS .NVT = PRODUCTIONS .NVT

AND PRODUCTIONS .NV=VINS .NV /* Critères de jointure */

AND CRU = 'Muscadet'; /* Critère de restriction */

Autre solution, (cf. requêtes imbriquées). Expression avec le langage algébrique (cf. plus haut)

Insertion de tuples dans une relation

→ d'un seul tuple

(a) INSERT INTO VINS

VALUES (100, 'Jurançon', 1979, 12)

(b) INSERT INTO VINS (NV, CRU)

VALUES (200, 'Gamay')

→ d'un ensemble de tuples

Annexe 35

CREATE TABLE BORDEAUX

(NV NUMBER, MIL NUMBER, DEG NUMBER)

INSERT INTO BORDEAUX

SELECT NV, MIL, DEG

FROM VINS

WHERE CRU = 'BORDEAUX'

Suppression de tuples d’une relation

de tous les tuples

DELETE FROM VINS

selon critère sur la relation

DELETE FROM VINS

WHERE DEG < 9 AND DEG > 12

selon critère sur plusieurs relations

DELETE FROM COMMANDES

WHERE NB IN ( SELECT NB FROM BUVEURS

WHERE NOM = 'Dupond' )

Modification de tuples d'une relation

de tous les tuples

UPDATE COMMANDES

SET QTE = QTE + 10

selon un critère

UPDATE VITICULTEUR

SET VILLE = 'Bordeaux'

WHERE NVT = 150

selon critère sur plusieurs relations

UPDATE COMMANDES

SET QTE = QTE + 10

WHERE NB IN ( SELECT NB

FROM BUVEURS

WHERE NOM = 'Dupond' )

Annexe 36

Syntaxe complète de l’interrogation

SELECT {[ALL | DISTINCT ] <colonne et/ou calculs>} | *

FROM <table1 [var1], table2 [var2] …>

[ WHERE <critère_rest et/ou critère_join et/ou requête imbriquée>]

[GROUP BY <colonne1 , colonne2…>

[HAVING <critères_regroupement>] ]

[{UNION | INTERSECT | MINUS} SELECT …]

[ORDER BY <colonne1 [ASC | DESC], …>] /*En fin de requête*/

• Ligne SELECT

ALL -> Sans élimination de doubles (Par défaut)

DISTINCT -> Avec élimination des doubles

colonne -> soit nom_att (si pas d'ambiguïté sur ce nom),

soit nom_table.nom_att, soit var_table.nom_att

soit * (toutes les colonnes) ou {nom_tab | var_tab}.*

calcul -> soit expression sur des colonne avec des opérateurs :

+, -, *, / éventuellement avec (), ou || (concaténation pour les chaînes de caractères)

soit des fonctions simples (sur une valeur) ou agrégat (sur un ensemble de valeurs),

soit la combinaison des expressions, fonctions et agrégats.

alias -> si mentionné, alias de la colonne, attribué dans le résultat de la requête.

• Ligne FROM

tablei -> nom d'une table de jointure, préfixé évt du compte du propriétaire, suivi évt d'une variable (alias) attribuée durant la requête

• Ligne WHERE (Optionnelle)

critère -> expression logique de prédicats de type :

- colonne comparateur { valeur | colonne | sous-requête} où le comparateur est :

=, !=, >, <, >=, <=, LIKE, {>|<|…} {ALL| ANY}

- colonne {IN (pour ∈) | NOT IN} { (valeur1, valeur2,…) | sous-requête}

- colonne BETWEEN valeur1 AND valeur2

- {EXISTS (pour ∃) | NOT EXISTS} sous_requête

• Ligne GROUP BY (Optionnelle)

colonnes -> attributs de regroupement.

But : partitionner horizontalement la relation selon les valeurs de certaines colonnes, appliquer

ensuite une fonction agrégat (sur d'autres colonnes) aux partitions.

Annexe 37

• Ligne HAVING (Optionnelle après regroupement)

critère_regroup. -> fonction_agrégat([DISTINCT] colonne) comparateur {valeur |sous-requête}

Quelques fonctions agrégats (DISTINCT en option sur l'argument) :

SUM (Somme), AVG (Moyenne), VARIANCE -> d'1 colonne

MIN, MAX -> d'1 expression sur des colonnes

COUNT (Comptage) -> des tuples résultats.

Exemples :

Restrictions / Projections / Ordre

Donner tous les CRUS, avec élimination des doubles

SELECT DISTINCT CRU /* Projection sur CRU */

FROM VINS

Trier les buveurs par nom croissant et par prénom décroissant

SELECT NOM, PRENOM

FROM BUVEURS

ORDER BY NOM ASC, PRENOM DESC

Donner les vins dont le millésime n'est pas renseigné

SELECT NV, CRU

FROM VINS

WHERE MIL IS NULL

Donner les buveurs dont le nom s’écrit Dupond ou avec une autre lettre que “d” à la fin.

SELECT *

FROM BUVEURS

WHERE NOM LIKE 'Dupon_'

Donner les commandes dont la quantité est comprise entre 12 et 36

SELECT NC, QTE, NB, NV

FROM COMMANDES

WHERE QTE BETWEEN 12 AND 36

Donner le nom et le salaire du manager du département commercial de N° 30

SELECT ‘Le nom :‘ || Ename || ‘ et le salaire :’ || SAL || ‘du manager du département commercial’

FROM EMP

WHERE DEPTNO=30 and JOB= ‘MANAGER’;

Annexe 38

Utilisation des opérateurs ensemblistes

Donner les VINS faibles en alcool

SELECT MIL, CRU, NV

FROM VINS

MINUS SELECT *

FROM VINS_FORTS

Donner les CRUS des vins de millésime 76 et de degré 12 : 2° solution utilisant INTERSECT

SELECT CRU

FROM VINS

WHERE MIL = 1976

INTERSECT SELECT CRU

FROM VINS

WHERE DEG = 12

Donner le produit cartésien des commandes et des expéditions (Aucun intérêt sémantique)

SELECT C.*, E.* /* absence de critère de jointure, donc aucune mise en corr. des tuples */

FROM COMMANDES C, EXPEDITIONS E

Utilisation des agrégats

Donner le nombre total de buveurs

SELECT COUNT (*)

FROM BUVEURS

Donner le nombre de CRUS différents

SELECT COUNT (DISTINCT CRU) FROM VINS

Donner le nombre de buveurs par ville

SELECT VILLE, COUNT (*)

FROM BUVEURS

GROUP BY VILLE

Donner la quantité moyenne de vin 10 qui a été commandée

SELECT AVG (QTE)

FROM COMMANDES

WHERE NV = 10

Donner la quantité totale des vins qui ont été commandés au moins 10 fois

SELECT NV, SUM (QTE) QTE_CUMUL /*alias sur colonne, affiché au lieu de SUM(QTE)*/

FROM COMMANDES

GROUP BY NV

Annexe 39

HAVING COUNT (*) >= 10

Jointures

Donner les commandes et leur expédition (jointure naturelle)

SELECT C.*, E.DATE DATE_ENVOI, E.QTE QTE_ENVOYEE /*distinction par alias sur colonne*/

FROM COMMANDES C, EXPEDITIONS E /*variables sur tables = abréviations*/

WHERE C.NC = E.NC /* critère d’une jointure */

Donner les viticulteurs et les vins qu’ils produisent

SELECT VT.*, V.*

FROM PRODUCTIONS P, VITICULTEURS VT, VINS V

WHERE P.NVT = VT.NVT AND P.NV = V.NV /* critères de 2 jointures */

Donner les viticulteurs qui produisent au moins 3 crus différents

SELECT VT.NVT, NOM, PRENOM /* préfixe VT obligatoire sinon ambiguïté */

FROM PRODUCTIONS P, VITICULTEURS VT, VINS V /*variables sur tables = abréviations*/

WHERE P.NVT = VT.NVT AND P.NV = V.NV

GROUP BY VT.NVT, NOM, PRENOM /* Omettre une des colonnes=> Erreur ! */

HAVING COUNT (DISTINCT CRU) >= 3

Utilisation des requêtes imbriquées

Quels sont les nom des viticulteurs qui produisent du Muscadet (exprime des semi-jointures)

SELECT NOM

FROM VITICULTEURS VT

WHERE NVT IN (SELECT NVT

FROM PRODUCTIONS P

WHERE NV (SELECT NV

FROM VINS V

WHERE CRU = 'Muscadet') )

Quelles sont les commandes de vins en quantité supérieure à la moyenne

SELECT NC, NV, NB

FROM COMMANDES

WHERE QTE > (SELECT AVG (QTE) FROM COMMANDES )

Donner les viticulteurs qui produisent tous les vins (exprime l’opérateur de division !)

⇔ ceux pour lesquels il n’y a pas de vins qui n’a pas été produit

SELECT VT.*

Annexe 40

FROM VITICULTEURS VT /*variables sur tables = abréviations*/

WHERE NOT EXISTS (SELECT *

FROM VINS V

WHERE NOT EXISTS (SELECT *

FROM PRODUCTIONS P

WHERE P.NV = V.NV AND P.NVT = VT.NVT))

Donner la quantité maximum de vin commandée

SELECT NC, QTE, NB, NV

FROM COMMANDES

WHERE QTE >= ALL (SELECT QTE FROM COMMANDES)

Donner le vin dont la quantité commandée est maximum, toute commandes confondues

SELECT NV

FROM COMMANDES

GROUP BY NV

HAVING SUM (QTE) >= ALL (SELECT SUM (QTE)

FROM COMMANDES

GROUP BY NV)

Création de table avec chargement


( <nom d'attribut 1> [NOT NULL],

<nom d'attribut 2> [NOT NULL], …

) AS <SELECT ...>

Exemples :

Création d'une table des vins de degré plus de 12 degré.

CREATE TABLE VINS_FORTS ( ANNEE, TYPE, NUMÉRO )

AS SELECT MIL, CRU, NV

FROM VINS

WHERE DEG >= 12

Annexe 41

VUES, INTÉGRITÉ

Annexe 42

Vue

Relation virtuelle (non stockée) d'un schémas, calculée à partir des relations de la base par une question SQL. Appelée aussi : vue partielle de la base de données ou schéma externe.

Intérêts :

• Adaptation aux applications (cache la complexité et permet des points de vues ≠)

• Simplification d'écriture des requêtes

• Permet certains contrôles de l'intégrité (CHECK OPTION)

• Confidentialité plus souple (Droits sur la vue)

• Intégration d'applications existantes (de schéma logique ≠)

• Dynamique du schéma de la base

• Décentralisation de l'administration (Droit DBA sur quelques vues)

Création d'une vue

CREATE VIEW <nom de vue> [(att1[, att2] ...)]

AS <REQUÊTE>

[WITH CHECK OPTION [CONSTRAINT <nom de contrainte> ]

Suppression d'une vue

DROP VIEW <nom de vue>

Une vue peut être manipulée exactement comme une table, sauf pour les mises à jour, où il

existe des restriction sur le type de requête définissant la vue.

Exemple :

VINS (NV,CRU,MIL, DEG,NVT)

VITICULTEURS (NVT,NOM,PRENOM,VILLE)

BUVEURS (NB,NOM,PRENOM,VILLE)

COMMANDES (DATE,NB,NV,QTE)

DÉFINITION ET UTILITÉ DES VUES

Annexe 43

On peut définir de nombreuses applications particulières, qui n'utilisent qu'une partie des

données. Si on s'intéresse à l'influence géographique sur la consommation de l'année 1990 :

• Par simple projection/restriction :

BUVEURS_1 (NB,VILLE)

VINS_1 (NV,CRU)

ACHETE_1 (NB,NV,QTE,DATE) /* Restriction aux dates de 1990 */

• Par restructuration :

ACHETE_2 (NB,VILLE,NV,CRU, QTE,DATE) /* Jointure des trois tables et restriction */

• Par agrégation :

BUVEURS_3 (NB,VILLE)

VINS_3 (NV,CRU)

CONSOMME_3 (NB, NV,SQTE) /* SQTE : somme des QTE pour l’année 90 par vin et buveur */

• Par combinaison des précédentes méthodes (Consommation Par Ville -CPV-):

CPV (VILLE, CRU,SQTE)

Cette dernière est créée en SQL par :

CREATE VIEW CPV (VILLE, CRU,SQTE) /* SQTE est un attribut de CPV calculé */

AS SELECT VILLE, CRU, SUM (QTE)

FROM BUVEURS B, COMMANDES C, VINS V

WHERE B.NB=C.NB AND V.NV=C.NV

AND DATE IS BETWEEN '90-JAN-01' AND '90-DEC-31'

GROUP BY VILLE, CRU

On veut définir que NV (respectivement NB) de COMMANDES existe dans VINS

(respectivement dans BUVEURS). C'est un moyen de définir des contraintes référentielles (cf.

plus loin) :

CREATE VIEW CMDES_VALIDES

AS SELECT *

FROM COMMANDES

WHERE NV IN

(SELECT NV FROM VINS)

AND NB IN

(SELECT NB FROM BUVEURS)

WITH CHECK OPTION

Annexe 44

Manipulation au travers des vues :

• Interrogation toujours possible

• Mise à jour INSERT : Si mono-table, sans agrégats (sans jointures)

UPDATE: sur colonnes non calculées, si mono-table sans agrégats

L'interrogation est traduite par une interrogation sur les tables d'origine. Les mises à jour

sont répercutées sur les tables d'origine.

Exemple :

• SELECT *

FROM CPV

WHERE VILLE = ‘PARIS’ OR VILLE=‘LYON’

• INSERT INTO CPV VALUES ('BORDEAUX', 'SANCERRE', 2292)

Ne peut être effectuée (répercussion impossible).

Interprétation à l'exécution.

Deux techniques :

• Modification de question

remplacement de la requête sur les vues par une requête sur les tables d’origine.

• Modification de l’arbre

mise bout à bout de l’arbre algébrique de la requête sur vue(s) et de(s) arbre(s) de(s) requête(s) définissant la (les) vue(s).

Exemple :

(a) Traduction de la requête d’interrogation précédente par modification de question :

SELECT VILLE, CRU, SUM (QTE)

FROM BUVEURS B, COMMANDES C, VINS V

WHERE B.NB=C.NB AND V.NV=C.NV

AND DATE IS BETWEEN '90-JAN-01' AND '90-DEC-31'

AND (VILLE = ‘PARIS’ OR VILLE=‘LYON’)

GROUP BY VILLE, CRU

Annexe 45

(b) Sa traduction par modification de l’arbre algébrique :

=>

B C V

DATE >= '89/01/01' et <='89/12/01'

NB NB

=

=

NV NV

CPV

SUM (QTE)

Ville = 'PARIS ou Ville = 'LYON'

Res

Cru, Sqte

CPV

Ville, Cru, Qte

B C V

DATE >= '89/01/01' et <='89/12/01'

NB NB

=

=

NV NV

SUM (QTE)

Ville = 'PARIS ou Ville = 'LYON'

Res

Cru, Sqte

Ville, Cru, Qte

Annexe 46

Le SGBD doit permettre à l'utilisateur de définir des règles (ou contraintes) sur les données.

Ces contraintes d’intégrité sont spécifiées lors de la définition du schéma de la BD. Elles

constituent des assertions qui seront vérifiées à chaque modification du contenu de la base.

Toute opération ne respectant pas une contrainte est rejetée. Les contraintes sont spécifiées soit

lors de la définition d’une table soit par la suite par une commande de modification de schéma.

Selon la norme SQL, chaque contrainte doit être nommée (ce qui permettra de la désigner par

un ordre ALTER TABLE. La contrainte peut être définie sur une seule colonne. Elle suit la

définition de la colonne dans un ordre CREATE TABLE (pas dans un ordre ALTER TABLE).

Elle peut porter sur une ou plusieurs colonnes (contrainte sur table). Elles se placent au même

niveau que les définitions des colonnes dans un ordre CREATE TABLE ou ALTER TABLE.

On distingue les contraintes portant sur un attribut de celles portant sur plusieurs attributs,

dites contrainte sur relation.

Types de contraintes par l’exemple

• Non nullité, ex : l’attribut NV ne peut être nul

• Plages de valeurs, ex : le DEGRÉ est compris entre 10 et 15

• Unicité, ex : l’attribut NV est clé de la relation VINS

• Dépendance fonctionnelle, ex : (CRU, PAYS) -> REGION –rarement implémentée

• Dépendance référentielle, ex : tout vin de la base doit être produit par un producteur de la base

• Condition générale sur la relation, ex: la COMMISSION est au plus 2 fois le SALAIRE

• Contrainte temporelle, ex : Le SALAIRE ne peut pas décroître – gérée à l’aide des déclencheurs (triggers)

• Contrainte avec agrégat, ex : la moyenne des Salaires doit être supérieure à 9000 –rarement implémentée

• Contraintes dynamiques (Voir Trigger, plus loin)

Syntaxe SQL


( <nom d'attribut 1> TYPE [DEFAULT <expression>] [<contrainte sur attribut1>, …]

[,<nom d'attribut 2> TYPE [DEFAULT <expression>] [<contrainte sur attribut2>, …], …]

[<1ère contrainte sur relation> [,<2ème contrainte sur relation>] ...]

)

INTÉGRITÉ SÉMANTIQUE

Annexe 47

où : contrainte sur attribut =

[ CONSTRAINT <nom_contrainte>] { NULL | NOT NULL | UNIQUE |

PRIMARY KEY | CHECK ( <condition sur attribut> ) |

REFERENCES <relation> [(<attribut>)]

[{ON DELETE |ON UPDATE}{NO ACTION |CASCADE |SET DEFAULT|SET NULL}]

[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

[ DEFERRABLE | NOT DEFERRABLE ]

et : contrainte sur relation =

[ CONSTRAINT <nom_contrainte>] { UNIQUE | PRIMARY KEY | CHECK

( <condition sur la table> ) |

FOREIGN KEY (<att> [,...]) REFERENCES <relation> [(<att> [,... ])] [{ON DELETE |ON UPDATE}{NO ACTION |CASCADE |SET DEFAULT|SET NULL}]

[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

[ DEFERRABLE | NOT DEFERRABLE ]

Signification

• Pour une contrainte sur relation :

PRIMARY KEY (colonne1, colonne2,...) indique la clé primaire de la table. Les colonnes qui

composent cette clé ne peuvent être NULL.

• Pour une contrainte sur une colonne : PRIMARY KEY

• Pour une contrainte sur une table :

UNIQUE (colonne1, colonne2,...)

• Pour une contrainte sur une colonne :

UNIQUE interdit qu’une colonne (ou la concaténation de plusieurs colonnes) contienne deux

valeurs identiques. Il peut y avoir des NULL auquel cas elles ne sont pas concernées par le test

d’unicité.

• Pour une contrainte sur une table :

FOREIGN KEY (colonne1, colonne2,...)

REFERENCES tableref [(col1, col2,...)]

[ {ON DELETE | ON UPDATE} {NO ACTION | CASCADE | SET DEFAULT | SET

NULL}]

• Pour une contrainte sur une colonne :

REFERENCES tableref [(col1 )]

… idem …

Annexe 48

indique que la concaténation de colonne1, colonne2,... (ou la colonne que l’on définit pour une

contrainte sur une colonne) est une clé étrangère qui fait référence à la concaténation des

colonnes col1, col2,... de la table tableref (contrainte d’intégrité référentielle). Si aucune

colonne de tableref n’est indiquée, c’est la clé primaire de tableref qui est prise par défaut.

Cette contrainte ne permettra pas d’insérer une ligne de la table si la table tableref ne contient

aucune ligne dont la concaténation des valeurs de col1, col2,... est égale à la concaténation des

valeurs de colonne1, colonne2,...

col1, col2,... doivent avoir la contrainte PRIMARY KEY ou UNIQUE. Ceci implique qu’une

valeur de colonne1, colonne2,... va référencer une et une seule ligne de tableref.

L’option ON DELETE (ou ON UPDATE) CASCADE indique que la suppression d’une ligne

de tableref (ou la modification de la clé) va entraîner automatiquement la suppression (ou la

répercussion de la modification) des lignes qui la référencent dans la table. Hormis

CASCADE, les autres options sont SET NULL (ou set DEFAULT) qui met à NULL (ou à la

valeur par défaut) la clé étrangère, NO ACTION qui est l’option par défaut qui empêche de

supprimer (ou modifier) des lignes de tableref qui seraient référencées par des lignes de la

table et génère une erreur.

CHECK(condition ) donne une condition que la ou les colonnes devront vérifier. On peut

ainsi indiquer des contraintes d’intégrité de domaines. D’après la norme, en contrainte de

colonne, la condition ne peut porter que sur une colonne. Les contraintes multi-colonnes sont

définies au niveau de la table.

Des contraintes d’intégrité peuvent être ajoutées ou supprimées par la commande ALTER

TABLE (exemple à la fin de cette section). Mais pour modifier une contrainte, il faut la

supprimer et ajouter ensuite la contrainte modifiée.

Activation d’une contrainte :

Une contrainte est par défaut activée (INITIALLY IMMEDIATE), c’est à dire que la

vérification est faite immédiatement à la requête de mise à jour. L’option INITIALLY

DEFERRED permet de différer la vérification à la fin de la transaction (ensemble de requêtes).

Il est par défaut possible de changer de mode d’activation en DEFERRABLE, à moins qu’elle

soit définie NOT DEFERRABLE.

Pour changer le mode d’activation la commande SQL est la suivante :

SET COSNTRAINTS [ALL | telle_contrainte [,…]] { IMMEDIATE | DEFERRED }

Exemple :

CREATE TABLE EMP

( EMPNO NUMERIC NOT NULL PRIMARY KEY,

ENAME CHAR (10) CHECK (ENAME = UPPER (ENAME)),

Annexe 49

JOB CHAR (9),

MGR NUMERIC REFERENCES EMP(EMPNO),

HIREDATE DATE CHECK (HIREDATE >= SYSDATE),

SAL NUMERIC(10,2) CHECK (SAL >= 500),

COMM NUMERIC(9,0) DEFAULT NULL,

DEPTNO NUMERIC(2) NOT NULL REFERENCES DEPT(DEPTNO) )

L’exemple précédent ne contient pas de contrainte sur la relation. Le suivant montre

l'utilisation d'une contrainte de relation nommée. Il montre aussi qu'on peut rajouter ou

modifier une contrainte par la requête ALTER.

ALTER TABLE EMP

ADD ( PHONE_AREA_CODE CHAR(3) NOT NULL ,

PHONE_NUMBER CHAR(8) NOT NULL ,

UNIQUE (PHONE_AREA_CODE, PHONE_NUMBER) CONSTRAINT contrainte_tel )

Certaines contraintes portent sur plusieurs colonnes et ne peuvent être indiquées que comme

contraintes de table :

CREATE TABLE PARTICIPATION ( MATR NUMBER(5) CONSTRAINT R—EMP REFERENCES EMP,

CODEP VARCHAR2(10) CONSTRAINT R—PROJET REFERENCES PROJET, …,

CONSTRAINT PKPART PRIMARY KEY(MATR, CODEP))

Avec ALTER TABLE on peut ajouter, enlever ou modifier des contraintes de colonnes ou de

tables :

ALTER TABLE EMP

DROP CONSTRAINT NOM—UNIQUE

ADD (CONSTRAINT SAL—MIN CHECK(SAL + NVL(COMM,0) >= 5000))

ALTER TABLE EMP

MODIFY NOME CONSTRAINT NOM—UNIQUE UNIQUE

Trigger - contrainte dynamique - déclencheur ou démon

Permet le déclenchement automatique, d'une ou plusieurs actions sur la base, suite à un événement

Annexe 50

Références bibliographiques en bases de données

C. Date “An introduction to database systems”, Addison Wesley, 1986 (rééditions multiples)

P. Delmal, « SQL2-SQL3, Applications à Oracle », Collection Bibliothèque des Universités,

De Boeck Université, ISBN 2-8041-3561-6, 2000.

G. Gardarin « Bases de données – édition de poche », Eyrolles, 2003 (site :

www.gardarin.org)

G. Gardarin « Bases de données objets et relationnelles », Eyrolles, 2001 (site :

www.gardarin.org)

J. Ullman “Principles of database systems”, Computer Science Press, 1982

R. Elmasri, S. Navathe “Fundamentals of database systems”, The Benjamin Cummings

Publishing Compagny, 1989

concepts de bases des bases de données...

Documents