MCQ

Paper - II (xi) — Database Management System MCQ - Practice Questions with Answers

Solve 44 Paper - II (xi) — Database Management System questions for RAS/RPSC preparation.

Practice questions

Q1For R(A, B, C) with F = {AB -> C, C -> B}, which statement about normal forms and decomposition is correct?

A R is in BCNF because every right-hand side attribute is prime.

B R is in 3NF but not in BCNF, and the BCNF decomposition into (C, B) and (A, C) need not preserve AB -> C.

C R is not in 3NF because C -> B has a non-key determinant.

D Decomposing into R1(A, B) and R2(B, C) is guaranteed lossless because B is common.

Explanation

The candidate keys are AB and AC: AB determines C directly, and AC determines B through C -> B. Thus A, B, and C are all prime attributes. The dependency C -> B has a determinant that is not a superkey, so BCNF is violated, but its right-hand side B is prime, so 3NF is satisfied. A BCNF split into CB and AC is lossless on C, yet AB -> C is not derivable from the projected dependencies alone.

Q2A composite B-tree index is defined on (district_id, exam_date, candidate_id). Which predicate is expected to use the index most efficiently for ordered lookup under the usual left-prefix rule?

A WHERE district_id = 17 AND exam_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-30'

B WHERE exam_date = DATE '2026-06-05' AND candidate_id = 9001

C WHERE candidate_id = 9001

D WHERE exam_date = DATE '2026-06-05'

Explanation

In a multicolumn B-tree, the leading column matters most for ordered search. Equality on district_id narrows the first segment of the index, and a range on exam_date can then be scanned within that segment; predicates that begin from the second or third column are weaker matches.

Q3Which statement about two-phase locking (2PL), strict 2PL, and transaction schedules is correct?

A Basic 2PL permits a transaction to acquire new locks after it has released one lock, provided the new lock is shared.

B A recoverable schedule is necessarily conflict serializable, because no transaction commits before the transaction it read from.

C Basic 2PL guarantees conflict serializability, but it can still allow cascading aborts if exclusive locks are released before commit.

D Strict 2PL prevents deadlock because every transaction releases all locks only after it commits or aborts.

Explanation

Under 2PL, each transaction has a growing phase for lock acquisition and a shrinking phase after it starts releasing locks; this guarantees conflict serializability. However, basic 2PL does not require write locks to be held until commit. Strict 2PL adds that discipline for exclusive locks, which prevents dirty reads and cascading aborts, but deadlocks still remain possible.

Q4Which tuple relational calculus expression is unsafe because its result can range over tuples outside the finite database instance?

A {t | Student(t) and t.age > 21}

B {t | Student(t) and exists e (Enrol(e) and e.sid = t.sid)}

C {t | not Student(t)}

D {t | Student(t) and not exists e (Enrol(e) and e.sid = t.sid)}

Explanation

A safe relational calculus query must produce a finite result determined by the active domain of the database. In {t | not Student(t)}, the free variable t is not positively range-restricted by any relation. It may denote arbitrary tuples that do not occur in Student, making the result unbounded and not equivalent to a finite relational algebra query.

Q5Two transactions run concurrently on accounts A and B, each reading both balances and then updating a different account so that a cross-row constraint is violated only when both commit. Which phenomenon is this, and which isolation level is intended to rule it out in the SQL standard model?

A Write skew, ruled out by SERIALIZABLE isolation

B Non-repeatable read, ruled out by making all constraints deferred

C Lost update, ruled out only by adding a secondary index

D Dirty read, ruled out first at READ COMMITTED

Explanation

Write skew occurs when transactions read overlapping data, update different rows, and together break an invariant even though neither overwrites the other. Serializable isolation is the standard-level guarantee that concurrent execution has an equivalent serial order, so one transaction must be blocked or aborted in such a conflict pattern.

You've seen 5 of 44 sample questions

Unlimited practice on Paper - II (xi) — Database Management System comes with the RAS Test Series + Practice pack or Gate Pass.

More questions

6A table EMP(emp_id, manager_id, salary) must enforce the rule: an employee's salary must not exceed the salary of the employee referenced by manager_id, except when manager_id is NULL. Which implementation is the most appropriate in a typical SQL RDBMS?

AA trigger or assertion-like mechanism that checks the referenced manager row during relevant INSERT or UPDATE operations.

BA CHECK constraint on EMP comparing salary with the manager's salary through a subquery on EMP.

CA foreign key from manager_id to emp_id alone, because referential integrity also enforces dependent salary rules.

DA UNIQUE constraint on manager_id, because each manager can then have only one subordinate salary to compare.

7An ER diagram has entity set Employee with primary key emp_id and a weak entity set Dependent with partial key dep_name. Dependent has total participation in an identifying relationship with Employee. Which relational design best preserves the weak-entity semantics?

ADependent(dep_id, emp_id, dep_name, ...), with dep_id as the only key and emp_id optional

BEmployee(emp_id, dep_name, ...), with one dep_name column added to Employee

CDependent(emp_id, dep_name, ...), with primary key (emp_id, dep_name) and emp_id as a foreign key to Employee

DDependent(dep_name, ...), with dep_name as the primary key and no emp_id column

8For the schedule S: r1(X), r2(X), w1(X), r3(Y), w2(X), w3(Y), c1, c2, c3, which statement is correct under conflict-serializability analysis?

AS is not conflict-serializable only because T3 writes Y.

BS is conflict-serializable because all commits occur after all writes.

CS is not conflict-serializable because the precedence graph has T1 -> T2 and T2 -> T1.

DS has only the edge T1 -> T2, so it is equivalent to T1, T2, T3.

9A database trigger must validate each inserted row using the row's new values before the row is physically stored, and must be able to modify a derived column in that same row. Which trigger timing and granularity best match this requirement?

ABEFORE INSERT FOR EACH ROW

BAFTER INSERT FOR EACH STATEMENT

CAFTER INSERT FOR EACH ROW

DINSTEAD OF INSERT FOR EACH STATEMENT on the base table

10Consider the schedule S: r1(X), r2(X), w1(X), w2(X), where ri and wi denote read and write by transaction Ti. Which conclusion is correct?

AS is not conflict-serializable because the precedence graph contains both T1 -> T2 and T2 -> T1.

BS is conflict-serializable with serial order T1 followed by T2.

CS is conflict-serializable because both transactions access only one data item.

DS is conflict-serializable only if both writes are ignored while building the graph.

11In an E-R design for a university database, Section is identified by (course_id, semester, year, sec_id), and every Section must belong to exactly one Course. If sec_id is unique only within a given course offering period, which modelling choice is most appropriate before mapping to relations?

AModel Section as a weak entity with partial key sec_id, identified through Course and the offering-period attributes.

BModel Section as a strong entity with sec_id as its primary key.

CReplace the relationship by a multivalued attribute sections on Course.

DModel Course as a weak entity owned by Section.

12A table Employee(emp_id, dept_id, salary, city) has a composite B-tree index on (dept_id, salary). Which query pattern is most directly supported by the leftmost-prefix property of this index?

AWHERE salary = 70000 AND dept_id > 20

BWHERE city = 'Jaipur' AND salary > 50000

CWHERE salary BETWEEN 50000 AND 80000

DWHERE dept_id = 10 AND salary BETWEEN 50000 AND 80000

13Consider the tuple relational calculus formula { t.A | R(t) OR NOT R(t) }. In a conventional database theory setting, why is this formula rejected as an unsafe query?

AIt contains a negation operator, and tuple calculus never permits negation

BThe free variable t is not range-restricted to the active database domain, so the result may depend on the underlying infinite domain

CIt is equivalent to an empty relation for every possible database instance

DProjection on a single attribute is not expressible in tuple relational calculus

14Let Enrolled(student_id, course_id) and Required(course_id) be relations. Which relational algebra expression returns exactly those students who are enrolled in every required course?

Api_student_id(Enrolled join Required)

Bpi_student_id(Required join Enrolled) - pi_student_id(Required)

Cpi_student_id(Enrolled - Required)

Dpi_student_id(Enrolled) - pi_student_id((pi_student_id(Enrolled) x Required) - Enrolled)

15Which statement about strict two-phase locking (strict 2PL) is most accurate for transaction processing?

AIt eliminates all deadlocks because every lock is released only at commit.

BIt allows a transaction to release exclusive locks before commit as long as it does not request new locks later.

CIt guarantees conflict serializability and avoids cascading aborts by holding write locks until the transaction ends.

DIt is weaker than timestamp ordering because it permits dirty reads by design.

Paper - II (xi) — Database Management System MCQ - Practice Questions with Answers

Practice questions

You've seen 5 of 44 sample questions

More questions

More topics in DBMS & Software Engineering (Senior CI)

Explore other subjects