create table region (   
         regionid   integer,
         name   varchar(32),
         primary key (regionid));

create table nation (   
         nationid   integer,
         name   varchar(32),
         regionid   integer,
         primary key (nationid),
         foreign key (regionid) references region);

create table parts(   
         partid   integer,
         name     varchar(32),
         mfgr     varchar(32),
         brand    varchar(32),
         type     varchar(32),
         retailprice   float,
         primary key (partid));

create table suppliers(   
         suppid   integer,
         name   varchar(32),
         address   varchar(128),
         nationid   integer,
         phone   varchar(32),
         acctbal   float,
         primary key (suppid),
         foreign key (nationid) references nation);

create table partsupp(   
         partid   integer,
         suppid   integer,
         availqty   integer,
         supplycost   float,
         foreign key (partid) references parts,
         foreign key (suppid) references suppliers);

insert into region(regionid, name)  values (1, 'Asia');
insert into region(regionid, name)  values (2, 'North America');
insert into nation(nationid, name, regionid)  values (1, 'USA', 2);
insert into nation(nationid, name, regionid)  values (2, 'Canada', 2);
insert into nation(nationid, name, regionid)  values (3, 'Mexico', 2);
insert into nation(nationid, name, regionid)  values (4, 'China', 1);
insert into nation(nationid, name, regionid)  values (5, 'Japan', 1);
insert into suppliers(suppid, name, address, nationid, phone, acctbal) values (1, 'cava international', '20th and washington, phila, pa', 1, '555-5555', 40000);
insert into suppliers(suppid, name, address, nationid, phone, acctbal) values (2, 'donatucci kitchens', '20th and washington, phila, pa', 1, '555-1212', 80000);
insert into suppliers(suppid, name, address, nationid, phone, acctbal) values (3, 'carter tile', 'shanghai', 4, '123-456-7890', 30000);
insert into parts(partid, name, mfgr, brand, type, retailprice)  values (1, 'widget', 'dupont', 'dupont home', 'generic', 10.0);
insert into parts(partid, name, mfgr, brand, type, retailprice) values (2, 'toto', 'dupont', 'dupont home', 'generic', 5.0);
insert into parts(partid, name, mfgr, brand, type, retailprice) values (3, 'zaza', 'dupont', 'dupont home', 'generic', 8.0);
insert into partsupp(partid, suppid, availqty, supplycost)  values (1, 1, 25, 75.0);
insert into partsupp(partid, suppid, availqty, supplycost)  values (2, 2, 13, 26.0);




create table sellers(
         sellerid   integer,
         rating     char,
         email      varchar(32),
         primary key (sellerid));

create table items(   
         itemid   integer,
         type     varchar(16),
         primary key (itemid));

create table buyers(   
         buyerid   integer,
         email     varchar(32),
         address   varchar(64),
         primary key (buyerid));

create table stock(   
         itemid     integer,
         sellerid   integer,
         startbid   float,
         quantity   integer,
         endingtime integer,
         foreign key (itemid) references items,
         foreign key (sellerid) references sellers);


create table purchases(   
         itemid    integer,
         buyerid   integer,
         sellerid  integer,
         price     float,
         purchasequantity   integer,
         bidtime   integer,
         foreign key (itemid) references items,
         foreign key (buyerid) references buyers,
         foreign key (sellerid) references sellers);

insert into sellers(sellerid, rating, email)   values (1, 'a', 'tom@cis.upenn.edu');
insert into sellers(sellerid, rating, email)   values (2, 'c', 'jessie@cis.upenn.edu');
insert into sellers(sellerid, rating, email)   values (3, 'a', 'mike@cis.upenn.edu');

insert into items(itemid, type)  values (1, 'car');
insert into items(itemid, type)  values (2, 'computer');
insert into items(itemid, type)  values (3, 'book');

insert into buyers(buyerid, address, email)  values (1, '3330 walnut st', 'john@cis.upenn.edu');
insert into buyers(buyerid, address, email)  values (2, '3815 spruce st', 'tom@cis.upenn.edu');
insert into buyers(buyerid, address, email)  values (3, '3815 spruce st', 'erika@cis.upenn.edu');

insert into stock(itemid, sellerid, startbid, quantity, endingtime)  values (1, 2, 10.0, 100, 20);
insert into stock(itemid, sellerid, startbid, quantity, endingtime)  values (2, 2, 3.0,  50,  15); 
insert into stock(itemid, sellerid, startbid, quantity, endingtime)  values (3, 1, 50.0, 20,  10);
insert into stock(itemid, sellerid, startbid, quantity, endingtime)  values (3, 3, 40.0, 10,  20);

insert into purchases(itemid, buyerid, sellerid, price, purchasequantity, bidtime) values (1, 1, 2, 11.0, 1,  10);
insert into purchases(itemid, buyerid, sellerid, price, purchasequantity, bidtime) values (1, 3, 2, 10.0, 10, 20);
insert into purchases(itemid, buyerid, sellerid, price, purchasequantity, bidtime) values (2, 2, 1, 5.0,  5,  5);
insert into purchases(itemid, buyerid, sellerid, price, purchasequantity, bidtime) values (3, 3, 2, 11.0, 1, 10);