DBMS Interview Questions and Answers

A primary key uniquely identifies each row in a table. It must be unique and not null, and a table has at most one. It may be a single column or a composite of several, and it is usually backed by a unique index. A natural key comes from the data, such as an email or ISBN; a surrogate key is generated, such as an auto-increment id or UUID.

A foreign key is a column, or set of columns, in one table that references the primary key or unique key of another. It enforces referential integrity: a child row cannot reference a parent that does not exist.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

Deletes and updates on the parent can be restricted, cascaded or set null depending on the rule. Index the foreign key column, because otherwise joins and cascade checks scan the child table. Primary keys define identity; foreign keys define relationships.

A file system stores bytes in files and directories but knows nothing about their structure. A DBMS adds a layer that understands records, types, relationships and constraints, and coordinates concurrent access.

Key advantages of a DBMS:

• Structured queries through SQL, including joins and aggregation, without hand-written parsing code.
• Integrity: primary keys, foreign keys, checks and transactions keep data consistent.
• Concurrency control: many users and transactions read and write safely with isolation guarantees.
• Recovery: write-ahead logging and backups restore a consistent state after a crash.
• Security: fine-grained privileges, roles, auditing and views.
• Abstraction and independence: applications are less coupled to physical storage.

app -> SQL -> DBMS -> storage engine -> disk

A plain file approach can be simpler and faster for tiny or single-writer workloads, such as logs or configuration. But once you need multi-user concurrency, complex queries or crash safety, reimplementing those features in application code is far more error-prone.

These three remove data at very different levels.

DELETE FROM t WHERE ... is DML. It removes selected rows, fires triggers, is fully logged, can be rolled back within a transaction and can cascade to child rows. Without a WHERE clause it removes everything, potentially slowly.

TRUNCATE TABLE t is DDL. It removes all rows quickly by deallocating pages rather than logging each row, resets identity counters in many systems, usually cannot be filtered and typically cannot be rolled back in the same way as DELETE. It is much faster for emptying a table.

DROP TABLE t removes the table definition itself plus its data, indexes, constraints and triggers. The table no longer exists until it is recreated.

DELETE FROM logs WHERE created_at < '2020-01-01';
TRUNCATE TABLE staging_events;
DROP TABLE obsolete_table;

Choose based on whether you need selective removal, speed, or removal of the object itself.