Discussion :

[Nathanz]

Bonjour, je débute en sql, j'ai une question concernant les clés étrangères.
Voici mes relations :
Salle (NoSalle, Categorie, NbPlaces)

UE (CodeUE, NomUE, Formation, HC, HTD, HTP, HCRes, HTDRes, HTPRes)

Groupes (Groupe, Formation, Effectif)

Reservation (NoReservation, NoSalle, CodeUE, Groupe, Formation, Nature)

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE UE ( CodeUE VARCHAR(8) PRIMARY KEY,
		NomUE VARCHAR2(40), 
                Formation VARCHAR2(40),
		HC NUMBER(5,2) ,
		HTD NUMBER(5,2) ,
		HTP NUMBER(5,2) ,
		HCRes NUMBER(5,2) DEFAULT 0,
                HTDRes NUMBER(5,2) DEFAULT 0,
                HTPRes NUMBER(5,2) DEFAULT 0
		);

Ici est ce que formation est une clé étrangère comme la clé de groupe est composé ? Devrais-je plûtot mettre:
Formation VARCHAR(40) REFERENCES Groupes(Formation)

De même :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE Reservation( NoReservation NUMBER(5,2) PRIMARY KEY,
                                         NoSalle VARCHAR(8) REFERENCES Salle(NoSalle),
                                         CodeUE VARCHAR(8) REFERENCES UE(CodeUE),
                                         Formation VARCHAR2(40),
                                         Groupe VARCHAR(8) ,
                                         Nature VARCHAR(8),
);

Dois-je mettre une référence a formation et groupe ?
Sachant que ma table Groupes ressemble à ça:

Code :

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

1
2
3
4
5
6
        
CREATE TABLE Groupes( Groupe VARCHAR(8) ,
                      Formation VARCHAR(8),
                      constraint Groupe_Formation primary key (Groupe,Formation),
                      Effectif NUMBER(4)
                     );

Je vous remercie d'avance pour vos réponses

[al1_24]

Bonjour,

Dois-je mettre une référence a formation et groupe ?

Dans l'absolu, en conservant ce modèle de données, la réponse serait oui.

Toutefois, l'usage de chaines de caractères en tant que clé primaire n'est pas optimal. On préférera utiliser un identifiant numérique auto-incrémenté, non porteur de sens. Ce qui n'empêche pas d'ajouter une contrainte d'unicité sur le libellé associé.
Ainsi, si le libellé devait être modifié, il ne sera pas nécessaire d'aller modifier toutes les lignes correspondantes dans les tables liées par une contrainte d'intégrité référentielle.

Le modèle de données ressemblerait alors à cela : (# clé primaire, * clé unique, & clé étrangère)

Salle (#Id_salle, *NoSalle, Categorie, NbPlaces)
UE (#Id_Ue, *CodeUE, NomUE, Formation, HC, HTD, HTP, HCRes, HTDRes, HTPRes)
Groupes (#Id_Groupe, *Groupe, *Formation, Effectif)
Reservation (#Id_Reservation, NoReservation, &Id_Salle, &Id_UE, &Id_Groupe, Nature)

Mais ce qui est encore plus important, c'est de commencer sa modélisation en détaillant les règles fonctionnelles qui décrivent les entités et leurs relations :

Une salle a un numéro, correspond à une catégorie et un nombre de places
Le numéro de la salle est unique
La salle peut faire l'objet de plusieurs réservations
etc

[Nathanz]

Bonjour,
Toutefois, l'usage de chaines de caractères en tant que clé primaire n'est pas optimal. On préférera utiliser un identifiant numérique auto-incrémenté, non porteur de sens. Ce qui n'empêche pas d'ajouter une contrainte d'unicité sur le libellé associé.
Ainsi, si le libellé devait être modifié, il ne sera pas nécessaire d'aller modifier toutes les lignes correspondantes dans les tables liées par une contrainte d'intégrité référentielle.

Le modèle de données ressemblerait alors à cela : (# clé primaire, * clé unique, & clé étrangère)

Salle (#Id_salle, *NoSalle, Categorie, NbPlaces)
UE (#Id_Ue, *CodeUE, NomUE, Formation, HC, HTD, HTP, HCRes, HTDRes, HTPRes)
Groupes (#Id_Groupe, *Groupe, *Formation, Effectif)
Reservation (#Id_Reservation, NoReservation, &Id_Salle, &Id_UE, &Id_Groupe, Nature)

Oui je suis d'accord que le type n'est pas judicieux mais mon problème ne réside pas dans cela.
L'attribut Formation dans UE n'est pas une clé étrangère ?
Les clés étrangères Groupe et Formation dans Reservation sont reférencées par Groupe et Formation ou respectivement par Groupe et Formation ( dans le sens où ils font références à une partie de la clé ou toute la clé de Groupes ? car la clé de Groupes est composé)
Dans mon code de je ne sais pas quoi mettre après references

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE Reservation( NoReservation NUMBER(5,2) PRIMARY KEY,
                                         NoSalle VARCHAR(8) REFERENCES Salle(NoSalle),
                                         CodeUE VARCHAR(8) REFERENCES UE(CodeUE),
                                         Formation VARCHAR2(40) REFERENCES ??,
                                         Groupe VARCHAR(8) REFERENCES ??,
                                         Nature VARCHAR(8),
);

[al1_24]

Pour répondre à ces questions, il faudrait connaître les règles fonctionnelles qui régissent ces informations...

[escartefigue]

Je confirme les propos de al1_24.

Il ne faut pas réfléchir aux tables et aux clefs étrangères, c'est une mauvaise approche.
Il faut réfléchir aux règles de gestion, les tables et les clefs étrangères ne sont qu'une conséquence de ces règles de gestion.

Voyez dans les autres sujets à quel point ce sont bien les règles de gestion qui structurent le modèle de données et donc les tables

Par exemple ici :
https://www.developpez.net/forums/d2.../#post11812511

ou encore là :
https://www.developpez.net/forums/d2...s-cotisations/

[Nathanz]

Pour répondre à ces questions, il faudrait connaître les règles fonctionnelles qui régissent ces informations...

C'est un exercice qu'on m'a donné. Je ne pense pas que le but est de remettre en question le schéma relationnel mais plutôt de l'implémenter, je n'ai aucune règle fonctionnel donné.
Donc avec du bon sens qu'elle serait les clés à référencer ?

[al1_24]

Mon bon sens et ma longue expérience me feront réclamer les règles fonctionnelles ou au moins un modèle conceptuel de données (MCD) avant de pouvoir implémenter des contraintes dans un quelconque schéma...

Le but de l'exercice est peut-être de mettre en évidence qu'il manque des informations pour aller au bout de la mise en œuvre

[Nathanz]

D'accord merci. Dernières questions, un attribut peut être référencer par une clé composé ?
Qu'elle est la différence en sql entre FOREIGN KEY et REFERENCES ?

[escartefigue]

Qu'elle est la différence en sql entre FOREIGN KEY et REFERENCES ?

FOREIGN KEY permet de déclarer une clef étrangère et REFERENCES permet de préciser à quelle colonne de quelle table cette clef étrangère fait référence. REFERENCES fait partie de la déclarative FOREIGN KEY
Voici un exemple de MCD très classique qui concerne les clients, leurs commandes, les lignes de ces commandes et les produits concernés par ces lignes.



Et le script correspondant généré automatiquement par le logiciel de modélisation (ici Looping) en fonction du choix du SGBD (ici j'ai choisi SQL server) :

Code SQL :

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
CREATE TABLE CL_client(
   CL_ident INT IDENTITY,
   CL_nom VARCHAR(50) NOT NULL,
   CL_prenom VARCHAR(50) NOT NULL,
   PRIMARY KEY(CL_ident)
);
 
CREATE TABLE PR_produit(
   PR_ident INT IDENTITY,
   PR_reference CHAR(8) NOT NULL,
   PR_description VARCHAR(50) NOT NULL,
   PRIMARY KEY(PR_ident),
   UNIQUE(PR_reference)
);
 
CREATE TABLE CO_commande(
   CO_ident INT IDENTITY,
   CO_date DATE NOT NULL,
   CL_ident INT NOT NULL,
   PRIMARY KEY(CO_ident),
   FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident)
);
 
CREATE TABLE LC_ligne_commande(
   CO_ident INT,
   LC_seq SMALLINT,
   LC_qte DECIMAL(9,2) NOT NULL,
   PRIMARY KEY(CO_ident, LC_seq),
   FOREIGN KEY(CO_ident) REFERENCES CO_commande(CO_ident)
);

On trouve dans ce script plusieurs déclaratives FOREIGN KEY qui parlent d'elles-mêmes quand on regarde le MCD correspondant

D'accord merci. Dernières questions, un attribut peut être référencer par une clé composé ?

Une FOREIGN KEY peut faire référence à plusieurs colonnes, mais ce sont plusieurs colonnes de la table T2 qui font référence au même nombre de colonnes de même type dans la table T1.

Si je reprend le même modèle de données, mais que je décide d'identifier la commande relativement au client (présence du R près des cardinalités coté commande), j'obtiens ce nouveau modèle (j'ai simplifié en supprimant les produits) :



Et le script correspondant, toujours pour SQL server, devient à son tour :

Code SQL :

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
CREATE TABLE CL_client_1(
   CL_ident INT IDENTITY,
   CL_nom VARCHAR(50) NOT NULL,
   CL_prenom VARCHAR(50) NOT NULL,
   PRIMARY KEY(CL_ident)
);
 
CREATE TABLE CO_commande_1(
   CL_ident INT,
   CO_ident INT IDENTITY,
   CO_date DATE NOT NULL,
   PRIMARY KEY(CL_ident, CO_ident),
   FOREIGN KEY(CL_ident) REFERENCES CL_client_1(CL_ident)
);
 
CREATE TABLE LC_ligne_commande_1(
   CL_ident INT,
   CO_ident INT,
   LC_seq SMALLINT,
   LC_qte DECIMAL(9,2) NOT NULL,
   PRIMARY KEY(CL_ident, CO_ident, LC_seq),
   FOREIGN KEY(CL_ident, CO_ident) REFERENCES CO_commande_1(CL_ident, CO_ident)
);

On voit cette fois une déclarative FK qui référence bien deux colonnes :
FOREIGN KEY(CL_ident, CO_ident) REFERENCES CO_commande_1(CL_ident, CO_ident)