Clay Database

Connection Information:

Schema:

	Table1: ClayUser
	- username    varchar(30)  Primary Key
	- firstname   varchar(20)
	- lastname    varchar(20)
	- password    varchar(20)
	- affiliation varchar(100)
	- isLoggedIn  boolean
	- chatFgColor varchar(7)
	- chatBgColor varchar(7)

	Table2: Projects
	- name        varchar(100)
	- id          autoincrement integer Primary key
	- summary     varchar(500)
	- status      varchar(20)
	- startdate   varchar(20)
	- enddate     varchar(20)

	Table3: UserProjects
	- username  varchar(30) foreign key
	- id        integer foreign key
	- isAdmin   boolean

SQL Scripts:

CREATE TABLE ClayUser(username varchar(30) not null,firstname varchar(20),lastname varchar(20),password varchar(20),affiliation varchar(100),isLoggedIn char(1),chatFgColor varchar(7),chatBgColor varchar(7), primary key(username));
CREATE TABLE Projects(name varchar(100),id mediumint not null,summary varchar(255),status varchar(20),startdate varchar(20),enddate varchar(20),primary key(id));
CREATE TABLE UserProjects(username varchar(30),id,isAdmin char(1),primary key(id,username));

CREATE TABLE ClayUser(username varchar(30) primary key,firstname varchar(20),lastname varchar(20),password varchar(20),affiliation varchar(100),isLoggedIn boolean,chatFgColor varchar(7),chatBgColor varchar(7));
CREATE TABLE Projects(name varchar(100),id serial primary key,summary varchar(255),status varchar(20),startdate varchar(20),enddate varchar(20));
CREATE TABLE UserProjects(username varchar(30),id,isAdmin boolean, FOREIGN KEY(username) REFERENCES ClayUser, FOREIGN KEY(id) REFERENCES Projects, PRIMARY KEY(username,id));

INSERT INTO ClayUser ( username, firstname, lastname, password, affiliation, isLoggedIn, chatFgColor, chatBgColor )
VALUES( 'mikeylo','michael','locasto','mikeylo','mnm industries', false,'#00CCDD','#FFFFFF');

INSERT INTO ClayUser ( username, firstname, lastname, password, affiliation, isLoggedIn, chatFgColor, chatBgColor )
VALUES( 'justin','justin','tracy','justin','mediahaven.net', false, '#00CCDD','#FFFFFF');

INSERT INTO ClayUser ( username, firstname, lastname, password, affiliation, isLoggedIn, chatFgColor, chatBgColor )
VALUES( 'holmes','michael','hulme','holmes','mnm industries', false, '#00CCDD','#FFFFFF');

INSERT INTO Projects ( name, summary, status, startdate, enddate )
VALUES( 'clay','clay rmi synchronous collaborative architecture','open','10.1.2001','5.17.2002' );

INSERT INTO Projects ( name, summary, status, startdate, enddate )
VALUES( 'qastiir','question answer system with information retrieval and semantic processing','open','09.1.2001','9.1.2002' );

INSERT INTO UserProjects ( username, id, isAdmin )
VALUES( 'mikeylo',1,true);

INSERT INTO UserProjects ( username, id, isAdmin )
VALUES( 'holmes',1,true);

INSERT INTO UserProjects ( username, id, isAdmin )
VALUES( 'holmes',2,false);

INSERT INTO UserProjects ( username, id, isAdmin )
VALUES( 'justin',1,false);