Database Fundamentals
What Is a Database?
Understand what databases are, how relational databases work, and core concepts like tables, primary keys, and relationships.
What Is a Database?
A database is an organised collection of related data stored and accessed electronically. A Database Management System (DBMS) is the software that manages the database — it handles storage, retrieval, security, and integrity. A Relational Database Management System (RDBMS) stores data in tables (relations) and enforces relationships between them.
Tables, Rows, and Columns
| Term | Meaning |
|---|---|
| Table | a collection of related records (e.g. Movie table) |
| Row | one record (e.g. one movie: movieNo, title, relyear, runtime) |
| Column | one attribute/field (e.g. title, relyear) |
Example Movie table
| movieNo | title | relyear | runtime | ratingCode |
|---|---|---|---|---|
| 15 | The Hunger Games | 2012 | 142 | PG |
| 41 | Silver Linings Playbook | 2012 | 122 | MA |
| 135 | The Hangover | 2009 | 100 | MA |
| 156 | X-Men Days of Future Past | 2014 | 101 | PG |
Primary Keys
Every table needs a Primary Key (PK): a column (or set of columns) whose value uniquely identifies each row. A DBMS automatically creates an index on the Primary Key field to speed up lookups. Entering a duplicate PK value causes an error.
| Choice | Field | Reason |
|---|---|---|
| Good PK | movieNo | unique integer per movie, never changes |
| Bad PK | title | movies can share names across years |
| Bad PK | relyear | obviously not unique |
Foreign Keys and Relationships
A Foreign Key (FK) is a column in one table that references the Primary Key of another table. This creates a relationship (link) between the two tables. A Many-to-One relationship means many rows in the FK table can point to the same PK value.
Rating table (Primary Key = ratingCode)
| ratingCode | ratingName | description |
|---|---|---|
| G | General | Suitable for all ages |
| PG | Parental Guidance | Recommended for 15+ |
| M | Mature (15+) | Persons under 15 accompanied |
| MA | Mature Accompanied (15+) | Parental guidance recommended |
| R | Restricted (18+) | Restricted to adults 18+ |
Referential Integrity
Enforcing referential integrity means the RDBMS will reject any FK value that does not match an existing PK value. For example, inserting a movie with ratingCode = 'X' would fail because 'X' does not exist in the Rating table. This prevents orphaned records.
Why Separate Tables?
Storing rating details inside the Movie table causes data redundancy: the same rating description would be repeated for every movie with that rating. This wastes space and makes updates difficult. A separate Rating table stores the description once, and Movie rows simply reference it via ratingCode.
Cartesian Products: A Warning
If two tables are joined in a query without defining the relationship, the RDBMS produces a Cartesian product — every row from table A is matched with every row from table B. With 318 movies and 5 ratings, this yields 318 × 5 = 1,590 rows of useless data. Always define relationships before querying multiple tables.