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_id (σProfessor = 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_id (σProfessor = 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
Share your thoughts in the comments
Please Login to comment...