Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Database Management

Entity-Relationship Model and Database Design Explained

Updated 11 October 2026 · Fact-checked

The **Entity-Relationship (ER) model** describes data as entities, their attributes and the relationships between them, drawn as an ER diagram. To solve a question, identify entities, pick keys, list attributes, define relationships with cardinality, draw the diagram, then convert it into tables.

Understand Entity-Relationship Model and Database Design

A database stores facts about things a business cares about. Before you create tables, you plan what those things are and how they connect. The ER model is the standard planning tool. It gives you a picture, the ER diagram, that both business users and technical people can read.

An entity is a real-world thing about which data is stored, such as Customer, Employee or Invoice. An entity set is the collection of all entities of one type. An attribute is a property of an entity, such as name or date of birth. Attributes can be simple (cannot be split) or composite (can be split, like address into city and PIN code). They can be single-valued or multi-valued (like phone numbers), and stored or derived (age derived from date of birth).

A relationship links entities, such as Employee works in Department. Cardinality says how many instances can link: one-to-one, one-to-many, many-to-one or many-to-many. Participation says whether every entity must take part (total) or not (partial).

Keys identify records. A super key is any set of attributes that uniquely identifies a row. A candidate key is a minimal super key. The primary key is the candidate key you choose, and it cannot be null. An alternate key is a candidate key not chosen. A foreign key is an attribute in one table that refers to the primary key of another table, and it links the tables.

A strong entity has its own primary key and exists independently. A weak entity has no full key of its own. It depends on an owner (strong) entity through an identifying relationship, and is identified by the owner's key plus its own partial key (discriminator). Example: Dependent of an Employee.

Key rules to remember

Super key: Any attribute set that uniquely identifies every row; It may contain extra attributes. Every candidate key is a super key.
Candidate key: Minimal super key (no attribute can be removed); A table can have several. One becomes the primary key.
Primary key: Chosen candidate key; unique and NOT NULL; Only one per table. It can be composite.
Foreign key: Attribute(s) in child table referring to primary key of parent table; It may repeat and may be null unless restricted. It enforces referential integrity.
Weak entity key: Owner's primary key + partial key (discriminator); Needs an identifying relationship, usually shown with a double rectangle and double diamond.
ER to table mapping: 1:N: Put the primary key of the 'one' side as a foreign key on the 'many' side; No separate table is needed.
ER to table mapping: M:N: Create a new table with both primary keys as a composite key; Relationship attributes go into this table.
Diagram symbols: Rectangle = entity; ellipse = attribute; diamond = relationship; underline = key; double ellipse = multi-valued; dashed ellipse = derived; Draw them consistently.

How to solve Entity-Relationship Model and Database Design questions

Use this order for any ER or database design question. State each step in your answer so the examiner sees your reasoning.

1Read the scenario and underline the nouns. These are candidate entities. Verbs between them are candidate relationships.
2List the entities and mark each as strong or weak.
3For each entity, list attributes and classify them (simple, composite, multi-valued, derived).
4Choose the primary key for each entity. For a weak entity, name the partial key and the owner.
5Define each relationship with its cardinality and participation, and add any relationship attributes.
6Draw the ER diagram using standard symbols and label cardinalities.
7Convert to tables: one table per entity, foreign keys for 1:N, a new table for M:N. Mention that you would then check normalization.
8Add a line on practical value, such as data integrity, reduced redundancy or data protection.

Quickest way: Noun-verb-key shortcut

When to use it: Use it when the scenario is short and the question asks for a diagram or a list of tables in limited time.

Write entities in a row, with the primary key underlined beside each.
Join them with relationships and write 1, N or M beside each end.
Write the table list below: Table(PK, attributes, FK).
Add a new table for every M:N link.
Finish with one sentence on integrity.

Common mistakes in Entity-Relationship Model and Database Design

Treating a candidate key and a primary key as the same thing.
Both identify rows uniquely, so they look identical.
Fix: Say that candidate keys are all the minimal options, and the primary key is the one chosen from them.
Saying a foreign key must be unique and not null.
It is confused with the primary key rules.
Fix: A foreign key refers to a primary key elsewhere. In the child table it can repeat, and it can be null unless the design forbids it.
Showing a many-to-many relationship without a separate table.
Students stop at the diagram and do not map it to tables.
Fix: Always add a junction table holding both primary keys, plus any relationship attributes.
Calling a weak entity one that is simply unimportant.
The word 'weak' is read in its everyday sense.
Fix: A weak entity lacks its own full key and depends on an owner entity for identification.
Making derived or multi-valued attributes ordinary columns.
Students copy all attributes without classifying them.
Fix: Mark derived attributes with dashed ellipses and do not store them if they can be computed. Move multi-valued attributes to a separate table.
Leaving out cardinality labels on the diagram.
Drawing shapes feels like the main task.
Fix: Label every relationship line with 1, N or M, since marks are given for correct cardinality.

Worked examples

Example 1

A company keeps data on employees and departments. Each department has many employees and each employee belongs to one department. Draw the ER design in words and map it to tables.

Show the solution

Entities: Employee and Department. Both are strong entities.
Employee attributes: EmpID (key), Name, Salary. Department attributes: DeptID (key), DeptName.
Relationship: Works_In between Department and Employee. Cardinality is one-to-many (1 Department : N Employees). Employee participation is total, as each employee belongs to a department.
Mapping: Department(DeptID, DeptName) and Employee(EmpID, Name, Salary, DeptID). DeptID in Employee is a foreign key referring to Department.
Because it is 1:N, no extra table is needed.

Answer: Two entities with a 1:N relationship Works_In. Tables: Department(DeptID PK, DeptName) and Employee(EmpID PK, Name, Salary, DeptID FK).

Example 2

A bank stores customers and accounts. A customer can hold many accounts, and an account can be joint with many customers. Each customer-account link records the date the holder was added. Identify the relationship type and give the tables with keys.

Show the solution

Entities: Customer(CustID, Name) and Account(AccNo, Type, Balance), both strong.
One customer has many accounts and one account has many customers, so the relationship Holds is many-to-many.
The date added belongs to the link, not to either entity. It is a relationship attribute.
A many-to-many relationship needs a new table. Holds(CustID, AccNo, DateAdded) with the composite primary key (CustID, AccNo).
CustID and AccNo in Holds are each foreign keys referring to Customer and Account.

Answer: Relationship is many-to-many. Tables: Customer(CustID PK, Name); Account(AccNo PK, Type, Balance); Holds(CustID FK, AccNo FK, DateAdded) with composite primary key (CustID, AccNo).

Exam tips

Draw a small neat diagram even in a theory answer, and label keys and cardinality. It earns marks quickly.
When asked to differentiate keys or entity types, use a two-column comparison with an example for each.
Use Indian examples such as a company's Employee, Shareholder or Director to make answers practical.
Always finish a design answer by linking it to data integrity and data protection, which fits the law-and-practice angle of the paper.
Write the mapping rule (1:N or M:N) explicitly instead of only listing tables.

Practice questions from Database Management

Entity-Relationship Model and Database Design: frequently asked questions

What is the difference between a strong and a weak entity?

A strong entity has its own primary key and exists independently. A weak entity has no full key of its own and depends on an owner entity. It is identified by the owner's key combined with its own partial key.

What is the difference between a primary key and a candidate key?

A candidate key is any minimal set of attributes that uniquely identifies a row. The primary key is the one candidate key chosen for the table. It must be unique and cannot be null.

What is a foreign key used for?

A foreign key links a table to another table by referring to that table's primary key. It enforces referential integrity, so a record cannot point to something that does not exist.

How do you draw an ER diagram step by step?

Identify entities, add attributes, choose keys, define relationships with cardinality, then draw using rectangles, ellipses and diamonds. Finally, convert the diagram into tables.