\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}