ER Modeling

Entity-Relationship (ER) modeling is a conceptual technique (introduced by Peter Chen in 1976) for describing data as entities, their attributes, and the relationships between them.

Concept

Entity-Relationship (ER) modeling is a conceptual technique (introduced by Peter Chen in 1976) for describing data as entities, their attributes, and the relationships between them. ER diagrams are the blueprint for relational schemas.

A thing about which data is stored Vehicle, ECU, Customer Entity set

All entities of the same type (becomes a table) Property of an entity Key attribute Uniquely identifies an entity Composite attribute Made of sub-parts Address → street, city, pincode Multi-valued attribute Can have several values

Phone numbers (becomes a separate table) Derived attribute Computable from others Age from date of birth Association between entities Vehicle *has* ECU

Maximum number of associations (1:1, 1:N, M:N) A vehicle has many ECUs

Optionality (participation / modality) Minimum number: mandatory (1) or optional (0)

An ECU must belong to a vehicle; a customer may have no orders Weak entity

Cannot be identified by its own attributes alone; depends on an owner entity Order line (order_id + line_no) Associative entity

An entity that represents an M:N relationship and may carry attributes Enrollment, OrderItem

Why It Matters

ER diagrams let engineers and business stakeholders agree on the data model before any code is written, and they map systematically to tables.

Syntax

Common notations:

Crow's foot notation (most common in industry tools):

Chen notation (academic): rectangles for entities, ovals for attributes, diamonds for relationships, double rectangles for weak entities, double diamonds for identifying relationships, double ovals for multi-valued attributes, dashed ovals for derived attributes.

Min-max notation: (min, max) on each side, e.g., (0, N), (1, 1).

Symbol at line end        Meaning
──────||                  exactly one (mandatory one)
──────o|                  zero or one (optional one)
──────|<                  one or many (mandatory many)
──────o<                  zero or many (optional many)

Example

34.1 Cardinality and optionality Mapping to tables:

Table with its own PK Several columns (street, city, pincode) Separate table with FK

Usually not stored (compute in query/view) 1:N relationship FK in the N side 1:1 relationship FK with UNIQUE in the optional side M:N relationship Junction table (associative entity) Mandatory participation of the N side FK column NOT NULL Table whose PK includes the owner's PK 34.2 Weak entities

An order line has a line number that is unique only within its order: line 1 exists in every order. It is identified by the combination (order_id, line_no), so it is a weak entity with an identifying relationship to ORDER.

Other weak entities: hotel room (hotel_id, room_no), apartment (building_id, flat_no), CAN signal within a message (message_id, signal_name), dependant of an employee (emp_id, dependant_name).

34.3 Associative entities When an M:N relationship has its own attributes, model it as an entity:

34.4 Complete ER example: automotive ECU software Requirements summary: vehicles contain ECUs; each ECU is of a hardware type from a supplier; software versions are released for hardware types; each ECU has a history of software installations; test runs verify a software version on a vehicle.

This model is implemented in full in Module 36.

34.5 Specialization / generalization (subtypes) An entity can have subtypes with extra attributes: VEHICLE → PASSENGER_CAR, TRUCK, ELECTRIC_VEHICLE.

Implementation options:

One table with a type column and nullable subtype columns Many NULLs, weak constraints Table per subtype (class table)

Base table + one table per subtype sharing the PK (1:1) Normalized, constraints per subtype Table per concrete class Separate complete tables per subtype No joins per type

Hard to query across all types, duplicate columns

CUSTOMER ||──────o< ORDER
"Each order belongs to exactly one customer (mandatory);
 each customer places zero or many orders (optional many)."

VEHICLE ||──────|< ECU
"Each ECU is installed in exactly one vehicle;
 each vehicle has one or more ECUs."

EMPLOYEE ||──────o| PARKING_PERMIT
"Each employee has zero or one parking permit; each permit belongs to exactly one employee."

STUDENT >o──────o< COURSE        (M:N, resolved via ENROLLMENT)

ORDER ||═════|< ORDER_LINE        (double line = identifying relationship)

CREATE TABLE order_lines (
    order_id    INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    line_no     INT NOT NULL,
    product_id  INT NOT NULL REFERENCES products(product_id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_no)     -- owner key + partial key
);

DOCTOR ||────o< APPOINTMENT >o────|| PATIENT
APPOINTMENT: appointment_time, reason, status, fee

CREATE TABLE appointments (
    appointment_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doctor_id         INT NOT NULL REFERENCES doctors(doctor_id),
    patient_id        INT NOT NULL REFERENCES patients(patient_id),
    appointment_time  TIMESTAMP NOT NULL,
    reason            VARCHAR(200),
    status            VARCHAR(20) NOT NULL DEFAULT 'SCHEDULED',
    CONSTRAINT uq_doctor_slot UNIQUE (doctor_id, appointment_time)
);

SUPPLIER ||────o< ECU_TYPE ||────o< SOFTWARE_VERSION
                     |                      |
                     ||                     ||
                     |<                     o<
VEHICLE ||────|< ECU ||────o< SOFTWARE_INSTALL >o────|| SOFTWARE_VERSION
   |
   ||────o< TEST_RUN >o────|| SOFTWARE_VERSION

Entities and key attributes:
  SUPPLIER(supplier_id, name, country)
  ECU_TYPE(ecu_type_id, part_no, name, supplier_id)
  VEHICLE(vehicle_id, vin, model, model_year)
  ECU(ecu_id, vehicle_id, ecu_type_id, serial_no)
  SOFTWARE_VERSION(sw_version_id, ecu_type_id, version_label, release_date)
  SOFTWARE_INSTALL(install_id, ecu_id, sw_version_id, installed_at)      ← associative entity
  TEST_RUN(test_run_id, vehicle_id, sw_version_id, run_at, result)

Expected Result

The mapping of STUDENT >o──o< COURSE with attribute grade produces three tables: students, courses, and enrollments(student_id, course_id, grade) with a composite primary key and two foreign keys.

Common Mistakes

Modeling M:N relationships directly (no junction) or as comma lists.

Confusing cardinality (max) with optionality (min).

Storing multi-valued attributes as repeated columns.

Missing weak-entity identification (using a global line number where a per-order line number is required, or vice versa).

Treating attributes of a relationship as attributes of one entity (e.g., putting grade in students).

Best Practices

Write each relationship as two sentences, one from each side, including "must/may" and "one/many".

Resolve all M:N relationships into associative entities before implementation.

Use a modeling tool (dbdiagram, draw.io, ERwin, MySQL Workbench, pgModeler) and keep diagrams in sync with migrations.

Review models with domain experts.

Interview Relevance

Medium-high: "What is an ER diagram?", "Explain cardinality and participation", "What is a weak entity?", "How do you convert an ER diagram to tables?", "Draw an ER diagram for a library/hospital".

Practice Questions

Draw an ER diagram for a library: members, books, copies, loans, authors.

Identify a weak entity in a hospital system and write its CREATE TABLE.

Convert "A flight has many seats; a passenger books seats on flights" into tables.

Explain the difference between cardinality and optionality with an example.

Choose a subtype implementation strategy for vehicles (car, truck, bike) and justify it.

Quiz Checkpoint 6 (Modules 30 – 34)

Q1. (Multiple choice) Which statement best describes authorization? A) Verifying who a user is B) Determining what a user can do C) Encrypting data D) Logging queries Answer: B.

Q2. (True/False) Using an ORM guarantees that an application is free from SQL injection.

Answer: False. Raw SQL or string-built queries inside an ORM can still be vulnerable.

Q3. (Multiple choice) Restoring requires the last full backup plus only the latest ______ backup. A) Incremental B) Differential C) Log D) Snapshot

Q4. (Definition) An RPO of 15 minutes means what?

Answer: The business accepts losing at most 15 minutes of data in a disaster, so backups/replication must capture changes at least every 15 minutes.

Q5. (Find the error) Answer: The value is formatted into the SQL string (injection risk). Use cursor.execute("SELECT * FROM products WHERE category = %s", (category,)).

Q6. (Multiple choice) How is a weak entity's primary key typically formed? A) A random UUID B) The owner's key plus a partial key C) Any unique column D) It has no key

Q7. (Write the query) Give a role auditor_ro read access to salary_audit only.

Q8. (True/False) Replication to a standby server is a sufficient backup strategy.

Answer: False. Logical mistakes and corruption replicate too; you still need point-in-time backups.

cursor.execute("SELECT * FROM products WHERE category = '%s'" % category)

CREATE ROLE auditor_ro;
GRANT SELECT ON salary_audit TO auditor_ro;