View
215
Download
0
Category
Preview:
Citation preview
Bases de Données – Sylvain Brandel – 1
Bases de Données
Université Louis-Pasteur de StrasbourgDépartement d'informatique
Youcef Bouchebaba
Pôle API, Boulevard Sébastien Brant67 400 ILLKIRCH
bureau a 253tél : 03 90 24 45 43
e-mail : bouchebaba@dpt-info.u-strasbg.fr
Bases de Données – Sylvain Brandel – 2
Chapitre 1
Bases de Données et Systèmes de Gestion de Bases de Données
Bases de Données – Sylvain Brandel – 3Chapitre 1. BD et SGBD
Introduction
• Déf. Une BD est un ensemble de données modélisant les objets d’ une partie du monde réel et servant de support à une application informatique
• Ensemble– structuré– autorisant l’ extraction sélective d’ informations
• Exemple de BD : annuaire• Deux types d'opérations
– manipulation– définition
• Tout ensemble de données ne justifie pas forcément une BD exemple : CDs audio
• Déf. Un SGBD est un logiciel permettant d’ interagir avec une BD• Exemples de SGBD : Access, Oracle, mySQL
Bases de Données – Sylvain Brandel – 4
Exemple : CDs audio Exemple : École
Applications non coordonnées Applications « touffues »
Résultats différents des souhaits
Analyse
Chapitre 1. BD et SGBD
• Avant de penser BD, poser des questions, étudier, analyser les besoins
• Pourquoi MERISE ? Problèmes des grosses applications
• Principes de base :– méthode globale et intégrée– séparation données / traitements– 3 niveaux de modélisation
Bases de Données – Sylvain Brandel – 5
Niveau conceptuel : MCD
• Entité : collection d'objets ayant des propriétés analogues et possédant un identifiant
• Exemple : CLASSE < id-classe, nb-élèves, année >
• Association : Lien logique entre plusieurs entités
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 6
Niveau conceptuel : MCT
• Événement : compte-rendu fait au SI que quelque chose s'est produit dans l'univers extérieur ou dans le SI
• Les événements peuvent être :– internes– externes
• Opération : actions accomplies par le SI en réaction à un (ou des) événement(s)
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 7
Inconvénients de MERISE :- lourdeur- difficulté d'apprentissage
Niveaux organisationnel et technique
• MLD : MCD repensé• MOT : intégration de règles de gestion• Exemples de règles d'organisation :
– à chaque livraison, le magasinier contrôle la marchandise livrée– l'inventaire est effectué tous les ans les 29 et 30 décembre
• MPhD : implantation physique• MOpT : contraintes techniques
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 8
Bases de Données
• BD : Collection de représentation de la réalité sous forme de données inter-reliées
• Les précurseurs des BD : les systèmes de fichiers– informations communes – redondance non gérée
• Redondance :– volume de données important– temps de saisie et de mise à jour – manque de cohérence
• Cohérence :– être juste par rapport à une certaine réalité– respect des contraintes d'intégrité
• Centralisation :– BD : entité commune, centrale, à laquelle accèdent tous les utilisateurs
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 9
SGBD
• SGBD : ensemble de logiciels permettant aux utilisateurs de manipuler efficacement des données dans une grande masse d’ informations partagées avec d’ autres utilisateurs
• 1ère Génération : fin des 60's– modèles hiérarchique et réseau
• 2ème génération : fin des 70's– modèle relationnel
• 3ème génération : fin des 80's– Extensions objet des systèmes relationnels
• 4ème génération : 90's et actuellement– Internet et Web
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 10
1. Toutes les informations sur les données sont représentées au niveau logique et non physique (pas besoin d’ informations sur la façon dont sont enregistrées physiquement les données).
2. Les données sont accessibles uniquement par la combinaison du nom de la table, de la clé primaireet du nom de la colonne (pas de chemin à donner).
3. Une valeur spéciale doit représenter l’ absence de valeur (NULL).4. La description de la base de données doit être accessible comme les données ordinaires
(un dictionnaire des données est enregistré dans la base).5. Un langage doit permettre de définir les données, définir des vues (visions particulières de la base,
enregistrées comme des relations), manipuler les données, définir des contraintes d’ intégrité,des autorisations de gérer des transactions.
6. On peut faire des mises à jour par les vues lorsque c’ est logiquement possible.7. Le langage doit comporter des ordres effectuant l’ insertion, la mise à jour et la suppression des
données (un seul ordre pour effectuer chacune de ces fonctions)8. Indépendance des programmes vis-à-vis de l’ implantation physique des données.9. Indépendance des programmes vis-à-vis de l’ implantation logique des données
(si les informations manipulées par les programmes n’ ont pas été modifiées ou supprimées).10. Les contraintes d’ intégrité doivent pouvoir être définies dans le langage relationnel et enregistrées
dans le dictionnaire des données.11. Indépendance vis-à-vis de la répartition des données sur divers sites.12. On ne peut jamais contourner les contraintes (d’ intégrité ou de sécurité) imposées par le langage
du SGBD en utilisant un langage de plus bas niveau (par exemple le C).
Les 12 règles de Codd
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 11
Objectif : assurer - la sécurité des données- la confidentialité des données- l’ intégrité des données- de bonnes performances
Objectifs des SGBD
• Un SGBD doit permettre d’ écrire des applications indépendantes de l’ implantation physique des données
• Plusieurs utilisateurs peuvent accéder simultanément à la BD
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 12
• Une perte d'informations peut s'avérer fatale– la sécurité est primordiale
• Sécurité de fonctionnement
• Après un incident (matériel ou logiciel) :– redémarrer le système– remettre la BD dans un état cohérent
• Exemple de transaction : transfert d’ une somme d’ argent entre deux comptes d’ un client d’ une banque.– Validation : COMMIT– Annulation : ROLLBACK
Archivage
Journalisation
Sécurité
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 13
Mécanisme des verrous : interdire l'accès à une donnéependant un temps minimal
Risque : deadlock
Sécurité
• Gestion de la concurrence d'accès
• Exemple : éditer un même fichier partagé
• Exemple : Mises à jour perdues.
2 personnes veulent modifier une quantité q en stockTransaction :
- lire (q)- modifier (q) (valeur stockée dans un buffer)- écrire (q) (dans la BD)
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 14
• Lecture inconsistante ou lecture impropre
• Lecture non répétitive, ou non reproductible, ou incohérente?
- une transaction T2 lit une valeur donnée par - une autre transaction T1- ensuite la transaction T1 annule son affection de V
la valeur lue par T2 est fausse
Ne peut pas arriver si les modifications effectuées par unetransaction ne sont visibles par les autres qu’ aprèsvalidation de la transaction
- une transaction lit deux fois une même valeur etne trouve pas deux fois le même résultat
Blocage des données suffisamment longtemps pour éviterque les autres transactions ne puisse pas les modifier
Sécurité
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 15
- une sélection de lignes récupère des lignes qui sontmodifiées par une autre transaction et nevérifient plus le critère de la sélection
Blocage explicite des lignes en question
- Les SGBD gèrent automatiquement les accèsconcurrents de plusieurs utilisateurs sur lesmêmes lignes des tables
Oracle assure une lecture consistante
Sécurité
• Lignes fantômes
• Traitement des accès concurrents par le SGBD
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 16
• Contrôle de l’ accès à la base
• Contrôle de l’ accès aux données
Droits sur les objets
Gestion des droits d'accès
Gestion des privilèges
Vues
Confidentialité
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 17
Les commandes qui ne respectent pas ces contraintessont automatiquement annulées
- définition de la clé primaire d’ une table- clés étrangères dont les valeurs doivent exister
dans une autre table- unicité des valeurs d’ une rubrique- fourchette pour les valeurs acceptables- …
Intégrité
• Respect implicite des contraintes d'intégrité :
• Exemple : BD client / fournisseur / produit
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 18
• Problème des modèles relationnels : pas de stratégie d'accès aux données
• Solutions
index
clusters
performances dégradées
Performances
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 19
LMD Menu
Schéma externe A
Schéma externe B
Schéma conceptuel
Langage hôte
Schéma interne
SGBD
BD SE
Utilisateur
Niveaulogique
Niveauphysique
Architecture des SGBD
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 20
Différentes approches :- liaison : schémas externes / schéma conceptuel - schéma externe = dérivation du schéma conceptuel- schéma conceptuel = synthèse des schémas externes
Exemple : BD buveur / vin / cru
- deux utilisateurs peuvent appeler une même variablesous deux noms différents
- deux utilisateurs peuvent appeler deux variables différentessous un même nom
Les différents schémas
• Schéma conceptuel
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 21
Il existe autant de visions externes que d'utilisateurs
Mais les visions des différents utilisateurs vont se recouper(informations communes)
Exemple : BD buveur / vin / cru
Description de l'implantation physique
Pris en charge par le système d'exploitation
Les différents schémas
• Schémas externes
• Schéma interne
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 22
- bon fonctionnement- création de la BD- déclaration d'un utilisateur- droits d'accès- modification des schémas externes- modification des chemins d'accès aux données- maintien des performances d’ accès aux données- sauvegardes et reprises après pannes - …
- développement d'applications- droits sur ses applications
- utilisation des applications- accès limité
Les différents utilisateurs
• Administrateurs :
• Programmeur :
• Utilisateur final :
Chapitre 1. BD et SGBD
-
Bases de Données – Sylvain Brandel – 23
• LDD
• LMD
définition de : - schéma conceptuel- schémas externes- données- liens- contraintes d'intégrité
interrogation et mises à jour de la base de données instructions interactives :
- outils en ligne- interfaces avec des outils de bureautique ou avec le web
instructions insérées dans un langage de programmation :- langage hôte de 3ème génération- langage hôte de 4ème génération
menus formulaires
Les différents langages
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 24
Exemple de fonctionnement d'un SGBD
• Le programme d'application, accédant à la BD via le schéma externe B, demande la lecture d'une donnée de la base
Chapitre 1. BD et SGBD
LMD Menu
Schéma externe A
Schéma externe B
Schéma conceptuel
Langage hôte
Schéma interne
SGBD
BD SE
Utilisateur
Niveaulogique
Niveauphysique
Bases de Données – Sylvain Brandel – 25
Réparer les dommages occasionnés par des pannes
- logicielles- matérielles- réseau
Attitude différente suivant que les fichiers ont étéendommagés ou non
Résistance aux pannes
• Sauvegardes
• Types de pannes
• Reprise après accident
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 26
Redo logbuffer
Instance
SGA
LGWRDBWR
Control
files
Data files
Redo log
filesDatabase
Password
file
Parameter
file
Archived
log files
CKPT ARCH
Databasebuffercache
Exemple d'implantation d'un SGBD : Oracle
• Architecture globale
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 27
• Fichiers de la base
• Images "avant" et "après" Images "avant" : fichiers systèmes, ou emplacement spécial
de la base, contenant les informations nécessaires pourremettre la base dans un état antérieur à une modification
Image "après" : fichiers système contenant les informationsnécessaires pour refaire une modification à partir d'unétat antérieur de la base
Control
files
Data filesRedo log
filesDatabasePassword
file
Parameter
file
ALERT
file
Exemple d'implantation d'un SGBD : Oracle
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 28
Exemple d'implantation d'un SGBD : Oracle
• Images "avant" et "après"
DML Statement
New image
Table
Old image
Rollbacksegment
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 29
La place disque réservée aux images "avant" et "après" estlimitée : elle est recyclée pour enregistrer les dernièresimages
Il peut être intéressant d'archiver sur des médias stablesles images "après" pour les réutiliser en cas de panne
On peut ainsi dérouler toutes les dernières actions effectuéespar les utilisateurs depuis la dernière sauvegarde
Les différents SGBD peuvent avoir des implantationstrès différentes sur certains points, en particuliersur la façon de traiter les accès concurrents
Exemple d'implantation d'un SGBD : Oracle
• Archivage des images "après"
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 30
Exemple d'implantation d'un SGBD : Oracle
• Processus clients – serveurs
Oracle Server
Server process
User process
User
Client Server
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 31
Exemple d'implantation d'un SGBD : Oracle
• Écriture des données dans la base
DBWR
InstanceSGA
Databasebuffercache
Shared pool
ControlfilesData files
Redo logfilesDatabase
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 32
New image
Table
Image at statement commencement
SELECT * FROM table;
Exemple d'implantation d'un SGBD : Oracle
• Segments de rollback
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 33
member
member
member
member
member
member
Group 1 Group 2 Group 3
Disk 1
Disk 2
Exemple d'implantation d'un SGBD : Oracle
• Fichiers redo log
Chapitre 1. BD et SGBD
Bases de Données – Sylvain Brandel – 34
Chapitre 2
Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 35
Relations
♦ Produit cartésien d'un ensemble de domaines D1, D2, … Dn :ensemble des vecteurs (d1, d2, … dn) où di est une valeurde Di (1 ≤ i ≤ n)
définition en "extension" : en donnant la liste des valeurs(ex : couleur-vins = {blanc, rouge, rosé} crus = {chablis, sylvaner} )
♦ Domaine : ensemble de valeurs caractérisées par un nom
définition en "intension" : en donnant une caractéristique(ex : caractères = chaînes de caractères de longueur
fixe ou variable )
ex : couleurs-vins × crus =
blanc chablisblanc sylvanerrouge chablisrouge sylvanerrosé chablisrosé sylvaner
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 36
Relations
♦ Colonne : sous-ensemble des valeurs d'un domaine
♦ Relation : sous-ensemble du produit cartésien d'une liste dede domaines, caractérisé par un nom
ex : un sous-ensemble decouleurs-vins × crus =
blanc chablisblanc sylvanerrouge chablisrosé chablis
ex : domaine = ensemble des entiers,une colonne =
12512
couleurs-crus
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 37
Relations
♦ Tuples : ligne d'une relation correspondant à un enregistrement(appelé aussi n-uplet ou ligne)
♦ Attribut : colonne d'une relation caractérisée par un nom (pas forcément le même que le nom de domaine)
ex : un tuple de couleurs-crus = (blanc, sylvaner)
signification sémantique d'une colonne
ex : attributs de couleurs-crus = couleur et cru
♦ Schéma de relation : nom de la relation suivi de la liste de sesattributs et de la définition de leurs domaines
ex : couleurs-crus (couleur : charvar, cru : charvar) vins (nv : integer, cru : charvar, degré : float)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 38
Tables
♦ Propriétés d'une relation : nom unique dans l'ensemble des relations de la base nombre fixe de colonnes nombre variable de lignes
F# Nom Ville
F1F2F3F4F5
SmithMeyerMullerRyanMartin
LondresStrasbourgStrasbourgLondresParis
P#
P1P5P18
Nom
visécrou 6 pansboulon
F#
F1F1F3F1F5
P#
P1P18P5P18P18
ex : fournisseur pièce fournit
♦ Une BD relationnelle est un ensemble de relations : relations de l'utilisateur (relations de tables) relations de catalogues (relations systèmes)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 39
Colonnes
♦ unicité du nom de l'attribut dans une relation(mais pas forcément dans la base)
colonne = champ = attribut
ex : fournisseur : F# fournit : F#
♦ L'attribut prend sa valeur dans un domaineex : ville ∈ { Strasbourg, Paris, Londres, New-York}
♦ L'ordre des attributs n'a pas d'importanceex : fournisseur (F#, Nom, Ville) = fournisseur (Nom, F#, Ville)
♦ Le nombre d'attributs définit le degré de la relation
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 40
Lignes
♦ Les lignes sont supposées toutes différentes
ligne = n-uplet = tuple
F# Nom Ville
F1F2F1F4F5
SmithMeyerSmithRyanMartin
LondresStrasbourgLondresLondresParis
ex : fournisseur INTERDIT
♦ L'ordre des lignes n'a pas d'importanceP#
P1P5P18
Nom
visécrou 6 pansboulon
ex : pièce = pièce
P#
P1P18P5
Nom
visboulonécrou 6 pans
♦ Le nombre de n-uplets définit la cardinalité
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 41
Catalogue
♦ Tables créées et gérées par le système :
catalogue des relations
catalogue des constituants
catalogue des chemins d'accès
catalogue des utilisateurs
catalogue des droits d'accès
etc.
♦ Les utilisateurs y accèdent uniquement en lecture
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 42
Langages d'interrogation
♦ Trois théories mathématiques :
algèbre relationnelle langage SQL (Oracle, mySQL, …)
calcul relationnel des tuples langage QUEL (Ingres)
calcul relationnel des domaines langage QBE (IBM)
♦ L'algèbre relationnelle est de loin la théorie la plus utilisée
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 43
Algèbre relationnelle
♦ Opérations de base :
opérations ensemblistes :- union- différence- produit cartésien
opérations spécifiques :- projection- restriction- jointure
♦ Opérations dérivées :- intersection- division- complément- éclatement- jointure externe- semi-jointure
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 44
Union
♦ Opération binaire appliquée à deux relations de même schémaex : fournisseur1 (F#, Nom, Ville), fournisseur2 (F#, Nom, Ville)
F# Nom Ville
F1F2F3
SmithMeyerMuller
LondresStrasbourgStrasbourg
F# Nom Ville
F2F4
MeyerRyan
StrasbourgLondres
∪
Chapitre 2. Le Modèle Relationnel
F# Nom Ville
F1F2F3F4
SmithMeyerMullerRyan
LondresStrasbourgStrasbourgLondres
Bases de Données – Sylvain Brandel – 45
Différence
♦ Opération binaire appliquée à deux relations de même schémaex : fournisseur1 (F#, Nom, Ville), fournisseur2 (F#, Nom, Ville)
F# Nom Ville
F1F2F3
SmithMeyerMuller
LondresStrasbourgStrasbourg
F# Nom Ville
F2F4
MeyerRyan
StrasbourgLondres
−
♦ Opérateur non commutatif
Chapitre 2. Le Modèle Relationnel
F# Nom Ville
F1F3
SmithMuller
LondresStrasbourg
Bases de Données – Sylvain Brandel – 46
Produit cartésien
ex : R1(A, B), R2(C, D)
R1 × R2 = R(A, B, C, D)
ex : R (A, B), R (A, B)
problème de nom
en SQL on peut renommer les relations le tempsde la requête
♦ Opération binaire appliquée à deux relations deschémas quelconques
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 47
Produit cartésien
ex : fournisseur1 (F#, Nom, Ville), pièce (P#, Nom)
F# Nom Ville
F1F2F3
SmithMeyerMuller
LondresStrasbourgStrasbourg
×P#
P1P5P18
Nom
visécrou 6 pansboulon
Chapitre 2. Le Modèle Relationnel
F# fournisseur.Nom Ville
F1F1F1F2F2F2F3F3F3
SmithSmithSmithMeyerMeyerMeyerMullerMullerMuller
LondresLondresLondresStrasbourgStrasbourgStrasbourgStrasbourgStrasbourgStrasbourg
P#
P5P1P18P5P1P18P5P1P18
pièce.Nom
visécrou 6 pansboulonvisécrou 6 pansboulonvisécrou 6 pansboulon
Bases de Données – Sylvain Brandel – 48
Projection
♦ Opération unaire : sélection d'une ou plusieurs colonnesd'une relation
F# Nom Ville
F1F2F3F4F5
SmithMeyerMullerRyanMartin
LondresStrasbourgStrasbourgLondresParis
ex : fournisseur
projVille(fournisseur) projNom, Ville(fournisseur)
Chapitre 2. Le Modèle Relationnel
Ville
LondresStrasbourgParis
Nom Ville
SmithMeyerMullerRyanMartin
LondresStrasbourgStrasbourgLondresParis
Bases de Données – Sylvain Brandel – 49
Restriction (ou sélection)
♦ Opération unaire : sélection d'une ou plusieurs lignesd'une relation
♦ R | E
E : <attribut> θ <valeur>
θ ∈ {=, ≠, <, ≤, >, ≥}
Formule atomique :<attribut> θ <attribut><attribut> θ <constante>
Les formules atomiques peuvent être reliées pardes connectivités - ET
- OU- NON
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 50
Restriction (ou sélection)
F# Nom Ville
F1F2F3F4F5
SmithMeyerMullerRyanMartin
LondresStrasbourgStrasbourgLondresParis
ex : fournisseur
fournisseur | ville = "Londres"
ex : stock(P#, Quantité-stock, Quantité-seuil) stock | quantité-stock < quantité-seuil
Chapitre 2. Le Modèle Relationnel
F# Nom Ville
F1F4
SmithRyan
LondresLondres
Bases de Données – Sylvain Brandel – 51
Jointure
♦ Opération binaire : composition de deux relations à l'aided'un critère de jointure
F# Nom Ville
F1F2F3F4F5
SmithMeyerMullerRyanMartin
LondresStrasbourgStrasbourgLondresParis
ex : fournisseur (F#, Nom, Ville), fournit(F#, P#)F#
F1F1F3F2F5
P#
P1P18P5P18P18
*
=
Chapitre 2. Le Modèle Relationnel
F# Nom Ville
F1F1F3F2F5
SmithSmithMeyerMullerMartin
LondresLondresStrasbourgStrasbourgParis
P#
P1P18P18P5P18
Bases de Données – Sylvain Brandel – 52
Jointure
♦ La jointure peut être obtenue par sélection et projection duproduit cartésien
ex : fournisseur * fournit =projF#, Nom, Ville, P# (fournisseur × fournit |
fournit.F# = fournisseur.F#)
♦ Le θ-produit est défini en remplaçant l'égalité par n'importequel opérateur de l'ensemble θ
le θ-produit est noté inéqui-jointure (la jointure est alors notée équi-jointure)
♦ Lorsque plusieurs attributs sont communs aux deux relations,la comparaison porte sur tous les attributs communs
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 53
Intersection
♦ Opération binaire appliquée à deux relations de même schémaex : fournisseur1 (F#, Nom, Ville), fournisseur2 (F#, Nom, Ville)
F# Nom Ville
F1F2F3
SmithMeyerMuller
LondresStrasbourgStrasbourg
F# Nom Ville
F2F4
MeyerRyan
StrasbourgLondres
∩
=
♦ Opération redondante : elle peut être obtenue par combinaisond'opérateurs de base
R1 ∩ R2 = R1 − (R1 − R2), ou bien R1 ∩ R2 = R2 − (R2 − R1).
Chapitre 2. Le Modèle Relationnel
F# Nom Ville
F2 Meyer Strasbourg
Bases de Données – Sylvain Brandel – 54
Division
♦ Opération binaire appliquée à deux relations deschémas quelconques
D1(A1,… Ap, Ap+1, … An) ÷ D2(Ap+1, … An)= Q(A1, … Ap) dont les tuples sont ceux qui concaténés à
tout tuple de D2 donnent un tuple de D1
ex :Nom Ville
MullerMullerMeyerMeyerMartin
StrasbourgStrasbourgStrasbourgStrasbourgParis
P#
P1P5P18P5P18
Ville
StrasbourgStrasbourg
P#
P18P5
÷
=
Chapitre 2. Le Modèle Relationnel
Nom
Meyer
Bases de Données – Sylvain Brandel – 55
Complément
♦ Opération unaireex : fournit (F#, P#)
F#
F1F1F3F2F5
P#
P1P18P5P18P18
¬ fournit
Chapitre 2. Le Modèle Relationnel
F# P#
F1F2F2F3F3F4F4F4F5F5
P5P1P5P1P18P1P5P18P1P5
Bases de Données – Sylvain Brandel – 56
Le langage SQL : interrogation
♦ Structured Query Language
♦ Requête :
SELECT ... (attributs sur lesquels on projette)FROM ... (relation, ou produit cartésien)[WHERE ...] (sélection, optionnel)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 57
Le langage SQL : interrogation
♦ Exemple fournisseur / pièce / fournit
fournisseur(F#, Nom, ville)pièce(P#, Nom)fournit(F#, P#)
Chapitre 2. Le Modèle Relationnel
projVille(fournisseur)
SELECT VilleFROM fournisseur ;
projNom, Ville(fournisseur)
SELECT Nom, VilleFROM fournisseur ;
Bases de Données – Sylvain Brandel – 58
Le langage SQL : interrogation
fournisseur(F#, Nom, ville)pièce(P#, Nom)fournit(F#, P#)
Chapitre 2. Le Modèle Relationnel
fournisseur | Ville = "Londres"
SELECT F#, Nom, Ville (= SELECT *)FROM fournisseurWHERE Ville = 'Londres' ;
projP#(fournisseur × fournit | fournisseur.F# = fournit.F#et ville = Londres) SELECT P#
FROM fournisseur, fournitWHERE fournisseur.F# = fournit.F#
AND ville = 'Londres' ;
Bases de Données – Sylvain Brandel – 59
Le langage SQL : interrogation
♦ Exemple joueur / rencontre / gain
joueur(nom, prénom, âge, pays)rencontre(nrencontre, gagnant, perdant, lieu-tournoi, date, score)gains(nom-joueur, lieu-tournoi, date, prime, sponsor)
1. Nom et âge des joueurs américains, autres que 'Connors',âgés de plus de 30 ans
2. Les pays des joueurs de 20 ans (sauf France)
3. Nom et âge des joueurs ayant participé à Roland Garros en 99
4. Nom et pays des joueurs ayant participé, en 95,aux tournois de Roland Garros et Wimbledon
5. Nom des joueurs ayant perdu toutes leurs rencontres
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 60
Le langage SQL : création de tableCREATE TABLE <nom-de-table> (<colonne> | <contrainte-de-relation>)+ <nom-de-table> : chaîne de caractères simple (ex. etudiant),
ou composé (un nom de schéma suivi d'un nom de table, ex. crous.etudiant) <colonne> = <nom-de-colonne> <type-de-données> [<défaut>] [<contrainte-de-colonne>]
<type-de-données> : - chaînes de caractères de longueur fixe : CHAR (size), où size est facultatif
(omis : taille = 1, max = 255) - chaînes de caractères de longueur variable :
- VAR CHAR 2 (size), où size est obligatoire (max = 2000)- LONG, sans préciser de taille (on peut y mettre des valeurs jusqu'à 2 Go), mais avec restrictions :
- un seul champ de type LONG par relation- contrainte d'intégrité : uniquement NULL ou NOT NULL pour ce champ (NULL = on ne peut rien y mettre)- index interdits- ne peut pas apparaître dans certaines parties d'une commande SQL (WHERE, GROUP BY, DISTINCT...)
- numérique (entier positif, négatif, virgule fixe ou flottante) : NUMBER(p,s), où :- p = précision : nombre total de chiffres (1 ≤ p ≤ 38), facultatif- s = scale : nombre de chiffres à droite de la virgule (s > 0), facultatif ; si s < 0, alors arrondi à gauche de la virgule- NUMBER(p) : s = 0 entiers- NUMBER : p = 38 et s indéfini nombres à virgule flottante- par compatibilité, on peut utiliser NUMERIC, DECIMAL, INTEGER, INT, SMALLINT, FLOAT, REAL, DOUBLE PRECISION
- dates : DATE, toutes les dates valides comprises entre le 1er janvier 4712 av. JC et le 31 décembre 4712 ap. JC. Format : centenaire + année + mois + jour + heure + min + sec Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 61
Le langage SQL : création de table
<défaut> : DEFAULT val, où val est la valeur que l'on donne à l'attribut lorsque l'utilisateurn'en fournit pas
Restriction :- cohérence de type- ne peut pas contenir de référence à une autre colonne- ne peut pas contenir de date non complètement spécifiée
<contrainte-de-colonne> : permet de spécifier différentes CI portant sur un seul attribut,y compris les contraintes référentielles :
- valeur null impossible : NOT NULL - unicité de l'attribut : UNIQUE ou PRIMARY KEY - contrainte référentielle : REFERENCES <table-référencée> [<colonne-référencée>]
(le second argument est optionnel s'il est identique à la colonne référençante) - contrainte générale : CHECK <condition>, la condition pouvant être exprimée avec :
- <Attribut> θ <Valeur>, θ ∈ {=, <>, <, <=, >,>=}- prédicat d'intervalle : BETWEEN AND- prédicat de comparaison de texte : LIKE- test de nullité : NULL- test d'appartenance : IN
<contrainte-de-relation> : peuvent porter sur plusieurs attributs : - contrainte d'unicité : UNIQUE (<attribut>)+ - contrainte référentielle : [FOREIGN KEY (<colonne-référençante>)+]
REFERENCES <table-référencée> [(<colonne-référencée>)+] - contrainte générale : CHECK <condition>
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 62
Le langage SQL : création de table
Exemples :
CREATE TABLE prix (age NUMBER (2) PRIMARY KEY, tarif CHAR (2) DEFAULT 'T1', CHECK (age > 25) ) ;
CREATE TABLE etudiant (net NUMBER (2), nom VAR CHAR 2 (30), age NUMBER (2)
[ REFERENCES prix [ (age) ] ], adresse VAR CHAR 2 (60), PRIMARY KEY (net), [ FOREIGN KEY (age)
REFERENCES prix [ (age) ] ] ) ;
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 63
Le langage SQL : insertion de données
INSERT INTO <nom-de-table> [ ( <nom-de-colonne> )+ ]{ VALUES ( <constantes> )+ | <commande-de-recherche> }
INSERT INTO etudiant (net, nom, age, adresse)VALUES (1, Meyer, 28, Strasbourg) ;
Exemples :
INSERT INTO prixVALUES SELECT DISTINCT age FROM etudiant
WHERE... ;
Remarque : si toutes les colonnes ne sont pas précisées, les manquantes sont remplies avec NULL
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 64
Le langage SQL : suppression de données
DELETE FROM <nom-de-table>[ WHERE { <condition-de-recherche> |
CURRENT OF <nom-de-curseur> } ]
DELETE FROM etudiantWHERE nom LIKE Meyer OR age > 36 ;
Exemples :
DELETE FROM prix ;
Remarque : le second exemple élimine toute la table
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 65
Le langage SQL : mises à jour
UPDATE <nom-de-table>SET { <nom-de-colonne> =
{ <expression-de-valeur> | NULL } }+
WHERE { <condition-de-recherche> |CURRENT OF <nom-de-curseur> }
UPDATE prixSET tarif = 'T12'WHERE age = 30 ;
Exemples :
UPDATE etudiantSET age = age + 1WHERE nom = Meyer ;
Remarque : seuls les n-uplets vérifiant la condition (optionnelle) sont mis à jour
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 66
Dépendances fonctionnelles
♦ Contraintes d'intégrités très fortes
Soit R une relation dont les attributs sontU = {u1, u2, ..., un} et X ⊂ U, Y ⊂ U
∀ u ∈ R, ∀ v ∈ R,projX (R | u) = projX (R | v) ⇒ projY (R | u) = projY (R | v)
Y dépend fonctionnellement de X, ou X détermine Y, si :
Chapitre 2. Le Modèle Relationnel
u1
aa
u2
bb
u3 … … un
X = u1 et Y = u2 par ex.
Si deux lignes ont la même valeur sur X,alors ils ont la même valeur sur Y
dans ce cas, X → Y
Bases de Données – Sylvain Brandel – 67
Dépendances fonctionnelles : exemples
♦ n-ch
123456
type
ABAABC
nb-lits
212213
douche
ouiouinonnonouinon
♦ < Nom, Prénom, Ville, CP, Département >
DF ?
DF ?
♦ < Nom, Prénom, Moyenne, Âge, Enseignant > DF ?
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 68
Règles d'inférence
Chapitre 2. Le Modèle Relationnel
♦ Réflexivité
♦ Augmentation
X = {Nom, Catégorie}Y = Nom ou Y = {Catégorie} ou Y = {Nom, Catégorie}
Nom, Catégorie → NomNom, Catégorie → Catégorie
CP → Département CP, Ville → Département, Ville CP, Ville → Département
♦ Transitivité
CP → DépartementDépartement → Pays
CP → Pays
Bases de Données – Sylvain Brandel – 69
Règles d'inférence
Chapitre 2. Le Modèle Relationnel
♦ Union
♦ Pseudo-transitivité
Nom → CatégorieNom → NbEtoiles
Nom → Catégorie, NbEtoiles
♦ Décomposition
CP → DépartementDépartement, Langue → Pays
CP, Langue → Pays
Nom → Catégorie, NbEtoiles Nom → Catégorie
Bases de Données – Sylvain Brandel – 70
Ajout / Suppression d'attributs
Supposons G1, G2 → D1, D2 VRAIE
Chapitre 2. Le Modèle Relationnel
♦ Ajout d'attributs à gauche : X, G1, G2 → D1, D2
♦ Ajout d'attributs à droite : NON (à moins qu'une autre DF le permette)
♦ Suppressions d'attributs à gauche : NON (à moins qu'une autre DF le permette)
♦ Suppression d'attributs à droite : G1, G2 → D1
Bases de Données – Sylvain Brandel – 71
Choix des DF à retenir
♦ Ne pas trop simplifier par transitivitéX → YY → Z
il vaut mieux garder les deux premières
X → Z
♦ Autre simplificationG1 → X
il vaut mieux garder la première
G1, G2 → X
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 72
Fermeture
♦ Ensemble des DF obtenues à partir de toutes les décompositionspossibles des DF de F
F1 + DF (obtenue par application d'une règle) F2
F2 + DF (obtenue par application d'une règle) F3
…
Fn + DF (obtenue par application d'une règle) Fn+1
Lorsqu'il n'est plus possible de trouver de nouvelle DF F+ = Fn
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 73
Couverture
♦ Ensemble minimal de DF suffisant à retrouver F
Méthode : on enlève une DF et on essaye de la retrouver à partir des DF restantes
Exemple : Q → ST → RR, S → PT, Q → PP, R → S
F = {
}
Chapitre 2. Le Modèle Relationnel
Est-il possible de reconstituer F à partir de F' = { T → RR, S → PT, Q → PP, R → S
}
?
Même démarche avec toutes les DF de F
Bases de Données – Sylvain Brandel – 74
DF élémentaires, clés
♦ Y pleinement dépendant de X s'il n'existe pas de sous-ensemblestrict X' de X tel que X' → X
♦ Une clé est un ensemble minimal d'attributs qui déterminetous les autres
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 75
Passage DF – schéma relationnel
nom de l'attribut type descriptifnom chaîne nom de l'hôtel (unique dans une région donnée)catégorie entier nombre d'étoiles de l'hôtelnb-chambres entier nombre total de chambres pour un hôtel donnénum-chambre entier numéro de chambretype entier type de chambreprix réel prix d'une chambrenbre-lits entier nombre de litsdouche booléen vrai si la chambre comporte une douche privéebaignoire booléen vrai si la chambre comporte une baignoire privéeWC booléen vrai si la chambre comporte des WC privéslib-div chaîne libellé des divertissements proposés par l'hôtelgratuit booléen indique si un divertissement proposé par un hôtel est gratuit ou payantsaison {'H','B'} H : haute saison, B : basse saison
Le type de chambre détermine les commodités offertes : nombre de lits, présence de douche, debaignoire, de WC indépendants. Le prix d'une chambre dépend du type de la chambre. Ainsi, dans un hôtel donné, toutes les chambres de même type auront le même prix. Cette hypothèse n'est pas vraiepour deux hôtels différents. Le prix d'une chambre dépend également de la saison : haute ou basse
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 76
Passage DF – schéma relationnel
1. Les dépendances fonctionnelles suivantes sont-elles correctes ?
1. nom → catégorie, nb-chambres2. num-chambre → type3. type → prix4. type → nbre-lits5. type, prix → douche, baignoire, WC6. nom, catégorie → nb-chambres7. nom, num-chambre →type, douche, baignoire, WC8. nom, gratuit → lib-div9. catégorie, lib-div → gratuit10. nom, saison, type → nbre-lits
2. Déterminer la fermeture
3. Déterminer la couverture
4. Transformation en modèle relationnel
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 77
Normalisation
♦ Exemple : base étudiantsnet : numéro d'étudiantnomâgeadressetarif : tarif CROUS ∈ {T1, T2, T3} selon l'âge de l'étudiantmatièreenseignant : enseignant responsable de la matièremoyenne : moyenne dans une matière donnée
ETUDIANT < net, nom, âge, adresse, tarif, matière, enseignant, moyenne>
net → nom, âge, adressematière → enseignantnet, matière → moyenneâge → tarif
DF : clé ? vérifier qu'aucun attribut ne manque
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 78
Pourquoi normaliser
Redondance et anomalies :
Insertion, suppression Redondance Mise à jour Effet de bord
net nom âge adresse tarif matière enseignant moyenne
121240
MeyerMeyerMuller
252518
StrasbourgStrasbourgBrumath
T1
T1
T2
UV1UV2UV3
BrandelTellierAgnus
NULL1014
Pour y remédier formes normales
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 79
1ère Forme normale (1FN)
♦ Exemple :
net adresse
numéro rue ville CP
nom
INTERDIT
Soit mise à plat :
net numéro rue ville CP nom
Soit nouvelle relation :net adresse rue ville CPnom
12
id-adr
12
et
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 80
1ère Forme normale (1FN)
Si on a un ensemble (tous les éléments distincts), on duplique :
Si on a une liste (différent d'un ensemble car l'ordre a une importanceet on peut avoir plusieurs fois la même valeur), il faut rajouter unattribut :
net matière note
1 UV1 {12, 11, 16}
net matière note
111
UV1UV1UV1
121116
net note
1 UV1 {12, 11, 12}
net matière note
111
UV1UV1UV1
121112
ordre
123
matière
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 81
1ère Forme normale (1FN)
♦ Exemple : base étudiants
ETUDIANT < net, nom, âge, adresse, tarif, matière, enseignant, moyenne>
En 1FN ?
♦ Remarque : l'atomicité d'un domaine dépend du niveau dedécomposition
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 82
2ème Forme normale (2FN)
♦ Une relation est en 2FN si :- elle est en 1FN- aucun attribut n'est dépendant d'une partie seulement
de la clé
♦ Exemple typique de relation NON en 2FN :
X Y Z WR :
parce que Y → W on décompose en : R1 <Y, W>R2 <X, Y, Z>
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 83
2ème Forme normale (2FN)
♦ Exemple : base étudiants
ETUDIANT < net, nom, âge, adresse, tarif, matière, enseignant, moyenne>
En 1FN ? OUI
La clé est le couple net, matière
net, matière → nom pleine ? net, matière → âge pleine ? net, matière → adresse pleine ? net, matière → tarif pleine ? net, matière → enseignant pleine ? net, matière → moyenne pleine ?
En 2FN ? NON
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 84
2ème Forme normale (2FN)
on découpe d'après les DF :net → nom, âge, adresse, tarifmatière → enseignantnet, matière → moyenne
ETUDIANT<net, nom, âge, adresse, tarif>ENSEIGNEMENT<matière, enseignant>NOTE<net, matière, moyenne>
En 2FN ? OUI
net matière nom âge adresse tarif enseignant moyenne
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 85
2ème Forme normale (2FN)
♦ Remarque : si on supprime une moyenne, on ne perd plusd'étudiant
♦ Par contre, il faut fixer une Contrainte d'Intégrité référentielle(CI réf) exprimant que si on supprime un étudiant,il faut que la moyenne disparaisse aussi :
CI réf : projnet (NOTE) ⊂ projnet (ETUDIANT) projmatière (NOTE) ⊂ projmatière (ENSEIGNEMENT)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 86
3ème Forme normale (3FN)
♦ Une relation est en 3FN si :- elle est en 2FN- tous les attributs n'appartenant pas à une clé sont directement
dépendants d'une clé
♦ Exemple typique de relation NON en 3FN :
X Y Z WR :
parce que Y → W on décompose en : R1 <Y, W>R2 <X, Y, Z>
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 87
3ème Forme normale (3FN)
♦ Exemple : base étudiants
ETUDIANT<net, nom, âge, adresse, tarif>ENSEIGNEMENT<matière, enseignant>NOTE<net, matière, moyenne>
En 2FN ? OUI
ETUDIANT<net, nom, âge, adresse, tarif> en 3FN ? NON parce que net → âge → tarif
on découpe en
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>
ENSEIGNEMENT<matière, enseignant> en 3FN ? OUI
NOTE<net, matière, moyenne> en 3FN ? OUI
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 88
3ème Forme normale (3FN)
Le schéma devient alors :
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>
En 3FN ? OUI
♦ CI réf à ajouter pour éviter des redondances en cas desuppression dans la relation ETUDIANT :
CI réf : projâge (PRIX) ⊂ projâge (ETUDIANT)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 89
Forme normale de Boyce-Codd (FNBC)
♦ Une relation est en FNBC s'il n'y a pas d'autres DF que K → A, où- K est la clé- A est un attribut non clé
♦ Exemple typique de relation NON en FNBC :
X Y Z WR :
parce que W → X on décompose en : R1 <W, X>R2 <X, Y, Z>
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 90
Forme normale de Boyce-Codd (FNBC)
♦ Exemple : base étudiants
En FNBC ? OUI
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>
LOCALISATION <cru, pays, région, qualité>
♦ Exemple : base vins
cru, pays → régioncru, pays → qualitérégion → pays
DF :
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 91
Forme normale de Boyce-Codd (FNBC)
En 3FN ? OUI parce que le cru ne détermine pas la région
En FNBC ?
cru pays région qualité
NON parce que région → pays
on décompose :
CRUS <cru, pays, qualité>REGION <région, pays>
cru, pays → qualitérégion → cru
DF :
La décomposition est sans perte malgré la perte d'une DF
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 92
4ème Forme normale (4FN)
♦ Exemplenet matière sport
11112
BDBDAlgoAlgoAlgo
escaladetennisescaladetennisVTT clé : net, cours, sport
Redondances, mais pas de DF permettant de lessupprimer
4FN : COURS <net, cours>SPORT <net, sport>
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 93
5ème Forme normale (5FN)
♦ Exemple
clé : buveur, cru, producteur
Redondances, mais pas de DF ni de DM permettant de lessupprimer
5FN :
buveur
MeyerMeyerMeyerBrandel
cru
EdelEdelGewurtzEdel
producteur
ClaudeAndréAndréAndré
buveur
MeyerMeyerBrandel
cru
EdelGewurtzEdel
producteur
ClaudeAndréAndré
buveur
MeyerMeyerBrandel
cru
EdelEdelGewurtz
producteur
ClaudeAndréAndré
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 94
Exemples de décomposition
R <A, B, C>
Quelle est la décomposition en 3FN dans les trois cas :
1. F = {A → B, B → C }2. F = {A → B, C}3. F = { }
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 95
Exemples de décomposition
Quelle est la décomposition en 3FN ?
GITES < nom, nb-chambres, num-chambre, type, prix, nbre-lits, douche, baignoire, WC, lib-div, gratuit, saison>
F0 = { nom → catégorie, nbre-chambres
nom, num-chambre → typetype → nb-lits, douche, baignoire, WCnom, lib-div → gratuitnom, type, saison → prix }
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 96
Insertion – suppression
♦ Emploi de n-uplets, sans précision sur son mode d'obtention(entrée au clavier + vérification)
♦ Couple pré-condition – post-condition :- pré-condition : doit être vérifiée pour avoir le droit
d'exécuter l'opération- post-condition : le résultat de l'opération doit la vérifier
♦ L'ordre d'insertion / suppression dans les relations est important
♦ Vérifier qu'il n'y a pas de situation d'interblocage :par exemple insérer avant un net peut permettred'insérer autre chose
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 97
Insertion – suppression
DF = { net → nom, âge, adressematière → enseignantnet, matière → moyenneâge → tarif }
CI réf : projnet (NOTE) ⊆ projnet (ETUDIANT)projmatière (NOTE) ⊆ projmatière (ENSEIGNEMENT)projâge (ETUDIANT) ⊆ projâge (PRIX)
♦ Exemple : base étudiant
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>
Attributs : net, nom, âge, adresse, tarif, matière, enseignant, moyenne
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 98
Insertion – suppression
♦ Insertion dans PRIXOn cherche à insérer un n-uplet n dans PRIX
- pré-condition : n.âge ∉ projâge (PRIX)- post-condition : PRIX = PRIX ∪ {n}
♦ Suppression dans PRIXOn cherche à supprimer un n-uplet n de PRIX
- pré-condition : n.âge ∉ projâge (ETUDIANT) - post-condition : PRIX = PRIX - {n}
Suppression en cascade :- pré-condition : VRAI- post-condition : - PRIX = PRIX - {n} - ETUDIANT = ETUDIANT –
(ETUDIANT | âge = n.âge) - NOTE = NOTE –
(NOTE | net ∈ projnet (ETUDIANT | âge = n.âge))
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 99
Insertion – suppression
♦ Insertion d'un n-uplet n dans ETUDIANT :- pré-condition : - n.net ∉ projnet (ETUDIANT) - n.âge ∈ projâge (PRIX)- post-condition : ETUDIANT = ETUDIANT ∪ {n}
♦ Suppression dans ETUDIANT :- pré-condition : n.net ∉ projnet (NOTE)- post-condition : ETUDIANT = ETUDIANT – {n}
Suppression en cascade :- pré-condition : VRAI- post-condition : - ETUDIANT = ETUDIANT – {n} - NOTE = NOTE - (NOTE | net = n.net)
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 100
Insertion – suppression
♦ Insertion d'un n-uplet n dans NOTE :- pré-condition : - (n.net, n.matière) ∉ projnet, matière (NOTE)
(différent de (n.net ∉ projnet (NOTE) et n.matière) ∉ projmatière (NOTE))
- n.net ∈ projnet (ETUDIANT) - n.matière ∈ projmatière (ENSEIGNEMENT) - post-condition : NOTE = NOTE ∪ {n}♦ Suppression dans NOTE :
- pré-condition : VRAI (ne vient pas de suppression en cascade mais de l'absence de conditions)
- post-condition : NOTE = NOTE - {n}
Chapitre 2. Le Modèle Relationnel
Bases de Données – Sylvain Brandel – 101
Chapitre 3
Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 102
Entité
♦ Entité = individu dans MERISE
♦ classe (ou type) d'entité : une classe regroupetoutes les entités de même nature
Exemple : M. Meyer, département de vente d'une entreprise... M. Meyer est UNE entité
♦ Entité = objet concret ou abstrait qui peut être distinctementidentifié dans l'esprit du concepteuret qui présente un intérêt particulier
Exemple : M. Meyer classe Personne(M. Meyer est une occurrence de la classe Personne)
Exemple : M. Meyer possède une 205
Chapitre 3. Le Modèle Entités / Associations
Personne Personne Voiture
Bases de Données – Sylvain Brandel – 103
Association
♦ Association = relation dans MERISE
♦ Classe (ou type) d'association =ensemble des associations ayant même sémantique,définies sur un même type d'entités
♦ Association = relation entre une ou plusieurs entités
♦ Un type d'association ne peut pas être directement reliéavec un autre type d'association :
INTERDIT (sauf extension agrégation)
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 104
Association
♦ Un type d'entité de peut être relié à un autre type d'entitéque par un lien d'association :
INTERDIT
♦ Il n'y a pas de restriction sur le nombre de typesd'associations entre types d'entités :
Chapitre 3. Le Modèle Entités / Associations
Personne VoiturePossède
Conduit
Habite Ville
Bases de Données – Sylvain Brandel – 105
Cardinalité et fonctionnalité
♦ Cardinalité min et max reportées sur la graphique :
Personne Possède Voiturea,b c,d
a = nombre min d'occurrences de B liées à l'occurrence de A par A - B (∈ {0,1})b = nombre max d'occurrences de B liées à l'occurrence de A par A - B (∈ {1,n})c = nombre min d'occurrences de A liées à l'occurrence de B par A - B (∈ {0,1})d = nombre max d'occurrences de A liées à l'occurrence de B par A - B (∈ {1,n})
Remarque : selon les auteurs, les couples (a,b) et (c,d)peuvent être représentés sur une liaison ou sur l'autre
Chapitre 3. Le Modèle Entités / Associations
Personne VoiturePossède
Conduit
Habite
0,n 1,1
0,n 0,n
0,n
0,n
1 × n
m × n
Ville
Bases de Données – Sylvain Brandel – 106
Cardinalité et fonctionnalité
♦ cardinalité : couple (a,b) ou (c,d). 4 alternatives pour lesdeux couples (a,b) et (c,d) : 0,1 - 0,n - 1,1 - 1,nPas d'autre choix possible.
♦ fonctionnalité : couple (b,d). 3 alternatives : 1 × 1 - 1 × n - n × m
♦ Dimension (ou ordre ou degré) du type d'association =nombre de types d'entités distinctes qui interviennentdans le type d'association.
relation unaire (dimension = 1) :
femme
mari
Personn
e
marié à Personn
e
Personn
e
marié à
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 107
Cardinalité et fonctionnalité
relation binaire (dimension = 2) :
relation ternaire (dimension = 3) :
…relation n-aire (dimension = n) :
Chapitre 3. Le Modèle Entités / Associations
Personne Personne Voiture
Personne Personne Voiture
Chateau
Salle
Emploi du temps
Enseignant
Horaire Groupe d'étudiants
Bases de Données – Sylvain Brandel – 108
Cardinalité et fonctionnalité
♦ Problèmes quand il y a plus de deux types d'entités :
♦ Les cardinalités peuvent varier selon les questions que l'on se pose :
Chapitre 3. Le Modèle Entités / Associations
Produit Ligne produit
Client
Commande
0,n
1,1
mauvaise modélisation
Passée parProduit Ligne produit Commande1,n0,n 1,1 Client0,n
Bases de Données – Sylvain Brandel – 109
Attribut
♦ Attribut = propriété dans MERISE
♦ Attribut = propriété d'un type d'entité ou d'association
Personne
idnomâgeadresse
Autre notation :- id- nom- âge- adresse
Personne
♦ Attribut :- valeurs simples, pas de structure, pas d'ensemble- valeurs facultatives ou obligatoires
♦ Attribut :- soit des identifiants : clés, soulignés
dans la représentation graphique - soit des descripteurs : tous les autres
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 110
Attribut
♦ Attributs du type d'association :clés de chacun des types d'entités de l'association
+ des attributs propres à l'association Produit
Id-fournNom
Ligne catalogue
Prix
0,n 0,nFournisseur
Id-produitDésignation
♦ Clé de l'association : a priori, elle est constituée de toutes les clés de l'ensemble des clés des entités qui interviennent dans l'association
Exemple : la clé de ligne catalogue est (Id-fourn, Id-produit)
En pratique, ce n'est pas toujours le cas :
Produit0,1 Le prix ne dépend que
du numéro de produitChapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 111
Attribut
MatièreId-élèveNom…
InscritNote
0,n 0,nElève
Id-matièreLibellé…
inscrit < Id-élève, Id-matière, note > n'autorise pas d'avoirplusieurs notes pour un étudiant donné dans une matière donnée
analyser la cause de la répétitivitéet introduire un type d'entités pour la traduire
inscrit < Id-élève, Id-matière, Date, note >, on peut à présentavoir plusieurs notes par élève (une par date).
Chapitre 3. Le Modèle Entités / Associations
MatièreId-élèveNom…
InscritNote
0,n 0,nElève
Id-matièreLibellé…
DateDate
1,n
Bases de Données – Sylvain Brandel – 112
Attribut
Il existe des exemples où cela ne fonctionne pas :
Prison
Id-banditNom…
Séjour prison
Date débutDate fin
0,n 0,nBandit
Id-prison…
Date
Date
1,ndébut
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 113
Normalisation
♦ L'attribut est affecté au type d'entités ou d'associationsqu'il décrit le plus directement :
Voiture
Date de naissance
Possède
Date d'achat
Personne
Date de 1ère imat
Les trois dates sont très différentes
♦ Choix entité / attribut :
BureauId-empNom…
OccupeSurface
0,1 0,nEmployé
Id-bureauSurface
Id-empNomNo_bureauSurface
Employé
OU
Si plusieurs employés par bureau Si un seul employé par bureau
Le choix dépend de l'importance du bureau Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 114
Normalisation
♦ Un type d'entité qui ne comporte qu'un identifiant peut être traitécomme un attribut, à condition qu'il n'intervienne pasdans un nouveau type d'association et qu'il ne génère pasd'attribut à valeur multiple :
Il faut toujours vérifier si les associations de degré supérieurà deux ne peuvent pas être rapportées à plusieursassociations de degré inférieur
Un type d'association peut être éliminé s'il est la compositionde deux types d'associations qui, par des cardinalités(0,1) ou (1,1), expriment la même réalité
Lieu
AffectationStation ski Situation Hôtel1,11,n 1,n
Chambre1,1
1,n 1,1
INTERDIT
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 115
Modèle E/A ⇔ modèle relationnel
Voiture
Id-persNom
Possède
Date d'achat
Personne
ImmatriculationType
0,n 0,1
♦ Exemple voitures
Chapitre 3. Le Modèle Entités / Associations
Plusieurs possibilités, selon les cardinalités :
Personne < Id-pers., nom, date, Immatriculation, type >
Personne < Id-pers., nom >Voiture < Immatriculation, type >
Possède < Id-pers., Immatriculation, date >Personne < Id-pers., nom >Voiture < Immatriculation, type, Id-pers., date >
Contrainte d'intégrité référentielle pour le dernier cas : projId-pers. (Voiture) ⊂ projId-pers. (Personne) ∪ { NULL }
Bases de Données – Sylvain Brandel – 116
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>
Modèle E/A ⇔ modèle relationnel
♦ Exemple étudiants
Solution 1 :
Chapitre 3. Le Modèle Entités / Associations
Enseignement
netNomAdresse
Note0,n 0,n
Etudiant
MatièreEnseignant
1,1
Agé de
Age
Année
0,n Tarif
Détermine1,1 0,n
Id-tarif
Bases de Données – Sylvain Brandel – 117
ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>
Modèle E/A ⇔ modèle relationnel
Solution 2 :
Chapitre 3. Le Modèle Entités / Associations
Enseignement
netNomAdresse
Note0,n 0,n
Etudiant
MatièreEnseignant
1,1
Paie AgeTarif
Prix0,n
Bases de Données – Sylvain Brandel – 118
Modèle E/A ⇔ modèle relationnel
♦ Exemple employés SERVICE <nS, nomS>EMPLOYE <nE, nomE, nS>PROJET <nP, nomP, coût>PARTICIPE <nE, nP, nomR>
Chapitre 3. Le Modèle Entités / Associations
Service
nENomE
Travaille0,1 0,n
Employé
nSnomS
0,n
Participe nPnomPCoût
Projet0,n
nomR
Bases de Données – Sylvain Brandel – 119
Généralisation / spécialisation
♦ Généralisation : regrouper les différents types d'entitéen faisant abstraction de leurs différences
♦ Spécialisation : mise en évidence des particularités des sous-types
Personne
idnom
Homme
service militaire
Femme
nom jeune fille
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 120
Généralisation / spécialisation
♦ Il peut y avoir N niveaux d'abstraction :
Véhicule
Terrestre
Diesel
Aérien
Essence
Atmosphérique Compressé Carburation Injection
Diesel Essence
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 121
Généralisation / spécialisation
♦ Sur N critères :Personne
Homme Femme Majeur Mineur
♦ Association à tous les niveaux :Employé
Programmeur Chef de projet Secrétaire
Travaille
Dirige Dispose Voiture
Berline Monospace
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 122
Modèle étendu : MERISE 2
♦ Intégration d'informations supplémentaires dans le modèle E/A :- partition (+) : disjonction + couverture (l'un ou l'autre)- totalité (T) : ¬ disjonction + couverture
(l'un ou l'autre ou les deux, mais pas autre chose) - exclusion (×) : disjonction + ¬ couverture
(l'un ou l'autre ou autre chose, mais pas les deux)
♦ Fait appel aux notions de :- couverture : ensemble recouvrant tous les éléments- Disjonction : pas d'éléments commun à deux sous-ensembles
Partition : +
Totalité : T
Exclusion : ×
Représentation graphique :
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 123
Modèle étendu : MERISE 2
Personne
Homme
Femme
Voiture
Diesel Turbo
Partition : Totalité :
Diesel EssenceVoiture
(GPL)
Exclusion :
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 124
Modèle étendu : MERISE 2
+
PersonneId-persNom
ChanteurVoix
MusicienInstrument
♦ Exemple
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 125
Modèle étendu : MERISE 2
une relationpersonne <Id-pers, nom, voix, instrument >
partition : voix ≠ NULL XOR instrument ≠ NULLtotalité : voix ≠ NULL OR instrument ≠ NULLexclusion :voix = NULL OU instrument = NULL
autre possibilité :
personne < id-pers, nom, type, voix, instrument >
partition : type ∈ {C,M}type = C ET voix ≠ NULL ET instrument = NULLtype = M ET voix = NULL ET instrument ≠ NULL
totalité : type ∈ {C,M,D}exclusion : type ∈ {C,M,NULL}
type = C ET voix ≠ NULL ET instrument = NULLtype = M ET voix = NULL ET instrument ≠ NULL
Expression des contraintes d'intégrité référentielles :
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 126
Modèle étendu : MERISE 2
trois relations
partition : C3 (C1 et C2 non nécessaires)totalité : C4 (C1 et C2 non nécessaires)exclusion :C1 ET C2 ET C3
autre possibilité :
partition : type ∈ {C,M}projid-pers (chanteur) ⊂ projid-pers (personne | type = C)projid-pers (musicien) ⊂ projid-pers (personne | type = M)
totalité : type ∈ {C,M,D}exclusion : type ∈ {C,M,NULL}
projid-pers (chanteur) $ ⊂ projid-pers (personne | type ∈ {C,D})projid-pers (musicien) ⊂ projid-pers (personne | type ∈ {M,D})
personne < id-pers, nom >chanteur < id-pers, voix >musicien < id-pers, instrument >
C1 : projid-pers (chanteur) ⊂ projid-pers (personne)C2 : projid-pers (musicien) ⊂ projid-pers (personne)C3 : projid-pers (musicien) ∩ projid-pers (chanteur) = ∅C4 : projid-pers (musicien) ∪ projid-pers (chanteur) = projid-pers (personne)
personne < id-pers, nom, type >chanteur < id-pers, voix >musicien < id-pers, instrument >
Chapitre 3. Le Modèle Entités / Associations
Bases de Données – Sylvain Brandel – 127
Chapitre 4
Utilisation d'un SGBD :mySQLApachePHPPHPmyAdmin
Bases de Données – Sylvain Brandel – 128
Serveur HTTP robuste très couramment utilisé
Apache
SGBD robuste pauvre en fonctionnalités
mySQL Générateur de pages webdynamiques ("remplace" javascript) très couramment utilisé simple (syntaxe proche
du C)
PHP
Interface PHP pour utiliseret administrer mySQL très intuitif puissant
PHPmyAdmin
gratuit robuste multi plate-formes
Chapitre 4. Exemple de SGBD
Présentation
Bases de Données – Sylvain Brandel – 129
EasyPHP
• Le plus simple à installer
• Distribution Windows, testée pour :– Win9x / Me– NT / 2K (à voir pour XP)
• Télécharger le fichier : easyphp1-6_setup.exe (10,3 Mo)• Installer avec toutes les options par défaut• Par défaut, tout est installé dans
c:\Program Files\EasyPHP\
• Démarrer, configurer (services)...
24/04/02 :EasyPHP 1.6 (version 1.7 en vue, beta dispo):- apache 1.3.24- php 4.2.0- mysql 3.23.49- phpmyadmin 2.2.6
http://www.easyphp.org
Bases de Données – Sylvain Brandel – 130
Protection de mySQL
• Impératif de protéger l'accès à mySQL• Protection des répertoires fournie par Apache (alias, httpd.conf)• Créer un fichier .htaccess avec un éditeur :
• Créer un fichier .passwd avec l'utilitaire Apache htpasswd (dans une fenêtre invite de commande) :
• Placer les fichiers .htaccess et .passwd dans c:\Program Files\EasyPHP\phpmyadmin, éditer httpd.conf
AuthName "Acces BD"AuthType BasicAuthUserFile "c:\Program Files\EasyPHP\phpmyadmin\.passwd"AuthGroupFile Nonerequire valid-user
c:\>cd Program Files\EasyPHP\apache\binc:\Program Files\EasyPHP\apache\bin>htpasswd –c .passwd brandelAutomatically using MD5 format on Windows.New password: ********Re-type new password: ********Adding password for user brandel
Bases de Données – Sylvain Brandel – 131
Utilisation de Apache / PHP
• Le point d'entrée du site WEB est dans : c:\Program Files\EasyPHP\www
• Ce répertoire contient un fichier index.php exécuté lorsqu'on tape http://localhost dans la zone URL d'un navigateur (IE par ex.)
• Créer un fichiersaluer.php :
• Placer ce fichier dans c:\Program Files\EasyPHP\www
• Exécution par l'URL http://localhost/saluer.php
<html><head><title> un titre un vrai ! </title></head><body><?phpif ($prénom != "") { print("<h2>Bonjour, $prénom !</h2>"); exit;}?>
<FORM ACTION="http://localhost/saluer.php",METHOD="POST">
Votre prénom ?<INPUT TYPE="text" NAME="prénom" VALUE=""><INPUT TYPE="submit" VALUE="Cliquez ici"></FORM></body></html>
Bases de Données – Sylvain Brandel – 132
Utilisation de mySQL
• (au moins) 3 méthodes :– PHPmyAdmin– mysql– PHP
• Accessible avec un navigateur :– soit directement à l'URL http://localhost/mysql/– soit interactivement depuis http://localhost/home/
• Possibilités :– créer des bases, des objets, des utilisateurs…– manipuler des données– exporter des requêtes– …
PHPmyAdmin
Bases de Données – Sylvain Brandel – 133
Utilisation de mySQL
• Interpréteur SQL en ligne (genre sql*plus) dans une fenêtre invite de commande
• L'utilitaire mysql se trouve dans c:\Program Files\EasyPHP\mysql\bin
• Préciser éventuellement un nom d’ utilisateur :
• Pour accéder aux objets d'une base :
mysql
mysql> create database disco;mysql> connect discomysql> CREATE TABLE disques( -> titre varchar(30),artiste varchar(30),date date);mysql> INSERT INTO `disques` (`titre`, `artiste`, `date`) -> VALUES ('aux armes', 'gainsbourg', '12/12/2000');
mysql –u root
Bases de Données – Sylvain Brandel – 134
Utilisation de mySQL
• Langage de programmation servant à générer des pages HTML dynamiques (interfacées avec mySQL) ou statiques
• Exemple 1 :
PHP
<html><head><title> Test de mySQL </title></head><body>
<?php$link = @mysql_pconnect("localhost","root","") or exit();mysql_select_db("disco") or exit();$result = mysql_query("SELECT count(*) FROM disques") or exit();if ($row = mysql_fetch_array($result)) echo "<p> il y a ".$row[0]." ligne(s) dans la table disques";mysql_free_result($result);?></body></html>
Bases de Données – Sylvain Brandel – 135
Utilisation de mySQL
• Exemple 2 :
PHP
<html><head><title> Test de mySQL </title></head><body><?php$link = @mysql_pconnect("localhost","root","") or exit();mysql_select_db("disco") or exit();$result = mysql_query("SELECT * FROM disques",$link);if ($result) { echo "<p>Contenu de la table disques :<br>"; while ($row = mysql_fetch_object($result)) echo "titre: $row->titre, artiste: $row->artiste, date: $row->date<br>\n";}else { echo "<b>erreur dans l'exécution de la requête</b><br>"; echo "<b>message d'erreur de mysql :</b> ".mysql_error($link);}?></body></html>
Bases de Données – Sylvain Brandel – 136
•
•*
Bases de Données – Sylvain Brandel – 137
Recommended