Entity-Relation Model for People and Thermos Bottles

.

Pablo Carballo

Modelling is creating an abstract representation of reality. The representation itself, (called model) is usually illustrated as a sketch or a drawing, varying depending on what we want the model for. Particularly in the case of database design, it is called Entity-Relation Model (whose acronym is MER).

Let’s suppose that for any reason whatsoever, we need to develop a system to manage information of all the thermos bottles in the world. First, we should be acquainted with the properties of thermos bottles. Simply resorting to common sense and general knowledge, we could start by supposing that a thermos bottle has a brand name, a certain capacity, and we could include information on its measures such as height and diameter, if we suppose it has a cylindrical shape. We could also add that thermos bottles have colours and express their weight in kilograms. None of these elements (brand, capacity, height, diameter, colour, weight) are attributes of thermos bottles. We can then assume that all thermos bottles in the world share this set of attributes. There is when the concept of entity appears, or more technically speaking, Class,​Thermos bottles. ​

Needless to say, my thermos bottle is not like yours, nor José’s who is living in the U.S.A. and to recall some Uruguayan costumes, he carried his cherished thermos bottle in his suitcase. We will say, then, that your thermos bottle as well as José’s and mine are instances of Thermos Bottles class and that each of them will be stored with a different register in the database..

I do not mean to be repetitive or redundant but allow me to add a new supposition to this example: in addition to storing all data of all thermos bottles, we have to register the data of their owners. Therefore, we must add a new entity: class Persona. Next, we should start to deduce which individual attributes would be relevant to our database (name, age, identification, blood group, among others).

So now we have two entities or classes: entity Thermos Bottles and entity Persona. Well then, how shall we register the information on which thermos bottles someone has? To this end, having two separate entities is not enough, we must define a relation between them.

A relation is what links two entities, depending on the kind of link. Let us imagine that there is a law according to which no one may own more than one thermos bottle at a time. In this case, the Persona -Thermos Bottle relation would be a N to 1 relation (many to one), which is expressed as follows: N individuals may have no more than a 1 thermos bottle each. The best way to represent this would be to define a thermos bottle identifier, just as another attribute of entity persona. Moreover, if it were compulsory for every person to have a thermos bottle, then, the attribute thermos bottle identifier would have to be added to entity Persona. This means that attribute cannot be left blank or without a value, or in other words, there is nobody who has not got a thermos bottle.

Nevertheless, such is not life. Usually, there is no restriction whatsoever on the number of thermos bottles that anyone can have, so it is a one to many relations meaning: an individual can have N different thermos bottles and each thermos bottle is assigned to a single person. In this scenario, it would be advisable to add another attribute to entity Thermos Bottle, that is, an identifier of the individual (Persona) who owns it. Additionally, we could impose a restriction that there may not be a thermos bottle without an owner (if it made sense to be analysed), in which case it would be compulsory to add the attribute persona identifier in entity Thermos Bottle.

We can think of a less probable case than the previous one: imagine a world where everyone can own more than one thermos bottle, but that more than one person can share each thermos bottle. It sounds as if two people had agreed to share the payment of a thermos bottle and used it half of the time each. This would be a many to many situation; a Persona-Thermos Bottle relation of N to N kind, broadly meaning that an individual can have N thermos bottles and each thermos bottle in its turn, may be assigned or shared with N people. This kind of relations are usually modelled as a new entity, which represents the same relation (we could call it entity Persona -Thermos Bottle) where two attributes are added: a persona identifier, which refers to one of the owners of the Thermos Bottle and a thermos bottle identifier, featuring one of the thermos bottles that this individual "shares” or "owns.”

The reality analysed in this example is always the same. What varies is the representative model, depending on the time this information is stored. It depends on how the reality to be modelled is interpreted, how we understand the relation between entities, and the requirements made when we are asked to model something. If there are so many possible variables of attributes and relations to be represented out of a simple reality where there are only two entities (in this post we refer to individuals and thermos bottles only to exemplify, but it could have been any other couple of entities), just imagine how complex it would be to model the actual world scenarios, where there are far more than just two entities and a relation.

That’s all, I must go, now. It is mate time and I need to pour some hot water in my thermos bottle..

Pablo Carballo Soria  @PabloXaCarballo

IT Analyst

Software Developer