The cardinality of a relation gives us how the elements of one table relate to the elements of another table. There are four possible cardinalities:
1:1: each element of the first table is related to a single element of the second table. The converse is true: each element of the second table has only one element of the first table as antecedent.
1:p: each element of the first table can have several elements of the second table in relation. On the other hand, the elements of the second table can have only one antecedent.
p:1: each element of the first table can have only one element of the second related table. On the other hand, an element of the second table can have several elements of the first as an antecedent.
n:p: each element of the first table can have several elements of the second in relation, and the elements of the second table can also have several antecedents.
To each of these types of relationship correspond a way of constructing a table structure, and a structure of primary keys / foreign keys.
1:1For example, a relationship exists between a municipality and a mayor: a municipality can only have one mayor, and one cannot be mayor of several municipalities. Let us establish the structure of tables which makes it possible to store this relation.
Example 61. Relationship
- table of communes create table Communes ( id int primary key , common_name varchar (32), id_maire int ); - table of mayors create table Mayors ( id int primary key , mayor_name varchar (32) ); - id_maire must reference a mayor alter table Communes add constraint FK_communes_maires foreign key CommunesBis (id_maire) references Mayors (id); - request to list the municipalities with their mayor select nom_commune, nom_maire from Communes join Maires on Communes.id = Maires.id;
We have already written such a join when we built the list of sailors and their birth towns. We will see in more detail the use of
We have already seen a relationship of this type in our examples: it is the one between a player and his team. The same player belongs to only one team, but a team is made up of several players. Let’s see how we can write such a relation.
Example 62. Relationship
- creation of the players table create players table ( id int primary key , player_name varchar (32), team_id int ); - creation of the team table create team table ( id int primary key ,varchar team_name (32) ); - team_id must reference the team id alter table Players add constraint FK_joueur_equipe foreign key Players (team_id) references Teams (id); - request allowing to draw up the list of the players of the teams select nom_equipe, nom_joueur from Players join Equipes on Players.id_equipe = Equipes.id order by nom_equipe, nom_joueur;
Note that the selection request is written in the same way as in the case of a relation
p:1, in the relational model, is the symmetrical of a relation
1:p. In the context of object / relational mapping, things are a bit different. We can take another example: the relationship that exists between the communities of birth of seafarers and seafarers. The same municipality sees the birth of several sailors.
Example 63. Relationship
- creation of the table of communes create table Communes ( id int primary key , common_name varchar (32) ); - creation of the table of sailors create table of sailors ( id int primary key , sailor_name varchar (32), common_id int ); - a municipality of birth must be referenced by a sailor alter table Marins add constraint FK_marins_communes foreign key Marins (id_commune) references Communes (id); - construction of the table of communes and sailors select nom_commune, nom_marin from Communes join Marins on Communes.id = Marins.id_commune;
The first three cases we have seen all look alike. The case of the relationship
n:pis, on the other hand, a little more difficult. As we saw in cases
p:1, the table on the multiple side of the relation cannot carry join information. As in the case
n:p, the two tables carry a multiple relation, neither of them can carry this information.
This problem is solved by creating a technical, intermediate table , called the join table . In the case of the relationship between our musicians and their instruments (a musician can play several instruments, and the same instrument can be played by several musicians), this table will itself be in relation with each of the musicians and instruments tables. Let’s write this on an example.
Example 64. Relationship
- creation of the table of musicians create table Musicians ( id int primary key , musician_name varchar (32) ); - creation of the instrument table create table Instruments ( id int primary key , instrument_name varchar (32) ); - creation of the join table create table Musiciens_Instruments ( musician_id int not null , instrument_id int not null , primary key (musician_id, instrument_id) ); - the join table must reference a musician alter table Musicians_Instruments add constraint FK_musiciens_instruments foreign key Musicians_Instruments (musician_id) references Musicians (id); - the join table must reference an instrument alter table Musicians_Instruments add constraint FK_instruments_musiciens foreign key Musicians_Instruments (instrument_id) references Instruments (id); - the query allowing to display the musicians and their instruments select musician_name, instrument_name from ((Musicians join Musicians_Instruments on Musicians.id = Musicians_Instruments.id_musicien) join Instruments on Instruments.id = Musicians_Instruments.id_instrument);