Database Systems Lab 9
Due Date: May 27, 2023 at 11:59pm
15 marks in total = 1.5% of the finalgrade
• This lab has an attendance component of [3 marks]
Theory Consider the following B+-tree on a relation R. Each block can hold up to 3 tuples of R; alternatively,
each block can hold a combination of up to 4 keys of R and 5 pointers.
Figure 1: A B+-tree with 2 levels
Answer the following questions.
1. Insert 59 to the B+ tree. Show the resulting tree. [3 marks]
2. After inserting 59, what is the I/O cost of finding all the keys in the range of (35, 55)? [2 marks]
3. How many tuples of relation R a B+tree of 4 levels (level-0, · · · , level-3) can hold at most?
[2 marks]
4. How many tuples of relation R a B+tree of 4 levels (level-0, · · · , level-3) can hold at least?
[2 marks]
Practice In SQL, “explain” is frequently used for showing the execution plan of a statement. Please see this
documentation as an explanation of “explain”. In this lab, you will use the postgreSQL database system
to understand the execution plan of different statements. Please import the university data described
here in two steps:
– Import SQL of DDL,
– Import SQL for creating large relations.
1. Execute the following commands and try to understand the execution plan based on the documentation. In particular, focus on the application of file scan, index scan, hash join, block nested
loop join, and merge join.
explain select id, name, tot_cred from student
where dept_name = 'Comp. Sci.';
explain analyze select id, name, tot_cred from student
where dept_name = 'Comp. Sci.';
explain (analyze, buffers) select id, name, tot_cred from student
where dept_name = 'Comp. Sci.';
explain (format json) select id, name, tot_cred from student
where dept_name = 'Comp. Sci.';
explain select id, name, tot_cred from student
where dept_name = 'Comp. Sci.';
create index on student using btree(dept_name);
explain select id, name, tot_cred from student
1
where dept_name = 'Comp. Sci.';
explain select * from student natural join takes;
explain select * from student as r natural join takes
where r.tot_cred < (select avg(tot_cred) from student);
set enable_hashjoin = false;
explain select * from student natural join takes;
explain select * from student as r natural join takes
where r.tot_cred < (select avg(tot_cred) from student);
Specify the queries that were executed with merge join. [2 marks]
2. Compose three queries by yourself, understand their query execution plans. [1 marks]