Notice: In response to student request, I have copied the author's text solutions for Chapters 7, 11, 12, 13, 14 onto the T: drive (in the CSMIS43 directory). They are in a folder called HWSolutions. Be warned that there are likely some errors in these files.

Notice: Here and here are copies of Exam 3 from 2002. As always, be aware that the coverage of material is somewhat different this year.

Query Program:

• Be familiar with the problem. I expect to describe one of the queries, give some "reminder" background or definitions, then ask you to write a Java method to answer that query.
• I will not ask Java-related questions concerning the code that I made available to you -- opening files, writing the constructors or access methods for the classes, and the like. So concentrate on translating from SQL into Java.

Chapter 7:  SQL, emphasizing nested queries, aggregate functions, modifying database content with SQL and SQL as DDL.

• Look for two "kinds" of questions. One involves SQL statements to modify content of a database (Section 7.2). The other concerns creation and modification of the database schema (Section 7.3).

• Section 11.1 concerns hardware and software for disks;  there can be exam questions, including the understanding of the delays associated with disk accesses. In particular, pay attention to the class discussion that involved seek, rotational latency, & transmission delays.
• For purposes of this exam, do not be concerned with Java or the details of Java I/O classes. So, restrict your attention to Section 11.1 in this chapter.

• Here is a link to my lecture notes for Chapter 12.
• It is crucial to know the reasons for having indexes.  It is equally important to understand the costs associated with indexes.
• While you will not be asked to write any Java code, you should understand the SimpleIndex class.
• Multilevel indexes -- and most especially,B+-trees are central to using database management systems.
• You will be given a B+-tree and asked to demonstrate that you understand the insertion and deletion algorithms as described in class.
• The kinds of calculations found near the top of page 303 should be understood.
• Indexed sequential files.
• Representing Indexes with Hash Tables. We went over most of this quickly since students have seen hashing in CS 29.  There could well be some questions regarding aspects that are related to databases.
• Skip Section 12.5 in preparing for this exam.

• The big example in 13.1 showed that the presence, kind, and structure of indexes (Table 13.1) have a large impact on the costs of various plans for executing queries.  Problems 8 - 10 in the non-assignment (page 352) gave us a chance to see how that works.
• We learned  four techniques to execute a simple join -- block nested loops, indexed nested loops, sort-merge,and hash.
• We saw that a relational algebra expression can be represented as a tree, and that there are equivalence rules for relational algebra expressions.
• We looked at a complicated SQL statement, and three tree/plans to implement it.
• For this exam, you might want to skip over Section 13.5

• We were introduced to the concept and the ACID properties.
• We saw the actions of open/begin, read, writ, commit/rollback.
• We saw two approaches to rollback -- Oracle's RBS and a caching strategy.
• We learned about the importance of allowing concurrency, and of the difficulties it poses. In particular we learned about schedules , the "lost update" and "dirty read" problems, as illustrated in Figure 14.6.
• Students are invited to omit the other problems illustrated in 14.2.1.
• We should know about locking in its various forms, as discussed in 14.2.2
• We should know about deadlocks and the need to deal with them.
• For purposes of this exam, students are welcome to omit 14.3