SQL Starter Notes for Project 2

I write this document so you can read about the steps typical for setting up a database via the Structured Query Language (SQL). Our Project 2 (filedist) 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 27th. As you learn SQL, you will be able to try out your SQL Commands via our class SQL Select Tool. From there, you can type in SQL SELECT statements against our class project database.

After using SSH2 (secure shell 2) software to connect to www.oworld.org (our class server) with my account, I typed createdb filedist at the ssh2 prompt to create a database named filedist. 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 filedist at the ssh2 session command prompt. The ssh console, which is just a simple character based interaction program, responded: 

bash-2.05b$ psql filedist
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

filedist=#
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. 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 2 Web site might want to process dynamically (note that no one used it last year which was fine): 

CREATE TABLE Customer (
Name text,
Country text,
Password text,
Balance real,
Accesscount int,
Lastaccess date,
Member int,
Initials text,
CustomerID serial);

INSERT INTO Customer VALUES ('Suyna', 'USA', 'it7s9dA', 10, 3, '05/10/2004', 1, 'bdc');

CREATE TABLE Download (
CustomerFK int,
FileFK int,
Date date,
Time int,
IPaddress text,
Groupnum int,
Initials text,
DownloadID serial);

INSERT INTO Download VALUES(1, 1, '05/12/2004', 340, '203.102.112.94', 1, 'bdc');

CREATE TABLE File (
Name text,
Size int,
Rating int,
Category int,
Format int,
Path text,
Createdate date,
Initials text,
FileID serial);

INSERT INTO File VALUES ('Atomic', 324423, 4, 1, 2, 'oldies', '05/12/2004', 'bdc');

CREATE TABLE Supplier (
Name text,
Password text,
Member int, 
Initials text,
SupplierID serial);

INSERT INTO Supplier VALUES ('Dramata', 'k#kdD3f', 0, 'bdc');

CREATE TABLE Upload (
SupplierFK int,
FileFK int,
Date date,
GroupName text,
Value real,
Initials text,
UploadID serial);

INSERT INTO Upload VALUES (1, 1, '04/04/2005', 'dna12', 20, 'bdc');

CREATE TABLE Moderator (
Name text,
Initials text,
ModeratorID serial);

INSERT INTO Moderator VALUES ('Pierce', 'bdc');

CREATE TABLE Review (
FileFK int,
ModeratorFK int,
Date date,
Origname text,
Initials text,
ReviewID serial);

INSERT INTO Review VALUES (1, 1, '04/23/2005', 'Cincinnati', '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);