Database miscellaneous Easy Questions and Answers

Database miscellaneous

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))< size(s (S)), the join will have fewer number of disk block accesses if

1. relation r (R) is in the outer loop.
1. relation s (S) is in the outer loop.
1. join selection factor between r (R) and s (S) is more than 0.5.
1. join selection factor between r (R) and s (S) is less than 0.5.

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation. employee (empId, empName, empAge) dependent(depId, eId, depName, depAge) Consider the following relational algebra query :
Π_empId (employee)- Π_empId (employee^|X| _{(empId = eID)^.(empAge. depAge)}dependent)
The above query evaluates to the set of empIds of employees whose age is greater than that of

1. some dependent.
1. all dependents.
1. some of his/her dependents.
1. all of his/her dependents.

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amount 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan₁ on left, Plan₂ on right) to “list all courses taken by students who have paid more than x”:

A disk seek takes 4 ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 µs. Which of the following statements is correct?

1. Plan₁ and Plan₂ will not output identical row sets for all databases
1. A course may be listed more than once in the output of Plan₁ for some databases
1. For x = 5000, Plan₁ executes faster than Plan₂ for all databases
1. For x = 9000, Plan₁ executes slower than Plan₂ for all databases

Let r be a rational instance with schema R = (A, B, C, D). We define r₁ = Π_{A, B, C}(r) and r₂ = Π_{A, D}(r). Let s = r₁ * r₂ where * denotes natural join. Given that the decomposition of r into r₁ and r₂ is lossy, which one of the following is true?

Let R₁ (A, B, C) and R₂ (D, E) be two relational sachemas, where the primary keys are shown underlined, and let C be a foreign key in R₁ referring to R₂. Suppose there is no violation of the above referential integrity constraint in the corresponding relational instances r₁ and r₂ .Which one of the following relational algebra expressions would necessarily produce an empty relation?

1. Π_D(r₂) - Π_C(r₁)
1. Π_C(r₁) - Π_D(r₂)
1. Π_D(r₁ ⋈ _{C ≠ D}(r₂)
1. Π_C(r₁ ⋈ _{C = D}(r₂)