Consider the following relation schema:
Student(S_id, S_name, DOB){S_id is the candidate key and there are 4 student}

S_id S_name DOB
S1 Jason 20/09/1995
S2 Max 01/01/1998
S3 Jimmy 14/02/1994
S4 Aston 11/12/1996

Program(P_id, Professor){P_id is the candidate key and there are 4 Courses}

P_id Standard
C1 James
C2 Shane
C3 James
C4 Jay

Admission(S_idP_id, Standard){S_idP_id is the candidate key and there are 4 tuples in Admisssion}

S_idC_id Standard
S1C1 I
S2C2 I
S1C2 I
S3C3 II

Which of the following query will result in the minimum cost of a query to retrieve student\’s id who enrolled some course taught by James?
I – πS_id (σ(Admission XAdmission.P_id = Program.P_id ^ Professor = James Program)
II – πS_id (σ(Admission XAdmission.P_id = Program.P_idProfessor = JamesProgram)
III – πS_id (σ(R_S ⨝Admission.P_id = Program.P_id ((σProfessor = JamesProgram)))
IV – πS_id (σ(Admission ⨝< (σProfessor = JamesProgram))

(A) I query
(B) II query
(C) I and II query
(D) II and IV query


Answer: (D)

Explanation: I – πS_id (σ(Admission XAdmission.C_id = Program.C_id ^ Professor = James Program) will result into 32 comparision.
II – πS_id (σ(Admission XAdmission.C_id = Program.C_idProfessor = JamesProgram)will result into 12 comparision.
III – πS_id (σ(R_S ⨝Admission.C_id = Program.C_id ((σProfessor = JamesProgram)))will result into 20 comparision.
IV – πS_id (σ(Admission ⨝ (σProfessor = JamesProgram)) will result into 12 comparision.
II and IV query needs 12 comparisions.
So, option (D) is correct.

Quiz of this Question


  • Last Updated : 30 Nov, 2018

Share your thoughts in the comments