ER Model & Mapping to Relations
Draw the world as boxes, diamonds, and lines — then turn that picture into tables. The two skills GATE tests over and over in database design.
What you'll learn
- Entities are real-world things; attributes describe them; relationships connect them
- Cardinality: 1:1, 1:N, M:N — the rule that decides table shape
- ER to relational mapping: each entity becomes a table
- M:N becomes its own junction table; 1:N folds the foreign key into the 'many' side
Before you start
Before any table exists, you sketch the world. Students take courses. Orders ship to customers. Books have authors. That sketch — boxes for the things, diamonds for how they connect — is an Entity-Relationship (ER) diagram. It’s a designer’s whiteboard tool: drawing it well is half the work; turning it into tables is the other half, and the rules are mechanical. It’s also the first thing you do before building any data product — get the M:N call wrong and the schema can’t even store the data you need, let alone query it.
The three building blocks
- Entity — a real-world thing the database tracks. Student. Course. Order. Drawn as a rectangle.
- Attribute — a property describing an entity. name, age, course_id. Drawn as an ellipse attached to the entity (or just listed inside the box).
- Relationship — a verb connecting entities. Student enrolls in Course. Drawn as a diamond linking two rectangles.
Every relationship has a cardinality — how many of one side can pair with the other:
- 1:1 — one of each. Person has one Passport.
- 1:N — one on the left, many on the right. Department has many Employees.
- M:N — many on both sides. Student takes many Courses; each Course has many Students.
Mapping ER to relations — three rules
Every ER diagram turns into tables by a small fixed recipe:
- Each entity becomes a table. Attributes become columns; the entity’s key becomes the primary key.
- M:N relationship becomes its own table (a junction table). Its columns are the primary keys of both sides (each is a foreign key — a column that points at another table’s key); together they’re the primary key of the junction.
- 1:N relationship folds the foreign key into the “many” side. No new table — just add a column on the many-side referencing the one-side’s PK.
(A 1:1 relationship can be folded either way; we usually pick the side that makes the foreign key NOT NULL more natural.)
How GATE asks this
Two patterns recur. First, MCQ on cardinality — “Which of the following best
models the relationship between Department and Employee?” with 1:1, 1:N,
M:N as choices. Second, NAT-style counting — “Given this ER diagram with 3
entities and 1 M:N + 1 1:N relationship, how many relations does the schema
require?” The answer is mechanical once you apply the three rules.
Worked example
Design: Student enrolls in Course, where a student can take many courses and a course can have many students (M:N).
Apply the rules:
- Rule 1:
Student(student_id, name, age)— one table. - Rule 1:
Course(course_id, title, credits)— one table. - Rule 2 (M:N):
Enrolls(student_id, course_id)— junction table. The pair(student_id, course_id)is the primary key; each column is also a foreign key to its parent table.
Three relations total. If instead the relationship were 1:N (one Course is
taught by many Students — say a homeroom), Rule 3 would say: no junction. Just
add course_id as a foreign key column in the Student table. Two relations.
Quick check
Quick check
Practice this in an interview
All questionsOLTP systems handle many small, latency-sensitive transactions that read and write a few rows at a time, so they are optimized for fast point lookups and row-level locking. OLAP systems run infrequent but wide analytical queries over millions of rows, so they benefit from columnar storage, bulk scans, and denormalized schemas that minimize joins.
Data warehouses favor denormalization — wide, flat tables that trade storage for query simplicity and performance. Normalization (splitting tables to eliminate redundancy) reduces storage but multiplies join hops, increasing query complexity and optimizer cost. In columnar warehouses with compression, the storage cost of redundancy is negligible, so denormalized star schemas consistently outperform normalized models for analytical workloads.
1NF eliminates repeating groups and requires atomic column values. 2NF further removes partial dependencies on a composite key. 3NF removes transitive dependencies — every non-key column must depend on the key, the whole key, and nothing but the key. Denormalization trades update anomalies for read performance, and is appropriate when the read path dominates and write correctness can be enforced at the application layer or with materialized views.
ETL transforms data before loading it into the destination, which was necessary when warehouses were expensive and compute-constrained. ELT loads raw data first and transforms inside the warehouse, leveraging cheap cloud compute and making raw data available for reprocessing. ELT is the default in modern cloud stacks; ETL still makes sense when you must mask sensitive fields before they ever land in the warehouse.