Study Guide for EEN523, Spring 1999, final examination.

• SQL
• Creating tables,
• Sensible data representations:
• sizes of numeric fields, floating vs. fixed,
• Sizes of strings, fixed vs. variable size,
• Sizes and resolution of date and time formats (year 2000 & 2038 problems)
• Inserting data,
• Select commands,
• Creating and using views,
• Cursors and their importance for embedded SQL.
• Physical and Logical File Formats
• Structure of disc, free-list, etc.
• Continuous run block allocation
• Single-level, two-level, and multi-level indexes, and I-nodes
• Calculating maximum supported file-sizes
• Calculating best, average, and worst-case data access times for sequential and random access
• Creating Indexes
• How long it takes to create an index,
• How long it takes to search a table using an index,
• How long it takes to update an index after insertion,
• For the most important indexes:
  • ISAM (i.e. sorted, or linear index)
  • Calculating expected time to sort a disc file
  • Details specific to: Selection sort, Bubble sort, Quick sort, Merge sort, etc.
  • B-Trees
  • Hash Tables
• Optimising Queries
• Estimating time for a query with amd without indexes
• Taking into account time needed to create indexes
• Time for canonical query tree (naively nested loops) vs. optimised query trees
• Relational Algebra
• The basic operations
• what they mean, how they are used
• composition rules (e.g. )
• how composition rules apply to query tree opimisation
• Distributed Database Operations
• Cost of query depends on amount of data transferred
• calculating amount of data transferred
• assigning work to different processors to minimise it
• Semi-join
• what it is, how it works
• using it to reduce amount of data transferred
• Entity-Relationship Model and the Philosophy of Relational Databases
• Components: Entity, Relationship, Attribute
• ER Diagrams: understanding and creating them
• Weak entities and identifying relations
• Dual nature of relations: Table vs. Calculated condition
• Transactions
  • What they are
  • atomic appearance
  • guaranteed permanent once committed
  • all or nothing: will either be completely done or completely undone
  • preserving consistency of whole database
  • Life-cycle of a transaction
  • What to do when a transaction fails in order to recover
  • Rollback: what it is, why it is done, how it is done
  • The system log, what it is, what it contains, how it is used
  • Concurrency control:
    • what can go wrong if two transactions simultaneously work on the same records
    • How to prevent problems without being too restrictive
    • locking records
  • Deadlock: what it is, how it is caused, what to do about it