Discussion :

[SpaceX]

Bonjour,

J'ai un examen la semaine prochaine et je m’entraîne sur des exercices d'ancien examens , pouvez-vous me dire si mes réponses sont justes ou totalement incorrect pour que je ne puisse pas reproduire l'erreur ?

Je me suis appuyé du cours de ce site suivant : http://www.gaudry.be/sgbdr-formes-normales.html

id = primary key
# = foreigner key

----------------

• En quelle forme normale est ce modèle, s'il n'est pas en 3NF corrigez le.

a) Supposons que le numéro étudiant et le numéro de sécurité sociale sont uniques.

Etudiant(idEtudiant, numéroSécu,, nomPersonne, prénomPersonne)

Solutions :

• 1NF => Ok car clé unique et chaque atttribut est atomique
• 2NF => Ok car les champs dépent que d'une partie d'une clé, ici idEtudiant
• Déjà en 3NF car il n'y à pas de dépendance fonctionnel

Je vous remercie d'avance.

[fsmrel]

Bonsoir SpaceX,

Remarque préalable :

Votre message initial comportait 8 exercices, et seul le 1er a été conservé, les 7 autres se sont volatilisés. Etant donné que j’ai traité des 4 premiers, j’en rappelle les énoncés et vos propositions, à l’intention des forumeurs soucieux de progresser dans l’étude de la normalisation.

a) Supposons que le numéro étudiant et le numéro de sécurité sociale sont uniques.

Etudiant(idEtudiant, numéroSécu,, nomPersonne, prénomPersonne)

Solutions :

1NF => Ok car clé unique et chaque atttribut est atomique
2NF => Ok car les champs dépent que d'une partie d'une clé, ici idEtudiant
Déjà en 3NF car il n'y à pas de dépendance fonctionnel

b) Affectation(idPersonne, idEtablissement, nomPersonne, prénomPersonne)

Supposons qu'une personne peut être dans plusieurs établissements et qu'un établissement peut accueillir plusieurs personne

Solutions :

Cette table satisfait à la première forme normale (nous avons une clé unique, et chaque attribut est atomique)
Pas à la deuxième forme normale car les attributs nomPersonne et prénomPersonne ne dépendent pas de idEtablissement

Personne(idPersonne, nomPersonne, prénomPersonne)
Etablissement(idEtablissement,#idPersonne, #nomPersonne)

Dans cette forme, elle respecte la 2NF et la 3NF puisqu'on à plus de dépendance fonctionnel.

c) Supposons qu'une personne ne peut avoir qu'une seule adresse mais qu'elle peut avoir fait plusieurs dons et qu'un don ne peut avoir été fait que par une seule personne. Supposons également que nous ne puissions pas avoir d'homonymes.

Don(nom, prénom, ville, rue, montantsDon, dates)

Solutions :

Cette table ne satisfait pas la première forme normale (nous avons aucune clé unique, et des attributs redondantes 'ville', 'rue' -> remplacer par adresse)

Personne(idPersonne, nomPersonne, prénomPersonne, adressePersonne)
Don(idDon, #idPersonne, #nomPersonne, #adressePersonne, montantsDon)

Ici, j'ai un doute si on est bien en 2NF.

d) Supposons que nous de puissions pas avoir deux homonymes :

Patient(nom,prénom,maladie)
Ville(nom,hopital)
Hopital(nom)
Hospitalisation(nomPatient,nomVille,nomHopital)

Solutions :

Patient(idPatient, nomPatient, maladiePatient)
Hopital(idHopital, nomHopital , adresseHopital)
Hospitalisation(#idPatient,#nomPatient, #adresseHopital)

J’ai regardé d’un peu plus près ce qu’a écrit l’auteur de l’article auquel vous faites référence...

Vérifier la robustesse du modèle relationnel

Le modèle relationnel est défini par les 5 composants que j’ai résumés ici. Le modèle relationnel est la théorie formelle, la fondation sur laquelle on s’appuie pour construire des bases de données relationnelles. Cette théorie est à considérer comme une branche des mathématiques appliquées. Vouloir « vérifier la robustesse du modèle relationnel », c’est comme vouloir vérifier la robustesse de la théorie des ensembles !

Ceci prouve que l’auteur ferait bien de mettre à jour ses connaissances.

A la réflexion, ce qu’il nomme « modèle relationnel » ne peut correspondre qu’au MLD « relationnel » de Merise, c'est-à-dire une structure tabulaire par opposition à la structure réseau (CODASYL) des bases de données navigationnelles de jadis. Il y a un gros quiproquo.

1FN : Première forme normale
« Une relation est en première forme normale si tout attribut contient une valeur atomique.
Chaque entité doit posséder un identifiant (une clé) qui la caractérise de manière unique. »

L’auteur parle d’entité et de relation, il se situe donc au niveau du MCD de Merise. Il est donc en droit de dire ce qu’il veut, puisque les énoncés merisiens concernant la normalisation ne relèvent d’aucune théorie, mais ne sont que des recettes, parfois utiles, parfois néfastes, recopiées et déformées au fil des ans.

Quand il écrit « Chaque entité doit posséder un identifiant », c’est du Merise pur jus, « canonique », cela fut évidemment énoncé dans le 1er document officiel (tamponné par le Ministère de l’Industrie, en 1979) : Méthode de définition d’un système d’informations, fascicule 4, guide pratique pour l’élaboration des modèles de données et des traitements.

Dans ce document officiel, on lit :

Page 9 : « Parmi l’ensemble des propriétés qui caractérisent un objet, une propriété particulière est choisie comme identifiant ».

L’« objet » est ce qu’on appelle aujourd’hui un type d’entité (ou un type d’individu). La « propriété » est ce que l’auteur appelle un « attribut ».

Page 9 encore : « L’identifiant d’une relation est obtenue par concaténation des identifiants des objets qui participent à la relation ».

Cela dit le document officiel ne parle pas de normalisation...

Quand l’auteur énonce : « Une relation est en première forme normale si tout attribut contient une valeur atomique », pour commencer il ferait mieux de remplacer « atomique » qui est trop flou, vague, contestable et contesté par « scalaire » bien plus précis. Par ailleurs, la 1NF ne concerne pas que les relations merisiennes ! Quid des entités-types ? Leurs attributs pourraient contenir des listes de valeurs ?

Nous devons absolument séparer l'adresse en différentes valeurs (rue, numéro, etc.) pour respecter la première forme normale.

L’auteur a une vision très personnelle, sinon dévoyée. L’atomicité a encore frappé. Si le type de l’attribut Adresse est scalaire, la 1NF n’est pas violée. La décomposition en rue, numéro, etc., relève simplement du besoin fonctionnel de l’utilisateur, à répercuter dans le MCD.

La vraie définition de la 1NF (une relation est ici une valeur de relvar) :

Database Design & Relational Theory, Normal Forms and All That Jazz

Soit la relation r d’attributs A1, ..., An, respectivement de types T1, ..., Tn ; r est en première forme normale (1NF) si et seulement si, pour tous les tuples t présents dans r, la valeur de l’attribut Ai dans t est du type Ti (i =1, ...n).

Soit vous continuez à vous référer à l’auteur en cause et on en reste là, soit vous voulez travailler sérieusement et donc vous l’ignorerez, ce que je fais pour ma part maintenant, en restant dans le cadre de la théorie relationnelle et dans celui de la normalisation.

a) Supposons que le numéro étudiant et le numéro de sécurité sociale sont uniques.

Etudiant(idEtudiant, numéroSécu, nomPersonne, prénomPersonne)

Solutions :

1NF => Ok car clé unique et chaque attribut est atomique
2NF => Ok car les champs dépend que d'une partie d'une clé, ici idEtudiant
Déjà en 3NF car il n'y à pas de dépendance fonctionnel

.
Pourquoi souligner idEtudiant ? Cet attribut est-il « plus égal » que numéroSécu ?

Anyway. On va dire que la relvar Etudiant est en 1NF dans la mesure où :

(a) L’ordre des attributs dans l’en-tête de la relvar ne joue pas ;

(b) L’ordre des tuples des relations (valeurs prises par la relvar) ne joue pas ;

(c) Les tuples en double sont interdits ;

(d) L’intersection d’un attribut et d’un tuple contient exactement une valeur du type de l’attribut, et rien d’autre ;

(e) Les attributs sont tous réguliers : ils ont un nom (ce qui n’est pas forcément le cas avec SQL quand on produit des résultats avec des colonnes non nommées ou dont les noms doublonnent), quand il n’y a pas de colonnes cachées, object Ids, timestamps...)

Il va de soi que par construction, une relvar respecte la 1NF : En vertu de la définition qu’en donne le modèle relationnel de données de Codd, il ne peut en être autrement. On ne peut pas en dire autant d’une table SQL dans le cas général. (Si une table respecte les points précédents, alors seulement elle est en 1NF).

Quand vous écrivez : « 1NF => Ok car clé unique et chaque attribut est atomique », vous sous-entendez que vous êtes d’accord pour respecter au moins le point (d) ci-dessus. L’expression, « clé unique » est à dégager, car une relvar a toujours une clé, au moins implicite, et elle peut en avoir un nombre quelconque : les merisiens sérieux, à l’instar de Dominique Nanci (RIP) sont d’accord sur ce point). Supposons que vous ayez oublié de faire mention des contraintes, relevant en fait des règles de gestion des données, « le numéro étudiant et le numéro de sécurité sociale sont uniques » : du point de vue du MCD merisien vous seriez pris en faute (objet sans identifiant, donc sémantiquement non valide), mais ça n’est pas le cas du point de vue du modèle relationnel de données (mathématique), pour lequel la clé de la relvar serait de facto composée par défaut de l’ensemble des attributs de l’en-tête de la relvar, à savoir le quadruplet :

{idEtudiant, numéroSécu, nomPersonne, prénomPersonne}

Notez l’emploi des accolades, car l’en-tête d’une relvar est un ensemble, au sens de la théorie des ensembles.

Maintenant, en reprenant les contraintes imposées : « le numéro étudiant et le numéro de sécurité sociale sont uniques », il s’ensuit que le quadruplet n’est plus clé (candidate), mais reste surclé.

Reprenons ce que vous écrivez :

« 2NF => Ok car les champs dépend que d'une partie d'une clé, ici idEtudiant »

Il y a à boire et à manger.

Rappelons une (vraie) définition de la 2NF :

La relvar R est en deuxième forme normale (2NF) si et seulement si, pour chaque clé K de R et pour chaque attribut non-clé A de R, la DF (dépendance fonctionnelle) K → {A} est irréductible.

Notez encore les accolades : {A} est un ensemble (tout comme K). Notez aussi que la 1NF n’est pas mentionnée (c’est avec raison, car une relvar est forcément 1NF, c’est sans condition).

Prenons la 1re clé : K1 = {idEtudiant}. Les attributs non-clés sont nomPersonne et prénomPersonne. Puisque {idEtudiant} est « unique », c'est-à-dire clé, automatiquement il existe les DF :

DF01 - {idEtudiant} → {nomPersonne}
DF02 - {idEtudiant} → {prenomPersonne}
DF03 - {idEtudiant} → {numeroSecu}

DF03 c’est du bonus. Les dépendances fonctionnelles DF01 et DF02 sont irréductibles (à gauche) car le déterminant {idEtudiant} est singleton (non composé, mono-attribut).

Prenons la 2e clé : K2 = {numeroSecu}. Les attributs non-clés sont encore nomPersonne et prénomPersonne. Puisque {numeroSecu} est « unique », c'est-à-dire clé, automatiquement il existe les DF :

DF04 - {numeroSecu} → {nomPersonne}
DF05 - {numeroSecu} → {prenomPersonne}
DF06 - {numeroSecu} → {idEtudiant}

Les dépendances fonctionnelles DF04 et DF05 sont irréductibles (à gauche) car le déterminant {numeroSecu} est singleton lui aussi.

Pour chacune des clés {idEtudiant}, {numeroSecu} et pour chaque attribut non clé {nomPersonne} et {prenomPersonne} les DF sont irréductibles, donc la relvar Etudiant est en 2NF.

Vous voyez qu’en n’ayant considéré que la seule clé {idEtudiant}, vous êtes loin du compte. Je subodore que vous avez pris en bloc la paire {idEtudiant, numeroSecu} pour clé : c’est une surclé, mais pas une clé.

Quant à la troisième forme normale (3NF), elle est respectée. Vous donnez un motif non valide, car dans une relvar il y a toujours des DF (au moins triviales) et dans votre cas, il y a 6 DF non triviales (mais sans doute faudrait-il remplacer « il n'y à pas de dépendance fonctionnelle » par « il n'y à pas de dépendance fonctionnelle transitive ». Quoi qu’il en soit, je vous donne une définition valide de cette forme normale :

La relvar R est en troisième forme normale (3NF) si et seulement si, pour chaque dépendance fonctionnelle non triviale X → Y, X est une surclé, ou Y est une sous-clé.

Notez qu’on ne mentionne pas la 1NF (avec raison, une fois de plus), et qu’on n’a pas besoin non plus de citer la 2NF.

Les seules dépendances fonctionnelles non triviales sont celles qu’on a mises en évidence ci-dessus : DF01 à DF06. Chaque déterminant de ces DF, à savoir {idEtudiant} et {numeroSecu} est clé, donc surclé : la 3NF est respectée.

[fsmrel]

Suite du film,

b) Affectation (idPersonne, idEtablissement, nomPersonne, prénomPersonne)

Supposons qu'une personne peut être dans plusieurs établissements et qu'un établissement peut accueillir plusieurs personne

Solutions :

Cette table satisfait à la première forme normale (nous avons une clé unique, et chaque attribut est atomique)
Pas à la deuxième forme normale car les attributs nomPersonne et prénomPersonne ne dépendent pas de idEtablissement.

Je ne reviendrai pas sur votre définition de la 1NF.

En ce qui concerne la 2NF, vous devez commencer par mettre en évidence les clés candidates. En l’occurrence, il n’y en a qu’une, à savoir la paire {idPersonne, idEtablissement}. Je vous demande de le prouver. Vous devrez dresser l’inventaire des DF (réductibles et irréductibles).

Les DF sont les suivantes, à vous de faire la part entre celles qui sont réductibles ou non.

DF11 - {idPersonne} → {prenomPersonne}
DF12 - {idPersonne} → {nomPersonne}
DF13 - {idPersonne, idEtablissement} → {prenomPersonne}
DF14 - {idPersonne, idEtablissement} → {nomPersonne}
DF15 - {idPersonne, idEtablissement, nomPersonne} → {prenomPersonne}
DF16 - {idPersonne, idEtablissement, prenomPersonne} → {nomPersonne}

Il y en a de moins intuitives si je puis dire, par exemple :

DF17 - {idPersonne, idEtablissement, nomPersonne, prenomPersonne} → {}
DF18 - {idPersonne} → {}
DF19 - {} → {}

Etc.

Mais avec la théorie de la normalisation, on ne fait pas dans l’art conceptuel...

Dans votre décomposition de la relvar Affectation, en ce qui concerne la nouvelle relvar Personne, précisez que {idPersonne} est clé, ne serait-ce qu’en soulignant l’attribut. Pourquoi avoir renommé Affectation en Etablissement ? (Ça c’est pour l’aspect sémantique, si vous définissez une entité-type Etablissement, elle sera caractérisée par des attributs propres à un établissement (nom, adresse, etc.)). De toute façon, l’attribut nomPersonne n’a rien à faire dans cette relvar qui gardera son nom, Affectation, et vous devrez mettre en évidence la clé, qui demeure la paire {idPersonne, idEtablissement} :

Personne {idPersonne, nomPersonne, prénomPersonne}
Affectation {idPersonne, idEtablissement}

Notez à nouveau l’utilisation des accolades, l’en-tête d’une relvar est un ensemble.

[fsmrel]

Suite du feuilleton,

c) Supposons qu'une personne ne peut avoir qu'une seule adresse mais qu'elle peut avoir fait plusieurs dons et qu'un don ne peut avoir été fait que par une seule personne. Supposons également que nous ne puissions pas avoir d'homonymes.

Don(nom, prénom, ville, rue, montantsDon, dates)

Solutions :
Cette table ne satisfait pas la première forme normale (nous avons aucune clé unique, et des attributs redondantes 'ville', 'rue' -> remplacer par adresse)

Tout est faux.

Vous êtes resté muet quant à l’attribut date (à mettre au singulier, tout comme montantDon). Evacuons cet attribut dans la mesure où il n’apparaît pas dans vos décompositions.

Dans ces conditions, les dépendances fonctionnelles sont les suivantes :

DF31 - {nom, prenom} → {ville}
DF32 - {nom, prenom} → {rue}
DF33 - {nom, prenom} → {ville, rue}
DF34 - {nom, prenom, montantDon} → {ville}
DF35 - {nom, prenom, montantDon} → {rue}
DF36 - {nom, prenom, montantDon} → {ville, rue}

En poussant un peu :

DF37 - {nom, prenom, rue} → {ville}
DF38 - {nom, prenom, ville} → {rue}

Vu les DF, seul le triplet {nom, prenom, montantDon} est clé.

Par ailleurs, votre auteur de référence a écrit :

« Chaque entité doit posséder un identifiant (une clé[1]) qui la caractérise de manière unique ».

Le triplet {nom, prenom, montantDon} étant clé, il caractérise donc l’entité-type Don (et pas entité SVP, une entité est seulement une instance d’entité-type).

Les prétendues « redondances » n’en sont pas. Remplacer les attributs Ville et Rue par un attribut Adresse, cela relève de la modélisation et des préférences de l’utilisateur : soit il a besoin de connaître la ville et la rue, soit ça ne l’intéresse pas, mais en tout cas la normalisation n’a rien à voir avec ça.

Quoi qu’il en soit, la 1NF est respectée. La 2NF ne l’est pas. Pour qu’elle le soit, la relvar Don doit être décomposée. Pour faire cela sans se planter (vous vous êtes planté...), il faut utiliser le théorème de Heath :

Soit la relvar R {A, B, C} dans laquelle A, B et C sont des sous-ensembles d'attributs de R. Si R satisfait à la dépendance fonctionnelle A → B, alors R est égale à la jointure de ses projections sur {A, B} et {A, C}.

Posons A = {nom, prenom}, B = {ville, rue}, C = {montantDon} ;  

En vertu du théorème, La relvar Don est décomposable en :

{A, B}, c'est-à-dire {nom, prenom, ville, rue}
{A, C}, c'est-à-dire {nom, prenom, montantDon}

Maintenant, pour sortir du cadre strict de la normalisation dont ça n’est pas le problème, vous êtes en droit de définir des identifiants non significatifs (c’est même très vivement conseillé, voyez la règle d’or d’Yves Tabourier), comme vous l’avez fait avec l’attribut idPersonne.

Dans ces conditions, appliquer le théorème de Heath à Don {idPersonne, nom, prenom, ville, rue, montantDon}, avec :

A = {idPersonne}, B = {nom, prenom, ville, rue}, C = {montantDon} ;

produit la décomposition :

{idPersonne, nom, prenom, ville, rue}
{idPersonne, montantDon}

Pour les exercices suivants, corrigez-les en fonction des observations que j’ai faites. Et surtout, laissez tomber votre auteur de référence, quand on lit :

Les formes normales (FN) désignent certains types de relations entre les entités

On se pose des questions...


C’est tout pour le moment, mais à suivre.

[escartefigue]

On se pose des questions...

Ou pas

L'auteur a également quelques lacunes en orthographe :

La conception d'une base de données n'a rien de bien compliqué en sois

Une base de donnée en soie aurait adouci le propos

[fsmrel]

d) Supposons que nous de puissions pas avoir deux homonymes :

Patient(nom,prénom,maladie)
Ville(nom,hopital)
Hopital(nom)
Hospitalisation(nomPatient,nomVille,nomHopital)

Solutions :

Patient(idPatient, nomPatient, maladiePatient)
Hopital(idHopital, nomHopital , adresseHopital)
Hospitalisation(#idPatient,#nomPatient, #adresseHopital)

Deux patients peuvent non seulement avoir le même nom et le même prénom, mais ils peuvent aussi avoir la même maladie. Indépendamment de la normalisation, du point de vue de la modélisation référez-vous à nouveau à la règle d’or d’Yves Tabourier, en mettant en oeuvre un identifiant non significatif, ce que vous avez heureusement fait avec l’attribut idPatient.

Là où le bât blesse, c’est que les attributs nomPatient et adresseHopital n’ont strictement rien à faire dans la relvar Hospitalisation qui manifestement a pour objet d’établir une association entre les relvars Patient et Hopital. Si au stade MCD, idPatient est identifiant de l’entité-type Patient et l’attribut idHopital, identifiant de l’entité-type Hopital, alors au stade MLD la table Patient (équivalent de la relvar relationnelle Patient) a pour clé (candidate) le singleton {idPatient}, la table Hopital a pour clé (candidate) le singleton {idHopital}.

Pour des raisons pratiques, j’utilise la représentation du modèle relationnel de données, en l’occurrence avec Tutorial D (comme je l’ai fait ici) :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 

VAR Patient BASE RELATION 
{
         idPatient         INTEGER
       , nomPatient        CHAR(48)
       , prenomPatient     CHAR(48)
}
KEY {idPatient} ;

VAR Hopital BASE RELATION 
{
         idHopital         INTEGER
       , nomhopital        CHAR(48)
       , adresseHopital    CHAR(256)
}
KEY {idHopital} ;

Côté modélisation conceptuelle (MCD Merise), des gens sérieux et très au fait du métier, tels CinePhil, Escartefigue ou SQLpro vous suggéreraient de revoir l’attribut adresseHopital, et d’externaliser au moins le code postal. Cela dit, ça n’est pas l’objet de la normalisation, je n’en dirais donc pas plus sur les adresses.

En ce qui concerne la relvar Hospitalisation, tout dépend des règles de gestion des données que vous vous êtes fixées :

(1) Soit une personne n’est hospitalisée qu’une seule fois dans sa vie, auquel cas la structure de la table est la suivante (j’ai ajouté un attribut dateHospitalisation, car une hospitalisation sans la date qui va bien c’est un peu léger au plan sémantique (MCD Merise), même si au plan de la normalisation ça n’est pas le sujet) :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
VAR Hospitalisation BASE RELATION 
{
         idPatient              INTEGER
       , idHopital              INTEGER
       , dateHospitalisation    INTERVAL
}
KEY {idPatient}
FOREIGN KEY {idPatient} REFERENCES Patient {idPatient}
FOREIGN KEY {idHopital} REFERENCES Hopital {idHopital} ;

(2) Soit une personne peut être hospitalisée plus d’une fois (ce qui est plus réaliste), auquel cas la représentation est la suivante :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
VAR Hospitalisation BASE RELATION 
{
         idPatient              INTEGER
       , idHopital              INTEGER
       , dateHospitalisation    INTERVAL
}
KEY {idPatient, dateHospitalisation}
FOREIGN KEY {idPatient} REFERENCES Patient {idPatient}
FOREIGN KEY {idHopital} REFERENCES Hopital {idHopital} ;

En notant que le type INTERVAL permet de préciser une date de début et une date de fin d’hospitalisation (voire date et heure), avec les opérateurs permettant d’autoriser/interdire les recouvrements des intervalles.

Ce que dit la clé : existence d’une dépendance fonctionnelle {idPatient, dateHospitalisation} → {idHopital}, c'est-à-dire qu’on ne peut pas souffrir en même temps dans deux hôpitaux différents (sauf si l’intrication quantique est de mise )

Du point de vue de la modélisation, d’aucuns pourraient être tentés de définir une 2e clé, à savoir {idPatient, idHopital} mais il y aurait alors existence de la dépendance fonctionnelle {idPatient, idHopital} → {dateHospitalisation}, c'est-à-dire qu’une personne ne pourrait pas être hospitalisée deux fois dans le même hôpital, ce qui n’est pas très réaliste.

[fsmrel]

L'auteur a également quelques lacunes en orthographe :

La conception d'une base de données n'a rien de bien compliqué en sois

Une base de donnée en soie aurait adouci le propos

Tu as raison Capitaine !

En tout cas, ça n’est pas à nous que « l’auteur » va apprendre qu’en matière de bases de données la conception pose quand même quelques problèmes (ah ! ces fichues règles de gestion des données plus ou moins complètes et ambiguës, sinon contradictoires) ! Et qu’à l’accouchement il peut y avoir de fâcheuses surprises, si la vigilance s'est un tant soit peu relâchée...