I provide this document so you can read about the typical database set up steps using the
Structured Query Language (SQL) and can investigate the database schema we agreed upon together.
Remember that our schema comes out of a modeling exercise document called an Entity-Relationship Diagram:
Our Project 3 (environ) database is created via a simple createdb call made to the Postgres postmaster.
The createdb command sets up a standard database shell and allocates starter space in the database storage area
on the server hard drive. I plan on updating this document to represent any changes to the database that we agree
on upon your review of the choices we made in class on April 26th. As you learn SQL, you will be able to try out
your SQL SELECT Commands via our class SQL Select Tool.
Then, you will be able to INSERT new records to the environ database using our class SQL Insert Tool.
After using the UW-recommended SSH2 (secure shell 2) software to connect to www.oworld.org (our class server) with my account,
I typed createdb environ at the ssh2 prompt to create a database named environ. Once the database
existed, I could use the Postgres psql interface to run SQL commands against our database. To get the psql
interface pointed towards our database, I typed psql environ at the ssh2 session command prompt. The ssh console,
which is just a simple character-based interaction program, responded:
bash-2.05b$ psql environ
Welcome to psql, the PostgreSQL 8.0 interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
environ=#
I then ran the following CREATE TABLE and INSERT INTO commands at the psql prompt (psql stands for Postgres Structured
Query Language which is an extension of the SQL specification you are studying for class). Note how all the commands
handle the appropriate type information (integer, text, date, etc.) for each data attribute. The Generic table is added
for you to use if you want to store additional data related to anything your Project 3 Web site might want to process
dynamically (note that no one used it last year which was fine):
CREATE TABLE Customer (
Name text,
Address text,
City text,
State text,
Accesscount int,
Lastaccess date,
Initials text,
CustomerID serial);
INSERT INTO Customer VALUES ('Suyna', '13 Main Street', 'Puyallup', 'WA', 3, '05/10/2004', 'bdc');
CREATE TABLE Collaboration (
CustomerFK int,
ScientistFK int,
Date date,
Initials text,
CollaborationID serial);
INSERT INTO Collaboration VALUES(1, 1, '05/12/2004', 'bdc');
CREATE TABLE Scientist (
Name text,
Specialty int,
Initials text,
ScientistID serial);
INSERT INTO Scientist VALUES ('Roger Brown', 4, 'bdc');
CREATE TABLE Observation (
ScientistFK int,
ReportFK int,
Initials text,
ObservationID serial);
INSERT INTO Observation VALUES (1, 1, 'bdc');
CREATE TABLE Report (
Date date,
Time text,
Reporter text,
DataItem int,
Value real,
Initials text,
ReportID serial);
INSERT INTO Report VALUES ('04/04/2005', '01:01:34', 'Suyna', 2, 12.35, 'bdc');
CREATE TABLE Run (
ScientistFK int,
ModelFK int,
Date date,
Comments text,
Initials text,
RunID serial);
INSERT INTO Run VALUES (2, 1, '05/04/2006', 'No exceptions', 'bdc');
CREATE TABLE Model (
Name text,
Nextrun date,
Initials text,
ModelID serial);
INSERT INTO Model VALUES ('POM', '05/04/2006', 'bdc');
CREATE TABLE Timestep (
RunFK int,
Time text,
TimestepID serial);
INSERT INTO Timestep VALUES (1, '00:00:00');
CREATE TABLE Forecast (
DataItemFK int,
TimestepFK int,
LocationFK int,
Value real,
ForecastID serial);
INSERT INTO Forecast VALUES (1, 1, 1, 143.94);
CREATE TABLE DataItem (
Name text,
Units text,
Initials text,
DataItemID serial);
INSERT INTO DataItem VALUES ('PO4', 'mmo', 'bdc');
CREATE TABLE Location (
UTM_northing real,
UTM_easting real,
UTM_tile int,
Latitude real,
Longitude real,
layer int,
Initials text,
LocationID serial);
INSERT INTO Location VALUES ('5123243', '378382', 9, 48.34, -126.37, 0, 'bdc');
CREATE TABLE Generic (
First text,
Second text,
Third text,
Fourth text,
Fifth int,
Sixth int,
Seventh int,
Eighth real,
Ninth real,
Tenth date,
Initials text,
Gen_ID serial);
|
