数据库课程设计报告.doc

摘 要中国的领土面积约960 万平方公里居世界前列，人们在如此大的领土上出行可谓是一个社会性的难题。中国铁路线四通八达总长度可谓世界第一，一直以来中国铁路以安全、稳定、快速和较为便宜的票价，吸引了占世界三分之一的人口乘坐。但近年来，随着我国国民经济的飞速发展和人民生活水平的提高，铁路旅客运输面临着增加运能、提高卖票服务质量、提高竞争能力的迫切需要。基于网络自主的火车票售票管理系统替代传统的人工售票，实现自动化售票和对票务的自动化管理，极大的方便了乘客购票，也提高了铁路部门的管理效率和运营效率。由此可见，售票管理系统是解决当前问题的重要手段，是火车运输发展的必然趋势。 本论文围绕铁路运输售票的现状，引入火车票售票管理系统，并对该系统的开发及原理进行详细的介绍。 关键词火车票；售票系统；订票；管理系统 AbstractChinas territorial area of approximately 960 million square kilometers of the world,people in such a large territory trip is a social problem. China railway to extend in all directions，and its total length is the longest of the world . All along,Chinas railway provide a stable ,safe and fast service ,which affected many peoples what about the traffic tools on the decision. But in recent years, with the rapid development of Chinas national economy and the improvement of living standards, railway passengers are confronted with the urgent request of increasing transportation capacity growing ticket service quality, and the improvement of competitiveness. Train ticket management system which based on network replaced the traditional ticket-selling system. This new system to convenient passengers booking ticket, which also improved the management and operating of railway department more efficiency. Thus, ticket management system is a important solution to the current issues of the development of the train transport. This paper illustrated the cumanagement system. Which specia of the whole system. Key wordsticket; ticketing system; booking; management system 目 录1.系统功能需求分析12.数据库设计分析23.数据库整体逻辑结构设计34.数据库实现54.1建立数据库和表54.2建立视图104.3建立存储过程234.4建立触发器235.课程设计总结24参考文献25谢辞2625火车票售票系统数据库的设计与实现Ticket Selling System Design and Implementation of the Database数学与信息工程学院 计算机科学与技术（师范）专业徐丹丹指导教师：李振龙1. 系统功能需求分析中国是一个人口大国，人们出行最实惠最常用的就是乘火车。火车票查询系统可以使出行更方便。火车票查询是比较实用的，对铁路部门卖票及票务管理有很大帮组，也能方便乘客买票。随着我国市场经济的快速发展和信息化水平的不断提高，如何利用先进的管理手段，提高火车售票管理系统的水平，是当今社会所面临的一个课题。提高企业的管理水平，必须全方位地提高企业的管理意识。只有高标准、高质量的管理才能满足铁路的发展需求。面对信息时代的挑战，利用高科技手段来提火车售票管理系统无疑是一条行之有效的途径。在某种意义上，信息与科技在企业管理与现代化建设中显现出越来越重要的地位。火车售票管理系统方面的信息化与科学化，已成为现代化生活水平步入高台阶的重要标志。 火车票查询系统有一个管理员和若干个客户，管理员负责火车票相关信息的实时更新以及密码修改，负责维护数据，此管理员可以导入新的数据，更新数据.系统数据在软件启动时自动连接本机器数据库。（1）此系统设一个系统管理员，他需要密码才可以登录，登录后可以对火车票的信息进行管理，可以添加新的线路，删除已有的线路，进行密码修改，但是火车线路常常是固定的，因此操作也不是很平凡。（2）系统还有一般的用户登录登陆，需要注册。用户等路后可以查询车次的信息，信息包括火车票量，火车票价，各到站时间，各站出发时间，里程，运行时间，站站查询，中转查询，密码修改等。数据字典用户：用户名，密码，生日，真实姓名，身份证号码，管理员标志位 车票：车票编号，始发站，终点站，日期，发车时间，车次，车厢，座位号，是否卧铺，是否有票标志位 2. 数据库设计分析针对以上的功能分析，对数据库的设计做了以下分析。考虑到火车票售票的特殊要求，数据库的设计应该包含以下信息：列车信息：列车车次，列车的线路经过的各站及到达各站的时间，里程，站次，站名，列车备注（临时信息）。列车系统信息：列车编号，硬座，硬卧票价及相应票量，站点备注。此系统设一个系统管理员，他需要密码才可以登录，登录后可以对火车票的信息进行管理，可以添加新的线路，删除已有的线路，进行密码修改，但是火车线路常常是固定的，因此操作也不是很平凡。系统还有一般的用户登录登陆，需要注册。用户等路后可以查询车次的信息，信息包括火车票量，火车票价，各到站时间，各站出发时间，里程，运行时间，站站查询，中转查询，密码修改等。整体E-R图如下：图1 火车票售票系统数据库 E-R 图 3. 数据库整体逻辑结构设计数据库的设计分析所得的 E-R 模型是对用户需求的一种抽象的表达形式，它独立于任何一种具体的数据模型，因而也不能为任何一个具体的 DBMS 所支持。为了能够建立起最终的物理系统，还需要将概念结构进一步转化为某一 DBMS 所支持的数据模型，然后根据逻辑设计的准则、数据的语义约束、规范化理论等对数据模型进行适当的调整和优化，形成合理的全局逻辑结构，并设计出用户子模式。这就是数据库逻辑设计所要完成的任务。 数据库逻辑结构的设计分为两个步骤：首先将概念设计所得的 E-R 图转换为关系模型；然后对关系模型进行优化。 在火车票售票管理系统当中包括了以上几个ER模型向关系模型的转换： 用户信息表（用户名，密码，生日，真实姓名，身份证号码，管理员标志位） 车票信息表（车票编号，始发站，终点站，日期，发车时间，车次，车厢，座位号，是否卧铺，是否有票标志位） 购票车信息表（车票编号，用户名） 下面是将各个实体和联系转化为相应的二维表即关系模式，指定各个关系的主关键字和外部关键字，并对各个关系的约束加以限定：用户信息表，如表1所示。车票信息表，如表2所示。购票车信息表，如表3所示。各表之间的关系表如图2所示。表1 用户信息表(user)编号 字段名称 字段类型 字段说明 1userName varchar用户表主键,用户名 2 passWord varchar 密码 3 birthDay varchar生日 4trunName varchar 真实姓名 5 card varchar 身份证号码6 sign int 管理员标志位 表2 车票信息表(ticket)编号 字段名称字段类型 字段说明 1idint表主键,车票编号2 startvarchar始发站 3destination varchar终点站4data varchar 日期 5timevarchar 发车时间 6 trainNum varchar车次7 trainBox int 车厢 8seat int 座位号 9 sleep varchar 是否卧铺 10 signint 是否有票标志位 表3 购票车信息表(relation)编号 字段名称字段类型字段说明1 userNamevarchar用户名2 idint 表主键，车票编号 图2 各表之间的关系表4. 数据库实现4.1 建立数据库和表4.1.1 建立数据库“火车票售票系统”USE masterGO/* Object: Database 火车票售票系统 Script Date: 12/15/2011 20:07:41 */CREATE DATABASE 火车票售票系统 ON PRIMARY ( NAME = N火车票售票系统, FILENAME = NE:作业数据库报告火车票售票系统.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N火车票售票系统_log, FILENAME = NE:作业数据库报告火车票售票系统_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE 火车票售票系统 SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled)beginEXEC 火车票售票系统.dbo.sp_fulltext_database action = enableendGOALTER DATABASE 火车票售票系统 SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE 火车票售票系统 SET ANSI_NULLS OFF GOALTER DATABASE 火车票售票系统 SET ANSI_PADDING OFF GOALTER DATABASE 火车票售票系统 SET ANSI_WARNINGS OFF GOALTER DATABASE 火车票售票系统 SET ARITHABORT OFF GOALTER DATABASE 火车票售票系统 SET AUTO_CLOSE OFF GOALTER DATABASE 火车票售票系统 SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE 火车票售票系统 SET AUTO_SHRINK OFF GOALTER DATABASE 火车票售票系统 SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE 火车票售票系统 SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE 火车票售票系统 SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE 火车票售票系统 SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE 火车票售票系统 SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE 火车票售票系统 SET QUOTED_IDENTIFIER OFF GOALTER DATABASE 火车票售票系统 SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE 火车票售票系统 SET DISABLE_BROKER GOALTER DATABASE 火车票售票系统 SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE 火车票售票系统 SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE 火车票售票系统 SET TRUSTWORTHY OFF GOALTER DATABASE 火车票售票系统 SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE 火车票售票系统 SET PARAMETERIZATION SIMPLE GOALTER DATABASE 火车票售票系统 SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE 火车票售票系统 SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE 火车票售票系统 SET READ_WRITE GOALTER DATABASE 火车票售票系统 SET RECOVERY FULL GOALTER DATABASE 火车票售票系统 SET MULTI_USER GOALTER DATABASE 火车票售票系统 SET PAGE_VERIFY CHECKSUM GOALTER DATABASE 火车票售票系统 SET DB_CHAINING OFF GO4.1.2 建立表“user”USE 火车票售票系统GO/* Object: Table dbo.user Script Date: 12/15/2011 20:09:50 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.user(userName varchar(50) NOT NULL,passWord varchar(50) NOT NULL,birthDay varchar(50) NOT NULL,trunName varchar(50) NOT NULL,card varchar(50) NOT NULL,sign int NOT NULL, CONSTRAINT PK_user PRIMARY KEY CLUSTERED (userName ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO4.1.3 建立表“ticket”USE 火车票售票系统GO/* Object: Table dbo.ticket Script Date: 12/15/2011 20:10:43 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.ticket(id int NOT NULL,start varchar(50) NOT NULL,destination varchar(50) NOT NULL,data varchar(50) NOT NULL,time varchar(50) NOT NULL,trainNum varchar(50) NOT NULL,trainBox int NOT NULL,seat int NOT NULL,sleep varchar(50) NOT NULL,sign int NOT NULL, CONSTRAINT PK_ticket PRIMARY KEY CLUSTERED (id ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO4.1.4 建立表“relation”USE 火车票售票系统GO/* Object: Table dbo.relation Script Date: 12/15/2011 20:11:27 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.relation(userName varchar(50) NOT NULL,id int NOT NULL, CONSTRAINT PK_relation PRIMARY KEY CLUSTERED (id ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO4.2 建立视图4.2.1 建立“用户信息”视图SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW dbo.用户信息ASSELECT userName AS 用户名, passWord AS 密码, birthDay AS 生日, trunName AS 真实姓名, card AS 身份证号码, sign AS 管理员标志位FROM dbo.userGOEXEC sys.sp_addextendedproperty name=NMS_DiagramPane1, value=N0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = (H (140 420 220 3) ) End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = (H (1 50 4 25 3) End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = (H (1 50 2 25 3) End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = (H (4 30 2 40 3) End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = (H (1 56 3) End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = (H (2 66 3) End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = (H (4 50 3) End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = (V (3) End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = (H (156 418 2) ) End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = (H (1 75 4) End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = (H (166 2) ) End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = (H (4 60 2) End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = (H (1) ) End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = (V (4) End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = (V (2) End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = user Begin Extent = Top = 6 Left = 38 Bottom = 118 Right = 170 End DisplayFlags = 344 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End EndEnd , level0type=NSCHEMA,level0name=Ndbo, level1type=NVIEW,level1name=N用户信息GOEXEC sys.sp_addextendedproperty name=NMS_DiagramPaneCount, value=1 , level0type=NSCHEMA,level0name=Ndbo, level1type=NVIEW,level1name=N用户信息GO4.2.2 建立“车票信息”视图SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW dbo.车票信息ASSELECT id AS 车票编号, start AS 始发站, destination AS 终点站, data AS 日期, time AS 发车时间, trainNum AS 车次, trainBox AS 车厢, seat AS 座位号, sleep AS 是否卧铺, sign AS 是否有票标志FROM dbo.ticketGOEXEC sys.sp_addextendedproperty name=NMS_DiagramPane1, value=N0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = (H (140 420 220 3) ) End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = (H (1 50 4 25 3) End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = (H (1 50 2 25 3) End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = (H (4 30 2 40 3) End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = (H (1 56 3) End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = (H (2 66 3) End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = (H (4 50 3) End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = (V (3) End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = (H (156 418 2) ) End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = (H (1 75 4) End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = (H (166 2) ) End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = (H (4 60 2) End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = (H (1) ) End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = (V (4) End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = (V (2) End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = ticket Begin Extent = Top = 6 Left = 38 Bottom = 118 Right = 170 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End EndEnd , level0type=NSCHEMA,level0name=Ndbo, level1type=NVIEW,level1name=N车票信息GOEXEC sys.sp_addextendedproperty name=NMS_DiagramPaneCount, value=1 , level0type=NSCHEMA,level0name=Ndbo, level1type=NVIEW,level1name=N车票信息GO4.2.3 建立“购票车信息”视图SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW dbo.购票车信息ASSELECT userName AS 用户名, id AS 车票编号FROM dbo.relationGOEXEC sys.sp_addextendedproperty name=NMS_DiagramPane1, value=N0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = (H (140 420 220 3) ) End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = (H (1 50 4 25 3) End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = (H (1 50 2 25 3) End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = (H (4 30 2 40 3) End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = (H (1 56 3) End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = (H (2 66 3) End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = (H (4 50 3) End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = (V (3) End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = (H (156 418 2) ) End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = (H (1 75 4) End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = (H (166 2) ) End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = (H (4 60 2) End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = (H (1) ) End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = (V (4) End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = (V (2) End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = relation Begin Extent = Top = 6 Left = 38 Bottom = 88 Right = 170 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOr