Figure from the original question paper

(B) Option figure from the original question paper

(A) 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 relation players(Name, Team, Coach, Runs) with the data: Figure from the original question paper Check whether the relation players is in 4NF or not. If not, then decomposed it into\ 4NF.\ Choose the correct option.

(B) Option figure from the original question paper

(A) 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 given grocery table which represents the items purchased while grocery shopping. Grocery | grocery_item | quantity | value | discount | |---|---|---|---| | Cereal | 3 | 400 | 10 | | Chips | 4 | 200 | 5 | | Milk | 2 | 300 | 0 | | Banana | 10 | 350 | 5 | | Tomato | 5 | 200 | 5 | Figure 2: grocery Which among the following query will fetch the output given below? | grocery_item | quantity | |---|---| | Milk | 2 | | Banana | 10 | | Cereal | 3 |

(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 given schedules S1 and S2 which performs independent tasks. | T1 | T2 | |---|---| | | Lock-X(A) | | | Write-(A) | | | Lock-S(B) | | Lock-S(B) | Read-(B) | | Read-(B) | Unlock-(B) | | | Commit | | | Unlock-(A) | | Lock-X(A) | | | Write-(A) | | | Commit | | | Unlock-(A) | | | Unlock-(B) | | Table 8: Schedule S1 | T1 | T2 | |---|---| | | Lock-S(A) | | | Read-(A) | | Lock-X(B) | | | Read-(B) | | | Write-(B) | | | Unlock-(B) | | | commit | | | | Unlock-(A) | | | commit | Table 9: Schedule S2 Which among of the following statement is correct?

(D) Only S1 follows Strict two-phase locking protocol.

(A) Both S1 and S2 follow the rigorous two-phase locking protocol.

(B) Both S1 and S2 follow the Strict two-phase locking protocol.

(C) Only S2 follows Strict two-phase locking protocol.

Consider the relation as shown in the table : | customer_name | |---| | Akash | | Akshay | | A | | A.K. | | Aksh | | Ak | | Ak.S. | | Aks | Table 11: customer\ What is the output of the below SQL query? SELECT customer_name FROM customer WHERE customer_name LIKE 'Ak_%' ORDER BY customer_name DESC FETCH FIRST 3 ROWS ONLY

(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

(D) Option figure from the original question paper

(B) Option figure from the original question paper

(C) Option figure from the original question paper

(E) Option figure from the original question paper

Figure from the original question paper

(A) The decomposition is lossless

Consider a state of transactions as shown in Figure 3. Timeline of transactions T1 to T5 relative to a Checkpoint and a System Failure (Figure 3: State of Transactions) According to the above figure, which among the following statement(s) is/are correct?

(B) The transactions T3 and T4 needs to be redone

(D) The only transaction that needs to be undone is T5.

(A) The transactions T1 and T2 can be ignored.

(C) The only transaction that needs to be undone are T4 and T5.

Figure from the original question paper

(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

Consider two logs of transaction as shown below, where immediate database modification scheme is used in Table 1 and deferred database modification scheme is used in Table 2. | Steps | Details of log | |---|---| | 1 | | | 2 | | | 3 | | | 4 | | | 5 | | | 6 | | Table 1: Immediate Database Modification Scheme | Steps | Details of log | |---|---| | 1 | | | 2 | | | 3 | | | 4 | | | 5 | | Table 2: Deferred Database Modification Scheme In Table 1 \& Table 2, if a system crash occurs after step 6 and step 5 respectively and the recovery of the system is successfully completed, identify the correct actions from the above diagram.

(A) After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1600 and 3000 respectively.

(D) In Table 1, {T0} needs to be redone and {T1} needs to be undone.

(B) In Table 1, {T0} needs to be undone and {T1} needs to be redone.

(C) After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1700 and 2500 respectively.

May 2022 term · Database Management Systems · BSCS2001

DBMS End Term: 7 August 2022, Set QPB1 (May 2022 term)

The IIT Madras BS Database Management Systems (DBMS) End Term paper sat on 7 Aug 2022, in the May 2022 term, set QPB1: 25 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: 25
Marks: 50
Duration: 180 min
MCQ: 16
MSQ: 7
Numerical: 2

Take as mock test Practise with answers

Updated 25 Sept 2026

Official paper: IIT M FOUNDATION DIPLOMA ENDTERM QPD1 07 Aug 2022 IBA NS · No negative marking.

Question 1

+1 markOne correct option

Show answer

Correct answer

Question 1 on its own page

Question 2

+1 markOne correct option

Consider a relational schema student(roll_no, name, class).
In order to change the data type of roll_no, which of the following category of SQL command is used for this purpose?

A
DML
B
TCL
C
DDL
D
DCL

Show answer

Correct answer

C
DDL

Question 2 on its own page

Question 3

+1 markOne correct option

A role Department_Lead has the privilege of select, insert and delete on all the tables of the database. A new role Employee1 is created, and the following statement is executed. grant Department_Lead to Employee1
What privileges will be inherited by Employee1?

A
All privileges - select, insert and delete
B
Only update
C
Only select and insert
D
Only delete and update

Show answer

Correct answer

A
All privileges - select, insert and delete

Question 3 on its own page

Question 4

+1 markOne correct option

Consider the following statements:
Statement 1: System Buffer blocks are those blocks residing on the disk.
Statement 2: Physical blocks are the blocks residing temporarily in main memory.
Statement 3: Immediate modification scheme allows updates of an uncommitted transaction to be made to the buffer/disk only at the time of the transaction commit.
Statement 4: Deferred modification scheme allows updates of an uncommitted transaction to be made to the buffer/disk only at the time of the transaction commit.
Which among the given statements is/are incorrect?

A
Statement 1 & 4
B
Statement 1, 2 & 3
C
Statement 2 & 4
D
Only statement 3

Show answer

Correct answer

B
Statement 1, 2 & 3

Question 4 on its own page

Question 5

+2 marksOne correct option

Show answer

Correct answer

Question 5 on its own page

Question 6

+2 marksOne correct option

Consider the relation players(Name, Team, Coach, Runs) with the data:

Check whether the relation players is in 4NF or not. If not, then decomposed it into
4NF.
Choose the correct option.

Show answer

Correct answer

Question 6 on its own page

Question 7

+2 marksOne correct option

Consider the following schedule S with four transactions T1, T2, T3,T4:
S: R3(A);W2(A);R1(A);W1(A);R3(B);W4(B);
Where, Ri(A) denotes a read operation by transaction Ti on a data item A, Wi(A) denotes a write operation by transaction Ti on a data item A.
What is the possible number of conflict serializable schedule of the above schedule S.

Show answer

Correct answer

Question 7 on its own page

Question 8

+2 marksOne correct option

Consider the given relations:
instructor*(ID, name, dept_name, salary)
department(dept_name, building, budget)*
Identify the most optimized expression tree from the given options that find the name of instructor whose salary is greater than 70000 and department building is ’Taylor’.

Show answer

Correct answer

Question 8 on its own page

Question 9

+2 marksOne correct option

Consider the relation election(voter_id, voter_name, age, candidate_vote_for). The number of tuples in the election relation is 1024. The attribute candidate_vote_for has 7 different values and the attribute age has four different values(i.e., below 25 years, 25 to below 45, 45 to below 60 and 60 to above). Construct a bitmap index for relation election on the attribute candidate_vote_for. What is the size of the bitmap index file?

A
7168 bytes
B
4096 bytes
C
896 bytes
D
11264 bytes

Show answer

Correct answer

C
896 bytes

Question 9 on its own page

Question 10

+2 marksOne correct option

Consider the smartphone relation as shown below:
smartphone(IMEI, brand, color, price)
(Note: IMEI is the unique number of each smartphone.)
Construct a B+ tree indexing of order 8 using search key as IMEI. If the size of the block is 1024 bytes. IMEI attribute is of varchar datatype and size is 64 bytes. What is the size of the tree pointer of the B+ tree?

A
64 bytes
B
72 bytes
C
82 bytes
D
128 bytes

Show answer

Correct answer

B
72 bytes

Question 10 on its own page

Question 11

+2 marksOne correct option

Consider a multilevel index with four levels L1, L2, L3, L4. L4 is the innermost level and L1 is the outermost level. The disk block size is 1024 bytes. The size of one entry in the index file is 16 bytes. The number of entries in the L4 is 2²⁴. Then, find the total number of blocks in the L2 and L3 levels in the multilevel index.

A
64
B
4096
C
4160
D
8256

Show answer

Correct answer

C
4160

Question 11 on its own page

Question 12

+2 marksOne correct option

Consider the given grocery table which represents the items purchased while grocery shopping.

Grocery

grocery_item	quantity	value	discount
Cereal	3	400	10
Chips	4	200	5
Milk	2	300	0
Banana	10	350	5
Tomato	5	200	5

Figure 2: grocery

Which among the following query will fetch the output given below?

grocery_item	quantity
Milk	2
Banana	10
Cereal	3

Show answer

Correct answer

Question 12 on its own page

Question 13

+3 marksOne correct option

Consider the following case study:
An IITM Online Degree Project needs a database to store information about instructors (identified by ins_id, name and phone as attributes), courses (identified by cid, cname and credits as attributes), and health information (identified by health_id and checkup_date as attributes). The instructor can work in various courses and courses can have more than one instructor; each course is managed by an instructor and an instructor can manage more than one courses. A health information must be identified uniquely by health_id when the instructor is known. We are not interested in information about a health record once the instructor leaves the organization. Choose the correct ER-Diagram based on the given information.

Show answer

Correct answer

Question 13 on its own page

Question 14

+3 marksOne correct option

Consider the given schedules S1 and S2 which performs independent tasks.

T1	T2
	Lock-X(A)
	Write-(A)
	Lock-S(B)
Lock-S(B)	Read-(B)
Read-(B)	Unlock-(B)
	Commit
	Unlock-(A)
Lock-X(A)
Write-(A)
Commit
Unlock-(A)
Unlock-(B)

Table 8: Schedule S1

T1	T2
	Lock-S(A)
	Read-(A)
Lock-X(B)
Read-(B)
Write-(B)
Unlock-(B)
commit
	Unlock-(A)
	commit

Table 9: Schedule S2

Which among of the following statement is correct?

A
Both S1 and S2 follow the rigorous two-phase locking protocol.
B
Both S1 and S2 follow the Strict two-phase locking protocol.
C
Only S2 follows Strict two-phase locking protocol.
D
Only S1 follows Strict two-phase locking protocol.

Show answer

Correct answer

D
Only S1 follows Strict two-phase locking protocol.

Question 14 on its own page

Question 15

+3 marksOne correct option

Consider the relation as shown in the table :

customer_name
Akash
Akshay
A
A.K.
Aksh
Ak
Ak.S.
Aks

Table 11: customer
What is the output of the below SQL query?

sql

1SELECT customer_name
2FROM customer
3WHERE customer_name LIKE 'Ak_%'
4ORDER BY customer_name DESC
5FETCH FIRST 3 ROWS ONLY

Show answer

Correct answer

Question 15 on its own page

Question 16

+3 marksOne correct option

Consider a RAID-4 system with 5 disks which stores the following data shown in Figure 4:

Disk-1	Disk-2	Disk-3	Disk-4	Disk-5
0100	1000	---	0001	1001	Block A
0100	1101	---	1101	0000	Block B
0100	0101	---	0010	0110	Block C

Figure 4: RAID-4 data

According to the figure, Disk-3 has crashed. Identify the correct data present in the three blocks of Disk-3. Also, assuming that the binary values represent 8 bit ASCII code, identify the correct data word present inside the RAID-4 storage system. Consider the following statements and identify the correct statements.
1. Block A: 0100, Block B: 0100, Block C: 0101
2. Block A: 1011, Block B: 1011, Block C: 1010
3. Block A: 0100, Block B: 1011, Block C: 1010
4. The word is: ’HAMMER’
5. The word is : ’hammer’
Note:
• Assuming block size is 4 bits.
• Disk-5 is the parity disk.
• The ASCII value of ’A’ is 65 and ’a’ is 97.

A
Statements 2 & 4
B
Only statement 3
C
Statements 1 & 4
D
Statements 3 & 5

Show answer

Correct answer

C
Statements 1 & 4

Question 16 on its own page

Question 17

+2 marksOne or more correct options

Select all that apply.

Show answer

Correct answers

Question 17 on its own page

Question 18

+2 marksOne or more correct options

Choose the correct sets of functional dependencies for the relation R(A,B,C,D) under which R is in 2NF but not in 3NF.

Select all that apply.

Show answer

Correct answer

Question 18 on its own page

Question 19

+2 marksOne or more correct options

Select all that apply.

A
The decomposition is lossless
B
B → C
C
B → A
D
D → C
E
D → A

Show answer

Correct answers

B
B → C
C
B → A
D
D → C
E
D → A

Question 19 on its own page

Question 20

+2 marksOne or more correct options

Consider the following schedule S with three transactions T1, T2 and T3:
S: R2(A);W2(A);C2;R3(A);W3(A);C3;R1(B);W1(B);C1;
Where Ri(A) denotes a read operation by transaction Ti on a data item X, Wi(A) denotes a write operation by transaction Ti on a data item A, Ci denotes a commit by Ti.
Which among of the following statement(s) is/are correct?

Select all that apply.

A
S is a recoverable schedule.
B
S is a non recoverable schedule.
C
S is a cascading rollback
D
S is a cascadeless schedule

Show answer

Correct answers

A
S is a recoverable schedule.
D
S is a cascadeless schedule

Question 20 on its own page

Question 21

+2 marksOne or more correct options

Consider a state of transactions as shown in Figure 3.

According to the above figure, which among the following statement(s) is/are correct?

Select all that apply.

A
The transactions T1 and T2 can be ignored.
B
The transactions T3 and T4 needs to be redone
C
The only transaction that needs to be undone are T4 and T5.
D
The only transaction that needs to be undone is T5.

Show answer

Correct answers

B
The transactions T3 and T4 needs to be redone
D
The only transaction that needs to be undone is T5.

Question 21 on its own page

Question 22

+3 marksOne or more correct options

Select all that apply.

Show answer

Correct answers

Question 22 on its own page

Question 23

+3 marksOne or more correct options

Consider two logs of transaction as shown below, where immediate database modification scheme is used in Table 1 and deferred database modification scheme is used in Table 2.

Steps	Details of log
1	$< T_0, start >$
2	$< T_0, P, 700, 500 >$
3	$< T_1, start >$
4	$< T_1, Q, 1300, 1000 >$
5	$< checkpoint\ \{T_0, T_1\} >$
6	$< T_0, commit >$

Table 1: Immediate Database Modification Scheme

Steps	Details of log
1	$< T_2, start >$
2	$< T_2, A, 1700, 1600 >$
3	$< T_2, commit >$
4	$< T_3, start >$
5	$< T_3, B, 3000, 2500 >$

Table 2: Deferred Database Modification Scheme

In Table 1 & Table 2, if a system crash occurs after step 6 and step 5 respectively and the recovery of the system is successfully completed, identify the correct actions from the above diagram.

Select all that apply.

A
After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1600 and 3000 respectively.
B
In Table 1, {T0} needs to be undone and {T1} needs to be redone.
C
After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1700 and 2500 respectively.
D
In Table 1, {T0} needs to be redone and {T1} needs to be undone.

Show answer

Correct answers

A
After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1600 and 3000 respectively.
D
In Table 1, {T0} needs to be redone and {T1} needs to be undone.

Question 23 on its own page

Question 24

+1 markNumerical answer

Consider the following schedule S with three transactions T1, T2 and T3:
S: R2(B);R1(B);R1(A);W1(A);R3(C);W3(C);
The number of serial schedule for given schedule S is________

Show answer

Correct answer: 6

Question 24 on its own page

Question 25

+1 markNumerical answer

Construct a binary search tree by inserting the following values in the following order 15, 14, 20, 10, 23, 17, 16, 2, 1, 6. What is the sum of all leaf nodes of the constructed binary search tree?

Show answer

Correct answer: 46

Question 25 on its own page