CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE SUPPLIER(SUPPID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(80),STATUS VARCHAR(2) NOT NULL,ADDR1 VARCHAR(80),ADDR2 VARCHAR(80),CITY VARCHAR(80),STATE VARCHAR(80),ZIP VARCHAR(5),PHONE VARCHAR(80))
CREATE MEMORY TABLE SIGNON(USERNAME VARCHAR(25) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(25) NOT NULL)
CREATE MEMORY TABLE ACCOUNT(USERID VARCHAR(80) NOT NULL PRIMARY KEY,EMAIL VARCHAR(80) NOT NULL,FIRSTNAME VARCHAR(80) NOT NULL,LASTNAME VARCHAR(80) NOT NULL,STATUS VARCHAR(2),ADDR1 VARCHAR(80) NOT NULL,ADDR2 VARCHAR(40),CITY VARCHAR(80) NOT NULL,STATE VARCHAR(80) NOT NULL,ZIP VARCHAR(20) NOT NULL,COUNTRY VARCHAR(20) NOT NULL,PHONE VARCHAR(80) NOT NULL)
CREATE MEMORY TABLE PROFILE(USERID VARCHAR(80) NOT NULL PRIMARY KEY,LANGPREF VARCHAR(80) NOT NULL,FAVCATEGORY VARCHAR(30),MYLISTOPT INTEGER,BANNEROPT INTEGER)
CREATE MEMORY TABLE BANNERDATA(FAVCATEGORY VARCHAR(80) NOT NULL PRIMARY KEY,BANNERNAME VARCHAR(255))
CREATE MEMORY TABLE ORDERS(ORDERID INTEGER NOT NULL PRIMARY KEY,USERID VARCHAR(80) NOT NULL,ORDERDATE DATE NOT NULL,SHIPADDR1 VARCHAR(80) NOT NULL,SHIPADDR2 VARCHAR(80),SHIPCITY VARCHAR(80) NOT NULL,SHIPSTATE VARCHAR(80) NOT NULL,SHIPZIP VARCHAR(20) NOT NULL,SHIPCOUNTRY VARCHAR(20) NOT NULL,BILLADDR1 VARCHAR(80) NOT NULL,BILLADDR2 VARCHAR(80),BILLCITY VARCHAR(80) NOT NULL,BILLSTATE VARCHAR(80) NOT NULL,BILLZIP VARCHAR(20) NOT NULL,BILLCOUNTRY VARCHAR(20) NOT NULL,COURIER VARCHAR(80) NOT NULL,TOTALPRICE DECIMAL(10,2) NOT NULL,BILLTOFIRSTNAME VARCHAR(80) NOT NULL,BILLTOLASTNAME VARCHAR(80) NOT NULL,SHIPTOFIRSTNAME VARCHAR(80) NOT NULL,SHIPTOLASTNAME VARCHAR(80) NOT NULL,CREDITCARD VARCHAR(80) NOT NULL,EXPRDATE VARCHAR(7) NOT NULL,CARDTYPE VARCHAR(80) NOT NULL,LOCALE VARCHAR(80) NOT NULL)
CREATE MEMORY TABLE ORDERSTATUS(ORDERID INTEGER NOT NULL,LINENUM INTEGER NOT NULL,TIMESTAMP DATE NOT NULL,STATUS VARCHAR(2) NOT NULL,CONSTRAINT PK_ORDERSTATUS PRIMARY KEY(ORDERID,LINENUM))
CREATE MEMORY TABLE LINEITEM(ORDERID INTEGER NOT NULL,LINENUM INTEGER NOT NULL,ITEMID VARCHAR(10) NOT NULL,QUANTITY INTEGER NOT NULL,UNITPRICE DECIMAL(10,2) NOT NULL,CONSTRAINT PK_LINEITEM PRIMARY KEY(ORDERID,LINENUM))
CREATE MEMORY TABLE CATEGORY(CATID VARCHAR(10) NOT NULL PRIMARY KEY,NAME VARCHAR(80),DESCN VARCHAR(255))
CREATE MEMORY TABLE PRODUCT(PRODUCTID VARCHAR(10) NOT NULL PRIMARY KEY,CATEGORY VARCHAR(10) NOT NULL,NAME VARCHAR(80),DESCN VARCHAR(255),CONSTRAINT FK_PRODUCT_1 FOREIGN KEY(CATEGORY) REFERENCES CATEGORY(CATID))
CREATE INDEX PRODUCTCAT ON PRODUCT(CATEGORY)
CREATE INDEX PRODUCTNAME ON PRODUCT(NAME)
CREATE MEMORY TABLE ITEM(ITEMID VARCHAR(10) NOT NULL PRIMARY KEY,PRODUCTID VARCHAR(10) NOT NULL,LISTPRICE DECIMAL(10,2),UNITCOST DECIMAL(10,2),SUPPLIER INTEGER,STATUS VARCHAR(2),ATTR1 VARCHAR(80),ATTR2 VARCHAR(80),ATTR3 VARCHAR(80),ATTR4 VARCHAR(80),ATTR5 VARCHAR(80),CONSTRAINT FK_ITEM_1 FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(PRODUCTID),CONSTRAINT FK_ITEM_2 FOREIGN KEY(SUPPLIER) REFERENCES SUPPLIER(SUPPID))
CREATE INDEX ITEMPROD ON ITEM(PRODUCTID)
CREATE MEMORY TABLE INVENTORY(ITEMID VARCHAR(10) NOT NULL PRIMARY KEY,QTY INTEGER NOT NULL)
CREATE MEMORY TABLE SEQUENCE(NAME VARCHAR(30) NOT NULL PRIMARY KEY,NEXTID INTEGER NOT NULL)
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
INSERT INTO SUPPLIER VALUES(1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797')
INSERT INTO SUPPLIER VALUES(2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797')
INSERT INTO SIGNON VALUES('ACID','ACID')
INSERT INTO SIGNON VALUES('j2ee','j2ee')
INSERT INTO ACCOUNT VALUES('ACID','acid@yourdomain.com','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO ACCOUNT VALUES('j2ee','yourname@yourdomain.com','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO PROFILE VALUES('ACID','english','CATS',1,1)
INSERT INTO PROFILE VALUES('j2ee','english','DOGS',1,1)
INSERT INTO BANNERDATA VALUES('BIRDS','')
INSERT INTO BANNERDATA VALUES('CATS','')
INSERT INTO BANNERDATA VALUES('DOGS','')
INSERT INTO BANNERDATA VALUES('FISH','')
INSERT INTO BANNERDATA VALUES('REPTILES','')
INSERT INTO CATEGORY VALUES('BIRDS','Birds',' Birds')
INSERT INTO CATEGORY VALUES('CATS','Cats',' Cats')
INSERT INTO CATEGORY VALUES('DOGS','Dogs',' Dogs')
INSERT INTO CATEGORY VALUES('FISH','Fish',' Fish')
INSERT INTO CATEGORY VALUES('REPTILES','Reptiles',' Reptiles')
INSERT INTO PRODUCT VALUES('AV-CB-01','BIRDS','Amazon Parrot','Great companion for up to 75 years')
INSERT INTO PRODUCT VALUES('AV-SB-02','BIRDS','Finch','Great stress reliever')
INSERT INTO PRODUCT VALUES('FI-FW-01','FISH','Koi','Fresh Water fish from Japan')
INSERT INTO PRODUCT VALUES('FI-FW-02','FISH','Goldfish','Fresh Water fish from China')
INSERT INTO PRODUCT VALUES('FI-SW-01','FISH','Angelfish','Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FI-SW-02','FISH','Tiger Shark','Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FL-DLH-02','CATS','Persian','Friendly house cat, doubles as a princess')
INSERT INTO PRODUCT VALUES('FL-DSH-01','CATS','Manx','Great for reducing mouse populations')
INSERT INTO PRODUCT VALUES('K9-BD-01','DOGS','Bulldog','Friendly dog from England')
INSERT INTO PRODUCT VALUES('K9-CW-01','DOGS','Chihuahua','Great companion dog')
INSERT INTO PRODUCT VALUES('K9-DL-01','DOGS','Dalmation','Great dog for a Fire Station')
INSERT INTO PRODUCT VALUES('K9-PO-02','DOGS','Poodle','Cute dog from France')
INSERT INTO PRODUCT VALUES('K9-RT-01','DOGS','Golden Retriever','Great family dog')
INSERT INTO PRODUCT VALUES('K9-RT-02','DOGS','Labrador Retriever','Great hunting dog')
INSERT INTO PRODUCT VALUES('RP-LI-02','REPTILES','Iguana','Friendly green friend')
INSERT INTO PRODUCT VALUES('RP-SN-01','REPTILES','Rattlesnake','Doubles as a watch dog')
INSERT INTO ITEM VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-19','AV-SB-02',15.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-20','FI-FW-02',5.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-21','FI-FW-02',5.29,1.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-22','K9-RT-02',135.50,100.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-23','K9-RT-02',145.49,100.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-24','K9-RT-02',255.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-25','K9-RT-02',325.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-26','K9-CW-01',125.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-27','K9-CW-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-28','K9-RT-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO INVENTORY VALUES('EST-1',10000)
INSERT INTO INVENTORY VALUES('EST-10',10000)
INSERT INTO INVENTORY VALUES('EST-11',10000)
INSERT INTO INVENTORY VALUES('EST-12',10000)
INSERT INTO INVENTORY VALUES('EST-13',10000)
INSERT INTO INVENTORY VALUES('EST-14',10000)
INSERT INTO INVENTORY VALUES('EST-15',10000)
INSERT INTO INVENTORY VALUES('EST-16',10000)
INSERT INTO INVENTORY VALUES('EST-17',10000)
INSERT INTO INVENTORY VALUES('EST-18',10000)
INSERT INTO INVENTORY VALUES('EST-19',10000)
INSERT INTO INVENTORY VALUES('EST-2',10000)
INSERT INTO INVENTORY VALUES('EST-20',10000)
INSERT INTO INVENTORY VALUES('EST-21',10000)
INSERT INTO INVENTORY VALUES('EST-22',10000)
INSERT INTO INVENTORY VALUES('EST-23',10000)
INSERT INTO INVENTORY VALUES('EST-24',10000)
INSERT INTO INVENTORY VALUES('EST-25',10000)
INSERT INTO INVENTORY VALUES('EST-26',10000)
INSERT INTO INVENTORY VALUES('EST-27',10000)
INSERT INTO INVENTORY VALUES('EST-28',10000)
INSERT INTO INVENTORY VALUES('EST-3',10000)
INSERT INTO INVENTORY VALUES('EST-4',10000)
INSERT INTO INVENTORY VALUES('EST-5',10000)
INSERT INTO INVENTORY VALUES('EST-6',10000)
INSERT INTO INVENTORY VALUES('EST-7',10000)
INSERT INTO INVENTORY VALUES('EST-8',10000)
INSERT INTO INVENTORY VALUES('EST-9',10000)
INSERT INTO SEQUENCE VALUES('linenum',1000)
INSERT INTO SEQUENCE VALUES('ordernum',1000)
