📙 Definition
An ER diagram (Entity Relationship Diagram) is a graphical representation of entities, attributes, and relationships in a database. It is a conceptual data model that is used to represent the data requirements of a system or organization.
In an ER diagram, entities represent real-world objects such as customers, products, and orders, while attributes represent the characteristics of these entities, such as the name, address, and phone number of a customer. Relationships represent the connections between these entities, such as a customer placing an order for a product.
ER diagrams use symbols and notations to represent entities, attributes, and relationships. The most common symbols used in ER diagrams are rectangles for entities, diamonds for relationships, and ovals for attributes.
An example of ER diagram is shown below -
📙 Why should we use ER Diagrams?
ER diagrams are used for a variety of reasons in the database design process. Some of the main reasons why ER diagrams are used include:
Data modeling: ER diagrams provide a way to visually represent the structure of a database, making it easier for designers to model the data and relationships between entities.
Communication: ER diagrams serve as a communication tool between designers, developers, and stakeholders, allowing them to discuss and refine the data model and ensure that everyone has a common understanding of the database structure.
Documentation: ER diagrams provide a detailed documentation of the database structure, making it easier to maintain and modify the database over time.
Optimization: ER diagrams can be used to identify potential performance issues and to optimize the database structure for improved query performance.
Scalability: ER diagrams can be used to design databases that are scalable, allowing for growth and expansion as the needs of the organization change over time.
📙 Symbols used in ER Diagram
ER diagrams, also known as Entity-Relationship diagrams, are graphical representations of entities and their relationships to each other. The following are the symbols and notations used in ER diagrams:
Entity: An entity represents a real-world object, such as a person, place, thing, or event. It is depicted as a rectangle with the entity name written inside.
Attribute: An attribute represents a characteristic or property of an entity. It is depicted as an oval connected to the entity rectangle by a line.
Primary key: A primary key is a unique identifier for an entity. It is depicted as an underline beneath the attribute that serves as the primary key.
Relationship: A relationship represents an association between two or more entities. It is depicted as a diamond shape, with the relationship name written inside.
Cardinality: Cardinality describes the number of instances of one entity that can be associated with another entity. It is depicted as a line with a number at each end. The numbers indicate the minimum and maximum number of entities that can be associated.
Crow's Foot Notation: This is a popular notation used to represent cardinality in ER diagrams. It uses symbols to represent the minimum and maximum cardinality of a relationship. A single line indicates a minimum cardinality of one, while a crow's foot indicates a minimum cardinality of zero. A circle represents a maximum cardinality of one, while a straight line represents a maximum cardinality of many.
Weak Entity: A weak entity is an entity that cannot exist without a relationship with another entity. It is depicted as a rectangle with double lines.
Inheritance: Inheritance is used to represent a hierarchical relationship between entities. It is depicted as a triangle connected to the parent entity, with the child entity connected to the triangle.
These are the main symbols and notations used in ER diagrams. Understanding these symbols is essential to creating effective ER diagrams.
📙 ER Diagram Components
An ER diagram (Entity-Relationship diagram) is a visual representation of the entities, attributes, and relationships that exist within a database. The following are the main components of an ER diagram:
♦ Entities
An entity represents a real-world object, such as a person, place, thing, or event. Entities are depicted as rectangles with their names written inside.
Entities are the objects or concepts in the real world that are being modeled in a database system. Entities are represented in an ER diagram as rectangles, and the name of the entity is written inside the rectangle. Some important topics related to entities include:
Entity Type: An entity type is a category of entities that share common characteristics or attributes. For example, in a library database, "book," "patron," and "borrowing" would be three different entity types.
Entity Instance: An entity instance is a specific occurrence or member of an entity type. For example, the book titled "To Kill a Mockingbird" would be an instance of the "book" entity type.
♦ Attributes
An attribute represents a characteristic or property of an entity. Attributes are depicted as ovals connected to the entity rectangle by a line.
In an ER diagram, attributes are used to describe the characteristics or properties of entities. There are several types of attributes that can be used in an ER diagram, including:
Img Ref - whatisdbms
Simple Attribute: A simple attribute is an attribute that cannot be further divided into smaller sub-attributes. For example, the "name" attribute of a "person" entity is a simple attribute.
Composite Attribute: A composite attribute is an attribute that can be further divided into smaller sub-attributes. For example, the "address" attribute of a "customer" entity might be further divided into sub-attributes like "street," "city," and "zip code."
Img Ref - whatisdbms
Single-Valued Attribute: A single-valued attribute is an attribute that can have only one value for each instance of an entity type. For example, the "birthdate" attribute of a "person" entity is a single-valued attribute.
Multi-Valued Attribute: A multi-valued attribute is an attribute that can have multiple values for a single instance of an entity type. For example, a "customer" entity might have a "phone number" attribute that can have multiple values
Img Ref - whatisdbms
Derived Attribute: A derived attribute is an attribute that is not directly stored in the database but is instead calculated or derived from other attributes. For example, the age of a "person" entity might be a derived attribute that is calculated based on the person's birthdate.
Key Attribute: A key attribute is an attribute that uniquely identifies an instance of an entity type. For example, the "book" entity type might have a key attribute of "ISBN number" that uniquely identifies each book.
Null-Valued Attribute: A null-valued attribute is an attribute that does not have a value for a particular instance of an entity type. This can occur when the value of the attribute is unknown or not applicable.
Stored Attribute: A stored attribute is an attribute that is stored directly in the database. This is in contrast to a derived attribute, which is calculated or derived from other attributes.
Complex Attribute: A complex attribute is an attribute that has multiple components or sub-attributes, and is used to represent complex data structures. For example, a "product" entity might have a complex attribute called "features," which includes sub-attributes like "size," "color," and "weight."
♦ Relationships
Relationships represent the connections or associations between entities. Relationships are depicted as diamonds, with the name of the relationship written inside. In an ER diagram, relationships are used to describe how entities are related to each other. There are several types of relationships that can be used in an ER diagram, including:
Given below is diagram for Hospital Management System with all relationships given below -
ImgRef - LucidChart
One-to-One (1:1) Relationship: A one-to-one relationship exists when each instance of one entity is related to exactly one instance of another entity, and vice versa. For example, a "Employee" entity might have a one-to-one relationship with a "Team" entity, since each employee can have only one team and each team can be assigned to only one person.
One-to-Many (1:N) Relationship: A one-to-many relationship exists when each instance of one entity is related to one or more instances of another entity, but each instance of the second entity is related to only one instance of the first entity. For example, a "customer" entity might have a one-to-many relationship with an "order" entity, since each customer can place multiple orders but each order can be placed by only one customer.
Many-to-One (N:1) Relationship: A many-to-one relationship exists when each instance of one entity is related to only one instance of another entity, but each instance of the second entity is related to one or more instances of the first entity. For example, an "order" entity might have a many-to-one relationship with a "customer" entity, since each order can be placed by only one customer but each customer can place multiple orders.
Many-to-Many (N:N) Relationship: A many-to-many relationship exists when each instance of one entity can be related to one or more instances of another entity, and vice versa. For example, a "student" entity might have a many-to-many relationship with a "class" entity, since each student can take multiple classes and each class can have multiple students.
Associative Entity Relationship: An associative entity relationship exists when two entities are related through a third entity, which is used to represent the relationship between the first two entities. For example, a "customer" entity might be related to a "product" entity through a "purchase" entity, which represents the relationship between the customer and the product for each purchase made.
♦ Cardinality
Cardinality describes the number of instances of one entity that can be associated with another entity. It is depicted as a line with a number at each end. The numbers indicate the minimum and maximum number of entities that can be associated.
In ER diagrams, cardinality refers to the numerical constraints that determine the number of instances of one entity that can be associated with another entity through a relationship. There are three main types of cardinality: one-to-one, one-to-many, and many-to-many.
One-to-One Cardinality: In a one-to-one relationship, each instance of one entity is associated with only one instance of another entity, and vice versa. This is represented in an ER diagram by placing the cardinality notation "1" next to the appropriate entity. For example, a person may have only one passport, and a passport may belong to only one person.
One-to-Many Cardinality: In a one-to-many relationship, each instance of one entity can be associated with multiple instances of another entity, but each instance of the second entity can be associated with only one instance of the first entity. This is represented in an ER diagram by placing the cardinality notation "1" next to the first entity and the cardinality notation "N" (representing "many") next to the second entity. For example, a customer can place many orders, but each order belongs to only one customer.
Many-to-Many Cardinality: In a many-to-many relationship, each instance of one entity can be associated with multiple instances of another entity, and vice versa. This is represented in an ER diagram by placing the cardinality notation "N" next to both entities. For example, a student can enroll in many courses, and each course can have many students.
There are also other variations of cardinality, such as optional relationships and mandatory relationships.
Primary Key: A primary key is a unique identifier for an entity. It is depicted as an underline beneath the attribute that serves as the primary key.
Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It is depicted as a line connecting the two entities, with a label indicating the relationship between them.
These are the main components of an ER diagram. By using these symbols and notations, designers can create effective ER diagrams that accurately represent the entities, attributes, and relationships in their database system.
📙 Steps for creating ER Diagram
To create an ERD for a hospital management system, you would need to follow these steps:
Identify the Entities: Start by identifying the entities in the hospital management system. Some entities might include patients, doctors, nurses, hospital staff, rooms, medications, and medical tests.
Define the Relationships: Next, define the relationships between the entities. For example, a patient might be treated by one or more doctors, a doctor might treat many patients, a patient might receive medication, and a patient might undergo one or more medical tests. These relationships can be one-to-one, one-to-many, or many-to-many.
Determine the Attributes: Identify the attributes associated with each entity. For example, a patient entity might have attributes such as name, date of birth, address, and medical history. A doctor entity might have attributes such as name, salary, and id.
Draw the Diagram: Once you have identified the entities, relationships, and attributes, it's time to draw the diagram. Use standard symbols to represent entities, relationships, and attributes. For example, use rectangles to represent entities, diamonds to represent relationships, and ovals or circles to represent attributes. Use lines to connect entities and relationships, and label each line with the appropriate cardinality notation. The diagram will look like as shown in figure below -
Refine and Validate the Diagram: Once you have created the initial diagram, review it carefully to ensure that it accurately represents the relationships and attributes of the entities. You may need to refine the diagram by adding or removing entities or relationships, or by adjusting the attributes associated with each entity. Once you are satisfied with the diagram, validate it by testing it against real-world scenarios to ensure that it can store and retrieve the necessary data.
Thanks for reading, and happy coding!
Exploring Entity Relationship (ER) Diagram Model in Database Management Systems (DBMS) ->
Relational Algebra in DBMS: A Complete Guide