Relational database management systems mcq quiz with answers 22

MCQ on various aspects in DBMS including cost for reading disk block pages, count the number of disk pages required to store a table, inner join, functional dependency, RDBMS Multiple Choice Questions with Answers

Database management systems - MCQ exam questions

1. Let us consider blocks of size 4KB to store variable-length records. Each block has a fixed 32 byte header used to store information including the number of records in the block. In addition to this fixed header, the header contains variable number of 2 byte pointers to each record in the block. Records can start at any byte offset and are packed as densely as possible. What would be the free space available in a single block after storing 10 records of size 400 bytes each?

a) 40 bytes

b) 44 bytes

c) 48 bytes

d) 10 bytes

Answer: (b) 44 bytes We can calculate the available initial free space for storing N records as follows; Free space = Block size (in bytes) – header size (in bytes) – 2 bytes * N                         = 4096 – 32 – (2 * N) bytes                         = 4096 – 32 – (2 * 10) bytes                         = 4044 bytes Memory required to store our records = 10 * 400 = 4000 bytes Free space after storing records = 4044 – 4000 = 44 bytes.

 

2. Which of the following is NOT a valid non-trivial functional dependency for the given instance of relation R(A, B, C)?

A	B	C
a2	b2	c1
a1	b1	c3
a1	b1	c2
a3	b3	c1

 

a) A → B

b) AC → B

c) AB → C

d) BC → A

Answer: (c) AB → C The combination of attributes A and B does not uniquely determine C. Functional dependency states that the left hand side attributes must uniquely determine the right hand side attributes. For a given LHS values, there should be only one RHS value. That is, if the LHS value is repeated in some records, then the same RHS value should appear in all those records. For a functional dependency, A → B, if t1[A] = t2[A] then t1[B] must be equal to t2[B]. If such a condition is violated for at least a record in the table, then we can say that the FD does not hold in that table. In simpler terms, if I write an SQL query with LHS attribute(s) in the WHERE condition, then I must get 0 or 1 record for any value of LHS attribute. In the given table, the FD AB → C is violated for the second and third tuples. For the combination (a1, b1) of attributes A and B, we have two different C values c3 and c2.

 

3. Suppose that a relation R has n tuples and S has m tuples. Assume that the attribute A is the primary key of R and the attribute D is the foreign key in S which refers R(A). If we execute an inner join between R and S on R.A = S.D, what will be the maximum number of tuples that can be output?

a) n

b) m

c) n + m

d) n * m

Answer: (b) m Maximum of m rows would be included in the result set. From the question, it is understood that the attribute A cannot have duplicates (primary key) and D can have duplicate values. But D cannot have any values which is not part of A. Hence, the number of records in S decides the count of result set. For example, R.A may have 10 values (unique) and S.D can have values from R.A of these 10 records only. Single value of R.A may be included many times in S.D but S.D cannot have values which is not part of R.A.

 

************************

Related posts:

• 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

Quiz questions with answers on DBMS concepts

calculate blocking factor

cost for reading disk block pages for variable length records

What is recoverable schedule?

What will be the maximum number of records that would result from an inner join operation?

What is non-trivial functional dependency