DBMS Notes
An Entity-Relationship (ER) Diagram is a visual representation of the entities, attributes, and relationships in a database design. It is the primary tool...
What is an ER Diagram?
An Entity-Relationship (ER) Diagram is a visual representation of the entities, attributes, and relationships in a database design. It is the primary tool used during the conceptual design phase — before any tables are created, before any SQL is written, the database designer draws an ER diagram to model the real-world scenario.
ER diagrams serve as a communication tool between database designers and stakeholders (clients, managers, domain experts). They are intuitive enough for non-technical people to understand, yet precise enough to guide the implementation of actual database tables.
Types of Attributes in ER Diagrams
| Attribute Type | Notation | Example |
|---|---|---|
| Simple (Atomic) | Regular oval | Name, Age, Salary |
| Composite | Oval with sub-ovals connected | Name → (FirstName, LastName) |
| Multi-valued | Double-bordered oval | PhoneNumbers (a person can have many) |
| Derived | Dashed-border oval | Age (derived from DateOfBirth) |
| Key attribute | Underlined text in oval | StudentID (uniquely identifies entity) |
Drawing a Complete ER Diagram: Step-by-Step Example
Let us design an ER diagram for a University Course Registration System.
Step 1: Identify Entities
From the requirements, we identify the following entities:
- Student — individuals enrolled at the university
- Course — subjects offered for study
- Instructor — faculty members who teach courses
- Department — organizational units within the university
Step 2: Identify Attributes for Each Entity
| Student | StudentID (PK), FirstName, LastName, Email, DateOfBirth, GPA |
| Course | CourseID (PK), CourseName, Credits, Syllabus |
| Instructor | InstructorID (PK), Name, Qualification, JoinDate |
| Department | DeptID (PK), DeptName, Building, Budget |
Step 3: Identify Relationships and Cardinality
Step 4: Identify Relationship Attributes
The "enrolls" relationship between Student and Course has its own attributes:
- Grade (earned by a specific student in a specific course)
- Semester (when the enrollment happened)
- EnrollmentDate
These attributes belong to the relationship itself, not to either entity independently.
Step 5: Draw the Complete Diagram
Cardinality Notation Styles
Different textbooks use different notations for cardinality. The most common are:
| Style | One | Many | Example |
|---|---|---|---|
| Chen notation | 1 | N or M | 1:N placed near the line |
| Crow's foot | Single line | Fork (crow's foot) | Line ending indicates cardinality |
| (min,max) notation | (1,1) | (0,N) | Minimum and maximum participation |
Participation Constraints
Total participation (mandatory): Every entity instance must participate in the relationship. Drawn with a double line.
Partial participation (optional): Some entity instances may not participate. Drawn with a single line.
| Every EMPLOYEE must work in a DEPARTMENT | total participation (double line) |
| Not every DEPARTMENT needs a manager | partial participation (single line) |
| (total | every employee must work somewhere) |
| (partial | not every employee manages a department) |
Common Mistakes in ER Diagrams
| Mistake | Problem | Correction |
|---|---|---|
| Multi-valued attribute where entity needed | Cannot represent complex structures | Create a separate entity (e.g., Phone entity) |
| Relationship between more than two entities when not needed | Overly complex diagram | Most relationships are binary; ternary only when necessary |
| Missing cardinality | Ambiguous design | Always specify cardinality constraints |
| Derived attribute stored as regular | Wastes space, may cause inconsistency | Mark with dashed border, compute at query time |
| Forgetting weak entity notation | Loss of dependency information | Use double border and identifying relationship |
From ER Diagram to Relational Schema
The ER diagram is not the final product — it must be converted to relational tables for implementation:
- Each strong entity becomes a table with its attributes as columns
- Each weak entity becomes a table with the owner's PK included
- Each M:N relationship becomes a separate junction table
- Each 1:N relationship adds a foreign key to the "many" side
- Multi-valued attributes become separate tables
- Composite attributes are flattened into their simple components
This mapping process is systematic and follows well-defined rules, making the ER diagram a reliable blueprint for database implementation.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for ER Diagrams.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, diagrams, er diagrams
Related Database Management Systems (DBMS) Topics