petstore.sql

仿照著名的petstore写的网上书店

create table category(
    categoryid VARCHAR(10) NOT NULL,
    name VARCHAR(25) NOT NULL,
    description VARCHAR(255) NOT NULL,
    imageurl VARCHAR(55),
    primary key (categoryid)
);

CREATE TABLE product (
 productid VARCHAR(10) NOT NULL,
 categoryid VARCHAR(10) NOT NULL,
 name VARCHAR(25) NOT NULL,
 description VARCHAR(255) NOT NULL,
 imageurl VARCHAR(55),
 primary key (productid),
 foreign key (categoryid) references category(categoryid)
);

CREATE TABLE Address (
 addressid VARCHAR(10) NOT NULL,
 street1 VARCHAR(55) NOT NULL,
 street2 VARCHAR(55),
 city VARCHAR(55) NOT NULL,
 state VARCHAR(25) NOT NULL,
 zip VARCHAR(5) NOT NULL,
 latitude DECIMAL(14,10) NOT NULL,
 longitude DECIMAL(14,10) NOT NULL,
 primary key (addressid)
);

CREATE TABLE SellerContactInfo (
 contactinfoid VARCHAR(10) NOT NULL,
 lastname VARCHAR(24) NOT NULL,
 firstname VARCHAR(24) NOT NULL,
 email VARCHAR(24) NOT NULL,
 primary key (contactinfoid)
);

CREATE TABLE item (
 itemid VARCHAR(10) NOT NULL,
 productid VARCHAR(10) NOT NULL,
 name VARCHAR(500) NOT NULL,
 description VARCHAR(500) NOT NULL,
 imageurl VARCHAR(100),
 imagethumburl VARCHAR(55),
 price DECIMAL(14,2) NOT NULL,
 address_addressid VARCHAR(10) NOT NULL,
 contactinfo_contactinfoid VARCHAR(10) NOT NULL,
 totalscore INTEGER NOT NULL,
 numberofvotes INTEGER NOT NULL,
 disabled INTEGER NOT NULL,
 primary key (itemid),
 foreign key (address_addressid) references Address(addressid),
 foreign key (productid) references product(productid),
 foreign key (contactinfo_contactinfoid) references SellerContactInfo(contactinfoid)
);

CREATE TABLE id_gen (
 gen_key VARCHAR(20) NOT NULL,
 gen_value INTEGER NOT NULL,
 primary key (gen_key)
);

CREATE TABLE ziplocation (
 zipcode INTEGER NOT NULL,
 city VARCHAR(30) NOT NULL,
 state VARCHAR(2) NOT NULL,
 primary key (zipcode)
);


create table tag(
    tagid INTEGER NOT NULL,
    tag VARCHAR(30) NOT NULL,
    refcount INTEGER NOT NULL,
    primary key (tagid),
    unique(tag)
);


create table tag_item(
    tagid INTEGER NOT NULL,
    itemid VARCHAR(10) NOT NULL,
    unique(tagid, itemid),
    foreign key (itemid) references item(itemid),
    foreign key (tagid) references tag(tagid)
);


INSERT INTO category VALUES('MEDICAL', 'Medical', 'Loving and finicky friends', 'medical_icon.jpg');
INSERT INTO category VALUES('SCIENCE', 'Science', 'Loving and furry friends', 'science_icon.JPG');
INSERT INTO category VALUES('HISTORY', 'History', 'Loving and feathery friends', 'history-icon.jpg');
INSERT INTO category VALUES('ARTS', 'Arts', 'Loving and scaly friends', 'arts-icon.JPG');
INSERT INTO category VALUES('BUSINESS', 'Business', 'Loving aquatic friends', 'business_icon.jpg');

INSERT INTO product VALUES('feline01', 'MEDICAL', 'Chinese ', 'Great for reducing your weight', 'medical.jpg');
INSERT INTO product VALUES('feline02', 'MEDICAL', 'West', 'benification of cancer patient', 'medical.jpg');
INSERT INTO product VALUES('canine01', 'SCIENCE', 'Computer', 'bring you to the C++ world', 'science.jpg');
INSERT INTO product VALUES('canine02', 'SCIENCE', 'Modern', 'give you more details on MS and linux','science.jpg');
INSERT INTO product VALUES('avian01', 'HISTORY', 'Asia ', 'the most important thing that affect Americans', 'history.jpg');
INSERT INTO product VALUES('avian02', 'HISTORY', 'Europa ', 'the greatest man in the US history','history.jpg');
INSERT INTO product VALUES('fish01', 'BUSINESS', 'Investing', 'useful thing for bussiness','business.jpg');
INSERT INTO product VALUES('fish02', 'BUSINESS', 'Operating ', 'we should have the proper attitude towards treasure ','business.jpg');
INSERT INTO product VALUES('reptile01', 'ARTS', 'Painting', 'revolutionary concept in current painting','arts.jpg');
INSERT INTO product VALUES('reptile02', 'ARTS', 'Music', 'introduce the experiences of several stars','arts.jpg');

INSERT INTO Address VALUES('1', 'W el Camino Real & Castro St', '', 'Mountain View','CA','94040',37.38574,-122.083973);
INSERT INTO Address VALUES('2', 'Shell Blvd & Beach Park Blvd', '', 'Foster City','CA','94404',37.546935,-122.263978);
INSERT INTO Address VALUES('3', 'River Oaks Pky & Village Center Dr', '', 'San Jose','CA','95134',37.398259,-121.922367);
INSERT INTO Address VALUES('4', 'S 1st St & W Santa Clara St', '', 'San Jose','CA','95113',37.336141,-121.890666);
INSERT INTO Address VALUES('5', '1st St & Market St ', '', 'San Francisco','CA','94105',37.791028,-122.399082);
INSERT INTO Address VALUES('6', 'Paseo Padre Pky & Fremont Blvd', '', 'Fremont','CA','94555',37.575035,-122.041273);
INSERT INTO Address VALUES('7', 'W el Camino Real & S Mary Ave', '', 'Sunnyvale','CA','94087',37.371641,-122.048772);
INSERT INTO Address VALUES('8', 'Bay Street and Columbus Ave ', '', 'San Francisco','CA','94133',37.805328,-122.416882);
INSERT INTO Address VALUES('9', 'El Camino Real & Scott Blvd', '', 'Santa Clara','CA','95050',37.352141 ,-121.959569);
INSERT INTO Address VALUES('10', 'W el Camino Real & Hollenbeck Ave', '', 'Sunnyvale','CA','94087',37.369941,-122.041271);
INSERT INTO Address VALUES('11', 'S Main St & Serra Way', '', 'Milpitas','CA','95035',37.428112,-121.906505);
INSERT INTO Address VALUES('12', 'Great Mall Pky & S Main St', '', 'Milpitas','CA','95035',37.414722,-121.902085);
INSERT INTO Address VALUES('13', 'Valencia St &  16th St', '', 'San Francisco','CA','94103',37.764985,-122.421886);
INSERT INTO Address VALUES('14', 'S 1st St & W Santa Clara St', '', 'San Jose','CA','95113',37.336141,-121.890666);
INSERT INTO Address VALUES('15', 'Bay Street and Columbus Ave ', '', 'San Francisco','CA','94133',37.805328,-122.416882);
INSERT INTO Address VALUES('16', 'El Camino Real & Scott Blvd', '', 'Santa Clara','CA','95050',37.352141 ,-121.959569);
INSERT INTO Address VALUES('17', 'Millbrae Ave &  Willow Ave', '', 'Millbrae ','CA','94030',37.596635,-122.391083);
INSERT INTO Address VALUES('18', 'Leavesley Rd & Monterey Rd', '', 'Gilroy','CA','95020',37.019447,-121.574953);
INSERT INTO Address VALUES('19', 'S Main St & Serra Way', '', 'Milpitas','CA','95035',37.428112,-121.906505);
INSERT INTO Address VALUES('20', '24th St &  Dolores St', '', 'San Francisco','CA','94114',37.75183,-122.424982);
INSERT INTO Address VALUES('21', 'Great Mall Pky & S Main St', '', 'Milpitas','CA','95035',37.414722,-121.902085);
INSERT INTO Address VALUES('22', 'Grant Rd & South Dr ', '', 'Mountain view','CA','94040',37.366941,-122.078073);