ER Model: Entities & Attributes

By the end of this lesson you will be able to find the entities in a problem statement, list their attributes, classify each attribute (simple, composite, single-valued, multivalued, stored, derived, key) and draw them in an ER diagram. This is the first step of designing any database.

Why draw before you build

The ER model (Entity-Relationship model) is a way to describe a database as a picture before you create any tables. Peter Chen introduced it in 1976. An architect draws a house plan before laying a single brick; a database designer draws an ER diagram before writing a single CREATE TABLE. Mistakes on paper cost nothing to fix.

An ER diagram has three building blocks:

1. Entities: the things you store data about (a student, a course).
2. Attributes: the facts about each thing (name, roll number).
3. Relationships: how things are connected (a student enrols in a course). These come in the next lesson, Relationships, Cardinality & Participation.

Entities and entity sets

An entity is a real-world object or concept that can be told apart from others. Priya, roll number 2403, is an entity. The DBMS course is an entity. A hostel room is an entity.

An entity type is the category, such as STUDENT. An entity set is the collection of all entities of that type stored at one moment, such as all 1,200 students of the college. In the final database, an entity type usually becomes a table and each entity becomes a row.

How do you find entities in a problem statement? Look for important nouns that have their own facts. Read this requirement:

"A college in Pune has departments. Each student belongs to a department and has a roll number, a name, a date of birth, an address and one or more phone numbers. Courses have a code, a title and credits."

The nouns college, department, student and course are candidates. The college is just one thing (the whole database is about it), so it is not an entity type. That leaves DEPARTMENT, STUDENT and COURSE. Words like name and credits are facts about those things, so they are attributes.

Kinds of attributes

An attribute is a property that describes an entity. Each attribute has a domain: the set of values it may take, such as whole numbers from 1 to 6 for credits.

Two more ideas complete the picture. A NULL value means "unknown" or "not applicable", such as a missing email. A complex attribute combines composite and multivalued, such as several addresses, each with parts.

Drawing it: ER notation

In Chen notation, an entity is a rectangle, an attribute is an ellipse, a key attribute is underlined, a multivalued attribute is a double ellipse and a derived attribute is a dashed ellipse. Because this course draws diagrams with plain characters, we use this legend:

Here is the STUDENT entity type from the requirement:

          (first_name)   (last_name)
                 \         /
     (#roll_no)   (name)          ((phone))
             \      |             /
              +-----------+------+
              |  STUDENT  |
              +-----------+------+
             /      |             \
(date_of_birth)  (~age)         (address)
                                /   |    \
                        (street) (city) (pincode)

You should be able to read it aloud: a student has a key roll_no, a composite name, one or more phone numbers, a stored date of birth, a derived age, and a composite address.

From attributes to columns

You will learn the full mapping rules in Converting an ER Diagram to Tables, but a preview helps each kind of attribute make sense.

Step 1: composite attributes become several columns, and the key becomes the primary key. The multivalued phone attribute cannot fit in one column, so it gets its own table.

CREATE TABLE students (
  roll_no INTEGER PRIMARY KEY,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  date_of_birth DATE NOT NULL,
  street VARCHAR(60),
  city VARCHAR(30),
  pincode VARCHAR(6)
);

CREATE TABLE student_phones (
  roll_no INTEGER,
  phone VARCHAR(15),
  PRIMARY KEY (roll_no, phone)
);

INSERT INTO students VALUES
  (2403, 'Priya', 'Deshmukh', '2006-08-14', 'FC Road', 'Pune', '411004'),
  (2404, 'Rohan', 'Kulkarni', '2005-12-02', 'MG Road', 'Pune', '411001');
INSERT INTO student_phones VALUES
  (2403, '9822000001'), (2403, '9822000002'), (2404, '9822000003');

SELECT s.roll_no, s.first_name, COUNT(p.phone) AS phones
FROM students s
LEFT JOIN student_phones p ON p.roll_no = s.roll_no
GROUP BY s.roll_no, s.first_name
ORDER BY s.roll_no;

roll_no | first_name | phones
--------+------------+-------
2403    | Priya      | 2
2404    | Rohan      | 1
(2 rows)

You should see that Priya has two phone numbers and Rohan has one. Each number is a separate row, which keeps every column atomic.

Step 2: derived attributes are usually not stored. They are calculated when needed, so they can never go out of date. In the practice database, an order's total value is derived from its items:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
WHERE order_id IN (101, 103, 106)
GROUP BY order_id
ORDER BY order_id;

order_id | order_total
---------+------------
101      | 2147
103      | 5398
106      | 3948
(3 rows)

If the shop stored order_total as a column, someone could change an item and forget to update the total. Computing it avoids that inconsistency. Age works the same way: store the date of birth, calculate the age.

Advanced: choosing between an attribute and an entity

Sometimes the same fact can be an attribute or an entity. Should department be an attribute of STUDENT, or an entity of its own?

Ask two questions:

1. Does it have facts of its own? A department has a name, a building and a head. Yes.
2. Is it shared by many other entities? Many students and many courses belong to one department. Yes.

If either answer is yes, make it an entity and connect it with a relationship. If it is just a label with no facts of its own, such as a blood group, keep it as an attribute.

Common mistakes

• Making the whole system an entity. "College" or "Shop" is usually the database itself, not an entity type.
• Storing derived values without a reason. Store date_of_birth, not age. Age is wrong a year later.
• Putting many values in one column. '9822000001, 9822000002' in a phone column breaks searching and updating. A multivalued attribute needs its own table.
• Choosing a name as the key. Two students can share a name. Pick something that is guaranteed unique, such as a roll number.

Key points

• The ER model describes data as entities, attributes and relationships before tables exist.
• An entity is one real-world thing; an entity set is all entities of one type.
• Attributes can be simple or composite, single-valued or multivalued, stored or derived.
• A key attribute uniquely identifies each entity and is underlined in Chen notation.
• Composite attributes become several columns; multivalued attributes become a separate table.
• Derived attributes are normally calculated by queries rather than stored.

CREATE TABLE books (
  isbn VARCHAR(13) PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  publisher VARCHAR(60),
  publication_year INTEGER,
  copies INTEGER NOT NULL
);

CREATE TABLE book_authors (
  isbn VARCHAR(13),
  author_name VARCHAR(60),
  PRIMARY KEY (isbn, author_name)
);