Unit 1 - 6 Practice Questions Solutions

[cheatsheet1999]

1. Which of the following are the advantages of using a DBMS?
   (All Correct)
   a. Data Independence
   b. Data Security
   c. Concurrency
   d. Data Administration

2. At which level of abstraction is the logical structure defined?
   a. Internal Schema
   b. External Schema
   c. [Correct] Conceptual Schema
   d. Physical Schema

3. Which of the following is true with regard to weak entities?
   a. Owner entity set and weak entity set can participate in a many-to-many relationship.
   b. [Correct] Owner entity set and weak entity set must participate in a one-to-many relationship.
   c. [Correct] Weak entity must have total participation in the identifying relationship set. (When the owner entity is deleted, all owned weak entities must also be deleted.)
   d. Weak entities can exist without the owner entity

4. SQL is a ______________ language.
   a. Representative
   b. [Correct] Declarative (A declarative language tells what data is to be retrieved but does not tell the system how to retrieve the data.)
   c. Functional
   d. Procedural

5. Which query can be used to list the student information for computer science majors [C]
   who are not double majors in electrical engineering [E]?
   a. [Correct] C - E (The difference operator, ‘-’ can be used to list the necessary information.)
   b. C U E
   c. E x C
   d. E - C

6. Which of the following is true when using Heap Files?
   a. [Correct] Heap Files are efficient for loading bulk data.
   b. Heap Files are efficient for sorting.
   c. [Correct] Heap Files are not efficient for selective queries.
   (1. Heap files are not efficient for sorting and may be time-consuming.
   Heap files are efficient while working on relatively small relations as indexing overheads
   are avoided.)
   d. Heap Files are not efficient while working on relatively small relations.

7. Which component loads pages from hard disk to memory?
   a. Load Manager
   b. [Correct] Buffer Manager (Buffer manager stages pages from external storage to the main memory buffer pool)
   c. Page Manager
   d. Memory Manager

8. Which of the following questions should be answered before creating an index?
   a. [Correct] What field(s) should be the search key?
   b. [Correct] Should you build several indexes?
   c. [Correct] Which relations should have indexes?
   d. What records(s) should be the search key? (False, The record(s) retrieved should not affect the decision of choosing an index)

9. Which clustered index is the best choice for the following condition:
   Age = 35 40,000 < Sal < 80,000
   a.
   b.
   c. [Correct] <Age,Sal>
   (<Age,Sal> is a better choice than <Sal,Age> as the conditions use equality selection on
   the age attribute and a range selection over the salary attribute.)
   d. <Sal,Age>

10. Which of the following indexes can be used to implement an index-only plan to evaluate the following query: [E.ssn is the primary key in this relation]

a. b. c. d. Even though is a more optimal choice, even can be used to implement an index-only plan in this case.

11. Which of the following is not a principle of database transaction:
    a. Atomicity
    b. Consistency
    c. [Correct] Integrity
    (Atomicity, Consistency, Isolation, and Durability are the four principles, which are also
    known as the ACID properties.)
    d. Durability

12. Consider the following transactions:
    T1: X = X + 1300 Y = X - 800
    T2: X = X * 0.85 Y = Y * 1.25
    i. Initial value of X is 2000 and Y is 5000.
    ii. T1 arrives first.
    iii. Serial Schedule
    d. Y = 3125.00
    End of T1: X = 3300; Y = 2500 End of T2: X = 2805; Y = 3125

13. Which component manages lock based concurrency control for transactions?
    a. [Correct] Lock manager (All lock and unlock requests are handled by the lock manager)
    b. Concurrency manager
    c. Buffer manager
    d. Control Manager
    e. Transaction manager

14. Which lock can be upgraded should a transaction need to write into the database?
    a. Read lock
    b. Concurrency lock
    c. [Correct] Shared lock
    (If a transaction already holds a shared lock, it can be upgraded to hold an exclusive lock)
    d. Exclusive lock
    e. Write lock

15. There are 4 transactions with 2 atomic instructions in each. How many possible serial
    schedules can be made?
    a. 6
    b. 8
    c. 16
    d. [Correct] 24 (4 transactions can be scheduled in 4! = 432*1 = 24 ways)