In relational database theory, the process that allows us to obtain the logical model from the conceptual model is particularly important. When designing a relational database, the reference conceptual model is the E/R (Entity Relationship) schema, from which we obtain the relational logical model. To be clear, relational databases are those that are queried with SQL.

In the translation from the conceptual model to the logical schema, the entities are translated first. Specifically, for each entity, a table is created using the entity's simple attributes for the fields. The table key is chosen among the entity's candidate keys.

The associations introduced in this post are then translated. There are two strategies for associations, to be used depending on the type of association. Associations can be incorporated into the entity tables or new tables can be created. In the case of many-to-many associations, a table must always be created; in other cases, the relationship can be incorporated into the entity tables.

The Relational Model

The relational model is probably the most widespread logical model in databases, and certainly the most studied. The relational model is called this way. Because every database table is a relation, understood in the sense of mathematical logic. The values assumed by a row in the table are called tuple or record. In the relational model, the constraints that can exist within relationships or between different relationships are of fundamental importance. If we wanted to summarize the essential concepts, we would have to list at least these two constraints:

• Primary Key. A primary key is defined as a (non-redundant) subset of attributes for which each value uniquely identifies each tuple in the relation. It is important to note that the values of a primary key must always be entered; it is also said that it does not allow null values.

• Foreign Key. Intuitively, a foreign key is an attribute (or set of attributes) that links one relation to another, requiring a correspondence between the attributes common to the two tables. In particular, the referential integrity constraint between R1 and R2 requires that if F is the foreign key of R2, then all values of F that appear in R2 must appear as primary key values in R1.

Rules for Deriving from the Conceptual Model to the Logical Model

Considering that columns refer to attributes (table fields) and rows refer to tuples or records, we can list seven different conversion rules that allow you to move from the conceptual model to the logical model in a relational database. The first four rules can refer to the conversion of entities, while the last three concern associations.

1. Each entity becomes a table.
2. The attributes of the entity become columns of the table
3. Columns inherit the characteristics of the attributes
4. The primary key of the entity becomes the key of the table
5. If the association is 1 to N, a column is added on the N side, corresponding to the primary key of the 1 side. This column is the foreign key of the relationship.
6. If the association is 1 to 1, you can choose where to add the column (always identified in the primary key of one of the two tables).
7. If the association is N to N, a third table is added, which contains the keys of the other two tables.