DBMS interview questions
Keys, normalisation, ACID, transactions, indexing and the database theory interviewers ask.
1. What is a DBMS? How is an RDBMS different?
A DBMS is software for storing, retrieving and managing data (with security, backup and concurrent access). An RDBMS stores data in related tables with rows and columns, uses keys to link tables and SQL to query them: MySQL, PostgreSQL, Oracle, SQL Server.
2. What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table and cannot be NULL; a table has one. A foreign key is a column that refers to the primary key of another table, linking the tables and enforcing referential integrity; it can repeat and can be NULL.
Asked in: TCS NQT, Wipro Elite NTH, Capgemini
3. What are candidate, super, unique and composite keys?
A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key; one is chosen as the primary key. A unique key also enforces uniqueness but allows a NULL. A composite key is a key made of two or more columns.
4. What is normalisation? Explain 1NF, 2NF, 3NF and BCNF.
Organising tables to reduce duplicate data and update anomalies. 1NF: atomic values, no repeating groups. 2NF: 1NF and no partial dependency on part of a composite key. 3NF: 2NF and no transitive dependency (non-key columns depend only on the key). BCNF: every determinant is a candidate key.
Asked in: Wipro Elite NTH
5. What are the ACID properties?
Atomicity (a transaction happens completely or not at all), Consistency (it moves the database from one valid state to another), Isolation (concurrent transactions do not interfere) and Durability (once committed, changes survive a crash). A bank transfer is the usual example.
Asked in: TCS NQT, Capgemini
6. What is a transaction? COMMIT versus ROLLBACK?
A group of operations treated as one unit of work. COMMIT saves all its changes permanently; ROLLBACK undoes them back to the start (or to a SAVEPOINT).
7. What is an index and why is it used?
A data structure (usually a B-tree) that lets the database find rows quickly without scanning the whole table, like a book's index. It speeds up searches and joins but uses extra space and slows inserts and updates. Primary keys are indexed automatically.
Asked in: Cognizant GenC, LTIMindtree
8. What is a view?
A virtual table defined by a saved SELECT query. It stores no data itself; it simplifies complex queries and can restrict which columns users see.
9. What is an ER diagram?
An entity-relationship diagram models a database: entities (tables) as rectangles, attributes as ovals and relationships (one-to-one, one-to-many, many-to-many) as diamonds. It is drawn before creating tables.
10. What are DDL, DML, DCL and TCL?
DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML changes data (INSERT, UPDATE, DELETE; SELECT is often grouped here). DCL controls access (GRANT, REVOKE). TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).
11. What is the difference between SQL and NoSQL databases?
SQL databases are relational, with fixed schemas, tables and strong consistency (MySQL, PostgreSQL). NoSQL databases store documents, key-value pairs, wide columns or graphs with flexible schemas and scale out easily (MongoDB, Redis, Cassandra, Firestore).