Introduction aux bases de données relationnelles

Formation
« Gestion des données scientifiques : stockage et consultation en
utilisant des bases de données »
24 au 27 /06/08
Introduction aux
bases de données relationnelles
Christine Tranchant-Dubreuil – UMR DIA-PC
[email protected]
Introduction
Base de données : ensemble structuré et organisé permettant le stockage de
grandes quantités d’informations afin d'en faciliter l'exploitation (ajout, mise à jour,
recherche de données).
Système de Gestion de Base de Données (SGBD) : ensemble de logiciels
systèmes permettant aux utilisateurs d'insérer, de modifier, et de rechercher
efficacement des données spécifiques dans une grande masse d'informations
(pouvant atteindre plusieurs milliards d'octets) partagée par de multiples
utilisateurs. Un SGBD est caractérise par un modèle de description des données.
Utilisateurs
- Mises à jour de la BD
- Interrogations
- Administration
- Gestion des fichiers
- Gestion de bases de données
SGBD
BD
SGBD : principaux objectifs
Indépendance physique : la façon dont les données sont définies doit être
indépendante des structures de stockage utilisées
Indépendance logique : un même ensemble de données peut être vu
différemment par des utilisateurs différents. Toutes ces visions personnelles des
données doivent être intégrées dans une vision globale.
Non redondance d’information : afin d’éviter les problèmes lors des mises à
jour, chaque donnée ne doit être présente qu’une seule fois dans la base
Partage des données : interrogations et modifications « en même temps »
dans un contexte multi-utilisateurs
Sécurité et reprise sur panne
SGBD : niveau de description des données
Niveau interne ou physique (le plus bas) : définit comment sont stockées
physiquement les données
Niveau logique ou conceptuel :
– décrit par un schéma conceptuel
– indique quelles sont les données stockées et quelles sont leurs relations
indépendamment de l’implantation physique
Niveau externe ou vue :
– propre à chaque utilisateur
– décrit par un ou plusieurs schémas externes
- permet une perception de tout ou partie de la base par un groupe donné
d’utilisateurs, indépendamment des autres
Quelques SGBD connus et utilisés
ACCESS : plate-forme Windows, mono-poste, licence commerciale
SQL SERVER : plate-forme Windows, mode client/serveur, licence commerciale
ORACLE : plate-formes
Windows et
Linux, mode client/serveur, licence
Windows
Linux,
commerciale
SYBASE
:
plate-formes
et
mode
client/serveur,
licence
commerciale
POSTGRESQL : plate-formes Windows et Linux, mode client/serveur, licence libre
MYSQL : plate-formes Windows et Linux, mode client/serveur, licence libre
Comparatif de SGBD :
http://fadace.developpez.com/sgbdcmp/
Architecture client/serveur
Client
réponse
requête
Serveur
Client
Le client émet une requête vers le serveur grâce à son adresse IP et le port,
qui désigne un service particulier du serveur
Le serveur reçoit la demande et répond à l'aide de l'adresse de la machine
cliente et son port
Modèle relationnel : quelques dates
1970
Papier fondateur de CODD sur la théorie des relations, fondement de la
théorie des bases de données relationnelles
1980 Apparition des SGBDr sur le marché (Oracle, Ingres,Informix, Sybase…)
1987 Standardisation du langage SQL (Structured Query Langage), qui étend
l’algèbre relationnelle
Objectifs du modèle relationnel
• proposer des schémas de données faciles à utiliser ;
• améliorer l’indépendance logique et physique;
• mettre à la disposition des utilisateurs des langages de haut niveau ;
• optimiser les accès à la base de données ;
• améliorer l’intégrité et la confidentialité ;
• fournir une approche méthodologique dans la construction des schémas.
• Données organisées sous forme de tables à deux dimensions et
manipulées par des opérateurs de l’algèbre relationnelle ;
• Etat cohérent de la base défini par un ensemble de contraintes
d’intégrité.
Schema relationnel
Relation Equipe(Code, Nom)
Code
Nom
DIV
DIVA
DYN
DYNADIV
GEN
GENOCA
Propriété (attribut, colonne,
champ) et type
- Indivisibilité : les données ne sont pas décomposables
- Domaine unique : les attributs ne peuvent prendre n’importe
quelle valeur (intervalle, type de données)
- Ordre : l’ordre des attributs n’a pas d’importance
3 propriétés
Schema relationnel
Relation Equipe(Code, Nom)
Code
Nom
DIV
DIVA
DYN
DYNADIV
GEN
GENOCA
Propriété (attribut, colonne, champ)
et type
Tuple (n-uplet, ligne,
enregistrement, record)
- Ordre des tuples sans d’importance
- Unicité des tuples : il ne peut y avoir de tuples identiques
=> identifiant (ou clé) : un ou plusieurs attributs permettent
d’identifier un enregistrement de la table
--
2 propriétés
Schema relationnel
Equipe (Code, Nom)
Clé primaire
Propriété Code
Code
Nom
DIV
DIVA
DYN
DYNADIV
GEN
GENOCA
Clé étrangère (clé primaire provenant d’une autre table)
Nom
Prénom
DateNaissa
nce
Code
Tremblay
Joseph
1950-03-12
DIV
Bouchard
Marie
1966-08-23
DYN
Girard
Johanne
1943-06-30
GEN
PERSONNE(Nom, Prenom, DateNaissance, #Code)
Avec Personne (Code) Equipe (Code)
Schema relationnel : contrainte de référence
Contrainte d’intégrité référentielle : toute valeur d’une clé étrangère est égale à
la valeur nulle ou à la valeur de la clé primaire à laquelle la clé étrangère se
réfère
Valeur nulle
– valeur conventionnelle introduite dans une relation pour représenter une
information inconnue ou inapplicable
– tout attribut peut prendre une valeur nulle excepté les attributs de la clé
primaire (contrainte d’entité)
Contraintes de domaine : contrainte d'intégrité qui impose qu'une colonne
d'une relation doit comporter des valeurs vérifiant une assertion logique
Algèbre relationnelle : définitions
Ensemble d’opérateurs qui s’appliquent aux relations
L’algèbre relationnelle permet de faire des recherches dans les relations
Résultat : nouvelle relation qui peut à son tour être manipulée
Opérations de base: sélection (noté σ), projection (π)
Opérations ensemblistes: produit cartésien (x), jointures (|X|), union (∪),
∪
intersection (∩),
différence (−), division (/)
Algèbre relationnelle : opérateur de base
SELECTION
sélection des nuplets satisfaisant un certain prédicat
σ(Numéro≥5)Personne
Numéro
Nom
Prénom
5
Durand
Caroline
1
Germain
Stan
12
Dupont
Lisa
3
Germain
Rose-Marie
Numéro
Nom
Prénom
5
Durand
Caroline
12
Dupont
Lisa
Algèbre relationnelle : opérateur de base
PROJECTION
Elimination de certains attributs d’une relation
Π(Nom)Personne
Numéro
Nom
Prénom
5
Durand
Caroline
1
Germain
Stan
12
Dupont
Lisa
3
Germain
Rose-Marie
Nom
Durand
Germain
Dupont
Germain
Algèbre relationnelle : opérateurs ensemblistes
Soient R et S deux relations de même schéma :
Union: T = R ∪ S
Intersection: T = R∩S
Différence: T=R-S
Produit cartésien T=RxS
différence (−), division (/)
Algèbre relationnelle : opérateurs ensemblistes
Union ∪
• syntaxe : R ∪ S
• sémantique : réunit dans une même relation les tuples de R et de S
R1 (A,B)
A
B
a
b
b
b
y
z
R2 (A,B)
A
B
u
v
y
z
R1 ∪ R2 A B
A
B
a
b
b
b
y
z
u
v
Algèbre relationnelle : opérateurs ensemblistes
Intersection ∩
• syntaxe : R ∩ S
• sémantique : sélectionne les tuples qui sont à la fois dans R et S
R1 (A,B)
A
B
a
b
b
b
y
z
R2 (A,B)
A
B
u
v
y
z
R1 ∩ R2 A B
A
B
y
z
Algèbre relationnelle : opérateurs ensemblistes
Difference • syntaxe : R - S
• sémantique : sélectionne les tuples de R qui ne sont pas dans S
R1 (A,B)
A
B
a
b
b
b
y
z
R2 (A,B)
A
B
u
v
y
z
R1 ∩ R2 A B
A
B
a
b
b
b
Algèbre relationnelle : opérateurs ensemblistes
Produit cartésien x
• but: construire toutes les combinaisons de tuples de deux relations
• syntaxe : R × S
• Sémantique : tuple de R est combiné avec chaque tuple de S
RxS
R (A,B)
S (C,D,E)
A
B
C
D
E
a
b
c
d
e
a
b
c
d
e
A
B
C
D
E
a
b
c
d
e
a
b
c
d
e
b
c
b
a
b
b
C
b
a
b
b
c
b
a
b
c
b
a
a
c
b
c
b
a
b
c
b
a
a
c
c
b
a
a
c
c
b a a c
n x m tuples
n tuples
m tuples
Algèbre relationnelle : opérateurs ensemblistes
Division
• relation composée des n-uplets tels que le produit cartésien avec le
diviseur soit un sous-ensemble de la relation dividende
Participer
Epreuve
Participer % Epreuve
Athlete
Nom
Nom
Athlete
Dupont
200 m
200 m
Dupont
Durand
400 m
400 m
Dupont
400 m
110 m H
Martin
110 m H
Dupont
110 m H
Martin
200 m
Algèbre relationnelle
Jointure naturelle
• but: créer toutes les combinaisons significatives entre tuples de deux
relations
significatives = portent la même valeur pour les attributs de même domaine !
• précondition: les deux relations ont au moins un attribut de même domaine
• Produit cartésien suivi d’une sélection
R (A,B)
S (B,C,D)
A
B
B
C
D
A
B
C
D
a
b
b
c
D
a
B
c
D
b
C
a
a
b
c
B
c
d
c
b
d
a
c
Langage SQL
langage normalisé de requête non procédural
• DDL : DATA DEFINITION LANGAGE
•CREATE
• DROP
• ALTER
• DML : DATA MANIPULATION LANGAGE
• INSERT
• DELETE
• UPDATE
• SELECT
• DCL : DATA CONTROL LANGAGE
• GRANT
• REVOKE
Langage SQL : DDL
CREATE TABLE
CREATE TABLE table (
col1 type 1 [NOT NULL] ,
col2 type2 [NOT NULL] …
)
Contraintes :
CONSTRAINT nom_contrainte
PRIMARY KEY (liste attributs clé primaire)
| NOT NULL immédiatement après la déclaration de
l’attribut
| CHECK (condition) après la déclaration de l’attribut
| UNIQUE après la déclaration de l’attribut
| FOREIGN KEY (clé étrangère)
REFERENCES nom_table (liste-colonne)
Langage SQL : DDL
CREATE TABLE Enseignant
(
Enseignant_ID integer,
Departement_ID integer NOT NULL,
Nom varchar(25) NOT NULL,
Prenom varchar(25) NOT NULL,
Grade varchar(25)
CONSTRAINT CK_Enseignant_Grade
CHECK (Grade IN ('Vacataire', 'Moniteur','ATER', 'MCF’),
CONSTRAINT PK_Enseignant PRIMARY KEY (Enseignant_ID),
CONSTRAINT "FK_Enseignant_Departement_ID"
FOREIGN KEY (Departement_ID)
REFERENCES Departement (Departement_ID)
ON UPDATE CASCADE ON DELETE CASCADE
);
Contrainte de domaine
Définition de la clé primaire
Définition de la clé étrangère
Langage SQL : DDL
ALTER TABLE
ALTER TABLE table
ADD (col1 type1, col2 type2 …)
| MODIFY (col1 type1, col2 type2 …)
| DROP PRIMARY KEY
| DROP CONSTRAINT nom_contrainte
DROP TABLE table
CREATE [UNIQUE] INDEX nom_index ON table (col1,col …)
Langage SQL : DML
SELECT
SELECT [DISTINCT] *
FROM table_1 [synonyme_1], table_2 [synonyme_1], …
[WHERE prédicat_1
AND [ou OR] prédicat_2 …]
SELECT [DISTINCT] exp_1 [AS nom_1], exp_2 ...
FROM table_1 [synonyme_1], table_2 [synonyme_1], …
[WHERE prédicat_1
AND [ou OR] prédicat_2 …]
Langage SQL : DML
SELECT
SELECT Nom, Prénom
FROM Etudiant
WHERE Ville = ‘Paris’ ;
SELECT Nom, Prénom
FROM Etudiant
WHERE Ville = ‘Paris’
AND Nom LIKE ‘Du%’;
SELECT Nom, Prénom
FROM Etudiant
WHERE Fax IS NULL;
SELECT Intitulé,
(NbSeances*3) AS NbHeures
FROM Cours
WHERE (NbSeances*3)
BETWEEN 24 AND 27 ;
SELECT Nom, Prénom
FROM Enseignant
WHERE Département_ID IN
(‘INFO’, ‘MATH’ , ‘ECO’)
Langage SQL : DML
PREDICAT DU WHERE
exp1
exp1
exp1
exp1
exp1
exp1
exp1
exp1
exp1
exp1
exp1
exp1
= exp2
!= exp2
> exp2
< exp2
<= exp2
>= exp2
BETWEEN exp2 AND exp3
LIKE exp2
IN (exp2, exp3, …)
NOT IN (exp2, exp3, …)
IS NULL
IS NOT NULL
SELECT Intitulé,
FROM Cours
WHERE NbSeances <=
( SELECT AVG(NbSeances)
FROM Cours);
Langage SQL : DML
Fonctions d’agrégation
• Un groupe est un sous-ensemble de lignes d’une table ayant la même valeur
pour un attribut.
• On peut effectuer des calculs statistiques
• COUNT, MIN, MAX, AVG, SUM, ORDER BY, GROUP BY
SELECT COUNT(nom)
FROM Etudiant ;
SELECT AVG(Capacité),
SUM(Capacité)
FROM Salle ;
SELECT Département_ID,
Nom, Prénom
FROM Enseignant
ORDER BY Département_ID
DESC, Nom, Prénom ;
SELECT Département_ID,
COUNT(Nom)
FROM Enseignant
GROUP BY Département_ID;
Langage SQL : DDL
Jointure
SELECT Nom, Prénom, Nom_Département
FROM Enseignant E, Département D
WHERE E.Département_ID = D.Département_ID ;
Jointure externe : sous Oracle
SELECT Nom, Prénom, Nom_Département
FROM Enseignant E, Département D
WHERE E.Département_ID = D.Département_ID (+);
S’il existe des enseignants attaché à aucun département, la valeur de
Département_ID sera NULL.
En SQL2 : [RIGHT | LEFT | FULL] OUTER JOIN
Langage SQL : DML
Insertion
INSERT INTO table(col1, col2, … coln)
VALUES (val1, val2, … valn)
Suppression
DELETE FROM table
WHERE prédicat
Mise à jour
UPDATE table
SET col1 = exp1, col2 = exp2
WHERE prédicat