Consider a log of a transaction as shown below, where the immediate database modification scheme is used. | step | log details | |---|---| | 1 | | | 2 | | | 3 | | | 4 | | | 5 | | Table 1: log table Suppose the transaction failed before step 5, then which of the following is true?

(C) Option figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

(D) Option figure from the original question paper

Figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

(C) Option figure from the original question paper

(D) Option figure from the original question paper

Figure from the original question paper

(C) Option figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

(D) Option figure from the original question paper

Consider the Employee table given below: | EmpID | Name | Dept | Salary | |---|---|---|---| | 001 | Harry | IT | 80000 | | 002 | Louis | HR | 75000 | | 003 | Liam | Finance | 60000 | | 004 | Niall | IT | 75000 | | 005 | Zayn | IT | 68000 | Table 4: Employee Table Which of the following relational algebra expression(s) correctly retrieves the names of employees working in the IT department who earn more than 70,000?

(A) Option figure from the original question paper

(D) Option figure from the original question paper

(B) Option figure from the original question paper

(C) Option figure from the original question paper

Figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

(C) Option figure from the original question paper

(D) Option figure from the original question paper

Consider the following Entity Relationship Diagram: ER diagram with ATM Machine, ATM CARD, Bank and Customer entities and Inserted in, Provides and Uses relationships (Figure 1: ER Diagram) Choose the correct statements.

(A) A bank can provide more than one ATM machine

(B) An ATM card is used by only one customer

(D) An ATM card can be inserted in many ATM machines

(C) A customer can use only one ATM card

Figure from the original question paper

(A) Schedule S is conflict serializable.

(B) Schedule S can be two-phase lockable.

(C) Two phase lockable schedule are always serializable schedule.

(D) Schedule S is not View Serializable

Consider the SQL query to create a table Insurance and Nominee as shown below and answer the given subquestions CREATE TABLE insurance (ins_id varchar(20) primary key, policyHolderName varchar(20) not null, age int not null, premium int not null ) CREATE TABLE nominee (nominee_id varchar(20) primary key, NomineeName varchar(20), relationship varchar(20), ins_id varchar(20), foreign key (ins_id) references insurance(ins_id) ON DELETE CASCADE) The instance of the table insurance and nominee is as shown below: | ins_id | policyHolderName | age | premium | |---|---|---|---| | INS001 | Ramesh | 28 | 9800 | | INS002 | Sumesh | 29 | 8800 | | INS003 | Suresh | 33 | 12200 | Table 6: insurance | nominee_id | NomineeName | relationship | ins_id | |---|---|---|---| | NM001 | Shakti | Wife | INS001 | | NM002 | Shyam | Son | INS001 | | NM003 | Mukti | Wife | INS002 | | NM004 | Yukti | Daughter | INS003 | Table 7: nominee The following insert statements are executed in the given sequence in the same instance given as the previous question for insurance and nominee tables. insert into insurance values ('INS004', 'Rakesh', 24, 7000); insert into insurance values ('INS005', 'Sachin', 34, 10000); insert into nominee values ('NM004', 'Sonu', 'Son', 'INS004'); insert into nominee values ('NM005', 'Suman', 'Wife', 'INS006'); insert into nominee values ('NM005', 'Monu', 'Son', 'INS004'); insert into nominee(nominee_id, ins_id) values ('NM010','INS005'); What will the output of the query below be? …

(C) Option figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

(D) Option figure from the original question paper

January 2025 term · Database Management Systems · BSCS2001

Database Management Systems End Term: 13 April 2025, Set QDD1 (January 2025 term)

The IIT Madras BS Database Management Systems (DBMS) End Term paper sat on 13 Apr 2025, in the January 2025 term, set QDD1: 20 questions for 50 marks in 180 minutes. Every question is below with its answer. Take it as a timed mock test to be marked, or read it through first.

Questions: 20
Marks: 50
Duration: 180 min
MCQ: 7
MSQ: 6
Numerical: 7

Take as mock test Practise with answers

Updated 25 Sept 2026

Official paper: IIT M DIPLOMA AN EXAM QDD3 13 Apr 2025 · No negative marking.

Question 1

+2 marksOne correct option

Consider a B+-tree index to be built on the attribute StudentID of a table Students, with the following properties:

The length of the attribute StudentID is 10 bytes.
The size of each child pointer is 14 bytes.
The size of each disk block size is 470 bytes.

With the given information, what is the best choice for the order of the non-leaf nodes of the B+-tree?

A
19
B
20
C
21
D
22

Show answer

Correct answer

B
20

Question 1 on its own page

Question 2

+2 marksOne correct option

Consider a log of a transaction as shown below, where the immediate database modification scheme is used.

step	log details
1	$< T_1, start >$
2	$< T_1, A, 1000, 700 >$
3	$< T_1, B, 600, 450 >$
4	$< T_1, C, 800, 1300 >$
5	$< T_1, Commit >$

Table 1: log table

Suppose the transaction failed before step 5, then which of the following is true?

Show answer

Correct answer

Question 2 on its own page

Question 3

+2 marksOne correct option

Consider the following monthly backup schedule used by a company:

Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
1/ Full	2/ Incremental	3/ Incremental	4/ Incremental	5/ Incremental	6/ Incremental	7/ Differential
8/ Incremental	9/ Incremental	10/ Incremental	11/ Incremental	12/ Differential	13/ Incremental	14/ Incremental
15/ Incremental	16/ Incremental	17/ Differential	18/ Incremental	19/ Incremental	20/ Incremental	21/ Incremental
22/ Differential	23/ Incremental	24/ Incremental	25/ Incremental	26/ Incremental	27/ Incremental	28/ Incremental
29/ Incremental	30/ Incremental

If a failure occurs on the 12th day of the month before the backup for the day has been completed, how many backup sets have to be loaded for a full recovery?

Show answer

Correct answer

Question 3 on its own page

Question 4

+2 marksOne correct option

Show answer

Correct answer

Question 4 on its own page

Question 5

+3 marksOne correct option

Show answer

Correct answer

Question 5 on its own page

Question 6

+3 marksOne correct option

Consider the following relational schema:
Employee(emp_id, first_name, last_name, dept_id)
Department(dept_id, dept_name, mngr_id)
Manager(mngr_id, mngr_name)

Questions:

Find the first names of employees whose names start with 'J' and contain at least 6 characters.
Find the total number of employees in each department.
List the department names managed by a manager named 'Brown'.

SQL queries:

sql

1a. SELECT first_name FROM Employee WHERE first_name LIKE 'J_____';
2b. SELECT first_name FROM Employee WHERE first_name LIKE 'J_____%';
3c. SELECT dept_id, COUNT(emp_id) FROM Employee GROUP BY emp_id;
4d. SELECT dept_id, COUNT(emp_id) FROM Employee GROUP BY dept_id;
5e. SELECT DISTINCT d.dept_name FROM Department d INNER JOIN Manager m
6   ON d.mngr_id = m.mngr_id WHERE m.mngr_name = 'Brown';
7f. SELECT DISTINCT d.dept_name FROM Department d, Manager m WHERE
8   d.mngr_id = m.mngr_id AND m.mngr_name = 'Brown';

Match the correct SQL queries with the corresponding Questions.

A
1-a, 2-c, 3-f
B
1-b, 2-c, 3-e
C
1-a, 2-d, 3-e
D
1-b, 2-d, 3-f

Show answer

Correct answer

D
1-b, 2-d, 3-f

Question 6 on its own page

Question 7

+1 markOne or more correct options

Choose the correct statement(s).

Select all that apply.

A
In static hashing, the size of the hash table remains fixed, which may lead to overflow or underutilization of space.
B
In dynamic hashing, the hash table grows and shrinks dynamically as data is inserted or deleted.
C
Static hashing is more efficient for applications where the number of records is expected to change frequently.
D
Dynamic hashing does not handle collisions effectively compared to static hashing.

Show answer

Correct answers

A
In static hashing, the size of the hash table remains fixed, which may lead to overflow or underutilization of space.
B
In dynamic hashing, the hash table grows and shrinks dynamically as data is inserted or deleted.

Question 7 on its own page

Question 8

+2 marksOne or more correct options

Consider the instance of a relation R. Which among the following functional dependencies set(s) can be inferred from the given information?

P	Q	R	S
p1	q1	r1	s1
p2	q2	r2	s2
p3	q3	r3	s3
p1	q1	r1	s1
p4	q4	r4	s4
p2	q1	r2	s2

Table 2: Relation R

Select all that apply.

Show answer

Correct answer

Question 8 on its own page

Question 9

+2 marksOne or more correct options

Consider the Employee table given below:

EmpID	Name	Dept	Salary
001	Harry	IT	80000
002	Louis	HR	75000
003	Liam	Finance	60000
004	Niall	IT	75000
005	Zayn	IT	68000

Table 4: Employee Table

Which of the following relational algebra expression(s) correctly retrieves the names of employees working in the IT department who earn more than 70,000?

Select all that apply.

Show answer

Correct answers

Question 9 on its own page

Question 10

+2 marksOne or more correct options

Select all that apply.

Show answer

Correct answers

Question 10 on its own page

Question 11

+2 marksOne or more correct options

Consider the following Entity Relationship Diagram:

Choose the correct statements.

Select all that apply.

A
A bank can provide more than one ATM machine
B
An ATM card is used by only one customer
C
A customer can use only one ATM card
D
An ATM card can be inserted in many ATM machines

Show answer

Correct answers

A
A bank can provide more than one ATM machine
B
An ATM card is used by only one customer
D
An ATM card can be inserted in many ATM machines

Question 11 on its own page

Question 12

+3 marksOne or more correct options

Select all that apply.

A
Schedule S is conflict serializable.
B
Schedule S can be two-phase lockable.
C
Two phase lockable schedule are always serializable schedule.
D
Schedule S is not View Serializable

Show answer

Correct answers

A
Schedule S is conflict serializable.
B
Schedule S can be two-phase lockable.
C
Two phase lockable schedule are always serializable schedule.

Question 12 on its own page

Question 13

+3 marksNumerical answer

Consider you have a file in your hard disk of size 1000 KB. Seek time of your hard disk read head is 3ms and rotational speed in 30,000 rpm. The disk has 200 sectors per track and 512 bytes per sector. Considering the fact that the file data is stored in non consecutive sectors. How much time will be required to read the whole file after a read request is made? (in ms)

Show answer

Correct answer: 24

Question 13 on its own page

Question 14

+3 marksNumerical answer

Consider a system using the Least Recently Used (LRU) page replacement policy. The system has a main memory buffer with 4 slots, and the page reference sequence is as follows:
3, 4, 1, 4, 2, 3, 1, 4, 2, 3
Calculate the total number of page misses (page faults) during this sequence.

Show answer

Correct answer: 4

Question 14 on its own page

Question 15

+3 marksNumerical answer

Consider the given log records at an instance of time:

Table 5: Log records

$< T_0 \text{ start} >$
$< T_0, A, 100, 200 >$
$< T_1 \text{ start} >$
$< T_1, B, 400, 300 >$
$< T_0, C, 500, 600 >$
$< T_2 \text{ start} >$
$< T_2, D, 800, 700 >$
$< Commit\ T_0 >$
$< Checkpoint\ L >$
$< T_2, C, 500, 1000 >$
$< T_1, A, 100, 350 >$
$< Commit\ T_2 >$
$< T_1, D, 800, 500 >$

Suppose there is a system crash after the last log record. What will be the value of the expression (B-A)+(C-D), based on the values stored on the disk at that point?

Show answer

Correct answer: 500

Question 15 on its own page

Question 16

+3 marksNumerical answer

Show answer

Correct answer: 24

Question 16 on its own page

Question 17

+3 marksNumerical answer

Show answer

Correct answer: 5

Question 17 on its own page

Question 18

+3 marksNumerical answer

Consider a Block nested loop join for the two relations, instructor and department. Assuming the worst-case memory availability and instructor as the outer relation, the provided details are as follows:

Total number of block transfers: 10500
Total number of seeks required: 500
Number of block in the outer relation: 250

What is the number of blocks in the inner relations?

Show answer

Correct answer: 41

Question 18 on its own page

Question 19

+3 marksNumerical answer

Consider the SQL query to create a table Insurance and Nominee as shown below and answer the given subquestions

sql

1CREATE TABLE insurance (ins_id varchar(20) primary key,
2policyHolderName varchar(20) not null,
3age int not null, premium int not null )
4
5CREATE TABLE nominee (nominee_id varchar(20) primary key,
6NomineeName varchar(20), relationship varchar(20), ins_id
7varchar(20), foreign key (ins_id) references
8insurance(ins_id) ON DELETE CASCADE)

The instance of the table insurance and nominee is as shown below:

ins_id	policyHolderName	age	premium
INS001	Ramesh	28	9800
INS002	Sumesh	29	8800
INS003	Suresh	33	12200

Table 6: insurance

nominee_id	NomineeName	relationship	ins_id
NM001	Shakti	Wife	INS001
NM002	Shyam	Son	INS001
NM003	Mukti	Wife	INS002
NM004	Yukti	Daughter	INS003

Table 7: nominee

Show answer

Correct answer: 4

Question 19 on its own page

Question 20

+3 marksOne correct option

Consider the SQL query to create a table Insurance and Nominee as shown below and answer the given subquestions

sql

1CREATE TABLE insurance (ins_id varchar(20) primary key,
2policyHolderName varchar(20) not null,
3age int not null, premium int not null )
4
5CREATE TABLE nominee (nominee_id varchar(20) primary key,
6NomineeName varchar(20), relationship varchar(20), ins_id
7varchar(20), foreign key (ins_id) references
8insurance(ins_id) ON DELETE CASCADE)

The instance of the table insurance and nominee is as shown below:

ins_id	policyHolderName	age	premium
INS001	Ramesh	28	9800
INS002	Sumesh	29	8800
INS003	Suresh	33	12200

Table 6: insurance

nominee_id	NomineeName	relationship	ins_id
NM001	Shakti	Wife	INS001
NM002	Shyam	Son	INS001
NM003	Mukti	Wife	INS002
NM004	Yukti	Daughter	INS003

Table 7: nominee

The following insert statements are executed in the given sequence in the same instance given as the previous question for insurance and nominee tables.

sql

1insert into insurance values ('INS004', 'Rakesh', 24, 7000);
2insert into insurance values ('INS005', 'Sachin', 34, 10000);
3insert into nominee values ('NM004', 'Sonu', 'Son', 'INS004');
4insert into nominee values ('NM005', 'Suman', 'Wife', 'INS006');
5insert into nominee values ('NM005', 'Monu', 'Son', 'INS004');
6insert into nominee(nominee_id, ins_id) values ('NM010','INS005');

What will the output of the query below be?

sql

1SELECT * FROM nominee

Show answer

Correct answer

Question 20 on its own page