Page 2
Q1. b, d
Q2. a,c
Q3. a,b,c,e
Page 3
Sample Solutions to Midterm #1
Q4 The data table can only be sorted/ordered in one way at a time, and this is called the clustering order of the data. The index on those field(s) is called a clustering index, and the determination of the clustering field is done by the DBA.
Q5. Steal a page (ARIES approach). This means we won’t have to abort the transaction.
Q6. When reading k pages, we prefer a sequential read because it minimizes the seek and rotational latency times. Thus, we’ll read a bunch of contiguous pages, rather than a bunch of pages that are scattered over the disk drive.
Page 4
Q7. 8 tracks/cylinder * 16,384 sectors/track * (1 page / 2 sectors) = 65,536 pages/cyl.
Q8.
A doctor record has size: 10 * 4 bytes + 3 * 25 bytes = 115 bytes/record.
floor( 4096 bytes/page / 115 bytes/record ) = 35 records/page
35 rec/page * 65,536 pages/cyl = 2,293,760 rec/cyl.
Page 5 Q9.
Read 5 full cylinders AND account for 5 one-cylinder movements of the disk assembly/arm.
5 cylinders * 8 tracks/cylinder = 40 tracks 40 revolutions or “spins” are needed
1 spin = ??? milliseconds
o 14,000rpm=14,000rev/1minute=14,000rev/60sec.*1sec./1,000 ms = 0.233 rev/ms (OK to write 0.2333333 rec/ms)
Equivalently, if you take the reciprocal, that’s 4.292 ms/rev (or 4.28571 ms/rev, if you go to more decimal places)
Thus, 40 rev * 4.292 ms/rev = 171.680 ms
Don’t forget to add in 5 seeks at 1 cylinder’s worth of movement:
o 5*(1ms+1/800ms)=5*1.001ms=5.005ms
Grand total = 171.680 ms + 5.005 ms = 176.685 ms (or very close to it)
Q10.
32,000 cyl * 8 tracks/cyl * 16,384 sectors/track * 2048 bytes/sector = 8.589934592 * 1012 bytes
Divide by 1,024 bytes/KB
Divide by 1,024 KB/MB
Divide by 1,024 MB/GB
Divide by 1,024 GB/TB
to get 8,388,608,000 KB. to get 8,192,000 MB.
to get 8,000 GB.
to get 7.8125 TB.
Equivalently, just divide 8.589934592 * 1012 bytes by 240 bytes/TB.
Page 6
Q11. Reference string = 10, 50, 90, 50, 35, 25
Q12. Clock Algorithm. Trivia: The page numbers consist of Seattle Seahawks players.
Ref. String
3
82
3
89
3
24
3
15
F1
(25 / 1)
25 / 0
(25 / 0)
(25 / 0)
89 / 1
89 / 1
89 / 0
(89 / 0)
15 / 1
F2
24 / 0
3/1
3/1
3/1
(3 / 1)
(3 / 1)
24 / 1
24 / 1
(24 / 1)
F3
89 / 0
(89 / 0)
82 / 1
82 / 1
82 / 1
82 / 1
(82 / 0)
3/1
3/1
PF?
Y
Y
Y
N
Y
N
Y
Y
Y