ER model: entities, attributes and key attributes

An entity is a thing a database must record and tell apart from every other thing of its kind: a resident of a hostel, a room, a booking. Things of one kind form an entity set, and the facts kept about each of them are its attributes, such as a resident’s name and date of birth. One attribute, or a few together, must pick out exactly one entity of the set; that is a key attribute, such as a roll number. The entity-relationship (ER) model, which Peter Chen proposed in his 1976 paper, describes data in these terms before any table exists, so a wrong decision costs a redrawn box rather than a data migration. This lesson reads a short brief, finds its entities and attributes, sorts the attributes into composite, multivalued and derived ones, picks the keys, and draws the result in the three notations you will meet in books, exams and design tools: Chen, crow’s foot and UML.

Entities and entity sets

Chen’s paper treats an entity as anything that can be distinctly identified: a particular person, company or event. Entities are grouped into entity sets such as EMPLOYEE or PROJECT, and every entity set comes with a test that says whether a given thing belongs to it. The paper also allows entity sets to overlap: a male person belongs both to MALE-PERSON and to PERSON, an idea the module returns to with specialisation.

Everyday usage is looser. People say “the Resident entity” when they mean the entity set, and the word “entity type” appears in many books for the same idea. What matters is the difference between the two levels: a box labelled RESIDENT stands for the set, and each resident, which becomes one row once the design is turned into tables, is an entity.

To find the entity sets in a written requirement, ask three questions of every noun in it:

1. Does the brief ask you to record facts about it? A hostel has a name, a warden and facilities, so it is a candidate. A city in a home address has nothing recorded about it but its name, so it stays a value.
2. Must its instances be told apart? Each room is a separate thing with its own beds; two rooms with the same number in different hostels are still two rooms.
3. Does it take part in relationships? Residents are allotted to rooms, and rooms belong to hostels.

A noun that answers no to all three is an attribute value. Nouns that name the system itself, such as “the office” or “the college”, are usually its context rather than entities.

The hostel brief

The rest of this lesson, and its quiz, work from this brief:

The three questions give three entity sets: HOSTEL, ROOM and RESIDENT. The allotment connects a resident to a room and carries a date, which makes it a relationship, the subject of the next lesson. The warden is a value here, because the brief records only a name; if it also asked for each warden’s phone number and duty hours, the warden would become an entity of its own. That is the general rule: something becomes an entity when the design needs to say more about it than one value.

Attributes, domains and NULL

An attribute gives each entity a value from a domain, the set of values it may take. Chen defined an attribute as a function from an entity set into a value set, or into a combination of value sets: in his example, NAME maps each person to a pair of values, one from FIRST-NAME and one from LAST-NAME. The domains you choose here become column types and CHECK constraints later. A floor might be a whole number from 0 to 20, a roll number text in a fixed pattern such as H24-017.

Sometimes an entity has no value for an attribute, for one of two different reasons:

• not applicable: a resident with one phone has no second phone number;
• unknown: a resident has a postcode, but it was left blank on the form.

SQL stores both as NULL and cannot tell them apart. When the difference matters, the design has to show it another way, and the multivalued phone attribute below is an example: once phone numbers sit in their own table, “no second phone” is simply a missing row.

Simple or composite, single- or multivalued, stored or derived

Every attribute answers three separate questions, and each answer changes how it is drawn and how it becomes columns.

Composite or simple? A composite attribute has parts that are used on their own. The brief needs the resident’s name in two parts, since lists are sorted by last name and letters greet the first name, and the address in three, since reports count residents per city. An attribute is composite only when the requirement uses its parts: a phone number has a country code inside it, yet it stays simple until something needs the code separately.

Single-valued or multivalued? A multivalued attribute holds several values for one entity at the same time, like a resident’s phone numbers or a hostel’s facilities. A value that changes over time is a different matter: a room’s number of beds can change, but at any moment there is one, so it is single-valued, and keeping its old values is a question of history, which the case study at the end of this module takes up.

Stored or derived? A derived attribute can be computed from other data. The full name comes from the first and last names, the age at allotment from the date of birth and the allotment date, and the free beds of a room from its beds and the residents allotted to it. Designs compute derived values instead of storing them, because a stored copy goes stale the moment one of its inputs changes. Store one only for a reason you can name: the computation is too slow to repeat, or the value records a moment that its inputs can no longer reproduce, such as the price charged on an old invoice after the list price has changed.

The questions really are independent. A list of addresses would be both composite and multivalued, and an age is simple, single-valued and derived.

Key attributes

A key attribute has a value that no two entities of the set may share, so it identifies one entity. Chen notation underlines it. Some entity sets have several candidates: roll number and email address are both unique for every resident, and the design picks one as the primary key and keeps the other as an alternate key. Some keys need several attributes together. A room number repeats across hostels, so a room is identified by its hostel’s name and its room number together; the third lesson of this module explains why a key that is unique only within its owner is called a partial key.

A key is a rule about every entity the set could ever hold, not an observation about today’s data. Data can show that something is not a key, never that it is one:

-- Which attributes of these residents are unique right now?
CREATE TABLE resident (
  roll_no    TEXT PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  birth_date TEXT NOT NULL
);
INSERT INTO resident VALUES
  ('H24-017', 'Meera', 'Iyer',  'meera.iyer@college.example',  '2006-09-14'),
  ('H24-031', 'Arjun', 'Bose',  'arjun.bose@college.example',  '2007-01-30'),
  ('H24-044', 'Tara',  'Iyer',  'tara.iyer@college.example',   '2006-09-14'),
  ('H24-052', 'Kabir', 'Sethi', 'kabir.sethi@college.example', '2005-12-02');

SELECT count(*)                                   AS residents,
       count(DISTINCT roll_no)                    AS roll_numbers,
       count(DISTINCT email)                      AS emails,
       count(DISTINCT first_name || ' ' || last_name) AS full_names,
       count(DISTINCT birth_date)                 AS birth_dates
FROM resident;

-- The full names happen to be unique today. A new resident shows they were never a key.
INSERT INTO resident VALUES ('H25-003', 'Meera', 'Iyer', 'meera.iyer2@college.example', '2007-03-21');
SELECT count(*) AS residents, count(DISTINCT first_name || ' ' || last_name) AS full_names FROM resident;

-- The declared keys refuse a second row with the same value.
INSERT INTO resident VALUES ('H25-003', 'Ravi', 'Nair', 'ravi.nair@college.example', '2006-05-05');
INSERT INTO resident VALUES ('H25-009', 'Ravi', 'Nair', 'arjun.bose@college.example', '2006-05-05');

┌───────────┬──────────────┬────────┬────────────┬─────────────┐
│ residents │ roll_numbers │ emails │ full_names │ birth_dates │
├───────────┼──────────────┼────────┼────────────┼─────────────┤
│ 4         │ 4            │ 4      │ 4          │ 3           │
└───────────┴──────────────┴────────┴────────────┴─────────────┘
┌───────────┬────────────┐
│ residents │ full_names │
├───────────┼────────────┤
│ 5         │ 4          │
└───────────┴────────────┘

Runtime error near line 27: UNIQUE constraint failed: resident.roll_no
Runtime error near line 28: UNIQUE constraint failed: resident.email

On the first four rows, the full names were as unique as the roll numbers. A fifth resident with a common name broke that, while the declared keys kept working: the database refused a second resident with an existing roll number and another with an existing email address. The birth dates were never candidates; two residents already shared one.

Two kinds of attribute make poor keys, for reasons worth stating:

• Derived attributes change when their inputs change. An age goes up every year, and a key that changes forces a change in every row that refers to it.
• Multivalued attributes have no single value per entity. A resident with two phone numbers has no one phone number to identify them by, and a number can pass from one person to another.

An attribute that can be NULL makes no primary key either: an entity without a value would have nothing to be identified by.

Derived attributes in SQL

When the design becomes tables, a derived attribute is computed in one of three places: in the query that needs it, in a view, or in a generated column, a column whose value the database computes from the other columns of the same row. SQLite has generated columns of two kinds: a VIRTUAL one, the default, is computed whenever it is read, and a STORED one is computed when the row is written and kept in the file.

-- A resident of the hostel brief: the key, the two parts of the composite name, and two derived values.
CREATE TABLE resident (
  roll_no    TEXT PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  birth_date TEXT NOT NULL,   -- stored: age is computed from it when a report needs it
  full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name),               -- VIRTUAL: computed on read
  sort_name  TEXT GENERATED ALWAYS AS (lower(last_name || ', ' || first_name)) STORED  -- computed on write
);

INSERT INTO resident (roll_no, first_name, last_name, birth_date)
VALUES ('H24-017', 'Meera', 'Iyer', '2006-09-14'),
       ('H24-031', 'Arjun', 'Bose', '2007-01-30');

SELECT roll_no, full_name, sort_name FROM resident ORDER BY sort_name;

-- The catalogue tells the kinds apart: hidden is 2 for a VIRTUAL column and 3 for a STORED one.
SELECT name, hidden FROM pragma_table_xinfo('resident');

-- A derived value is computed, never written.
UPDATE resident SET full_name = 'M. Iyer' WHERE roll_no = 'H24-017';

┌─────────┬────────────┬─────────────┐
│ roll_no │ full_name  │  sort_name  │
├─────────┼────────────┼─────────────┤
│ H24-031 │ Arjun Bose │ bose, arjun │
│ H24-017 │ Meera Iyer │ iyer, meera │
└─────────┴────────────┴─────────────┘
┌────────────┬────────┐
│    name    │ hidden │
├────────────┼────────┤
│ roll_no    │ 0      │
│ first_name │ 0      │
│ last_name  │ 0      │
│ birth_date │ 0      │
│ full_name  │ 2      │
│ sort_name  │ 3      │
└────────────┴────────┘

Parse error near line 21: cannot UPDATE generated column "full_name"

pragma_table_xinfo shows what the plain pragma_table_info leaves out: its hidden column is 2 for the virtual full_name and 3 for the stored sort_name. The last statement shows that a derived value cannot be written directly; the database refused the change, and full_name changes only when first_name or last_name does.

Why age cannot be a generated column

A generated column may use only its own row and functions that always give the same result for the same input. Today’s date is neither, so an age computed from the current date breaks the rule:

-- Age is derived from birth_date and today's date. Can it be a generated column?
CREATE TABLE resident (
  roll_no    TEXT PRIMARY KEY,
  birth_date TEXT NOT NULL,
  age        INTEGER GENERATED ALWAYS AS (
               CAST(strftime('%Y', 'now') AS INTEGER) - CAST(strftime('%Y', birth_date) AS INTEGER)
             )
);
SELECT 'the table was created' AS step;

INSERT INTO resident (roll_no, birth_date) VALUES ('H24-017', '2006-09-14');
SELECT count(*) AS rows_stored FROM resident;

┌───────────────────────┐
│         step          │
├───────────────────────┤
│ the table was created │
└───────────────────────┘
┌─────────────┐
│ rows_stored │
├─────────────┤
│ 0           │
└─────────────┘

Runtime error near line 11: non-deterministic use of strftime() in a generated column

SQLite accepted the definition and refused the first row. Its date and time functions count as deterministic until they are given 'now', and in a generated column they raise an error when that happens, so the problem appears at the first INSERT rather than at CREATE TABLE. Compute the age in the query instead, from birth_date and the date it is needed for, which is what the exercise below asks you to do.

Derived from other rows: a view

The free beds of a room depend on rows of another table, the residents allotted to the room, and a generated column may not read other rows at all:

-- Free beds depend on other rows (the residents allotted to the room), not on the room's own row.
CREATE TABLE room (
  hostel  TEXT NOT NULL,
  room_no TEXT NOT NULL,
  beds    INTEGER NOT NULL CHECK (beds > 0),
  PRIMARY KEY (hostel, room_no)
);
CREATE TABLE allotment (
  roll_no TEXT PRIMARY KEY,
  hostel  TEXT NOT NULL,
  room_no TEXT NOT NULL,
  FOREIGN KEY (hostel, room_no) REFERENCES room (hostel, room_no)
);
INSERT INTO room VALUES ('Kaveri', '101', 3), ('Kaveri', '102', 2), ('Ganga', '101', 4);
INSERT INTO allotment VALUES ('H24-017', 'Kaveri', '101'), ('H24-031', 'Kaveri', '101'), ('H24-044', 'Ganga', '101');

-- A generated column may only read its own row: this one is refused.
ALTER TABLE room ADD COLUMN free_beds INTEGER GENERATED ALWAYS AS (
  beds - (SELECT count(*) FROM allotment AS a WHERE a.hostel = hostel AND a.room_no = room_no)
);

-- A view computes the derived attribute from both tables every time it is read.
CREATE VIEW room_free_beds AS
SELECT r.hostel, r.room_no, r.beds, r.beds - count(a.roll_no) AS free_beds
FROM room AS r
LEFT JOIN allotment AS a ON a.hostel = r.hostel AND a.room_no = r.room_no
GROUP BY r.hostel, r.room_no, r.beds;

SELECT * FROM room_free_beds ORDER BY hostel, room_no;

┌────────┬─────────┬──────┬───────────┐
│ hostel │ room_no │ beds │ free_beds │
├────────┼─────────┼──────┼───────────┤
│ Ganga  │ 101     │ 4    │ 3         │
│ Kaveri │ 101     │ 3    │ 1         │
│ Kaveri │ 102     │ 2    │ 2         │
└────────┴─────────┴──────┴───────────┘

Runtime error near line 18: error in table room after add column: subqueries prohibited in generated columns

SQLite refused the column (“subqueries prohibited in generated columns”), and the view computed the same value from both tables each time it was read. Room 102 of Kaveri, with no residents, still appears, because the view joins with LEFT JOIN and counts only matching residents.

One design, three notations

ER diagrams are drawn in several notations. They change the picture, not the meaning, and every exam question or design document should say which one it uses.

Chen notation, named after the model’s author, draws an entity set as a rectangle and each attribute as an oval joined to it by a line. In the form most books use, the key attribute is underlined, a composite attribute has the ovals of its parts hanging from it, a multivalued attribute has a double outline and a derived attribute a dashed one.

Crow’s foot diagrams draw each entity as a box listing its attributes, usually one per line with the key marked, and draw relationships as lines whose ends show how many entities take part: a bar for one, a ring for zero, a three-pronged “crow’s foot” for many. They are drawn close to the tables, so a multivalued attribute becomes an entity of its own joined by a one-to-many line, and derived attributes are left out or marked as computed.

UML class diagrams describe the same entity set as a class with properties. A slash before a name marks a derived property, a multiplicity in brackets says how many values a property has ([1..*] is one or more), and {id} marks the properties that identify an object.

The three drawings carry the same facts: roll number as the key, a two-part name, one or more phone numbers, a stored date of birth and an age derived from it. Crow’s foot already shows the phone numbers as the table they will become; the lesson on mapping ER diagrams to tables does that step for every kind of attribute.

Interview questions

What is the difference between a composite, a multivalued and a derived attribute? A composite attribute is made of parts that are useful on their own, such as a name made of a first and a last name. A multivalued attribute holds several values for one entity at the same time, such as a resident’s phone numbers. A derived attribute is computed from other data instead of being recorded, such as an age computed from a date of birth. The three are independent: a list of addresses would be both composite and multivalued. In tables, a composite attribute becomes one column per part, a multivalued one gets a table of its own, and a derived one is computed in a query, a view or a generated column.

Key takeaways

• An entity is one thing a database tells apart from others of its kind; an entity set is all of them; attributes are the facts recorded about each.
• A noun in a brief becomes an entity when the design records more about it than one value; otherwise it is an attribute value.
• Ask three separate questions of every attribute: composite or simple, single- or multivalued, stored or derived.
• A key must hold for every entity the set could ever contain: data can disprove a key but never prove one. Derived, multivalued and nullable attributes make poor keys.
• Compute derived values in a query, a view or a generated column; a generated column may read only its own row and deterministic functions, so an age based on today’s date and a count over other rows both need another place.
• Chen, crow’s foot and UML draw the same design differently; always say which notation you use.