ER Model Fundamentals

An entity is a real-world object; an entity set is a collection of similar entities. Attribute types to memorize (mnemonic SCAM-D): Simple vs Composite (Name -> First, Last), Single-valued vs Multivalued (phone numbers, shown by double ellipse), Stored vs Derived (Age derived from DOB, shown by dashed ellipse), Key attribute (underlined). A weak entity has no key of its own and depends on an owner (identifying) entity; it is drawn with a double rectangle and connected via a double-diamond identifying relationship. Its discriminator (partial key) is underlined with a dashed line. NULL handling: attributes can be NULL when not applicable or unknown. Remember: composite = breakable into parts; multivalued = many values for one entity; derived = computable, never stored.

ER diagram symbols (memorize for quick recall): Rectangle = entity set; Double rectangle = weak entity; Ellipse = attribute; Double ellipse = multivalued attribute; Dashed ellipse = derived attribute; Underlined attribute = primary key; Diamond = relationship; Double diamond = identifying relationship (for weak entity); Line connecting = participation. Cardinality is written on lines as 1, N, M. Participation: single line = partial, double line = total. Min-max (look-across vs look-here) notation gives precise (min,max) constraints. Shortcut: a weak entity ALWAYS has total participation in its identifying relationship, so that connecting edge is always a double line.

Specialization is top-down (split a superclass into subclasses, e.g., Employee -> Engineer, Secretary). Generalization is bottom-up (combine entities into a superclass). Both use the ISA triangle and support attribute inheritance. Constraints: Disjoint (d) vs Overlapping (o) tells whether an entity can belong to multiple subclasses; Total vs Partial tells whether every superclass entity must belong to some subclass. Aggregation treats a relationship (with its participating entities) as a higher-level entity so it can participate in another relationship (modeling a relationship between an entity and a relationship). Memory aid: Specialization = Subclass creation (both start with S); Aggregation = treating a relationship as one Abstract block.

Cover the answer, recall, then check. 12 cards on ER model fundamentals for GATE.

Q1. Difference between an entity and an entity set?
A1. An entity is a single real-world object (one specific student). An entity set is a collection of entities of the same type sharing the same attributes (all students). The rectangle in an ER diagram denotes the entity set.

Q2. Name the five attribute types and their ER diagram shapes.
A2. Simple/atomic — plain ellipse; Composite (splits into parts) — ellipse with attached sub-ellipses; Multivalued — double ellipse; Derived (computed, e.g. Age from DOB) — dashed ellipse; Key — underlined ellipse.

Q3. What is the degree of a relationship?
A3. The number of entity sets participating in it. Unary/recursive = 1, binary = 2, ternary = 3. Degree is about the relationship; it is not the cardinality ratio.

Q4. List the cardinality ratios for a binary relationship.
A4. 1:1, 1:N, N:1, and M:N. It bounds how many entities of one set can associate with the other.

Q5. Total vs partial participation — how is total shown?
A5. Total participation (every entity of the set must take part) is drawn with a double line connecting the entity to the relationship diamond. Partial participation uses a single line.

Q6. What is a weak entity set and how is it drawn?
A6. An entity set with no attributes sufficient to form its own primary key. Drawn as a double rectangle; it depends on an owner (identifying) entity via an identifying relationship (double diamond) and always has total participation in it.

Q7. What is a partial key (discriminator)?
A7. An attribute of a weak entity that distinguishes its entities only within a single owner. Shown with a dashed underline. The weak entity's primary key = owner's primary key + partial key.

Q8. Meaning of the min–max (structural) constraint (min, max) on an edge?
A8. It bounds how many relationship instances an entity of that set participates in. min = 0 ⇒ partial participation; min ≥ 1 ⇒ total. max = 1 or N controls the cardinality. Note: min–max labels sit on the same side as the entity, opposite to the arrow-head convention.

Q9. Specialization vs generalization?
A9. Specialization is top-down: split a superclass into subclasses (ISA). Generalization is bottom-up: combine several entity sets sharing features into a superclass. Both use the ISA triangle; they are inverse design processes.

Q10. Disjoint vs overlapping, and total vs partial in specialization?
A10. Disjoint: an entity belongs to at most one subclass; overlapping: it may belong to several. Total: every superclass entity must belong to some subclass; partial: it need not.

Q11. What is aggregation in the ER model?
A11. Treating an entire relationship set (together with its participating entities) as a single higher-level entity so you can build a relationship on that relationship — used when a relationship must itself participate in another relationship.

Q12. Can every ternary relationship be replaced by three binary relationships?
A12. No. A ternary relationship can capture constraints among three entities simultaneously that three separate binaries cannot faithfully represent, so decomposing it may lose information.

Worked Example

Problem: Consider the relationship "Employee works_for Department," where every employee must belong to exactly one department, and a department may have zero or more employees. (a) State the cardinality ratio of the relationship. (b) State the participation (total/partial) constraint of each entity.

Solution:
(a) Cardinality ratio — determine how many entities of each side can be associated with the other:

• One department can be associated with many employees ("zero or more employees").
• One employee is associated with exactly one department.
  So the ratio Department : Employee is 1 : N (one-to-many).

(b) Participation constraints — whether every entity instance must take part:

• Employee: "every employee must belong to exactly one department" → every Employee participates → total participation (shown as a double line to the relationship).
• Department: "may have zero or more employees" → a department can exist with no employees → partial participation (single line).

Answer: (a) Cardinality ratio 1:N (Department to Employee); (b) Employee has total participation, Department has partial participation.