民航订票管理系统设计+文档.zip

/*用户信息*/ create table Users( userName varchar(20) not null, password varchar(20) not null) insert into Users values('Aaron','123456'),('Jeff','456789'),('Tom','789456') /*管理员信息*/ create table Administrators( userName varchar(20) not null, password varchar(20) not null) insert into Administrators values('10086','123456'),('12306','456789') insert into Administrators values('administrator','456789') /*航空公司信息*/ create table AirCompany( name varchar(30) primary key not null, address varchar(30) not null, tel varchar(20) not null, description varchar(100) not null) insert into AirCompany values('上海航空公司','上海市静安区江宁路212号','95530','前身是上海航空公司，成立于1985年12月') insert into AirCompany values('中国东方航空','上海市闵行区虹翔三路36号','95530','成立于1988年6月25日') insert into AirCompany values('中国南方航空','广东广州','4006695539','成立于1995年3月25日') insert into AirCompany values('海南航空','海南省海口市','95339','于1993年成立') insert into AirCompany values('四川航空','四川省成都市','95378','成立于1986年9月19日') insert into AirCompany values('深圳航空','深圳市宝安区宝','95361','成立于1992年10月'), ('山东航空','济南市历下区二环东路5746号','95369','1994年经中国民用航空总局和山东省政府批准成立'), ('中国国航','北京','95583','于1988年在北京正式成立,'), ('厦门航空','福建厦门','95557','成立于1984年7月25日') select name from AirCompany drop table AirCompany /*航线信息*/ create table Airline( airlineNo varchar(20) primary key not null, origin varchar(20) not null, destination varchar(20) not null, distance float not null, aircompany varchar(30) not null) alter table Airline add constraint airlineNo_check check(len(airlineNo)>0 and len(airlineNo)<20) select distinct origin from Airline select distinct destination from Airline select count(*) from Airline drop table Airline insert into Airline values('SHTOBJ001','上海','北京',1197,'上海航空公司'), ('SHTONJ002','上海','南京',343,'上海航空公司'), ('SHTOHEB003','上海','哈尔滨',1854,'上海航空公司'), ('SHTOXA004','上海','西安',1351,'上海航空公司'), ('SHTOSZ005','上海','深圳',1221,'上海航空公司'), ('SHTONC006','上海','南昌',644,'上海航空公司') insert into Airline values('HKTOSH001','海口','上海',1661,'海南航空'), ('CDTOSH001','成都','上海',1782,'四川航空'), ('SZTOSH001','深圳','上海',1343,'深圳航空'), ('JNTOSH001','济南','上海',852,'山东航空'), ('XMTOSH001','厦门','上海',878,'厦门航空') insert into Airline values('BJTOHEB001','北京','哈尔滨',1095,'中国国航') select * from Airline delete from Airline where airlineNo='SHTOXG007' /*VIP客户信息*/ create table VIPCustomers( name varchar(20) not null, sex varchar(3) not null, age int, id varchar(20) not null primary key, tel varchar(20) not null, address varchar(50) not null, vipgrade int, vipscore int) alter table VIPCustomers add constraint vipname_check check(len(name)>=1) alter table VIPCustomers add constraint vipage_check check(age>=0) alter table VIPCustomers add constraint vipid_check check(len(id)>=15 and len(id)<=18) alter table VIPCustomers add constraint viptel_check check(len(tel)>=3 and len(tel)<=15) alter table VIPCustomers add constraint vipgrade_check check(vipgrade>=0) alter table VIPCustomers add constraint vipscore_check check(vipscore>=0) select * from VIPCustomers drop table VIPCustomers insert into VIPCustomers values('吴林春','男',20,'321000199803192222','13917052985','上海市浦东新区','3','300') ('王五','男',30,'641xxxxxx','138200xxxxx','上海市黄浦区','3','1000'); /*订单信息*/ create table BookTicketOrder( name varchar(20) not null, sex varchar(3) not null, age int, id varchar(20) not null, tel varchar(20) not null, address varchar(30) not null, origin varchar(20) not null, destination varchar(20) not null, leaveTime DateTime not null, arriveTime DateTime not null, flightNo varchar(20) not null, airlineNo varchar(20) not null, seatType varchar(10) not null, airCompany varchar(30) not null, vipgrade int, vipscore int, ticketPrice float, /*numeric(10,2),长度10带两位小数*/ discount float, actualPrice float) alter table BookTicketOrder add constraint listname_check check(len(name)>=1) alter table BookTicketOrder add constraint listage_check check(age>=0) alter table BookTicketOrder add constraint listid_check check(len(id)>=15 and len(id)<=18) alter table BookTicketOrder add constraint listtel_check check(len(tel)>=3 and len(tel)<=15) alter table BookTicketOrder add constraint listvipgrade_check check(vipgrade>=0) alter table BookTicketOrder add constraint listvipscore_check check(vipscore>=0) alter table BookTicketOrder add constraint listticketPrice_check check(ticketPrice>=0) alter table BookTicketOrder add constraint listdiscount_check check(discount>=0 and discount<=1) alter table BookTicketOrder add constraint listactualPrice_check check(actualPrice>=0 and actualPrice<=ticketPrice) drop drop table BookTicketOrder delete from BookTicketOrder where id='320922197905138447' select * from BookTicketOrder delete * from BookTicketOrder; select sum(actualPrice) from BookTicketOrder /*航班信息*/ create table FlightInformation( flightNo varchar(20) primary key not null, airlineNo varchar(20) not null, origin varchar(10) not null, destination varchar(10) not null, leaveTime DateTime not null, arriveTime DateTime not null, totalSeats int not null , firstPrice float not null , businessPrice float not null , economyPrice float not null , firstRemain int not null , businessRemain int not null, economyRemain int not null , occupancy float not null , planeModel varchar(10) not null, airCompany varchar(30) not null) /*foreign key(airlineNo) references Airline(airlineNo))*/ /*使用触发器做级联删除*/ /*alter table FlightInformation add constraint leaveTime_check check(len(leaveTime)=12) alter table FlightInformation add constraint arriveTime_check check(len(arriveTime)=12)*/ alter table FlightInformation add constraint totalSeats_check check(totalSeats>0) alter table FlightInformation drop constraint totalSeats_check alter table FlightInformation add constraint firstPrice_check check(firstPrice>=0) alter table FlightInformation add constraint businessPrice_check check(businessPrice>=0) alter table FlightInformation add constraint economyPrice_check check(economyPrice>=0) alter table FlightInformation add constraint firstRemain_check check(firstRemain>=0 and firstRemain<= totalSeats) alter table FlightInformation add constraint businessRemain_check check(businessRemain>=0 and businessRemain<= totalSeats) alter table FlightInformation add constraint economyRemain_check check(economyRemain>=0 and economyRemain<= totalSeats) alter table FlightInformation add constraint occupancy_check check(occupancy>=0 and occupancy<= 1) insert into FlightInformation values('8L9603A','SHTOBJ001','上海','北京','2018-10-30 12:00','2018-10-30 16:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司'), ('8L9604B','SHTOBJ001','上海','北京','2018-11-02 12:00','2018-11-02 16:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司'), ('8L9605C','SHTONJ002','上海','南京','2018-11-01 02:00','2018-11-01 06:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司'), insert into FlightInformation values('8L9606D','SHTOHEB003','上海','哈尔滨','2018-10-30 12:00','2018-10-30 16:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司') insert into FlightInformation values('8L9607E','SHTOBJ001','上海','北京','2018-11-06 12:00','2018-11-06 16:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司') ('8L9608F','SHTOXA004','上海','西安','2018-10-30 12:00','2018-10-30 16:00',100,1000,800,500,10,30,60, 0,'波音737','上海航空公司'); insert into FlightInformation values('9L9604A','XMTOSH001','厦门','上海','2018-11-20 09:30','2018-11-20 10:30',80,1200,800,500,10,20,50, 0,'波音787','厦门航空'),('9L3614W','XMTOSH001','厦门','上海','2018-11-21 09:30','2018-11-2