BASES DE DONNÉES RELATIONNELLES Introduction BASES DE

I
BASES DE DONNÉES
RELATIONNELLES
Introduction
Christine Bonnet
sources : divers et
Cycle de vie du développement
d’un système
Stratégie et
Analyse
Conception
Prototypage
Documentation
Transition
Production
I-2
Christine Bonnet
sources : divers et
HISTORIQUE
PREMIÈRES GÉNÉRATIONS DE SGBD
1ère GÉNÉRATION :
Conception - 1960
Niveau Opérationnel - 1970
SGBD Hiérarchique et Réseau (CODASYL)
Exemples : IMS, IDSII, IDMS, etc.
2ème GÉNÉRATION :
Conception - 1970
Niveau Opérationnel - 1980
SGBD Relationnel (Concept Mathématique de Relations)
Exemples : ORACLE, SYBASE, ACCESS, INGRES, INFORMIX, etc.
SGBD conçus au départ pour des applications classiques de gestion.
I-3
Christine Bonnet
sources : divers et
BASES
BASES DE
DE DONNÉES
DONNÉES AVANCÉES
AVANCÉES ::
TROISIÈME
TROISIÈME GÉNÉRATION
GÉNÉRATION
3ème GÉNÉRATION :
Conception : 1980
Niveau opérationnel : 1990…2000
OBJECTIFS : Lever les limites des SGBDR
• Augmenter la puissance de calcul des LMD
• Augmenter la richesse sémantique des modèles de BD
APPROCHES IMPORTANTES :
• Les BD Relationnelles Objets
(ORACLE V.8i)
• Les BD déductives
I-4
Christine Bonnet
sources : divers et
Concept de Base de Données
Relationnelles
•• Dr.
Dr. E.
E. F.
F. Codd
Codd proposa
proposa le
le modèle
modèle relationnel
relationnel
en
en 1970.
1970.
•• Base
Base pour
pour un
un Système
Système de
de Gestion
Gestion de
de Bases
Bases de
de
Données
Données Relationnelles
Relationnelles (SGBDR).
(SGBDR).
•• Le
Le modèle
modèle relationnel
relationnel consiste
consiste en:
en:
–– Collection
Collection d’objets
d’objets ou
ou de
de relations
relations
–– Ensemble
Ensemble d’opérateurs
d’opérateurs pour
pour agir
agir sur
sur les
les
relations
relations
–– L’intégrité
L’intégrité des
des données
données pour
pour l’exactitude
l’exactitude et
et
la
la cohérence
cohérence
I-5
Christine Bonnet
sources : divers et
BASES
BASES DE
DE DONNÉES
DONNÉES RELATIONNELLES
RELATIONNELLES :: RAPPELS
RAPPELS
CARACTÉRISTIQUES
Bases de données
Stockage et gestion de gros volumes de données
Gros volumes de données
Données persistantes partagées
Technologies :
→ recherche efficace
→ mises à jour fiables
Requêtes
Efficacité de l’accès
Accès à des données
- persistantes,
- partagées,
- résistantes aux pannes,
- pouvant être accédées et mises à jour de façon concurrente
par d’autres applications.
Contraintes
Exprimées dans le langage de définition de données.
Langage de définition et de manipulation de données orienté utilisateurs : SQL.
I-6
Christine Bonnet
sources : divers et
Définition d’une Base de
Données Relationnelle
Une
Une BD
BD relationnelle
relationnelle est
est une
une collection
collection de
de
relations
-dimensionnelles
relations ou
ou de
de tables
tables bi
bi-dimensionnelles
BD
Nom de table : EMP
EMPNO
EMPNO
7839
7839
7698
7698
7782
7782
7566
7566
I-7
ENAME
ENAME
KING
KING
BLAKE
BLAKE
CLARK
CLARK
JONES
JONES
JOB
JOB
PRESIDENT
PRESIDENT
MANAGER
MANAGER
MANAGER
MANAGER
MANAGER
MANAGER
Christine Bonnet
DEPTNO
DEPTNO
10
10
30
30
10
10
20
20
Nom de table : DEPT
DEPTNO
DEPTNO
10
10
20
20
30
30
40
40
DNAME
DNAME
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
SALES
SALES
OPERATIONS
OPERATIONS
sources : divers et
LOC
LOC
NEW
NEW YORK
YORK
DALLAS
DALLAS
CHICAGO
CHICAGO
BOSTON
BOSTON
Stockage des données sur
différents médias
SALGRADE
SALGRADE
GRADE
LOSAL
HISAL
GRADE
LOSAL
HISAL
----------------- ----------------- ----------------DEPT
DEPT
11
700
1200
700
1200
DEPTNO
LOC
DEPTNO DNAME
DNAME
LOC
22
1201
1400
1201
1400
----------------- --------------------------- ------------------33
1401
2000
1401
2000
10
NEW
10 ACCOUNTING
ACCOUNTING
NEW YORK
YORK
44
2001
3000
2001
3000
20
DALLAS
20 RESEARCH
RESEARCH
DALLAS
55
3001
9999
3001
9999
30
CHICAGO
30 SALES
SALES
CHICAGO
40
BOSTON
40 OPERATIONS
OPERATIONS
BOSTON
Feuille de calcul
électronique
I-8
Christine Bonnet
Base de
données
Classeur
sources : divers et
STRUCTURE RELATIONNELLE
) CONCEPTS DE BASE DU MODÈLE RELATIONNEL
Domaine : ensemble de valeurs.
Exemples : Domaine produits = {aspirine, alcool, vitamine,…}
Domaine D1 = ensemble des entiers.
Attribut : variable prenant ses valeurs dans un domaine.
Exemple : l'attribut "n° représentant" prend ses valeurs dans D1.
Relation : une relation R sur un ensemble d'attributs U={A1, A2, …, An}
de domaines respectifs D1, D2, …, Dn est un sous-ensemble
du produit cartésien D1×D2 ×... ×Dn.
Exemple :
commande(n° commande, date commande, date livraison, n° client).
I-9
Christine Bonnet
sources : divers et
STRUCTURE RELATIONNELLE
Terminologie :
Chaque ligne d'une relation (table) est appelée n-uplet ou tuple.
L'ordre d'une relation est le nombre d'attributs (de colonnes) de la relation.
La cardinalité d'une relation est le nombre de tuples de la relation.
I-10
Christine Bonnet
sources : divers et
Terminologie des BD relationnelles
2
3
EMPNO ENAME
JOB
4
MGR
HIREDATE
SAL
COMM DEPTNO
6
------------- ------------ --------------------- -------- ---------------- ----------- --------------
1
I-11
7839 KING
PRESIDENT
7698 BLAKE
MANAGER
7782 CLARK
-----------
17-NOV-81
5000
10
7839
01-MAY-81
2850
30
MANAGER
7839
09-JUN-81
2450
10
7566 JONES
MANAGER
7839
02-APR-81
2975
20
7654 MARTIN
SALESMAN
7698
28-SEP-81
1250
1400
30
7499 ALLEN
SALESMAN
7698
20-FEB-81
1600
300
30
7844 TURNER SALESMAN
7698
08-SEP-81
1500
0
30
7900 JAMES
CLERK
7698
03-DEC-81
950
7521 WARD
SALESMAN
7698
22-FEB-81
1250
7902 FORD
ANALYST
7566
03-DEC-81
3000
20
7369
SMITH
CLERK
7902
17-DEC-80
800
20
7788
SCOTT
ANALYST
7566
09-DEC-82
3000
20
7876
ADAMS
CLERK
7788
12-JAN-83
1100
20
7934
MILLER
CLERK
7782
23-JAN-82
1300
10
Christine Bonnet
5
sources : divers et
30
500
30
STRUCTURE RELATIONNELLE
Dépendance Fonctionnelle (DF) : exprime une contrainte.
Soit X et Y deux sous-ensembles d'attributs de l'ensemble des attributs
d'une relation R, il existe une dépendance fonctionnelle de X vers Y,
notée X→Y, si pour une valeur donnée à chaque attribut de X, il correspond
au plus une valeur pour chaque attribut de Y.
⇔ lorsque 2 n-uplets ont les mêmes valeurs pour les attributs de X, ils ont
les mêmes valeurs sur les attributs de Y.
Exemple :
I-12
n° commande → date commande, date livraison, n° client.
Christine Bonnet
sources : divers et
STRUCTURE RELATIONNELLE
Type de dépendance fonctionnelle :
quelconque.
élémentaire :
U : ensemble d'attributs
X, Y ⊂ U, X→Y est élémentaire ⇔ ∃ X' ⊂ X tel que X' →Y
Exemple : n° commande, n° produit → n° client, n'est pas élémentaire.
directe :
X, Y ⊂ U, X→Y est directe ⇔ ∃ Z ⊂ U tel que X→Z et Z →Y.
Exemple : n° commande → n° client
n° client → nom client
n° commande → nom client, n'est pas directe.
I-13
Christine Bonnet
sources : divers et
STRUCTURE RELATIONNELLE
Clé candidate, clé primaire, clé étrangère :
Une clé candidate est un sous-ensemble minimal d'attributs tel qu'il existe
une dépendance fonctionnelle de ce sous-ensemble vers tout autre attribut
de la relation.
Une clé primaire est une des clés candidate (exemple : n° commande).
Une clé étrangère est un ensemble d'attributs, clé primaire d'une autre relation
(exemple : n° client dans la table commande).
Contraintes :
D'unicité de la clé : il ne peut exister deux n-uplets ayant la même valeur pour
une occurrence de relation.
D'intégrité référentielle : toute valeur d'une clé étrangère dans une relation doit
exister dans une autre relation comme valeur de clé primaire (exemple : toute
valeur de n° client dans la table commande doit exister dans la table client).
D'entité : tout attribut participant à une clé primaire ne doit pas avoir de valeur nulle.
I-14
Christine Bonnet
sources : divers et
STRUCTURE RELATIONNELLE
Forme normale d'une relation :
1FN : tous les attributs sont simples et la relation possède une clé primaire.
2FN : 1FN et tous les attributs non clé sont en DF élémentaire avec la clé.
3FN : 2FN et tous les attributs non clé sont en DF directe avec la clé.
I-15
Christine Bonnet
sources : divers et
Modèles de données
Modèle du
système dans
l’esprit du Modèle EA du modèle
du client
client
Modèle de tables du
modèle EA
Serveur
Tables sur
disques
I-16
Christine Bonnet
sources : divers et
Modèle EA
•• Création
Création d’un
d’un diagramme
diagramme EA
EA correspondant
correspondant aux
aux
spécifications
spécifications
EMPLOYEE
EMPLOYEE
#*
number
#*
number
**
name
name
oo
job
job title
title
assigned to
DEPARTMENT
DEPARTMENT
#*
number
#*
number
**
name
name
oo
location
location
•• Scénario
Scénario
–– “.
“. .. .. Attribuer
Attribuer un
un ou
ou plusieurs
plusieurs employés
employés àà un
un
département.
département. .. .”
.”
–– “.
“. .. .. Quelques
Quelques départements
départements n’ont
n’ont pas
pas encore
encore
d’employé.
d’employé. .. .”
.”
I-17
Christine Bonnet
sources : divers et
Lier plusieurs tables
•• Chaque
Chaque ligne
ligne dans
dans une
une table
table est
est identifiée
identifiée
de
de façon
façon unique
unique par
par une
une clé
clé primaire.
primaire.
•• Vous
Vous pouvez
pouvez lier
lier logiquement
logiquement des
des
données
données de
de plusieurs
plusieurs tables
tables en
en utilisant
utilisant
les
les clés
clés étrangères
étrangères
Nom de la table : EMP
EMPNO
7839
7698
7782
7566
ENAME
KING
BLAKE
CLARK
JONES
Clé primaire
I-18
JOB
PRESIDENT
MANAGER
MANAGER
MANAGER
Nom de la table : DEPT
DEPTNO
10
30
10
20
Clé étrangère
Christine Bonnet
DEPTNO
10
20
30
40
DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
Clé primaire
sources : divers et
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
Communiquer avec un SGBDR
en utilisant SQL
Un ordre SQL est
entré
SQL>
SQL> SELECT
SELECT loc
loc
22 FROM
FROM dept;
dept;
L’ordre est envoyé à
la BD
BD
Les données sont
affichées
LOC
LOC
------------------------NEW
NEW YORK
YORK
DALLAS
DALLAS
CHICAGO
CHICAGO
BOSTON
BOSTON
I-19
Christine Bonnet
sources : divers et
SGBDR
Serveur
Tables
utilisateur
I-20
Christine Bonnet
sources : divers et
Dictionnaire
des données
Solution Oracle Complète
Applications
Applications
Finance
Finance
Fabrication
Fabrication
...
...
SQL
SQL
Oracle 8/9/10
Oracle
Oracle Developer
Developer
Discoverer
Discoverer
Oracle
Oracle Designer
Designer
PL/SQL
PL/SQL
Database
SQL*
SQL* Plus
Plus
Dictionnaire
des données
Tables de
données
I-21
Christine Bonnet
sources : divers et
ORDRES SQL
I-22
SELECT
Recherche de données
INSERT
UPDATE
DELETE
Langage de manipulation de
données (DML)
CREATE
ALTER
DROP
RENAME
TRUNCATE
Langage de définition de
Données (DDL)
COMMIT
ROLLBACK
SAVEPOINT
Contrôle des transactions
GRANT
REVOKE
Langage de contrôle des
données (DCL)
Christine Bonnet
sources : divers et
Tables Utilisées dans le Cours
EMP
EMPNO
JOB
MGR
EMPNO ENAME
ENAME
JOB
MGR
----------------- ------------------- ----------------- ----------------7839
PRESIDENT
7839 KING
KING
PRESIDENT
7698
MANAGER
7839
7698 BLAKE
BLAKE
MANAGER
7839
7782
MANAGER
7839
7782 CLARK
CLARK
MANAGER
7839
7566
MANAGER
7839
7566 JONES
JONES
MANAGER
7839
7654
SALESMAN
7698
7654 MARTIN
MARTIN
SALESMAN
7698
7499
SALESMAN
7698
7499 ALLEN
ALLEN
SALESMAN
7698
7844
SALESMAN
7698
7844 TURNER
TURNER
SALESMAN
7698
DEPT
7900
CLERK
7698
7900 JAMES
JAMES
CLERK
7698
7521
WARD
SALESMAN
7698
7521
SALESMAN
7698
DEPTNO
LOC
DEPTNO DNAME
DNAMEWARD
LOC
7902
FORD
ANALYST
7566
7902
FORD
ANALYST
7566
-------------------------- -----------------------------------7369
SMITH
CLERK
7902
7369
SMITH
CLERK
7902
10
NEW
YORK
10 ACCOUNTING
ACCOUNTING
NEW
YORK
7788
SCOTT
ANALYST
7566
7788
SCOTT
ANALYST
7566
20
DALLAS
20 RESEARCH
RESEARCH
DALLAS
7876
ADAMS
CLERK
7788
7876
CLERK
7788
30
CHICAGO
30 SALES
SALESADAMS
CHICAGO
7934
MILLER
CLERK
7782
7934
MILLER
CLERK
7782
40
BOSTON
40 OPERATIONS
OPERATIONS
BOSTON
I-23
Christine Bonnet
HIREDATE
SAL
COMM
DEPTNO
HIREDATE
SAL
COMM
DEPTNO
----------------- ----------------- ----------------- ----------------17-NOV-81
5000
10
17-NOV-81
5000
10
01-MAY-81
2850
30
01-MAY-81
2850
30
09-JUN-81
2450
10
09-JUN-81
2450
10
02-APR-81
2975
20
02-APR-81
2975
20
28-SEP-81
1250
1400
30
28-SEP-81
1250
1400
30
20-FEB-81
1600
300
30
20-FEB-81
1600
300
30
08-SEP-81
1500
00
30
08-SEP-81
1500
30
03-DEC-81
950
30
03-DEC-81
950
30
22-FEB-81
1250
500
30
22-FEB-81
1250
500
30
03-DEC-81
3000
20
03-DEC-81 SALGRADE
3000
20
17-DEC-80
800
20
17-DEC-80
800
20
GRADE
LOSAL
HISAL
GRADE
LOSAL
HISAL
09-DEC-82
3000
20
09-DEC-82 --------3000 --------20
----------------- ----------------12-JAN-83
1100
20
12-JAN-83
110011
20
700
1200
700
1200
23-JAN-82
1300
10
23-JAN-82
130022
10
1201
1400
1201
1400
33
1401
2000
1401
2000
44
2001
3000
2001
3000
55
3001
9999
3001
9999
sources : divers et
Interaction entre SQL and
SQL*Plus
SQL Statements
Buffer
SQL Statements
Server
SQL*Plus
SQL*Plus
Commands
Query Results
Formatted Report
I-24
Christine Bonnet
sources : divers et
Ordres SQL / Commandes SQL*Plus
SQL
• Un langage
• Standard ANSI
• Mots clés : pas
d’abréviation
• Les ordres manipulent
des définitions de
données et de tables
de la base de données
SQL
statements
I-25
SQL
buffer
Christine Bonnet
SQL*Plus
• Un environnement
• Propriété d’Oracle
• Abréviation possible des
mots clés
• Les commandes ne
permettent pas la
manipulation des valeurs
de la base de données.
SQL*Plus
commands
sources : divers et
SQL*Plus
buffer
Aperçu de SQL*Plus
•• Ouvrir
Ouvrir une
une session
session SQL*Plus.
SQL*Plus.
•• Décrire
Décrire la
la structure
structure de
de la
la table.
table.
•• Éditer
Éditer vos
vos ordres
ordres SQL.
SQL.
•• Exécuter
Exécuter SQL
SQL àà partir
partir de
de SQL*Plus.
SQL*Plus.
•• Sauvegarder
Sauvegarder des
des ordres
ordres SQL
SQL dans
dans des
des
fichiers
fichiers et
et ajouter
ajouter des
des ordres
ordres SQL
SQL aux
aux
fichiers.
fichiers.
•• Exécuter
Exécuter des
des fichiers
fichiers sauvegardés.
sauvegardés.
•• Charger
Charger des
des commandes
commandes àà partir
partir d’un
d’un
fichier
fichier vers
vers un
un buffer
buffer pour
pour les
les éditer.
éditer.
I-26
Christine Bonnet
sources : divers et
Se connecter à SQL*Plus
• A partir de l’environnement Windows :
INVITE
ANTOINEBERMON
INVITE
ANTOINEBERMON
Lyon
Lyon
• A partir d’une ligne de commande:
connect [username[/password
[@database]]]
I-27
Christine Bonnet
sources : divers et
Afficher la structure des tables
Commande
Commande SQL*Plus
SQL*Plus :: DESCRIBE
DESCRIBE
DESC[RIBE]
DESC[RIBE] tablename
tablename
I-28
Christine Bonnet
sources : divers et
Afficher la structure des tables
SQL>
SQL> DESCRIBE
DESCRIBE dept
dept
Name
Name
--------------------------------DEPTNO
DEPTNO
DNAME
DNAME
LOC
LOC
I-29
Null?
Null?
--------------NOT
NOT NULL
NULL
Christine Bonnet
Type
Type
----------------------NUMBER(2)
NUMBER(2)
VARCHAR2(14)
VARCHAR2(14)
VARCHAR2(13)
VARCHAR2(13)
sources : divers et
Commandes d’édition de
SQL*Plus
•• A[PPEND]
A[PPEND] text
text
•• C[HANGE]
C[HANGE] // old
old // new
new
•• C[HANGE]
C[HANGE] // text
text //
•• CL[EAR]
CL[EAR] BUFF[ER]
BUFF[ER]
•• DEL
DEL
•• DEL
DEL n
n
•• DEL
DEL m
mn
n
I-30
Christine Bonnet
sources : divers et
Commandes d’édition de
SQL*Plus
•• I[NPUT]
I[NPUT]
•• I[NPUT]
I[NPUT] text
text
•• L[IST]
L[IST]
•• L[IST]
L[IST] n
n
•• L[IST]
L[IST] m
mn
n
•• R[UN]
R[UN]
•• n
n
•• n
n text
text
•• 00 text
text
I-31
Christine Bonnet
sources : divers et
Commandes de fichiers de
SQL*Plus
•• SAVE
SAVE filename
filename
•• GET
GET filename
filename
•• START
START filename
filename
•• @
@ filename
filename
•• EDIT
EDIT filename
filename
•• SPOOL
SPOOL filename
filename
I-32
Christine Bonnet
sources : divers et
EXEMPLE
SQL>
SQL> ed
ed exo1
exo1
--- sauvegarde
sauvegarde sur
sur le
le compte
compte personnel
personnel
--- tests
tests
SQL>
SQL> sta
sta exo1
exo1
--- modification
modification de
de exo1
exo1
SQL>
--Ouvrir
SQL> ed
ed
-- ou
ou Fichier
Fichier-Ouvrir
-ésultats dans
-- impression
impression des
des rrésultats
dans un
un fichier
fichier
SQL>
SQL> spo
spo exo1imp
exo1imp
-écution de
-- ex
exécution
de exo1
exo1
SQL>
SQL> spo
spo off
off
I-33
Christine Bonnet
sources : divers et