------------------------------------------------------------------------------
MC logo
Ch. 16: Databases
[^] Chapter Outlines
------------------------------------------------------------------------------
<<Ch. 15: Advanced Spreadsheet Ch. 23: Limits>>
  1. Databases.
    1. Organized collections of data.
    2. Usual type: Relational databases
  2. Organized as a table.
    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.
        Whales
        nametext 15 The common English name
        scientific_nametext 30 Genus and species
        foodtext 12 Primary food source
        weightnumber Typical max. adult weight, kg
        length_rangetext 20 Lower-upper adult length range, meters
        imageGIF Picture of mature adult
        Primary Key: name
    4. 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.
<<Ch. 15: Advanced Spreadsheet Ch. 23: Limits>>