BASES DE DONNEES

I- Concepts de base

I-1 Définition de la base données

Une BD est une collection de données persistantes, structurées, reliées entre elles, stockées
sur supports numériques centralisés ou distribués, servant pour les besoins d'une ou
plusieurs applications, interrogeables et modifiables par un ou plusieurs utilisateurs.

I-2 Exemple : Compagnie aérienne

Une base de données constituée pour la gestion de l'activité d'une compagnie aérienne. C'est
une collectuon d'information concernant les voyageurs, les vols, les avions, le personnel, les
réservations, etc. Une telle base de données pourrait permettre la gestion des réservations,
des disponibilités des avions en fonction des vols à effectuer, des affectation des personnels
volants, etc.

I-3- Système de gestion de fichiers vs bases de données

Avant l'avènement des bases de données, chaque application informatique dans l'entreprise
impliquait sa propre équipe de développement, ses propres supports physiques, ses propres
fichiers, ses propres normes, ses propres langages, etc.
Conséquences :
L'existence conjointe et croissante de ces applications indépendantes avait des effets
négatifs, tels que :
a- La multiplication des tâches de saisie, de développement et de support informatique
b- La redondance anarchique des informations dans les fichiers
c- L'incohérence des versions simultanées de fichiers
d- La non-portabilité des traitements en raison des différences dans les formats et langages.
e- La multiplication des coûts de développement et de maintenance des applications.
Les données des fichiers sont décrites dans les programmes, d'où leur dépendance de
ces derniers.
Apport des bases de données :
Les données de la base de données sont décrites hors des programmes dans la base elle-
même. Elles sont donnes indépendants des programmes, ce qui permet à plusieurs
applications de partager les mêmes données.

I-4 Système de Gestion de bases de données (SGBD)

I-4-1 Définition

Un Système de Gestion de Bases de Données est un logiciel qui prend en charge la

structuration, le stockage, la mise à jour et la maintenance d'une base de données. Il est
l'unique interface entre les informaticiens et les données (définition des schémas,
programmation des applications), ainsi qu'entre les utilisateurs et les données (consultation
et mise à jour).

I-4-2 Exemples de SGBD

1- Oracle est un SGBD relationnel (et Relationnel-Objet dans ses dernières versions) très
reconnu pour les applications professionnelles.

2- SQL Server est un SGBD relationnel conçu et diffusé par Microsoft et qui se présente
comme un concurrent à Oracle pour les applications professionnelles.

3- MySQL est un SGBD relationnel libre (licence GPL et commerciale), simple d'accès et
très utilisé pour la réalisation de sites Web dynamiques. Depuis la version 4 MySQL
implémente la plupart des fonctions attendues d'un SGBD relationnel.

4- PosgreSQL est un SGBD relationnel et relationnel-objet très puissant qui offre une
alternative open-source aux solutions commerciales comme Oracle ou IBM.

5- Access est un SGBD relationnel Microsoft, qui offre une interface conviviale permettant
de concevoir rapidement des applications de petite envergure ou de réaliser des prototypes à
moindre frais.

I-4-3 Objectifs des SGBD

1- Indépendance physique des données

Le changement des modalités de stockage de l'information (optimisation, réorganisation,
segmentation, etc.) n'implique pas de changements des programmes.

2- Indépendance logique des données

L'évolution de la structure d'une partie des données n'influe pas sur l'ensemble des données.

3- Manipulation des données par des non-informaticiens

L'utilisateur n'a pas à savoir comment l'information est stockée et calculée par la machine,
mais juste à pouvoir la rechercher et la mettre à jour à travers des IHM [Interface Homme
Machine] ou des langages assertionnels simples.

4- Administration facilitée des données

Le SGBD fournit un ensemble d'outils (dictionnaire de données, audit, tunning, statistiques,
etc.) pour améliorer les performance et optimiser les stockages.

5- Optimisation de l'accès aux données

Les temps de réponse et de débits globaux sont optimisés en fonctions des questions posées
à la BD.

6- Contrôle de cohérence (intégrité sémantique) des données

Le SGBD doit assurer à tout instant que les données respectent les règles d'intégrité qui
leurs sont imposées.

7- Partageabilité des données

Les données sont simultanéments consultables et modifiables.

8- Sécurité des données

La confidentialité des données est assurée par des systèmes d'authentification, de droits
d'accès, de cryptage des mots de passe, etc.

9- Sûreté des données

La persistence des données, même en cas de panne, est assurée, grâce typiquememnt à des
sauvegardes et des journaux qui gardent une trace persistante des opérations effectuées.

I-5 Notion de modèle de données

Un modèle de données est un ensemble de concepts et de règles de composition de ces
concepts permettant de décrire des données.
Un modèle est souvent représenté au moyen d'un formalisme graphique permettant de
décrire les données (ou plus précisément les types de données) et les relations entre les
données.
On distingue trois niveaux de modélisation pour les bases de données :
1- Le modèle conceptuel
Il permet de décrire le réel selon une approche ontologique, sans prendre en compte les
contraintes techniques.

2- Le modèle logique
Il permet de décrire une solution, en prenant une orientation informatique générale (type de
SGBD typiquement), mais indépendamment de choix d'implémentation précis.

3- Le modèle physique
Il correspond aux choix techniques, en terme de SGBD choisi et de sa mise en œuvre
(programmation, optimisation, etc.).

Exemple de formalisme de modélisation conceptuelle

1- Le modèle Entité-Association a été le plus répendu dans le cadre de la conception de
bases de données.
2- Le modèle UML, qui se généralise pour la conception en informatique, se fonde sur une
approche objet.

Exemple de formalisme de modélisation logique

1- Le modèle relationnel est le modèle dominant.
2- Le modèle relationnel-objet (adaptation des modèles relationnel et objet au cadre des
SGBD) est actuellement en
pleine croissance.
3- Le modèle objet "pur" reste majoritairement au stade expérimental et de la recherche.
4- Des modèles plus anciens (hiérarchique, réseau, etc.) ne sont plus guère utilisés
aujourd'hui.

I-6 Notion de schéma de données

Un Schéma de données est une description, au moyen d'un langage formel, d'un ensemble
de données dans le contexte d'une base de données.
Un schéma permet de décrire la structure d'une base de données, en décrivant
l'ensemble des types de données de la base. L'occurence d'une base de données est
constituée de l'ensemble des données correspondant aux types du schéma de la base.
I-7 Notion de langage de données

Le langage de données est un Langage informatique permettant de décrire et de manipuler

les schémas d'une BD d'une une manière assimilable par la machine.
Exemple : SQL
SQL est le langage orienté données consacré aux SGBD relationnels et relationnels-objet.
Un langage de données peut être décomposé en trois sous langages :
1- Le Langage de Définition de Données
Le LDD [Langage de Définition de Données] permet d'implémenter le schéma conceptuel
(notion de table en SQL) et les schémas externes (notion de vue en SQL).

2- Le Langage de Contrôle de Données

Le LCD [Langage de Contrôle de Données] permet d'implémenter les droits que les
utilisateurs ont sur les données et participe donc à la définition des schémas externes.

3- Le Langage de Manipulation de Données

Le LMD [Langage de Manipulation de Données] permet l'interrogation et la mise à jour des
données. C'est la partie du langage indispensable pour exploiter la BD et réaliser les
applications.

Exemple : Définition de données en SQL

CREATE TABLE Etudiant (NumEtu : integer,
Nom : string,
Ville : string)
Cette instruction permet de créer une relation "Etudiant" comportant les propriétés
"NumEtu", "Nom" et "Ville".

Exemple : Contrôle de données en SQL

GRANT ALL PRIVILEGES ON Etudiant FOR 'Utilisateur'
Cette instruction permet de donner tous les droits à l'utilisateur "Utilisateur" sur la relation
"Etudiant".
Exemple : Manipulation de données en SQL
SELECT Nom FROM Etudiant WHERE Ville = 'Compiègne'
Cette instruction permet de rechercher les noms de tous les étudiants habitant la ville de
Compiègne.

I-8 Notion d'administration de données

Administrateur
Personne ou groupe de personnes responsables de la définition des différents niveaux de
schéma.
On distingue un type d'administrateur par niveau de schéma :
1- L'administrateur entreprise est en charge de la gestion du schéma conceptuel et des
règles de contrôle des données.

2- L'administrateur de données est en charge de la gestion des schémas externes et de leur

correspondance avec le schéma conceptuel.

3- L'administrateur base de données est en charge de la gestion du schéma interne et de sa

correspondance avec le schéma conceptuel.

Dictionnaire des données

Le dictionnaire de données d'un SGBD contient les informations relatives aux schémas et
aux droits de toutes les bases de données existantes au sein de ce SGBD. Il s'agit d'un outil
fondamental pour les administrateurs.
Les dictionnaires de données sont généralement implémentés sous la forme d'une base de
données particulière du SGBD, ce qui permet de gérer les données relatives aux bases de
données de la même façon que les autres données de l'entreprise.

II- Conception d'une base de données

II-1 Etapes de conception d'une base de données

Les étapes de la conception d'une base de données sont les suivantes :

1. Analyse de la situation existante et des besoins
2. Création d'une série de modèles conceptuels qui permettent de représenter tous les
aspects importants du problème
3. Traduction des modèles conceptuels en modèle logique et optimisation (normalisation) de
ce modèle logique
4. Implémentation d'une base de données dans un SGBD, à partir du modèle logique
L'importance de l'étape d'analyse
La première étape de la conception repose sur l'analyse de l'existant et des besoins. De la
qualité de la réalisation de cette première étape dépendra ensuite la pertinence de la base de
données par rapports aux usages. Cette première étape est donc essentielle et doit être menée
avec soins. La perception de l'existant et des besoins reste une étape qui repose
essentiellement sur l'expertise d'analyse de l'ingénieur.

L'importance de l'étape de modélisation conceptuelle

Etant donnée une analyse des besoins correctement réalisée, la seconde étape consiste à la
traduire selon un modèle conceptuel. Le modèle conceptuel étant formel, il va permettre de
passer d'une spécification en langage naturel, et donc soumise à interprétation, à une
spécification non ambigüe. Le recours aux formalismes de modélisation tels que Entité-
Association ou UML est donc une aide fondamentale pour parvenir à une représentation qui
ne sera plus liée à l'interprétation du lecteur.

Les étape suivantes sont plus mécaniques, dans la mesure où un modèle logique est déduit
de façon systématique du modèle conceptuel et que l'implémentation logicielle est
également réalisée par traduction directe du modèle logique.

II-2 Modélisation conceptuelle : Merise / modèle entité-association (E-A)

La méthode MERISE a pour fondement le modèle E-A, qui a fait son succès.
Les principales caractéristiques du modèle E-A sont :
1- Une représentation graphique simple et naturelle
2- Une puissance d'expression élevée pour un nombre de symboles raisonnables
3- Une lecture accessible à tous et donc un bon outil de dialogue entre les acteurs techniques
et non techniques
4- Une formalisation non ambigüe et donc un bon outil de spécification détaillée

III-2-1 Le Modèle conceptuel de données (MCD)

Le MCD est l'élément le plus connu de MERISE et certainement le plus utile. Il permet
d'établir une représentation claire des données du SI [Système d'Information] et définit les
dépendances des données entre elles.

III-2-1-1 Entité
Une entité est un objet du monde réel avec une existence indépendante.
Une entité est une chose (concrète ou abstraite) qui existe et est distinguable des autres
entités. L'occurrence d’une entité est un élément particulier correspondant à l’entité et
associé à un élément du réel. Chaque entité a des propriétés (ou attributs) qui la décrivent.
Chaque attribut est associé à un domaine de valeur. Une occurence a des valeurs pour
chacun de ses attributs, dans le domaine correspondant.

Exemple d'entité :

III-2-1-2 Relation
Une association (ou type d’association) représente un lien quelconque entre différentes
entités.
Une occurrence d’une association est un élément particulier de l’association constitué d’une
et une seule occurrence des objets participants à l’association. On peut définir des attributs
sur les associations. Le degré d'une association est le nombre d'entités y participant (on
parlera notamment d'association binaire lorsque deux entités sont concernées).

III-2-1-3 Cardinalité d'une association

Pour les associations binaires la cardinalité minimale (resp. maximale) d'une association est
le nombre minimum (resp. maximum) d'occurrences de l'entité d'arrivée associées à une
occurrence de l'entité de départ.

Remarque : Trois grands types de cardinalité

1- Les associations 1:N (qui incluent les association 0,1:N)

2- Les associations N:M
3- Les associations 1:1 (qui incluent les association 0,1:1)
Les autres associations peuvent toujours être ramenées à des associations N:M (dans le cas
général) ou à plusieurs associations 1:N (cas des associations 2:N ou 3:N par exemple).

III-2-1-4 Modèle Conceptuel de Données

Voici un exemple de diagramme MCD

III-2-1-5 Le passage E-A vers Relationnel

Afin de pouvoir implémenter une base de données, il faut pouvoir traduire le modèle
conceptuel en modèle logique. Cela
signifie qu'il faut pouvoir convertir un modèle E-A ou un modèle UML en modèle
relationnel. Dans les deux cas, E-A ou
UML, les modèles conceptuels sont suffisamment formels pour ce passage soit systématisé.
Nous étudions à présent les
règles permettant de réaliser ce passage, en nous concentrant sur le passage du modèle E-A
au modèle relationnel. Les règles
de passage de UML au modèle relationnel seront suffisamment similaires pour ne pas être
détaillées.
Transformation des entités
Entité identifiée
Pour chaque entité (identifiée) E, on crée une relation R dont le schéma est celui de l'entité.
La clé primaire de R est une des clés de E.

Entité non identifiée

Pour chaque entité non identifiée I ayant un identifiant étranger E, on crée une relation R qui
comprend tous les attributs de I, ainsi que les attributs clés de la relation correspondant à E.
La clé de R est la concaténation de la clé locale de I et de la clé de E.
Transformation des associations
Association 1:N
Pour chaque association binaire A de type 1:N (le cas échéant 0,1:N) entre les entités S et T
(représentés par les relations RS et RT respectivement) on inclut dans la définition de RS
comme clé étrangère la clé de RT ainsi que tous les attributs simples de A.
Association M:N et associations de degré supérieur à 2
Pour chaque association binaire A de type M:N ou pour chaque association A de degré
supérieur à 2, on crée une nouvelle relation RA pour représenter A. On met dans RA comme
clé étrangère, les clés de toutes les relations correspondant aux entités participant à A et dont
la concaténation formera sa clé. On ajoute également à RA (et éventuellement dans sa clé
pour les attributs clés) les attributs définis sur A.
Association 1:1
Pour chaque association binaire A de type 1,1:1,1 ou 1,1:1,0 entre les entités S et T
(représentées par les relations RS et RT respectivement) on substitut dans la définition de
RS l'éventuelle clé primaire (si RS était identifiée) par la clé primaire de RT qui est
également définie comme clé étrangère vers RT. On ajoute également à RS l'ensemble
des attributs de A. Notons que dans le cas d'une association 1,1:1,1 RT peut-être choisie à la
place de RS pour accueillir la clé étrangère.
Pour chaque association binaire A de type 0,1:0,1 entre les entités S et T (représentées par
les relations RS et RT respectivement) on inclut dans la définition de RS comme clé
étrangère la clé de RT ainsi que tous les attributs simples de A. La clé étrangère vers RT
incluse dans RS est également définie comme étant unique, afin d'assurer la cardinalité
maximum de 1. Notons qu'il n'était pas possible dans ce cas 0,1:0,1 que la clé étrangère vers
RT incluse dans RS soit également la clé primaire, car la cardinalité de 0 supposerait que la
clé primaire puisse être "null", ce qui est illégal pour une clé.

Exemple de passage E-A vers relationnel

III-3 Modélisation UML : Diagramme des classes
III-3-1 Diagramme des classes

Si le modèle dominant en conception de bases de données a longtemps été le modèle E-A, le

modèle UML se généralise de plus en plus. Nous ne donnons ici (parmi l'ensemble des
outils d'UML) qu'un aperçu du diagramme de classes, qui plus est limité aux aspects
particulièrement utilisés en modélisation de bases de données.

Classes :
Une classe est un type abstrait caractérisé par des propriétés (attributs et méthodes)
communes à un ensemble d'objets et permettant de créer des instances de ces objets, ayant
ces propriétés.

Héritage
L'héritage est relation entre deux classes permettant d'exprimer que l'une est plus générale
que l'autre. L'héritage implique une transmission automatique des propriétés (attributs et
méthodes) d'une classe A à une classe A'.
Dire que A' hérite de A équivaut à dire que A' est une sous-classe de A. On peut également
dire que A est une généralisation de A' et que A' est une spécialisation de A.

Association
Une association est une relation logique entre deux classes (association binaire) ou plus
(association n-aire) qui définit un ensemble de liens entre les objets de ces classes. Une
association est nommée, généralement par un verbe. Une association peut avoir des
propriétés (à l'instar d'une classe). Une association définit le nombre minimum et maximum
d'instances autorisée dans la relation (on parle de cardinalité).

Cardinalité
La cardinalité d'une association permet de représenter le nombre minimum et maximum
d'instances qui sont autorisées à participer à la relation. La cardinalité est définie pour les
deux sens de la relation.

Les cardinalité les plus courantes sont :

1- 0..1 (optionnel)
2- 1..1 ou 1 (un)
3- 0..n ou 0..* ou * (plusieurs)
4- 1..n ou 1..* (obligatoire)

Classe d'association
On utilise la notation des classes d'association lorsque l'on souhaite ajouter des propriétés à
une association.

Composition
On appelle composition une association particulière qui possède les propriétés suivantes :

1- La composition associe une classe composite et des classes parties, tel que tout objet
partie appartient à un et un seul objet composite. C'est donc une association 1:N.

2- La composition n'est pas partageable, donc un objet partie ne peut appartenir qu'à un seul
objet composite à la fois.

3- Le cycle de vie des objets parties est lié à celui de l'objet composite, donc un objet partie
disparaît quand l'objet composite auquel il est associé disparait.

Diagramme de classes :

Les relations de ce diagramme expriment que les conducteurs sont des personnes qui ont un
permis ; que toute voiture est possédée par une unique personne (qui peut en posséder
plusieurs) ; que les voitures peuvent être conduites par des conducteurs et que les
conducteurs peuvent conduire plusieurs voitures. Les conducteurs sont des personnes.

III-3-2 Passage du diagramme des classes (UML) au schéma relationnel

On peut déduire les chéma relationnel d'une base de données à partir du diagramme de
classes. Voici quelques règles permettant de déduire le schéma relation :

Règles UML-Relationel :

Classe
Pour chaque classe C non abstraite, on crée une relation R dont le schéma est celui de la
classe. La clé primaire de R est une des clés de C.

Association 1:N
Pour chaque association binaire A de type 1:N (le cas échéant 0,1:N) entre les classes S et T
(représentés par les relations RS et RT respectivement) on inclut dans la définition de RS
comme clé étrangère la clé de RT.

Association M:N et associations de degré supérieur à 2

Pour chaque association binaire A de type M:N ou pour chaque association A de degré
supérieur à 2, on crée une nouvelle relation RA pour représenter A. On met dans RA comme
clé étrangère, les clés de toutes les relations correspondant aux classes participant à A et
dont la concaténation formera sa clé.

Association 1:1
Pour chaque association binaire A de type 1,1:1,1 ou 1,1:1,0 entre les classes S et T
(représentés par les relations RS et RT respectivement) on substitut dans la définition de RS
l'éventuelle clé primaire (si RS était identifiée) par la clé primaire de RT qui est également
définie comme clé étrangère vers RT. Notons que dans le cas d'une association 1,1:1,1 RT
peut-être choisi à la place de RS pour accueillir la clé étrangère.
Pour chaque association binaire A de type 0,1:0,1 entre les classes S et T (représentés par les
relations RS et RT respectivement) on inclut dans la définition de RS comme clé étrangère
la clé de RT. La clé étrangère vers RT incluse dans RS est également définie comme étant
unique, afin d'assurer la cardinalité maximum de 1. Notons qu'il n'était pas possible dans ce
cas 0,1:0,1 que la clé étrangère vers RT incluse dans RS soit également la clé primaire, car
la cardinalité de 0 supposerait que la clé primaire puisse être "null", ce qui est illégal pour
une clé.

Cardinalité minimale 0 ou 1
Selon que la cardinalité minimale est 0 ou 1 (ou plus) du côté de la relation référençante on
ajoutera ou non une contrainte de non nullité sur la clé étrangère.
Selon que la cardinalité minimale est 0 ou 1 (ou plus) du côté de la relation référencée on
ajoutera ou non une contrainte d'existance de tuples référençant pour chaque tuple de la
relation référencée.

Attributs simples
Pour chaque attribut élémentaire et monovalué A d'une classe E (idem pour les
associations), on crée un attribut correspondant à A sur la relation RE correspondant à E.

Attributs composites
Pour chaque attribut composite C, comprenant N sous-attributs, d'une classe E (idem pour
les associations), on crée N attributs correspondants à C sur la relation RE correspondant à
E.
Classe d'association 1:N
Les attributs de la classe d'association sont ajoutés à la relation issue de la classe côté N.

Classe d'association N:M

Les attributs de la classe d'association sont ajoutés à la relation issue de l'association N:M.

Classe d'association 1:1

Les attributs de la classe d'association sont ajoutés à la relation qui a été choisie pour
recevoir la clé étrangère. Bien entendu si les deux classes ont été fusionnées en une seule
relation, les attributs sont ajoutée à celle-ci.

Compositions
Soit la composition entre la classe composite C et la classe partie P (représentés par les
relations RC et RP respectivement) on inclut dans la définition de RP comme clé étrangère
la clé de RC. La clé de RP est redéfinie comme la concaténation de la clé de P (clé locale)
avec la clé étrangère vers RC.

Transformation de la relation d'héritage

Le modèle relationnel ne permet pas de représenter directement une relation d'héritage,
puisque que seuls les concepts de relation et de référence existent dans le modèle. Il faut
donc appauvrir le modèle conceptuel pour qu'il puisse être représenté selon un schéma
relationnel.

Trois solutions existent pour transformer une relation d'héritage :

1- Représenter l'héritage par une référence entre la classe mère et la classe fille.
2- Représenter uniquement les classes filles par des relations.
3- Représenter uniquement la classe mère par une relation.

Schéma relationnel :

Personne(id, nom, prenom, date_naissance)

Conducteur(id_conducteur, type_permis,date_permis,#id_peronne)

Voiture(id_voiture,nbre_portes,puissance,kilometrage,marque,type )
IV- Algèbre relationnelle

IV-1 Opérateurs ensemblistes classiques

IV-1-1 Union

IV-1-2 Intersection

IV-1-3 Différence
IV-1-4 Produit cartésien

IV-2 Opérateurs spécifiques aux bases de données

IV-2-1 Sélection

 L’operateur de selection, note par SEL (ou σ), extrait

l’ensemble des tuples d’une relation qui verifient une condition donnee
 Une condition cond est une expression booleenne
(c'est [Link] dont la valeur est Vrai ou Faux) dont
– les operandes sont soit des valeurs de domaine, soit des noms d’attributs de la relation a
laquelle s’applique la selection
– les operateurs sont les comparateurs : <, <=, =, >=, >
– et les connecteurs logiques : AND, OR, NOT
Exemple :

1- Employes qui gagnent plus que 50

2- Employes qui gagnent plus que 50 et travaillent a Lyon

IV-2 Projection

L’operateur de projection, note PROJ (ou π) extrait les sous-tuples d’une relation sur un
ensemble d’attributs

Pour tous les employes, selectionner:

 NSS et nom

IV-2 Jointure (Join)

 L'operation de join est l'operation la plus significative de l'algebre relationnelle

 Le join permet de correler des donnees dans des relations diverses
 Il existe sous plusieurs formes:
1- Natural Join
Observer que les tuples des deux relations qui sont combines par le NJOIN doivent
forcement avoir les memes valeurs pour les attributs communs
2- Join (Theta-Join et Equi Join)
Le produit cartesien est significatif en pratique seulement s'il est suivi par une operation de
selection:
SELcond (R1 x R2)
 Cette operation est appelee theta-join (ou simplement join) et est denotee par R1
JOINcond R2
 La condition cond est souvent une conjonction (AND) de comparaisons A1 A2 ou 
(theta) est un comparateur (=, >, <,…)
 Si le comparateur est toujours l'egalite (=) alors on parle de equi-join

3- External Join (Left, Right, Full)

 Parfois il est utile de conserver dans le jointure (soit NJOIN ou JOIN) les tuples des deux
relations qui ne peuvent pas etre fusionnes.
 Exemple : donner la liste des employes avec leurs departements et les responsable, s’il y a
un responsable
 Le outer join etend par des valeurs NULL les tuples qui seraient enleves par un join
 Le outer join existe dans trois formes :
• left (gauche) : gardes tous les tuples de la premiere relation et les etend avec
NULL (s'il faut)
 • right (droite) : gardes tous les tuples de la deuxième relation et les etend avec
NULL (s'il faut)

• full (complete) : gardes tous les tuples de toutes les deux relations et les etend
avec NULL (s'il faut)

IV-2 Division
 L’operateur de division, note ÷, exprime la quantification universelle.
 Soit deux expressions algebriques P et Q telles que :
schema(P) = (A1, …, Am, B1, …, Bn)
schema(Q) = (B1, …, Bn)
 l’operateur div est defini par :
schema(P ÷ Q) = (A1, …, Am)
Exemple : Trouver les etudiants qui ont soutenu tous les examens
IV-2 Renommage

 REN: change les noms de quelque attribut d'une

relation, mais il ne change pas son extension
 Soit R une relation telle que :
schema(R) = (A1,…,Ai,...,An)
soit Bi un nom d'attribut
l’operateur REN Ai  Bi = (A1,…,Bi,..., An)

V- Langage SQL

SQL : Structured Query Language est un langage conçu pour la manipulation des données
(création, mise à jour, extraction) en interaction avec les SGBDR (Systèmes de Gestion de
Bases de Données Relationnelles).
Les instructions de ce langage sont appelées des requêtes.

V-1 Création d'une table

Instruction CREATE TABLE:
– définit un schéma de relation en créant une instance vide
– spécifit les attributs, les domaines, les contraintes
Syntaxe :
CREATE TABLE nom
(liste de définition de champs
[liste de contraintes de table])
définition de champ =
nom
type
[liste de contraintes de champ]
[DEFAULT valeur_par_défaut]
contrainte de champ ::=
[CONSTRAINT nom]
contrainte de champ
contrainte de champ ::=
PRIMARY KEY
| NOT NULL
| UNIQUE
| CHECK (condition_sur_valeur)
| REFERENCES nom_de_table(nom_de_champ)

contrainte_de_table ::=
[CONSTRAINT nom]
contrainte de table
contrainte de table ::=
PRIMARY KEY (liste de nom_de_champ)
| NOT NULL (liste de nom_de_champ)
| UNIQUE (liste de nom_de_champ)
| CHECK (condition_sur_ligne)
| FOREIGN KEY liste de nom_de_champ
REFERENCES nom de table(liste de nom_de_champ)

Contraintes Intra-relationnels
 NOT NULL: la valeur d'un attribut ne peut pas
être null
 UNIQUE pour définir des clés
 PRIMARY KEY: pour définir la clé primaire
(implique NOT NULL)
 CHECK contraintes génériques

PRIMARY KEY
Deux formes:
1. dans la définition d'un attribut, s'il forme une
clé tout seul
2. comme contrainte séparé, sinon

Exemple :
CREATE TABLE Employee (
NSS CHAR(6) PRIMARY KEY,
Prenom CHAR(20) NOT NULL,
Nom CHAR(20) NOT NULL,
Depart CHAR(15),
Salary Int DEFAULT 0,
FOREIGN KEY(Depart) REFERENCES
Department(NomDep),
UNIQUE (Nom,Prenom)
)
Primary Key avec plusieurs attributs
create table WORK_ON(
PCODE int,
NSS char(6),
DateDep date,
constraint WORK_ON_PK primary key (PCODE, NSS)
)

UNIQUE
Nom CHAR(20),
Prenom CHAR(20),
UNIQUE (Prenom,Nom),
Nom CHAR(20) UNIQUE,
Prenom CHAR(20) UNIQUE,

Plusieurs contraintes:
Nom CHAR(20) NOT NULL UNIQUE,
Prenom CHAR(20) NOT NULL UNIQUE,

Contraintes Inter-relationnels
 REFERENCES et FOREIGN KEY: permettent de
définir des contraintes d'integrité référentielle
 deux formes syntaxiques:
– pour attributs seuls
– pour plusieurs attributs

Foreign Key

create table Department

(
DeptName char(15) primary key,
Site char(20),
)

create table Employee

(
NSS char(6) primary key,
FamName char(20) not null,
FirstName char(20) not null,
Dept char(15)
references Department(DeptName),
Salary numeric(9) default 0,
unique(FamName,FirstName)
)

Remarque : Il faut que les types des attributs referencés soient les mêmes!
On peut également utiliser la syntaxe suivante (pour invoquer une clé étrangère) :

create table Employee

(
NSS char(6) primary key,
FamName char(20) not null,
FirstName char(20) not null,
Dept char(15),
Office numeric(3),
Salary float default 0,
unique(FamName,FirstName)
Foreign Key(Dept) references
Department(DeptName)
)

Ou :

create table Employee

(
NSS char(6) primary key,
FamName char(20) not null,
FirstName char(20) not null,
Dept char(15),
Office numeric(3),
Salary float default 0,
unique(FamName,FirstName)
Constraint FK_EmployeeDept
Foreign Key(Dept) references Department(DeptName)
)

Contraintes generiques: check

 Syntax:
check ( Condition )
 Specification des contraintes:
– de valeur
– de tuple
Check, exemple
create table NewEmployee
(
NSS char(6) primary key,
FamName char(20) not null,
FirstName char(20) not null,
Sex char not null check(Sex in ('M', 'F')),
Dept char(15)
references Department(DeptName),
Office numeric(3),
Salary numeric(9) default 0,
unique(FamName,FirstName),
constraint SalProd check(Dept <> 'Production' or Salary <
10000)
)

Modification d'une table (update/alter) - Violation d'une contrainte d'integrité

Plusieurs actions référentielles :

 no action: l'opération qui cause une violation de l'integrité référentielle est refusée
(default)
 cascade: tous les tuples qui contient l'élément sont mis à jours avec la nouvelle valeur
 set null: la valeur de l'attribut modifié devient null
 set default: la valeur de l'attribut modifié devient la valeur default, (il faut qu'elle soit
défini)

Suppression: (delete/drop) - Violation d'une contrainte d'integrité

Plusieurs actions referentielles:

 no action: l'operation qui cause une violation de l'integrité référentielle est refusé (default)
 cascade : tous les tuples contenant la clé étrangere occurrant dans le tuple supprimé sont
supprimés
 set null: la valeur de l'attribut modifié devient null
 set default: la valeur de l'attribut modifié devient la valeur default, (il faut qu'elle soit
défini)

Pour sécuriser les mises à jour, on utilise la syntaxe suivante :

 Pour chaque contrainte FK on peut définir une strategie de reaction differente:

create table Employee

(
NSS char(6) primary key,
FamName char(20) not null,
FirstName char(20) not null,
Dept char(15),
Office numeric(3),
Salary float default 0,
unique(FamName,FirstName)
Constraint FK_EmployeeDept
Foreign Key(Dept) references Department(DeptName)
on delete cascade on update set null
)

V-2 Modification (de la structure) d'une table

V-2-1 ALTER TABLE : modification de la structure de la table

ALTER TABLE nom_de_table modification_de_table

modification_de_table ::=
ADD définition_de_colonne
ADD CONSTRAINT contrainte_de_table
DROP COLUMN nom_de_colonne
DROP CONSTRAINT nom_de_contrainte

exemples:

ALTER TABLE employee

ADD Address char(20) default null
ALTER TABLE Department
ADD Manager char(6) default null
ALTER TABLE Departement
ADD constraint FKey_EMP_SUP foreign
key(Manager) references EMPLOYEE(NSS)

ALTER TABLE employee

DROP column address

ALTER TABLE employee

DROP constraint FKey_EMP_SUP

V-2-2 DROP TABLE : Suppression de la table

DROP TABLE nom_de_table

Exemple :
DROP TABLE employee
la suppression est autorisée seulement si la
table n'est pas referencée

V-3 Opérations sur les tuples

– insertion: insert
– suppression: delete
– modification: update
 Ces opérations impliquent un ou plusieurs tuples

V-3-1 Insertion
 Syntaxe:
INSERT INTO NomDeTable [ ( Attributs) ]
VALUES( Valeurs )
 Insertion d'un single tuple:
INSERT INTO NomDeTable[ ( Attributs) ]
VALUES(Valeurs )
 Insertion de plusieurs tuples selectionnés de quelque
relation
INSERT INTO NomDeTable[ ( Attributs)]
SELECT …

On considère une table personne(prenom,age,salaire)

Exemples :

INSERT INTO Personne VALUES ('Mario',25,52)

INSERT INTO Personne(Prénom, Age, Salaire)
VALUES('Mario',25,52)
INSERT INTO Personne(Prénom, Salaire) VALUES('Lino',55)
INSERT INTO Personne ( Prénom ) SELECT Pere FROM Paternitè WHERE Pere NOT IN
(SELECT Prénom FROM Personne)

V-3-2 Modification
Syntaxe :
UPDATE NomDeTable
SET Nom_d’Attribut = < Expression |
SELECT … |
NULL |
DEFAULT >
[FROM TABLES]
[ WHERE Condition ]

Exemples :
UPDATE Personne SET Salaire = 45 WHERE Prénom = 'Pierre'
Résultat : Fixer le salaire à 45 pour des personnes dont le prénom est « «Pierre »

UPDATE Personne SET Salaire = Salaire * 1.1 WHERE Age < 30

Résultat : Augmenter le salaire du 10% des personnes d'age < 30

V-3-3 Suppression
Syntaxe :
DELETE FROM NomDeTable [ WHERE Condition ]
Exemples :
 DELETE FROM Personne
WHERE Age < 35

Résultat : suppression des tuples ayant Age < 35

 DELETE FROM Paternite WHERE not exists (select * from Personne where Enfant =
Prénom)

Résultat : suppression des tuples de Paternite dans lesquels les enfants n'existent pas dans
Personne

 DELETE FROM Paternite

Résultat : suppression de tous les tuples

V-4 Interrogations (Select)

SELECT ListeAttributs
FROM ListeTables
[ WHERE Condition]

V-4-1 Sélection et projection

select Prénom, Salaire from Personne where Age < 30
 l'opérateur * : indique tous les attributs
select * from Personne where Age < 30
 corresponde à:
select Prénom, Age, Salaire from Personne where Age < 30

1- Projection sans sélection

 Prénom et Salaire de toutes les Personnes
select Prénom, Salaire from Personne
est équivalent à:
select Prénom, Salaire from Personne where TRUE

2- Select avec Condition plus complexe

select * from Personne where Salaire > 25 and (Age < 30 or Age > 60)

3- SELECT, Renommage des attributs

select Prénom as Name, Salaire as Rémuneration from Personne where Age < 30

L'expression Salaire as Rémunération, indique que l'attribut Salaire sera renommé

Rémunération. On dit que Rémunération est l'Alias de Salaire

Règle pour les noms d'attributs

 En SQL deux attributs peuvent avoir le même nom, mais dans des relations différentes
 Pour éviter l'ambiguité de noms égaux dans relations différentes il faut préfixer les noms
égaux par les noms des tables auxquelles ils appartiennent

Exemple :
Select [Link] From Employé, Supervision Where Employé.Salaire > 40
and [Link]é = Employé.NSS

V-4-2 Jointures
1- Explicit Join
SELECT …
FROM Table { … JOIN Table ON Cond }, …
[ WHERE AutreCondition ]

 Père et mère de chaque personne select Paternité.Enfant, Paternité.Père, Maternité.Mère

from Maternité, Paternité where Paternité.Enfant = Maternité.Enfant
select Paternité.Enfant, Paternité.Père, Maternité.Mère from Maternité join Paternité on
Paternité.Enfant = Maternité.Enfant

2- Jointure externe: outer join

select Paternité.Enfant, Paternité.Père, Maternité.Mère from Paternité left join Maternité
on Paternité.Enfant = Maternité.Enfant

select Maternité.Enfant, Paternité.Père, Maternité.Mère from Paternité right join Maternité

on Paternité.Enfant = Maternité.Enfant

select Paternité.Enfant, Paternité.Père,Maternité.Enfant, Maternité.Mère from Paternité full

join Maternité on Paternité.Enfant = Maternité.Enfant

V-4-3 Ordonner les tuples dans le résultat

 Syntaxe :
order by Attribut [asc | desc] {, Attribut [asc | desc]}
– asc (default) ordre croissant
– desc: ordre décroissant
Exemples :

1- select Prénom, Salaire from Personne where Age < 30 order by Nom

2- select Prénom, Salaire from Personne order by Salaire desc

V-5 Fonctions d'agrégations
 Dans une requête
select Fonction (attribut) from .... where ....
1. les tuples satisfaisant la clause where sont selectionnés
2. la Fonction est appliquée à l'ensemble des valeurs d'attribut
3. les valeurs NULL ne sont pas tenues en compte

V-5-1 fonction COUNT

Exemple : le nombre d'enfants de Franc
select count(*) as NombreEnfantsFranc from Paternité where Père = 'Franc'

Autres exemples :
select count(*) from Personne
select count(Salaire) from Personne
select count(distinct Salaire) from Personne

V-5-1 Autres fonctions : SUM, AVG, MAX, MIN

SUM : Calcul la somme des valeurs de l'attribut indiqué

AVG: Calcul la moyenne des valeurs de l'attribut indiqué
MAX: Indique la plus grande valeur de l'attribut indiqué
MIN: Indique la plus petite valeur de l'attribut indiqué

Exemples :
select avg(Salaire) from Personne join Paternité on Prénom=Enfant where Pere='Franc'

V-5-2 Fonctions d'agrégation et agrégats

 Les fonctions peuvent être appliquées à des partitionnements de relations
 Clause GROUP BY:
GROUP BY liste d’Attributs
Utiliser GROUP BY seulement si la requête contient une fonction d’agrégation !
Exemple : Le nombre d'enfants de chaque père
select Père, count(*) AS NombreEnfant from Paternité group by Père

Selection sur le resultat de la fonction d'agrégation

 Afficher les étudiants (et leur moyenne) qui ont une moyenne >= 13
(on ne peut pas seléctioner ces étudiants avec une condition dans la clause where !)
Exemples :

Afficher les etudiants (et leur moyenne) qui ont une moyenne >=13
select Enom, [Link] from Etudiant join Examen on [Link]= [Link]
group by [Link], Enom having avg(Note) >= 13

select Enom, [Link], avg(Note) as Moyenne from Etudiant join Examen on

[Link]= [Link] group by [Link], Enom having avg(Note) >= 13

(on ne peut pas sélectionner ces étudiants avec une

condition dans la clause where !)

V-6 Sous-requêtes (requêtes imbriquées)

La clause WHERE peut contenir une requête (SELECT, FROM, WHERE)
les sous-requêtes sont utilisées pour :

1. effectuer un comparaison entre une valeur d'un attribut et une valeur calculé à partir d'un
ensemble d'attributs

2. effectuer un comparaison entre une valeur d'un attribut et une ensemble de valeurs

Exemples de sous-requêtes :

select * from Personne where Salaire = (select max(Salaire) from Personne)

select Enom from Etudiant where Mat in (select Mat from Examen where code = 02)

select Enom from Etudiant where Mat not in (select Mat from Examen where code =
02)

V-7 Intersection/Différence
 Différence: except
 Intersection: intersect
 Ils ne sont pas forcement disponibles dans tous les
SGDB
(par exemple dans SQL server 2000, on ne les a pas)
 On peut réaliser ces opérations avec in/not in

V-7-1 Intersection
Matricules des étudiants qui ont soutenu l'Examen de Code 04 et aussi l'Examen de Code 03
select Mat from Examen where Code = 04 and Mat in (select Mat from Examen where Code = 03)

Ou:
select Mat from Examen where Code = 03 and Mat in (select Mat from Examen where Code = 04)
Avec l’operateur d’intersection:

(select Mat from Examen where Code = 04) intersect (select Mat from Examen where Code = ‘03)

V-7-2 Différence
Matricules des étudiants qui ont soutenu l'Examen de Code 04 mais qui n'ont pas soutenu
l'Examen de Code =03 :
Requêtes :
select Mat from Examen where Code = 04 and Mat not in (select Mat from Examen where
Code=03)

Avec l’operateur de différence :

(select Mat from Examen where Code = 04) except (select Mat from Examen where Code = ‘03)

V- 8 Les Vues
 Une relation virtuelle dont le contenu est défini
par une requête SQL Pour l’utilisateur, la vue apparaît comme une table réelle, mais elle
n’existe pas dans la base comme un ensemble de valeurs stocké dans la mémoire
 Elles réalisent le niveau externe de l ’architecture à 3 niveaux ANSI/SPARC
 Les vues peuvent être utilisées comme des relations de base
– Interrogation
– Mise à jour sous certaines conditions

• Terminologie : Vue relationnelle, table virtuelle

Avantages des vues

 Les vues proposent aux utilisateurs une perception de la base plus proche de leurs
besoins
 Meilleure indépendance logique des programmes par rapport aux données
 Une vue peut présenter une image cohérente, même si les tables sont décomposées,
restructurées, renommées
 Simplification des requêtes utilisateurs:
– Masquer la complexité des schémas
– Transformer des requêtes multi tables en requêtes mono table
 Sécurité: un utilisateur ne peut accéder qu’aux données des vues auxquelles il a droit
d’accès
Inconvénients-Problèmes
 Performances:
Le SGBD doit toujours traduire les vues en requêtes (souvent complexes) : temps important
pour exécuter une requête sur une vue
Solution: matérialisation de la vue dans une table physiquement existante
Problème de la mise à jour efficace de la vue matérialisée
 Restrictions de mise à jour:
Une mise à jour sur une vue se répercute sur les tables composantes; les vues complexes
sont forcement en lecture seule, donc mise à jour impossible
 Nous verrons seulement l'utilisation de vues pour requêtes de sélection

 Création des vues:

create view NomVue [ ( Attributs ) ] as SelectSQL [ with check option ]

 Suppression de vues:

drop view NomVue

Exemples :
1- Vue des employés du departement Production ayant un salaire élévé (salaire >=
6000) :
create view EmployeeProductionSalEl as
select SSN, FamName, FirstName, Salary, Office
from Employee
where Dept = 'Production' and
Salaire >= 6000
2- Avec renommage des attributs:
create view EmployeeProductionSalEl (SSN, Nom, Prenom, Salaire, Office) as
select SSN, FamName, FirstName, Salary, Office from Employee where Dept = 'Production'
and Salaire >= 6000

3- Suppression de la vue :

drop view EmployeeProductionSalEl