Wednesday, December 9, 2020

Relational database management systems mcq quiz with answers 20

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.

 

************************
Related posts:


Quiz questions with answers on DBMS concepts

calculate blocking factor

cost for reading disk block pages using binary search

how many disk blocks required to store a database file of fixed size records with no spanning of records

Number of page reads required to access the entire table

Cost for sequential scan of entire database table

No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery