4375 Final Exam

This course was to survey DBA concerns as represented by the material in chapters 7 through 11.  Database design issues (Normalization, ER-modeling) were seemingly covered in the first course where the focus was otherwise on the syntax of the SELECT statement and making embedded calls to the database. 

 

Chapter 7: DBA capabilities for enforcing integrity and consistency of the database, DBA access control administration, and DBA catalogs.

Chapter 8:  DBA capabilities to improve access speed by controlling table locations and creating indices and clusters.

Chapter 9:  DBA capabilities to tune the database by advising the Query Optimizer and examining its output.  How to read the Plan Table, how to use a Benchmark to better configure a system to its workload.

Chapter 10:  DBA configuration of transaction processing systems, observing ACID properties of transactions, the checkpointing and recovery process, stable storage, and the TPC-C benchmark.

Chapter 11:  Multi-processor database architecture and the extension of ACID properties to distributed shared-nothing systems (two phase commit).   Complexities of distributed query optimization and the scalability problem for commercial vendors. 

 

  1. Be prepared to describe the problems of administering a large (distributed) database application. 
  2. Be prepared to discuss the solutions that DBMS have implemented for the common problems of database applications: response time, concurrent transactions, fault-tolerant operation.
  3. Be able to trace the DBMS modules that process a transaction and discuss what each module does.
  4. Define and explain
    1. A transactional History and its interpretation
    2. ACID properties
    3. Conflicting operations
    4. Transaction precedence and serialization
    5. Two-phase locking
    6. Roll back and Roll Forward
    7. Levels of isolation
    8. Checkpoint varieties
    9. Concurrent update anomalies: lost update, inconsistent state, phantom update, nonrepeatable read, etc.
    10. Deadlock, Blocking
    11. Two-phase commit
    12. Multi-processor Database Architecture alternatives.
  5. Explain the concept of a benchmark and discuss the role that benchmarking plays in the DBMS industry.
  6. Know the theorems of serializability.
  7. Be prepared to answer any question from Exam #2.