体育场售票系统

/*体育馆*/ create table gymnasium ( gid VARCHAR2(6) not null, gname VARCHAR2(60), gaddress VARCHAR2(120), constraint PK_GYMNASIUM primary key (gid) ); insert into gymnasium values('100001','八一体育场','合肥市，高新区，天元路'); insert into gymnasium values('200001','安工大室内篮球场','马鞍山市，安徽工业大学'); insert into gymnasium values('100003','百花井体育广场','合肥市，百花井区，长江路'); insert into gymnasium values('100004','中科大新区体育广场','合肥市，中国科技大学'); insert into gymnasium values('200002','安工大东区体育场','马鞍山市，安徽工业大学（东区）'); insert into gymnasium values('200003','马鞍山市体育场','马鞍山市，解放路'); insert into gymnasium values('100005','合肥市体育场','合肥市，市中心'); insert into gymnasium values('100006','合工大新区体育场','合肥市，合肥工业大学'); /* 座位 */ create table seat ( gid VARCHAR2(6), sid VARCHAR2(6) not null, rid VARCHAR2(2), constraint PK_SEAT primary key (sid,gid) ); insert into seat values('100001','1','1'); insert into seat values('100001','2','1'); insert into seat values('100001','3','1'); insert into seat values('100001','4','1'); insert into seat values('100001','5','1'); insert into seat values('100001','6','1'); insert into seat values('100001','7','1'); insert into seat values('100001','8','1'); insert into seat values('100001','9','1'); insert into seat values('100001','10','1'); insert into seat values('100001','11','1'); insert into seat values('100001','12','1'); insert into seat values('100001','13','1'); insert into seat values('100001','14','2'); insert into seat values('100001','15','2'); insert into seat values('100001','16','2'); insert into seat values('100001','17','2'); insert into seat values('100001','18','2'); insert into seat values('100001','19','2'); insert into seat values('100001','20','2'); insert into seat values('100001','21','2'); insert into seat values('100001','23','2'); insert into seat values('100001','24','2'); insert into seat values('100001','25','2'); insert into seat values('100001','26','3'); insert into seat values('100001','27','3'); insert into seat values('100001','28','3'); insert into seat values('100001','29','3'); insert into seat values('100001','30','3'); insert into seat values('100001','31','3'); insert into seat values('100001','32','3'); insert into seat values('100001','33','3'); insert into seat values('100001','34','3'); insert into seat values('100001','35','3'); insert into seat values('100001','36','3'); insert into seat values('200001','1','1'); insert into seat values('200001','2','1'); insert into seat values('200001','3','1'); insert into seat values('200001','4','1'); insert into seat values('200001','5','1'); insert into seat values('200001','6','1'); insert into seat values('200001','7','1'); insert into seat values('200001','8','1'); insert into seat values('200001','9','1'); insert into seat values('200001','10','1'); insert into seat values('200001','11','1'); insert into seat values('200001','12','1'); insert into seat values('200001','13','1'); insert into seat values('200001','14','2'); insert into seat values('200001','15','2'); insert into seat values('200001','16','2'); insert into seat values('200001','17','2'); insert into seat values('200001','18','2'); insert into seat values('200001','19','2'); insert into seat values('200001','20','2'); insert into seat values('200001','21','2'); insert into seat values('200001','23','2'); insert into seat values('200001','24','2'); insert into seat values('200001','25','2'); insert into seat values('200001','26','3'); insert into seat values('200001','27','3'); insert into seat values('200001','28','3'); insert into seat values('200001','29','3'); insert into seat values('200001','30','3'); insert into seat values('200001','31','3'); insert into seat values('200001','32','3'); insert into seat values('200001','33','3'); insert into seat values('200001','34','3'); insert into seat values('200001','35','3'); insert into seat values('200001','36','3'); alter table seat add constraint FK_SEAT_REFERENCE_GYMNASIU foreign key (gid) references gymnasium (gid); /*体育赛事*/ create table sportingEvents ( se_id NUMBER(8) not null, se_name VARCHAR(30), constraint PK_SPORTINGEVENTS primary key (se_id) ); insert into sportingEvents values(10001,'篮球赛'); insert into sportingEvents values(10002,'篮球赛'); insert into sportingEvents values(20001,'足球赛'); insert into sportingEvents values(30001,'兵乓球赛'); insert into sportingEvents values(40001,'羽毛球赛'); insert into sportingEvents values(40002,'羽毛球赛'); insert into sportingEvents values(40003,'羽毛球赛'); /*座位级别*/ create table seat_rank ( sr_ID VARCHAR2(3) not null, sr_name VARCHAR(20), constraint PK_SEAT_RANK primary key (sr_ID) ); insert into seat_rank values('1','贵宾座位'); insert into seat_rank values('2','标准座位'); insert into seat_rank values('3','低价座位'); alter table seat_rank add constraint FK_SEAT_RAN_REFERENCE_SEAT foreign key (sr_ID) references seat (sid); /* 售票 */ drop table booking; create table booking ( b_s_id VARCHAR2(6) not null, cp_id NUMBER(8), b_no VARCHAR2(20), yes NUMBER(1), constraint PK_BOOKING primary key (b_s_id,cp_id) ); insert into booking values('1',71000001,'',1); insert into booking values('2',71000001,'',1); insert into booking values('3',71000001,'',1); insert into booking values('4',71000001,'',1); insert into booking values('5',71000001,'',1); insert into booking values('6',71000001,'',1); insert into booking values('7',71000001,'',1); insert into booking values('8',71000001,'',1); insert into booking values('9',71000001,'',1); insert into booking values('10',71000001,'',1); insert into booking values('12',71000001,'',1); insert into booking values('13',71000001,'',1); insert into booking values('14',71000001,'',1); insert into booking values('15',71000001,'',1); insert into booking values('16',71000001,'',1); insert into booking values('17',71000001,'',1); insert into booking values('18',71000001,'',1); insert into booking values('19',71000001,'',1); insert into booking values('20',71000001,'',1); insert into booking values('21',71000001,'',1); insert into booking values('22',71000001,'',1); insert into booking values('23',71000001,'',1); insert into booking values('24',71000001,'',1); insert into booking values('25',71000001,'',1); insert into booking values('26',71000001,'',1); insert into booking values('27',71000001,'',1); insert into booking values('28',71000001,'',1); insert into booking values('29',71000001,'',1); insert into booking values('30',71000001,'',1); insert into booking values('31',71000001,'',1); insert into booking values('32',71000001,'',1); insert into booking values('33',71000001,'',1); insert into booking values('34',71000001,'',1); insert into booking values('35',71000001,'',1); insert into booking values('36',71000001,'',1); alter table booking add constraint FK_BOOKING_REFERENCE_COMPETIT foreign key (cp_id) references competitionPlan (cp_id); alter table booking add constraint FK_BOOKING_REFERENCE_SEAT foreign key (b_s_id) references seat (sid); /* 赛事安排 */ create table competitionPlan ( cp_id NUMBER(8) not null, cp_se_id NUMBER(8), cp_g_id VARCHAR2(8), cp_c_id NUMBER(8), cp_mo_id NUMBER(8), cp_date2 DATE, cp_date1 DATE, cp_startdate DATE,