DROP CONTRAINT UOASxxx.PK_ADDRSSID;
DROP INDEX UOASxxx.ADDRSSID_NDX;
DROP TABLE UOASxxx.address;


CREATE TABLE UOASxxx.address 
	(addressid int NOT NULL,
	firstname varchar(25),
	lastname varchar(30),
	address1 varchar(35),
	address2 varchar(35),
	city varchar(25),
	prov char(2),
	postal varchar(12),
	phone varchar(14),
	email varchar(35)
	)
IN UOASxxx.TSSxxx;


CREATE UNIQUE INDEX UOASxxx.ADDRSSID_NDX ON UOASxxx.ADDRESS(ADDRESSID);

ALTER TABLE UOASxxx.ADDRESS ADD CONSTRAINT PF_ADDRSSID PRIMARY KEY(ADDRESSID);


INSERT INTO UOASxxx.address
	(addressid, firstname, lastname, address1, address2,  city, prov,
postal, phone, email)
VALUES
	(1, 'Peter', 'MacIntyre', 'PO Box 2076', 'Station A', 'Stratford',
'PE', 'C1A 7J6', '902-555-1234', 'peter_maci@anymail.com') ;
	

INSERT INTO UOASxxx.address
	(addressid, firstname, lastname, address1, address2,  city, prov,
postal, phone, email)
VALUES
	(2, 'Dawn', 'Riley', '31 Marjorie Cres.', '', 'Stratford', 'PE',
	'C1B 1X4', '902-555-1236', 'testmail@anymail.com') ;

INSERT INTO UOASxxx.address
	(addressid, firstname, lastname, address1, address2,  city, prov,
postal, phone, email)
VALUES
	(3, 'Fred', 'Flintstone', 'PO Box 2076', 'Station F', 'Bedrock',
'PE',
	'C1A 7J6', '902-555-4234', 'fflint@anymail.com') ;

INSERT INTO UOASxxx.address
	(addressid, firstname, lastname, address1, address2,  city, prov,
postal, phone, email)
VALUES
	(4, 'Frodo', 'Baggins', 'PO Box 2276', 'Station A', 'The Shire',
'ME',
	'C1A 7J6', '902-556-1134', 'thering@anymail.com') ;

INSERT INTO UOASxxx.address
	(addressid, firstname, lastname, address1, address2,  city, prov,
postal, phone, email)
VALUES
	(5, 'Simon', 'MacIntyre', 'PO Box 2076', 'Station A', 'Stratford',
'PE',
	'C1A 7J6', '902-555-1114', 'simonsoccker@anymail.com') ;

DROP CONSTRAINT UOASxxx.PK_USERID;
DROP INDEX UOASxxx.USERID_NDX;
DROP TABLE UOASxxx.USERS;

CREATE TABLE UOASxxx.users (
userid int not null,
firstname varchar(25),
lastname varchar(30),
username varchar(15),
password varchar(10),
accesslevel int not null,
phone varchar(14),
email varchar(35) )
IN UOASxxx.TSSxxx;

CREATE UNIQUE INDEX UOASxxx.USERID_NDX ON UOASxxx.USERS(USERID);

ALTER TABLE UOASxxx.USERS ADD CONSTRAINT PK_USERID PRIMARY KEY(USERID);

INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email)
Values (1,  'administrator', 'administrator', 'admin', 'entry', 1, '555-1234', 'admin@admin.com') ;

INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email)
Values (2, 'Peter', 'MacIntyre', 'pbmacintyre', 'happy', 1, '555-1234', 'peter_maci@hotmail.com') ;

INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email)
Values (3, 'Fred', 'Flintstone', 'fredflint', 'pebbles', 2, '111-1234', 'fred@bedrock.com') ;


DROP CONSTRATINT UOASxxx.PK_PROVID
DROP INDEX UOASxxx.PROVID_NDX;
DROP TABLE UOASxxx.prov;


CREATE TABLE UOASxxx.prov (
provid int not null,
provcode char(2),
provname varchar(35) )
IN UOASxxx.TSSxxx;

CREATE UNIQUE INDEX UOASxxx.PROVID_NDX ON UOASxxx.prov(provid);

ALTER TABLE UOASxxx.prov ADD CONSTRAINT PK_PROVID PRIMARY KEY(provid);

INSERT INTO UOASxxx.prov (provid, provcode, provname) values(
1, 'PE', 'Prince Edward Island');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
2, 'NF', 'Newfoundland');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
3, 'NS', 'Nova Scotia');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
4, 'NB', 'New Brunswick');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
5, 'PQ', 'Quebec');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
6, 'ON', 'Ontario');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
7, 'MB', 'Manitoba');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
8, 'AB', 'Alberta');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
9, 'SK', 'Saskatechwan');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
10,'BC', 'British Columbia');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
11, 'ME', 'Maine');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
12, 'NH', 'New Hampshire');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
13, 'VT', 'Vermont');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
14, 'NY', 'New York');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
15, 'CO', 'Colorado');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
16, 'NV', 'Nevada');
INSERT INTO UOASxxx.prov(provid, provcode, provname) values(
17, 'NM', 'New Mexico');