Unité IN3R22 Introduction aux bases de - [Site de Bertrand …bliaudet.free.fr/IMG/pdf/ESIEE-IN3R22-Cours_SGBD-02.… · · 2010-05-19Table, tuple, attribut, clé primaire 8 Schéma

ESIEE – IN3R22 - BASE DE DONNÉES - page 1/67 - Bertrand LIAUDET

Ecole Supérieure d’Ingénieurs en Electronique et Electrotechnique

Cité Descartes – BP 99

93162 NOISY-LE-GRAND CEDEX

Tél : 01 45 92 65 00

Fax : 01 45 92 66 99

www.esiee.fr

Unité IN3R22

Introduction aux bases de

données relationnelles

2ème partie : modélisation relationnelle

Support de cours

B. LIAUDET

Mai 2010


BASES DE DONNEES 2 : Modélisation

IN3R22 – ESIEE

Bertrand LIAUDET

SOMMAIRE

SOMMAIRE 2

MODELE RELATIONNEL BRUT 6

1. Les 3 objectifs majeurs d’une BD et d’un SGBD 6 L’intégrité des données : altération et incohérence 6

La distinction entre données et traitements 6

Performance et optimisation 7

2. La modélisation 7

3. Le modèle relationnel 8 Présentation 8

Table, tuple, attribut, clé primaire 8

Schéma de la BD 9

Définition de la BD 10

4. Notion de clé étrangère 11 Clé étrangère 11

Clé étrangère réflexive 12

5. Clé primaire concaténée : une difficulté du modèle relationnel 14 Exemple traité 14

Modèle relationnel 14

Une difficulté du modèle relationnel : quand on a plusieurs clés étrangères dans une table 14

Schéma de la BD 15

Formalisme 15

Distinction entre « table-nom » » et « table-verbe » 15

Graphe des tables 16

Intérêt de la clé primaire concaténée 16

Clé primaire concaténée ou clé secondaire concaténée ? 16

Syntaxe SQL 17

6. Clé étrangère concaténée 18 Principe 18


Exemple 18

Modèle relationnel 18

Syntaxe SQL 19

7. Attributs calculés 20 Principe 20

Exemple traité 20

Solutions 20

Conséquences du choix d’un attribut calculé : les risques d’incohérence, gestion de trigger 20

8. Ontologie relationnelle : tous les cas de clé primaire 22 1 : Clé primaire simple : les « tables d’objets » et les « tables de types » 22

2 : Clé primaire simple et étrangère : les « tables-espèces » et les « tables de compléments » 23

3 : Clé primaire concaténée avec un identifiant relatif : les « tables de composants » 24

4 : Clé primaire concaténée avec une date : les « tables d’historiques » 25

5 : Clé primaire concaténée avec uniquement des clés étrangères : les « tables de liaisons » 26

6 : Table complexe 27

7 : Synthèse 28

9. Évolutions d’un modèle 30 Présentation 30

Évolution par gestion de l’historique d’un attribut 30

Evolution par passage d’un attribut monovalué à un attribut multivalué 30

Evolution par transformation d’un attribut en type 31

10. Méthode : comment fabriquer un schéma de BD ? 32 Analyse du problème formulé 32

Les « tables-noms » 32

Clés étrangères et « tables-verbes » 33

Tables espèce et table d’historique unique 33

Table de composition 33

Le secret de la modélisation : être concret !!! 33

MODELE RELATIONNEL VALORISE 34

1. Contraintes d’intégrité des données 34 Présentation 34

Les 12 contraintes 34

Les contraintes d’intégrité référentielle 37

2. Contraintes et cycle de vie des tuples 39 Présentation 39

Présentation 39

Cycle de vie et analyse fonctionnelle 39

Exemples 40

3. Dictionnaire des attributs 41 Présentation 41


Exemple 1 41

Exemple 2 42

4. Jeu de tests 43 Présentation 43

Astuces pour bien construire son jeu de tests 43

MODELE RELATIONNEL NORMALISE 44

Notion de dépendance fonctionnelle : DF 44 Définition des DF 44

DF et modèle relationnel 44

Propriétés de base des DF 45

Théorème de décomposition (de Casey et Delobel) 45

Clés candidates et clés secondaires 46

Graphe des dépendances fonctionnelles : GDF 47 Présentation 47

Exemples 47

Sémantique du graphe du GDF 50

Les 3 premières formes normales de CODD : 1FN, 2FN, 3FN 51 Principe de la normalisation 51

Deux caractères remarquables des dépendances fonctionnelles 51

1ère forme normale : 1FN : la clé 51

2ème forme normale : 2FN : toute la clé 52

3ème forme normale : 3FN : rien que la clé 53

Formes normales 4 et 5 54 4ème forme normale , dite de BOYCE-CODD : BCNF 54

5ème forme normale : par réduction de la clé 56

Normalisations sémantiques 57 6ème normalisation : transitivité 57

7ème normalisation : attributs calculés 58

8ème normalisation : conflit de clé primaire – clé secondaire 58

9ème normalisation : dépendance fonctionnelle entre espèce et genre 59

TD 60

1 Exercices de modélisation relationnelle 60 1 Association et donateur 60

2 Les employés et leurs carrières 61

3 La cinémathèque 62

4 Les chantiers d’été 63

5. Gestion de projet 64

2. Exercices de normalisation 65 1. Normalisations formelles : théorème de décomposition 65

2. Normalisations formelles : formes normales 65

3. Les personnes et leurs téléphones 65


4. La table des ventes de voitures d’occasion 66

5. La table des commandes 66

6. Les avions 67

7. L’éditeur 67


MODELE RELATIONNEL BRUT

PRINCIPALES NOTIONS Modèle relationnel Clé primaire Table Clé significative Tuple Schéma de la BD Attribut NULL Clé étrangère Clé étrangère réflexive Clé primaire concaténée « table-nom » « table-verbe » Table d’objets Table de types Table-espèce Table de compléments Table de composants Table d’historiques Table de liaisons Table complexe

1. Les 3 objectifs majeurs d’une BD et d’un SGBD

L’intégrité des données : altération et incohérence

Garantir l’intégrité des données, c’est éviter l’altération et l’incohérence des données.

L’altération des données

Il y a plusieurs sources d’altération possibles : l’usure, les pannes, les erreurs, les malveillances. Une BD (et un SGBD) aura comme objectif d’en limiter la possibilité. Ce problème relève de la modélisation et de problèmes de sécurité.

L’incohérence des données

Une données est incohérente si elle est contradictoire avec une autre donnée. Ce problème relève de la modélisation.

Il y a deux grand types d’incohérence :

• La duplication des données avec des valeurs différentes. Exemple : deux adresses différentes pour une même personne.

• Les valeurs aberrantes. Exemples : un âge négatif ou supérieur à 150 ; une donnée faisant référence à une autre donnée qui n’existe pas.

La BD a pour objectif d’être un réservoir d’informations canonique (unique et commun), garantie sans incohérences (donc sans duplication de données).

La distinction entre données et traitements

Les données existent indépendamment des traitements qu’on leur applique.

La BD permet d’apporter une vision unifiée des données manipulées (dans une entreprise ou n’importe quel système d’informations, scientifique par exemple), indépendamment des traitements qui leur sont appliqués.


Cette vision unifiée permet une meilleure compréhension de la réalité représentée par les données.

Elle permet aussi de rationaliser et donc de faciliter les traitements appliqués aux données.

Performance et optimisation

Une BD doit fournir des performances acceptables par l’utilisateur. Ce problème relève de l’indexation, de l’optimisation des requêtes et de la modélisation.

2. La modélisation

La modélisation est l’activité qui consiste à produire un modèle.

Un modèle est ce qui sert ou doit servir d’objet d’imitation pour faire ou reproduire quelque chose.

On s’intéresse ici à la modélisation des données.

Un modèle des données est une représentation de l’ensemble des données. Cette représentation prend en compte un outil de représentation (un langage) et un niveau de précision (des contraintes méthodologiques).

Il existe plusieurs modèles de représentation des données : hiérarchique, relationnel, entité-association, objet, ensembliste, etc.

Les deux modèles dominants actuellement sont : le modèle relationnel : MR (qui correspond aux SGBD-R) et le modèle entité-association : MEA (qui est indépendant du type de SGBD utilisé). Ces deux modèles correspondent à 2 langages différents.

Les diagrammes de classe UML peuvent être utilisés comme langage permettant de réaliser des MR ou des MEA.

La méthode MERISE, utilisée quasi-exclusivement en France, distingue entre 3 types de modèles selon des critères méthodologique : le modèle conceptuel des données : MCD, le modèle logique des données : MLD et le modèle physique des données : MPD. L’usage tend à rendre équivalents MCD et MEA , MLD et MR , MPD et SQL.

La « jungle » des modèles !

Méthode MCD MLD MPD

Langage MEA, schema E-R, UML MR SQL

Type de langage Algo client Algo informaticien Code

Niveau MOA : maîtrise d’ouvrage MOE : maîtrise d’œuvre


3. Le modèle relationnel

Présentation

Le modèle relationnel a été inventé par Codd à IBM-San Jose en 1970.

C’est un modèle mathématique rigoureux basé sur un concept simple : celui de relation (ou table, ou tableau).

Ce modèle, c’est celui qui est implanté dans les SGBR-R.

Il permet à la fois de fabriquer la BD et de l’interroger.

Table, tuple, attribut, clé primaire

Exemple traité

Un service de ressources humaines dans une entreprise veut gérer le personnel. Dans un premier temps, on veut pouvoir connaître le nom, la fonction, la date d’entrée, le salaire et la commission (part de salaire variable) de chaque employé.

Chaque employé a donc les caractéristiques suivantes :

Nom, fonction, date d’entrée, salaire, commission

Table, tuples et attributs

Pour ranger ces données, on peut faire un tableau à 5 colonnes :

RELATION 5 attributs :

Employé Nom Fonction Date d’entrée Salaire Commission

TURNER SALESMAN 8-SEP-81 1500 0

JAMES CLERK 3-DEC-81 950 NULL

4 tuples : WARD SALESMAN 22-FEB-81 1250 500

TURNER ANALYST 3-DEC-81 3000 NULL

Vocabulaire

Relation = tableau = table = classe = ensemble = collection

Tuple = ligne du tableau = élément = enregistrement = individu = objet = donnée

Attribut = colonne du tableau = caractéristique = propriété = champ

BD = toutes les lignes de toutes les tables

NULL

NULL est la seule information codée qu’on rentre dans une table : elle signifie « non renseignée ». La valeur « 0 », par contre, ne signifie pas du tout « non renseignée », mais bien « valeur = 0 », comme on dirait « valeur = 500 ».

En règle générale, il faut limiter les valeurs NULL.

A noter que MySQL, pour gagner facilement en performance, préconise d’éviter les valeurs NULL et de mettre ‘0’ à la place.


Clé primaire

On souhaite pouvoir distinguer facilement chaque ligne d’une autre ligne. Or, certains employés ont le même nom.

Pour distinguer chaque ligne, on introduit la notion de clé primaire.

La clé primaire est un attribut qui détermine tous les autres.

Une clé primaire est toujours renseignée.

Exemple type de clé primaire : le numéro de sécurité sociale dans un tableau de personne. Quand on connaît le numéro de sécurité sociale, on sait de qui on parle, donc tous les attributs sont déterminés (même si on ne connaît pas leur valeur à un instant donné).

Dans le tableau des employés, la clé primaire pourrait être un numéro de référence choisi par l’entreprise. On le nomme NE (pour Numéro d’Employe).


Employés NE Nom Fonction Date d’entrée

Salaire Commission

1 TURNER SALESMAN 8-SEP-81 3000 0

2 JAMES CLERK 3-DEC-81 1800 NULL

3 WARD SALESMAN 22-FEB-81 2500 500

4 tuples : 4 TURNER ANALYST 3-DEC-81 5000 NULL

Clé secondaire

Une clé secondaire est un attribut qui pourrait être clé primaire, mais qui ne l’est pas.

Par exemple, dans le tableau des employés, on pourrait avoir le numéro de sécurité social. Cet attribut détermine tous les autres. Si on garde le numéro d’employé comme clé primaire, le numéro de sécurité sociale est alors clé secondaire.

En l’occurrence, on a tout intérêt à ne pas faire du numéro de sécurité sociale la clé primaire car on peut imaginer que l’employé existe sans que cette information soit renseignée.

Une clé secondaire peut ne pas être renseignée.

Clé significative

La clé significative, c’est l’attribut qui sert de clé dans le langage ordinaire. Dans le cas des employés, c’est leur nom. Toutefois, il peut y avoir des homonymes : la clé significative est utile dans le langage ordinaire pour savoir de qui on parle, mais elle est insuffisante dans le langage mathématique pour garantir l’identification de l’individu.

Schéma de la BD

Schéma des tables et schéma de la BD

Le schéma d’une table consiste a écrire la table sur une ligne avec les noms de code des attributs:

EMPLOYES (NE, (numSécu), nom, fonction, dateEmb, sal, comm)

L’ensemble des schémas des tables forme le schéma de la BD.


Formalisme

La clé primaire est notée en premier et est soulignée.

Les clés secondaires sont notées après la clé primaire et mises entre parenthèses.

La table des employés représente une réalité physique. On l’appelle « table-nom ».

Le nom donné à une « table-nom » est un nom commun, au pluriel.

La clé primaire d’une « table-nom » est N (pour numéro) suivi de la première lettre du nom de la table.

Définition de la BD

Une BD c’est un ensemble de tables avec leurs tuples.

Un SGBD gère plusieurs BD distinctes.


4. Notion de clé étrangère

Clé étrangère

Présentation

Au problème précédent de gestion des ressources humaines, on ajoute les spécifications suivantes :

Le service du personnel souhaite aussi connaître le nom du département dans lequel l’employé travaille. L’entreprise est répartie dans plusieurs villes. Les départements sont donc caractérisés par leur nom et par leur ville. Un employé travaille dans un département et un seul. Il peut y avoir plusieurs départements qui ont le même nom.

On va dont ajouter une table : la table des départements :


Départements ND Nom Ville

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

4 tuples : 40 OPERATIONS BOSTON

• ND, le numéro de département, était déjà dans la table des employés.

• Désormais, le numéro de département ND de la table des employés fait référence au numéro de département de la table des départements.

• Dans la table des départements, ND est clé primaire.

• Dans la table des employés, ND est clé étrangère.


Employés NE Nom Fonction Date d’entrée

Salaire Comm. # ND

1 TURNER SALESMAN 8-SEP-81 3000 0 10

2 JAMES CLERK 3-DEC-81 1800 NULL 30

3 WARD SALESMAN 22-FEB-81 2500 500 20

4 tuples : 4 TURNER ANALYST 3-DEC-81 5000 NULL 10

Définition

Une clé étrangère est un attribut qui fait référence à une clé primaire.


Schéma de la BD

Le schéma de la BD consiste à écrire chaque table sur une ligne avec les noms de code des attributs :

EMP (NE, nom, job, datemb, sal, comm., #ND)

DEPARTEMENTS (ND, nom, ville)

Formalisme

1. Les clés primaires sont soulignées et placées en premier dans la liste des attributs.

Le nom de la clé primaire est constitué de : « N »+1ère lettre de la table (NE).

Les clés étrangères sont précédés d’un #.

Les clés étrangères sont mis en dernier dans la liste des attributs.

Graphe des tables

Le graphe des tables montre les tables et le lien entre les tables :

Emp

Dept

On peut aussi présenter le nom de l’attribut commun :

Emp

ND

Dept

Clé étrangère réflexive

Présentation

Au problème précédent de gestion des ressources humaines, on ajoute les spécifications suivantes :

Chaque membre du personnel a un supérieur hiérarchique et un seul lui-même membre du personnel, sauf le président qui n’a pas de supérieur hiérarchique.

La table résultat ressemblera à celle-ci

RELATION 8 attributs:

Employés NE Nom Fonction Date d’entrée Salaire Comm. # ND *NEchef

1 TURNER SALESMAN 8-SEP-81 3000 0 10 5

2 JAMES CLERK 3-DEC-81 1800 NULL 30 1

3 WARD SALESMAN 22-FEB-81 2500 500 20 4

5 tuples : 4 TURNER ANALYST 3-DEC-81 5000 NULL 10 5

5 BOSS PRESIDENT 10-DEC-80 7000 NULL 5 NULL


Définition

Une clé étrangère réflexive est un attribut qui fait référence la clé primaire de sa table.

Schéma de la BD

Le schéma de la BD consiste à écrire chaque table sur une ligne avec les noms de code des attributs :

EMPLOYES (NE, nom, job, datemb, sal, comm., #ND, *NEchef)


Formalisme

1. Les clés primaires sont soulignées et placées en premier dans la liste des attributs.

2. Le nom de la clé primaire est constitué de : « N »+1ère lettre de la table (NE).

3. Les clés étrangères sont précédés d’un #.

4. Les clés étrangères sont mis en dernier dans la liste des attributs.

5. Les clés étrangères réflexives sont précédés d’un *.

6. Les clés étrangères réflexives sont mises après les clés étrangères non-réflexives.

7. Le nom des clés étrangères réflexives est constitué de : clé primaire + code de l’attribut (NEchef)

Graphe des tables

Le graphe des tables montre les tables et le lien entre les tables :

EMPLOYES NEchef

DEPARTEMENTS

On précise le nom de l’attribut clé étrangère réflexive.

On peut aussi dupliquer la tables des EMPMOYES :

EMPLOYES

NEchef

DEPARTEMENTS EMPLOYES


5. Clé primaire concaténée : une difficulté du modè le relationnel

Exemple traité

Une bibliothèque gère les emprunts des livres de ses adhérents. Les livres ont un titre et un auteur. Les exemplaires physiques des livres ont un numéro différent par exemplaire. Ils correspondent à un livre et ont un éditeur. Les adhérents ont un nom, un prénom, une adresse et un téléphone. On souhaite archiver tous les emprunts. Un livre ne peut pas être rendu le jour même de son emprunt. La durée maximum d'emprunt est de 14 jours.

La bibliothèque souhaite pouvoir connaître à tout moment la situation de chaque abonné (nombre de livres empruntés, retards éventuels). Elle souhaite aussi pouvoir faire des statistiques sur la pratique des clients (nombre de livres empruntés par an, répartition des emprunts par genre, nombre d’emprunts par livre, etc.

Modèle relationnel

Tables des Adhérents et des Oeuvres

De l’analyse du texte précédent, on extrait aisément la table des adhérents et celle des oeuvres :

ADHERENTS (NA, nom, prenom, adr, tel)

OEUVRES (NO, titre, auteur)

Table des Livres

On arrive aussi à extraire assez facilement la table des livres physiques :

LIVRES (NL, editeur, #NO) � Remarque

La table des livres physique est nommée : « Livres », ce qui facilitera l’interprétation des questions (un adhérent vient rendre un livre, quels sont les livres en retard, etc.).

Tables des Emprunts

On peut envisager une table des emprunts. La table des emprunts est une liste de livres par adhérent, mais aussi une liste d’adhérents par livre.

Les attributs de cette table sont les suivants :

EMPRUNTER (#NA, #NL, datemp, datretmax, datret)

Quelle est la clé primaire de cette table ?

On pourrait penser créer un attribut : « NEMP » et en faire la clé primaire.

Une difficulté du modèle relationnel : quand on a plusieurs clés étrangères dans une table

Règle fondamentale de modélisation relationnelle :

Quand on a plus d’une clé étrangère dans une table, il faut se demander si la concaténation de plusieurs attributs de la table n’est pas clé primaire de la table.


Méthode pour déterminer la clé primaire quand on a plusieurs clés étrangères

La méthode de recherche de la clé primaire sera la suivante :

1) Se demander si la concaténation des clés étrangères ne forme pas la clé primaire.

2) Si ç’est le cas, se demander si on ne peut pas retirer quelques clés étrangères de la concaténation.

3) Si ce n’était pas le cas, essayer d’ajouter des attributs non clé étrangère pour trouver la clé primaire.

4) Une fois trouvé, essayer de supprimer des attributs clés étrangères de la nouvelle clé primaire concaténée.

Application

1ère hypothèse : EMPRUNTER (#NA, #NL, datemp, dureeMax, datret)

Est-ce que NA et NL forment bien la clé primaire ? Non : un adhérent peut emprunter plusieurs fois le même livre à des dates différentes.

2ème hypothèse : on ajoute datemp : EMPRUNTER (#NA, #NL, datemp, dureeMax, datret)

Le tripmet (NA, NL, datemp) est clé primaire

3ème hypothèse : on supprime NL : EMPRUNTER(#NA, #NL, datemp, dureeMax, datret)

Le couple (NA, datemp) n’est pas clé primaire.

4ème hypothèse : on supprime NA : EMPRUNTER(#NA, #NL, datemp, dureeMax, datret)

Le couple (NL, datemp) est clé primaire.

Conclusion : EMPRUNTER(#NL, datemp, dureeMax, datret, #NA)

Schéma de la BD

ADHERENTS (NA, nom, prenom, adr, tel)


LIVRES (NL, editeur, #NO)

EMPRUNTER(#NL, datemp, dureeMax, datret, #NA)

Formalisme � Les clés primaires sont soulignées et placées en premier dans la liste des attributs. � Le nom d’une clé primaire simple est constitué de : « N »+1ère lettre de la table (NA). � Dans une clé primaire concaténée, les attributs clés étrangères sont placés en premier. � Les clés étrangères sont précédées d’un #. � Les clés étrangères sont mises en dernier dans la liste des attributs.

Distinction entre « table-nom » » et « table-verbe »

On a donc deux grands types de tables : les « tables-noms » et les « tables-verbes ».

Les « tables-noms »

En général, les « tables-noms » représentent une réalité matérielle : les adhérents, les livres. Les oeuvres sont aussi une « table-nom ».

Elles ont une clé primaire simple.


Les « tables-verbes »

En général, les « tables-verbes » représentent une relation, un lien entre deux « tables-noms ».

Formalisme

Le nom des « tables-noms » est un nom commun au pluriel : les Adhérents.

Le nom des « tables-verbes » est un verbe à l’infinitif : Emprunter. Ce verbe désigne la relation que la « table-verbe » établit entre les deux « tables-noms » : les adhérents empruntent des livres.

Graphe des tables

EMPRUNTER

LIVRES ADHERENTS

OEUVRES

Intérêt de la clé primaire concaténée

Pourquoi n’a-t-on pas utilisé un attribut NE (numéro d’emprunt) comme clé primaire ?

Pour 3 raisons :

• En déclarant (NL, datemp) comme clé primaire, on garantit l’unicité du couple NL, datemb, ce qui garantit la cohérence sémantique des données.

• On évite de créer un attribut inutile.

• On met au jour le fait qu’un emprunt est défini par le couple (NL, datemb). Cela permet de mieux comprendre les données.

Clé primaire concaténée ou clé secondaire concaténée ?

Principe : toujours éviter, si possible, la clé secondaire concaténée !!!

Si le SGBD permet la création de clé primaire concaténée et de clé étrangère faisant référence à une clé primaire concaténée, alors on évitera toujours la clé secondaire concaténée.

Formalisme des clés secondaires

EMPRUNTER(NE, (#NL, datemp), datretmax, datret, #NA)

On met la clé secondaire juste après la clé primaire, entre parenthèses pour la repérer.

Conséquence syntaxique

UNIQUE (NL, datemp)


Syntaxe SQL

Clé primaire concaténée

CREATE TABLE emprunter ( NL integer not null, foreign key(NL) references livres(NL), datEmp date not null, … primary key (NL, datEmp) );


6. Clé étrangère concaténée

Principe

Une clé étrangère fait référence à une clé primaire.

Une clé étrangère qui fait référence à une clé primaire concaténée sera elle aussi concaténée.

Exemple

Quand les adhérents sont en retard, la bibliothèque envoie des courriers de rappel aux adhérents. On considère, ce qui n’est pas très réaliste !, que la bibliothèque envoie un courrier par emprunt en retard. Par exemple, un adhérent a emprunter 3 livres (A, B, C), en a rendu 1 (C), en a réemprunté 1 (D). Le temps passe. Les livres A et B sont en retard. Le livre C est rentré. Le livre D est emprunté et pas en retard. La bibliothèque envoie deux courriers : un pour le livre A et un pour le livre B.

Modèle relationnel

Au modèle précédent, on ajoute :

COURRIERS (NC, texte, date, # (NL, datemp) )

Le courriers fait référence à un emprunt (NL, datemp) qui lui-même fera référence à un adhérent.

Formalisme

Les attributs de la clé étrangère concaténée sont mis entre parenthèses et un « # » est mis devant les parenthèses. A noter qu’il n’y a plus de « # » devant « NL ». En effet, la clé étrangère concaténée fait référence à une clé primaire concaténée et c’est ensuite la clé primaire concaténée qui fait référence à des clés primaires simples.

Graphe des tables

COURRIERS

EMPRUNTER

LIVRES ADHERENTS

OEUVRES


Syntaxe SQL

Clé étrangère concaténée

Une clé primaire concaténée peut aussi devenir clé étrangère dans une autre table : CREATE TABLE test ( NL integer not null, datEmp date not null, foreign key(NL, datemp) references emprunter(NL, datemp) ) ;


7. Attributs calculés

Principe

Un attribut calculé est un attribut dont la valeur se déduit de l’état de la BD.

Le principe général est qu’il faut éviter les attributs calculés pour éviter les incohérences dans la BD.

Exemple traité

On reprend la bibliothèque et on ajoute qu’on veut savoir à tout moment le nombre de livres actuellement empruntés par un adhérent.

Solutions

Requête

La demande précédente peut être traitée par une requête :

Select count(*) from emprunter

Where na = notreAdhérent

And dateRet is null;

Cette requête permet de répondre à la question sans avoir à créer un nouvel attribut. C’est la meilleure solution dans un premier temps.

Attribut calculé

On peut être tenté d’ajouter l’attribut « nbEmprunts » dans la table des adhérents :

ADHERENTS (NA, nom, prenom, adr, tel, nbEmprunts)



EMPRUNTER(#NL, datEmp, dureeMax, datRet, #NA)

Conséquences du choix d’un attribut calculé : les risques d’incohérence, gestion de trigger

Risque d’incohérence

L’adhérent 32 vient emprunter le livre 10. Mettre à jour la BD :

Insert into emprunter values (10, current_date( ), 14, NULL, 32); Update adherents Set nbEmprunts = nbEmprunts +1 Where NA = 32;


En plus de la mise à jour de la table emprunter, il faut mettre à jour la table adhérents. Si one ne fait pas la mise à jour dans la table adhérents, les données seront incohérentes : la requête vue précédemment ne correspondra pas à la valeur de l’attribut nbEmprunts.

Gestion d’un trigger

Pour éviter le risque d’incohérence, on va gérer un trigger : ma mise à jour de l’attribut calculé sera automatique et pas manuel.

Les triggers sont des scripts déclenchés automatiquement à l’occasion d’une instruction du DML (insert, update ou delete).

Dans notre exemple, il faut mettre à jour l’attribut « nbEmprunts » de la table « Adhérents » à l’occasion d’un insert ou d’un update dans la table « Emprunter ».

Un attribut calculé doit toujours être associé à un système de trigger qui gère ses mises à jour automatiquement. � Exemple de code MySQL

drop trigger if exists tai_emprunter; # tai : trigger after insert delimiter // create trigger tai_emprunter after insert on emprunter for each row begin update adherents set nbEmprunts = nbEmprunts + 1 where NA = new.NA; end; // delimiter ;


8. Ontologie relationnelle : tous les cas de clé pr imaire

1 : Clé primaire simple : les « tables d’objets » et les « tables de types »

Exemples

1 : Les employés et les départements.

2 : Les livres de la bibliothèque.

3 : Les avions et leurs types

Solutions

Employés (NE, nom, fonction, salaire, #ND)

Départements (ND, nom, ville)

Livres (NL, éditeur, dateAchat, #NO)

Oeuvres (NO, titre, auteur, dateCréation)

Avions (NA, année, couleur, propriétaire, #typeAvion)

TypeAvion(typeAvion, nombre places, année, moteur)

Principe de la distinction entre « table d’objets » et « table de types »

En général, une table avec une clé primaire simple correspond à une réalité physique : les employés, les départements, les exemplaires physiques des livres. Ce sont les « table d’objets ». On peut aussi les considérer comme des tables d’instance (en référence à la notion d’instanciation de la programmation objet).

Une table avec une clé primaire simple peut aussi correspondre à des types de la réalité physique : c’est le cas des « TypeAvion », par exemple, le A320, ou des « œuvres » qui peuvent être considérées comme un type de « livres », le livre comme l’avion étant les exemplaires physiques (tous les « Voyage au bout de la nuit » de « Louis Ferdinand CELINE »). Ce sont les « tables de types »


2 : Clé primaire simple et étrangère : les « tables-espèces » et les « tables de compléments »

Exemple 1

On gère des personnes. Certaines sont étudiantes et suivent des études : année, domaine, spécialisation. D’autres sont salariés et ont une fonction, un salaire et une date d’embauche.

Solution 1

Personnes (NP, nom, prénom, adresse, téléphone)

Etudiants (#NP, domaine, spécialisation, année)

Salariés (#NP, fonction, salaire, datemb)

Exemple 2

Une galerie d’art vend des tableaux qui ont un titre, une année de création, une technique, un format, un prix et un auteur. Les tableaux sont des pièces uniques. Ils sont vendus une seule fois à un des clients. On enregistre aussi le prix et la date de la vente. Les clients ont un nom et une adresse

Solution 2

Tableaux (NT, titre, année, technique, format, prix, auteur)

Ventes (#NT, prix, date, #NC)

Clients (NC, nom, adresse)

Principe

Une table avec une clé primaire simple et clé étrangère en même temps peut être :

Soit une « table-espèce » (les étudiants) : elle correspond à une spécialisation d’une autre table (les personnes qui correspondent alors au genre de l’espèce des étudiants). La clé primaire de la « table-espèce » est constituée par celle de la table qu’elle spécialise et est donc clé étrangère en même temps. Un étudiant est caractérisé par les données de son tuple dans la « table-espèce » et les données du tuple référencé dans la table orrespondant à son genre, les personnes ici.

On dit que chaque tuple de la « table-espèce » « hérite » des attributs du tuple de la « table-genre » auquel il fait référence.

Soit une « table de compléments » : elle ajoute des informations à sa table d’origine : ici, la vente vient compléter les informations du tableau. La vente est un historique du tableau : la table « Ventes » contient une date. Mais la date ne participe pas à la clé primaire car un tableau n’est vendu qu’une seule fois.

L’intérêt de ne pas fusionner les « tables de compléments » avec leurs table d’origine, c’est de garantir la cohérence des données : en effet dans l’exemple proposé, on peut considérer que tous les attributs de la vente sont obligatoires, ce qui ne serait pas gérable en fusionnant la table des ventes et la table des tableaux (on pourrait avoir le NC renseigné mais pas de prix et pas de date). La table de compléments peut donc être considérée comme une « table des attributs facultatifs (pas obligatoires) liés entre eux ». Dans l’exemple, le prix, la date et le numéro de clients ne sont pas obligatoires pour une oeuvre (ils ne seront renseignés qu’à l’occasion de la vente), mais sont liés entre eux : si on en renseigne un, il faut renseigner les autres.


3 : Clé primaire concaténée avec un identifiant relatif : les « tables de composants »

Exemple

On gère des projets qui ont un nom, une date de début, une date de fin et un budget. Les projets sont composés d’étapes en nombres variables. Une étape est définie par son numéro d’ordre dans le projet (de 1 à N), par une date de début et une date de fin, un nom d’étape et un budget d’étape.

Solution

Projets (NP, nom, début, fin, budget)

Etapes (#NP, NE, nom, début, fin, budget)

Principe

Le numéro d’étape est un identifiant relatif : de 1 à N. Il y a plusieurs étapes qui ont le même numéro d’étape. C’est le couple «NP, NE » qui est unique.

L’étape est un composant du projet : elle disparaît nécessairement avec le projet (elle n’a pas d’existence indépendamment du projet).

La table « Etapes » est une « table de composants ». La suppression d’un tuple dans la « table-composé » correspondante (les projets ici) implique nécessairement la suppression des tuples de la « table de composants ».

A noter que l’identifiant relatif peut être un numéro ou n’importe quelle information.


4 : Clé primaire concaténée avec une date : les « tables d’historiques »

Exemple

1 : les emprunts à la bibliothèque.

2 : l’historique des adresses des adhérents de la bibliothèque

Solution

Livres (NL, éditeur, dateAchat, #NO)

Oeuvres (NO, titre, auteur, dateCréation)

Adhérents (NA, nom)

Emprunter (#NL, datEmp, dureeMax, dateRet, #NA)

HistoAdressesAdherents (#NA, date, adresse)

Principe

Dès qu’une clé primaire contient une date, c’est un historique.

Dans le cas de l’adresse, on a sorti l’attribut adresse de la table « Adhérents ».

Remarque : historique et historique unique

L’exemple de l’historique unique concerne des tableaux qui sont vendus une seule fois :

Ventes (#NT, prix, date, #NC)

La table « Ventes » est ici une « table de compléments ».

Si les tableaux pouvaient être vendus plusieurs fois (imaginons une location pour une certaine durée), la table d’historique unique deviendrait une table d’historique :

Location (#NT, date, durée, prix, #NC)


5 : Clé primaire concaténée avec uniquement des clés étrangères : les « tables de liaisons »

Exemple 1

Un employé participe à plusieurs projets. Plusieurs employés participent à un projet. � Solution

Employés (NE, nom, dateEmbauche)

Projets (NP, intitulé, dateDébut, dateFin, budget)

Participer (#NE, #NP)

Principe

La table « Participer » est une « table de liaison » entre les employés et les projets.

La clé primaire comporte plusieurs attributs dont au moins une clé étrangère.

Il n’y a pas d’attribut « date » dans la clé primaire.

Il n’y a pas de relation de composition entre les attributs de la clé primaire.

Il peut y avoir des attributs en plus de la clé primaire.

Exemple 2

On ajoute à l’exemple 1 le fait que l’employé joue un rôle unique sur le projet. � Solution

Participer (#NE, #NP, rôle)

Exemple 3

On considère maintenant que l’employé peut jouer plusieurs rôles sur le projet et que pour chaque rôle de l’employé sur le projet on définit le nombre de jours d’activité. � Solution

Participer (#NE, #NP, rôle, nbJours)

Principe

Une table de liaison peut être constituée de plus de 2 attributs.

Les attributs d’une table de liaison ne sont pas forcément des clés primaires mais ils sont forcément des identifiants.

Dans l’exemple 3, l’attribut « rôle » jour le rôle d’un identifiant : il pourrait être clé primaire de la table des rôles dans laquelle on trouverait par exemple le coût journalier pour un rôle donné.


6 : Table complexe

Exemple 1

On gère des projets qui ont un nom, une date de début, une date de fin et un budget. Les projets sont composés d’étapes en nombres variables. Une étape est définie par son numéro d’ordre dans le projet (de 1 à N), par une date de début et une date de fin, un nom d’étape et un budget d’étape.

Le budget des étapes peut varier. On veut garder l’historique.

Solution 1

Projets (NP, nom, début, fin, budget)

Etapes (#NP, NE, nom, début, fin)

HistoBudgetEtapes (#(NP, NE), date, budget)

Principe 1

Les clés étrangères peuvent toujours faire référence à n’importe quel type de clé primaire. Elles peuvent donc toujours être concaténées.

Dans l’exemple traité, on un historique : c’est un historique de la table de composition.

La clé étrangère de la table d’historique fait référence à une clé primaire concaténée.

Exemple 2

On envoie des courriers en nombre à des clients. Un courrier est caractérisé par un libellé et une date. Un même courrier peut être envoyé plusieurs fois à la même personne. On veut savoir quel client à reçu quel courrier

Solution 2

Courriers (NCO, libellé, date)

Clients (NCL, nom, adresse)

Envoyer (#NCL, #NCO, date)

Principe 3

C’est le même principe qu’un historique simple. C’est un historique de table de liaison.

La clé étrangère de la table d’historique fait référence à une clé primaire concaténée : une table de liaison. On pourrait écrire :

HistoEnvoyer (#(NCL, NCO), date)

Envoyer (#NCL, #NCO)

Dans notre exemple, il est inutile de séparer les deux tables car la table envoyer ne contient pas d’attribut en dehors de la clé.

Exemple abstrait

On peut imaginer une table de liaison historique qui relie une table de liaison historique avec une table de composition, ce qui donnerait comme clé primaire :

#(CP, n°), #(CP1, CP2, date), date


7 : Synthèse

Ontologie relationnelle

On a initialement distingué entre 2 types de tables : celles à clé primaire simple (les « tables-noms ») et celles à clés primaires concaténées (les « tables-verbes »).

On distingue maintenant 5 types de clés primaires : CP, #CP, (#CP, date), (#CP, n°), (#CP1, #CP2).

Ces 5 types de clé primaire correspondent à la description de 7 types de réalité : c’est la sémantique des tables de l’ontologie relationnelle.

A cela s’ajoute la possibilité de former des tables complexes.

TYPOLOGIE

Type de clé primaire

TYPOLOGiE

Type de table

ONTOLOGIE RELATIONNELLE

Sémantique de la table

1 CP « table-nom » 1 : « table d’objets » ; 2 : « table de types »

2 #CP « table-nom » 3 : « table-espèce » ; 4 : « table de compléments »

3 #CP, n° « table-nom » 5 : « table de composants » (identifiant relatif)

4 #CP, date « table-nom » ou

« table-verbe »

6 : « table d’historiques »

5 #CP1, #CP2

ou #CP1, attribut

« table-verbe » 7 : « table de liaisons »

+1 complexe « table-nom » ou

« table-verbe »

+1 : table complexe de 3 à 7 � Remarque orthographique

Table-espèce : espèce au singulier car la table définit et contient une et une seule espèce.

Table-nom et table-verbe : nom et verbe au singulier car ce sont des néologismes.

Table d’objets, de types, de composants, de compléments, d’historiques, de liaisons : tous au pluriel car il y en a plusieurs par table à chaque fois. � Remarque 1

Les tables de liaison peuvent avoir plus de 2 clés étrangères. � Remarque 2

Dans une table de liaison, un attribut de la clé primaire peut, tout en étant un identifiant absolu (et non pas relatif), ne pas être clé étrangère. On peut donc avoir des tables de liaison de la forme : #CP1, attribut. � Remarque 3

Les tables d’historiques peuvent être des « tables-noms » ou des « tables-verbes ». L’historique d’un attribut est une « table-nom » : (#NEmployé, date, salaire). L’historique des emprunts de livres est une « table-verbe » : (#NLivre, dateEmp, dateRetour, #NAdhérent). C’est une « table-verbe » car elle relie les livres et les adhérents. � Remarque 4


Les tables complexes peuvent être des « tables-noms » dans le cas où on fait l’historique d’un attribut d’une « table-espèce » ou d’une « table de composants ». Par exemple, si on voulait conserver l’historique du budget prévisionnelle d’une étape d’un projet, on obtiendrait cette table : (#(NProjet, NEtape), date, budget). On a l’historique du budget d’une étape. La clé étrangère fait référence à une étape qui est un composant du projet.

Principe de la découverte de la totalité des clés primaires

A partir d’une clé primaire simple, on peut avoir 3 types de relations :

• Des relations d’héritage (inclusion d’ensemble).

• Des relations de composition

• Des relations de liaison simple

Ces trois types de relations sont celles qu’on retrouvera en UML dans les diagrammes de cas d’utilisation et dans les diagrammes de classes.

A cela s’ajoute dans tous les cas :

• la possibilité d’un historique.


9. Évolutions d’un modèle

Présentation

Un modèle relationnel modélise une situation donnée.

On peut envisager des modifications du modèle en fonction des évolutions prévisibles de la situation.

Ces évolutions concernent les attributs. On va transformer un attribut en table.

On peut concevoir 3 types d’évolution :

1. Gestion de l’historique d’un attribut : création d’une « table d’historique »

2. Passage d’un attribut monovalué à un attribut multivalué : création d’une « table de liaisons » ou d’une « table de composants »

3. Transformation d’un attribut en type : création d’une « table de types »

Il n’y a pas d’autres évolution car les autres types de tables (espèce, compléments, objets) ne sont pas concernées par cette évolution.

Du bon usage des évolutions dans le MR brut et valorisé

En première analyse, dans un MR brut et valorisé, il vaut mieux éviter la prise en compte des évolutions pour ne pas surcharger le modèle.

Ces évolutions pourront apparaître dans un second temps après justification.

Évolution par gestion de l’historique d’un attribut

Principe

Soit la table : (CP, ..., attribut)

On peut faire évoluer la table en gérant un historique de l’attribut. On obtient alors :

(CP, ...)

(#CP, date, attribut) : cette table est une « table d’historiques »

Exemple : l’historique des salaires des employés

Employés (NE, nom, job, salaire)

devient :

Emloyés (NE, nom, job)

HistoSalaire(#NE, date, sal)

Evolution par passage d’un attribut monovalué à un attribut multivalué

Principe



On peut faire évoluer la table en considérant que la CP ne détermine plus une seule valeur de l’attribut mais plusieurs :

(CP, ...)

(#CP, attribut ) : cette table est soit une « table de liaisons », soit une « table de composants »

Exemple 1 : les employés ont plusieurs jobs en même temps


devient :

Emloyés (NE, nom, salaire)

JobsEmployés(#NE, job )

Evolution par transformation d’un attribut en type

Principe


On peut faire évoluer la table en autonomisant l’attribut en en faisant une table :

(CP, ...,, #attribut)

(attribut ) : cette table est une « table de types ».

L’intérêt de cette transformation est de permettre de créer une liste de « attribut » qui soit indépendante de l’usage qu’on en fait dans une autre table.

A noter qu’il n’est pas nécessaire de créer un numéro comme clé primaire déterminant l’attribut.

Si toutefois on le faisait, l’attribut serait clé secondaire et on obtiendrait :

(CP, ...,, #NA)

(NA , (attribut) )

Exemple : les employés et leur job


devient :

Emloyés (NE, nom, salaire, #job)

Jobs( job )

L’intérêt de cette transformation est de permettre de créer une liste de « job » qui soit indépendante de l’usage qu’on en fait dans la table des employés. Ainsi certains « job » peuvent être définis dans la table des Jobs sans être utilisés dans la table des employés.


10. Méthode : comment fabriquer un schéma de BD ?

Analyse du problème formulé

On part d’une situation décrite par un texte. Le texte décrit les données du monde réel. Ces données peuvent aussi provenir d’interviews d’acteurs du système.

Dans le texte, on ne s’intéresse pas aux objectifs à atteindre, mais seulement aux informations qui sont manipulées.

Correspondance grammaticale

A chaque mot du texte, on va pouvoir faire correspondre une table ou un attribut, selon un principe grammatical : � Principes de correspondance :

Type grammatical Elément du MR

Nom « table-nom » ou attribut

Adjectif Attribut

Verbe reliant deux tables clé étrangère ou « table-verbe »

Attention, ces principes de correspondance ne sont pas formels : ils servent de point de départ à l’analyse.

Ontologie sémantique

Sémantiquement, on trouve 7 types de tables :

Des noms (1), des types (2), des espèces (3), des enfants uniques (4), des composants (5), des liaisons (6) et des historiques (7).

Chaque fois qu’on trouve une table, il faut se demander à quel type de table elle correspond et ensuite quelle est sa clé primaire.

Les « tables-noms »

Quelles tables concevoir en premier ?

Il faut commencer par identifier des « tables-noms » dont les tuples représentent une réalité physique concrète : donc les « tables d’objets ».

Par exemple : les employés sont des réalités physiques. On aura une table des employés.

On peut ensuite s’intéresser aux « tables de types ».Erreur ! Aucune entrée d'index n'a été trouvée.

Questions à se poser pour chaque table envisagée :

1. Quelle est la clé primaire ? S’il n’y a pas de clé primaire, ce n’est pas une table

2. Quels sont les attributs ? S’il n’y a pas d’attributs, ce n’est pas une table. Les attributs doivent correspondre à une description du monde réel. Il ne faut pas en inventer.


3. Puis-je imaginer plusieurs tuples (lignes) concrets dans la table ? S’il n’y a pas de tuples, ce n’est pas une table.

Quelques remarques sur les tables

1. Si une table contient un nombre petit et fixe de tuples, c’est que ce n’est pas une table, mais un ensemble de valeurs constantes.

2. Si deux tables contiennent les mêmes tuples, ou les mêmes attributs, ou la même clé primaire, c’est qu’elles sont identiques.

3. Si une table n’a que 2 attributs, dont la clé primaire, elle peut probablement être fusionnée dans une autre table qui pointe sur elle.

a. A(A, Ax, #B1) et B(B1, B2) devient : A(A, Ax, B2)

Clés étrangères et « tables-verbes »

Clés étrangères

Dès qu’on a deux tables, il faut se demander si la clé primaire de l’une ne pourrait pas être clé étrangère dans l’autre et réfléchir à la signification d’une telle relation.

Par exemple : avec Employés et Départements. On ne peut pas avoir de numéro d’employé dans la table des départements. Par contre, on peut avoir un numéro de département dans la table des employés : ce sera le département dans lequel l’employé travaille.

« Tables-verbes »

Dès qu’on a deux tables, il faut se demander si on ne peut pas créer une nouvelle table qui soit une liste qui regroupe les deux clés primaires de ces deux tables et réfléchir à la signification possible d’une telle table. Il faudra aussi se demander si on ne peut pas ajouter des attributs dans cette nouvelle table.

Par exemple : avec les Livres et les Abonnés. On peut concevoir une liste de couples (NL, NA). La signification correspond à la relation possible entre les deux attributs : un abonné emprunte un livre. C’est une table d’emprunts. On peut y ajouter la date d’emprunt comme attribut.

Tables espèce et table d’historique unique

Quand on a une première table qui fait référence via une clé étrangère à une seconde table traitant grosso modo de la même réalité, il faut se demander si la clé primaire de la première table ne peut pas être remplacée par la clé étrangère vers la seconde table.

Table de composition

Dès qu’une première contient une clé étrangère vers une deuxième table et que la première table est un composant de la seconde, alors il existe probablement un attribut identifiant relatif dans la première table.

Le secret de la modélisation : être concret !!!

Pour avoir une bonne modélisation, il faut concrétiser le modèle.

Autrement dit, il faut mettre des tuples dans les tables qu’on crée, pour vérifier la validité des tables.


MODELE RELATIONNEL VALORISE

1. Contraintes d’intégrité des données

Présentation

Dès que le nombre de données est important, il y a des risques d’incohérence, c’est-à-dire de contradiction :

• Duplication des données avec des valeurs différentes. Exemple : deux adresses différentes pour une même personne.

• Valeurs aberrantes. Exemples : un âge négatif ou supérieur à 150 ; une donnée faisant référence à une autre donnée qui n’existe plus.

La BD a pour objectif d’être un réservoir d’informations unique et commun : « canonique », garantie sans incohérence (donc sans duplication de données).

Les contraintes d’intégrité sont des caractéristiques d’un système d’information (d’une base de données) qui permettent de garantir l’intégrité des données.

Les 12 contraintes

Il y a 12 types de contraintes qu’on peut préciser.

1 : Nom de code

On a donné un nom à l’attribut. Il va falloir leur donner un nom de code qui sera utilisé dans la programmation. Dans une même table, deux attributs ne peuvent pas avoir le même nom. Par contre, deux attributs de deux tables différents peuvent avoir le même nom.

2 : Type

Entier, réel, booléen, date, caractère, chaîne de caractère, texte, image, etc.

3 : Clé primaire

Les attributs clés primaires sont soulignés. Un attribut clé primaire est telle que la valeur est toujours renseignée (elle est obligatoire) et qu’elle est différente pour tous les éléments de la table (elle est unique). � SQL : PRIMARY KEY

4 : Clé étrangère

Un attribut clé étrangère est un attribut qui fait référence à un attribut clé primaire. On précise le numéro de l’attribut auquel il fait référence.

Les clés étrangères donnent lieu à des contraintes particulières : les contraintes d’intégrité référentielle.

La contrainte d’intégrité référentielle consiste à vérifier que la clé primaire référencée existe, que ce soit au niveau de la table ou au niveau des tuples.


� SQL : FOREIGN KEY

5 : Obligatoire

Précise si l’attribut est obligatoire ou pas. On dit aussi « not NULL ». Un attribut obligatoire possède nécessairement une valeur. Il faut distinguer entre la valeur « 0 » : c’est une possibilité de valeur entière ou réelle parmi les autres, et la valeur NULL. La valeur NULL signifie que la valeur n’est pas renseignée. Si un attribut est obligatoire, sa valeur doit être renseignée. Elle ne peut pas valoir NULL. Par défaut un attribut n’est pas obligatoire. � SQL : Not NULL

6 : Unicité

Précise si la valeur de l’attribut est unique ou pas pour tous les tuples de la table. Cette unicité ne concerne pas la valeur NULL, autrement dit, un attribut peut être unique mais non obligatoire. Les clés primaires et secondaires sont uniques et obligatoires. Par défaut, un attribut n’est pas unique. � SQL : UNIQUE

7 : Valeur par défaut

La valeur par défaut est une valeur donnée par défaut si aucune valeur n’est donnée.

Toutefois, on peut saisir une autre valeur et aussi la valeur NULL.

La valeur par défaut peut être une constante ou être une valeur calculée. Si c’est une valeur calculée, diffère de l’attribut calculé car elle n’est calculée qu’à la création du tuple et ne sera plus mise à jour automatiquement ensuite.

Par exemple, un attribut « date de création » peut recevoir automatiquement la date du jour au moment de la création. Un attribut clé primaire peut recevoir automatiquement une valeur grâce à un auto-incrément. � SQL : DEFAULT, auto_increment, date de création, date de modification, trigger

8 : Énuméré (valeurs possibles en extension)

Précise la liste des valeurs possibles pour un attribut. La liste de valeur peut être fixe, ou bien modifiable, ou encore calculée à partir de ce qu’on trouve dans la base de donnée. � SQL : check, trigger

9 : Limites et contraintes de valeurs (valeurs possibles en intension)

Cette contrainte précise la liste des valeurs possibles pour un attribut en intension. Par exemple, des bornes inférieures et supérieures. Ces bornes peuvent être fixes, ou bien fonction de la valeur d’autres attributs (attribut 1 < attribut2 ; ou bien attribut 1 < moy (attribut2) ; etc.) On peut aussi préciser une valeur par défaut conditionnelle. Par exemple : si attribut 1=NULL, alors attribut 2=NULL. � SQL : check, trigger

10 : Calculé


Un attribut calculé est un attribut dont la valeur est calculée à partir d’informations qui sont dans la base de données. C’est donc un attribut qui duplique l’information.

En général, les attributs calculés sont non-saisissables, mais ce n’est pas obligatoire.

Un attribut calculé peut être vu comme un cas particulier de valeur par défaut. � SQL : trigger, vue

11 : Non saisissable

Un attribut non saisissable est un attribut qui ne peut pas être saisi. Il a donc forcément une valeur par défaut, ou bien est calculé. � SQL : trigger, gestion des droits, vue

12 : Non modifiable

Précise si on peut modifier la valeur une fois qu’elle a été définie. Avant d’être définie une valeur vaut NULL. Par défaut, un attribut est modifiable.

Le caractère non-modifiable d’un attribut est lié à son cycle de vie.


Les contraintes d’intégrité référentielle

Présentation

La clé étrangère est un attribut qui fait référence à un autre attribut clé primaire.

Cette référence implique un certain nombres de contraintes qu’on appelle « contraintes d’intégrité référentielle ».

Le principe de l’intégrité référentielle est le suivant :

• Une clé étrangère ne peut pas faire référence à une clé primaire qui n’existe pas.

On ne peut donc pas :

• Créer un tuple qui référence un autre tuple si ce dernier n’existe pas.

• Supprimer un tuple si ce dernier est référencé par un autre tuple.

Contrainte d’intégrité référentielle en création � Contrainte sur la création des tables

On ne peut pas créer une table qui contient un attribut clé étrangère si la clé primaire référencée n’a pas déjà été créée.

Donc on doit d’abord créer les tables qui n’ont pas de clés étrangères. Ensuite on crée les tables avec des clés étrangères.

Par exemple : on ne peut pas créer la table des employés qui fait référence à la table des départements si la table des départements n’a pas déjà été créée.

On peut aussi créer toutes les tables, dans n’importe quel ordre, sans préciser quelles sont les clés étrangères (CREATE TABLE). Après la création, on modifie les tables en ajoutant les clés étrangères (ALTER TABLE). � Contrainte sur la création des tuples

Le principe est le même pour les tuples : on ne peut pas créer un tuple qui contiet un attribut clé étrangère si la clé primaire référencée n’a pas déjà été créée.

Par exemple : on ne peut pas créer un employé dans le département 10 si le département 10 n’existe pas.

Contrainte d’intégrité référentielle en suppression � Les tables

On ne peut pas supprimer une table dont la clé primaire est clé étrangère dans une autre table.

Il faut commencer par détruire les tables qui ne sont référencées par aucunes autres tables.

Par exemple : il faut commencer par détruire les tables des employés, ensuite on peut détruire la table des départements.

On peut aussi supprimer toutes les clés étrangères dans n’importe quel ordre (ALTER TABLE), et ensuite supprimer les tables dans n’importe quel ordre (DROP TABLE). � Les tuples

Le principe est le même pour les tuples : on ne peut pas détruire un tuple s’il est référencé par un autre tuple.


Par exemple : on ne peut pas détruire le département 10 si des employés travaillent dans le département 10. � Paramétrage des contraintes d’intégrité référentielle en suppression

On vient de voir la situation par défaut.

Il y a deux autres possibilités :

• ON DELETE CASCADE : si on veut détruire un tuple référencé par un ou plusieurs autres tuples, alors on détruit aussi ce ou ces autres tuples. Cette caractéristique s’applique à l’attribut clé étrangère.

• ON DELETE SET NULL : si on veut détruire un tuple référencé par un ou plusieurs autres tuples, alors on passe la référence au tuple détruit de ce ou ces autres tuples à NULL. Cette caractéristique s’applique à l’attribut clé étrangère.

Par exemple : si on veut détruire le département 10, alors on détruit tous les employés qui travaillent dans le département 10, ou alors on passe le ND des employés du département 10 à NULL.

Contrainte d’intégrité référentielle en modification � Les tables

On ne peut pas modifier le nom d’un attribut dans une table s’il est référencé par un autre attribut. � Les tuples

Le principe est le même pour les tuples : on ne peut pas modifier la valeur d’un attribut d’un tuple si cette valeur est référencée par un autre tuple.

Par exemple : on ne peut pas modifier la valeur 10 du département 10 (en la passant à 11 par exemple) si des employés travaillent dans le département 10. � Paramétrage des contraintes d’intégrité référentielle en modification

On vient de voir la situation par défaut.

Il y a deux autres possibilités :

• ON UPDATE CASCADE : si on veut modifier un tuple référencé par un ou plusieurs autres tuples, alors on modifie la référence au tuple modifié de ce ou ces autres tuples. Cette caractéristique s’applique à l’attribut clé étrangère.

• ON UPDATE SET NULL : si on veut modifier un tuple référencé par un ou plusieurs autres tuples, alors on passe la référence au tuple détruit de ce ou ces autres tuples à NULL. Cette caractéristique s’applique à l’attribut clé étrangère.

On peut concevoir une 3ème possibilités :

• ON UPDATE « DELETE CASCADE » : si on veut modifier un tuple référencé par un ou plusieurs autres tuples, alors on alors on détruit ce ou ces autres tuples.. Cette caractéristique s’applique à l’attribut clé étrangère.

Par exemple : si on veut modifier la valeur 10 le département 10 et la faire passer à 11, alors on modifie aussi le numéro de département de tous les employés qui travaillent dans le département 10 en les faisant passer à 11.


2. Contraintes et cycle de vie des tuples

Présentation

Dans une base de données, les tuples ont un cycle de vie déterminé.

Ils peuvent être créés, modifiés, supprimés. Quand ils sont créés ou modifiés, tous les attributs ne sont pas nécessairement concernés.

Présentation

Création (insert into) et contrainte obligatoire

Les attributs obligatoires (not NULL) sont nécessairement renseignés à la création.

C’est la situation par défaut d’un attribut (elle correspond à une « forme normale 1 » stricte).

Modification (update)

Un attribut peut être :

• Normalement non modifiable : la date d’embauche d’un employé.

• Normalement modifiable : le salaire d’un employé.

• Exceptionnellement modifiable : le nom d’un employé.

La situation par défaut d’un attribut est d’être non modifiable.

Si tous les attributs d’un tuples sont non modifiables, alors c’est le tuple lui-même qui est non-modifiable.

Suppression (delete)

Le tuple peut être entièrement supprimé. C’est finalement assez rare. On a plutôt tendance à tout conserver pour garder des historiques qui permettent ensuite de faire de l’analyse de données et du « data mining ».

Archivage

Le tuple peut être archivé. Cela signifie qu’il est détruit dans la BD courante mais conservé dans une archive.

Cycle de vie et analyse fonctionnelle

L’analyse fonctionnelle, c’est l’analyse des usages du système qui va utiliser la BD.

Le cycle de vie des tuples est lié à l’analyse fonctionnelle.

En réfléchissant au cycle de vie des tuples, on met au jour l’usage de la BD et de ce fait, les usages du système qui utilisera la BD, celui-ci consistant généralement (pour une application OLPT) à créer, modifier et supprimer des tuples.

L’analyse du cycle de vie des tuples permet de vérifier que les données restent cohérentes pendant le cycle de vie.


Exemples

Les employés et les départements � Présentation

EMPLOYES (NE, nom, job, datemb, sal, comm., #ND, *NEchef)


Valoriser le modèle consiste pour chaque attribut à réfléchir aux caractères obligatoire, unique et modifiable ainsi qu’aux valeurs par défaut.

• Tout les attributs sont obligatoires (c’est la situation par défaut) sauf « comm ».

• Job, Sal, Comm, #ND, *NEchef, sont normalement modifiables.

• Nom d’employé, nom de département, et ville sont exceptionnellement modifiable.

• NE, ND, datemb sont non mofiables.

• Aucun attribut n’est unique en dehors des clés primaires.

• Il n’y a pas de valeurs par défaut. � Modèle valorisé

EMPLOYES (NE, nom, job, datemb, sal, comm., #ND, *NEchef) M M ∅M M M

DEPARTEMENTS (ND, nom, ville) � Usages du système

1. Saisie d’un employé

2. Modification d’un employé

3. Saisie d’un département

La bibliothèque � Modèle valorisé

ADHERENTS (NA, nom, prenom, adr, tel) M ∅M



EMPRUNTER(#NL, datemp, dureeMax, datret, #NA) today ∅ � Usages du système

1. Saisie d’un adhérent

2. Modification de l’adresse et du tél d’un adhérent

3. Saisie d’une aoeuvre

4. Saisie d’un livre

5. Saisie d’un emprunt (avec datemp = date du jour par défaut)

6. Modification d’un emprunt = saisie d’un retour


3. Dictionnaire des attributs

Présentation

Le dictionnaire des attributs regroupe tous les attributs dans un tableau.

On sépare les groupes d’attributs par table. On met la clé primaire en premier et la ou les clés étrangères en dernier.

Le tableau précise en plus les contraintes de valeurs pour tous les attributs.

Exemple 1

N° Désignation Code Type Obligé Uniq. Val déf Saisi Modif Enum Limites Calculé #

1 Numéro d'adhérent NA E Oui Oui Auto++ Non Non

2 Nom de l'adhérent Nom A 20 Oui

3 Adresse de l'adhérent Adr A 40

4 Numéro de livre NL E Oui Oui Auto++ Non Non

5 Editeur Editeur A 20

6 Numéro d’œuvre NO E Oui 7

7 Numéro d’œuvre NO E Oui Oui Auto++ Non Non

8 Titre du livre Titre A 20 Oui

9 Auteur du livre Auteur A 20

7 Numéro de livre NL E Oui Non 4

8 Date d'emprunt Datemp D Oui today Non Non

9 Date de retour Datret D today Non Non

10 Numéro d'adhérent NA E Oui Non 1

Valeurs par défaut

Auto-incrément pur NA, NL, NO

Date du jour pour datemp et datret

Saisissable

Les clés primaires auto-incrémentés seront non saisissables.

Datemp et Datret sont non saisissables

Modifiable

Les clés primaires sont non modifiables.

Dateret et le NA des Emprunts sont non modifiables aussi : quand on fait un emprunt, NL, datemp et NA sont renseignés et ne sont pas modifiables. Quand on ramène le livre, datret est renseigné et n’est pas modifiable. Finalement, tous les attributs de la table des emprunts sont non modifiables.


Exemple 2

N° Désignation Code Type Obligé Uniq. Val déf Saisi Modif Enum Limites Calculé #

1 N° de l'employé NE E Oui Oui Auto++ Non Non

2 Nom de l'employé Ename A 10 Oui

3 Fonction de l'employé Job A 9 Oui

4 Date d'embauche Hiredate D Oui Non

5 Salaire Sal R Oui > 0

6 Commission Comm. R >= 0

7 Chef NEChef E 1

8 N° de départemt Deptno E Oui 9

9 N° de départemt ND E Oui Oui Auto++ Non Non

10 Nom du département Dname A 14

11 Localité du départemt Loc A 13

Valeurs par défaut

Auto-incrément pur NE, ND

Saisissable

Les clés primaires auto-incrémentés seront non saisissables.

Modifiable

Les clés primaires sont non modifiables.

La date d’embauche est non modifiable.

Enuméré

Fonction : 'SALESMAN', 'MANAGER', 'PRESIDENT'.

E2 : valeurs possibles: select empno from emp;

E3 : valeurs possibles: select deptno from dept;

Calculé

Pas d’attribut calculé.


4. Jeu de tests

Présentation

Le jeu de tests est un ensemble de tuples qui permet de tester la base de données.

Il doit être construit judicieusement.

En effet, il ne faut pas oublier qu’une requête peut sembler donner de bons résultats sur un jeu de tests et mais pas sur un autre.

Le jeu de tests doit être choisi judicieusement pour permettre de bien tester les requêtes.

Astuces pour bien construire son jeu de tests

Nombre de tuples

Pour bien construire son jeu de tests, il faut mettre au minimum 3 tuples par tables-nom et 6 tuples par tables-verbe.

On est amené à grossir le nombre de tuples en appliquant les règles suivantes.

Les clés primaires

On peut numéroter les clés primaires de 1 à N.

Les clés étrangères

Pour bien construire son jeu de tests, il faut surtout faire attention aux clés étrangères.

• Les clés étrangères doivent correctement faire référence aux clés primaires.

• Si une clé étrangère peut avoir des doublons, le jeu de test doit le montrer.

• Si une clé étrangère peut valoir NULL, le jeu de test doit le montrer.

• Si une clé primaire doit forcément être référencée par une clé étrangère, le jeu de tests doit le montrer.

• Si une clé primaire peut ne pas être référencée par une clé étrangère, le jeu de tests doit le montrer.

Les dates

Pour bien construire son jeu de tests, il faut faire attention aux attributs date.

Les autres attributs

Pour bien construire son jeu de tests, il faut faire attention à mettre des doublons dès que cela semble judicieux, et mettre des valeurs NULL dès que c’est possible. On n’est pas obligé de mettre des « vrais valeurs » : on peut mettre des codes : n1, n2, n3 pour des noms, ad1, ad2, ad3 pour des adresses, etc.


MODELE RELATIONNEL NORMALISE

PRINCIPALES NOTIONS Dépendance fonctionnelle Dépendance élémentaire Dépendance directe Formes normales Normalisation sémantique Attributs calculés Transitivité

Notion de dépendance fonctionnelle : DF

Définition des DF

Présentation

Un attribut Y (ou premier groupe d’attributs) dépend fonctionnellement d’un attribut X (ou second groupe d’attributs), si étant donné une valeur de X, il lui correspond une valeur unique de Y (et ceci quel que soit l’instant considéré).

On dit que X détermine fonctionnellement Y et on note :

X - > Y

X est le déterminant, Y le déterminé.

Exemple

La clé de toute table détermine fonctionnellement tous les autres attributs de la table.

Le numéro de sécurité sociale - > nom de la personne

Précisions

On peut écrire :

X (1) - > (*)Y

Ce qu’on peut lire : « à une valeur de X correspond une valeur (1) de Y » et « à une valeur de Y correspond plusieurs valeurs (*) de X ».

DF et modèle relationnel

Application aux BD relationnelles

Le déterminant est clé primaire pour le déterminé.

Numéro d’employé -> nom

NE -> nom

CP -> attributs

Passage d’une détermination fonctionnelle à une table de BD relationnnelle

X - > Y <==> Table (X, Y)


Propriétés de base des DF

L’étude mathématique des DF fait apparaître plusieurs propriétés.

Quelles que soient A, B, C, D des propriétés d’une relation :

Réflexivité A - > A

Augmentation si A - > B alors A, C - > B

Transitivité si A - > B et B - > C alors A - > C

Pseudo-transitivité si A - > B et B, C - > D alors A, C - > D

Union si A - > B et A - > C alors A - > B, C

Désunion si A - > B, C alors A - > B et A - > C

Ces propriétés sont assez évidentes intuitivement.

Elles permettent de mieux comprendre la notion de dépendance fonctionnelle.

Elles interviennent indirectement dans la normalisation.

Exemples

Réflexivité numéro de sécu (NSS) - > NSS

Augmentation Si NSS - > date de naissance (DN), alors NSS et adresse - > DN

Transitivité NSS - > DN et DN - > Signe astrologique (SA), alors NSS - > SA1

Pseudo-transitivité si NSS - > DN et DN + heure et lieu de naissance (HLN) - > ascendant astrologique (AA) alors NSS et HLN - > AA.

Union si NSS - > DN et NSS - > département de naissance (DEPT) alors NSS - > DN et DEPT

Désunion si NSS - > DN et DEPT, alors A - > B et A - > C

Théorème de décomposition (de Casey et Delobel)

Soit la relation R (A1, A2, A3)

Si A1 - > A2 alors on peut remplacer la relation R par :

R1 (A1, A2)

R2 (#A1, A3)

La jointure naturelle entre R1 et R2 produira la relation R de départ.

1 Sur l’astrologie, pour un logicien : http://www.homme-moderne.org/societe/socio/teissier/analyse/philo.html, et

pour un cadre plus général : http://bliaudet.free.fr/article.php3?id_article=82


Exemple

Un employé a un nom et est caractérisé par un numéro d’employé. Dans l’entreprise, il participe à plusieurs équipes, une équipe étant caractérisée par son nom qui est unique.

La relation :

R(NE, nomEmp, nomEquip)

peut traduire la situation.

Toutefois :

NE ->nomEmp

On peut donc créer les deux relations suivantes :

Emp(NE, nomEmp)

Participer(#NE, nomEquip)

La jointure suivante :

Select e.ne, e.nomEmp, p.nomEquip

From emp e, participer p

Where e.ne = p.ne

permet de reconstituer la table R.

Clés candidates et clés secondaires

Principe

Si

A1 - > A2 et A2 -> A1 alors A1 et A2 sont clés candidates, c’est-à-dire deux attributs qui peuvent être clé primaire.

Alors, on a au choix :

Soit R1(A1, (A2)) avec A2 clé secondaire

Soit R1(A2, (A1)) avec A1 clé secondaire.

Théorème des clés candidates

Si

A -> C et B -> C et il n’existe pas D tel que A -> D ou exclusif B -> D

Alors on a au choix :

Soit R1 (A, (B), C)

Soit R1 (B, (A), C)


Graphe des dépendances fonctionnelles : GDF

Présentation

Le graphe des dépendances fonctionnelles montre toutes les dépendances fonctionnelles sous la forme d’un graphe orienté : chaque attribut est un nœud du graphe et chaque DF est une arrête du graphe.

Exemples

Exemple 1

EMPLOYES (NE, nom, job, dateEmb, salaire, comm., #ND)


NE

nom job dateEmb salaire comm. ND

nom loc � Le graphe se lit ainsi � NE est clé primaire de nom, job, dateEmb, salaire, comme, et ND � ND est clé étrangère de NE � ND est clé primaire de nom et loc.

Exemple 2 : auto-jointure

EMPLOYES (NE, nom, job, dateEmb, salaire, comm., #ND, *NEchef)


NE NEchef

nom job dateEmb salaire comm. ND

nom loc


� Le graphe se lit ainsi � NE est clé primaire de nom, job, dateEmb, salaire, comm, ND et NEchef � NEchef est clé étrangère réflexive de NE.. � ND est clé étrangère de NE. � ND est clé primaire de nom et loc.

Exemple 3 : clé primaire concaténée

ADHERENTS (NA, nom, adr)



EMPRUNTER(#NL, datemp, nbjmax, datret, #NA)

NL + datemp

NL NA dateret nbjmax

éditeur NO nom adr

titre auteur

� Le graphe se lit ainsi � NL+datemp » est clé primaire concaténée de NA, dateret et nbjmax. � NA est clé étrangère de NL+datemp. � NA est clé primaire de nom et adr. � NL est clé étrangère de NL+datemp. � NL est clé primaire de éditeur et NO. � NO est clé étrangère de NL. � NO est clé primaire de titre et auteur.


Exemple 4 : jointure artificielle

DISQUES (ND, titre, année)

CHANSONS (NC, titre, durée)

MUSICIENS (NM , nom, nationalité)

JOUER ( #NM, #NC, instrument )

REGROUPER ((#ND, #NC, piste)

ND, NC NC, NM, instrument

ND NC NM

titre année titre durée nom nationalité

� Le graphe se lit ainsi : � NC+ND est clé primaire concaténée � ND est clé étrangère de NC+ND � ND est clé primaire de titre et année � NC est clé étrangère de NC+ND � NC est clé primaire de titre et durée � NC+NM+instrument est clé primaire concaténée � NM est clé primaire de NC+NM+instrument � NM est clé primaire de nom et nationalité � Il existe une jointure artificielle entre la table dont la clé primaire est (ND, NC) et celle dont la clé primaire est (NC, NM, instrument). Cette jointure passe par NC. Elle est représentée par une flèche à double sens en pointillé.


Sémantique du graphe du GDF

Sémantique générale

1. Les attributs qui pointent vers d’autres attributs sont des clés primaires et sont soulignés.

2. Les attributs pointés par une clé primaire sont les attributs de la table correspondant à la clé primaire.

3. Les attributs pointés par une clé primaire CP1, et qui pointent eux aussi vers des attributs, sont à la fois clé étrangère dans la table de CP1 et clé primaire CP2 pour une nouvelle table.

4. Un attribut peut pointer sur lui-même. Dans ce cas, on précise sur la flèche le nom de l’attribut clé étrangère.

5. Quand un attribut d’une clé primaire concaténée est clé étrangère, on duplique cet attribut dans le graphe pour faciliter la lisibilité.

6. Les jointures artificielles entre deux attributs de deux tables sont représentées par une flèche à double sens en pointillé entre les attributs concernés.

Passage d’un GDF à un schéma relationnel

Le déterminant est appelé « nœud parent ». Le déterminé : « nœud enfant ».

Tout nœud parent est clé primaire pour ses nœuds enfants.

Tout nœud enfant qui est aussi parent sera clé étrangère en tant qu’enfant.

Passage d’un schéma relationnel à un GDF

A partir du schéma d’une base de données, on peut faire le graphe des dépendances fonctionnelles.

Trois règles permettent de construire le graphe :

1 : Toutes les clés primaires sont des nœuds parents du graphe : un nœud peut donc être constitué de plusieurs attributs (cas des clés primaires concaténés).

2 : Tous les attributs non clé-primaire sont des nœuds enfants du graphe.

attribut (s) clé primaire attributs non-clés primaires

3 : Les attributs n’apparaissent qu’une seule fois dans le graphe, sauf les clés étrangères participant à une clé primaire concaténée qui apparaissent dans la clé primaire concaténé et individuellement en tant que clé primaire

#CP1, att CP1


Les 3 premières formes normales de CODD : 1FN, 2FN, 3FN

Principe de la normalisation

La normalisation consiste à éviter la duplication d’information pour éviter les incohérences.

À partir de l’analyse des dépendances fonctionnelles, plusieurs règles ont été trouvées qui permettent de garantir que la table a une forme normale.

Deux caractères remarquables des dépendances fonctionnelles

Notion de DF élémentaire

La DF : A - > B est dite élémentaire si :

aucune partie de A ne détermine B.

Autrement dit, la DF est élémentaire si c’est « tout A » qui détermine B. Donc la DF n’est pas le résultat d’une augmentation (cf. propriété des DF).

Notion de DF directe

La DF : A - > B est directe si :

il n’existe pas C (distinct de A et B) tel que A - > C et C - > B.

Autrement dit, la DF est directe s’il n’y a pas d’intermédiaire entre les deux attributs. Donc la DF n’est pas le résultat d’une transitivité (cf. propriété des DF).

1ère forme normale : 1FN : la clé

Définition

La première forme normale concerne toutes les tables. Une table est en première forme normale si elle possède une clé primaire et si la valeur de tout attribut est « atomique », c’est-à-dire n’est pas constituée par une liste de valeurs.

Exemple

Dans la table des logiciens, on propose, pour chaque logicien, une liste de livres : Frege en a 2, Leibniz en a 1, Porphyre en a 0.

Logiciens

NumLogicien NomLogicien Liste des livres

1 Frege Sens et dénotation, Concept et fonction

2 Leibniz La caractéristique

3 Porphyre


NumLogicien est la clé de la table. Mais cette table n’est pas en première forme normale. En effet : il y a 2 valeurs pour la liste des livres de Frege, et 0 pour celle de Porphyre. � Normalisation

La table mise en première forme normale sera :

Logiciens EstAuteurDe

NumLogicien NomLogicien NumLogicien Livre

1 Frege 1 Sens et dénotation

2 Leibniz 1 Concept et fonction

3 Porphyre 2 La caractéristique

Ces deux tables sont bien en première forme normale.

La clé de la table « estAuteurDe » est le couple (NumLogicien-Livre).

Porphyre n’apparaît que dans la table des logiciens : il n’est pas dans la table EstAuteurDe puisqu’il n’est auteur de rien.

2ème forme normale : 2FN : toute la clé

Définition

La deuxième forme normale concerne les tables dont la clé est constituée de plusieurs attributs.

Une table est en deuxième forme normale si elle est en première forme normale et si aucun attribut non clé ne dépend fonctionnellement d’une partie de cette clé (tout attribut dépend de toute la clé).

Ce qu’on pourrait écrire schématiquement et intuitivement ainsi :

La relation (A1, A2, A3, A4) est 2FN si il n’existe pas A1 - > A3

La correction consiste à créer deux tables :

(A1, A3)

(#A1, A2, A4)

Cette règle permet d’éviter la redondance des données, donc les risques d’incohérence.

Représentation avec un GDF

A1, A2 #A1, A2

=>

A3 A4 A1 A4

A3


3ème forme normale : 3FN : rien que la clé

Définition

Une relation est en troisième forme normale si elle est en deuxième forme normale et si aucun attribut non clé ne dépend fonctionnellement d’un attribut non-clé (tout attribut ne dépend que de la clé).


La relation (A1, A2, A3, A4) est en 3FN s’il n’existe pas A3 - > A4

La correction consiste à créer deux tables :

(A3, A4)

(A1, A2, #A3)

Cette règle évite la duplication d’information due à la transitivité.

Cette règle permet d’éviter la redondance des données, donc les risques d’incohérence.


A1, A2 A1, A2

=>

A3 A4 A3

A4


Formes normales 4 et 5

Les 3 premières formes normales sont les plus importantes. Toutes les autres sont plus théoriques, quoique utiles à l’occasion.

La numérotation (4, 5, 6, etc.) n’est pas standardisée.

4ème forme normale , dite de BOYCE-CODD : BCNF

La forme normale de BOYCE-CODD (BCNF) concerne les tables dont la clé est constituée de plusieurs attributs.

Une relation est en forme normale de BOYCE-CODD si elle est en troisième forme normale et si aucun attribut faisant partir de la clé ne dépend fonctionnellement d’un attribut non-clé.


La relation (A1, A2, A3, A4) est en BCNF s’il n’existe pas A3 - > A1

La solution est la suivante :

(A3, A1)

(#A3, A2)

(A1, A2, A4)

Démonstration

En appliquant la propriété de décomposition (et pas le théorème), à la relation (A1, A2, A3, A4) on obtient :

(A1, A2, A4)

(A1, A2, A3)

En appliquant le théorème de décomposition à la relation (A1, A2, A3), sachant que A3 - > A1, on obtient :

(A3, A1)

(#A3, A2)

Remarque

Ce cas est assez rare.

Par jointure entre les deux tables issues du théorème de décomposition, on obtient la dépendance : A3, A2 -> A1

On a perdu la dépendance suivante : (A1, A2, A3) : elle devra être traitée d’une autre façon.

On aura donc intérêt, en cas de mise au jour d’une violation BCNF, à bien vérifier la sémantique des dépendances fonctionnelles.

Exemple

Soit la relation : Etudiant, Professeur, Matière : (E, P, M)

On considère que :

Un professeur enseigne une matière et une seule : P -> M


pour une matière et un étudiant donnés, il y a seulement un professeur : E,M ->P

La décomposition donne donc : (P, M), (# P, E)

Elève Matière Prof Prof Matière Elève # Prof

1 1 1 1 1 1 1

2 1 1 2 2 2 1

3 1 1 3 2 3 1

1 2 2 4 3 1 2

2 2 2 5 3 2 2

3 2 3 6 3 3 3

1 3 4 1 4

2 3 5 2 5

3 3 6 3 6

Le premier modèle pose problème car si on veut supprimer le fait que l’élève 3 étudie la matière 3, je perd aussi le fait que le prof 6 enseigne la matière 3.

Le nouveau modèle me permet de régler le problème précédent. Mais on perd l’unicité du couple (élève-matière). Par exemple, on peut ajouter le prof 7 pour la matière 3 et l’élève 3 pour le prof 7. En faisant la jointure entre les deux tables, on trouve le couple élève matière à (3, 3), l’un avec le prof 6, l’autre avec le prof 7. On voit donc qu’on ne retrouve pas la clé primaire du premier modèle.

Eleve Matière Prof Prof Matière Eleve # Prof

3 3 7 7 3 3 7

Cette contrainte ne pourra être prise en compte qu’avec des triggers de vérification : si on veut insérer le couple élève-prof (ne, np), on vérifie que la matière enseignée par le prof « np »

Select NM From Prof where NP=np

n’est pas dans la liste des matières suivies par l’élève « ne » Select NM From EleveProf, Prof Where EleveProf.NP = Prof.NP And Eleve.NE = ne


A1, A2 A1, A2 A2, A3

=>

A3 A4 A4 A3

A1

Les jointures artificielles sont représentées avec un lien non orienté (double sens) en pointillé.


5ème forme normale : par réduction de la clé

L’analyse des formes normales 2, 3 et 4 laisse apparaître une possibilité à vérifier :

Soit la relation (A1, A2, A3, A4)

Que se passe-t-il si il existe A1 - > A2 ?

Dans ce cas, la relation devient :

(A1, A2, A3, A4)

C’est ce qu’on peut appeler la forme normale par réduction de la clé.


Normalisations sémantiques

Les normalisations sémantiques sont des cas particuliers des cas généraux correspondant aux 5 cas de normalisation déjà abordés.

Ces cas particuliers mettent en œuvre une sémantique typique.

6ème normalisation : transitivité

Principe de la transitivité des DF :

si A -> B

et B -> C

alors A -> C

Application : soit le schéma suivant :

(A, Ax, #B, #C)

(B, Bx, #C)

(C, Cx)

Ce schéma montre une transitivité des clés étrangères. La transitivité est la suivante :

A -> B

+ B -> C

= A -> C

La DF A->C duplique donc les DF A -> B et B -> C et risque de conduire à une incohérence, si toutefois, A -> C a bien la même signification que A -> B et B -> C, ce que seule l’analyse sémantique permet de dire.

La normalisation consiste à supprimer les chemins directs :

(A, Ax, #B)

(B, Bx, #C)

(C, Cx)


A A

=>

Ax B Ax B

Bx C Bx C

Cx Cx


Analyse par les 3 premières formes normales

On a (A, B, C) avec B -> C, il y a donc violation de la 3FN.

La correction est donc : (A, #B) et (B, C)

7ème normalisation : attributs calculés

Soit le schéma suivant :

(A, A1, A2)

(B, B1, B2)

Soit A2 attribut calculé tel que A2 = f(A1, B1)

On a donc A1, B1 -> A2

La normalisation consiste à supprimer les attributs calculés :

(A, A1)

(B, B1, B2)


A B A B

=>

A1 A2 B1 B2 A1 B1 B2

Analyse par les 3 premières formes normales

On a A1 -> A2 : il y a donc violation de la 3FN

La correction est (A, #A1) et (A1, A2)

Mais on a alors A2 -> A1 : on retombe sur un conflit de clé primaire. On peut donc se séparer d’un des deux attributs.

8ème normalisation : conflit de clé primaire – clé secondaire

Soit le schéma suivant :

(A, A1, A2)

Que se passe-t-il si il existe A1 - > A ?

Il y a deux types de correction :

1) élimination d’un des deux attributs : la table devient :

(A, A2)

ou bien

(A1, A2)

2) passage d’un des deux attributs en clé secondaire : la table devient :

(A, (A1), A2)


ou bien

(A1, (A), A2)

Le choix de garder les deux attributs dépend de la sémantique du modèle.

Remarque

On est dans un cas particulier de la 4ème FN (FNBC) : un attribut non clé détermine la totalité de la clé primaire.

9ème normalisation : dépendance fonctionnelle entre espèce et genre

Exemple

Les personnes (NP) ont un nom, un prénom, une adresse.

Les membres sont des personnes. Ils ont en plus une dateEntrée, une catégorie, une 2ème adresse.

Le modèle proposé est le suivant

Membres (NM, dateEntrée, catégorie, #NP)

Personnes (NP, nom, prénom, ad1)

On a #NP -> NM : il y a donc un conflit de clé primaire dans la table Membres.

Que se passe-t-il si il existe A1 - > A ?

Solution

La solution suit le principe de la normalisation en cas de conflit de clé primaire.

On arrive à deux options � La clé primaire de l’espèce (NM) est significative

Dans ce cas, on conserve les deux clés primaires.

On a donc deux solutions :

Membres (NM, (#NP), dateEntrée, catégorie)


#NP est clé secondaire

Ou :

Membres (#NP, (NM) dateEntrée, catégorie)


NM est clé secondaire � La clé primaire de l’espèce (NM) n’est pas significative

Dans ce cas, on supprime NM :

Membres (#NP, dateEntrée, catégorie)



TD

1 Exercices de modélisation relationnelle

1 Association et donateur

Une association reçoit des dons de donateurs privés. Pour chaque don, l’association enregistre la date du don, son montant ainsi que le nom et l’adresse du donateur. Une fois par an, l’association envoie des reçus fiscaux aux donateurs de l’année. Les reçus fiscaux précisent le nom et l’adresse du donateur, l’année fiscale (qui peut être différente de l’année de date du don), le montant et la date du don. Pour chaque don, on précise la date d’envoi du reçu fiscal. Un don ne donne lieu qu’à un reçu fiscal. Un reçu fiscal ne concerne qu’un seul don. Les reçus sont créés à l’occasion de l’envoi des reçus. L’année fiscale est déterminée au moment de la saisie du don. Toutes les données concernant les dons et les donateurs sont conservées. Pour chaque donateur, on connaît le nombre de dons de l’année en cours.

L’association envoie des courriers aux donateurs pour les solliciter. Un courrier est caractérisé par un libellé (un titre), une date d’envoi et son texte. Plusieurs mailings différents peuvent être envoyés à la même date. Il arrive que plusieurs courriers aient le même titre sans avoir le même texte. Un don peut faire référence à un courrier ou pas. Pour chaque courrier, on connaît le montant total des dons qui s’y rapporte.

1. Faire le schéma de la BD correspondant au MR Brut

2. Faire le graphe des tables correspondant.

3. Valoriser le modèle (obligatoire, unique, modifiable, valeur par défaut)

4. Quels attributs calculés peut-on envisager ?


2 Les employés et leurs carrières

On souhaite gérer le personnel d'une société. Chaque membre du personnel a un nom, une fonction, un salaire, une commission (part de salaire variable), un salaire totale (salaire + commission), une date d'entrée dans la société. Chaque membre du personnel travaille dans un département caractérisé par son nom (commercial, production, personnel, comptable et recherche) et la ville dans laquelle il se trouve.

Chaque membre du personnel a un supérieur hiérarchique et un seul lui-même membre du personnel, sauf le président qui n’a pas de supérieur hiérarchique.

A cette situation, qui donne lieu au schéma de la BD abordé en cours, on ajoute les éléments suivants :

Le service des Ressources Humaines veut gérer la carrière des employés. Pour chaque employé, on considère chaque modification de salaire, chaque changement de poste, chaque changement de supérieur hiérarchique et chaque changement de département comme une étape dans sa carrière.

L’historique des commissions n’est, par contre, pas gardé.






3 La cinémathèque

Une cinémathèque veut mettre en place un système d’information concernant des films et leurs diffusions. Pour chaque film on connaît son titre, sa date de création, ses acteurs avec leur nom, leur prénom et leur nationalité, les réalisateurs avec les mêmes informations, la langue du film. On connaît aussi les salles dans lesquelles il a été projeté, avec l’heure et le jour de la projection, la version projetée (version originale ou version française). On connaît le nombre de places des salles, le type d’écran et le type de son, ainsi que le cinéma correspondant, avec son nom, son adresse, son téléphone et le nombre de salles du cinéma. On veut pouvoir faire un classement du plus grand nombre d’entrées par film.






4 Les chantiers d’été

Une association organise des chantiers d’été. Il y en a de 3 sortes : chantiers de réhabilitation, chantiers de construction ou chantiers archéologiques. Pour les chantiers de construction et de réhabilitation, on connaît le type de bâtiment. Pour les chantiers de réhabilitation, on connaît la date de création de l’ancien bâtiment et des informations sur l’ancien bâtiment. Pour les chantiers archéologiques, on connaît la période archéologique et la surface du chantier (qui peut être très grande). Les chantiers ont une date de début et une date de fin (qui peut être plusieurs années après la date de fin). Des équipes viennent sur le chantier. Une équipe est constituée pour une durée limitée (environ un mois pendant l’été). Elle est affectée à un chantier et un seul. Les équipes sont constituées de participants. Les participants aux chantiers sont des personnes. Leur participation à l’équipe peut être inférieure à la durée de vie de l’équipe elle-même. Les personnes ont un nom, un prénom, une adresse, un email et un téléphone.

L’association peut recevoir des dons de personnes. Les dons ont un montant et une date de don. Ils sont fait par une personne. Ils donnent lieu à un reçu fiscal envoyé à une certaine date.

L’association envoie des courriers aux personnes. Les courriers ont un titre, une date et un libellé.

L’association gère aussi des membres. Les membres payent des cotisations annuelles. Les cotisations ne donnent pas lieu à un reçu fiscal. On distingue donc deux types de dons : ceux avec reçu et les cotisations.

Les membres de l’association peuvent participer au conseil d’administration (CA). Le CA est renouvelé en général tous les ans. Les participants au CA ont une fonction particulière. Cette fonction peut changer en cours de CA. On conserve les dates de début et de fin de chaque fonction occupée.






5. Gestion de projet

On modélise l’organisation des projets en tâches dans une entreprise. Un projet est caractérisé par un nom, une date début, une date de fin et un identifiant unique. Un projet est constitué de plusieurs tâches numérotées de 1 à N. Chaque tâche a un intitulé, une date de début et une date de fin. Elle est précédée par 0 ou plusieurs tâches. Elle est suivie par 0 ou plusieurs tâches. Deux tâches d’un même projet peuvent démarrer à la même date. Deux tâches d’un même projet peuvent se terminer à la même date. Une tâche démarre au plus tôt le lendemain de la fin de la tâche qu’elle suit. Une tâche ne peut pas commencer avant le début du projet, ni finir après la fin du projet.

Dans l’entreprise, les membres du personnel ont un nom, une fonction et un identifiant. A chaque projet, on affecte des membres du personnel avec une fonction spécifique pour chaque projet. Ensuite, on affecte les membres du projet à des tâches du projet.

Chaque tâche peut requérir des ressources spécifiques. L’allocation d’une ressource par une tâche se fait à partir d’un certain jour et pour une certaine durée. Une ressource peut être allouée plusieurs fois par une même tâche à des périodes différentes. Une ressource a un intitulé et coût journalier.






2. Exercices de normalisation

1. Normalisations formelles : théorème de décomposition

Soit les relations suivantes avec les dépendances associées :

1: R (A,B,C,D) avec (C -> D)

2: R (A,B,C,D) avec (B -> C) ; (B -> D)

3: R (A,B,C,D) avec (C -> D) ; (D -> A)

4: R (A,B,C,D,E) avec (C -> D) ; (D -> B) ; (D -> E)

Pour chaque relation : � Décomposer la relation en plusieurs relations en utilisant le théorème de décomposition. � Identifier la clé primaire d’origine et les clés primaires de toutes les relations décomposées. � Identifier les violations de forme normale. � Faire le graphe des dépendances fonctionnelles et le graphe des tables correspondant.

2. Normalisations formelles : formes normales

Soit les relations suivantes avec les dépendances associées :

1: R (A,B,C,D) avec (A,B -> C) ; (C -> D) ; (A,B -> D)

2: R (A,B,C,D) avec (B -> C) ; (B -> D) ; (A,B -> C) ;

3: R (A,B,C,D) avec (A, B -> C) ; (C -> D) ; (C -> A)

4: R (A,B,C,D,E) avec (A, B -> C) ; (C -> D) ; (D -> B) ; (D -> E)

Pour chaque relation : � Quelles sont les dépendances fonctionnelles ? � Faire le GDF initial � Quelle est la clé primaire de la table de départ ? Trouver les dépendances fonctionnelles par transitivité pour trouver la clé primaire. � Quelles sont les violations de formes normales ? � Faire un schéma de BD en forme normale. � Faire le GDF du modèle normalisé

3. Les personnes et leurs téléphones

Soit la relation R(nom, prénoms, INSEE, adresse, ville, CP, région, tél fixe, tél mobile) définie sur l’ensemble de la population française, dans laquelle tous les attributs sont définis à la seule exception du tél mobile. On supposera que tél fixe est un numéro France Télécom et que les prénoms des membres d’un même foyer sont distincts. On suppose que le n°INSEE détermine la personne. On suppose aussi que le CP détermine la ville

1) Quelles sont les dépendances fonctionnelles ?

2) Faire le GDF.


3) Quelle est la clé primaire de la (ou des) table(s) de départ ?

4) Quelles sont les violations de formes normales ?

5) Faire un schéma de BD en forme normale.

6) Faire le graphe des tables. Exercice repris à Xavier Hilaire - ESIEE

4. La table des ventes de voitures d’occasion

Soit la table suivante contient les informations sur la vente de voiture d’occasion.

NumV NumIm Marque Type Puis Coul. NumP Nom Prénom Date Prix 5 672 RH 75 RENAULT R 12 TS 6 ROUGE 1 MARTIN PAUL 10/02/98 10 000

1 800 AB 64 PEUGEOT 504 9 VERTE 1 MARTIN PAUL 11/06/95 30 000

3 686 HK 75 CITROEN 2 CV 2 BLEUE 3 DUPOND JEAN 20/04/97 5 000

2 720 CD 60 CITROEN AMI 8 5 BLEUE 2 DUPOND JEAN 20/08/96 15 000

4 672 RH 75 RENAULT R 12 TS 6 ROUGE 4 DURANT PIERRE 11/09/97 12 000

NumV est le numéro de la vente.

NumIm est le numéro d’immatriculation.

NumP est le numéro de la personne.

On connaît la date et le prix de vente.

Le type détermine la puissance et la marque.

Une voiture peut être vendue plusieurs fois (occasion)


2) Faire le GDF.




6) Faire le graphe des tables.

5. La table des commandes

Commandes(NC, dateCommande, numClient, nomClient, adClient, réductionCom)

ArticlesCommandes(NC, numArticle, nomArticle, descriptionArticle, quantitéCommandée, prixUnitaireArticle, prixTotal, réduction)

Une commande est constituée de plusieurs articles. Un article commandé l’est dans une certaine quantité et pour un certain prix unitaire. Chaque article commandé peut bénéficier d’une réduction particulière. Chaque commande peut bénéficier d’une réduction particulière. Pour calculer le prix réel de l’article, on prend en compte une seule réduction : la plus favorable au client.


2) Faire le GDF.






6. Les avions

Soit le tableau de 5 lignes suivant :

Suite du tableau :


2) Faire le GDF.





7. L’éditeur

Soit le tableau de données suivant :

Editeur Num_ouvrage Langue

Ed1 1, 2, 3 français, italien, anglais

Ed2 4, 5 anglais, français

Ed3 3, 6 français, anglais, russe, espagnol

Ce tableau traduit la situation suivante :

Un éditeur édite des ouvrages. Chaque éditeur édite systématiquement tous ses ouvrages dans les mêmes langues. Un ouvrage peut être édité par plusieurs éditeurs.


2) Faire le GDF.




numVol lieuDépart lieuArrivée heureDépart heureArrivée numVolEffectif dateDépart dateArrivée1 Paris Toulouse 7h30 8h30 250 01-juin-08 01-juin-081 Paris Toulouse 7h30 8h30 250 01-juin-08 01-juin-081 Paris Toulouse 7h30 8h30 262 02-juin-08 02-juin-081 Paris Toulouse 7h30 8h30 279 03-juin-08 03-juin-082 Paris Nice 16h00 17h15 255 01-juin-08 01-juin-08

numAvion typAvion nbPlaces dateRésa nbRésa totalPayé numCli nomCli adrCli1 A320 150 28-mai-08 2 80 1 Dupond rue truc1 A320 150 30-mai-08 1 45 2 Durand rue chose1 A320 150 30-mai-08 1 45 2 Durand rue chose2 B727 120 02-juin-08 3 100 3 Dugenou rue truc2 B727 120 30-mai-08 1 60 3 Dugenou rue truc

Documents

Unité IN3R22 Introduction aux bases de - [Site de Bertrand …bliaudet.free.fr/IMG/pdf/ESIEE-IN3R22-Cours_SGBD-02.… · · 2010-05-19Table, tuple, attribut, clé primaire 8 Schéma