Design of Databases - Part 2
Design of tables and their relationships
In the first part of this article we had created the model entity relationship of the example of tourist routes and we had the following:
Where we mentioned that a tourist can make several tours, and that a tour is made by several tourists, so we have a relationship of many to many between both entities: Tourists and Tours. In this model we have only two entities: Tourist and Travel. "Realiza" is the relationship that exists between the two entities.
Now, if we know that for each tourist we need to register their name, age, nationality and sex and that the tours are also identified with a route number, a name and a description of the route and the tourist attractions that are visited, as well as , the date in which each tourist makes a tour, the model can be completed with this information as follows:
The date is not placed on the route, since the entity Recorrido is a catalog of the tourist tours that are offered and that are carried out on different dates by different tourists, so the date is placed in the relationship between both entities.
With this information, we can now describe something similar to this: "John Smith, a 30-year-old British man, on January 20, 2008, the number 1 route - Teotihuacan, Tour of the archaeological zone, climbing the pyramid of the sun, food in restaurant xxx, .... etc. etc."
When we have entities with a lot of data, it does not become practical to place them all in the entity-relationship model, however, we can place the most relevant data to make sense of the model.
In the Recorrido entity, the route number will be the value that uniquely identifies each record, that is, each route, therefore there will not be two routes with the same number.
In the case of the Tourist entity, we do not have until now an attribute that uniquely identifies each tourist, we can not use the name, since several people can be called equal, the same would happen with age, sex and nationality , so we will add an attribute that is a unique identifier for each record or tourist, this identifier will be called idTurista and will have consecutive numerical values.
Our next step now is to carry out the transformation of the entindad-relation model to the relational model, that is, to design the tables of the database where we will store the information of this system.
Transformation rules
The three basic rules for transforming the entity-relationship model to the relational model are:
- Every entity is transformed into a table.
- The relationships of many to many (N: M) are transformed into tables.
- One-to-many relationships (1: N) give rise to either a unique identifier propagation or a table.
Taking into account the first rule, we transform the two entities of the model into two corresponding tables:
As mentioned above and we can see in the figure of the tables, the Tourist table contains an additional attribute (field) called idTurista, which becomes the primary key of this table, it is the attribute that allows us to establish a unique identifier for every tourist. For the case of the Tour table, the unique identifier (primary key) will be the route number.
The relational model imposes a series of inherent restrictions:
- In a table there can not be two equal registers (mandatory primary key).
- The order of the records and the attributes is not relevant.
- Each attribute can only take a single value of the domain over which it is defined, with the exception of the repetitive fields that are allowed in FileMaker.
- No attribute that is part of the primary key can have null or empty values (entity integrity).
The restrictions with respect to the attributes, we will see later.
Meanwhile, for the second transformation rule we have that: The N: M ratios give rise to a table whose primary key or unique identifier will be the concatenation of the main identifiers of the entities that are linked to this relation. In this way, the fields that form the primary key of this new table are foreign or foreign keys with respect to the tables in which these fields are primary key.
The "Realiza" relationship gives rise to a table that we will call Realiza, (the name can be different), which also contains the attributes that form the primary key, the date field.
idTurista refers to the unique identifier of the tourist table, but in this table it becomes a foreign key that is part of the primary key of the Realiza table. number is the primary key in the Route table, but it becomes a foreign key in this table and is also part of the primary key of the Realize table.
The table performs links to the Tourist and Travel tables, therefore its primary key or unique identifier is formed by two fields, that is, it is a composite primary key.
Now, what happens if the same tourist makes a same trip twice, either because he liked it, because he wanted to repeat the experience, etc. for whatever it is, but if the same tourist does it twice, it is obvious that he can not do it the same day if we take into account that they are full day trips.
Taking into account the rules and restrictions mentioned above, in which we said that in a table there can not be two equal records, the value that would differentiate these two registers is precisely the date. Therefore, for the Realiza table, we add the date field to the primary key. In this way, the primary key of the Realiza table will be composed of the fields: idTurist, number and date.
Finally, when designing the three resulting tables and linking them to establish the relationship between them, the relational diagram is as follows:
We can not yet say that the tables will be built in this way in FileMaker, before building them we need to take into account some considerations on the domain of the attributes, multivalued values, functional peaks and restrictions regarding the information that will be stored in the database .
All these points will be discussed in the next part of this article, where we will see the normalization schemes to reduce data redundancy as much as possible.
