Saltar al contenido

Basis of databases: Entity-relationship model

21 mayo, 2021

The databases They are a great pillar of current programming, since they allow us to quickly and efficiently store and use huge amounts of data with some ease. At present, the majority of the relational databases (dominated by different managers through the language SQL, to a large degree).

But now we are going to give a little review of the most essential of the entity-relationship model, which is and has been for years the best way to represent the structure of these relational databases (or to represent their schemas).

What is the entity-relationship model?

As I have already commented, this model is only and exclusively a method that we have to design these schemes that later we must implement in a manager of BBDD (databases). This model is represented through diagrams and is made up of several elements.

This model usually, in addition to having a diagram that helps to understand the data and how they are related to each other, must be completed with a short summary with the list of the attributes and relationships of each element.

Elements of the entity-relationship model

Entity

Entities represent things or objects (whether real or abstract), which are clearly differentiated from each other.

In order to follow an example during the article I will add examples about a mechanical workshop, where the following entities could be created:

  • Cars (physical object): contains the information of each workshop.
  • Employee (object physical): worker information.
  • Employee title (thing abstract) – Employee role information.

These entities are represented in a diagram with a rectangle, like the following.

Entities

Attributes

Attributes define or identify entity characteristics (is the content of this entity). Each entity contains different attributes, which give information about this entity. These attributes can be of different types (numeric, text, date …).

Following the example from before we can analyze the attributes of our entity “Cars“, which will give us information about the cars in our supposed workshop.

Some possible attributes would be the following: chassis number, enrollment, Owner’s ID, brand, model and many others that complement the information of each car.

Attributes are represented as circles descending from an entity, and it is not necessary to represent all of them, but the most significant ones, as below.

Attributes

In a relational model (already implemented in a database) an example of a table inside a BBDD it could be the following.

Chassis number Enrollment Owner’s ID
5tfem5f10ax007210 4817 BFK 45338600L
6hsen2j98as001982 8810 CLM 02405068K
5rgsb7a19js001982 0019 GGL 40588860J

This example is with three attributes, but a car could have hundreds (if necessary) and they would follow the same column structure, after implementing it in a BBDD.

Relationship

It is a link that allows us to define a dependency between several entities, that is, it allows us to require that several entities share certain attributes in an indispensable way.

For example, the employees of the workshop (of the entity “Employees“) have a position (according to the entity”Employee title“). That is, an attribute of the entity”Employees“It will specify what position you have in the workshop, and it must be identical to the one that already exists in the entity”Employee title“.

Relationships are shown in diagrams as diamonds, which are attached to entities by lines.

Relationship

I, from my point of view, better understand this in a table (of an implementation in a BBDD), so I am going to give an example of how it would be represented (highlighted the relationship, which we will see later on how it would be done).

Employees

Name DNI Position
Carlos Sanchez 45338600L 001
Pepe Sanchez 02405068K 002
Juan Sanchez 40588860J 002

Employee title

Position ID Description
001 Foreman
002 Mechanical

Cardinality relationships

We can find different types of relationships depending on how the entities participate in them. That is, in the previous case, each employee can have a position, but the same position can be shared by several employees.

This complements the representations of the relationships, by an interval at each end of the relationship that specifies how many objects or things (of each entity) can intervene in that relationship.

One by one: An entity is related only to another and vice versa. For example, if we had an entity with different chassis and another with license plates, we should determine that each chassis can only have one license plate (and each license plate a chassis, not more in any case).

One-to-one relationship

One to many or several to one: determines that a record of an entity can be related to several of another entity, but in this entity exist only once. As it has been in the previous case of the workshop worker.

One-to-many relationship

Many to many: determines that one entity can be related to another with none or more records and vice versa. For example, in the workshop a car can be repaired by several different mechanics and those mechanics can repair several different cars.

Many-to-many relationship

The numerical indicators first indicate the minimum number of records in a relation and later the maximum (if there is no limit it is represented by a “n“).

Keys

It is the attribute of an entity, to which we apply a restriction that distinguishes it from the other records (not allowing the specific attribute to be repeated in the entity) or applies a link to it (exactly as we mentioned in the relationships). These are the different types:

Superkey: apply a key or restriction to various attributes of the entity, in order to ensure that as a whole they are not repeated several times and thus not be able to enter into doubts when wanting to identify a record.

Primary key: unambiguously identifies a single attribute not allowing it to be repeated in the same entity. As would be the license plate or the chassis number of a car (the same cannot exist twice).

Foreign key or foreign key: this field has to be strictly related to the primary key of another entity, in order to require that this key previously exist. Previously we have talked about it when we commented that an employee must have a position (which we have represented numerically), so if we tried to give him a non-existent position, the database manager would return an error.

Resume

This has been just a review of what is the entity-relationship model, without going into great detail.

Also, from my point of view, I think it is a good way to correctly design the databases, although sometimes it is faster to implement it directly in our database manager. BBDD without the need to create a large diagram, but by using simpler notes.

And how do you design the databases?

More information | Wikipedia