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