NPTEL Introduction to Database Systems Week 11 Assignment Answers 2025
1. Consider the following two relations that store the details of the employees, their departments, and managers of the departments working in an organization, where managerId is a foreign key that refers to empId of Employee relation.
Employee( empId, deptId) Department( deptId , deptName, managerId)Consider a join operation between the above two relations based on the join condition Employee.empId = Department.managerId .
Assume that indexes are available on both the relations on the fields used in the join condition. Suppose that the single loop join technique is chosen to perform the operation. Identify the correct option for the following statements:
S1. For the given join condition, the join selection factor of Department relation is more than the join selection factor of Employee relation
S2. For achieving better performance, the single loop join algorithm loops over the Employee records
- Both S1 and S2 are False
- S1 is False, S2 is True
- S1 is True, S2 is False
- Both S1 and S2 are True
Answer :- For Answers Click Here
2. Consider two files R and S stored on the disk using b1 and b2 blocks, respectively. The files are sufficiently large that they don’t fit into main memory. Assume that the equi-join operation R ⨝
R.A = S.B S is to be performed using partition-hash join method.Main memory space that can fit M blocks of data is available for the partition phase. The number of block accesses in partition phase is
- b1 + b2
- 2 (b1 + b2)
- b1 + b2 + 2M
- 2 (b1 + b2 + M)
Answer :-
3. For the information given in Question 2, assume that each of the partitions in R and S is stored in 10000 and 1000 blocks, respectively. To compute the join result of a particular partition file of R, the number of blocks of S that need to be accessed in the probe phase is (Ignore the time taken to write the result)
- 1000
- 11000
- 1000 * b2
- 2000 * b2
Answer :-
4. Consider two files R and S stored on a disk using 9000 and 1000 blocks, respectively. Assume that R is already sorted on A. Suppose that the equi-join operation R ⨝ R.A = S.B S is to be performed using sort-merge join. Main memory that can fit 11 blocks of data is available as the buffer space for performing external sorting. The number of block accesses required to perform the join operation (without counting the cost of writing the join results) is
- 10000
- 16000
- 18000
- 64000
Answer :-
5. Which of the following SQL operators isn’t commutative?
- INNER JOIN
- FULL OUTER JOIN
- LEFT OUTER JOIN
- INTERSECT
Answer :- For Answers Click Here
6. Use the following schema of the academic institution relational database for this question.
student( rollNo , name, degree, year, sex, deptNo, advisor) department( deptId , name, hod, phone) professor( empId , name, sex, startYear, deptNo, phone) deptNo is a foreign key in the student and professor relations referring to deptId of department relation; advisor is a foreign key in the student relation referring to empID of professor relation; Consider the following SQL query: Select s.rollNo, s.name From student s, professor p Where s.deptNo = 3 and p.deptNo <> 3 and s.advisor = p.empId
The above SQL query is converted into an equivalent optimized RA expression using heuristic optimization rules taught in the class. In the final RA expression, let A and B be the sets of columns projected from student and professor respectively after appropriate selection operations. The sizes of the sets A and B, respectively, are
- 1, 1
- 2, 2
- 3, 1
- 4, 2
Answer :-
7. Consider the following schedule S.
S: R1 (X); R2 (Z); R1 (Z); R3 (X); R3 (Y); W1 (X); W3 (Y); R2 (Y); W2 (Z);
In the precedence graph of S, the number of edges (both incoming and outgoing) of
nodes T1 and T2, respectively are
- 1, 1
- 1, 2
- 2, 1
- 2, 2
Answer :-
8. Which of the following serializations of transactions results in a schedule that is conflict equivalent to the schedule given in question 7?
- T1, T2, T3
- T3, T2, T1
- T1, T3, T2
- T3,T1, T2
Answer :-
9. Consider the following schedules.
S1: R1 (X) R1 (Y) R2 (X) R2 (Y) W2 (Y) W1 (X) S2: R1 (X) R2 (X) R2 (Y) W2 (Y) R1 (Y) W1 (X) Which of the above schedules are conflict-serializable?
- Only S1
- Only S2
- BOTH
- NONE
Answer :-
10. The acyclicity of the precedence graph of a schedule gives only a necessary condition, but not a sufficient condition to test conflict serializability
- True
- False
Answer :-
11. There exists a schedule that is view serializable but not conflict serializable
- True
- False
Answer :-
12. A conflict serializable schedule is conflict-equivalent to exactly one serial schedule
- True
- False
Answer :-
13. The operations that belong to the same transaction can never conflict
- True
- False
Answer :- For Answers Click Here
