Relational Algebra
Q1.
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?Q2.
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?Q3.
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:______Q4.
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?MSQQ5.
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?Q6.
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 ____________.Q7.
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 areQ8.
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 ifQ9.
Consider two relations R1(A,B) with the tuples (1,5), (3,7) and R2(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A,B,C): a = (1,5,null), b = (1,null,7), c = (3, null, 9), d = (4,7,null), e = (1,5,7), f = (3,7,null), g = (4,null,9). Which one of the following statements is correct?Q10.
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)?