bases de données · 2020. 4. 3. · fichiers (e.g., assembleur, cobol). 1.2. intérêt...

République Algérienne Démocratique et Populaire

Ministère de l’Enseignement Supérieur et de la Recherche

Scientifique Université Larbi Ben M’hidi – Oum El Bouaghi

Faculté des Sciences Exactes et des Sciences de la Nature et de la

Vie Département de Mathématiques et de l'Informatique

Polycopié de Cours

Bases de Données

Niveau : 2ème année licence en Informatique

Dr. SAIGHI Asma

Préambule

Le présent document s'agit d'un support de cours concernant la matière Bases de données, enseigné

au Département de Mathématiques et d'Informatique à l'université d'Oum El Bouaghi et destiné aux

étudiants de deuxième année licence en informatique.

Ce cours permet d’initier l'étudiant aux Bases de données d'une manière aussi simple et claire que

possible vu son importance, tout en essayant de donner une vision sur ce que c’est une base de données

ainsi que sur sa gestion. Le cours aborde également des concepts de base tels que les fichiers, les

différents modèles de données et spatialement le modèle relationnel, l’algèbre relationnel, le système

de gestion de base de données et le langage SQL d’interrogation de bases de données. Par conséquent,

l’étudiant peut comprendre l’intérêt de la structuration et la manipulation des données sous forme de

tables. Tout les concepts et les opérations sur les données sont illustrées par des exemples.

Pour que nos objectifs soient atteints, nous avons synthétisé les informations les plus pertinentes en

s'appuyant sur des sources variées (ouvrages, notes de cours, sites internet, etc.) tout en respectant le

canevas officiel défini par le ministère de l'enseignement supérieur et de la recherche scientifique.

Néanmoins, nous avons conscience que ce document restera partiel, tronqué et non exhaustif. C'est

pourquoi nous essayons de veiller à une actualisation permanente dans le but d'enrichir son contenu.

Ainsi, nous serions reconnaissant aux lecteurs de nous signaler toute erreur ou de nous proposer des

suggestions dans ce sens.

Oum El Bouaghi, Octobre 2019

Dr. SAIGHI Asma

i

Table des matières

Chapitre 1 : Présentation des bases de données

1. Notion de fichier........................................................................................................................... 1

1.1. Définition ................................................................................................................................ 1

1.2. Intérêts ..................................................................................................................................... 1

1.3. Limites des systèmes de fichiers........................................................................................... 1

2. Définition de base de données (BD) ......................................................................................... 2

3. Système de gestion de base de données .................................................................................... 3

3.1. Définition ................................................................................................................................ 3

3.2. Niveaux d’abstraction ............................................................................................................ 3

3.3. Objectifs des SGBD .............................................................................................................. 4

4. Types de modèles de données .................................................................................................... 6

4.1. Modèle sémantique ................................................................................................................ 6

4.2. Modèle Entité-Association ................................................................................................... 6

4.3. Modèle hiérarchique .............................................................................................................. 9

4.4. Modèle réseau ......................................................................................................................... 10

4.5. Modèle relationnel.................................................................................................................. 10

Chapitre 2 : Modèle relationnel

1. Introduction au modèle relationnel............................................................................................ 11

1.1. Définition du modèle relationnel ......................................................................................... 11

1.2. Concepts de base (attribut, Tuple, domaine, relation) ...................................................... 11

1.3. Schéma de relation ................................................................................................................. 12

1.4. Passage du modèle Entité/ Association au modèle relationnel ...................................... 12

2. Normalisation ................................................................................................................................ 14

2.1. Dépendances fonctionnelles (DFs) ..................................................................................... 14

2.1.1. Définition ...................................................................................................................... 14

2.1.2. Propriétés des DFs ....................................................................................................... 14

2.1.3. Types des DFs .............................................................................................................. 15

2.1.4. Graphe de Dépendances Fonctionnelles (GDF)..................................................... 16

ii

2.2. Fermeture transitive (Transitive closure) ............................................................................ 16

2.3. Couverture minimale (Minimal cover) ................................................................................ 16

2.4. Clé de relation ......................................................................................................................... 17

2.5. Contrainte d’intégrité ............................................................................................................. 18

2.6. Formes normales ................................................................................................................... 18

2.6.1. Première Forme Normale (First normal form) 1FN .............................................. 19

2.6.2. Deuxième Forme Normale (Second normal form) 2FN ....................................... 20

2.6.3. Troisième Forme Normale (Third nomal form) 3FN ............................................ 20

2.6.4. Forme Normale FN de Boyce-Codd BCNF (Boyce-Codd normal form) .......... 21

2.7. Schéma de base de données.................................................................................................. 22

3. Modèle relationnel logique (SQL) .............................................................................................. 23

3.1. Table, colonne et ligne ......................................................................................................... 23

3.2. Description de SQL (Structured Query Langage) ............................................................ 23

3.3. Définition de données .......................................................................................................... 24

3.3.1. Création de table (CREATE) .................................................................................... 24

3.3.1.1. Contrainte de colonne ................................................................................... 25

3.3.1.2. Contrainte de table ........................................................................................ 25

3.3.2. Modification de schéma (ALTER, DROP) ............................................................ 26

3.3.2.1. Suppression d’une table ................................................................................ 26

3.3.2.2. Modification d’une table ............................................................................... 27

3.4. Manipulation des données (INSERT, DELETE, UPDATE) ........................................ 27

3.4.1. INSERT INTO ........................................................................................................... 27

3.4.2. UPDATE ..................................................................................................................... 28

3.4.3. DELETE...................................................................................................................... 29

Chapitre 3

1. Définition de l’algèbre relationnelle ........................................................................................... 30

2. Opérations de base ....................................................................................................................... 30

2.1. Opérations et opérateurs unaires (spécifiques) ................................................................. 30

2.1.1. Sélection (Restriction) ............................................................................................... 30

2.1.2. Projection .................................................................................................................... 31

2.1.3. Thêta Jointure ............................................................................................................. 32

iii

2.1.4. Jointure naturelle ............................................................................................................ 33

2.1.5. Traduction en SQL ........................................................................................................ 34

2.1.5.1. Requêtes simples (SELECT-FROM) ............................................................. 34

2.1.5.2. Sélection de colonne (clause WHERE) .......................................................... 34

2.1.5.3. Tri de résultats (ORDER BY) .......................................................................... 36

2.2. Opérations ensemblistes (binaires) ...................................................................................... 36

2.2.1. Union ............................................................................................................................ 36

2.2.2. Différence..................................................................................................................... 36

2.2.3. Produit cartésien (Cartesian product)....................................................................... 38

3. Opérations dérivées ...................................................................................................................... 39

3.1. Intersection ............................................................................................................................ 39

3.2. Jointure externe ..................................................................................................................... 40

3.3. Semi jointure .......................................................................................................................... 42

3.4 Division ................................................................................................................................... 43

4. Le langage algébrique ................................................................................................................... 44

5. Traduction en SQL ....................................................................................................................... 44

5.1. Union ...................................................................................................................................... 44

5.2. Intersection ............................................................................................................................ 45

5.3. Différence .............................................................................................................................. 45

5.4. Produit cartésien (sans jointure) ......................................................................................... 45

5.5. Jointure de tables (condition de jointure) .......................................................................... 46

5.5.1. Thêta jointure .............................................................................................................. 46

5.5.2. Jointure naturelle ......................................................................................................... 47

5.5.3. Jointure externe ........................................................................................................... 47

6. Fonctions d’agrégat ...................................................................................................................... 48

7. Clause GROUP BY …HAVING .............................................................................................. 49

7.1. La clause GROUP BY ......................................................................................................... 49

7.2. La clause HAVING .............................................................................................................. 49

Séries d’exercices ............................................................................................................................... 51

Liste des figures et Tableaux

Figure 1.1. Architecture Générale d’un SGBD [11] ........................................................................... 3

Figure 1.2. Les trois niveaux de schémas ............................................................................................. 4

Figure 1.3. Modélisation d’une entité ................................................................................................... 7

Figure 1.4. Modélisation d’un type entité ............................................................................................. 7

Figure 1.5. Modélisation d’un type entité avec identifiant. ................................................................ 8

Figure 1.6. Exemple d’une association ................................................................................................. 8

Figure 1.7. Exemple de cardinalité 0,n. ................................................................................................ 9

Figure 1.8. Exemple de cardinalité 1,1 ................................................................................................. 9

Figure 1.9. Exemple de cardinalité 0,1. ................................................................................................ 9

Figure 1.10. Exemple de cardinalité 1,n. .............................................................................................. 9

Figure 1.11. Modèles de données........................................................................................................... 10

Figure 1.12. Transformation du modèle E/A au modèle relationnel [13] ...................................... 10

Figure 2.1. Exemple d’application de la règle1 de transformation ................................................... 13

Figure 2.2. Exemple d’application de la règle 2. ................................................................................. 13

Figure 2.3. Exemple d’application de la règle 3 .................................................................................. 13

Figure 2.1. Graphe de dépendance fonctionnelle ............................................................................... 16

Figure 2.4. Différence entre Schéma de base de données et Base de données [12]. ..................... 22

Tableau 2.1. Composants du langage SQL [5]. ................................................................................... 24

Tableau 2.2. Situations possibles de la commande ALTER TABLE ............................................. 27

Dr. Asma Saighi 1

Chapitre 1 : Présentation des bases de données 1. Notion de fichier

1.1. Définition

La notion de fichier (File) a été introduite en informatique durant les années50.

Un fichier est un récipient d’information caractérisé par un nom, constituant une mémoire secondaire

idéale, permettant d’écrire des programmes d’application indépendants des mémoires secondaires [1].

Il y avait deux types de fichiers :

1. Fichier des données : des séquences d’enregistrements dont l’accès est séquentiel ou indexé.

2. Fichier de traitement : un ensemble d’instructions permettant la manipulation des données des

fichiers (e.g., assembleur, Cobol).

1.2. Intérêt

L’objectif des fichiers était de simplifier l’utilisation des mémoires secondaires des ordinateurs. Les

fichiers fournissent des récipients de données plus manipulables aux programmes et sont gérées par

un système de gestion de fichiers.

Suite à la sophistication des systèmes informatiques, les données stockées dans des fichiers

(informations système ou utilisateur) sont devenues structurées. En effet, aujourd’hui, les fichiers sont

à la base des systèmes d’information. De ce fait, le premier niveau d’un SGBD (Système de Gestion

de Base de Données) est la gestion de fichiers. Le SGBD sera présenté dans les paragraphes suivants.

Les données gérées par l’entreprise et les programmes spécifiant les traitements sur les données sont

stockés dans des fichiers gérés par le système informatique. Par exemple, le traitement de l’application

de comptabilité d’une entreprise de livraison de produits (gestion des comptes des clients et édition

des factures, etc.).

La gestion des fichiers permet de traiter et de stocker des quantités importantes de données, et de les

partager entre plusieurs programmes. De plus, elle sert de base au niveau interne des Systèmes de

Gestion de Bases de Données.

1.3. Limites des systèmes de fichiers

Cette approche a souffert des problèmes suivants :

Suite à la redondance des données (i.e. les doublons) dans les fichiers, le volume des données et

le temps d’accès aux données augmentent et la validité du système diminue.

La mise à jour dans l’approche système de fichier est pénible parce que le changement d’une

donnée entraine la nécessité de la changer partout pour conserver la validité du système (e.g.,

si l’adresse d’un client change, il faut la mettre à jour partout).

Chapitre 1 Présentation des bases de données

Dr. Asma Saighi 2

L’accès à l’information est problématique : recherche et lecture/écriture de l’information.

L’application est dépendante du mode de stockage des données.

L’utilisation de fichiers oblige l’utilisateur de connaitre : le mode d’accès à savoir séquentielle,

indexé ou autre, la structure physique des enregistrements ainsi que la localisation des fichiers

utilisés.

Lorsqu’il s’agit des applications nouvelles, l’utilisateur doit : écrire de nouveaux programmes, créer de

nouveaux fichiers contenants peut être des informations qui existe déjà. Par conséquent, toute

mise à jour de la structure des enregistrements (e.g., ajout d’un champ) conduit à la réécriture

de tous les programmes dédiés à la manipulation de ces fichiers.

Manque de sécurité : Dans le cas où tout programmeur peut accéder d’une manière directe

aux fichiers, il n’est pas possible d’assurer la sécurité et l’intégrité des données.

Dans un environnement où plusieurs utilisateurs accèdent aux mêmes fichiers, des problèmes

de concurrence d’accès se posent.

Les données ne sont pas structurées.

Une solution évidente à ces limites consiste à regrouper les fichiers de données en une seule entité

appelée ‘Bases de Données’, dont les données et les traitements sont indépendants.

2. Définition de base de données (BD)

Plusieurs définitions ont été proposées, en peut citer entre autres les définitions suivantes :

Selon Gardarin [1], une base de données est un ensemble de données modélisant les objets

d’une partie du monde réel et servant de support à une application informatique. Pour mériter

le terme de base de données, un ensemble de données non indépendantes doit être

interrogeable par le contenu, c’est-à-dire que l’on doit pouvoir retrouver tous les objets qui

satisfont à un certain critère.

Une Base de données est un ensemble structuré de données apparentées qui modélisent un

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

(administration, banque, université, hôpital, ...). Les BD ont une place essentielle dans

l'informatique [9].

Une Base de données (BD) est un ensemble cohérent, intégré, partagé de données structurées

défini pour les besoins d’une application [10].


Dr. Asma Saighi 3

Une Base de données est un gros ensemble d’informations structurées mémorisées sur un

support permanent [4].

3. Système de gestion de base de données

3.1. Définition

Un SGBD (en anglais DBMS pour Database Management System) est un logiciel système qui permet

de manipuler (insertion, suppression, mise à jour, recherche efficace) de grandes quantités de données

stockées dans une base de données. Ces données peuvent atteindre quelques milliards d’octets

partagée par de multiples utilisateurs simultanément. Les données stockées sont partagées en

interrogation et en mise à jour d’une manière transparente. D’autres fonctions complexes peuvent être

assurée par le SGBD telle que la protection des données partagées contre les incidents.

Contrairement aux systèmes de fichiers, les SGBD permettent de décrire les données de manière

séparée de leur utilisation et de retrouver les caractéristiques d’un type de données à partir de son nom

(par exemple, comment est décrit un article). La Figure 1.1 représente l’architecture générale d’un

SGBD.

Figure 1.1. Architecture Générale d’un SGBD [11].

3.2. Niveaux d’abstraction

Un objectif majeur des SGBD est d’assurer une abstraction des données stockées sur disques pour

simplifier la vision des utilisateurs [1]. Pour cela, trois niveaux de description de données ont été

définis :

- Niveau conceptuel : Le niveau conceptuel, également appelé niveau logique, est le niveau central.

Le schéma conceptuel permet de décrire l’ensemble des données de l’entreprise. Exemple :

nom, prénom, adresse, etc. C’est une définition logique de la BD (représentation) via le modèle

de données et est faite par l’administrateur de la BD qui identifie et décri les regroupements

de données et leurs interactions.

- Niveau interne : Ce niveau appelé aussi niveau physique permet la gestion : du stockage des

données sur des supports physiques, des structures de mémorisation (fichiers) et d'accès

(gestion des index, des clés, etc.).


Dr. Asma Saighi 4

- Niveau externe : Ce niveau est aussi appelé niveau vue, c’est le plus haut niveau d’abstraction

de la base de données. A la différence du niveau conceptuel et interne dont les schémas

décrivent toute une base de données, les schémas externes décrivent seulement la partie des

données présentant un intérêt pour un utilisateur ou un groupe d’utilisateurs. En d’autres

termes, le niveau externe associe à un utilisateur ou à un groupe d’utilisateurs une vue partielle

du monde réel. En conséquence, il y a plusieurs vues d’une même BDD.

Note : Pour une BDD, la description conceptuelle et le schéma interne sont uniques. Par contre,

plusieurs schémas externes en général peuvent être définis.

La Figure 1.2 présente les trois schémas d’une BD centralisée.

Figure 1.2. Les trois niveaux de schémas.

3.3. Objectifs des SGBD

L’objectif essentiel d’un SGBD est de garantir l’indépendance des données par rapport aux

programmes (i.e. possibilité de modifier les schémas conceptuel et interne des données sans modifier

les programmes). Le but est d’éviter une maintenance coûteuse des programmes lors des modifications

des structures logiques et physiques des données. Par cela, on trouve l’indépendance physique et logique

En plus, pour assurer une meilleure indépendance des programmes aux données il est important de

manipuler les données aussi bien en interrogation qu’on mise à jour via des langages. De ce fait, l’accès

aux données restent invisibles aux programmes d’application. Les descriptions de données sont établies

par les administrateurs des données, donc, le SGBD doit faciliter cette tâche.

Lorsque le SGBD met en commun les données d’une entreprise dans une BD, et que plusieurs

utilisateurs accèdent simultanément aux données, il est nécessaire de garantir : l’efficacité des accès, le

partage des données, la protection de la BD contre les mises à jour erronées ou non autorisées et la sécurité

des données en cas de panne.

En résumé, voici les objectifs des SGBD :

Indépendance physique : permettre le changement du schéma physique (modifier

l'organisation physique des fichiers, d’ajouter ou supprimer des méthodes d'accès) sans

changer le schéma conceptuel. Cela présente deux avantages : le fait de ne pas manipuler des

entités complexes rend les programmes d’application plus simples à écrire, la modification des


Dr. Asma Saighi 5

applications n’est pas obligatoire dans le cas de modification des caractéristiques du niveau

physique.

Indépendance logique : possibilité de modification du niveau conceptuel sans changement

du schéma externe. Cela a deux avantages : pour les programmes d’application du niveau vue,

il n’est pas nécessaire d’avoir une vue globale de l’entreprise. En outre, en cas de modification

du schéma du niveau logique, les applications du niveau vue sont réécrites seulement si cette

modification entraine celle de la vue.

Manipulation des données : permettre à tous types d’utilisateurs d’accéder à la base selon

leurs besoins et connaissances. Par conséquent, un ou plusieurs :

- Administrateurs de la base doivent avoir la possibilité de décrire les données aux niveaux

interne et logique,

- Développeurs d’applications écrivent des programmes d’application pour les utilisateurs

finaux ou pour eux-mêmes, cela est à partir du niveau conceptuel ou externe,

- Utilisateurs peuvent manipuler les données via un langage simple dont ils ont besoin.

Administration facilitée des données :

En effet, la centralisation des descriptions de données faites par un groupe spécialisé entraine

une difficulté d’organisation. Le SGBD permet de décentraliser cette description à travers des

outils. Pour cela, un dictionnaire de données dynamique peut aider les concepteurs de BD.

Redondance contrôlée des données :

La suppression des données redondantes permet d’assurer la cohérence de l'information ainsi

que la simplification des mises à jour. En effet, avec les BD réparties, il est préférable de gérer

par le système des copies multiples de données. L’objectif est d’optimiser les performances en

interrogation tout en évitant les transferts sur le réseau et permettre le parallélisme des accès.

Conséquemment, parfois la redondance gérée par le SGBD est nécessaire spécialement au

niveau physique des données.

Par contre, il faut éliminer la redondance anarchique qui force les programmes utilisateurs à

mettre à jour une même donnée plusieurs fois. Donc, il faut bien contrôler la redondance.

Cohérence des données :

Dans une approche BD, les données ne sont pas indépendantes (i.e. il peut exister certaines

dépendances entre ces données). En d’autres termes, souvent une donnée ne peut pas prendre

une valeur quelconque.

Exemples : une note doit être supérieur ou égale à 0 et ne doit pas dépasser 20, un salaire

mensuel doit être supérieur à 40 000 Dinar et doit raisonnablement rester inférieur à 200 000

Dinar.


Dr. Asma Saighi 6

Un SGBD doit veiller à ce que ces règles soient respectées par les applications lors des

modifications des données et ainsi assurer la cohérence des données. Ces règles sont appelées

contraintes d’intégrité.

Partage des données :

L’objectif est ici de permettre le partage des données entre différents utilisateurs et

applications. Un utilisateur n'a pas à se soucier si quelqu'un d'autre travaille sur les mêmes

informations au même moment et peut accéder aux données en consultation ou en mise à jour

comme s'il était seul. Le système doit gérer les conflits en refusant ou en retardant

éventuellement un ou plusieurs accès. Il faut assurer que le résultat d’une exécution simultanée

de transactions est le même que celui d’une exécution séquentielle. Par exemple, ne pas

autoriser la réservation du même siège pour deux passagers différents.

Sécurité des données :

La sécurité des données consiste à :

- Refuser les accès aux personnes non autorisées ou mal intentionnés. Le système doit

présenter un mécanisme de vérification des droits d'accès aux objets de la base.

Par exemple : un employé peut connaître seulement les salaires des personnes qu’il

dirige mais pas le salaire des autres employés de l’entreprise.

- Protéger les données contre les pannes. Le système doit garantir des reprises après

panne tout en restaurant la BD dans le dernier état cohérent avant la panne.

Efficacité des accès aux données :

Les Entrées/Sorties disque reste problématique dans les systèmes de BD car une E/S disque

coûtent quelques dizaines de millisecondes. L’interrogation et la mise à jour de la BD en

utilisant des langages non procéduraux très puissants est une autre limite. Le SGBD prend en

charge l’optimisation du coût et le nombre d’accès (E/S) d’une requête

Avant qu’une BDD prend sa forme finale (i.e. une forme utilisable par un SGBD), il faut passer par

une étape de conception afin de décrire les objets de la réalité ainsi que les relations entre ces objets.

Pour cela, la modélisation à travers des modèles est nécessaire.

4. Types de modèles de données

4.1. Modèle sémantique

Le modèle sémantique est parmi les modèles de bases de données les moins courants. Il comprend

des informations sur la façon dont les données stockées sont rattachées au monde réel.

4.2. Modèle Entité-Association

Le modèle Entité-Association (EA) en français, ER en anglais (Entity Relationship) permet de décrire

l'aspect conceptuel des données à l’aide d’entités et d’associations.


Dr. Asma Saighi 7

Le modèle entité-association, permet de modéliser des situations du monde réel décrites en langage

naturel. Les noms correspondent aux entités, les verbes aux associations et les adjectifs ou

compléments aux propriétés.

Par exemple : Le client a commandé un produit, ici deux entités sont identifiées : client et produit (voir

Figure 1.6).

Le paragraphe suivant décrit les composant du diagramme entité/association.

Entité

Une entité correspond à un objet du monde réel (matériel ou immatériel) défini en général par un nom

(e.g., voiture, commande, etc.). Ces entités sont identifiables de manière unique, interagissent et font

ou subissent des actions. La Figure 3 montre la représentation schématique d’une entité.

Par exemple : voiture, étudiant, patient, etc.

Nom de l’entité

Liste des propriétés

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

Type d’entité

Le type d’entité désigne un ensemble d’entités ayant une sémantique et des propriétés communes (voir

Figure 4).

Par exemple : Mohammed a prêté le livre d’Ahmed. Mohammed et Ahmed sont des entités de type

entité Personne car ils ont les mêmes caractéristiques et le livre est une entité du type entité Livre.

Notons que par abus de langage, on dit entité au lieu de type d’entité et c’est les types d’entité qui sont

représentés dans le modèle et non pas les entités.

Figure 1.4. Modélisation d’un type entité.

Attribut, valeur

Un attribut ou une propriété d’une entité ou d’une association caractérisée par un nom et un type.

Par exemples : nom d'une personne, titre d'un livre, puissance d'une voiture, etc.

Chaque attribut possède un domaine qui définit l'ensemble des valeurs qui peuvent être choisies pour

lui (entier, chaîne de caractères, booléen…). Au niveau de l'entité, chaque attribut possède une valeur

compatible avec son domaine.

Identifiant ou clé

Un identifiant (ou clé) d'un type entité ou d'un type association est l’ensemble minimum d’attributs

qui identifient d’une manière unique une entité. Donc, il n’est pas possible qu’un identifiant d'un type

Personne


Dr. Asma Saighi 8

entité ou type association prenne la même valeur pour deux entités (respectivement deux associations)

distinctes.

Par exemple : numéro de client pour un client, code article pour un article, le code ISBN d’un livre

pour un livre, numéro de sécurité sociale pour une personne.

Figure 1.5. Modélisation d’un type entité avec identifiant.

Association

Une association (ou une relation) est un lien entre plusieurs entités.

Un type association (ou type relation) désigne un ensemble de relations qui possèdent les caractéristiques

semblables et permet de décrire un lien entre plusieurs type entité.

Attention ! C’est par abus de langage qu’on utilise le mot association en lieu de type-association.

Cependant, il ne faut pas confondre entre les deux concepts.

L’association est représentée par une ellipse. Une association peut avoir des propriétés particulières.

Par exemple, l’association Commander a la propriété Date commande (voir figure 1.6).

Par exemple :

Type-association : un adhérant emprunte un livre.

Association : Ali a emprunté le livre des Bases de données, etc.

Figure 1.6. Exemple d’une association.

Cardinalité

La cardinalité reliant un type d’association et un type d’entité représente le nombre de fois minimal et

maximal de participations de chaque occurrence d’entité à une association.

La cardinalité minimale est 0 ou 1 et doit être inférieure ou égale à la cardinalité maximale et la

cardinalité maximale est toujours 1 ou n. Donc, les cardinalités admises peuvent être expliquées

comme suit :

Personne

NSS Nom Prénom DDN


Dr. Asma Saighi 9

0,n : Une occurrence du type entité peut exister mais n’est pas impliquée dans aucune

association et peut être impliquée, sans limitation, dans plusieurs associations.

Figure 1.7. Exemple de cardinalité 0,n.

1,1 : Une occurrence du type entité ne peut exister que si elle est impliquée dans exactement

(au moins et au plus) une association.

Figure 1.8. Exemple de cardinalité 1,1.

0,1 : Une occurrence du type entité ne peut exister que si elle est impliquée dans au moins une

association.

Figure 1.9. Exemple de cardinalité 0,1.

1,n : Une occurrence du type entité ne peut exister que si elle est impliquée dans au moins une

association.

Figure 1.10. Exemple de cardinalité 1,n.

Dimension

La dimension d'un type-association est le nombre de types-entités qui y participent. On distingue :

- Les associations binaires qui relient 2 entités.

- Les associations ternaires qui relient 3 entités.

- Les associations n-aires qui relie plus de trois entités.

- Les associations réflexives qui relient une seule entité (e.g. Personne mariée à une personne).

4.3. Modèle hiérarchique

Le modèle hiérarchique (ou arbre) organise les données dans une structure arborescente, où chaque

enregistrement n’a qu’un seul parent (racine). Les enregistrements frères et sœurs sont triés dans un

ordre particulier. Ce modèle convient à la description de plusieurs relations du monde réel. Exemple :

ADABASE (1970), System 2000 (1967).


Dr. Asma Saighi 10

4.4. Modèle réseau

Modèle réseau ou graphe est un modèle hiérarchique étendu qui autorise relations transverses (i.e.

relations plusieurs-à-plusieurs entre des enregistrements liés). Un enregistrement peut être un membre

ou un enfant dans plusieurs ensembles. Cela permet de traduire des relations complexes. Ex : TOTAL

(1978).

4.5. Modèle relationnel

Dans le modèle relationnel, les informations décomposées et organisées sont stockées dans des tables. Exemple : 80% des SGBD sont relationnelles, ORACLE (85% du marché), DB2, SQL Server, ACCESS, etc. Le schéma relationnel est l'ensemble des RELATIONS qui modélisent le monde réel ; tel que les relations représentent les entités du monde réel (par exemple : des personnes, des objets, etc.) ou les associations entre ces entités.

Figure 1.11. Modèles de données. Le passage d'un schéma conceptuel E-A à un schéma relationnel se fait suivant des règles qui seront présentées dans le prochain chapitre.

Figure 1.12. Transformation du modèle E/A au modèle relationnel [13].

Dr. Asma Saighi

11

Chapitre 2 Modèle relationnel

1. Introduction au modèle relationnel

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

L’objectif était de surmonter les lacunes des modèles hiérarchique et réseaux qui ne sont pas capables

de gérer les BD volumineuses, ne garantissent pas l’intégrité de données, ne traitent pas les

redondances de données, etc. Les Systèmes de Gestion de Base de données Relationnelles (SGBD)

commerciaux sont apparus dans les années 80 avec des outils tel qu’ORACLE.

Le modèle relationnel est un modèle facile, simple et qui repose sur des bases théoriques solides : la

théorie des ensembles et la logique des prédicats du premier ordre. Ce modèle permet : l’amélioration

de l’indépendance logique et physique ainsi que l’amélioration de l’intégrité et de la confidentialité, la

proposition de schémas de données faciles à utiliser, l’optimisation de l’accès à la BD, etc.

1.1. Définition du modèle relationnel

Dans ce modèle, un concept unique représente les objets et les associations, c’est la relation. Les

relations sont des tableaux à deux dimensions appelées Tables. Dans une table, une ligne correspond à

un enregistrement et une colonne à un champ de cet enregistrement. Des opérateurs de l’algèbre

relationnel permettent la manipulation des données et l’ensemble de contraintes d’intégrité définissent

l’état cohérent de la base.

Exemple :

Enseignant

NEns Nom Prénom Département

20 Nasri Ahmed Marketing

40 Taleb Mohammed Informatique

200 Hadad Omar Comptabilité

Le nom de la relation de l’exemple ci-dessus est Enseignant, cette relation décrit les enseignants tel

que chaque ligne de la table correspond à une occurrence.

Par exemple : <40, Taleb, Mohammed, Informatique>.

1.2. Concepts de base (attribut, Tuple, domaine, relation)

Attribut : Le nom donné à une colonne d’une relation est appelé attribut. L’attribut prend sa

valeur dans un domaine. Par exemple : NEns et Nom sont des attributs de la relation

Enseignant.


12 Dr. Asma Saighi

Tuples : Les lignes d’une table sont appelées n-uplet ou tuples en anglais.

Domaine : Un domaine est un ensemble de valeurs que peut prendre un attribut. Cet ensemble

de valeurs peut être finies ou infinies (e.g., chaine de caractère, entier, etc.).

Par exemple : Le domaine des couleurs primaires : DC= {rouge, bleu, jaune}.

Le domaine des booléens : DB= {0,1}.

Relation : Une relation est un ensemble de tuples déterminé par un nom et qui peut être

exprimée en intension ou en extension. En d’autres termes, une relation est un sous-ensemble

du produit cartésien d’une liste de domaines caractérisé par un nom.

Exemple de relation :

Client

1.3. Schéma de relation

Le schéma de la relation est présenté par le nom de la relation suivi par les attributs et leurs domaines

d’application (i.e. les tuples de la relation ne sont pas listées). La clé de la relation est constituée d’un

sous-ensemble des attributs de la relation. Ce sous-ensemble sera souligné dans le schéma.

Exemple : CLIENT (N0Cli : entier, Nom : CC, Prénom : CC, Datnais : Date).

Souvent, le schéma de relation se limite au nom de la relation suivi de ses attributs pour des raisons

d’allégement d’écriture.

Exemple : CLIENT (N0Cli, Nom, Prénom, Datnais).

Notons que le schéma d’une relation représente son intention (i.e. les propriétés communes et

invariantes des tuples qu’elle va contenir).

Les tuples de la relation présentée en extension sont visibles (i.e. listés). La relation dans ce cas est

présentée sous forme de tableau. Notons que :

Le degré de la relation indique son nombre d’attributs (e.g., le degré de la relation Client est 4).

Le nombre de tuples d’une relation représente la cardinalité de cette relation (e.g., la cardinalité

de la relation Client est 2).

1.4. Passage du modèle Entité/ Association au modèle relationnel

Le passage du modèle E/A vers le relationnel est possible via des règles de transformation.

Règle 1 : Toute entité devient une relation dans laquelle l’identifiant de l’entité devient clé primaire de

la relation et les attributs de l'entité deviennent attributs de la relation (voir l’exemple dans la figure

2.1).

N0Cli Nom Prénom Datnais

101 Talbi Ahmed 02/03/2000

102 Dali Mohammed 05/06/1999


13 Dr. Asma Saighi

Figure 2.1. Exemple d’application de la règle1 de transformation.

Règle 2 : Une association binaire de cardinalité 1,1 devient une relation portant dans la relation fille

la clé primaire de la relation mère et l'attribut ajouté s'appelle clé étrangère dont le symbole est # (voir

l’exemple dans la figure 2.2).

Figure 2.2. Exemple d’application de la règle 2.

Règle 3 : Dans une association binaire de cardinalité plusieurs à plusieurs, chaque entité devient une

relation et une relation particulière est créée et aura comme clés étrangères les identifiants des 2 entités

associées en ajoutant la ou les propriétés éventuelles de cette relation.

Figure 2.3. Exemple d’application de la règle 3.

Règle 4 : Une association de dimension supérieur à 2, s’écrit suivant la règle 3.

Membre

C-MEMBRE Nom Prenom DDN

Departement

C-DEP Nom Faculte

Membre

C-MEMBRE Nom Prenom DDN

Publication

C-DEP Nom Faculte

1,N 1,1 Travailler

Fille Membre (C-MEMBRE, Nom, Prenom, DDN, # C-DEP)

Mère Département (C-DEP, Nom, Faculte)

Clé étrangère

1,N 1,N Publi

Date-publi

Membre (C-MEMBRE, Nom, Prenom, DDN) Departement (C-DEP, Nom, Faculte)

Publi (# C-MEMBRE, # C-DEP, Date-publi)

,


14 Dr. Asma Saighi

2. Normalisation

La théorie de la normalisation consiste en la décomposition des informations en plusieurs relations en

se basant sur les dépendances fonctionnelles. Les buts de la normalisation sont :

Limitation des redondances des tuples, ce qui permet la réduction de l’espace de stockage.

Suppression des valeurs NULL et par conséquent, suppression des difficultés sur les jointures

et les fonctions d’agrégation.

Pour limiter les incohérences au sein des données.

Limitation des problèmes de mise à jour et amélioration des performances des traitements.

2.1. Dépendances fonctionnelles (DFs)

Les dépendances fonctionnelles (Functional dependency) permettent l’établissement des liens entre

attributs ou groupe d’attributs.

2.1.1. Définition

Soit R (A1, A2… An) avec n ≥2 un schéma de relation, soit X et Y deux sous-ensembles d’attribut de

{A1, A2… An} tel que X∩Y=A. On dit que X → Y (X détermine Y, ou Y dépend fonctionnellement

de X) si : Ɐ les tuples (x,y) et (x’,y’) de R, x=x’ → y=y’.

Par exemple : Type → Marque.

Cela veut dire qu’à chaque valeur de X dans R correspond une seule valeur de Y. Les DFs déterminent

les contraintes entre les attributs (e.g., deux individus peuvent avoir le même nom et le même prénom

mais jamais le même numéro). Les dépendances fonctionnelles sont spécifiées théoriquement par

l’administrateur puis contrôlées par les SGBD.

2.1.2. Propriétés des DFs

Les trois règles suivantes sont connues sous le nom d’axiomes d’Armstrong et permettent d’effectuer

des inférences de DFs à partir d’autre DFs :

Réflexivité : pour tout Y⊆ X ⇒ X → Y : tout ensemble d’attributs détermine lui-même ou une

partie de lui. Par exemple :

NE, NP → NE, NP

NE, NP → NE

Augmentation : si X → Y ⇒ X, Z → Y, Z ; si X détermine Y, alors les deux ensembles

d’attributs peuvent être enrichis par un même troisième.

Exemples :

1) Jour, Heure, sigleCours, noGroupe → Jour, Heure, codeProfesseur

Augmentation de : SigleCours, noGroupe → codeProfesseur


15 Dr. Asma Saighi

2) Titre_film→ Genre_Film

Par augmentation : Titre_Film, Année→ Genre_Film, Année

Transitivité : si X → Y et Y → Z ⇒ X → Z. Par exemple :

SigleCours, noGroupe → codeProfesseur et codeProfesseur → Local

Par transitivité : sigleCours, noGroupe → Local

Plusieurs autres règles peuvent être déduites de ces axiomes de base :

Union : si X → Y et X → Z ⇒ X → Y,Z.

Exemple : NumEtud → Nom, Prenom et NumEtud → AnneeEtud

Alors NumEtud → Nom, Prenom, AnneeEtud

Pseudo-transitivité : si X → Y et WY → Z ⇒ WX → Z.

Exemple : NumEns → Grade et Grade, NomEns → Salaire

Alors NumEns, NomEns → Salaire

Décomposition : si X → Y et Z ⊆ Y alors X → Z.

Exemple : Titre_Film → Genre_Film, Année

Par décomposition : Titre_Film → Genre_Film

2.1.3. Types des DFs

o DF triviale : X → Y est triviale si Y ⊆ X.

o DF élémentaire : X→ Y est élémentaire si Ɐ X’⊆ X, X’ ne détermine pas Y (i.e. Y ne dépend

pas d’une partie de X).

Exemple : NumProduit → NomProduit est une DF élémentaire.

NumFacture, NumProduit → NomProduit n’est pas élémentaire.

o DF canonique : une DF X→ Y est dite canonique si ca partie droite est réduite à un seul

attribut. Toute DFs qui n’est pas canonique peut être transformées par décomposition en DFs

canoniques. Par exemple : NumCli→NomCL, PrénomCl, AgeCl n’est pas canonique, par

décomposition, elle donne trois DFs canoniques :

NumCli →NomCL

NumCli →PrénomCl

NumCli →AgeCl

o DF directe : une DF X→ Y est dite directe si elle est élémentaire et Y ne dépend pas par

transitivité de X. Autrement dit, la dépendance entre X et Y ne peut pas être obtenue par

transitivité (∄ Z/ X→Z et Z →Y).


16 Dr. Asma Saighi

2.1.4. Graphe de Dépendances Fonctionnelles (GDF)

Le graphe des DFs permet la visualisation facile des DFs et d’isoler les DFs élémentaires. Les sommets

sont les attributs et les arcs sont les DFs.

Exemple :

N°pièce

prix-unit libellé catégorie

TVA

Figure 2.4. Graphe de dépendance fonctionnelle.

- Pièce (N°pièce, prix-unit, libellé, catégorie).

- Catégorie (catégorie, TVA).

2.2. Fermeture transitive (Transitive closure)

La fermeture transitive d’un ensemble F de DF est un ensemble notée F+ composé de F et de

l’ensemble des DFs déduits par transitivité.

Exemple : à partir de l’ensemble de DF :

F = {NV → TYPE ; TYPE → MARQUE ; TYPE → PUISSANCE}

On déduit la fermeture transitive : F+ = F ∪ {NV → MARQUE ; NV → PUISSANCE}.

Remarque : Si deux ensembles de DFs ont la même fermeture transitive, alors ils sont équivalents.

Par suite, il est intéressant de déterminer un sous-ensemble minimal de DFs permettant de générer

tout les autres. C’est la couverture minimale d’un ensemble de DFs [1].

2.3. Couverture minimale (Minimal cover)

La couverture minimale appelée aussi couverture irredondante et notée IRR(F) est un ensemble F de

DFEs (élémentaires) associé à un ensemble d’attributs vérifiant les propriétés suivantes :

- Aucune dépendance dans F n’est redondante. Cela signifie que pour toute DF f de F, F – {f}

n’est pas équivalent à F.

- Toute DFE des attributs est dans la fermeture transitive de F.

La couverture minimale est un sous-ensemble minimal de DFEs permettant de générer toutes les

autres.


17 Dr. Asma Saighi

Algorithme de calcul de la couverture minimale IRR(F) [5] :

Le rôle de cet algorithme est de détecter les DFs redondantes (i.e. les DFs qu’on peut déduire à partir

des autres DFs restantes en utilisant les axiome d’Amstrong).

Couverture(F); Begin IRR := F Remplacer Chaque DF X→(A1,...,An) par n DF X→A1,..., X→An Pour chaque DF : f =X → A dans IRR SI (IRR –{f}) Implique {f} Alors IRR = IRR -{f} FPOUR Return IRR Fin Algorithme détaillé de IRR(F) : Cet algorithme détaille la notion de redondance de DFs. On dit donc qu'une Df f est redondante si la fermeture de sa partie gauche sur l'ensemble des DFs restantes (F-f) comporte la partie droite de f. Cela signifie que pour une DF X→Y et même en enlevant f, le lien sémantique entre X et Y est conservé. Procédure Couverture Minimale(F:Ensemble de DF) Var CM : Ensemble de DF Début CM = F Décomposer les parties droites de DF Pour chaque DF f : X→Y MC snad Faire Début Calculer X+(F-{f}) Si Y est inclus dans X+ Alors CM=CM-{f} FPour Retourner CM Fin La couverture minimale va constituer un élément essentiel pour composer des relations sans perte

d’informations directement à partir des attributs.

2.4. Clé de relation

Dans le modèle relationnel, la clé de relation est un concept de base. En effet, La clé d’une relation R

est un ensemble minimal d’attributs de R permettant de déterminer tous les attributs de R. Une

définition plus formelle de la clé d’une relation est comme suit :

Soit R une relation ayant l’ensemble des attributs A et soit X un sous-ensemble de A. X est une clé de

R si Ɐ a∈ A, X→ a.


18 Dr. Asma Saighi

Clé minimale : Un sous-ensemble d’attributs X d’une relation R est dit clé minimale si :

X est une clé ;

Toute dépendance fonctionnelle X→ a de R est élémentaire.

Clé candidate/ primaire : Une relation R peut avoir plusieurs clés appelées clés candidates.

Parmi les clés candidates une est choisi comme en général comme clé primaire.

Superclé : Tout ensemble d’attributs incluant tous les attributs de la clé primaire est dit superclé.

Notons que l’ensemble de tous les attributs d’une relation est une superclé. En outre, la clé

primaire est une superclé.

Clé étrangère : Afin de permettre au SGBDR de maintenir la cohérence des lignes de deux

relations, la clé étrangère sert de lien entre deux relations d’une même BDD.

2.5. Contrainte d’intégrité

Lors des mises à jour des données d’une BD, le SGBD doit assurer la cohérence des données. Ces

données ne sont pas indépendantes, mais obéissent à des règles sémantiques appelées contraintes

d’intégrité [1].

Il existe différents types de contraintes d'intégrité :

Unicité de clé : une relation doit posséder une clé primaire dont la valeur est unique. C’est un

groupe d’attributs non nul dont la valeur permet de déterminer un tuple unique dans une table

Contrainte Référentielle : représente une association entre deux tables dites de clé étrangère

imposant que la valeur d'attribut de la relation R1 apparaît comme valeur de clé dans une autre

relation R2.

Contrainte de domaine : permet de restreindre les valeurs d’un domaine (e.g., une note doit être

comprise entre 0 et 20).

Contrainte de non nullité : la valeur d’un attribut doit être renseignée. Un attribut d’une clé ne doit

pas posséder de valeurs nulles (vides).

Exemple :

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

- Chaque OUVRAGE doit avoir au moins un auteur.

2.6. Formes normales

La normalisation sert à concevoir d’une manière correcte le schéma d’une base de données

relationnelle. Les trois premières formes normales ont pour objectif de permettre la décomposition


19 Dr. Asma Saighi

de relations sans perdre d’informations, à partir de la notion de dépendance fonctionnelle [6]. Chaque

forme normale est une progression vers des relations présentant moins de redondances.

Cette décomposition permet d’éliminer la redondance des données ainsi que les anomalies de mise à

jour.

Exemples :

Soit la relation universelle (i.e. relation qui regroupe toutes les informations à stocker) suivante :

Etudiant (NEtud, Nom, Prénom, Matière, Note)

NEtud Nom Prénom Matière Note

101 Nasri Ahmed Bases de données 10

101 Nasri Ahmed Réseaux 13

101 Nasri Ahmed Génie Logiciel 08

102 Haddad Omar Bases de données 09

102 Haddad Omar Réseaux 11

102 Haddad Omar Génie Logiciel 07

On remarque que le numéro de l’étudiant, son nom et son prénom, sont répétés autant de fois que

le nombre des matières ⇒ redondance des données.

Soit la relation : Livraison (N°fourn, adrF, N°prod, prixP, qté)

N°fourn adrF N°prod prixP qté

3 Annaba 52 65 100

22 Alger 10 15 50

22 Alger 33 10 120

3 Annaba 33 10 50

3 Constantine 10 15 200

Si un fournisseur change d’adresse et qu’un seul tuple est mis à jour, ou si un nouveau tuple

est inséré pour un fournisseur connu avec une adresse différente ⇒ incohérence.

Si un fournisseur n'a pas de livraison en cours ⇒ perte d’adresse.

La normalisation est un processus qui permet d’éviter la redondance, la perte et l’incohérence de données

dans le modèle relationnel.

2.6.1. Première Forme Normale (First normal form) 1FN

Une relation est en première forme normale si :

Elle possède une clé,

Tout attribut contient une valeur atomique (i.e. les attributs ne sont pas multivalués).

Notes : Un attribut est multivalué n’est qu’une relation dans une relation. Un attribut atomique n’a à

un instant donné qu’une seule valeur.


20 Dr. Asma Saighi

La 1FN consiste à éviter les domaines composés de plusieurs valeurs.

Exemple : Soit la relation Etudiant (NEtud, Matière, Nom, Prénom, Note).

Dans le cas où on prend en considération les notes de rattrapage, l’attribut Note aura deux valeurs.

Donc, la relation n’est pas en 1FN.

Afin que la relation Etudiant soit en 1FN, en décompose Note en : NoteExamen et NoteRattr.

On aura la relation : Etudiant (NEtud, Matière, Nom, Prénom, NoteExamen, NoteRattr).

2.6.2. Deuxième Forme Normale (Second normal form) 2FN

Une relation R est en deuxième forme normale si et seulement si :

Elle est en 1FN.

Toutes les DFs sont élémentaire (les attributs qui n’appartiennent pas à une clé ne dépendent

pas d’une partie de cette clé).

Afin de normaliser une relation en 2FN, il faut décomposer la relation R en plusieurs relations. La

décomposition se fait en plaçant les attributs qui violent la 2FN dans une nouvelle relation avec la clé

primaire ou la clé partielle.

Exemple : Soi la relation : Livraison (N°fourn, N°prod, prixP, qté).

Avec : N°prod → prixP

Cette relation n’est pas en 2FN car N°prod → prixP (partie de clé détermine un attribut non clé).

La relation sera donc décomposée en :

- Livraison (N°fourn, N°prod, qté)

- Produit (N°prod, prixP)

Notes :

Une relation en 2FN traduit le fait que les attributs non clé dépendent complétement de la

clé.

Une relation en 2FN peut contenir des informations redondantes. Donc, la 2FN ne permet

pas d’éviter toutes les redondances.

2.6.3. Troisième Forme Normale (Third nomal form) 3FN

Une relation R est en troisième forme normale si et seulement si :

Elle est en 2FN.

Tout attribut n’appartenant pas à la clé ne dépend pas d’un autre attribut non clé. Cela veut

dire que toutes les dépendances fonctionnelles sont directes.


21 Dr. Asma Saighi

Exemple 1 :

Soit : Enseignant (NumEns, Non, Prénom, Grade, ChargeHoraire).

Avec : Grade → ChargeHoraire. La relation n’est pas en 3FN car un attribut non clé détermine un

attribut non clé.

Cette relation doit être scindée en deux relations :

Enseignant (NumEns, Non, Prénom, Grade)

Charge (Grade, ChargeHoraire).

Exemple 2 :

Soit : R(Vol, AéroportDépart, Appareil, AéroportArrivée).

Avec : Vol, AéroportDépart → AéroportArrivée et Vol →Appareil.

R n’est pas en 2FN car une partie de la clé détermine un attribut non clé. La décomposition de R

donne : R1(Vol →Appareil) et R2(Vol, AéroportDépart, AéroportArrivée).

2.6.4. Forme Normale FN de Boyce-Codd BCNF (Boyce-Codd normal form)

La 3FN est insuffisante lorsqu’une relation admet plusieurs clés candidates, car des anomalies peuvent

avoir lieu. Pour cela, Boyce et Codd ont introduit la forme normale BCNF.

Une relation est en BCNF si et seulement si :

Elle est en 3FN.

Pour toute dépendances fonctionnelles, la partie gauche est une clé.

Exemple : Soit la relation R(A,B,C)

Avec : A,B →C et C→ A

Cette relation n’est pas en BCNF car le membre gauche de la DF C→ A n’est pas une clé. On

décompose la relation en : R1(B,C) et R2(A, C).

Cette décomposition ne préserve pas la DF A,B → C.


22 Dr. Asma Saighi

Notes :

- Il n’est pas toujours possible d’aboutir à une décomposition en BCNF sans perte

d’information et qui préserve les DFs.

- On mesure la qualité d'une relation par son degré de normalisation.

Figure 2.5. Formes normales.

2.7. Schéma de base de données

Le schéma d'une base de données est défini par l'ensemble des schémas des relations qui composent

la base de données. Le schéma de la BDR dit comment les données sont organisées dans la base [9].

Il indique quelles tables ou relations constituent la base de données, ainsi que les champs inclus dans

chaque table. Un schéma contient un groupe de tables, alors qu’une BD contient un groupe de

schémas (voir l’exemple dans la figure 2.4).

Figure 2.4. Différence entre Schéma de base de données et Base de données [12].


23 Dr. Asma Saighi

3. Modèle relationnel logique (SQL)

3.1. Table, colonne et ligne

RELATION = TABLE

A1 B1 C1 D1

A2 B2 C2 D2

A3 B3 C3 D3

A4 B4 C4 D4

ÉLÉMENT ou n-uplet = LIGNE

LIGNE

1 élément

On ne peut pas avoir 2 lignes identiques.

ATTRIBUT = COLONNE

COLONNE

Attribut ou Propriété

3.2. Description de SQL (Structured Query Langage)

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

standard. Il permet la communication avec les SGBDRs et a été défini par l’ANSI (American National

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

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 [3].

A1 B1 C1 D1

A1

A2

A3


24 Dr. Asma Saighi

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 [4].

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 [3].

SQL est un langage relationnel, c’est-à-dire, il manipule des tables via des requêtes et produisent des

tables.

Des versions différentes ont été proposées :

SQL 1 86 : la base.

SQL 1 89 : l’intégrité.

SQL 2 92 : la nouvelle norme.

SQL 3 99 : les évolutions objets.

SQL se compose de cinq parties (voir Tableau 2.1).

TCL Set Transaction, Commit, rollback

DDL Create Alter Drop

DML Insert

Update Delete Select

DCL Connect Grant

Revoke

Programmation SQL Declare, Fetch, Prepare, Describe, Execute

Tableau 2.1. Composants du langage SQL [5].

Data Definition Language (Langage de Définition des Données) est la partie du SQL

permettant la création des bases de données, etc.

Data Manipulation Language (Langage de Manipulation des Données) est la partie de

traitement des données (ajout, suppression, etc.).

Data Control Language (Langage de contrôle des Données).

TCL pour gestion des transactions.

La programmation SQL dynamique.

3.3. Définition de données

3.3.1. Création de table (CREATE)

La commande CREATE TABLE permet de créer les tables SQL comme suit :

CREATE TABLE <nom de table> (<élément de table>+)


25 Dr. Asma Saighi

Un élément de table est soit une définition de colonne ou de contrainte :

<ÉLÉMENT DE TABLE> ::= <DÉFINITION DE COLONNE> |

<CONTRAINTE DE TABLE>

La syntaxe de la définition de colonne est comme suit :

<DÉFINITION DE COLONNE> : := <NOM DE COLONNE> <TYPE DE DONNÉES>

[<CLAUSE DÉFAUT>] [<CONTRAINTE DE COLONNE>]

3.3.1.1. Contrainte de colonne

La contrainte de colonne peut être :

Interdictions des valeurs nulles (existence des valeurs) des attributs en utilisant le mot clé

NOT NULL.

Utilisation de la clause UNIQUE afin de s’assurer de l’unicité des valeurs des attributs (pas

de valeurs dupliquées dans les colonnes).

CHECK (condition) pour vérifier que l'attribut réalise la condition lors de l'insertion de n-

uplets.

PRIMARY KEY : Clé primaire.

DEFAULT value pour spécifier la valeur par défaut de l'attribut.

3.3.1.2. Contrainte table

La contrainte de table peut être :

FOREIGN KEY (colonne…) REFERENCES table [(colonne…)]

Soit les deux tables :

Personne Ordre

L’attribut "PersonID" est commun entre les deux tables et il est clé primaire dans la table "Personne".

L’attribut "PersonID" est une clé étrangère dans la table "Ordre"

La requête SQL suivante créer une clé étrangère dans la colonne "PersonID" lorsque la table "Ordre"

est créé :

CREATE TABLE Ordre (

OrdreID int NOT NULL,

NumeroOrdre int NOT NULL,

PersonID NomP PrénomP

1 Talbi Ali

2 Bendali Ahmed

3 Azizi Farid

OrdreID NumeroOrdre PersonID

1 77895 3

2 44678 3

3 22456 2

4 24562 1


26 Dr. Asma Saighi

PersonID int,

PRIMARY KEY (OrdreID),

FOREIGN KEY (PersonID) REFERENCES Personne(PersonID)

);

Les types de données utilisés lors de la création des tables en SQL dans ACCESS sont comme suit :

- Booléen : BIT - Nombre entier : SHORT(entier), SMALLINT(entier), LONG(enter 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) : taille variante, CHAR(n) ou Text(n) : n est le nombre de caractères.

Exemple 1 : CREATE TABLE AVION( Num_avion SMALLINT PRIMARY KEY, TYPE VARCHAR(10) NOT NULL, CONSTRUCTEUR VARCHAR(20) NOT NULL, CAPACITE SMALLINT CHECK (CAPACITE >0), COMPAGNIE CHAR(35) NOT NULL ); Exemple 2 : Create table realisateur ( id_real integer primary key, nom varchar(16) not null, prenom varchar(16) not null);

3.3.2. Modification de schéma (ALTER, DROP)

3.3.2.1. Suppression d’une table

Les tables SQL sont supprimées en utilisant la syntaxe suivante :

DROP TABLE nom_table ;

Exemple : DROP TABLE employé ;

La suppression d’une table permet d’éliminer :

o La structure de cette table.

o Toutes les données qu’elle contient.


27 Dr. Asma Saighi

o Les indexes associés.

Notes :

- Une clé étrangère d’une autre table ne doit pas référencer la table à supprimée.

- Les clés étrangères qui référencent la table à supprimée peuvent être supprimées en Oracle en

ajoutant le mot clé CASCADE à la fin.

3.3.2.2. Modification d’une table

L’instruction ALTER TABLE permet de modifier la structure des tables SQL en ajoutant ou en

modifiant la structure d’une table. La syntaxe est comme suit :

ALTER TABLE nom-table modification ;

Plusieurs situations peuvent avoir lieu comme suit :

Syntaxe Explication

ALTER TABLE nom_table ADD att type NOT NULL ;

Insérer à la table un attribut att qui contient des données correspondant à type. Exemple : ALTER TABLE utilisateur ADD adresse_rue VARCHAR(255) ;

ALTER TABLE nom_table RENAME COLUMN att TO nouvel att;

Changer le nom d’un attribut en un nouveau nom.

ALTER TABLE nom_table DROP COLUMN att ;

Supprimer un attribut de la table. Exemple : ALTER TABLE Etudiant DROP COLUMN Date-naiss ;

ALTER TABLE nom_table ADD CONSTRAINT nom contrainte ;

Ajouter une contrainte dans la table.

ALTER TABLE nom_table DROP PRIMARY KEY ;

Supprimer la clé primaire de la table.

ALTER TABLE nom_table DROP FOREIGN KEY nom clé ;

Supprimer la clé étrangère.

Tableau 2.2. Situations possibles de la commande ALTER TABLE.

L’instruction suivante permet le renommage d’une table :

RENAME ancien_nom TO nouveau_nom;

3.4. Manipulation des données sur les tables (INSERT, DELETE, UPDATE)


28 Dr. Asma Saighi

Le Langage de Manipulation des Données (Data Manipulation Language) est la partie du SQL qui

permet la manipulation des lignes des tables à travers trois instructions : INSERT INTO, UPDATE et

DELETE.

3.4.1. INSERT INTO

La commande INSERT INTO permet l’insertion de lignes dans une table. La syntaxe de la

commande d’insertion est comme suit :

INSERT INTO nom_table (nom_colonne_1, nom_colonne_2, ...) VALUES ('valeur 1', 'valeur 2', ...)

Dans le cas où la liste des noms de colonnes est omise (n’est pas spécifiée), tous les attributs de la

relation doivent être fournis dans l’ordre de déclaration. Si uniquement quelques colonnes sont

spécifiées, les autres sont insérées avec la valeur NULL.

Exemples :

- Insertion d’une seule ligne aves spécification des colonnes.

INSERT INTO client (prenom, nom, ville, age) VALUES ('Talbi', 'Ahmed', 'Boumerdes', 20)

- Insertion d’une seule ligne sans spécification des colonnes.

INSERT INTO client VALUES ('Talbi', 'Ahmed', 'Boumerdes', 20)

- Insertion de plusieurs lignes.

INSERT INTO client (prenom, nom, ville, age)

VALUES

('Talbi', 'Ahmed', 'Boumerdes', 20),

('Mansouri', 'Ridha', 'Oran', 24),

('Salem', 'Omar', 'Alger', 29)

3.4.2. UPDATE

La commande UPDATE permet la mise à jour (Modification des lignes) des valeurs d’attributs de

tuples existants. La modification peut concerner tous les tuples de la table en fournissant des valeurs

à changées, ou en élaborant des valeurs via une expression. On peut définir des conditions de mise à

jour dans une clause WHERE qui est une clause optionnelle. La syntaxe de la commande UPDATE

est la suivante :

UPDATE nom_table SET nom_col_1 = {expression_1 | ( SELECT ...) }, nom_col_2 = {expression_2 | ( SELECT ...) }, ... nom_col_n = {expression_n | ( SELECT ...) }


29 Dr. Asma Saighi

WHERE predicat

Exemple 1 :

Modifier l’email de l’auteur Ahmed Talbi.

UPDATE Auteur

SET Mail= '[email protected]'

WHERE Nom='Talbi' AND Prénom='Ahmed'

Exemple 2 :

Ajouter deux points aux étudiants qui ont une note inférieure à 8.

UPDATE Etudiant

SET note=note+2

WHERE note<8

3.4.3. DELETE

La commande DELETE permet d’enlever (supprimer) d’une relation des tuples existants. La

syntaxe de la commande est la suivante :

DELETE FROM nom_table WHERE condition

Tout les tuples pour lesquelles le prédicat est évalué à vrai sont supprimés. Par contre si la clause WHERE ne figure pas dans la commande, tous les tuples de la relation sont supprimés.

Exemple 1 :

Supprimer toutes les lignes de la table Etudiant. DELETE FROM Etudiant

Exemple 2 :

Supprimer les étudiants dont la Moyenne est inférieure à 10. DELETE FROM Etudiant WHERE Moy_etud< 10

30 Dr. Asma Saighi

Chapitre 3 : Algèbre relationnelle

1. Définition de l’algèbre relationnelle

L’algèbre relationnelle a été inventée par E. Codd comme une collection d’opérations formelles qui

agissent sur des relations et produisent les relations en résultats [1]. C’est un langage opérationnel, les

requêtes sont écrites comme une succession d’opérations effectuées sur des relations. Cet algèbre

permet la spécification des opérations à exécuter afin de calculer le résultat d’une requête.

Selon Gardarin [1], ces opérations sont classées selon leurs caractéristiques en :

Opérations de base permettent de déduire les autres. Il existe deux types d’opérations de base ;

opérations ensemblistes et opérations spécifiques.

Les opérations ensembliste ou binaire englobent : l’union, la différence et le produit cartésien.

Alors que les opérations spécifiques ou unaires concernent : la projection, la sélection, la thêta

jointure et la jointure naturelle.

Opérations dérivées (obtenues par combinaison des opérations de base). Parmi ses

opérations, on a : l’intersection, la jointure externe, la semi-jointure et la division.

2. Opérations de base

2.1. Opérations et opérateurs unaires (spécifiques)

Les opérations spécifiques sont les opérations unaires, cela veut dire que à partir d’une relation, une

autre est construite.

2.1.1. Sélection (Restriction)

La restriction (sélection) consiste à créer à partir d’une relation R1(A1, A2,.., An), une relation R2(A1,

A2,…,An) de même schéma, mais dont les tuples sont ceux de R1 vérifiant une condition C. Les

conditions sont du type :

<Attribut> <Opérateur> <Valeur>

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

Notations

σcondition (R1)

R1[Condition]

RESTRICT(R1, Condition)

Chapitre 3 Algèbre relationnelle

31 Dr. Asma Saighi

Notation graphique

Figure 3.1. Représentation graphique de la sélection.

Exemple : Soit la table Client en extension :

NumC Nom Age

101 Ahmed 38

102 Farid 20

103 Ali 25

104 Mohammed 40

σAge>30 (Client)

NumC Nom Age

101 Ahmed 38

104 Mohammed 40

2.1.2. Projection

La projection d’une relation R1(A1,…,An) sur les attributs (Ai,…,Am), m<n, consiste à créer une relation R2 de schéma Ai,…,Am obtenus en supprimant de la relation R1 les attributs non-mentionnés

en opérandes et en éliminant les tuples en double risquant d’apparaître dans la nouvelle table.

Notations

ΠA1, A2… Am (R1) R1 [Ai, Aj... Am]

PROJECT (R1, A1, A2,…, Am)

Notation graphique

Figure 3.2. Représentation graphique de la projection.


32 Dr. Asma Saighi

Exemple : Soit la relation R(Num, Nom, Tel)

En extension :

Num Nom Tel

101 Ahmed 021352638

102 Farid 021584231

ΠNom (R)

2.1.3. Thêta Jointure

La thêta Jointure de deux relations R1 et R2 selon une condition C, est une nouvelle relation R3. Le

schéma de R3 est la concaténation des attributs de R1 et R2 et les tuples sont ceux du produit cartésien

entre R1 et R2 vérifiant la condition C. La condition C est de la forme :

<Attribut> <Opérateur> <Valeur>

Les opérateurs peuvent être arithmétiques (=,<, ≤, ≥, >, ≠) ou logique (Et, Ou, Non).

La thêta-jointure est équivalente à un produit cartésien suivi d'une opération de sélection.

Lorsque l’opérateur est l’égalité « = », on parle alors d’une Equi-jointure sinon (i.e. si l’opérateur est

l’inégalité « ≠ ») c’est une Inéqui-jointure.

Notation

JOIN(R1,R2,Condition)

Représentation graphique

Figure 3.3. Représentation graphique de la jointure.

Exemple :

Soit les deux relations : Employé(NomE, SalaireE) et Chef(NomC, SalaireC).

Nom

Ahmed

Farid


33 Dr. Asma Saighi

Employé Chef

Employé Chef

Cette opération permet de répondre à la question : quels sont les noms des employés qui gagnent plus

que le chef.

2.1.4. Jointure naturelle

La jointure naturelle entre deux relations R1 et R2 qui n’ont pas forcément le même schéma, forme une troisième relation R3 dont les attributs sont obtenus par concaténation des attributs de R1 et R2. Les tuples de R3 sont ceux de R1 et de R2 ayant mêmes valeurs pour les attributs de même nom (respectant une équijointure entre les attribut communs). Ces attributs communs ne sont pas dupliqués dans la relation R3, mais fusionnés en une seule colonne.

Notation

JOIN(R1,R2)


Figure 3.4. Représentation graphique de la jointure naturelle.

Exemple :

Ami Cadeau

NomE SalaireE

Taleb 20000

Mansouri 10000

Benmohammed 6000

NomC SalaireC

Dali 25000

Tir 12000

NomE SalaireE NomC SalaireC

Taleb 20000 Tir 12000

Age Article Prix

40 Parfum 120

6 Montre 320

20 Garniture 82

Nom Prénom Age

Taleb Amira 6

Mansouri Ali 40

Benmohammed Farid 20

Merouani Mouna 6

SlaireE>SalaireC


34 Dr. Asma Saighi

R = Ami Cadeau

2.1.5. Traduction en SQL

2.1.5.1. Requêtes simples (SELECT-FROM)

L'opérateur de projection Π(A1,… An)(relation) se traduit en SQL par la requête :

SELECT A_1, ..., A_n FROM relation - SELECT : Indique la liste des attributs qui constituent le résultat. - FROM : Indique la (ou les) tables dans lesquelles se trouve les attributs utiles à la requête. - Lorsqu’on ajoute DISTINCT après SELECT cela permet de demander explicitement

l’élimination des doubles car SQL n’élimine pas les doubles, tandis que ALL est l’option par défaut.

2.1.5.2. Sélection de colonne (clause WHERE)

L'opérateur de sélection σ(condition)(relation) se traduit en SQL par la requête :

SELECT * FROM relation WHERE condition

WHERE permet de spécifier les critères de sélection. Cette clause indique les conditions que doivent

satisfaire les n-uplets pour faire partie du résultat. En SQL ces critères sont très riches, dans ce cours

nous nous contentons de présenter les plus utilisés. Notons que * veut dire toutes les colonnes.

Notons que l’omission de la clause WHERE nous permet d’afficher l'intégralité d'une table, et avoir

ainsi toutes les lignes et toutes les colonnes, on peut lister tous les attributs ou utiliser le caractère *

ayant la même signification.

Conditions possibles dans la clause WHERE :

Condition de base de comparaison à l'aide des opérateurs {=, ≠, <, >, <=, >=} :

Colonne =|<|>|<>|<=|>= Constante

Exemple :

SELECT *

FROM Pays

Nom Prénom Age Article Prix

Taleb Amira 6 Montre 320

Mansouri Ali 40 Parfum 120

Benmohammed Farid 20 Garniture 82

Merouani Mouna 6 Montre 320


35 Dr. Asma Saighi

WHERE population < 20 ;

Condition BETWEEN permettant de tester l’appartenance à un intervalle (i.e. obtenir une

recherche par intervalle). Notons que NOT BETWEEN est utilisé pour la négation.

- WHERE population >= 50 AND population <= 60

Équivalent à :

- WHERE population BETWEEN 50 AND 60

Condition LIKE de comparaison de chaînes de caractères.

Colonne [NOT] LIKE modèle de chaîne

Le modèle de chaîne peut contenir n’importe quel caractère et les caractères 'chaîne' contient des

caractères jokers (%, _)

Exemples :

Quel sont les produits dont le nom commence par 'P'.

SELECT *

FROM Produit

WHERE Desig Like "P%"

Autres exemples :

- WHERE pays LIKE ‘%lande’ -> Irlande, Islande, Finlande, Hollande

- WHERE pays LIKE ‘%ran%’ -> Iran, France

- WHERE pays LIKE ‘I_lande’ -> Irlande, Islande

Condition NULL de test de nullité permettant de vérifier si une colonne est nulle ou non, car en

pratique il est possible d’avoir des valeurs non définies (unknown).

Colonne IS [NOT] NULL

Exemple : Les commandes avec une quantité différente de NULL.

SELECT *

FROM COMMANDE

WHERE QTE IS NOT NULL ;

Condition IN permettant de tester l'appartenance à un ensemble de valeurs possibles. Cet ensemble

peut être spécifié explicitement ou peut être le résultat de sous-requêtes (i.e. requêtes imbriquées).

Notons que NOT IN indique la non appartenance à un ensemble.

Exemple 1 :


36 Dr. Asma Saighi

WHERE monnaie = ‘Euro’ OR monnaie = ‘Dollar’ OR monnaie = ‘Dinar’

Équivalent à :

WHERE monnaie IN (‘Euro’, ‘Dollar’, ‘Dinar’)

La condition EXISTS (sous-requête) renvoi vrai quand le résultat de la sous-requête n’est pas vide.

Le contraire NOT EXISTS (sous-requête).

Exemple : Soient les deux relations :

Fournisseur (FournisseurID, FournisseurNom, ContactNom, Address)

Produit (ProduitID, NomP, FournisseurID, CategoriID, Unite, Prix)

Donner les fournisseurs ayant fourni des produits dont le prix est inférieur à 20.

SELECT FournisseurNom

FROM Fournisseur

WHERE EXISTS (SELECT NomP FROM Produit WHERE Produit.FournissurID=

Fournissur.FournissurID AND Prix < 20);

2.1.5.3. Tri de résultats (ORDER BY)

La clause ORDER BY permet de trier les n-uplets du résultat d'une requête. Une liste d’attributs

servant de critère au tri doit suivre cette clause qui a la syntaxe suivante :

ORDER BY colonne [DESC]

Afin de trier en ordre descendant, on ajoute DESC après la liste des attributs et pour avoir un tri

ascendant, on ajoute ASC. Si l’ordre de tri n’est pas spécifié, par défaut les attributs seront triés en

ordre ascendant.

Exemple : Donner la liste des employés du département 10 par ordre décroissant de leur salaire.

SELECT Num_Dept, Nom_Emp, Salaire

FROM EMPLOYES

WHERE Num_Dept = 10 ORDER BY Salaire DESC;

Les opérations de thêta jointure et de la jointure naturelle seront traduites dans la section 5.5.

2.2. Opérations ensemblistes (binaires)

Les opérations ensemblistes sont des opérations binaires (i.e. à partir de deux relations, une troisième

relation peut être construite).

2.2.1. Union

L’union de deux relations R1 et R2 ayant le même schéma, est une nouvelle relation R. Les tuples de

la nouvelle relation R appartiennent à : R1 ou R2 ou bien R1 et R2.


37 Dr. Asma Saighi

Notation

R= R1 ⋃ R2

UNION(R1,R2)

APPEND (RELATION1, RELATION2)

Modélisation graphique

Relation1 Relation2

Figure 3.5. Représentation graphique de l’union.

Exemple : Soit les deux relations Acteur1 et Acteur2 dont l’extension est comme suit. Acteur1

Nom Prénom Datenaissance

DiCaprio Leonardo 11/11/1974

Spacey Kevin 26/ 07/1959

Eastwood Clint 31 /05/ 1930

Freeman Morgan 1/06/ 1937

Acteur2



Johnny Depp 9 /06/ 1963

Acteur1 ⋃Acteur2= UnionActeur


DiCaprio Leonardo 11/11/1974

Spacey Kevin 26/ 07/1959

Eastwood Clint 31 /05/ 1930


Johnny Depp 9 /06/ 1963

2.2.2. Différence

La différence est une opération qui porte sur deux relations R1 et R2 de même schéma servent à

construire une nouvelle relation R3 de même schéma. Les tuples de la relation résultante R3

appartiennent à R1 et n’appartiennent pas à R2. Il est à noter que l’ordre des relations opérandes est

important car la différence est un opérateur qui n’est pas commutatif (i.e. R2−R1≠R1−R2).

⋃

Résultat


38 Dr. Asma Saighi

Notation

R3= R1 – R2

DIFFERENCE (R1, R2)

REMOVE (R1, R2)

MINUS (R1, R2)


Relation1 Relation2

Figure 3.6. Représentation graphique de la différence.

Exemple :

Client1

NumCl NomCl Tel

101 Ahmed 032482564

102 Mohammed 021256687

Client2

NumCl NomCl Tel

102 Mohammed 021256687

Client1-Client2=DifClient

NumCl NomCl Tel

101 Ahmed 032482564

2.2.3. Produit cartésien (Cartesian product)

Le produit cartésien de deux relations R1 et R2 qui n’ont pas obligatoirement le même schéma, est

une relation R3. Le schéma de la relation R3 est constitué de la concaténation des attributs du schéma

de R1 et de R2 et dont les tuples sont constitués de toutes les combinaisons possibles entre les tuples

de R1 et les tuples de R2.

Notation

R1 X R2

PRODUCT (R1, R2)

TIMES (R1, R2)

-

Résultat


39 Dr. Asma Saighi


Relation1 Relation2

Figure 3.7. Représentation graphique du produit cartésien.

Exemple : Soit la relation : Pays (Nom, Capitale, Monnaie).

En extension :

Pays

Soit la relation : Monnaie (Num, Nom).

En extension :

Monnaie

Pays× Monnaie= ProduiPM

P.Nom Capitale Monnaie Num M.nom

Algérie Alger 1 1 Dinar Algérien

Maroc Rabat 6 1 Dinar Algérien

Algérie Alger 1 2 Euro

Maroc Rabat 6 2 Euro

Algérie Alger 1 6 Dirham

Maroc Rabat 6 6 Dirham

Remarque : Si les deux relations R1 et R2 ont un attribut de même nom, on renomme cet attribut ou

bien en spécifie le nom de la relation à laquelle il appartient (e.g., R1.A, R2.A).

3. Opérations dérivées

3.1. Intersection

L’intersection de deux relations de même schéma R1 et R2 est une nouvelle relation R3 de même

schéma ayant pour tuples ceux appartenant à R1 et à R2.

Nom Capitale Monnaie

Algérie Alger 1

Maroc Rabat 6

Num Nom

1 Dinar Algérien

2 Euro

6 Dirham

×

Résultat


40 Dr. Asma Saighi

Notation

R1 ∩ R2

INTERSECT (R1, R2)

AND (R1, R2)


Relation1 Relation2

Figure 3.8. Représentation graphique de l’intersection.

Exemple : Considérant l’exemple suivant tiré de [2].

Deux bibliothèques B1 et B2 fusionnent et décident de rechercher les livres qu’elles ont en commun

pour n’en garder un seul exemplaire. Chaque bibliothèque possède une relation LivreB1 et LivreB2.

LivreB1

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

LivreB2

NLIV TITRE NOMAUT

101 La basilique de Paris Victorien


210 Le roi de la forêt Aiglon


LivreB2 ∩ LivreB2= INTERLIVRE

NLIV TITRE NOMAUT



3.2. Jointure externe

∩

Résultat


41 Dr. Asma Saighi

La jointure externe entre deux relations R1 et R2 de schéma quelconque est une relation R3 dont le

schéma est la concaténation des attributs de R1 et de ceux de R2 en ne représentant les attributs ayant

le même nom qu’une seule fois. Les tuples de R3 sont ceux obtenus avec une jointure naturelle entre

R1 et R2 et ceux de R 1 et de R2 ne participants pas à la jointure en représentant par des valeurs nulles

ceux de l’autre relation.

Dans le but de conserver les tuples quand les relations jointes n’ont pas de projection identique sur

l’attribut de jointure, Codd a introduit les jointures externes qui sont nécessaires en particulier pour

composer des vues sans perte d’informations.

La jointure externe entre deux relations R1 et R2 génère une relation R3 qui a comme :

Attributs la concaténation des attributs de R1 et de R2 en ne représentant les attributs commun

qu’une seule fois.

Tuples ceux obtenus avec une jointure naturelle entre R1 et R2 et ceux des deux relations

initiales R 1 et R2 ne participants pas à la jointure en représentant par des valeurs nulles ceux

de l’autre relation.

Notation

EXT-JOIN(R1,R2)


Figure 3.9. Représentation graphique de la jointure externe.

Notons que pour la jointure externe gauche notée LEXT-JOIN, on garde tous les n-uplets de la

première table (gauche). Tandis que pour la jointure externe droite notée REXT-JOIN, on garde tous

les n-uplets de la deuxième table (droite).

Exemple : prenant l’exemple de deux tables Client et Commande dont l’attribut commun est le

numéro du client.

Client

NumClient NomClient PrénomClient NumTel

1001 Mansouri Ali 032482223

1002 Ahmed Salem 032485760

1003 Mohammed Talbi 032488080

1004 Yacine Hafsa 032486666

Commande

NumCommande NumClient Total

C001 1001 1800,00

C002 1003 2364,76


42 Dr. Asma Saighi

C003 1001 3000,00

Jointure externe de Client et Commande

NumClient NomClient PrénomClient NumTel NumCommande Total

1001 Mansouri Ali 032482223 C001 1800,00

1002 Ahmed Salem 032485760 - -

1003 Mohammed Talbi 032488080 C002 2364,76

1001 Mansouri Ali 032482223 C003 3000,00

1004 Yacine Hafsa 032486666 - -

Comme les clients 1002 et 1004 n’ont pas de commandes dans la table Commande, les attributs

NumCommande et Total prennent la valeur NULLE. Cette opération a permis de garder les clients

sans commande. Donc, elle est utile en pratique.

3.3. Semi jointure

La semi-jointure de deux relationsR1 et R2 est une relation R3 ayant le schéma de R1 et les tuples sont

ceux de R1 participant à la jointure naturelle entre R1 et R2. Ici encore, R1et R2 sont de schéma de

quelconque.

Notation

R1 R2

SEMI-JOIN (R1, R2)

Présentation graphique

R1 R2

Figure 3.10. Modélisation graphique de la semi jointure.

Exemple :

Client



1002 Ahmed Salem 032485760


1004 Yacine Hafsa 032486666

Commande

Résultat


43 Dr. Asma Saighi

NumCommande NumClient Total

C001 1001 1800,00

C002 1003 2364,76

Client Commande




Note : Lorsqu’il n’est pas nécessaire de conserver tous les attributs des deux relations (i.e. seuls les

attributs d’une des deux relations sont conservés en résultat), la semi-jointure est très utile afin

d’optimiser l’évaluation des questions.

3.4. Division

C’est une opération portant sur deux relations R1 et R2, telles que le schéma de R2 est inclus strictement dans celui de R1. La relation générée R3 aura le schéma de R1 moins les attributs de R2 et est formée de tous les tuples qui concaténés à chaque tuple de R2 donnent toujours un tuple de R1. La relation R2 ne peut pas être vide. Si R1 est vide, la relation résultante de la division est vide.

L’intérêt de cette opération réside dans le fait qu’elle permet de rechercher dans une table les sous-tables qui sont complétées par tous ceux d’une autre table et de répondre à des requêtes de la forme : « quel que soit X, trouver Y ».

Notation

R1/R2

DIVISION(R1,R2)


Relation1 Relation2

Figure 3.11. Modélisation graphique de la division.

Exemple :

Pièce Nature

Nom

Vis

Clou

Nom Couleur Poids Ville

Clou noir 20 Constantine

Clou bleu 25 Alger

Vis noir 20 Constantine

Boulon rouge 23 Oran

÷

Résultat


44 Dr. Asma Saighi

Pièce ÷ Nature= PNature

Couleur Poids Ville

Noir 20 Constantine

Bleu 25 Alger

4. Le langage algébrique

Le langage algébrique est un langage d’interrogation de BDD. Les opérations de base de l’algèbre

relationnelle constituent un langage complet. 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 et les feuilles correspondent aux relations. L’objectif est de mieux comprendre

l’optimisation logique de requête.

Exemple : tiré des sources de la société Oracle.

EMP(ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO).

DEPT(DNO, DNOM, DIR, VILLE).

Donner les numéros des employés travaillant à Boston.

R= ΠENO(EMP⋈σVILLE=’Boston’(DEPT)).

L’arbre algébrique est :

Figure 3.12. Arbre algébrique correspondant à l’exemple.

5. Traduction en SQL

Vis bleu 25 Alger

Boulon vert 18 Constantine


45 Dr. Asma Saighi

Cette section présentera la traduction en SQL des opérations ensemblistes (binaires) et des

opérations dérivées.

5.1. Union

L'opérateur d'union permet de fusionner deux tables ayant le même schéma et se traduit tout

simplement en SQL par la requête :

SELECT * FROM relation_1 UNION SELECT * FROM relation_2

5.2. Intersection

L'opérateur d'intersection permet d’obtenir les lignes communes de deux tables et se traduit en SQL

comme suit :

SELECT * FROM relation_1 INTERSECT SELECT * FROM relation_2 Exemple : Soient les relations Etudiant (NumEtud, ENom, DateN)

Cours (NumCour, CNom, Ens)

Examen (NumEtud, NumCour, Note)

Donner les numéros des étudiants qui ont soutenu l'examen de numéro cours 02 et aussi l'examen de numéro cours03.

SELECT NumEtud FROM Examen WHERE NumCour = 02

INTERSECT

SELECT NumEtud FROM Examen WHER NumCour = 03 ;

5.3. Différence

L'opérateur MINUS permet d’effectuer la différence entre deux tables et se traduit SQL par la requête

suivante :

SELECT * FROM relation_1 EXCEPT SELECT * FROM relation_2 Exemple : Donner les numéros des étudiants qui ont soutenu l'examen de Numéro Cours 02 mais qui n'ont pas

soutenu l'examen de Numéro Cours 03.

(SELECT NumEtud FROM Examen WHERE NumCour = 02) EXCEPT (SELECT NumEtud FROM Examen WHERE NumCour = 03) ;


46 Dr. Asma Saighi

5.4. Produit cartésien (sans jointure)

Le produit cartésien Relation1 × Relation2 se traduit en SQL par une simple requête entre deux tables comme suit :

SELECT * FROM relation_1, relation_2

Il peut aussi s'écrire en utilisant le mot-clé JOIN destiné aux jointures comme suit :

SELECT * FROM table_1 CROSS JOIN table_2 Remarque : SQL ne comporte pas d’opérateurs spécifiques permettant de réaliser directement une

division. Néanmoins, il est possible d’exprimer la sémantique de la division en utilisant les opérateurs

logiques.

5.5. Jointure de tables (condition de jointure)

La requête SELECT avec une seule relation dans la clause FROM permet de réaliser des sélections et des projections. Par contre, avec plusieurs relations dans la clause FROM réalise une jointure (et produit cartésiens). Afin d’exprimer des jointures avec SQL, il existe plusieurs manières dont la syntaxe

globale possible est la suivante :

table_1 [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 ON predicat [...]

table_1 [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 USING (colonnes) [...]

table_1 NATURAL [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 [...]

5.5.1. Thêta jointure

La thêta-jointure est exprimée avec une condition sur les colonnes communes des deux tables dans la clause Where en utilisant une requête simple. La thêta-jointure utilise n’importe quel opérateur de comparaison pour joindre deux tables.

SELECT att1, att2,... FROM table1 INNERJOIN table2

ON table1.attx 𝜃 table2.attx [WHERE autres_conditions];

𝜃 est un opérateur parmi =,<, ≤, ≥, >, ≠, like, ...

La condition de jointure table1.attx 𝜃 table2.attx est exprimée avec le mot ON.

Les autres conditions sont appliquées après la condition de jointure.

Lorsque la thêta-jointure utilise seulement la condition d’égalité, elle devient une équi-jointure.

Ou plus simplement : SELECT ... FROM <table gauche>


47 Dr. Asma Saighi

[INNER]JOIN <table droite> ON <condition de jointure>

Exemple 1 :

SELECT CLI_NOM, TEL_NUMERO FROM T_CLIENT INNER JOIN T_TELEPHONE ON T_CLIENT.CLI_ID = T_TELEPHONE.CLI_ID

Ou en utilisant le sur nommage :

SELECT CLI_NOM, TEL_NUMERO FROM T_CLIENT C INNER JOIN T_TELEPHONE T ON C.CLI_ID = T.CLI_ID

Exemple 2 : SELECT * FROM film, realisateur WHERE film.id_real = realisateur.id_real; SELECT * FROM film JOIN realisateur ON film.id_real = realisateur.id_real; SELECT * FROM film INNER JOIN realisateur ON film.id_real = realisateur.id_real;

Exemple 3 :

Les numéros des fournisseurs et des clients qui sont situés dans la même ville. L’attribut ville est commun entre les deux relations.

SELECT NumF, NumC FROM FOURNISSEUR, CLIENT WHERE FOURNISSEUR.VILLE = CLIENT.VILLE;

5.5.2. Jointure naturelle

La jointure naturelle peut être exprimée en utilisant la clause NATURAL JOIN comme suit :

SELECT ... FROM <table gauche> NATURAL JOIN <table droite> [USING <noms de colonnes>] Exemple : Exemple tiré de [3]. Soient les deux relations : Realisateur(id_real, nom, prenom) et Film(id_film, id_real, titre). La jointure naturelle entre la table film et la table realisateur est comme suit:


48 Dr. Asma Saighi

SELECT * FROM film NATURAL JOIN realisateur; Il est possible aussi d’exprimer la jointure naturelle avec une thêta-jointure en mentionnant dans la clause USING les colonnes en commun comme suit : SELECT * FROM film JOIN realisateur USING (id_real); SELECT * FROM film INNER JOIN realisateur USING (id_real); 5.5.3. Jointure externe Les jointures externes à gauche, à droite et globale sont obtenues avec la clause: left | right | full outer join ...ON condition. La syntaxe est comme suit : SELECT ... FROM <table gauche> LEFT | RIGHT | FULL OUTER JOIN <table droite> ON condition de jointure Note: Les mots clé OUTER et INNER sont facultatifs.

Exemple : Donner les avions, y compris ceux qui ne sont affectés à un vol.

SELECT Num FROM Avion LEFT OUTER JOIN Vol ON Avion.Num=Vol.Num; 6. Fonctions d’agrégat

Les fonctions d’agrégat sont un ensemble de fonctions qui permettent d’effectuer des statistiques sur

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

COUNT : nombre, etc.).

COUNT : permet de compter le nombre de valeurs d'un ensemble ;

SUM permet de sommer les valeurs d'un ensemble ;

AVG permet de calculer la valeur moyenne d'un ensemble ;

MAX permet de calculer la valeur maximale d'un ensemble ;

MIN permet de calculer la valeur minimale d'un ensemble.

Exemples :

Soit la base de données suivante qu’on va utiliser pour exemplifier les fonctions d’agrégat :

Emp (Eno, Ename, Title, City).

Project (Pno, Pname, Budget, City).

Pay(Title, Salary).

Works (Eno, Pno, Resp, Dur).


49 Dr. Asma Saighi

Q1. Quel est le nombre des employés Constantinois ?

SELECT COUNT(*) FROM Emp WHERE City='Constantine'; Q2. Quel est le plus grand salaire et le plus petit salaire de toutes les professions ?

SELECT MAX(Salary), MIN(Salary) FROM Pay;

Q3. Quels sont les noms des professions qui payent le plus et les salaires correspondant) ?

SELECT Title, Salary FROM Pay WHERE Salary = (SELECT MAX(Salary) FROM Pay); Q4. Donner les budgets totaux des budgets des projets d’Alger ?

SELECT SUM(Budget) FROM Project WHERE City = 'Alger'; Q4. Quel est le nombre de villes où il y a un projet avec l'employé E101?

SELECT COUNT (DISTINCT City) FROM Project, Works WHERE Project.Pno = Works.Pno AND Works.Eno = 'E101’; Q 5. Quels sont les noms des projets dont le budget est supérieur au budget moyen? SELECT Pname FROM Project WHERE Budget > (SELECT AVG(Budget) FROM Project); 7. Clause GROUP BY …HAVING

7.1. La clause GROUP BY

La clause GROUP BY permet de définir des groupes pour partitionner les n-uplets résultats en

fonction des valeurs de certains attributs en groupes homogènes.


50 Dr. Asma Saighi

Cette clause est utile lorsqu’on veut effectuer des calculs sur des groupes de lignes (e.g., le salaire moyen le plus bas par profession, etc.). Notons que la clause GROUP BY doit être utilisée lorsqu’en utilise

des calculs au niveau de la clause SELECT sinon, le résultat n’aura pas un sens.

Exemple : Donner le salaire moyen le plus bas par profession.

SELECT MIN(AVG(SAL)) FROM EMP GROUP BY PROF ;

7.2. La clause HAVING

La clause HAVING spécifie un filtre (i.e. condition de regroupement des n-uplets) sur les résultats en exprimant des conditions sur le groupe de lignes générés. Le but est de ne sélectionner que certains groupes. Cette clause se place après la clause GROUP BY.

Une requête de groupement qui comporte une clause GROUP BY, peut contenir une clause WHERE

et une clause HAVING. Dans ce cas : D’abord, la clause WHERE est appliquée afin de sélectionner

les lignes. Puis, les groupes seront constitués à partir des lignes sélectionnées, ensuite, les fonctions de

groupe seront évaluées. Enfin, et la clause HAVING sera enfin appliquée pour sélectionner les

groupes. La condition ne porte pas sur chaque tuple de la table comme pour le where mais sur

l’ensemble des tuples d’un groupe.

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING <condition>

Exemple 1 : Soit la table “Achat” qui contient les achats de différents clients avec le coût du panier

pour chaque achat.

id Client tarif date_achat

1 Ahmed 102 2018-10-23

2 Ali 47 2018-10-27

3 Asma 18 2018-11-05

4 Asma 20 2018-11-14

5 Ahmed 160 2018-12-03

Q1 . Donner la liste des clients qui ont commandé plus de 40 Dinar, toute commande confondue.

SELECT client, SUM(tarif) FROM achat GROUP BY client HAVING SUM(tarif) > 40; Résultat :

client SUM(tarif)

Ahmed 262

Ali 47

Exemple 2 :


51 Dr. Asma Saighi

Soit la relation : Pays(Nom, Capital, Population, Surface, Continent).

Q 2. Donner la population totale par continent, tel que chaque continent a une population > 50.

SELECT continent, SUM(population) FROM PAYS GROUP BY continent HAVING SUM(pop) > 50 ;

51

Série d’exercices 1

Modèle Entité/Association et

Passage du modèle E/A au modèle Relationnel

Question : Donner deux avantages d’un SGBD par rapport à un système de gestion de fichiers

classique.

Exercice 1 :

Un avion a un numéro d'immatriculation, un type et une localisation (la ville de l'aéroport d'attache de

l'avion). Chaque type d'avion est décrit par son nom (Boeing 747, Airbus A340 ...), son poids, sa

capacité et son rayon d'action. Un technicien de la compagnie a un nom, un matricule, une adresse (la

ville de résidence), un numéro de téléphone, un salaire et est expert sur un ou plusieurs types d'avion

pendant une période donnée (date début et date fin). Un pilote est décrit par les mêmes attributs

qu'un technicien. De plus il doit passer un examen médical annuel. Chaque avion doit également passer

un certain nombre de tests de bon fonctionnement. Chaque test a un numéro qui l'identifie, un nom

et une valeur minimale (un seuil à atteindre). Nous souhaitons conserver la date et l'état de chacun des

tests. Chaque vol est commandé par un seul pilote et concerne un seul avion. Un vol a une ville de

départ (ville_dep) une ville d'arrivée (ville_arr) et une heure de départ (h_dep) une heure d'arrivée

(h_arr)

Q 1. Proposer un schéma conceptuel des données (modèle entités associations). Ne pas oublier les

cardinalités et de souligner les clés.

Q 2. Traduire le modèle obtenu en un modèle relationnel.

Exercice 2 :

Cantine Scolaire : La cuisine centrale à Montpellier voudrait gérer les données relatives à la cantine

scolaire à l'aide d'une base de données relationnelle. Elle explique que le prix du repas dépend de la

tranche dans laquelle l'enfant se situe et du type d'école (jardin d'enfant, maternelle, primaire). La

tranche est définie en fonction du quotient familial. Chaque enfant à une carte de cantine personnelle

avec un numéro. Les familles approvisionnent la carte d'un certain montant. La cuisine centrale

voudrait enregistrer tous les paiements journaliers, puis par la suite mettre à jour l'information du

montant total versé. Chaque jour, elle voudrait établir et archiver une liste des enfants ayant mangé à

la cantine ainsi que le menu du jour. Le menu est composé d'une entrée, d'un plat et d'un dessert.

Q 1. Elaborer le modèle entités associations.

Q 2. Traduire ce modèle en un modèle relationnel.

52

Exercice 3 :

Une entreprise veut améliorer sa gestion du matériel ainsi que celle des commandes de ces clients.

Pour cela, elle envisage les orientations suivantes :

Elle veut connaître à tout instant la quantité disponible d’un matériel dans un magasin donné. Les

matériaux sont classés en catégories pour faciliter leur gestion.

On doit pouvoir connaître les composants d’un matériel et les matériaux dans lesquels on trouve un

composant donné. Lors d’une rupture de stock, un matériel peut être remplacé par un matériel de

substitution.

Chaque client a un seuil maximal de commandes autorisé (droit d’approvisionnement) par catégorie

de matériel pour une période donnée. Un client ne peut s’approvisionner que dans un magasin et un

seul. Une commande est définie par un numéro : elle concerne un seul client et différents matériaux,

et précise la quantité commandée.

Q 1. Elaborer le modèle entités associations.


Exercice 4 :

Une société travaille avec des transporteurs et dessert 50 destinations. Lorsqu'une commande est

passée par un client, le responsable des expéditions se charge de l’enregistrer et de rechercher le

transporteur le plus adéquat pour transporter cette commande. Deux critères sont pris en compte

pour choisir le transporteur : la vitesse de livraison (i.e., distance entre le transporteur et le client) ou

le coût de livraison (i.e., tarifs (au km) de chaque transporteur concurrent). Note : la distance est

calculée ville à ville.

Dans le but de gagner du temps, le responsable des expéditions ainsi que son directeur ont été

convaincu de l'intérêt d'un système d'information. Ce dernier, permettra la gestion rapide des

commandes de la société.

Q 1. Donner le modèle entités associations.


53


Normalisation

Exercice 1 : Soit la relation :

COURS (NOMPROF, VILLE, DEPARTEMENT, NOMETUDIANT, AGE, COURS, NOTE) Soit l'ensemble des dépendances fonctionnelles initiales suivant : 1. NOMPROF → VILLE 2. VILLE → DEPARTEMENT 3. NOMPROF → DEPARTEMENT 4. NOMETUDIANT → AGE 5. NOMETUDIANT, COURS → NOTE 6. COURS → NOMPROF

Q. Quelle est la forme normale de la relation R ? Si elle n'est pas en 3FN proposer une décomposition

en 3FN.

Exercice 2 : La relation suivante décrit des commandes faites par des clients, avec les produits et quantités commandés par client. Commandes (NumCom, DateCom, NumCli, AdrCli, NumProd, Prix, Qte) a. Quelle est la clé a. de cette relation ? b. En quelle forme normale elle est ? c. La mettre en 3FN le cas échéant.

Exercice 3 :

Soit la relation Département (Mle-Etud, Note, Classe, Cours, Module, No-Ens, Nom-Ens, Nom-Etud, Nb-h) Avec les DFs : 1. Mle-Etud → Nom-Etud, Classe 2. No-Ens → Nom-Ens 3. Cours → Module 4. Cours, Module → Nb-h 5. Classe, Cours, Module → No-Ens, Nom-Ens 6. Mle-Etud, Cours, Module → Note

54

Questions : Q 1. Expliquer chaque DF. Q 2. Eliminer les DFs redondantes. Q 3. Normaliser la relation en 3FN. Indication : Montrer que si A → B et A, B → C alors A → C (donc A → B, C par groupement) Utiliser ce résultat pour la question b).

Exercice 4 : Soit la relation :

Projection (NoFilm, TitreFilm, DuréeFilm, NoSalle, CapacitéSalle, TypePlace, PrixPlace,

DateProjection, HeureDeb) exprime que le film NoFilm intitulé TitreFilm dure dureeFilm et est projeté

dans la salle NoSalle ayant une capacité Capacité places.

Le film est projeté à la date DateProjection à HeureDeb heures. Deux films peuvent avoir le même titre.

Le prix de la place est fonction de son type.

Question : En déduire un schéma de relations en 3FN.

Exercice 5 : Soit R (A ; B ; C ; D ; E ; F ; G) avec les dépendances fonctionnelles F = {A -> BC ; C -> DE ; F -> G}. Questions : (a) Calculez la fermeture de A ; AC ; B ; C ; F sous F. (b) Identifiez toutes les clés candidates de R.

55


Algèbre relationnel

Exercice 1 :

Soit la base de données relationnelle Appartement/ Ecole :

IMMEUBLE (ADI, NBETAGES, DATEC, PROP)

APPIM (ADI, NAPR, OCCUP, TYPE, SUPER, ETAGE)

PERSONNE (NOM, AGE, PROF, ADR, NAPR)

ÉCOLE (NOMEC, ADEC, NBCLASSES, DIR)

CLASSE (NOMEC, NCL, MAITRE, NBEL)

ENFANT (NOMP, PRENOM, AN, NOMEC, NCL)

Remarque : ADI : adresse d’immeuble, clé. NBETAGES : nombre d’étages d’un immeuble.

DATEC : date de construction. PROP : nom du propriétaire de l’immeuble qui est une personne

APPIM (Appartement). ADI : adresse d’immeuble. NAPR : numéro d’appartement.

OCCUP : occupant de l’appartement (nom de la personne). SUPER : superficie de l’appartement.

NOM : nom de personne, clé. PROF : profession de la personne (directeur d’école n’est pas une

profession). ADR : adresse de la résidence d’une personne. NBEL : nombre d’élèves dans la classe.

Question :

Exprimer les requêtes suivantes à l’aide de l’algèbre relationnelle.

1. Donner l’adresse des immeubles ayant plus de 10 étages et construits avant 1970.

2. Donner les noms des personnes qui habitent dans un immeuble dont ils sont propriétaires

(occupants et habitants).

3. Donner le nom et la profession des propriétaires d’immeubles où il y a des appartements

vides.

4. Donner la liste des occupants (nom, âge, profession) des immeubles possédés par Ahmed.

56

Exercice 2 : On considère la base de données BD AIRBASE suivante :

PILOTE (NUMPIL : D_NUMPIL, NOMPIL: D_NOMPIL, ADR : D_VILLE, SAL : D_SAL)

AVION (NUMAV : D_NUMAV, NOMAV : D_NOMAV, CAP : D_CAP, LOC : D_VILLE)

VOL (NUMVOL : D_NUMVOL, NUMPIL : D_NUMPIL, NUMAV : D_NUMAV, VILLE_DEP

: D_VILLE, VILLE_ARR : D_VILLE, H_DEP : D_HEURE, H_ARR : D_HEURE)

Q1 : Donnez la liste des avions dont la capacité est supérieure à 350 passagers.

Q2 : Quels sont les numéros et noms des avions localisés à Alger ?

Q3 : Quels sont les numéros des pilotes en service et les villes de départ de leurs vols ?

Q4 : Donnez toutes les informations sur les pilotes de la compagnie.

Q5 : Quel est le nom des pilotes domiciliés à Paris dont le salaire est supérieur à 15000 F ?

Q6 : Quels sont les avions (numéro et nom) localisés à Alger ou dont la capacité est inférieure à 350

passagers ?

Q7 : Liste des vols au départ de Constantine allant à Alger après 18 heures ?

Q8 : Quels sont les numéros des pilotes qui ne sont pas en service ?

Q9 : Quels sont les vols (numéro, ville de départ) effectués par les pilotes de numéro 100 et 204 ?

Q10 : Donnez le numéro des vols effectués au départ d’Alger par des pilotes Algérois ?

Q11 : Quels sont les vols effectués par un avion qui n'est pas localisé à Alger ?

Q12 : Quels sont les pilotes (numéro et nom) assurant au moins un vol au départ de Constantine avec

un avion de capacité supérieure à 300 places ?

Q13 : Quels sont les noms des pilotes domiciliés à Alger assurant un vol au départ de Constantine

avec un Airbus ?

Q14 : Quels sont les numéros des vols effectués par un pilote Algérois au départ ou à l'arrivée d’Alger

avec un avion localisé à Constantine ?

Q15 : Quels sont les pilotes (numéro et nom) habitant dans la même ville que le pilote Bendali ?

Q16 : Quels sont les numéros des pilotes en service différents de celui de Bendali ?

Q17 : Quelles sont les villes desservies à partir de la ville d'arrivée d'un vol au départ d’Alger ?

Q18 : Quels sont les appareils (leur numéro) localisés dans la même ville que l'avion numéro 100 ?

Q19 : Quels sont les numéros et noms des pilotes domiciliés dans la même ville que le pilote Dupont

et dont le salaire est supérieur à celui de Bendali ?

Q20 : Quels sont les numéros et noms des pilotes qui effectuent un vol au départ de leur ville de

résidence ?

Q21 : Y a-t-il des homonymes parmi les pilotes ? Si oui, donner leur numéro et nom.

57


SQL

Exercie 1 : Soit la base de données d’un festival de musique : Dans une représentation peut

participer un ou plusieurs musiciens. Un musicien ne peut participer qu’à une seule représentation.

Representation (Num_Rep , titre_Rep , lieu) Musicien (Num_mus , nom , Num_Rep #)

Programmer (Date , Num_Rep # , tarif)

Ecrire la commande SQL permettant de rechercher :

1. La liste des titres des représentations. 2. La liste des titres des représentations ayant lieu au « théâtre allissa ». 3. La liste des noms des musiciens et des titres et les titres des représentations auxquelles ils

participent. 4. La liste des titres des représentations, les lieux et les tarifs du 25/07/2008. 5. Le nombre des musiciens qui participent à la représentations n°20. 6. Les représentations et leurs dates dont le tarif ne dépasse pas 20DH. 7. Après un certain nombre de représentation, le directeur du festival a constaté que certains

musiciens participent à plusieurs présentations. Pourquoi la description proposée ne permet pas de traiter ce cas. Expliquer les opérations à faire pour résoudre ce problème.traduire les étapes de la question en SQL afficher les listes des représentations du musicien numéro 128.

Exercice 2 : Soit la base de données suivante :

Départements:( DNO, DNOM, DIR, VILLE) Employés: ( ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO#)

Exprimez en SQL les requêtes suivantes :

1. Donnez la liste des employés ayant une commission 2. Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque

emploi, par salaire décroissant 3. Donnez le salaire moyen des employés 4. Donnez le salaire moyen du département Production 5. Donnez les numéros de département et leur salaire maximum 6. Donnez les différentes professions et leur salaire moyen Donnez le salaire moyen par

profession le plus bas 7. Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen

58

Exercice 3 : Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :

EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif) COUREUR(NuméroCoureur, NomCoureur, #CodeEquipe, #CodePays) PAYS(CodePays, NomPays) TYPE_ETAPE(CodeType, LibelléType) ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, #CodeType) PARTICIPER(#NuméroCoureur, #NuméroEtape, TempsRéalisé) ATTRIBUER_BONIFICATION(#NuméroEtape, #NuméroCoureur, km, Rang,

NbSecondes)

Exprimez en SQL les requêtes suivantes :

1. Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ? 2. Quel est le nombre de kilomètres total du Tour de France 97 ? 3. Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"? 4. Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ? 5. Quels sont les noms des coureurs qui ont participé à toutes les étapes ? 6. Quel est le classement général des coureurs (nom, code équipe, code pays et temps des

coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ?

7. Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

Exercice 4 : Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d'une promotion d'étudiants:

ETUDIANT(N°Etudiant, Nom, Prénom) MATIERE(CodeMat, LibelléMat, CoeffMat) EVALUER(#N°Etudiant, #CodeMat, Date, Note)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par Exprimez en SQL les requêtes suivantes :

1. Quel est le nombre total d'étudiants ? 2. Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ? 3. Quelles sont les moyennes de chaque étudiant dans chacune des matières ? 4. Quelles sont les moyennes par matière ? 5. Quelle est la moyenne générale de chaque étudiant ? 6. Quelle est la moyenne générale de la promotion ? 7. Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne

générale de la promotion ?

59

Exercice 5 : Ci-après, on donne la représentation textuelle simplifiée d’une base de données concernant un cycle de formation destiné à des étudiants. Il regroupe un ensemble de matières. On considère que chaque enseignant n’enseigne qu’une seule matière et qu’à la fin du cycle de formation, une note par matière, est attribuée à chaque étudiant. D’autre part, les étudiants peuvent ne pas suivre les mêmes matières.

ETUDIANT(CodeEt, NomEt, DatnEt) MATIERE(CodeMat, NomMat, CoefMat) ENSEIGNANT(CodeEns, NomEns, GradeEns, #CodeMat) NOTE(#CodeEt, #CodeMat, note)

Ecrire les requêtes SQL permettant d’afficher :

1. Les informations relatives aux étudiants (Code, Nom et Date de naissance) selon l’ordre alphabétique croisant du nom

2. Les noms et les grades des enseignants de la matière dont le nom est ‘BD’. 3. La liste distincte formée des noms et les coefficients des différentes matières qui sont

enseignées par des enseignants de grade ‘Grd3’. 4. La liste des matières (Nom et Coefficient) qui sont suivies par l’étudiant de code ‘Et321’. 5. Le nombre d’enseignants de la matière dont le nom est ‘Informatique’

Exercice 6 : Soit la base de données intitulée « gestion_projet » permettant de gérer les projets relatifs au développement de logiciels. Elle est décrite par la représentation textuelle simplifiée suivante :

Developpeur (NumDev, NomDev, AdrDev, EmailDev, TelDev) Projet (NumProj, TitreProj, DateDeb, DateFin) Logiciel (CodLog, NomLog, PrixLog, #NumProj) Realisation (#NumProj, #NumDev)

Ecrire les requêtes SQL permettant :

1. D’afficher les noms et les prix des logiciels appartenant au projet ayant comme titre « gestion de stock », triés dans l’ordre décroissant des prix

2. D’afficher le total des prix des logiciels du projet numéro 10. Lors de l’affichage, le titre de la colonne sera « cours total du projet ».

3. Afficher le nombre de développeurs qui ont participé au projet intitulé « gestion de stock » 4. Afficher les projets qui ont plus que 5 logiciels 5. Les numéros et noms des développeurs qui ont participés dans tous les projets. 6. Les numéros de projets dans lesquelles tous les développeurs y participent dans sa réalisation

bases de données · 2020. 4. 3. · fichiers (e.g., assembleur, cobol). 1.2. intérêt...

Documents