Bases de l'algèbre relationnellefoufoukh.free.fr/1ere année GEA/Informatique/AL_REL.doc · Web viewL'algèbre relationnelle est un ensemble de règles et d'opérateurs ensemblistes

AR;CoursL'algèbre relationnelle est un ensemble de règles et d'opérateurs ensemblistes sur lesquels sont basés tous les gestionnaires de bases de données dit relationnels ( ex : ACCESS, PARADOX, ORACLE, INFORMIX, SQL server, etc. ). L'algèbre relationnelle permet de traduire des questions par une combinaison d'opérations sur des ensembles. La connaissance de l'Algèbre Relationnelle n'est pas nécessaire pour une utilisation basique d'ACCESS, mais elle s'avère indispensable lorsqu'il s'agit de répondre à des questions complexes ( ex : Quels sont les fournisseurs qui ne fournissent pas tous les produits d'une catégorie donnée ? ).Pour bien comprendre les notions suivantes, il est indispensable de se détacher du gestionnaire de bases de données avec lequel on veut exploiter les informations.

1 Relation : la structure de données._______________________2

2 Les opérateurs relationnels : traducteurs du langage courant._32.1 Les opérateurs unaires : s'appliquent à une seule relation..................3

2.1.1 La sélection : diminuer le nombre de lignes d'une relation................42.1.2 La projection : modifier la structure de la table (enlever des colonnes).........................................................................................................4

2.2 Les opérateurs binaires s'appliquent à deux relations..........................52.2.1 Sur deux structures identiques : Union, Intersection, Soustraction. . .52.2.2 Sur deux structures identiques ou différentes : Produit cartésien, Jointures, Division............................................................................................7

3 Compléments : critères, agrégation, traductions___________123.1 Possibilités et syntaxe des critères plus ou moins complexes de sélection et de jointure....................................................................................12

3.1.1 Utilisation d'un comparateur :..........................................................123.1.2 Opérateurs spéciaux : ESTNULL, ENTRE ET, Maintenant()...........123.1.3 Opérateurs logiques : ET, OU, NON................................................13

3.2 Projection d'agrégation et Sélection de regroupement : résultats "statistiques" sur plusieurs enregistrements................................................143.3 Traduction d'une syntaxe relationnelle pour l'utiliser sur un Système de Gestion de Bases de Données Relationnelles..........................................15

3.3.1 Mise en oeuvre d'une base exemple et recherche des moyens de traduction de l'Algèbre relationnelle...............................................................15

1 Relation : la structure de données.

On appelle relation un tableau qui contient des informations structurées. On appelle "Champs" les colonnes de ce tableau et "Enregistrements" les lignes. Une relation ne contient pas de doublons (deux lignes où les champs sont égaux deux à deux).

Voici un exemple de relation :

Nom Prénom DateNaiss.Dubois Marc 24/3/58Dubois Adeline 23/12/57Durant Marc 24/3/58Martin Carolle 15/7/60Dubois Adeline 19/4/71Martin Marc 15/7/60

Une base de données relationnelle contient évidement plusieurs relations (Tables) qui sont organisées entre elles en fonction de règles de non-redondance. Ces règles sont spécifiées dans toutes les méthodes de représentation basée sur des modèles "Entité / Relation". Par exemple le Modèle Logique des Données (MLD) de MERISE qui est obtenu par une transformation du Modèle Conceptuel des Données (MCD) respecte ces règles.

page 2/17 Algèbre Relationnelle Paul Guion

2 Les opérateurs relationnels : traducteurs du langage courant.

Tous les opérateurs relationnels servent à traduire une question exprimée en langage courant par des opérations sur des ensembles de données. Il est bien sur indispensable que les relations que l'on utilise contiennent les informations nécessaires à la réponse. Les opérateurs relationnels créent une nouvelle relation en fonction du contenu des relations auxquelles on les applique. La relation nouvellement créée à le même statut qu'une relation de base, on peut donc appliquer à cette relation les opérateurs relationnels.

Pour apprendre à traduire des questions en "phrases relationnelles" il est important d'effectuer fréquemment la démarche inverse, c'est à dire examiner la signification des informations présentes dans une relation obtenue par une succession d'opérateurs relationnels.

2.1 Les opérateurs unaires : s'appliquent à une seule relation Les opérateurs unaires servent à extraire d'une seule relation les informations correspondant à une question simple. Exemples : Quels sont les clients dont le CA est supérieur à 20000 F ? Quelles sont les villes dans lesquelles nous avons des clients ?

Pour expliquer le fonctionnement de ces opérateurs nous utiliserons la relation exemple suivante :

CLIENT

NOM RUE CP VILLE CADubois 42, av. du Gal de Gaulle 92000 Nanterre 15 000Martin 125, rue de paris 95130 St Ouen l'aumône 23 500Jaques 12, place de la Boule 92000 Nanterre 21 000Durant 25, av. des Gobelins 75012 Paris 14 600

Cette relation est présenté en extension, c'est à dire sa structure (noms de ses champs) et tous ses enregistrements.

Paul Guion Algèbre Relationnelle page 3/17

2.1.1 La sélection : diminuer le nombre de lignes d'une relation. La sélection permet de choisir des enregistrements en fonction d'une combinaison de conditions qu'on appelle critère. La syntaxe générale est la suivante :

sCritère

( Relation )exemple : Liste des clients dont le CA est supérieur à 20 000 F.

sCA>20000

( CLIENT )résultat :

NOM RUE CP VILLE CAMartin 125, rue de paris 95130 St Ouen l'aumône 23 500Jaques 12, place de la Boule 92000 Nanterre 21 000

La sélection enlève les lignes qui ne correspondent pas au(x) critère(s) spécifié(s). Lorsque certaines colonnes ne nous intéressent pas, il faut utiliser l'opérateur de projection.

2.1.2 La projection : modifier la structure de la table (enlever des colonnes)

La projection ne retiendra que les colonnes qui nous intéressent. Sa syntaxe générale est la suivante :

pNoms du(es) champ(s) désiré(s)

( Relation )exemple : Liste des codes postaux et des villes dans lesquelles nous avons des clients.

pCP, Ville

( CLIENT )résultat :

CP VILLE92000 Nanterre95130 St Ouen l'aumône75008 Paris

Attention en algèbre relationnelle l'opération de projection élimine systématiquement les doublons (ex : "92000 Nanterre" n'est présent qu'une fois) ce n'est pas forcément le cas pour les logiciels mais il existe toujours une option qui permet d'éliminer les doublons comme en algèbre relationnel.


2.2 Les opérateurs binaires s'appliquent à deux relations Les opérateurs binaires vont permettre de croiser des informations en provenance de plusieurs relations afin de répondre à des questions plus complexes. Lors de l'emploi des opérateurs binaires il convient d'être très attentif aux structures des relations mises en jeux.

2.2.1 Sur deux structures identiques : Union, Intersection, Soustraction Pour ces opérateurs les structures des tables doivent être strictement identiques : Même nombre de champs et champs de même type (même nombre de caractères pour les chaînes, et même codage pour les nombres) on ne mélange pas des entiers et des réels...

Pour expliquer le fonctionnement des trois opérateurs ci-après nous utiliserons les tables suivantes :

Fournisseurs ClientsNomFrs (30) PreFrs (25) NomCli (30) PreCli (25)Dubois Ernest Martin LaureDurant Patrick Durant MarcDubois Carolle Viguier SylvieDurant Marc Dubois CarolleMatin Laure

2.2.1.1 Union : ajouter les lignes de deux relations de même structure L'union permet de créer une relation qui contient tous les éléments de deux relations de même structure. Les enregistrements communs ne seront présent qu'une fois. Cet opérateur permet de répondre aux questions pour lesquelles les éléments de réponse doivent être présents dans au moins un des ensembles pris en considération. La syntaxe générale est la suivante :

Relation1 È Relation2

Exemple : créer une relation qui contienne les noms et prénoms de toutes les personnes en relation avec l'entreprise.

Fournisseurs È Clients

Nom (30) Pre (25)Dubois ErnestDurant PatrickDubois CarolleMartin LaureDurant MarcViguier Sylvie


2.2.1.2 Intersection : ne conserver que les lignes qui sont présentes dans les deux relations.

L'intersection permet de créer une relation qui contient les éléments communs de deux structures identiques. Cet opérateur permet de répondre à des questions dans lesquelles les éléments de réponse doivent être présent dans plusieurs ensembles. La syntaxe générale est la suivante :

Relation1 Ç Relation2

Exemple : les noms et prénoms des personnes qui sont clients et aussi fournisseurs

Nom (30) Pre (25)Dubois CarolleMartin LaureDurant Marc

2.2.1.3 Soustraction : ne conserve que les lignes qui sont dans une table et pas dans l'autre.

La soustraction crée une nouvelle relation qui contient les éléments de la relation qui ne sont pas présents dans l'autre relation. Cet opérateur permet de répondre à des interrogations qui contiennent une négation. Les deux relations misent en jeux doivent avoir la même structure. La syntaxe générale est la suivante :

Relation1 - Relation2

Exemple : Liste des fournisseurs qui ne sont pas des clients.

Fournisseurs - Clients

Nom (30) Pre (25)Dubois ErnestDurant Patrick

Attention : contrairement à l'union et à l'intersection, la soustraction n'est pas

commutative. Le résultat de "Client - Fournisseurs" n'est pas le même.

Clients - Fournisseurs

Nom (30) Pre (25)Viguier Sylvie


2.2.2 Sur deux structures identiques ou différentes : Produit cartésien, Jointures, Division

Ces opérateurs permettent d'effectuer des opérations sur des relations de structure différentes toutefois pour certains les structures doivent avoir des particularités l'une par rapport à l'autre.

Pour expliquer les opérateurs suivants nous utiliserons les relations ci-dessous

Commande LignesNOCOM DATCOM NUMCOM REF QTE732 15/3 732 x123 2733 16/3 732 x124 4734 16/3 732 x125 5735 16/3 733 x121 3

733 x124 2734 x121 2734 x123 5734 x124 2734 x125 3735 x124 2735 x125 1


2.2.2.1 Produit cartésien : toutes les combinaisons possibles Le produit cartésien n'est pratiquement jamais utilisé tout seul mais les jointures sont toujours basées sur un produit cartésien. La syntaxe générale est la suivante :

Relation1 ´ Relation2

La structure du produit cartésien est la juxtaposition des structures des deux tables. Chaque enregistrement de la première table est combiné avec tous les enregistrements de la seconde. Aucune condition sur les structures des deux relations n'est requise.

Exemple : Toutes les combinaisons possibles entre "Commande" et "Lignes".

Commande ´ Lignes

NOCOM DATCOM NUMCOM REF QTE732 15/3 732 x123 2732 15/3 732 x124 4732 15/3 732 x125 5732 15/3 733 x121 3732 15/3 733 x124 2732 15/3 734 x121 2732 15/3 734 x123 5732 15/3 734 x124 2732 15/3 734 x125 3732 15/3 735 x124 2732 15/3 735 x125 1733 16/3 732 x123 2733 16/3 732 x124 4733 16/3 732 x125 5733 16/3 733 x121 3Etc...

Le produit cartésien n'est pas représenté de manière exhaustive, il devrait comporter 44 lignes (4 Commandes par 11 lignes)

Le produit cartésien n'est pas utilisé en tant que tel car il fourni un croisement d'informations généralement dénué de sens. On utilisera une jointure qui est un sous ensemble du produit cartésien. Toutefois le calcul d’une jointure implique la création du produit cartésien.


2.2.2.2 Jointures : produits cartésiens moins les éléments sans significations

La jointure est en fait une sélection d'un produit cartésien, la sélection opère une comparaison entre deux champs et ne conserve que les éléments qui répondent au critère de la sélection.

s ChampR1 q ChampR2 (R1 ´ R2)

on note :

R1 R2

On parle en général de thêtajointure où thêta (q) représente le comparateur du critère de la sélection. Les champs comparés à l'aide de q doivent être de même type. La jointure la plus employée est l'équijointure (le critère est une comparaison d'égalité) dans cette jointure généralement les deux champs représente le même type d'objet.

La structure de la relation résultante d'une jointure est la juxtaposition des structures des relations de base. Les deux relations jointes doivent chacune posséder un champ de même format, dans l'utilisation courante de gestion ces champs doivent en plus représenter des sous-ensembles d'une même réalité.

Exemple : Liste des commandes avec leurs lignes

Commandes Lignes

NOCOM DATCOM NUMCOM REF QTE732 15/3 732 x123 2732 15/3 732 x124 4732 15/3 732 x125 5733 16/3 733 x121 3733 16/3 733 x124 2734 16/3 734 x121 2734 16/3 734 x123 5734 16/3 734 x124 2734 16/3 734 x125 3735 17/3 735 x124 2735 17/3 735 x125 1


2.2.2.3 Division : réponse aux questions "tous les" La division permet de répondre au questions qui contiennent le quantificateur "tous". Les structures des relations misent en jeux doivent correspondre au schéma suivant :

Syntaxe générale : RelDividende ¸ RelDiviseur

Exemple 1 : Liste des commandes qui contiennent toutes les références.

Il convient dans un premier temps de trouver les structures qui correspondent au schéma ci-dessus. Le dividende doit comprendre les commandes avec les références qu'elles contiennent.

Relation dividende : pNUMCOM, REF

( Lignes )

NUMCOM REF732 x123732 x124732 x125733 x121733 x124734 x121734 x123734 x124734 x125735 x124735 x125

Le diviseur doit comprendre toutes les références. Ici il ne s'agira évidement que des révférences ayant été commandées au moins une fois.

Relation diviseur : pREF

( Lignes )REFx121x123x124x125


Le résultat (Relation Quotient) comprendra les N° des commandes qui contiennent toutes les références.

Relation Quotient :pNUMCOM, REF

( Lignes ) ¸ pREF

( Lignes )

NUMCOM734

Exemple 2 : Les références qui sont présentes dans toutes les commandes.

Le dividende doit comprendre les commandes avec les références qu'elles contiennent. C'est donc la même relation que précédemment.

Relation dividende : pNUMCOM, REF

( Lignes )Le diviseur doit comprendre toutes les commandes :

Relation diviseur : pNOCOM

( Commande )

NOCOM732733734735

Le résultat (Relation Quotient) comprendra les Références qui sont présentes dans toutes les commandes.

Quotient :

pNUMCOM, REF

(Lignes) ¸

pNOCOM

(Commandes)


REFx124


3 Compléments : critères, agrégation, traductions

3.1 Possibilités et syntaxe des critères plus ou moins complexes de sélection et de jointure.

La ligne de critère doit toujours aboutir à une valeur logique par rapport à chaque enregistrement. Dans une ligne de critère on peut trouver les expressions ci-après :

3.1.1 Utilisation d'un comparateur : q désignera un comparateur (=, >, >=, <, <=, <>)

NomDeChamp q Valeur (du même type que le champ)

exemple : CA > 20000 pour chaque enregistrement l'expression est testée et on ne conserve que les enregistrements pour lesquels le résultat de l'expression est "VRAI".

NomDeChamp1 q NomDeChamp2

exemple : PURevient < 35% * PUVente

3.1.2 Opérateurs spéciaux : ESTNULL, ENTRE ET, Maintenant() NomDeChamp ESTNULL renvoi VRAI lorsque le champ n'est pas rempli dans l'enregistrement testé.

NomDeChamp ENTRE Valeur1 ET Valeur2 Affiche les enregistrements pour lesquels les valeurs sont comprises entre Valeur1 et Valeur2. Il faudra toutefois vérifier les "effets de bords" de cette fonction suivant le logiciel employé. Par exemple sous ACCESS 1.1 si il n'y a que deux valeurs dans la plage et que ces deux valeurs sont les bornes, aucun enregistrement n'est satisfaisant par contre lorsqu'une troisième valeur est comprise dans l'intervalle alors les trois enregistrements sont valable ???

NomDeChampDeTypeDate q Maintenant() +/- Valeur1. Dans tous les logiciels il existe une fonction qui renvoi le "temps système" La syntaxe ci-dessus permet de vérifier si une date d'un champ est positionner d'une certaine façon par rapport à un intervalle de temps basé sur l'instant présent.

exemple : DateNais < (Maintenant() - 3652) : permet d'extraire les personnes ayant aujourd'hui plus de dix ans (3652 jours correspondant à peu prés à dix ans).


3.1.3 Opérateurs logiques : ET, OU, NON Les expressions ci-dessus "renvoi" toujours une valeur logique on peut donc utiliser ces résultats pour les combiner entre eux. Le résultat de la ligne de critère renverra donc le résultat logique correspondant.

ValLog1 ET ValLog2 : renvoi "VRAI" lorsque les deux valeurs logiques sont "VRAI" sinon renvoi "FAUX".

ValLog1 OU ValLog2 : renvoi "VRAI" si l'une au moins des deux valeurs logiques est "VRAI" et renvoi "FAUX" uniquement quand les deux valeurs logique sont "FAUX"

NON ValeurLogique : renvoi "FAUX" si la valeur logique est "VRAI" et réciproquement.

exemple : Les emprunts de l'année dernière qui ont durés plus de 90 jours ou les emprunts qui n'ont pas été rendus quelque soit leur date de début.

[(DatEmp ENTRE (Maintenant() - 365) ET (Maintenant() - 730))

ET ((DateRet - DatEmp) > 90) ET NON( DatRet ESTNULL)]OU (DatRet ESTNULL)

Attention la syntaxe du critère ci-dessus est à adapter en fonction des caractéristiques du logiciel employé.


3.2 Projection d'agrégation et Sélection de regroupement : résultats "statistiques" sur plusieurs enregistrements.

Les notations suivantes ne sont pas formelles mais utilisables. Trois exemples sont suffisants pour comprendre leur utilisation.

Quel est le CA des clients parisiens ?On utilisera pour répondre à cette question un projection sur une fonction d'agrégation :

pS(CA) (sCP ENTRE 75000 ET 76000 (Client))

Quel la moyenne des CA des différentes catégorie de client ?On utilisera pour répondre à cette question une sélection de regroupement et une projection sur la fonction d'agrégation Moyenne().

pMoyenne(CA) (sGrouperSur(COCAT) (Client))

Nombre de clients et CA par ville ou arrondissements ?On utilisera pour répondre à cette question une sélection de regroupement et une projection sur la fonction d'agrégation NbValeurs().

pNbValeur(COCLI), S(CA) (sGrouperSur(CP) (Client))


3.3 Traduction d'une syntaxe relationnelle pour l'utiliser sur un Système de Gestion de Bases de Données Relationnelles.

L'algèbre relationnelle est un moyen de traduire une interrogation en langage courant par des opérations sur des ensembles de données. Bien entendu ce niveau de traduction n'est pas suffisant pour obtenir une liste qui sort sur une imprimante. Il faut donc prévoir un autre niveau de traduction qui permet de passer d'une "phrase relationnelle" à une requête dans un Système de Gestion de Bases de Données Relationnelles (SGBDR).

Tous les SGBDR admettent SQL (Structured Query Language) comme langage commun. Toutefois la puissance de SQL n'est pas la même dans tous les SGBDR, par exemple la soustraction et la division ne sont pas disponibles directement sous ACCESS 1.1 alors qu'ils le sont sous ORACLE. D'autre part, certains SGBDR permettent de créer des requêtes graphiquement sans se préoccuper de syntaxe SQL (c'est par exemple le cas de ACCESS). La méthode de traduction de l'algèbre relationnelle dépendra donc du SGBDR disponible.

3.3.1 Mise en oeuvre d'une base exemple et recherche des moyens de traduction de l'Algèbre relationnelle.

La syntaxe SQL, ou les méthodes graphiques dépassent le champ du présent document. On peut tout à fait créer des requêtes sur un SGBDR graphique sans connaître l'algèbre relationnelle ou SQL. Toutefois pour pouvoir répondre à certaines interrogations de gestion il est nécessaire d'avoir un outil qui permette un certain recul par rapport au SGBDR utilisé, l'algèbre relationnelle peut être cet outil. Il est donc nécessaire pour utiliser un SGBDR de comprendre les opérateurs relationnels et d'être capable de les traduire dans le SGBDR disponible.

Pour bien assimiler le fonctionnement du SGBDR dont on dispose il convient de construire un exemple qui permettra de vérifier les opérateurs relationnels. C'est à dire un exemple suffisamment complet pour permettre des interrogations complexes mais avec le minimum d'enregistrements pour pouvoir vérifier toutes les résultats de manière manuelle.

3.3.1.1 Créer la structure d'une base de données exemple. Dans un SGBDR on trouve deux sortes de relations.

Les tables : Ce sont les informations stockées de manière permanente par le SGBDR.

Les requêtes : Ce sont des transformations basées sur des tables ou des requêtes qui sont disponible de manière temporaire.

A partir d'un modèle entité relation retrouver les structures des tables puis implémenter ces tables en fonction des spécificités du SGBDR. Il est utile d'examiner les possibilités de contrôle de l'intégrité référentielle du logiciel. Cela permettra ainsi de construire un exemple solide et de ne pas avoir de problèmes dus à des erreurs de saisie lors du report des clés externes dans les tables "esclaves".


3.3.1.2 Trouver des questions qui utilisent les opérateurs relationnels. Il faut ensuite déterminer un certain nombre de questions qui permettent d'utiliser tous les opérateurs relationnels, d'abord un par un puis par combinaison de plusieurs opérateurs. Ces question devront être traduites en algèbre relationnelle et accompagnées d'un schéma de la structure résultante.

A partir de ces questions il est possible de remplir la base de données de manière à obtenir un élément de réponse à chaque question. Une fois que l'on a saisi les enregistrements exemples on va pouvoir apprendre à traduire les opérateurs relationnels.

3.3.1.3 Création des requêtes : traduction des opérateurs relationnels. Lors de la traduction des "phrases relationnelles" en requêtes on s'attachera au respect de l'AR notamment au niveau des structures résultantes et du dédoublonage. Ceci permettra l'utilisation de l'AR comme outil de réflexion, en effet si l'on se contente de traductions approximatives au niveau des structures ou du dédoublonage automatique on risque par la suite d'obtenir des résultats incohérents. Ces incohérences sont faciles à déceler sur une base exemple mais ne seront pas détectables dans un environnement réel.

Le résultat de chaque requête devra être comparé au résultat manuel obtenu à partir des extensions des tables exemples.

Certains opérateurs comme la division peuvent être obtenus par un "bricolage" à partir d'autres opérateurs notamment en utilisant les fonctions d'agrégation (NbValeurs() et les notions de dédoublonage automatique lors d'une projection judicieusement choisie).

Pour être efficace AR doit être un mode de réflexion sûr. Les traductions dans un sens comme dans l'autre doivent toujours donner le même résultat. Vous ne devez donc jamais être surpris par un résultat, si cela vous arrive vous devez absolument comprendre pourquoi en effectuant la traduction vers le langage courant en passant par l'AR.


Documents

Bases de l'algèbre relationnellefoufoukh.free.fr/1ere année GEA/Informatique/AL_REL.doc · Web viewL'algèbre relationnelle est un ensemble de règles et d'opérateurs ensemblistes