DATABASE SYSTEM
3. (a) Identify four main inputs used and three main outputs produced in the (7 Marks) (b) A Product file has 400,000 fixed-length records with record size of physical database design. 100 bytes. Suppose that the file is not ordered by the key field ProductNo, which is 11 bytes long. The block size of the disk used is 2048 bytes. A block pointer is 8 bytes long. If a single-level index is implemented on ProductNo, calculate the following (i) (ii) (iii) (iv) The number of data blocks required. The numbers of index entries that can be held in each index block. The number of index blocks required. The number of block accesses needed to search and retrieve a record from the file given the ProductNo. (8 Marks) (c) Consider the Product file described in part 3(b). If a dynamic multilevel index is implemented on ProductNo using a single-level index, calculate the following with the further assumption thata record pointer is 10 bytes long and each node is approximately 69% full B-tre e structure instead of the (i) (ii) (iii) (iv) (v) The order p of the B-tree The number of search values that can be held in each node The number of levels needed. The total number of blocks required by the B-tree. The minimum and maximum numbers of block accesses needed to search for and retrieve a record from the file given the ProductNo (10 Marks) 3. (a) Identify four main inputs used and three main outputs produced in the (7 Marks) (b) A Product file has 400,000 fixed-length records with record size of physical database design. 100 bytes. Suppose that the file is not ordered by the key field ProductNo, which is 11 bytes long. The block size of the disk used is 2048 bytes. A block pointer is 8 bytes long. If a single-level index is implemented on ProductNo, calculate the following (i) (ii) (iii) (iv) The number of data blocks required. The numbers of index entries that can be held in each index block. The number of index blocks required. The number of block accesses needed to search and retrieve a record from the file given the ProductNo. (8 Marks) (c) Consider the Product file described in part 3(b). If a dynamic multilevel index is implemented on ProductNo using a single-level index, calculate the following with the further assumption thata record pointer is 10 bytes long and each node is approximately 69% full B-tre e structure instead of the (i) (ii) (iii) (iv) (v) The order p of the B-tree The number of search values that can be held in each node The number of levels needed. The total number of blocks required by the B-tree. The minimum and maximum numbers of block accesses needed to search for and retrieve a record from the file given the ProductNo (10 Marks)
