What is an E-R model?

Let us imagine we are going to build a house, then what is the first thing that we do? Do we directly start building our house? No, we first get it designed from an architect or prepare a basic model of the house according to our requirements. Then we give this model to the builder to build. This is done so that we can have a conceptual view of what we are going to build.

Similarly, if we are going to design a database for any organisation like school, hospital, etc. we first talk to the organisation member and get to know what they want. At this time we don't do any implementation because the requirements may change. So, first, we design a model of their requirements and show to them. Once, they are satisfied then only the programmers start implementing and designing the database. So, how is this conceptual database designing done? Yes, the E-R model will help us in doing this. So, let's get started.

E-R Model (Entity-Relationship Model)

E-R model is a graphical representation of the logical structure of the database. In this model, we represent the real-world problem in the pictorial form to make it easy for the stakeholders to understand. It is also very easy for the developers to understand the system by just looking at the ER diagram. We use the ER diagram as a visual tool to represent an ER Model. ER diagram has the following three components:

Components of an E-R diagram

• Entities: Entity is a real-world thing. It can be a person, place, or even a concept. It is represented by a rectangle. Each entity has one of its attributes(attributes are properties of an entity) as the primary key which can define it uniquely. Such an entity is called a strong entity. There are some entities which cant be uniquely identified from there attributes. Such an entity is called a weak entity. We will study them in details in some other blog. Example: Teachers, Student, Course, Building, Department, etc are some of the entities of a School Management System. Student entity is represented as below.

• Attributes: An entity contains a real-world property called attribute. This is the characteristics of that attribute. It is represented by an oval. Example: The entity car has properties like Car_no, Color, Price, etc. There are many types of attributes which we will study in detail in some other blog. Here, we will have an overview of it. A simple attribute contains an atomic value which cannot be further divided. Example: Roll Number of Student.

Key Attribute: Key attribute is used to uniquley identify the records and it represents the primary key of the table. It is represented by oval and the text in it is underlined. Example: If we have Roll_no as the key attribute then it would be represneted as:

Composite Attribute: This attribute can be further divided into other attributes. Example: Name of a student can be further divided into first name, middle name and last name.

Multivalued Attribute: This attribute has more than one value. It is represented by a double oval. Example: A student can have more than one e-mail address.

Derived Attribute: The value of this attribute are derived from some other attributes. This is done mainly because the value for such attribute keeps on changing. It is represented by a dashed oval. Example: The value of age attribute is derived from the DOB(date of birth) attribute.

Note: Entities along with attributes are called entity type or schema. Example: Car entity has attributes Car_no, Color, Price. So, we represent an entity type as follows:

• Relationship: Association between two entities is called a relationship. Entities take part in the relationship. It is represented by a diamond shape. Example: Teacher teaches a student. There are three types of relationships that can exist between two entities.

One-to-One Relationship: Such a relationship exists when each record of one table is related to only one record of the other table. Example: If there are two entities 'Person' and 'Passport'. So, each person can have only one passport and each passport belongs to only one person.

One-to-Many Relationship: Such a relationship exists when each record of one table can be related to one or more than one record of the other table. Example: If there are two entities 'Owner' and 'Pet' then each owner can have more than one pet but each pet is owned by only one owner.

Many-to-Many Relationship: Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. Example: If there are two entities 'Customer' and 'Product' then each customer can buy more than one product and a product can be bought by many different customers.

Using the above knowledge of how to make an ER diagram, we can make an ER diagram for a hospital management system. We will have three entities i.e Doctor, Patient, Medicine.

In the above diagram, entity Doctor has key attribute 'doctor_id' which will be used to identify the doctors. It also has two multivalued attributes as 'specialization' and 'qualification' as a doctor may have more than one qualification and may be specialized in more than one fields. The Doctor and Patient entity have a one-to-many relationship as a Doctor may treat more than one patient. Similarly, Patient and Medicine have a many-to-many relationship as a patient may buy more than one medicine and vice-versa. 'Code' is the key attribute for Medicine which is unique for every medicine. The Patient has many attributes Patient_id, DOB, Age, etc. 'Age' is the derived attribute here. Also, it has a composite attribute 'Address' which can further be divided into two attributes 'Locality' and 'Town'.

Features of ER Model

• Graphical Representation for Better Understanding: It is very easy and simple to understand so it can be used by the developers to communicate with the stakeholders.
• ER Diagram: ER diagram is used as a visual tool for representing the model. As seen above the ER diagram makes it very easy for us to represent the real-world problems.
• Database Design: This model helps the database designers to build the database and is widely used in database design. ER model as discussed above provides a base for designing the database. Example: In the above hospital management system, we can create the relational database according to the attributes, entities and the relationship among them. We can have a separate table for each entity. The attributes can make the column of each table and the association between the tables is defined according to the relationship according to them.

Advantages of ER Model

• Simple: Conceptually ER Model is very easy to build. If we know the relationship between the attributes and the entities we can easily build the ER Diagram for the model.
• Effective Communication Tool: This model is used widely by the database designers for communicating their ideas.
• Easy Conversion to any Model: This model maps well to the relational model and can be easily converted relational model by converting the ER model to the table. This model can also be converted to any other model like network model, hierarchical model etc.

Disadvantages of ER Model

• No industry standard for notation: There is no industry standard for developing an ER model. So one developer might use notations which are not understood by other developers.
• Hidden information: Some information might be lost or hidden in the ER model. As it is a high-level view so there are chances that some details of information might be hidden.

This is all about the E-R model of DBMS. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here.

Keep Learning 🙂

Team AfterAcademy!