Sample Solutions for Quiz 4, Question 4, INL Join Calculations
8 MARKS (Note: Not much explanation is needed from the students. But what they have provided may be helpful for detecting part marks—but it also depends on the context, so, when in doubt, look at the overall calculation. We won’t award marks when it trivializes the solution, in which case the double-deduction guideline may not apply. I’m providing much more information here than what is needed from the students.)
Version 1: Reserves = 45,000 pages @ 150 rec/page; Sailors = 150 pages @ 30 rec/page
a) [1 mark] 3 index pages for the B+ tree (because of the unclustered B+ tree index on sname; note that all those named “Jack” should be on one index page since an index page holds 100 data entries; and of 4,500 sailors, only 1/300 is “Jack”)
a. Award 0.5 marks: Some students may have mixed the index pages with the data pages = 3 + 15 = 18.
b) [1 mark] 15 data pages (there are 4,500 sailors and only 1/300 of them are “Jack” = 15; and the data is not clustered by sname, so one data page for each)
c) [0.5 marks] 15 records qualify from the left child
d) [0.5 marks] (a) + (b) = 3 + 15 = 18 page I/Os (or whatever their (a) + (b) works
out to be)
e) [2 marks] There are (45,000 * 150) reservations / 4,500 sailors = 1,500
reservations/sailor. So, 1 sid matches 1,500 reservations or data entries (DE). The index calculation is approximately: (3 – 1) + ceiling(1,500 DE / 292 DE/page) = 2 + 6 = 8 index pages (OK for 3 + 6 = 9, or even 2 + 5.13 = 7.13, etc.)
a. Award0.5marksforthe3(orthe3–1=2)
b. Award 0.5 marks for 1,500 DE
c. Award 1 mark for ceiling(1,500 / 292) = ceiling(5.14) = 6
i. If they used a fraction (like 5.14) in the rest of their calculations, that’s fine.
f) [2 marks] Reserves is clustered on sid; therefore, there are 1,500 reservations per sailor / 150 reservations/page = 10 data pages/sailor (OK if they said 11 pages)
a. You can award 1 mark for (whatever # of reservations per sailor they calculated) / 150 reservations per page.
b. Or award one mark if they got 1,500 right, but the denominator is wrong.
g) [1 mark] Total is approximately 15 * (sum of (e) + (f)) = 15 * (8 + 10) = 15 *
18 = 270 page I/Os (don’t worry about rounding up or down)
a. Or: 15*(2+5.14+10)=257or258pageI/Os
b. Or: 15 * (2 + 5 + 10) = 255 page I/Os, etc.
c. Don’t double-deduct if they used a number from an earlier calculation.
For example, if they wrote 30 qualifying records and they re-used 30, that’s OK.
1
Version 2: Reserves = 9,000 pages @ 100 rec/page; Sailors = 120 pages @ 30 rec/page
a) [1 mark] 3 index pages for the B+ tree (because of the unclustered B+ tree index on sname; note that all those named “Jack” should be one index page since an index page holds 100 data entries; and of 3,600 sailors, only 1/300 is “Jack”)
a. Award 0.5 marks: Some students may have mixed the index pages with the data pages = 3 + 12 = 15.
b) [1 mark] 12 data pages (there are 3,600 sailors and only 1/300 of them are “Jack” = 12; and the data is not clustered by sname, so one data page for each)
c) [0.5 marks] 12 records qualify from the left child
d) [0.5 marks] (a) + (b) = 3 + 12 = 15 page I/Os (or whatever their (a) + (b) works
out to be)
e) [2 marks] There are (9,000 * 100) reservations / 3,600 sailors = 250
reservations/sailor. So, 1 sid matches 250 reservations or data entries (DE). The index calculation is approximately: (3 – 1) + ceiling(250 DE / 292 DE/page) = 2 + ceiling(0.86) = 3 index pages (OK for 3 + 1 = 4, or even 2 + 0.86 = 2.86, etc.)
a. Award0.5marksforthefirst3(orthe3–1=2)
b. Award 0.5 marks for 250 DE
c. Award 1 mark for ceiling(250 / 292) = ceiling(0.86) = 1
i. If they used a fraction (like 0.86) in the rest of their calculations, that’s fine.
f) [2 marks] Reserves is clustered on sid. There are 250 reservations per sailor / 100 reservations/page = 2.5 data pages/sailor, or ceiling(2.5) = 3 data pages /sailor
a. You can award 1 mark for the correct numerator of 250 (or whatever # of reservations per sailor they calculated earlier).
b. You can award one mark for the right denominator: 100.
g) [1 mark] Total is approximately 12 * (sum of (e) + (f)) = 12 * (3 + 3) = 72 page
I/Os (don’t worry about rounding up or down)
a. Or: 12*(2+1+3)=72pageI/Os
b. Or: 12*(2+1+2.5)=66pageI/Os,etc.
c. Don’t double-deduct if they used a number from an earlier calculation.
For example, if they wrote 30 qualifying records and they re-used 30, that’s OK.
2
Version 3: Reserves = 40,000 pages @ 75 rec/page; Sailors = 250 pages @ 30 rec/page
a) [1 mark] 3 index pages for the B+ tree (because of the unclustered B+ tree index on sname; note that all those named “Jack” should be one index page since an index page holds 100 data entries; and of 7,500 sailors, only 1/300 is “Jack”)
a. Award 0.5 marks: Some students may have mixed the index pages with the data pages = 3 + 25 = 28.
b) [1 mark] 25 data pages (there are 7,500 sailors and only 1/300 of them are “Jack” = 25; and the data is not clustered by sname, so one data page for each)
c) [0.5 marks] 25 records qualify from the left child
d) [0.5 marks] (a) + (b) = 3 + 25 = 28 page I/Os (or whatever their (a) + (b) works
out to be)
e) [2 marks] There are (40,000 * 75) reservations / 7,500 sailors = 400
reservations/sailor. So, 1 sid matches 400 reservations or data entries (DE). The index calculation is approximately: (3 – 1) + ceiling(400 DE / 292 DE/page) = 2 + ceiling(1.37) = 4 index pages (OK for 3 + 2 = 5, or even 2 + 1.37 = 3.74, etc.)
a. Award0.5marksforthefirst3(orthe3–1=2)
b. Award 0.5 marks for 400 DE
c. Award 1 mark for ceiling(400 / 292) = ceiling(1.37) = 2
i. If they used a fraction (like 1.37) in the rest of their calculations, that’s fine.
f) [2 marks] Reserves is clustered on sid. There are 400 reservations per sailor / 75 reservations/page = 6 data pages to visit (OK if they said 7)
a. You can award 1 mark for the correct numerator of 400 (or whatever # of reservations per sailor they calculated earlier).
b. You can award one mark for the right denominator: 75.
g) [1 mark] Total is approximately 25 * (sum of (e) + (f)) = 25 * (4 + 6) = 250
page I/Os (don’t worry about rounding up or down)
a. Or: 25*(2+1.37+6)=234or235pageI/Os
b. Or: 25*(3+2+6)=265pageI/Os,etc.
c. Don’t double-deduct if they used a number from an earlier calculation.
For example, if they wrote 30 qualifying records and they re-used 30, that’s OK.
3
Version 4: Reserves = 24,000 pages @ 75 rec/page; Sailors = 150 pages @ 20 rec/page
a) [1 mark] 3 index pages for the B+ tree (because of the unclustered B+ tree index on sname; note that all those named “Jack” should be one index page since an index page holds 100 data entries; and of 3,000 sailors, only 1/300 is “Jack”)
a. Award 0.5 marks: Some students may have mixed the index pages with the data pages = 3 + 10 = 13.
b) [1 mark] 10 data pages (there are 3,000 sailors and only 1/300 of them are “Jack” = 10; and the data is not clustered by sname, so one data page for each)
c) [0.5 marks] 10 records qualify from the left child
d) [0.5 marks] (a) + (b) = 3 + 10 = 13 page I/Os (or whatever their (a) + (b) works
out to be)
e) [2 marks] There are (24,000 * 75) reservations / 3,000 sailors = 600
reservations/sailor. So, 1 sid matches 600 reservations or data entries (DE). The index calculation is approximately: (3 – 1) + ceiling(600 DE / 292 DE/page) = 2 + ceiling(2.05) = 5 index pages (OK for 3 + 2 = 5, or even 2 + 2.05 = 4.10, etc.)
a. Award0.5marksforthefirst3(orthe3–1=2)
b. Award 0.5 marks for 600 DE
c. Award 1 mark for ceiling(600 / 292) = ceiling(2.05) = 3
i. If they used a fraction (like 2.05) in the rest of their calculations, that’s fine.
f) [2 marks] Reserves is clustered on sid. There are 600 reservations per sailor / 75 reservations/page = 8 data pages to visit (OK if they said 9)
a. You can award 1 mark for the correct numerator of 600 (or whatever # of reservations per sailor they calculated earlier).
b. You can award one mark for the right denominator: 75.
g) [1 mark] Total is approximately 10 * (sum of (e) + (f)) = 10 * (5 + 8) = 130
page I/Os (don’t worry about rounding up or down)
a. Or: 10*(2+2.05+8)=120or121pageI/Os
b. Or: 10*(2+2+8)=120pageI/Os,etc.
c. Don’t double-deduct if they used a number from an earlier calculation.
For example, if they wrote 30 qualifying records and they re-used 30, that’s OK.
4