Thursday, March 22, 2018

SQL and Relational Algebra short exercises solved

SQL and Relational Algebra short exercises solved

SQL and Relational Algebra Exercises

1. Consider relation R1(A, B, C) and R2(B,D), where number of tuples in R1, T(R1) = 25 and number of tuples in R2, T(R2) = 40, and B is a key for R1. How many tuples R1 ⋈ R2 will give?

Answer: 40 tuples
B is the foreign key in R2. The permissible values for attribute B in R2 are the values that are stored in attribute B of R1. The value of B can be duplicated. Hence, the result is all the tuples from R2.

2. Write the following query in relational algebra, for relations R(a, b) and S(c, d): select a, d from R, S where R.a > 10 and R.b = S.c.

Answer: πa,d(σa>10(R b=c S))

3. Write the following relation algebra expression in SQL, for relations Students(regno, name, age) and Stu_Tour_Regd(regno, name, tour_amt): πregno, name(σage > 20(STUDENTS)) − πregno, name(STU_TOUR_REGD)

Answer: (SELECT regno, name FROM Students WHERE age > 20) EXCEPT (SELECT regno, name FROM Stu_tour_regd)
This query finds the register number and names of students whose age is greater than 20 and not registered for tour.

4. For relation Student(regno, name, dept), suppose dept can be one of {ite, cse, ece, eee, mech, civil}. If number of tuples in relation Student is 1000, then what is the size estimate of the query σdept = ‘physics(Student)?

Answer: Nil.
Physics is not one of the departments. Hence, no students belong to that department.

5. How exactly the relational algebra expression R θ S can be expressed using only the basic relational algebra operators?

Answer: σθ(R X S)
The given expression is called as theta join. It is a combination of a join condition (θ) and the join operation (X).



solved exercises in dbms
solved exercises in relational algebra
solved exercises in SQL
SQL and relational algebra short answers
SQL and relational algebra short exercises

No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery