ER Model Notes + MCQ
GATE · UGC · RPSC · ISROObjectives: Reduce redundancy, improve consistency, integrity, performance.
Proposed by: Peter Chen (1976). Used in Conceptual Design, DBMS independent.
| Entity | Entity Type | Entity Set |
|---|---|---|
| Rahul (instance) | Student (blueprint) | All Students (collection) |
| Strong | Weak |
|---|---|
| Independent, has PK, single rectangle | Dependent, no complete PK, double rectangle, total participation |
Weak example: Employee → Dependent (PK: EmployeeID + DependentName)
| Degree | Example |
|---|---|
| Unary | Employee manages Employee |
| Binary | Student enrolls Course (most common) |
| Ternary | Supplier supplies Part to Project |
Relationship between relationships. Example: Manager monitors (Works-On).
- Strong entity → one table
- Weak entity → owner PK + partial key
- Composite → split into atomic
- Multi-valued → separate table
- Derived → generally not stored
- 1:1 → FK in either
- 1:N → FK on Many side
- M:N → new relation
| Symbol | Meaning |
|---|---|
| Rectangle | Entity |
| Double Rectangle | Weak Entity |
| Diamond | Relationship |
| Double Diamond | Identifying Relationship |
| Oval | Attribute |
| Double Oval | Multi-Valued |
| Dashed Oval | Derived |
| Underlined | Key |
ER → Peter Chen (1976) · Weak → Total Participation · 1:N → FK on Many · M:N → separate table
Bottom-Up = Generalization · Top-Down = Specialization
- ER proposed by Peter Chen (1976)
- Weak entity → total participation
- Derived attribute not stored
- Composite → split into atomic
- M:N → separate table
- 1:N → FK on Many side
- Generalization → Bottom-Up
- Specialization → Top-Down
- Cardinality → Maximum
- Participation → Minimum
Click on an option below to check your answer. The explanation will be revealed instantly.
Question: Let R be a binary relationship set between entity sets X and Y. If the structural constraint of X in R is specified as (0, *), and for Y in R it is specified as (1, 1), what does this imply about the relationship?
• Structural Constraints Notation: (min, max).
• min = Participation (0 = partial, ≥1 = total).
• max = Cardinality (1 = one, * = many).
• X(0,*) → partial participation, many. Y(1,1) → total participation, one.
• Therefore, Y is total, X is partial, and relationship is 1:N from X to Y.
Question: Consider the following statements regarding Specialization and Generalization in an ER Diagram:
- Statement I: Generalization is a top-down design process where we view a high-level entity split into lower-level entities.
- Statement II: In an overlapping specialization, a lower-level entity instance can belong to more than one subclass.
Which of the above statements is/are CORRECT?
• Statement I is incorrect: Generalization is bottom-up, not top-down.
• Statement II is correct: Overlapping specialization allows an entity to belong to multiple subclasses.
Question: An ER diagram contains a strong entity Customer (5 attributes, including 1 multi-valued attribute), another strong entity Order (3 simple attributes), and a 1:N relationship Places connecting Customer to Order. What is the minimum number of relational tables needed to implement this design cleanly without violating 1NF?
1. Customer simple attributes → Table 1.
2. Multi-valued attribute → separate Table 2.
3. 1:N relationship → place FK of Customer (1-side) into Order (Many-side) → Table 3.
Total = 3 tables.
Question: Suppose an ER diagram has two strong entities A and B linked by a binary 1:1 relationship R. If the participation of A in R is Total and the participation of B in R is Partial, what is the most optimal way to map this into relational tables to minimize both the table count and NULL values?
• For 1:1, we can avoid a third table by combining into one of the entity tables.
• If FK placed in B (partial side), many NULLs appear because B may not participate.
• If FK placed in A (total side), every row in A participates, so no NULLs. Hence, place PK of B inside A.
Question: A ternary relationship set R involves three strong entity sets A (primary key KA), B (primary key KB), and C (primary key KC). The relationship R has no many-to-one constraints (it is a many-to-many-to-many relationship). When mapping this ternary relationship into the relational model, what will be the primary key of the table generated for R?
• For a many-to-many-to-many ternary relationship, no single or pair of keys can uniquely identify a combination.
• The primary key must be the composite of all three foreign keys: (KA, KB, KC).