CSC 195 2014S, Class 12: Relational Databases
=============================================

_Overview_

* Preliminaries.
    * Admin.
    * Questions.
* Basic concepts: Databases, Database management system, Relational database.
* A sample relational database.
* Core database operations.
* Database design.
* An exercise in database design.

Preliminaries
-------------

### Admin

* If you have not registered for 213, make sure to do so by the 4 pm
  today.
* This week, we look broadly at relational databases.  Next week, we'll 
  move on to SQL.
* Next homework: Finish today's database design.  Try to express in SQL.
* It may be fun to skim your classmates' homework, but not in class.

### Questions

Basic concepts: Databases, Database management system, Relational database
--------------------------------------------------------------------------

What is a database?

* "A base of data"?
* A collection of values
* What distinguishes this from lists, arrays, etc?  
    * Perhaps organization
    * Databases often store compound data (multiple fields per entry)
    * Designed to support fast and complex searching - Some separation
      of interface from implementation
    * We often think of lists, arrays, etc. as being in memory, while
      we assume databases are external to our program and need to be
      accessed remotely.
    * Databases are persistent
    * Databases often have multiple collections
* Early computing days - Careful arrangement of data on disk or memory
  to achieve goals
    * Database management systems - Programs that make it easier to
      make databases and achieve the goals
* Relational databases - Collections of tables ; table is a relation
    * Develop/design by E.F. Codd
* There is a growing movement to explore and use other kinds of databases
  (e.g., think about the world as a collection of objects)
* SQL is the standard language for working with relational databases
    * Slightly different design for each, particularly in the additional
      programming part
    * Not imperative, not object-oriented, not functional; it's declarative
    * Declare the character of the data you want, the DBMS figures out
      the algorithm for getting it.

A sample relational database
----------------------------

Suppose we want to represent information on courses at Grinnell College

* Table for courses
    * Unique identifier: 21512 
    * Department (abbreviation): "CSC"
    * Course number: 195
    * Section: "01"
    * Instructor: 32

* Table for instructors
    * ID: 32
    * LName; Rebelsky
    * FName: Samuel
    * Email: rebelsky@grinnell.edu
    * Instructor Office Number: x4410
    * ...

* Table for students
    * ID: 45232
    * LName: Smith
    * FName: Smith
    * Email: SmithSmith@grinnell.microsoft.com
    * BoxNumber: 
    * ...

How do we keep track of what courses a student is in?

* Option 1: Have a list of courses in each student record
* Option 2: Have a list of students in each course record
* Option 3: Have a table of course/student pairs
* Option 4: Have fields for each course in a student record
    * Semester 1, year 1, course 1: 
    * Semester 1, year 1, course 2: 
    * Semester 1, year 1, course 3: 
    * Semester 1, year 1, course 4: 
    * Semester 1, year 1, course 5: 
    * Semester 1, year 1, course 6: 
    * Semester 2, year 1, course 1: 
    * Semester 2, year 1, course 2: 
    * Semester 2, year 1, course 3: 
    * Semester 2, year 1, course 4: 

Option 3 is preferred 
* Course id: 21512
* Student id: 43452

SELECT (Student.LName,Student.FName) from (Courses,Students,Option3) where (Courses.DEPT="CSC" and Courses.Number=195, and ...)

Key idea: Tables that join information together

Core database operations
------------------------

* Extract information
    * Pick one or more rows of a table based on some criteria
    * Extract information from those rows
    * Combine duplicate information
    * Join Combine tables into an implicit table
    * Given a table, extract one column
    * Rearrange results based on some criterion (sort)
    * Pretty print using style sheet
* Add/change information in the database
    * Delete one or more rows/entry (and maybe associated information)
      REMOVE where Student.Attitude = Snarky
      REMOVE where Student.ThinksThat = "PHP means Ponies Help People" 
    * Change one or more rows/entry (e.g., time changes for a course)
      UPDATE (Student.Grade = Pass) where CourseId=21512
    * Add a new row/entry
    * Add a new table
    * Add a new column from a table - Painful; good early design helps
    * Remove a column from a table
    * Remove a table
    * Give hints to the DBMS - E.g., "I'm going to query this database 
      by *this field* a lot."
* Other administrative tasks
    * Create a new database
    * Remove an existing database
    * Grant and remove privileges - E.g., User 3123 can read this database,
      but not modify it, Zoe can read and modify the database, Ajuna
      and Toby can read, modify, and grant privs to other users.
    * Add and remove users
    * Export / import
    * Backup!
    * Combine two databases

Database design
---------------

Database design 

* What tables you have
* What types the fields in each table have
* Relationships between tables (which are themselves tables)
* What things you identify as keys (likely to be used for queries)
* ...

What makes a good database?

* Performance for expected queries
    * Note: We should have use cases when we design a database.
* Don't repeat data
* Naming that will make sense to the user/client

An exercise in database design
------------------------------

* What tables would you want for CDThing?
* Homework