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 |
In this section, we'll cover common interview questions and best practices for DBMS.