ER and EER Diagrams for Beginners: A Practical Guide to Database Design
Every well-built database starts life as a drawing before it becomes a single line of SQL. That drawing is usually an ER (Entity-Relationship) or EER (Enhanced Entity-Relationship) diagram, and if you’re new to database design, the terminology around them can feel like alphabet soup. This guide walks through core ideas in plain language, so you can read and draw these diagrams with confidence.
What Is the ER Model?
The Entity Relationship (ER) model is a high level, conceptual way of Modeling data. Instead of jumping straight into tables and columns, it lets you first map out the real world things your system cares about, and how those things relate to one another. Everything else in database design builds on this foundation.
Entity-Relationship (ER) model is a high-level conceptual data model.
Entities and Entity Sets
An entity is any object in the real world that has an independent existence a person, a car, a company, a job, a university. When you group similar entities together, you get an entity set. By convention, entities are named with a noun or noun phrase (think Car or Student), and in diagrams they’re drawn as rectangles.
Attributes: Describing an Entity
Every entity is described by a set of attributes of properties that capture its details. Graphically, attributes are drawn as ovals connected to their entity. A Student entity, for example, might have the attributes Name, Age, and Roll No.
Domain of an Attribute:
The domain of an attribute is simply the set of legal values it can take. Age might be restricted to integers from 0 to 120, while Name might be limited to a 20 to character string.
Multi valued Attributes:
An attribute that can hold more than one value at once is called multivalued. A person’s set of phone numbers is a classic example there’s no single correct answer, just a list of them.
Derived Attributes:
A derived attribute is one that can be calculated from other attributes rather than stored directly, and it’s drawn with a dotted line. Age is the textbook example: it can always be derived from a person’s date of birth and the current date, so there’s no need to store it separately.
Keys: Identifying Entities Uniquely
A key attribute is the minimal set of attributes needed to uniquely identify an entity within its entity set. A single entity can have more than one attribute (or combination of attributes) capable of doing this each one is called a candidate key. Whichever candidate key you choose to actually use becomes the primary key.
Take a Student entity defined as Student(SID, Name, Address, Contact, GPA). Here, SID is the minimal attribute that uniquely identifies each student, so it’s designated the primary key.
Sometimes no single attribute is enough, and it takes a group of attributes working together to guarantee uniqueness that’s a composite key. More broadly, any set of attributes that uniquely identifies a tuple, even if it isn’t minimal, is called a superkey.
Relationships
Entities rarely exist in isolation relationships describe how they connect. A few properties define the shape of a relationship:
Degree of a Relationship
The degree is simply the number of entities participating in the relationship:
- Binary: 2 participating entities
- Ternary: 3 participating entities
- Quaternary: 4 participating entities
Cardinality
For binary relationships, cardinality describes how many instances of the relationship an entity can take part in. There are three possibilities:
- One-to-one (1:1) = an employee manages at most one department, and a department has only one manager.
- One-to-many (1:N) = an employee works in at most one department, but a department has many employees.
- Many-to-many (N:M) = entities on both sides can be linked to multiple entities on the other.
One-to-One relationship
Get Techloom.ai Pvt Ltd’s stories in your inbox
Join Medium for free to get updates from this writer.
An employee manages at most one department. A department can have only one manager managing it.
One-to-Many relationship
An employee works in at most one department.
Many-to-many relationship
An employee can work on several projects. A project can have many employees working on it
Recursive Relationships
Sometimes an entity relates to itself this is called a recursive (or unary) relationship. A classic example is an Employee entity where one employee supervises another.
Descriptive Attributes
Occasionally a relationship itself needs to store information that doesn’t belong to either entity individually. These are descriptive attributes they describe the relationship, not the entities on either end of it.
Participating Constraints
A participation constraint defines whether an entity’s existence depends on being connected to another entity through a relationship. If every employee must belong to a department, the “Works In” relationship has total participation from Employee to Department.
Weak Entity
Not every entity can stand on its own. A weak entity is one that cannot be identified using only its own attributes and instead depends on another entity its owner for identification. Entity types with no key attributes of their own fall into this category. A few terms go hand in hand with weak entities:
- Partial key : the attribute(s) in the weak entity that help identify it within the context of its owner.
- Identifying relationship : the relationship connecting the strong owner entity to the weak entity.
- Cardinality : an owner can have one or many weak entities (1:1 or 1:M).
- Total participation : every weak entity must be linked to an owner to exist at all.
The EER Model: Adding Specialization and Generalization
The Enhanced (or Extended) Entity-Relationship model builds on the basic ER model by adding two powerful concepts: specialization and generalization.
Specialization: dividing an entity into more specific subclasses based on their differences.
For example: Employee → Permanent Employee.
Generalization: the reverse process, combining similar entities into a broader superclass.
For example: Permanent Employee + Contract Employee → Employee
ISA Relationships
An ISA relationship expresses that one entity is a specialized type of another. If Employee is generalized from Permanent Employee and Contract Employee, then we say Permanent Employee IS A Employee, and Contract Employee IS A Employee.
Overlapping constraint
An overlapping constraint determines whether two subclasses are allowed to share the same entity. Consider Person specialized into Student and Employee: if a single person can be both student and an employee at the same time, that’s an overlapping constraint.
Covering constraint
A covering constraint determines whether the subclasses, taken together, account for every entity in the superclass. If Employee is specialized into Permanent Employee and Contract Employee, and every employee must fall into one of those two categories, then the covering constraint is complete (or total).
Conclusion
Mastering ER and EER diagrams is one of the most important early steps in learning database design. Once you’re comfortable identifying entities, attributes, keys, and the relationships between them, translating a real world problem into a solid schema becomes far more intuitive. I hope this guide helps make these foundational concepts a little clearer for anyone starting out.
