DBMS vs file system and types of keys
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)"Why use a DBMS instead of plain files?" and "What is the difference between a candidate key and a primary key?" open most DBMS rounds. The interviewer checks that your basics are crisp: one clear line, then a small example.
Problems with file systems: redundancy, inconsistency, concurrency
A DBMS (database management system) is software that stores data in a database and controls every read and write, so many users and programs can share the data safely. MySQL, PostgreSQL, Oracle and SQL Server are examples.
A college keeps one student file in the hostel office and another in the accounts office. The example below shows what goes wrong when the same phone number lives in both files.
Example 1 · Two offices, two files, one inconsistency
# Two separate "files", each keeping its own copy of the phone number.hostel_file = {"21CS045": "Priya, 98450 11111"}accounts_file = {"21CS045": "Priya, 98450 11111"}
# Priya changes her number; only the hostel office updates its file.hostel_file["21CS045"] = "Priya, 99000 22222"
print(f"Hostel file: {hostel_file['21CS045']}")print(f"Accounts file: {accounts_file['21CS045']}")print("Consistent?", hostel_file["21CS045"] == accounts_file["21CS045"])Hostel file: Priya, 99000 22222 Accounts file: Priya, 98450 11111 Consistent? False
What this code does
- The phone number is stored twice (redundancy), so updating one copy leaves the other wrong (inconsistency). A fee reminder now goes to an old number.
- In a DBMS the phone is stored once in a student table, and both offices read it from there.
Interviewers want these four problems named, each with a one-line college example:
- Redundancy and inconsistency: the same data is stored in many files, and when one copy changes, the others become wrong.
- Concurrent access problems: two programs updating the same file at once can overwrite each other's changes.
- Integrity and atomicity: rules such as "fee ≥ 0" live in program code, and a crash halfway through a transfer can leave half-updated files.
- Data access and security: every new question needs a new program, and file permissions cannot say "accounts may see fees but not marks". Revise: Why databases (opens in a new tab).
Three-level architecture and data independence
Data independence is the ability to change one level of a database without rewriting the level above it. Logical data independence: change the tables (add a column) without breaking user views. Physical data independence: change storage (add an index) without changing the tables.
The three-level architecture (ANSI/SPARC) separates how users see data from how it is stored. External level: views for each user group. Conceptual level: all tables and relationships. Internal level: files, indexes and storage.
External level: view for hostel office | view for accounts | view for exam cell \ | /Conceptual level: tables student, hostel_room, fee_payment, marks (+ constraints) |Internal level: data files, B+ tree indexes, pages on diskPhysical independence is easier to achieve than logical: adding an index changes nothing a program can see, while renaming or dropping a column breaks every view and query that names it.
Super, candidate, primary, alternate, foreign and composite keys
A candidate key is a minimal set of columns whose values are unique for every row: remove any column and uniqueness is lost. The primary key is the candidate key you choose; the other candidate keys are alternate keys.
In a student table, roll number, college email and Aadhaar number are each unique. Which one do you pick as the key, and what do you call the others? The SQL below declares every kind of key.
Example 2 · Primary, alternate, composite and foreign keys in SQL
Runs in: MySQL 8
CREATE TABLE student ( roll_no VARCHAR(10) PRIMARY KEY, -- chosen candidate key aadhaar_no CHAR(12) NOT NULL UNIQUE, -- alternate key email VARCHAR(80) NOT NULL UNIQUE, -- alternate key name VARCHAR(60) NOT NULL);CREATE TABLE marks ( roll_no VARCHAR(10), subject_code VARCHAR(8), score INT, PRIMARY KEY (roll_no, subject_code), -- composite key FOREIGN KEY (roll_no) REFERENCES student(roll_no));INSERT INTO student VALUES ('21CS045', '123412341234', 'priya@college.edu', 'Priya');INSERT INTO marks VALUES ('21CS045', 'CS301', 88);INSERT INTO marks VALUES ('21CS045', 'CS301', 91);INSERT INTO marks VALUES ('21CS999', 'CS301', 75);SELECT * FROM marksERROR 1062 (23000): Duplicate entry '21CS045-CS301' for key 'marks.PRIMARY' ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`college`.`marks`, CONSTRAINT `marks_ibfk_1` FOREIGN KEY (`roll_no`) REFERENCES `student` (`roll_no`)) +---------+--------------+-------+ | roll_no | subject_code | score | +---------+--------------+-------+ | 21CS045 | CS301 | 88 | +---------+--------------+-------+
What this code does
- The second marks insert fails because (roll_no, subject_code) must be unique together; the third fails because roll number 21CS999 does not exist in
student. - UNIQUE on aadhaar_no and email turns those alternate keys into rules the database enforces, not just ideas on paper.
A super key is any set of columns that is unique for every row, so every candidate key is a super key, but (roll_no, name) is a super key without being minimal. A composite key has more than one column, such as (roll_no, subject_code). A foreign key refers to a key of another table, which enforces referential integrity. Revise: Primary keys (opens in a new tab) and Foreign keys (opens in a new tab).
Predict the output
Name the key
Read each table, then pick the answer you would say aloud.
Round 1 of 3
roll_no and email are both unique; roll_no is the primary key.
Worked example identifying keys in a table
The student table has roll_no, aadhaar_no, email, name, phone and branch. Decide the keys from what the data means, then let the program confirm that the sample rows do not contradict you.
- List the columns and ask which ones can never repeat, by meaning.
- Check pairs of columns for combinations that are unique together.
- Keep only minimal sets: these are the candidate keys.
- Pick one as primary key (short, stable, never null); the rest are alternate keys.
- Look for columns that refer to other tables: those are foreign keys.
Example 3 · Which column sets are unique in the data?
from itertools import combinations
rows = [ {"roll_no": "21CS045", "email": "priya@c.edu", "name": "Priya", "branch": "CSE"}, {"roll_no": "21CS046", "email": "rahul@c.edu", "name": "Rahul", "branch": "CSE"}, {"roll_no": "21EC010", "email": "priya.k@c.edu", "name": "Priya", "branch": "ECE"}, {"roll_no": "21EC011", "email": "arun@c.edu", "name": "Arun", "branch": "ECE"},]cols = list(rows[0])
def is_unique(subset): values = [tuple(row[c] for c in subset) for row in rows] return len(set(values)) == len(values)
# Every non-empty set of columns whose values never repeat.super_keys = [s for n in range(1, len(cols) + 1) for s in combinations(cols, n) if is_unique(s)]# Minimal: no smaller set inside it is already unique.candidates = [s for s in super_keys if not any(set(t) < set(s) for t in super_keys)]
print("Super keys found:", len(super_keys))print("Minimal ones (candidate keys in this data):", [list(s) for s in candidates])Super keys found: 13 Minimal ones (candidate keys in this data): [['roll_no'], ['email'], ['name', 'branch']]
What this code does
- It tries every non-empty set of columns (15 for 4 columns), keeps those with no repeated values, and marks the minimal ones.
- {name, branch} looks unique in these four rows, but two Priyas in CSE are perfectly possible. Data can only disprove a key; the meaning of the columns decides the real candidate keys: {roll_no} and {email}.
So the candidate keys are {roll_no}, {aadhaar_no} and {email}. Choose roll_no as the primary key (short, stable, meaningful to the college); aadhaar_no and email are alternate keys. Every superset of a candidate key, such as {roll_no, name}, is a super key. Name, phone and branch are not keys: two students can share a name, siblings can share a phone, and a branch has many students.
Find the bug
Find the wrong key claim
Each snippet has one wrong line. Tap it.
A learner listed keys of student(roll_no, aadhaar_no, email, name).
Round 1 of 3Interview questions with model answers
DBMS fundamentals questions are usually the first few in a technical round: why a DBMS, what each key is, primary key vs unique key, and what referential integrity means. Answer with a one-line definition and a small college or shopping example.
The Interview tab has the full bank, from "What is a DBMS?" to follow-ups such as "Can a foreign key be NULL?" and "Why not use Aadhaar as the primary key?".
- Must know: super key ⊇ candidate key ⊇ primary key (one chosen).
- Must know: primary key = unique + NOT NULL, one per table.
- Must know: unique key allows NULL (in MySQL, many NULLs); many per table.
- Must know: foreign key → referential integrity; can be NULL unless declared NOT NULL.
Next step
Check your understanding
12 quick questions