Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Material Type: Notes; Class: Database Sys Implement; Subject: Computer Science; University: Georgia Institute of Technology-Main Campus; Term: Unknown 1989;
Typology: Study notes
1 / 25
This page cannot be seen from the preview
Don't miss anything!
1
Ling Liu Associate Professor College of Computing, Georgia Tech
DB26 (^) FinalReview
z Chapter 1 z Chapter 2 – Section 2.1 to 2. z Chapter 3 – Section 3.1 to 3. z Chapter 4 z Chapter 5 – Section 5.1 to 5.3. z Chapter 6 – Section 6.1 to 6.4, Section 6.6 to 6. z Chapter 7 – Section 7.1 to 7.5.3, Section 7.7 to 7.7. z Chapter 8-9-10- Æ skim 9. Æ skim 10.4, 10.5, 10.6, 10.
3
Open Book
DB26 (^) FinalReview
Data Storage Review
Memory Hierarchy z Cache (on-board+level2) Æ R/W between cache and processor: 1MG with <=10 nanoseconds Æ R/W between cache and memory: 100 nanoseconds z Memory (100MG) Æ Random access, Typical time to access data: 10-100 nanoseconds z Disk and Virtual Memory (>=4GB) Æ Kilo,Mega, Giga, Tera, Peta … Disk Organization z Platter = 2 surfaces z Tracks, Sectors, gaps z Cylinder, disk head, disk controller z Seek time, rotation speed (rpm)
7
Seek Time: Time to position the head assembly at the proper cylinder
3 or 5x
x 1 N Cylinders Traveled
Time
Beginning at some value x for a distance of one cylinder
∑ ∑ SEEKTIME (i → j)
S=
N(N-1)
i=1 j= j≠i
DB26 (^) FinalReview
Rotational Delay The time for the disk to rotate so the first of the sectors containing the block reaches the head (rotation latency) A typical disk rotates completely takes 10ms
Head Here
Block I Want
9
Transfer Time: t
DB26 (^) FinalReview
Data Storage Q/A Question 1: (Page 39 Exercise 2.2.1) Given that a disk has the following characteristics: z 10 surfaces, with 10,000 tracks each z Each track holds an average of 1000 sectors of 512 bytes z The time it takes the head to move n tracks is 1+ 0.001n ms What is the capacity of the disk? z The disk has 10 * 10,000 = 100,000 tracks. z The average track has 1000 * 512 = 512,000 bytes. z Thus, the capacity is 51.2 gigabytes. What is the maximum seek time z The maximum seek time occurs when the heads have to move across all the tracks. z Let n = 10,000 starting from one. Thus 1+0.001 n = 1 + 0.001*10,000 = 11 milliseconds.
13
B-Trees
z Number of levels varies with size of the data file being indexed, but often 3 z Useful for primary, secondary indexes
z All nodes have the same format: n keys, n+1 pointers
(1) All leaves at same lowest level (balanced tree) (2) Pointers in leaves point to records except for “sequence pointer”
DB26 (^) FinalReview
Non-leaf (non-root) n+1^ n^ (n+1)/^2 ^ (n+1)/^2 - 1 Leaf (non-root) n+1^ n Root n+1 n 1 1
Max Max Min Min ptrs keys ptrs→data keys
(n+ 1) / 2 (n+ 1) / 2
Note by Hector Garcia-Molina
15
B+Tree ExampleRoot n=
Non-leaf (non-root) 4 3 2 1 Leaf (non-root) 4 3 Root 4 3 1 1
Max Max Min Min ptrs keys ptrs→data keys
DB26 (^) FinalReview
B-tree example n=
19
B+Tree Indexing
Answer: (4) How about the height of the index tree? If leave nodes are 85% occupied they actually use 227*85% = 194 data pointers. The leaf level will require 90910/194 = 468.6 = 469 nodes. The level above that will require 3 nodes which are all children of the root. Then the root
(5) What is the total? The total is 90910+469+3+1=91383 blocks
DB26 (^) FinalReview
Hashing Tables
z If a record with search key K exists, then it must be in bucket h (K).
z Cuts search down by a factor of B z One disk I/O of there is only one block per bucket
Extract from Note by J. Ullman
21
EXAMPLE 2 records/bucket
0
1
2
3
d
a c b
e
Extract from Note by H.G.M.
DB26 (^) FinalReview
Efficiency of Hash Table Indexes
z Performance of a hash table may degrade if there are too many records in one bucket
z Methods to allow graceful growth of number of buckets and to maintain the above relationship z Extensible Hashing: grows B by doubling it each time z Linear Hashing: grows B by add one more bucket each time
25
Types of Database Queries
z Specify values of n dimensions (n>=1)
z Specify ranges for n dimensions (n>=1)
z Closest point to a given point in a n dimensional space
z given a point, find where (e.g., in which shape) it is located
DB26 (^) FinalReview
parse
convert
apply laws
estimate result sizes
consider physical plans estimate costs
pick best
execute
{(P1,C1),(P2,C2)...}
Pi
answer
SQL query
parse tree
logical query plan
“improved” l.q.p
l.q.p. +sizes
statistics
Note by Hector Garcia-Molina
Query Processing/Optimization
27
Important Contents
z Algebraic Transforms Rules z Good Transformation
z Result size z IOs
DB26 (^) FinalReview
Estimating cost of query plan
Selection, Projection, Join
Selection, Projection, Join, Sort Count # of disk blocks that must be read (or written) to execute query plan Factors considered z Relations Contiguous or not z Index or not z Hash or not Factors affecting the Ios: z Join ordering z Join Algorithm used Æ iterative, sort-merge, index, hash
31
Failure Recovery DB Operations and Transactions Atomic transaction Transaction Failure (due to system crashes or media failure) Recovery Techniques z Logging z Logging schemes Æ Undo Log + WAL ÆRedo Log ÆUndo/Redo Log
DB26 (^) FinalReview
Example Question
Suppose A and B are database elements and their initial values are both 0. After transaction T executed their values are both changed to 1. The content for log are given below:
33
Concurrency Control
Correctness (informally)
DB26 (^) FinalReview
Concepts
37
Exercise 3:
DB26 (^) FinalReview
Question(a): is this schedule conflict serializable? Which serial schedule it is equivalent to?
T1 T Read(A); A ← A+ Write(A);
Write(A); Read(B); B ← B+100; Write(B);
Write(B);
Answer: Yes. It is equivalent to serial schedule of T proceeding T
39
Question (b): is this schedule conflict serializable? Which serial schedule it is equivalent to?
T1 T Read(A); A ← A+ Write(A); Read(A);A ← A×2; Write(A); Read(B);B ← B×2; Write(B); Read(B); B ← B+100; Write(B);
Answer: Not serializable, and it is NOT equivalent to any serial schedule
DB26 (^) FinalReview
Locking Schemes
