ER (Entity Relationship) Model – Basic Concepts:
Introduction
Entity-Relationship (ER) diagrams are a basic yet important tool in database design. They demonstrate visual representation of the relationships between entities within a system. These diagrams provide a clear and concise way to model the structure of a database and its entities.
In this comprehensive guide, we will explore the key concepts of ER diagrams, their components, and their practical application. Additionally, we will delve into an example to illustrate the creation and interpretation of ER diagrams.
What is ER Diagram?
An Entity Relationship model was introduced by Peter Pin-Shan Chen in 1976.
An E-R model is defined as a conceptual data model that views the real world as a set of objects (entities) and relationships among these objects (entities).
ER model defines the conceptual (logical) view of a database.
At the view level, the ER model is considered a good option for designing databases.
ER Diagram Notations:
ER model uses three basic notions:
1) Entity set 2) Relationships set 3) Attributes
1) Entity Sets:
Entity:
An entity is a “thing” or “object” existing in the real world and is of interest to an organization. It is distinguishable from all other objects.
For example, any Customer of a bank is an entity. A Customer, say “Kevin” exist in the real world, as he is living thing. He can be distinguishable from other Customers based on his Customer id, address, and other data. Also, he is different from an employee, an Account, a Loan, transaction of a branch.
An entity possesses certain properties. The values of these properties distinguish one entity from the other entity. For example, the properties of Customer include Customer id, address, name and contact number.
Entity set:
An entity set is a set of entities of the same type.
All entities in an entity set share the same properties or attributes.
The set of all individual Customers are referred as to an entity set “Customer”.
Entity sets do not need to be a disjoint. This means that, a single entity can be a member or extension of more than one entity set.
For e.g. a single person can be a Customer as well as an employee of the bank. And if this is the case, then he/she can be an extension of both the entity sets – Customer as well as Employee.
Attributes:
An entity is represented by a set of attributes.
An attribute is property or characteristics of an entity type that is of interest to an organization. Customer entity has attributes like cust_id, cname, address, city, contact_no
Each entity has a value for each of its attributes. For e.g. a particular Customer entity may have value ‘C01’ for cust_id, ‘Suresh’ for cname and so on. The cust_id attribute is used to identify Customers uniquely, since there may be more than one Customer with the same name, address, and city.
For each attribute, there is a set of permitted values called the domain or value set of an attribute. For e.g. a bank is organized in three branches named ‘Bharuch’, ‘GNFC’ & ‘Ank’. So the set of permitted values for an attribute bname is {‘Bharuch’, ‘GNFC’, ‘Ank’}.
Types of Attributes:
Simple (or atomic):
A simple attribute is an atomic attribute, which cannot be divided into sub-parts.
Simple attributes are also called as atomic attributes.
E.g. cust_id, age, salary etc
Composite Attribute
The composite attribute is defined as an attribute made up of multiple components each with an independent existence.
Composite attributes can be divided into smaller sub-parts.
E.g. address attribute can be divided into sub parts such as society, city & pin-code. Also, society can be further divided into street number & society name.
Single Valued Attribute
Single-valued attribute id defined as an attribute that holds a single value for a single entity.
E.g. student-id is single valued attributes. For any Student, there will be only one student id.
Multi-Valued Attribute
The multi-valued attribute can defined as an attribute that holds multiple values for a single entity.
E.g. Mobile no. is a multi-valued attribute, since any particular Customer can have zero, one or more mobile numbers.
Stored Attribute
The stored attribute can be defined as an attribute whose value cannot be derived from the values of other related attributes. It is also called as a base attribute.
It exists in the physical database
Derived Attribute
The derived attribute can be defined as an attribute whose value can be derived from the values of other related attributes.
It does not exist in the physical database
Consider two attributes age and birth_date for Customer entity. Here age can be calculated by using birth date and a current date. So, age is considered as a derived attribute, while birth_date is considered as a stored attribute.
Relationship Set:
Relationship
A relationship is an association or connection among several entities.
In a banking application, each and every Account is associated with some particular Customer. In other words, each and every Customer owns an Account in a bank. This kind of association is known as a relationship.
Relationship set:
A relationship set is a set of relationships of the same type.
Descriptive attributes:
Like any entity, a relationship may also have attributes. These attributes are called as descriptive attributes.
Degree of the Relationship set:
The total number of entity sets participating in a relationship is called as a degree of relationship.
A relationship set of degree 2 is called a binary relationship set.
Similarly, a relationship set of degree 3 is called a ternary relationship set.
In general, a relationship set of degree ‘n’ is called the n-ary relationship set.
Mapping Cardinalities:
It expresses the number of entities to which another entity can be associated via a relationship set.
It is also referred as Cardinality ratio or Cardinality constraint.
a) One-to-One relationship (1:1): An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. (see figure 4(a))
Example: one Customer can have one Account in a particular branch of a bank and one Account can be associated with only one Customer.
b) One-to-Many relationship (1: M): An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most one entity in A. (see figure 4(b)).
Example: one teacher teaches many students.
c) Many-to-One relationship (M: 1): An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number (zero or more) of entities in A. (see figure 4(c))
d) Many-to-Many relationship (M: M): An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A. (see figure 4(d)).
Example: Many teachers teach many students.
E-R Diagrams Symbols:
An E-R diagram is a graphical representation of the overall logical structure of a database.
Most E-R diagrams are simple and clear. They do not include any implementation details. Any non-technical user can also understand data requirements of an organization represented by E-R diagrams.
E-R schema represented by E-R diagram can directly be mapped on to a relation model. Hence E-R diagram are very popular and widely used.
Example of Complete ER Diagram
Creating an Entity-Relationship Diagram
1. Identify Entities
First step is to identify the main entities in the system. These are the objects or concepts that need to be represented in the database. For a library database, entities could include Book, Author, and Borrower.
2. Identify Attributes
Next step is to determine the attributes for each entity. These are the properties or characteristics that describe the entities. For the Book entity, attributes could include ISBN, Title and Publication Year.
3. Identify Relationships
Once entities and their attributes are determined, then next step is to ddentify the relationships between entities. Determine how entities are connected to each other and the nature of these connections. For example, there may be a relationship between Book and Author entities representing the fact that an author can write many books, and a book can have multiple authors.
4. Define Cardinality and Participation Constraints
Specify the cardinality and participation constraints for each relationship. Determine how many instances of one entity can be associated with another (one to one, one to many, many to one or many to many) and whether participation is mandatory (total) or optional (partial).
5. Draw the Diagram
Draw the ER diagram using standard notation/symbols. Represent entities as rectangles, attributes as ovals, and relationships as lines connecting entities. Use diamonds to indicate the cardinality and participation constraints.
6. Refine the Diagram
Review the initial diagram and refine it as needed. Ensure that it accurately represents the relationships and constraints within the system. Make adjustments for clarity and completeness.
Example: Library Management System ER Diagram
Let's illustrate the creation of an Entity-Relationship Diagram with an example of a Library Management System. This system manages books, authors, and borrowers.
1. Identify Entities
Entities in the Library Management System include:
Book
Author
Borrower
2. Identify Attributes
Attributes for each entity include:
Book:
ISBN (Primary Key)
Title
Publication Year
Genre
Author:
AuthorID (Primary Key)
Name
Nationality
Birthdate
Borrower:
BorrowerID (Primary Key)
Name
Contact Number
Address
3. Identify Relationships
Relationships between entities include:
Author writes Book: A many-to-many relationship indicating that an author can write many books, and a book can have multiple authors.
Book is borrowed by Borrower: A one-to-many relationship indicating that a book can be borrowed by many borrowers, but a borrower can borrow multiple books.
4. Define Cardinality and Participation Constraints
Author writes Book: Many authors can write many books (M:M relationship).
Book is borrowed by Borrower: A book must be borrowed by at least one borrower (1:M relationship).
5. Draw the Diagram
Use standard notation to draw the ER diagram, representing entities, attributes, and relationships. Clearly indicate the cardinality and participation constraints.
6. Refine the Diagram
Review the diagram to ensure it accurately captures the relationships and constraints within the Library Management System. Make any necessary adjustments for clarity and completeness.
Sounds simple right? In a complex system, it can be a nightmare to identify the relationships. This is something you’ll perfect only with practice.
In short, remember below mentioned points before creating an ER diagram.
- Identify all the entities in the system. An entity should be unique. Draw rectangles for all entities and provide a meaningful name to them.
- Identify relationships between entities. Use a diamond shape symbol to connect them using a line.
- Add attributes for entities. Give meaningful & simple names to attributes so they can be understood easily.
ER Diagram Best Practices
- Provide a meaningful, unique and easy name for each entity, attribute, and relationship in the diagram.
- Remove confusing, duplicate or unnecessary relationships between entities.
- Never connect a relationship to another relationship.
- Use colors to group similar entities or to highlight key areas in your diagrams.
Conclusion
Entity-Relationship diagrams play a pivotal role in the database design process, offering a visual representation of the relationships between entities.
By understanding the components of ER diagrams and following a systematic approach to their creation, designers can effectively model and communicate complex databases.
The example of a Library Management System ER diagram demonstrates how these diagrams can be applied to real-world scenarios.
Embracing the use of ER diagrams can lead to improved communication, better database design, and the identification of potential design flaws early in the development process.
