Assume a table Emp of employee records with columns name, age, and salary. The t

Question

Assume a table Emp of employee records with columns name, age, and salary. 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 2KB pages (all full of records) on a single disk drive with 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 2KB page of data is 10 msecs. We are about to run a query on this Emp table to find the name of the employee with a given age, say 40; i.e., in SQL, "select name from Emp where age=40".

Assuming there are no indices of any kind for the Emp table, how long this operation will take?

Express your answer in both, number of disk accesses (I/O) and in hours. Assume the cost of everything else besides disk accesses is negligible and, to simplify calculations, assume 1 MB = 1,000 KB.

Explanation / Answer

CREATE TABLE Emp ( eid INTEGER, ename CHAR(10), age INTEGER, salary REAL,

PRIMARY KEY (eid) )

CREATE TABLE Works ( eid INTEGER, did INTEGER, pcttime INTEGER, PRIMARY KEY (eid, did), FOREIGN KEY (did) REFERENCES Dept, FOREIGN KEY (eid) REFERENCES Emp, ON DELETE CASCADE)

CREATE TABLE Dept ( did INTEGER, buget REAL, managerid INTEGER , PRIMARY KEY (did), FOREIGN KEY (managerid) REFERENCES Emp, ON DELETE SET NULL)

Consider a situation in which most of the queries are equality queries based on the search key field. It pays to build a hashed index on this field in which case we can get the required record in one or two disk accesses. A heap file organisation may require a full scan of the file to access a particular record. A very large file which requires a directory spanning in several pages.

bytes/disk = bytes/surf ace × surf aces/disk = 50, 000K × 10 = 500, 000K

If the disk platters rotate at 5400rpm, the time required for a rotation, which is the maximum rotational delay, is (1/5400)× 60 = 0.011seconds

The average rotational delay is half of the rotation time, 0.006 seconds.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.