Database Fundamentals
Entity-Relationship (ER) Diagrams
Learn how Entity-Relationship (ER) diagrams model real-world data, and how to design multi-table databases with correct relationships.
Entity-Relationship (ER) Diagrams
An ER diagram is a visual model of the data in a system before creating any tables. It shows entities (things we store data about), their attributes, and the relationships between them. ER diagrams are the blueprint used to design database tables.
| Term | Meaning |
|---|---|
| Entity | a thing about which data is stored (e.g. Movie, Actor, Rating) |
| Attribute | a property of an entity (e.g. title, runtime, ratingCode) |
| Relationship | how two entities are connected (e.g. Actor acts in Movie) |
Relationship Types
| Relationship | Notation | Meaning | Example |
|---|---|---|---|
| One-to-One | (1:1) | one row in A matches at most one row in B | one person has one passport |
| One-to-Many | (1:N) | one row in A matches many rows in B | one rating code applies to many movies |
| Many-to-Many | (M:N) | many rows in A match many rows in B | one actor appears in many movies; one movie has many actors Resolved by an INTERSECTION (junction) table |
Resolving Many-to-Many Relationships
A Many-to-Many relationship between Actor and Movie cannot be stored directly in a relational database. We create a Casting table to resolve it:
| Table | Columns |
|---|---|
| Actor table | actorNo | firstName | surname | gender |
| Movie table | movieNo | title | relyear | runtime | ratingCode |
| Casting table | castId (PK) | actorNo (FK) | movieNo (FK) |
Each row in Casting represents ONE actor in ONE movie
| castId | actorNo | movieNo |
|---|---|---|
| 1 | 51329 | 82693 <- Bradley Cooper in Silver Linings |
| 2 | 72129 | 82693 <- Jennifer Lawrence in Silver Linings |
| 3 | 72129 | 70160 <- Jennifer Lawrence in Hunger Games |
Relationships Diagram (Updated Movie Database)
Normalisation Principles
Good database design follows normalisation rules to eliminate redundancy and update anomalies. The key principle: store each fact in exactly one place. If rating descriptions are stored in the Movie table and a description changes, you must update every row — this is an update anomaly. Separate tables avoid this.
| Term | Meaning |
|---|---|
| 1NF | Atomic values — no repeating groups or multi-valued fields |
| 2NF | No partial dependencies on a composite key |
| 3NF | No transitive dependencies (non-key fields depend only on the PK) |
Indexes and Performance
An index is a sorted data structure the RDBMS maintains alongside a table to speed up searches. The RDBMS automatically creates a Primary Key index. Binary search on a 100,000-row index takes ~17 comparisons instead of 50,000 sequential reads. Trade-off: indexes speed up reads but slow down writes (every INSERT/UPDATE/DELETE must also update each index).