Bien que la barre soit mine de rien placée assez haut, l’exemple proposé par SQLpro est intéressant à plus d’un titre :
— Au lieu de la seule et sempiternelle clé primaire, la relvar Dispensaire comporte plusieurs clés candidates.
— Cette relvar comporte une DF "mortelle" : {medecin, date consultation} → {salle d’auscultation}.
— La relvar viole la BCNF, c’est sûr, mais la normaliser conduirait à la perte d’une règle de gestion, comme on l’enseigne au sujet de la normalisation des relvars 3NF et non BCNF. En l'occurrence, il s'agit de la règle d'unicité exprimée par la 3e clé candidate : {salle d'auscultation, date consultation, heure consultation}.
Cela dit, la question suivante est posée :
Envoyé par
SQLpro
Que se passerait-il si l'on voulait changer la salle d'auscultation pour le médecin DUPONT à la date du 20/01/06 ?
Si l’on écrit de façon rationnelle, c'est-à-dire dans l’esprit relationnel (ensembliste), les choses de passent de façon tout à fait normale. Exemple de requête, en utilisant le style SQL :
1
2
3
4
| Update Dispensaire
Set Salle_auscultation = 'Rose'
Where medecin = 'DUPONT'
And date_consultation = '20/01/06' |
La salle Rose étant disponible, tout se passe bien, la mise à jour est effectuée.
Évidemment, si d’aventure on cherche à remplacer la salle Pervenche par la salle Tulipe, celle-ci n'étant pas libre, il doit y avoir rejet, puisque l’on violerait la règle de gestion exprimée par la 3e clé candidate :
1
2
3
4
| Update Dispensaire
Set Salle_auscultation = 'Tulipe'
Where medecin = 'DUPONT'
And date_consultation = '20/01/06' |
=> tentative de création d'une clé en double.
Donc, à la question :
Envoyé par
SQLpro
Proposez une nouvelle modélisation permettant d'éviter le problème
=> Je réponds : Quel problème ?
Indépendamment de cela, il est possible de normaliser, en décomposant Dispensaire en deux relvars. Je rappelle d’abord le théorème de Heath :
Soit la relvar R {A, B, C} dans laquelle A, B et C sont des ensembles d’attributs de R.
Si R satisfait à la DF : A → B, alors R est égale à la jointure de ses projections sur {A,B} et {A,C}.
Par application de ce théorème, la relvar Dispensaire peut être décomposée par projection selon les deux relvars :
R1 {D, M, S}, de clé candidate {D, M},
R2 {D, M, H, P} de clés candidates {D, M, H} et {D, P}.
L’intégrité référentielle permet de garantir que les couples {D, M} de R2 sont des couples de R1. Mais, on a perdu une règle de gestion, celle qui correspond à la 3e clé candidate. On peut bien sûr ajouter une relvar pour rattraper le coup :
R3 {S, D, H, P}, de clés candidates {S, D, H} et {D, P}.
Mais d’autres problèmes de cohérence se poseront. On ne fait que déplacer la difficulté, tout en compliquant les choses : le plus raisonnable est paradoxalement de violer la BCNF, à condition, pour garantir la DF mortelle, de fournir une assertion ad-hoc (ou un trigger si ça n’est pas possible).
Exemple d’ajout devant provoquer un rejet de la part du SGBD eu égard à la DF mortelle :
1
2
|
Insert Into Dispensaire Values (204, '20/01/06', '11:00', 'DUPONT', 'Tulipe') ; |
Pour que le rejet soit effectif, on peut utiliser un trigger du genre (cas de l’insertion, à aménager pour les modifications) :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| Create Trigger T1 On Dispensaire Instead of Insert As
Select 'T1'
From Inserted x
, Dispensaire y
Where x.medecin = y.medecin
And x.date_consultation = y.date_consultation
And x.salle_auscultation <> y.salle_auscultation ;
If @@Rowcount > 0
Begin
Raiserror ('Viol BCNF !',10,1)
Return
End
Insert Into Dispensaire
Select *
From Inserted
GO |
Note concernant les clés candidates, primaires et alternatives
Envoyé par
SQLpro
La relation Dispensaire à 3 clefs candidates :
1 - (N° patient, date consultation)
2 - (medecin, date consultation, heure consultation)
3 - (salle d'auscultation, date consultation, heure consultation)
Si nous choisissons (N° patient, date consultation) comme clef primaire de la relation...
A titre simplement d'information, je rappelle que le concept de clé primaire est, depuis une quinzaine d’années, mentionné dans la théorie relationnelle à titre purement historique. Il est vrai qu’à l’origine, Ted Codd mettait particulièrement en avant ce concept, mais dans un article paru dans InfoDB en 1993 (et repris dans http://www.amazon.com/Relational-Dat.../dp/0201824590 "The Primacy of Primary Keys: An Investigation", Chris Date explique qu’au nom du rasoir d’Ockham, clés primaires et alternatives peuvent être évacuées de la théorie et donc que les clés candidates suffisent à notre bonheur.
Je cite encore et traduis Chris Date : "Que l’on retienne une clé candidate pour être clé primaire, relève de considérations purement psychologiques, sortant du champ du Modèle relationnel." (The Relational Database Dictionary).
La phrase "Si nous choisissons..." n’apporte donc strictement rien pour l’exemple qui nous intéresse, lequel n'implique pas un langage en particulier, mais seulement le Modèle relationnel. A défaut, on pourrait penser que vous anticipez sur une relation clé primaire - clé étrangère entre les relvars R1 et R2 décrites plus haut, c'est-à-dire que l'on serait déjà au niveau SQL.
Envoyé par
SQLpro
1 - N° patient, date consultation → heure consultation, medecin, salle d'auscultation (clef primaire)
2 - medecin, date consultation, heure consultation → N° patient (clef candidate)
3 - salle d'auscultation, date consultation, heure consultation → medecin, N° patient (clef candidate)
Pour des raisons de symétrie, quitte à parler de la clé primaire (1re clé dans l'exemple), parler des 2e et 3e clés de l'exemple en tant que clés alternatives. Clé primaire et clé alternative peuvent être perçues comme des spécialisations de la clé candidate (toujours d’un point de vue psychologique...)
[NF]décomposition d'une entité non en FNBC
Répondre avec citation
Partager