Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

The ER model

The ER model (Entity-Relationship model) is a high-level conceptual tool for designing and communicating the logical structure of a database using entities, attributes and relationships.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Two or three sentences: where an engineer uses this.

The Entity-Relationship (ER) model is the main tool engineers use at the start of database design to capture real-world requirements clearly and unambiguously. Good ER diagrams guide schema creation, help discuss requirements with non-technical stakeholders, and reduce design errors later (when implementing relations, integrity constraints and queries in SQL or mapping to other topics like normalization).

Key ideas

The section below defines each basic concept from first principles and gives exact, exam-style statements.

  • Entity: An object in the real world that is distinguishable from other objects. Example: a specific student, a specific car. Use this term only after definition.

  • Entity type: A collection of entities that share the same properties (attributes). Example: Student is an entity type containing all student entities.

  • Entity set: The current set of entity instances of an entity type; formally a set. If Student is an entity type, StudentSet is the set of its instances.

  • Attribute: A property or characteristic of an entity type. Example: RollNo, Name. An attribute has a domain (set of allowed values).

  • Key attribute (or simply key): An attribute (or minimal set of attributes) whose values uniquely identify each entity in an entity set. Exact statement (exam style): "A key K of an entity type E is a minimal attribute set such that for any two distinct entities e1, e2 in E, values of K differ." Emphasise uniqueness and minimality (no proper subset of K is unique).

  • Composite attribute: An attribute that can be divided into subparts (example: Address -> Street, City). Define before use.

  • Multi-valued attribute: An attribute that may have more than one value for a single entity (example: PhoneNumbers).

  • Derived attribute: An attribute whose value can be computed from other attributes (example: Age from DateOfBirth).

  • Relationship: An association among two or more entities. Define formally: a relationship instance is a tuple connecting entity instances.

  • Relationship type: A set of relationships of the same nature; formally a subset of the Cartesian product of participating entity sets. Exact statement: "If R is a relationship type between entity sets E1, E2, ..., En, then R ⊆ E1 × E2 × ... × En." (This is often asked in theory questions.)

  • Degree: The number of entity types participating in a relationship type. Binary means degree 2 (most common); ternary means degree 3, etc.

  • Cardinality constraints: For a binary relationship between E and F, the cardinality constraint describes how many instances of F may be associated with an instance of E and vice versa. Standard patterns: one-to-one (1:1), one-to-many (1:N), many-to-many (M:N). Exact exam statement: For relationship R between E and F, R is 1:N if for each e in E there may be many f in F such that (e,f) in R, but for each f in F at most one e in E with (e,f) in R.

  • Participation constraint (also called total/partial participation): If every entity of E must participate in at least one relationship instance of R, then E has total participation in R; otherwise partial participation.

  • Weak entity: An entity type that cannot be uniquely identified by its own attributes; it needs an identifying relationship with another entity type (owner). Exact statement: "A weak entity has no key; it has a partial key (a discriminator) and relies on an identifying relationship from an owner entity where the owner key plus the partial key uniquely identify the weak entity." Weak entities always have total participation in their identifying relationship.

  • Identifying relationship: A relationship that links a weak entity to its owner entity and provides the identifying attributes.

  • Specialization / Generalization (IS-A): A way to represent inheritance among entity types: a superclass (general) and subclasses (specialised). Define terms: disjointness constraint (subclasses are disjoint) and completeness constraint (partial/total: whether every superclass entity must appear in some subclass).

  • Aggregation: Treating a relationship (possibly with its attributes) as a higher-level entity to participate in other relationships (useful when relationships themselves need to be related to other entities).

  • ER diagram notation (what to draw for exams): Two common notations are Chen and Crow's Foot. If asked to draw, follow these exact instructions:

    • For Chen notation: draw rectangles for entity types (label inside with entity type name); ellipses for attributes (connect to entity rectangle with a line); diamonds for relationships (label inside diamond); draw keys as underlined attribute names; double rectangles for weak entity types; double diamonds for identifying relationships; connect participation with a double line for total participation. Also: use double ellipses for multivalued attributes; dashed ellipses for derived attributes; represent composite attributes by linking an attribute ellipse to its component attribute ellipses.
    • For Crow's Foot: rectangles for entity types, list attributes inside (underlined keys), use crow's-foot symbols to show many side, single line to show one side, circle to show zero allowed for optional, vertical line to show mandatory.
  • Formal mapping of cardinality to functions (exam-style formalism): For a binary relationship R ⊆ E × F,

    • R is 1:1 if R is bijective between some subsets of E and F (each e relates to at most one f and vice versa).
    • R is 1:N (from E to F) if for each f ∈ F there is at most one e ∈ E with (e,f) ∈ R; equivalently, R is a partial function g: F → E. There is no upper bound on |{ f ∈ F | (e,f) ∈ R }| for an e ∈ E.

When an exam asks for a derivation, show set notation for relationships and explain the uniqueness/minimality conditions for keys.

Formulas

(Each formula on its own line, then explanation, unit and when it applies.)

R ⊆ E1 × E2 × ... × En — R is a relationship type connecting entity sets E1..En. Unit: not applicable. Applies when defining n-ary relationships.

key(E) = K where ∀e1,e2 ∈ E, e1 ≠ e2 ⇒ value_K(e1) ≠ value_K(e2) and ∀K' ⊂ K, ∃e1 ≠ e2 with value_{K'}(e1) = value_{K'}(e2) — K is a minimal key for entity set E (uniqueness and minimality). Unit: not applicable. Applies when specifying keys.

1:N (E→F) ⇔ ∀f ∈ F, |{ e ∈ E | (e,f) ∈ R }| ≤ 1 — formal condition for 1:N from E to F (no restriction on how many F-instances a given E-instance may relate to). Unit: not applicable. Applies to binary relationships.

Weak entity identification: UID = OwnerKey ∪ PartialKey — the unique identifier of a weak entity is the concatenation of owner entity key and its partial key. Unit: not applicable. Applies only to weak entities.

(There are no SI units in ER theory; unit lines above state "not applicable".)

Worked examples

Model-answer style; draw instructions included. These are long question types you will see in exams.

Example 1 — University: Students, Courses, Enrollment Question (paraphrase): Design an ER diagram for a university where Students enrol in Courses. Each Student has RollNo, Name, DOB. Each Course has CourseID, Title, Credits. Enrollment stores the Semester and Grade for each student-course pair. A student can enroll in many courses; a course can have many students.

Model answer (step-by-step):

  1. Identify entity types and keys: Student (key: RollNo), attributes Name, DOB. Course (key: CourseID), attributes Title, Credits.
  2. Relationship: Enrolls between Student and Course. Degree: binary. Attributes of relationship: Semester, Grade.
  3. Cardinality: Many-to-many (M:N) because a student can enroll in many courses and a course can have many students. Participation: partial on both sides (a course may have zero students at some times; a student may not enroll in any course in a semester). Represent by a diamond labeled Enrolls connecting Student rectangle and Course rectangle. Write M on both sides or use crow's-foot on both sides.
  4. Draw attributes: ellipses for attributes connected to respective rectangles; draw Semester and Grade as ellipses attached to the Enrolls diamond. Underline RollNo and CourseID.

What to draw and label exactly: Draw two rectangles labeled Student and Course. Under Student, list RollNo (underlined), Name, DOB. Under Course, list CourseID (underlined), Title, Credits. Between them draw a diamond labeled Enrolls with lines to both rectangles. Next to the lines annotate M on both sides (or draw crow's foot at both ends). Connect ellipses Semester and Grade to the diamond.

A full-marks answer will also state relationship type formally: Enrolls ⊆ StudentSet × CourseSet, and remark that Enrolls is M:N so mapping to relations requires a separate relation (this maps to a later topic: mapping ER diagrams to tables).

Example 2 — Bank: Customer and Account (weak entity) Question (paraphrase): Model customers and their accounts. Accounts are identified only by AccountNumber unique per customer (i.e., multiple customers could have the same AccountNumber if we used only that), so Account is a weak entity. Each Account has Balance and OpenDate.

Model answer (step-by-step):

  1. Entities: Customer (key: CustomerID), attributes Name, Address. Account (weak entity) attributes: AccountNumber (partial key), Balance, OpenDate.
  2. Identifying relationship: Owns between Customer and Account. Owns is identifying; Customer is owner. Participation of Account in Owns is total (double line) because every account must be owned by some customer.
  3. Key rule: UID for Account = Customer.CustomerID ∪ Account.AccountNumber. Represent Account as double rectangle, Owns as double diamond (Chen notation) or annotate accordingly in Crow's Foot.
  4. Cardinality: Customer to Account is 1:N (a customer can own many accounts), Account to Customer is N:1 but with identifying relationship must be total participation on Account side.

Draw: Rectangle Customer with CustomerID underlined, Name, Address. Double rectangle Account with AccountNumber (dashed underline to show partial key) and Balance, OpenDate. Double diamond Owns connecting Customer and Account; annotate 1 on Customer side and N on Account side; draw double line from Owns to Account.

Full answer includes exact statement: Owns ⊆ CustomerSet × AccountSet, and for identification UID(Account) = CustomerID ∪ AccountNumber.

Common mistakes

  • Omitting relationship attributes (like Grade in Enrollment) or putting them as attributes of an entity incorrectly.
  • Mixing notation styles inconsistently (e.g., using Chen shapes but Crow's-Foot cardinality lines without explaining). Use one consistent notation.
  • Forgetting minimality for keys: writing a composite key where a single attribute suffices.
  • Not stating participation constraints (total/partial) when they affect weak entities.

For your exam

Kinds of questions this topic produces and what full-marks answers include:

  • Definitions and short notes: define terms like Entity, Relationship, Weak Entity, Key. A full answer gives both plain definition and a short example.
  • Draw/Analysis questions: draw an ER diagram from a paragraph. A full answer lists entity sets, attributes (keys underlined), relationship types (name, degree), cardinality and participation constraints, and any weak entities or specialisation. Use a consistent notation and label everything.
  • Derivations/theory: show formal statements (e.g., relationship as subset of Cartesian product), prove properties like why a weak entity requires total participation. A full answer uses set notation and explains uniqueness/minimality.
  • Long design questions: design ER for a domain, justify design choices, show diagram, and explain mapping to relations briefly (mapping is a separate topic but a short note is useful).

Always: write keys, state cardinalities and participation, and explain reasons for weak entities and identifying relationships.

Quick check

  1. What is a weak entity?
  2. Give the formal definition of a relationship type of degree n.
  3. How do you show total participation in Chen notation?

Answers: 1) An entity with no key that needs an owner to be identified (partial key + identifying relationship). 2) R ⊆ E1 × E2 × ... × En (a subset of the Cartesian product of participating entity sets). 3) Draw a double line between the entity and the relationship.

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?