ER Model in DBMS: Entities, Relationships, ER to Tables

The entity–relationship (ER) model, introduced by Peter Chen in 1976, is how a database is designed before it is built. You describe the world the database must remember as entities (things), attributes (their properties) and relationships (how things are connected), together with rules such as "every book has exactly one publisher". The result, an ER diagram, is easy to discuss with people who will never write SQL, and it converts into tables by a short list of fixed rules. Interviewers ask both halves: draw or read a diagram, then turn it into tables.

Entities and entity sets

An entity is a real-world object that can be told apart from all others: the student with roll number 101, the member with card number 5521. An entity set is the set of all entities of the same type: all students, all books. In a diagram an entity set is a rectangle, and it usually becomes one table, with each entity a row.

A strong entity set has a key of its own. A weak entity set does not, and depends on another entity set for its identity (covered below).

Attributes

Attributes describe an entity. Each has a domain, the set of values it may take. The types interviewers ask about:

An attribute can also be null when the value is unknown or does not apply, such as a member with no email address.

Chen notation

Many tools use crow's foot notation instead, where the line ends show cardinality (a single bar for one, a three-pronged "crow's foot" for many) and a circle or bar shows optional or mandatory participation. The meaning is the same.

Relationships and their degree

A relationship associates entities: Asha borrows copy 2 of a book. A relationship set is the set of all such associations of one kind, drawn as a diamond. A relationship can have attributes of its own: issue_date belongs to borrows, not to the member or the copy.

The degree of a relationship is the number of entity sets taking part:

A ternary relationship is not the same as three binary ones. Knowing that a supplier supplies a part, that the supplier works for a project, and that the project uses the part does not tell you that this supplier supplies this part to this project.

Cardinality and participation

Mapping cardinality says how many entities a single entity can be related to through the relationship:

Participation says whether every entity must take part. Total participation (double line) means every entity takes part at least once; it is also called an existence dependency. Partial participation (single line) means some may not. Cardinality is the maximum; participation is the minimum. Some books write both as a pair (min, max), so "a book has (1, 1) publisher" means total participation and at most one.

Weak entities

A weak entity set has no attribute set that identifies its entities on its own. A library owns several copies of each book, numbered 1, 2, 3 within that book; "copy 2" is ambiguous until you say which book. So:

• The weak entity set (Copy) is identified through an identifying relationship (has) with its owner or identifying entity set (Book).
• Its own distinguishing attribute is a partial key or discriminator (copy_no).
• Its full key is the owner's key plus the partial key: (isbn, copy_no).
• It always has total participation in the identifying relationship, which is one-to-many from owner to weak entity.

If a book is removed, its copies have no meaning left, so the copies' table usually declares ON DELETE CASCADE on its foreign key to the owner.

Generalization, specialization and aggregation

The extended ER (EER) model adds three ideas for larger designs.

Specialization is top down: split an entity set into subclasses that have extra attributes or relationships. Employee specializes into Engineer (with skill) and Manager (with budget). Generalization is bottom up: combine entity sets that share attributes into a superclass, as Car and Truck generalize into Vehicle. Both produce an "is-a" hierarchy, and a subclass inherits all attributes and relationships of its superclass. Two constraints describe the hierarchy:

• Disjoint (an employee is at most one subclass) or overlapping (an employee may be both).
• Total (every employee belongs to some subclass) or partial (some belong to none).

Aggregation treats a relationship, together with its entities, as a higher-level entity so that it can take part in another relationship. Employees work on projects, and a manager monitors each employee-project assignment, not the employee or the project alone. Aggregating works on lets monitors connect Manager to the assignment.

Worked example: a library

Requirements: publishers publish books, and every book has exactly one publisher. Each book has one or more authors, and an author may write many books. The library holds numbered copies of each book. A member may have several copies on loan at once, and a copy is with at most one member at a time. For members we store a name in two parts, phone numbers (any number of them) and a date of birth; age is shown but never stored.

The entity sets and their attributes:

1. A publisher publishes many books and every book has exactly one publisher: 1 on the Publisher side, N on the Book side, and a double line because every book must take part.
2. Authors and books are many-to-many (M:N). Every book has at least one author, so the Book side is a double line; an author may have no book in the library yet, so that side is single.
3. Copy is weak: a double box, joined to its owner Book by the double diamond has, with total participation. Its partial key copy_no is dash-underlined, because copy 2 means something only for one book.
4. A member may hold several copies and a copy is with at most one member: 1:N, partial on both sides. issue_date and due_date describe the loan itself, so they hang off the diamond, not off either entity.
5. The finished diagram: five entity sets, four relationships, each key on its entity. Read every line twice, once for cardinality (1, M, N) and once for participation (double or single), before turning it into tables.

Applying the mapping rules gives seven tables:

1. Each strong entity set becomes a table keyed by its key: publisher, book, author, member. Member's composite name becomes first_name and last_name, its derived age is not stored, and its multi-valued phone waits for a table of its own.
2. publishes is 1:N, so the one side's key, publisher_id, goes into book as a foreign key, NOT NULL because every book has a publisher. writes is M:N and becomes its own table, writes, keyed by both foreign keys.
3. The weak entity Copy becomes a table keyed by its owner's isbn plus copy_no. borrows is 1:N towards Copy, so member_id and the loan's issue_date and due_date go into copy, nullable because most copies are on the shelf.
4. The multi-valued phone becomes member_phone(member_id, phone), one row per number, keyed by both. That makes 7 tables, with every relationship now a foreign key or a table.

Two honest notes on this design. First, foreign keys cannot enforce "every book has at least one author" (total participation on the M:N side); that needs application logic or a trigger. Second, because most copies sit on the shelf, member_id, issue_date and due_date in copy are usually null; a separate loan(isbn, copy_no, member_id, issue_date, due_date) table keyed by (isbn, copy_no) is an equally correct mapping that avoids the nulls.

Converting an ER diagram to tables

For Employee specialized into Engineer and Manager, the three layouts are: a table per entity set, where engineer(emp_id, skill) and manager(emp_id, budget) reference employee(emp_id, name, salary) (always works, needs joins); tables for the subclasses only, each repeating name and salary (only when the specialization is total, and best when it is disjoint); or one employee table with a type column and nullable skill and budget (no joins, many nulls).

A favourite question asks for the minimum number of tables for two strong entity sets E1 and E2 joined by one binary relationship R, with no multi-valued attributes:

Common mistakes

• Putting the foreign key of a 1:N relationship on the "one" side: it goes on the "many" side, or one row would need many values.
• Storing a multi-valued attribute as a comma-separated column: it breaks first normal form; give it its own table.
• Giving a weak entity's table only its partial key as the primary key: copy_no alone repeats for every book.
• Storing derived attributes such as age: they go stale; compute them.
• Reading cardinality as participation: 1:N limits the maximum; whether every entity must take part is a separate rule.
• Replacing a ternary relationship with three binary ones: the combined fact is lost.

Interview questions

What is an ER diagram used for? It is a conceptual design of the database, made before tables exist. It captures entities, attributes, relationships and constraints in a form non-programmers can check, and it maps to a relational schema by fixed rules.

What are the types of attributes? Simple and composite, single-valued and multi-valued, stored and derived, plus key attributes. A multi-valued attribute becomes a separate table, a composite one is flattened into its parts, and a derived one is not stored.

What is a weak entity? Give an example. An entity set without a key of its own, identified through its owner by an identifying relationship. A copy of a book, a dependent of an employee, or an instalment of a loan; the key is the owner's key plus the partial key.

What is the difference between total and partial participation? Total means every entity must take part in the relationship (double line), partial means it need not (single line). Total participation on the N side of a 1:N relationship becomes a NOT NULL foreign key.

How do you convert a 1:N and an M:N relationship into tables? For 1:N, add the "one" side's key as a foreign key on the "many" side. For M:N, create a new table with both keys as foreign keys, together forming its primary key, plus any relationship attributes.

What is aggregation in the ER model? Treating a relationship and its entities as one higher-level entity so it can take part in another relationship. It models facts about an association itself, such as a manager monitoring an employee's work on a project.

What is a recursive relationship? A relationship between an entity set and itself, such as Employee manages Employee. The two ends get role names, and in tables it becomes a foreign key referencing the same table, such as manager_id referencing emp_id.

Can a relationship have attributes? Yes. An attribute that describes the association rather than either entity belongs to the relationship, such as the issue date of a loan or the grade in an enrolment. For an M:N relationship it becomes a column of the relationship's table.

Next, the keys that make those tables work: Keys in DBMS. Then test your design reading with the SQL (Basic) skill test.