EPFL - Databases - Quiz 2
Relational Algebra and SQL
1) Which basic operator selects a subset of
rows
from a relation?
Selection
Projection
Cross Product
Union
2) Given two relations R1 and R2, where R1 contains N1 tuples, R2 contains N2 tuples, and N2 > N1 > 0, give the minimum and maximum possible size for the expression: R1 ∪ R2 (union).
Min: N2, Max: N1 + N2
Min: N1, Max: N1 + N2
Min: 0, Max: N1
Min: 0, Max: N2
3) Consider the following relations containing airline flight information:
Flights(no: integer, from: string, to: string, distance: integer, departs: time, arrives: time)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certiffied(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Choose the relational algebra query which finds the names of pilots certiffied for some Boeing aircraft.
π
ename
(σ
aname='Boeing'
(Aircraft |><| Certified |><| Employees))
σ
aname='Boeing'
(π
ename
(Aircraft |><| Certified |><| Employees))
σ
aname='Boeing'
(Aircraft |><| Certified |><| Employees)
π
eid
(σ
aname='Boeing'
(Aircraft |><| Certified |><| Employees))
4) When are triggers executed?
Anytime.
Whenever the database is changed.
Whenever changes to the database meet the conditions of the trigger.
Whenever the application calls them.
5) The result of an SQL query is a set ...
always.
only if the DISTINCT keyword is used.
if we select multiple columns.
if the DISTINCT keyword is used or if there is no duplicate in the resulting tuples.
Files and Indexing
6) What is the average cost of a range search in a heap file consisting of B blocks?
B
0.5 * B
log2(B)
log2(B) + 2 * (B/2)
7) What is the average cost of an equality search (assuming exactly one match) in a sorted file consisting of B blocks?
B
0.5 * B
log2(B)
log2(B) + 2 * (B/2)
8) Consider a disk with sector size 512 bytes and 100 sectors per track. Which of the following blocks sizes is not valid?
256
512
1024
51200
9) The search key of an index on the relation R ...
is the same as the primary key.
has unique values.
can consist only of one attribute of R.
can consist of any subset of attributes of R.
10) Consider a primary key index of a relation R, which is stored using a sorted file on the index attribute. How should we perform a range scan if we want to ensure the lowest cost?
Traverse the index until the first tuple of the range and then scan the rest of the file accepting matching tuples.
Scan the file sequentially.
Traverse the index for each value in the range.
Traverse the index until the first tuple of the range and then scan the rest of the range (stop as soon as we meet the first tuple with value out of the given range).
Feedback form
If you want to ask for feedback please fill in
this form
with the information of the question, of your answer and any question you may have.
Quiz script provided by
JavaScriptKit.com