Entities, attributes and relationships

Part of the free LogicWiz SQL & Databases course, Module 9: ER modelling. The queries and practice run in the app on real Postgres.

Aarav and Meher must build the database for their college app. Before a single CREATE TABLE, their senior hands them a marker: "Draw it first." What exactly do they draw?

You'll learn to

Tell entities, attributes and relationships apart
Spot simple, composite, multivalued, derived and key attributes
Recognise a weak entity and find its key
Turn each kind of attribute into the right columns or tables

Learn

Three building blocks

An ER diagram (Entity-Relationship diagram) is a drawing of your data before it becomes tables.

An entity is a thing you keep facts about: a student, a course, a hostel. All students together are the entity set Student.
An attribute is one fact about it: name, date of birth, phone.
A relationship links entities: a student enrols in a course, a room belongs to a hostel.

Nouns in the problem statement usually become entities, and verbs become relationships.

Reading an ER diagram (Chen notation)

Shape	Means	Example
Rectangle	Strong entity	Student
Double rectangle	Weak entity	Room (of a hostel)
Ellipse	Attribute	city
Ellipse, name underlined	Key attribute	studentid, drawn underlined
Ellipse, name with a dashed underline	Partial key of a weak entity	roomno
Double ellipse	Multivalued attribute	phones
Dashed ellipse	Derived attribute	age
Ellipse with smaller ellipses hanging off it	Composite attribute	name → first, last
Diamond	Relationship	enrols in
Double diamond	Identifying relationship (weak entity to its owner)	has room
Double line	Total participation	every room is in a hostel

Five kinds of attribute, and where each one goes

Kind	In Student	Becomes
Simple	dob, city	one column
Composite (made of parts)	name = first + last	one column per part: firstname, lastname
Multivalued (many values)	phones	its own table: studentphones(studentid, phone)
Derived (can be calculated)	age from dob	usually not stored: compute it in the query
Key (unique per entity)	studentid	the PRIMARY KEY

The Student entity, as tables

CREATE TABLE students (
  student_id  INT PRIMARY KEY,
  first_name  VARCHAR(30) NOT NULL,
  last_name   VARCHAR(30) NOT NULL,
  dob         DATE NOT NULL,
  city        VARCHAR(30)
);
CREATE TABLE student_phones (
  student_id  INT REFERENCES students(student_id),
  phone       VARCHAR(15),
  PRIMARY KEY (student_id, phone)
);
INSERT INTO students VALUES
  (1, 'Aarav', 'Sharma', '2005-03-14', 'Delhi'),
  (2, 'Meher', 'Kaur', '2004-11-02', 'Amritsar'),
  (3, 'Kabir', 'Khan', '2008-01-20', 'Lucknow');
INSERT INTO student_phones VALUES
  (1, '9876543210'), (1, '9123456780'), (2, '9988776655');

SELECT s.first_name || ' ' || s.last_name AS name,
       EXTRACT(YEAR FROM AGE(DATE '2025-08-01', s.dob)) AS age,
       p.phone
FROM students s
LEFT JOIN student_phones p ON p.student_id = s.student_id
ORDER BY s.student_id, p.phone;

The composite name became two columns, the multivalued phones became a second table (Aarav has two rows there, Kabir none), and the derived age is calculated as of 1 Aug 2025 instead of stored, so it never goes stale.

Weak entities: a room needs its hostel

"Room 101" means nothing alone: Ganga hostel has a Room 101 and so does Kaveri. A weak entity cannot be identified by its own attributes; it needs an owner (identifying) entity.

room_no is only a partial key (also called a discriminator): unique inside one hostel.
The full key is owner key + partial key: (hostel_id, room_no).
A weak entity always has total participation in its identifying relationship: no room exists without a hostel.

Other examples: an order line inside an order, an employee's dependents, an EMI instalment of a loan.

A weak entity as a table

CREATE TABLE hostels (
  hostel_id  INT PRIMARY KEY,
  name       VARCHAR(30) NOT NULL
);
CREATE TABLE rooms (
  hostel_id  INT REFERENCES hostels(hostel_id) ON DELETE CASCADE,
  room_no    INT,
  beds       INT NOT NULL,
  PRIMARY KEY (hostel_id, room_no)
);
INSERT INTO hostels VALUES (1, 'Ganga'), (2, 'Kaveri');
INSERT INTO rooms VALUES (1, 101, 2), (1, 102, 3), (2, 101, 2);

SELECT h.name AS hostel, r.room_no, r.beds
FROM rooms r
JOIN hostels h ON h.hostel_id = r.hostel_id
ORDER BY h.name, r.room_no;

Two rooms numbered 101 live happily side by side, because the key is the pair. ON DELETE CASCADE says it in SQL: if the hostel goes, its rooms go too. Try adding INSERT INTO rooms VALUES (1, 101, 4); at the end and run it.

The classic mistake: storing a multivalued attribute as one text cell, like phones = '9876543210, 9123456780'. Now WHERE phones = '9123456780' finds nothing, you cannot put a UNIQUE or a foreign key on one phone, and updating one number means editing a string. One value per cell; many values get their own table.

Exams love: "Identify the attribute types in this diagram" and "What is the primary key of the weak entity's table?" Answer the second as primary key of the owner + partial key, and say that the owner's key is also a foreign key with ON DELETE CASCADE.

In one line

Entities become tables, composite attributes become columns, multivalued ones get their own table, derived ones are computed, and a weak entity is keyed by owner key + partial key.

Interview corner

What is a weak entity? Give an example.

An entity that cannot be identified by its own attributes and depends on an owner entity, like a room in a hostel or a dependent of an employee. It has a partial key (room_no) that is unique only within one owner. Its table's primary key is the owner's key plus the partial key, with a foreign key to the owner and usually ON DELETE CASCADE.

How do you store a multivalued attribute such as phone numbers?

In a separate table with the owner's key and the value, for example student_phones(student_id, phone) with PRIMARY KEY (student_id, phone). Never as a comma-separated string, because you can't search, index or constrain one value inside it.

Should you store a derived attribute like age or order total?

Usually not: compute it from the base data (AGE(dob), SUM(qty * price)) so it can't go stale. Store it only as a deliberate optimisation, such as a cached total on a hot read path, and keep it in sync.