ER Model: Entities, Attributes & Relationships

Scenario

Before a single line of SQL is written, a database designer sits down with a blank sheet of paper and a business problem: 'We need to track students, courses, and enrollments.' She draws boxes, ovals, and lines. That sketch will become the entire database schema.

What are the rules of this visual language, and how does it capture real-world complexity faithfully?

Mental model

An ER diagram is a city map of your data: entities are the buildings, attributes are the labels on each building (address, floors, purpose), and relationships are the roads connecting them.

Just as a city map is not the city but a precise, agreed-upon abstraction of it, an ER diagram is not the database but a precise model of the real-world concepts the database must represent. The map lets city planners reason about layout before any concrete is poured. The ER diagram lets database designers reason about structure before any table is created. Cardinality markers on the roads tell you 'this street leads to one building' or 'this road connects a neighbourhood to many buildings' — capturing rules like 'a student can enroll in many courses, and each course has many students'.

Explanation

The Entity-Relationship (ER) model, introduced by Peter Chen in 1976, is a high-level, conceptual data model. Its purpose is to describe what data a system must store and how the data items relate to each other, in a way that is independent of any particular DBMS or storage technology. Think of it as the blueprint phase of database design — done before you commit to any implementation.

An entity is a 'thing' in the real world that is distinguishable from all other things and that the database needs to represent. Concrete examples: a Student, an Employee, a Product, an Order. An entity type is the class (Student); an entity instance is a specific member of that class (the student named Priya Sharma with ID 2023-0045). In ER diagrams, entity types are drawn as rectangles. Weak entities are a special case: they cannot be uniquely identified by their own attributes alone — they depend on a related strong entity. A Dependent (spouse/child) of an Employee is a classic example: 'Dependent named Rahul' is only meaningful relative to a specific employee. Weak entities are drawn with double rectangles.

Attributes describe properties of an entity. A Student entity might have attributes: StudentID, Name, DateOfBirth, Email. Attributes are drawn as ovals connected to their entity. Key attributes (those that uniquely identify an instance) are underlined in the diagram — StudentID is the key attribute for Student. Composite attributes are made of sub-parts: Name can be broken into FirstName and LastName. Multivalued attributes can hold multiple values per instance: PhoneNumbers (a student might have two phone numbers) — drawn as double ovals. Derived attributes can be computed from other stored data: Age can be derived from DateOfBirth and the current date — drawn as dashed ovals. You store DateOfBirth; Age is derived.

Relationships describe how entity instances associate with each other. An Enrolls relationship connects Student and Course. A relationship type is the class (Enrolls); a relationship instance is a specific pairing (Priya enrolled in CS301). In ER diagrams, relationships are drawn as diamonds. Relationships can also have attributes — the Enrolls relationship might carry a Grade attribute, because grade belongs to the enrollment, not to the student or the course alone. The degree of a relationship is how many entity types participate: binary (two entities, most common), ternary (three entities), etc.

Cardinality and participation constraints are the most important part of ER design because they encode business rules. Cardinality specifies the maximum number of instances one entity can be associated with via the relationship: one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N). A Department has many Employees (1:N); a Student can enroll in many Courses and a Course can have many Students (M:N); a Person has at most one Passport (1:1). Participation specifies whether every entity instance must participate: total participation (every Student must be enrolled in at least one Course — mandatory) versus partial participation (not every Employee manages a Department — optional). Total participation is shown with a double line in the diagram. Getting cardinality and participation right is the entire intellectual work of ER design — the rest is notation.

Key points

• Entity & entity type: An entity is a distinguishable real-world object. An entity type (rectangle) is the class; an entity instance is one member of that class.
• Weak entity: An entity whose instances cannot be uniquely identified without a related strong entity. Drawn with a double rectangle. Example: Dependent identified only relative to an Employee.
• Simple attribute: Atomic, single-valued property. Drawn as an oval. Key attributes (uniquely identify the entity) are underlined.
• Composite attribute: An attribute composed of sub-attributes (e.g., Name = FirstName + LastName). Shown as an oval with child ovals.
• Multivalued attribute: Can hold multiple values per instance (e.g., PhoneNumbers). Drawn as a double oval.
• Derived attribute: Computed from other stored data (e.g., Age from DateOfBirth). Drawn as a dashed oval — stored in the real world, not necessarily in the DB.
• Relationship & cardinality: Relationships (diamonds) link entity types. Cardinality (1:1, 1:N, M:N) encodes the maximum pairings. Participation (single/double line) encodes whether membership is optional or mandatory.

Common mistakes

• Putting relationship attributes on the wrong entity: Grade belongs to the Enrolls relationship, not to Student or Course — because one student can have different grades in different courses. Always ask: 'Does this attribute describe the association, or one of the participants alone?' If the association, it goes on the relationship diamond.
• Confusing entity with attribute: A Phone Number can be an attribute (multivalued oval on Person) or an entity (its own rectangle with relationships). If you need to store additional properties about phone numbers (e.g., carrier, type), model it as an entity. If it's just a contact detail, an attribute is fine.
• Getting cardinality direction wrong: Cardinality is read from one entity TOWARD the other. '1:N from Department to Employee' means one Department has many Employees, not the other way around. Always verify by asking 'can a single X relate to many Ys?' for each direction.
• Ignoring participation constraints: Cardinality (max) and participation (min) are separate. A relationship can be 1:N with partial participation (an Employee doesn't have to manage a Department). Both constraints together fully specify the business rule.

Glossary

Entity-Relationship

A visual diagramming technique used to sketch out the blueprint of a database by showing what 'things' exist and how they are connected.

data model

An abstract way of organizing and defining how data should be structured, stored, and related within an information system.

DBMS

The complex software application (like MySQL or Oracle) that physically creates, maintains, and secures the actual database on a server.

Recall questions

• What is the difference between an entity type and an entity instance?
• Why does the Grade attribute belong to the Enrolls relationship rather than to Student or Course?
• What is a weak entity and what makes it 'weak'?
• Distinguish between cardinality and participation constraints.

Questions & answers

Draw an ER diagram for a library system: books can be borrowed by members; a book can have multiple authors; each loan has a due date.

Entities: Book (BookID, Title), Member (MemberID, Name), Author (AuthorID, Name). Relationships: Written-By (M:N between Book and Author, no attribute), Borrows (M:N between Member and Book, DueDate attribute on relationship). Book participates totally in Written-By (every book has at least one author); Member participates partially in Borrows.

Approach: Start by identifying the nouns (entities) and the verbs (relationships). Then place attributes — check whether each attribute describes one entity alone or the interaction between two. Finally add cardinality and participation.

What is the difference between a multivalued attribute and a composite attribute? Give an example of each.

A multivalued attribute holds multiple values of the same type for one entity instance (e.g., a Person can have multiple PhoneNumbers). A composite attribute is made of structured sub-parts (e.g., Address = Street + City + PinCode). They can coexist: a Person could have multiple composite Addresses.

Approach: The key distinction: multivalued = multiple copies of the same field; composite = one value with internal structure. In a relational database, both require special handling: multivalued attributes become a separate table; composite attributes can be stored as sub-columns or split into flat columns.

Continue learning

Next: ER to Relational Mapping

Return to Core CS Roadmap