Discussion :

[ecervetti]

Bonjour
Je ne parle pas du mariage, mais du problème suivant:
-soit une table 'ecuries'
-soit une table 'voitures'
-soit une table 'pilotes'

Une voiture appartient à une ecurie,
Un pilote appartient à une ecurie.

Je veux représenter dans mon MBDD la relation "un pilote est associé à une voiture" , mais je voudrais ajouter la contrainte "un pilote n'est associé à une voiture que s'ils sont dans la même ecurie tous les deux".

Est-ce quelque chose qui ne peut se gêrer que du côté software, ou est-ce qu'il y a une "bonne" manière de représenter cela dans une base de données ?

Merci beaucoup pour vos réponses,

Emmanuel C

[SQLpro]

C'est un problème de modélisation conceptuelle est vous tentez de raisonner avec des tables. C'est pourquoi vous ne comprenez pas ce que vous faites...

Commencez par analyser quelles sont les dépendances fonctionnelles :
Une voiture appartient à, une écurie je suis d'accord. Donc
Voiture -> écurie
en revanche pilote -> écurie, j'en suis mois sûr car un pilote est libre d'aller dans l'écurie qu'il souhaite au fil du temps. C'est beaucoup moins vrai pour les voitures !
par contre si vous faites :
pilote -> voiture vous avez résolu votre problème !
Maintenant les cardinalités...
Une écurie peut avoir plusieurs voitures.
Un pilote peut conduire plusieurs voitures ? Oui dans le temps, mais pas au même moment. Donc un pilote change d'écurie par le simple fait qu'il passe d'une voiture à l'autre.

CQFD

A +

[ecervetti]

Bonjour,
Merci tout d'abord infiniment pour votre réponse rapide,
Cependant en changeant mon paradigme vous n'avez pas répondu à ma question, j'ai pris les ecuries et les pilotes à titre d'exemple mais mon problème concret n'est pas d'ordre sémantique :

Pour faire plus prosaïque:
Un A est associé à plusieurs B
Un A est associé à plusieurs C
(et ce schema là ne peut pas changer , merci de partir de ce postulat... )

Ma question est la suivante: Peut-on représenter en base cette assertion :
"Un B ne peut être dans une relation R avec un C que si B et C sont en relations avec le même A."

Concrêtement le problème que j'ai à résoudre est le suivant; (tres tres simplifié)
J'ai une liste de services samu
j'ai des professionels appartenant à un service samu
J'ai des vehicules appartenant à un service samu

Je veux représenter en base une "intervention" qui, à une date , un idSamu , et un appel; associe N véhicules et N professionels .

Naturellement, je serais tenté de représenter cela de cette manière:
TABLES:
tbl_intervention : idIntervention,idSamu,date,txt_appel
rel_intervention : idRelation,idIntervention,idProfessionel,idVehicule

CLES ETRANGERES:
rel_intervention:idIntervention n->1 tbl_intervention:idIntervention
rel_intervention:idProfessionel n->1 tbl_professionel:idProfessionel
rel_intervention:idVehicule n->1 tbl_vehicule:idVehicule

Est-ce à votre avis la "bonne" façon de faire, ou si il existe une modélisation plus "intelligente" qui pourrait représenter plus de dépendances fonctionelles ?

(je ne comprend effectivement plus ce que je fait, c'est l'effet "modéliser un MBDD de plus de 70 tables alors qu'on a jamais dépassé les 10", mais il faut bien progresser ! )

En vous remerciant encore une fois pour votre aide.

[SQLpro]

Encore un fois tout dépend si vous introduisez la notion de temps ou si vous raisonnez en statique. S'il s'agit d'une réelle application, alors je suppose que vous raisonnez en dynamique donc, avec une notion de temps, auquel cas votre question n'a pas de sens...

A +

[Sw3eT]

Cela ne peut pas être représenté au niveau d'une table.
Les assertions sont font parties de la norme SQL et sont des objets à part entière de la base de donnée.
SQL Server les supportes (et Oracle aussi?) comme vous pouvez le voir ici : http://sqlpro.developpez.com/cours/sqlaz/ddl/
Si votre SGBDR ne le permet pas il faudra alors utiliser un Trigger.

[fsmrel]

Je veux représenter dans mon MBDD la relation "un pilote est associé à une voiture" , mais je voudrais ajouter la contrainte "un pilote n'est associé à une voiture que s'ils sont dans la même écurie tous les deux".

Indépendamment de l’aspect temporel des choses, évoqué par SQLpro, je ferai une remarque concernant la contrainte selon laquelle un pilote ne peut être associé à une voiture qu’à la condition qu’ils soient de la même écurie, ou plus généralement, je cite :

« Un B ne peut être dans une relation R avec un C que si B et C sont en relation avec le même A. »

D’un strict point de vue technique, il existe une vieille astuce, probablement aussi vieille que l’intégrité référentielle elle-même, qui consiste à doubler la clé primaire d’une table par une surclé ce celle-ci et qui serve de référence pour une autre table.

Soit les tables E (comme Écurie), P (comme Pilote), V (comme Voiture) et PV (comme association Pilote, Voiture). Le MLD (Power AMC) est le suivant :




Concernant la table P, on observera que la clé primaire est constituée du singleton {Pid} (mickey <pk>, comme <primary key>), tandis que l’on a par ailleurs une surclé constituée de la paire {Pid, Eid} (mickey <ak>, comme <alternate key>), tandis que Eid participe à la clé étrangère {Eid} (mickey <fk>, comme <foreign key>).

Concernant la table V, on peut faire des observations analogues, toutes choses égales par ailleurs.

La table PV a pour clé primaire la paire {Pid, Vid} et les clés étrangères {Pid, Eid} d’une part, {Vid, Eid} d’autre part. Ces clés étrangères peuvent surprendre car, si on s’attend plutôt à ce qu’elles référencent à des clés primaires (respectivement {Pid} et {Vid}), en fait elles peuvent parfaitement faire référence à des clés non primaires.

Par ce biais, on peut demander au SGBD de garantir la contrainte selon laquelle un pilote ne peut être associé à une voiture qu’à la condition qu’ils soient de la même écurie.

Ci-joint le code SQL.

Code :

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
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
Create table E (
     Eid     Char(3)        Not null
   , Enom    varchar (48)   Not null
 , Constraint E_PK Primary Key (Eid)
) ;
Create table P (
     Pid     Char(3)        Not null
   , Eid     Char(3)        Not null
   , Pnom    varchar (48)   Not null
 , Constraint P_PK Primary Key (Pid)
 , Constraint P_AK Unique (Pid, Eid)
, Constraint PE_FK Foreign Key (EiD)  References E
) ;
 
Create table V (
     Vid     Char(3)        Not null
   , Eid     Char(3)        Not null
   , Vnom    varchar (48)   Not null
 , Constraint V_PK Primary Key (Vid)
 , Constraint V_AK Unique (Vid, Eid)
 , Constraint VE_FK Foreign Key (EiD)  References E
) ;
Create table PV (
     Pid     Char(3)        Not null
   , Vid     Char(3)        Not null
   , Eid     Char(3)        Not null
 , Constraint PV_PK Primary Key (Pid, Vid)
 , Constraint PV_AKP Foreign Key (Pid, EiD)  References P (Pid, Eid)
 , Constraint PV_AKV Foreign Key (Vid, EiD)  References V (Vid, Eid)
) ;

Les assertions sont font parties de la norme SQL et sont des objets à part entière de la base de donnée. SQL Server les supporte [...]

Certes, la norme SQL propose l’instruction CREATE ASSERTION, mais je ne sache pas que SQL Server en fasse autant. Si je ne m’abuse, votre référence à l’article de SQLpro concerne la norme.

[SQLpro]

Oui, SQL Server ne connais pas la notion d'Assertion. A ma connaissance seul le SGBDR Ocelot de Peter Gulutzan implémentait l'assertion SQL.

A +

[fsmrel]

[...]A ma connaissance seul le SGBDR Ocelot de Peter Gulutzan implémentait l'assertion SQL.

N'oublions pas... SEQUEL (sous la forme ASSERT), dans les années soixante-dix...

[SQLpro]

OK sur SEQUEL, mais
1) on est dans le forum SQL
2) je parlais de machine et non de langage
3) a ma connaissance aucun SGBDR commercial n'a utilisé SEQUEL.

A +

[ecervetti]

Bonsoir, merci à tous pour vos réponses,

Merci beaucoup fsmrel pour votre astuce, j'ai quand même un peu de mal à voir pourquoi les clés étrangères:
(Pid, EiD)
(Vid, EiD)
Ne pointeraient pas chacune vers des sur-clés qui auraient chacune une ecurie différente..

Bref quoi qu'il en soit j'ai la réponse à ma question: on est clairement dans le rôle du soft pour résoudre cette contrainte, et non plus vraiment dans le rôle du SGBD, même si certaines astuces le permettent.
C'était surtout cette information dont j'avais besoin.

Bonne soirée.

[fsmrel]

OK sur SEQUEL, mais
1) on est dans le forum SQL

Comme vous le savez très bien, sans un certain problème de copyright, IBM aurait continué à utiliser le terme SEQUEL et non pas SQL pour son langage hybride prédicatif/algébrique, développé initialement sous la houlette de Don Chamberlin et Raymond Boyce (mort prématurément d’un anévrisme en 1974).

SEQUEL fut le langage du SGBD prototype SYSTEM R et, en 1976, comportait le trio infernal SELECT-FROM-WHERE, avec le duo GROUP BY-HAVING comme comparse, plus des complices nommés COUNT, AVG, SUM, MIN et MAX, sans parler de DELETE-UPDATE, des vues, assertions et triggers et autres choses.

Autrement dit, aux évolutions près dues aux années qui passent, parler de SQL c'est parler de SEQUEL (et réciproquement comme aurait dit Pierre Dac), c’est bonnet blanc et blanc bonnet, synonyme, alias et compagnie.

Sans cette histoire de copyright, IBM n’aurait pas renommé le langage et, en toute logique, on serait sur le forum SEQUEL.

2) je parlais de machine et non de langage

Quelle est donc cette machine dont vous parliez ?

3)a ma connaissance aucun SGBDR commercial n'a utilisé SEQUEL.

Et pour cause. A ceci près, que SQL/DS est la version commercialisée (1981) de SYSTEM R, à quelques aménagements près (suppression par exemple des assertions et triggers...)

Ceci dit, je ne saisis pas l'intérêt de votre remarque.

[fsmrel]

Bonjour,

[...] j'ai quand même un peu de mal à voir pourquoi les clés étrangères:
(Pid, EiD)
(Vid, EiD)
Ne pointeraient pas chacune vers des sur-clés qui auraient chacune une ecurie différente.

Considérons l’exemple suivant, dans lequel le pilote Bernard fait partie de l’écurie DVP et la voiture Rosalie fait partie de l’écurie EWQ. Par référence au code SQL fourni dans mon précédent message, montrons que Bernard ne peut pas piloter Rosalie.

[...] quoi qu'il en soit j'ai la réponse à ma question: on est clairement dans le rôle du soft pour résoudre cette contrainte, et non plus vraiment dans le rôle du SGBD, même si certaines astuces le permettent.
C'était surtout cette information dont j'avais besoin.

Vous allez vite en besogne. Les concepts de surclé, clé candidate, clé primaire, clé étrangère font partie de la théorie relationnelle (à ceci près que celui de clé primaire est obsolète du point de vue de cette théorie, mais peu importe dans le cadre ce l’exemple). Si le code SQL que j’ai pris la peine de vous fournir n’est pas à votre goût, et si votre SGBD ne vous permet pas d’utiliser l'instruction CREATE ASSERTION, vous pouvez en passer par un trigger.

En tout état de cause, le principe fondamental à respecter est le suivant : sous-traiter au SGBD les contraintes, parce que celui-ci les garantit automatiquement. Si vous les externalisez dans un programme dont le l’exécution est du ressort du bon vouloir de l’utilisateur final (humain ou programme), l’expérience montre qu’il y aura fatalement des ratés. Du point de vue du spécialiste des bases de données, votre conclusion hâtive n’est pas recevable.