DATABASE SYSTEM QUESTION / QUERY QUESTION Consider the following relational sche
ID: 3729568 • Letter: D
Question
DATABASE SYSTEM QUESTION / QUERY QUESTION
Consider the following relational schema:
student ( s-id, s-name, cgpa)
membership ( s-id, c-id, since)
club ( c-id, c-name)
Assume there are 10000 tuples in student, 5000 tuples in membership, and 80 tuples in club. Half of the clubs do not have any members. What are the min. and max. number of rows that the following SQL queries would result?
(a) select C.c-id, C.c-name
from club C, membership M, student S
where C.c-id = M.c-id and S.s-id = M.s-id and S.cgpa > 3.0
(b) select S.s-id
from membership M, student S
where S.s-id = M.s-id
group by S.s-id
having count(*) > 1
(c) select distinct S.s-id
from club C, membership M, student S
where C.c-id = M.c-id and S.s-id = M.s-id
Explanation / Answer
a) In the query we are enlisting all the tuples which have entry in the membership table. Now, in case no student have cgpa > 3 we would have no tuples in the query. In case all the students have cgpa > 3, then there are maximum of 5000 tuples in the membership table and all would be returned as a result.
Thus,
Min = 0
Max = 5000
b) Here we are again enlisting the membership table, such that grouping is done by student ID and the count in each group is greater than 1. For minimality a student should enroll in all the clubs that are available thus due to grouping only one tuple would be shown for this student. There are only 40clubs which have any participation(as half of clubs are empty), therefore we need 125 students who participate in all those 40clubs, to fill 5000tuples of the membership table.
Min = 125
For maximality, we would try to keep as much distinct students as possible in the membership table, but if we place 5000unique students in the table, then in grouping none will appear as the count in each group would be only 1. Thus we have to keep a minimum of two entries of each student in the membership table(ie every student joins two clubs). We therefore get 5000 / 2 = 2500 tuples after the grouping
Max = 2500
c) We are now again enlisting entries of the membership table,
For minimality we need minimum distinct students in membership table, thus a single student goes to 40clubs. To fill up 5000tuples of membership table, we would need 125 such students.
Min = 125
At max there can be 5000 distinct students enrolling for clubs.
Max = 5000
It can still get confusing, if you don't understand it just post a comment of your query. I will make sure you understand it :D
Best regards.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.