In DBMS, the database life cycle includes stages involved in implementing a database for any project. These stages are Requirements Analysis, Logical Design, Physical Design, Implementation and Monitoring, Modification and Maintenance.
For this tutorial, we want to focus on the second phase i.e. Logical Design. In the initial part of this phase, based on the requirements collected in the first phase (Requirements Analysis), a global schema is created. It is a conceptual data model (a technology-independent model that describes the structure of the data without specifying how and where it will be stored physically) that shows data and their relationships. One of the ways to represent such a data model is to use an ER diagram.
This tutorial discusses the definition of an ER diagram, its components, and the symbols used to draw an ER diagram.
What is an ER Model and ER Diagram?
An entity relationship model is a conceptual model that is used to describe database structure in an abstract manner. An entity relationship diagram (ERD) is a graphical representation of an ER model.
ER Diagram is a visual representation of requirements stated in the requirements specification. ER diagram represents data as entities and relationships; entities have attributes (explained in the next section).
ER diagram acts as a blueprint for database design.
Components of an ER Diagram
The basic ER model has the following components: Entity, Attributes, and Relationships.
Entity
An entity is a principal data object about which we store data; something that can be distinctly identified. Examples of an entity are a person, place, building, bank account, etc. In an ER diagram, an entity is represented by a rectangle, and the entity name is written inside the rectangle as displayed below.
In the above example, for an HRMS system, ‘Employee’ is an entity.
Also, the following are some types of entities:
- Strong Entity – Strong entities can exist without depending on other entities. They have their own primary key. In the above example, ‘Employee’ is a strong entity.
- Weak Entity – Weak entities depend on other entities; they cannot exist without their parent entity. Weak entities do not have primary keys; they need to use a foreign key to create a primary key. In this case, the primary key belongs to the parent entity.
Weak entities are represented with double rectangles as displayed below.
In the above example, Education is a weak entity and its parent entity is ‘Employee’. Education details cannot exist without Employee details.
Attribute
An attribute is a characteristic of an entity; it represents the property of an entity. For example, an employee entity can have attributes such as Employee ID, Employee Name, Joining Date, Department, Supervisor, etc. It is described as an oval in an ER diagram as displayed below.
Attributes have the following four types:
1. Key Attribute – A key attribute is used to uniquely identify an entity. A key attribute is described in an oval and the text of the attribute is underlined. For example, for an Employee entity, ‘Employee ID’ is a key attribute; every employee has a unique id in the organization.
Below is an example of a key attribute:
2. Composite Attribute – A composite attribute is an attribute that can be further divided into subparts. For example, ‘Employee Name’ can be divided into ‘First Name’, ‘Middle Name’, and ‘Last Name’.
Below is an example of a composite attribute:
3. Multivalued Attribute – Multivalued attributes can take more than one value. They are described with a double oval in an ER diagram. For example, an employee can have multiple phone numbers (Office Phone, Home Phone, etc.).
Below is an example of a multivalued attribute:
4. Derived Attribute – A derived attribute is one that is derived from another attribute(s) and has a dynamic value. It is described with a dashed oval in an ER diagram. For example, an employee’s ‘Age’ attribute, is a derived attribute, derived from the ‘Date of Birth’ attribute.
Below is an example of a derived attribute:
Relationship
The relationship component is used to represent relationships between the entities in an ER diagram. Relationships are usually denoted by verb phrases. Cardinality is defined as the number of times an entity is associated with other entities via a relationship. It is described using a diamond shape in an ER diagram as displayed below:
For relationships, mapping cardinalities have the following four types (this section discusses only binary relationships; binary relationships are the ones where only two entities are involved):
1. One-to-One Relationship – One to one relationship is where only one instance of an entity is associated with only one instance of another entity via a relationship. One of the ways to describe one-to-one relationships is to add ‘1’ on the line joining the relationship ‘diamond’ with entity ‘rectangle’. For example, one employee manages only one department, so the ‘Employee’ entity is in a one-to-one relationship (‘Manages’) with the ‘Department’ entity.
Below is an example of one to one relationship:
2. One-to-Many Relationship – One-to-many relationship is where more than one instance of an entity is associated with only one instance of another entity via a relationship. One of the ways to describe one-to-many relationships is to add ‘1’ on the line joining relationship ‘diamond’ with entity ‘rectangle’ (for an entity with only one instance associated) and add ‘N’ on the line joining relationship ‘diamond’ with entity ‘rectangle’ (for an entity with more than one instance associated). For example, one subject has many books, so the ‘Subject’ entity is in a one-to-many relationship (‘Has’) with the ‘Book’ entity.
Below is an example of one-to many-relationship:
3. Many-to-One Relationship – Many-to-one relationship is where only one instance of an entity is associated with more than one instance of another entity via a relationship. One of the ways to describe many to one relationship is to add ‘N’ on the line joining relationship ‘diamond’ with entity ‘rectangle’ (for an entity with more than one instance associated) and add ‘1’ on the line joining relationship ‘diamond’ with entity ‘rectangle’ (for an entity with only one instance associated). For example, many employees work in one department so the ‘Employee’ entity is in a one-to-many relationship (‘Works in’) with the ‘Department’ entity.
Below is an example of a many-to-one relationship:
4. Many-to-Many Relationship – Many-to-many relationship is where more than one instance of an entity is associated with more than one instance of another entity via a relationship. One of the ways to describe a many-to-many relationship is to add ‘M’ and ‘N’, on the line joining the relationship ‘diamond’ with entity ‘rectangle’, for left and right entities respectively. For example, many employees have more than one skill, so the ‘Employee’ entity is in a many-to-many relationship (‘Has’) with the ‘Skill’ entity.
Below is an example of a many-to-many relationship:
Conclusion
An ER diagram is a graphical representation of the database structure; its entities and their relationships. Learning how to create ER diagrams is one of the important aspects of Software Engineering. By preparing the ER diagram before designing the database, the development team can be sure of the design as it becomes easier to find any issues with the design in the ER diagram.
well done