\documentclass[landscape]{slides}
\usepackage{alltt}

\setlength{\topmargin}{-1.25in}
\addtolength{\textheight}{1.25in}

\def\slist{\list{-}{
  {\setlength{\parsep}{0ex plus0.2ex}
   \setlength{\itemsep}{0.2ex plus0.2ex minus0.2ex}
  }
}}
\let\endslist\endlist

\def\salltt{\vspace{-\baselineskip}\alltt}
\let\endsalltt\endalltt




\def\stitle#1{
\begin{center}
\large #1
\end{center}
}

\begin{document}
\pagestyle{plain}
\special{papersize=11in,8.5in}

\begin{slide}
\stitle{Select Into Example}

\begin{slist}
\item
\begin{salltt}
  EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
    WHERE users_id = :user;
\end{salltt}


\item  The \texttt{into} clause  stores the data from a select returning one row into  variables in the host programming language.

\item When host variables are used in embedded SQL, they are prefixed with colons.

\item Care must be taken with values that could be null.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Declare Cursor Example}

\begin{slist}
\item To retrieve multiple rows in embedded SQL, you need to declare a cursor:
\begin{salltt}
  EXEC SQL DECLARE csr103 CURSOR FOR
    SELECT list_id, ref_count   FROM imembers
    WHERE member_id = :lid AND member_type = 'LIST';
\end{salltt}

\item The scoping of the cursor name is poorly defined.  In general, only use it within the current function.


\end{slist}

\end{slide}

\begin{slide}
\stitle{Open and Fetch}

\begin{slist}
\item
\begin{salltt}
  EXEC SQL OPEN csr103; /* Check for errors would go here.*/ 
  while (1) \{ EXEC SQL FETCH csr103 INTO :id, :ref; \}
\end{salltt} 

\item \texttt{fetch} returns an error on the last row.  

\item Some errors may be returned at open, others at first fetch.  

\item Again, beware of nulls.  
\end{slist} 
\end{slide} 
\begin{slide}
\stitle{Perl and DBI}



\begin{salltt}
my $cursor_a = $dbh->prepare(``select mode,ino,name from ?'');
$cursor_a->execute('/usr');
 my @row = $cursor_a->fetchrow();
\end{salltt}

\end{slide}


\begin{slide} 
\stitle{Simplistic, Incorrect Procedure for Checking Out Book} 
\begin{slist} 
\item Confirm copy is not currently checked out.  
\item Remember the number of checkouts for the book.
\item Update the copies table indicating that the book is \texttt{out\_to} 
the appropriate member.  
\item Update the checkouts in the books table to be the number remembered
plus one.
\end{slist} 
\end{slide}

\begin{slide} 
\stitle{Problems with Simplistic Procedure} 
\begin{slist}
\item A book may be checked out between the time that the procedure
confirms it isn't checked out and the copies table is updated.  We
could argue this isn't a problem since  a copy  should only exist at one checkout station.

\item If two copies of the book are checked out at the same time, the checkouts will be updated incorrectly.

\item Both these problems could be fixed by atomic use of update:
\begin{salltt}
update books set checkouts = checkouts + 1
    where \textit{constraint}
\end{salltt}

\end{slist}

\end{slide}


\begin{slide}
\stitle{Why does Atomic Update Work?}

\begin{slist}
\item  The final result of checkouts is independent of order.

\item The update of copies is idempotent by using \texttt{where out\_to
is null}; if executed multiple times, only the first update will take effect.

\item The checkout program can check to see if the update took effect
or the copy was checked out by someone else by looking at the
\texttt{out\_to} field by performing a select.

\item Not all concurrency problems can be solved by simple atomic operations.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Incorrect Procedure for Fining Member}

\begin{slist}
\item Select available  fine credit into $C$ for member being fined.  Let $f$ be amount of fine.

\item  Insert fine of $f-c$ into fines table if $f-c > 0$.

\item Set fine credit to the maximum of 0 and $c-f$.
\end{slist}
\end{slide}


\begin{slide}
\stitle{Problem with Incorrect Fining Procedure}
Assume the member starts with \$2.00 fine credit; Librarian A wants
to fine them \$3.00 while at the same time Librarian B wants to fine
them \$1.00:

\begin{tabular}{ll}
Librarian A & Librarian B \\ \hline
set $C=2$ & \\
          & set $C=2$ \\
set $f=3$ & \\
          & set $f=1$ \\
insert fine of $f-c = 1$ & \\
	& $f-c < 0$ so insert no fine \\
$c-f < 0$ so set credit to 0 & \\
	& $c-f = 1$ so set credit to 1 \\
\end{tabular}

Final state is credit \$1, outstanding fine of \$1; not only should these
cancel, but the member is only down \$2 total, not \$4.
\end{slide}

\begin{slide}
\stitle{Transactions}

\begin{slist}
\item Transactions  allow multiple operations to be treated atomically.

\item In general, no change made in the transaction  is visible to any other session until that transaction \textit{commits}.

\item If a transaction \textit{rolls back}, then no change it  made is ever externally visible\footnote{You can read uncommitted data if you try, but we ignore this.}.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Transaction  Isolation Leveles}

\begin{slist}

\item OFten, you want to  guarantee that there is effectively a  serial order  of transaction execution.  That is, there is an ordering of transactions so that from external appearances,  one transaction was  completed before  the next started.

\item Other isolation levels exist: read committed, repeatable read and read uncommitted.

\item  The  fine  procedure is correct  if executed inside  a serial transaction.
\end{slist}
\end{slide}


\begin{slide}
\stitle{Using Transactions}

\begin{slist}

\item Normally, all SQL statements should be in a transaction.  Some
implementations require you to give a statement to start a
transaction.

\item The \texttt{commit work} statement commits the transaction and saves all data.

\item The \texttt{rollback} statement removes any changes made by the current transactions.



\item The \texttt{set transaction isolation level serializable} guarantees serial execution order.
\end{slist}

\end{slide}


\begin{slide}
\stitle{How are Transactions Implemented}

\begin{slist}

\item As rows are updated, they are locked.

\item Oracle and many other databases tend to avoid read locks.

\item Deadlocks are possible;  often deadlock detection or timeouts are implemented.

\item Serialization requires either read locks  or writers to get random serial failures.

\end{slist}

\end{slide}

\begin{slide}
\stitle{When Additional Locking is Required}

\begin{slist}

\item If the entire transaction is not serialized or if your database does not have sufficient granularity for serialization

\item If the module desiring locking is called from some larger
application, it may not be able to require serialized transactions.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Handling Concurrency Without Full Serialization}

\begin{slist}

\item If new information doesn't introduce integrity problems, then  less isolation may work.

\item Oracle and other databases provide primitives  to lock a table.

\item Other locking facilities inside or outside the database.

\end{slist}

\end{slide}


\begin{slide}
\stitle{Indexes}

\begin{slist}
\item Indexes allow  a database to  perform queries faster.

\item 
\begin{salltt}
create index \textit{index\_name} on \textit{table}(\textit{col1}, \textit{col2}, ...);
\end{salltt}

\item Queries that select a few rows based on the values of indexed
columns can be  executed more quickly with the index.

\item If used,  indexes can slow down queries that touch a large number of rows.

\item Oracle  automatically creates unique indexes for unique and primary keys.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Joins and Indexes}

\begin{slist}

\item A nested-loops join uses indexes to provide  quick joins between tables.

\item Merge join will sort two tables and merge the results.

\item Hash join probes a hash of one table with another.

\item Nested-loops much faster for small joins , but much slower for larger joins.


\end{slist}

\end{slide}


\begin{slide}
\stitle{Exercise: Library Inventory}

\small

For the annual Inventory, the library would like to produce a Shelfdex:
a list of all the book copies that should currently be in the library,
sorted by where they are on the shelves.

First, circulating copies should be separated from non-circulating.
Copies that are currently checked out should be ignored.

Within each of these categories, books should be alphabetized by author.
If the book has multiple authors, we want only the first one (alphabetically).

Next, the books should be sorted alphabetically by series; if the
book is not in a series, then its title should be used here.

Next, the books should be sorted by series position; position-less books
may be placed either before or after the others.

Next, those books with equivalent series and position should be sorted
by title.

Output the resulting ordered list of bookid, circ, author,
series, position, title, and of course number of identical copies.

\end{slide}




\end{document}
