一、小组成员:
金智恒,李星良,刘杨
二、选题概要
需求描述:
航空售票管理系统是一个为航空公司、机场和旅客提供航班查询、机票预订、退票和改签等服务的系统。该系统需要能够管理航班信息,包括航班号、起飞和到达城市、起飞和到达时间、航班状态、座位数量、座位类型(经济舱、商务舱、头等舱)等。旅客应能够查询航班信息,选择合适的航班进行机票预订。预订时需要提供旅客的个人信息,包括姓名、联系方式、证件号码等。旅客还应能够对已预订的机票进行退票或改签操作,退票和改签的规则应根据航空公司的政策来设定。此外,系统还需要能够管理旅客的个人信息,包括姓名、联系方式、证件号码等。旅客应能够在个人中心查看和修改自己的个人信息。最后,系统应能够生成和管理旅客的机票订单,包括订单状态、支付状态等。旅客应能够在个人中心查看自己的所有订单。航空公司可以查看收益。
功能描述:
1.航班信息管理:系统管理员可以添加新的航班信息,修改现有航班的信息,或者删除已取消的航班。航班信息包括航班号、起飞和到达城市、起飞和到达时间、航班状态、座位数量、座位类型等。
2.机票预订:旅客可以通过输入起飞和到达城市以及日期来查询航班。查询结果会显示所有符合条件的航班信息,旅客可以选择合适的航班进行预订。预订时需要提供旅客的个人信息,包括姓名、联系方式、证件号码等。预订成功后,系统会生成一个订单。
3.退票和改签:旅客可以查看自己的机票订单,选择需要退票或改签的订单进行操作。退票后,系统会自动将机票状态更改为已退票。改签后,系统会生成新的机票订单。
4.旅客信息管理:旅客在预订机票时需要输入个人信息,系统会保存这些信息。旅客可以在个人中心查看和修改自己的个人信息。系统应保证旅客信息的安全,不会泄露给第三方。
5.订单管理:系统会为每一次机票预订生成一个订单,旅客可以在个人中心查看自己的所有订单,包括订单的状态和支付状态。系统应支持多种支付方式,包括信用卡、借记卡、支付宝、微信支付等。系统应能查询每日收益。
三、数据库实现(非最终结果)
er图:
关系模式:
1.航班信息表(Flights)
属性:航班编号 (Flight_ID), 航空公司 (Airline), 机型 (Aircraft_Type), 出发城市 (Departure_City), 到达城市 (Arrival_City)
主键:航班编号 (Flight_ID)
2,班次信息表(Flight_Schedules)
属性:班次编号 (Schedule_ID), 出发时间 (Departure_Time), 到达时间 (Arrival_Time), 出发机场 (Departure_Airport), 到达机场 (Arrival_Airport), 甲等余票 (First_Class_Available), 乙等余票 (Economy_Class_Available), 甲等价格 (First_Class_Price), 乙等价格 (Economy_Class_Price), 是否延误 (Is_Delayed), 航班编号 (Flight_ID)
主键:班次编号 (Schedule_ID)
外键:航班编号 (Flight_ID) 关联到航班信息表 (Flights)
3.座位信息表(Seats)
属性:座位编号 (Seat_ID), 班次编号 (Schedule_ID), 座位状态 (Seat_Status), 乘客证件号 (Passenger_ID)
主键:座位编号 (Seat_ID)
外键:
班次编号 (Schedule_ID) 关联到班次信息表 (Flight_Schedules)
乘客证件号 (Passenger_ID) 关联到用户信息表 (Passengers),可为空
4.订单信息表(Orders)
属性:订单号 (Order_ID), 乘客姓名 (Passenger_Name), 乘客证件号 (Passenger_ID), 下单日期 (Order_Date), 座位号 (Seat_ID), 账号 (Account_ID), 舱位 (Cabin_Type), 班次号 (Schedule_ID)
主键:订单号 (Order_ID)
外键:
账号 (Account_ID) 关联到用户信息表 (Passengers)
班次号 (Schedule_ID) 关联到班次信息表 (Flight_Schedules)
座位号 (Seat_ID) 关联到座位信息表 (Seats)
5.用户信息表(Passengers)
属性:账号 (Account_ID), 密码 (Password), 姓名 (Name), 年龄 (Age), 电话 (Phone), 性别 (Gender), 紧急联系人 (Emergency_Contact)
主键:账号 (Account_ID)
6.机场信息表(Airports)
属性:机场编号 (Airport_ID), 机场名称 (Airport_Name), 所在城市 (City), 地址 (Address)
主键:机场编号 (Airport_ID)
7.支付信息表(Payments)
属性:支付编号 (Payment_ID), 订单编号 (Order_ID), 支付方式 (Payment_Method), 支付状态 (Payment_Status), 支付日期 (Payment_Date)
主键:支付编号 (Payment_ID)
外键:订单编号 (Order_ID) 关联到订单信息表 (Orders)
8. 航空公司表(Airline_Details)
属性:航空公司编号 (Airline_ID), 航空公司名称 (Airline_Name), 总部所在地 (Headquarters)
主键:航空公司编号 (Airline_ID)
9. 飞机类型表(Aircraft_Types)
属性:机型编号 (Aircraft_ID), 机型名称 (Aircraft_Name), 最大乘客数 (Max_Passengers), 航程 (Range)
主键:机型编号 (Aircraft_ID)
10. 航空公司基地关系表(Airline_Base_Link)
属性:航空公司编号 (Airline_ID), 机场编号 (Airport_ID)
作用:标识哪些航空公司在哪些机场有运营基地或提供航班服务。
外键:
航空公司编号 (Airline_ID) 关联到航空公司表 (Airline_Details)
机场编号 (Airport_ID) 关联到机场信息表 (Airports)
11. 航班机型关系表(Flight_Aircraft_Link)
属性:航班编号 (Flight_ID), 机型编号 (Aircraft_ID)
作用:关联每个航班所使用的机型,以便跟踪和安排飞机。
外键:
航班编号 (Flight_ID) 关联到航班信息表 (Flights)
机型编号 (Aircraft_ID) 关联到飞机类型表 (Aircraft_Types)
12. 乘客订单关系表(Passenger_Order_Link)
属性:乘客证件号 (Passenger_ID), 订单号 (Order_ID)
作用:存储乘客的所有订单,用于追踪乘客的购票历史和订单状态。
外键:
乘客证件号 (Passenger_ID) 关联到用户信息表 (Passengers)
订单号 (Order_ID) 关联到订单信息表 (Orders)
13. 退票处理表(Order_Refunds)
属性:退票编号 (Refund_ID), 订单号 (Order_ID), 退票日期 (Refund_Date), 退款金额 (Refund_Amount), 处理状态 (Processing_Status), 处理日期 (Processed_Date)
主键:退票编号 (Refund_ID)
外键:订单号 (Order_ID) 关联到订单信息表 (Orders)
14. 航空公司机型关系 (Aircraft_ID_Aircraft_ID)
属性:航空公司编号 (Airline_ID), 机型编号 (Aircraft_ID)
作用:标识航空公司有哪些机型提供航班服务。
外键:
航空公司编号 (Airline_ID) 关联到航空公司表 (Airline_Details)
机型编号 (Aircraft_ID) 关联到飞机类型表 (Aircraft_Types)
数据流图:
sql创建:
CREATE DATABASE AirlineDB;
USE AirlineDB;
-- 航班信息表
CREATE TABLE Flights (
Flight_ID INT PRIMARY KEY, -- 航班编号
Airline VARCHAR(255), -- 航空公司
Aircraft_Type VARCHAR(255), -- 机型
Departure_City VARCHAR(255), -- 出发城市
Arrival_City VARCHAR(255) -- 到达城市
);
-- 班次信息表
CREATE TABLE Flight_Schedules (
Schedule_ID INT PRIMARY KEY, -- 班次编号
Departure_Time DATETIME, -- 出发时间
Arrival_Time DATETIME, -- 到达时间
Departure_Airport VARCHAR(255), -- 出发机场
Arrival_Airport VARCHAR(255), -- 到达机场
First_Class_Available INT, -- 甲等余票
Economy_Class_Available INT, -- 乙等余票
First_Class_Price DECIMAL(10, 2), -- 甲等价格
Economy_Class_Price DECIMAL(10, 2), -- 乙等价格
Is_Delayed BOOLEAN, -- 是否延误
Flight_ID INT, -- 航班编号
FOREIGN KEY (Flight_ID) REFERENCES Flights(Flight_ID) -- 外键,关联到航班信息表
);
-- 座位信息表
CREATE TABLE Seats (
Seat_ID INT PRIMARY KEY, -- 座位编号
Schedule_ID INT, -- 班次编号
Seat_Status VARCHAR(255), -- 座位状态
Passenger_ID VARCHAR(255), -- 乘客证件号
FOREIGN KEY (Schedule_ID) REFERENCES Flight_Schedules(Schedule_ID) -- 外键,关联到班次信息表
);
-- 订单信息表
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY, -- 订单号
Passenger_Name VARCHAR(255), -- 乘客姓名
Passenger_ID VARCHAR(255), -- 乘客证件号
Order_Date DATE, -- 下单日期
Seat_ID INT, -- 座位号
Account_ID VARCHAR(255), -- 账号
Cabin_Type VARCHAR(255), -- 舱位
Schedule_ID INT, -- 班次号
FOREIGN KEY (Schedule_ID) REFERENCES Flight_Schedules(Schedule_ID), -- 外键,关联到班次信息表
FOREIGN KEY (Seat_ID) REFERENCES Seats(Seat_ID) -- 外键,关联到座位信息表
);
-- 用户信息表
CREATE TABLE Passengers (
Account_ID VARCHAR(255) PRIMARY KEY, -- 账号
Password VARCHAR(255), -- 密码
Name VARCHAR(255), -- 姓名
Age INT, -- 年龄
Phone VARCHAR(255), -- 电话
Gender CHAR(1), -- 性别
Emergency_Contact VARCHAR(255) -- 紧急联系人
);
-- 机场信息表
CREATE TABLE Airports (
Airport_ID INT PRIMARY KEY, -- 机场编号
Airport_Name VARCHAR(255), -- 机场名称
City VARCHAR(255), -- 所在城市
Address VARCHAR(255) -- 地址
);
-- 支付信息表
CREATE TABLE Payments (
Payment_ID INT PRIMARY KEY, -- 支付编号
Order_ID INT, -- 订单编号
Payment_Method VARCHAR(255), -- 支付方式
Payment_Status VARCHAR(255), -- 支付状态
Payment_Date DATE, -- 支付日期
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) -- 外键,关联到订单信息表
);
-- 航空公司表
CREATE TABLE Airline_Details (
Airline_ID INT PRIMARY KEY, -- 航空公司编号
Airline_Name VARCHAR(255), -- 航空公司名称
Headquarters VARCHAR(255) -- 总部所在地
);
-- 飞机类型表
CREATE TABLE Aircraft_Types (
Aircraft_ID INT PRIMARY KEY, -- 机型编号
Aircraft_Name VARCHAR(255), -- 机型名称
Max_Passengers INT, -- 最大乘客数
`Range` INT -- 航程
);
-- 航空公司基地关系表
CREATE TABLE Airline_Base_Link (
Airline_ID INT, -- 航空公司编号
Airport_ID INT, -- 机场编号
FOREIGN KEY (Airline_ID) REFERENCES Airline_Details(Airline_ID), -- 外键,关联到航空公司表
FOREIGN KEY (Airport_ID) REFERENCES Airports(Airport_ID) -- 外键,关联到机场信息表
);
-- 航班机型关系表
CREATE TABLE Flight_Aircraft_Link (
Flight_ID INT, -- 航班编号
Aircraft_ID INT, -- 机型编号
FOREIGN KEY (Flight_ID) REFERENCES Flights(Flight_ID), -- 外键,关联到航班信息表
FOREIGN KEY (Aircraft_ID) REFERENCES Aircraft_Types(Aircraft_ID) -- 外键,关联到飞机类型表
);
-- 乘客订单关系表
CREATE TABLE Passenger_Order_Link (
Passenger_ID VARCHAR(255), -- 乘客证件号
Order_ID INT, -- 订单号
FOREIGN KEY (Passenger_ID) REFERENCES Passengers(Account_ID), -- 外键,关联到用户信息表
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) -- 外键,关联到订单信息表
);
-- 退票处理表
CREATE TABLE Order_Refunds (
Refund_ID INT PRIMARY KEY, -- 退票编号
Order_ID INT, -- 订单编号
Refund_Date DATE, -- 退票日期
Refund_Amount DECIMAL(10, 2), -- 退款金额
Processing_Status VARCHAR(255), -- 处理状态
Processed_Date DATE, -- 处理日期
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) -- 外键,关联到订单信息表
);
CREATE TABLE Airline_Aircraft_Link (
Airline_ID INT, -- 航空公司编号
Aircraft_ID INT, -- 机型编号
FOREIGN KEY (Airline_ID) REFERENCES Airline_Details(Airline_ID), -- 外键,关联到航空公司表
FOREIGN KEY (Aircraft_ID) REFERENCES Aircraft_Types(Aircraft_ID) -- 外键,关联到飞机类型表
);
触发器:
- 更新班次信息表的延误状态:当插入或更新Flight_Schedules表的记录时,如果出发时间或到达时间更改,使用触发器来自动将Is_Delayed字段设置为true。
DELIMITER // CREATE TRIGGER update_delay_status BEFORE UPDATE ON Flight_Schedules FOR EACH ROW BEGIN IF NEW.Departure_Time != OLD.Departure_Time OR NEW.Arrival_Time != OLD.Arrival_Time THEN SET NEW.Is_Delayed = true; END IF; END; // DELIMITER ;
- 更新座位信息表的座位状态:当插入Orders表的新记录时,使用触发器来自动将对应座位的Seat_Status字段设置为’已预订’。
DELIMITER // CREATE TRIGGER update_seat_status AFTER INSERT ON Orders FOR EACH ROW BEGIN UPDATE Seats SET Seat_Status = '已预订' WHERE Seat_ID = NEW.Seat_ID; END; // DELIMITER ;
- 更新航班余票:当Orders表插入新记录时,自动更新Flight_Schedules表中对应航班的余票数量。
DELIMITER // CREATE TRIGGER update_ticket_availability AFTER INSERT ON Orders FOR EACH ROW BEGIN IF NEW.Cabin_Type = '甲等' THEN UPDATE Flight_Schedules SET First_Class_Available = First_Class_Available - 1 WHERE Schedule_ID = NEW.Schedule_ID; ELSEIF NEW.Cabin_Type = '乙等' THEN UPDATE Flight_Schedules SET Economy_Class_Available = Economy_Class_Available - 1 WHERE Schedule_ID = NEW.Schedule_ID; END IF; END; // DELIMITER ;
- 更新座位状态:当订单被退订时,自动将Seats表中对应座位的状态更新为’未预订’。
DELIMITER // CREATE TRIGGER update_seat_status_after_refund AFTER INSERT ON Order_Refunds FOR EACH ROW BEGIN UPDATE Seats SET Seat_Status = '未预订' WHERE Seat_ID = (SELECT Seat_ID FROM Orders WHERE Order_ID = NEW.Order_ID); END; // DELIMITER ;
- 更新航班余票:当订单被退订时,自动更新Flight_Schedules表中对应航班的余票数量。
DELIMITER // CREATE TRIGGER update_ticket_availability_after_refund AFTER INSERT ON Order_Refunds FOR EACH ROW BEGIN DECLARE cabin_type VARCHAR(255); SET cabin_type = (SELECT Cabin_Type FROM Orders WHERE Order_ID = NEW.Order_ID); IF cabin_type = '甲等' THEN UPDATE Flight_Schedules SET First_Class_Available = First_Class_Available + 1 WHERE Schedule_ID = (SELECT Schedule_ID FROM Orders WHERE Order_ID = NEW.Order_ID); ELSEIF cabin_type = '乙等' THEN UPDATE Flight_Schedules SET Economy_Class_Available = Economy_Class_Available + 1 WHERE Schedule_ID = (SELECT Schedule_ID FROM Orders WHERE Order_ID = NEW.Order_ID); END IF; END; // DELIMITER ;
- 更新支付状态:当订单状态变为’已退款’时,自动将Payments表中对应支付记录的状态更新为’已退款’。
DELIMITER // CREATE TRIGGER update_payment_status_after_refund AFTER UPDATE ON Orders FOR EACH ROW BEGIN IF NEW.Order_Status = '已退款' THEN UPDATE Payments SET Payment_Status = '已退款' WHERE Order_ID = NEW.Order_ID; END IF; END; // DELIMITER ;
查询:
1.表查询
SELECT * FROM Flights;
SELECT * FROM Flight_Schedules;
SELECT * FROM Seats;
SELECT * FROM Orders;
SELECT * FROM Passengers;
SELECT * FROM Airports;
SELECT * FROM Payments;
SELECT * FROM Airline_Details;
SELECT * FROM Aircraft_Types;
SELECT * FROM Airline_Base_Link;
SELECT * FROM Flight_Aircraft_Link;
SELECT * FROM Passenger_Order_Link;
SELECT * FROM Order_Refunds;
2.查询某个航班的剩余座位数
DELIMITER //
CREATE FUNCTION GetAvailableSeats(flight_id INT)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE available_seats INT;
SELECT COUNT(*) INTO available_seats
FROM Seats
WHERE Schedule_ID IN (SELECT Schedule_ID FROM Flight_Schedules WHERE Flight_ID = flight_id)
AND Seat_Status = '未预订';
RETURN available_seats;
END; //
DELIMITER ;
SELECT GetAvailableSeats(航班ID);
3.查询某个订单及其对应的支付信息
DELIMITER //
CREATE PROCEDURE GetOrderAndPaymentInfo(IN order_id INT)
BEGIN
SELECT Orders.*, Payments.*
FROM Orders
INNER JOIN Payments ON Orders.Order_ID = Payments.Order_ID
WHERE Orders.Order_ID = order_id;
END; //
DELIMITER ;
CALL GetOrderAndPaymentInfo(订单ID);
4.查询某一天内所有班次的信息
DELIMITER //
CREATE PROCEDURE GetSchedulesByDate(date DATE)
BEGIN
SELECT *
FROM Flight_Schedules
WHERE Departure_Time BETWEEN CONCAT(date, ' 00:00:00') AND CONCAT(date, ' 23:59:59');
END; //
DELIMITER ;
CALL GetSchedulesByDate(日期);
5.查找出发地和目的地的班次
DELIMITER //
CREATE PROCEDURE GetSchedulesByCities(IN departure_city VARCHAR(255), IN arrival_city VARCHAR(255))
BEGIN
SELECT *
FROM Flight_Schedules
INNER JOIN Flights ON Flight_Schedules.Flight_ID = Flights.Flight_ID
WHERE Flights.Departure_City = departure_city AND Flights.Arrival_City = arrival_city;
END; //
DELIMITER ;
CALL GetSchedulesByCities('出发地', '目的地');
6.查询某个用户的订单
DELIMITER //
CREATE PROCEDURE GetOrdersByAccount(IN account_id VARCHAR(255))
BEGIN
SELECT *
FROM Orders
WHERE Orders.Account_ID = account_id;
END; //
DELIMITER ;
CALL GetOrdersByAccount('用户ID');