Core CS · DBMS
Draw the kind of thing, not the thing
An entity is Asha. STUDENT is the rectangle the college draws once for all forty thousand of them. This lesson takes one requirement sentence apart into six attributes, and shows which two of the six never become columns at all.
Turn one requirement sentence into a diagram →01 The idea
The rectangle is a kind, the set is today’s contents
The ER model has exactly one job before relationships enter the picture: decide what kinds of thing the database stores facts about, and what facts it stores about each kind. The kinds become rectangles. The facts become ovals hanging off them. Everything difficult about this topic is one question asked in different clothes, which is whether a noun in the requirement is a thing or a fact about a thing.
Start with a college. Asha Menon, roll number 21CS007, is a thing you can point at. So are Ravi and Meera. Each of them is an entity: something distinguishable that the database keeps facts about. The college does not draw a rectangle for Asha, one for Ravi and one for Meera. It draws one rectangle called STUDENT, because all three are the same kind of thing and the same list of facts applies to each. That rectangle is the entity type, and the three of them together are the entity set.
The exam question is almost always the gap between those last two, because only one of them moves. Admit a student and the entity set grows by one. Graduate a batch and it shrinks. Delete every row in the table and the entity set becomes empty, and the entity type is still exactly what it was, because the type is a definition and definitions do not depend on how many things currently satisfy them. The relational parallel is worth holding on to: the entity type is the CREATE TABLE, the entity set is what SELECT * returns today, and an entity is one row of that result.
02 Worked example
From three students to one rectangle
Here is the entity set the college holds this morning. Three entities, one type. Read the rows as three things in the world, not as a table yet, and notice that Asha’s phone cell already holds two values at once.
| Roll no | Name | Address | Phone | Date of birth |
|---|---|---|---|---|
| 21CS007 | Asha Menon | 12, MG Road, Bengaluru 560001 | 9876543210 / 08041234567 | 2004-08-19 |
| 21CS012 | Ravi Kumar | 8, Park Lane, Hyderabad 500084 | 9440011223 | 2003-11-02 |
| 21CS019 | Meera Nair | 4, Lake View, Kochi 682016 | 9847556677 | 2005-01-30 |
Three rows, and every column is a question you can ask of all three. That is what makes them one entity set rather than three unrelated records. Now climb the ladder from the girl in the classroom to the shape on the whiteboard. The highlighted node is the one students skip, and it is the one the exam asks about.
Nodes 3 and 4 are the whole of the definition question. Node 3 is a sentence about what a student is; node 4 is a count of who there are. An interviewer who asks “entity or entity set?” is checking whether you can put an operation on the right side of that line: admit a student changes node 4 and leaves node 3 untouched, while start recording blood group changes node 3 and forces every entity in node 4 to answer a new question.
Node 5 is where the notation lives. The requirement sentence the college started from was: the roll number, the name, the address, the phone numbers, the date of birth; and the office prints the age on the ID card. Six facts, and five different ways of drawing an oval, because they are not all the same kind of fact.
[ S T U D E N T ] entity type: a rectangle | +-- ( _roll_no_ ) key: underline the name inside the oval +-- ( name ) simple, single-valued: a plain oval +-- ( dob ) stored: a plain oval +-- (- age -) derived from dob: a dashed oval +-- (( phone )) multivalued: a double oval +-- ( address ) composite: an oval with child ovals +-- ( door_no ) +-- ( street ) +-- ( city ) +-- ( pin ) -- 1 rectangle · 10 ovals · 1 underlined · 1 dashed · 1 doubledCount the ovals with your finger and you get ten, not six: address contributes five on its own, because a composite attribute is drawn as a parent with its parts attached. That count is the first thing to check on your own drawing, and it is the number the console in section 04 arrives at one step at a time.
Those five treatments are not decoration. The plain oval costs you nothing, the underline costs you a PRIMARY KEY clause, the child ovals cost one column each, and the two unusual shapes — the double oval and the dashed one — are the expensive ones, because they are the only two that do not end up as columns of STUDENT at all: one buys a whole second table, the other buys nothing. These are Chen’s shapes, which is what exams and textbooks draw; the crow’s-foot tools you will meet at work list attributes inside the rectangle and have no shape for multivalued or derived at all, so the two expensive decisions stop being visible on the diagram and show up only in the schema. The next section is that sentence made precise.
03 Mechanics
Eight labels, and the one test that decides each
Attribute kinds are not a list to memorise; they are four independent questions you ask of every attribute, and most attributes come back with two or three labels. Simple against composite is one question, single-valued against multivalued is a second, stored against derived is a third, and key against non-key is a fourth. The middle column is the test. The last column is what the label actually costs you, and it is the column interviewers push on.
| Label | The test that decides it | In our STUDENT | Chen notation | What it becomes in SQL |
|---|---|---|---|---|
| Simple (atomic) | Can it be split into parts somebody would query separately? No. | roll_no, name, dob, city |
plain oval | one column |
| Composite | Yes, and each part is meaningful on its own and has its own domain. | address = (door_no, street, city, pin) |
an oval with child ovals attached | one column per leaf; the parent itself is never a column |
| Single-valued | At most one value for one entity at one instant. | roll_no, name, dob, address |
plain oval | one column |
| Multivalued | Two values true for the same entity at the same instant |
phone = {9876543210, 08041234567} |
double oval | its own table, keyed on the parent key plus the value |
| Derived | You could delete it and get it back with no new information |
age, from dob and the current date |
dashed oval | no column you maintain: a view, an expression, or a generated column |
| Stored | The attribute a derived one reads. Nothing else in the database produces it. | dob |
plain oval | one column |
| Key | Distinct across every entity of the type, in every legal state, not just today. | roll_no |
the name underlined inside its oval | PRIMARY KEY, or UNIQUE NOT NULL |
| Complex | Composite and multivalued nested inside each other, to any depth. | {contact({phone(std_code, number)}, address(...))} |
braces for multivalued, parentheses for composite | a table for every pair of braces |
Now the same six attributes as a schema. Nothing here is a new idea; every line is one of the labels above, spent. Read the comments as the bill.
CREATE TABLE student ( roll_no CHAR(7) PRIMARY KEY, -- key: distinct for all legal data, never NULL name VARCHAR(60) NOT NULL, -- simple, single-valued door_no VARCHAR(10), -- \ street VARCHAR(60), -- | composite address, one column per leaf, city VARCHAR(40), -- | so "students in Bengaluru" is a lookup pin CHAR(6), -- / six characters declared; "digits only" is not dob DATE -- stored, and left nullable. age is deliberately absent);CREATE TABLE student_phone ( -- the multivalued attribute gets a table roll_no CHAR(7) REFERENCES student(roll_no), phone VARCHAR(15), PRIMARY KEY (roll_no, phone) -- the pair: no number twice for one student);CREATE VIEW student_age AS -- derived: computed on read, never stored SELECT roll_no, name, date_part('year', age(dob)) AS age FROM student;The composite attribute cost four columns and no tables. Lines 4 to 7 are the four leaves of address. There is no address column, because a composite attribute exists only in the diagram; what reaches the table is its parts. Keep it as one string instead and counting students by city becomes a substring search that no index helps and that a street named “Bengaluru Road” quietly corrupts.
The multivalued attribute cost a whole table. Line 10 exists because first normal form says a cell holds one value, and Asha has two phone numbers that are both true right now. Line 13 makes the pair the key, so the same number cannot be recorded twice for one student. Columns named phone1 and phone2 are the alternative, and they fix a maximum you will regret and turn “who owns this number” into a search across however many columns somebody happened to create.
The derived attribute cost nothing, as long as you do not store it. Line 16 computes age on read. Asha was born on 19 August 2004: on 27 July 2026 she is 21, and on 19 August 2026 she is 22, with no insert and no update in between. A column that changes without a write is a column that will be wrong. The expression is vendor-flavoured — MySQL writes it TIMESTAMPDIFF(YEAR, dob, CURDATE()) — but the placement is not.
The domain, which Elmasri calls the value set, is the set of values an attribute is allowed to take. The domain of pin is the six-digit strings, the domain of dob is dates and not any date, and the domain of a mark is the integers 0 to 100. In SQL you write the coarse half as the column type and the rest as a CHECK. Line 7 declares six characters and stops there, so “ABC123” is still accepted: whatever you leave out of both halves is a value the database will take without complaint.
NULL is not in the domain. It is a marker meaning there is no value in this cell, and the marker carries three different meanings that it cannot tell apart. Three students, two extra columns, three blanks, one of each meaning:
| Roll no | dob | hostel_room | landline |
|---|---|---|---|
| 21CS007 | 2004-08-19 | NULL · not applicable | 08041234567 |
| 21CS012 | NULL · missing | H-204 | 04023456789 |
| 21CS019 | 2005-01-30 | H-118 | NULL · unknown |
Not applicable: Asha is a day scholar, so there is no hostel room to record and there never will be while she is a day scholar. Missing: Ravi has a date of birth; the admission form was left blank, so the value exists in the world and not in the database. Unknown: nobody can say whether Meera’s house even has a landline, so you cannot call the value missing without first claiming it exists. Elmasri splits the second and third slightly differently from this, and some syllabi name the third “not recorded”; every version of the list has three buckets, and the first bucket is the one that carries marks, because it is the only one that says the design is wrong rather than the data incomplete.
Those three blanks change what queries return, and this is where the topic stops being vocabulary. Against exactly the three rows above: COUNT(*) is 3 and COUNT(dob) is 2, because aggregates skip NULLs. WHERE hostel_room = 'H-204' returns 1 row and WHERE hostel_room <> 'H-204' returns 1 row, which is 2, not 3. Asha’s row is in neither, because comparing anything with NULL gives unknown rather than true or false, and WHERE keeps only the rows where the condition is true. IS NULL is the only operator that inspects the marker instead of comparing to it, and it finds her.
05 Cheat sheet
Every term on one card
Thirteen terms, and for each one the mistake that loses the mark. The right-hand column is the useful half: nobody is tested on whether you can recite a definition, only on whether you can apply it to a noun you have not seen before.
| Term | What it means, in one line | The trap |
|---|---|---|
| Entity | one thing you can point at: Asha, roll number 21CS007 | calling the rectangle an entity |
| Entity type | the definition: a name and a list of attributes. This is the rectangle | Silberschatz says "entity set" for this too |
| Entity set | every entity of that type in the database right now, also called the extension | it changes on every admission; the type does not |
| Simple (atomic) | not divisible into parts anybody queries separately | "simple" and "atomic" are the same word here |
| Composite | divides into parts that are separately meaningful, each with its own domain | stored as one string, "count by city" becomes a text search |
| Single-valued | at most one value for one entity at any one instant | a value that changes over time is still single-valued |
| Multivalued | two or more values true for one entity at the same instant | phone1, phone2, phone3 fixes a maximum you will regret |
| Derived | recomputable from stored data, or from stored data and the clock | stored, it is wrong from the morning of the birthday |
| Stored | the attribute a derived one reads; nothing else produces it | a measured value that changes is stored, not derived |
| Key attribute | distinct for every entity of the type, in every legal state | read off today's rows instead of off the rule |
| Complex | multivalued and composite nested: braces and parentheses | notation only; a relational cell cannot hold one |
| Domain (value set) | the set of values the attribute is allowed to take | the column type is the coarse half; the rest is a CHECK |
| NULL | no value here: not applicable, missing, or unknown | NULL = NULL is unknown, so use IS NULL |
06 Where & why
What each label costs you in a real database
Attribute kinds sound like diagram vocabulary until you try to declare one. Every real system supports some of them directly, refuses others, and disagrees with its neighbours about NULL. These four are where the disagreements are, and naming one of them in an interview is the difference between having drawn ER diagrams and having built a schema.
Both let you declare a derived attribute as a generated column, so the database recomputes it: GENERATED ALWAYS AS (...) STORED in PostgreSQL from version 12, VIRTUAL or STORED in MySQL. Age is the one that will not compile, because the expression has to be deterministic and the current date is not. That is why age lives in a view. SQL Server does allow a non-deterministic computed column, but it will neither persist nor index it.
PostgreSQL has composite types (CREATE TYPE address AS (...)) and array columns (text[]), so both shapes can live inside a single column. Most schemas still give the multivalued attribute its own table, because an array cannot carry a foreign key, cannot be constrained value by value, and gives you no place to record when each phone number was added.
PRIMARY KEY implies NOT NULL everywhere, which is the ER rule enforced. UNIQUE is where vendors split: SQL Server allows exactly one NULL row in a unique column because it treats NULLs as equal, while PostgreSQL, MySQL and SQLite allow many because two unknowns are not known to be equal. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT if you want the other behaviour.
SQLite uses type affinity, not type enforcement, so a column declared INTEGER will store the text 'hello' without complaint. The attribute’s domain exists only if you add a CHECK or declare the table STRICT, available since 3.37. It is the clearest demonstration that a domain is a rule you write down, not a property the column has by being named.
07 Interview questions
What they actually ask
This is the opening of almost every ER round, and the first two questions are definitions the interviewer expects you to get wrong in a specific way. Answer with an example from a system you have built, not from a textbook college, and the round moves on to relationships faster.
What is the difference between an entity and an entity set?
Entity type or entity set? They sound like the same thing.
What makes an attribute a key attribute?
What is a composite attribute, and what happens to it in a table?
What is a multivalued attribute?
Why should age not be a column?
Something changes over time. Does that make it derived?
What is the domain of an attribute?
What does NULL actually mean?
I ran one query for room = H-204 and another for room <> H-204, and together they returned fewer rows than the table has. Why?
Can a key attribute be NULL?
When would you actually use any of this?
08 Practice problems
Six to work through
For every one, ask the four questions in order before you write a label: can one entity have two of these at the same instant, can it be split into parts somebody queries, could you delete it and recompute it, and is it distinct for every entity in every legal state. Labelling first and justifying afterwards is how people end up calling a measurement derived.