CS 357: Database Systems Implementation
Solution to Homework Assignment 9


1.   SimpleDB implements autocommit mode, in which the system automatically decides when to commit a transaction.  Standard JDBC allows the client to turn off autocommit mode, and to commit and rollback its transactions explicitly.  The JDBC Connection interface has a method setAutoCommit(boolean ac), which allows a client to turn auto-commit mode on or off, a method getAutoCommit, which returns the current auto-commit status, and the methods commit and rollback.  Implement these methods. 
 
The methods setAutoCommit, getAutoCommit, commit, and rollback need to be added to RemoteConnection and SimpleConnection, in the usual way.
 
The class RemoteConnectionImpl should keep a boolean variable that indicates whether autocommit is on or off.  It should also make the methods commit and rollback be pulic remote methods.  The method executeUpdate in class RemoteStatementImpl should be modified so the that it checks the connection’s autocommit status before deciding to commit.  Similarly, the method close in RemoteResultSet should also check the connection’s autocommit status.
 
Here are the revised files for RemoteConnectionImpl, RemoteStatementImpl, and RemoteResultSetImpl.

2.  Explain which indexes could be useful for evaluating each of the following queries.

a)
select SName
from STUDENT, DEPT
where MajorId=DId and DName='math' and GradYear<>2001
 
An index on either MajorId or DId would make the join more efficient.  An index on DName would make the selection more efficient.  Note that an index on GradYear would not be useful here.
 
b)
select Prof
from ENROLL, SECTION, COURSE
where SectId=SectionId and CourseId=CId
and Grade='F' and Title='calculus'
 
An index on either SectId or SectionId would make the first join more efficient, and similarly an index on either CourseId or CId would make the second join more efficient.  An index on Title would make the selection more efficient.  An index on Grade is probably not useful, as mentioned in class.

3. Consider a query that joins two tables, such as
 
select SName, DName
from STUDENT, DEPT
where MajorId = DId
 
Suppose STUDENT contains an index on MajorId, and DEPT contains an index on DId.  There are two ways to implement this query using an index join, one way for each index.  Using the cost information from Figure 16-7, compare the cost of these two plans.  What general rule can you conclude from your calculation?
 
The plan using the index on MajorId loops through DEPT.  For each DEPT record, it uses the index on MajorId to find matching STUDENT records.  The cost is thus:
 
  B(DEPT)  + R(DEPT)*( indexcost + #Matching STUDENT records)
 
which, using the statistics from Figure 16-7, gives:

2 + 40 * (4 + 1125) = 45,162 block accesses

The plan using the index on DId loops through STUDENT.  For each STUDENT record, it uses the index on DId to find the matching DEPT record.  The cost is thus:
 
  B(STUDENT)  + R(STUDENT) * (indexcost + #Matching DEPT records)
 
which, using the statistics from Figure 16-7, gives:

 4,500 +  45,000 * (2 + 1) = 139,500 block accesses

In each case, the number of matching records is the same, which means that the cheaper plan will be the one that scans through the smaller table.  In other words, do the index join using the index on the larger table.