ER diagrams — entities, attributes, relationships, cardinality: Question bank

Reminders you may need while answering (all built in the parent note):

Entity = a thing you'd make a table for (rectangle). Attribute = a fact describing one entity (ellipse). Relationship = a verb linking entities (diamond).
Cardinality = how many (max). Participation = whether mandatory (min). They are independent.
FK placement rule: in 1:N the foreign key lives on the many side; in M:N you need a junction table.

True or false — justify

Every attribute belongs to exactly one entity.

False — an attribute can also describe a relationship itself (a descriptive attribute), e.g. grade on the Student–Course enrols-in diamond belongs to the relationship, not to Student or Course alone.

A composite attribute and a multivalued attribute are the same idea.

False — composite means one value that splits into parts (name → first, last), so still one name; multivalued means many separate values of the same kind (phone_numbers). One is "one thing with parts", the other is "several things".

Every entity must have a key attribute of its own.

False for weak entities — they have no key of their own and are identified only in combination with the owner entity's key (via a partial/discriminator key plus the owner's key).

The number of lines on a relationship diamond tells you the cardinality ratio.

False — a double line signals total participation (min = mandatory), not the ratio. Ratio (1:1, 1:N, M:N) is written as labels/arrows near the diamond; participation is the single-vs-double line.

A derived attribute like age should be stored as a real column in the table.

False in the conceptual model — it is drawn dashed precisely because it is computed from dob. Storing it risks it going stale; you may physically cache it later, but the ER diagram marks it derived on purpose.

If a relationship is 1:N, then swapping the two entities makes it N:1 and this changes the model.

False — 1:N and N:1 are the same relationship read from opposite ends. "Department has many Employees" and "Employee belongs to one Department" describe one identical fact.

Total participation means the relationship is many-to-many.

False — participation (min, mandatory) and cardinality (max) are independent. A 1:N relationship can be total (every Loan must have a Customer) or partial (a Customer may have no Loan).

A 1:1 relationship should always be merged into a single table.

Mostly-but-not-always — merging is common when participation is total on both sides, but you keep them separate when one side is optional, sparsely filled, or has different security/access needs. It is a design choice, not a hard rule.

Spot the error

"I'll store skills as one column 'java,sql,python' in the Employee table — it's just a property."

The flaw: skills is multivalued, and comma-packing one cell breaks 1NF and makes searching/joining impossible. Fix: model it as a multivalued attribute → its own table EmployeeSkill(emp_id, skill).

"Both Student and Course are 'many', so I'll put a course_id column in Student to link them."

The flaw: it's M:N — a student takes many courses and a course has many students, so one column can't hold a set. Fix: a junction table Enrollment(student_id, course_id).

"Max relationship pairs for 1:N Department–Employee with 3 depts and 10 employees is 3×10 = 30."

The flaw: the "one" side cannot be reused per employee — each employee has exactly one department, so each contributes one pair. Max = 10 (the many side), not 30.

"Department–Employee is 1:N, so I'll put emp_list on Department to hold all its employees."

The flaw: that stores a list in one cell, violating 1NF. In 1:N the FK goes on the many side: Employee gets a dept_id column, one value per row.

"enrols-in links Student and Course, so it's an entity — I'll draw a rectangle."

The flaw: enrols-in is a verb linking two nouns, so it's a relationship (diamond), not an entity (rectangle). The rectangles are Student and Course.

"name splits into first and last, so it's multivalued — I'll draw a double ellipse."

The flaw: splitting into parts of one value is composite (ellipse with child ellipses), not multivalued (double ellipse, which means many separate values).

"A Passport belongs to one Person and a Person has one Passport, so I need a junction table."

The flaw: that's 1:1, not M:N. No junction table needed — put the FK on either side with a UNIQUE constraint to enforce "at most one".

Why questions

Why does the foreign key go on the "many" side of a 1:N relationship, not the "one" side?

Because each entity on the many side has exactly one partner, so a single FK column suffices there. Putting it on the one side would force it to store a list of partners, which violates 1NF.

Why can't an M:N relationship be captured with a foreign key on either table?

Because both sides have many partners, a single FK column can only name one — it can't hold the set. You must externalise the pairs into a junction table with a composite key.

Why do we draw the ER diagram before writing CREATE TABLE?

To separate things from facts from links while thinking in pictures, catching duplicated data, missing relationships, and wrong cardinality before they get baked into table structure and are expensive to undo.

Why is cardinality "the heart of the model"?

Because the ratio (1:1 / 1:N / M:N) directly decides where the FK goes and whether you need a junction table — get it wrong and the entire physical schema is broken or duplicated.

Why underline the key attribute rather than mark it any other way?

It's a fixed convention so any reader instantly sees which attribute uniquely identifies each instance — the anchor every relationship and foreign key will point to.

Why is participation drawn separately from cardinality instead of combined into one symbol?

Because they answer different, independent questions: cardinality asks how many (max), participation asks is it mandatory (min). A relationship needs both facts, and any combination is possible.

Why does a derived attribute get a dashed ellipse instead of just being left off the diagram?

Because it's still a meaningful fact about the entity that queries will ask for; the dashed outline documents that it is computed, warning designers not to store it as an independent source of truth.

Edge cases

What is the maximum number of relationship pairs in an M:N with |A| = 4 and |B| = 5, and why?

4 × 5 = 20, by the rule of product — every one of the 4 A's can pair with every one of the 5 B's, with no reuse restriction on either side.

Can a relationship connect an entity to itself?

Yes — a recursive (unary) relationship, e.g. Employee manages Employee. The same rectangle links to one diamond twice, usually with role labels ("manager"/"subordinate") to tell the two ends apart.

What happens to cardinality bounds when one entity set is empty (|A| = 0)?

The maximum pairs = |A| × |B| = 0 — with no A instances there can be no (a, b) pairs at all, so the relationship set is necessarily empty regardless of |B|.

If a relationship involves three entities at once, is it still a diamond?

Yes — a ternary relationship is one diamond with three edges (e.g. Supplier–Part–Project supplies). It is not the same as three separate binary relationships and cannot always be decomposed into them.

Can two entities be linked by more than one distinct relationship?

Yes — e.g. an Employee works-in a Department and also manages a Department. These are two separate diamonds; each carries its own cardinality and participation.

In a 1:1 relationship where one side has total and the other partial participation, which side should hold the FK?

Put the FK on the total-participation side so every row there is guaranteed a partner (non-null FK), avoiding nulls and matching the "must participate" constraint cleanly.

Is student_id inside the Enrollment junction table a key, a foreign key, or both?

Both — it is a foreign key pointing to Student, and together with course_id it forms the composite primary key of the junction table, so it plays two roles at once.