Question 1 |

Consider the following three relations in a relational database.

Employee(eId, Name), Brand (bId, bName), Own(eId ,bId)

Which of the following relational algebra expressions return the set of eIds who own all the brands?

Employee(eId, Name), Brand (bId, bName), Own(eId ,bId)

Which of the following relational algebra expressions return the set of eIds who own all the brands?

**MSQ** \Pi _{eId}(\Pi _{eId,bId}(Own)/\Pi _{bId}(Brand)) | |

\Pi _{eId}(Own)-\Pi _{eId}\left ((\Pi _{eId}(Own) \times \Pi _{bId}(Brand) )-\Pi _{eId,bId}(Own) \right ) | |

\Pi _{eId}(\Pi _{eId,bId}(Own)/\Pi _{bId}(Own)) | |

\Pi _{eId}\left ((\Pi _{eId}(Own) \times \Pi _{bId}(Own) )/\Pi _{bId}(Brand) \right ) |

Question 1 Explanation:

Question 2 |

The following relation records the age of 500 employees of a company, where empNo ( indicating the employee number) is the key:

empAge(\underline{empNo},age)

Consider the following relational algebra expression:

\Pi_{empNo}(empAge \Join_{(age > age1)} \rho_{empNo1,age1}(empAge))

What does the above expression generate?

empAge(\underline{empNo},age)

Consider the following relational algebra expression:

\Pi_{empNo}(empAge \Join_{(age > age1)} \rho_{empNo1,age1}(empAge))

What does the above expression generate?

Employee numbers of only those employees whose age is the maximum | |

Employee numbers of only those employees whose age is more than the age of exactly one other employee | |

Employee numbers of all employees whose age is not the minimum | |

Employee numbers of all employees whose age is the minimum |

Question 2 Explanation:

Question 3 |

Consider the following relation P(X, Y, Z), Q(X, Y, T) and R(Y, V):

How many tuples will be returned by the following relational algebra query?

Answer:______

How many tuples will be returned by the following relational algebra query?

Answer:______

1 | |

2 | |

3 | |

4 |

Question 3 Explanation:

Question 4 |

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query

Q:r \Join (\sigma _{B\lt 5}(s))

Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent to Q?

Q:r \Join (\sigma _{B\lt 5}(s))

Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent to Q?

\sigma _{B \lt 5}(r\Join s) | |

\sigma _{B\lt 5}(r LOJ s) | |

r LOJ(\sigma _{B\lt 5}(s)) | |

\sigma _{B\lt 5}(r) LOJ s |

Question 4 Explanation:

Question 5 |

Consider a database that has the relation schema CR (StudentName, CourseName). An
instance of the schema CR is as given below.

The following query is made on the database

T1\leftarrow \pi _{CourseName}(\sigma _{StudentName='SA'}(CR))

T2\leftarrow CR\div T1

The number of rows in T2 is ____________.

The following query is made on the database

T1\leftarrow \pi _{CourseName}(\sigma _{StudentName='SA'}(CR))

T2\leftarrow CR\div T1

The number of rows in T2 is ____________.

2 | |

3 | |

4 | |

5 |

Question 5 Explanation:

Question 6 |

Given the relations

employee (name, salary, dept-no), and

department (dept-no, dept-name,address),

Which of the following queries cannot be expressed using the basic relational algebra operations \left(\sigma, \pi,\times ,\Join, \cup, \cap,-\right)?

employee (name, salary, dept-no), and

department (dept-no, dept-name,address),

Which of the following queries cannot be expressed using the basic relational algebra operations \left(\sigma, \pi,\times ,\Join, \cup, \cap,-\right)?

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 6 Explanation:

Question 7 |

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 |

Question 7 Explanation:

Question 8 |

Consider the relational schema given below, where eId of the relation dependentis a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

The above query evaluates to the set of empIds of employees whose age is greater than that of

The above query evaluates to the set of empIds of employees whose age is greater than that of

some dependent | |

all dependents | |

some of his/her dependents | |

all of his/her dependents |

Question 8 Explanation:

Question 9 |

What is the optimized version of the relation algebra expression \pi _{A1}(\pi _{A2}(\sigma _{F1}(\sigma_{F2}(r)))) , where A1, A2 are sets of attributes in r with A_{1}\subset A_{2} and F1, F2 are Boolean expressions based on the attributes in r?

\pi _{A1}(\sigma_{(F1 \wedge F2)}(r)) | |

\pi _{A1}(\sigma_{(F1\vee F2)}(r)) | |

\pi _{A2}(\sigma_{(F1\wedge F2)}(r)) | |

\pi _{A2}(\sigma_{(F1 \vee F2)}(r)) |

Question 9 Explanation:

Question 10 |

Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop
method. There are 3 buffers each of size equal to disk block size, out of which one buffer is
reserved for intermediate results. Assuming size(r(R))\ltsize(s(S)), the join will have fewer
number of disk block accesses if

relation r(R) is in the outer loop. | |

relation s(S) is in the outer loop | |

join selection factor between r(R) and s(S) is more than 0.5. | |

join selection factor between r(R) and s(S) is less than 0.5 |

Question 10 Explanation:

There are 10 questions to complete.