Cours Base de données Mohamed Anis BACH TOBJI 2è E-Commerce + E-Services

Cours Base de données
Mohamed Anis BACH TOBJI
Assistant à l’ESCE – Université de Manouba
2è E-Commerce + E-Services
Introduction
Base de Données (BD)
« Un ensemble structuré de données enregistrées sur
un ordinateur et accessibles à un ensemble
d’utilisateurs ».
Système de Gestion de Base de Données (SGBD)
« Un logiciel qui permet d’interagir avec une BD ».
Introduction
Limites des Système de Gestion de Fichiers (SGF)
Redondance des données
Manipulation lourde des données
La disponibilité des données est limitée à ce que les applications prévoient
La gestion des utilisateurs est effectuée par les applications
Introduction
Fonctions d’un SGBD
Description des données
Définition et Manipulation des données
Interrogation sélective des données
Définition de contraintes d’intégrité des données
Accès concurrentiel aux données
Procédures de reprise en cas d’accident
Gestion des utilisateurs
Introduction
Niveaux de description des données (ANSI/SPARC)
Niveau externe correspond à la perception de la base de données par les
utilisateurs finaux.
Niveau conceptuel description de toutes les données, de leurs
propriétés, des liens entre elles sans se soucier de la manière avec laquelle
les utilisateurs vont s’en servir (sans se soucier de l’implantation de la base).
Niveau interne s’appuie sur un système de gestion de fichiers pour
définir la stratégie de stockage, de placement et d’accès des données.
Introduction
Types d’utilisateurs
L’administrateur chargé de donner/enlever les droits d’accès aux
utilisateurs, de la configurer, de la sauvegarder, de la reprise en cas de
panne…
Le programmeur créé les différents objets de la BD qui seront manipulés
via son application. Il définit avec l’administrateur les droits d’accès pour les
différents utilisateurs finaux.
L’utilisateur final a accès aux données qui lui sont autorisées. En général,
il utilise les données sans les définir.
Evolution des SGBDs
Modèle hiérarchique
Une base de données hiérarchique est une forme de SGBD qui lie des
enregistrements dans une structure arborescente de façon à ce que chaque
enregistrement n’ait qu’un seul possesseur (par exemple, un produit n’est
livré que par un seul Fournisseur).
Ne tiens pas compte de certains cas du monde réel. Exemple; un patient
peut être traité par plusieurs médecins
Le modèle hiérarchique ne modélise que les relations « 1 à N »
Evolution des SGBDs
Modèle réseau
Modélise les relations de type « N à N » (un médecin peut avoir plusieurs
patients et un patient peut être traité par plusieurs médecins.
Le problème des modèles hiérarchiques et réseaux, c’est que les
applications dépendent des structures logiques des données.
Sur le marché
IMS (hiérarchique), Codasyl (réseau)…
Evolution des SGBDs
Modèle relationnel
Fondé sur la théorie mathématique des relations. La BD est représentée
sous forme de tables (lignes et colonnes).
Plus de pointeurs qui figeaient la structure de la base.
Développement d’un langage non procédural de gestion de données
(SQL). L’utilisateur ou le programmeur n’ont plus à naviguer dans la BD, ils
formulent leurs requêtes et c’est le SGBD qui se charge de les exécuter et
leur renvoyer le résultat.
Sur le marché
Oracle (Oracle), Informix, DB2 (IBM), Sybase (Sybase), SQL SERVER
(Microsoft), Postgress, Mysql…
Evolution des SGBDs
Modèle Orienté Objet
Enregistrement des données sous forme « d’objets » (au sens définie par la
POO).
Ainsi, les données sont enregistrés avec les fonctions/procédures qui les
manipulent.
Les éditeurs qui se sont intéressés à ce modèle se sont trouvés dans une
impasse (certains ont disparu) au vu de la réussite et de la demande
incessante des SGBDR (vs. SGBDOO).
Sur le marché
O2, Objectivity, Objectstore… (n’ont pas réussi)
Le modè
modèle relationnel
Notions de base
Relation
Une relation R est un sous-ensemble du produit cartésien de n ensembles
D1,D2,…,Dn les domaines des attributs A1,A2,…,An.
R est donc un ensemble de n-uplets (v1,v2,…,vn) tel que vi ∈Di appelés
occurrences.
Une relation est notée R(A1,A2,…,An).
Exemple
Soit la relation Personne(CIN,Nom,Genre)
DCIN={Toutes les CIN possible}
DNom={Ensemble de tous les noms possibles}
DGenre={M, F}
Le triplet (06125478,Kamoun,M) est
une occurrence de Personne
Le modè
modèle relationnel
Notions de base
Clé primaire
Chaque élément d’une relation R doit être unique.
Toute relation R doit avoir un identifiant, qui est un sous-ensemble des
attributs (Ai,Aj,…,Ak), appelé clé primaire.
Deux occurrences d’une même relation peuvent avoir les mêmes valeurs
pour tous les attributs, sauf pour la clé primaire.
Exemple
Dans la relation Personne(CIN,Nom,Genre), l’attribut CIN est la clé
primaire.
R peut contenir des occurrences qui ont les mêmes valeurs pour Nom et
Genre, mais pas pour CIN.
Le modè
modèle relationnel
Notions de base
Table
Dans les SGBDs, les relations sont représentées par des tables.
Les occurrences sont dites lignes, et les attributs sont dits colonnes.
Exemple
PERSONNE
CIN
Nom
Genre
06125478 Kamoun
M
05487964 Laâbidi
F
07548976 Kamoun
M
Le modè
modèle relationnel
Notions de base
Passage diagramme de classes - schéma relationnel
DEPT
Deptno
Dname
Location
Participer
EMP
Empno
1
0..*
Ename
contient
Job
Sal
Trois règles:
NbrH
0..*
0..*
PROJECT
Prjctno
Pname
1. Une classe est traduite en relation.
2. Une association «un à plusieurs» engendre la migration de la clé primaire de
la table mère à la table fille en clé étrangère.
3. Une association «plusieurs à plusieurs» est représentée par une relation
dont la clé primaire est composée par les clés primaires des tables associées.
Le modè
modèle relationnel
Notions de base
Passage diagramme de classes - schéma relationnel
DEPT
Deptno
Dname
Location
Exercice:
EMP
Empno
Ename
Job
Sal
Deptno
Participer
Empno
Prjctno
NbrH
PROJECT
Prjctno
Pname
Compléter le diagramme de classe suivant d’une entreprise commerciale qui veut
développer une application de facturation, et donner son schéma relationnel :
Classes : Facture(Nfact, date, montant), Client(Nclt,nom,adresse) et
Produit(Cpdt,libelle,stock,prix). L’association entre Produit et Facture est
porteuse de données (Qte).
Le modè
modèle relationnel
Algè
Algèbre relationnelle
Algèbre relationnelle
Définit un ensemble d’opérations qui sont appliquées aux relations d’une
base de données. Le langage SQL s’en est inspiré.
Il existe deux types d’opérateurs : relationnels et ensemblistes.
Opérateurs relationnels
La projection
La projection de R sur les attributs Ai, Aj,…,Ak est notée R[Ai,Aj,…Ak].
Exemple
La projection de EMP sur ENAME et JOB est notée EMP[ENAME,JOB].
Le modè
modèle relationnel
Opé
Opérateurs relationnels
Opérateurs relationnels
La restriction (ou sélection)
La restriction de R selon une condition C, est l’ensemble des occurrences de
R qui satisfont la condition C. Elle est notée R<C>.
Exemple
L’opération algébrique qui renvoie la liste des employés payés plus que 1000
est notée EMP<SAL>1000>.
La jointure
La jointure de deux relations R et S sur leurs colonnes respectives A et B est
la relation dont les occurrences sont obtenues par concaténation des
occurrences de R et ceux de S qui ont la même valeur pour A et B. Elle est
notée R►◄S<A=B>.
Le modè
modèle relationnel
Opé
Opérateurs relationnels
Opérateurs relationnels (suite)
Exemple
La jointure de EMP avec DEPT donne les informations des employés ainsi que
els informations de leurs départements EMP►◄DEPT. Ici, on ne met de
condition, car les attributs sur lesquels se fait la jointure portent le même
nom.
La non equi-jointure
On peut joindre deux relations avec une condition de comparaison différente
de l’égalité.
Exemple
La jointure de la relation EMP à SALGRADE calcule les grades des employés,
elle est notée EMP►◄SALGRADE<SAL BETWEEN LOSAL AND HISAL>.
Le modè
modèle relationnel
Opé
Opérateurs relationnels
Opérateurs relationnels (suite)
La division
Supposons une relation R avec les attributs A et B et une relation S avec
l’attribut B. La division de R par S est la relation D=R÷BS
D = {a ∈ R[A]/ ∀ b ∈ S, (a, b) ∈ R} = {a ∈ R[A]/ ∃/b ∈ S, (a, b) ∉ R}
Exemple
R
S
A
B
a1
b1
a1
b2
a2
b2
÷B
B
b1
b2
=?
Le modè
modèle relationnel
Opé
Opérateurs ensemblistes
Opérateurs ensemblistes
Les opérations ensemblistes peuvent être exécutées sur n’importe quel
couple de relations ayant le même nombre et domaines d’attributs.
Soient R1 et R2 deux relations ayant le même nombre d’attributs (qui ont les
mêmes domaines).
L’union de R1 et R2 est notée R1∪R2. L’intersection de R1 et R2 est notée
R1∩R2 . La différence entre R1 et R2 est notée R1-R2.
Exemple
Exécuter toutes les
opérations possibles
entre R1 et R2.
R1
R2
A
B
A
B
a1
b1
a1
b1
a1
b2
a2
b1
a2
b2
a2
b2
Le modè
modèle relationnel
Opé
Opérateurs ensemblistes
Opérateurs ensemblistes (suite)
Le produit cartésien par contre peut être exécuté sur n’importe quel couple
de relations. Il est noté R1×R2
Exemple
Calculer le produit cartésien entre R1 et R2:
R1
R2
A
B
C
D
a1
b1
c3
d1
a1
b2
c2
d1
Check Point
Soit les entités suivantes :
FEUR: CF, NOM, ADRESSE, TEL, VILLE
VOITURE: CV, MARQUE, ETAT, NBPLACES
CLIENT: CC, NOM, ADRESSE, TEL, VILLE
Une voiture est acquise d’un fournisseur. Un client loue une voiture à
une date précise, pour un nombre de jours précis.
A. Compléter le MCD, puis générer le schéma relationnel?
B. Ecrire an algèbre relationnelles les opérations qui retournent:
1. Toutes les informations des fournisseurs?
2. Les noms et les numéros de téléphone de tous les clients?
3. Les informations des locations effectuées par le client de code ‘C03’?
Check Point (suite)
B. Ecrire an algèbre relationnelle les opérations qui retournent:
4. Les noms des clients qui habitent ‘BIZERTE’?
5. Pour toute location, le nom du client et le code de la voiture?
6. Pour toute location, le nom du client et la marque de la voiture?
7. Soit la table H_VOITURE dans laquelle on a mis les voitures acquises avant
2005. Certaines d’entre elles ont été cédées en 2005, elles ne sont donc plus
dans la table VOITURE.
a. Liste des voitures (code & marque) acquises depuis la création de l’agence?
b. Liste des voitures acquises avant 2005 et qui circulent jusqu’à maintenant?
c. Liste des voitures acquises après 2005?
8. Les codes des clients ayant loué toutes les voitures de l’agence?
Normalisation d’
d’un sché
schéma
relationnel
Une mauvaise répartition des données dans les relations peut
engendrer :
Des problèmes de stockage de données.
De manipulation de données.
Normaliser le schéma relationnel.
Idée: « un seul fait dans un seul lieu ».
La normalisation repose sur deux concepts fondamentaux: la clé
candidate et la dépendance fonctionnelle.
Normalisation d’
d’un sché
schéma
relationnel
Dépendance fonctionnelle
Il existe une dépendance fonctionnelle entre les attributs X et
Y d’une même relation R, si la valeur de X détermine la valeur de
Y. Elle est notée XY
Si E1 et E2, deux uplets de R, ont la même valeur de X, alors
ils ont forcément la même valeur de Y.
Exemple: Soit la relation Personne(Nom, Ville, CP, Gouvernorat)
CPGouvernorat, mais on n’est pas certain que villegouvernorat, car deux
villes différentes de deux gouvernorats différents peuvent avoir le même nom.
Normalisation d’
d’un sché
schéma
relationnel
Pourquoi un « seul fait, un seul lieu » ?
Soit la relation EMP(empno, ename, deptno, dname):
Remarquez la dépendance deptnodname. Anomalies :
Insertion on ne peut ajouter un département vide d’employés.
Modification si on veut changer le nom d’un département, on doit mettre à
jour le dname de tous ses employés.
Suppression si on supprime le dernier employé d’un département, on perd le
code et le nom du département.
Redondance outre le fait que l’information dname est redondante, ce qui
provoque des saisies de la même information, et un gaspillage de stockage.
Solution… ?
Les dépendances fonctionnelles doivent être inter-relations, et non intra-relations
Normalisation d’
d’un sché
schéma
relationnel
Première forme normale
Une relation R est en 1ère FN si elle ne comporte pas d’attribut mutlivalué.
LIVRE(codelivre, titre, auteurs)
LIVRE(codelivre, titre) REDACTION(codelivre, codeauteur)
Deuxième forme normale
1. R doit être en 1FN
2. Un attribut X de R, qui ne fait pas partie de la clé candidate, ne doit pas
dépendre d’un sous-ensemble strict de la clé candidate.
REDACTION(codelivre, codeauteur, nomauteur) codeauteur
nomauteur !
REDACTION(codelivre, codeauteur) AUTEUR(codeauteur, nomauteur)
Normalisation d’
d’un sché
schéma
relationnel
Troisième forme normale
Tout attribut X de R n’appartenant pas à la clé candidate, ne doit dépendre que
de la clé candidate.
EMP(empno, ename, deptno, dname) deptno
dname !
EMP(empno, ename) DEPT(deptno, dname)
Forme normale de Boyce-Codd
Les seules sources de dépendance sont les clés candidates.
REDACTION(codelivre, nomauteur, codeauteur) codeauteur
nomauteur !
REDACTION(codelivre, codeauteur) AUTEUR(codeauteur, nomauteur)
Normalisation d’
d’un sché
schéma
relationnel
Décomposition
Soit une relation R(A,B,C), avec BC. La décomposition de R en R1[A,B] et
R2[B,C] est sans perte de données, c.-à-d., R est égale à la jointure naturelle de
R1 et de R2.
EMP(empno, ename, deptno, dname) deptno
dname !
EMP(empno, ename) DEPT(deptno, dname)
Caracté
Caractéristiques d’
d’un
SGBD
I.INTÉGRITÉ DES DONNÉES
1. Transaction
C’est un ensemble de modifications (LMD) qui forment un tout indivisible. La
validation ou l’annulation concerne toute la transaction, et non une partie.
Une transaction se termine soit par une validation (COMMIT), soit par une
annulation (ROLLBACK)
Une modification non validée n’apparait qu’à l’utilisateur qui est en train de
l’exécuter. Les autres utilisateurs voient la base dans l’état qui a précédé la
transaction.
Une transaction doit être atomique (indivisible), cohérente (p/r aux contraintes),
isolée (visible une fois validée) et durable.
Caracté
Caractéristiques d’
d’un
SGBD
Point de reprise
Dans une transaction on peut désigner des points de reprises. L’utilisateur peut donc
annuler les modifications qui ont eu lieu après le point de reprise, et éviter ainsi
d’annuler la totalité de la transaction au cas où il veut en annuler uniquement une
partie.
maj1
maj2
SAVEPOINT A
maj3
maj4
SAVEPOINT B
maj5
ROLLBACK TO A
Les mises à jour effectuées après
maj3 sont annulées.
Caracté
Caractéristiques d’
d’un
SGBD
I. INTÉGRITÉ DES DONNÉES
2. Accès concurrentiel
Nécessite de gestion des accès/modifications concurrentielles
Temps
User 1
t1
Lire s
t2
t3
t4
User 2
Lire s
s=s+100
s=s+200
valider
Temps
User 1
t1
v=100
t2
t3
valider
User 2
Lire v
annuler
Caracté
Caractéristiques d’
d’un
SGBD
II. SÉCURITÉ
1. Accès à la base de données
Identification via un login/password à la BD.
Chaque utilisateur a des droits (des privilèges).
2. Accès aux données
Une table appartient à son créateur. Ce dernier peut attribuer des droits
(consultation, insertion, modification, suppression) sur cette table aux
utilisateurs qu’il veut.
L’ensemble des objets créés par un utilisateur constituent son schéma.
Caracté
Caractéristiques d’
d’un
SGBD
III. RESISTANCE AUX PANNES
1. Sauvegarde
Présence de mécanismes de sauvegarde et de restauration des données.
Enregistrement des modifications exécutées sur la BD sur des fichiers journaux.
2. Reprise après incident
IV. PERFORMANCE
Accélération des traitements en utilisant par exemple les index et les clusters.
Caracté
Caractéristiques d’
d’un
SGBD
V. DICTIONNAIRE DE DONNÉES
Le dictionnaire de données est un ensemble de tables qui contiennent les
définitions de tous les objets de la base de données.
Exemple: USER_TABLES, USER_USERS, USER_INDEXES...