INFS2200 Relational Database Systems
Relational Database Systems
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFS2200 Relational Database Systems
This exam paper must not be removed from the venue
Information Technology and Electrical Engineering
EXAMINATION
INFS2200 Relational Database Systems
This paper is for St Lucia Campus students.
Examination Duration: 120 minutes
Reading Time: 10 minutes
Exam Conditions:
This is a Central Examination
This is a Closed Book Examination - specified materials permitted
During reading time - write only on the rough paper provided
This examination paper will be released to the Library
Materials Permitted In The Exam Venue:
(No electronic aids are permitted e.g. laptops, phones)
Any unmarked paper dictionary is permitted
An unmarked Bilingual dictionary is permitted
Calculators - Casio FX82 series or UQ approved (labelled)
Materials To Be Supplied To Students:
Instructions To Students:
Additional exam materials (eg. answer booklets, rough paper) will be
provided upon request.
Please answer all questions on the examination paper. Total Marks: 100 (to be
scaled down to 55).
Question 1 [4 marks] Which of the following is a false statement about B+ trees?
A. B+-trees are balanced
B. non-leaf nodes include direct pointers to data records
C. insertion of a key can lead to node splitting
D. deletion of a key can lead to node coalescing
Question 2 [4 marks] Which of the following is a correct statement about
transactions?
A. Redo is needed for atomicity
B. Undo is needed for durability
C. Concurrency Control is realized using Triggers and Assertions
D. Deadlocks do not occur in serial executions
E. None of the above.
Question 3 [4 marks] Which of the following transaction schedules does not
contain conflicting operations? Recall that r = read and w = write.
A. r1 (A), r2 (A), w1 (C), r1 (B), r2 (B)
B. r1 (A), r1 (B), w1 (A), r2 (B), r2 (A), w2 (A)
C. r1 (A), w1 (A), r1 (B), w1 (B), r2 (A), w2 (A), r2(B), w2(B)
D. r1(A), w1(A), r2(B), w2(B), r2(A), w2(A), r1 (B), w1 (B)
E. All (A)-(D) contain conflicts
Question 4 [4 marks] If a database system supports ACID properties for
transaction execution, which of the following pairs of values is a possible result for
A and B, after executing the below transactions T1 and T2 concurrently, with an
initial value of A=100 and B=200?
8 [3 points] Which of the following is a correct
statement about transactions?
A. Redo is needed for atomicity
B. Undo is needed for durability
C. Concurrency Control is realized using
Triggers and Assertions.
D. Deadlocks do not occur in serial executions
E. None of the above.
ANSWER: D
9 [3 points] Which of the following transaction
schedules does not contain conflicting opera-
tions? Recall that r = read and w = write.
A. r1(A), r2(A), w1(C), r1(B), r2(B)
B. r1(A), r1(B), w1(A), r2(B), r2(A), w2(A)
C. r1(A), w1(A), r1(B), w1(B), r2(A), w2(A),
r2(B), w2(B)
D. r1(A), w1(A), r2(B), w2(B), r2(A), w2(A),
r1(B), w1(B)
E. All (A)-(D) contain conflicts
ANSWER: A
10 [4 points] Which of the following is a correct
statement about the two transactions schedules
H3 and H4? Recall that r = read and w = write.
H3= r1(x), r3(z), w1(y), r2(x), w1(z), w2(x), r2(y),
w2(y)
H4= r1(x), w3(z), w1(z), r2(x), w1(y), r2(y), w2(x),
w2(y)
A. Have different serialization order and
they are not conflict equivalent.
B. Have different serialization order and
they are conflict equivalent.
C. Have same serialization order and
they are not conflict equivalent.
D. Have same serialization order and
they are conflict equivalent.
E. None of the above
ANSWER: C
11 [4 points] If a database system supports ACID
properties for transaction execution, which of
the following (A)-(D) pairs of values is a pos-
sible result for A and B, after executing the be-
low transactions T1 and T2 concurrently, with
an initial value of A=100 and B=200?
T1:
read(B)
B=B+50
write(B)
read(A)
A=A-50
write(A)
T2:
read(B)
tmp=B*0.1
B=B-tmp
write(B)
read(A)
A=A+tmp
write(A)
A. A=70 , B=230
B. A=50 , B=180
C. A=120 , B=250
D. A=50 , B=250
E. None of the above
ANSWER: A
A B C D
a1 10 5 x
a2 20 5 y
a3 10 10 z
a4 20 5 z
Figure 3: Relation r4
12 [3 points] Assume relation r4 (Fig. 3) and query
B=20. The selectivity of that query is:
A. 0%
B. 25%
C. 50%
D. 75%
E. 100%
ANSWER: C
13 [3 points] Assume relation r4 (Fig. 3) and a
bitmap index is created on attributeD. The total
number of bits required for that index is:
A. 1
B. 3
6
A. A=70 , B=230
B. A=50 , B=180
C. A=120 , B=250
D. A=50 , B=250
E. None of the above
Question 5 [4 marks] The write-ahead logging (WAL) protocol simply means
that:
A. writing of a data item should be done ahead of any logging operation.
B. the log record for an operation should be written before the actual data is
written.
C. all log records should be written before a new transaction begins
execution.
D. the log never needs to be written to disk.
Question 6 [4 marks] If a steal/no-force buffer management policy is in place,
which of the following is true about system recovery?
A. Both the Redo and Undo operations are needed
B. Neither the Redo nor the Undo operations is needed
C. Redo is needed but Undo is not needed
D. Redo is not needed but Undo is n eded
8 [3 points] Which of the following is a correct
statement about transactions?
A. Redo is needed for atomicity
B. Undo is needed for durability
C. Concurrency Control is realized using
Triggers and Assertions.
D. Deadlocks do not occur in serial executions
E. None of the above.
ANSWER: D
9 [3 points] Which of the following transaction
schedules does not contain conflicting opera-
tions? Recall that r = read and w = write.
A. r1(A), r2(A), w1(C), r1(B), r2(B)
B. r1(A), r1(B), w1(A), r2(B), r2(A), w2(A)
C. r1(A), w1(A), r1(B), w1(B), r2(A), w2(A),
r2(B), w2(B)
D. r1(A), w1(A), r2(B), w2(B), r2(A), w2(A),
r1(B), w1(B)
E. All (A)-(D) contain conflicts
ANSWER: A
10 [4 points] Which of the following is a correct
statement about the two transactions schedules
H3 and H4? Recall that r = read and w = write.
H3= r1(x), r3(z), w1(y), r2(x), w1(z), w2(x), r2(y),
w2(y)
H4= r1(x), w3(z), w1(z), r2(x), w1(y), r2(y), w2(x),
w2(y)
A. Have different serialization order and
they are not conflict equivalent.
B. Have different serialization order and
they are conflict equivalent.
C. Have same serialization order and
they are not conflict equivalent.
D. Have same serialization order and
they are conflict equivalent.
E. None of the above
ANSWER: C
11 [4 points] If a database system supports ACID
properties for transaction execution, which of
the following (A)-(D) pairs of values is a pos-
sible result for A and B, after executing the be-
low transactions T1 and T2 concurrently, with
an initial value of A=100 and B=200?
T1:
read(B)
B=B+50
write(B)
read(A)
A=A-50
write(A)
T2:
read(B)
tmp=B*0.1
B=B-tmp
write(B)
read(A)
A=A+tmp
write(A)
A. A=70 , B=230
B. A=50 , B=180
C. A=120 , B=250
D. A=50 , B=250
E. None of the above
ANSWER: A
A B C D
a1 10 5 x
a2 20 5 y
a3 10 10 z
a4 20 5 z
Figure 3: Relation r4
12 [3 points] Assume relation r4 (Fig. 3) and query
B=20. The selectivity of that query is:
A. 0%
B. 25%
C. 50%
D. 75%
E. 100%
ANSWER: C
13 [3 points] Assume relation r4 (Fig. 3) and a
bitmap index is created on attributeD. The total
number of bits required for that index is:
A. 1
B. 3
6
Question 7 [4 marks] Assume the relation shown above and query σB=20. The
selectivity of that query is:
A. 0%
B. 25%
C. 50%
D. 75%
E. 100%
Question 8 [4 marks] Again, assume the relation shown above and a bitmap
index is created on attribute D. The total number of bits required for that index is:
A. 1
B. 3
C. 4
D. 12
E. 16
Questions 9-11 Consider the B+ tree below, in which left-pointer < Key ≤ right
pointer. Answer the following questions:
10 20 30 40 50 60 70 80
30 60
Figure 2: A B+ tree
7 [4 points]
Consider the B+ tree in Figure 2, in which left-pointer < Key rights-pointer. What would be the correct B+
tree after insert 90:
Answer A:
10 20 30 40 50 60 70 80
30 60
90
90
Answer B:
10 20 30 40 50 60 70
30 60 90
9080
Answer C:
10 20 30 40 50 60 70
30 60 80
9080
Answer D:
10 20 30 40 50 60 70 80
30 60
90
Answer E: none of the above
ANSWER: C
5
Question 9 [4 marks] How many tree nodes are accessed when searching for
the value 100?
A. 0
B. 1
C. 2
D. 3
E. 4
Question 10 [4 marks] What would be the correct number of leaf nodes after
inserting 90?
A. 0
B. 1
C. 2
D. 3
E. 4
Question 11 [4 marks] What are the values in the root node after inserting 90?
A. 60
B. 30
C. 30, 60
D. 30, 60, 80
E. 30, 60, 90
Questions 12-14 Assume relation R with attributes A, B, C, D, and relation S with
attributes D, E, F. Which of the following are equivalent relational expressions?
Select True if equivalent, False otherwise.
Question 12 [4 marks] πA,B(σC<12(R)) = σC<12(πA,B(R))
A. True
B. False