1:1 : chaque élément de la première table est en relation avec un unique élément de la seconde table. La réciproque est vraie : chaque élément de la seconde table n'a qu'un seul élément de la première table comme antécédent.
1:p : chaque élément de la première table peut avoir plusieurs éléments de la seconde table en relation. En revanche, les éléments de la seconde table ne peuvent avoir qu'un seul antécédent.
p:1 : chaque élément de la première table ne peut avoir qu'un unique élément de la deuxième table en relation. En revanche, un élément de la seconde table peut avoir plusieurs éléments de la première comme antécédent.
n:p : chaque élément de la première table peut avoir plusieurs éléments de la seconde en relation, et les éléments de la seconde table peuvent aussi avoir plusieurs antécédents.
1:1 existe par exemple entre une commune et un maire : une commune ne peut avoir qu'un seul maire, et on ne peut pas être maire de plusieurs communes. Établissons la structure de tables qui permet de stocker cette relation.
Exemple 61. Relation
1:1
-- table des communes create table Communes ( id int primary key, nom_commune varchar(32), id_maire int ) ; -- table des maires create table Maires ( id int primary key, nom_maire varchar(32) ) ; -- id_maire doit référencer un maire alter table Communes add constraint FK_communes_maires foreign key CommunesBis(id_maire) references Maires(id) ; -- requête permettant de dresser la liste des communes avec leur maire select nom_commune, nom_maire from Communes join Maires on Communes.id = Maires.id ;
Communes. Cela aurait très bien pu être l'inverse.
Nous avons déjà écrit une telle jointure lorsque nous avons construit la liste des marins et leurs communes de naissance. Nous verrons plus en détails l'utilisation de
join.
Exemple 62. Relation
1:p
-- création de la table des joueurs create table Joueurs ( id int primary key, nom_joueur varchar(32), id_equipe int ) ; -- création de la table des équipes create table Equipes ( id int primary key, nom_equipe varchar(32) ) ; -- id_equipe doit référencer l'id d'une équipe alter table Joueurs add constraint FK_joueur_equipe foreign key Joueurs(id_equipe) references Equipes(id) ; -- requête permettant de dresser la liste des joueurs des équipes select nom_equipe, nom_joueur from Joueurs join Equipes on Joueurs.id_equipe = Equipes.id order by nom_equipe, nom_joueur ;
Equipes ne peut pas référencer directement des
id de joueurs, du fait que la relation est multiple. C'est donc la table
Joueurs qui doit porter l'information de jointure.
On notera que la requête de sélection s'écrit de la même façon que dans le cas d'une relation
1:1.
p:1, dans le modèle relationnel, est le symétrique d'une relation
1:p. Dans le contexte du mapping objet / relationnel, les choses sont un peu différentes. On peut prendre un autre exemple : la relation qui existe entre les communes de naissance des marins, et les marins. Une même commune voit naître plusieurs marins.
Exemple 63. Relation
p:1
-- création de la table des communes create table Communes ( id int primary key, nom_commune varchar(32) ) ; -- création de la table des marins create table Marins ( id int primary key, nom_marin varchar(32), id_commune int ) ; -- une commune de naissance doit être référencée par un marin alter table Marins add constraint FK_marins_communes foreign key Marins(id_commune) references Communes(id) ; -- construction de la table des communes et des marins select nom_commune, nom_marin from Communes join Marins on Communes.id = Marins.id_commune ;
1:1.
n:p est en revanche un peu plus ardu. Comme nous l'avons vu dans les cas
1:p et
p:1, la table qui se trouve du côté multiple de la relation ne peut pas porter l'information de jointure. Comme dans le cas
n:p, les deux tables portent une relation multiple, aucune d'entre elles ne peut porter cette information.
On résoud ce problème en créant une table technique, intermédiaire, appelée
table de jointure
. Dans le cas de la relation entre nos musiciens et leurs instruments (un musicien peut jouer de plusieurs instruments, et un même instrument peut être pratiqué par plusieurs musiciens), cette table sera elle-même en relation
1:p avec chacune des tables musiciens et instruments. Écrivons ceci sur un exemple.
Exemple 64. Relation
n:p
-- création de la table des musiciens create table Musiciens ( id int primary key, nom_musicien varchar(32) ) ; -- création de la table des instruments create table Instruments ( id int primary key, nom_instrument varchar(32) ) ; -- création de la table de jointure create table Musiciens_Instruments ( id_musicien int not null, id_instrument int not null, primary key(id_musicien, id_instrument) ) ; -- la table de jointure doit référencer un musicien alter table Musiciens_Instruments add constraint FK_musiciens_instruments foreign key Musiciens_Instruments(id_musicien) references Musiciens(id) ; -- la table de jointure doit référencer un instrument alter table Musiciens_Instruments add constraint FK_instruments_musiciens foreign key Musiciens_Instruments(id_instrument) references Instruments(id) ; -- la requête permettant d'afficher les musiciens et leurs instruments select nom_musicien, nom_instrument from ((Musiciens join Musiciens_Instruments on Musiciens.id = Musiciens_Instruments.id_musicien) join Instruments on Instruments.id = Musiciens_Instruments.id_instrument) ;
select, et le placement des parenthèses.