GEOG 583
Geospatial System Analysis and Design

Databases

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.

Field 1 Field 2 Field 3 Field 3
Record 1
Record 2
Record 3
An example database table, in which each column is a "field" and each row is a "Record".
Credit: Timoshenko page 20.

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

A relational database showing links between both tables pertaining to employees
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.

Your Turn:

For your database, create a relational table which will be used to add data to your geospatial design, including the key fields, and the arrows connecting the key fields, to visualize how the relational databases can be related/joined.

References:

Timoshenko, G. Introduction to Relational Database Management Systems. Database Management and Analytical Techniques for Agricultural Planning: A Course Manual, 19.