Cours : Bases de données

Université de Ngaoundéré, Cameroun

Par : P. Dayang

Bases de données
Chapitre 1 : Environnement de base de données
1 Système de bases de données dans l’environnement informatique

Les systèmes de bases de données constituent une discipline fondamentale dans plusieurs
domaines scientifiques et en particulier dans le domaine informatique. Nous pouvons localiser
les SBD comme suit :
2 L’historique du développement des BDs
L’historique des bases de données remonte au début des années 60. Le système était alors basé
sur des fichiers. Il s’agit de l’organisation classique en fichiers.
Un fichier est un ensemble d’information, désigné par un nom précis, que le système
d’exploitation d’un ordinateur manipule comme une simple entité, dans sa mémoire ou sur un
support de stockage.
La fin des années 60 a vu l’apparition des premiers SGBD (Système de Gestion de Bases de
Données), les systèmes réseaux et hiérarchiques. La deuxième génération de SGBD, appelée les
systèmes relationnels, a été introduite à partir de 1970. En fin au début des années 80, la 2e
génération SGBD cède la place à la 3e appelée systèmes orientés objets.

2.1 L’historique du développement des BDs

Le système basé sur des fichiers est par tradition le prédécesseur du système de base de données.
L’approche basée sur des fichiers est une première tentative d’informatiser le système de fichier
manuel.
Le système basé sur des fichiers est une collection de programmes d’application qui assurent
des services pour les utilisateurs finals tels que la production de rapports. Chaque programme
définit et gère ses propres données.

2.2 Limites de l’approche basée sur des fichiers

L’approche schématisée comme suit présente différentes limites.

saisie traitement fichier

fichier

saisie Etats de
traitement sortie
 ∗ Séparation et isolement des données
∗ Dédoublement des données
o Les doublons constituent une perte de temps
o Les doublons consomment de l’espace de stockage avec des coûts associés
o Les doublons entraînent une perte d’intégrité des données qui perdent ainsi leur
cohérence
∗ Incompatibilité des formats de fichiers. L’incompatibilité des fichiers limite leur
utilisation conjointe. Ceci est dû à la structure des fichiers dans les programmes
d’application.
∗ La prolifération des programmes d’application. Les exigences des nouvelles requêtes par
l’utilisateur final entraînent la modification du code gérant le fichier concerné.

2.3 Approche de la base de données

L’approche de la base de données connait diverses motivations. Principalement, l’objectif visé
consiste à :
- L’éradication de la particularisation de la saisie et des traitements en fonction des fichiers
pour faire place à l’uniformisation de la saisie et de la standardisation des traitements.
- Le partage de données entre plusieurs traitements ou applications.
- La limitation de la redondance des données
Pour atteindre ces objectifs apparurent alors la base de données (database) et le système de
gestion de base de données (SGBD ou DBMS : Database Management System).
Définition :
Base de données : Une collection partagée de données en relation logique et une description des
données conçues pour satisfaire les besoins d’information d’une organisation. C’est une
collection des données cohérentes et structurées.

Saisie + Etats de
Conrôle DB traitement sortie

Utilisateurs
 Organisation de base de données
L’organisation de base de données garantit l’indépendance entre les données et les programme-
données c’est-à-dire les applications de traitement des données. Elle garantit également
l’exploitation et la gestion simultanée des données par plusieurs utilisateurs. A cet effet, il
convient de redéfinir la base de données comme une collection auto-descriptive
d’enregistrements intégrés.
La description des données est désignée de catalogue système ou dictionnaire des données ou
encore métadonnées c’est-à-dire les « données à propos des données ».
L’approche adoptée par les systèmes de base de données est comparable à l’approche suivie dans
le développement des logiciels modernes, où une définition interne d’un objet et une définition
externe distincte sont fournies. Les utilisateurs d’un objet ne voient que la définition externe et
ignorent comment l’objet est défini et comment il fonctionne.
L’organisation de BD présente les propriétés suivantes :
• Usage multiple des données
• Accès facile, rapide, protégé, puissant.
• Coût réduit de stockage, de mise à jour et de saisie
• Disponibilité, exactitude, cohérence, non redondance
• Indépendance des données et des programmes
Chapitre 2 : Système de gestion de base de données
(SGBD)
1 Historique des systèmes de base de données

L’origine du SGBD remonte au début des années 1960, lorsque le projet Apollo d’alunissage
fut lancé. Alors un logiciel de structure hiérarchique fut développé sour le nom de GUAM
(Generalized Update Access Method). Au milieu des années 1960 IBM se joignit à NAA (North
American Aviation, qui initie du projet de faire alunir un homme) pour développer ensemble
l’IMS (Information Management System).
Le milieu des années 1960 a vu émerger un autre logiciel appelé l’IDS (Integrated Data
Store), c’est-à-dire stockage des données intégrées. Ces travaux de développement de logiciel
vont donner naissance à un nouveau type de système de base de données connu sous le terme de
SGBD en réseau.
En 1965, suite à la conférence portant sur les langages de systèmes de données, le groupe
List Processing Task Force, qui fut renommé Data Base Task Group (DBTG) dut crée.
DBTG édite en 1971 le premier rapport définissant les normes de spécification qui
permettent la création de base de données et la manipulation des données.
Dans le cadre de la normalisation, le DBTG a spécifié trois langages.
- Un langage de définition de données (LDD)
En anglais DDL, Data Definition Language, il permet à l’administrateur de Base de Données de
définir le schéma. On entend par schéma, l’organisation logique de la base de données (nom de
BD type d’enregistrement).
Un LDD de sous-schéma qui permet aux programmes d’application de définir les parties de
base de données dont ils ont besoin.
Un langage de manipulation de données (LMD) en anglais DML, Data Manipulation
Language. Il permet de la recherche des données, la mise à jour ; la transformation des données.
Un langage de protections d'accès (LCD). Il est possible de définir des permissions au niveau
des utilisateurs d'une base de données. On parle de DCL (Data Control Language).
Le développement d’un langage de requête structuré appelé SQL vers la fin des années 1970 a
permis à mettre sur pied un langage standard des SGBD relationnels.

2 Les SGBD
SGBD est le système logiciel qui permet aux utilisateurs de définir, créer, mettre à jour une base
de données et d’en contrôler l’accès. Le SGBD est le logiciel qui interagit avec les profanes
d’application de l’utilisateur et la base de données.
En général, un SBGD assure les fonctions suivantes :
- Description des données à l’aide d’un LDD
- Insertion, mise à jour, suppression, recherche des données à l’aide d’un LMD. LMD est
appelé langage de requête (en anglais query language). Il permet la recherche générale
sur les données.
- Contrôle des accès à la base de données à l’aide d’un LCD
La disponibilité d’un langage de requête soulage le problème des systèmes basés sur les fichiers
où l’utilisateur doit se contenter d’une série de requêtes bien préfinies et fixes.
Le langage de requête de plus connu est le SQL : Le langage de requête structuré (en anglais
Structured Query Language).
Les programmes d’application de base de données constituent des programmes informatiques qui
interagissent avec la base de données en lui adressant une requête appropriée (généralement sous
la forme d’une instruction SQL) au SGBD. Les utilisateurs naifs interagissent avec la base de
données par l’entremise de programmes d’application qui servent à manipuler les données et
générer les informations (lot de commandes, les applications dites en ligne exp : [Link]).

2.1 Composants de l’environnement d’un SGBD

Nous pouvons distinguer 5 composants dans l’environnement SGBD : Le matériel, le logiciel,
les données, les procédures et les personnes.

Données

Matériels Logiciels Procédures Personnes

Machines Pont Être humain

Environnement du système de gestion de BD
Le matériel va d’un simple ordinateur personnel au rideau d’ordinateur en passant par le serveur
centralisé (serveur assure le travail d’arrière-plan : backend, tandis que le client assure la partie
frontrale : frontend).
Les logiciels : Ce sont les programmes d’application regroupe avec le système d’exploitation
Les données : Le composant central joue le rôle d’un pont entre l’Être humain et le monde des
machines.
Les procédures : Ce sont les règles qui gouvernent la conception et l’usage de la base de
données. Elles contiennent les instructions telles que :
- Ouvrir une session sur le SGBD
- Utiliser un utilitaire spécifique du SGBD
- Créer des copies de sauvegarde de la base de données
- Gérer les pannes matérielles ou logicielles
- Changer la structure d’une table, réorganiser la base de données sur plusieurs disques,
archiver les données
Les personnes : Ce sont des personnes impliquées dans le système
Le SGBD peut se décomposer en trois sous-systèmes :
1. Le système de gestion de fichiers : Il gère le stockage des informations sur un support
physique.
2. Le SGBD interne : Il gère l’ordonnance des informations
3. Le SGBD externe : Il représente l’interface avec l’utilisateur
Les principaux systèmes de gestion de base de données sont les suivants :
Microsoft SQL Server, Oracle, Microsoft Access, IBM DB2, MySQL, PostgreSQL, mSQL

2.1 Processus de conception d’une BD

Monde réel

spécification
indépendant
d’un SGBD
Schéma conceptuel

spécifique à
un SGBD

Schéma logique
transformation
modèle logique Schéma interne
Conception
physique
Nous pouvons distinguer 2 niveaux ou phases de conceptions : la conception logique de BD et la
conception de BD physique.
La conception logique de BD consiste à identifier les données c’est-à-dire les entités et les
attributs, les associations entre les données et les contraintes sur les données qu’il faut stocker
dans la base de données. Pour être efficace, le concepteur logique de BD doit impliquer tous les
utilisateurs potentiels de la BD dans le développement du modèle de données et cette implication
doit commencer aussitôt que possible.
La conception de base de données physique consiste à décider de la manière dont le design
logique de données est physiquement réalisé. Le concepteur de BD physique transpose le design
logique en un ensemble de tables et contraintes d’intégrité. Il décide de la structure de stockage
spécifique et de méthodes d’accès afin d’atteindre la meilleure performance. Il conçoit les
mesures de sécurité requises au niveau des données.

3 Avantages et inconvénients des SGBD

Le système de gestion de base de données promet des avantages potentiels. Malheureusement il
présente aussi des inconvénients.
3.1 Avantages
- Contrôle de la redondance des données : L’approche de la base de données n’élimine pas
totalement la redondance mais elle contrôle la quantité de redondance inhérente à la base
de données. Tandis que les systèmes basés sur des fichiers gaspillent de l’espace de
stockant les mêmes données ou informations dans plus d’un fichier, l’approche de la base
de donnée dédouble les données en pour améliorer les performances.
- Cohérence des données : L’élimination ou tout au moins le contrôle de la redondance
permet de réduire les risques d’incohérences. Si une donnée est stockée une fois dans la
BD, toute mise à jour de sa valeur n’est à effectuer qu’une seule fois et la valeur est
immédiatement disponible pour tous les utilisateurs.
- Partage des données : entraînant les économies des coûts c’est-à-dire économie d’echelle
- Intégrité améliorée des données. L’intégrité de la base de données fait référence à la
validité et à la cohérence des données stockées. Elle s’exprime généralement sous forme
de contraintes, c’est-à-dire des règles que la base de données ne peut transgresser.
 - Sécurité améliorée : La sécurité d’une base de données est la protection contre les
utilisateurs non autorisées. L’accès d’un utilisateur autorisé aux données peut se voir
restreint selon le type d’opération (lecture, insertion, mise à jour, suppression)
- Accessibilité et réponse améliorées des données
- Maintenance améliorée via l’indépendance des données
- Concurrence ou simultanéité accrue : c’est-à-dire plusieurs utilisateurs sont autorisés à
accéder simultanément au même fichier.

3.2 Inconvénients
- Complexité : La fourniture des fonctionnalités que nous espérons d’un SGBD fait de
celui-ci un logiciel extrêmement complexe.
Les concepteurs et les développeurs de base de données, les administrateurs de BD et les
utilisateurs doivent comprendre ces fonctionnalités pour en tirer le parti maximal.
- Taille : La complexité ainsi que la manne de fonctionnalités font du SGBD un logiciel
extrêmement lourd, occupant de nombreux mégaoctets d’espace disque et requérant de
substantielles quantités de mémoire pour fonctionner efficacement.
- Coût des SGBD : Le coût des SGBD varie vraisemblablement, selon l’environnement et
les fonctionnalités offertes.
- Coût de conversion : On parle ici aussi de legacy system c’est-à-dire système hérité dont
le coût de mise à jour est très élevée.
- Performances : Le SGBD est écrit où développé pour satisfaire plusieurs applications.
Ceci engendre un fonctionnement lent des systèmes d’application.
- Impact plus marqué d’une panne : La centralisation des ressources augmente la
vulnérabilité du système. Etant donné que les SGBD, toute panne de certains composants
peut entrainer l’arrêt de tous les opérations.
Chapitre 3 : Architecture des bases de données
1. Architecture à trois niveaux ANSI-SPARC
L’objectif de l’architecture à trois niveaux ANSI-SPARC est de séparer les vues que chaque
utilisateur peut avoir de la base de données et de la manière dont la base de données se présente
physiquement. Dans un système de base de données, les structures de données sont représentées
par trois schémas introduits dans l’architecture ANSI-SPARC sous forme de niveaux et désignés
de niveau interne, conceptuel et externe.

Utilisateur 1 Utilisateur 2 Utilisateur n

niveau
externe Vue 1 Vue 2 ..... Vue n

niveau
Schéma conceptuel
conceptuel

niveau
interne Schéma interne

Base de
données

La façon dont les utilisateurs perçoivent les donnés s’appelle le niveau externe. La vue externe
ne reprend que les entités, les attributs et les associations du monde réel qui intéressent
l’utilisateur. Le niveau externe offre différentes représentations des mêmes données.

La façon dont le SGBD et le système d’exploitation perçoivent les données constitue le niveau
interne, où les données sont réellement stockées dans les structures de données et les
organisations de fichiers. Le niveau interne désigne la représentation physique de la base de
données dans l’ordinateur.
Le niveau conceptuel fournit à la fois la correspondance et l’indépendance souhaitées entre les
niveaux externes et internes. Ce niveau décrit la structure logique de toute la base de données. Il
représente les entités, leurs attributs et leurs associations, les contraintes qui s’y appliquent aux
données, les informations sémantiques ainsi que la sécurité et l’intégrité des données.

La description globale de la base de données s’appelle le schéma de la base de données. Nous

distinguons trois types de schémas dont chacun correspondant à l’architecture à trois niveaux
précédemment illustré. Les schémas externes qui correspondent à différentes vues de données.
Le schéma conceptuel qui décrit toutes les entités, tous les attributs et toutes les associations
ainsi que les contraintes d’intégrité. Au niveau le plus bas, nous avons le schéma interne qui
représente une description complète du modèle interne.

Exemple de différentes vues du personnel de l’université.

Vue externe 1

numPerson prénom nom âge salaire

Vue externe 2

numPersonel nom numDépartement

Niveau
conceptuel numPersonnel prénom nom dateDeNaissance salaire numDépartement

Niveau interne struct PERSONNEL {

int numPersonnel ;
int numDépartement ;
shar prénom[15] ;
shar nom [15] ;
struct date dateDeNaissance ;
float salaire ;
Strucht PERSONNEL *suivant
};
index numPersonnl, index numDépartement ;
2. Architecture client-serveur à trois niveaux

L’architecture propose trois couches qui s’exécutent potentiellement sur des plates-formes
différentes.

1) La couche d’interface utilisateur, encore appelée couche présentation fonctionne sur

l’ordinateur de l’utilisateur final (le client).

2) La couche de logique métier et de traitement des données s’exécute sur un serveur,

souvent désigné comme serveur d’application.

3) La dernière couche, dont le rôle principale est de stocke les données nécessaires récues de
la couche intermédiaire, s’exécute sur un serveur, désigné comme serveur de base de
données.

Premier niveau : Tâches:

client Interface utilisateur

4)
Deuxième conceptuel :
Serveur d’application Tâches:
Logique métier
Logique de traitement

Troisième niveau:
Serveur

Tâches:
Validation des données
Accès à la base de données

Base de
données
3. Modélisation de base de données

La modélisation des données vise la structuration systématique afin d’obtenir une vue élégante
sur toutes les données de l’entreprise. A cette fin, les types de données constituent les
composantes centrales de chaque système d’information. Chaque type comporte des attributs.
Les valeurs que les attributs peuvent prendre dans un contexte sont désignées d’informations.

Les objets sont reliés les uns aux autres. A travers différents niveaux d’abstraction la vue sur
les données couvre tous les rapports d’information sur le plan des tâches dans le système
d’information. La base de donnée est un modèle c’est-à-dire une partie relevante du système
d’objet de l’entreprise. Le schéma conceptuel des données définit la structure statique de la base
des données. Le modèle des données ou modèle meta est un outil pour la construction des
schémas de données conceptuels. La modélisation des données appartient au processus de
conception d’une base de donnée et est extrêmement importante surtout dans le principe d’une
vue globale de l’entreprise sur les données. Elle a pour but de garantir l’intégrité sémantique des
données (libre des inconsistances et des contradictions). Le fondement théorique du calcul
relationnel est la logique des prédicats selon Codd 1970. Les propriétés sont les suivantes :

♦ Les relations sont des tables bidimensionnelles

♦ Les colonnes d’une table sont des attributs
♦ Les relations sont invariantes par rapport à l’échange des colonnes
♦ Les attributs ont des noms uniques
♦ Les valeurs des attributs sont atomiques
♦ Les lignes d’une table se nomment tuplets
♦ Les relations sont invariantes par rapport à l’échange des lignes

La modélisation conceptuelle est une phase importante dans la conception réussie d’une base
de donnée. L’application d’une base de donnée se rapporte en général à une base de données
particulières - par exemple une base de données bancaires s’occupe des comptes des clients - et
les programmes y associés implémentent les requêtes et les actualisations (updates).
 Le modèle de données est un système de concepts pour la description des données. Il définit
la syntaxe des schémas de la base de données et l’interprétation Schéma de la base de données Æ
Etats de la base de données. Les modèles classiques des données sont : le modèle hiérarchique,
le modèle en réseau, le modèle relationnel et le modèle orienté objet.
4. Le modèle Entité/Association (E/R)
Développé par Peter Chen en 1976, le modèle E/R a aujourd’hui une grande signification dans
l’informatique et ce en rapport avec la modélisation des relations du monde réel. Le modèle E/R
s’est imposé pour plusieurs raisons :
• Il est indépendant d’un système de base de données quelconque
• Ses concepts de base « entité » « relationship/association » sont des moyens d’expression
naturelle.
Ce modèle méta est très répandu et sert à la modélisation des choses et de leurs relations.
Les concepts de base sont les entités, les relations et les attributs.
Entity : Objet réel ou imaginaire qui appartient à un type d’entité. Par exemple. « Monsieur X
(entité) est employé (type d’entité). Le symbole de représentation est un rectangle. La description
se fait au singulier. Client

Voiture

Relationship : Relation entre plusieurs entities : Par exemple, Monsieur X est chef (relationship)
de monsieur Y. Pour la notation l’on utilise le singulier et le symbole de représentation est le
losange. La cardinalité appartient au typ de la relation ou association.

Client

loue

Voiture

Complexité ou cardinalité d’une relation. Elle donne le rapport que les entités entretiennent entre
elles, soit unique (1 :1), un à plusieurs (1 :m) ou complexe (n :m). Elle donne combien d’entités
A sont en relation avec une entité B. Elle est toujours collée à l’entité en question. Il est
important que la cardinalité reflète les faits réels entre les entités. Par exemple, une voiture de
location peut être louée par personne ou par un seul client. Mais un client peut louer une ou
plusieurs voitures.

Client

(0,1)
loue

(1 n)

Voiture

Un attribut décrit les propriétés d’un type d’entité ou de relations, par exemple, matricule d’un
étudiant ou la date à laquelle un employé a été embauché. Les attributs obéissent au principe des
types abstraits et appartiennent généralement à des domaines bien précis. Le symbole est ovale et
les inscriptions sont des substantifs au singulier. Il existe aussi des attributs identificateurs
désignés de key (clé), primaire ou étrangère. Une clé est une combinaison minimale d’attributs
identificateurs. Le symbole des clés est un ovale avec les attributs soulignés.
1:n
Nr-Client., Nom,
Lieu, Telephone Client

Client
achète
1
Nr-Client., , Nr-
Immatriculation.,
Voiture
n
loue Durée

Voiture

Nr-Immatriculation., VoiturePersonnelle Camion

Marque,
Couleur

1:1 n:m 1:n

Employé Etudiant Client

1 n 1
est_chef suit achète

1 m n
Département UV Voiture
Entité
Définition : Une déclaration d’entité a la forme E = (X, K). Elle se compose d’un nom E,
d’un format X et d’une clé primaire K qui se compose des éléments de X.
Les éléments du format X sont notés comme suit
(i) Attributs unaires : A
(ii) Attributs à valeur multiple : {A}
(iii) Attributs composés : A(B1,…, Bk)
L’on dit aussi entité.
Exemple Livre = ({InvNr, {Auteur}, Titre, Maison(Nom, Lieu), Année}, {InvNr})
Lecteur = ({LNr, Nom(PreN, NomF), Adresse(Rue, Lieu)}, {LNr})

Associations/Relationships
Pour représenter les rapports entre les entity sets, le modèle E/R propose les relations.
Définition : Une déclaration d’association (relation) a la forme R = (Ent, Y). R est le nom de
la déclaration (ou nom de l’association), Ent est la séquence des noms des déclarations d’entités
entre lesquelles une relation est définie et Y est une séquence d’attributs (pouvant être vide).

Lecteur
Fournisseur Accessoires

emprunte DateR
livre

Nombre Firme
Livre

emprunte = ((Livre, Lecteur), (DateR)) Livre = ((Fournisseurs, Accessoires, Firme),

(nombre))
Les relations peuvent comporter plusieurs complexités aussi appelées cardinalité. L’on a 1 :1,
1 :n, m :n
1 :1 one to one relationship : Une telle relation existe entre E1 et E2 si chaque entité du type
E1 est en relation avec au trop un élément de l’entité de type E2. La relation « est chef de » entre
 Employés et Département. Chaque employé est le chef d’un seul département, et un
département est dirigé par au trop un seul chef.
Lecteur
1 :n one to many.
1
emprunte DateR

n
Livre

Si la relation 1:n existe entre les entités E1 et E2, alors entre E2 et E1, il existe une relation
m :1.
emprunte = ((Livre, Lecteur), (DateR)) est de type m :1. Chaque livre ne peut être emprunté
(simultanément) qu’à un seul lecteur, mais un lecteur peut emprunter plusieurs livres.
m :n many to many.
m emprunte
1 Pays

Lecteur
Livre exporte

n
m 1 Produit
emprunte

Les relations is-a

Il arrive parfois que les entités d’un set ne soient pas seulement distinguées par leurs attributs
mais par leur relation à d’autres attributs.
Nom Adr
Profession
Nr
Employé

is

Pilote Technicien

Heures Licence NrT

 Ici Pilote ⊆ Employé et Technicien ⊆ Employé. Les deux entités sont des spécialisations de
Employé et utilisent la même clé que leur généralisation. L’on peut aussi avoir une hiérarchie
des spécialisations.

Personne

is

Manager Employé Secrétaire

is

Technicien Vendeur Programmeur

Chapitre 4 : Le modèle relationnel.
Le modèle relationnel inventé par Codd est le standard dans l’industrie depuis les années 1980.
Ce modèle peut être vu comme composé de trois principales parties: la structure, l’intégrité et la
manipulation des données.
1 Structure de données relationnelle
Plusieurs aspects structuraux sont présents dans une relation. L’on a la relation elle-même, les
lignes constituées des éléments des produits cartésiens des attributs, la cardinalité, l’attribut, le
degré, le domaine, la clé primaire, les tuples.

J# Nom Statut Ville

domaines
Clé primaire

J# :Joueur# JNom :Nom Statut:Statut Ville:Ville

J1 Eto’o 20 Barcelone
relation
J2 Mboma 19 Tokyo
cardinalité
J3 Geremi 18 Chelsea
J4 Perrier 15 Paris
J5 Kameni 12 Garoua

attributs

degré

Figure 3.1: Terminologie structurale

Une relation est une table avec des colonnes et des lignes. Un attribut est une colonne nommée
de la relation. Un domaine set un ensemble de valeurs admissibles pour un ou plusieurs attributs.
Les éléments d’une relation sont des les lignes, n-uplets ou tuples. Une relation peut-être unaire
donc un tuple de un : i elle comporte deux ou trois attributs alors elle est respectivement binaire
ou tertiaire. Le terme n-aire (n-uplet) pour designer une relation de degré n ou ayant n attributs.
Un tuple est une ligne dans une relation. Le degré d’une relation est le nombre d’attributs qu’elle
contient. La cardinalité ou le cardinal d’une relation est le nombre de tuples qu’elle contient.
Une base de données relationnelle est donc une collection de relations normalisées, portant des
noms de relation distincts.
Relations mathématiques
Supposons nous disposons de deux ensembles, E1 et E2, où E1 = {2, 4} et E2 = {1, 3, 6}. Le
produit cartésien de ces deux ensembles s’écrit E1 x E2 et constitue l’ensemble de toutes les
paires ordonnées dont le premier élément est un membre de E1 et le second élément est un
membre de E2.
Dans ce cas nous avons E1 x E2 = {(2, 1), (2, 3), (2, 6), (4, 1), (3, 3), (4, 6)}. Tout sous-semble
de ce produit est une relation, par exemple nous pouvons produire une relation R = {(2, 1), (1,
4)}. Nous pouvons imposer une condition à sélection de nos sous-ensembles. Par exemple :
R = {(x, y) | x є E1, y є E2 et y=1} ou S = {(x, y) | x є E1, y є E2 et x=2y}

2 Les relations des bases de données

Une base de donnée est constituée des tables composées à leur tour des lignes parmi lesquelles la
première est particulière parce qu’elle contient les attributs. Si (A1,…,An) est une suite d’attributs
avec les domaines de valeurs dom(Ai) , 1 ≤ i ≤ n, alors une relation r est un sous-ensemble du
produit cartésien de ce domaine.
r ⊆ dom (A1) ×…× dom(An)
Exemple
Livre InvNr PAut Aaut Titre
123 Date n Intro DBS
234 Jones y Algorithms
345 King n Operating Systems

Emprunt InvNr LecteurNr DateR

123 225 22.06.2002
234 347 y

Lecteur LecteurNr Name …

347 Pierre
234 Laure
L’application de notions sur les relations mathématiques nous permet de définir un schéma de la
relation.
Un schéma relationnel est une relation nommée définie par un ensemble de paires d’attribut et de
nom de domaine. Un schéma relationnel a la forme R=(X, ΣX). Il comporte un nom (R), un
ensemble d’attributs ou un format de relation X et un ensemble de dépendance de données intra
relationnelles.
Livre =({InvNr, Paut, Titre, Maison}, {InvNr Æ {InvNr, Paut, Titre, Maison}})
ou sous une forme compacte Livre =({InvNr, Paut, Titre, Maison}, {InvNr })
Un exemple d’instance de la relation Livre est {(234, Jone, y, Algorithms)} ou alors plus
exactement {(InvNr :234, PAut :Jone, :Aaut :y, Titre :Algorithms)}.
a) Les propriétés des relations
Une relation a les propriétés suivantes ;
• Une relation a un nom distinct de ceux de toutes les autres relations du schéma
relationnel
• Chaque cellule de la relation contient exactement une seule valeur atomique
• Chaque attribut a un nom distinct
• Les valeurs d’un attribut font, toutes, partie du même domaine
• Chaque tuple est distinct, il n’y a aucun tuple en double
• L’ordre des attributs n’est pas significatif
Nous supposons que chaque domaine de valeurs d’un attribut apparaissant dans un format de
relation est élémentaire c’est-à-dire qu’il ne contient pas des ensembles ou d’autres attributs
comme éléments. Si cette condition est remplie, l’on dit que R est en première forme normale
(1NF).

b) Les clés des relations

Les clés relationnelles sont un ou plusieurs attributs qui identifient de manière unique chaque
tuple dans une relation. Une superclé est un attribut ou un ensemble d’attributs qui identifie de
façon unique un tuple au sein d’une relation. Une clé canditate C doit remplir les conditions
suivantes :
L’unicité, dans chaque tuple R, les valeurs de C identifient de façon unique ce tuple
L’irréductibilité, aucun sous-ensemble de C n’a la propriété d’unicité
La clé primaire (primary key) est la clé candidate choisie pour identifier de facon unique les
tuples au sein de la relation.
La clé étrangère ou secondaire est un attribut ou un ensemble d’attributs d’une relation qui
correspond à une clé candidate d’une relation.

3 Transformation d’un diagramme en modèle relationnel

Il s’agit de passer de la représentation graphique (modèle E/R) ou de la description
mathématique à la présentation dans des tables de la base de données. Pour chaque type d’entité,
nous introduisons un schéma relationnel propre.

Livre InvNr, Titre, Maison,

Lieu, Année

L’on obtient après avoir enlevé les Auteurs du livre la transformation suivante
Livre InvNr Titre Maison Lieu Année

AuteursLivre InvNr Autor

Les relations m :n. L’on ne possède pour ce type d’associations que la notion de relation. Il est
nécessaire de la transformer sous une forme plus simple. Il est nécessaire de la transformer sous
une syntaxe plus simple de type 1 :1 ou 1 :n.
Par exemple pour la relation Exporte, l’on a, en supposant que chaque pays possède une désignation, et chaque produit à un numéro
(ProduitNr), la table suivante.
Pays

exporte PaysNr ProduitNr Valeur m

exporte

La clé est constituée ici de deux attributs PaysNr et ProduitNr.

n
Produit

Relations 1 :1 et 1 :n. Leur transformation est simple. Par exemple pour la relation Emprunt,
l’on peut créer facilement une nouvelle table.
Mais en introduisant les clés LecteurNr et l’attribut DateR parmi les attributs du schéma
relationnel Livre, l’on obtient :
 Livre InvNr Titre MaisonNom MaisonLieu Année LecteurNr DateR

L’on obtient ainsi une optimisation par réduction du nombre de tables à gérer.
La relation is-a : Pour le cas Pilot ⊆ Employé, l’on obtient une table
Pilote EmployéNr Heures Licence

4 Conditions ou contraintes d’intégrité

Elles sont les différentes restrictions à imposer sur l’ensemble des valeurs autorisées pour les
attributs des relations. Elles sont multiples : contraintes de domaine, de clé, intégrité d’entité et
l’intégrité référentielle. La contrainte de domaine impose que les valeurs de chaque attribut soit
simple c’est-à-dire non composée (atomic value). Les valeurs permises dans les domaines sont :
integers (short-integer, integer, long integer), real (float and double-precision float), characters,
fixed-length strings, and variable length, date, time, timestamp, money.
L’intégrité d’entité stipule que la valeur de la clé primaire ne peut pas être nulle. En effet, la
valeur de la clé primaire est utilisée pour identifier les n-uplets individuels dans la relation.
L’intégrité référentielle, elle, est spécifiée entre deux relations et est utilisée pour maintenir la
consistance entre les n-uplets et les relations. D’une manière informelle, l’intégrité référentielle
stipule qu’un n-uplet dans une relation qui se réfère à une autre relation doit se référer à un n-
uplet existant dans cette relation.
Un ensemble d’attributs FK dans un schéma relationnel R1 est une clé étrangère (foreign key)
de R1 qui se réfère à la relation R2 si les deux conditions suivantes sont remplies :
• Les attributs de FK ont le(s) même(s) domaine(s) que les attributs de la clé primaire
PK de R2 ;
• Une valeur de FK dans le n-uplet t1 dans l’actuelle état r1(R1) apparaît soit comme
valeur de PK dans un n-uplet t2 de l’actuel actuel r2(R2) ou est nul.
•

4.1 Opérations d’actualisation et de contraintes

Les opérations sur un modèle relationnel peuvent se classer en deux catégories : retrievals et
updates.
 Insert operation: Elle a une liste d’attributs pour un nouveau n-uplet t à insérer dans une
relation R. Insert <’Cecilia’, ‘F’, ‘Kolonsky’, null, ‘1960-05-05’, ‘6537 Windy Lane, Katy, Tx’,
F, 28000, ‘987654321’, 4> into Employee
Si l’insertion viole une ou plusieurs règles de contraintes d’intégrité, alors la réaction par
défaut consiste à rejeter l’insertion.
Delete operation : Delete the works_on tuple with ESSN=’999887777’ et ‘PNO=10’.
Update operation est utilisé pour changer les valeurs d’un ou de plusieurs attributs dans un n-uplet (des n-uplets) d’une certaine relation R.
Il est nécessaire de spécifier une condition sur les attributs de la relation dont veut sélectionner les n-uplets à changer.

Update the salary of the Employee tuple with SNN=’999887777’ to 280000

5 Les opérations de base de l’algèbre relationnelle

En plus de la définition de la structure et des contraintes de la base de données, le modèle de
donnée doit permettre un ensemble d’opérations pour manipuler les données. Un ensemble de
base d’opérations sur le modèle relationnel constitue l’algèbre relationnelle. Ces opérations
permettent à l’utilisateur la spécification des requêtes de base. Les opérations algébriques livrent
de nouvelles relations qui à leur tour peuvent être manipulées. Une séquence d’opérations
algébriques (relationnelles) constitue une expression d’algèbre relationnelle dont le résultat peut
encore être relation.
Les opérations de l’algèbre relationnelle sont subdivisées en deux catégories : les opérations de
la théorie mathématique des ensembles : UNION, INTERSECTION, DIFFERENCE,
CARTESIAN PRODUCT. L’autre groupe sont des opérations définies spécifiquement pour les
bases de données relationnelles : SELECT, PROJECT, JOIN.
SELECT : Elle est utilisée pour sélectionner un sous-ensemble de n-uplets d’une relation
donnée qui satisfont à une condition de sélection. L’on peut considérer l’opération SELECT
comme un filtre qui ne garde que les n-uplets satisfaisant à une condition qualifiante. Par
exemple pour sélectionner les n-uplets d’EMPLOYEE dont le département est 4 ou ceux dont le
salaire est supérieur à 30000 l’on peut procéder comme suit
σ DNO = 4 ( EMPLOYEE)
σ SALARY>30000 (EMPLOYEE)
SELECT a la forme générale σ <selection condition> (R )
 où σ (sigma) désigne l’opérateur SELECT et la condition de sélection est une expression
booléenne spécifiée sur les attributs de la relation R. L’expression booléenne est composée des
clauses
<attribute name><comparison op><constant value>
<attribute name><comparison op><attribute name>
<comparison op> sont {=,<,≤, >, ≥, ≠}
<constant value> est une valeur constante du domaine de l’attribut en question. Les clauses
peuvent être connectées arbitrairement par les opérations booléennes AND, OR et NOT pour
former des conditions de sélection plus générales. Par exemples pour sélectionner les lignes de
tous les employés qui travaillent soit pour le département 4 et ont plus de 25000 par an ou ceux
qui travaillent pour le département 5 et ont plus de 30000 par an, l’on peut procéder comme suit.
σ (DNO = 4 AND SALARY > 25000) OR (DNO = 5 AND SALARY >30.000) (EMPLOYEE)
PROJECT : Considérant une relation comme une table, l’opération SELECT sélectionne
certaines lignes et oublie d’autres, alors que l’opération PROJECT sélectionne certaines
colonnes. Par exemple pour lister le prénom, le nom de famille et le salaire de chaque employé,
nous utilisons l’opération
πLNAME, FNAME, SALARY(EMPLOYEE)
La forme générale est π<attribute list>(R)
où π(pi) est le symbole utilisé pour l’opération PROJECT et <attribute list> est un sous-
ensemble des attributs de la relation R. Le résultat de l’opération PROJECT ne comporte que les
attributs listés dans <attribute list> et dans l’ordre tels qu’ils apparaissent dans <attribute list>.
Le degré de l’opération est le nombre d’attributs apparaissant dans <attribute list>. L’opération
PROJECT élimine tous les doubles de n-uplets en question. Cette relation est connue comme
élimination de duplicata.
Séquences d’opération et renommination (renaming)
L’on peut appliquer des opérations à des relations et sauvegarder des résultats intermédiaires. Par
exemple pour ressortir tous les prénoms, les noms de famille, le salaire de tous les employés
travaillant pour le département 5, il faut appliquer une sélection et une projection.
πLNAME, FNAME, SALARY( σ DNO = 5 ( EMPLOYEE ))
Cependant l’on peut aussi nommer les expressions intermédiaires
DEPT5_EMPS Å σ DNO = 5 ( EMPLOYEE )
RESULT Å πLNAME, FNAME, SALARY(DEPT5_EMPS)

L’on peut alors définir RENAME qui peut renommer le nom de la relation, les attributs ou les
deux. L’opération RENAME appliquée à une relation R de degré n est dénotée par
ρS(B1,B2,…,Bn)(R) ou
ρS(R)
ρ(B1,B2,…,Bn)(R)
où ρ (rho) est utilisé pour dénoter l’opérateur RENAME
S est le nouveau nom de la relation et
B1, B2,…, Bn sont les noms attributs
La première variante renomme aussi bien le nom de la relation que les attributs
La deuxième renomme seulement la relation
La troisième seulement les attributs
Si les attributs de R sont (A1, A2, …, An) alors chaque Ai est renommé en Bi.

6. Les opérations sur la théorie des ensembles

Pour ressortir le numéro de sécurité sociale de tous les employés qui travaillent soit pour le
département 5 ou supervise directement un employé qui travaille dans le département 5, l’on
peut utiliser l’opération UNION.
DEPT5_EMPS Å σ DNO = 5 ( EMPLOYEE )
RESULT1 Å πSSN(DEPT5_EMPS)
RESULT2 Å πSUPERSSN(DEPT5_EMPS)
RESULT Å RESULT1 ∪ RESULT2

UNION : Le résultat de cette opération dénotée par R ∪ S inclut tous les n-uplets de R et tous
ceux de S. Les duplicata sont éliminés.
INTERSECTION : Le résultat dénoté par R ∩ S inclut tous les n-uplets qui sont éléments de R
et de S.
SET DIFFERENCE : le résultat dénoté par R-S inclut tous les n-uplets qui sont dans R et non
dans S.
Le produit cartésien (CARTESIAN PRODUCT ou CROSS PRODUCT ou encore CROSS
JOIN) est dénoté par ×. En général, le résultat de R(A1,A2, …, An) × S(B1,B2, …, Bm) est une
relation Q(A1,A2, …, An,B1,B2, …, Bm) avec n+m attributs. Si R a nR uplets et S nS uplets alors R
× S a nR * nS.
Par exemple si l’on veut ressortir tous les employés dépendants d’un employé de sexe féminin.
FEMALE_EMPS Å σ SEX =’ F’ ( EMPLOYEE )
EMPNAMES Å πFNAME, LNAME, SSN(FEMALE_EMPS)
EMP_DEPENDENTS Å EMPNAMES × DEPENDENT
ACTUAL_DEPENDENT Å σ SSN =ESSN (EMP_DEPENDENTS)
RESULT Å πFNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPENDENT )

Le produit cartésien des n-uplets avec les attributs combinés de deux relations.

JOIN : L’opération JOIN ⋈ est utilisée pour combiner les n-uplets de deux relations dans des n-
uplets simples. Cette opération est très importante pour les bases de données relationnelles avec
plus d’une relation car elle permet de traiter les relations entre les tables.
Pour ressortir le nom des managers, l’on a besoin de combiner chaque n-uplet de département
avec celui d’employé dont la valeur SSN correspond au MGRSSN dans le n-uplet de
département.

DEPT_MGR Å DEPARTMENT ⋈MGRSSN=SSN EMPLOYEE

RESULT Å π DNAME, LNAME, FNAME (DEPT_MGR)

Il faut noter que MGRSSN est une clé étrangère (secondaire) et que la contrainte d’intégrité
référentielle joue un important rôle dans le fait de trouver des n-uplets dans la table
EMPLOYEE. L’on peut exprimer le produit cartésien précédent par un JOIN
EMP_DEPENDENTS Å EMPNAMES × DEPENDENT
ACTUAL_DEPENDENT Å σ SSN =ESSN (EMP_DEPENDENTS)
Les deux lignes deviennent simplement

ACTUAL_DEPENDENT Å EMPNAMES ⋈SSN=ESSN DEPENDENT

La forme générale pour deux relations R(A1,A2, …, An) et S(B1,B2, …, Bm) est

R ⋈<join condition> S
Le résultat d’un JOIN est une relation Q(A1,A2, …, An,B1,B2, …, Bm) avec n+m attributs. Q a un
n-uplet pour chaque combinaison de n-uplets chaque fois que la combinaison satisfait à la
condition de jonction. Ceci constitue la différence principale du CARTESIAN PRODUCT avec
le JOIN. Dans le JOIN, seuls les n-uplets satisfaisant à la condition sont admis dans la table
résultat alors que dans CARTESIAN PRODUCT toutes les combinaisons sont acceptables. La
condition générale du JOIN est la suivante
<condition> AND <condition> AND … AND <condition>
ou chaque condition est de la forme Ai θ Bj
avec Ai est l’Attribut de R et Bj est l’attribut de S.
Ai et Bj ont le même domaine et θ est l’un de opérateurs de comparaisons {=,<,≤, >, ≥, ≠}. Une
opération JOIN avec une telle condition de JOIN est appelée THETA JOIN. Quand l’opération
est l’égalité, l’on parle d’EQUIJOIN.
DIVISION : En général l’opération DIVISION est appliquée à deux relations R(Z) ÷S(X) avec X
⊆ Z. Le résultat de la division est une relation T(Y) qui comporte un n-uplet t si des n-uplets tR
apparaissant dans R avec tR[Y] = t et avec tR[X] =tS pour chaque n-uplet tS de S. Cela signifie
que, pour qu’un n-uplet t apparaisse dans le résultat T de la division, les valeurs de t doivent
apparaître dans R en combinaison avec chaque n-uplet dans S.

7. Fonctions d’agrégation et de regroupement

Il existe certaines fonctions qui ne sont pas couvertes par les opérations mathématiques précitées.
Quelques unes concernent par exemple la moyenne, la somme des salaires, le nombre total des
employés. Les fonctions les plus utilisées sur les collections des valeurs numériques sont SUM,
AVERAFE, MAXIMUM, MINIMUM. La fonction COUNT est utilisée pour compter les n-
uplets ou les valeurs.
Une fonction d’agrégat utilisant F est spécifiée comme suit

<grouping attribute>F <function list>(R)

ou <grouping attribute> est une liste d’attribut de la table spécifiée dans R., <function list > est
uneliste de paires (<function><attribute>). Function est l’une des fonctions suivantes SUM,
AVERAGE, MAXIMUM, MINIMUM, COUNT. attribute est un attribut de la table spécifiée
dans R.

ρR(DNO,NO_OF_EMPLOYEES, AVERAGE_SAL)(DNO F COUNT SSN, AVERAGE SALARY(EMPLOYEE)

 Chapitre 5 : Le langage standard SQL
SQL est un langage complet. SQL est un acronyme de Structured Query Language. Un effort
commun de l’ANSI et de l’ISO a conduit à une version standard de SQL appelé SQL-86 ou
SQL1. Une version révisée l’a étendu en lui donnant le nom de SQL2 (SQL-92). En projet,
SQL3 sera augmenté avec des propriétés orientées objet.
Il comporte des instructions pour la définition des données, les requêtes, et les updates
(actualisation). Il est donc à la fois DDL et DML. Il comporte aussi des facilités pour la
définition des vues (views) sur la base de données, la spécification de la sécurité et de
l’autorisation, la définition des contraintes d’intégrité et la spécification du contrôle des
transactions. SQL est un langage orienté transformation, c’est-à-dire un langage concu pour
utiliser des relations et transformer des entrées en des sorties requises

4.1 Les objectifs de SQL

Un Langage de base de données doit permettre à un utilisateur de :
• Créer la structure de la base de données et de ses tables
• Exécuter les tâches de base de la gestion des données, telles que l’insertion, la
modification et la suppression de données des tables
• Effectuer des requêtes simples ou complexes

1. La définition des données et écriture de commandes SQL

SQL utilise les termes table (table), colonnes (columns) et lignes (rows) pour désigner
relation, attribut et les n-uplets. Les commandes pour la désignation des données sont create,
alter, drop. Une instruction SQL est ainsi constituée de mots réservés ou mot mots-clés et de
mots définis par l’utilisateur. La plupart des instructions SQL sont insensibles à la casse, ce qui
signifie que les composants peuvent en être rédigés en minuscules ou en majuscules.
Pour définir les instructions et commandes SQL nous utilisons la notation de Backus Naur
(BNF, Backus Naur Form)
• Les lettres capitales (majuscules) représentent des mots réservés
• Les minuscules représentent les mots définis par l’utilisateur
 • Une barre verticale ( | ) indique un choix possible parmi des alternatives, par exemple
a|b|c
• Les accolades indiquent un élément obligatoire, par exemple {a}
• Les cochets indiquent un élément facultatif, par exemple [a]
• Des points de suspension (…) indiquent une répétition optionnelle d’un élément à
concurrence de zéro à plusieurs fois
Par exemple {a | b} (,c…)

1.1. Commande create table et les types de données

Le schéma est créé par la commande create schema comme suit

CREATE SCHEMA COMPANY AUTHORIZATION NGAHA ;

Le schéma s’appelle COMPANY et son propriétaire est Ngaha. La commande CREATE

TABLE quant à elle spécifie une nouvelle table en lui octroyant un nom et en spécifiant ses
attributs et ses contraintes. Les attributs sont spécifiés en premier. A chaque attribut l’on donne
un nom, un type pour spécifier les domaines de ses valeurs et les contraintes sur les attributs. La
clé, l’intégrité d’entité et les contraintes d’intégrité référentielle peuvent être spécifiées après la
déclaration des attributs. Mais, elles peuvent aussi être introduites plus tard par la commande
alter table.
L’on a la syntaxe
CREATE TABLE [Link] …
ou
CREATE TABLE EMPLOYEE …
La première variante montre une table qui appartient au schéma COMPANY alors que la
deuxième ne spécifie pas le schéma.

La syntaxe générale de la commande CREATE est la suivante

CREATE TABLE NomDeTable
{(nomDeColonne typeDeDonnée [NOT NULL] [UNIQUE]
[DEFAULT optionPrédéfinie][CHECK (conditionDeRecherche)][,…]
[PRIMARY KEY (listeDeColonnes),]
 {[UNIQUE (listeDeColonnes),] [,…]}
{[FOREIGN KEY (listeDeColonnesDeCléEtrangère)
REFERENCES NomDeTableParente[(listeDeColonnesDeCléCandidate)],
[MATCH{PARTIAL | FULL}
[ON UPDATE actionRéférentielle]
[ON DELETE actionRéférentielle]] [,….]}
{[CHECK (conditionDeRecherche)] [,…]})

Types de données et domaines de valeurs dans SQL2. Les types de données présents
couvrent les valeurs numériques, les chaînes de caractères, les chaînes de bits, la date et le temps.
Les valeurs numériques incluent les nombres entiers de diverses dimensions (integer ou int,
smallint), les nombres réels de précision différentes (float, real, double precision). SQL permet
de formater les par la fonction DECIMAL(i,j), ou DEC(i,j) ou NUMERIC (i,j) où i est la
précision, le nombre total de chiffres avant la virgule et j l’échelle (scale), le nombre de chiffres
après la virgule.
Les caractères et chaînes de caractères sont soit de longueur fixe CHAR(n) ou
CHARACTER(n) où n est le nombre de caractères soit de longueur variable, VARCHAR(n),
VARYING CHAR(n), CHAR VARYING(n), avec n le nombre maximum de caractères.
Les chaînes de bits sont soit fixe, de longueur n, BIT(n) ou de longueur variable BIT
VARYING( n). La valeur par défaut de n est 1.
Il existe de nouveaux types de données dans SQL2 : date et time. L’on peut spécifier
directement les tables dans les types des attributs mais l’on peut aussi introduire des domaines
comme suit
CREATE DOMAIN SSN_TYPE AS CHAR (9).
L’on peut alors utiliser SSN_TYPE en lieu et place de CHAR(9) partout où il apparaîtra.

Spécification des contraintes et des valeurs par défaut : Puisque SQL permet la valeur NULL
comme valeurs d’attribut, il est donc nécessaires de spécifier NOT NULL là où les valeurs
nulles ne sont pas permisses. Cela doit être toujours le cas pour les valeurs des clés primaires de
toutes les relations. Il est aussi possible d’introduire pour un attribut quelconque une valeur par
défaut avec le mot clé DEFAULT <value>. Suivant la spécification des contraintes
additionnelles peuvent être ajoutées.
La clause PRIMARY KEY introduit un ou plusieurs attributs comme la clé primaire de la
relation. Le mot clé UNIQUE spécifie des clés alternatives. L’intégrité référentielle est spécifiée
par la clause FOREIGN KEY.

CREATE TABLE EMPLOYEE

( FNAME VARCHAR( 15) NOT NULL,
MINI CHAR,
LNAME VARCHAR( 15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY(SSN),
FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER) );
CREATE TABLE DEPARTEMENT
( DNAME VARCHAR( 15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRBDATE DATE,
PRIMARY KEY(DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN));
CREATE TABLE DEPT_LOCATIONS
( DNUMBER INT NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY(DNUMBER, LOCATION),
FOREIGN KEY(DNUMBER) REFERENCES DEPARTMENT(DNUMBER));
CREATE TABLE PROJECT
( PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION VARCHAR(15),
DNUM INT NOT NULL,
PRIMARY KEY(PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY(DNUM) REFERENCES DEPARTMENT(DNUMBER));
CREATE TABLE WORKS_ON
( ESSN CHAR(9) NOT NULL,
PNO INT NOT NULL,
HOURS DECIMAL(3,1) NOT NULL,
PRIMARY KEY(ESSN,PNO),
FOREIGN KEY(ESSN) REFERENCES EMPLOYEE (SSN)
FOREIGN KEY(PNO) REFERENCES PROJECT(PNUMBER));
CREATE TABLE DEPENDENT
( ESSN CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX CHAR,
 BDATE DATE,
RELATIONSHIP VARCHAR(8),
PRIMARY KEY(ESSN,DEPENDENT_NAME),
FOREIGN KEY(ESSN) REFERENCES EMPLOYEE (SSN));

Figure 4.1 : Une illustration de la définition des schémas dans SQL2.

1.2. Commande drop schema and drop table

Quand un schéma avec toutes ses tables doit être supprimé, alors l’on utilise la commande DROP
SCHEMA qui a deux options : CASCADE et RESTRICT. Pour supprimer le schéma avec
toutes ses tables, ses domaines de définition et les autres éléments y relatifs, l’on utilise la
commande
DROP SCHEMA COMPANY CASCADE ;
Si l’option RESTRICT est choisie, le schéma ne sera supprimé que s’il ne contient aucun
élément. Quand une table n’est plus utilisée dans une base de donnée quelconque, par exemple
DEPENDENT dans COMPANY, l’on peut la supprimer par
DROP TABLE DEPENDENT CASCADE ;
En utilisant l’option RESTRICT la table DEPENDENT n’est supprimée que si elle n’est pas
référencée ailleurs dans d’autres tables.

4.1.3 Commande alter table

Une table peut être changée en utilisant la commande ALTER TABLE qui est une commande
d’évolution de schéma. Les actions possibles par la commande ALTER TABLE sont : inclure
ou supprimer une colonne (attributs), changer la définition de la colonne, ajouter ou enlever des
contraintes. Par exemple, pour ajouter un attribut dans la table EMPLOYEE pour tenir compte
des jobs des employés l’on peut exécuter la commande suivante
ALTER TABLE [Link] ADD JOB VARCHAR(12) ;

L’on doit ensuite remplir ce nouvel attribut sinon il contiendra partout des valeurs NULL. On
peut supprimer un attribut par la commande

ALTER TABLE <name of table> DROP <attribute name> OPTION

Avec la sémantique des options CASCADE et RESTRICT discutée plus haut.
L’on peut cependant aussi changer juste la définition d’une colonne en remplaçant une clause par
défaut par une nouvelle valeur ou en la supprimant.
Exemples
ALTER TABLE [Link] ALTER MGRSSN DROP DEFAULT;
ALTER TABLE COMPANY. DEPARTMENT ALTER MGRSSN SET DEFAULT ‘333445555’;

4.3 Les requêtes de base dans SQL

Le but de l’instruction SELECT est de rechercher et d’afficher des données provenant d’une ou
plusieurs tables. C’est une commande très puissante et effectuant l’équivalent des oipérations de
sélection, de projection et de conjonction de l’algèbre relationnelle en une seule instruction.
SELECT est la commande la plus utilisée. La forme primaire de l’instruction SELECT appelée
parfois mapping ou bloc select-from-where a la syntaxe suivante
SELECT <attribute list>
FROM <table list>
WHERE <condition>

où <attribute list> est une liste de noms d’attributs dont les valeurs sont à ressortir par la requête.
<table list> est la liste des noms de relations requises pour traiter la requête
<condition> est une expression booléenne qui identifie les n-uplets (les lignes) à ressortir par
requête.
Définition de la syntaxe de SELECT en BNF

SELECT [DISTINCT | ALL] {* | [expressionDeColonne [AS

nouveauNom]] [,…]}
FROM NomDeTable [alias] [, …]
[WHERE condition]
[GROUP BY listeDeColonnes][HAVING condition]
[ORDER BY listeDeColonnes]

expressionDeColonne représente un nom de colonne ou une expression, NomDeTable est le nom

d’une table. La séquence de traitement au sein de l’instruction SELECT est comme suit :
FROM spécifie la table ou les tables à utiliser
WHERE filtre les lignes selon une condition donnée
GROUP BY forme des groupes de lignes de même valeur de colonne
HAVING filtre les groupes sujets à une certaine condition
SELECT spécifie les colonnes qui doivent apparaître dans les résultats
ORDER BY spécifie l’ordre d’apparition des données dans le résultat

Query 0 : Retrieve the birthdate and the address of the employee(s) whose name is ‘John K.
Smith’
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME = ‘John’ AND MINIT=’K’ AND LNAME =’Smith’;

Cette requête correspond à l’expression de l’algèbre relationnelle

πBDATE, ADDRESS(σFNAME = ‘John’ AND MINIT=’K’ AND LNAME =’Smith’(EMPLOYEE))

Une requête SQL avec une seule relation dans la clause FROM est similaire à une paire
SELECT-PROJECT dans les opérations de l’algèbre relationnelle. La clause du SELECT
spécifie les attributs de la projection et la clause WHERE la condition de sélection.

Query 1 : Retrieve the name and address of all employees who work for the “Research”
department
SELECT FNAME, LNAME ; ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME = ‘Research’ AND ‘DNUMBER = DNO;
Q1 est un select-project-join.
DNAME = ‘Research’ est la condition de sélection du SELECT et la condition DNUMBER =
DNO est la condition du join.

Query 2 : For every project located in “Stafford”, list the project number, the controlling
department number, and the department manager’s last name, address, and birthdate
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTEMENT, EMPLOYEE
WHERE DNUM = DNUMBER AND MGRSSN = SSN AND PLOCATION = ‘Stafford’

Ici l’on a deux conditions de jonction (join). DNUM = DNUMBER relie un projet au
département qui le contrôle et MGRSSN = SSN relie le département contrôleur à l’employée qui
le dirige.
4.2.1 Traitement des noms d’attributs ambigus
Dans SQL le même nom peut être utilisé pour plus de deux attributs tant que ceux-ci sont
dans des relations différentes. Dans ce cas si une requête se rapporte À deux ou plus d’Attributs
avec le même nom, alors l’on doit le qualifier le nom de l’attribut avec le nom de sa relation pour
prévenir l’ambiguïté. On préfixe alors le nom de l’attribut par le nom de sa relation.
Si dans la requête Q1 les DNO et LNAME de EMPLOYEE étaient nommés DNUMBER et
NAME comme dans DEPARTMENT l’on serait obligé de désambiguïser en reformulant la
requête comme suit :

Q1A : SELECT FNAME, [Link], ADDRESS

FROM EMPLOYEE, DEPARTMENT
WHERE [Link] = ‘Research’ AND
[Link] = [Link]

L’ambiguïté peut aussi apparaître quand on se réfère à la même table plus d’une fois.

Query 3. For each employee, retrieve the employee’s first and last name and the first and last
name of his or her immediate supervisor.
SELECT [Link], [Link], [Link], [Link],
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE [Link]. = [Link]

Ici l’on est obligé d’introduire des alias pour la relation EMPLOYEE.

4.2.2 Absence de la clause WHERE et utilisation des astérisques

Une clause WHERE manquante signifie qu’il n’y a pas de condition de sélection, donc tous
les n-uplets de la relation spécifiée dans la clause FROM sont sélectionnés. Sémantiquement, elle
correspond à WHERE TRUE. Si plus d’une relation est spécifiée dans la clause FROM et qu’il
n’y ait pas de clause WHERE alors le produit cartésien sélectionné, c’est-à-dire toutes les
combinaisons de n-uplets possibles.

Queries 4 and 5: Select all employee SSNs (Q4) and all combinations [Link] and
[Link] in the database.
SELECT SSN
FROM EMPLOYEE
 SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT

NB. Il est extrêmement important de spécifier chaque condition de sélection et de jonction

(join) dans la clause WHERE. Ces conditions sont elles omises, de larges tables incorrectes
peuvent en résulter.
Pour ressortir toutes les valeurs des attributs des n-uplets sélectionnés, l’on n’a pas besoin de
le faire explicitement dans SQL. L’on spécifie juste un astérisque (*). Par exemple pour ressortir
toutes les valeurs des lignes dans EMPLOYEE qui travaillent dans le département 5, l’on aura la
requête suivante

Q1c: SELECT *
FROM EMPLOYEE
WHERE DNO = 5
Q1d: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME = ‘Research’ AND DNO = DNUMBER

Pour chaque employé du département ‘Research’, Q1d ressort tous les attributs et les attributs
du département pour lequel il ou elle travaille.

4.2.3 Les opérations ensemblistes en SQL

Le langage SQL permet de réaliser sur un groupe de deux tables les trois opérations de base de la
théorie des ensembles.
l'union
l'intersection
la différence
L'union de deux tables

nom prénom
Chose Jules
Machin Pierre
Truc Patrick
union
nom prénom
Pouf Jean
nom prénom
Chose Jules
Chose Jules
 Machin Pierre
Pouf Jean
= Truc Patrick

SELECT nom, prénom

FROM Table1
UNION
SELECT nom, prénom
FROM Table2;

L'intersection de deux tables

nom prénom
nom prénom
Chose Jules nom prénom
inter Pouf Jean =
Machin Pierre Chose Jules
Chose Jules
Truc Patrick

SELECT nom, prénom

FROM Table1
WHERE [Link] IN (SELECT nom FROM Table2) AND [Link]énom IN (SELECT
prénom FROM Table2);

La différence de deux tables

nom prénom
nom prénom nom prénom
Chose Jules
diff Pouf Jean = Machin Pierre
Machin Pierre
Chose Jules Truc Patrick
Truc Patrick

SELECT nom, prénom

FROM Table1
WHERE [Link] NOT IN (SELECT nom FROM Table2) AND [Link]énom NOT IN
(SELECT prénom FROM Table2);

Le produit cartésien de deux tables

nom prénom
nom prénom
Chose Jean
Chose X Jean =
Chose Jules
Machin Jules
Machin Jean
 Machin Jules

Le code SQL correspondant s'écrit (requêtes emboîtées):

SELECT [Link], [Link]énom
FROM Table1, Table2;

4.2.4 Sous-chaînes, opérations arithmétiques et tri

L’on peut procéder dans SQL à la comparaison des portions de string en utilisant
l’opérateur de comparaison LIKE. Les chaînes de caractères partielles sont spécifiées par deux
signes spéciaux : ‘%’ remplace un nombre arbitraire de caractères et (_, underscore) remplace un
seul caractère.

Q8 Retrieve all employees whose address is Houston, Texas.

SELECT FNAME; LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE ‘%HOUSTON, TX%’ ;

Pour ressortir les employés nés dans les années 1950s l’on peu procéder comme suit

Query 8A Find all employees born during the 1950s

SELECT FNAME; LNAME
FROM EMPLOYEE
WHERE BDATE LIKE ‘__5_______’

L’on peut aussi utiliser les opérateurs arithmétiques tels +, -, /, * dans les requêtes.
Q9: Show the resulting salaries if every employee working on the ‘ProductX’ project is given a
10 percent raise.
SELECT FNAME, LNAME, 1.1* SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN= ESSN AND PNO = PNUMBER AND PNAME = ‘ProductX’;

Q10 : Retrieve all employees in department 5 whose salary is between $30,000 and 40,000
SELECT *
FROM EMPLOYEE
WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5;

SQL permet aussi de trier les résultats par la clause ORDER BY.
Q11 : Retrieve a list of employees and the projects they are working on, ordered by department,
ordered alphabetically by last name, first name.
SELECT DNAME, LNAME, FNAME, PNAME
FROM DPEARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER = DNO AND SSN = ESSN AND PNO = PNUMBER
 ORDER BY DNAME, LNAME, FNAME
La valeur par défaut est la valeur ascendante (ASC), mais l’on peut aussi spécifier l’ordre
descendante (DESC). Le tri prendrait un autre ordre si l’on spécifiait
ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

4.4 Les requêtes complexes

Certaines requêtes exigent que les valeurs de la base de données soient récoltées pour être
utilisées dans une condition de comparaison. Cette situation est prise en compte dans les requêtes
imbriquées (nested queries).
Query 13: SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN (SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM= DNUMBER AND MGRSSN = SSN AND
LNAME = ‘Smith’)
OR
PNUMBER IN (SELECT PNO
FORM WORKS_ON, EMPLOYEE
WHERE ESSN = SSN AND LNAME =’Smith’);

En plus de l’opération IN qui compare une valeur v à un multi-set et livre TRUE si v est l’une
des valeurs de ce bag, il existe d’autres comparaisons tels ANY (SOME) en rapport avec les
opérateurs tels {=, <, ≤, >, ≥, ≠}. Le mot clé ALL peut être combiné avec tous ces opérateurs.
Pour renvoyer les noms des employés dont le salaire est supérieur à celui de tous les employés
du département 5, l’on a
SELECT LNAME, LNAME
FROM EMPLOYEE AS E
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
WHERE DNO = 5);

Q14 Retrieve the name of each employee who has a dependent with the same first name and
same sex as the employee.
SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE [Link] IN (SELECT ESSN
FROM DEPENDENT
WHERE [Link] = [Link] AND [Link] = SEX);
 4.3.1 Les fonctions EXISTS dans SQL
Dans SQL, la fonction EXISTS est utilisée pour tester si le résultat d’une requête imbriquée est
vide ou non.
Q14 SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE [Link] IN (SELECT *
FROM DEPENDENT
WHERE [Link] = ESSN AND [Link] = SEX AND [Link] =
DEPENDENT_NAME);

En général, EXISTS(Q) renvoie TRUE si le résultat de la requête contient au moins un n-uplet,

sinon il renvoie false. NOT EXISTS renvoie TRUE si le résultat de Q est vide.

Q15: Retrieve the names of employees who have no dependents

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN = ESSN)

4.5 Fonctions d’agrégat et de regroupement

Q16 Find the sum of salaries of all employees, the maximum salary, the minimum salary, and
the average salary.
SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG(SALARY)
FROM EMPLOYEE;

Q17/18: Retrieve the total number of employees in the company and the number of employees
in the ‘Research’ department
Q17. SELECT COUNT (*)
FROM EMPLOYEE
Q18 SELECT COUNT (*)
FROM EMPOYEE, DEPARTMENT
WEHRE DNO=DNUMBER AND DNAME = ‘Research’;

Dans certaines situations, l’on voudrait appliquer les fonctions d’agrégat á des sous-groupes de
relations. Dans ce cas l’on a besoin de regrouper les n-uplets qui ont les mêmes valeurs ou des
attributs appelés attributs de regroupement (grouping attributes). SQL prévoit la clause GROUP
BY à cette fin.

Q19 For each department, retrieve the department number, the number of employees in the
department and their average salary.
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO;

Ces n-uplets de EMPLOYEE sont regroupés selon l’attributs de regroupement DNO et les
fonctions COUNT et AVG sont appliquées à chaque groupe de n-uplets.
DNO Count(*) AVG(SALARY)
5 4 33250
4 3 31000
1 1 55000

Q20: For each project retrieve the project number, the project name, and the number of
employees who work on that project
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME

Il arrive parfois qu’après le regroupement, l’on veuille juste sélectionner les n-uplets satisfont à
une certaine condition. SQL prévoit la clause HAVING à cette fin.
Q21 For each project on which more that two employees work, retrieve the project number, the
project name, and the number of employees who work on the project
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) >2

PNUMBER PNAME Count(*))

2 ProductY 3
10 Computerization 3
20 Reorganization 3
30 Newbenefits 3

4.5 Instructions DELETE et UPDATE dans SQL

Dans SQL, trois commandes peuvent être utilisées pour modifier la base de données : INSERT,
DELETE et UPDATE
INSERT. Cette opération sert à ajouter un n-uplet (une ligne) à une table. L’on doit spécifier le
nom de la relation et la liste des valeurs pour cette ligne. Les valeurs doivent se présenter dans le
même ordre que les attributs correspondants tels que présenté lors de CREATE TABLE.
U1 :INSERT INTO EMPLOYEE
VALUES (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oak
Forest Katy, TX’, ‘M’, 37000, 987654321’, 4);

Une autre variante d’INSERT permet à l’utilisateur de spécifier les attributs pour lesquels l’on
veut insérer de nouvelles valeurs. Ces attributs doivent inclure tous ceux avec une spécification
NOT NULL :
U1A: INSERT INTO EMPLOYEE (FNAME,LNAME, DNO, SSN)
VALUES (‘Richard’, ‘653298653’, 4, ‘653298653’);

L’on peut cependant aussi utiliser une variante de la commande d’insertion en rapport avec la
création d’une nouvelle table que l’on veut remplir avec le résultat d’une requête.
CREATE TABLE DEPTS_INFO
(DEPT_NAME VARCHAR(15),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);

INSERT INTO DEPTS_INFO (DEPT:NAME, NO_OF_EMPS, TOTAL_SAL)

SELECT DNAME, COUNT(*), SUM (SALARY)
FROM DEPARTMENT JOIN EMPLOYEE ON DNUMBER = DNO
GROUP BY DNAME;

Commande DELETE: La commande DELETE supprime des n-uplets d’une relation. Elle inclut
une clause WHERE semblable à celle des requêtes SQL. Quand la clause WHERE est omise
tous les n-uplets de la relation sont supprimés.
DELETE FROM EMPLOYEE
WHERE LNAME = ‘Brown’ ;

DELETE FROM EMPLOYEE

WHERE DNO IN (SELECT DNUMBER
FROM DEPARTEMENT
WHERE DNAME = ‘Research’)

DELETE FROM EMPLOYEE;

La commande UPDATE: Elle est utilisée pour modifier les valeurs des attributs d’un ou de
plusieurs lignes sélectionnées. Elle est analogue à DELETE avec une clause de sélection
WHERE.
UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM = 5
WHERE PNUMBER = 10 ;

Plusieurs n-uplets peuvent être changés en même temps.

UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = ‘Research’);
 Chapitre 6: Normalisation

6.1 Introduction à la normalisation

Le processus de normalisation comme introduit par Codd en 1972 prend un schéma

relationnel et "certifie" à travers une série de tests s'il respecte une certaine forme normale. Le
processus allant d'une manière top down et évaluant chaque relation à partir des critères et de la
décomposition des relations peut-être considéré à juste titre comme une conception relationnelle
par l'analyse (relational design by analysis). Initialement Codd a proposé trois formes normales
qu'il a appelées forme normale première (1NF), deuxième (2NF) et troisième (3NF). Une
définition plus stricte du 3NF appelée Boyce-Codd normal form (BCNF) a été introduite plus
tard. Une quatrième (4NF) et une cinquième (5NF) ont été proposées qui, elles, sont basées sur
les dépendances multivaleurs et celles de jonction.
Normaliser une donnée peut-être vu comme un processus d'analyse des schémas relationnels
donnés basée sur les FD et les clés primaires pour obtenir les propriétés désirées de (1)
minimalisation de la redondance et (2) minimalisation des anomalies d'insertion, de suppression
et de changement.
La procédure de normalisation pourvoit donc les concepteurs de base de donnée avec :
- un cadre formel pour l'analyse des schémas relationnels basés sur les clés et les FD entre les
attributs
- une série de tests de forme normale qui peuvent être menés sur les schémas individuels de
manière que la base de donnée relationnelle peut être normalisée à un degré désiré.
La forme normale d'une relation se rapporte au degré le plus élevé de la condition de forme
normale.

6.2 1NF - Première forme normale

1NF rapporte que le domaine d'un attribut ne doit contenir que des valeurs atomiques (atomic)
simples et indivisibles et que la valeur d'un attribut dans un n-uplet doit être une valeur simple du
domaine de cet attribut. Ainsi 1NF exclut les ensembles des valeurs, les n-uplets de valeurs. En
d'autres mots, 1NF interdit les relations dans les relations ou les relations comme attributs ou n-
uplets.
EMPLOYEE f.k.
ENAME SSN BDATE ADDRESS DBUMBER
p.k

DEPARTMENT f.k.
DNAME DNUMBER DMGRSSN
 p.k.

DEPT_LOCATIONS
DNUMBER DLOCATION
p. k.

PROJECT f.k.
PNAME PNUMBER PLOCATION DNUM
p.k.

WORKS_ON
f.k. f.k.
SSN PNUMBER HOURS
p. k.

EMPLOYEE
ENAME SSN BDATE ADDRESS DBUMBER
Smith, John B. 123456789 1965-01-09 731 Fondren, Houston, TX 5
Wong, Franklin T. 333445555 1955-12-08 638 Voss, Houston, TX 5
Zelaya, Alicia J. 999887777 1968-07-19 3321 Castle, Spring, TX 4
Wallace, Jennifer S. 987654321 1941-06-20 291 Berry, Bellaire, TX 4
Narayan, Remesh K. 66688444 1962-09-15 975 Fire Oak, Humble, TX 5
English, Joyce A. 453453453 1972-07-31 5631 Rice, Houston, TX 5
Jabbar, Ahmad V. 987987987 1969-07-29 980 Dallas, Houston, TX 4
Borg, James E. 888665555 1937-11-10 450 Stone, Houston, TX 1

DEPARTMENT
DNAME DNUMBER DMGRSSN
Research 5 33344555
Administration 4 987654321
Headquarters 1 888665555

DEPT_LOCATIONS
DNUMBER DLOCATION
1 Houston
4 Stafford
5 Bellaire
5 Sugarland
5 Houston

PROJECT
PNAME PNUMBER PLOCATION DNUM
ProductX 1 Bellaire 5
ProductY 2 Sugarland 5
ProductZ 3 Houston 5
Computerization 10 Stafford 4
Reorganization 20 Houston 1
Newbenefits 30 Stafford 4

WORKS_ON
SSN PNUMBER HOURS
123456789 1 32,5
123456789 2 7,5
666884444 3 40,0
453453453 1 20,0
453453453 2 10.0
333445555 2 10,0
333445555 3 10,0
333445555 10 10,0
333445555 20 10,0
999887777 30 30,0
999887777 10 10,0
987987987 10 35,0
987987987 30 5,0
987654321 30 20,0
987654321 20 15,0
888665555 20 null
 Si l'on veut étendre la table Department dont la clé primaire est DNUMBER en introduisant
DLOCATIONS comme suit,

Une relation qui n'est pas 1NF

DEPARTMENT
DNAME DNUMBER DMGRSSN DLOCATIONS

Instance de la relation
DEPARTMENT
DNAME DNUMBER DMGRSSN DLOCATIONS
Research 5 33344555 {Bellaire, Sugarland, Houston}
Administration 4 987654321 {Stafford}
Headquarters 1 888665555 {Houston}

Relation 1NF avec redondance

DEPARTMENT
DNAME DNUMBER DMGRSSN DLOCATIONS
Research 5 33344555 Bellaire
Research 5 33344555 Sugarland
Research 5 33344555 Houston
Administration 4 987654321 Stafford
Headquarters 1 888665555 Houston

et en supposant que chaque département possède un certain nombre de localisations, alors la

nouvelle table n'est pas en 1NF car DLOCATIONS n'est pas une valeur atomare tel que le
montre la première valeur du n-uplet de l'instance de cette table. On peut essayer de normaliser
cette relation par les propositions alternatives suivantes :

1. Ôter l'attribut DLOCATIONS qui viole la 1NF et le placer dans une relation à part,
DEPT_LOCATIONS, avec la clé primaire DNUMBER de DEPARTMENT. La clé primaire de cette
relation est alors la combinaison {DNUMBER, DLOCATION}. Un n-uplet distinct existe pour
chaque localisation d'un département. Ceci décompose la relation non-1NF en deux relations
1NF.

2. Etendre la clé primaire de manière qu'il existe des n-uplets séparés dans la relation
DEPARTMENT pour chaque localisation de département. Dans ce cas, la clé primaire devient la
combinaison {DNUMBER, DLOCATION}. Mais cette solution a l'inconvénient d'introduire une
redondance dans la table.
 3. Si le nombre maximum de valeurs est connu pour cet attribut alors remplacer
DLOCATIONS par ces valeurs. Par exemple, si l'on sait que le maximum d'une location de
Department est 3 alors introduire DLOCATION1, DLOCATION2, DLOCATION3. Mais
l'inconvénient majeur réside dans le fait que des valeurs nulles sont introduites si les
départements possèdent moins de localisation.

De ces 3 solutions, la première est de loin la meilleure car elle ne souffre pas de redondances
et de limitation de valeurs. La 1NF exclut les attributs multivalués qui sont eux-mêmes
composés. De telles relations sont appelées nested relations (relations imbriquées).

EMP_PROJ
SSN ENAME PROJS
PNUMBER HOURS
Une instance possible avec l'imbrication

EMP_PROJ
SSN ENAME PNUMBER HOURS
123456789 Smith, John B. 1 32,5
2 7,5
666884444 Narayan, Ramesh K. 3 40,0
453453453 English, Joyce A. 1 20,0
2 20,0
333445555 Wong, Franklin T. 2 10,0
3 10,0
10 10,0
20 10,0
999887777 Zelya, Alicia J. 30 30,0
10 10,0
987987987 Jabbar, Ahmad V. 10 35,0
30 5,0
987654321 Wallace, Jennifer S. 30 20,0
20 15,0
888665555 Borg, James E. 20 null

décomposition en 1NF
EMP_PROJ1
SSN ENAME

EMP_PROJ2
SSN PNUMBER HOURS

Le schéma est représenté par EMP_PROJ (SSN, ENAME, {PROJ(PNUMBER, HOURS)}

La clé primaire est SSN pour EMP_PROJ pendant que PNUMBER est la clé primaire
partielle de la relation imbriguée. Pour normaliser cette relation en 1NF, l'on ressort les attributs
de la relation imbriguée dans une nouvelle relation et l'on y propage la clé primaire.
 Cette procédure peut être utilisée plusieurs fois pour "aplatir" une relation ayant plusieurs
niveaux d'imbrications.

6.3 2NF La Seconde Forme Normale

La 2NF est basée sur le concept de dépendance fonctionnelle complète. Une dépendance
fonctionnelle X -> Y est une dépendance fonctionnelle complète si la suppression d'un attribut A
appartenant à X signifie que la dépendance n'est plus valable c'est-à-dire ∀ (A) A ∈ X, (X-{A})
ne détermine plus d'une manière fonctionnelle Y. Dans le cas contraire, elle est dite partielle.

∃ A ∈ X, (X-{A}) -> Y.

Par exemple {SSN, PNUMBER} -> HOURS est dépendance fonctionnelle complète, car ni SSN
-> HOURS ni PNUMBER -> HOURS n'est valide.

Par contre {SSN, PNUMBER} -> ENAME est partielle à cause de SSN-> ENAME qui est
valide.

Pour tester la 2NF, il faut tester les dépendances fonctionnelles dont les attributs de la partie
gauche font partie de la clé primaire. Un schéma relationnel R est en 2NF si chaque attribut non
clé (non prime) A dans R est en dépendance fonctionnelle complète avec la clé primaire de R.
Les deux schémas relationnels, EMP_DEPT et EMP_PROJ suivants sont présentées avec leurs dépendances fonctionnelles. Les deux
souffrent d'anomalies d'actualisation.

EMP_DEPT

ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN

EMP_PROJ

SSN PNUMBER HOURS ENAME PNAME PLOCATION

FD1
FD2
FD3

La relation EMP_PROJ est en 1NF mais pas en 2NF puisque ENAME viole la régle 2NF à
cause de FD2. Il en est de même pour FD3 avec les attributs non clé PNAME et PLOCATION.
 Si un schéma relationnel n'est pas en 2NF, on peut la normaliser 2NF en un nombre de
relations 2NF dans lesquelles les attributs non clés sont associés seulement sur la partie de la clé
primaire sur laquelle ils sont complètement et fonctionnellement dépendants.

SSN PNUMBER HOURS ENAME PNAME PLOCATION

FD1
FD2
FD3

2NF NORMALIZATION

EP1 EP2 EP3

SSN PNUMBER HOURS SSN ENAME PNUMBER PNAME PLOCATION
FD1 FD2 FD3

6.4 3NF Troisième Forme Normale

3NF est basée sur le concept de dépendance transitive. Une dépendance fonctionnelle X->Y
dans un schéma relationnel R est une dépendance transitive s'il existe un ensemble d'attributs Z
qui n'est ni une clé candidat ni un sous-ensemble d'une clé R et X->Z et Z-> Y sont valables. La
dépendance SSN -> DMGRSSN est transitive dans EMP_DEPT.
A cause des dépendances SSN -> DNUMBER et DNUMBER -> DMGRSSN et DNUMBER
n'est clé elle-meme ni sous-ensemble d'une clé de EMP_DEPT.

EMP_DEPT
ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN

3NF NORMALIZATION
ED1 ED2
ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN

Normalisation en 3NF de EMP_DEPT

 6.5 Boyce-Codd Normal Form et Autres Formes Normales
Un schéma relationnel R est en Boyce-Codd Normal Form (BCNF) si pour chaque
dépendance fonctionnelle non triviale X->A dans R, X est une super clé de R. La seule
différence entre 3NF et BCNF réside dans la suppression de la condition de la 3NF selon laquelle
l'attribut doit être membre d'une clé candidat.

Cette condition est plus stricte que celle du 3NF . En effet,

R est en BCNF => R est en 3NF.

Il existe aussi d'autres formes normales telles 4NF et 5NF.

Cours conçu à partir des sources :

ƒ Systèmes de bases de données : Approche pratique de la conception, de l'implémentation
et de l'administration de Thomas Connolly et Carolyn Begg
ƒ Introduction aux bases de données de Kolyang