Consider the relation players(Name, Team, Coach, Runs) with the data: | Name | Team | Coach | Runs | |---|---|---|---| | Sharma | CSK | Steven | 99 | | Sharma | MI | Mahela | 50 | | Sharma | MI | Mahela | 99 | | Sharma | CSK | Steven | 50 | | Khan | RCB | S K Joseph | 45 | | Khan | GT | Matthew | 80 | | Khan | GT | Matthew | 45 | | Khan | RCB | S K Joseph | 80 | Table 1: players Check whether the relation players is in 4NF or not. If not, then decomposed it into 4NF. Choose the correct option.

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

(D) Option 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

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

(C) Only S2 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.

(D) Only S1 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 Which of the following SQL statement gives the result given below? 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

Figure from the original question paper

(A) Option figure from the original question paper

(C) Option figure from the original question paper

(B) Option figure from the original question paper

(D) 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 2. Timeline of transactions T1 to T5 relative to a checkpoint and a system failure (Figure 2: State of Transactions) According to the above figure, which among the following statement(s) is/are incorrect?

(C) The transactions T1 and T3 can be ignored.

(A) The transaction T4 can be ignored.

(B) The transactions that need to be undone are T2 and T5.

(D) The transactions that need to be redone are T1 and T3.

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 | | | 6 | | Table 2: Deferred Database Modification Scheme In Table 1 \& Table 2, if a system crash occurs after the step 6 and the recovery of the system is successfully completed, identify the correct actions from the above diagram.

(C) After completion of recovery in immediate database modification scheme, the value of P, Q and R in the buffer will be 400, 800 and 1500 respectively

(A) After completion of recovery in immediate database modification scheme, the value of P, Q and R in the buffer will be 750, 1000 and 1000 respectively.

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

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

May 2022 term · Database Management Systems · BSCS2001

DBMS End Term: 7 August 2022, Set QPD1 (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 QPD1: 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

A role Department_Lead has the privilege of delete and update 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, delete and update
B
Only select
C
Only delete and update
D
Only select and insert

Show answer

Correct answer

C
Only delete and update

Question 2 on its own page

Question 3

+1 markOne correct option

Consider the following statements:
Statement 1: In case a transaction is not completely executed due to logical errors, the redo operation is used for transaction rollback.
Statement 2: A transaction T1 needs to be undone, if it contains the record < T1, start > and also contains the record < T1, commit >.
Statement 3: A transaction T1 needs to be redone, if it contains the record < T1, start >, but does not contain the record < T1, commit >.
Statement 4: When a transaction T1 finishes its last statement successfully, the log record < T1, commit > is written.
Which among the given statements is/are correct?

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

Show answer

Correct answer

D
Only statement 4

Question 3 on its own page

Question 4

+1 markOne correct option

Consider a relational schema student(roll_no, name, class).
In order to insert a record in student table, 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

A
DML

Question 4 on its own page

Question 5

+2 marksOne correct option

Consider the relation students(roll_no, name, course, section, department) having functional dependencies

$\begin{aligned} &\mathcal{F} = \{roll\_no \rightarrow name, \\ &roll\_no \rightarrow section, department \\ &course \rightarrow department\} \end{aligned}$

If the relation students is decomposed into student_dept(roll_no, course, section, department) and student_info(roll_no, name)
Then, choose the appropriate SQL query to create student_dept and student_info tables.

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:

Name	Team	Coach	Runs
Sharma	CSK	Steven	99
Sharma	MI	Mahela	50
Sharma	MI	Mahela	99
Sharma	CSK	Steven	50
Khan	RCB	S K Joseph	45
Khan	GT	Matthew	80
Khan	GT	Matthew	45
Khan	RCB	S K Joseph	80

Table 1: players

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 given grocery table which represents the items purchased while grocery shopping

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

Figure 1: grocery

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

grocery_item	discount
Cereal	10
Milk	0

Show answer

Correct answer

Question 7 on its own page

Question 8

+2 marksOne correct option

Consider the following schedule S with four transactions T1, T2, T3,T4:
S: R3(A);W4(A);R2(A);W2(A);R4(B);R1(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 8 on its own page

Question 9

+2 marksOne correct option

Show answer

Correct answer

Question 9 on its own page

Question 10

+2 marksOne correct option

Consider the relation Car(Car_num_plate,Engine_num,Model,Make, Fuel_type).
Construct a bitmap index for relation Car on the attribute Fuel_type.The number of records in the relation Car is x. The size of the bitmap index file is 1000 bytes. The attribute Fuel_type has 4 different values(i.e., Petrol, Diesel, CNG, Electric). The value of the x is

A
250
B
500
C
1000
D
2000

Show answer

Correct answer

D
2000

Question 10 on its own page

Question 11

+2 marksOne correct option

Consider a primary sparse index on the instructor table. The number of records in the instructor relation is 10000. The block size is 512 bytes. The record size is 32 bytes. The size of the search key is 16 bytes and the size of the block pointer is 14 bytes. The number of the blocks in the primary sparse indexing file is

A
16
B
36
C
37
D
625

Show answer

Correct answer

C
37

Question 11 on its own page

Question 12

+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 13 bytes. The size of the tree pointer is 64 bytes. The size of the record pointer of a non-leaf node of the B tree is

A
60 bytes
B
64 bytes
C
73 bytes
D
128 bytes

Show answer

Correct answer

A
60 bytes

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 a RAID-4 system with 5 disks which stores the following data shown in Figure 3:

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

Figure 3: 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: 0110, Block C: 0101
2. Block A: 0100, Block B: 1011, Block C: 0010
3. Block A: 0100, Block B: 0100, Block C: 0101
4. The word is : ’leader’
5. The word is: ’LEADER’
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 3 & 4
B
Statements 2 & 5
C
Statements 1 & 4
D
Statements 3 & 5

Show answer

Correct answer

D
Statements 3 & 5

Question 14 on its own page

Question 15

+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)
Unlock-(A)
Unlock-(B)
Commit

Table 8: Schedule S1

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

Table 9: Schedule S2

Which among 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

C
Only S2 follows Strict two-phase locking protocol.

Question 15 on its own page

Question 16

+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

Which of the following SQL statement gives the result given below?

Show answer

Correct answer

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 3NF but not in BCNF.

Select all that apply.

A
{AB → CD}
B
{AB → CD,C → A}
C
{AB → C,B → D,C → D}
D
{AB → C,C → D,AB → D}

Show answer

Correct answer

B
{AB → CD,C → A}

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 → E
C
B → G
D
D → C
E
D → A

Show answer

Correct answers

D
D → C
E
D → A

Question 19 on its own page

Question 20

+2 marksOne or more correct options

Consider a state of transactions as shown in Figure 2.

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

Select all that apply.

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

Show answer

Correct answer

C
The transactions T1 and T3 can be ignored.

Question 20 on its own page

Question 21

+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);R1(D);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 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 cascadeless schedule
D
S is a cascading rollback

Show answer

Correct answers

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

Question 21 on its own page

Question 22

+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, 750, 400 >$
3	$< T_0, Q, 1000, 800 >$
4	$< T_1, start >$
5	$< T_1, R, 1500, 1000 >$
6	$< T_1, commit >$

Table 1: Immediate Database Modification Scheme

Steps	Details of log
1	$< T_2, start >$
2	$< T_2, A, 1600, 1200 >$
3	$< T_2, commit >$
4	$< T_3, start >$
5	$< T_3, B, 1400, 1100 >$
6	$< T_3, commit >$

Table 2: Deferred Database Modification Scheme

In Table 1 & Table 2, if a system crash occurs after the step 6 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 immediate database modification scheme, the value of P, Q and R in the buffer will be 750, 1000 and 1000 respectively.
B
In Table 1, {T0} needs to be redone and {T1} needs to be undone.
C
After completion of recovery in immediate database modification scheme, the value of P, Q and R in the buffer will be 400, 800 and 1500 respectively
D
After completion of recovery in deferred database modification scheme, the value of A and B in the buffer will be 1600 and 1400 respectively

Show answer

Correct answer

C
After completion of recovery in immediate database modification scheme, the value of P, Q and R in the buffer will be 400, 800 and 1500 respectively

Question 22 on its own page

Question 23

+3 marksOne or more correct options

Consider the following relations:

Choose the correct SQL statement(s) to find names of the music which were produced by producer name ’YourFavMusic.com’ but the songs which were not sung by singer ’Lady Jaye’.

Select all that apply.

Show answer

Correct answers

Question 23 on its own page

Question 24

+1 markNumerical answer

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

Show answer

Correct answer: 24

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 30, 20, 27, 86, 103, 25, 60, 90, 10, 15. What is the sum of the key values in non-leaf
(including root) nodes of the constructed binary search tree?

Show answer

Correct answer: 276

Question 25 on its own page