a) To determine the cost of sorting the relation in seconds with b b = 1 , and b b = 100 . Given: Sort of relation = 40 G B Blocks = 4 K B Memory size = 40 M B Seek cost = 5 m s Disk transfer rate = 40 M B Explanation: Calculate the number of relation blocks and number of blocks by using the calculations given below: Number of relation blocks b r = relation size block size = 40GB 4 K B = 40 × 10 9 4 × 10 3 = 40 × 10 9-3 4 =10000000 blocks Number of memory blocks: M= memory size block size = 40MB 4KB = 10000 Initial number of runs, b r M = 10000000 10000 = 1000 Calculate the number of block transfers, b r 2 × 1 + 1 = 10 , 000 , 000 3 = 30,000,000 blocks Calculating the number of seeks for b b = 1 : 2 b r M + b r b b 2 × 1 − 1 = 2 10000000 10000 + 10000000 1 1 = 2000 + 10000000 = 10002000 seeks Total sorting cost in seconds, = number of block transfer× block size disk transfer rate +number of seeks cost of seeks = 30,000,000× 4KB 40MB +10002000 5 1000 = 3000 + 50010 = 53010 seconds Calculating the number of seeks for b b = 100 : 2 b r M + b r b b 2 × 1 − 1 = 2 10000000 10000 + 10000000 100 1 = 2000 + 100000 = 102000 seeks Total sorting cost in seconds, = number of block transfer× block size disk transfer rate +number of seeks cost of seeks = 30,000,000× 4KB 40MB +102000 5 1000 = 3000 + 510 = 3510 seconds Conclusion: Hence, the total sorting costs for b b = 1 is 53010 seconds and for b b = 100 is 3510 seconds. b) To determine the required number of merge passes. Number of required merge pass: log M − 1 b r M = log 10 , 000 − 1 10 , 000 , 000 10 , 000 = log 9999 1000 = 0 .7500 ≈ 1 Therefore, the required number of merges is equal to 1. c) To calculate the total cost of sorting by considering the provided data. Given: Sort of relation = 40 G B Blocks = 4 K B Memory size = 40 M B latency = 20 m s transfer rate = 400 M B Calculate the number of relation blocks and number of blocks by using the calculations given below: Number of relation blocks b r = relation size block size = 40GB 4 K B = 40 × 10 9 4 × 10 3 = 40 × 10 9-3 4 =10000000 blocks Number of memory blocks: M= memory size block size = 40MB 4KB = 10000 Initial number of runs, b r M = 10000000 10000 = 1000 Calculate the number of block transfers, b r 2 × 1 + 1 = 10 , 000 , 000 3 = 30,000,000 blocks Calculating the number of seeks for b b = 1 : 2 b r M + b r b b 2 × 1 − 1 = 2 10000000 10000 + 10000000 1 1 = 2000 + 10000000 = 10002000 seeks Total sorting cost in seconds, = number of block transfer× block size disk transfer rate +number of seeks cost of seeks = 30,000,000× 4KB 400MB +10002000 20 1000 = 201240 seconds Calculating the number of seeks for b b = 100 : 2 b r M + b r b b 2 × 1 − 1 = 2 10000000 10000 + 10000000 100 1 = 2000 + 100000 = 102000 seeks Total sorting cost in seconds, = number of block transfer× block size disk transfer rate +number of seeks cost of seeks = 30,000,000× 4KB 400MB +102000 20 1000 = 3240 seconds Conclusion: Therefore, the required sorting cost in seconds for b b = 1 is 201240 seconds and for b b = 100 is 3240 seconds.

Database System Concepts

7th Edition

ISBN: 9780078022159

Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher: McGraw-Hill Education

See similar textbooks

Expert Solution & Answer

Want to see the full answer?

Check out a sample textbook solution

See solution

Chapter 15, Problem 16PE

Chapter 15, Problem 18E

Students have asked these similar questions

Suppose you need to sort a relation of 50 gigabytes, with 4 kilobyte blocks, using a memory size of 40 megabytes. Suppose the cost of a seek is 4 milliseconds, while the disk transfer rate is 50 megabytes per second. a. Find the cost of sorting the relation, in seconds, with bb = 100 b. How many merge passes are required?

Suppose you need to sort a relation of 40 gigabytes, with 4-kilobyte blocks,using a memory size of 40 megabytes. Suppose the cost of a seek is 5 milliseconds, while the disk transfer rate is 40 megabytes per second. Suppose a flash storage device is used instead of a disk, and it has a latency of 20 microsecond and a transfer rate of 400 megabytes per second. Recompute the cost of sorting the relation, in seconds, with bb = 1 and with bb = 100, in this setting.

Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a non-key field, and the file organization is unspanned. The file is stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file, and a multilevel index scheme is used to store the secondary index, the number of first-level and second-level blocks in the multilevel index are respectively

Knowledge Booster

Similar questions

SEE MORE QUESTIONS

Recommended textbooks for you

Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education