Learning design standards - Entity Relationship
Hello. This article is devoted to one of the most popular, as well as many familiar, design model - ER ( Entity Relationship ), which was proposed by a scientist, in the field of computer science - Peter Chen, in 1976.
In the course of the article in simple language with simple examples from life - we will develop different versions of the diagram, which will depend on their type of communication. Let's start!
First of all, I would like to say a few words about OOP (Object Oriented Programming / Design) so that there are no problems with understanding the paradigm of the diagram itself. It is more convenient for me to abstract this model with the principle of OOP, where the essence is an object, the attributes are its characteristics, and the connections are something like an intermediary (in some cases, as a method).
The main advantage of the Entity Relationship design model is that it is universal. You can design the database (Databases), the work of any program, the principles of interaction, etc.
What you need to know at the start of the study?
- You need to know at the start that the main work is carried out on the relationship of the essence and communication. For easier perception, it is worth remembering that an entity is a noun that is in a rectangle, and a link is a verb that is in a rhombus. Let's give an example:
I think you understand what's what. Our programmer teaches Python. It seems everything is logical. But, just, what are those in the example?
- This is an indicator of the type of connection! In this example, the communication type is One to One:
We will return to the types of communication, but a little later!
PS I hope you are interested. Such diagrams you can create in Diagram Editor - Dia.
So, we have a programmer, but we don’t know anything about him ... Without what, the programmer is not a programmer?
- Without any attributes!
Let's add our example:
Yes, the attributes do not particularly distinguish our programmer from the average person ... but in the future we will fix this with new attributes! In my view, the attribute is the COLUMN (column) in the Database table.
Attributes are empty.
If in the table of your DB it is not necessary to indicate the last name (then the attribute will be optional), then the attribute must consist of two ovals: external and internal (inside which the name of the attribute is).
Identifying Attributes
You may find the underscore attribute name in the diagram - this is normal. You should not be afraid of it, because it is just an identifying attribute. That is, it is an attribute that must always be filled in, which is mandatory (primary key). As an example - the well-known id.
Well, now we need to give the programmer knowledge (what languages, technologies he knows).
- But we will not immediately enumerate with each separate attribute the components of its knowledge?
True, we will use a composite attribute ( an attribute that consists of attribute components )! I want to note that the attributes-components can also be composite. The only question is how you will implement it.
Fine. With this we were able to figure it out. Now consider the remaining types of communication!
Let's continue with the type of connection - One to many:
I will show by example:
Now our programmer is also studying Perl. Not bad.
By the way, there may be more branches.
Remains the last type of connection - Much to many things:
As usual, I will show you by example, but no longer with the Programmer, but by the example of the interrelation of the Viewer with the Film, on some service for watching Movies:
There are two controversial points. Let's start to understand.
First:
- Why does the connection look more like an entity?
- Why are there no branches?
And now we will consider another way of implementing the “Much to many things” connection, which will be slightly more difficult to write, but perhaps more understandable to those who do not know about intermediate entities:
As you may have noticed, in this example there is a “One to Many” connection type, and even several.
It's true and easy to explain. The fact is, the type of communication “Much to Much” equals two “One to Much”.
Perhaps you are interested in why we have, between the connection and the essence, two edges.
This is also easy to explain, but you need a little explanation and
attentive reading from your side.
Recall the example of “One to Many,” where, after the “Teach” link, there were names of PL (Programming Languages), which led to a large number of forks. What I showed in the last example is, in its essence, a powerful way to reduce the recording of the relationship of entities to a connection, which is called full participation . Just think, because we do not have to branch off to each PL. We can simply create the “Programming Language” entity in which we place attributes that are responsible for its name, age, power, and more. I think you understand. I advise you to use the abbreviated entry "Much to much."
Consider the final concept.
Imagine that you have the Parent and Child tables, respectively, the same entities in the diagram. Can one exist without the other? I think no. As in the biological and generally logical, there can be no apple without an apple tree .
In this example, the entity “Child” is a weak entity.
However, such cases are taking place, but this can be corrected by adding an additional attribute. In this case, the attribute “Name” is what creates such a situation, and it is called the key component of a weak entity . The name of such attributes, in ovals, is underlined by a dotted line, and the essence and connection are placed in the additional figures in which they consist.
I will present you with an example:
In conclusion, I would like to say that one of the fundamental competent cooperative work is a good explanation of the tasks set, a good presentation of the product that needs to be developed, and the design models help. Entity Relatioship is a standard that has been popular for decades. It allows you to build elegant diagrams that, with the right approach, can be complemented and modified in the future. Do not be lazy to learn. Thanks for attention!
- The book "Guide to MySQL" Authorship:
Seyed M.M. "Saied" Tahaghoghi, Hugh E. Williams
- en.wikipedia.org/wiki/Home_page
In the course of the article in simple language with simple examples from life - we will develop different versions of the diagram, which will depend on their type of communication. Let's start!
Object Oriented Design
First of all, I would like to say a few words about OOP (Object Oriented Programming / Design) so that there are no problems with understanding the paradigm of the diagram itself. It is more convenient for me to abstract this model with the principle of OOP, where the essence is an object, the attributes are its characteristics, and the connections are something like an intermediary (in some cases, as a method).
Fast start
The main advantage of the Entity Relationship design model is that it is universal. You can design the database (Databases), the work of any program, the principles of interaction, etc.
What you need to know at the start of the study?
- You need to know at the start that the main work is carried out on the relationship of the essence and communication. For easier perception, it is worth remembering that an entity is a noun that is in a rectangle, and a link is a verb that is in a rhombus. Let's give an example:
I think you understand what's what. Our programmer teaches Python. It seems everything is logical. But, just, what are those in the example?
- This is an indicator of the type of connection! In this example, the communication type is One to One:
$$ display $$ 1: 1 $$ display $$
We will return to the types of communication, but a little later!
PS I hope you are interested. Such diagrams you can create in Diagram Editor - Dia.
Attributes
So, we have a programmer, but we don’t know anything about him ... Without what, the programmer is not a programmer?
- Without any attributes!
Let's add our example:
Yes, the attributes do not particularly distinguish our programmer from the average person ... but in the future we will fix this with new attributes! In my view, the attribute is the COLUMN (column) in the Database table.
Attributes are empty.
If in the table of your DB it is not necessary to indicate the last name (then the attribute will be optional), then the attribute must consist of two ovals: external and internal (inside which the name of the attribute is).
Identifying Attributes
You may find the underscore attribute name in the diagram - this is normal. You should not be afraid of it, because it is just an identifying attribute. That is, it is an attribute that must always be filled in, which is mandatory (primary key). As an example - the well-known id.
Well, now we need to give the programmer knowledge (what languages, technologies he knows).
- But we will not immediately enumerate with each separate attribute the components of its knowledge?
True, we will use a composite attribute ( an attribute that consists of attribute components )! I want to note that the attributes-components can also be composite. The only question is how you will implement it.
Types of communication
Fine. With this we were able to figure it out. Now consider the remaining types of communication!
Let's continue with the type of connection - One to many:
$$ display $$ 1: N $$ display $$
I will show by example:
Now our programmer is also studying Perl. Not bad.
By the way, there may be more branches.
Remains the last type of connection - Much to many things:
$$ display $$ M: N $$ display $$
As usual, I will show you by example, but no longer with the Programmer, but by the example of the interrelation of the Viewer with the Film, on some service for watching Movies:
There are two controversial points. Let's start to understand.
First:
- Why does the connection look more like an entity?
To simplify the “Many-to-many” type of communication, intermediate entities are used .
- Why are there no branches?
- The viewer can subscribe to many Movies.
- Films can have many viewers who subscribe to them.
And now we will consider another way of implementing the “Much to many things” connection, which will be slightly more difficult to write, but perhaps more understandable to those who do not know about intermediate entities:
As you may have noticed, in this example there is a “One to Many” connection type, and even several.
It's true and easy to explain. The fact is, the type of communication “Much to Much” equals two “One to Much”.
$$ display $$ M: N = 1: N + N: 1 $$ display $$
Perhaps you are interested in why we have, between the connection and the essence, two edges.
This is also easy to explain, but you need a little explanation and
attentive reading from your side.
Recall the example of “One to Many,” where, after the “Teach” link, there were names of PL (Programming Languages), which led to a large number of forks. What I showed in the last example is, in its essence, a powerful way to reduce the recording of the relationship of entities to a connection, which is called full participation . Just think, because we do not have to branch off to each PL. We can simply create the “Programming Language” entity in which we place attributes that are responsible for its name, age, power, and more. I think you understand. I advise you to use the abbreviated entry "Much to much."
Weak entities
Consider the final concept.
Imagine that you have the Parent and Child tables, respectively, the same entities in the diagram. Can one exist without the other? I think no. As in the biological and generally logical, there can be no apple without an apple tree .
In this example, the entity “Child” is a weak entity.
Weak entities are those entities that cannot exist without another entity.We create the “Child” entity, in the hope that the Parent / Parents do not have children with the same name, otherwise it will be difficult to call our essence, which can be a table in the database, Normalized (the table in which the rules of data Automatism and there is a primary key identifier), because we simply can’t distinguish children.
However, such cases are taking place, but this can be corrected by adding an additional attribute. In this case, the attribute “Name” is what creates such a situation, and it is called the key component of a weak entity . The name of such attributes, in ovals, is underlined by a dotted line, and the essence and connection are placed in the additional figures in which they consist.
I will present you with an example:
Conclusion
In conclusion, I would like to say that one of the fundamental competent cooperative work is a good explanation of the tasks set, a good presentation of the product that needs to be developed, and the design models help. Entity Relatioship is a standard that has been popular for decades. It allows you to build elegant diagrams that, with the right approach, can be complemented and modified in the future. Do not be lazy to learn. Thanks for attention!
Sources
- The book "Guide to MySQL" Authorship:
Seyed M.M. "Saied" Tahaghoghi, Hugh E. Williams
- en.wikipedia.org/wiki/Home_page
Source: https://habr.com/ru/post/440556/