Entitiy Relationship Modeling
Donald Green
What is ER modeling?
Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It is a high-level data model that defines the data elements and their relationship for a particular software system. The ER model is used to represent real world objects.
An entity is a thing or object in the real world that is different from its natural environment. For example, each employee of an organization is a separate entity. The following are some of the main characteristics of objects.
- An entity has a set of properties.
- Object properties can have values.
Let's look at our first example again. An employee of an organization is a subject. If "Peter" is a programmer ( employee ) at Microsoft, he may have attributes ( properties) such as name, age, weight, height, etc. Obviously, these contain values ​​related to him.
Each attribute can have values . In most cases, one attribute has one value. But it is possible for attributes to have multiple values . For example, Peter's age has one meaning. But its "phone number" property can have multiple values.
Entities can have relationships with each other. Let's look at the simplest example. Let's assume that every Microsoft programmer has a computer. Clearly, Peter's computer is also an entity. Peter uses this computer and the same computer is used by Peter. In other words, there is a mutual connection between Peter and his computer.
In entity relationship modeling, we model entities, their attributes, and relationships between entities.
Enhanced Entity Relationship (EER) model
The Enhanced Entity Relationship (EER) model is a high-level data model that provides extensions to the original Entity Relationship (ER) model . EER Models supports more detailed designs. EER Modeling emerged as a solution for modeling very complex databases.
EER uses UML notation. UML is an acronym for Unified Modeling Language; is a general-purpose modeling language used in the design of object-oriented systems. Entities are represented as class diagrams. Relationships are represented as associations between entities. The diagram shown below illustrates an ER diagram using UML notation.
Why use ER Model?
Now you might be thinking, why use ER modeling when we can just create a database and all of its objects without ER modeling? One of the challenges faced when designing a database is that designers, developers, and end users tend to perceive data and how it is used differently. If this situation is left unchecked, we may end up with a database system that does not meet user requirements.
Communication tools that are understandable to all stakeholders (both technical and non-technical users) are critical in building database systems that meet user requirements. ER models are examples of such tools.
ER diagrams also improve user productivity because they can be easily converted to relational tables.
Example: ER diagram for video library "MyFlix"
Let's now work with the MyFlix Video Library database system to help understand the concept of ER diagrams. We will use this database for all practical work in the remainder of this tutorial.
MyFlix is ​​a legal entity that rents out movies to its members. MyFlix stores its entries manually. Now management wants to move to a DBMS
Let's take a look at the steps to develop an EER chart for this database
- Define the entities and define the relationships that exist between them.
- Each entity, attribute, and relation should have appropriate names that can be easily understood by non-technical people as well.
- Relationships don't have to be directly related to each other. Relationships must connect entities.
- Each attribute in a given object must have a unique name.
Objects in the "MyFlix" library
Objects to be included in our ER diagram:
- Members - This organization will store member information.
- Movies - this entity will contain information about movies
- Categories − This entity will contain information that puts movies into various categories such as Drama, Action, Epic, etc.
- Movie Rentals - This organization will store information about movies that are rented out to its members.
- Payments - This organization will store information about payments made by members.
Defining Relationships Between Entities
Members and films
The following is true regarding the interaction between two objects.
- Member can rent more than 1 movie in a given period.
- A film may be rented by more than one participant in a given period.
From the scenario above, we can see that the nature of the relationship is many-to-many. Relational databases do not support many-to-many relationships. We need to introduce a connection object . This is the role played by the MovieRentals object. It has a one-to-many relationship with the members table and another one-to-many relationship with the movies table.
Films and categories of persons
The following is true for movies and categories.
- A movie can only belong to one category, but a category can have multiple movies.
From this we can conclude that the nature of the relationship between the categories and the movie table is one-to-many.
Participants and payment organizations
The following is true for members and payments
- A member can only have one account, but can make multiple payments.
From this we can conclude that the nature of the relationship between participants and payment institutions is one-to-many.
Now let's create an EER model using MySQL Workbench
In the MySQL workspace, click the "+" button
Double-click the Add Diagram button to open the ER Diagram workspace.
The following window appears
Let's take a look at two objects we'll be working with.
The table object allows us to create objects and define attributes associated with a particular object.
The Place Relations button allows us to define relationships between objects.
Members of the organization will have the following attributes
- Membership number
- Full names
- Floor
- Date of Birth
- Physical adress
- mailing address
Let's now create the members table
1. Drag a table object from the toolbar.
2. Place it on the workspace. Entity appears with table name 1
3. Double click on it. The properties window shown below will appear
Next ,
- Change table 1 to Members
- Change default idtable1 to position_number
- Click on the next line to add the next field
- Do the same for all attributes specified in the member entity.
Your properties window should now look like this.
Repeat the above steps for all identified objects.
Your chart workspace should now look like the one below.
Allows you to create a relationship between members and movie rentals
- Select place relation using existing columns
- Click on membership_number in the Members table
- Click on the link number in the MovieRentals table
Repeat the above steps for other relationships. Your ER diagram should now look like this:
Summary
- The full form of ER is entity and relationship diagrams. They play a very important role in the database design process. They serve as a non-technical means of communication for technical and non-technical people.
- Entities represent things in the real world; they can be conceptual like a sales order or physical like a customer.
- All faces must have unique names.
- ER models also allow database developers to identify and define relationships that exist between entities.
The entire ER model is attached below. You can just import it into MySQL Workbench
