ER diagrams — entities, attributes, relationships, cardinality

The three core building blocks

Types of attributes (WHAT can an attribute be)

Cardinality — the heart of the model

Formula Counting maximum relationship instances (HOW cardinality bounds data)

Let entity set $A$ have $|A|$ instances and $B$ have $|B|$ . The maximum number of relationship rows possible is: $R_{\max} = |A| \times |B|$ Derivation from first principles: a relationship instance is a pair $(a,b)$ with $a\in A,\ b\in B$ . By the rule of product (each of $|A|$ choices of $a$ can pair with each of $|B|$ choices of $b$ ), the total number of distinct pairs is $|A|\cdot|B|$ . Cardinality constraints then restrict this:

1:1 forces $R \le \min(|A|,|B|)$ — each element used at most once on both sides.
1:N (one A → many B) forces $R \le |B|$ — each $b$ has exactly one partner, so #pairs = #of B's that participate $\le |B|$ .
M:N allows up to the full $|A|\cdot|B|$ .

Figure — ER diagrams — entities, attributes, relationships, cardinality

From ER to tables (HOW to translate)

Common mistake Steel-manning the classic errors

Mistake 1: "Phone numbers is just an attribute, store it as one column." Why it feels right: a phone is a property of a person. The flaw: it's multivalued — packing "123,456" into one cell breaks 1NF and search. Fix: model as multivalued attribute → becomes its own table.

Mistake 2: "1:N max pairs = |A|×|B|." Why it feels right: that's the count for all possible pairs. The flaw: the "one" side restricts reuse, so max is $|B|$ (the many side). Always ask "which side is forced to a single partner?".

Mistake 3: "Make enrolment a column inside Student." Why it feels right: students enrol, so put it with students. The flaw: M:N → a student has many courses and a course has many students; a column can't hold a set. Fix: junction table.

Mistake 4: Confusing cardinality (max) with participation (min). Why it feels right: both look like "lines on the diamond". The flaw: cardinality = how many; participation = whether mandatory. They are independent — a 1:N can be total or partial.

Recall Feynman: explain to a 12-year-old

Imagine a box of toys. Each toy is a thing (entity) — a car, a doll. The toy's colour and size are facts about it (attributes). Now, "the doll rides in the car" — that's a link (relationship). Cardinality is just answering "can ONE car carry MANY dolls, or only one?" Once you know that, you know how to write it down: if one car holds many dolls, you write the car's name on each doll's tag (FK on the many side). If many dolls share many cars, you keep a separate notebook of (doll, car) pairs (junction table). The whole diagram is just boxes for things, ovals for facts, diamonds for links, with numbers saying how many.

Flashcards

What is an entity in an ER diagram?

A real-world thing we store data about; drawn as a rectangle.

What is an attribute and its shape?

A property describing an entity; drawn as an ellipse.

What is a relationship and its shape?

An association between entities; drawn as a diamond.

How is a key attribute marked?

Underlined.

How is a multivalued attribute drawn?

Double ellipse.

How is a derived attribute drawn?

Dashed ellipse.

What are the four cardinality ratios?

1:1, 1:N, N:1, M:N.

In a 1:N relationship, where does the foreign key go?

On the "many" side.

How do you implement an M:N relationship?

With a junction/associative table holding both foreign keys.

Maximum relationship pairs for entity sets of size |A| and |B| (M:N)?

|A| × |B|.

Maximum pairs for a 1:N relationship?

|B| (the many side), since each B has one A.

Difference between cardinality and participation?

Cardinality = how many (max); participation = whether mandatory (min, total vs partial).

How is total participation shown?

A double line connecting entity to relationship.

What does a composite attribute mean?

An attribute that decomposes into sub-parts (e.g. name → first, last).

Connections

Relational Model — tables, keys, foreign keys
Normalization — 1NF, 2NF, 3NF
Primary keys and Foreign keys
SQL CREATE TABLE and constraints
Cardinality and JOIN behaviour
Database design lifecycle — conceptual to physical

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Socho tumhe ek college ka data store karna hai. Pehle code likhne ke bajay, hum ek picture banate hain jise ER diagram kehte hain. Isme teen cheezein hoti hain: Entity (ek real cheez jaise Student ya Course — rectangle me), Attribute (us cheez ki property jaise name, age — ellipse me), aur Relationship (do cheezon ke beech ka connection jaise "Student enrols-in Course" — diamond me). Simple rule: agar noun ki alag table banegi to entity, agar sirf describe karti hai to attribute, agar verb hai jo do nouns ko jodta hai to relationship.

Sabse important concept hai cardinality — yeh batata hai kitne se kitne. Ek department me bahut saare employees ho sakte hain par ek employee ek hi department me — yeh 1:N hai. Ek student bahut courses leta hai aur ek course bahut students leta hai — yeh M:N hai. Yeh decision sabse zaroori hai kyunki isi se decide hota hai ki foreign key kahan jayegi. Rule yaad rakho: 1:N me FK hamesha "many" wali side pe jaati hai, aur M:N ke liye ek alag junction table banani padti hai jisme dono ki keys hoti hain.

Ek common galti: log phone numbers ko ek hi column me comma se daal dete hain — yeh galat hai kyunki phone multivalued attribute hai, iski alag table banni chahiye (1NF break hoti hai warna). Doosri galti: cardinality (max kitne) ko participation (mandatory hai ya nahi) se confuse karna — yeh do alag cheezein hain. Double line ka matlab total participation, yaani har instance ko relate karna zaroori hai.

Bottom line: ER diagram pehle banao, fir mechanically tables me convert karo. Boxes for things, ovals for facts, diamonds for links, aur numbers batate hain how many. Itna clear ho gaya to database design half-done samjho.