Database Management Systems (DBMS)
In this module, we'll cover the fundamentals of database management systems (DBMS), including relational and non-relational databases, and their key concepts and features.
A database is an organized place to store data so it can be saved, searched, and updated reliably.
A DBMS (Database Management System) is the software that actually stores, organizes, and lets you interact with a database. MySQL, PostgreSQL, and SQLite are all examples of a DBMS.
SQL (Structured Query Language) is the language used to talk to a relational database — to create tables, insert data, ask questions about it, and change it.
In this section, we'll cover the key concepts and theory behind DBMS, including relational and non-relational databases, and their key features and trade-offs.
- Relational vs Non-Relational Databases
- Keys
- Normalization
- ACID Properties
- Indexes
- Constraints
| Key Type | Definition | Example |
|---|---|---|
| Candidate Key | Any column(s) that could be the primary key | id, employee_code, ssn |
| Primary Key | The candidate key actually chosen | id |
| Alternate Key | Candidate keys not chosen as primary | employee_code, ssn |
| Super Key | Any set of columns that uniquely identifies a row, minimal or not | (id, email) |
| Composite Key | A primary key made of multiple columns combined | (book_id, genre_id) |
| Foreign Key | References a primary key in another table | department_id → departments.id |
| Unique Key | Enforces no duplicates; multiple allowed per table | email |
| Surrogate Key | System-generated, no real-world meaning | id INT AUTO_INCREMENT |
| Natural Key | Made from real-world, meaningful data | ssn, email |
1. One-to-One Relationship
Each row in Table A matches exactly one row in Table B, and vice versa. Less common — usually used to split a table for organization, security, or performance reasons.
Example: a users table and a user_profiles table, where each user has exactly one profile.
users user_profiles
┌────────┬──────────────┐ ┌────────┬──────────────┬────────────┐
│ id(PK) │ email │ │ id(PK) │ user_id(FK) │ bio │
├────────┼──────────────┤ ├────────┼──────────────┼────────────┤
│ 1 │ a@mail.com │<────────>│ 1 │ 1 │ Hi there │
│ 2 │ b@mail.com │<────────>│ 2 │ 2 │ Hello │
└────────┴──────────────┘ └────────┴──────────────┴────────────┘
1 1
└──────────────────────────────────┘
One-to-One Relationship2. One-to-Many Relationship
Each row in Table A can relate to many rows in Table B, but each row in Table B relates back to only one row in Table A. This is the most common relationship type.
Example: one author can write many books, but each book has one author.
authors books
┌────┬────────────────┐ ┌────┬──────────────────┬───────────┐
│ id │ name │ │ id │ title │ author_id │
├────┼────────────────┤ ├────┼──────────────────┼───────────┤
│ 1 │ Frank Herbert │<──┬───┤ 1 │ Dune │ 1 │
│ 2 │ George Orwell │<─┐└───┤ 2 │ Dune Messiah │ 1 │
└────┴────────────────┘ │ ├────┼──────────────────┼───────────┤
└────┤ 3 │ 1984 │ 2 │
└────┴──────────────────┴───────────┘
one author ─────────> many books3. Many-to-Many Relationship
Rows in Table A can relate to many rows in Table B, and rows in Table B can relate to many rows in Table A. This can't be modeled with a single foreign key — it needs a third table in between, called a junction table (or pivot table).
Example: a book can have multiple genres, and a genre can apply to multiple books.
books book_genres genres
┌────┬───────┐ ┌─────────┬──────────┐ ┌────┬──────────┐
│ id │ title │ │ book_id │ genre_id │ │ id │ name │
├────┼───────┤ ├─────────┼──────────┤ ├────┼──────────┤
│ 1 │ Dune │<─┬─┤ 1 │ 1 │───>│ 1 │ Sci-Fi │
│ │ │ └─┤ 1 │ 2 │───>│ 2 │ Adventure│
│ 2 │ 1984 │<───┤ 2 │ 1 │───>│ 1 │ Sci-Fi │
└────┴───────┘ └─────────┴──────────┘ └────┴──────────┘
books <──── many-to-many ────> genresIn this section, we'll cover common interview questions and best practices for DBMS.