数据库综合实验之火车订票.doc

数据库原理数据库原理 课程设计报告课程设计报告 项目名称 项目名称 火车订票系统火车订票系统 专专 业 业 计算机科学与技术计算机科学与技术 班班 级 级 14 级级 1 班班 姓姓 名 名 组长姓名组长姓名 组员组员 1 姓名姓名 组员组员 2 姓名姓名 组员组员 3 姓名姓名 组员组员 4 姓名姓名 年年 月月 日日 目目 录录 1 任务描述任务描述 2 1 1 团队及任务分配介绍 2 1 2 功能需求 3 1 3 系统总体设计 4 数据库设计数据库设计 4 2 1 概念结构设计 4 2 2 数据库的逻辑设计 6 2 3 数据库的物理设计 7 2 4 关系图 8 3 数据库源代码数据库源代码 8 3 1 数据库建立 8 3 2 数据初始化 10 4 视图设计视图设计 函数 存储过程函数 存储过程 用 用 SQL 语言实现 而且用截图展示结果 语言实现 而且用截图展示结果 14 5 数据查询处理数据查询处理 编写至少编写至少 5 个实用的查询语句 包含多表连接 聚合函数的统计查询 排序等 个实用的查询语句 包含多表连接 聚合函数的统计查询 排序等 要在查询中用到视图 自定义函数 要在查询中用到视图 自定义函数 11 1 任务描述任务描述 1 11 1 团队及任务分配介绍团队及任务分配介绍 姓名班级任务描述数据库设 计 数据库源 代码 视图设计 函数 存 储过程 数据查询 处理 组长 组员 组员 组员 组员 1 21 2 功能需求功能需求 介绍介绍 火车订票管理系统需要完成功能主要有 1 查询 用户和管理员都可以查询车次信息 可以通过对车的目的地 初始地 票务的价格 座位的类型等查询 2 订票 用户订票成功后可查询自己的订单信息 3 退票 可将购买的车票删除 4 修改个人信息 用户可更改个人的身份证 姓名等基本信息 1 31 3 系统总体设计系统总体设计 数据库设计数据库设计 2 12 1 概念结构设计概念结构设计 介绍介绍 2 1 1 类别实体类别实体 E R 图 图 车次信息 E R 图 订票信息 E R 图 用户信息 E R 图 余票信息 E R 图 实体联系 E R 图 2 2 数据库的逻辑设计 逻辑结构设计 1 用户信息 订单号 身份证号 姓名 性别 年龄 2 订票信息 订单号 车次号 出发时间 到达时间 车票种类 票价 3 车次信息 车次号 始发站 终点站 出发时间 到达时间 4 余票信息 车票号 车次号 始发站 终点站 车票种类 剩余票数 票价 订票 时间 2 3 数据库的物理设计 用户信息 在数据库中的英文名称 表中列名表中列名数据类型数据类型可否为空可否为空说明说明 药品编号 INT NOT NULL 主键 药品编号 药品名称 CHARNOT NULL 药品名称 药品分类 CHARNOT NULL 药品分类 药品规格 CHARNOT NULL 药品规格 制造商编号 INTNOT NULL 制造商编号 进药单价 MONEYNULL 进药单价 卖药单价 MONEYNULL 卖药单价 制造商信息 在数据库中的英文名称 表中列名表中列名数据类型数据类型可否为空可否为空说明说明 制造商编号 INT NOT NULL 主键 制造商编号 制造商名称 VARCHARNOT NULL unique 制造商名称 制造商地址 VARCHARNOT NULL 制造商地址 制造商联系电话 VARCHARNULL 制造商联系电话 药品存储信息 在数据库中的英文名称 表中列名表中列名数据类型数据类型可否为空可否为空说明说明 仓库编号 INT NOT NULL 主键 仓库编号 药品编号 INT NOT NULL 外主键 药品编号 药品数量 INTNOT NULL 药品数量 买药人信息 在数据库中的英文名称 表中列名表中列名数据类型数据类型可否为空可否为空说明说明 买药人编号 INT NOT NULL 主键 买药人编号 药品编号 INT NOT NULL 外主键 药品编号 药品数量 INTNULL 药品数量 买药人姓名 CHARNULL 买药人姓名 买药人性别 CHARNULL 买药人性别 买药人年龄 INTNULL 买药人年龄 买药人联系电话 CHARNULL 买药人联系电话 2 42 4 关系图 关系图 数据库存表关系图 3 数据库源代码数据库源代码 必须把所有源代码最后保存为一个 SQL 文件 文件名称以项目名称命名 包含数据库建 立 数据表的建立 数据的输入 单表查询 视图设计 存储过程及触发器 3 13 1 数据库建立数据库建立 create database 药品管理系统 on name 药品管理 DATA filename e 药品管理系统 DATA 药品管理 DATA MDF size 10 maxsize 50 filegrowth 2 log on name 药品管理 LOG filename e 药品管理系统 DATA 药品管理 LOG LDF size 2 maxsize 10 filegrowth 1 go 3 1 2 创建创建药品信息表药品信息表 create table 药品信息 药品编号INT NOT NULL PRIMARY KEY 药品名称CHAR 40 NOT NULL 药品分类CHAR 20 NOT NULL 药品规格CHAR 20 制造商编号INT NOT NULL FOREIGN KEY REFERENCES 制造商信息 制造商编号 进药单价MONEY CHECK 进药单价 0 卖药单价MONEY CHECK 卖药单价 0 3 1 3 创建创建制造商信息表制造商信息表 CREATE TABLE 制造商信息 制造商编号INT NOT NULL PRIMARY KEY 制造商名称VARCHAR 40 NOT NULL unique 制造商地址VARCHAR 40 NOT NULL 制造商联系电话VARCHAR 20 3 1 4 创建创建药品存储信息表药品存储信息表 CREATE TABLE 药品存储信息 仓库编号INT NOT NULL PRIMARY KEY 药品编号INT NOT NULL FOREIGN KEY REFERENCES 药品信息 药品编号 药品数量INT NOT NULL CHECK 药品数量 0 3 1 5买药人信息表买药人信息表 CREATE TABLE 买药人信息 买药人编号INT NOT NULL PRIMARY KEY 药品编号INT NOT NULL FOREIGN KEY REFERENCES 药品信息 药品编号 药品数量INT 买药人姓名CHAR 20 买药人性别CHAR 2 DEFAULT 男 买药人年龄INT 买药人联系电话CHAR 20 3 23 2 数据初始化数据初始化 3 2 1 添加药品到表药品信息中添加药品到表药品信息中 INSERT 药品信息 VALUES 2001 珍视明滴眼液 眼科用药 250ml 1002 4 5 8 INSERT 药品信息 VALUES 2002 阿莫西林胶囊 消炎药 0 25克 粒 1001 4 5 8 INSERT 药品信息 VALUES 2003 健胃消食片 胃病用药 0 15mg 1001 3 5 INSERT 药品信息 VALUES 2004 维C银翘片 感冒用药类 0 15mg 1005 5 5 8 INSERT 药品信息 VALUES 2005 速效伤风胶囊 感冒用药类 0 15mg 1000 4 5 6 INSERT 药品信息 VALUES 2006 慢咽舒宁 咽喉口腔用药 0 15mg 1004 3 5 INSERT 药品信息 VALUES 2007 炎可宁片 消炎药 0 2mg 1003 7 8 3 2 2 添加添加制造商到表制作商信息中制造商到表制作商信息中 insert 制造商信息 values 1000 上海强生制药有限公司 上海 600 48414566 insert 制造商信息 values 1001 广州白云山制药股份有限公司 广东 014 04540560 insert 制造商信息 values 1002 江西珍视明药业有限公司 江西 045 44645646 insert 制造商信息 values 1003 北京同仁堂 北京 048 46504841 insert 制造商信息 values 1004 太极集团锦阳制药有限公司 北京 056 40641899 insert 制造商信息 values 1005 云南白药制药有限公司 云南 245 48197887 3 2 3 添加库存信息到表添加库存信息到表药品存储信息中药品存储信息中 INSERT 药品存储信息 VALUES 0001 2001 14000 INSERT 药品存储信息 VALUES 0001 2002 20000 INSERT 药品存储信息 VALUES 0001 2003 14300 INSERT 药品存储信息 VALUES 0002 2003 18740 INSERT 药品存储信息 VALUES 0003 2004 24300 INSERT 药品存储信息 VALUES 0003 2005 43500 INSERT 药品存储信息 VALUES 0004 2006 15200 INSERT 药品存储信息 VALUES 0004 2007 34300 3 2 4 添加买药人到表买药人信息中添加买药人到表买药人信息中 INSERT 买药人信息 VALUES 20101 2001 5 苏子威 女 50INSERT 买药人信息 VALUES 20102 2003 10 叶小四 女 70INSERT 买药人信息 VALUES 20103 2004 1 张三 男 20INSERT 买药人信息 VALUES 20104 2006 6 李四 男 30INSERT 买药人信息 VALUES 20105 2007 8 王五 女 18INSERT 买药人信息 VALUES 20106 2003 8 李刚 女 53 视图设计视图设计 函数 存储过程 用函数 存储过程 用 SQL 脚本语言实现 而且用截图脚本语言实现 而且用截图 展示结果 展示结果 至少设计一个存储过程 一个自定义函数 一个视图 至少设计一个存储过程 一个自定义函数 一个视图 一 视图一 视图 1 1 考考虑虑到到车车站站管管理理员员统统计计和和查查看看每每天天旅旅客客乘乘坐坐列列车车的的基基本本信信息息为方便为方便所所以以要要建建一一个个 视视图图 乘乘客客信信息息 create view 乘客信息 as select a orderNo a username a sex a age a idcardNo b trainNo from Userinfo a Orderinfo b where a orderNO b orderNo 如图 2 2 考虑到乘客要查看自己订的车票时 为方便所以需要建立一个视图 考虑到乘客要查看自己订的车票时 为方便所以需要建立一个视图 车票信息车票信息 create view 车票信息 as select b orderNo b trainNo b tickettype b fares b begintime b endtime a beginstation a endstation from Traininfo a Orderinfo b where a trainNo b trainNO 如图 3 3 考考虑虑到到方方便便后后台台管管理理人人员员查查看看列列车车 基基本本信信息息需需要要 建建立立一一个个 车车次次信信息息 视视图图 create view 车次信息 as select a ticket a trainNo b beginstation b endstation c fares from Standbyticket a Traininfo b Orderinfo c where a trainNo b trainNo and b trainNo c trainNo 如图 4 4 考虑用户和后台方便查看列车剩余车票信息 所以需要建立一个视图 考虑用户和后台方便查看列车剩余车票信息 所以需要建立一个视图 余票信息余票信息 create view 余票信息 as select a trainNo a tickettype a fares a ticketnum from standbyticket a 如图 二 存储过程 二 存储过程 1 创建一个存储过程 通过给定订单号 查询车次基本信息和用户信息 创建一个存储过程 通过给定订单号 查询车次基本信息和用户信息 create proc 查询指定订单号的车次和个人信息 orderNo char 10 as select a trainNo a tickettype a fares b userName b idcardNo from Orderinfo a Userinfo b where a orderNo orderNo and a orderNO b orderNo 测试脚本语句如下 exec 查询指定订单号的车次和个人信息 A0522 测试结果如图所示 2 创建一个存储过程 通过给定信息 将车次信息添加到订单信息表中 创建一个存储过程 通过给定信息 将车次信息添加到订单信息表中 create proc 添加订票信息 订票 varchar 40 as if 订票 成都到北京卧铺 insert into Orderinfo values 00001 K1111 19 00 00 23 00 00 卧铺 80元 测试脚本语句如下 exec 添加订票信息 成都到北京卧铺 测试结果如图所示 三 自定义函数 三 自定义函数 1 创建一个自定义函数 实现输入给定订单号 显示车次号 费用 Create function 查询车次费用 orderNo varchar 10 Returns table table trainNo varchar 20 null fares varchar 20 null As Begin Insert table Select trainNO fares from Orderinfo Where orderNo like orderNo return End 测试脚本语句如下 select from 查询车次费用 A0522 测试结果如图所示 2 创建一个自定义函数 实现输入车次号 显示车次的余票情况 或车次号无效则提示不 存在 CREATE FUNCTION 是否有余票 trainNo varchar 20 returns varchar 30 AS BEGIN DECLARE a varchar 20 select a ticketnum from Standbyticket where trainNo trainNo If exists select 1 from Standbyticket where trainNo trainNo if a 0 begin set a 无票 end else begin set a 有票 end else begin set a 不存在 end RETURN a END 测试脚本语句如下 select dbo 是否有余票 K1111 as 余票情况 select dbo 是否有余票 C1114 as 余票情况 select dbo 是否有余票 C111343434 as 余票情况 测试结果如图所示 四 触发器 四 触发器 1 drop trigger 订票触发器 插入数据即订票时修改车次余票数 go Create Trigger 订票触发器 on Orderinfo For Insert as declare a varchar 20 b char 10 select a trainNo b tickettype from inserted update Standbyticket set ticketnum ticketnum 1 where trainNo a and tickettype b go 2 删除订单表的一行数据后在余票信息表增加对应车次的余票数 Create Trigger 退票触发器 on Orderinfo For delete as declare a char 10 b char 10 select a orderNo b tickettype from deleted update Standbyticket set ticketnum ticketnum 1 where tickettype b and orderNo a go 4 结果数据处理结果数据处理 编写至少编写至少 5 个实用的查询语句 包含多表连接 聚个实用的查询语句 包含多表连接 聚 合函数的统计查询 排序等 要在查询中用到视图 自定义函数 合函数的统计查询 排序等 要在查询中用到视图 自定义函数 4 14 1 单表查询 写出每张表的查询结果 必须用单表查询 写出每张表的查询结果 必须用 T T SQLSQL 语句完成 而且用截图语句完成 而且用截图 展示结果 展示结果 4 1 1 表表 Userinfo 中查询演示中查询演示 select from Userinfo 4 1 2 表表Orderinfo中查询演示中查询演示 Select from Orderinfo 4 1 3 表表Traininfo中查询演示中查询演示 Select from Traininfo 4 1 4 表表Standbyticket查询演示查询演示 Select from Standbyticket 查询旅客年龄小于 20 岁的姓名 在视图中查询 SQL 语言 select userName from 乘客信息