Introduction BD /SGBD BD /SGBD Propriétés des bases

1
Département MIDO
L3 Informatique
Bases de Données Relationnelles
Geneviève Jomier - 2015-2016
Notes de cours
2
Introduction
1 - Qu'est-ce qu'une base de données ?
2 - Base de données / Système de Gestion de Bases de Données
3 - Bref historique
4 – Modélisation
3
Problèmes liés à l'usage de fichiers de données
* redondance des données stockées
* difficultés d'accès aux données (programme)
* problème de partage des données
* risques d'incohérence
* problèmes de sécurité et confidentialité
* non portabilité des applications
* problèmes de maintenance
4
BD /SGBD
Base de Données /
Système de Gestion de Bases de Données
Définition informelle :
BD : ensemble organisé de données persistantes reliées
entre elles
Objectif :
* stocker des données de manière persistante
* les mettre à jour aisément en maintenant leur cohérence
(problème de doublons, erreurs, contraintes...)
* les interroger facilement
* obtenir vite la réponse
5
BD /SGBD
Base de Données /
Système de Gestion de Bases de Données
* Système : "Ensemble d'éléments opérant en coordination
afin d'obtenir un résultat"
* Le SGBD offre aux utilisateurs
une "vue" des données dégagée des aspects physiques
6
Propriétés des bases de données
1 - Stockage de grandes quantités de données persistantes
2 - Accès aux données par langage de requête
3 - Accès rapide aux données
4 - Transactions
5 - Partage, accès multiutilisateurs concurrents
6 – Fiabilité : reprise après panne
7
Principales fonctions des SGBD
* modèle de données
* persistance des données
* gestion de la mémoire persistante
* gestion de la mémoire vive
* interrogation ad hoc
* gestion des transactions
* partage des données
* fiabilité des données
* sécurité des données
8
Exemples de requêtes
Base de données de scolarité des étudiants
1 - Nom et prénom de chacun des étudiants inscrits en L3
informatique ou L3 math ?
2 - Quels enseignants interviennent dans toutes les années de
licence informatique au Département MIDO ?
3 - Combien y a t'il d'étudiants dans chacun des Masters 2 de
MIDO ?
4 - Quels étudiants de MIDO ne suivent pas de cours de
langue ?
5 - Note finale moyenne pour chacun des cours de L3 ?
9
Modèles de Données
* Modèle conceptuel / Modèle de données d'un logiciel
* Schéma / Données
* Schémas de base de données :
logique,
physique,
externe ou vue
* Données

10
Histoire : modèles de données - SGBD
Génération 0 : Systèmes de Gestion de Fichiers depuis 1960
Génération 1 : Modèles hiérarchique (arbre) et réseau (graphe)
1960 élaboration, 1970 diffusion : IMS, IDS, Socrate - Clio... Norme CODASYL
Génération 2 : Modèle relationnel
1970 papier fondateur de Codd
1975 la technologie est là : INGRES à Berkeley, QUEL
1978 System R (IBM San José), SEQUEL, QBE
1980 les produits sont là : Oracle, Ingres, Informix, Sybase, DB2..
1985 le marché est là, 1990 les produits dominent le marché
Génération 3 : Modèle objet
1983 début Gemstone, 1985 la technologie est là
1990 les produits sont là : Gemstone, O2, Orion, Objecstore, Ontos, Versant, M
2000 ont disparu du marché, sauf produits libres ; le relationnel - objet apparaît
Aujourd'hui : Semi-structuré (XML, XQuery...), Multimédia, BD géographiques etc.
Les BD sont omniprésentes, souvent invisibles : imaginez la société sans BD !
11
Chapitre I - Le modèle Entité - Association
1- Notion d'entité et d'ensemble d'entités
2- Notion d'association
3- Représentation standard
4- Généralisation et spécialisation
5- Agrégation
6- Modélisation Entité/Association
12
1- Entité et ensemble d'entités
* Définition :
Une entité est un élément, ou objet, qui existe et
qu'on peut distinguer des autres éléments
* Une entité est représentée par un ensemble
d'attributs qui la décrivent
* Chaque attribut a un domaine de valeurs :
formellement un attribut est une application Att : {entité} → domaine
* Identification d'entité
entité faible
* Ensemble d'entités de même type
13
2 - Association
* Association entre plusieurs entités, souvent binaire
* Les associations peuvent avoir des attributs
* Cardinalité
* Ensemble d'associations de même type
14
6 – Méthodologie de modélisation E/A
a) Déterminer entités et attributs (étape itérative) :
* si de l'information décrit un objet il devient entité
* s'il y a seulement besoin d'un identifiant pour un objet : devient attribut
* les attributs multi - valués deviennent des entités
* si un attribut a une association 1:N avec une entité il doit devenir entité
* les attributs doivent être attachés aux entités qu'ils décrivent le plus
directement
* éviter les identificateurs composites : souvent ce sont des associations
b) Identifier généralisation et spécialisation
c) Définir les associations
* élimination des associations redondantes
* attention aux associations ternaires (n - aires)
15
Chapitre II – Le modèle relationnel
1. Notions fondamentales
2. Passage entité/association à relationnel
3. Algèbre relationnelle
4. Calcul relationnel
16
1- Notions fondamentales
1. BD relationnelle : {relation identifiée par nom unique}
* relation = table
* attribut = colonne
* domaine de valeurs d'un attribut / type
* n-uplet = ligne
2. Pourquoi "relation" ?
Soit D1, D2, ... Dn des domaines
Une relation est un sous-ensemble du produit cartésien
D1*D2*..*Dn
17
1 - Notions fondamentales
3. Propriétés :
schéma de relation R ≠ instance de relation r
Schéma
* noms de relation : tous différents
* noms d'attribut :
- tous différents dans une relation
- si identiques dans 2 relations alors
même sémantique et même domaine
Instance
* nuplets d'une relation: tous différents ⇒ identification
* pas de valeur d'attribut manquante, NULL
* valeur d'attribut : atomique ⇒ 1ère forme normale
18
2 - Passage E/A → relationnel
N : M
1 : N
N : 1
1 : 1

19
3 - Algèbre relationnelle
Complétude
1. Opérations primitives :
* renommage !
* pas de complémentaire
Unaires :
* sélection, σ
* projection, π
Binaires :
* produit cartésien *
* union ∪
* différence /
2. Opérations dérivées
* intersection ∩
* théta - jointure, θ
* équijointure, jointure naturelle
* division (ensembliste), :
20
Exemple jouet
EstParentDe (Parent, Enfant) ou R1
Personne (NomPersonne, Age, Sexe) ou R2
Scolarité(Enfant, Ecole) ou R3
Dom (Parent, Enfant, NomPersonne) = {nom de personne}
Dom (Sexe) = {M, F} ; Dom (Ecole) = {nom d'école}
Hypothèse : le nom de la personne l'identifie
r1= {(Pol, Marie), (Jean, Jack), (Marie, Jules), (Jean, Jules)}
r2= {(Pol, 50, M), (Jean, 25, M), (Marie, 23, F), (Jules, 5, M),
(Jack, 12, F)}
r3= {(Marie, X), (Jean, X), (Jack, MC), (Jules, MC)}
21
Prédicats de l'algèbre relationnelle 1
Vocabulaire :
(i) Opérandes : minuscules (constantes),
MAJUSCULES (noms d'attributs)
(ii) Opérateurs de comparaison : = , > , ≥ , < , ≤ , ≠
(iii) Connecteurs logiques ∧, V , ¬
(iv) ( , )
Atomes :
(i) A θ c
(ii) A θ B
où θ est un opérateur de comparaison
22
Prédicats de l'algèbre relationnelle 2
Prédicats :
(i) Un atome est un prédicat
(ii) Si P est un prédicat, (P) et ¬P sont des prédicats
(iii) Si P et Q sont des prédicats,
P ^ Q et P ∨ Q sont des prédicats
Exemple : R(A,B,C)
((A=C) ^ (B= b)) V (A>C)
23
Algèbre relationnelle étendue
1- Valeurs nulles
2 - Projection généralisée
3 - Jointure extérieure : outer join
* right outer join
* left outer join
* full outer join
4 - Aggrégats
* agrégats : AGR (ExprAtt)
sum, avg, count, min, max ; (distinct)
* groupement : ListeAttGrAGRExprAtt (R)
24
Modifications de la base
1- Effacement de nuplet
2- Insertion de nuplet
3- Mise à jour de nuplet
25
4 - Calcul relationnel
Introduction :
* Langage impératif / déclaratif
* Hypothèse monde fermé
/ Hypothèse monde ouvert
* Calcul à variables-nuplet
/ Calcul à variables-domaine
26
Prédicat
Formule contenant des variables et
pouvant être vraie ou fausse
Exemples :
* n ≥ m notée P(n,m)
* ∃ p entier : 4*p = q notée R(q)
* La suite A commence par 0101 notée S(A)
P(n,m) vrai ou faux selon la valeur de n et m :
P(2,3) est faux, P(3,2) est vrai
R(4) est vrai, R(5) est faux
S(10010000) est faux
27
Prédicats et modèle relationnel
Relation : EstPère (Père, Fils)
Si Pierre est père de Paul
(Pierre, Paul ) est nuplet de EstPère
EstPère (Pierre, Paul) est vrai
Monde fermé
si (Pierre, Paul ) n'est pas nuplet de EstPère
alors EstPère (Père, Fils) faux
Monde ouvert
si (Pierre, Paul ) n'est pas nuplet de EstPère
alors EstPère (Père, Fils) faux ou "ne sait pas"

28
4 - Calcul relationnel
* Variable : nuplet
* Requête : { t / F (t) }
ensemble des nuplets t tels que F(t) est vraie
* Variable non quantifiée t : libre
quantifiée ∀x ou ∃x : liée
Exercice : exprimer en calcul relationnel chaque opération relationnelle
29
4 - Calcul relationnel
Atomes :
(i) R(s) où R est nom de relation et s variable - nuplet
(ii) s.A θ t.B
où s et t sont des variables nuplets,
A, attribut de la relation où s est défini,
B, attribut de la relation où t est défini,
θ, comparateur =, >, ≥, < , ≤, ≠
(iii) s.A θ c
où s est variable - nuplet,
A, attribut de la relation où s est défini
c, constante de même domaine que A
θ, comparateur =, >, ≥, < , ≤, ≠
30
4 - Calcul relationnel
Formules :
(i) Un atome est une formule
(ii) Si F est une formule
(F) et ¬F sont des formules
(iii) Si F et G sont des formules
F ^ G et F V G sont des formules
(iv) Si F est une formule où t est une variable libre alors
∃t F(t) et ∀t F(t) sont des formules
31
Calcul relationnel – Formules saines
Domaine de la formule {t / F(t)} :
Dom( F) : { valeurs } qui
* soit apparaissent comme constantes dans F(t),
* soit existent dans un nuplet quelconque des relations référencées
dans F(t)
Une formule est saine si :
(i) toutes les valeurs de son résultat appartiennent à son domaine
(ii) pour toute sous-formule de la forme ∃ s F1(s), la sous-formule est
vraie s'il existe un nuplet s dont les valeurs sont dans Dom(F1) tel que
F1(s) est vraie
(iii) pour toute sous-formule de la forme ∀s F1(s), la sous-formule est
vraie si F1(s) est vraie pour tous les nuplets s qui ont leurs valeurs dans
Dom(F1)
32
Chapitre 3
SQL
Histoire
Création SEQUEL 1978,
Normes : SQL en 1986 puis 89, SQL2 en 1992, SQL3 en 1999 puis 2003
Chaque SGBD a son propre dialecte évolutif et plus ou moins conforme à la norme en cours.
Conclusion pratique
Lorsqu'on utilise un SGBD il faut toujours se reporter à la documentation SQL correspondante.
Bonne pratique : si on cherche la portabilité, utiliser au maximum les formes normalisées des
phrases SQL.
Avertissement
Ce document présente des formes simples des phrases SQL les plus communément utilisées
sans viser à l'exhaustivité qui le rendrait illisible. Pour plus de détails et d'exhaustivité se
reporter à : SQL in a nutshell de KLINE Kevin, KLINE Daniel, BRAND Hunt ; 2 ème édition
traduite en français "SQL en concentré" édition O'Reilly, 690 pages .
33
LANGAGES COMMERCIAUX de BD
* définition et évolution du schéma logique
* interrogation de la base
* mise à jour des données
* maintien de la cohérence :
contraintes
transactions, validation/abandon
* schéma externe : vues, autorisations d'accès
* schéma physique : chemins d'accès rapides aux données,
index
34
SQL
Convention d'écriture de la grammaire
MAJUSCULES : mots réservés de SQL
minuscules : mots réécrits dans une autre règle ou
noms et valeurs de la base de données
(= mots terminaux non réécrits)
::= : "est réécrit"
[ ] : optionnel
/ : ou
{} : ensemble* : tous les attributs de la relation
35
SQL - Interrogation
select_de_base ::=
SELECT [ALL | DISTINCT] attributs_sélectionnés
FROM tables_sélectionnées
[WHERE prédicat ]
[ORDER BY spécification_tri ]
attributs_sélectionnés::= *| liste_attributs
tables_sélectionnées ::= nom_table [ [AS] var ] var-nuplet
prédicat::= [NOT] prédicat _de _base | prédicat_général
| prédicat_between | prédicat_in
| prédicat_like | prédicat_null
| prédicat_quantifié
| prédicat_exists
36
SQL - Interrogation
spécification_tri::= ORDER BY liste_spécif[ication]_élementair
spécif[ication]_élem[entaire] ::=
{entier_non_signé |nom_attribut} [ASC | DESC]
prédicat _de _base ::= prédicat_élem[entaire] AND | OR
prédicat_élem[entaire]
prédicat_élem ::= attribut comparateur {attribut |constante}
comparateur ::= = | <> | != | > | >= | < | <=

37
SQL-Interrogation
prédicat_général ::= expr[ession]_éval[uable] comparateur
{expr[ession]_éval[uable] | sous_requête}
expr[ession]_éval[uable] ::= terme | terme { + | - } terme
terme ::= facteur | terme { * | / } facteur
facteur ::= [+ | - ] constante | [+ | - ] attribut
| [+ | - ] agrégat | ( expression_évaluable )
38
SQL-Interrogation
agrégat ::= COUNT(*)
| AVG ([ALL | DISTINCT] expression_évaluable)
| MAX (expression_évaluable)
| MIN (expression_évaluable)
| SUM ([ALL | DISTINCT] expression_évaluable)
| COUNT ([ALL | DISTINCT] expression_évaluable)
GROUP BY attribut [{,attribut}...] forme simple
HAVING prédicat
39
SQL-Interrogation
prédicat_between::= expr_éval [NOT] BETWEEN
expr_éval AND expr_éval
≤ et ≥
prédicat_in ::= expr_éval [NOT] IN
({liste_valeurs |sous_requête})
liste_valeurs ::= valeur [{, valeur}...]
prédicat_like :: attribut [NOT] LIKE motif
motif : chaine de caractères avec
_ un caractère indéfini
% nombre indéfini de caractères indéfinis
40
SQL-Interrogation
prédicat_null ::= attribut IS [NOT ] NULL
(prédicat null) {AND|OR} prédicat = {True| False| Unknown}
logique à 3 valeurs
prédicat_quantifié ::= expression_évaluable comparateur
{ALL | SOME | ANY} (sous_requête)
prédicat_exists ::= EXISTS (sous requête)
41
SELECT
SELECT [{ALL | DISTINCT} ] élément_select [AS alias] [,...]
FROM liste_table_ref
[WHERE condition ]
[clause_grouper ]
[clause_sélection_groupes ]
élément_select ::= * |nom_table.* | expression_attribut
expression_attribut :
nom d'attribut d'une table ou d'une vue,
calcul mathématique, fonction agrégat ou scalaire,
paramètre ou variable, sous-requête
42
SQL2 -SELECT... FROM table_ref
table_ref ::= nom_table [ [AS] range_variable
[(column_name_comma_list )]]
| (table_expression) [[AS] range_variable
[(column_name_comma_list )]]
| join_table_expression
table_expression ::= join_table_expr | nonjoin_table_expr
join_table_expr ::=
table_ref [NATURAL] [join-type ] JOIN table_ref
[ON cond-expr | USING (column_name_comma_list )]
| table_ref CROSS JOIN table_ref
| (join_table_expr )
43
SQL2 - Explicites Join et NonJoin
join_type ::= INNER
| LEFT [OUTER]
| RIGHT [OUTER]
| FULL [OUTER]
nonjoin_table_expr ::=
table_expr [ INTERSECT | UNION | EXCEPT] [ALL]
[CORRESPONDING BY (column_name_comma_list )]]
table_expr
Note : la clause Corresponding By généralement pas implantée dans les SGBD
44
SQL3 – sous-clause JOIN
FROM table [ AS alias ] { CROSS JOIN table
| { [NATURAL] [type_jointure] JOIN table_jointe [AS alias]
{ON cond_jointure [ { AND | OR } cond_jointure] [....] ]
| USING (colonne1 [,....] ) } }
[....]
cond[ition]_jointure ::= table1.att1 comparateur table2.att2
comparateur ::= = | <> | != | > | >= | < | <=
45
SQL3 - Mise à jour des données
INSERT INTO [ONLY] {nom_table " nom_vue } [(liste_attribut)]
[OVERRIDE {SYSTEM /USER} VALUES]
{DEFAULT VALUES / VALUES (liste_valeur ) | requête} ;
valeur ::= constante
| sélection_d'une colonne
| expression_évaluable
| NULL
| DEFAULT
| CURRENT_DATE
| CURRENT_TIME
| CURRENT_TIMESTAMP
Only : cas héritage