COSC 460 Databases Midterm 1 Study Guide

The exam is closed book – no notes of any kind.

Topics

• Relational model
• DBMS architecture: data storage
• SQL
• Algebra
• Indexing
• B+Trees

Finer-grain view of topics

• Relational model
  • Definitions, terminology
  • Physical data independence
  • Logical data independence
  • Integrity constraints (e.g., keys)
• DBMS architecture
  • Basic components and interactions between them: disk manager, buffer manager, “file of records” (e.g., heap file)
  • Memory hierarchy
  • Basic disk characteristics: seek, rotation, transfer
  • Heapfile organization
  • Page formats (fixed- vs. variable-length)
  • Record formats (fixed- vs. variable-length)
  • Buffer manager (pool, dirty bit, pin count)
  • Buffer manager vs. OS virtual memory
  • Replacement (aka eviction) policies: LRU, Clock, MRU, Random; sequential flooding
• Relational algebra
  • Basic operators
  • Equivalent expressions
  • How some operators can be derived from others
  • Division “operator” and how to derive it
• SQL
  • Basic select from where, renaming, order by
  • Set operations: UNION, EXCEPT, INTERSECT
• B+Trees and Indexing
  • Insertions, deletions
  • Cost of various operations (insert, delete, search)
  • Comparison with other techniques (heap file, sorted file)
  • Clustered vs. unclustered
  • Data entry alternatives