We want to collect information on the results of sports competitions in a world championship held in different locations on different dates throughout the year. In each race, competitors finish in a certain position and, based on that position, can earn points, which will determine the winner at the end of the year.

Trying to obtain a conceptual model, we immediately identify the entities Competitors and Races. At this point, a fundamental question arises: how to track the scores of each competitor? Each competitor's score cannot be an attribute of Competitors or Races. In fact, in the former case, we would have to duplicate all the competitor's personal information just to assign them a score. In the second case, we would first insert the competitor's attributes into Races and then duplicate all the race information for each competitor to whom we want to assign a score.

A possible modeling solution instead consists of introducing a non-immediately visible entity, the Results entity, which links the competitor's result in that specific race. The E/R model is the one in the figure, which highlights the 1:N associations that link both the instances of the entity Competitors to those of Results, and the instances of Tenders and Results.

The logical model

At this point, applying the rules of derivation, we obtain the so-called logical model, in which we highlight the primary keys and foreign keys.

In particular, primary keys are underlined while foreign keys are indicated with an asterisk.

Competitors(Code, Surname, Name, Day, Month, BirthYear, Nationality)
Races(RaceId, Day, Month, Year, Location, Description, Name)
Results(CodRis, Position, Score, RaceId*, Code*)

To determine the correct data type for each attribute, we can choose the DBMS or application we prefer to create our database. There are many possible choices, ranging from simple applications like Access, to more complex solutions like MySQL, MariaDB, or PostreSQL, where the SQL language can be used directly to create the various tables.