CS204 Exam Study Guidelines

The exam will cover only the topics taught in class. It will not include topics already covered in
the midterm exam. The format of the questions will be similar to that of homework exercises. It
would be a good idea to use lecture slides as the primary resources and reinforce the understanding
using the textbook.

Please let me provide a list of topics to review.
 Database programming
− Concepts of cursor, stored procedure, and trigger -- key ideas, e.g., what they are, what
they are useful for.
− Reading a PL/SQL program code (i.e., explaining what a given program code does); there
will be no program writing.
 Physical database design
− File structures: configurations, fixed vs. variable length records, record blocking, blocking
factor, basic operations on file records
− File organizations: heap file, sequential file, algorithms for basic file operations on them
− Index structures: B+ tree index (concepts, key insertion algorithm, key deletion algorithm),
hash index (concept, overflow handling mechanism)
− Index design: determining indexed columns and index structures.
 Query processing and optimization
− Definition of selectivity and estimation of selectivity.
− Algorithms for conjunctive and disjunctive selection operations.
− Algorithms for join operations – two-way inner join only.
− Costs of join processing for different algorithms – at the tuple level (running time) and at
the disk I/O level (cost formula).
− Algorithms for projection operations and their comparisons.
− Heuristic query optimization: query tree transformation, the equivalent transformation of
relational algebra expression.
− Cost-based query optimization: cost parameters, cost functions, query execution plan
generation and selection – concepts only; no need to actually generate the cost function of
a query execution plan.
 Transaction management
− Transaction concepts: ACID properties
− Potential problems with no concurrency control: lost update, dirty read, non-repeatable
read.
− Schedule of concurrent transactions: recoverable schedule, serializable schedule, test of
serializability.
− Concurrency control protocols: two phase locking (basic, conservative, strict), deadlock
situations.
− Recover from failure: concepts of system log and write ahead logging.
− Log-based recovery techniques: immediate updates and deferred updates with commit
points and check points.
If some of these topics cannot be covered due to time limit, then those topics will not be in the
exam.