Database Management Systems (DBMS): 19 MCQs with answers

A database is the data; a DBMS is the software managing it.

A database is the organized collection of data itself, while a DBMS is the software that defines, stores, retrieves, and manages that data.

Physical level

The physical level deals with files, blocks, and indexes on disk. The logical level describes tables and relationships, and the view level shows customized slices to users.

Data independence

Data independence, provided by the three-level architecture, lets you change physical storage without breaking applications or user views.

Atomicity

ACID stands for Atomicity, Consistency, Isolation, Durability. Atomicity means a transaction is all-or-nothing.

DDL

DDL (Data Definition Language) defines structure and includes CREATE, ALTER, and DROP. DML works with data, DCL controls access, and TCL manages transactions.

Primary key

A primary key uniquely identifies each row. A foreign key references a primary key in another table to create a relationship.

True

ACID properties (Atomicity, Consistency, Isolation, Durability) are the guarantees that make DBMS transactions reliable under crashes and concurrent access.

Referential integrity

Referential integrity enforces foreign key constraints, preventing orphan records. Since Enrollment.student_id references Student.student_id, a non-existent student is rejected.

Isolation

Isolation prevents concurrent transactions from interfering with each other, so simultaneous withdrawals do not double-spend.

Update anomaly

Normalization makes each fact live in exactly one place, preventing update anomalies where a change is applied to some copies but not others.

Parsing → optimization → execution → storage access

The parser checks syntax and builds a parse tree, the optimizer chooses the cheapest plan, the execution engine runs it, and the storage manager handles files, pages, and indexes.

2NF

2NF removes partial dependencies — a non-key attribute depending on only part of a composite key. Here student_name depends on student_id alone, violating 2NF.

You specify what you want, and the DBMS decides how to get it.

In SQL you state what result you want; the query optimizer figures out the how by choosing an execution plan.

The DBA is the human administrator; the DBMS is the software tool.

The DBA is the human role that installs, configures, backs up, tunes, and secures the DBMS. The DBMS is the tool; the DBA is the person using it.

The optimizer chose a more expensive execution plan.

The same query can run in milliseconds or minutes depending on the plan chosen. The optimizer's choice of index and join order is the critical performance factor.

Graph NoSQL DBMS

Graph stores such as Neo4j are designed for relationship-heavy data. A relational DBMS with a junction table can model many-to-many links but is not optimized for traversing deep relationship graphs.

Analyze the execution plan generated by the query optimizer to identify inefficiencies.

Analyzing the execution plan generated by the query optimizer allows the DBA to identify inefficiencies in how the query is being executed, which is crucial for diagnosing performance issues.

Change the foreign key constraints to allow null values.

Allowing null values in foreign key constraints enables the deletion of records without violating referential integrity, as it permits the foreign key to point to no record.

Repeatable Read

The Repeatable Read isolation level ensures that if a transaction reads a value, it will see the same value throughout its execution, preventing it from seeing intermediate states caused by concurrent updates.