Discussion :

[Gaetch]

Bonjour j'ai une table VILLE qui contient une id, un nom et un code postal. Je me demandais si l'id était un choix vraiment judicieux ici, ou s'il ne valait pas mieux tout simplement metre la clé composée cp-nom en clé primaire ?

[CinePhil]

On ne le répétera jamais assez : Une clé primaire est un entier auto-incrémenté !
La clé primaire sert aux clés étrangères dans les autres tables. Si c'est pour remettre dans les tables qui ont besoin de la ville le nom de celle-ci, autant ne pas faire de table ville !

Au fait, au passage, il y a beaucoup de villes qui ont plus d'un code postal. Ta table ne serait-elle pas plutôt une table des codes postaux ?
Auquel cas il faudrait qu'elle ait une clé étrangère portant l'identifiant de la table ville.
Et bien sûr l'inverse est probablement vrai aussi : un code postal peut couvrir plusieurs communes.

Allez, deux liens qui te seront sans doute utiles :
http://sqlpro.developpez.com/cours/normes/#L3.1.2
http://sqlpro.developpez.com/optimis...SQLserver3.pdf
Voir notamment page 20 pour le second.

[millie]

On ne le répétera jamais assez : Une clé primaire est un entier auto-incrémenté !
La clé primaire sert aux clés étrangères dans les autres tables. Si c'est pour remettre dans les tables qui ont besoin de la ville le nom de celle-ci, autant ne pas faire de table ville !

Ca me semble pas forcement si évident que ça. Notamment car on perd le troisième forme normale.

[CinePhil]

Oui OK, la clé primaire d'une table associative est composée des clés primaires des tables entrant en jeu dans l'association.
N'empêche que dans le cas précis demandé ici, ce ne sera de toute façon pas le nom de la ville et le code postal.

[Gaetch]

Je suis d'accord sur le fait que la clé est mieux en entier c'était débile de ma part de vouloir mettre le couple CP+nom ville en id pour les clés étrangère j'y avais pas pensé...

Bon après c'est l'auto-incrémenté que j'aime pas beaucoup, notamment à cause du fait qu'on ne peut pas récuperer l'id d'un enregistrement qu'on vient d'effectuer.

[CinePhil]

Bon après c'est l'auto-incrémenté que j'aime pas beaucoup, notamment à cause du fait qu'on ne peut pas récuperer l'id d'un enregistrement qu'on vient d'effectuer.

Quelle est la base de donnée ?
En PHP/MySQL c'est tout à fait possible. Il suffit de faire appel à la fonction mysql_insert_id(identifiant de session MySQL)

[Gaetch]

Et si jamais je change de SGBD ? non je préfère faire quelque chose qui sera indépendant et fonctionnera à tous les coups, par exemple un compteur puis utiliser des transactions.

D'autant plus qu'avec la méthode que tu donnes, si deux utilisateurs font un insert en même temps, l'un des deux recevra une valeur erronnée (enfin pas à tous les coups c'est chaud d'avoir d'eux enregistrements exactement en même temps mais c'est un risque, certes minime, mais possible que je prendrai pas)

[CinePhil]

D'autant plus qu'avec la méthode que tu donnes, si deux utilisateurs font un insert en même temps, l'un des deux recevra une valeur erronnée (enfin pas à tous les coups c'est chaud d'avoir d'eux enregistrements exactement en même temps mais c'est un risque, certes minime, mais possible que je prendrai pas)

Faux !
Tu as bien lu l'argument qui est passé à la fonction ?

Scénario :
1) Toto insère une ligne dans la table. S'ensuit un traitement un peu long.
2) Titi insère une ligne dans la table pendant que le traitement de Toto continue de s'effectuer
3) Le traitement de Toto a besoin du dernier id qu'il a inséré. Comme l'argument passé est la connexion de Toto à MySQL et pas celle de Titi, le traitement de Toto va bien récupérer sa propre dernière insertion et pas celle de Titi.

[Gaetch]

Okay là d'accord, personnellement je parlais de manière générale pas pour MySQL.

[millie]

Faux !
Tu as bien lu l'argument qui est passé à la fonction ?

Scénario :
1) Toto insère une ligne dans la table. S'ensuit un traitement un peu long.
2) Titi insère une ligne dans la table pendant que le traitement de Toto continue de s'effectuer
3) Le traitement de Toto a besoin du dernier id qu'il a inséré. Comme l'argument passé est la connexion de Toto à MySQL et pas celle de Titi, le traitement de Toto va bien récupérer sa propre dernière insertion et pas celle de Titi.

Mais quand c'est embarqué dans une application, il est possible que ce soit le même identifiant (je n'ouvre pas une connexion par traitement métier).

EDIT : Enfin, je fais pas de php, alors je suis pas sûr à 100% de ce que je dis

[CinePhil]

Mais quand c'est embarqué dans une application, il est possible que ce soit le même identifiant (je n'ouvre pas une connexion par traitement métier).

Ben p'têt' que c'est pas une bonne idée !

[fsmrel]

Bonjour,

Je me demandais si l'id était un choix vraiment judicieux ici, ou s'il ne valait pas mieux tout simplement metre la clé composée cp-nom en clé primaire ?

Indépendamment de ce qui vous a été dit dans cette discussion, voici ce qu’on demande à la clé primaire PK d’une table T :

— Respecter les contraintes d’unicité et d’irréductibilité (ceci est obligatoire) :

Unicité : Deux lignes distinctes de T ne peuvent prendre la même valeur pour PK ;

Irréductibilité : il n’existe pas de sous-ensemble strict de PK vérifiant la contrainte d’unicité.

— Être invariante (en théorie ceci n’est pas obligatoire, mais ça l’est en pratique) :

Dans le temps, la valeur de PK ne peut pas être remplacée. En corollaire, cela veut dire que l’utilisateur dans l’entreprise (et a fortiori tout système externe à l’entreprise) ne peut avoir de prise sur la clé primaire.

Ainsi, la paire {code postal, nom} garantit les contraintes d’unicité et d’irréductibilité (une commune peut avoir plusieurs codes postaux, un code postal peut recouvrir plusieurs communes).

Quant à l’invariance elle n’est pas assurée. En conséquence, la paire {code postal, nom} n’est pas à retenir comme clé primaire, mais alternative (contrainte UNIQUE de l’instruction CREATE TABLE).

Une clé primaire est un entier auto-incrémenté

Ceci correspond à une technique assez répandue, mais il en existe d’autres pour valoriser une clé primaire. Par exemple si le système d’exploitation fournit des valeurs de timestamps calés sur la microseconde, voire la nanoseconde, on dispose là d’une possibilité fort intéressante. D’autre part, concernant les entiers, on peut préférer des valeurs attribuées selon le mode aléatoire plutôt que séquentiel : dans la mesure où les lignes des tables sont (malheureusement) stockées telles quelles au niveau physique, il peut se produire des problèmes de contention que le hachage permet d’évacuer.

En outre, à supposer qu’on ait utilisé un entier auto-incrémenté pour la clé primaire F# de la table F des fournisseurs et pour la clé primaire P# de la table P des produits, la clé primaire des la table FP des associations entre fournisseurs et produits sera composée de la paire {F#,P#} et non pas d’un singleton issu d’un attribut de type entier, auto-incrémenté, inventé pour la cause et inessentiel (donc candidat au rasoir d'Ockham).

Ca me semble pas forcement si évident que ça. Notamment car on perd le troisième forme normale.

Quelle est votre définition de la 3NF ?

Quoi qu'il en soit, la définition donnée par son inventeur, Ted Codd, repose sur le concept de clé candidate, dont la clé primaire n’est qu’un cas particulier :

« A relation R is in third form normal if it is in second normal form and every non-prime attribute of R is non-transitively dependant on each candidate key of R. »

(Un prime attribute, est un attribut participant à au moins une clé candidate.)

En conséquence de quoi, la table dont l’en-tête est le suivant :

VILLE {Id, CodePostal, NomCommune, ...}

et ayant pour clés candidates le singleton {Id} et la paire {CodePostal, NomCommune} respecte la 3NF (sous réserve, par exemple, que VILLE ne contienne pas des sous-ensembles A et B d’attributs non-clés (non-prime) tels que A → B).

Bon après c'est l'auto-incrémenté que j'aime pas beaucoup, notamment à cause du fait qu'on ne peut pas récuperer l'id d'un enregistrement qu'on vient d'effectuer.

A priori, la valeur de l’Id n’offre pas d’intérêt du point de vue de l'utilisateur. Mais si vous tenez à en récupérer la valeur pour une paire {CodePostal, NomCommune} donnée, vous pouvez toujours coder

SELECT Id FROM VILLE WHERE CodePostal = x AND NomCommune = y

D'autant plus qu'avec la méthode que tu donnes, si deux utilisateurs font un insert en même temps, l'un des deux recevra une valeur erronnée

Que voulez-vous dire par "valeur erronée" ? Un SGBD digne de ce nom gère, entre autres, un système de verrouillage interdisant les doublons. En tout état de cause, CinePhil explique bien les choses.

Mais quand c'est embarqué dans une application, il est possible que ce soit le même identifiant (je n'ouvre pas une connexion par traitement métier).

Au besoin, la clé primaire doit être dotée d’un attribut supplémentaire, identifiant par exemple le sous-système hébergeant l’application, ou autre artifice identifiant (respectant évidemment le principe d'invariance des clés primaires).

[millie]

Quelle est votre définition de la 3NF ?

Quoi qu'il en soit, la définition donnée par son inventeur, Ted Codd, repose sur le concept de clé candidate, dont la clé primaire n’est qu’un cas particulier :

« A relation R is in third form normal if it is in second normal form and every non-prime attribute of R is non-transitively dependant on each candidate key of R. »

(Un prime attribute, est un attribut participant à au moins une clé candidate.)

En conséquence de quoi, la table dont l’en-tête est le suivant :

VILLE {Id, CodePostal, NomCommune, ...}

et ayant pour clés candidates le singleton {Id} et la paire {CodePostal, NomCommune} respecte la 3NF (sous réserve, par exemple, que VILLE ne contienne pas des sous-ensembles A et B d’attributs non-clés (non-prime) tels que A → B).

Quand je disais troisième forme normale, je pensais notamment à la propriété : "tous les attributs n'appartenant pas à la clé ne dépendent pas d'un attribut non-clé."

Si (CodePostal, NomCommune) est unique et si ce n'est pas la clef. Alors la table :
Id, CodePostal, NomCommune, NbHabitant ne serait pas en troisième forme normale car NbHabitant dépend du couple : (CodePostal, NomCommune) au sens que si on connait le CodePostal et la NomCommune, on trouve nécessairement le nombre d'habitant.

[fsmrel]

Quelle est votre définition de la 3NF ?

Quoi qu'il en soit, la définition donnée par son inventeur, Ted Codd, repose sur le concept de clé candidate, dont la clé primaire n’est qu’un cas particulier :
« A relation R is in third form normal if it is in second normal form and every non-prime attribute of R is non-transitively dependant on each candidate key of R. »

(Un prime attribute, est un attribut participant à au moins une clé candidate.)
En conséquence de quoi, la table dont l’en-tête est le suivant :
VILLE {Id, CodePostal, NomCommune, ...}
et ayant pour clés candidates le singleton {Id} et la paire {CodePostal, NomCommune} respecte la 3NF (sous réserve, par exemple, que VILLE ne contienne pas des sous-ensembles A et B d’attributs non-clés (non-prime) tels que A → B).

Quand je disais troisième forme normale, je pensais notamment à la propriété : "tous les attributs n'appartenant pas à la clé ne dépendent pas d'un attribut non-clé."

La propriété dont vous faites mention est approximative, non conforme à ce qu'écrit Codd, et il faudrait la remplacer par :

"tous les attributs n'appartenant pas à une clé ne dépendent pas d'un attribut non-clé."

A défaut, vous aboutiriez à une contradiction.

Je développe. La paire {CodePostal, NomCommune} n’est pas retenue comme clé primaire de la table VILLE, mais elle n’en est pas moins clé candidate, en l’occurrence alternative. Par ailleurs, la définition de la 3NF ne peut pas faire l'impasse sur les clés candidates, sinon, il y aurait une contradiction avec la BCNF (forme normale de Boyce-Codd), dont l’énoncé est le suivant (voyez par exemple Fagin ou Ullman) :

Une table T est en forme normale de Boyce-Codd (BCNF) si et seulement si les seules dépendances fonctionnelles non triviales auxquelles elle satisfait sont de la forme X → Y, où X représente une surclé et Y un sous-ensemble d’attributs de l’en-tête de T.

Une surclé désigne un ensemble d’attributs de T respectant la propriété d’unicité (ce dont j’ai fait mention dans mon précédent message), sans avoir à respecter obligatoirement la propriété d’irréductibilité (idem). Une clé candidate n’est alors qu’une surclé spécialisée, dans la mesure où elle respecte en plus cette propriété d'irréductibilité.
On a vu que {Id} et {CodePostal, NomCommune} sont des clés candidates, donc des surclés. Voici quelques unes des dépendances fonctionnelles associées à la table

VILLE {Id, CodePostal, NomCommune, NbHabitants}

(je ne vais pas toutes les énumérer...) :

DF01 : {Id} → {CodePostal}
DF02 : {Id} → {NomCommune}
DF03 : {Id} → {NbHabitants}
DF04 : {Id} → {Id}
DF05 : {Id} → {CodePostal, NomCommune}
DF06 : {Id} → {CodePostal, NomCommune, NbHabitants}
DF07 : {CodePostal, NomCommune} → {CodePostal}
DF08 : {CodePostal, NomCommune} → {NomCommune}
DF09 : {CodePostal, NomCommune} → {NbHabitants}
DF10 : {CodePostal, NomCommune} → {Id}
DF11 : {CodePostal, NomCommune} → {Id, NbHabitants}
DF12 : {CodePostal} → {CodePostal}
DF13 : {NomCommune} → {NomCommune}
DF14 : {NbHabitants} → {NbHabitants}
Etc.

Sont triviales les DF dont le dépendant est inclus dans le déterminant (inclusion non stricte). Ainsi, les dépendances fonctionnelles DF04, DF07, DF08, DF12, DF13, DF14 sont triviales.
Les sous-ensembles {Id} et {CodePostal, NomCommune} donnent lieu à surclés (du fait des dépendances fonctionnelles DF06 et DF11). Par application des axiomes d’Armstrong et calcul de la fermeture de la table VILLE, on vérifie qu’il n’y a pas d’autre sous-ensemble donnant lieu à surclé.

Ainsi, la table VILLE respecte la BCNF.

Maintenant, voici une définition équivalente de la BCNF, on la doit à Carlo Zaniolo :

Soit T une table, X un sous-ensemble d’attributs de l’en-tête de T et A un attribut de cet en-tête. T est en forme normale de Boyce-Codd (BCNF) si et seulement si, pour chaque dépendance fonctionnelle X → {A} vérifiée dans T, au moins une des conditions suivantes est satisfaite :

1. Cette dépendance fonctionnelle est triviale (A est un élément de X).

2. X est une surclé de T.

Zaniolo donne par ailleurs la définition suivante de la 3NF :

Soit T une table, X un sous-ensemble d’attributs de l’en-tête de T et A un attribut de cet en-tête. T est en troisième forme normale (3NF) si et seulement si, pour chaque dépendance fonctionnelle X → {A} vérifiée dans T, au moins une des conditions suivantes est satisfaite :

1. Cette dépendance fonctionnelle est triviale (A est un élément de X).

2. X est une surclé de T.

3. A appartient à une clé candidate de T.

Vous observerez qu’il suffit de supprimer la condition 3 pour retrouver la définition de la BCNF. Autrement dit, une table peut ne pas respecter la BCNF tout en respectant la 3NF (à cause de cette condition 3), mais si cette table respecte la BCNF, elle respecte nécessairement la 3NF :

=>

Puisque la table VILLE respecte la BCNF, elle respecte donc la 3NF.

[millie]

Et bien merci beaucoup pour les explications.

[fsmrel]

Bonsoir,

Quand je disais troisième forme normale, je pensais notamment à la propriété : "tous les attributs n'appartenant pas à la clé ne dépendent pas d'un attribut non-clé."

La propriété dont vous faites mention est approximative, non conforme à ce qu'écrit Codd, et il faudrait la remplacer par :
"tous les attributs n'appartenant pas à une clé ne dépendent pas d'un attribut non-clé."
A défaut, vous aboutiriez à une contradiction.

Et encore, je n’ai pas tout dit...
En effet, il faudrait en réalité remplacer :

"ne dépendent pas d'un attribut non-clé."

par

"ne dépendent pas d'un sous-ensemble d’attributs non-clés",

expression à peu près aussi maladroite que les précédentes, mais quand même plus pertinente. En effet, considérons l’en-tête de table suivant (dans lequel j’ai repris l’attribut NbHabitants que vous aviez proposé pour la table VILLE) :

Fournisseur {FourId, NomCommune, CodePostal, NbHabitants}

La table a pour clé (candidate ou primaire, à vous de choisir...) {FourId} et son prédicat est :

Le fournisseur FourId, est localisé dans la commune de nom NomCommune et de code postal CodePostal, et dont le nombre d’habitants est NbHabitants.

Les dépendances fonctionnelles non triviales sont les suivantes :

DF01 : {FourId → NomCommune}
DF02 : {FourId → CodePostal}
DF03 : {FourId → NbHabitants}
DF04 : {NomCommune, CodePostal} → {NbHabitants}

Et si je reprends les définitions de Zaniolo de la BCNF et de la 3NF, du fait de DF04, non seulement la BCNF est violée, mais aussi la 3NF (ce qui, pour les besoins de la cause, est évidemment intentionnel de ma part). Or, c’est bien le sous-ensemble d’attributs {NomCommune, CodePostal} qui est la cause du viol et non pas un attribut en particulier, puisque les DF {NomCommune} → {NbHabitants} et {CodePostal} → {NbHabitants} n’existent pas.