DBMS Concepts and Interview Questions for Freshers

Keys: How Rows Are Identified and Linked

Keys are the most common opening question. The picture below shows how they relate, with a students table as the example.

• Super key: any set of columns that uniquely identifies a row. It may contain extra columns.

• Candidate key: a minimal super key, meaning no column can be removed without losing uniqueness.

• Primary key: the candidate key you choose. It must be unique and not NULL, and a table has only one.

• Alternate key: a candidate key that was not chosen as the primary key.

• Foreign key: a column that refers to the primary key of another table, which is how tables are linked.

• Composite key: a key made of two or more columns together.

A table that links students to courses is a good composite-key example:

CREATE TABLE enrollments (
    student_id INT,
    course_id  INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

Neither column is unique on its own, but the pair is. Each column is also a foreign key pointing back to its parent table.

Normalization: Removing Redundancy

Normalization organizes data into tables so that each fact is stored once. It prevents three kinds of anomalies: insert (you cannot add data without unrelated data), update (you must change the same fact in many rows) and delete (removing a row also destroys a different fact). The first three normal forms are the ones that matter in interviews.

• 1NF: every column holds a single, atomic value, and there are no repeating groups.

• 2NF: the table is in 1NF, and no non-key column depends on only part of a composite key.

• 3NF: the table is in 2NF, and no non-key column depends on another non-key column (no transitive dependency).

• BCNF: a stricter 3NF, where every determinant must be a candidate key.

A 1NF example. The first table stores two courses in one cell, so it breaks 1NF. The second fixes it:

Before (not 1NF)          After (1NF)
student | courses         student | course
Asha    | DSA, DBMS       Asha    | DSA
                          Asha    | DBMS

A 3NF example. In employees(emp_id, dept_id, dept_name), dept_name depends on dept_id, not on emp_id. That is a transitive dependency. The fix is to move dept_name into a separate departments table and keep only dept_id in employees.

Transactions and ACID

A transaction is a group of operations treated as one unit of work. Transferring money is the classic example: debit account A, credit account B. ACID is the set of guarantees a database gives for transactions.

The interview line: atomicity is all-or-nothing, consistency keeps the data valid, isolation keeps concurrent transactions from interfering, and durability means committed data is not lost.

Questions Interviewers Ask Most

DELETE vs TRUNCATE vs DROP

• DELETE: removes rows, optionally with a WHERE clause. It is a DML command and can be rolled back.

• TRUNCATE: removes all rows from a table at once, with no WHERE clause. It is faster than DELETE, and whether it can be rolled back depends on the database.

• DROP: removes the entire table, including its structure.

What is an index, and why use one?

An index is a data structure, usually a B-tree, that lets the database find rows by a column without scanning the whole table. It speeds up reads on that column, but costs extra storage and slows down inserts and updates, because the index must be maintained too.

Clustered vs non-clustered index

A clustered index decides the physical order of the rows, so a table can have only one. A non-clustered index is a separate structure that points back to the rows, and a table can have many.

Primary key vs unique key

Both enforce uniqueness. A primary key cannot be NULL and a table has only one. A unique key can be applied to several columns, and it generally allows NULL (the exact rule varies by database).

SQL vs NoSQL

SQL databases store data in tables with a fixed schema and give strong ACID guarantees. NoSQL databases, such as MongoDB, use flexible formats like documents or key-value pairs and are built to scale out across many servers. Neither is better in general; the choice depends on how structured the data is and how it will be queried.

What is a deadlock?

A deadlock happens when two transactions each hold a lock that the other needs, so neither can continue. The database detects the cycle and aborts one of the transactions so the other can finish.

Common Mistakes to Avoid

• Mixing up super key, candidate key and primary key. Use the nested picture: each is a narrower version of the previous one.

• Reciting “remove redundancy” for normalization without naming the anomalies it prevents.

• Listing ACID letters without being able to explain each one with the money-transfer example.

• Saying DELETE and TRUNCATE are the same. Mention WHERE, speed and rollback.

• Claiming indexes are free. Always mention the cost on writes and storage.

Key Takeaways

• A primary key is the candidate key you choose; a foreign key links one table to another.

• 1NF means atomic values, 2NF removes partial dependencies, 3NF removes transitive dependencies.

• ACID: atomicity, consistency, isolation, durability. Explain each with a money transfer.

• DELETE removes rows, TRUNCATE empties the table, DROP removes the table itself.

• Indexes speed up reads and slow down writes.

Test Your CS Fundamentals Under Time Pressure

Knowing a definition is different from recalling it cleanly in an interview or a timed test. Take a timed skill test on Skillrank to see how your core concepts hold up under real assessment conditions.