Q.1. The Employee table of our example has the following maximum attribute sizes: Fname (16bytes), Minit (1 byte), Lname (20 bytes), SSN (9 bytes), Bdate (9 bytes), Address (50 bytes), Sex (1 byte), Salary (8 bytes), SuperSSN (9 bytes) and DNo (2 bytes).

1. Assuming a block size of 1KB, and the table size of 6,000 records, how many blocks will be required to store the table ?

 

2. The data is stored in an unordered file (heap file). If the average seek time is 20 msec, and the average block transfer time is 1 msec, then what is the average time required to find a given record using linear search ? [assume that the effort in search once the data is loaded is negligible].

 

3. If the data is sorted by the SSN, then calculate the worst case time required to search a record, given the SSN.

 

4. You create an index file, using SSN as the index, for this table. What is the worst case time spent in searching for a record given the SSN ?

Q.2.

1. A table for PARTS data is stored as follows: two records can be stored per block; the records are stored using Hashing, with the Hashing function h(K) = K mod 8, where K is the value of the Hashing Key, called Part#. For the following Part# values, 2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 6975, 4981, 9208, put the records into blocks (assuming the first block of the table is at location block0001.
2. Calculate the average number of block accesses for a random retrieval using Part#.

Q. 3. May we have more than one primary indexes for a table ? Why/why not ? May we have more than one secondary index on a table ? Why/why not ?