ER Model in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn ER model, entity, attributes, relationships, cardinality, participation, weak entity, ER diagram symbols, and conversion to tables.
ER Model stands for Entity Relationship Model. It is used to design a database at the conceptual level before creating actual tables. The ER model helps students and database designers understand what data should be stored and how different parts of the data are connected. A beginner can think of an ER model as a planning diagram. Before building a college database, we first identify important things such as Student, Course, Department, Teacher, and Marks. Then we identify their properties and relationships. This planning avoids confusion when actual tables are created later.
The ER model is important because a database should not be designed randomly. If tables are created without understanding entities and relationships, the database may contain duplicate data, missing links, and difficult queries.
Why ER Model Is Used
ER model is used to convert real-world requirements into a clear database design. It gives a visual and logical understanding of the system before implementation. For example, if a college wants a database, the designer must know what records are needed. Students, courses, departments, teachers, attendance, and marks may all be required. The ER model helps decide which of these should be entities, which should be attributes, and how they should be connected.
ER modeling also helps communication. A developer, teacher, admin, and database designer can look at the same ER diagram and understand the main structure of the database.
Basic ER Model Flow
The following simple diagram shows how ER modeling moves from real-world understanding to database tables.
```text
Real-world system
|
v
Identify entities
|
v
Identify attributes
|
v
Identify relationships
|
v
Create ER diagram
|
v
Convert into relational tables
```
This process is useful because it keeps design organized. Instead of directly creating tables, we first understand the problem clearly.
Entity
An entity is a real-world object, person, place, event, or concept about which data is stored. Examples include Student, Teacher, Course, Department, Book, Customer, Product, and Order. An entity usually becomes a table when the ER model is converted into a relational database. For example, a Student entity may become a Students table, and a Course entity may become a Courses table.
An entity should represent something meaningful in the system. If the system needs to store information about it, search it, update it, or connect it with other information, it may be an entity.
Entity Set
An entity set is a collection of similar entities. For example, all students in a college form a Student entity set. One student is an entity, while the complete group of students is an entity set. In table form, one row may represent one student, while the full table represents the Student entity set. This idea helps connect ER model concepts with relational tables.
Strong Entity
A strong entity is an entity that can exist independently and has its own primary key. For example, Student can be a strong entity if each student has a unique roll number. Strong entities do not depend on another entity for identification. They are usually represented by a rectangle in ER diagrams.
In a college database, Student, Course, Teacher, and Department are common examples of strong entities, depending on the design.
Weak Entity
A weak entity is an entity that cannot be uniquely identified by its own attributes alone. It depends on another entity, called the owner entity, for identification. For example, suppose a database stores dependent family members of employees. A dependent may have a name, but that name alone may not be unique across the company. The dependent is identified along with the employee it belongs to.
Weak entities are usually represented by a double rectangle in ER diagrams. They teach an important idea: not every entity can stand alone in database design.
Attribute
An attribute describes an entity. It is a property or characteristic of an entity. For a Student entity, attributes may include roll number, name, date of birth, phone number, email, and department. When an entity becomes a table, attributes usually become columns. For example, roll_no, name, and phone can become columns in a Students table.
Choosing correct attributes is important because a database can only store and answer questions based on the data it contains. If date of birth is not stored, the system cannot calculate student age later without extra information.
Types of Attributes
The main types of attributes are listed below. Numbering them makes it easier to remember each type separately.
1. Simple attribute: It cannot be divided further in a useful way. For example, age may be treated as a simple attribute.
2. Composite attribute: It can be divided into smaller parts. For example, address can include city, state, and pin code.
3. Single-valued attribute: It has only one value for one entity. For example, one student usually has one roll number.
4. Multi-valued attribute: It can have multiple values for one entity. For example, a student may have more than one phone number.
5. Derived attribute: It is calculated from another attribute. For example, age can be derived from date of birth.
Key Attribute
A key attribute uniquely identifies an entity in an entity set. For a Student entity, roll number can be a key attribute if every student has a different roll number. The key attribute becomes very important when the ER model is converted into relational tables. It usually becomes the primary key of the table.
A good key should be unique, stable, and always available. If a value changes frequently or can be missing, it is not a good primary identifier.
Relationship
A relationship shows how two or more entities are connected. For example, a Student enrolls in a Course, a Teacher teaches a Course, and a Department has Students. Relationships are important because a database is not only separate lists of data. Real data is connected. ER model helps represent these connections clearly before tables are created.
In ER diagrams, relationships are usually represented by diamonds. The relationship name should be meaningful, such as enrolls, teaches, manages, or places.
Relationship Set
A relationship set is a collection of similar relationships. For example, all enrollment links between students and courses form an Enrollment relationship set. If Rahul enrolls in DBMS, Priya enrolls in Java, and Amit enrolls in Python, each is a relationship instance. Together, they form the relationship set.
This concept helps students understand that relationships are also stored and managed in database design, especially when many-to-many relationships exist.
Degree of Relationship
The degree of a relationship means the number of entity sets participating in it. The common types are listed below.
1. Unary relationship: One entity set participates in the relationship. For example, an Employee may manage another Employee.
2. Binary relationship: Two entity sets participate in the relationship. For example, Student enrolls in Course.
3. Ternary relationship: Three entity sets participate in the relationship. For example, Supplier supplies Part to Project.
Most beginner ER diagrams use binary relationships because they are easier to understand and very common in practical database design.
Cardinality
Cardinality describes how many entities of one type can be associated with entities of another type. It is one of the most important parts of ER modeling. The common cardinality types are listed below. These types help decide how tables and foreign keys should be created later.
1. One-to-one: One record on the first side connects to one record on the second side. Example: Person and Passport.
2. One-to-many: One record on the first side connects to many records on the second side. Example: Department and Students.
3. Many-to-one: Many records on the first side connect to one record on the second side. Example: Students and Department.
4. Many-to-many: Many records on both sides can connect with each other. Example: Students and Courses.
One-to-One Relationship
In a one-to-one relationship, one entity of the first type is associated with at most one entity of the second type, and vice versa. For example, one person may have one passport, and one passport belongs to one person. This is a one-to-one relationship in a simple design. One-to-one relationships are less common than one-to-many relationships, but they are useful when data should be separated for security, clarity, or optional details.
One-to-Many Relationship
In a one-to-many relationship, one entity can be associated with many entities of another type, but each of those many entities belongs to one entity on the first side. For example, one department can have many students, but each student may belong to one department. This is a common relationship in college databases. When converted into tables, the primary key of the one side usually appears as a foreign key in the many side table.
Many-to-Many Relationship
In a many-to-many relationship, many entities on one side can be associated with many entities on the other side. For example, a student can enroll in many courses, and a course can have many students. This cannot be represented cleanly by placing one foreign key in only one table. In relational design, a many-to-many relationship is usually converted into a separate table. For Student and Course, this table may be called Enrollment and may contain student_id and course_id.
Participation
Participation describes whether every entity must participate in a relationship or only some entities may participate. There are two main types.
1. Total participation: Every entity in the entity set must participate in the relationship. For example, if every student must belong to a department, Student participation is total.
2. Partial participation: Some entities may participate and some may not. For example, not every student may borrow a library book, so Student participation in Borrow relationship can be partial.
ER Diagram Symbols
The following table shows common ER diagram symbols. This table is useful because symbols are often asked in exams and help students read diagrams properly.
| ER Concept | Common Symbol | Meaning |
| --- | --- | --- |
| Entity | Rectangle | Real-world object such as Student or Course |
| Weak entity | Double rectangle | Entity dependent on another entity |
| Attribute | Oval | Property such as name or roll number |
| Key attribute | Underlined oval/text | Attribute that uniquely identifies entity |
| Relationship | Diamond | Association between entities |
| Multi-valued attribute | Double oval | Attribute with multiple values |
| Derived attribute | Dashed oval | Attribute calculated from another value |
Simple ER Diagram Example
The following text diagram shows a simple Student-Course enrollment design. In a visual ER diagram, Student and Course would be entities, and Enrolls would be the relationship.
```text
[Student] ---- enrolls ---- [Course]
| |
roll_no course_id
name course_name
department credits
```
This example shows that Student has its own attributes, Course has its own attributes, and the relationship tells that students enroll in courses.
Converting ER Model to Tables
When converting an ER model into tables, strong entities usually become tables. Attributes become columns. Key attributes become primary keys. For one-to-many relationships, the primary key of the one side is usually added as a foreign key in the many side table. For example, department_id can be added to the Student table.
For many-to-many relationships, a separate table is created. For example, Student and Course can be connected using an Enrollment table with student_id and course_id.
Common Mistakes in ER Modeling
A common mistake is treating every noun as an entity. Not every word needs to become an entity. Some words are better represented as attributes. Another mistake is ignoring cardinality. If cardinality is wrong, table design may become wrong later. For example, a many-to-many relationship should not be forced into a single foreign key without proper design.
Students should also avoid adding unnecessary attributes. A good ER model stores useful information without making the design unnecessarily complex.
Important Points to Remember
ER model is used before table creation to plan the database clearly. Entity represents an object, attribute represents a property, and relationship represents association between entities. Cardinality and participation are important because they explain how entities are connected. A clear ER model makes relational table design easier and reduces mistakes in database structure.
For beginners, the main idea is simple: first understand the real-world system, then identify entities, attributes, and relationships, and only after that create tables.