Database develop. life cycle - Entity-Relationship (ER) Diagram Design

An Entity-Relationship (ER) Diagram is a visual representation of the structure of a database. It shows the important objects or entities in a system, the properties of those entities, and the relationships between them. ER diagrams are mainly used during the database design stage to understand how data should be organized before creating actual database tables.

1. What is an Entity?

An entity is a real-world object, person, place, event, or concept about which information needs to be stored in a database. Each entity usually becomes a table when the database is implemented.

For example, in a college database, common entities could be:

  • Student

  • Teacher

  • Course

  • Department

  • Examination

A Student entity may contain information such as student ID, name, date of birth, email address, and phone number.

2. What is an Attribute?

An attribute describes a property or characteristic of an entity. In a database table, attributes generally become columns.

For example, the Student entity can have the following attributes:

  • Student_ID

  • Student_Name

  • Date_of_Birth

  • Email

  • Phone_Number

  • Address

Here, Student_ID uniquely identifies each student and can therefore be selected as the primary key.

Attributes can be classified into different types. A simple attribute cannot normally be divided into smaller meaningful parts, such as age. A composite attribute can be divided into smaller components, such as an address containing street, city, state, and postal code. A multivalued attribute can contain multiple values, such as several phone numbers for one student.

3. What is a Relationship?

A relationship describes how two or more entities are associated with each other.

For example, consider the entities Student and Course. A student can enroll in a course, so the relationship can be represented as:

Student → Enrolls In → Course

Similarly, in a company database:

Employee → Works For → Department

Relationships help database designers understand how information in different tables should be connected.

4. Cardinality in ER Diagrams

Cardinality specifies how many instances of one entity can be associated with instances of another entity.

The major types of cardinality are:

One-to-One (1:1):
One record in one entity is associated with only one record in another entity.

Example:

Person → Passport

One person may have one passport, and one passport belongs to one person.

One-to-Many (1:N):
One record in one entity can be associated with multiple records in another entity.

Example:

Department → Employee

One department can have many employees, while an employee belongs to a particular department.

Many-to-Many (M:N):
Multiple records in one entity can be associated with multiple records in another entity.

Example:

Student → Course

A student can enroll in multiple courses, and a course can have multiple students.

In a relational database, a many-to-many relationship is generally implemented using an additional junction or associative table, such as Student_Course.

5. Primary Keys in ER Design

A primary key is an attribute or combination of attributes that uniquely identifies each instance of an entity.

For example:

Student

Student_ID Student_Name Email
101 Ravi [email protected]
102 Anu [email protected]

Here, Student_ID can be used as the primary key because every student has a unique ID.

Choosing appropriate primary keys is important because relationships between entities often depend on these unique identifiers.

6. Foreign Keys and Relationships

A foreign key is an attribute in one table that refers to a primary key in another table.

Suppose there are two tables:

Department

Department_ID Department_Name
1 Computer Science
2 Commerce

Student

Student_ID Student_Name Department_ID
101 Ravi 1
102 Anu 1
103 Meena 2

Here, Department_ID is the primary key of the Department table and acts as a foreign key in the Student table. This establishes a relationship between students and departments.

7. ER Diagram Symbols

Traditional ER diagrams use specific symbols to represent different components.

  • Rectangle: Represents an entity.

  • Oval: Represents an attribute.

  • Diamond: Represents a relationship.

  • Underlined attribute: Represents a key attribute.

  • Lines: Connect entities, attributes, and relationships.

  • Double oval: Represents a multivalued attribute in traditional notation.

  • Dashed oval: Represents a derived attribute in traditional notation.

Different ER modeling tools may use slightly different visual notations, but the underlying concepts remain similar.

8. Example of an ER Design

Consider an online shopping system.

The main entities could be:

Customer

  • Customer_ID

  • Customer_Name

  • Email

  • Phone

Order

  • Order_ID

  • Order_Date

  • Total_Amount

Product

  • Product_ID

  • Product_Name

  • Price

  • Stock

A customer can place multiple orders, so there is a one-to-many relationship:

Customer → Places → Order

An order can contain multiple products, and a product can appear in multiple orders. Therefore, there is a many-to-many relationship between Order and Product.

This relationship can be implemented using an additional entity such as Order_Item, containing:

  • Order_ID

  • Product_ID

  • Quantity

  • Unit_Price

The resulting structure makes the relationships clear and provides a foundation for creating the relational database.

9. Importance of ER Diagram Design

ER diagrams provide several benefits during database development.

First, they make complex database structures easier to understand because the entities and relationships can be viewed visually.

Second, they help identify missing information before database implementation begins. A designer can examine the model and determine whether all important entities, attributes, and relationships have been included.

Third, ER diagrams help reduce design errors. Incorrect relationships or unnecessary duplication can often be identified before tables are created.

Fourth, they improve communication between database designers, developers, analysts, and other stakeholders. A visual model can be easier to discuss than a collection of SQL statements.

Finally, an ER diagram serves as a blueprint for converting a conceptual database design into relational tables.

10. ER Diagram Design Process

A typical ER diagram design process involves several steps.

Step 1: Identify the requirements
Understand what information the database needs to store and what operations users need to perform.

Step 2: Identify entities
Find the major objects or concepts involved in the system.

Step 3: Identify attributes
Determine the properties that need to be stored for each entity.

Step 4: Select primary keys
Identify attributes that can uniquely distinguish individual records.

Step 5: Identify relationships
Determine how the entities are connected.

Step 6: Define cardinality
Determine whether relationships are one-to-one, one-to-many, or many-to-many.

Step 7: Review the model
Check whether the design represents the requirements accurately and whether any entities, attributes, or relationships are missing.

Step 8: Convert the model into database tables
After the ER design is finalized, the entities and relationships can be mapped to a relational database structure.

Conclusion

Entity-Relationship Diagram Design provides a structured way to represent a database before its physical implementation. It identifies entities, attributes, keys, relationships, and cardinalities, allowing designers to understand how different pieces of information are connected. A well-designed ER diagram acts as a blueprint for creating database tables and helps reduce design errors during later stages of database development.