The ER Model

The Entity–Relationship (ER) model is used to design a database before any table is created. It describes the things the database is about (entities), their properties (attributes) and how they are connected (relationships).

Entities and attributes

An entity is one real-world thing, such as the student Asha. An entity set (or entity type) is the collection of similar entities: all students. In an ER diagram an entity set is drawn as a rectangle.

An attribute is a property of an entity (drawn as an oval). Attributes come in several kinds:

Relationships

A relationship connects entities, such as "Asha enrols in DBMS". A relationship set (a diamond in the diagram) is the collection of similar relationships. A relationship can have its own attributes: the grade belongs to the enrolment, not to the student or the course alone.

The degree of a relationship is the number of entity sets it connects: binary (2, the most common), ternary (3) or unary/recursive (an entity set related to itself, such as employee manages employee).

Cardinality and participation

The cardinality ratio of a binary relationship says how many entities on one side can be related to one entity on the other:

Participation says whether every entity must take part:

• Total participation (double line): every entity is in at least one relationship. Every employee must work in some department.
• Partial participation (single line): some entities may take part in none. Not every employee manages a department.

Weak entities

A weak entity set has no key of its own; its entities are identified only together with an owner (identifying) entity. Example: Dependent(name, age) of an employee. Two employees can each have a dependent named "Riya", so name alone is not a key. A weak entity has a partial key (discriminator, dashed underline), and its full key is owner's key + partial key: (emp_id, dependent_name). A weak entity always has total participation in its identifying relationship (drawn with double rectangle and double diamond).

Specialization and generalization

• Specialization (top-down): split an entity set into subclasses, such as Employee into Engineer and Manager, each with extra attributes.
• Generalization (bottom-up): combine similar entity sets, such as Car and Truck, into a superclass Vehicle.
• Subclasses inherit the attributes of the superclass. A specialization can be disjoint or overlapping, and total or partial.

From ER diagram to tables

The mapping rules are a GATE favourite, especially "what is the minimum number of tables needed?":

Here is the student–course M:N relationship mapped to three tables. The enrols table holds one row per relationship, with both keys and the relationship's own attribute.

CREATE TABLE student (roll INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE course (code TEXT PRIMARY KEY, title TEXT);
CREATE TABLE enrols (
  roll  INTEGER REFERENCES student(roll),
  code  TEXT REFERENCES course(code),
  grade TEXT,
  PRIMARY KEY (roll, code)
);
INSERT INTO student VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO course VALUES ('CS301', 'DBMS'), ('CS302', 'OS');
INSERT INTO enrols VALUES (1, 'CS301', 'A'), (1, 'CS302', 'B'), (2, 'CS301', 'A');
SELECT s.name, c.title, e.grade
FROM enrols e JOIN student s ON s.roll = e.roll JOIN course c ON c.code = e.code;

name  title  grade
----  -----  -----
Asha  DBMS   A
Asha  OS     B
Ravi  DBMS   A

Asha appears twice and DBMS appears twice: that is exactly what "many-to-many" means, and it is why the relationship needs its own table.

Practice questions