Database miscellaneous Easy Questions and Answers

Database miscellaneous

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)
π _name (r_{sex female} (student)) - π _name (student_sex P_{o , x , m}

1. names of girl students with the highest marks
1. names of girl students with more marks than some boy student
1. names of girl students with marks not less than some boy student
1. names of girl students with more marks than all the boy students

Consider the following relational schema pertaining to a student’s database:
Students (Rollno., name, address)
Enroll (Rollno., Courseno., coursename)
Where the primary keys are shown underlined. The numbers of tuples in the student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum numbers of tuples that can be present in (Student * Enroll), where * denotes natural join?

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₂)

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?

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