Entities, Relationships and Tables

1Entities and attributes

The campus kiosk needs to remember people and products. An entity is a thing we store a row for: a student such as Asha, or an item such as a samosa. An attribute is a fact about that thing: Asha's hostel is A; a samosa costs Rs 20. The entity is the noun; the attributes are the columns we will later write down.

Two students can share a hostel. That does not make hostel an entity of its own in this tiny shop — it is still a fact about the student. We promote hostel to its own entity only when we start storing facts about the hostel itself (a warden, a capacity). Until then it stays a column on Student.

Figure. Two boxes: a student with a hostel, an item with a price. Those are entities; the words inside are attributes.

How to name a thing

1. Spot the nounStudent and Item are things the kiosk talks about even when no sale has happened.
2. List the factsname and hostel belong to a student; item_name and price_rs belong to an item.
3. Keep hostel a columnHostel is shared, but we have no extra hostel facts yet, so it is not its own box.

2A relationship is a fact

Asha buying two samosas is not a property of Asha alone and not a property of the samosa alone. It is a fact that links them: this student bought this item, in this quantity. That linking fact is a relationship. In the kiosk we name it Sale.

If we stuffed "2 Samosa" into a cell on Asha's student row, we would lose the item's price as a reusable fact and we could not list every sale without rewriting Asha. The relationship earns its own place because it has its own attributes — sale_id and qty — that belong to neither Student nor Item.

Figure. A sale is the fact that one student bought one item. It sits between Student and Item.

When a fact needs its own place

1. Two nounsThe fact mentions a student and an item.
2. Its own numbersqty and sale_id are not a property of Asha or of Samosa.
3. Name itCall the relationship Sale and keep it between the two entities.

3One student, many sales

Cardinality says how many of one entity can sit with how many of the other. One student may have many sales: Asha has sale 100 and sale 102. One item may appear on many sales: Tea is on sale 101 and sale 103. One sale names exactly one student and exactly one item.

That is two one-to-many links, not a many-to-many blob. Student-to-Sale is one-to-many; Item-to-Sale is one-to-many. The many-to-many "students buy items" is what you get if you hide Sale and look only at the two ends.

Figure. One Asha node fans out to two Sale nodes. That is one-to-many from student to sale.

Read the counts off the shop

1. AshaTwo sales (100, 102) — one student, many sales.
2. TeaTwo sales (101, 103) — one item, many sales.
3. Sale 100Exactly one student and one item.

4Boxes become tables

The relational model stores each entity as a table: one row per instance, one column per attribute. Student becomes a three-row table. Item becomes a three-row table. The Sale relationship becomes a table too, because it has its own attributes and it is the many side of both links.

To remember which student a sale belongs to, Sale stores student_id — a copy of Student's identifier, called a foreign key. It stores item_id the same way. Those two columns are how a relationship becomes rows you can join later. They are not a second copy of Asha's name or the samosa's price.

Figure. Three tables. Sale's middle numbers are foreign keys: who and what, not a second copy of the name or the price.

ER to the three shop tables

1. Entity boxStudent → table Student; Item → table Item.
2. Relationship with attributesSale → table Sale, because qty and sale_id live there.
3. Foreign keysSale.student_id points at Student; Sale.item_id points at Item.

The three tables as SQL

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  name TEXT,
  hostel TEXT
);
CREATE TABLE item (
  item_id INT PRIMARY KEY,
  item_name TEXT,
  price_rs INT
);
CREATE TABLE sale (
  sale_id INT PRIMARY KEY,
  student_id INT REFERENCES student(student_id),
  item_id INT REFERENCES item(item_id),
  qty INT
);