SQL

Course 18: Relations between elements in SQL

282 Views
Oct 3, 2021
Course 18: Relations between elements in SQL

3.1. Cardinality of a relationship

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:

  • cardinality 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.
  • cardinality 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.
  • cardinality 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.
  • cardinality 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.

3.2. Relationship 1:1

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 1:1

- 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;


Here we have arbitrarily decided that the table that carries the join information is the table Communes. It could very well have been the other way around.

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 join.

3.3. Relationship 1:p

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 1:p

- 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;


This time, the table Equipescannot directly reference idplayers, because the relationship is multiple. It is therefore the table Joueursthat must carry the join information.

Note that the selection request is written in the same way as in the case of a relation 1:1.

3.4. Relationship p:1

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 p:1

- 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;


Again, the selection relation is written in the same way as in the case of a relation 1:1.

3.5. Relationship n:p

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 1:pand 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. 1:p

Example 64. Relationship n:p

- 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);


This technique of adding a join table is widely used in practice. Note the double join in the query select, and the placement of parentheses.

Leave a Reply

Your email address will not be published. Required fields are marked *