Note: I cover essentially pp 432–450. I do not discuss XML, and
will not ask about it on any exam.
- Organized collections of data.
- Relational databases
- Data is organized in tables.
- This is the usual type.
- Other types exist, but they are specialized or experimental.
- Structure of the table is recorded with the table.
- Even an empty table has structure.
- Structural info an example of metadata.
- A table is also known as a relation.
- Each row contains an entity.
- Each entity has attributes.
- Each attribute has a value.
- Each row contains the set of attribute values for one entity.
- A row is also known as a tuple.
- The table is a set of tuples.
- Rows are also sometime called records. (Had enough names
for the same thing yet?)
- Given any particular structure, a particular set of rows is an
instance of the table.
- What a table definition contains.
- Table name.
- List of field specifiers, each with:
- A name.
- A format (type and size of data).
- Maybe a comment.
- A primary key (one of the fields).
- A primary key identifies a record.
- No two records should ever have the same primary key value.
- Standard example: Social security number.
- Rows need to differ.
- No two rows may have the same value.
- Inserting a duplicate row will not update the table.
- Standard fix is to use an arbitrary id number, as in the
- Fields should be atomic: Make the parts separate fields.
- Names: First, last, middle are separate fields.
- Separate state and zip from address.
- Operations on tables.
- Select: Choose rows based on a test.
- Project: Choose columns by name.
- Union: Combine unique rows from all tables.
- Difference: Remove rows present in one table from another.
- Product: Append every pair of rows.
- Combine rows on a matching field value.
- Subset of product.
- Tables have relationships, usually based on common fields:
Same student number, customer number, SSN, etc.
- One entry may refer to another by a sequence number.
- Tables are often joined on such common fields.
- These relationships should be given descriptive names. Lives_At,
Owes_Us, Owns, etc.
- Tables: Property, Owners, Renters, Addresses.
- Owners own properties.
- Renters rent properties.
- An owner livesAt and address.
- A Property is locatedAt and address.
- Structured Query Language (SQL)
- 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;
- SQL is rarely used on a desktop.
- SQL is common in a server environment, for instance, to provide the
data for a web site: