CS 405G: Introduction to Database Systems

CS 405G: Introduction to Database Systems 25 Exercise Chen Qian University of Kentucky

Project presentation • Demonstrate all functionalities of your application • Complete some requests • Introduce any additional features. • The bonus part, if you have completed it. Chen Qian @ University of Kentucky

4/24 Thursday Chen Qian @ University of Kentucky

4/29 Tuesday Chen Qian @ University of Kentucky

Exercise: Functional dependency • Suppose you are given a relation R with four attributes ABCD. For of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). • Candidate keys: B • R is in 2NF but not 3NF. Chen Qian @ University of Kentucky

Review • WorkOn (EID, Ename, email, PID, hour) • We say X ->Y is a partial dependency if there exist a X’ X such that X’->Y • e.g. EID, PID->Ename • Otherwise, X ->Y is a full dependency • e.g. EID, PID ->hours Chen Qian @ University of Kentucky

2nd Normal Form • Note about 2nd Normal Form • by definition,every nonprimary attribute is functionally dependent on every key of R • In other words, R is in its 2nd normal form if we could not find a partial dependency of a nonprimary key to a key in R. Chen Qian @ University of Kentucky

Third normal form • 3NF requires that there are no non-trivial functional dependencies of non-key attributes on something other than a superset of a candidate key. • Recall: non-trivial FD means LHS has no intersection with RHS. • In summary, all non-key attributes are mutually independent. Chen Qian @ University of Kentucky

Exercise: Functional dependency • Suppose you are given a relation R with four attributes ABCD. For of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). • Candidate keys: BD • R is in 1NF but not 2NF. Chen Qian @ University of Kentucky

Exercise: Functional dependency • Suppose you are given a relation R with four attributes ABCD. For of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). • 3. ABC → D, D → A • Candidate keys: ABC, BCD • R is in 3NF but not BCNF Chen Qian @ University of Kentucky

Boyce-Codd normal form (BCNF) • BCNF requires that there are no non-trivial functional dependencies of attributes on something other than a superset of a candidate key (called a superkey). • All attributes are dependent on a key, a whole key and nothing but a key (excluding trivial dependencies, like A->A). Chen Qian @ University of Kentucky

Exercise: Functional dependency • Suppose you are given a relation R with four attributes ABCD. For of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). • 4. A → B, BC → D, A → C • Candidate keys: A • R is in 2NF but not 3NF • (because of the FD: BC → D). Chen Qian @ University of Kentucky

Exercise: Functional dependency • Suppose you are given a relation R with four attributes ABCD. For of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). • 5. AB → C, AB → D, C → A, D → B • (a) Candidate keys: AB, BC, CD, AD • R is in 3NF but not BCNF • (because of the FD: C → A). Chen Qian @ University of Kentucky

Exercise: Concurrency control • Consider the following actions taken by transaction T 1 on database objects X and Y : • R(X), W(X), R(Y), W(Y) • 1. Give an example of another transaction T 2 that, if run concurrently to transaction T without some form of concurrency control, could interfere with T 1. • If the transaction T2 performed W(Y ) before T1 performed R(Y ), and then T2 aborted, the value read by T1 would be invalid and the abort would be cascaded to T1 (i.e. T1 would also have to abort). Chen Qian @ University of Kentucky

Exercise: Concurrency control • Consider the following actions taken by transaction T 1 on database objects X and Y : • R(X), W(X), R(Y), W(Y) • 2. Explain how the use of Strict 2PL would prevent interference between the two transactions. • Strict 2PL would require T2 to obtain an exclusive lock on Y before writing to it. This lock would have to be held until T2 committed or aborted; this would block T1 from reading Y until T2 was finished, thus there would be no interference. Chen Qian @ University of Kentucky

Exercise: disks • Explain the terms seek time, rotational delay, and transfer time. • 1. Seek time is the time taken to move the disk heads to the track on which a desired block is located. • 2. Rotational delay is the waiting time for the desired block to rotate under the disk head; it is the time required for half a rotation on average, and is usually less than the seek time. • 3. Transfer time is the time to actually read or write the data in the block once the head is positioned, i.e., the time for the disk to rotate over the block. Chen Qian @ University of Kentucky

Exercise: disks • If you have a large file that is frequently scanned sequentially, explain how you would store the pages in the file on a disk. • A: The pages in the file should be stored ‘sequentially’ on a disk. We should put two ‘logically’ adjacent pages as close as possible. In decreasing order of closeness, they could be on the same track, the same cylinder, or an adjacent cylinder. Chen Qian @ University of Kentucky

Exercise: disks • Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, five double-sided platters, and average seek time of 10 msec. • 1. What is the capacity of a track in bytes? What is the capacity of each surface? What is the capacity of the disk? • bytes/track = bytes/sector × sectors/track = 512 × 50 = 25K • bytes/surface = bytes/track × tracks/surface = 25K × 2000 = 50, 000K • bytes/disk = bytes/surface× surfaces/disk = 50, 000K × 5 × 2 = 500, 000K Chen Qian @ University of Kentucky

Exercise: disks • Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, five double-sided platters, and average seek time of 10 msec. • 2. Give examples of valid block sizes. Is 256 bytes a valid block size? 2048? 51200? • The block size should be a multiple of the sector size. We can see that 256 is not a valid block size while 2048 is. 51200 is not a valid block size in this case because block size cannot exceed the size of a track, which is 25600 bytes. Chen Qian @ University of Kentucky

Exercise: disks • Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, five double-sided platters, and average seek time of 10 msec. • 3. If the disk platters rotate at 5400 rpm (revolutions per minute), what is the maximum rotational delay? • If the disk platters rotate at 5400rpm, the time required for one complete rotation, which is the maximum rotational delay, is • The average rotational delay is half of the rotation time, 0.006 seconds. Chen Qian @ University of Kentucky

Exercise: disks • Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, five double-sided platters, and average seek time of 10 msec. • 4. If one track of data can be transferred per revolution, what is the transfer rate? • The capacity of a track is 25K bytes. Since one track of data can be transferred per revolution, the data transfer rate is Chen Qian @ University of Kentucky

Exercise: disks • Consider … average seek time of 10 msec. suppose that a block size of 1024 bytes is chosen. Suppose that a file containing 100,000 records of 100 bytes each is to be stored on such a disk and that no record is allowed to span two blocks. • 5. What time is required to read a file containing 100,000 records of 100 bytes each sequentially? • A file containing 100,000 records of 100 bytes needs 40 cylinders or 400 tracks in this disk. The transfer time of one track of data is 0.011 seconds. Then it takes 400 × 0.011 = 4.4seconds to transfer 400 tracks. • This access seeks the track 40 times. The seek time is 40 × 0.01 = 0.4seconds. Therefore, total access time is 4.4+ 0.4 = 4.8seconds. Chen Qian @ University of Kentucky

Exercise: disks • 6. What is the time required to read a file containing 100,000 records of 100 bytes each in a random order? Assume that each block request incurs the average seek time and rotational delay. • For any block of data, averageaccesstime = seektime + rotationaldelay + transfertime • The average access time for a block of data would be 16.44 msec. For a file containing 100,000 records of 100 bytes, the total access time would be 164.4 seconds. Chen Qian @ University of Kentucky

Tree structure • Each intermediate node can hold up to five pointers and four key values. Each leaf can hold up to four records • Name all the tree nodes to be fetched to answer the following query: “Get all records with search key greater than 38.” Chen Qian @ University of Kentucky

Tree structure • Name all the tree nodes to be fetched to answer the following query: “Get all records with search key greater than 38.” • I1, I2, and everything in the range [L2..L8]. Chen Qian @ University of Kentucky

Tree structure • inserting a record with search key 109 Chen Qian @ University of Kentucky

inserting a record with search key 109 Chen Qian @ University of Kentucky

Tree structure • deleting the record with search key 81 from the original tree. Chen Qian @ University of Kentucky

Chen Qian @ University of Kentucky

Tree structure • Name a search key value such that inserting it into the (original) tree would cause an increase in the height of the tree. Chen Qian @ University of Kentucky

Tree structure • We can infer several things about subtrees A, B, and C. First of all, they each must have height one, since their “sibling” trees (those rooted at I2 and I3) have height one. Also, we know the ranges of these trees (assuming duplicates fit on the same leaf): subtree A holds search keys less than 10, B contains keys ≥ 10 and < 20, and C has keys ≥ 20 and < 30. In addition, each intermediate node has at least 2 key values and 3 pointers. Chen Qian @ University of Kentucky

Tree structure • Suppose that this is an ISAM index. What is the minimum number of insertions needed to create a chain of three overflow pages? Chen Qian @ University of Kentucky

Tree structure • If this is an ISAM tree, we would have to insert at least nine search keys in order to develop an overflow chain of length three. These keys could be any that would map to L4, L5, L7, or L8, all of which are full and thus would need overflow pages on the next insertion. The first insert to one of these pages would create the first overflow page, the fifth insert would create the second overflow page, and the ninth insert would create the third overflow page (for a total of one leaf and three overflow pages). Chen Qian @ University of Kentucky

CS 405G: Introduction to Database Systems