Sample Solutions for Quiz 4, Question 6, Calculations
6 MARKS – We won’t double-deduct unless it trivializes the solution. Watch the
context.
Version 1: Treatment has 1,000,000 records. Each record is 250 bytes. 2,500 distinct procedures. 25 bytes for procedure.
a) [1 mark] floor(4096 bytes/page / (25 + 10) bytes/data entry) = 117 DE/page a. -0.5 if they said 118
b) [1 mark] 1,000,000 DE / 2,500 procedures = 400 DE/procedure
c) [2 marks] ceiling(1.2 – 1) + ceiling(400/117) + 400(1) = 1 + 4 + 400 = 405 page
I/Os (approximately)
a. Don’t worry if it’s 1.2 or 0.2. Don’t worry about the ceiling for that,
either.
b. 1 mark for computing ceiling(400/117) = 4 pages for the index
i. OK if they said 3 pages
c. 1 mark for computing 400 * 1 page = 400 data table pages to look up
(because the data is not clustered by the procedure order)
d) [1 mark] floor(4096/250) = 16 records per page; therefore, there are 1,000,000 /
16 = 62,500 data pages
a. If students did a calculation to determine the number of pages to store the
output records from the query, that’s not desired, but OK. I think it works
out to at most 1.
e) [1 mark] 1,000,000 records / (3 * 365) days = 913 or 914 records/day
b. If students did a calculation to determine the number of output queries per day, that’s not desired, but OK. I think it works out to at most 1.
Version 3: Treatment has 1,000,000 records. Each record is 150 bytes. 2,750 distinct procedures. 30 bytes for procedure.
a) [1 mark] floor(4096 bytes/page / (30 + 10) bytes/data entry) = 102 DE/page a. -0.5 if they wrote 103
b) [1 mark] 1,000,000 DE / 2,750 procedures = 363.6 or 363 or 364 DE/procedure
c) [2 marks] ceiling(1.2 – 1) + ceiling(363/102) + 363 = 1 + 4 + 363 = 368 page
I/Os (approximately)
a. Don’t worry if it’s 1.2 or 0.2. Don’t worry about the ceiling for that.
b. 1 mark for computing ceiling(363/102) = 4 pages for the index
i. OK if they said 3 pages
c. 1 mark for computing 363 data table pages to look up (because the data is
not clustered by the procedure order)
d) [1 mark] floor(4096/150) = 27 records per page; therefore, there are 1,000,000 /
27 = 37,038 data pages (OK if they wrote 37,037)
a. -0.5 if they did a total bytes calculation and ignored the floor
1
c. If students did a calculation to determine the number of pages to store the output records from the query, that’s not desired, but OK. I think it works out to at most 1.
e) [1 mark] 1,000,000 records / (2 * 365) days = 1369 or 1370 records/day
d. If students did a calculation to determine the number of pages to store the
output records from the query, that’s not desired, but OK. I think it works out to at most 1.
Version 2: Patient has 250,000 records. Each record is 250 bytes. 500 distinct diseases. 28 bytes for disease.
b) [1 mark] floor(4096 bytes/page / (28 + 10) bytes/data entry) = 107 DE/page a. -0.5 if they said 108
c) [1 mark] 250,000 DE / 500 diseases = 500 DE/disease
d) [2 marks] ceiling(1.2 – 1) + ceiling(500/107) + 500(1) = 1 + 5 + 500 = 506 page
I/Os (approximately)
a. Don’t worry if it’s 1.2 or 0.2. Don’t worry about the ceiling for that,
either.
b. 1 mark for computing ceiling(500/107) = 5 pages for the index
i. OK if they said 4 pages
c. 1 mark for computing 500 * 1 page = 500 data table pages to look up
(because the data is not clustered by the disease order)
e) [1 mark] floor(4096/250) = 16 records per page; therefore, there are 250,000 / 16
= 15,625 data pages
a. -0.5 if they did a total bytes calculation and ignored the floor
b. If students did a calculation to determine the number of pages to store the
output records from the query, that’s not desired, but OK. I think it works
out to at most 1.
f) [1 mark] 250,000 records / (2 * 365) days = 342 or 343 records/day
a. If students did a calculation to determine the number of pages to store the output records from the query, that’s not desired, but OK. I think it works out to at most 1.
Version 4: Patient has 250,000 records. Each record is 150 bytes. 750 distinct diseases. 34 bytes for disease.
a) [1 mark] floor(4096 bytes/page / (34 + 10) bytes/data entry) = 93 DE/page a. -0.5 if they said 94
b) [1 mark] 250,000 DE / 750 diseases = 333 or 334 DE/disease
c) [2 marks] ceiling(1.2 – 1) + ceiling(333/93) + 333(1) = 1 + 4 + 333 = 338 page
I/Os (approximately)
a. Don’t worry if it’s 1.2 or 0.2. Don’t worry about the ceiling for that,
either.
2
b. 1 mark for computing ceiling(333/93) = 4 pages for the index i. OK if they said 3 pages
c. 1 mark for computing 333 * 1 page = 333 data table pages to look up (because the data is not clustered by the disease order)
d) [1 mark] floor(4096/150) = 27 records per page; therefore, there are 250,000 / 27 = 9,259 or 9,260 data pages
a. -0.5 if they did a total bytes calculation and ignored the floor
b. If students did a calculation to determine the number of pages to store the
output records from the query, that’s not desired, but OK. I think it works
out to at most 1.
e) [1 mark] 250,000 records / (3 * 365) days = 228 or 229 records/day
a. If students did a calculation to determine the number of pages to store the output records from the query, that’s not desired, but OK. I think it works out to at most 1.
3