Question;1- A;file has r=20,000 STUDENT records of fixed-length. Each record has the;following fields: NAME (30 bytes), SSN (9 bytes), ADDRESS (40 bytes), PHONE (9;bytes), BIRTHDATE (8 bytes), SEX (1 byte), MAJORDEPTCODE (4 bytes);MINORDEPTCODE (4 bytes), CLASSCODE (4 bytes, integer), and DEGREEPROGRAM (3;bytes). An additional byte is used as a deletion marker.;Consider B=512;Calculate the;record size R in bytes.;Calculate the;blocking factor bfr and the number of file blocks b assuming an unspanned;organization.;Calculate the;average time it takes to find a record by doing a linear search on the;file if (i) the file blocks are stored contiguously and double buffering;is used, and (ii) the file blocks are not stored contiguously.;Assume the file;is ordered by SSN, calculate the time it takes to search for a record;given its SSN value by doing a binary search.;2 -;A PARTS file with Part# as hash key includes records with the following Part#;values: 2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750;6975, 4981, 9208. The file uses 8 buckets, numbered 0 to 7. Each bucket is one;disk block and holds two records. Load these records into the file in the given;order using the hash function h(K)=K mod 8. Calculate the average number of;block accesses for a random retrieval on Part#.3 - Suppose that a file initially contains r=120,000 records of;R=200 bytes each in an unsorted (heap) file. The block size B=2400 bytes, the;average seek time s=16 ms, the average rotational latency rd=8.3 ms and the;block transfer time btt=0.8 ms. Assume that 1 record is deleted for every 2;records added until the total number of active records is 240,000.;How many block;transfers are needed to reorganize the file?;How long does it;take to find a record right before reorganization?;How long does it;take to find a record right after reorganization?;4 -;A PARTS file with Part# as key field includes records with the following Part#;values: 23, 65, 37, 60, 46, 92, 48, 71, 56, 59, 18, 21, 10, 74, 78, 15, 16, 20;24, 28, 39, 43, 47, 50, 69, 75, 8, 49, 33, 38. Suppose the search field values;are inserted in the given order in a B + -tree of order p=4 and p leaf =3, show;how the tree will expand and what the final tree looks like.;5 -;Develop cost functions for the PROJECT, UNION, INTERSECTION, SET DIFFERENCE;and CARTESIAN PRODUCT algorithms discussed in section 19.4.;6 -;Extend the sort-merge join algorithm to implement the LEFT OUTER JOIN operation.
Paper#36455 | Written in 18-Jul-2015Price : $24