Note: I cover essentially pp 432–450. I do not discuss XML, and will not ask about it on any exam.

Video lecture: stream download

1. Databases.
   1. Organized collections of data.
   2. Relational databases
      1. Data is organized in tables.
      2. This is the usual type.
      3. Other types exist, but they are specialized or experimental.
2. Tables.
   
   1. Structure of the table is recorded with the table.
      1. Even an empty table has structure.
      2. Structural info an example of metadata.
      3. A table is also known as a relation.
   2. Terminology.
      1. Each row contains an entity.
      2. Each entity has attributes.
      3. Each attribute has a value.
      4. Each row contains the set of attribute values for one entity.
      5. A row is also known as a tuple.
      6. The table is a set of tuples.
      7. Rows are also sometime called records. (Had enough names for the same thing yet?)
      8. Given any particular structure, a particular set of rows is an instance of the table.
   3. What a table definition contains.
      1. Table name.
      2. List of field specifiers, each with:
         1. A name.
         2. A format (type and size of data).
         3. Maybe a comment.
      3. A primary key (one of the fields).
         1. A primary key identifies a record.
         2. No two records should ever have the same primary key value.
         3. Standard example: Social security number.

         PacificIslands
         	Field Name	Data Type
         	ID	AutoNumber
         	IslandName	Short Text
         	Area	Number
         	Elev	Number
         	Archipelago	Sort Text
         	Primary Key: ID

   4. Rows need to differ.
      1. No two rows may have the same value.
      2. Inserting a duplicate row will not update the table.
      3. Standard fix is to use an arbitrary id number, as in the islands example.
   5. Fields should be atomic: Make the parts separate fields.
      1. Names: First, last, middle are separate fields.
      2. Separate state and zip from address.
3. Operations on tables.
   1. Select: Choose rows based on a test.
   2. Project: Choose columns by name.
   3. Union: Combine unique rows from all tables.
   4. Difference: Remove rows present in one table from another.
   5. Product: Append every pair of rows.
   6. Join
      1. Combine rows on a matching field value.
      2. Subset of product.
   7. Examples.
4. Relationships.
   1. Tables have relationships, usually based on common fields: Same student number, customer number, SSN, etc.
   2. One entry may refer to another by a sequence number.
   3. Tables are often joined on such common fields.
   4. These relationships should be given descriptive names. Lives_At, Owes_Us, Owns, etc.
   5. Example:
      1. Tables: Property, Owners, Renters, Addresses.
      2. Relationships.
         1. Owners own properties.
         2. Renters rent properties.
         3. An owner livesAt and address.
         4. A Property is locatedAt and address.
5. Structured Query Language (SQL)
   1. A text language used to expression database operations.
      select name, capital from states where population > 10000000;
      select coasters.coaster_name, coasters.park_name, states.name from coasters, states where coasters.state = states.abbrev and coasters.length > 2000;
   2. SQL is rarely used on a desktop.
   3. SQL is common in a server environment, for instance, to provide the data for a web site: