Questions sur la création d'une BDD en SQL (débutant)

Bonjour à tous, j'ai besoin de créer une BDD afin d'y travailler plus tard à l'aide de Delphi.

J'ai donc fait mon MCD, mais je ne suis vraiment pas sur de son éficacité. =/

Voici la création de mes tables :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

Create table Site (Site_ID integer, Site_Nom CHAR(25), Site_Service CHAR(15), CONSTRAINT Pk_Site primary key(Site_Nom));   Create table SERVICE (Service_ID integer, Service_Nom CHAR(15), CONSTRAINT Pk_Service primary key(Service_Nom));   Create table Personnel (Pers_ID integer, Pers_Nom CHAR(25), Pers_Prenom CHAR(25), Pers_CA integer, Pers_EM integer, Pers_Site, Pers_Service, CONSTRAINT Pk_Personnel primary key(Pers_Nom), CONSTRAINT Fk_PersSite foreign key(Pers_Site) references SITE(Site_Nom), CONSTRAINT Fk_PersService forgein key(Pers_Service) references SERVICE(Service_Nom));   Create table Absence (Abs_ID integer, Abs_Date datetime, CONSTRAINT Pk_Absence primary key(Abs_ID));   Create table TYPE (Type_ID integer, Type_Nom CHAR(34), CONSTRAINT Pk_Type primary key(Type_Nom));   Create table Liste (List_ID integer, List_Site CHAR(25), List_Service CHAR(15), List_PersNom CHAR(25), List_PersPrenom CHAR(25), List_Abs Datetime, List_Type CHAR(34), CONSTRAINT Pk_Liste primary key(List_ID), CONSTRAINT Fk_ListSite foreign key(List_Site) references SITE(Site_Nom), CONSTRAINT Fk_ListService foreign key(List_Service) references SERVICE(Service_Nom), CONSTRAINT Fk_ListPersNom foreign key(List_PersNom) references PERSONNEL(Pers_Nom), CONSTRAINT Fk_ListPersPrenom foreign key(List_PersPrenom) references PERSONNEL(Pers_Prenom), CONSTRAINT Fk_ListAbs foreign key(List_Abs) references ABSENCE(Abs_Date), CONSTRAINT Fk_ListType foreign key(List_Type) references TYPE(Type_Nom));

---

Sachant que :

Un salarié peut être absent à une date.
Une absence appartient à un type.
Un salarié est rataché à un site.
Un site contient un ou plusieurs services.

Mon problème est là!
J'ai créé une table liste afin de pouvoir y stocké une ou deux information de chaque table, les faire ressortirs et enfin les imprimmer (Sous Delphi).
Mais est-il correct d'utiliser les foreign keys comme celà?
Est-ce que sa marchera?
J'aimerais avoir votre avis sur mon code SQL car je suis sur qu'il n'est pas bon, qu'il manque quelquechose ou que cette table Liste est une c******e.:?

Merci pour vos futures réponses! :P

bonjour,

J'ai regardé succinctement vos scripts.
Comment faites vous le lien entre une absence et une personne ?

En ce qui concerne la table Liste, ne serait il pas judicieux d'utiliser les vues pour effectuer ce travail. En effet il va vous devenir vite fastidieux de de gerer l'intégrité de vos données.

Bon courage

Pour gérer une absence d'un salarié, j'ai pensé à insérer la valeur "ABS" dans la table "PERSONNEL", mais la limite serait qu'un salarié ne pourrait avoir qu'une absence. Juste?

Deuxième possibilité, insérer cette absence dans l'association qui relierait PERSONNEL et ABSENCE. Possible?
--> Si c'est le cas, comment puis-je créer cette association qui générerait une valeur (Pour Personnel ET pour Absence) en code SQL? Foreign Key?

Pour le cas de ma table LISTE, j'aimerais trouver un autre moyen de générer une liste contenant les clefs primaires des autres tables, mais comment?

Yanika_bzh, merci pour votre réponse.

Et merci d'avance aux autres futures réponses, qui seront nombreuses je l'espère! :P

salut
j ai décelé quelques petits problèmes dans ton schéma relationnel
- au niveau des clé primaire il est plus judicieux de mettre l'identifiant en clé primaire plutôt que le nom pour éviter tout problème de doublons à moins que tu sois vraiment sur qu'il y en aura aucun (ce qui m'étonnerait surtout pour la table personnel !)

- tu dis "Une absence appartient à un type." mais tu ne fais pas ressortir l'intégrité référentiel (tu devrais le traduire par la présence d'une clef étrangère dans ta table absence)

- je cite : "Un site contient un ou plusieurs services" mais est ce qu'un service est présent sur plusieurs sites ? si oui il ya association porteuse de données

sinon pour la table liste je pense que l'idée de la vue n'est pas mal à moins que tu desires reelement cette table au sein de ta base mais à ce moment la il faudrait remplacer ces deux attributs
List_PersNom CHAR(25),
List_PersPrenom CHAR(25),
par List_PersId pour suivre la logique de ta table

voila....en espérant t avoir aider :king:

Citation:

---

J'ai donc fait mon MCD

---

Où est-il ?
Le présenter permettrait de lever quelques ambiguïtés telle celle qui est évoquée par Snipes : un service est-il en relation avec un seul site ou avec plusieurs ? Etc.

Utilisez par exemple Toad Data Modeler à cet effet ou tout autre outil qui vous est familier (Toad DM produit plus un MLD qu’un MCD, mais disons que cela peut suffire ici).

Sinon, la table Liste n’a pas de légitimité dans un MCD. A la limite, il s’agit d’une vue, ou d’un snapshot. En vertu de la 3e forme normale (qui n’a pas que des avantages théoriques), elle vole en éclat. Sa mise en œuvre part d’une bonne intention, mais elle risque de créer plus de problèmes qu’elle n’en résout.

Présentez un MCD, en évacuant Liste, on y verra plus clair.

Tout d'abord merci pour vos réponses!

Snipes, en effet un même service peut être présent sur plusieurs sites, par exemple : Le service Crêche est présent dans tel village mais aussi dans les autres.

Mais quelles données seront portées par cette association?

Pour ce qui est des Identifiants, j'ai tout modifier et la table liste, ainsi que la table Historique (que j'ai rajoutée récemment à cause de nouvelles idées qui ont été apportées pour le programme) n'appellent en clefs étranges plus que des ID, et les autres tables n'ont pour clef primaires que les Identifiants de leur table.

fsmrel, pour mon MCD, je l'ai créer à partir de PowerAMC, mais je ne connais pas très bien ce programme, et je n'aime pas le code SQL qu'il peut générer.

Mais pour la table liste, je suis d'accord qu'elle risque de poser problème, mais je ne vois pas comment sortir les mêmes résultats sans table.

Voici mon MCD sans la table LISTE, ni la nouvelle table HISTORIQUE (qui va me poser les mêmes problèmes que la table LISTE (car elle fera appel à toutes les autres tables).

PS: MCD en pièce jointe, je n'arrive pas à simplement l'afficher avec mon post.

Bonsoir CleeM,

Citation:

---

Envoyé par CleeM

un même service peut être présent sur plusieurs sites, par exemple : Le service Crêche est présent dans tel village mais aussi dans les autres.
Mais quelles données seront portées par cette association?

---

Les données portées par cette association seront celles qui ont un sens pour le couple {Site, Service} et non pas que pour un seul partenaire de ce couple.
L’association Posséder est une relation de plusieurs à plusieurs. Par définition, elle a pour identifiant le couple des identifiants de Site et de Service : {Site_Id, Serv_Id} (ou dans l’autre ordre des noms). Posséder a donc déjà pour attributs Site_Id et Serv_Id, même si cela n’apparaît pas sur le MCD. En revanche, cela apparaîtra au stade suivant (étape MPD dans le cas de Power AMC), ainsi que dans le script généré.
Supposons en plus que l’on ait à gérer la date à laquelle le service a été rattaché à un site, cela fera l’objet d’un attribut porté par l’association, puisque cette date n’a de sens qu’en fonction du site et du service.

Le choix du nom des associations doit être sémantiquement acceptable : l’association Posséder mériterait de changer de nom et être nommée par exemple Héberger (un site héberge des services).

Entité-type Site : Que vient faire l’attribut Site_Serv ? Comme on vient de le dire, tout ce qui concerne un couple {Site, Service} doit être pris en compte dans l’association Posséder (Héberger comme on vient de le dire).


Partie correspondante du MCD :



http://img242.imageshack.us/img242/1...servmcdbv8.jpg



Étape suivante : MLD (MPD selon PowerAMC) :



http://img118.imageshack.us/img118/7...servmldfl2.jpg


D’où le script SQL produit par PowerAMC :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Create Table Service ( Serv_Id Int Not null, Serv_Nom Varchar(48) Not null, Constraint PK_Service Primary Key (Serv_Id) ) ;   Create Table Site ( Site_Id Int Not null, Site_Nom Varchar(48) Not null, Constraint PK_Site Primary Key (Site_Id) ) ;   Create Table Heberger ( Site_Id Int Not null, Serv_Id Int Not null, Date_Hebergement Datetime Not null, Constraint PK_Heberger Primary Key (Site_Id, Serv_Id), Constraint FK_Heberger_Heberger_Site Foreign Key (Site_Id) References Site (Site_Id), Constraint FK_Heberger_Heberger_Service Foreign Key (Serv_Id) References Service (Serv_Id) ) ;

---

Concernant l’entité-type Personnel. Il faut la renommer en Personne. En effet on modélise une personne, laquelle a pour attributs un nom, un prénom, etc. : le personnel n’a pas de prénom mais représente une collection de personnes et ça n’est pas lui que l’on modélise en tant que tel. Par ailleurs, les attributs Pers_SiteId et Pers_ServId doivent être évacués du MCD. En effet, ils représentent le site et le service de la personne, or au niveau MCD ces attributs existent en puissance par le canal des associations établies avec les entités-types Site et Service. En outre, puisque manifestement une personne fait partie d’un service, il faut représenter l’association entre Personne et Service.



http://img242.imageshack.us/img242/6...persmcdid1.jpg



Vous pouvez constater qu’à l’étape suivante, les attributs de Personne sont au complet :



http://img242.imageshack.us/img242/4...persmldev6.jpg



Je vous laisse générer les scripts et compléter les modèles en ce qui concerne les absences des personnes. Si problème, n’hésitez pas.

A noter en passant que selon cette modélisation, une personne pourrait très bien travailler sur un site qui n’a rien à voir avec son service. Pour s’en sortir avec Power AMC il faudrait utiliser la notation entité/relation plutôt que la notation Merise (Outils \ Option du modèle \ Notation), mais quand il n’y aura plus que cela à régler, vous aurez bien progressé par ailleurs...

Le but de la manœuvre étant d’arriver à ceci au niveau tabulaire :



http://img242.imageshack.us/img242/9...persmldzz5.jpg

Et bien fmsrel, je ne sais pas comment vous remercier!

Voici une aide complète qui va m'être plus qu'utile, en effet je vais reprendre toute ma base de donnée (créée "à la main") et mon MCD.
Peut-être même généré le code SQL à partir de Power AMC directement et voir ce que sa donne (vu que mon MCD sera bon).
Car je me souviens avoir généré du code SQL à partir de Power AMC et il était bizarre. :p

Merci encore!!!

PS: Voici mon nouveau MCD qui est bien plus clair (je n'ai pas mis la Date d'Hébergement entre Service & Site car ce n'est pas nécessaire dans le cadre de l'utilisation du futur programme). Petite question : L'association Avoir (Personne/Absence) peut-elle être porteuse d'information (sachant qu'une absence d'ID 1 ne concernera qu'une et une seule personne, donc en ayant comme cardinalités d'Absence à Personne : 1,1 et de Personne à Absence 0,N) ? Il me semble que ce n'est pas le cas, donc si nécessaire je laisserais comme cardinalités : 0,N & 0,N.

PS2: Et mon nouveau code :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

CREATE TABLE SITE (Site_ID integer, Site_Nom CHAR(25), CONSTRAINT PK_Site PRIMARY KEY (Site_ID));   CREATE TABLE SERVICE (Serv_ID integer, Serv_Nom CHAR(15), CONSTRAINT PK_Service PRIMARY KEY (Serv_ID));   CREATE TABLE HEBERGER (Site_ID integer, Serv_ID integer, CONSTRAINT PK_Heberger PRIMARY KEY (Site_ID, Serv_ID), CONSTRAINT FK_Heberger_Site FOREIGN KEY (Site_ID) REFERENCES Site (Site_ID), CONSTRAINT FK_Heberger_Service FOREIGN KEY (Serv_ID) REFERENCES Service (Serv_ID));   CREATE TABLE PERSONNE (Pers_ID integer, Pers_Nom CHAR(25), Pers_Prenom CHAR(25), Pers_CA integer, Pers_EM integer, CONSTRAINT PK_Personne PRIMARY KEY (Pers_ID));   CREATE TABLE TRAVAILLER (Site_ID integer, Pers_ID integer, CONSTRAINT PK_Travailler PRIMARY KEY (Site_ID, Pers_ID), CONSTRAINT FK_Travailler_Site FOREIGN KEY (Site_ID) REFERENCES Site (Site_ID), CONSTRAINT FK_Travailler_Personne FOREIGN KEY (Pers_ID) REFERENCES Personne (Pers_ID));   CREATE TABLE EMARGER (Serv_ID integer, Pers_ID integer, CONSTRAINT PK_Emarger PRIMARY KEY (Serv_ID, Pers_ID), CONSTRAINT FK_Emarger_Service FOREIGN KEY (Serv_ID) REFERENCES Service (Serv_ID), CONSTRAINT FK_Emarger_Personne FOREIGN KEY (Pers_ID) REFERENCES Personne (Pers_ID));   CREATE TABLE ABSENCE (Abs_ID integer, Abs_Date datetime, CONSTRAINT PK_Absence PRIMARY KEY (Abs_ID));   CREATE TABLE AVOIR (Pers_ID integer, Abs_ID integer, CONSTRAINT PK_Avoir PRIMARY KEY (Pers_ID, Abs_ID), CONSTRAINT FK_Avoir_Personnel FOREIGN KEY (Pers_ID) REFERENCES Personne (Pers_ID), CONSTRAINT FK_Avoir_Absence FOREIGN KEY (Abs_ID) REFERENCES Absence (Abs_ID));   CREATE TABLE TYPE (Type_ID integer, Type_Nom CHAR(34), CONSTRAINT PK_Type PRIMARY KEY (Type_ID));   CREATE TABLE APPARTENIR (Abs_ID integer, Type_ID integer, CONSTRAINT PK_Appartenir PRIMARY KEY (Abs_ID, Type_ID), CONSTRAINT FK_Appartenir_Absence FOREIGN KEY (Abs_ID) REFERENCES Absence (Abs_ID), CONSTRAINT FK_Appartenir_Type FOREIGN KEY (Type_ID) REFERENCES Type (Type_ID));

---

En espérant que cette fois tout soit niquel, mais sa m'a l'air bien plus propre et efficace que l'ancien.

Bonjour CleeM,


Ceci constituait l’Acte II : passons au suivant.
Il faut bien comprendre certains aspects sémantiques. Ainsi, vous dites qu’une absence appartient à un type : le terme "Type" est ici trop vague, il faudrait le remplacer par "Type d’absence", car votre MCD doit avoir un côté bande dessinée expliquant clairement les choses pour vos interlocuteurs (qu’ils soient ou non informaticiens) : c’est (entre autres) un outil de communication précieux. En outre, un type d’absence décrit, caractérise une absence, plutôt que celle-ci ne lui appartient. Le choix des verbes pour nommer les associations est important.

Vous avez utilisé des cardinalités 0,N de chaque côté de l’association Avoir : cela veut donc dire dans un premier temps qu’une personne (disons M. CleeM) a pu être absente plusieurs fois, ce qui est logique. Mais l’autre 0,N tend à faire comprendre que, dans un deuxième temps, plusieurs personnes sont impliquées dans l’absence de M. CleeM : par exemple, il pourrait y avoir celles qui ont provoqué l’absence de M. CleeM, celles qui en ont subi les conséquences, celles chargées éventuellement de sanctionner M. CleeM : je ne pense pas que l’on en soit là. Je suppose que vous souhaitez plutôt tenir à jour les absences successives de M. CleeM, tout simplement. Dans cette hypothèse, le lien unissant Personne et Absence est porteur d’une cardinalité 1,1 côté Absence : une absence ne concerne qu’une personne.
Sémantiquement parlant, une absence est en fait la propriété d’une personne, et si celle-ci n’avait droit qu’à une seule absence, vous auriez remonté l’information au niveau Personne. Problème : une personne peut être plusieurs fois absentes, d’où la mise en place de l’entité-type Absence pour tenir compte de cette multiplicité. Maintenant, cette entité-type pèse bien peu en face de l’entité-type Personne qui est ce que l’on appelle une entité-type forte, autonome, indépendante, autrement dit ça n’est pas parce que l’on cherchera à supprimer un service que les personnes qui y émargent disparaîtront pour autant : il y aura une réaction très vive du système et le service ne pourra pas disparaître de la base de données tant qu’il y restera au moins une personne. Par contre, si une personne disparaît de la base de données, ses absences peuvent-elles s’y opposer ? Certes non, les absences sont la chose de la personne et doivent disparaître avec elle : une absence sans personne en face ne signifie rien. Absence est une entité-type faible.
En relation avec tout cela, la base de données a un métabolisme, elle n’est pas statique, elle n’est pas qu’anatomique et cela s’exprime au niveau de l’instruction Create Table à l’aide de la clause Constraint ... On Delete... : on y reviendra plus loin.
En tout état de cause, pour montrer la faiblesse de l’entité-type Absence vis-à-vis de l’entité-type Personne et signifier qu’elle n’en est qu’une propriété multivaluée, on utilise l’identification relative, laquelle est repérable dans le cas de PowerAMC par une mise entre parenthèses de la cardinalité 1,1.



http://img363.imageshack.us/img363/2...dreletnxq5.jpg
Dans le cas de l’identification absolue, l’identifiant d’Absence est le singleton {Abs_Id}. Dans le cas de l’identification relative, il s’agit du couple {Pers_ID, Abs_Id}.
Techniquement parlant, dans le premier cas, l’attribut identifiant Abs_Id prend les valeurs successives 1, 2, 3, ... n, toutes personnes confondues. Dans le second cas, la numérotation redémarrera à 1 pour chaque personne. Ceci n’est pas important au niveau MCD, mais peut devenir décisif pour les très grandes bases de données en production (table de plusieurs millions de personnes) : possibilité de regroupement dans la même page physique des absences d’une personne, d’où réduction des accès aux disques et des phénomènes d’attente de fin de ces E/S (I/O bound) et (en fonction du SGBD) possibilité de réduction des accès concurrents à la même page.


Au niveau logique, on repère bien la clé primaire d’Absence ; les attributs Pers_ID et Abs_Id sont soulignés et notés <pk> :



http://img363.imageshack.us/img363/4...drelmldrk5.jpg


Cette approche sémantique de la nature de la relation existant entre les personnes et leurs absences met en évidence que lorsqu’une personne est supprimée de la base de données, ses absences doivent aussi disparaître : cela figure dans l’instruction Create Table (option Cascade) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Create Table Absence ( Pers_Id Int Not null, Abs_Id Int Not null, Type_Abs_Id Int Not null, Abs_Date Datetime Not null, Constraint PK_Absence Primary Key (Pers_Id, Abs_Id), Constraint FK_Absence_Personne Foreign Key (Pers_Id) References Personne (Pers_Id) On Delete Cascade, Constraint FK_Absence_Type_abs Foreign Key (Type_Abs_Id) References Type_absence (Type_Abs_Id));

---

(Pour parvenir à cela avec PowerAMC : avant la génération du script, cliquer sur l’arc reliant Personne et Absence, choisir l’onglet intégrité et cocher Cascade pour la contrainte de suppression).



http://img243.imageshack.us/img243/6...cascadecr7.jpg


Autre point capital : aucun attribut ne doit pouvoir prendre la valeur nulle or, selon votre script, tous vos attributs le peuvent (y-compris les identifiants, or comment identifier quelque chose par l’absence d’information ?) Observez l’instruction Create Table Absence ci-dessus : tous les attributs sont Not null.

Maintenant, il y a encore pas mal de choses à dire concernant votre MCD. Par exemple, vous avez prévu un attribut Abs_Date, mais cela est-il suffisant pour suivre les absences ? Tel quel, cet attribut signifie seulement qu’une absence a débuté à une certaine date et c’est tout. Il faudra peut-être mettre en place une entité-type Historique des absences.
Je vous conseille la lecture des discussions :

http://www.developpez.net/forums/sho...d.php?t=334343
http://www.developpez.net/forums/sho...d.php?t=336896


Avec PowerAMC, vous pouvez aussi vous exercer à utiliser la notation Entité/Relation à la place de la notation Merise, par exemple pour que l’entité-type Personne soit directement en relation avec Heberger et assurer ainsi une contrainte nécessaire.

MCD avec la notation Merise (sans prise en compte de la contrainte) :


http://img243.imageshack.us/img243/8...risemcdmh3.jpg


Avec la notation Entité/relation, la contrainte est prise en compte :


http://img363.imageshack.us/img363/2...utermcdia4.jpg

info utile

consernant la clé primaire , choisi toujours l'identifiant qui n'aura aucune redondance (données identique) comme un numero unique pour eviter tout probleme deconfusion entre des noms par exemples

Merci beaucoup à vous, j'ai dévoré ce concentré d'informations avec plaisir!
Fsmrel, très jolis posts mêlant connaissances, qualité d'écriture et de présentation.

J'ai enfin un MCD propre et juste, j'ai rempli ma BDD avec les infos nécessaires, et je vais donc pouvoir m'attaquer au Delphi pur.^^

Un grand merci, car si j'avais continuer sur ma 1ere BDD, je courais à la catastrophe!