CREATE USER SA PASSWORD "" ADMIN
/*C1*/CONNECT USER sa PASSWORD ""
create table supplier (\u000a suppid int not null,\u000a name varchar(80) null,\u000a status varchar(2) not null,\u000a addr1 varchar(80) null,\u000a addr2 varchar(80) null,\u000a city varchar(80) null,\u000a state varchar(80) null,\u000a zip varchar(5) null,\u000a phone varchar(80) null,\u000a constraint pk_supplier primary key (suppid)\u000a)
\u000a\u000acreate table signon (\u000a username varchar(25) not null,\u000a password varchar(25) not null,\u000a constraint pk_signon primary key (username)\u000a)
\u000a\u000acreate table account (\u000a userid varchar(80) not null,\u000a email varchar(80) not null,\u000a firstname varchar(80) not null,\u000a lastname varchar(80) not null,\u000a status varchar(2) null,\u000a addr1 varchar(80) not null,\u000a addr2 varchar(40) null,\u000a city varchar(80) not null,\u000a state varchar(80) not null,\u000a zip varchar(20) not null,\u000a country varchar(20) not null,\u000a phone varchar(80) not null,\u000a constraint pk_account primary key (userid)\u000a)
\u000a\u000acreate table profile (\u000a userid varchar(80) not null,\u000a langpref varchar(80) not null,\u000a favcategory varchar(30),\u000a mylistopt int,\u000a banneropt int,\u000a constraint pk_profile primary key (userid)\u000a)
\u000a\u000acreate table bannerdata (\u000a favcategory varchar(80) not null,\u000a bannername varchar(255) null,\u000a constraint pk_bannerdata primary key (favcategory)\u000a)
\u000a\u000acreate table orders (\u000a orderid int not null,\u000a userid varchar(80) not null,\u000a orderdate date not null,\u000a shipaddr1 varchar(80) not null,\u000a shipaddr2 varchar(80) null,\u000a shipcity varchar(80) not null,\u000a shipstate varchar(80) not null,\u000a shipzip varchar(20) not null,\u000a shipcountry varchar(20) not null,\u000a billaddr1 varchar(80) not null,\u000a billaddr2 varchar(80) null,\u000a billcity varchar(80) not null,\u000a billstate varchar(80) not null,\u000a billzip varchar(20) not null,\u000a billcountry varchar(20) not null,\u000a courier varchar(80) not null,\u000a totalprice decimal(10,2) not null,\u000a billtofirstname varchar(80) not null,\u000a billtolastname varchar(80) not null,\u000a shiptofirstname varchar(80) not null,\u000a shiptolastname varchar(80) not null,\u000a creditcard varchar(80) not null,\u000a exprdate varchar(7) not null,\u000a cardtype varchar(80) not null,\u000a locale varchar(80) not null,\u000a constraint pk_orders primary key (orderid)\u000a)
\u000a\u000acreate table orderstatus (\u000a orderid int not null,\u000a linenum int not null,\u000a timestamp date not null,\u000a status varchar(2) not null,\u000a constraint pk_orderstatus primary key (orderid, linenum)\u000a)
\u000a\u000acreate table lineitem (\u000a orderid int not null,\u000a linenum int not null,\u000a itemid varchar(10) not null,\u000a quantity int not null,\u000a unitprice decimal(10,2) not null,\u000a constraint pk_lineitem primary key (orderid, linenum)\u000a)
\u000a\u000acreate table category (\u000a\u0009catid varchar(10) not null,\u000a\u0009name varchar(80) null,\u000a\u0009descn varchar(255) null,\u000a\u0009constraint pk_category primary key (catid)\u000a)
\u000a\u000acreate table product (\u000a productid varchar(10) not null,\u000a category varchar(10) not null,\u000a name varchar(80) null,\u000a descn varchar(255) null,\u000a constraint pk_product primary key (productid),\u000a constraint fk_product_1 foreign key (category)\u000a references category (catid)\u000a)
\u000a\u000acreate index productCat on product (category)
\u000acreate index productName on product (name)
\u000a\u000acreate table item (\u000a itemid varchar(10) not null,\u000a productid varchar(10) not null,\u000a listprice decimal(10,2) null,\u000a unitcost decimal(10,2) null,\u000a supplier int null,\u000a status varchar(2) null,\u000a attr1 varchar(80) null,\u000a attr2 varchar(80) null,\u000a attr3 varchar(80) null,\u000a attr4 varchar(80) null,\u000a attr5 varchar(80) null,\u000a constraint pk_item primary key (itemid),\u000a constraint fk_item_1 foreign key (productid)\u000a references product (productid),\u000a constraint fk_item_2 foreign key (supplier)\u000a references supplier (suppid)\u000a)
\u000a\u000acreate index itemProd on item (productid)
\u000a\u000acreate table inventory (\u000a itemid varchar(10) not null,\u000a qty int not null,\u000a constraint pk_inventory primary key (itemid)\u000a)
\u000a\u000aCREATE TABLE sequence\u000a(\u000a name varchar(30) not null,\u000a nextid int not null,\u000a constraint pk_sequence primary key (name)\u000a)
INSERT INTO SEQUENCE VALUES('ordernum',1000)
INSERT INTO SEQUENCE VALUES('linenum',1000)
INSERT INTO SIGNON VALUES('j2ee','j2ee')
INSERT INTO SIGNON VALUES('ACID','ACID')
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 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 PROFILE VALUES('j2ee','english','DOGS',1,1)
INSERT INTO PROFILE VALUES('ACID','english','CATS',1,1)
INSERT INTO BANNERDATA VALUES('FISH','')
INSERT INTO BANNERDATA VALUES('CATS','')
INSERT INTO BANNERDATA VALUES('DOGS','')
INSERT INTO BANNERDATA VALUES('REPTILES','')
INSERT INTO BANNERDATA VALUES('BIRDS','')
INSERT INTO CATEGORY VALUES('FISH','Fish',' Fish')
INSERT INTO CATEGORY VALUES('DOGS','Dogs',' Dogs')
INSERT INTO CATEGORY VALUES('REPTILES','Reptiles',' Reptiles')
INSERT INTO CATEGORY VALUES('CATS','Cats',' Cats')
INSERT INTO CATEGORY VALUES('BIRDS','Birds',' Birds')
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('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('K9-BD-01','DOGS','Bulldog','Friendly dog from England')
INSERT INTO PRODUCT VALUES('K9-PO-02','DOGS','Poodle','Cute dog from France')
INSERT INTO PRODUCT VALUES('K9-DL-01','DOGS','Dalmation','Great dog for a Fire Station')
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('K9-CW-01','DOGS','Chihuahua','Great companion dog')
INSERT INTO PRODUCT VALUES('RP-SN-01','REPTILES','Rattlesnake','Doubles as a watch dog')
INSERT INTO PRODUCT VALUES('RP-LI-02','REPTILES','Iguana','Friendly green friend')
INSERT INTO PRODUCT VALUES('FL-DSH-01','CATS','Manx','Great for reducing mouse populations')
INSERT INTO PRODUCT VALUES('FL-DLH-02','CATS','Persian','Friendly house cat, doubles as a princess')
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 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 ITEM VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large',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-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 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-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 INVENTORY VALUES('EST-1',10000)
INSERT INTO INVENTORY VALUES('EST-2',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 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-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)
