Page de garde - Université Larbi Ben M'hidi O.E.B BD.pdfde gestion de fichiers, il y avait deux types de fichiers : 1. Fichier des données : représentent des séquences d’enregistrements

Dr. Chergui Leila

Cours de bases de données

2ème Année LMD informatique Université L’arbi Ben M’hidi - Oum El-Bouaghi - Algérie

Chapitre 1 : Introduction générale aux bases de données……………..….1

Chapitre 2 : Le modèle relationnel……….……………........................….13

Chapitre 3 : Le calcul relationnel………………………………..………..34

Chapitre 4 : SQL : Interrogation d’une base de données………….…….41

Chapitre 5 : SQL : Définition et modification d’une base de données….48

Sommaire

Chapitre 1: Introduction générale

aux bases de données

Chapitre 1 : Introduction générale aux bases de données

Dr. Chergui Leila 2

Introduction Auparavant, les données sont stockées sous forme de fichiers et ils sont gérées par un système

de gestion de fichiers, il y avait deux types de fichiers :

1. Fichier des données : représentent des séquences d’enregistrements dont l’accès est

séquentiel ou indexé.

2. Fichier de traitement : représentent un ensemble d’instructions servant à manipuler les

données des fichiers (assembleur, Cobol).

Mais cette approche a souffert des problèmes suivants :

3. Redondance d’information (Mise A Jours (MAJ) difficiles).

4. Problème d’incohérence, de fiabilité et de sécurité.

5. Manque de structuration des données.

6. Si un changement est effectué sur la structure d’un fichier de données tous les

programmes qui l’utilisent doivent être modifiés et on va perdre du temps pour la

maintenance.

Solution : regrouper les fichiers de données en une seule entité= BD, dont les données et les

traitements sont indépendants.

1.1. Concepts de base Dans ce qui suit on va expliquer le principe de quelques concepts de base.

1.1.1. Base de Données (BD)

Une BD est faite pour enregistrer des faits, des opérations au sein d'un organisme

(administration, banque, université, hôpital, ...), c’est un :

Ensemble structuré de données.

Enregistré sur des mémoires secondaires (disque dur).

Créé et tenu à jour pour les besoins d’un ensemble d’utilisateurs ou de programmes.

1.1.2. Objectifs d’un Système de Gestion de Base de Données

Un Système de Gestion de Bases de Données (SGBD) (en anglais DBMS pour Database

Management System) est un système qui permet de créer et gérer une BD partagée par

plusieurs utilisateurs simultanément.

Il représente un ensemble de services (applications logicielles) permettant de :


Dr. Chergui Leila 3

Définir la structure d’une BD (de décrire les données et les liens entre elles d’une

façon logique sans se soucier du comment cela va se faire physiquement dans les

fichiers) par un Langage de Description de Données (LDD).

Rechercher des données dans une BD (Langage de Manipulation des Données :

LMD).

Insérer, modifier, supprimer des données dans une BD (LMD).

Assurer la cohérence et la confidentialité des données (LMD) et la non-redondance

des données.

Assurer la sécurité.

Assurer la cohérence : les données sont soumises à certains nombre de contraintes

d’intégrité qui définissent un état cohérent de la base. Ces contraintes sont décrites

dans le langage de description de données (LDD). Ex : l’âge des employés ne peut pas

être supérieur à 55. Si un utilisateur ajoute une entité employé, le SGBD doit vérifier

l’attribut âge, s’il ne respecte pas cette contrainte, il est rejeté. Une contrainte

d’intégrité est une propriété que les objets décrits par le schéma (entité, association,

attribut) doivent respecter de manière à représenter le problème.

Assurer la confidentialité : les données doivent pouvoir être protégées contre les accès

non-autorisés. Pour cela, il faut associer à chaque utilisateur des droits d’accès aux

données.

Assurer la sécurité après panne : on peut tomber dans des situations où certains

fichiers ne sont plus lisibles ou une panne au milieu d’une MAJ. Le SGBD doit

assurer la reprise après panne. Il existe plusieurs méthodes : récupérer les données

avant la modification, terminer l’opération interrompue, ou utiliser la journalisation

qui consiste à mémoriser les états successifs de la BD.

Permettre le partage des données : permettre à plusieurs utilisateurs d’accéder aux

mêmes données au même moment de manière transparente, en contrôlant les accès

concurrents. Ou on parle des transactions. Une transaction est une opération unitaire

qui transforme le contenu de la BD d’un état A vers un état B.

Assurer une efficacité d’accès : en terme du temps de réponse et de débit global ainsi

que des modes d’accès simples (hachage, arbre blanchi). Le temps de réponse est le

temps d’attente moyen pour une requête. Le débit global représente le nombre de

transactions exécutées par second.


Dr. Chergui Leila 4

1.1.3. Système d’Information (SI)

Il permet de gérer les informations nécessaires au bon fonctionnement d’une entreprise. Les

BDs sont au cœur d’un SI. La différence entre un SGBD et un SI est indiquée dans la figure

1.1.

Figure 1.1. Différence entre un SGBD et un SI.

La plupart des SGBDs fonctionnent selon un mode client/serveur, le serveur (la machine

stockant les données) reçoit des requêtes de plusieurs clients et ceci de manière concurrente.

Le serveur analyse la requête, le traite et retourne le résultat au client.

1.1.4. Domaines d’application d’un SGBD

Les SGBD sont des logiciels complexes et stratégiques, utilisés dans de très nombreuses

applications informatiques, parmi lesquelles : 1. Gestion d’entreprise (stock, personnel, client).

2. Banque (comptes, emprunte).

3. Système de réservation (avion, train).

4. Bibliothèque, vidéothèque.

5. Bureautique.

6. Géographie (carte routière : GPS).

7. Informatique (programmation, documentation).

8. Le E-commerce.

9. Les dossiers médicaux.

10. Les blogs et les wikis.


Dr. Chergui Leila 5

Remarques

Un blog est un type de site web (ou une partie d'un site web) utilisé pour

la publication périodique et régulière de nouveaux articles, généralement succincts, et

rendant compte d'une actualité autour d'un sujet donné ou d'une profession.

Un wiki est un site web dont les pages sont modifiables par les visiteurs afin de

permettre l'écriture et l'illustration collaboratives des documents numériques qu'il

contient. Il utilise un langage de balisage et son contenu est modifiable au moyen

d’un navigateur web.

1.1.5. Niveaux d'abstraction

On peut distinguer trois niveaux d’abstraction comme il est illustré dans la figure 1.2 :

Niveau externe : vue partielle des données suivant l'utilisateur (utilisateur final ou

programmeur d’application). Ex : l’utilisateur n° 1 est chargé de paye, donc il voit que

les informations employé. Le programmeur n° 1 est chargé de faire les facturations,

donc il s’intéresse aux tables client et commande. Il existe plusieurs schémas

externes.

Niveau conceptuel : vue globale de l'organisation des données, c’est la définition

logique de la BD (représentation) via le modèle de données, elle est faite par

l’administrateur de la BD qui est chargé d’identifier et décrire les regroupements

de données et leurs interactions. Ex : nom, prénom, adresse, etc. Il existe un seul

schéma logique.

Niveau interne : organisation physique des données qui concerne le type de stockage

et les modes d’accès. Ce niveau est réalisé par le SGBD. Ex : nom : 20 octets,

prénom : 20 octets, adresse : 40 octets, salaire : 4 octets.

Figure 1.2. Niveaux d’abstraction dans un SGBD.


Dr. Chergui Leila 6

1.1.6. Indépendance de données

L'architecture à trois niveaux définit ci-dessus permet de garantir l'indépendance des données

par rapport aux programmes : elle permet de modifier le schéma de la base de données à un

niveau sans restructurer les autres.

On trouve deux types d’indépendance :

1. L’indépendance physique : est la possibilité de changer le schéma physique et de

modifier l'organisation physique des fichiers, de rajouter ou supprimer des méthodes

d'accès sans remettre en cause le schéma conceptuel.

2. L’indépendance logique : est la possibilité de modifier le niveau conceptuel sans

changer le schéma externe.

1.1.7. Les modèles logiques

1. Le modèle hiérarchique : ou arbre, il lie les enregistrements dans une structure

arborescente où chaque enregistrement n’a qu’un seul possesseur. Pour chaque nœud

donné, un seul nœud père. Ex : ADABASE (1970), IMS (1966), System 2000 (1967).

2. Le modèle réseau : ou graphe, c’est un modèle hiérarchique, mais permet en plus

d’établir des relations transverses. Ex : TOTAL (1978), IDMS (1978), IDMS2 (1978),

SOCRATE.

3. Le modèle relationnel : il stocke les informations décomposées et organisées dans des

tables. Ex : 80% des SGBD sont relationnelles, ORACLE (85% du marché), DB2,

SQL Server, My SQL (libre), Postgre SQL (libre), ACCESS, PARADOX , DBASEV,

INGRES, INFORMIX, RDB.

4. Le modèle orienté objet : il stocke les informations groupées sous forme de collections

d’objets persistants dans des classes. Ex : Versan, Object store, O2, ONTOS, ORION.

5. Le modèle XML : il s’appuie sur le modèle de données fourni par XML. Ex :

Academic search.

Des exemples de modèle hiérarchique et modèle réseau sont affichés dans la figure 1.3.

Figure 1.3. Exemples de modèles logiques.


Dr. Chergui Leila 7

Remarques

1. Donnée exhaustive : la base contient toutes les informations requises pour le service

que l’on en attend, il n’ya pas de données manquantes.

2. Donnée persistent : elle doit survivre à la fin d’une application particulière sur la BD

pour qu’elle puisse être réutilisée plus tard.

3. Interopérabilité : BD accessibles par différents systèmes.

4. Hétérogénéité : BD fondée sur des modèles distincts.

5. Le catalogue système ou dictionnaire de données : il contient toutes les méta-données

utiles au système. Les méta-données sont les représentations permettant la

description :

Des données (type, taille, valeur autorisée, etc.).

Des autorisations d’accès.

Des vues et autres éléments système.

Le catalogue renferme encor la description des différents schémas des trois niveaux

ainsi que les règles de passage d’un schéma vers l’autre ;

1.2. Modélisation Entité/Association Entité/Association (EA) en français, ER en anglais (pour Entity Relationship) décrit l'aspect

conceptuel des données à l’aide d’entités et d’associations. Le passage du monde réel vers le

modèle logique est montré dans la figure 1.4.

Figure 1.4. Transformation de monde réel vers le modèle logique.

Exemple : un client commande un article.

Monde réel

Modèle conceptuel

Modèle logique

MERISE

E/A

Hiérarchique

Relationnel

Réseau


Dr. Chergui Leila 8

Figure 1.5. Exemple de modélisation E/A.

A partir de la figure 1.5, chaque client réel est une occurrence de l’entité client.

Le client nommé Omar est une instance ou occurrence de l’entité Client.

Plusieurs composantes apparaissent dans ce modèle. Dans le paragraphe suivant, on va

expliquer le principe de chacune d’elles.

Entité : représentation d’un objet matériel ou immatériel. La figure 1.6 montre la

représentation schématique d’une entité.

Par exemple un employé, un projet, un bulletin de paie, etc.

Propriétés : données élémentaires relatives à une entité.

Par exemple, un numéro d’employé, une date de début de projet, etc.

Figure 1.6. Modélisation d’une entité.

Identifiant : propriété ou groupe de propriétés qui sert à identifier une entité. L’identifiant

d’une entité est choisi par l’analyste de façon à ce que deux occurrences de cette entité ne

puissent pas avoir le même identifiant.

Par exemple, le numéro de client sera l’identifiant de l’entité Client.

Associations : représentation d’un lien entre deux entités ou plus, une association peut avoir

des propriétés particulières (date d’empreint dans la figure 1.7). Schématiquement,

l’association prend la forme d’une ellipse.

Figure 1.7. Exemple d’une association.

0,n 1,n

Client

Numcli

Nom Commander

IDcom+IDadr Date, Qte

Article

Numarti

Désignation

Prix


Dr. Chergui Leila 9

Une relation est binaire, si elle relie deux entités. Elle est ternaire si elle relie trois entités, n-

aire, si elle relie un nombre d’entités supérieur à 2.

Association cyclique (récursive) : elle lie une entité à elle même (cycle). On doit donc

spécifier les rôles de l’association. Un rôle représente le rôle d’une entité dans une

association. Un exemple est indiqué dans la figure 1.8.

Figure 1.8. Exemple d’une association récursive.

Cardinalités : la cardinalité d’une association pour une entité constituante est composée

d’une borne minimale et d’une borne maximale :

Minimale : nombre minimum de fois qu’une occurrence de l’entité participe aux

occurrences de l’association, généralement 0 ou 1.

Maximale : nombre maximum de fois qu’une occurrence de l’entité participe aux

occurrences de l’association, généralement 1 ou n.

Figure 1.9. Association des cardinalités.

Dans la figure 1.9, la cardinalité 0,3 indique qu’un adhérent peut être associé à 0, 1, 2 ou 3

livres, c’est-à-dire qu’il peut emprunter au maximum 3 livres.

A l’inverse un livre peut être emprunté par un seul adhérent, ou peut ne pas être emprunté.

Remarques

Les cardinalités maximum sont nécessaires pour concevoir le schéma de la base de

données.

Les cardinalités minimums sont nécessaires pour exprimer les contraintes d’intégrité.


Dr. Chergui Leila 10

En notant uniquement les cardinalités maximum, on distingue 3 types de liens :

Lien fonctionnel 1:n (figure 1.10). Une instance de A ne peut être associée

qu’à une seule instance de B. Dans l’exemple de la figure un employé ne peut

travailler que dans un seul département.

Lien hiérarchique n:1. Dans la figure 1.11, une instance de A peut être associée

à plusieurs instances de B. Ici, un département emploie généralement plusieurs

employés.

Lien maillé n:m. Comme il est montré dans la figure 1.12, une instance de A

peut être associée à plusieurs instances de B et inversement. L’exemple de

cette figure indique qu’un employé peut participer à plusieurs projets.

Figure 1.10. Exemple de liens fonctionnels.

Figure 1.11. Exemple de liens hiérarchiques.

Figure 1.12. Exemple de liens maillés.

n 1 A

B

n 1 Employé

Départ

Travaille

1 n A

B

1 n Départ

Employé

Emploie

m n A

B

m n Employé

Projet

Participe



Un exemple de diagramme E/A est présenté dans la figure 1.13.

Figure 1.13. Exemple d’un diagramme E/A.

Chapitre 2: Le modèle relationnel

Chapitre 2 : Le modèle relationnel


Introduction Le modèle relationnel de données été défini en 1970 par le Edgar Frank Codd chercheur

britannique pour surpasser les inconvénients des modèles hiérarchiques et réseaux (incapacité

de gérer des BDs volumineuses, incapacité de traiter les redondances de données, la non-

garantie de l’intégrité de données, etc.). Les premiers SGBDR commerciaux font leur

apparition dans les années 80, avec des outils comme ORACLE principalement.

Le modèle relationnel est simple, facile à comprendre même pour les non-spécialistes et

repose sur des solides bases théoriques notamment la théorie des ensembles et la logique des

prédicats du premier ordre qui permettent notamment de conduire à une amélioration des

performances des outils.

Les objectifs du modèle relationnel sont :

- Proposer des schémas de données faciles à utiliser.

- Améliorer l’indépendance logique et physique.

- Mettre à la disposition des utilisateurs des langages de haut niveau.

- Optimiser les accès à la base de données.

- Améliorer l’intégrité et la confidentialité.

- Fournir une approche méthodologique dans la construction des schémas.

2.1. Concepts de base Dans ce qui suit, on va introduire quelques concepts de base concernant le modèle relationnel.

2.1.1. Modèle relationnel

Dans ce modèle, les objets et les associations sont représentés par un concept unique ; la

relation. Les relations sont des tableaux à deux dimensions souvent appelées Tables dont les

lignes sont appelées n-uplet ou tuples en anglais.

Les données sont manipulées par des opérateurs de l’algèbre relationnelle et l’état cohérent de

la base est défini par un ensemble de contraintes d’intégrité.

Exemple 1:

ETUDIANT

N°Etud Nom Prénom Age

10

20

100

Taleb

Badaoui

Naser

Omar

Mounir

Fateh

20

21

19



Cet exemple représente une relation décrivant les étudiants. Le nom de la relation est

ETUDIANT. Les entités dans les colonnes représentent les attributs qui sont : (N°Etud, Nom,

Prénom, Age).

Chaque ligne de la table correspond à une occurrence ou un tuple, par exemple : <100, Naser,

Fateh, 19>.

Exemple 2 : la table OUVRAGES décrit un ouvrage.

Côte Titre Editeur Année NbExemplaire Thème

12TA1 Réseaux

informatiques

Eyrolles 1998 10 Réseaux,

Internet

13GO1 Algorithmes

génétiques

Addison

Wesley

1994 5 Informatique

évolutionnaire

15TA2 Système

d’exploitation

Eyrolles 1993 6 UNIX, SE

Une table est composée d’un ensemble d’attributs et d’un ensemble de tuples.

Un attribut est le nom donné à une colonne d’un tableau représentant une relation.

2.1.2. Domaine

Un domaine est un ensemble de valeurs finies ou infinies que peut prendre un attribut. Ex :

entier, chaine de caractères, réels, etc.

Exemples :

Le domaine des booléen : Db={0,1}.

Le domaine des couleurs primaires : Dc= {jaune, rouge, bleu}

Le domaine des prénoms : chaine de caractères.

Le domaine des âges : entier entre 17 et 30.

2.1.3. Relation

Une relation est un ensemble de tuples, déterminée par un nom. Une relation peut être

exprimée en deux formats :

1. Relation en intension (schéma de relation) : ici, on représente la relation par son

nom suivi de l’ensemble de ses attributs et de leurs domaines d’application. Parmi ces

attributs, un sous-ensemble (un ou plusieurs attributs) constitue la clé de la relation et

sera soulignée dans le schéma.



Exemple 1 : ETUDIANT (N°Etud : entier, Nom : CC, Prénom : CC, Age : entier).

Exemple 2 : OUVRAGES (Côte : texte, Titre: Texte, Editeur: Texte, NbExemplaire:

Numérique, Année:Date, Thème:Texte).

Pour alléger l’écriture, le schéma de relation se limite souvent au nom de la relation

suivi de ses attributs.

Exemple 1 : ETUDIANT (N°Etud, Nom, Prénom, Age).

2. Relation en extension : quand la relation est exprimée en intension, ses tuples ne sont

pas visibles. Par contre, la présentation de la relation en extension permet de les lister.

La relation en extension est représentée sous forme de tableau. Les lignes de la table

sont les tuples.

Le degré d’une relation désigne son nombre d’attributs. Ex : le degré de la relation

ETUDIANT est 4 et le degré de la relation OUVRAGE est 6.

La cardinalité d’une relation représente le nombre de ses tuples.

L’ordre des lignes et de colonnes n’a pas d’importance car il n’y a pas d’ordre dans un

ensemble.

Exemple 3 : donner le degré et la cardinalité de la relation suivante ainsi que le domaine de

chaque attribut.

Id Nom Prénom Département Salaire

142046 Chirifi Nabil Comptabilité 55000

142100 Hadad Salim Marketing 33500

142190 Ferah Omar Ressources humaines 42000

Le domaine des salaires : réels positifs.

A chaque fois une relation est représentée au moyen d’une table, les conditions suivantes

doivent être satisfaites :

La table a un nom unique.

Chaque colonne de la table comporte un nom unique ; c’est-à-dire qu’il n’y a pas deux

colonnes de la même table qui possèdent des noms identiques.

L’ordre des colonnes dans la table est sans importance.

Toutes les lignes de la table ont le même format et le même nombre d’entrées.

Les valeurs de chaque colonne appartiennent au même domaine (chaine de caractères,

entiers, etc.).



Chaque entrée (l’intersection d’une ligne et d’une colonne) de chaque tuple de la

relation doit être une valeur unique. Ceci signifie qu’aucune liste ou ensemble de

valeurs n’y sont permis.

L’ordre des tuples (lignes) est sans importance du fait qu’elles sont identifiées par leur

contenu et non par leur position dans la table.

Il n’ya pas deux tuples identiques dans toutes leurs entrées.

Le nom d’un attribut peut apparaître dans plusieurs schémas de relations.

Tout attribut peut prendre une valeur nulle excepté les attributs de la clé primaire.

Il n’y a pas de « case vide » dans la table, donc toutes les valeurs de tous les attributs

sont toujours connues.

Exemple 4 :

Considérant la relation DEPT et les lignes montrées à la suite. Expliquez si ces lignes peuvent

être ou non insérées dans la relation DEPT.

N°DEPT Nom Lieu Budget

20 Ventes Constantine 1 700 000

10 Marketing Alger 2 500 000

10 Recherche Alger 1 500 000

Comptabilité Oran 1 200 000

15 Informatique Constantine 1 500 000

1. La première ligne : non, elle viole la propriété d’unicité de la clé car le département

N° 10 existe déjà.

2. La 2ème ligne : non, la clé ne peut pas être nulle.

3. 3ème ligne : oui.

Remarques

Du fait que la clé primaire identifie de manière unique les tuples d’une relation,

aucune de ses attributs ne doit être nulle.

Dans une relation, une valeur nulle représente des informations manquantes,

inconnues ou des données inapplicables. Donc, la valeur NULL n’est pas une valeur

zéro et elle ne représente pas une valeur particulière pour l’ordinateur.



2.1.4. Clé d’une relation

C’est un attribut ou une composition minimale d’attributs dont chacune des valeurs permet de

déterminer, d’une manière unique, un tuple de la relation. Ainsi, la valeur d’une clé ne peut

exister qu’une seule fois dans la relation en extension. La clé de la relation, qu’elle soit décrite

en intension ou extension, sera soulignée.

Dans une relation, il peut y avoir plusieurs attributs ou compositions d’attributs qui permettent

de déterminer, d’une manière unique, un tuple. Ces attributs forment des clés candidats, mais

la relation doit avoir une seule clé primaire. Le choix est fait selon les traitements appliqués à

la relation.

Exemple 5 :

1. ETUDIANT (N°Etud, Nom, Prénom, Age). Il y a une seule clé candidat une seule

clé primaire.

2. TRAVAILLEUR (N°Empl, N°poste, Designation de poste de travail), ici on a deux

clés candidat : N°Empl, N°poste. On doit choisir une clé primaire.

2.1.5. Clé étrangère

C’est un attribut d’une relation qui fait référence à un attribut clé primaire d’une autre

relation. C’est un attribut qui sert à faire le lien entre deux relations d’une même BD.

Exemple 6 :

2.1.6. Contraintes d’intégrité

Trois types de contraintes d’intégrité sont obligatoires :

1. Contrainte de clé : une relation doit posséder une clé primaire.

2. Contrainte d’entité : un attribut d’une clé ne doit pas posséder de valeurs nulles

(vides).

3. Contrainte de référence (pour les clés étrangères).

Exemple 7 : on souhaite poser les contraintes suivantes :

1. Le nombre d’exemplaire de chaque OUVRAGE doit être supérieur à 0 (zéro).



2. Chaque OUVRAGE doit avoir au moins un auteur.

2.1.7. Base de données relationnelle

C’est un ensemble exhaustif et cohérent de schémas de relations.

Exemple 8 : la BD relationnelle des produits d’une entreprise industrielle pourra compter les

schémas de relations suivants :

- PRODUIT (N°Prod, Libellé, Datfabric, Prix).

- Article (N°Art, Libellé, Quantstock).

- NOMENCLATURE (N°Prod, N°Art, Qte).

Le schéma relationnel ETUDIANT (N°Etud, Nom, Prénom, Age). N’y figurera pas. Elle ne

fait pas partie du domaine d’étude.

Exemple 9 : le schéma de la base de données permettant la gestion de notices

bibliographiques est :

- AUTEURS (NumAuteur, Nom, Prénom).

- OUVRAGES (Côte, Titre, NbExemplaire, Année, NumEditeur, Thème).

Contrainte de domaine : NbExemplaire >0.

Contraintes référentielles :

OUVRAGES.NumEditeur REFERENCE EDITEURS.NumEditeur

- ECRIT (NumAuteur, Côte)

Clé primaire : NumAuteur, cote.

Contraintes référentielles :

ECRIT.NumAuteur REFERENCE AUTEURS.NumAuteur

ECRIT.Côte REFERENCE OUVRAGES.Côte

2.2. Opérateurs de l’algèbre relationnelle C’est l’ensemble des opérations qui peuvent être appliquées aux relations pour obtenir de

nouvelles opérations résultantes. Ces opérations permettront de répondre à des besoins précis

des utilisateurs.

2.2.1. Opérateurs unaires

Ils manipulent des relations de même schémas.

Projection : la projection d’une relation R consiste à créer une nouvelle relation, à partir de R

mais en ne conservant que les attributs cités en opérande.

Elle consiste à :

1. Supprimer, d’une relation, les attributs non-mentionnés en opérande.

2. Et à éliminer les tuples, en doublon, qui risque d’apparaître dans la nouvelle table.



D’une manière formelle, soit un schéma de relation R(A1, A2, …,An) avec ∀푖휖퐸(푒푛푡푖푒푟) ,

Ai étant un attribut dont les valeurs appartiennent à un domaine Di. La projection R’ de R sur

A1, A2 s’écrira : R’= proj(R,A1,A2)=∏퐴1,퐴2(푅).

La modélisation graphique est :

Exemple 1 : considérant la relation CLIENT (N°CLI, Nom, Prenom, Datnais).

On représente la relation en extension :

CLIENT

Requête : on voudrait connaître que les noms et prénoms des clients.

PCLIENT1=proj(CLIENT, Nom, Prenom)=∏푁표푚,푃푟푒푛표푚(퐶퐿퐼퐸푁푇).

La représentation graphique est :

Donc, la relation en intension est : PCLIENT1 (Nom, Prenom).

En extension : PCLIENT1

N°CLI Nom Prenom Datnais

101 Taleb Omar 14/03/1959

102 Mansouri Nabil 13/03/1946

230 Badaoui Khaled 14/05/1978

104 Salem Rida 11/12/1985

214 Ayad Fateh 11/11/1975

311 Ferah Nabil 15/04/1968

Nom Prenom

Taleb Omar

Mansouri Nabil

Badaoui Khaled

Salem Rida

Ayad Fateh

Ferah Nabil

CLIENT

PCLIENT1

Nom, Prenom

R

R’

A1, A2



La deuxième étape consiste à supprimer les doublons, pour cette relation on n’a pas.



CLIENT

Requête : on voudrait connaître que les noms et prénoms des clients.

PCLIENT2 en extension :

PCLIENT2

Après la suppression des doublons, on aura :

PCLIENT2

Sélection (restriction) : elle consiste à créer une relation à partir d’une autre, en ne gardant

que les tuples pour lesquels un attribut vérifie une certaine propriété.

La sélection est l’opération qui consiste, à partir d’une relation R (A1, A2,…, An), à créer une

nouvelle relation R’(A1, A2,…,An) dont tous les tuples vérifient une propriété d’un attribut

Ai.

On notera : R’=휎퐴푖 < 표푝é푟푎푡푒푢푟 >< 푣푎푙푒푢푟 > (푅)∀푖 ∈ 퐸.


101 Taleb Omar 14/03/1959



104 Salem Rida 11/12/1985

214 Ayad Fateh 11/11/1975

311 Ferah Nabil 15/04/1968

Nom Prenom

Taleb Omar

Mansouri Nabil

Mansouri Nabil

Salem Rida

Ayad Fateh

Ferah Nabil

Nom Prenom

Taleb Omar

Mansouri Nabil

Salem Rida

Ayad Fateh

Ferah Nabil



Ou R’=Restrict (R,Ai<opérateur><valeur>).

Ou R’=R[Ai<opérateur><valeur>].

L’opérateur appartient à l’ensemble {=,<, >,≤, ≥, ≠}.

La valeur appartient au domaine de l’attribut Ai. La modélisation graphique est :



CLIENT

Requête : on cherche les clients qui ont 35 ans ou plus au 1/01/2008.

La condition se traduit mathématiquement : Datnais≤01/01/1973, il faut que tous les tuples

vérifient cette condition et il faut conserver toutes les informations (attribut) concernant un

client.

SCLIENT1=휎퐷푎푡푛푎푖푠 ≤ ′01/01/1973′(퐶퐿퐼퐸푁푇)

= CLIENT [Datnais ≤’01/01/1973’]=

=Restrict(CLIENT, Datnais ≤’01/01/1973’).

La représentation graphique :


101 Taleb Omar 14/03/1959


230 Badaoui Khaled 14/05/1978

104 Salem Rida 11/12/1985

214 Ayad Fateh 11/11/1975

311 Ferah Nabil 15/04/1968

Ai <opérateur> <val>

R

R’

Datnais≤’01/01/1973’

CLIENT

SCLIENT1



La relation en intension s’écrira SCLIENT1 (N°CLI, Nom, Prenom, Datnais). Le schéma de

la relation résultante reste identique à celui de la relation originale : même clé primaire, même

attributs. En extension, elle sera :

SCLIENT1

Requête 2 : à partir de la table CLIENT, quels sont les clients ayant un prénom : Ahmed.

SCLIENT2= 휎푃푟푒푛표푚 = ′Ahmed′(퐶퐿퐼퐸푁푇)

= CLIENT [Prenom=’Ahmed’]=

=Restrict(CLIENT, Prenom=’Ahmed’)=Ф.

2.2.2. Opérateurs binaires

Ces opérateurs vont permettre, à partir de deux relations, d’en construire une troisième. La

totalité des attributs de chacune des relations est conservée.

Intersection : l’intersection de deux relations R1 et R2 est une nouvelle relation R dont les

tuples appartiennent à R1 et R2. Les trois relations R1, R2 et R3 ont le même schéma. On

notera :푅 = 푅1 ∩ 푅2.

La modélisation graphique de l’intersection est :

Exemple 1 : supposant que deux bibliothèques B1 et B2 fusionnent et décident de rechercher

les livres qu’elles ont en commun pour n’en garder qu’un exemplaire. Chacune des

bibliothèques possède une relation LIVREB1 et LIVREB2 :

LIVREB1 (NLIV, TITRE, NOMAUT).

LIVREB2 (NLIV, TITRE, NOMAUT, PRENAUT).

On ne peut pas faire l’union car les deux relations n’ont pas le même schéma.

Exemple 2 : si maintenant on a :



101 Taleb Omar 14/03/1959


311 Ferah Nabil 15/04/1968




Et on veut répondre à la même question. Considérant LIVREB1 et LIVREB2 en extension :

LIVREB1

LIVREB2

INTERLIVRE

Union : l’union de deux relations R1 et R2 est une nouvelle relation R dont les tuples

appartiennent à R1 ou à R2 ou appartiennent à R1 et R2. Les trois relations ; R1, R2 et R3 ont

le même schéma. On notera : 푅 = 푅1 ∪ 푅2.

La modélisation graphique de l’union est :

Exemple1 : concernant les deux relations :


LIVREB2 (NLIV, TITRE, NOMAUT, PRENAUT).

On ne peut pas faire l’union car les deux relations n’ont pas le même schéma.

Exemple 2 : si maintenant on a :



Les deux bibliothèques décident de regrouper tous leurs livres.

UNIONLIVRE= LIVREB1 ∪ LIVREB2.

Le schéma de cette relation est : UNIONLIVRE (NLIV, TITRE, NOMAUT).

NLIV TITRE NOMAUT

101 Les fourmis Bertrand

102 Le soir des fourmis Bertrand

210 La révolte des fourmis Bertrand

104 Les 10 mousquetaires Artagnan

NLIV TITRE NOMAUT

101 La basilique de Paris Victorien


210 Le roi de la forêt Aiglon


NLIV TITRE NOMAUT





Remarque

La relation résultante n’a pas de clé primaire définie.

Considérant LIVREB1 et LIVREB2 en extension :

LIVREB1

LIVREB2

On va visualiser cette union par étapes :

1. La relation UNIONLIVRE aura pour schéma (NLIV, TITRE, NOMAUT) (éliminer

les tuples en doubles).

2. Puis, elle regroupera tous les tuples appartenant à LIVREB1 et LIVREB2. Il n’ya

qu’un tuple qui vérifie cette condition.

UNIONLIVRE

3. On ajoutera les tuples restants de la relation LIVREB1.

4. On ajoutera les tuples restants de la relation LIVREB2.

5. Onrraura :

UNIONLIVRE

NLIV TITRE NOMAUT





NLIV TITRE NOMAUT



217 Le jardin en folie Rahhan

NLIV TITRE NOMAUT


NLIV TITRE NOMAUT









Différence : la différence R1-R2 de deux relations R1 et R2 est une nouvelle relation R dont

les tuples appartiennent à R1 ET n’appartiennent pas à R2. Les trois relations R1, R2 et R3

ont le même schéma. On notera : R=R1-R2. Aucun exemplaire commun n’est conservé.

La modélisation graphique de la différence est la suivant :

Exemple : supposant qu’une grande librairie B1 achète une petite librairie B2 et décide de ne

conserver en vente que les livres de B1 ; mais de plus, s’il y a un livre de B1 qui est référencé

chez B2, il est retiré de la vente. Les relations en intension sont :



La relation résultante DIFLIVRE aura pour schéma : (NLIV, TITRE, NOMAUT) et

regroupera tous les tuples appartenant à LIVREB1 et n’appartenant pas à LIVREB2.

DIFLIVRE= LIVREB1- LIVREB2.

Considérant LIVREB1 et LIVREB2 en extension :

LIVREB1

LIVREB2

Nous allons visualiser cette différence par étapes :

1. La relation résultante DIFLIVRE aura pour schéma (NLIV, TITRE, NOMAUT).

2. Elle contiendra tous les tuples appartenant à LIVREB1.

NLIV TITRE NOMAUT





NLIV TITRE NOMAUT






DIFLIVRE

3. Puis, il faut ensuite éliminer de cet ensemble, les tuples qui appartiendraient aussi à

LIVREB2. C'est-à-dire le tuple < 102, Le soir des fourmis, Bertrand >. Nous

obtenons :

DIFLIVRE

Remarque

푅2− 푅1 ≠ 푅1 − 푅2.

Produit cartésien : le produit cartésien de deux relations R1 et R2 est la relation R, dont :

Le schéma relationnel est constitué de la concaténation des attributs du schéma de R1

et du schéma de R2.

Les tuples sont issus de toutes les combinaisons des tuples de R1 avec les tuples de

R2.

Les deux tables participant au produit cartésien n’ont pas forcément le même schéma.

On notera : R=R1xR2= R1*R2. Graphiquement on aura :

Exemple 1 : LIVRE (NUML,TITRE, NBPAGE).

ANNEXE (NUMA, VIL).

LIVRE ANNEXE

En appliquant le produit cartésien, on aura une nouvelle relation contenant un tuple par ville

possible et par livre possible. R1= LIVRE*ANNEXE.

NLIV TITRE NOMAUT





NLIV TITRE NOMAUT




NUMA VIL

367 CONSTANTINE

420 ALGER

600 ORAN

NUML TITRE NBPAGE

101 Les fourmis 375

102 Le soir des fourmis 453

210 La révolte des fourmis 423

R1

R

R2

x



Son schéma est la concaténation de schéma de LIVRE et de schéma d’ANNEXE.

R1 (NUML, NUMA,TITRE, NBPAGE, VIL).

R1 en extension :

NUML TITRE NBPAGE NUMA VIL

101 Les fourmis 375 367 CONSTANTINE

101 Les fourmis 375 420 ALGER

101 Les fourmis 375 600 ORAN

102 Le soir des fourmis 453 367 CONSTANTINE

102 Le soir des fourmis 453 420 ALGER

102 Le soir des fourmis 453 600 ORAN

210 La révolte des fourmis 423 367 CONSTANTINE

210 La révolte des fourmis 423 420 ALGER

210 La révolte des fourmis 423 600 ORAN

Remarque

La relation représentant la réalité sera généralement un sous-ensemble du produit cartésien.

Jointure : la jointure est dérivée du produit cartésien avec, en plus, une condition permettant

de comparer la valeur d’attributs. Il y aura une étape de concaténation d’attributs provenant

des deux relations puis élimination des tuples ne vérifiant pas la condition de rapprochement.

Ici encor, les deux tables n’ont pas forcément le même schéma.

On trouve différents types ; les jointures internes (la 휃-jointure, l’équi-jointure et la jointure

naturelle), et les jointures externes (la jointure externe entière, la jointure externe gauche et la

jointure externe droite).

La jointure naturelle consiste donc à combiner deux tables ligne à ligne en vérifiant la

concordance entre certaines colonnes des deux tables. Autrement dit, cela permet de relier

deux tables ayant un champ commun et de faire correspondre les lignes qui ont une même

valeur. R= R1 ⋈[ é é]R2.

Elle porte sur des attributs de même nom, même domaine mais appartenant à des relations

distinctes. Aussi, en pratique, nous ferons le plus souvent des jointures naturelles qui

porteront sur une clé primaire et une clé étrangère. La modélisation graphique de la jointure

est :

R1

R

R2



Exemple 1 :

LIVRE (NUML,TITRE, NomAUT, NUAN).

ANNEXE (NUMA, VIL).

Requête : on voudrait conservant que les livres dont le numéro d’annexe existe dans la

relation annexe.

LIVRE

NUML TITRE NomAUT NUAN

101 Les fourmis Bertrand 375

102 Le soir des fourmis Bertrand 375

210 La révolte des fourmis Bertrand 600

ANNEXE

On doit passer par les étapes suivantes :

1. Faire le produit cartésien des deux relations.

2. Eliminer les tuples qui ne vérifient pas la relation de jointure (NUAN=NUAN).

3. Supprimer l’attribut en double.

La relation finale est : R2

NUML TITRE NomAUT NUAN VIL

210 La révolte des fourmis Bertrand 600 ORAN

Division : c’est un opérateur binaire qui s’applique entre deux relations R1 et R2 pour donner

une relation R=푅 ÷ 푅 . Le schéma de la relation R2 doit être une partie du schéma de R1. La

relation R obtenue aura le schéma de R1 moins les attributs de R2. Tout tuple de R sera tel

que : quelque soit le tuple de R2 qui lui sera concaténé, il donnera un tuple de la relation

initiale R1.

Intérêt :

1. Elle permet de rechercher dans une table les sous-tables qui sont complétées par tous

ceux d’une autre table.

2. Elle permet ainsi, de répondre à des requêtes de la forme : quelque soit X trouver Y.

NUMA VIL

367 CONSTANTINE

420 ALGER

600 ORAN



2.2.3. Arbre algébrique

Une expression algébrique peut être représentée sous forme d’arbre dont :

La racine correspond à la requête.

Les nœuds correspondent aux opérateurs algébriques.

Les feuilles correspondent aux relations.

L’objectif est de mieux comprendre l’optimisation logique de requête.

Exemple 1 :

LIVRE (NUML,TITRE, NomAUT, NUAN).

ANNEXE (NUMA, VIL).

Requête : on veut conserver que les titres des livres appartenant à une annexe et le nom de

celle-ci. Donc, on applique une jointure naturelle suivie par une projection.

R3= 휋[ ](퐿퐼푉푅퐸 ⋈[ . . ] 퐴푁푁퐸푋퐸).

L’arbre algébrique est :

La relation R3 en extension est :

TITRE VIL

La révolte des fourmis ORAN

Exemple 2 :



Requête : on cherche les numéros et les titres des livres en commun des deux relations, dont le

numéro de livre est inférieur à 200. Donc, on va faire une intersection suivie d’une projection

suivie d’une sélection.

R4=휎[ ]휋[ , ](퐿퐼푉푅퐸퐵1∩ 퐿퐼푉푅퐸퐵2).

Donner l’arbre algébrique.

LIVRE

LIVRE ANNEXE

ANNEXE

R3

Titre



2.2.4. Fonctions et agrégats

Tous les opérateurs précédents nous ont permis d’extraire les données brutes des tuples ; mais

dans la gestion des BDs, ces informations seront insuffisantes pour répondre aux besoins des

utilisateurs. En effet, nous n’avons pas traité les demandes d’informations de synthèse, telle

que le calcul d’un chiffre d’affaires par département, le cumul des montants des achats d’un

client, etc. C’est pour cela qu’il faut introduire les fonctions de calcul et d’agrégation dans

l’algèbre relationnelle.

Fonction de calcul : il est possible de remplacer, dans les conditions des opérations, un

attribut utilisé en tant qu’argument par une composition des fonctions appliquées sur des

attributs de la relation ou des constantes.

Généralement, ce sont des fonctions arithmétiques qui seront utilisées. Ainsi, il est possible

d’additionner des attributs, d’ajouter une constante à un attribut, etc.

Remarque

Les domaines des valeurs des attributs auxquels sont appliquées les fonctions doivent être

compatibles avec celles-ci. Il ne sera pas possible d’additionner un attribut ville (chaîne de

caractères) à une constante numérique.

Exemple 1 :

Considérant la relation CLIENT (NUMC, NOM, PRENOM, ACHATTOTAL).

En extension :

CLIENT

NUMC NOM PRENOM ACHATTOTAL

101 Khaldi Nabil 802

102 Badaoui Fateh 50

210 Maarouf Samir 354

104 Seradj Omar 1098

214 Fareh Sami 950

310 Taleb Salim 900

Requête : on veut connaître les numéros des clients à qui il suffirait de faire un achat de

100DA pour égaliser ou dépasser un total d’achats de 1000DA.

Il faut extraire les tuples dont le total d’achats effectués+100DA est supérieur à 1000DA.

Arithmétiquement, cela s’écrira ACHATTOTAL+100 ≥1000. Ensuite, on fera la projection.



La relation CLIENT [ACHATTOTAL+100≥1000] en extension :



214 Fareh Sami 950

310 Taleb Salim 900

La relation finale en extension :

NUMC NOM

104 Seradj

214 Fareh

310 Taleb

Fonctions d’agrégat : elles vont permettre de calculer une valeur simple à partir d’un

ensemble de valeurs provenant d’un même attribut mais plusieurs tuples d’une relation.

Ces fonctions pourront s’appliquer à tous les tuples ou à une sélection de tuples d’une

relation. Les fonctions courantes, que l’on retrouvera en SQL, sont les suivantes :

COMPTE : compter les valeurs d’un attribut d’une relation.

SOMME : additionner les valeurs d’un attribut d’une relation.

MOYENNE : effectuer la moyenne des valeurs d’un attribut d’une relation.

MAXIMUM : chercher la valeur maximale d’un attribut d’une relation.

MINIMUM : chercher la valeur minimale d’un attribut d’une relation.

Exemple : considérant la relation CLIENT (NUMC, NOM, PRENOM, ACHATTOTAL).

En extension : CLIENT


101 Khaldi Nabil 802

102 Badaoui Fateh 50

210 Maarouf Samir 354


214 Fareh Sami 950

310 Taleb Salim 900

Requête : on veut connaître le total des achats effectués par tous les clients.



On va faire la somme de toutes les valeurs de l’attribut : ACHATOTAL. Et seul le résultat de

cette fonction nous suffit, c’est-à-dire, les autres attributs doivent être éliminés du résultat.

R=휋[ ( )]퐶퐿퐼퐸푁푇.

Arbre algébrique :

CLIENT

R

Somme (ACHATTOTAL)

Chapitre 3: Le calcul relationnel

Chapitre 3 : Le calcul relationnel


Introduction Il existe une différence entre l’algèbre relationnel et le calcul relationnel :

Algèbre relationnelle : langage procédural permettant d'expliciter une séquence

d'opérations qui conduiront à un résultat désiré. Il est opérationnel. L’algèbre relationnelle

permet de spécifier quelles sont les opérations à exécuter pour calculer le résultat de la

requête.

Calcul relationnel : langage non-procédural (déclaratif) permettant d'expliciter le résultat

que l'on désire sans spécifier la séquence des opérations à effectuer. Il n’est pas

opérationnel.

C’est un langage prédicatif qui permet de ne spécifier que le résultat cherché (pas comment

le calculer). Il s’agit de spécifier des prédicats qui doivent être vérifiés par les données

pour former le résultat. On le qualifie de langage prédicatif car il est basé sur le calcul de

prédicats (logique du 1er ordre).

3.1. Rappel sur le calcul des prédicats Le calcul relationnel est basé sur le calcul des prédicats. Pour cette raison, nous allons

introduire un rappel sur la logique des prédicats.

3.1.1. Syntaxe

L’alphabet du langage est composé des éléments suivants :

Un ensemble de variables notées x, y, z, etc.

Un ensemble de fonctions notées f, g, h ayant chacune une arité (nombre

d’arguments).

Un ensemble de symboles de prédicats notés P, Q, R, etc. ayant chacun une arité.

Des parenthèses : ( ).

Les connecteurs : (non), ∧ (et), ∨ (ou), → (implique).

Les quantificateurs : (il existe) et (quel que soit).

3.1.2. Notification

Terme :

Chaque variable est un terme.

Si t1, t2,…,tn sont des termes et f est un symbole de fonction alors f(t1, t2,…,tn) est

un terme.

Un terme est clos (fermé) s’il ne contient aucune variable.



Atome : il est de la forme P(t1, t2,…,tn) où P est un symbole de prédicat d’arité n et t1, t2,…,tn

sont des termes.

Formule :

Chaque atome est une formule.

Si A est une formule alors A est une formule.

Si A et B sont deux formules alors A∧ 퐵 , A∨B et A→퐵 sont des formules.

Si A est une formule et x est une variable alors ∀푥퐴(푥) et ∃푥퐴(푥) sont des

formules.

Variable libre et variable liée : une variable est dite libre dans une formule A, si elle n’est pas

quantifiée (elle n’apparaît pas après les quantificateurs ou ).

Formalisation du langage naturel : le langage naturel peut être formalisé en utilisant le calcul

de prédicat.

Exemples :

Tous les étudiants sont intelligents : x (Etudiant(x)Intelligent(x)).

Seulement les étudiants sont intelligents : x (Intelligent (x) Etudiant (x)).

Il existe un étudiant intelligent : x (Etudiant(x) ∧ Intelligent(x)).

Il n’existe pas un étudiant intelligent : x (Etudiant(x) ∧ Intelligent(x)).

Il existe des étudiants qui réussissent dans tous les modules :

x (Etudiant(x)∧y(Module(y) Reussi(x,y)).

3.1.3. Sémantique

Une formule prend les valeurs vrai ou faux.

Si A est vrai alors A est faux.

A∧B est vrai si et seulement si A est vrai et B est vrai.

A∨B est vrai si et seulement si A est vrai ou B est vrai ou les deux en même temps.

3.2. Langage relationnel prédicatif On trouve deux types :

3.2.1. Calcule de tuples

Dans ce type, les variables dans les expressions logiques portent sur les tuples des relations.

Comme exemple de langage, on trouve : ALPHA, langage QUEL (SGBD INGRES), et SQL.



C’est un langage d’interrogation de données formel permettant d’exprimer des questions à

partir de formules bien formées, où les variables prennent leurs valeurs dans les tuples d’une

relation, on parle de variable tuple. Ainsi la valeur d’une variable tuple peut être dans

n’importe quelle ligne d’une relation sur laquelle elle est définie, on écrira : nom de la relation

(nom de la variable tuple). Ex : x Etudiant.

Exemple :

Soit la relation : Module (Codem, Libellé, créd).

En extension : Module

Codem Libellé crédit

ALGO Algorithmique 4

SEXP Système d’exploitation 5

BD Base de Données 3

Module (x) : signifie que x est une variable tuple de la relation Module et peut prendre l’une

des valeurs suivantes :

x=(‘ ALGO’, ‘Algorithmique’, 4) : x.Codem=‘ ALGO’, x.Libellé=‘Algorithmique’,

x.créd=4.

x=(‘ SEXP’, ‘Système d’exploitation’, 5).

x=(‘ BD’, ‘Base de Données’, 3).

Formulation d’une requête : une requête se présente sous la forme suivante :

{Résultat de la requête/Prédicat(formule) à vérifier par les variables du résultat}.

Le résulta de la requête s’écrit sous la forme : nom de variable.nom d’attribut.

Formule élémentaire : elle s’écrit sous la forme :

variable.nom d’attribut opérateur variable.nom d’attribut.

L’opérateur est l’un des opérateurs de comparaison usuels : ‘=’, ‘>’, ‘<’, ‘<>’, ‘>=’,

‘<=’.

Le prédicat à vérifier est une combinaison des formules élémentaires.

Remarque

Une variable tuple qui apparaît dans le résultat de la requête ne doit pas être liée dans le

prédicat à vérifier, toutes les autres variables sont liées.



Exemple :

{x.Codem/x (Module (x)et x.créd=5)}est faux.

Car x doit être libre dans le prédicat spécifié (elle ne doit pas être quantifiée).

Exemple : soit la relation : Etudiant (Num, nom, prénom, datnais, adr).

Requête 1 : quels sont les noms des étudiants qui sont nés après 1982.

Réponse : {x.nom/Etudiant(x)∧x.datnais>=01/01/1982}.

Remarque

Si plusieurs attributs sont dans le résultat, ils seront séparés par des virgules.

Requête 2 : quels sont les noms et les prénoms des étudiants qui résident à Alger.

{x.nom, x.prénom/Etudiant(x)∧x.adr= « Alger »}.

Requête multi-relation : elles opèrent sur des relations différentes, et donc il faut manipuler

des variables tuples différentes.

Exemple : soit les relations suivantes :

Module (Codem, Libellé, créd, annétude).

Enseignant (Num, nom, prénom, datnais, adr, grad, nbheur).

Modens (Num, Codem).

Requête 1 : quels sont les noms des enseignants qui interviennent dans les modules de 2ème

année.

Réponse : {x.nom/Enseignant(x)∧y, z (Module(y)∧ Modens(z) ∧ y.annétude=2 ∧

y.Codem=z.Codem ∧x.Num=z.Num)}.

Requête 2 : quels sont les noms et les adresses des enseignants qui assurent tous les modules

de 2ème année.

Réponse : {x.nom, x.adr /Enseignant(x)∧y, z (Module(y)∧y.annétude=2z (Modens(z)

∧y.Codem=z.Codem ∧x.Num=z.Num)}.

3.2.2. Calcul de domaines

Ici, les variables dans les expressions logiques portent sur les valeurs des attributs des tuples.

Exemple de langage : langage QBE.

Les formules du calcul de domaines sont définies comme pour le calcul de tuples, en

remplaçant les variables tuples par les variables domaines. Ex : x Etudiant.nom.



Remarques

L’ordre des attributs devient important.

Les requêtes sont spécifiées ainsi : {x1, x2, ………, xn / f(x1, x2, ………, xn)}.

Exemple : soit la relation : Etudiant (Num, nom, prénom, datnais, adr).

Requête 1 : quels sont les noms des étudiants qui sont nés après 1982.

Réponse : {n/ a(Etudiant(nom :n, datnais :a)∧x.datnais>=01/01/1982)}.

La différence essentielle entre le calcul relationnel des tuples et le calcul relationnel des

domaines est l’ensemble dans lequel les variables prennent leurs valeurs :

Tuples : chaque variable prend ses valeurs dans l’ensemble des tuples d’une relation

particulière.

Domaines : chaque variable prend ses valeurs dans un domaine particulier des attributs

de la base.

Remarque

Le calcul relationnel et l’algèbre relationnelle ont une puissance d’expression équivalente

(complétude relationnelle), donc, on peut traduire tout opérateur de l’algèbre relationnelle en

calcul relationnel de tuples.

3.2.3. Passage de l’algèbre relationnelle au calcul relationnel tuple

Une relation se traduit par le prédicat correspondant.

On peut insérer les conditions des sélections directement dans les formules (en les

combinant en général avec ∧).

La projection correspond à ajouter des devant les variables tuple dont aucun attribut

n’est projeté.

L’union se traduit par un ⋁.

La différence A - B se traduit par 퐹 ∧퐹 où FA et FB ont les mêmes variables libres.

Le produit A x B se traduit par un FA ⋀ FB avec les variables libres de FA et FB

disjointes.

Le renommage peut, lorsque cela est nécessaire, se traduire via l’introduction d’une

nouvelle variable tuple combinée avec des égalités.

La jointure naturelle se traduit par l’ajout d’égalités entre les attributs communs aux

relations constituant la jointure.



3.2.4. Transfert du calcul relationnel à l’algèbre relationnelle

Un prédicat se traduit par la relation correspondante.

Un se traduit en général par une projection.

Un ∧ se traduit par un produit cartésien ou une intersection.

Un ⋁ se traduit par une union.

Une se traduit par une différence, mais la traduction n’est généralement pas directe.

On peut utiliser le renommage en cas de conflit sur les attributs.

On peut utiliser une jointure naturelle lorsque les attributs communs à plusieurs

relations sont liés par des variables.

Une comparaison se traduit par une sélection.

Chapitre 4: SQL : Interrogation

d’une base de données

Chapitre 4 : SQL : Interrogation d’une base de données


Introduction SQL (Structured Query Langage ou bien langage de requête structuré) est un langage

informatique standard pour la communication avec les SGBDRs. Il a été défini par l’ANSI

(American National Standard Institute) et l’ISO (International Standards Organization).

SQL peut être vu comme une boite à outils comprenant à la fois un formalisme proche, du

langage algébrique, et un autre proche du langage prédicatif. Il existe alors souvent, plus

d’une façon d’exprimer une même requête.

Le succès du langage SQL est dû essentiellement à sa simplicité et au fait qu’il s’appuie sur le

schéma conceptuel pour énoncer des requêtes en laissant le SGBD responsable de la stratégie

d’exécution.

Le langage SQL est un langage déclaratif qui permet d'interroger une base de données sans se

soucier de la représentation interne (physique) des données, de leur localisation, des chemins

d'accès, ou des algorithmes nécessaires.

Néanmoins, le langage SQL ne possède pas la puissance d’un langage de programmation :

entrées/sorties, instructions conditionnelles, boucles et affectations. Pour certains traitements

il est donc nécessaire de coupler le langage SQL avec un langage de programmation plus

complet.

De manière synthétique, on peut dire que SQL est un langage relationnel, il manipule donc

des tables (des relations, c’est-à-dire des ensembles) par l’intermédiaire de requêtes qui

produisent également des tables.

SQL peut être utilisé :

D’une manière interactive.

En association avec des interfaces graphiques.

Ou, très généralement, des langages de programmation.

Le tableau 4.1 présente un historique du langage SQL.

SQL est le langage des bases de données relationnelles répondant à la fois à la problématique

de création des objets de bases de données (modèle), de manipulation des données (algèbre

relationnelle), de gestion de la sécurité (droits d’accès), de traitements locaux de données

(procédures). De plus, il est désormais doté d’extensions objet.

Dans ce chapitre, on va s’intéresser à l’interrogation d’une BD en utilisant le langage SQL.



Année Appellations Apports majeurs

1986 SQL-86-SQL87 1ère publication par l’ANSI.

Ratification par l’ISO en 1987.

1989 SQL-89 Révisions mineurs.

1992 SQL92, SQL2 Révisions majeurs (ISO 9075).

1999 SQL99, SQL3 Expressions régulières.

Récursivité.

Déclencheurs.

Langage procédural.

Types abstraits.

Aspects objet.

2003 SQL:2003 Prise en compte de XML.

Fonctions de fenêtrage.

Séquences.

Colonnes d’identité.

2006 Intégration plus forte avec XML

(XQuerry).

2008 SQL2008 Correction de certains défauts et

de petits manques des versions

antérieures (fonctions, types,

curseurs, etc.).

Tableau 4.1. Historique du langage SQL.

4.1. Structure des instructions SQL : Langage de Manipulation de Données

(LMD) Toute instruction ou command SQL est une combinaison d’une ou de plusieurs clauses. Les

clauses sont généralement introduites par des mots-clés.

SELECT attribut1, attribut2,.., attributn

FROM Nom de la table

WHERE condition booléenne;



4.1.1. Clauses de base

On doit suivre les règles d’écriture suivantes :

1. Les instructions SQL ne sont pas sensibles à la casse. Toutefois, les mots clés qui

débutent par une clause sont généralement écrits en majuscules pour améliorer la

lisibilité des instructions SQL.

2. Les instructions SQL peuvent être écrites sur une ou plusieurs lignes. Il est habituel

d’écrire chaque clause sur une ligne distincte.

3. Un mot-clé ne peut être réparti sur deux lignes.

4. Les instructions SQL se terminent par un point-virgule.

Le principe des clauses utilisées pour manipuler les données est :

SELECT : indique la liste des attributs constituant le résultat.

FROM : indique la (ou les) tables dans lesquelles on trouve les attributs utiles à la

requête.

WHERE : indique les conditions que doivent satisfaire les n-uplets de la base pour

faire partie du résultat. La comparaison peut être effectuée suivant l’un des opérateurs

présentés dans la figure 4.1.

Figure 4.1. Opérateurs de comparaison.

Remarque

Pour afficher l'intégralité d'une table, et avoir ainsi toutes les lignes (on omet la clause

WHERE), et toutes les colonnes, on peut au choix lister tous les attributs ou utiliser le

caractère * qui a la même signification.

ORDER BY : est utilisée pour trier les résultats d'une requête. Cette clause doit être suivie de

la liste des attributs servant de critère au tri. Pour trier en ordre descendant, on ajoute le mot-

clé DESC après la liste des attributs. Et pour avoir un tri ascendant, on ajoute le mot-clé ASC.

Par défaut les attributs seront triés en ordre ascendant.



Remarques

1. Pour obtenir une recherche par intervalle, on peut également utiliser le mot-clé

BETWEEN.

2. Lorsque le SGBD construit la réponse d’une requête, il rapatrie toutes les lignes qui

satisfont la requête, généralement dans l’ordre ou il les trouve, même si ces dernières

sont en double (comportement ALL par défaut). C’est pourquoi il est souvent

nécessaire d’utiliser le mot clé DISTINCT qui permet d’éliminer les doublons dans la

réponse.

3. Il est possible d’utiliser les opérateurs mathématiques de base (‘+’, ‘-’, ‘*’ et ‘/’) pour

générer de nouvelles colonnes à partir, en générale, d’une ou plusieurs colonnes

existantes.

4. Le mot clé AS permet de renommer une colonne, ou de nommer une colonne créée

dans la requête.

5. L’opérateur || (double barre verticale) permet de concaténer des champs de type

caractères.

6. L’opérateur IN spécifie un ensemble de valeurs possibles.

7. En pratique, il est possible d’avoir des valeurs non définies qui sont représentées par le

mot clé NULL. On peut tester si une valeur n’est pas définie grâce à la condition IS

NULL (ou son contraire IS NOT NULL).

8. Si on trouve plusieurs tables dans une requête, cela représente un produit cartésien

entre ces différentes tables.

9. Si un attribut est présent dans plusieurs tables utilisées, on doit l’écrire : nom-table.att.

10. Dans les comparaisons des chaînes de caractères, il est possible d’utiliser l’opérateur

LIKE : une chaîne commence par un ensemble de caractères, et les caractères

génériques, ‘%’ qui remplace une chaîne de caractères de taille quelconque, et ‘ ?’ qui

remplace un seul caractère.

11. En SQL, la jointure s’exprime comme une sélection sur le produit cartésien :

SELECT att1, att2

FROM nom_table1, nom_table2

WHERE nom_table1.attx = nom_table2.attx;

4.1.2. Sous-requête

C’est l’utilisation du résultat d’une requête dans une autre requête. Le but est l’augmentation

de la puissance d’expression du langage. On peut utiliser les opérateurs suivants :

1. A IN (sous-requête), elle est vrai si A apparaît dans le résultat du sous-requête.



2. A (=; <; >;<=;>=) ANY (sous-requête), elle est vrai s’il existe un b parmi les lignes

renvoyées par la sous-requête tel que A (=; <; >;<=;>=) b soit vrai.

3. A (=; <; >;<=;>=) ALL (sous-requête), elle est vrai si pour toutes les lignes b

renvoyées par la sous-requête tel que A (=; <; >;<=;>=) b soit vrai.

4. EXISTS (sous-requête) (et son contraire NOT EXISTS (sous-requête)), elle est vrai

si le résultat de la sous-requête n’est pas vide, avec au moins un tuple.

5. On peut tester l’inclusion entre les ensembles via le mot clé CONTAINS.

4.1.3. Fonctions d’agrégats et regroupement

Fonctions d’agrégat : ensemble de fonctions qui permettent d’effectuer des statistiques sur le

résultat d’une requête (MIN : minimum, MAX : maximum, SUM : somme, AVG : moyenne,

COUNT : nombre, etc.).

Regroupements : possibilité de regrouper plusieurs lignes d’une même requêtes (souvent

associés aux fonctions d’agrégat). Pour cela, on utilise les clauses :

GROUP BY : permet de subdiviser la table en groupes, chaque groupe étant l’ensemble des

lignes ayant une valeur commune.

HAVING : permet de sélectionner des groupes de la requête de regroupement. Elle permet

d’introduire des conditions sur les groupes, afin d’éliminer du résultat les groupes n’obéissant

pas à certains critères.

Il faut respecter les règles suivantes :

Les colonnes du SELECT doivent toutes apparaître dans le GROUP BY (sauf les

opérations).

Les noms de colonnes dans le HAVING doivent aussi être dans le GROUP BY (ou

être fonction d’agrégat).

La clause GROUP BY va regrouper les lignes qui sont identiques sur les colonnes

mentionnées dans le GROUP BY.

Différence entre WHERE et HAVING

WHERE : sélectionne les lignes de la requête avant de faire les groupes. Elle agit

donc avant les regroupements.

HAVING : sélectionne les groupes une fois qu’ils sont constitués. Elle agit donc après

les regroupements.



4.1.4. Opérateurs ensemblistes

UNION : permet de fusionner deux tables ayant le même schéma.

INTERSECT : permet d’obtenir les lignes communes de deux tables.

MINUS : permet d’effectuer la différence entre deux tables. (EXCEPT pour la norme

SQL2).

Remarque

SQL ne comporte pas d’opérateurs spécifiques à la division. Cependant, il est possible

d’exprimer la sémantique de cet opérateur en se basant sur les opérateurs logiques.

Chapitre 5: SQL : Définition et

modification d’une base de données

Chapitre 5 : SQL : Définition et modification d’une base de données


Introduction SQL est un Langage de Définition de Données (LDD), c'est-à-dire qu'il permet de créer des

tables dans une base de données relationnelle, ainsi que d'en modifier ou en supprimer.

5.1. Création d’une table Les tables sont à la base de stockage des données dans les SGBDRs, elles comportent toutes

les données accessibles à l’utilisateur. Pour en créer une, il faut lui donner un nom et tous les

attributs qu’elle doit comporter. De plus, pour chaque attribut, l’utilisateur doit définir le type

de données concernées et, si nécessaire, les contraintes appropriées.

Le nom d’une table l’identifie comme objet unique dans le SGBDR. Les noms de colonnes ou

attributs doivent être uniques pour une table donnée.

5.1.1. Règles de création des tables avec SQL

Les tables SQL sont créées par l’instruction ou commande CREATE TABLE comme suit :

CREATE TABLE nom de la table

(

Nom de la colonne 1 type de données [valeur par défaut] [contrainte],

Nom de la colonne 2 type de données [valeur par défaut] [contrainte],

..

Nom de la colonne n type de données [valeur par défaut] [contrainte]

);

Exemple : créer une table nommée étudiant qui porte deux attributs, le nom et le prénom.

Les contraintes définies en SQL sont :

Non nullité des valeurs d'un attribut en se servant du mot clé NOT NULL qui permet

de vérifier qu'il existe une valeur pour chaque élément de la colonne.

Unicité de la valeur d'un attribut ou d'un groupe d'attributs en utilisant la clause

UNIQUE qui permet de s'assurer qu'il n'existe pas de valeur dupliquée dans la

colonne.

Valeur par défaut pour un attribut.

Contrainte de domaine en utilisant la clause CHECK qui permet de spécifier des

conditions logiques portant sur une ou plusieurs colonnes d'une même table.

Clé primaire (un attribut ou un groupe) par la clause PRIMARY KEY qui a le même

rôle que la clause UNIQUE mais ne peut être spécifiée qu'une seule fois dans la table.



Intégrité référentielle "minimale" par la clause REFERENCES sur les colonnes qui

matérialise une dépendance entre deux colonnes de la table. Et par la clause

FOREIGN KEY sur la table qui matérialise une dépendance entre deux colonnes de

deux tables

Exemple 1 :

CREATE TABLE étudiant

( Num INTEGER PRIMARY KEY,

Nom CHAR(40) NOT NULL,

Année INTEGER CONSTRAINT CAnnée CHECK (Année BETWEEN 1997 AND 2013),

Moyenne Number(2,3) CONSTRAINT CMoyenne CHECK (Moyenne BETWEEN 00,000

AND 20,000));

Exemple 2 :

CREATE TABLE Consommateur

( ID INTEGER CONSTRAINT PK_Consommateur PRIMARY KEY,

Nom TEXT(50) NOT NULL,

Prénom TEXT(50) NOT NULL,

Tél TEXT(10),

Email TEXT(50),

Address TEXT(40));

Exemple 3 :

CREATE TABLE Véhicules (Nom TEXT(30), Année TEXT(4), Prix CURRENCY);

Types de données utilisés : voici un ensemble représentatif des types de données utilisés lors

de la création d’une table en SQL dans ACCESS :

Booléen : BIT.

Nombre entier : SHORT (entier), SMALINT (entier), LONG (entier long), INTEGER

(entier long).

Nombre réel : SINGLE (réel simple), DOUBLE (réel double), Numeric (réel double).

Monétaire : CURRENCY, MONEY.

Date/heure : DATE, TIME, DATETIME.

Texte: VARCHAR(255), CHAR(n) ou TEXT(n), où n est le nombre de caractères.



5.1.2. Opérations sur les tables

Avec SQL, on peut supprimer, modifier ou renommer des tables.

Suppression d’une table : supprimer une table revient à éliminer sa structure et toutes les

données qu’elle contient. Les index associés sont également supprimés.

La syntaxe est la suivante :

DROP TABLE nom_table;

Il ne faut pas qu’une clé étrangère d’une autre table référence la table à supprimer.

En Oracle, on peut ajouter à la fin le mot clé CASCADE pour déclencher la suppression des

clés étrangères qui référencent la table à supprimer.

Exemple : DROP TABLE étudiant;

Modifier une table : en utilisant l’instruction : ALTER TABLE qui permet de modifier la

structure de la table, elle consiste donc à ajouter ou modifier des colonnes de la table.

Syntaxe : ALTER TABLE nom_table modification;

On peut avoir les situations suivantes :

ALTER TABLE nom table ADD att type NOT NULL;

Ajouter à la table nom table un attribut att contenant des données correspondant à

type.

On peut optionnellement spécifier NOT NULL lorsque l’on souhaite interdire la

valeur NULL.

Ex : ALTER TABLE Véhicules ADD COLUMN État TEXT(10);

ALTER TABLE nom table ALTER att nouveau type NOT NULL;

Changer le type de l’attribut att, en spécifiant optionnellement NOT NULL.

Ex : ALTER TABLE Véhicules ALTER État Text;

ALTER TABLE nom table RENAME COLUMN att TO nouvel att;

Changer le nom de att en nouvel att.

ALTER TABLE nom table DROP COLUMN att;

Supprimer l’attribut att de la table nom table.A

Ex : ALTER TABLE Véhicules DROP COLUMN Nom; jouter ou

ALTER TABLE nom table ADD CONSTRAINT nomc contrainte;

Ajouter la contrainte contrainte sur la table nom table.

CONSTRAINT nomc spécifie le nom optionnel de la contrainte.

ALTER TABLE nom table DROP PRIMARY KEY;

Supprimer la clé primaire.



ALTER TABLE nom table DROP FOREIGN KEY nom cle;

Supprimer la clé étrangère nommée nom cle.

Renommage d’une table : on peut renommer une table avec l’instruction suivante :

RENAME ancien_nom TO nouveau_nom;

5.1.3. Manipulation sur les tables

Dans SQL, on peut manipuler les lignes des tables par l’utilisation des trois instructions de

langage de manipulation de données ; INSERT INTO, UPDATE et DELETE.

Insertion de n-uplets : la commande INSERT INTO permet d’insérer une ligne dans une

table en spécifiant les valeurs à insérer.


INSERT INTO nom_table (nom_col_1, nom_col_2, ...)

VALUES (val_1, val_2, ...)

La liste des noms de colonne est optionnelle. Si elle est omise, la liste des colonnes sera par

défaut la liste de l’ensemble des colonnes de la table dans l’ordre de la création de la table. Si

une liste de colonnes est spécifiée, les colonnes ne figurant pas dans la liste auront la valeur

NULL.

Il est possible d’insérer dans une table des lignes provenant d’une autre table.


INSERT INTO nom_table (nom_col1, nom_col2, ...)

SELECT

Exemple:

INSERT INTO étudiant Values (‘123’, ‘Talebi’, ‘2010’,’12,54’);

Modification des lignes : la commande UPDATE permet de modifier les valeurs d’une ou

plusieurs colonnes, dans une ou plusieurs lignes existantes d’une table.


UPDATE nom_table

SET nom_col_1 = {expression_1 | ( SELECT ...) },

nom_col_2 = {expression_2 | ( SELECT ...) },

...

nom_col_n = {expression_n | ( SELECT ...) }

WHERE predicat;



Les valeurs des colonnes nom_col_1, nom_col_2, ..., nom_col_n sont modifiées dans toutes

les lignes qui satisfont le prédicat.

En l’absence d’une clause WHERE, toutes les lignes sont mises à jour.

Les expressions expression_1, expression_2, ..., expression_n peuvent faire référence aux

anciennes valeurs de la ligne.

Exemple :

UPDATE TABLE étudiant

SET Moyenne=’14,56’

WHERE Nom=’Talbi’;

Suppression de n-uplets : la commande DELETE permet de supprimer des lignes d’une

table.


DELETE FROM nom_table

WHERE predicat

Toutes les lignes pour lesquelles le prédicat est évalué à vrai sont supprimées.

En l’absence de clause WHERE, toutes les lignes de la table sont supprimées.

Exemple :

DELETE FROM TABLE étudiant

WHERE Nom=’Talbi’;

Documents

Page de garde - Université Larbi Ben M'hidi O.E.B BD.pdfde gestion de fichiers, il y avait deux types de fichiers : 1. Fichier des données : représentent des séquences d’enregistrements