Name of the employee with a given ssn

Assignment Help C/C++ Programming
Reference no: EM131100309

Assume a table Emp(ssn, 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 file 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 B-trees and the number of buckets for hash indices are the same with the number of pages required to store the table in the heap.

  1. We are about to run a query on this Emp table to find the name of the employee with a given ssn, say 1000; i.e., in SQL, "select name from Emp where ssn=1000". In a worst-case scenario, how long this operation will take? Express your answer in both, number of disk accesses (I/O) 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.
  2. Assume that in addition to storing the table as described above, we also have available a B-tree built for this table on 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 in (a) if we use the B-tree index? 
  3. Now assume that we have a hash index on ssn for the Emp table. 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 in (a) if we use the hash index? 
  4. Now, consider this query: find the maximum salary in the Emp table; i.e., in SQL, "select max(salary) from Emp". 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)?
  5. 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 the query in (d) if we use this B-tree index?
  6. 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 the query if we use the hash index? 
  7. Now, consider this update: 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 this operation (in number of disk accesses)?
  8. For the operation in (g), and assuming there is a B-tree as described in (e), what is the approximate cost (in number of disk accesses) of executing the operation if we use the B-tree?
  9. For the query in (g), and assuming there is a hash index as described in (f), what is the approximate cost (in number of disk accesses) of executing the operation if we use the hash index?  
  10. For questions (b) and (c). If , What is the approximate cost (in number of disk accesses) of executing the query in (a) if we use the corresponding index, if the indices were built following the alternative-1 where instead of having the records in the heap file, they are stored in the corresponding index

Note

  1. Assume the cost of everything else besides disk accesses is negligible.
  2. For question (a), to simplify calculations, assume 1 MB = 1,000 KB.
  3. Your answers in cost related questions must be plain numbers (e.g., 5, 90, 90.56) that include no formulas and/or computation of any kind. For example, the following types of answers will automatically get zero with no further consideration:  log base 2 of some N; cube of N square divided by log base 2 of N cube multiplied by log base 10 N; big O(log(N)); etc. However, you must explain how you  came up with your final (number) answer by indicating the steps.

Reference no: EM131100309

Questions Cloud

Maximal depth of decision tree derived : (1) How many scans of the database does your algorithm take if the maximal depth of decision tree derived is 5? (2) What is the maximum memory space your algorithm will use in your tree induction?
Formulate an ip model for this problem : A machine shop makes two products. Each unit of the first product requires 3 hours on machine 1 and 2 hours on machine 2. Each unit of the second product requires 2 hours on machine 1 and 3 hours on machine 2. Machine 1 is available only 8 hours p..
Find the percentage error over the band : find the percentage error over the band in making this approximation.
Write paper on should america go to second world war or not : Write a paper about should america go to Second World War or not. Have a clear thesis statement You are making an argument Plan your essay Integrate your quotes six pages double space, with Chicago Style.
Name of the employee with a given ssn : We are about to run a query on this Emp table to find the name of the employee with a given ssn, say 1000; i.e., in SQL, "select name from Emp where ssn=1000". In a worst-case scenario, how long this operation will take? Express your answer in bot..
What advantages might cold case investigators have : It is often said that, if a homicide is not solved within the first 48 hours, the chance of solving the crime drops dramatically. That is true. However, what advantages might cold case investigators have over the initial or original investigators?..
Why the central limit theorem does not apply here : Give a qualitative explanation of what the limiting distribution of G00 looks like. If this sort of thing amuses you, it is not hard to find the exact distribution.
How the united states went to war against the confederacy : Write a paper that will discuss how the United States went to war against the Confederacy in 1861. Every time you use material from another source it must be cited.
Trends of data warehousing and data mining : 1. Provide an executive overview that addresses the following: a. Explain the benefits and current trends of data warehousing and data mining. b. Provide two (2) examples of quality companies successfully using a data warehouse to support your answer..

Reviews

Write a Review

C/C++ Programming Questions & Answers

  Create program that uses functions and reference parameters

Create program that uses functions and reference parameters, and asks user for the outside temperature.

  Write a program using vectors and iterators

Write a program using vectors and iterators that allows a user to maintain a personal list of DVD titles

  Write the code required to analyse and display the data

Calculate and store the average for each row and column. Determine and store the values for the Average Map.

  Write a webservices application

Write a webservices application that does a simple four function calculator

  Iimplement a client-server of the game

Iimplement a client-server version of the rock-paper-scissors-lizard-Spock game.

  Model-view-controller

Explain Model-View-Controller paradigm

  Design a nested program

How many levels of nesting are there in this design?

  Convert celsius temperatures to fahrenheit temperatures

Write a C++ program that converts Celsius Temperatures to Fahrenheit Temperatures.

  Evaluate and output the value in the given base

Write C program that will input two values from the user that are a Value and a Base with which you will evaluate and output the Value in the given Base.

  Design a base class shape with virtual functions

Design a base class shape with virtual functions

  Implementation of classes

Implementation of classes Chart and BarChart. Class barChart chould display a simple textual representation of the data

  Technical paper: memory management

Technical Paper: Memory Management, The intent of this paper is to provide you with an in depth knowledge of how memory is used in executing, your programs and its critical support for applications.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd