微信客服：xiaoxionga100
微信客服：ITCS521
数据库代写-INFS2200
时间：2021-11-01
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 1 of 12
This exam paper must not be removed from the venue
School of Information Technology and Electrical Engineering
EXAMINATION
Semester Two Final Examinations, 2016
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).
Venue ____________________
Seat Number ________
Student Number |__|__|__|__|__|__|__|__|
Family Name _____________________
First Name _____________________
For Examiner Use Only
Question Mark
Total ________
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 2 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 3 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 4 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 5 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 6 of 12
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
Question 13 [4 marks] σC>12 and R.D=S.D (R x S) = (σC>12(R)) R.D=S.D S
A. True
B. False
Question 14 [4 marks] πA,D(R ∗ S) = (πA(R)) ∗ (πD(S))
A. True
B. False
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 7 of 12
A B C D
101 10 5 alex
102 20 6 alex
103 20 9 bob
104 10 7 x1
105 10 4 x2
106 20 3 carrie
107 20 8 dawn
Figure 2: Relation r1
C D E
4 bob 30
5 x3 40
6 x4 50
7 carrie 40
Figure 3: Relation s2
Assume relations r1 and s2 from Figures 2 and
3. Assume that their natural right outer join
is given in Figure 4.
Given these three tables, determine the missing
values for relations r1 and s2.
13 [2 points] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: C
14 [2 points] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: B
A B C D E
105 10 4 bob 30
102 20 6 alex 50
104 10 7 carrie 40
null null 5 elle 40
Figure 4: r1 natural right outer join s2
15 [2 points] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: E
16 [2 points] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: A
5
Questions 15-18 Assume the two relations shown above and assume that their
natural right outer join is given as: A B C D
101 10 5 alex
102 20 6 alex
103 20 9 bob
104 10 7 x1
105 10 4 x2
106 20 3 carrie
107 20 8 dawn
Figure 2: Relation r1
C D E
4 bob 30
5 x3 40
6 x4 50
7 carrie 40
Figure 3: Relation s2
Assume relations r1 and s2 from Figures 2 and
3. Assume that their natural right outer join
is given in Figure 4.
Given these three tables, determine the missing
values for relations r1 and s2.
13 [2 points] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: C
14 [2 points] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: B
A B C D E
105 10 4 bob 30
102 20 6 alex 50
104 10 7 carrie 40
null null 5 elle 40
Figure 4: r1 natural right outer join s2
15 [2 points] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: E
16 [2 points] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: A
5
Given these three tables, determine the missing values in the first two relations:
Question 15 [4 marks] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Question 16 [4 marks] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 8 of 12
Question 17 [4 marks] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Question 18 [4 marks] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 9 of 12
Question 19 [8 marks]:
You are given the following tables:
Student(StudId, Name, Addr, Status)
Transcript(Id, CrsCode, Semester, Grade)
Consider a query that outputs the names of all students who took INFS2200 in
2016. An execution plan for that query can be expressed as follows:
πName(σId=StudId AND CrsCode=′INFS2200′ AND Semester=′2016′ (Student × Transcript))
In the following, fill in the missing subscripts of the different operators (i.e., Π, σ, I)
so that to create an optimized plan that is equivalent to the one above.
ΠNAME [
(Π−−−−−−−−−−−−−−−−− Student) −−−−−−−−−−−−−−−−
(σ−−−−−−−−−−−−− (Π−−−−−−−−−−−−−− Transcript))
]
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 10 of 12
Question 20:
For each of the following schedules: 1) construct a precedence graph, 2)
determine if the schedule is conflict serializable, and 3) determine the equivalent
serial schedule.
(a) [4 marks] r1(X); r3(X); w1(X); r2(X); w3(X)
(b) [4 marks] r3(X); r2(X); w3(X); r1(X); w1(X)
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 11 of 12
Question 21:
Consider the join R R.a=S.bS, given the following information about the relations
to be joined. The cost metric is the number of block read operations.
Relation R contains 10,000 tuples and has 10 tuples per block.
Relation S contains 2000 tuples and also has 10 tuples per block.
Attribute b of relation S is the primary key for S.
Both relations are stored as simple heap files.
(a) [4 marks] What is the cost of joining R and S using a page-oriented nested
loop join?
(b) [4 marks] How many tuples does the join of R and S produce, at most and
how many blocks are required to store the result of the join back on disk?
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 12 of 12
Question 22:
(a) [2 marks] Briefly describe the basic Two-Phase Locking (2PL) protocol.
(b) [2 marks] In the timeout mechanism for handling deadlocks, compare the
pros and cons of long timeout vs. short timeout.
END OF EXAMINATION
学霸联盟
学霸联盟
Page 1 of 12
This exam paper must not be removed from the venue
School of Information Technology and Electrical Engineering
EXAMINATION
Semester Two Final Examinations, 2016
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).
Venue ____________________
Seat Number ________
Student Number |__|__|__|__|__|__|__|__|
Family Name _____________________
First Name _____________________
For Examiner Use Only
Question Mark
Total ________
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 2 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 3 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 4 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 5 of 12
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
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 6 of 12
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
Question 13 [4 marks] σC>12 and R.D=S.D (R x S) = (σC>12(R)) R.D=S.D S
A. True
B. False
Question 14 [4 marks] πA,D(R ∗ S) = (πA(R)) ∗ (πD(S))
A. True
B. False
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 7 of 12
A B C D
101 10 5 alex
102 20 6 alex
103 20 9 bob
104 10 7 x1
105 10 4 x2
106 20 3 carrie
107 20 8 dawn
Figure 2: Relation r1
C D E
4 bob 30
5 x3 40
6 x4 50
7 carrie 40
Figure 3: Relation s2
Assume relations r1 and s2 from Figures 2 and
3. Assume that their natural right outer join
is given in Figure 4.
Given these three tables, determine the missing
values for relations r1 and s2.
13 [2 points] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: C
14 [2 points] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: B
A B C D E
105 10 4 bob 30
102 20 6 alex 50
104 10 7 carrie 40
null null 5 elle 40
Figure 4: r1 natural right outer join s2
15 [2 points] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: E
16 [2 points] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: A
5
Questions 15-18 Assume the two relations shown above and assume that their
natural right outer join is given as: A B C D
101 10 5 alex
102 20 6 alex
103 20 9 bob
104 10 7 x1
105 10 4 x2
106 20 3 carrie
107 20 8 dawn
Figure 2: Relation r1
C D E
4 bob 30
5 x3 40
6 x4 50
7 carrie 40
Figure 3: Relation s2
Assume relations r1 and s2 from Figures 2 and
3. Assume that their natural right outer join
is given in Figure 4.
Given these three tables, determine the missing
values for relations r1 and s2.
13 [2 points] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: C
14 [2 points] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: B
A B C D E
105 10 4 bob 30
102 20 6 alex 50
104 10 7 carrie 40
null null 5 elle 40
Figure 4: r1 natural right outer join s2
15 [2 points] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: E
16 [2 points] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
ANSWER: A
5
Given these three tables, determine the missing values in the first two relations:
Question 15 [4 marks] The correct value for x1 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Question 16 [4 marks] The correct value for x2 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 8 of 12
Question 17 [4 marks] The correct value for x3 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Question 18 [4 marks] The correct value for x4 is:
A. alex
B. bob
C. carrie
D. dawn
E. elle
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 9 of 12
Question 19 [8 marks]:
You are given the following tables:
Student(StudId, Name, Addr, Status)
Transcript(Id, CrsCode, Semester, Grade)
Consider a query that outputs the names of all students who took INFS2200 in
2016. An execution plan for that query can be expressed as follows:
πName(σId=StudId AND CrsCode=′INFS2200′ AND Semester=′2016′ (Student × Transcript))
In the following, fill in the missing subscripts of the different operators (i.e., Π, σ, I)
so that to create an optimized plan that is equivalent to the one above.
ΠNAME [
(Π−−−−−−−−−−−−−−−−− Student) −−−−−−−−−−−−−−−−
(σ−−−−−−−−−−−−− (Π−−−−−−−−−−−−−− Transcript))
]
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 10 of 12
Question 20:
For each of the following schedules: 1) construct a precedence graph, 2)
determine if the schedule is conflict serializable, and 3) determine the equivalent
serial schedule.
(a) [4 marks] r1(X); r3(X); w1(X); r2(X); w3(X)
(b) [4 marks] r3(X); r2(X); w3(X); r1(X); w1(X)
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 11 of 12
Question 21:
Consider the join R R.a=S.bS, given the following information about the relations
to be joined. The cost metric is the number of block read operations.
Relation R contains 10,000 tuples and has 10 tuples per block.
Relation S contains 2000 tuples and also has 10 tuples per block.
Attribute b of relation S is the primary key for S.
Both relations are stored as simple heap files.
(a) [4 marks] What is the cost of joining R and S using a page-oriented nested
loop join?
(b) [4 marks] How many tuples does the join of R and S produce, at most and
how many blocks are required to store the result of the join back on disk?
Semester Two Final Examinations, 2016 INFS2200 Relational Database Systems
Page 12 of 12
Question 22:
(a) [2 marks] Briefly describe the basic Two-Phase Locking (2PL) protocol.
(b) [2 marks] In the timeout mechanism for handling deadlocks, compare the
pros and cons of long timeout vs. short timeout.
END OF EXAMINATION
学霸联盟
学霸联盟
