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

Problèmes liés à l'usage de fichiers de données
Introduction
1 - Qu'est-ce qu'une base de données ?
Département MIDO
L3 Informatique
2 - Base de données / Système de Gestion de Bases de Données
Bases de Données Relationnelles
Geneviève Jomier - 2015-2016
Notes de cours
* 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
3 - Bref historique
* problèmes de sécurité et confidentialité
4 – Modélisation
* non portabilité des applications
* problèmes de maintenance
1
BD /SGBD
2
BD /SGBD
Base de Données /
Système de Gestion de Bases de Données
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
* Système : "Ensemble d'éléments opérant en coordination
afin d'obtenir un résultat"
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
4
Principales fonctions des SGBD
* Le SGBD offre aux utilisateurs
une "vue" des données dégagée des aspects physiques
4 - Transactions
5 - Partage, accès multiutilisateurs concurrents
6
Exemples de requêtes
1 - Nom et prénom de chacun des étudiants inscrits en L3
informatique ou L3 math ?
* gestion des transactions
3 - Accès rapide aux données
5
* persistance des données
* interrogation ad hoc
1 - Stockage de grandes quantités de données persistantes
6 – Fiabilité : reprise après panne
* modèle de données
* gestion de la mémoire vive
Propriétés des bases de données
2 - Accès aux données par langage de requête
Base de données de scolarité des étudiants
* gestion de la mémoire persistante
3
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 ?
* partage des données
4 - Quels étudiants de MIDO ne suivent pas de cours de
langue ?
* fiabilité des données
5 - Note finale moyenne pour chacun des cours de L3 ?
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
* sécurité des données
7
8
9
Histoire : modèles de données - SGBD
Chapitre I - Le modèle Entité - Association
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
1- Notion d'entité et d'ensemble d'entités
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é
2- Notion d'association
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
4- Généralisation et spécialisation
3- Représentation standard
2 - Association
* 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
entité faible
6- Modélisation Entité/Association
10
* Définition :
Une entité est un élément, ou objet, qui existe et
qu'on peut distinguer des autres éléments
* Identification d'entité
5- Agrégation
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 !
1- Entité et ensemble d'entités
* Ensemble d'entités de même type
11
6 – Méthodologie de modélisation E/A
12
Chapitre II – Le modèle relationnel
a) Déterminer entités et attributs (étape itérative) :
* Association entre plusieurs entités, souvent binaire
* 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
* Les associations peuvent avoir des attributs
* si un attribut a une association 1:N avec une entité il doit devenir entité
2. Passage entité/association à relationnel
* les attributs doivent être attachés aux entités qu'ils décrivent le plus
directement
3. Algèbre relationnelle
* éviter les identificateurs composites : souvent ce sont des associations
* Cardinalité
1. Notions fondamentales
b) Identifier généralisation et spécialisation
4. Calcul relationnel
c) Définir les associations
* Ensemble d'associations de même type
* élimination des associations redondantes
* attention aux associations ternaires (n - aires)
13
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
14
1 - Notions fondamentales
3. Propriétés :
15
2 - Passage E/A → relationnel
schéma de relation R ≠ instance de relation r
Schéma
* noms de relation : tous différents
N:M
* 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
1:N
N:1
1:1
* pas de valeur d'attribut manquante, NULL
* valeur d'attribut : atomique ⇒ 1ère forme normale
16
17
18
3 - Algèbre relationnelle
Exemple jouet
Prédicats de l'algèbre relationnelle 1
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), :
EstParentDe (Parent, Enfant) ou R1
Personne (NomPersonne, Age, Sexe)
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)}
19
Prédicats de l'algèbre relationnelle 2
ou R2
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
20
Algèbre relationnelle étendue
21
Modifications de la base
1- Valeurs nulles
Prédicats :
2 - Projection généralisée
(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
3 - Jointure extérieure : outer join
* right outer join
* left outer join
* full outer join
1- Effacement de nuplet
2- Insertion de nuplet
3- Mise à jour de nuplet
4 - Aggrégats
* agrégats : AGR (ExprAtt)
sum, avg, count, min, max ; (distinct)
* groupement : ListeAttGrAGRExprAtt (R)
Exemple : R(A,B,C)
((A=C) ^ (B= b)) V (A>C)
22
4 - Calcul relationnel
23
Prédicat
Formule contenant des variables et
pouvant être vraie ou fausse
Introduction :
* Langage impératif / déclaratif
* Hypothèse monde fermé
/ Hypothèse monde ouvert
* Calcul à variables-nuplet
/ Calcul à variables-domaine
25
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
26
24
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"
27
4 - Calcul relationnel
4 - Calcul relationnel
4 - Calcul relationnel
Atomes :
* Variable : nuplet
(i) R(s) où R est nom de relation et s 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
Calcul relationnel – Formules saines
(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
(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 =, >, ≥, < , ≤, ≠
(iv) Si F est une formule où t est une variable libre alors
∃t F(t) et ∀t F(t) sont des formules
29
30
Chapitre 3
LANGAGES COMMERCIAUX de BD
SQL
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)
(i) Un atome est une formule
(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 =, >, ≥, < , ≤, ≠
28
* définition et évolution du schéma logique
Histoire
Une formule est saine si :
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.
(i) toutes les valeurs de son résultat appartiennent à son domaine
Conclusion pratique
(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
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.
(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)
Formules :
* interrogation de la base
* mise à jour des données
* maintien de la cohérence :
contraintes
transactions, validation/abandon
Avertissement
* schéma externe : vues, autorisations d'accès
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 .
* schéma physique : chemins d'accès rapides aux données,
index
31
32
33
SQL
Convention d'écriture de la grammaire
SQL - Interrogation
SQL - Interrogation
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
34
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 ]
spécification_tri::= ORDER BY liste_spécif[ication]_élementair
spécif[ication]_élem[entaire] ::=
{entier_non_signé |nom_attribut} [ASC | DESC]
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
35
prédicat _de _base ::= prédicat_élem[entaire] AND | OR
prédicat_élem[entaire]
prédicat_élem ::= attribut comparateur {attribut |constante}
comparateur ::=
= | <> | != | > | >= | < | <=
36
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 )
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}...]
37
SQL-Interrogation
(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_like :: attribut [NOT] LIKE motif
motif : chaine de caractères avec
_ un caractère indéfini
% nombre indéfini de caractères indéfinis
38
39
SELECT
SQL2 -SELECT... FROM table_ref
SELECT [{ALL | DISTINCT} ] élément_select [AS alias] [,...]
FROM liste_table_ref
[WHERE condition ]
[clause_grouper ]
[clause_sélection_groupes ]
table_ref ::= nom_table [ [AS] range_variable
[(column_name_comma_list )]]
| (table_expression) [[AS] range_variable
[(column_name_comma_list )]]
| join_table_expression
élément_select ::= * |nom_table.* | expression_attribut
table_expression ::= join_table_expr | nonjoin_table_expr
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
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 )
40
SQL2 - Explicites Join et NonJoin
41
42
SQL3 – sous-clause JOIN
SQL3 - Mise à jour des données
FROM table [ AS alias ] { CROSS JOIN table
| { [NATURAL] [type_jointure] JOIN table_jointe [AS alias]
{ON cond_jointure [ { AND | OR } cond_jointure] [....] ]
| USING (colonne1 [,....] ) } }
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
prédicat_in ::= expr_éval [NOT] IN
({liste_valeurs |sous_requête})
liste_valeurs ::= valeur [{, valeur}...]
prédicat_exists ::= EXISTS (sous requête)
join_type ::= INNER
| LEFT [OUTER]
| RIGHT [OUTER]
| FULL [OUTER]
prédicat_between::= expr_éval [NOT] BETWEEN
expr_éval AND expr_éval
≤ et ≥
forme simple
HAVING prédicat
prédicat_null ::= attribut IS [NOT ] NULL
SQL-Interrogation
SQL-Interrogation
[....]
cond[ition]_jointure ::= table1.att1 comparateur table2.att2
comparateur ::=
= | <> | != | > | >= | < | <=
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
43
44
45
SQL - Mise-à-jour des données
UPDATE nom_table
SET liste_attribut_valeur
[WHERE prédicat ] ;
attribut_valeur ::= attribut = expr_éval | NULL | DEFAULT
DELETE FROM nom_table
[WHERE prédicat ] ;
attention à destructions en cascade catastrophiques(récursion)
Définition du schéma
CREATE SCHEMA [nom_schéma ]
[AUTHORIZATION nom_propriétaire ]
[DEFAULT CHARACTER SET nom_ jeu_caractères ]
[PATH nom_schéma [,...]]
[liste_élément_schéma ]
élément_schéma ::= |créer_table
|créer_vue
|créer_index
|créer_déclencheur
|créer_type |créer_méthode
|créer_fonction|procédure
|créer_rôle, attribuer privilèges
46
47
CREATE DOMAIN nom [AS] type [valeur][contrainte]
CREATE TYPE
Pas disponible dans SQL3
valeur ::= DEFAULT constante |NULL |USER
|CURRENT_DATE |CURRENT_TIME
|CURRENT_TIMESTAMP
type ::= CHAR [(n)] | VARCHAR [(n)]
| SMALLINT | INTEGER
| NUMERIC [(p [ ,q ] )] | DECIMAL [(p [ ,q ] )]
| FLOAT [(n)]
| DATE | TIME frac
| TIMESTAMP frac
| INTERVAL type_inter
des TYPES
STANDARD SQL2003
BIT , BOOLEAN
CHAR[ACTER] [(n)]
VARCHAR [(n)]
SMALLINT, INT[EGER] , BIGINT
NUMERIC [(p [ ,q ] )] , DEC[IMAL] [ (p [ ,q ] ) ]
FLOAT [(n)], REAL , DOUBLE
DATE , TIME , TIMESTAMP
INTERVAL , INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
BLOB, CLOB,
POINT, POLYGON, CIRCLE
48
CREATE TABLE [{LOCAL|GLOBAL} TEMPORARY]
nom_table (liste_def_attribut [liste_contrainte_table ] ) ;
CREATE TYPE name AS
( [ attribute_name data_type [ COLLATE collation ] [, ...] ])
CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] )
CREATE TYPE name AS RANGE (SUBTYPE = subtype [, ...])
CREATE TYPE name ( INPUT = input_function,
OUTPUT = output_function [ ,])
CREATE TYPE name
def_attribut ::= nom_attribut nom_ type | nom_domaine
[ val_par_défaut ] {contrainte_attribut }
contrainte_attribut ::= CONSTRAINT [nom_contrainte]
type_contrainte_attribut [prédicat]
[report_contrainte] [moment_report]
type_contrainte_attribut ::= PRIMARY KEY
| NOT NULL | UNIQUE | DEFAULT expression
| CHECK (prédicat ) | FOREIGN KEY
report_contrainte ::= [ NOT ] DEFERRABLE
moment_report ::= INITIALLY DEFERRED
| INITIALLY IMMEDIATE
contrainte ::= CONSTRAINT nom_contrainte { NOT NULL |
NULL | CHECK (expression) }
49
50
51
CREATE TABLE (suite)
CREATE TABLE (suite)
SQL3 – Déclencheur
Contrainte d'intégrité référentielle attribut
[CONSTRAINT [nom_contrainte]
FOREIGN KEY (colonne_locale [,...]
REFERENCES nom_table [(attr [,...])]
[MATCH ] {FULL | PARTIAL | SIMPLE } ]
pr val null d'att
[ON {UPDATE | DELETE} { NO ACTION | CASCADE |
RESTRICT |SET NULL | SET DEFAULT } ] on att référencé
[report_contrainte] [moment_report]
contrainte_table ::= CONSTRAINT [nom_contrainte]
type_contrainte_table
[report_contrainte] [moment_report]
type_contrainte_table::= | PRIMARY KEY (liste_attribut )
| UNIQUE (liste_attribut )
| CHECK (condition )
| FOREIGN KEY (liste_attribut )
REFERENCES nom_table [(attr [,...])]
définie not deferrable
[MATCH ] {FULL | PARTIAL | SIMPLE } ]
pr val null d'att
[ON {UPDATE | DELETE} { NO ACTION | CASCADE |
RESTRICT | SET NULL | SET DEFAULT } ] on att référencé
no action: test après opération ;
restrict: test avant opération ;
52
cascade: même action sur référençant ;
set null/default à clé étrangère référençante
53
CREATE TRIGGER nom_déclencheur
{BEFORE | AFTER}
{INSERT |DELETE |UPDATE [OF nom_attribut [,...] ] }
ON nom_table
[REFERENCING {OLD {[ROW ]|TABLE } AS ancien_nom
|NEW {[ROW |TABLE ] } AS nouveau_nom ]
[FOR EACH {ROW | STATEMENT }]
pour chaque nuplet modifié| à chaque instruction SQL appliquée à la table. Ex màj de salaire de 100
employés "for each row" trigger exécuté 100 fois, "for each instruction" trigger exécuté une fois
[WHEN (condition)
[BEGIN ATOMIC]
bloc_code
[END]
une ou plusieurs instructions SQL
54
Déclencheur PostgreSQL
CREATE TRIGGER nom_déclencheur
{BEFORE | AFTER}
{{ [INSERT] [OR|,] [DELETE] [OR|,] [UPDATE]} [OR ...] }
ON nom_table
FOR EACH {ROW | INSTRUCTION }
EXECUTE PROCEDURE nom_fonction (paramètres)
Fonctions
fonction : identificateur demandant au SGBD d'effectuer dans
une instruction SQL une opération programmée
résultat : une valeur unique utilisée dans l'instruction SQL en
lieu et place de l'appel de la fonction
traitement : identique aux opérateurs (qui sont des appels à
des fonctions cachées = sucre syntaxique)
nom_fonction (parametre [, parametre])
55
Types de Fonction
fonction opérant sur des valeurs d'un nuplet
des valeurs d'un ensemble
un ensemble de nuplets
aucun paramètre : CURRENT_TIME
fonctions agrégats ANSI SQL2003:
ex. COVAR, PERCENTILE, REGR...
fonctions scalaires ANSI SQL2003 :
intégrées : CURRENT_DATE, CURRENT_USER...
CASE et CAST
dates et heures :
numériques : ABS, MOD, CHAR_LENGTH, SQRT...
chaînes
fenêtre
56
57
CREATE FUNCTION
SQL -Vues
PostgreSQL
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = }
default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name | ... | } ...
[ WITH ( attribute [, ...] ) ]
Vues
1- create view v as <query expression>
2- vues définies sur d'autres vues
3- mise à jour à travers de vues
et valeurs nulles
58
59
CREATE VIEW
nom_vue [(liste-attribut)]
AS instruction_select
[WITH [CASCADED| LOCAL] CHECK OPTION]
ALTER VIEW nom_vue
DROP VIEW nom_vue
60
Index
Modifications de schéma ALTER
CREATE [ UNIQUE ] INDEX nom_index ON
nom_table (liste_colonne) ;
colonne_tri ::= nom_attribut [ASC | DESC ]
dans PostgreSQL : [USING {BTREE | RTREE | HASH] }
ALTER TABLE nom_table
ajouter_def
|modifier_def
|supprimer_def
SQL3 – Suppression
d'objet créés par CREATE
DROP { type_objet } nom_objet { RESTRICT | CASCADE }
ajouter_def ::= ADD COLUMN def_attribut
| CONSTRAINT contrainte_table
hachage linéaire de Litwin
modifier_def ::= ALTER def_attribut
DROP INDEX nom_index
ALTER INDEX nom_index
supprimer_def ::= DROP COLUMN nom_attribut
| CONSTRAINT nom_contrainte
61
62
63
Privilèges
GRANT { privilege | rôle }
ON objet TO user
[WITH GRANT OPTION] ;
SQL3 - TRANSACTIONS
SQL Standard Niveaux d'Isolation de Transactions
SET TRANSACTION et START TRANSACTION
privilege ::=
SELECT |INSERT [(list_att)] | UPDATE(list_att) | DELETE
REVOKE {[GRANT OPTION FOR] | { privilege | rôle } }
ON objet FROM user [, ...]
[GRANTED BY {CURRENT USER | CURRENT ROLE} ]
{RESTRICT |CASCADE} ;
Lecture sale
Lecture non
répétable
Lecture de
fantômes
Possible
Possible
Possible
Lecture validée
Possible
Possible
Possible
Lecture répétable
Pas possible Pas possible
Possible
Sérialisation
Pas possible Pas possible
Pas possible
SET TRANSACTION [READ ONLY | READ WRITE]
[ ISOLATION LEVEL {READ COMMITTED
| READ UNCOMMITTED | REPEATABLE READ
| SERIALIZABLE } ]
Niveau d'Isolation
COMMIT
[WORK] [AND [NO] CHAIN]
ROLLBACK
[WORK] [AND [NO] CHAIN]
[TO SAVEPOINT nom_point_sauvegarde]
Lecture non validée
AUTOCOMMIT
64
65
Degrés d'isolation de SQL2
Programmation avec SQL
Cas du verrouillage
2 approches
d°0 : verrous courts exclusifs lors des écritures
=>"read uncommitted"=> pas de perte d'écriture ; lecture sales
66
Chapitre IV
Dépendances Fonctionnelles
* Extension de SQL :
ajout de caractéristiques de langage de programmation :
déclarations, tests, boucles... => PL/SQL, plpgSQL etc.
1. Notion de dépendance fonctionnelle, DF
d°1 : verrous longs exclusifs des écritures pas de verrous lect.
=> "read committed" => pas de perte d'écriture ni lecture sale
Usage facile mais limité
2. Implications logiques entre DF
d°2 : d°1 + verrous courts partagés lors des lectures
=> "repeatable read" si entre 2 Li(x) pas de Ej(x) (brève trans.)
=> restent des lectures non répétables et fantômes
* Plongement de SQL dns langage de programmation hôte
embedded SQL : Cobol, C, C++, Java...
problèmes de type : nombre de nuplets d'une relation indéfini
=> curseur et opérations nuplet par nuplet
3. Axiomes d'Amstrong
d°3 : d°1 + verrous longs partagés lors des lectures
=> sérialisation
Puissance du langage mais problèmes de performances
si programmeur incompétent en SQL
4. Fermeture d'une famille d'attributs
5. Equivalence d'ensembles de DF, Couverture
6. Clé
67
68
69
Dépendances Fonctionnelles
1.Notions
Dépendances Fonctionnelles
Dépendances Fonctionnelles
2. Implications logiques
Anomalies dues aux DF :
* redondance
* modification
* suppression
Définition :
Notation :
X→ Y
F |= { X →Y }
F implique logiquement { X →Y }
Notations :
R : schéma
X-> Y si dans les nuplets de R à une instance x de X
correspond une seule instance y de Y
/
A, B, C .... attribut
r : instance = données de R
/
X, Y, Z ... {attribut}
R( A1, A2, ... An)
70
X détermine fonctionnellement Y
ou Y dépend fonctionnellement de X
∀ t et u nuplets de r tels que t.X = u.X alors t.Y = u.Y
71
Définition :
La fermeture de F , F+, est l'ensemble des
dépendances déduites de F :
F + |= { X →Y / F |= X → Y }
72
Dépendances Fonctionnelles
3. Axiomes d'Amstrong
Dépendances Fonctionnelles
3. Axiomes d'Amstrong
Dépendances Fonctionnelles
4. Fermeture d' {attribut}
Propriétés :
Définition :
P1 - Union : X → Y et X → Z ⇒ X → YZ
X+ : fermeture de X par rapport à une famille de DF F
U univers d'attributs, F : { DF }
P2 - Pseudo-Transitivité : X→Y et WY→Z ⇒ WX→Z
A1- Réflexivité : Y ⊆ X ⇒ X → Y
P3 - Décomposition : X → Y et Z ⊆ Y ⇒ X → Z
X+ est l'ensemble des attributs A tels que X→A peut
être déduit de F par les axiomes d'Amstrong
Axiomes :
A2 - Augmentation : X → Y et Z ⊂ U ⇒ ZX → ZY
A3 - Transitivité : X → Y et Y → Z ⇒ X → Z
Lemme :
Forme canonique de DF :
X → A (un seul attribut à droite)
73
DF 4. Fermeture d'{attribut}
Algorithme de calcul X+, fermeture de X
Entrée :
* ensemble fini d'attributs U
* ensemble de DF F sur U
* X inclus dans U
Sortie : X+, fermeture de X par rapport à F
74
U = A, B, C, D, E
F = {A →B, BD → C, BC→ E, B → AE}
A+ ?
Définitions :
Soit F et G , 2 ensembles de DF sur U
1- F et G sont équivalents, noté G ≡ F, ssi F+ = G+
2- Si F+ ⊆ G+, G est une couverture de F
Problème concret :
A la détermination du schéma d'une application on peut trouver
beaucoup de DF : soit F.
Problème : trouver une famille G la plus simple possible et
équivalente à F
Comme X=X0 ⊆ X1 ⊆ X2 ⊆ ... Xi ⊆ Xi+1 ... ⊆ U et U est fini,
⇒ à partir d'un certain indice i, Xi+1 =Xi , c'est X+
76
77
{Dépendances Fonctionnelles}
minimal
Equivalence d'ensembles de DF
Algorithme :
Définition :
Un ensemble F de DF est minimal ssi :
Pour montrer que 2 familles de DF, F et G, sont
équivalentes, on montre :
1) Toute dépendance fonctionnelle de F est en forme
canonique
1) toute dépendance de F peut être déduite de G
⇒ F ⊆ G+ ⇒ F+ ⊆ G+
2) Il n'y a pas de DF X → A dans F telle que
F - {X → A} soit équivalente à F
2) toute dépendance de G peut être déduite de F
⇒ G ⊆ F+ ⇒ G+ ⊆ F+
3) Il n'y a pas de dépendance X → A dans F et Z ⊂ X
telle que F - {X → A } U {Z→ A} soit équivalente à F
1) et 2) ⇒ G+ = F+ ⇒ G ≡ F
79
75
Equivalence d'ensembles de DF
Couverture
Exemple :
Méthode :
1) X0 = X
2) X i+1 = Xi U { Z | Y → Z et Y inclus dans Xi }
Définition :
Une dépendance fonctionnelle est en forme canonique si elle
n'a qu'un attribut en partie droite.
X-> Y peut être déduit des axiomes d'Amstrong
ssi Y ⊆ X+
80
78
CLE
Définitions
Soit R une relation munie de F {dépendance fonctionnelle }
minimal
1) K est une clé/surclé de R ssi ∀A attribut de R, K → A
2) K est une clé (minimale) de R ssi K est une clé de R et
∀K' ⊂ K , K' n'est pas clé de R
Conséquence
Une relation est un {nuplet}. Dans un ensemble tous les
éléments sont différents ⇒ une relation a toujours au moins
une clé : l'ensemble de ses attributs.
81
Déterminer toutes les Clés minimales
de R muni de F, {DF } minimal
Algorithme :
Soit U { attribut de R } : U est une clé de R.
1) Un attribut absent de toute DF de F ∈ toutes les clés minimales
2) Un attribut toujours en partie gauche de DF ∈ toutes les clés minimales
3) Un attribut toujours en partie droite de DF ∈ aucune clé minimale
4) Un attribut présent en partie droite de certaines dépendances de F et en
partie gauche d'autres dépendances peut appartenir à certaine clés
minimales et pas à d'autres.
A partir de U et de 1), 2), 3) on construit un ensemble M d'attributs de R ; si
M est une clé c'est la clé minimale. Sinon à partir de M et 4) on construit un
ensemble L d'attributs de R qui est une clé de R. La détermination de
toutes les clés minimales est réalisée en enlevant de L successivement
un ou des attributs de 4) et en testant la minimalité du résultat.
Exercice : calculez le nombre d'étapes.
Chapitre V
Décomposition de schéma de relation
1- Décomposition SPI : Sans Perte d'Information
Algorithme de Chase
Introduction
Lemme de décomposition SPI : Soit R(XYZ) une
relation telle que X→Y alors R1(XY) et R2(XZ) est
une décomposition SPI. A démontrer
2 - 3ème Forme Normale : 3NF
2 - Décomposition SPD :
Sans Perte de Dépendance
4 - Décomposition BCNF, SPI
Définition
Un attribut appartenant à une clé minimale est appelé premier
ou primordial (prime).
NB "attribut clé" ou "attribut non clé" n'est pas défini : ambiguïté entre égalité et appartenance.
Définitions
Soit R relation munie d'un ensemble de DF minimal F.
Si B n'est pas primordial et Y pas clé de R, alors Y→ B est
* partielle si Y ⊂ K une clé minimale de R
* transitive si Y ⊄ K une clé minimale de R
Une dépendance Y→ B est triviale si B ∈ Y ;
NB. F minimal n'a pas de dépendance triviale
83
Boyce Codd Normal Form, BCNF
3ème Forme Normale
Définitions
Soit un schéma de relation R et une famille de dépendances
fonctionnelles F, minimale.
1) R est en BCNF ssi ∀ X→A ∈ F (donc non triviale, A ∉ X),
X est clé de R.
2) R est en 3NF ssi ∀ X→A ∈ F (donc non triviale, A ∉ X),
X est clé de R ou A est primordial (= premier).
A démontrer à partir de la définition 6 page suivante.
85
Décomposition BCNF, SPI
86
Décomposition 3NF, SPI, SPD
Algorithme :
Algorithme : itératif
R(Z) et F ensemble de dépendances fonctionnelles minimal.
Soit X→A une DF empêchant R d'être BCNF
R est décomposée SPI en R1 = XA et R2 ( Z - A )
Les dépendances sont projetées sur les Ri, i= 1,2
Si Ri n'est pas BCNF elle est à son tour décomposée
Remarques
1 : Toute relation à 2 attributs est BCNF (à démontrer)
2 : L'algorithme est exponentiel mais converge
3 : Il peut perdre des dépendances
88
1 - Boyce Codd Normal Form, BCNF
3 - 1ère et 2ème Formes Normales : 1NF et 2NF,NF2
5 - Décomposition 3NF, SPI, SPD
82
Dépendances partielles, transitives
Chapitre VI
Formes Normales
R et F minimal
1) A chaque DF de F de la forme Xi → Aj , i=1..n, on associe
une relation Ri(XiAj)
2) Les relations dont le schéma est inclus dans celui d'une
autre relation sont éliminées. NB projeter la DF associée.
3) Si aucune relation ne contient une clé minimale K de R on
crée une relation Rn+1(K)
Piste de démonstration :
* SPD par construction
* SPI : Algo. de Chase, le nuplet cherché apparaît à la ligne de Rn+1, clé K
* Les relations où ne se projette qu'une DF de R sont BCNF (donc 3NF)
* Les relations sur lesquelles sont projetées plusieurs DF de R (cas 2) sont
en 3NF (à démontrer)
* La relation créée par 3) est BCNF (donc 3NF) car pas de DF associée.
89
84
1ère et 2ème Formes Normales
Définition 4 :
Une relation en 1NF est ce qui est appelé "relation" depuis le
début du cours
Le domaine de chaque attribut est composé de valeurs
atomiques et la valeur d'un attribut est atomique
=> pas de liste de valeurs, de nuplet comme date ou adresse
Définition 5 :
Une relation est 2NF si elle est 1NF et n'a pas de dépendance
partielle.
Définition 6 :
Une relation est 3NF si elle est 2NF et n'a pas de dépendance
transitive
87