DBMS Interview Questions

"A DBMS is any software that manages data for you: storing it, letting many users read and write it safely, controlling who can see what, and handling backup and recovery. An RDBMS is one kind of DBMS that follows the relational model. Data sits in tables, each row is identified by a key, and tables relate to each other through foreign keys. You query it with SQL, and the database enforces rules you declare, like uniqueness or referential integrity. So every RDBMS is a DBMS, but not the other way round. A document store, a key-value store or an old hierarchical system are all DBMSs that aren't relational. PostgreSQL, MySQL and Oracle are typical relational ones."

"I think of the Cartesian product as the starting point: every row of one table paired with every row of the other. A join is that product filtered by a condition. If the condition is any comparison, it's a theta join. If it's equality, like the order's customer_id matching the customer's id, it's an equi-join, which is what most inner joins are. A natural join is an equi-join on every column the two tables share by name, keeping one copy of those columns. It's risky in real schemas, because adding a column with a common name, like created_at, silently changes the join. Outer joins keep rows that found no match. A left outer join keeps every customer, even those with no orders, and fills the order columns with nulls. A full outer join keeps unmatched rows from both sides."

"Take a Student table with student_id, email, dept_id, a roll number that's unique within a department, and name. Any set of columns that uniquely identifies a row is a super key, so student_id alone is one, and so is student_id plus name, even though name adds nothing. A candidate key is a minimal super key: student_id, email, and the pair dept_id plus roll number are all candidates, because dropping any column from them breaks uniqueness. From those I pick one as the primary key, usually student_id, since it's short and never changes. The primary key can't be null and there's only one per table. The other candidates become alternate keys, and I'd still put unique constraints on them so the database enforces them."

"A foreign key says the value in this column must exist as a key in the parent table. So an order can't point to customer 42 unless customer 42 exists. The database checks it when a child row is inserted or updated, and when a parent row is deleted or its key changes. When a parent is deleted I have a few choices. Restrict, or no action, refuses the delete while children still point at it. Cascade deletes the children too, which suits data that means nothing without the parent, like the lines of an order. Set null keeps the child and clears the link, which fits an employee whose manager has left. I choose based on what the data means, and I'm careful with cascade because one delete can quietly remove a lot of rows."

"A composite key fits when the combination of columns really is the identity. An enrollment table linking students to courses is the classic case: the pair student_id and course_id is naturally unique, so I'd make it the primary key, and that also stops the same student being enrolled twice. I'd add a surrogate, like an auto-generated id, when the natural key is wide, might change, or lots of other tables will reference it, because every child would otherwise have to carry all those columns. The trap with surrogates is forgetting the business rule. If I give enrollment its own id, I still add a unique constraint on student_id plus course_id, or two identical enrollments can slip in with different ids."

"First I'd separate Book from Copy, because the library owns several physical copies of one title and it lends copies, not titles. Book has the isbn, title and so on. Copy has a copy_id and a foreign key to its book. Member has a member_id and contact details. Members and copies are many-to-many over time, so I'd model that as a Loan entity with its own attributes: member, copy, loan date, due date and return date. When I map to tables, each one-to-many becomes a foreign key on the many side, so copies carry the isbn and loans carry member_id and copy_id. Authors and books are many-to-many, so that needs a book_author table. Finally I'd make sure a copy can have only one open loan, meaning one with no return date."

"Picture one table with student_id, student_name, course_id, course_name and instructor_phone, one row per enrollment, keyed on student plus course. An insertion anomaly: I can't record a new course until some student enrolls, because the key needs a student. An update anomaly: the instructor's phone is repeated in every enrollment row, so changing it means updating many rows, and if I miss one the table now disagrees with itself. A deletion anomaly: if the last student drops a course, deleting that row also wipes out everything we knew about the course. All three come from storing more than one fact in a row. Splitting into Student, Course and Enrollment tables stores each fact once, and the anomalies go away."

"Say I start with an Orders table holding order_id, order_date, customer_id, customer_city, and one cell listing several products. For 1NF, no lists in a cell, so I make one row per order and product, keyed on order_id plus product_id, with a quantity column. Now 2NF: product_name depends only on product_id, and the date and customer depend only on order_id. Those are partial dependencies on part of the key, so product details move to a Product table and order-level details to an Order table, leaving OrderItem with order_id, product_id and quantity. Then 3NF: in Order, customer_city depends on customer_id, not directly on order_id. That's a transitive dependency, so city moves to a Customer table. Now each table describes one thing."

"BCNF says that for every non-trivial functional dependency, the left side must be a super key. 3NF is a bit looser: it also allows the dependency if the right side is a prime attribute, meaning part of some candidate key. The classic example is a Teaching table with student, course and instructor. Each instructor teaches only one course, so instructor determines course. A student takes a course from one instructor, so student plus course determines instructor. The candidate keys are student plus course, and student plus instructor. Instructor to course passes 3NF because course is part of a key, but instructor isn't a super key, so it breaks BCNF. Splitting into instructor-course and student-instructor is lossless, but the rule that a student has one instructor per course can no longer be checked inside one table."

"I start with attributes that never appear on the right-hand side of a dependency, because nothing can derive them, so they must be part of every key. Here that's A and C. Then I take the closure of A and C together. From A I get B. Now I have B and C, so BC gives me D. D gives me E. So the closure of AC is all five attributes, which makes AC a super key. It's minimal, because A alone only reaches B, and C alone reaches nothing new. And since every key has to contain both A and C, AC is the only candidate key. Knowing that, I can also see the relation isn't in 2NF, because B depends on A alone, which is only part of the key."

"I denormalize when reads matter much more than writes and the normalized design forces the same expensive joins or sums on every request. A common example is storing the order total on the order row instead of adding up the order lines each time. Copying the customer's name onto an invoice is another, and there it's actually more correct, because an invoice should show the name as it was when it was issued. Reporting schemas, like star schemas in a data warehouse, are denormalized on purpose too. The cost is that one fact now lives in two places, so every write must update both, through the application, a trigger or a scheduled job, and if one path forgets, the data drifts. So I normalize first, measure, and denormalize only the spots that are proven slow."

"A transfer is two writes: take the amount out of account A and add it to account B. Atomicity means both happen or neither does. If the server dies after the debit, the database rolls it back so the money doesn't just vanish. Consistency means every rule holds before and after, like a balance can't go below zero, and the total across the two accounts is unchanged. Isolation means another transaction running at the same time, say a balance report, never sees the state where A has been debited but B hasn't been credited yet. Durability means that once I get the commit back, the transfer survives a crash or power cut, because the database has already written it to its log on disk."

"A dirty read is when T1 updates a price and T2 reads the new price before T1 commits. If T1 rolls back, T2 acted on a value that never officially existed. A non-repeatable read is when T1 reads a row, T2 updates it and commits, and T1 reads it again in the same transaction and sees a different value. A phantom is about sets of rows: T1 counts today's orders, T2 inserts a new order and commits, and T1's second count is higher. No existing row changed; a new one appeared. A lost update is when two people read a stock count of ten, each subtracts one and writes nine, and one sale disappears. The standard isolation levels are defined by which of the first three they allow."

"Read uncommitted lets a transaction see other people's uncommitted changes, so dirty reads can happen. Read committed only shows committed data, but if I read a row twice someone may have changed it in between. Repeatable read guarantees rows I've read won't change under me, though the standard still allows phantom rows in range queries. Serializable is the strictest: the outcome is as if transactions ran one after another. Each step up costs concurrency, through more locking or more transactions aborted and retried. For a typical web app I'd keep the database's default, which in many engines is read committed, and protect the few risky spots, like decrementing stock, with a row lock or a serializable transaction for just that operation. Real engines also differ, and some give more protection than the standard minimum."

"A schedule is the interleaved order in which several transactions' reads and writes actually run. Two operations conflict if they come from different transactions, touch the same data item, and at least one is a write, so read-write, write-read or write-write. A schedule is conflict serializable if I can reorder it by swapping only non-conflicting neighbours and end up with a serial schedule. The practical test is a precedence graph: one node per transaction, and an edge from Ti to Tj whenever an operation of Ti conflicts with a later operation of Tj. If the graph has no cycle, the schedule is conflict serializable, and a topological order of the graph gives the equivalent serial order. If there's a cycle, like T1 before T2 on X but T2 before T1 on Y, it isn't."

"The key idea is the write-ahead log. Before a changed data page is written to disk, the log record describing that change must already be on disk, and a transaction only counts as committed once its commit record has been flushed. The data pages themselves can be written lazily later. In the classic approach, recovery after a crash reads the log. For committed transactions whose changes may not have reached the data files, it redoes them, which gives durability. For transactions that never logged a commit, it undoes any of their changes that did reach disk, which gives atomicity. Checkpoints record a known point so recovery doesn't replay the log from the very beginning. This is also why commits are fast: appending to a log sequentially is cheaper than forcing scattered data pages to disk."

"A shared lock is taken for reading. Several transactions can hold it on the same row at once, since readers don't interfere with each other. An exclusive lock is for writing, and while one transaction holds it, nobody else can hold any lock on that item. Two-phase locking is the rule that a transaction first only acquires locks, the growing phase, and once it releases any lock it can never acquire another, the shrinking phase. That rule on its own guarantees a conflict serializable schedule. Basic two-phase locking can still let others read data that later rolls back, causing cascading aborts, so real systems use strict two-phase locking and hold exclusive locks until commit or abort. The catch is that it doesn't prevent deadlocks, so the database still has to detect or avoid them."

"Say transaction one updates account A and then tries to update account B, while transaction two updates B and then tries A. Each holds an exclusive lock the other needs, so neither can move. Most databases detect this with a wait-for graph, where an edge means one transaction is waiting on another. When they find a cycle, they pick a victim, often the one that's cheapest to undo, roll it back and return an error, so the other can finish. Textbook prevention schemes like wait-die and wound-wait use transaction timestamps to decide who waits and who aborts, so a cycle never forms. In application code, the practical fixes are to always touch rows in the same order, say by ascending account id, keep transactions short, and retry when you get a deadlock error."

"This is a check-then-act race. Both requests read that the seat is free, both decide to book, and both write. The fix is to make the check and the write one atomic step. One way is pessimistic: inside a transaction, select the seat row for update, which takes a row lock, so the second request waits until the first commits and then sees the seat is taken. The other way is optimistic: a single update that books the seat only where it's still free, then I check how many rows changed. If it's zero, someone got there first and I tell the user. Under light contention the optimistic version holds locks only for a moment and scales well. Either way I'd back it with a unique constraint on flight and seat wherever bookings are stored, so even a bug elsewhere can't create a double booking."

"Indexes live on disk, and the expensive part is reading a page, so the structure should touch as few pages as possible. A binary search tree has only two children per node, so it gets very deep. A B+ tree node is a whole page holding many keys, often hundreds, so the tree is wide and shallow, and finding one row among millions might take three or four page reads, with the top levels usually cached in memory. In a B+ tree the entries sit only in the leaves, and the leaves are linked in sorted order. So after finding the start of a range, like all orders in March, I just walk along the leaves. A hash index is great for exact matches, but it scatters keys, so it can't answer a range or return sorted output. The tree also stays balanced as rows come and go."

"A clustered index defines the order in which the table's rows are actually stored. Its leaf level is the data itself, so a table can have only one, because rows can only be physically ordered one way. Engines differ: MySQL's InnoDB clusters the table on its primary key, while PostgreSQL keeps rows in a heap, so all its indexes are non-clustered. A non-clustered index is a separate structure: it keeps the indexed values in sorted order plus a pointer back to the full row, either a row address or the clustered key. A table can have many of these. The difference shows up on lookups. With a non-clustered index the database finds the key, then has to go and fetch the rest of the row, unless the index already contains every column the query needs. That's called a covering index, and it skips the extra fetch."

"I'd agree with the goal and push back on the method. Every index is another structure the database has to update on each insert, update and delete, so on a busy orders table it directly slows writes, and it takes disk and memory. Single-column indexes on everything also tend to go unused. For a query that filters on customer and status and sorts by date, one composite index on those columns in the right order does far more than three separate ones. A column like status with only a few values rarely helps on its own. So I'd suggest we pull the slowest and most frequent queries, look at their execution plans, and add a small number of indexes that serve them. Then after a week we check the index usage numbers and drop anything that isn't pulling its weight."

"A view is a named, stored query. It holds no data; when I select from it, the database runs the query against the base tables, so it's always current. I use views to hide complex joins, to keep a stable interface while tables change underneath, and for security, like exposing only some columns to a reporting user. A materialized view actually stores the result, so reads are fast, which suits expensive summaries. The catch is it goes out of date and has to be refreshed, on a schedule or on demand, depending on the database. On updates, a simple view over one table with no grouping or distinct can usually be written through, and the change lands in the base table. Views with aggregates or complex joins generally can't be, unless you add something like an instead-of trigger."

"A stored procedure is named code in the database that I call explicitly. It can run many statements and is handy for a multi-step operation that should live next to the data. A function returns a value, so I can use it inside a query, in the select list or the where clause, and databases usually limit what side effects it can have. A trigger isn't called at all; it fires by itself when rows are inserted, updated or deleted. I'd use a trigger for things that must happen no matter which application writes the data, like writing an audit row or stamping an updated_at column. Teams are wary because triggers are hidden. Someone runs a simple update and three other tables change, bulk loads slow down, and none of it shows in the application code. So I keep triggers small and documented."

"I start from the data and how it'll be read. If it's highly connected, like customers, orders, payments and stock, and I need joins, constraints and transactions across rows, a relational database is the natural fit. NoSQL isn't one thing. Document stores suit records that are read and written as a whole, like a product with nested attributes. Key-value stores are great for caches and sessions. Wide-column stores handle very high write volumes, and graph databases suit relationship-heavy queries. Many NoSQL systems scale out across machines more easily, often by giving up joins or strict consistency. For most business apps my default is relational, because modern relational databases scale further than people expect and many handle semi-structured data in JSON columns. I'd pick NoSQL for a specific access pattern, not because it sounds more scalable."

"CAP is about a data store spread over several machines. Consistency here means every read sees the most recent write, as if there were only one copy. Availability means every request to a working node gets a non-error answer. Partition tolerance means the system keeps running when messages between nodes are lost. The theorem says you can't have all three while a partition is happening. Since networks do fail, partition tolerance isn't really optional, so the real choice is what happens during a partition: refuse or delay some requests to stay consistent, or keep answering and accept that some reads may be stale. A ledger of account balances leans consistent; a shopping cart or a social feed might lean available and reconcile later. With no partition you can have both, though stronger consistency usually costs latency."

"I'd start by asking what problem we're actually seeing, because 'doesn't scale' usually means one slow page or one hot table, and sometimes it isn't the database at all. For an ordering system, the core data, orders, payments and stock, needs multi-row transactions and constraints, which is exactly what a relational database does well. Moving that to a store with weaker guarantees means rebuilding those guarantees in application code, and that's where bugs like overselling come from. Before any rewrite I'd check query plans and indexes, add caching for hot reads, add read replicas for read-heavy traffic, and consider partitioning very large tables. If one piece fits another model better, like session data in a key-value store, I'd move just that piece. And I'd propose measuring first, so we decide on numbers, not on a slogan."

"In my final-year project I designed the schema for a hostel booking system: students, rooms, beds and bookings. One decision that held up was making a bed its own table instead of just a capacity number on the room. When the warden asked to see who was in which bed and to block beds for repairs, it was a small change. The decision I'd change was storing booking status as free text. Different screens wrote Confirmed with different capitalisation, and the reports undercounted. I fixed it with a status lookup table and a foreign key, plus a script to clean the old rows. Now, any column that holds a fixed set of values gets a check constraint or a lookup table from day one, because cleaning bad data later costs far more than preventing it."

"At my last company, support noticed some customers were getting two welcome emails. I checked the users table and found pairs of rows with the same email, created a fraction of a second apart. The sign-up code checked whether the email existed and then inserted, but a double click meant both requests passed the check before either insert happened. The only guard was in the application; the table had no unique constraint on email. The fix had three parts. I wrote a script to merge the duplicates, moving their orders to the surviving account. Then I added a unique index on the lower-cased email, so the database itself refuses a second row. Finally the code catches the unique-violation error and shows a friendly message. My lesson: rules that must always hold belong in the database, not only in application checks."

"We had an order service that started throwing lock-wait timeouts every evening. Looking at the database's views of active sessions and locks, I saw a nightly job holding locks for minutes. It updated the status of every overdue order in one big transaction, so any customer request touching those orders queued behind it. We were also getting occasional deadlocks, because the job and the checkout code updated orders and inventory in opposite orders. I made two changes. The job now works in batches of a few hundred rows, each in its own short transaction, so locks are held for moments instead of minutes. And we agreed that any code touching both tables locks orders first, then inventory. The timeouts stopped and the deadlocks went away. I also added an alert on long lock waits so we'd catch it early next time."