Relational-Algebra
Question 1 |
What does the above expression generate?
Employee numbers of all employees whose age is not the maximum. | |
Employee numbers of only those employees whose age is the maximum. | |
Employee numbers of all employees whose age is not the minimum. | |
Employee numbers of only those employees whose age is more than the age of exactly one other employee. |
Question 2 |
Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
Out of syllabus (For explanation see below) |
→ No need of using Union operation here. → In question they gave (∪, −) but we don't use both.
→ And also they are saying that only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
So, the expression is minimal.
Question 3 |
Consider the following relations A, B, C.
How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A.
(AUB)⋈A.Id>40∨C.Id<15 C
7 | |
4 | |
5 | |
9 |
Performs the cross product and selects the tuples whose A∙Id is either greater than 40 or C∙Id is less than 15. It yields:
Question 4 |
Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
∏B (r1) - ∏C (r2) = ∅ | |
∏C (r2) - ∏B (r1) = ∅ | |
∏B (r1) = ∏C (r2) | |
∏B (r1) - ∏C (r2) ≠ ∅ |
So we can say that r2(C) is the superset of r1(B).
So (subset - superset) is always empty.
Question 5 |
Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R1 and R2 are relations, C1, C2 are selection conditions and A1, A2 are attributes of R1?
σC1(σC1(R1)) → σC2(σC2(R1)) | |
σC1(σA1(R1)) → σA1(σC1(R1)) | |
σC1(R1 ∪ R2) → σC1(R1) ∪ σC1 | |
πA1(σC1(R1)) → σC1(σA1(R1)) |
Question 6 |
Given two union compatible relations R1(A,B) and R2(C,D), what is the result of the operation R1A = CAB = DR2?
R1 ∪ R2 | |
R1 × R2 | |
R1 - R2 | |
R1 ∩ R2 |
Question 7 |
The relational algebra expression equivalent to the following tuple calculus expression:
{t| t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} isσ(A=10∨B=20) (r) | |
σ(A=10) (r) ∪ σ(B=20) (r) | |
σ(A=10) (r) ∩ σ(B=20) (r) | |
σ(A=10) (r) - σ(B=20) (r) |
σ(A=10) (r) ∩ σ(B=20) (r)
Question 8 |
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are
m + n and 0 | |
mn and 0 | |
m + n and |m – n| | |
mn and m + n |
Suppose there is no common attribute in R and S due to which natural join will act as cross product. So then in cross product total no. of tuples will be mn.
For minimum:
Suppose there is common attribute in R and S, but none of the row of R matches with rows of S then minimum no. of tuples will be 0.
Question 9 |
Given the relations
employee (name, salary, deptno) and department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, -)?
Department address of every employee | |
Employees whose name is the same as their department name | |
The sum of all employees’ salaries | |
All employees of a given department |
Question 10 |
Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. Then the relational algebra expression σA=a(r⋈s) is always equal to
σA=a (r) | |
r | |
σA=a (r)⨝s | |
None of the above |
(b) Display table
(c) A=a for all Tables r and s
Question 11 |
Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
List of all vertices adjacent to a given vertex | |
List all vertices which have self loops | |
List all vertices which belong to cycles of less than three vertices | |
List all vertices reachable from a given vertex |
(b) Finding a self loop is also simple (Oop(X,X))
(c) If a → b, b → c then c!=a, finding this is also simple.
(d) List all the elements reachable from a given vertex is too difficult in Relational Algebra.
Question 12 |
A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:
COMPANY (cname, clocation) STUDENT (scrollno, sname, sdegree) INTERVIEW (cname, srollno, idate) OFFER (cname,srollno, osalary)
The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a students is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes.
(a) Write relational algebra expressions (using only the operator ⨝,σ,π,∪,−) for the following queries:
- (i) List the rollnumbers and names of those students who attended at least one interview but did not receive any job offer.
(ii) List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed.
(b) Write an SQL query to list, for each degree program in which more than five students were offered jobs, the name of the degree and the average offered salary of students in this degree program.
Theory Explanation is given below. |
Question 13 |
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
Relational algebra is more powerful than relational calculus. | |
Relational algebra has the same power as relational calculus. | |
Relational algebra has the same power as safe relational calculus. | |
None of the above. |
A query can be formulated in safe Relational Calculus if and only if it can be formulated in Relational Algebra.
Question 14 |
| S | |
| A | C |
| 10 | 90 |
| 30 | 45 |
| 40 | 80 |
| R | |
| A | B |
| 10 | 20 |
| 20 | 30 |
| 30 | 40 |
| 30 | 50 |
| 50 | 95 |
2 | |
Question 15 |
Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :
2000 | |
2500 | |
4500 | |
5000 |
Question 16 |
Consider the following relation schemas:
b-Schema = (b-name, b-city, assets)
a-Schema = (a-num, b-name, bal)
d-Schema = (c-name, a-number)
Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation.
Consider the following query:
Пc-name (σb-city = "Agra" ⋀ bal < 0 (branch ⋈ (account ⋈ depositor)
Which one of the following queries is the most efficient version of the above query?
Пc-name (σbal < 0 (σb-city = “Agra” branch ⋈ account) ⋈ depositor) | |
Пc-name (σb-city = “Agra”branch ⋈ (σbal < 0 account ⋈ depositor)) | |
Пc-name (σb-city = “Agra” branch ⋈ σb-city = “Agra” ⋀ bal < 0 account) ⋈ depositor) | |
Пc-name (σb-city = “Agra” ⋀ bal < 0 account ⋈ depositor)) |
Options (C) and (D) are invalid as there is no b-city column in a-schema.
Question 17 |
Consider a selection of the form σA≤100(r), where r is a relation with 1000 tuples. Assume that the attribute values for A among the tuples are uniformly distributed in the interval [0, 500]. Which one of the following options is the best estimate of the number of tuples returned by the given selection query ?
50 | |
100 | |
150 | |
200 |
Values for A among the tuples are uniformly distributed in the interval [0, 500]. This can be split to 5 mutually exclusive and exhaustive intervals of same width of 100 ([0-100], [101-200], [201-300], [301-400], [401-500], 0 makes the first interval larger - this must be type in this question) and we can assume all of them have same number of values due to uniform distribution. So no. of tuples with A value in first interval should be,
Total no. of tuples/5 = 1000/5 = 200
Question 18 |
Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: p is the rename operator).
The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"
names of girl students with the highest marks
| |
names of girl students with more marks than some boy student | |
names of girl students with marks not less than some boy students
| |
names of girl students with more marks than all the boy students |
Question 19 |
Consider the following relation schema pertaining to a students database:
Student (rollno, name, address) Enroll (rollno, courseno, coursename)
Where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
8, 8 | |
120, 8 | |
960, 8 | |
960, 120 |
→ In the question only enroll Id's are same with the student table.
→ The no. of minimum and maximum tuples is same i.e., 8, 8.