Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Problem 1 11 pt each) Assume a table Emp(s, name, salary) of employee records, where ssn is the primary key. The total size of the

image text in transcribed
image text in transcribed
Problem 1 11 pt each) Assume a table Emp(s, name, salary) of employee records, where ssn is the primary key. The total size of the table is 34,560MB. The table (i.e., the records of the table) is stored in a heap le in chunks of 2KB pages (all full of records) on a single disk drive. In all questions below that involve indices, assume that the number of leaf pages for Btrees and the number of buckets for hash indices are the same with the number of pages required to store the table in the heap. a. 01: select name from Emp where ssn=1000 We are about to run the above query to nd the name of the employee with the given ssn of 1000. In a worstcase scenario, how long this operation will take? Express your answer in both, number of disk accesses (IIO) and in hours. The disk drive has the following characteristics: average seek time is 8 msecs, average rotational delay is 1 msec, and average transfer rate is 1 msec per 2KB block so, the total time to locate and transfer a disk block of data is 10 msecs. b. Assume that in addition to storing the table as described above, we also have available a B-tree built for this table on Emp.ssn - this is the search key of the B-tree. A data entry in the tree is a pair (ssn, RID of a data record in the heap). What is the approximate cost (in number of disk accesses) of executing the query 01 if we use the B-tree index? c. Now assume that we have a hash index on Emp.ssn. A data entry in the hash is a pair (ssn, RID of a data record in the heap). What is the approximate cost (in number of disk accesses) of executing the query Q1 if we use the hash index? d. What is the approximate cost (in number of disk accesses) of cases (b) and (c) above if the corresponding indices were built following the altemative-1 approach where data records are stored in the corresponding index instead of the heap. e. 02: select max sala from Em Assuming no indexing of any kind, i.e., we just have the records of the table in the heap, what is the cost of this query (in number of disk accesses)? f. Assume we have available a B-tree on Emp.salary. A data entry in the tree is a pair (salary, RID of a data record in the heap). What is the approximate cost (in number of disk accesses) of executing Q2 if we use this B-tree index? g. Assume we have available a hash index on Emp.salary. A data entry in the hash index is a pair (salary, RID of a data record in the heap). Is the hash index useful to compute the query? If no, explain. If yes, i.e., you think it is better to use the hash index instead of the heap, explain how you do this search and what is the approximate cost (in number of disk accesses) of executing (12 if we use the hash index? h. 03: insert a new employee record {1000, "mike", 100) Assuming no indexing of any kind, i.e., we just have the records of the table in the heap, what is the approximate cost of 03 (in number of disk accesses)? i. Assume we have available a B-tree on Emp.ssn. A data entry in the tree is a pair (ssn, RID of a data record in the heap). What is the approximate cost (in number of disk accesses) of 03 if we use the B-tree

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Chance And Chaos

Authors: David Ruelle

1st Edition

069121395X, 9780691213958

More Books

Students also viewed these Mathematics questions

Question

=+b) In which graph is a larger value of a used?

Answered: 1 week ago

Question

2. To store it and

Answered: 1 week ago