Databases

You will be developing a prototype of the database for your design, which includes at a minimum, several of the tables your design will use including the fields and records/rows of the data the design is visualizing. Much of the information below may be familiar from GEOG 484.

A database is a collection of related tables stored and organized in a system that allows for additional analyses, querying, and filtering, etc. Each table contains fields, which are the columns of the database, and contain information about a subject. Each field consists of multiple records (e.g. specific data points).

In a relational database, tables are organized by subjects. In order to relate tables in a relational database, the tables need to have common fields between the tables, in order to “match” the tables. The similar fields are called key fields.

In the example below, notice how the key fields contain uniquely identifying information regarding employee number, which can be used to relate the two tables (Timoshenko).

Figure 2: A relational database, which contains two tables with "key" fields that can be used to connect the tables. Screen Capture by Brandi Gaertner

Click for a text description.

The image consists of two tables related by Employee Number data:

Table 1: Employee Information

This table lists employee details:

Last Name	First Name	Employee Number	Address	Job Title	Wage
Evans	Mark	04-234	21 Elm St.	Researcher	$10.00
van Druemel	Terry	07-456	45 Jalan Merdeka	Supervisor	$12.00
Nagase	Yoshi	01-637	87 Jalan Sempur	Secretary	$7.00
Cooper	Charlotte	04-734	29 Spagnum St.	Researcher	$10.00
Evans	Michelle	03-346	21 Elm St.	Accountant	$9.00

Table 2: Work Hours Information

This table lists details of employee hours:

Record Number	Employee Number	Week	Hours
1	04-234	02/02/98	35
2	07-456	02/02/98	35
3	01-637	02/02/98	42
4	04-734	02/02/98	28
5	03-346	02/02/98	35
6	04-234	09/02/98	35
7	07-456	09/02/98	35
8	01-637	09/02/98	42
9	04-734	09/02/98	28
10	03-346	09/02/98	35

Key and Relationship Between Tables

The data in the two tables are related by Employee Number:

In Table 1: The Employee Number column serves as a unique identifier for each employee.

In Table 2: The Employee Number column links to the Employee Number column in Table 1, and establishes which employee's hours are recorded in each row for this table.

An arrow in the image visually represents this link between the two tables.

Credit: Timoshenko page 23.

References: