\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{What are Relational Databases?}

\begin{slist}
\item Collections of records organized into \textit{tables}

\item Tables can be \textit{joined} or \textit{queried} to form new tables

\item \textit{Tables}  modified dynamically.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Why Relational Databases}

\begin{slist}
\item Abstraction between data representation and program

\item Existing tools for    optimization 

\item Extensible structure for dealing with data

\item Pre-existing administration, backup and recovery
\end{slist}

\end{slide}

\begin{slide}
\stitle{Structured Query Language}

\begin{slist}
\item Language for expressing queries and database setup

\item Supported by almost all relational database software

\item Standardized by ISO and ANSI

\end{slist}

\end{slide}

\begin{slide}
\stitle{What is SQL Like?}

\begin{slist}

\item Very verbose
\begin{salltt}
group by situation having  count(samples) > 5
    order by sum(samples) desc
\end{salltt}

\item Case insensitive

\item Comments start with double hyphen
\begin{salltt}
-- This is a comment.
\end{salltt}

\end{slist}
\end{slide}


\begin{slide}
\stitle{Some Examples are Oracle-Specific}

\begin{slist}

\item SQL is underspecified; things you need to do cannot be stated in the standard language.

\item Oracle provides solutions to these problems.

\item Oracle can scale to database tasks of almost any size.

\item Other databases provide similar solutions.

\end{slist}

\end{slide}

\begin{slide}
\stitle{A Few Definitions}

\begin{description}
\item[\textit{row}]  A single record of related \textit{columns} or \textit{fields}

\item[\textit{table}] A set of \textit{rows}

\item[\textit{schema}] A group of related tables; for example, the library example in  this class is a single schema

\end{description}
\end{slide}

\begin{slide}
\stitle{First, Consider Problem Domain}

\begin{slist}

\item Decide  on a general area.

\item Consider questions to answer/operations to implement.

\item What tables/objects are needed?
\end{slist}

\end{slide}

\begin{slide}
\stitle{The Library Example}

\begin{slist}

\item We consider the database required to maintain a  library.

\item Data based loosely on the needs of MITSFS---the MIT Science Fiction Society

\item Keep track of books, members, fines, etc

\end{slist}

\end{slide}

\begin{slide}

\stitle{What Might We Be Asked?}

\begin{slist}

\item What books do you have by  Piers Anthony?

\item Who has the copy of {\em Ender's Game\/} checked out and when should it be back?

\item What's the third book in the {\em Foundation\/} series?

\item Please check in this book.
\end{slist}

\end{slide}


\begin{slide}
\stitle{Books}

\begin{slist}

\item Unique IDs are nice.

\item Authors is complex because  of multiple authors per book.

\item We pretend title is simple.

\item We need to store series (single) and position in series.

\item Consider useful statistics.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Authors  as a Detail Table}

\begin{slist}

\item  One row in books corresponds to one or more rows in authors.

\item A row in authors belongs to exactly one book.

\item This relationship is called a master-detail relationship.

\item Linked based on unique ID

\end{slist}

\end{slide}

\begin{slide}
\stitle{Members and the Name Problem}
\begin{slist}

\item Holds information about members who can potentially borrow books
from library.

\item  Consider producing form letters.


\item Other biographic information could also be kept.

\item Some members are also librarians.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Copies Table}

\begin{slist}

\item Choice  for unique ID

\item Links to books

\item Stores information on who checked out/when due

\item Stores circulating status, date obtained, etc

	\end{slist}

\end{slide}

\begin{slide}
\stitle{Fines}

\begin{slist}
\item Stores who is fined and who is doing the fining

\item Stores date issued/paid

\item Stores reason and amount

\end{slist}

\end{slide}


\begin{slide}
\stitle{Tables and Datatypes}

\begin{slist}
\item A table is defined by its \emph{columns} (fields)

\item Each field is usually one of numeric(precision, scale), integer, 
	varchar(length), char(length), or date

\item Table creation syntax is finicky
\end{slist}

\end{slide}


\begin{slide}
\stitle{Create Table}

\begin{salltt}
create table books (
  bookid      integer,
  title       varchar(255), 
    -- should really be table like authors
  series      varchar(255), 
    -- should really be table like authors
  position    integer,  -- where in series
  checkouts   integer
);

create table authors (
  bookid      integer,
  author      varchar(255)
);
\end{salltt}

\end{slide}



\begin{slide}
\stitle{Constraints Enforce Data Consistency}

\begin{slist}
\item Constraints require that data rows or columns meet certain conditions

\item Prevent some forms of inconsistency

\item Can be unnamed 
\begin{salltt}
unique(social_security_num),
phone_number varchar(30) unique
\end{salltt}

\item Or named
\begin{salltt}
constraint one_ssn_per_cust unique(social_security_num)
\end{salltt}

\item Named constraints produce much better errors with Oracle and other  databases.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Constraints Not Always the Answer}

\begin{slist}
\item Constraints may violate abstraction.

\item Constraints may prevent rows from being deleted.

\item Not all business rules can be expressed as constraints.
\end{slist}
\end{slide}

\begin{slide}


\stitle{The Horror of Null}

\begin{slist}

\item All SQL datatypes can be \textit{null}.  

\item Null values make most expressions in which they are used null;
for example if \texttt{a} is null, \texttt{a + b} is null.

\item The expression \texttt{a is null} is true exactly when
\texttt{a} is null. There is also \texttt{a is not null}.

\item Nulls represent unspecified or not-available data.  For example, the member who is checking out a book copy is null if no one is checking out the copy.

\end{slist}

\end{slide}

\begin{slide}

\stitle{Not-Null Constraints}

\begin{slist}

\item If a column can be null, you must check whether the column is null when using it.

\item Some data must be specified for software to work correctly.  For example, books without titles are useless to a library.

\item In these cases, a column can be labeled \textit{not null}.
\begin{salltt}
title varchar(255) not null,
\end{salltt}

\item
You can have named not-null constraints, but unlike other  constraints, it doesn't really help.
\end{slist}

\end{slide}


\begin{slide}


\stitle{Not Null Example}


\begin{alltt}
create table books (
  bookid 	   integer,
  title 	   varchar(255) \underline{not null}, 
    --should really be table like authors
  series 	   varchar(255), 
    -- should really be table like authors
  position   integer,  -- where in series
  checkouts  integer \underline{not null}
);


create table authors (
  bookid	   integer \underline{not null},
  author	   varchar(255) \underline{not null}
);
\end{alltt}

\end{slide}


\begin{slide}
\stitle{Unique Constraints}

\begin{slist}
\item Having two members with the same ID would be bad; other similar situations arrise.

\item A unique constraint requires that a column or set of columns be
unique within a table.  If you have multiple columns in a unique
constraint, the composite of all values must be unique.

\item  Unique columns can be null.

\item Example:
\begin{salltt}
constraint  one_social_security_num unique(social_security_num)
\end{salltt}


\end{slist}

\end{slide}

\begin{slide}
\stitle{Unique Example}



\begin{alltt}
create table books (
  bookid 	   integer
   constraint books_primary unique(bookid),
  title 	   varchar(255) not null, 
    --should really be table like authors
  series 	   varchar(255), 
    -- should really be table like authors
  position   integer,  -- where in series
  checkouts  integer not null
);

create table authors (
  bookid	   integer,
  author	   varchar(255),
   \underline{constraint authors_primary unique(bookid, author)}
);
\end{alltt}
\end{slide}


\begin{slide}
\stitle{Primary Key}
\begin{slist}
\item Previous example was lacking, because  columns could have been null.

\item Column values must be unique and non-null

\item Example:
\begin{salltt}
constraint  books_primary primary key(bookid)
\end{salltt}

\item Alternatively:
\begin{salltt}
bookid integer
    constraint books_primary primary key,
\end{salltt}
\end{slist}

\end{slide}

\begin{slide}
\stitle{Primary Key Example}


\begin{alltt}
create table books (
  bookid 	   integer,
  \underline{constraint bookid_primary primary key(bookid)},
  title 	   varchar(255) not null, 
    --should really be table like authors
  series 	   varchar(255), 
    -- should really be table like authors
  position   integer,  -- where in series
  checkouts  integer not null
);

create table authors (
  bookid	   integer,
  author	   varchar(255),
  \underline{constraint authors_primary primary key (bookid, author)}
);
\end{alltt}

\end{slide}

\begin{slide}
\stitle{Foreign Keys}

\begin{slist}

\item In a master-detail relationship, it is important to make sure that the detail record refers to a real master record.
 A foreign key constraint enforces this by requiring that a
specific column or column group appears as in another table.

\item Foreign keys must map to unique or primary keys in the referencing table.

\item Example:
\begin{salltt}
constraint fines_librarian_exists foreign key(librarian)
    references members(memberid)
\end{salltt}

\item The foreign key can be null unless otherwise constrained.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Foreign Key Examples}
\begin{alltt}
create table books (
  bookid      integer
    constraint bookid_primary primary key,
  title       varchar(255) not null, 
    --should really be table like authors
  series      varchar(255), 
    -- should really be table like authors
  position    integer  -- where in series
);

create table authors (
  bookid      integer
  \underline{constraint authors_bookid_exists}
    \underline{foreign key(bookid) references books(bookid)},
  author      varchar(255),
  constraint authors_primary primary key (bookid, author)
);
\end{alltt}
\end{slide}

\begin{slide} \stitle{Check Constraints} \begin{slist} \item
Sometimes, constraints must be specified using general SQL
expressions.  \item Check constraints allow this.  \item Most
databases require the expression reference only the current row and
not reference run-time state like the system date.

\item Example:
\begin{alltt}
constraint circ_valid check(circ in (0, 1))
\end{alltt}

					 \end{slist} \end{slide}
\begin{slide} \stitle{Check Example} 
\begin{salltt}
create table books (
  bookid      integer
    constraint bookid_primary primary key,
  title       varchar(255) not null, 
    --should really be table like authors
  series      varchar(255), 
  position    integer,  -- where in series
  checkouts   integer not null  ,
  \underline{constraint position_meaningful}
    \underline{check(position is null or position >= 0)}
);
create table authors (
  bookid      integer
  constraint authors_bookid_exists
    foreign key(bookid) references books(bookid),
  author      varchar(255),
  constraint authors_primary primary key (bookid, author)
);
\end{salltt}

\end{slide}

\begin{slide} \stitle{Default Values} 
\begin{slist} 
\item A default clause can be added to columns
to specify a default value.  
\item Because of an Oracle bug, do not
include comments after default clauses; doing so can corrupt database
backups.  They are OK after the comma that closes the field or the
paren that closes the create table, but not before.  

\item Example:
\begin{salltt}
checkouts integer not null default 0
\end{salltt}
	\end{slist}

				     \end{slide}
\begin{slide}
\stitle{Default Example}
\begin{alltt}
create table books (
  bookid      integer
    constraint bookid_primary primary key,
  title       varchar(255) not null, 
  series      varchar(255), 
  position    integer,  -- where in series
  checkouts   integer not null  \underline{default 0},
  constraint position_meaningful 
    check(position is null or position >= 0)
);
create table authors (
  bookid      integer
  constraint authors_bookid_exists
    foreign key(bookid) references books(bookid),
  author      varchar(255),
  constraint authors_primary primary key (bookid, author)
);
\end{alltt}
\end{slide}

\begin{slide}
\stitle{Alter Table}
\begin{slist}
\item
You can add a column after a table is created:
\begin{salltt}
alter table copies add isbn integer;
\end{salltt}

\item Table constraints can also be added.

\item Implementations including Oracle allow column data types and null constraints to be modified.  There is not a standard SQL manner for doing this.

\end{slist}
\end{slide}

\begin{slide}
\stitle{Drop Table}
\begin{slist}
\item The \texttt{drop table} command can be used  to  delete a table and all contained data.

\item Tables may not be dropped if this would violate a foreign-key constraint.

\item Oracle provides the \texttt{ cascade constraints} operation to delete all constraints needed to allow the drop to happen.

\item Example:
\begin{salltt}
drop table books cascade constraints;
\end{salltt}
\end{slist}
\end{slide}



\end{document}

