MCQ on various aspects in DBMS including database disk access and querying, what is blocking factor, cost for reading disk block pages, count the number of disk pages required to store a table, sequential index scan, RDBMS Multiple Choice Questions with Answers
Database management systems - MCQ exam questions
Let us suppose that we have a retail database with two tables, FIRM(FID, FName, Addr, Contact, Manager) and CUSTOMER(CID, CName, Phone). The records of both the tables are fixed length. The length of each record of FIRM and CUSTOMER are 84 bytes and 30 bytes respectively. FIRM has 30000 records and CUSTOMER has 100000 records. The disk page size is 512 bytes. Use this data to answer the following questions:
1. What are the blocking factors (bfr) for relations FIRM and CUSTOMER?
a) 6, 17
b) 84, 30
c) 58, 195
d) 17, 6
|
Answer: (a) 6, 17 Blocking factor bfr = floor(block size/record size) bfr(FIRM) = floor(512/84) = floor(6.1) = 6 bfr(CUSTOMER) = floor(512/30) = floor(17.06) = 17 |
2. Let us suppose that FIRM is sorted on FID attribute and there is no index. What is the cost (in terms of page reads) for finding the FIRM with FID “1” using binary search?
a) 30000
b) 5000
c) 13
d) 59
|
Answer: (c) 13 To find the required number of pages reads, we need to first find the number of pages occupied by the relation FIRM. Number of disk pages for FIRM, N = Number of records/bfr = 30000/6 = 5000 pages.
Cost for reading disk pages using binary search = Ceiling(log2N) = Ceiling(log25000) = Ceiling(12.29) = 13 |
3. Let us suppose that FIRM is sorted on FID attribute and there is no index. What is the cost (in terms of page reads) for finding the FIRM with FName “ABC Agencies”?
a) 30000
b) 5000
c) 512
d) 13
|
Answer: (b) 5000 Table FIRM is not sorted on FName attribute. Hence, to find the firm “ABC agencies”, we need to perform SEQUENTIAL SCAN (ie., scan each record one by one from the first record). Number of page reads = number of disk pages occupied by FIRM = 5000 |
4. Let us suppose that the relation CUSTOMER is sorted on CID attribute and there is no index. Also, the disk block size is 1024 bytes. What is the cost (in terms of page reads) for finding the CUSTOMER with customer ID “C101” using binary search?
a) 13
b) 12
c) 512
d) 59
|
Answer: (b) 12 Blocking factor bfr(CUSTOMER) = floor(1024/30) = floor(34.13) = 34. Number of disk pages for CUSTOMER, N = Number of records/bfr = 100000/34 = 2942 pages.
Cost for reading disk pages using binary search = Ceiling(log2N) = Ceiling(log22942) = Ceiling(11.52) = 12 |
5. Let us suppose that all the records of FIRM relation are stored sequentially based on the FID attribute. Also, the FID values are 1, 2, 3, …, 30000. Let us suppose that we create an index IDX1 on FID. If each index block can store 50 entries, how many blocks must the system read to retrieve the contents of the record with id = 2527?
a) 50
b) 51
c) 52
d) 100
|
Answer: (c) 52 Number of blocks = Ceiling(Record position/50) + 1 = Ceiling(2527/50) + 1 = Ceiling(50.54) + 1 = 52 That is, 51 index block reads + 1 record block read. |
- Go to Multiple Choice Questions in DBMS home
- Go to Normalization - MCQs page
- Go to General Mixed Quiz in all topics of DBMS page
Go to Advanced DBMS Concepts page
No comments:
Post a Comment