DBMS · The ER Model

Draw the diagram, mark every line

Q: What is the difference between cardinality and participation?

Cardinality is how many — the 1 , N or M on each end. Participation is whether it is compulsory — a single line means some entities of that side need not take part, a double line means every one must. They are separate answers on the same line, so a 1:1 relationship with one optional side is still 1:1.

One paragraph of college requirements becomes one complete ER diagram, one symbol at a time. Then the same diagram again in the crow’s-foot notation your tools ship with — which cannot draw three of the things Chen just drew.

Build the diagram step by step →

A paragraphthirteen lines of what a college needs to record

→

One diagram5 boxes, 4 diamonds, 18 ellipses, every line end marked

→

Then in crow’s foot7 boxes, because three of those symbols do not exist

01 The idea

Three shapes, and then the markings that carry the marks

You have met the pieces already: entity types and attributes, then relationships and their cardinality, then weak entities, then specialisation. What you have not done yet is take a requirement written in English and end up with one finished drawing. That is what an exam asks for, and it is what an interviewer hands you a marker for.

The shapes themselves are quickly learned. A rectangle is a thing, an ellipse is a fact about that thing, a diamond is a fact that joins two things. Almost all the difficulty is in two other decisions: which English noun deserves a rectangle rather than an ellipse, and what goes on the ends of each line. The ends are where marks are won and lost, because a line with nothing written on it asserts nothing at all.

Every noun in the requirement becomes a rectangle, an ellipse hanging off a rectangle, or a diamond between two rectangles — and no line is finished until it carries a number on each end and is drawn single or double.

NotationThe fixed set of shapes a diagram is drawn in. This lesson uses the Chen and Elmasri convention, which is what Indian university papers and placement interviews are set from. Crow’s foot is the other one you will meet, in tools.

Cardinality markingThe 1, N or M written on each end of a relationship line. It says how many entities of that side one entity of the other side can be joined to.

Participation markingOn the same line, drawn as its line style: single means partial, so some entities need not take part; double means total, so every one must. A different question from cardinality, answered separately.

02 Worked example

The five passes you make over the paragraph

Here is the requirement this whole lesson uses. It is the same college that the next lesson converts into tables, so the diagram you finish here is the diagram you will convert there.

A college keeps records for its students, its courses and its instructors.A student has a roll number, and a name that splits into first and last.A student has a date of birth, and one or more phone numbers.The office prints the student's age on the ID card; nobody ever types it in.Every course has a code, a title and a credit count.Every instructor has an employee id and a name.A student may be issued one library card, with a card number and an issue date.Every card belongs to exactly one student and cannot exist without one.A student pays fees in instalments numbered 1, 2, 3 inside that one student,each instalment having an amount and a paid-on date.Every course is taught by exactly one instructor; an instructor may teach several.A student may enrol in many courses and a course may hold many students, andthe marks for one enrolment are recorded against that enrolment.

Do not try to draw it in one pass. Five passes, each answering one question, and each pass only touching one kind of symbol:

1 · Nounswhich things have an identity of their own: five of them here

→

2 · Noun or fact?a card has its own number, so it is a box; a date of birth is a fact, so it is an ellipse

→

3 · Attribute kindkey underlined, composite branching, derived dashed, multivalued doubled

→

4 · Diamondseach verb joining two boxes becomes a named diamond, never a bare line

→

5 · Mark the ends1, N or M on each end, then single or double for participation

Pass 3 is marked because it is where the four line styles live, and they are the only place the notation is doing real work: age dashed says never stored, phone doubled says several values, an underline says unique across the whole set, a dashed underline says unique only inside one owner. Draw those four as plain ellipses and you have written down a different requirement from the one you were given.

Pass 5 is the one students skip. Line 8 of the requirement — a card cannot exist without a student — is not cardinality. It is participation, and it becomes a double line. Nothing else in the diagram records it.

Pass 2 in detail: box or ellipse?

This is the judgement people get wrong, and the paragraph above contains three phrases that are deliberately close to the line. Ask three questions of each one, in this order.

Can you point at one without naming its owner? A library card can be named by its card number, so it survives on its own: rectangle. A date of birth cannot — “the 4th of March” is not a thing in this college, it is a fact about somebody: ellipse.

Does it have facts of its own? The card has an issue date, which is a fact about the card and not about the student. Two facts hanging together is the shape of an entity type. phone has none: a phone number is just a value, so it stays an attribute — a multivalued one.

Will anything else need to point at it? Nothing in this college refers to a phone number, so it never needs a rectangle. If the requirement had said “a phone number may be marked as the emergency contact for a hostel”, that would change the answer, and the diagram with it.

When the requirement genuinely does not say, do not guess silently. Write the assumption beside the diagram — “assumed a student may hold at most one card at a time” — and mark the line accordingly. In an exam that earns the mark you would otherwise lose; in an interview it is the answer they were waiting for.

03 Mechanics

Every symbol, and what it claims

The legend first, drawn rather than described. Every shape below is drawn by the same code that draws the console in the next section, so what you learn here is exactly what you will see there.

Now the same twelve constructs as claims, with the crow’s-foot column you will need the moment you open a real tool, and the mistake that costs marks in each row.

Construct	Chen symbol	What it claims	In crow’s foot	Where marks are lost
Entity type	rectangle	A thing with an identity of its own, worth storing rows about.	a box with a title bar	Boxing something that is only a fact about another box.
Weak entity type	double rectangle	No key of its own; identified through its owner.	a box whose key includes the owner’s	`drawing it single and inventing a key for it`
Key attribute	underlined ellipse	Unique across the whole entity set, in every state it can reach.	the PK marker in the box	Underlining a value that is merely never null.
Partial key	dashed underline	Unique only among the entities of one owner.	a second column in the key	`calling it the primary key on its own`
Composite attribute	ellipses hanging off an ellipse	One attribute with named parts.	separate fields; the parent has no field	One flat ellipse called name.
Derived attribute	dashed ellipse	Computed on demand, never stored.	`no symbol — a note only`	Storing it, and letting it go stale.
Multivalued attribute	double ellipse	One entity has several values of it at once.	`no symbol — a second box`	`a comma-separated column`
Relationship	diamond joining two boxes	A named fact that links entities of two types.	the line itself, verb written beside it	An arrow with a verb and no diamond.
Identifying relationship	double diamond	The one relationship a weak entity gets its identity through.	a solid line rather than a dashed one	A single diamond touching a weak entity.
Cardinality	1, N, M on each end	How many of that side one of the other side may join to.	bar for one, crow’s foot for many	`one number in the middle of the line`
Participation	single or double line	Double: every entity of that side must take part.	the inner mark: bar for compulsory, ring for optional	`leaving every line single without checking`
Relationship attribute	ellipse on the diamond	A fact about the pairing, true of neither side alone.	`no symbol — an associative box`	Pushing it into one of the two entity types.

Read the fourth column downwards. Three rows say no symbol: multivalued, derived, and a relationship attribute. Those are not gaps in the tool. Crow’s foot only draws what a table can hold, so a repeating value has to become a second box, a computed value has to become a note, and a fact about a pair has to become a box of its own. The next lesson turns exactly those three into tables. Drawing in crow’s foot does the conversion for you, early, whether you wanted it done or not.

Check a marking by reading it out loud

A diagram cannot be checked by looking at it, because a wrong marking looks exactly as neat as a right one. It can be checked by reading each line back as an English sentence and comparing that sentence with the requirement. There is a fixed template: one A takes part in [that end’s number] B, and [every / not every] A must take part.

Run it on the finished TEACHES line. Reading towards the course end: one instructor teaches N courses, and not every instructor must teach — the instructor end is a single line. Reading the other way: one course is taught by 1 instructor, and every course must have one — a double line. Both sentences appear in requirement line 11, so the line is right. Do this for all four relationships and the last pass takes under a minute.

The template also catches the classic inversion. If you had put N next to the instructor, the sentence would read “one course is taught by N instructors”, which line 11 flatly contradicts. Reading it aloud finds that in seconds; staring at the drawing does not.

Two markings per line, always, and they answer different questions. How many? is 1, N or M. Must every one? is single or double. A 1:1 relationship where one side is optional is still 1:1 — the optionality is the other marking, and it is the one that decides where the foreign key goes next lesson.

05 Cheat sheet

The symbols on one card

Left column is what you draw, middle is what it means, right is the follow-up question that catches people who learned the middle column by heart.

You draw	It means	The follow-up
Rectangle	Entity type	When is a noun not one? When it has no identity apart from its owner, or no facts of its own.
Double rectangle	Weak entity type	Its key is the owner’s key plus its own partial key — two columns, not one.
Ellipse	Attribute	Of what? An attribute belongs to one entity type, or to a relationship, never to both.
Underline	Key attribute	Unique in every state the set can reach, not just unique in today’s rows.
Dashed underline	Partial key	`unique only inside one owner`
Ellipses on an ellipse	Composite attribute	The parent contributes no column of its own; only its leaves do.
Dashed ellipse	Derived attribute	`becomes no column at all`
Double ellipse	Multivalued attribute	`becomes a table of its own`
Diamond	Relationship	Name it with a verb, and give it a degree: 2 lines is binary, 3 is ternary.
Double diamond	Identifying relationship	It only ever touches a weak entity type, and that side is always total.
1 / N / M on an end	Cardinality	A number counts its own end, for one entity of the other end: N beside COURSE means one instructor may have N courses. Both ends, always.
Single vs double line	Participation	Double means every entity of that side takes part, with no exceptions.
Ellipse on a diamond	Relationship attribute	On an M:N relationship it is what forces the extra table.

Two markings per lineA number for how many, a line style for whether it is compulsory. Answer both or the line is unfinished.

An underline is a claimIt says this value identifies one entity in the whole set. A dashed one narrows that claim to one owner.

Chen for papers, crow’s foot for toolsExams are set in Chen. Every drawing tool ships crow’s foot. Know which one you are being asked for.

06 Where & why

Where a drawn diagram is the deliverable

Four places this specific skill — paragraph in, marked diagram out — is the thing being judged.

University exam & GATE

“Draw the ER diagram for the following”

A long-answer question worth full marks. Examiners award the boxes cheaply and the markings dearly: unmarked ends, a plain rectangle on a weak entity, and a stored derived attribute are the three deductions that appear again and again.

Placement interview

A paragraph and a whiteboard

You are being watched for the questions you ask before you draw. “Must every card belong to a student?” and “can a student have more than one?” are the two that show you know participation and cardinality are separate.

draw.io · Lucidchart · dbdiagram.io

Crow’s foot, whether you like it or not

Tools ship the notation that maps onto tables, so the moment you open one, multivalued and derived attributes and relationship attributes have nowhere to go. Knowing that in advance is the difference between a clean redraw and a lossy one.

Reviewing someone else’s schema

Reading the diagram back out of the tables

The same rules run in reverse: a table whose whole primary key is other tables’ keys was a relationship, and a two-column table whose key is both columns was probably a multivalued attribute.

If you only remember one habit from this lesson, make it the last pass: walk every line and say out loud “how many, and must every one”. It takes twenty seconds and it is where most of the marks and nearly all the design mistakes are.

07 Interview questions

Asked out loud, answered in twenty seconds

The first three are asked to see whether you have drawn a diagram or only read about one. The rest are where the marks move.

What are the symbols in an ER diagram?

A rectangle is an entity type, an ellipse is one of its attributes, a diamond is a relationship, and lines join them. Then four doubled or dashed variants carry the real information: a double rectangle is a weak entity type, a double diamond is its identifying relationship, a double ellipse is multivalued, and a dashed ellipse is derived. An underline inside an ellipse marks a key, and a dashed underline marks a partial key.

What is the difference between cardinality and participation?

Cardinality is how many — the 1, N or M on each end. Participation is whether it is compulsory — a single line means some entities of that side need not take part, a double line means every one must. They are separate answers on the same line, so a 1:1 relationship with one optional side is still 1:1.

How do you show a derived attribute, and why not just store it?

A dashed ellipse. You do not store it because storing it means two sources of truth for one fact: age changes every birthday while dob never changes, so the stored copy silently goes wrong and nothing in the database notices. Compute it in the query or a view instead.

Why is a multivalued attribute not just a comma-separated column?

Because a cell holding "9876,9812" cannot be searched, constrained or joined on: no index helps, no foreign key can point at one of the values, and every reader has to split the string the same way. The double ellipse says several values, and the honest storage for several values is several rows.

When is something an entity type rather than an attribute?

When it has an identity of its own, or facts of its own, or something else needs to point at it. A library card has its own number and its own issue date, so it is an entity type; a date of birth is a bare value belonging to one student, so it is an attribute. If you can point at one without naming its owner, it is an entity type.

Why is a weak entity type drawn with a double rectangle and a double diamond?

The double rectangle says it has no key of its own. The double diamond says which relationship its identity arrives through, because there can be exactly one such relationship. Its full key is the owner’s key plus its own partial key, and that side of the relationship is always total participation — an instalment with no student is not a thing.

Where do you write the marks a student scored in a course?

On the ENROLS diamond, as an ellipse hanging off it. It is a fact about the pair, not about either side: put it in STUDENT and you lose which course it was for, put it in COURSE and you lose which student. On an M:N relationship an attribute like this is also what forces the extra table in the relational model.

Chen notation or crow’s foot — what is the difference?

Chen draws attributes outside the box as ellipses and relationships as diamonds. Crow’s foot puts attributes inside the box as rows and has no diamond at all, marking cardinality with a bar for one and a three-pronged foot for many. The consequence is that crow’s foot has no symbol for a multivalued attribute, a derived attribute or a relationship attribute, so each of those becomes a structural change instead.

On a 1:1 relationship, which side gets total participation?

The side that cannot exist alone. A library card must belong to a student, so the card end is a double line; a student need not hold a card, so the student end stays single. It matters beyond the drawing: it is the fact that decides which of the two tables receives the foreign key when you convert.

Is a line with no numbers on it wrong, or just incomplete?

Hard

You are handed seven tables. Keys are underlined in your copy; here they are written out: STUDENT(sid), INSTRUCTOR(empid), COURSE(cid, empid not null), LIBRARY_CARD(card_no, sid not null unique), INSTALMENT(sid, inst_no), ENROLS(sid, cid, marks), PHONE(sid, phone). Every non-key column named sid, cid or empid is a foreign key. Reconstruct the Chen diagram with every marking you can justify.

Follow-up

Two of the seven were never entity types, and one more is weak — deciding which from the keys alone is most of the work. Then find the marking that cannot be recovered at all: the schema simply does not record it, and saying so is part of the answer.

Show the hint

A table whose entire primary key is made only of other tables’ keys was never a thing; it was a link or a repeating value. For the missing marking, ask which of the two questions every line answers leaves no trace in a column.