table3.txt

电子投标系统

create table member(
	member_login		VARCHAR2(20),	//20
	member_password		VARCHAR2(14)	NOT NULL,	//14
	member_fname		VARCHAR2(20)	NOT NULL,
	member_middle		VARCHAR2(20),	
	member_last		VARCHAR2(20)	NOT NULL,
	member_email		VARCHAR2(30)	NOT NULL,	//30
	member_dob		DATE,
	member_phone		NUMBER(15),             //15
	member_stadd		VARCHAR2(60),
	member_city		VARCHAR2(12),
	member_code		NUMBER(6),
	member_state		VARCHAR2(20),	//20
	member_country		VARCHAR2(15),	//15
	member_visanumber	NUMBER(15)	NOT NULL,
	member_visaname		VARCHAR2(40)	NOT NULL,   	//40
	member_visaexpiry	DATE		NOT NULL,
	CONSTRAINT member_member_login_pk PRIMARY KEY(member_login));

create table auction(
	auction_id		 NUMBER(5),
	auction_start_date		DATE		NOT NULL,
	auction_duration		NUMBER(3)	NOT NULL,
	auction_start_bid		NUMBER(8),
	auction_reserved_bid	NUMBER(8)	 NOT NULL,
	CONSTRAINT auction_auction_id_pk PRIMARY KEY (auction_id));

create table auctiondetails(
	auction_id		NUMBER(5),
	seller_login		VARCHAR2(10)	NOT NULL,
	i_category		NUMBER(5),
	item_title		VARCHAR2(30)	 NOT NULL,
	item_description		VARCHAR2(100),
	item_quantity		NUMBER(3)	 NOT NULL,
	item_location		VARCHAR2(20)	 NOT NULL,
	item_country		VARCHAR2(15)	 NOT NULL,
	item_picture		VARCHAR2(25),
	item_sound		VARCHAR2(25),
	item_shipping_info	NUMBER(1)	 NOT NULL,
	item_payment_info	NUMBER(1)	 NOT NULL,
	CONSTRAINT auctiondetails_seller_login_fk FOREIGN KEY (seller_login)	
	REFERENCES member (member_login),
	CONSTRAINT auctiondetails_auction_id_fk FOREIGN KEY (auction_id)	
	REFERENCES auction (auction_id),		
	CONSTRAINT auctiondetails_i_category_fk FOREIGN KEY (i_category)	
	REFERENCES category (category_id),
	CONSTRAINT auctiondetails_auction_id_pk PRIMARY KEY (auction_id));

create table bidding(
	member_buyer_login	VARCHAR2(10),		
	auction_id		NUMBER(5)	NOT NULL,
	quantity			NUMBER(3)	NOT NULL,
	proxy_max_amount	NUMBER(5),
	proxy_increments		NUMBER(5),
	latest_bid_amount		NUMBER(5)	NOT NULL,
	CONSTRAINT bidding_member_buyer_login_fk FOREIGN 	KEY(member_buyer_login)
	REFERENCES member(member_login),
	CONSTRAINT bidding_auction_id_fk FOREIGN KEY(auction_id)
	REFERENCES auction(auction_id));

create table finalizedbids(
	buyer_login		VARCHAR2(10)	NOT NULL,		
	auction_id		NUMBER(5)	NOT NULL,
	Auction_quantity		NUMBER(3)	NOT NULL,
	Max_bid_amount		NUMBER(5)	NOT NULL,
	CONSTRAINT finalizedbids_buyer_login_fk FOREIGN KEY(buyer_login)
	REFERENCES member(member_login),
	CONSTRAINT finalizedbids_auction_id_fk FOREIGN KEY(auction_id)
	REFERENCES auction(auction_id));


create table category(
	category_id	NUMBER(5),
	category_name	VARCHAR2(15)	NOT NULL,
	CONSTRAINT category_category_id_pk PRIMARY KEY (category_id));

create table feedback(
	auction_id	NUMBER(5),
	giver_login	VARCHAR2(10),
	about_login	VARCHAR2(10),
	user_buyer	NUMBER(1),
	rating		NUMBER(2),
	FeedbackNotes	VARCHAR2(200),
	CONSTRAINT feedback_auction_id_fk FOREIGN KEY(auction_id)
	REFERENCES auction(auction_id),
	CONSTRAINT feedback_giver_login_fk FOREIGN KEY(giver_login)
	REFERENCES member(member_login),
	CONSTRAINT feedback_about_login_fk FOREIGN KEY(about_login)
	REFERENCES member(member_login));

create sequence auction_id
increment by 1
start with 1
MAXVALUE 99999
NOCYCLE;

 create sequence category_id
increment by 1
maxvalue 100
nocycle;

alter table member modify (member_login VARCHAR2(20));
alter table member modify (member_password VARCHAR2(14));
alter table member modify (member_email VARCHAR2(30));
alter table member modify (member_phone VARCHAR2(15));
alter table member modify (member_state VARCHAR2(20));
alter table member modify (member_country VARCHAR2(15));
alter table member modify (member_visaname VARCHAR2(20));