设计目的：
随着社会发展的不断进步，旅游业的蓬勃发展，机票预订系统对人们的生活影响越来越大，为了实现航空公司以及旅游业的现代化管理，方便旅客，需要开发一个机票预订系统。该系统具有完整的存储，查询，核对，打印机票等功能。在这个系统中，旅客在旅行社的机票预售代理点，通过输入计算机系统查询，系统为旅客安排航班，印出取票通知和账单，旅客在飞机起飞前凭其二代、取票通知和账单取票，系统校对无误后即打印机票给顾客。
数据库实施:以选定的DBMS为平台，创建出完整的数据库。在数据库中要表现出的知识点包括：
- 创建库、表 包括定义参照完整性、实体完整性、自定义完整性。
- 创建用户 包括创建用户或角色，并授予相应的权限(用户设计至少3个，有不同的权限，且设置合理)
- 数据库编程 恰当使用触发器、存储过程、游标或事务等相关技术(至少使用一种)，实现至少一个有效且有意义的功能。
E-R图
创建数据库的过程如下
完整的库可以直接导入，我在资源中已经上传。
数据库创建的注释都在代码中。
#创建航班数据库
CREATE DATABASE fight;
#创建航班信息表
CREATE TABLE table_fight
(
fight_no char(20) PRIMARY KEY,
start_place VARCHAR(30) not null,
end_place VARCHAR(30) not null,
departure_time datetime not null,
arrival_time datetime not null,
air_model char(20)
)
#飞机信息表
CREATE TABLE table_air
(
air_model char(20) not null,
air_setting int(3) not null,
crew_num int(2) not null
)
#乘客信息表
CREATE TABLE table_passenger
(
passenger_id char(18) PRIMARY KEY,
passenger_name VARCHAR(4) not null ,
passenger_tel char(11) not null,
passenger_sex CHAR(2) default '男' CHECK (passenger_sex in('男','女'))
)
#机组人员信息表
CREATE TABLE table_crew
(
crew_no char(20) PRIMARY KEY,
crew_name VARCHAR(30) not null,
crew_position VARCHAR(30) not null,
fight_no char(20) not null
)
#机票表
CREATE TABLE table_ticket
(
fight_no char(20) not null,
passenger_id char(18) not null,
passenger_name VARCHAR(4) not null ,
ticket_price int(5) not null,
passenger_seat VARCHAR(20) not null
)
#外键
#机组人员和航班信息外键
ALTER TABLE fight.table_crew
ADD CONSTRAINT fight_crew_no FOREIGN KEY (fight_no) REFERENCES fight.table_fight (fight_no);
#乘客机票和航班信息外键
ALTER TABLE fight.table_ticket
ADD CONSTRAINT fight_ticket_no FOREIGN KEY (fight_no) REFERENCES fight.table_fight (fight_no);
#索引
#为table_fight添加唯一索引，字段为fight_no
ALTER TABLE fight.table_fight
ADD UNIQUE INDEX no_index(fight_no);
#为table_passenger添加唯一索引，字段为passenger_id
ALTER TABLE fight.table_passenger
ADD UNIQUE INDEX id_index(passenger_id);
#插入数据
#创建航班信息
INSERT INTO table_fight VALUES
('南航CZ3172','北京大兴','深圳宝安T3','2022-06-10 16:00:00','2022-6-10 19:25:00','空客321(中)'),
('深航ZH9112','北京首都T3','深圳宝安T3','2022-06-10 12:25:00','2022-6-10 18:05:00','波音737(中)'),
('海航HU7715','北京首都T2','深圳宝安T3','2022-06-10 13:55:00','2022-6-10 17:25:00','波音737(中)'),
('南航CZ3176','北京大兴','深圳宝安T3','2022-06-10 12:00:00','2022-6-10 15:30:00','空客320(中)'),
('南航CZ3190','北京大兴','深圳宝安T3','2022-06-10 20:00:00','2022-6-10 23:30:00','空客321(中)'),
('海航HU7711','北京首都T2','深圳宝安T3','2022-06-10 09:00:00','2022-6-10 12:30:00','波音737(中)'),
('厦航MF8455','北京大兴','重庆江北T3','2022-06-10 21:20:00','2022-6-10 00:10:00','波音737(中)'),
('河北航NS8455','北京大兴','重庆江北T3','2022-06-10 21:20:00','2022-6-10 00:10:00','波音737(中)'),
('东航MU6687','北京大兴','重庆江北T3','2022-06-10 18:00:00','2022-6-10 21:00:00','空客321(中)'),
('南航CZ2704','北京大兴','重庆江北T3','2022-06-10 17:40:00','2022-6-10 20:45:00','空客320(中)'),
('东航MU6683','北京大兴','重庆江北T3','2022-06-10 08:00:00','2022-6-10 00:10:55','空客320(中)'),
('国航CA1431','北京首都T3','重庆江北T3','2022-06-10 08:00:00','2022-6-10 00:10:55','波音787(大)');
#查看航班信息
SELECT * FROM table_fight ORDER BY table_fight.departure_time asc
#插入飞机信息
INSERT INTO table_air VALUES
('空客321(中)',168,9),('波音737(中)',166,8),('空客320(中)',168,9),('波音787(大)',196,10);
#乘客信息表
INSERT INTO table_passenger VALUES
(130429120010604185,'王夏青',18056702810,'男'),
(545281199502141055,'陈倩',15133605890,'女'),
(251281198509041745,'周子旭',13135670280,'男'),
(341567200302041075,'赵守国',18243602890,'男'),
(987281198611041055,'李强',18033602890,'男'),
(341281200002041835,'钱明明',17833685890,'女'),
(154281200007044515,'王宏伟',18031402890,'男'),
(261281197502041035,'吴强力',19136028090,'男'),
(265881200011044135,'杨微',18033602890,'女'),
(452186199810260025,'杨四',18811652222,'男'),
(156235198002052366,'宋哈',15522226352,'女'),
(425632199905160031,'顾娃娃',19963652224,'男'),
(203645200011236632,'张发发',15566663636,'女'),
(102325200210160002,'李丽丽',18802020052,'男'),
(205632200606053200,'张哇',13777885858,'女'),
(425635199905301201,'王哇塞',13799996666,'女'),
(521365198205041036,'杨哥',12222553615,'女'),
(130429199908093636,'杨大大',15523654555,'男'),
(142536199610087896,'李哇塞',13052000016,'男'),
(341281200002041035,'李大力',18033602890,'男'),
(161254200602231253,'李赛博',13052000016,'男'),
(351281198712061068,'顾兴顺',12515647855,'男'),
(658741197708061428,'戴苗苗',18051564455,'女'),
(458281199711065428,'王子贤',15156156455,'男'),
(115124200308061365,'徐永福',19115647858,'男'),
(351281199911061058,'李子璇',18015547585,'女'),
(748576548968565867,'玩一涵',18790374747,'男'),
(450981200707894276,'故事',18738873738,'男'),
(674384948475758393,'周琳琳',1837348388,'女'),
(673467357435783457,'张菲菲',1672673624,'女'),
(623746746326325734,'张六子',1787894589,'男'),
(130445200102172525,'王奇迹',13052000016,'女'),
(625364199903261212,'杨辉',13052000016,'男'),
(625364199812169899,'李子涵',13052000016,'女'),
(125896199510300023,'王梓萌',13052000016,'女'),
(132546199906050006,'王博涵',13052000016,'男');
#机组人员信息插入
INSERT INTO table_crew VALUES
(0001,'李琳','乘务员','南航CZ3172'),
(0002,'顾小娟','乘务员','南航CZ3172'),
(0003,'王蒙','乘务员','南航CZ3172'),
(0004,'刘晓丽','乘务员','南航CZ3172'),
(0005,'李丽丽','乘务员','南航CZ3172'),
(0006,'杨静静','乘务员','南航CZ3172'),
(0007,'杨文文','乘务员','南航CZ3172'),
(0008,'李涛','副机长','南航CZ3172'),
(0009,'王斌','机长','南航CZ3172'),
(0011,'李贝贝','乘务员','深航ZH9112'),
(0012,'王佳宁','乘务员','深航ZH9112'),
(0013,'刘洋洋','乘务员','深航ZH9112'),
(0014,'周可欣','乘务员','深航ZH9112'),
(0015,'李可盈','乘务员','深航ZH9112'),
(0016,'王志强','乘务员','深航ZH9112'),
(0017,'杨大海','乘务员','深航ZH9112'),
(0018,'周腾','副机长','深航ZH9112'),
(0019,'王志航','机长','深航ZH9112');
#插入机票信息表
INSERT INTO table_ticket VALUES
#航班号，身份证，姓名，票价，位置
('南航CZ3172',130429120010604185,'王夏青',455,'8F'),
('南航CZ3172',545281199502141055,'陈倩',455,'7D'),
('南航CZ3172',251281198509041745,'周子旭',687,'1E'),
('南航CZ3172',341567200302041075,'赵守国',485,'3A'),
('南航CZ3172',205632200606053200,'张哇',498,'5A'),
('南航CZ3172',425635199905301201,'王哇塞',498,'5C'),
('南航CZ3172',521365198205041036,'杨哥',498,'8F'),
('南航CZ3172',130429199908093636,'杨大大',498,'9E'),
('南航CZ3172',142536199610087896,'李哇塞',478,'5B'),
('南航CZ3172',341281200002041035,'李大力',486,'3D'),
('南航CZ3172',161254200602231253,'李赛博',438,'5A'),
('深航ZH9112',351281198712061068,'顾兴顺',456,'3E'),
('深航ZH9112',658741197708061428,'戴苗苗',418,'6C'),
('深航ZH9112',458281199711065428,'王子贤',498,'8E'),
('深航ZH9112',115124200308061365,'徐永福',437,'9F'),
('深航ZH9112',351281199911061058,'李子璇',458,'6A'),
('南航CZ3172',130429120010604185,'王夏青',452,'7A'),
('南航CZ3172',545281199502141055,'陈倩',486,'7E'),
('南航CZ3172',251281198509041745,'周子旭',480,'6A'),
('南航CZ3172',341567200302041075,'赵守国',586,'6B'),
('南航CZ3172',987281198611041055,'李强',680,'6C'),
('南航CZ3172',341281200002041835,'钱明明',520,'6D'),
('南航CZ3172',154281200007044515,'王宏伟',650,'6E'),
('南航CZ3172',261281197502041035,'吴强力',650,'6F'),
('南航CZ3172',265881200011044135,'杨微',498,'3D'),
('南航CZ3172',452186199810260025,'杨四',465,'3F'),
('南航CZ3172',156235198002052366,'宋哈',700,'3E'),
('南航CZ3172',425632199905160031,'顾娃娃',650,'5F'),
('南航CZ3172',203645200011236632,'张发发',630,'5E'),
('南航CZ3172',102325200210160002,'李丽丽',645,'5A'),
('深航ZH9112',748576548968565867,'玩一涵',480,'21A'),
('深航ZH9112',450981200707894276,'故事',620,'23E'),
('深航ZH9112',674384948475758393,'周琳琳',480,'32A'),
('深航ZH9112',673467357435783457,'张菲菲',620,'14B'),
('深航ZH9112',623746746326325734,'张六子',480,'25C'),
('深航ZH9112',130445200102172525,'王奇迹',480,'25E'),
('深航ZH9112',625364199903261212,'杨辉',620,'32C'),
('深航ZH9112',625364199812169899,'李子涵',580,'23B'),
('深航ZH9112',125896199510300023,'王梓萌',560,'33E'),
('深航ZH9112',132546199906050006,'王博涵',540,'27B');
#创建部分航班视图
CREATE VIEW view_fight AS
SELECT start_place,end_place,departure_time,arrival_time FROM table_fight;
#创建部分乘客信息
CREATE VIEW view_passenger AS
SELECT passenger_name ,
passenger_tel FROM table_passenger;
#存储过程
#查询南航CZ3172航班是否已满
DELIMITER $$
CREATE PROCEDURE proc_reserve_nanhang()
BEGIN
select (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "南航CZ3172")as num1,
(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" )as num2,
case
when
(select count(table_ticket.fight_no) from table_ticket WHERE fight_no LIKE "南航CZ3172") =(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" ) then '未售空'
when (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "南航CZ3172") >(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" ) then '已售空'
else '未售空' end as '南航CZ3172机票状态';
END $$
DELIMITER ;
CALL proc_reserve_nanhang();
#查询深航ZH9112航班是否已满
DELIMITER $$
CREATE PROCEDURE proc_reserve_shenhang()
BEGIN
select (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "深航ZH9112")as num1,
(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "深航ZH9112" )as num2,
case
when
(select count(table_ticket.fight_no) from table_ticket WHERE fight_no LIKE "深航ZH9112") =(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "深航ZH9112" ) then '未售空'
when (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "深航ZH9112") >(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "深航ZH9112" ) then '已售空'
else '未售空' end as '深航ZH9112机票状态';
END $$
DELIMITER ;
CALL proc_reserve_shenhang();
#验证身份证
DROP TRIGGER tri_idcheck;
create trigger tri_idcheck
before insert
on table_ticket
for each row
begin
IF LENGTH(NEW.passenger_id)< 18
THEN SET NEW.passenger_id= 'error' ;
END IF;
end ;
#插入乘客信息（在乘客表中）
DROP PROCEDURE proc_insert_passenger;
DELIMITER $$
create procedure proc_insert_passenger (IN id char(18),IN name VARCHAR(4),IN tel char(11),IN sex char(2) )
begin
INSERT INTO table_passenger
VALUES
(id,name,tel,sex);
end $$
DELIMITER ;
call proc_insert_passenger('123123120000000000','刘仔',18188995000,'男');
#插入乘客（在机票表中）
DROP PROCEDURE proc_insert_ticket;
DELIMITER $$
create procedure proc_insert_ticket (IN no char(20),IN id char(18),IN name varchar(4),IN price int(5),IN seat varchar(20) )
begin
INSERT INTO table_ticket
VALUES
(no,id,name ,price ,seat);
end $$
DELIMITER ;
call proc_insert_ticket('南航CZ3172',402210160002,'123',546,'7B');
#删除乘客信息（在乘客表）
DROP PROCEDURE proc_delete_passenger;
DELIMITER $$
create procedure proc_delete_passenger (IN id char(18),IN name VARCHAR(4) )
begin
DELETE FROM table_ticket WHERE table_ticket.passenger_name=name AND table_ticket.passenger_id=id;
end $$
DELIMITER ;
call proc_delete_passenger(123123120000000000,'杨歌');
#删除乘客信息（在机票表）
DROP PROCEDURE proc_delete_ticket;
DELIMITER $$
create procedure proc_delete_ticket (IN id char(18),IN name VARCHAR(4) )
begin
DELETE FROM table_ticket WHERE table_ticket.passenger_name=name AND table_ticket.passenger_id=id;
end $$
DELIMITER ;
call proc_delete_ticket('error','123');
#查询乘客信息
DELIMITER $$
CREATE PROCEDURE proc_passenger()
BEGIN
select * FROM view_passenger;
END $$
DELIMITER ;
CALL proc_passenger();
#乘客视图
DROP PROCEDURE proc_morepassenger;
DELIMITER $$
CREATE PROCEDURE proc_morepassenger()
BEGIN
select * FROM table_passenger;
END $$
DELIMITER ;
CALL proc_morepassenger();
#从视图查看航班信息（不包含飞机型号）
DROP PROCEDURE proc_fight;
DELIMITER $$
CREATE PROCEDURE proc_fight()
BEGIN
select * FROM view_fight;
END $$
DELIMITER ;
CALL proc_fight();
#查看航班信息（包含飞机型号）
DROP PROCEDURE proc_morefight;
DELIMITER $$
CREATE PROCEDURE proc_morefight()
BEGIN
select * FROM table_fight;
END $$
DELIMITER ;
CALL proc_morefight();
#权限
CREATE USER'client'@'localhost'IDENTIFIED BY'123';
#管理员
CREATE USER'customer member'@'localhost'IDENTIFIED BY'123';
#操作员
CREATE USER'manager '@'localhost'IDENTIFIED BY'123';
GRANT SELECT,UPDATE,INSERT,DELETE on table_air to 'manager'@'localhost';
GRANT SELECT,UPDATE,INSERT,DELETE on table_crew to 'manager'@'localhost';
GRANT SELECT,UPDATE,INSERT,DELETE on table_fight to 'manager'@'localhost';
GRANT SELECT,UPDATE,INSERT,DELETE on table_ticket to 'manager'@'localhost';
GRANT SELECT,UPDATE,INSERT,DELETE on table_passenger to 'manager'@'localhost';
GRANT SELECT on table_air to 'client'@'localhost';
GRANT SELECT on table_fight to 'client'@'localhost';
GRANT SELECT on table_air to 'customer member'@'localhost';
GRANT SELECT on table_crew to 'customer member'@'localhost';
GRANT SELECT on table_fight to 'customer member'@'localhost';
GRANT SELECT on table_ticket to 'customer member'@'localhost';
GRANT SELECT on table_passenger to 'customer member'@'localhost';
java使用 jdbc连接数据库
- 调用存储过程
- 打印数据库内容
package mysql;
import java.sql.*;
public class Mysql {
public static void main(String args[])
{
try
{
Class.forName("com.mysql.jdbc.Driver"); //加载MYSQL JDBC驱动程序
System.out.println("成功加载驱动!");
}
catch (Exception e)
{
System.out.print("加载驱动失败!");
e.printStackTrace();
}
Connection con;
String url = "jdbc:mysql://localhost:3306/fight?useSSL=false";
String user = "root";
String password = "123456";
try {
con = DriverManager.getConnection(url,user,password);
if(!con.isClosed())
System.out.println("成功连接数据库");
//2.创建statement类对象，用来执行SQL语句！！
Statement statement = con.createStatement();
String sql = "call proc_delete_ticket('error','123');";
// String sql = "select * from table_air";
ResultSet rs = statement.executeQuery(sql);
// while (rs.next()) {
// System.out.print(rs.getString("air_model"));
// System.out.print(" ");
// System.out.print(rs.getString("air_setting"));
// System.out.print(" ");
// System.out.print(rs.getString("crew_num"));
// System.out.print(" \n");
// }
System.out.println("操作成功");
}
catch (Exception e) {
System.out.print("获取信息错误!");
}
}
}
实验总结：
本次综合实验来说，我们组选的是航班售票管理系统课程设计，难度中上等，最重要的是
需求分析，难度是各个表的之间链接与分析，其次是表的属性，每- -个表的属性关系到各
个表之间的联系，所以要考虑到很多因素。在这次的程序设计过程中，通过对选题的仔细
研究与分析，在确定了设计的基本任务和要求后，我们组查阅了大量的相关资料，查看了
大量的程序代码，最终完成了这次设计。同时这次程序设计的每-一个功能，每–个模块，
每一段程序我们都认真仔细的查看了几次。
通过这次课程设计，我学到了很多课本上学不到的软件使用方法，和实用技术。同时也加
深了对课本知识的了解。而且我发现，有目标的学习更能促进我高效的投入到学习中来，
体会到了学习课本知识需要更多的课后实践练手，那样的学习经验对我们来说十分珍贵更
加希望能在今后的不断的学习中和努力中、把程序做得更好。
