50 DBMS MCQs with Answers

Question 21

__________ rules used to limit the volume of log information that has to be handled and processed in the event of system failure involving the loss of volatile information.

• Write-ahead log

• Check-pointing

• Log buffer

• Thomas

Question 22

Which of the following RAID level provides the highest Data Transfer Rate (Read/Write)

• RAID 1
• RAID 3
• RAID 4
• RAID 5

Question 23

What is the equivalent serial schedule for the following transactions?

T1          T2          T3
                       R(Y)
                       R(Z)
R(X)
W(X) 
                       W(Y)
                       W(Z)
           W(Z)
R(Y)
W(Y) 
           R(Y)
           W(Y)
           R(X)
           W(X)                                

• T1 − T2 − T3
• T3 − T1 − T2
• T2 − T1 − T3
• T1 − T3 − T2

Question 24

Which of the following is the highest isolation level in transaction management?

• Serializable
• Repeated Read
• Committed Read
• Uncommitted Read

Question 25

Given the following statements:

    S1: A foreign key declaration can always 
        be replaced by an equivalent check
        assertion in SQL.
    S2: Given the table R(a,b,c) where a and
        b together form the primary key, the 
        following is a valid table definition.
        CREATE TABLE S (
            a INTEGER,
            d INTEGER,
            e INTEGER,
            PRIMARY KEY (d),
            FOREIGN KEY (a) references R) 

Which one of the following statements is CORRECT?

• S1 is TRUE and S2 is FALSE.
• Both S1 and S2 are TRUE.
• S1 is FALSE and S2 is TRUE.
• Both S1 and S2 are FALSE.

Question 26

A clustering index is defined on the fields which are of type

• non-key and ordering

• non-key and non-ordering

• key and ordering

• key and non-ordering

Question 27

Consider the following relational schema:

  employee(empId, empName, empDept)
  customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
       FROM employee E
       WHERE NOT EXISTS ( SELECT custId
                          FROM customer C
                          WHERE C.salesRepId = E.empId
                            AND C.rating <> `GOOD`);

• Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
• Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
• Names of all the employees with none of their customers having a ‘GOOD’ rating.
• Names of all the employees with all their customers having a ‘GOOD’ rating.

Question 28

The level of aggregation of information required for operational control is

• Detailed
• Aggregate
• Qualitative
• None of the above

Question 29

Consider the relation "enrolled(student, course)" in which (student, course) is the primary key, and the relation "paid(student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:

Query1: select student from enrolled where 
        student in (select student from paid)
Query2: select student from paid where 
        student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P 
         where E.student = P.student
Query4:  select student from paid where exists
        (select * from enrolled where enrolled.student
         = paid.student) 

Which one of the following statements is correct?

• All queries return identical row sets for any database
• Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
• There exist databases for which Query3 returns strictly fewer rows than Query2
• There exist databases for which Query4 will encounter an integrity violation at runtime.

Question 30

A data file consisting of 1,50,000 student-records is stored on a hard disk with block size of 4096 bytes. The data file is sorted on the primary key RollNo. The size of a record pointer for this disk is 7 bytes. Each student-record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file with records consisting of two fields, ANum value and the record pointer the corresponding student record, is built and stored on the same disk. Assume that the records of data file and index file are not split across disk blocks. The number of blocks in the index file is ________ .

• 698
• 898
• 899
• 4096