ER Diagram Concepts: Entities, Attributes, Relationships and Cardinality

An Entity-Relationship (ER) diagram is the visual language database designers use to map out a system before writing any SQL. It captures the real-world things worth tracking (entities), the facts about them (attributes), how they connect to each other (relationships), and exactly how many of one thing relate to how many of another (cardinality). Fluency in reading and drawing ER diagrams is foundational to every design decision covered in the rest of this module.

Key Definitions

• Entity: A real-world object or concept that is distinct and worth storing data about, such as a Doctor, Patient, or Department.
• Attribute: A property or characteristic of an entity, such as a Doctor's name or salary.
• Relationship: A meaningful association between two or more entities, such as a Doctor 'works in' a Department.
• Cardinality: A rule specifying how many instances of one entity can be associated with how many instances of another entity in a relationship, such as one-to-many.
• Composite attribute: An attribute that can be broken down into smaller sub-parts, such as an address split into street, city, and postal code.
• Derived attribute: An attribute whose value can be calculated from other stored data, such as age derived from a stored date of birth.
• Multi-valued attribute: An attribute that can hold more than one value for a single entity instance, such as a doctor having multiple phone numbers.

What You'll Learn

• Define entities, attributes, and relationships as used in ER modeling.
• Distinguish between different attribute types: simple, composite, derived, and multi-valued.
• Understand cardinality notation and what it communicates about a relationship.
• Read and interpret a basic ER diagram for a hospital management system.

Detailed Explanation

In a hospital system, Doctor, Patient, Department, and Appointment are all entities — distinct, identifiable things the business needs to track. Each entity has attributes: Doctor has doctor_id, doctor_name, and salary; Patient has patient_id, patient_name, and city. Some attributes are simple (an atomic value like doctor_name), while others might be composite (an address that breaks into street, city, and state), derived (age, calculated from date_of_birth rather than stored directly), or multi-valued (a doctor with multiple contact phone numbers, which cannot fit into a single column without violating good design principles, as covered in Lesson 8.7 on First Normal Form).

Relationships connect entities meaningfully: 'a Doctor works in a Department,' 'a Patient has an Appointment,' 'an Appointment is with a Doctor.' Each relationship carries cardinality, describing the numeric nature of the association. A Department can have many Doctors, but each Doctor works in exactly one Department — this is a one-to-many relationship from Department to Doctor. A Patient can have many Appointments, and each Appointment belongs to exactly one Patient — another one-to-many relationship. Cardinality is typically shown in ER diagrams using notation like 1, N, or crow's foot symbols, and getting cardinality right is essential, because it directly determines how foreign keys will be placed when the design is converted into actual tables (covered in Lesson 8.4).

Visual Summary

An ER diagram with four entity boxes: [Department], [Doctor], [Patient], [Appointment]. A line connects Department to Doctor labeled '1' near Department and 'N' near Doctor, representing one department has many doctors. A line connects Patient to Appointment labeled '1' near Patient and 'N' near Appointment. A line connects Doctor to Appointment labeled '1' near Doctor and 'N' near Appointment. Doctor's box lists attributes doctor_id (underlined, primary key), doctor_name, salary.

Quick Reference

ER Concept	Hospital System Example
Entity	Doctor, Patient, Department, Appointment
Simple attribute	doctor_name
Composite attribute	patient_address (street + city + postal code)
Derived attribute	patient_age (derived from date_of_birth)
Multi-valued attribute	doctor_phone_numbers (a doctor may have several)
Relationship	'Doctor works in Department', 'Patient has Appointment'
Cardinality	One Department has Many Doctors (1:N)

SQL Example

-- The ER diagram's entities and cardinality directly inform table structure.
-- Department (1) --- (N) Doctor  →  doctors table holds the foreign key
CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  salary          INT,
  department_id   INT,               -- foreign key: the "many" side holds the reference
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

This schema is a direct translation of the ER diagram's Department-Doctor relationship. Because the cardinality is one Department to many Doctors, the foreign key department_id is placed on the 'many' side — the doctors table — rather than on departments. This rule, that foreign keys live on the 'many' side of a one-to-many relationship, is one of the most important and frequently tested principles of converting ER diagrams into actual tables.

Real-World Examples

• Enterprise software architects use ER diagrams as the primary communication tool between business stakeholders and database developers before any schema is built.
• Database design tools like MySQL Workbench and dbdiagram.io let teams draw ER diagrams visually and auto-generate corresponding CREATE TABLE scripts.
• Healthcare and financial systems, where entity relationships are complex and regulatory scrutiny is high, rely heavily on thorough ER diagramming before implementation.

Common Mistakes to Avoid

• Confusing an entity with an attribute — for example, treating 'Department' as an attribute of Doctor instead of recognizing it as its own entity with a relationship to Doctor.
• Storing a derived attribute like age directly instead of computing it from date_of_birth, risking stale data.
• Placing a foreign key on the wrong side of a one-to-many relationship.

Interview Questions

Q1. What is an entity in an ER diagram?

An entity is a distinct, real-world object or concept that a system needs to store data about, such as a Doctor, Patient, or Department, typically represented as a rectangle in an ER diagram.

Q2. What is the difference between a derived attribute and a stored attribute?

A stored attribute holds a value directly in the database, like date_of_birth. A derived attribute is calculated from other stored data at query time rather than stored directly, such as age being computed from date_of_birth, avoiding data that could become stale or inconsistent.

Q3. How does cardinality affect where a foreign key is placed when converting an ER diagram to tables?

In a one-to-many relationship, the foreign key is always placed on the 'many' side of the relationship, referencing the primary key of the 'one' side. For example, in a one Department to many Doctors relationship, department_id is stored as a foreign key in the doctors table.

Practice MCQs

1. What is a multi-valued attribute?

1. An attribute that can never change
2. An attribute that can hold more than one value for a single entity
3. An attribute calculated from other attributes
4. An attribute that is always a primary key

Answer: B. An attribute that can hold more than one value for a single entity

Explanation: A multi-valued attribute, such as a doctor having multiple phone numbers, can legitimately hold several values for one entity instance, which requires special handling in relational design.

2. In a one-to-many relationship between Department and Doctor, where is the foreign key placed?

1. In the Department table
2. In the Doctor table (the 'many' side)
3. In both tables equally
4. In neither table

Answer: B. In the Doctor table (the 'many' side)

Explanation: The foreign key is always placed on the 'many' side of a one-to-many relationship, referencing the primary key of the 'one' side.

Quick Revision Points

• Entities are nouns worth tracking; attributes are their properties; relationships connect entities.
• Attribute types: simple, composite, derived, and multi-valued — a common exam classification question.
• In a 1:N relationship, the foreign key always goes on the 'many' side.

Conclusion

• ER diagrams model entities, their attributes, and the relationships connecting them.
• Cardinality defines the numeric nature of a relationship and directly determines foreign key placement.
• Recognizing attribute types (simple, composite, derived, multi-valued) is essential for good schema design.

ER diagrams are the visual foundation of database design, capturing entities (the things worth tracking), their attributes (properties describing them), the relationships connecting them, and cardinality (the numeric rules governing those relationships). Understanding attribute types — simple, composite, derived, and multi-valued — and correctly interpreting cardinality notation are essential skills, since cardinality directly determines how foreign keys are placed when an ER diagram is eventually converted into real relational tables, a process covered in Lesson 8.4.