A Database Management System (DBMS) is software that stores, retrieves and manages data while enforcing consistency, security and concurrent access. A relational DBMS organises data into tables (relations) of rows and columns, linked by keys. Design usually starts with an Entity-Relationship (ER) model that is then turned into tables.
Why a DBMS over files
Plain files duplicate data, can't enforce rules, and break under concurrent access. A DBMS gives controlled redundancy, integrity constraints, concurrent access, security and recovery — all through a single declarative query language (SQL).
Keys you must know
| Key | Meaning |
|---|---|
| Super key | any set of columns that uniquely identifies a row |
| Candidate key | a minimal super key |
| Primary key | the chosen candidate key; unique and not null |
| Foreign key | a column referencing another table's primary key |
| Composite key | a key made of more than one column |
⚡ The edge
- A primary key uniquely identifies a row and cannot be null; a foreign key links tables and enforces referential integrity (you can't reference a row that doesn't exist).
- In the ER model, entities become tables, attributes become columns, and relationships become foreign keys (or a join table for many-to-many).
Worked example
'What is the difference between a primary key and a unique key?'
- Both enforce uniqueness across rows.
- A primary key also cannot be NULL and there is exactly one per table; a unique key can allow one NULL and a table can have many.
- So: primary key = unique + not null + one per table; unique key = unique but nullable and repeatable.
Answer: Both are unique; the primary key is non-null and singular, a unique key allows a null and can be multiple.
Worked example
'How do you model a many-to-many relationship?'
- A many-to-many (e.g. students and courses) can't be a single foreign key.
- You introduce a junction (bridge) table holding the two foreign keys.
- Each row of the junction table represents one pairing, turning M:N into two 1:M relationships.
Answer: Use a junction table holding both foreign keys, splitting M:N into two one-to-many links.
⚠ Watch out
- A primary key is unique and not null; a unique constraint allows a (single) null.
- A foreign key enforces referential integrity — it's not just a comment.
- An ER diagram is a design tool; the database is the resulting tables, not the diagram.