Bonsoir Yeriko,
Sites et Niveaux
En relisant votre MCD, je me rends compte que les sites d’atterrissage et de décollage sont caractérisés par leur niveau : les entités-types SITE ATTERRISSAGE et SITE_DECOLLAGE doivent donc être débarrassées de l’attribut caractérisant le niveau et être mises en relation avec l’entité-type NIVEAU. Concernant le MLD, les attributs sont en place (tables SITE ATTERRISSAGE et SITE_DECOLLAGE), mais doivent donner lieu aux clés étrangères qui référencent la table NIVEAU (voir ci-dessous le code SQL qui doit être généré).
Pour bétonner la base de données, on pourra envisager la mise en oeuvre d’une assertion (ou d’un trigger dans le cas de SQL Server) pour interdire la présence dans la table VOL de valeurs de SiteDecolId et SiteAtterId incompatibles avec le niveau du pilote. On verra cela plus tard, quand le code SQL généré sera d’équerre.
Cardinalités 0,1
Considérons la règle 4 énoncée dans le document en question :
« Une association binaire de type 1:1 est traduite comme une association de type 1:N sauf que la clé étrangère se voit imposer une contrainte d’unicité en plus d’une éventuelle contrainte de non vacuité (cette contrainte d’unicité impose à la colonne correspondante de ne prendre que des valeurs distinctes). »
L’exemple qui est donné pour illustrer cette règle est le suivant :
http://www.fsmwarden.com/developpez/...n_Gruau_01.jpg
La traduction du MCD en MLD est la bonne car, outre que le fait que le bonhomme NULL ne peut pas se manifester dans l’attribut #n°employé de la table services, cette dernière comporte une clé étrangère {#n°employé} à laquelle est associé le commentaire « unique, non vide », ce qui revient à dire que {#n°employé} est clé alternative. Sous forme symbolique, le schéma ci-dessus donne lieu à :
Code:
1
2
3
4
5
6
7
8
|
employés {n°employé, nom}
PRIMARY KEY {n°employé} ;
services {n°service, nomservice, #n°employé}
PRIMARY KEY {n°service}
UNIQUE {#n°employé} ;
FOREIGN KEY {#n°employé} REFERENCES employés {n°employé} ; |
La règle 4 du document référencé comporte une 2e partie :
« Si les associations fantômes ont été éliminées, il devrait y avoir au moins un côté de cardinalité 0,1. C’est alors dans la table du côté opposé que doit aller la clé étrangère. Si les deux côtés sont de cardinalité 0,1, alors la clé étrangère peut être placée indifféremment dans l’une des deux tables. »
Je ne sais pas pourquoi une association peut être qualifiée de fantôme, peu importe, mais l’affirmation :
« Si les deux côtés sont de cardinalité 0,1, alors la clé étrangère peut être placée indifféremment dans l’une des deux tables. »
n’est pas acceptable, car outre qu’il faille favoriser de façon a priori aléatoire une table (on engendre ainsi un problème d’asymétrie), il se trouve plus fondamentalement qu’on impose la présence du bonhomme NULL.
A cette occasion, je note que la règle 2 formulée page 24 est contestable :
« Une association binaire de type 1:n disparaît, au profit d’une clé étrangère dans la table côté 0,1 ou 1,1 qui référence la clé primaire de l’autre table. Cette clé étrangère ne peut pas recevoir la valeur vide si la cardinalité est 1,1. »
En effet, si l’on procède ainsi dans le cas de la cardinalité 0,1, on impose là encore la présence du bonhomme NULL.
Au-delà des considérations théoriques, les conséquences pratiques de l’application de cette règle peuvent être désastreuses. J’ai audité — parmi bien d’autres ! —une application où l’on utilisait une table ARTICLE, un véritable « soleil » dont les rayons ciblaient une quinzaine de tables avec lesquelles elle entretenait des relations 0,1 / 0,N et pour laquelle la règle 2 avait été appliquée. La table ARTICLE comportait donc une quinzaine de d’attributs qui à 95% en moyenne étaient marqués NULL. Non seulement les performances en production étaient catastrophiques lors des mises à jour (autant d’index à mettre à jour), mais fonctionnellement cette table ARTICLE n’avait aucune existence légitime... Je n’irai pas plus loin, mais retenez que les cardinalités 0,1 ne doivent pas être traitées de façon simpliste.
Code SQL généré
Citation:
Voici le copié collé de ce que me pond Analyse SI pour le SQL.
Plutôt qu’un copié collé, je préférerais le code généré lui-même (utilisez la balise CODE).
Ceci dit :
Le bonhomme NULL est omniprésent : veuillez ajouter la clause NOT NULL pour chaque attribut de chaque table.
Le type de données uniqueidentifier (version SQL Server du Universally Unique Identifier (UUID) si je ne m’abuse) est mal venu. En effet, un seul pilote pourra porter un certain nom, être prénommé d’un certain prénom, etc. Et encore les valeurs correspondantes seront bizarroïdes (voyez l’article de SQLpro à propos des doublons, au paragraphe 2.1.2).
Le code généré pour la table PILOTE doit ressembler à ceci (même punition, même motif pour l’ensemble des tables) :
Code:
1
2
3
4
5
6
7
8
9
10
| CREATE TABLE PILOTE
(
PiloteId Int NOT NULL,
PiloteNom Varchar(48) NOT NULL,
PilotePrenom Varchar(48) NOT NULL,
PiloteAdresse Varchar(48) NOT NULL,
PiloteTelephone Varchar(16) NOT NULL,
PilotePoids Int NOT NULL
) ;
ALTER TABLE PILOTE ADD CONSTRAINT PILOTE_PK PRIMARY KEY (PiloteId) ; |
Analyse SI a généré un cycle entre les tables PILOTE et FONCTION : conformément à ce dont nous sommes convenus dans les discussions précédentes, virer l’attribut FonctionId de la table PILOTE, virer l’attribut PiloteId de la table FONCTION et mettre en œuvre la table EXERCER :
Code:
1
2
3
4
5
6
7
8
9
10
11
| CREATE TABLE EXERCER
(
PiloteId Int NOT NULL,
FonctionId Int NOT NULL,
CONSTRAINT EXERCER_PK PRIMARY KEY (PiloteId),
CONSTRAINT EXERCER_AK UNIQUE (FonctionId),
CONSTRAINT EXERCER_FONCTION_FK FOREIGN KEY (FonctionId)
REFERENCES Fonction (FonctionId),
CONSTRAINT EXERCER_PILOTE_FK FOREIGN KEY (PiloteId)
REFERENCES Pilote (PiloteId)
) ; |
En conséquence des remarques que j’ai faites à propos des sites d’atterrissage, de décollage et des niveaux, le code généré pour les tables SITE_ATTERRISSAGE et SITE_DECOLLAGE devient (je n’ai pas fait figurer tous les attributs) :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| CREATE TABLE SITE_ATTERRISSAGE
(
SiteAtterId Int NOT NULL,
NiveauId Int NOT NULL,
SiteAtterNom Varchar(48) NOT NULL,
CONSTRAINT SITE_ATTERRISSAGE_PK PRIMARY KEY (SiteAtterId),
CONSTRAINT SITE_ATTERRISSAGE_NIVEAU_FK FOREIGN KEY (NiveauId)
REFERENCES Niveau (NiveauId)
) ;
CREATE TABLE SITE_DECOLLAGE (
SiteDecolId Int NOT NULL,
NiveauId Int NOT NULL,
SiteDecolNom Varchar(48) NOT NULL,
CONSTRAINT SITE_DECOLLAGE_PK PRIMARY KEY (SiteDecolId),
CONSTRAINT SITE_DECOLLAGE_NIVEAU_FK FOREIGN KEY (NiveauId)
REFERENCES Niveau (NiveauId)
) ; |
Citation:
sachant que la contrainte d'exclusion entre Inscrire et Encadrer n'est pas prise en compte ; je n'ai pas réussi à la créer (j'ai retouché avec Paint...)
Les outils ne génèrent pas directement ce type de contrainte quand ils ne l’intègrent pas dans leur métamodèle. J’ai fait comme vous, en dessinant moi-même la contrainte d’exclusion. Pour la mettre en œuvre, on agit donc directement au niveau du code SQL. On verra cela un peu plus tard.
En revanche, certaines contraintes sont faciles à mettre en œuvre et il ne faut pas s’en priver. Supposons par exemple qu’une date de début de stage doive être antérieure à la date de fin du stage : on met en œuvre cette contrainte directement dans le corps de l’instruction CREATE TABLE :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
| CREATE TABLE STAGE
(
StageId Int NOT NULL,
BrevetId Int NOT NULL,
DateDebut Datetime NOT NULL,
DateFin Datetime NOT NULL,
Prix Int NOT NULL,
CONSTRAINT STAGE_PK PRIMARY KEY (StageId),
CONSTRAINT STAGE_BREVET_FK FOREIGN KEY (BrevetId)
REFERENCES Brevet (BrevetId),
CONSTRAINT STAGE_CK1 CHECK (DateDebut < DateFin)
) ; |
De la même façon, à supposer que les attributs StatutPaiement et InscriptionExam de la table INSCRIRE correspondent à des booléens (vrai/ faux, oui/non, 1/0, ...), on mettra en œuvre les contraintes correspondantes directement dans le corps de l’instruction CREATE TABLE :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| CREATE TABLE INSCRIRE
(
PiloteId Int NOT NULL,
StageId Int NOT NULL,
StatutPaiement Char(1) NOT NULL,
InscriptionExam Char(1) NOT NULL,
CONSTRAINT SUIVRE_PK PRIMARY KEY (PiloteId, StageId),
CONSTRAINT SUIVRE_PILOTE_FK FOREIGN KEY (PiloteId)
REFERENCES Pilote (PiloteId),
CONSTRAINT SUIVRE_STAGE_FK FOREIGN KEY (StageId)
REFERENCES Stage (StageId),
CONSTRAINT SUIVRE_CK1 CHECK (StatutPaiement IN ('v', 'f')),
CONSTRAINT SUIVRE_CK2 CHECK (InscriptionExam IN ('v', 'f'))
) ; |
Bon courage à vous.
