Entities, attributes and ER diagrams
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Database design is deciding which tables you need, which columns each table holds and how the tables link, before you write a single CREATE TABLE. The first step is to find the entities: the things the business must remember facts about.
From a requirement to entities
An entity is a kind of thing the system stores facts about, with many instances, such as a student, a hostel room or a train. Each entity usually becomes one table, and each single instance becomes one row.
Before any SQL, read the requirement and underline every noun. The text below sorts the nouns of a hostel requirement into entities, attributes and things that are not stored.
Example 1 · From a hostel requirement to entities
Requirement from the hostel office: "The hostel has blocks A to D. Each block has rooms, and a room has a room number, a floor and a number of beds. One warden looks after each block. Students (residents) get a room when they join; a few wait without a room for some days."Noun found Decision-------------------- -----------------------------------------------hostel the whole system, not a tableblock entity -> table blocksroom entity -> table roomsroom number, floor attributes of a roomnumber of beds attribute of a roomwarden entity -> table wardensstudent / resident one entity (same thing) -> table residentsday(s) part of a sentence, not storedEntities: blocks, rooms, wardens, residents Attributes so far: room number, floor, beds (all on rooms)
What this code does
- Every noun is sorted into one of three bins: entity (a table), attribute (a column of some table) or not stored at all.
- "Student" and "resident" name the same people, so they become one table. The name
residentsalso avoids confusion with the collegestudentstable in the practice database.
Why bother before writing SQL? A table created in a hurry is easy to change while it is empty. Once an app is live and the table holds lakhs of rows, every redesign means moving real data without losing any. Ten minutes with a requirement and a pencil saves days later. A simple, reliable method: read the requirement and underline every noun (a naming word). Then sort the nouns.
- A noun with many instances and facts of its own (block, room, student) is an entity.
- A noun that is just one value about something else (room number, floor, phone) is an attribute, not a table.
- The noun that names the whole system ("the hostel", "the app") is not a table.
- Two nouns that mean the same thing ("student", "resident") are one entity: pick one name.
Order of execution
Find the entities
Put the steps of the noun method in order.
Attributes and identifiers
An attribute is one fact about an entity and becomes a column. An identifier is the attribute, or group of attributes, that picks out exactly one instance, and it becomes the primary key or a UNIQUE key.
Each fact about a room becomes a column, and one column picks out the row. The script below shows a surrogate key (a number made by the database) next to a natural key (a real-world code) kept unique.
Example 2 · Attributes, a surrogate key and a natural key
CREATE TABLE residents ( resident_id INT AUTO_INCREMENT PRIMARY KEY, -- surrogate key: no meaning roll_no VARCHAR(12) NOT NULL UNIQUE, -- natural key: still unique name VARCHAR(60) NOT NULL, dob DATE NOT NULL -- store the date, not the age);
INSERT INTO residents (roll_no, name, dob) VALUES ('22CSE041', 'Nikhil Rao', '2004-08-19'), ('22ECE007', 'Sana Sheikh', '2005-01-02'), ('23ME112', 'Varun Pillai', '2006-03-30');
-- Age is derived: calculate it when you need itSELECT resident_id, roll_no, name, TIMESTAMPDIFF(YEAR, dob, '2026-07-01') AS age_on_1_julyFROM residentsORDER BY resident_id;
-- The natural key still blocks a second row for the same studentINSERT INTO residents (roll_no, name, dob)VALUES ('22CSE041', 'Nikhil R', '2004-08-19');+-------------+----------+--------------+---------------+ | resident_id | roll_no | name | age_on_1_july | +-------------+----------+--------------+---------------+ | 1 | 22CSE041 | Nikhil Rao | 21 | | 2 | 22ECE007 | Sana Sheikh | 21 | | 3 | 23ME112 | Varun Pillai | 20 | +-------------+----------+--------------+---------------+ ERROR 1062 (23000): Duplicate entry '22CSE041' for key 'residents.roll_no'
What this code does
resident_idis the primary key the database uses for links;roll_nois the real-world identifier and keeps its ownUNIQUErule, which is why the last insert fails with error 1062.- Age is not a column.
TIMESTAMPDIFF(YEAR, dob, date)works it out on the date you choose, so it is never out of date.
Some entities already have an identifier from the real world, such as a roll number, a PNR or a vehicle number. This is a natural key. Many designs still add a surrogate key: a number with no meaning, made by AUTO_INCREMENT, used as the primary key. The natural key then gets a UNIQUE constraint, so duplicates are still blocked.
Two kinds of attribute need care. A derived attribute can be worked out from others, such as age from date of birth: store the date and calculate the age in the query, or the stored age goes wrong every birthday. A multi-valued attribute has several values for one instance, such as a student's many phone numbers: it needs its own table, as the First normal form lesson shows.
UNIQUE (block_code, room_no) pair or a composite primary key.Drawing an ER diagram (Workbench and on paper)
An ER diagram (entity-relationship diagram) is a picture of a design: each entity is a box listing its attributes, and each relationship is a line between boxes. The marks at the line ends say how many rows can be on each side.
Draw the boxes and lines before the CREATE TABLE script. Below is the hostel design as a text diagram in crow's foot style, then the script that builds exactly what the diagram shows.
Example 3 · The hostel ER diagram
+-------------------+ +-------------------+
| wardens | | blocks |
|-------------------| |-------------------|
| PK warden_id | ||--------|| | PK block_code |
| name | | name |
| phone (UQ) | | FK warden_id (UQ) |
+-------------------+ +-------------------+
||
|
o{
+-------------------+
| rooms |
|-------------------|
| PK room_id |
| FK block_code |
| room_no |
| floor |
| beds |
+-------------------+
o|
|
o{
+-------------------+
| residents |
|-------------------|
| PK resident_id |
| roll_no (UQ) |
| name |
| dob |
| FK room_id |
+-------------------+Marks (read the mark next to the box it touches): || exactly one o| zero or one |{ one or many o{ zero or manyOne warden looks after exactly one block; each block has exactly one warden. One block has zero or many rooms; each room is in exactly one block. One room has zero or many residents; each resident has zero or one room.
What this code does
- Four boxes for the four entities, with
PK,FKandUQmarking the keys. Every line ends in two marks, one at each box. - The
o|next to rooms on the lower line is "zero or one room": a new resident may still be waiting for a room.
Example 4 · The CREATE TABLE script for the diagram
CREATE TABLE wardens ( warden_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, phone VARCHAR(15) NOT NULL UNIQUE);
CREATE TABLE blocks ( block_code CHAR(1) PRIMARY KEY, name VARCHAR(40) NOT NULL, warden_id INT NOT NULL UNIQUE, -- ||--|| : one warden per block FOREIGN KEY (warden_id) REFERENCES wardens (warden_id));
CREATE TABLE rooms ( room_id INT AUTO_INCREMENT PRIMARY KEY, block_code CHAR(1) NOT NULL, -- each room: exactly one block room_no SMALLINT NOT NULL, floor TINYINT NOT NULL, beds TINYINT NOT NULL CHECK (beds BETWEEN 1 AND 4), UNIQUE (block_code, room_no), -- room 101 exists in A and in B FOREIGN KEY (block_code) REFERENCES blocks (block_code));
CREATE TABLE residents ( resident_id INT AUTO_INCREMENT PRIMARY KEY, roll_no VARCHAR(12) NOT NULL UNIQUE, name VARCHAR(60) NOT NULL, dob DATE NOT NULL, room_id INT NULL, -- zero or one room FOREIGN KEY (room_id) REFERENCES rooms (room_id));
INSERT INTO wardens (name, phone) VALUES ('Mr Rao', '9800011111'), ('Ms Thomas', '9800022222');INSERT INTO blocks VALUES ('A', 'Aravali', 1), ('B', 'Brahmaputra', 2);INSERT INTO rooms (block_code, room_no, floor, beds) VALUES ('A', 101, 1, 2), ('A', 102, 1, 3), ('B', 101, 1, 2);INSERT INTO residents (roll_no, name, dob, room_id) VALUES ('22CSE041', 'Nikhil Rao', '2004-08-19', 1), ('22ECE007', 'Sana Sheikh', '2005-01-02', 3), ('23ME112', 'Varun Pillai', '2006-03-30', 1), ('24IT019', 'Ritu Jain', '2006-11-11', NULL);
SELECT r.roll_no, r.name, rm.block_code, rm.room_no, w.name AS wardenFROM residents rLEFT JOIN rooms rm ON rm.room_id = r.room_idLEFT JOIN blocks b ON b.block_code = rm.block_codeLEFT JOIN wardens w ON w.warden_id = b.warden_idORDER BY r.roll_no;
-- Every line of the diagram is a foreign keySELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAMEFROM information_schema.KEY_COLUMN_USAGEWHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('blocks', 'rooms', 'residents') AND REFERENCED_TABLE_NAME IS NOT NULLORDER BY TABLE_NAME;+----------+--------------+------------+---------+-----------+ | roll_no | name | block_code | room_no | warden | +----------+--------------+------------+---------+-----------+ | 22CSE041 | Nikhil Rao | A | 101 | Mr Rao | | 22ECE007 | Sana Sheikh | B | 101 | Ms Thomas | | 23ME112 | Varun Pillai | A | 101 | Mr Rao | | 24IT019 | Ritu Jain | NULL | NULL | NULL | +----------+--------------+------------+---------+-----------+ +------------+-------------+-----------------------+------------------------+ | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +------------+-------------+-----------------------+------------------------+ | blocks | warden_id | wardens | warden_id | | residents | room_id | rooms | room_id | | rooms | block_code | blocks | block_code | +------------+-------------+-----------------------+------------------------+
What this code does
- Each box became a table and each line became a foreign key. The marks became rules:
NOT NULLfor "exactly one", a nullable column for "zero or one", andUNIQUEonwarden_idso one warden cannot get two blocks. - The
LEFT JOINs keep Ritu, who has no room yet, so the "zero or one room" in the diagram shows up asNULLvalues in the result. - The last query asks MySQL's own catalogue (
information_schema) for the foreign keys. It lists three, the same three lines as the diagram.
The common style is crow's foot notation, named after the three-pronged "many" mark that looks like a bird's foot. This course draws it in plain text, so you can copy it into notes or a README: || means exactly one, o| zero or one, |{ one or many and o{ zero or many. Inside a box, PK marks the primary key, FK a foreign key and UQ a unique column.
On paper, draw the boxes first, then the lines, then the marks. In MySQL Workbench (the free desktop tool from the MySQL team) there are two ways. For new designs, choose File → New Model, double-click "Add Diagram" and place tables on the canvas. For tables that already exist, choose Database → Reverse Engineer, pick your database, and Workbench draws the boxes and lines from the real foreign keys.
UQ is a UNIQUE constraint.Reading an ER diagram
An optional relationship allows zero rows on that side (the o mark in crow's foot); a required one needs at least one (the | mark). In SQL, required becomes NOT NULL and optional becomes a nullable foreign key.
Every line of an ER diagram says two sentences, one in each direction. The ShopLite diagram below is read line by line, then checked against the real data.
Example 5 · Reading the ShopLite ER diagram
+------------------+ +------------------+ +------------------+
| customers | | orders | | order_items |
|------------------| |------------------| |------------------|
| PK customer_id | ||------o{ | PK order_id | ||------|{ | PK,FK order_id |
| name | | FK customer_id | | PK,FK product_id |
| email (UQ) | | order_date | | quantity |
| phone | | status | | unit_price |
| city | +------------------+ +------------------+
| joined_on | o{
+------------------+ |
||
+------------------+ +------------------+
| categories | o|--------------------------------------o{ | products |
|------------------| |------------------|
| PK category_id | | PK product_id |
| name (UQ) | o|--+ parent | name |
| FK parent_id | o{--+ children | FK category_id |
+------------------+ | price |
| stock |
+------------------+One customer places zero or many orders; each order belongs to exactly one customer. One order has one or many order items; each item belongs to exactly one order. One product appears in zero or many order items; each item is for exactly one product. One category holds zero or many products; a product has zero or one category. One category is the parent of zero or many categories; a category has zero or one parent.
What this code does
- This is the practice database you have queried all along.
order_itemshas a two-column primary key, and each part is also a foreign key (PK,FK). categorieslinks to itself throughparent_id: Mobiles, Laptops and Accessories have Electronics as their parent. A line from a box back to the same box is normal.
Example 6 · Checking the diagram against the data
-- "One customer places zero or many orders": some customers have 0SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders_placedFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.nameORDER BY c.customer_id;
-- "A product has zero or one category": the gift card has noneSELECT product_id, name, category_idFROM productsWHERE category_id IS NULL;+-------------+--------------+---------------+ | customer_id | name | orders_placed | +-------------+--------------+---------------+ | 1 | Asha Rao | 3 | | 2 | Ravi Kumar | 3 | | 3 | Meena Iyer | 1 | | 4 | Kabir Singh | 2 | | 5 | Priya Sharma | 2 | | 6 | Arjun Mehta | 1 | | 7 | Divya Nair | 1 | | 8 | Rahul Verma | 1 | | 9 | Sneha Patil | 1 | | 10 | Imran Khan | 0 | +-------------+--------------+---------------+ +------------+-------------------+-------------+ | product_id | name | category_id | +------------+-------------------+-------------+ | 15 | Gift Card Rs. 500 | NULL | +------------+-------------------+-------------+
What this code does
- The
o{at the orders end allows zero, and the data really has such a customer: Imran Khan has placed no orders yet. A design that said "one or many" (|{) here would be wrong for this store. - The
o|next to categories on the products line allows a product with no category, which is whycategory_idis nullable and the gift card hasNULLthere.
For blocks ||----o{ rooms: "One block has zero or many rooms" (the o{ touches rooms) and "Each room belongs to exactly one block" (the || touches blocks). The o means optional: zero is allowed. A | in its place means required: at least one.
The marks then turn into SQL rules. "Exactly one block" means block_code in rooms is NOT NULL with a foreign key. "Zero or one room" for a resident means room_id is a nullable foreign key: a new resident may not have a room yet. Interviewers often hand over a small diagram and ask exactly these sentences.
Next step
Check your understanding
12 quick questions