Discussion :

[johnny3]

Bonjour,

je suis en train de revoir des annales en vue d'un examen début février, et certaines choses m'échappent.

Dans le cours, il est dit, dans le cas d'une association ternaire, que si nous avons:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
A(a1, a2, a3))
B(b1, b2)
C(c1,c2, c3)

Nous obtenons la relation ternaire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

R(a1, b1, c1, c2, r1)

Avec les cardinalités 0,1 du côté de A, 0,N du côté de B et 0,N du côté de C, r a comme clefs étrangères a1, b1, (c1,c2)

A présent, dans les annales, examen septembre 2007, Véhicule a comme clef primaire N°v, idHab et comme clef étrangère idHab.

Mes questions:
1) toute clef étrangère est-elle considérée comme primaire et étrangère?

Je m'explique: lorsqu'on crée la table, on désigne toute clef (primaire ou étrangère) comme clef primaire et on indique ensuite quelles sont les clefs qui référencent d'autres tables et qui sont donc des clefs étrangères.
Une clef étrangère forme-t-elle donc toujours une clef composite dans une autre table si les cardinalités (1,1 notamment) sont présentes?


2) Toute clef étrangère doit-elle faire partie d'une association? (ici, idHab est clef primaire et étrangère de Véhicule, mais dans l'assocation "Louer", bien qu'elle ne relie que Véhicule et Parking, la clef primaire est triple, soit la clef primaire de Véhicule, sa clef étrangère et la clef primaire de Parking.

3) Si je prends le schéma suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Client (code_clt, nom_clt, prenom_clt, adresse_clt, codepost_clt, ville_clt, code_deagent)
Agent (code_agent, nom_agent, prenom_agent, commission_agent)
Voyage (code_voy, nom_voy, datedeb_voy, datefin_voy, prix_voy)
Reserve(code_clt, code_voy, paye)

Pourquoi la clef primaire de Client n'est pas une clef composite de code_clt et code_deagent étant donné qu'au moment de créer la table, on va déclarer ces deux clefs comme clef primaire et code_deagent comme clef étrangère référençant la table Agent?

Et, du coup, pourquoi Reserve ne comprend pas code_deagent dans ses clefs?

Pourriez-vous m'éclairer, s'il vous plait? Je vous mets l'annale corrigée en pièce jointe.

En vous remerciant par avance,

[CinePhil]

Je formulerais votre cours d'une manière légèrement différente :

Si nous avons une association ternaire entre les relations :
A(a1, a2, a3))
B(b1, b2)
C(c1,c2, c3)

Avec les cardinalités 0,1 du côté de A, 0,N du côté de B et 0,N du côté de C,

La table associative qui en découle sera :
R(a1, b1, c1, c2, r1)

R a comme clefs étrangères a1, b1, (c1,c2)

Ce qui fait que R n'a comme clé primaire que a1 est la cardinalité 0,1 du côté de A.
En effet, l'association entre A, B et C ne peut apparaître au plus qu'une fois et donc dépend totalement de la valeur de A.

1) toute clef étrangère est-elle considérée comme primaire et étrangère ?

Non bien sûr !
LE cas que vous exposez ci-dessus en est un exemple un peu subtil mais il y en a beaucoup d'autres. Le cas typique est :
A -1,1----Associer----0,n- B
A va avoir une clé étrangère venant de B mais qui ne sera généralement pas clé primaire ni même partie de clé primaire dans A.

Je m'explique: lorsqu'on crée la table, on désigne toute clef (primaire ou étrangère) comme clef primaire

Où diable vous a t-on enseigné cela !

Une clef étrangère forme-t-elle donc toujours une clef composite dans une autre table si les cardinalités (1,1 notamment) sont présentes?

Non. Voir ci-dessus.

2) Toute clef étrangère doit-elle faire partie d'une association?

Toute clé étrangère est issue d'une association présente dans le MCD.

A présent, dans les annales, examen septembre 2007, Véhicule a comme clef primaire N°v, idHab et comme clef étrangère idHab.

Que représente idHab ?
Est-ce un morceau de la clé primaire (conjointement avec N°v) ?
Seulement une clé étrangère ?

(ici, idHab est clef primaire et étrangère de Véhicule,

Encore une fois, que représente idHab ? J'ai du mal à voir son utilité dans l'identification d'un véhicule !

mais dans l'assocation "Louer", bien qu'elle ne relie que Véhicule et Parking, la clef primaire est triple, soit la clef primaire de Véhicule, sa clef étrangère et la clef primaire de Parking.

Je ne vois pas ce cas dans l'extrait d'annale que vous donnez en pièce jointe alors il est difficile pour moi de comprendre le sens de tout cela. Est-ce le corrigé ou votre interprétation ?

Pourquoi la clef primaire de Client n'est pas une clef composite de code_clt et code_deagent étant donné qu'au moment de créer la table, on va déclarer ces deux clefs comme clef primaire et code_deagent comme clef étrangère référençant la table Agent?

Imaginez que vous soyez le client et que vous réserviez un voyage et que l'agent de voyage vous dise, après que vous ayez signé le contrat : "A partir de maintenant, vous dépendez entièrement de moi !"
Vous réagirez comment ?

Plus sérieusement, si vous mettez le code_deagent en clé primaire conjointement avec le code_clt, vous autorisez la création multiple du même client par plusieurs agents.

Et, du coup, pourquoi Reserve ne comprend pas code_deagent dans ses clefs?

Parce que comme un client est suivi par un agent, on retrouve l'agent via le client.

[johnny3]

Bonjour,

merci pour votre réponse. Désolé, j'avais oublié de joindre une seconde pièce (voir mon message d'origine).

Voici l'énoncé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Habitant ( idHab, nom, adr, dateArrivée )
Véhicule ( N°v, idHab, dateAchat, prix, km )
Parking ( idPark, localisation, n°, surface )
Propriétaire (idPark, idHab, dateAchat, prix )
Louer ( N°v, idHab, idPark, nbmois )
Stationner (N°v, idHab, idPark, nbheures)

Les clés sont soulignées, les clés étrangères ne sont pas précisées.

Dans le corrigé, voici l'implémentation des tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Véhicule ( N°v VARCHAR(9),
idHab INTEGER(6),
dateAchat INTEGER(4) NOT NULL,
prix INTEGER(5),
km INTEGER(6),
PRIMARY KEY( N°v, idHab) ,
FOREIGN KEY (idHab) REFERENCES Habitant
ON DELETE CASCADE)

IdHab est donc dans la clef primaire et indique qu'il est aussi une clef étrangère référençant Habitant.

De même:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Louer ( N°v VARCHAR(9),
idHab INTEGER(6),
idPark INTEGER(4),
nbmois INTEGER(5) NOT NULL,
PRIMARY KEY( N°v, idHab, idPark),
FOREIGN KEY (N°v, idHab) REFERENCES Véhicule,
FOREIGN KEY (idPark) REFERENCES Parking
ON UPDATE CASCADE)

Je ne comprends donc pas. D'après ce corrigé (du CNAM), il semble que l'on mette la clef étrangère dans la clef primaire et qu'ensuite, on déclare une partie de ces clefs étrangères. Je n'y comprends plus rien!

Merci par avance pour votre aide,
Johnny

[CinePhil]

Comme je ne suis pas encore diplômé je ne devrais peut-être pas mais je vais me permettre quand même de critiquer ce corrigé du CNAM !

N°v est de type VARCHAR(9). Je suppose qu'il s'agit du numéro d'immatriculation du véhicule.

Cette colonne forme la clé primaire conjointement avec idHab.

Outre le fait que prendre une colonne de type VARCHAR et potentiellement modifiable dans une clé primaire n'est pas bon sur le plan des performances et pour la maintenance des clés étrangères, rien n'interdit qu'un même numéro de véhicule existe deux fois, associé à deux idHab différents !

Il y a là à mon avis une erreur de conception. Mais peut-être la suite de l'exercice consistait-elle justement à critiquer le modèle ? (l'art de se rattraper aux branches ! )

Ensuite, une dateAchat en INTEGER, c'est également une hérésie !

Pour répondre à votre question :

Je ne comprends donc pas. D'après ce corrigé (du CNAM), il semble que l'on mette la clef étrangère dans la clef primaire et qu'ensuite, on déclare une partie de ces clefs étrangères. Je n'y comprends plus rien!

Nous sommes ici dans la même requête CREATE TABLE et il est d'usage de placer l'instruction PRIMARY KEY avant FOREIGN KEY. En fait, la table va être créée d'un bloc et la contrainte de clé étrangère sera forcément satisfaite puisque la table ne contiendra pas encore de données !
L'ordre dans lequel on écrit cette partie de requête n'a peut-être pas une grande importance.

Il aurait été plus délicat de faire d'abord la requête CREATE TABLE avec seulement indication de la clé primaire, puis de ne penser que plus tard à y ajouter la contrainte de clé étrangère par une requête ALTER TABLE car des données auraient pu être insérées dans la table entre temps, lesquelles ne satisferaient peut-être pas toutes la contrainte de clé étrangère.

[johnny3]

Merci. Je commence à mieux comprendre.

Du coup, j'ai besoin de refixer des choses avant de passer l'examen (tout début février!)

Si j'ai ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Client (code_clt, nom_clt, prenom_clt, adresse_clt, codepost_clt, ville_clt, code_deagent)
Agent (code_agent, nom_agent, prenom_agent, commission_agent)
Voyage (code_voy, nom_voy, datedeb_voy, datefin_voy, prix_voy)
Reserve(code_clt, code_voy, paye)

Quelle création de la table Client est correcte?

Celle qui correspondrait aux exemples que j'ai cités ci dessus:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Client (code_clt NUMBER,
nom_clt VARCHAR(20) NOT NULL,
prenom_clt VARCHAR(20) NOT NULL,
adresse_clt VARCHAR(20) NOT NULL,
codepost_clt NUMBER NOT NULL,
ville_clt VARCHAR(20) NOT NULL,
PRIMARY KEY (code_clt, code_agent),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

ou celle-ci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Client (code_clt NUMBER,
nom_clt VARCHAR(20) NOT NULL,
prenom_clt VARCHAR(20) NOT NULL,
adresse_clt VARCHAR(20) NOT NULL,
codepost_clt NUMBER NOT NULL,
ville_clt VARCHAR(20) NOT NULL,
PRIMARY KEY (code_clt),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

Car, quand on regarde les exemples précédents,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Véhicule ( N°v VARCHAR(9),
idHab INTEGER(6),
dateAchat INTEGER(4) NOT NULL,
prix INTEGER(5),
km INTEGER(6),
PRIMARY KEY( N°v, idHab) ,
FOREIGN KEY (idHab) REFERENCES Habitant
ON DELETE CASCADE)

IdHab est une clef étrangère référençant Habitant mais est mise dans la clef primaire lors de la création de la table.

Du coup, que penser de ceci:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE Internaute (email VARCHAR (40) NOT NULL, 
                         nom VARCHAR (30) NOT NULL ,
                         prenom VARCHAR (30) NOT NULL,
                         region VARCHAR (30),
                         CONSTRAINT PKInternaute PRIMARY KEY (email));
 
CREATE TABLE Pays (code    VARCHAR(4) NOT NULL,
                   nom  VARCHAR (30) DEFAULT 'Inconnu' NOT NULL,
                   langue VARCHAR (30) NOT NULL,
                   CONSTRAINT PKPays PRIMARY KEY (code));

CREATE TABLE Artiste  (idArtiste INTEGER NOT NULL,
                       nom VARCHAR (30) NOT NULL,
                       prenom VARCHAR (30) NOT NULL,
                       anneeNaiss INTEGER,
                       CONSTRAINT PKArtiste PRIMARY KEY (idArtiste),
                       CONSTRAINT UniqueNomArtiste UNIQUE (nom, prenom));

CREATE TABLE Film  (idFilm INTEGER NOT NULL,
                    titre    VARCHAR (50) NOT NULL,
                    annee    INTEGER NOT NULL,
                    idMES    INTEGER,
                    genre VARCHAR (20) NOT NULL,
                    /* Remplacer TEXT par LONG pour ORACLE */
                    resume      TEXT,
                    codePays    VARCHAR (4),
                    CONSTRAINT PKFilm PRIMARY KEY (idFilm),
                    FOREIGN KEY (idMES) REFERENCES Artiste,
                    FOREIGN KEY (codePays) REFERENCES Pays);

CREATE TABLE Notation (idFilm INTEGER NOT NULL,
                       email  VARCHAR (40) NOT NULL,
                       note  INTEGER NOT NULL,
                       CONSTRAINT PKNotation PRIMARY KEY (idFilm, email));

CREATE TABLE Role (idFilm  INTEGER NOT NULL,
                   idActeur INTEGER NOT NULL,
                   nomRole  VARCHAR(30), 
                   CONSTRAINT PKRole PRIMARY KEY (idActeur,idFilm),
                   FOREIGN KEY (idFilm) REFERENCES Film,
                   FOREIGN KEY (idActeur) REFERENCES Artiste);

CREATE TABLE Genre (code    VARCHAR (20) NOT NULL,
                    CONSTRAINT PKGenre PRIMARY KEY (code));

de Philippe Rigaux? Ici, pour la table Film, il ne déclare que IdFilm en clef primaire et écrit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 FOREIGN KEY (idMES) REFERENCES Artiste,
                      FOREIGN KEY (codePays) REFERENCES Pays);

Je me sens vraiment perdu...

Merci par avance,
Johnny

[CinePhil]

Comme je l'ai dit dans mon message précédent, je trouve

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE Véhicule ( N°v VARCHAR(9),
idHab INTEGER(6),
...
PRIMARY KEY( N°v, idHab) ,

Très mauvais !

Pour la même raison évoquée pour les voitures :

rien n'interdit qu'un même numéro de véhicule existe deux fois, associé à deux idHab différents !

La deuxième CREATE TABLE Client qui fait une clé primaire double en associant le client à l'agent de voyage n'interdit pas la création du client en double associé à plusieurs agents.

Donc c'est la première qui est la bonne. Le client existe en tant que tel et est identifié par son numéro. le code de l'agent n'est qu'une clé étrangère.

Du coup, que penser de ceci:

[code]
# 1)
CREATE TABLE Internaute (email VARCHAR (40) NOT NULL,
nom VARCHAR (30) NOT NULL ,
prenom VARCHAR (30) NOT NULL,
region VARCHAR (30),
CONSTRAINT PKInternaute PRIMARY KEY (email));

# 2)

CREATE TABLE Pays (code VARCHAR(4) NOT NULL,
nom VARCHAR (30) DEFAULT 'Inconnu' NOT NULL,
langue VARCHAR (30) NOT NULL,
CONSTRAINT PKPays PRIMARY KEY (code));

# 3)

CREATE TABLE Artiste (idArtiste INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
CONSTRAINT PKArtiste PRIMARY KEY (idArtiste),
CONSTRAINT UniqueNomArtiste UNIQUE (nom, prenom));

# 4)

CREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR (20) NOT NULL,
/* Remplacer TEXT par LONG pour ORACLE */
resume TEXT,
codePays VARCHAR (4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);

# 5)

CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (40) NOT NULL,
note INTEGER NOT NULL,
CONSTRAINT PKNotation PRIMARY KEY (idFilm, email));

# 6)

CREATE TABLE Role (idFilm INTEGER NOT NULL,
idActeur INTEGER NOT NULL,
nomRole VARCHAR(30),
CONSTRAINT PKRole PRIMARY KEY (idActeur,idFilm),
FOREIGN KEY (idFilm) REFERENCES Film,
FOREIGN KEY (idActeur) REFERENCES Artiste);

# 7)

CREATE TABLE Genre (code VARCHAR (20) NOT NULL,
CONSTRAINT PKGenre PRIMARY KEY (code));[code]

1)
- VARCHAR(40) pour une adrel, ça peut être insuffisant. Mon adrel professionnelle compte 35 caractères.
- Faire de cette adrel la clé primaire est tout aussi mauvais que dans le cas des voitures. Une clé primaire est un identifiant anonyme de type entier non signé auto-incrémenté.
- La région pourrait être externalisée dans une autre table. On aurait alors ici l'Id de la région en tant que clé étrangère.

2)
- Code pays en VARCHAR(4) : idem ci-dessus.
- Nom de pays en VARCHAR(30) est insuffisant : "République Démocratique du Congo" = 32 caractères.

3)
- VARCHAR(30) pour le nom peut être insuffisant mais sinon ça va.

4)
- Titre en VARCHAR(50) est largement insuffisant : "Faut pas prendre les enfants du bon dieu pour des canards sauvages"
- Genre pourrait être externalisé dans une autre table et on aurait ici en clé étrangère l'ID du genre.

5)
- On a ici l'exemple qui justifie ma critique sur l'emploi de email comme clé primaire dans la table internaute : Si l'internaute change d'adrel, on doit aussi changer toutes les lignes de cette table puisque c'est utilisé en clé étrangère.
- D'ailleurs, il manque les contraintes de clés étrangères !

6)
Là c'est correct, à part le VARCHAR(30) sur le nomRole qui peut être insuffisant.

7)
Je découvre maintenant la table genre que j'avais préconisée un peu plus haut ! Outre qu'elle devrait être créée avant la table film :
- code en VARCHAR(20) est un mauvais choix, surtout en clé primaire, comme déjà évoqué plus haut.
- Une table externalisée qui n'a qu'une colonne présente un intérêt limité !
Elle devrait plutôt avoir la structure :
Genre(IdGenre, LibelleGenre)

pour la table Film, il ne déclare que IdFilm en clef primaire et écrit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 :

 FOREIGN KEY (idMES) REFERENCES Artiste,
                      FOREIGN KEY (codePays) REFERENCES Pays);

Tiens ! Je ne l'avais pas vue celle-là !
Je suppose que idMES est l'identifiant du metteur en scène ?
S'ils s'y sont mis à plusieurs pour réaliser le film, le modèle est mort !
Sinon dans le principe c'est normal. Le film existe en tant que tel et le pays ou l'identifiant du metteur en scène (unique ou considéré comme principal) sont des informations complémentaires sur le film. Ce sont en fait des attributs externalisés. Il n'y a donc en clé primaire que l'IdFilm et en clé étrangère ces infos externalisées.

Tu aurais peut-être intérêt à faire un tour du côté des cours Merise, tel que celui de SQLPro. Il y en a d'autres pas mal faits sur internet.


Bon courage pour l'examen. Moi j'ai fini les cours mais j'ai encore le mémoire à rédiger.

[johnny3]

ok, je vois! Leurs modèles sont un peu trop simplistes!

Bon, je commence cependant à y voir plus clair.

J'ai fait l'examen blanc récemment: 9,5/20. L'examen est le 2 février, je ne veux pas passer au rattrapage!

Merci pour l'aide apportée,
Johnny

[johnny3]

Finalement, je me suis rendu compte que j'avais encore besoin de précisions sur l'implémentation des tables.

Quand on crée un table d'une entité, on prend toutes les clefs que l'on déclare dans la clef primaire, puis on détermine les clefs étrangères dans celles déclarées.

Par exemple, pour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Client (code_clt, nom_clt, prenom_clt, adresse_clt, codepost_clt, ville_clt, code_deagent)
Agent (code_agent, nom_agent, prenom_agent, commission_agent)
Voyage (code_voy, nom_voy, datedeb_voy, datefin_voy, prix_voy)
Reserve(code_clt, code_voy, paye)

L'implémentation de la table Client donne ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Client (code_clt NUMBER-6),
nom_clt VARCHAR(20) NOT NULL,
prenom_clt VARCHAR(20) NOT NULL,
adresse_clt VARCHAR(20) NOT NULL,
codepost_clt NUMBER(5) NOT NULL,
ville_clt VARCHAR(20) NOT NULL,
PRIMARY KEY (code_clt, code_agent),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

où code_agent est une clef étrangère mais déclarée dans la clef primaire lors de la création des clefs, puis précisée comme clef étrangère référençant la table Agent.

Est-ce que cette implémentation de la table Reserve est correcte?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE Reserve(code_clt NUMBER (6),
code_voy NUMBER (6) NOT NULL,
paye VARCHAR (1),
PRIMARY KEY (code_clt, code_voy),
FOREIGN KEY (code_clt) REFERENCES Client,
FOREIGN KEY (code_voy) REFERENCES Voyage ON UPDATE CASCADE)

Cependant, si l'on considère le schéma suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
HOTEL (NumHotel, NomHotel, VilleHotel) 
RESERVATION (DateRéservation, NuméroRéservation, NumChambre, NumHotel, NumClient,
DateDébutprévue, DateFinprévue,) 
CLIENT (NumClient, NomClient, VilleClient) 
CHAMBRE (NumChambre, NumHotel, TypeChambre, PrixChambre) 
LIT (Numlit, NombrePlaces, NumChambre)

La relation RESERVATION contient toutes les réservations de chambres qui ont eu lieu dans les différents hôtels de HOTEL. Dans le réel décrit par cette base de données, un client ne peut pas, dans une même réservation, réserver une chambre au delà de 30 jours.
L‘attribut « TypeChambre » peut prendre la valeur « simple », « double » ou « famille ». Une chambre simple est une chambre à un lit. Une chambre double est une chambre à 1 lit à deux places ou à deux
lits à une place. Une chambre famille contient au moins 2 lits dont un est à 2 places. Le prix d‘une chambre est compris entre 20 et 150 euros. Le nombre de chambres par hôtel ne dépasse pas 100. La relation LIT regroupe l‘ensemble des lits des différentes chambres de tous les hôtels.

l'implémentation de la table réservation (qui, par son nom est, je suppose, une association entre deux entités), il n'y a que les clefs primaires qui sont véritablement déclarées en tant que clefs primaires et les clefs secondaires qui sont spécifiées ensuite:

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
CREATE TABLE RESERVATION  
( 
DateRéservation DATE,
NuméroRéservation NUMBER(3),  
NumChambre NUMBER(4) not null,  
NumHotel NUMBER(3) not null,  
NumClient NUMBER(20) not null,  
DateDébutprévue DATE not NULL,  
DateFinprévue DATE not NULL,  

PRIMARY KEY (DateRéservation, NuméroRéservation),  
Foreign KEY (NumClient) references CLIENT,  
Foreign KEY (NumChambre,NumHotel) references CHAMBRE,
CHECK (DateDébutPrévue >=SYSDATE),
CHECK (DateFinPrévue> DateDébutPrévue),
CHECK (add_months(DateDébutprévue,1)> DateFinprévue ) 
// cette dernière étant préférable à celle d‘avant mais l‘étudiant n‘est pas pénalisé s‘il ne l‘a pas mentionnée 
);

Est-ce toujours ainsi que l'on procède? Une association n'aura que sa clef primaire déclarée (qui sera l'association des clefs primaires des entités qu'elle relie) et une entité aura sa clef et ses clefs étrangères déclarées dans la clef primaire puis les clefs étrangères déclarées à la ligne suivante?

Merci par avance, car si c'est bien cela, alors je pense que je comprends de mieux en mieux.

Johnny

[CinePhil]

où code_agent est une clef étrangère mais déclarée dans la clef primaire lors de la création des clefs, puis précisée comme clef étrangère référençant la table Agent.

Si tu tiens absolument à unir le client et l'agent dans la clé primaire de la table, ce qui selon moi est une hérésie, comme déjà expliqué précédemment, oui on fait comme ça.
Mais si en fait comme je le préconise la clé primaire est constituée du seul identifiant du client et que l'agent n'est qu'une clé étrangère, on doit faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
PRIMARY KEY (code_clt),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

Et attention avec le ON DELETE CASCADE ! Si tu supprimes l'agent, tu supprimes le client !

Est-ce que cette implémentation de la table Reserve est correcte?

Il semble que la table Reserve soit une table associatice entre Client et Voyage donc oui c'est bon.
Avec la précision que les tables Client et Voyage doivent être créées avant la table Reserve. Idem dans le cas précédent, la table Agent doit être créée avant la table Client.

l'implémentation de la table réservation (qui, par son nom est, je suppose, une association entre deux entités),

Non, c'est une table issue d'une entité du MCD.
Le MCD peut avoir ces associations là :
Client -0,n----Effectuer----1,1- Réservation
Chambre -0,n----|

Il eut été possible de faire ceci (et c'était peut-être le MCD de départ) :
Client -0,n----Réserver----0,n- Chambre

Et d'avoir la table suivante :
Reservation(NumClient, NumChambre, NumHotel, DateReservation, DateDebutPrevue, DateFinPrevue)
La date de réservation doit être incluse dans la clé primaire car un client peut réserver la même chambre à une autre date (revenir dans le même hôtel l'année suivante et avoir la même chambre, ça m'est arrivé dans le cas de voyages professionnels).

Est-ce toujours ainsi que l'on procède? Une association n'aura que sa clef primaire déclarée (qui sera l'association des clefs primaires des entités qu'elle relie) et une entité aura sa clef et ses clefs étrangères déclarées dans la clef primaire puis les clefs étrangères déclarées à la ligne suivante?

Non !
Quel que soit le cas, on ne met dans la clé primaire que ce qui doit y être !
Dans le cas d'une entité, c'est le plus souvent un identifiant anonyme de type entier non null non signé et auto-incrémenté.
Dans le cas d'une association, c'est le plus souvent les clés étrangères issues des tables entrant en jeu dans l'association.

Mais une table issue d'une entité et une table issue au départ d'une association peuvent toutes deux avoir, en plus de leur clé primaire, d'autres clés étrangères.

J'ai le cas suivant :
1) "Un utilisateur peut participer à plusieurs projets et un projet peut voir participer plusieurs utilisateurs."
Utilisateur -0,n----Participer----0,n- Projet

Participer(IdUtilisateur, IdProjet)

2) "L'utilisateur n'a qu'un statut dans le projet."
J'ajoute l'attribut Statut dans la table associative :
Participer(IdUtilisateur, IdProjet, Statut)

Mais j'externalise le statut pour ne pas avoir le libellé du statut recopié plusieurs fois. Je transforme la table associative en entité mais en gardant la clé primaire double composée des clés étrangères issues des deux tables de la première association et j'ajoute la clé étrangère de la table Statut :
Participer(IdUtilisateur, IdProjet, IdStatut)

J'aurais pu faire aussi :
Participer(IdParticiper, IdUtilisateur, IdProjet, IdStatut) en ajoutant une contrainte UNIQUE sur le couple IdUtilisateur + IdProjet.

[johnny3]

Merci, je comprends mieux, mais du coup, je ne comprends pas le corrigé d'un exercice du CNAM.

Ou alors, je crois que ça relève de la logique. Comme tu dis, la clef primaire ne doit pas comporter n'importe quoi et on doit voir ce qui doit toujours être associé à la clef primaire ou non.

Si on a le schéma suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Ligne (n°ligne, nom, validité)
Tronçon ( idTronçon, n°ligne, villeDépart, villeArrivée, km, prix )
Véhicule ( n°véhicule,n°ligne, km, dateRévision )
Panne (n°véhicule, idTronçon, n°ligne, date, nature )

Sur des lignes de transports composées de tronçons entre deux villes, roulent des véhicules qui peuvent tomber en panne.
Une ligne peut-être ouverte toute l’année et sa validité est de 1.
Si elle n’ouvre que pendant les périodes scolaires sa validité est de 2 si elle n’est ouverte qu'hors période des vacances scolaires, sa validité est de 3.

Le corrigé nous donne, pour la création des tables Ligne et Tronçon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE Ligne (n°Ligne INTEGER(3) ,
nom VARCHAR(35) NOT NULL ,
validité INTEGER (1)
CHECK ( validité BETWEEN 1 AND 3 ) NOT NULL,
PRIMARY KEY (n°Ligne) )

CREATE TABLE Tronçon (idTronçon INTEGER (4) ,
n°Ligne INTEGER (3) NOT NULL,
villeDépart VARCHAR (15) NOT NULL,
villeArrivée VARCHAR (15) NOT NULL,
km INTEGER (4) NOT NULL,
prix INTEGER (3) NOT NULL,
PRIMARY KEY (idTronçon, n°Ligne) ,
FOREIGN KEY (n°Ligne) REFERENCES Ligne ON DELETE CASCADE)

En fait, ici, on associe volontairement idTronçon à n°Ligne pour savoir sur quelles lignes tel véhicule a roulé.

Du coup, ça me paraît finalement logique.

Mais comme n°Ligne est une clef étrangère, cela m'avait choqué de voir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
PRIMARY KEY (idTronçon, n°Ligne) ,
FOREIGN KEY (n°Ligne) REFERENCES Ligne ON DELETE CASCADE)

Sinon, pour revenir à ce qu'on disait, la bonne conception de la table Réserve est donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Client (code_clt NUMBER(6),
nom_clt VARCHAR(20) NOT NULL,
prenom_clt VARCHAR(20) NOT NULL,
adresse_clt VARCHAR(20) NOT NULL,
codepost_clt NUMBER(5) NOT NULL,
ville_clt VARCHAR(20) NOT NULL,
PRIMARY KEY (code_clt),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

, ce qui évite justement de supprimer le client en supprimant l'agent, comme tu le disais.

Bon, finalement, cela reste une histoire de logique, mais on ne nous l'a finalement pas vraiment appris comme ça (disons, pas aussi clairement)

Il me reste encore 10 jours avant l'examen, je vais bien lire le cours de SQLPro pour refixer tout cela, car je ne veux pas me planter sur les clefs étrangères et primaires à l'examen uniquement parce que je n'ai pas bien saisi le concept.

Merci encore,
Johnny

[wazup]

si j'ai bien suivi,

vous vous posez - entre autre - la question de savoir si

entre l'entité forte (EFO)

EFO(id_efo, champ1_efo, champ2_efo)

et l'entité faible (EFA)

EFA(id_efa, champ1_efa)

suivantes,

la mise en place d'une association

EFA -- 1,1 --- APPARTIENT ---- 0,n ----- EFO

EFO(id_efo, champ1_efo, champ2_efo)
EFA(id_efa, champ1_efa)

doit générer le modele relationnel A suivant:

EFO(id_efo, champ1_efo, champ2_efo)
EFA(id_efa, id_efo, champ1_efa)

où EFA.id_efo n'est qu'une FK, et EFA.id_efa reste la PK

ou le modèle B suivant:

EFO(id_efo, champ1_efo, champ2_efo)
EFA(id_efa, id_efo, champ1_efa)

où cette fois EFA.id_efo est toujours FK, mais où la PK de EFA
devient composite , c'est-à-dire (EFA.id_efa, EFA.id_efo).

En fait le modèle A correspond au schéma :

EFA -- 1,1 --- APPARTIENT ---- 0,n ----- EFO

tandis que le modèle B :

EFA -- (1,1) --- COMPOSE ---- 0,n ----- EFO

Le deuxième schéma présente une association de composition (comme son no l'indique).

Ceci signifie un lien plus "durable" et par exemple, la cardinalité (1,1) ne peut jamais, contrairement à l'association APPARTIENT, être 0,1.

C'est à dire qu'aucune occurence (tuple) de EFA ne peut exister en-dehors d'une occurence de EFO.

C'est pourquoi dans vos corrigés il est à mon avis inadapté d'utiliser un lien de composition dans le cas voiture ---- habitant, et comme le disait cinéphil dans le cas du client qui, devient effectivement lié definitivemebnt à l'agence.

On réserve ce type d'assiciation (générant un IDENTIFIANT RELATIF (cad la PK composée de l'ancienne PK et de la nouvelle FK) à des cas particulier, comme par exempble entre CHAMBRE et HOTEL.
(à moins de tomber sur un architecte de génie qui permettrait une modularité telle qu'on pourrait demonter une chambre pour la coller dans un autre hotel!)

Je ne sais si je vous ai éclairé, je l'espere en tout cas.





OU celui-ci:

[CinePhil]

Si on a le schéma suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Tronçon ( idTronçon, n°ligne, villeDépart, villeArrivée, km, prix )

Le corrigé nous donne, pour la création des tables Ligne et Tronçon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
PRIMARY KEY (idTronçon, n°Ligne) ,
FOREIGN KEY (n°Ligne) REFERENCES Ligne ON DELETE CASCADE)

En fait, ici, on associe volontairement idTronçon à n°Ligne pour savoir sur quelles lignes tel véhicule a roulé.

Sauf que dans la description de la table, seul idTroncon constitue la clé primaire alors que dans le CREATE TABLE il est associé à n°Ligne. Il y a incohérence entre ces deux représentations.

Sinon, pour revenir à ce qu'on disait, la bonne conception de la table Réserve est donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Client (code_clt NUMBER(6),
nom_clt VARCHAR(20) NOT NULL,
prenom_clt VARCHAR(20) NOT NULL,
adresse_clt VARCHAR(20) NOT NULL,
codepost_clt NUMBER(5) NOT NULL,
ville_clt VARCHAR(20) NOT NULL,
PRIMARY KEY (code_clt),
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE)

Il manque la définition de la colonne code_agent qui porte la clé étrangère !

ce qui évite justement de supprimer le client en supprimant l'agent, comme tu le disais.

Cette requête ne supprime pas ce risque puisqu'il y a toujours ON DELETE CASCADE qui signifie : "si une ligne de la table de référence (Agent) est supprimée, alors toutes les lignes de la table Client correspondant à cette valeur de clé primaire seront supprimées.
Un exemple :
Agent(idAgent, Nom, Prenom, ...)
1, Dupont, Jean, ...
2, Durand, Pierre, ...
3, Dumoulin, Jacques, ...

Client(code_Clt, nom, prenom, code_agent, ...)
1, Hallyday, Johnny, 1, ...
2, Palmade, Pierre, 1, ...
3, Martin, Anne, 2, ...
4, Burma, Nestor, 3, ...

Si tu supprimes l'agent 1, tu supprimeras également Johnny Hallyday et Pierre Palmade, qui, vu leurs revenus, sont peut-être les deux plus importants clients de cette agence !
Le patron va être content !

Mais si c'est prévu comme ça dans le cahier des charges, pourquoi pas !

Je te souhaite le meilleur pour l'examen.
Et si ça peut te rassurer, je n'ai eu que 11,5 à l'UE de bases de données.

[wazup]

Dans le cas des lignes et tronçons, effectivement il y a incohérence.

C'est un cas où on peut concevoir le lien de composition.

La PK composite se justifie, à mon sens.

[CinePhil]

Ce qui me gène un peu dans la table tronçon, c'est l'appellation de la colonne idTroncon. id est généralement employé pour un identifiant anonyme auto-incrémenté.
Si c'est le cas, l'ajout du numéro de ligne dans la clé primaire est inutile.
On peut ajouter une colonne n°troncon et spécifier une contrainte UNIQUE sur le couple (n°ligne, n°troncon).
Troncon(idTroncon, n°ligne, n°troncon, villeDépart, villeArrivée, km, prix )

En gardant la clé primaire double, il aurait été sémantiquement plus logique d'appeler la colonne n°Troncon.

[fsmrel]

Bonsoir Johnny,


Je n’ai fait que parcourir très superficiellement vos échanges avec CinePhil, mais je peux peut-être apporter quelques compléments concernant les concepts.

Je pense que vous faites des amalgames en relation avec le terme "clé" et à ses divers attributs, tels que "primaire" et "étrangère".

Une clé primaire (primary key) est un cas particulier de ce que l’on appelle une clé candidate (candidate key). Reportez-vous à la réponse que je vous avais adressée le mois dernier, dans laquelle je donnais la définition du concept de clé candidate ainsi que celle de la dépendance fonctionnelle (DF). Je rappelle (en utilisant le terme table plutôt que celui de relation) :

Une clé candidate est un sous-ensemble d’attributs K de l’en-tête d’une table R, respectant les deux contraintes suivantes :

— Unicité. Deux tuples distincts de R ne peuvent avoir même valeur de K.
— Irréductibilité (ou minimalité). Il n’existe pas de sous-ensemble strict de K garantissant la règle d’unicité.

Sur la base de cette définition, une table peut comporter plus d’une clé candidate. Par exemple, un véhicule peut avoir comme clé candidate un numéro attribué par le SGBD, garantissant ainsi que deux véhicules n’auront pas le même numéro. A supposer que les véhicules soient immatriculés, le numéro d’immatriculation porté par la carte grise représente lui aussi une clé candidate, de même que le numéro de série.

Une clé primaire est une clé candidate, que l’on retient parmi l’ensemble des clés candidates d’une table, sachant qu’il ne peut y avoir qu’une clé primaire par table. Les clés candidates non retenues sont dites alternatives : la clé primaire est à la clé alternative ce qu’une Miss est à ses dauphines (voyez Mme de Fontenay pour plus d’information). Sur quels critères une clé est-elle élue primaire ? CinePhil vous l’a dit, elle doit être d’une stabilité à toute épreuve, il est hors de question qu’on soit obligé de la modifier. Pour justifier cela je prends souvent l’exemple du numéro de SIREN des entreprises : si vous choisissez ce numéro comme clé primaire d’une table des entreprises françaises, vous aurez bonne mine quand l’INSEE, après avoir constaté avec un mois de décalage, que le numéro de SIREN de l’entreprise DVP est faux et vous en transmettra un nouveau ; il vous faudra mettre à jour toutes les tables de votre base de données, dans lesquelles le numéro de SIREN de DVP fait l’objet d’un attribut. Si la table des entreprises a pour clé un numéro attribué par le SGBD, seule la table porteuse du numéro de SIREN sera à mettre à jour, ce qui est infiniment plus sûr et économique.

Reprenons maintenant la remontrance que vous a adressée CinePhil :

Comme je l'ai dit dans mon message précédent, je trouve

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE Véhicule ( N°v VARCHAR(9),
idHab INTEGER(6),
...
PRIMARY KEY( N°v, idHab) ,

Très mauvais !

Le couple {N°v, idHab} vérifie certes la règle d’unicité énoncé plus haut.
Vérifie-t-il la règle d’irréductibilité ? Ça dépend.

Si par habitant on entend propriétaire du véhicule, celui dont le nom figure sur la carte grise, alors la règle est violée, car le singleton {N°v} doit être clé à lui seul : à défaut, plusieurs habitants pourraient être propriétaires du même véhicule.

Supposons maintenant que l’habitant soit à considérer comme faisant partie des gens qui, selon le contrat d’assurance, sont habilités à conduire le véhicule, alors la règle n’est pas violée. En effet, ramener la clé au singleton {N°v} signifierait qu’un seul habitant serait habilité à conduire le véhicule.

Autrement dit, ce sont les énoncés des règles de gestion des données de l’entreprise qui apporteront la réponse, selon qu’elles précisent que l’habitant est propriétaire du véhicule ou simplement conducteur habilité.

Concernant les clés étrangères.

Le terme clé étrangère (foreign key) est sans doute mal choisi, car clé est un mot qui est source de confusions. Quoi qu’il en soit, une clé étrangère ne fait que représenter dans une table T2 une liste d’attributs qui entrent par ailleurs dans la composition d’une clé candidate (en général la clé primaire) d’une table T1 : cette liste d’attributs dans T2, porte le nom de clé étrangère et constitue une référence à la clé primaire de la table T1 (ou à une clé alternative, mais ne compliquons pas les choses).

Considérez votre table RESERVATION. Elle contient un attribut NumClient et cet attribut n’est pas valorisé n’importe comment : parce que vous avez codé "Foreign KEY (NumClient) References CLIENT", si vous lui affectez la valeur 123, alors cette valeur 123 doit préalablement être une valeur affectée à la clé primaire {NumClient} de la table CLIENT. A défaut, si vous aviez omis de coder "Foreign KEY (NumClient) References CLIENT" et si la valeur 123 n’avait pas été affectée à l’attribut NumClient de la table CLIENT, vous auriez engendré une incohérence, il y aurait eu viol de ce que l’on appelle l’intégrité référentielle.

A noter que "Foreign KEY (NumClient) References CLIENT" est un raccourci pour "Foreign KEY (NumClient) References CLIENT (NumClient)".

La table RESERVATION contient un couple d’attributs {NumChambre, NumHotel} : en ayant codé "Foreign KEY (NumChambre, NumHotel) References CHAMBRE", ou encore "Foreign KEY (NumChambre, NumHotel) References CHAMBRE (NumChambre, NumHotel)", vous signifiez que chaque réservation fait référence à un couple {chambre, hôtel} donné, dont la valeur figure dans la table CHAMBRE, en tant que valeur de clé primaire de cette table.

Une clé étrangère peut-elle être en même temps clé primaire, tout ou partie ?

Bien entendu, il n’y a pas à ce sujet de contrainte.
Ainsi, l’attribut NumHotel entre dans la composition de la clé primaire de la table CHAMBRE, mais en même temps une chambre doit faire référence à un hôtel existant, en vertu de quoi on doit coder pour cette table la clause "Foreign KEY (NumHotel) References HOTEL (NumHotel)".

Remarque 1

Vous n’êtes pas obligé de coder la clause FOREIGN KEY dans l’instruction CREATE TABLE, vous pouvez différer et utiliser à cet effet l’instruction ALTER TABLE. Ceci permet de ne pas être contraint de créer les tables dans un ordre imposé.

En effet, si vous codez

CREATE TABLE RESERVATION ... Foreign KEY (NumClient) References CLIENT ...

il faudra que la table CLIENT ait été préalablement créée, sinon cette nstruction sera rejetée par le SGBD.
Par contre, vous pouvez parfaitement coder :

CREATE TABLE RESERVATION ... (sans clause FOREIGN KEY)
CREATE TABLE CLIENT ...
...
ALTER TABLE RESERVATION ... Foreign KEY (NumClient) References CLIENT ...

Et tout cela avant de commencer à charger la table RESERVATION, tant qu’à faire...

Remarque 2

Dans un autre exemple, vous avez codé

CREATE TABLE Client ...
FOREIGN KEY (code_agent) REFERENCES Agent ON DELETE CASCADE) ...

La présence de CASCADE signifie que la suppression d’un agent entraîne la suppression des clients faisant référence à cet agent. Je ne sais pas si l’agence de voyage serait d’accord... Si un agent quitte l’entreprise, on affecte son portefeuille de clients à ses collègues, puis seulement après on le supprime de la base de données. En revanche, si on ne code pas ON DELETE CASCADE, le système refusera la suppression de l’agent tant qu’un client y fera référence.

[johnny3]

Bonjour à tous,

j'ai lu attentivement vos réponses aujourd'hui (j'ai malheureusement été débordé et n'ai pu revenir sur la discussion avant)

Vous m'avez tous éclairé avec des angles différents et je commence à me sentir vraiment prêt pour l'examen (lundi 2 février, de 18h30 à 21h30... les neurones vont chauffer après une journée de boulot!)

Au départ, je me demandais en effet si une clef étrangère devenait automatiquement ou non partie de la clef primaire, comment on déclarait la table (que mettait-on dans la clef primaire, y avait-il une règle générale?)

A présent, les choses sont beaucoup plus claires, plus logiques, et je distingue beaucoup mieux clef primaire, étrangère, candidate, entité forte, faible.

Le cours n'était pas des plus clairs (formation faite à distance en FOD par le CNAM) et j'ai dû compléter avec des livres et les forums pour avoir beaucoup d'exemples.

Bon... je vous ferai part du sujet et de ce que j'ai mis, histoire d'avoir vos commentaires avant la note!

Encore merci,
Johnny3