Held: Thursday, 24 April 2014

Back to Outline 11 - PHP (2). On to Outline 13 - SQL.

Summary

We explore some basic issues regarding databases, particularly relational databases.

Related Pages

• EBoard 12 (Source 12) (HTML 12) (PDF 12)

Overview

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

Administrivia

• 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.

What is a database?

• Something that stores information in a systematic way
  • Does an array qualify? It stores information in a systematic way?
  • What does "stores" mean? You can access it again
    • Persistant
    • And accessible
  • What does "systematic" mean?
    • Tables. (Not the only view. - also graphs and OO databases)
    • Some algorithmic way to access data
• About thirty years ago, writing a database meant
  • Designing the layout of data on the disk
  • Providing functions for accessing and modifying those data
  • Etc.

What is a database management system? (DBMS)

• Set of programs that help you build databases
• You as programmer build your databases at a higher level
  • You don't have to worry (much) about layout, the system does it for you
  • You don't have to worry (much) about the functions you provide, as every database provides the same functions.

What is a relational database?

• One in which we represent data in tables
• What is a table?
  • Property which are columns
  • Entries which are rows
  • Each row should be identifiable in some way (typically be the "primary key")
• Each category of thing gets its own table
• We can use the primary keys to link data between tables.
• Often the properties are typed (integer, string, whatever).

A sample relational database

• Grinnell classes.
• Need a table for professors.
• Need a table for students.
• Need a table for courses.
• Need tables that link these together.

Core operations

• Read information from the tables (in at least three ways)
  • "Join" two tables: E.g., if we have a table of people, and each person has a home town, then we can use the places table to get lattitude and longitude for each person
  • Select entries from a table (or a join of tables)
  • Project: select columns
• Write to the tables
  • Change an existing value (change last name of 000-00-0000 from Wolfcat to Wolfdog)
  • Add additional entries to the table (add a person with SSN 111-11-1111 to table, first name = "Wolf" last-name = "Cat" mi = "NMI", home town = "Mars" etc.)
• Otherwise modify tables
  • Remove rows from tables
• Create tables (and databases)

Database design

• How do we design a good database?
• In some ways, the analysis of good seems subjective.
• But there are some objective answers:
  • In normal form: No duplication of data between tables.
  • Efficient: Answers the primary queries quickly.
  • Usable: Permits us to answer the questions we want to answer (if possible, with a concise query).
• The question (how do we design a good database) can also be interpreted as a process question: What steps do we use?
  • Study your problem domain!
  • Write use cases / Map out likely queries in advance.
  • Map out possible tables.
  • Give each table a primary key.
  • Map out relationships.
    • One-to-one
    • One-to-many
    • Many-to-many
  • Prototype! (Yay XP practices.)

A database design exercise

• Let's think about the tables in a database of CD's.
• More specifically, suppose that we're designing CDThing.org, similar to LibraryThing.org, except for CD collections.
• The main questions
  • What data are we likely to store?
  • What operations are we likely to want?
• We'll start with the CD data and then come back to the social networking data.