What is ER model in DBMS?

ER model in DBMS is the high-level data model. It stands for the Entity-relationship model and is used to represent a logical view of the system from a data perspective. Creating an ER Model in DBMS is considered a best practice before implementing your database because it makes it easier for the developers to understand the database system just by looking at the ER model. It develops a conceptual design for a database that provides a very simple and straightforward view of the data. ER model makes use of ER diagrams, which are the diagrams sketched to design a database. ER diagrams are built on three basic concepts: entities, attributes, and relationships between them. An ER diagram in DBMS defines entities, associated attributes, and relationships between entities. This helps visualize the logical structure of the database. The ER diagram was proposed by Peter Chen in 1971 as a visual tool to represent the ER model. He wanted to use the ER model as a conceptual modeling approach for DBMS. Let's understand this with the help of a simple example. Suppose you need to design a database for a school. In this database, a student is an entity with attributes such as an address, name, id, age, grades, etc. The address can be another entity with attributes like city, street name, pin code, etc and there will be a relationship between them.

In the upcoming sections, we will see what is er model in DBMS and what are different components of an ER diagram are.

Component of ER Diagram

The ER diagram consists of three basic concepts:

• Entities
• Attributes
• Relationships

Entity

An entity is anything in the real world, such as an object, class, person, or place. Objects that physically exist and are logically constructed in the real world are called entities. Each entity consists of several characteristics or attributes that describe that entity. For example, if a person is an entity, its attributes or characteristics are age, name, height, weight, occupation, address, hobbies, and so on. Read this article to learn more about Entity in DBMS.

Entity set: is a group of entities of similar kinds. It can contain entities with attributes that share similar values. It's collectively a group of entities of a similar type. For example, a car entity set, an animal entity set, a bank account entity set, and so on.

Entity types: are the basic building blocks for describing the structure of data. It's a category of a particular entity in an entity set. In summary, an Entity is an object of a Type Entity and the set of all entities is called an entity set.

Entities are of two types:

1. Strong Entity – A strong entity is an entity type that has a key attribute. It doesn't depend on other entities in the schema. A strong entity always has a primary key, and it is represented by a single rectangle in the ER diagram.

Example – roll_number identifies each student of the organization uniquely and hence, we can say that the student is a strong entity type.

2. Weak Entity – Weak entity type doesn’t have a key attribute and so we cannot uniquely identify them by their attributes alone. Therefore, a foreign key must be used in combination with its attributes to create a primary key. They are called Weak entity types because they can’t be identified on their own. It relies on another powerful entity for its unique identity. A weak entity is represented by a double-outlined rectangle in ER diagrams.

For example, the address can't be used to uniquely identify students as there can be many students from the same locality. So, for this, we need an attribute of Strong Entity Type i.e ‘student’ to uniquely identify entities of Address Entity Type.

The relationship between a weak entity type and a strong entity type is shown with a double-outlined diamond instead of a single-outlined diamond. This representation can be seen in the image given below. Each entity in the set of strong entities can be uniquely identified because it has a primary key, whereas identifying each entity in a set of weak entities is not possible. After all, it has no primary key and it may contain redundant entities.

Attribute

Attributes are the characteristics or properties which define the entity type. In ER diagram, the attribute is represented by an oval.

For example, here id, Name, Age, and Mobile_No are the attributes that define the entity type Student. There are five types of attributes:

1. Simple attribute: Attributes that cannot be further decomposed into sub-attributes are called simple attributes. It's an atomic value and is also known as the key attribute. The simple attributes are represented by an oval shape in ER diagrams with the attribute name underlined.

For example, the roll number of a student, or the student's contact number are examples of simple attributes. As you can see from the above image, the attributes id is represented in the shape of an ellipse along with having the attribute name underlined.

2. Composite attribute: An attribute that is composed of many other attributes and can be decomposed into simple attributes is known as a composite attribute in DBMS. The composite attribute is represented by an ellipse.

For example, a student's address can be divided into city, state, country, and pin code or a full name can be divided into first name, middle name, and last name. As you can see from the above images, the composite attribute is divided into multiple simpler attributes.

3. Multivalued attribute: Multivalued attributes in DBMS are attributes that can have more than one value. The double oval is used to represent a multivalued attribute.

For example, the mobile_number of a student is a multivalued attribute as one student can have more than one mobile number. 4. Derived attribute: Derived attributes in DBMS are the ones that can be derived from other attributes of an entity type. The derived attributes are represented by a dashed oval symbol in the ER diagram.

For example, the age attribute can be derived from the date of birth (DOB) attribute. So, it's a derived attribute. The complete student (represented by the rectangle shape) entity type is shown by the below diagram having different attributes, where address is the composite attribute, age is the derived attribute, phone number is the multivalued attribute, and roll number is the key attribute. Check out this article to learn more about types of attributes in DBMS.

Relationship

The concept of relationship in DBMS is used to describe the relationship between different entities. This is denoted by the diamond or a rhombus symbol. For example, the teacher entity type is related to the student entity type and their relation is represented by the diamond shape.

There are four types of relationships:

1. One-to-One Relationships: When only one instance of an entity is associated with the relationship to one instance of another entity, then it is known as one to one relationship. For example, let us assume that a male can marry one female and a female can marry one male. Therefore the relation is one-to-one.

2. One-to-Many Relationships: If only one instance of the entity on the left side of the relationship is linked to multiple instances of the entity on the right side, then this is considered a given one-to-many relationship. For example, a Scientist can invent many inventions, but the invention is done by only a specific scientist.

3. Many-to-One Relationships: If only one instance of the entity on the left side of the relationship is linked to multiple instances of the entity on the right side, then this is considered a given one-to-many relationship. For example, a Student enrolls for only one course, but a course can have many students.

4. Many to Many Relationships: If multiple instances of the entity on the left are linked by relationships to multiple instances of the entity on the right, this is considered a many-to-one-relationship means relationship. For example, one employee can be assigned many projects, and one project can be assigned by many employees.

Features of ER

The features of ER Model are as follows −

• ER Diagram: ER diagrams are the diagrams that are sketched out to design the database. They are created based on three basic concepts: entities, attributes, and relationships between them. In ER diagram we define the entities, their related attributes, and the relationships between them. This helps in illustrating the logical structure of the databases.

• Database Design: The Entity-Relationship model helps the database designers to build the database in a very simple and conceptual manner.

• Graphical Representation helps in Better Understanding: ER diagrams are very easy and simple to understand and so the developers can easily use them to communicate with stakeholders.

• Easy to build: The ER model is very easy to build.

• The extended E-R features: Some of the additional features of ER model are specialization, upper and lower-level entity sets, attribute inheritance, aggregation, and generalization.

• Integration of ER model: This model can be integrated into a common dominant relational model and is widely used by database designers for communicating their ideas.

• Simplicity and various applications of ER model: It provides a preview of how all your tables should connect, and what fields are going to be on each table, which can be used as a blueprint for implementing data in specific software applications.

Learn More:

• Attributes in DBMS.
• Structure of the DBMS.
• Specialization and Generalization in DBMS.