DATABASE SYSTEM

3. Briefly describe three basic methods for implementing field's data integrity control in a database application. (a) (6 Marks) (b) Consider a disk with block size of 1024 bytes. An ARTICLE file has 500,000 fixed-length records, with record size of 200 bytes each to store information of articles published in scientific journals. A block pointer is 8 bytes long. Suppose that the file is not ordered by the key field ArticleID, which is 16 bytes long. If a multilevel index is implemented on ArticlelD, calculate the following (i) (ii) (iii) The number of search values that can be held in each index block. The number of levels and total number of index blocks required. The number of block accesses needed to search and retrieve a record from the file given its ArticleID (9 Marks) (c) In part (b), instead of implementing a multilevel index on ArticleID, when a dynamic multilevel index is implemented on ArticlelD using a B+-tree structure, calculate the following with the further assumption that a record pointer is 10 bytes long and each node is approximately 69% full (i) The numbers of search values that can be held in each leaf node and (ii) The number of levels and total number of blocks required for the (iii) The number of block accesses needed to search and retrieve a record (10 Marks) each internal node in the B+-tree, respectively B+-tree from the file given its ArticleID 3. Briefly describe three basic methods for implementing field's data integrity control in a database application. (a) (6 Marks) (b) Consider a disk with block size of 1024 bytes. An ARTICLE file has 500,000 fixed-length records, with record size of 200 bytes each to store information of articles published in scientific journals. A block pointer is 8 bytes long. Suppose that the file is not ordered by the key field ArticleID, which is 16 bytes long. If a multilevel index is implemented on ArticlelD, calculate the following (i) (ii) (iii) The number of search values that can be held in each index block. The number of levels and total number of index blocks required. The number of block accesses needed to search and retrieve a record from the file given its ArticleID (9 Marks) (c) In part (b), instead of implementing a multilevel index on ArticleID, when a dynamic multilevel index is implemented on ArticlelD using a B+-tree structure, calculate the following with the further assumption that a record pointer is 10 bytes long and each node is approximately 69% full (i) The numbers of search values that can be held in each leaf node and (ii) The number of levels and total number of blocks required for the (iii) The number of block accesses needed to search and retrieve a record (10 Marks) each internal node in the B+-tree, respectively B+-tree from the file given its ArticleID