哈尔滨理工大学
软件与微电子学院
课程设计报告
| 课 程： | 数据库课程设计 |
| 题 目： | 飞机订票系统的数据库设计 |
| 班 级： | 软件18-1班 |
| 专 业： | 软件工程系 |
| 姓名： | 张立辉 |
| 学号： | 1814010130 |
| 指导教师： | 崔香 |
| 日期： | 2020年1月 3 日 |
一、数据库的背景及功能需求
系统背景：
航空业作为运输行业的基础 要提高我国运输行业的整体水平 必须从基础抓起。订票系统是航空业从事生产和管理的基层单位 加强订票系统是航空业基础地位的关键 也是保障航空业业可持续发展的重要基石。 随着现在航空运输业的发展 机票预订系统也成为了航空运输业的软件副产品 目前的管理系统都是与数据库关联 故数据库的管理也成为很热门的研究对象。
功能需求：
设计一个飞机订票系统
要求具备如下基本功能：
1、 客机基本信息的管理；
2、 航班信息的管理；
3、 旅客预定机票、取消预约、付款取票、退票的管理；
4、查询航班信息、航班预定情况、旅客信息。
二、 数据库的概念结构设计
名词： 客机，航线信息，舱位等级信息，订票信息，客户信息，客户类型信息
联系： 客机——（客机）——航线信息
航线信息——（订票2）——订票信息
订票信息——（等级）——舱位等级信息
订票信息——（订票）——客户信息
客户信息——（类型）——客户类型信息
属性和键：
客机：客机编号 航线编号 客机型号 航空公司 购买时间 服役时间 经济舱座位数 公务舱座位数量 头等舱座位数量 客机备注
航线信息：航线编号 出发城市 经停城市 到达城市 航班日期 出发时间 到达时间 经济舱价格 公务舱价格 头等舱价格 余票 航线信息备注
舱位等级信息：舱位等级编号 舱位等级名称 行李额 改签 退票 报销 舱位等级信息备注
订票信息：订单编号 客户编号 客户姓名 客户类型 折扣比例 航线编号 出发城市 到达城市 舱位等级 机票价格 结算金额 订票信息备注
客户信息：客户编号 客户姓名 客户性别 身份证号码 联系电话 客户类型编号 备注
客户类型信息：客户类型编号 客户类型名称 折扣比例 备注
E-R图：
三、数据库的逻辑结构设计
客机：（客机编号 航线编号 客机型号 航空公司 购买时间 服役时间 经济舱座位数量 公务舱座位数量 头等舱座位数量 客机备注）
航线信息：（航线编号 出发城市 经停城市 到达城市 航班日期 出发时间 到达时间 经济舱价格 公务舱价格 头等舱价格 余票 航线信息备注）
舱位等级信息：(舱位等级编号 舱位等级名称 行李额 改签 退票 报销 舱位等级信息备注)
订票信息：(订单编号 客户姓名 客户性别 联系电话 身份证号码 客户类型编号 折扣比例 出发城市 到达城市 经停城市 航班日期 航空公司 出发时间 到达时间 客机型号 舱位等级名称 机票价格 结算金额 订票信息备注)
客户信息：(客户编号 客户姓名 客户性别 身份证号码 联系电话 客户类型编号 备注)
客户类型信息：(客户类型编号 客户类型名称 折扣比例 备注)
四、数据库的物理结构设计
分析后建立以下表：
客机 表 储存客机信息
航线信息 表 储存航线信息
舱位等级信息 表 储存舱位等级信息
订票信息 表 储存订单信息
客户信息 表 储存用户信息
客户类型信息 表 储存用户等级信息
客机表：
航线信息表：
舱位等级信息表：
订票信息表：
客户信息表：
客户类型信息表：
关系图：
在SQL Server中建立 进销存管理系统 数据库
SQL语句如下：
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2012 */
/* Created on: 2019/12/30 15:35:12 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('客户信息') and o.name = 'FK_客户信息_类型_客户类型信息')
alter table 客户信息
drop constraint FK_客户信息_类型_客户类型信息
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('等级') and o.name = 'FK_等级_等级_舱位等级信息')
alter table 等级
drop constraint FK_等级_等级_舱位等级信息
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('等级') and o.name = 'FK_等级_等级2_订票信息')
alter table 等级
drop constraint FK_等级_等级2_订票信息
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('航线信息') and o.name = 'FK_航线信息_客机_客机')
alter table 航线信息
drop constraint FK_航线信息_客机_客机
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('订票信息') and o.name = 'FK_订票信息_订票_客户信息')
alter table 订票信息
drop constraint FK_订票信息_订票_客户信息
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('订票信息') and o.name = 'FK_订票信息_订票2_航线信息')
alter table 订票信息
drop constraint FK_订票信息_订票2_航线信息
go
if exists (select 1
from sysindexes
where id = object_id('客户信息')
and name = '类型_FK'
and indid > 0
and indid < 255)
drop index 客户信息.类型_FK
go
if exists (select 1
from sysobjects
where id = object_id('客户信息')
and type = 'U')
drop table 客户信息
go
if exists (select 1
from sysobjects
where id = object_id('客户类型信息')
and type = 'U')
drop table 客户类型信息
go
if exists (select 1
from sysobjects
where id = object_id('客机')
and type = 'U')
drop table 客机
go
if exists (select 1
from sysindexes
where id = object_id('等级')
and name = '等级2_FK'
and indid > 0
and indid < 255)
drop index 等级.等级2_FK
go
if exists (select 1
from sysindexes
where id = object_id('等级')
and name = '等级_FK'
and indid > 0
and indid < 255)
drop index 等级.等级_FK
go
if exists (select 1
from sysobjects
where id = object_id('等级')
and type = 'U')
drop table 等级
go
if exists (select 1
from sysindexes
where id = object_id('航线信息')
and name = '客机_FK'
and indid > 0
and indid < 255)
drop index 航线信息.客机_FK
go
if exists (select 1
from sysobjects
where id = object_id('航线信息')
and type = 'U')
drop table 航线信息
go
if exists (select 1
from sysobjects
where id = object_id('舱位等级信息')
and type = 'U')
drop table 舱位等级信息
go
if exists (select 1
from sysindexes
where id = object_id('订票信息')
and name = '订票2_FK'
and indid > 0
and indid < 255)
drop index 订票信息.订票2_FK
go
if exists (select 1
from sysindexes
where id = object_id('订票信息')
and name = '订票_FK'
and indid > 0
and indid < 255)
drop index 订票信息.订票_FK
go
if exists (select 1
from sysobjects
where id = object_id('订票信息')
and type = 'U')
drop table 订票信息
go
/*==============================================================*/
/* Table: 客户信息 */
/*==============================================================*/
create table 客户信息 (
客户编号 char(256) not null,
客户类型编号 char(256) null,
客户姓名 char(256) not null,
客户性别 char(256) null,
身份证号码 bigint null,
联系电话 int null,
客户信息备注 char(256) null,
constraint PK_客户信息 primary key nonclustered (客户编号)
)
go
/*==============================================================*/
/* Index: 类型_FK */
/*==============================================================*/
create index 类型_FK on 客户信息 (
客户类型编号 ASC
)
go
/*==============================================================*/
/* Table: 客户类型信息 */
/*==============================================================*/
create table 客户类型信息 (
客户类型编号 char(256) not null,
客户类型名称 char(256) null,
折扣比例 char(256) null,
客户类型信息备注 char(256) null,
constraint PK_客户类型信息 primary key nonclustered (客户类型编号)
)
go
/*==============================================================*/
/* Table: 客机 */
/*==============================================================*/
create table 客机 (
客机编号 char(256) not null,
客机型号 char(256) null,
航空公司 char(256) null,
购买时间 datetime null,
服役时间 datetime null,
经济舱座位数量 int null,
公务舱座位数量 int null,
头等舱座位数量 int null,
客机备注 char(256) null,
constraint PK_客机 primary key nonclustered (客机编号)
)
go
/*==============================================================*/
/* Table: 等级 */
/*==============================================================*/
create table 等级 (
舱位等级编号 char(256) not null,
订单编号 char(256) not null,
constraint PK_等级 primary key (舱位等级编号, 订单编号)
)
go
/*==============================================================*/
/* Index: 等级_FK */
/*==============================================================*/
create index 等级_FK on 等级 (
舱位等级编号 ASC
)
go
/*==============================================================*/
/* Index: 等级2_FK */
/*==============================================================*/
create index 等级2_FK on 等级 (
订单编号 ASC
)
go
/*==============================================================*/
/* Table: 航线信息 */
/*==============================================================*/
create table 航线信息 (
航线编号3 char(256) not null,
客机编号 char(256) null,
出发城市 char(256) null,
经停城市 char(256) null,
到达城市 char(256) null,
航班日期 datetime null,
出发时间 varchar(1024) null,
到达时间 varchar(1024) null,
经济舱价格 int null,
公务舱价格 int null,
头等舱价格 int null,
余票 int null,
航线信息备注 char(256) null,
航空公司 char(256) null,
客机型号 char(256) null,
constraint PK_航线信息 primary key nonclustered (航线编号3)
)
go
/*==============================================================*/
/* Index: 客机_FK */
/*==============================================================*/
create index 客机_FK on 航线信息 (
客机编号 ASC
)
go
/*==============================================================*/
/* Table: 舱位等级信息 */
/*==============================================================*/
create table 舱位等级信息 (
舱位等级编号 char(256) not null,
舱位等级名称 char(256) null,
行李额 char(256) null,
改签 char(256) null,
退票 char(256) null,
报销 char(256) null,
舱位等级信息备注 char(256) null,
constraint PK_舱位等级信息 primary key nonclustered (舱位等级编号)
)
go
/*==============================================================*/
/* Table: 订票信息 */
/*==============================================================*/
create table 订票信息 (
订单编号 char(256) not null,
航线编号3 char(256) null,
客户编号 char(256) null,
出发城市 char(256) null,
到达城市 char(256) null,
机票价格 char(256) null,
结算金额 char(256) null,
订票信息备注 char(256) null,
出发时间 varchar(1024) null,
到达时间 varchar(1024) null,
客户姓名 char(256) null,
客户性别 char(256) null,
客机型号 char(256) null,
经停城市 char(256) null,
联系电话 int null,
航班日期 datetime null,
航空公司 char(256) null,
舱位等级名称 char(256) null,
身份证号码 bigint null,
航线编号 char(10) null,
constraint PK_订票信息 primary key nonclustered (订单编号)
)
go
/*==============================================================*/
/* Index: 订票_FK */
/*==============================================================*/
create index 订票_FK on 订票信息 (
客户编号 ASC
)
go
/*==============================================================*/
/* Index: 订票2_FK */
/*==============================================================*/
create index 订票2_FK on 订票信息 (
航线编号3 ASC
)
go
alter table 客户信息
add constraint FK_客户信息_类型_客户类型信息 foreign key (客户类型编号)
references 客户类型信息 (客户类型编号)
go
alter table 等级
add constraint FK_等级_等级_舱位等级信息 foreign key (舱位等级编号)
references 舱位等级信息 (舱位等级编号)
go
alter table 等级
add constraint FK_等级_等级2_订票信息 foreign key (订单编号)
references 订票信息 (订单编号)
go
alter table 航线信息
add constraint FK_航线信息_客机_客机 foreign key (客机编号)
references 客机 (客机编号)
go
alter table 订票信息
add constraint FK_订票信息_订票_客户信息 foreign key (客户编号)
references 客户信息 (客户编号)
go
alter table 订票信息
add constraint FK_订票信息_订票2_航线信息 foreign key (航线编号3)
references 航线信息 (航线编号3)
go建立触发器：
删除客户编号时完全删除客户信息
--创建单更新触发器(删除客户)
create trigger 删除客户
on 客户信息
for delete
as
delete 客户信息 where 客户编号 in (select 客户姓名 from deleted)
delete 客户信息 where 客户编号 in (select 客户类型编号 from deleted)
delete 客户信息 where 客户编号 in (select 客户性别 from deleted)
delete 客户信息 where 客户编号 in (select 身份证号码 from deleted)
delete 客户信息 where 客户编号 in (select 联系电话 from deleted)
delete 客户信息 where 客户编号 in (select 客户信息备注 from deleted)
go修改客户信息表中数据时同时修改订票信息表中数据
--创建多表更新触发器(修改客户)
create Trigger 修改客户
On 客户信息
after update
As
if update(客户姓名) --变更姓名时触发
begin
update 订票信息 SET 订票信息.客户姓名 = 客户信息.客户姓名 FROM 订票信息,客户信息 WHERE 订票信息.客户编号 = 客户信息.客户编号;
end
if update(客户性别) --变更性别时触发
begin
update 订票信息 SET 订票信息.客户性别 = 客户信息.客户性别 FROM 订票信息,客户信息 WHERE 订票信息.客户编号 = 客户信息.客户编号;
end
if update(联系电话) --变更联系电话时触发
begin
update 订票信息 SET 订票信息.联系电话 = 客户信息.联系电话 FROM 订票信息,客户信息 WHERE 订票信息.客户编号 = 客户信息.客户编号;
end
if update(身份证号码) --变更身份证号码时触发
begin
update 订票信息 SET 订票信息.身份证号码 = 客户信息.身份证号码 FROM 订票信息,客户信息 WHERE 订票信息.客户编号 = 客户信息.客户编号;
end修改航线信息表中数据时同时修改订票信息表中数据
--创建多表更新触发器(修改航线)
create Trigger 修改航线
On 航线信息
after update
As
if update(出发城市 ) --变更出发城市时触发
begin
update 订票信息 SET 订票信息.出发城市 = 航线信息.出发城市 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end
if update(到达城市 ) --变更到达城市时触发
begin
update 订票信息 SET 订票信息.到达城市 = 航线信息.到达城市 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end
if update( 经停城市) --变更经停城市时触发
begin
update 订票信息 SET 订票信息.经停城市 = 航线信息.经停城市 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end
if update( 出发时间) --变更出发时间时触发
begin
update 订票信息 SET 订票信息.出发时间 = 航线信息.出发时间 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end
if update( 到达时间) --变更到达时间时触发
begin
update 订票信息 SET 订票信息. 到达时间= 航线信息.到达时间 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end
if update(航班日期) --变更航班日期时触发
begin
update 订票信息 SET 订票信息. 航班日期= 航线信息.航班日期 FROM 订票信息,航线信息 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end修改客机表中数据时同时修改航线信息表与订票信息表中数据
--创建多表更新触发器(客机)
create Trigger 客机触发器
On 客机
after update
As
if update(客机型号) --变更客机型号时触发
begin
update 航线信息 SET 航线信息.客机型号 = 客机.客机型号 FROM 客机,航线信息 WHERE 航线信息.客机编号 = 客机.客机编号 ;
update 订票信息 SET 订票信息.客机型号 = 客机.客机型号 FROM 航线信息,订票信息,客机 WHERE 订票信息.航线编号3 = 航线信息.航线编号3 ;
end修改舱位等级信息表中数据时同时修改等级表中数据
--创建多表更新触发器(舱位等级)
create Trigger 舱位等级
On 舱位等级信息
after update
As
if update(舱位等级编号) --变更客机型号时触发
begin
update 等级 SET 等级.舱位等级编号 = 舱位等级信息.舱位等级编号 FROM 等级,舱位等级信息 ;
end修改订票信息表中数据时同时修改等级表中数据
--创建insert插入类型触发器
if (object_id('等级1') is not null)
drop trigger 等级1
go
create trigger 等级1
on 订票信息
for insert --插入触发
as
--定义变量
declare @id char, @name char;
--在inserted表中查询已经插入记录信息
select @id = 订单编号 from 订票信息;
select @name = 舱位等级编号 from 舱位等级信息;
insert into 等级 values(@name, @id);
print '添加等级成功！';
go删除触发器：
drop trigger 删除客户
drop trigger修改客户
drop trigger修改航线
drop trigger客机触发器
drop trigger舱位等级
drop trigger 等级1
存储过程：显示客户
create procedure 显示客户
as
select 客户编号,客户姓名,客户性别,身份证号码,联系电话,客户信息备注,客户类型名称,折扣比例,客户类型信息备注
from 客户信息,客户类型信息
go
执行语句：
execute 显示客户
go
存储过程：订单航线信息
create procedure 订单航线信息
as
select 出发城市,到达城市,经停城市,航班日期,航空公司,出发时间,到达时间,客机型号,经济舱价格,公务舱价格,头等舱价格,余票,舱位等级名称,行李额,改签,退票,报销
from 舱位等级信息,航线信息
go
执行语句：
execute 订单航线信息
go
存储过程：客机信息
create procedure 客机信息
as
select 客机.客机型号,客机.航空公司,购买时间,服役时间,经济舱座位数量,公务舱座位数量,头等舱座位数量,经济舱价格,公务舱价格,头等舱价格,舱位等级名称,行李额,改签,退票,报销
from 客机,航线信息,舱位等级信息
go
执行语句：
execute 客机信息
go添加数据：
--添加数据
INSERT INTO 客户类型信息
VALUES('0011','vip','0.1','无');
INSERT INTO 客户信息
VALUES('00011','0011','客户','男','230121','150046','无');
INSERT INTO 舱位等级信息
VALUES('011','头等舱','10kg','Y','Y','Y','无');
INSERT INTO 客机
VALUES('01011','11112','中国航空',2000-08-12,2000-09-09,'10','10','10','无');
INSERT INTO 航线信息
VALUES('00111','01011','哈尔滨','无','长沙',01-01,'9:30','12:30','100','200','300','11','无','中国航空','KJ');
INSERT INTO 订票信息
VALUES('1111','00111','00011','哈尔滨','长沙','300','270','无','9:30','12:30','客户','男','01011','无','150046',2019-09-09,'中国航空','头等舱','230121', '00111 ');查询数据：
--查询数据
select * from 客户信息
where 客户姓名='客户';修改数据：
--修改数据
update 客户信息
set 客户姓名='小客户' where 客户编号 = '00011';删除数据：
ALTER TABLE 订票信息
DROP CONSTRAINT FK_订票信息_订票_客户信息
delete from 客户信息
where 客户编号 = '00011'
ALTER TABLE 订票信息 ADD CONSTRAINT FK_订票信息_订票_客户信息 FOREIGN KEY([客户编号])
REFERENCES 客户信息 ([客户编号])
GO五、自我总结
这次的数据库实验历时数周，但收获也是颇大的。
实验设计的工具比较多，包括对数据库进行操作的开发语言工具，数据库管理系统，还有一些辅助的工具，如powerdesigner等。也体会到了各种工具的方便性，但是无论什么工具都是为了实现功能服务的。
这门课的课设虽然是数据库课程设计，但其实对于数据结构的考察也是有的，数据库在数据结构的范围中可以看成是一种加强版的线性表的结构，也就是针对这张表来进行增删改查，实际上还帮助我们简化了自己通过编程去管理表结构的步骤，所以说，这次的课程设计的覆盖面也是很广的，做一个课程设计能够学到多方知识。
总而言之，这次的课程通过这么多天一个一个的敲进去，提高了我的动手能力，也让我对开发系统有了初步的认识。以后还是要多写程序来提高自己的编程和设计能力。