A Geography Quiz:
Understanding Databases

by Kirby Urner
First posted: March 11, 2005
Last modified: October 14, 2005

Our way of life depends on the existence of large repositories of information known as databases. When you buy an airline or theater ticket, chances are that this transaction is processed through one or more databases. The database will show who has paid for what, what seats have been reserved, and so on. Many sales representatives may be processing transactions though the same databases at the same time. Databases run on computers, large and small, all around the world. Some are accessible through the Internet, while others are only accessible to people inside a company, government agency, or private home.

A database may begin life as an electronic text file or script, the purpose of which is to define its tables and maybe populate them with data. Below is an example of such a script. The database it defines consists of only one table: states.

A table consists of rows and columns. In this case, each row is a state or territory associated with the USA. Each column contains a certain type of information: state name, abbreviation, two-letter postal code, and state capital. The raw information the script uses to fill the table, once it's defined, is called newstates.txt. Notice the use of quotes and commas -- a very typical format.

Fuller Projection

Data sets from databases are often displayed on maps. This Fuller Projection is showing average annual temperature data

drop database if exists usa;
create database usa;

use usa;

CREATE TABLE 'states' (
'statename' VARCHAR( 20 ) NOT NULL ,
'abbrev' VARCHAR( 10 ) NOT NULL ,
'postal' CHAR( 2 ) NOT NULL ,
'capital' VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( 'postal' )

LOAD DATA LOCAL INFILE 'newstates.txt'
INTO TABLE 'states' 

How might we use this database? Suppose you would like to quiz yourself regarding capital cities. You would like to have a web page which asks you about a state or territory and asks you to pick from a randomly ordered list of possibilities. After you make a selection, you click on a button, and the computer tells you if you got it right, plus lets you go again. This is a very simple use of a database, but it gives us some idea of how they're used.

Since the database we're using is MySQL, we need a programming language that will talk to MySQL, meaning it's able to select and/or update its databases. In this case, we only need to read from the states table. There's no need to update it.

The programming language we'll use is Python. Python lets us write scripts that generate web pages for the user or browser, and that know how to consult MySQL. The combination of these two abilities will make our quiz idea a reality.

How is a web page created? A web page comes to your browser through the Internet or through some local intranet. The format it comes in is called HTML or XHTML (these formats are slightly different) and consists mostly of text marked up with a lot of words in pointy brackets known as tags. HTML stands for "Hypertext Markup Language" and the "http://" you see at the start of most URLs stands for "hypertext protocol." The idea behind hypertext is that any word may become a link to some other page. This is a chief characteristic of the web: its high degree of interconnectivity.

Python book cover

Our Python script will have the job of providing these tags, as they instruct the browser what to display. The tags also set up an HTML form with a drop-down select box and clickable button.

When you buy an airline or theater ticket, or transfer money from one bank account to another, you may well find yourself doing this by means of HTML forms. Some script probably put it on your screen, and when you hit a button, a script will process your transaction, probably by talking to databases. The script might be written in Python or some other computer language.

As you use this simple quiz program to help you learn the capitals, think about how you're using a database, and how your browser is interpreting tags sent to you from Python scripts. Your teacher may want to give you more details about how the Python scripts get the job done. You may get some idea by simply reading them. As a classroom activity, these scripts may be available for editing. Perhaps your teacher will show you how to get MySQL running on your own computer, or maybe it's already running on the classroom server.


  • newstates.txt was saved in UTF-8 format from Notepad in Windows XP. The data was originally harvested from web pages and used to populate a spreadsheet
  • different operating systems have different ways of terminating a line of text. On Windows, the line ending is typically \r\n (carriage return, newline), whereas on Unix or GNU (Linux), the line ending is just \n.
  • Python provides a simple server for testing cgi scripts in the form of the CGIHTTPServer module. This lets you do some testing without booting a full-fledged server (such as Apache) on a development computer

For further reading:


oregon.gif - 8.3 K
Oregon Curriculum Network