|CPSC 343||Database Theory and Practice||Fall 2017|
The final exam will be an open book take-home exam. This means that you can use the textbook on reserve in the library (Elmasri and Navathe), your own notes made prior to the exam being handed out, the materials posted directly on the course website, and the official MySQL documentation. Other books, other people's notes, and other websites (even external websites linked to the course pages) are not allowed, nor is communicating with anyone else about the exam (other than asking me clarification questions).
The exam will be handed out on the last day of class. To allow for studying and additional preparation during reading period, the exam will be handed out in an envelope. Once you have opened the envelope, you have begun the exam and the rules above about allowed resources and talking to other people apply. They will continue to apply until you have turned in the exam. (Though you should continue to not talk about the exam in places where those still working on it may overhear you until the exam deadline has passed.)
The exam will be cumulative; expect roughly a third of the exam on each of the following:
data modeling and the relational model, including the ER and EER models and diagrams, ER- and EER-to-relational mapping, design principles, and normalization
SQL, including data definition, queries (simple queries, products and joins, set operations, subqueries, grouping, aggregation), data modification (INSERT, LOAD DATA INFILE, DELETE, UPDATE), views, stored routines, triggers, transactions, and related concepts (e.g. three-valued logic, the handling of NULL values, and the handling of duplicates)
file organization, indexes, and query processing (including heuristic and cost-based query optimization)
Expect questions similar to the homework assignments and practice problems. For the new topics, this includes creating a query tree for an SQL query, applying heuristic optimization to a query tree, and computations such as the number of blocks read when searching a particular type of index and the cost of a execution plan (along with the smaller computations that are part of and related to that). There may also be short answer questions about related concepts (such as describing how to find all records matching a certain condition using a particular type of index or explaining why we focus on the number of blocks as a measure of running time) or questions asking you to make and/or defend choices (such as to identify and justify which index would be most effective for speeding up the execution time of a particular query).