基于WEB的航班查询订票系统数据库设计说明书.doc

1 引言 .21.1 编写目的.21.2 背景.21.3 参考资料.22 外部设计 .32.1 数据库生成脚本.32.2 使用它的程序模块.42.2.1、订票子模块.42.2.2、航班管理子模块.52.2.3、折扣管理系统.52.2.4、机票管理系统.52.2.5 公用模块.52.3 约定.62.4 表间关系.83 安全保密设计 .83.1 细化权限控制.83.2 为标准登陆提供强制口令政策.81基于WEB的航班查询和订票系统数据库设计说明书1 引言1.1 编写目的数据库设计是系统设计的重中之重，它维护着系统赖以运行的数据。编写该数据库设计说明书旨在规范程序员数据库设计，便于两个本来相关的模块能独立设计。在该教师授课评价系统中，主要分为两个模块，数据库的管理者模块和数据库的客户模块，管理者和客户都要完成数据库的查询和更新，所以若先搭好数据库这个基础平台，就能够并发的开发这两个模块，提高实现系统的效率。1.2 背景企业今天面临着许多数据方面的挑战；例如，需要根据数据做出更快、更多的决策，数据安全性，完整性等等。SQL Server 给企业数据和分析应用程序带来增强的安全性、可伸缩性和可用性，使得它们更易于创建、部署和管理。它基于 SQL Server 的强大功能之上，提供了一个完整的数据管理和分析解决方案，它将会给不同规模的组织带来帮助：构建、部署和管理企业应用程序，使其更加安全、伸缩性更强和更可靠。能够在多个平台、应用程序和设备之间共享数据，更易于连接内部和外部系统。在不牺牲性能、可用性、可伸缩性或安全性的前提下有效控制成本。1.3 参考资料【1】 萨师煊 王珊。 数据库系统概论。第三版。北京：高等教育出版社，2000：3242【2】 SQL Server 中文文档。22 外部设计2.1 数据库生成脚本用户信息 Class：CREATE TABLE dbo.Class(id int IDENTITY(1,1) NOT NULL,departID int NULL,classNum nvarchar(50) NULL DEFAULT (),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 PRIMARYGOALTER TABLE dbo.Class WITH CHECK ADD FOREIGN KEY(departID)REFERENCES dbo.Department (id)用户信息 Class：CREATE TABLE dbo.ClassAppraise(id int IDENTITY(1,1) NOT NULL,classNum nvarchar(50) NOT NULL,classID int NULL,termID int NULL,teacherID int NULL,teacherName nvarchar(50) NULL,departCourseID int NULL,departCourseName nvarchar(50) NULL,mark int NULL,departID nchar(10) NULL,username_all varchar(50) NULL, CONSTRAINT PK_ClassAppraise_14B10FFA 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 OFFGOALTER TABLE dbo.ClassAppraise WITH CHECK ADD CONSTRAINT FK_ClassAppraise_Class FOREIGN KEY(classID)REFERENCES dbo.Class (id)GOALTER TABLE dbo.ClassAppraise CHECK CONSTRAINT FK_ClassAppraise_ClassGOALTER TABLE dbo.ClassAppraise WITH CHECK ADD CONSTRAINT FK_ClassAppraise_Teacher FOREIGN KEY(teacherID)REFERENCES dbo.Teacher (id)GOALTER TABLE dbo.ClassAppraise CHECK CONSTRAINT FK_ClassAppraise_TeacherGOALTER TABLE dbo.ClassAppraise WITH CHECK ADD CONSTRAINT FK_ClassAppraise_Term FOREIGN KEY(termID)REFERENCES dbo.Term (id)GOALTER TABLE dbo.ClassAppraise CHECK CONSTRAINT FK_ClassAppraise_Term用户信息 Class：CREATE TABLE dbo.ClassCourse(id bigint IDENTITY(1,1) NOT NULL,classID int NULL,teacherID int NULL,termID int NULL,departID int NULL,departCourseID int NULL,course nvarchar(50) NULL, CONSTRAINT PK_ClassCourse_22AA2996 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 PRIMARYGOALTER TABLE dbo.ClassCourse WITH CHECK ADD CONSTRAINT FK_ClassCourse_Class FOREIGN KEY(classID)REFERENCES dbo.Class (id)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.ClassCourse CHECK CONSTRAINT FK_ClassCourse_ClassGOALTER TABLE dbo.ClassCourse WITH CHECK ADD CONSTRAINT FK_ClassCourse_Department FOREIGN KEY(departID)REFERENCES dbo.Department (id)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.ClassCourse CHECK CONSTRAINT FK_ClassCourse_DepartmentGOALTER TABLE dbo.ClassCourse WITH CHECK ADD CONSTRAINT FK_ClassCourse_Teacher FOREIGN KEY(teacherID)REFERENCES dbo.Teacher (id)GOALTER TABLE dbo.ClassCourse CHECK CONSTRAINT FK_ClassCourse_TeacherGOALTER TABLE dbo.ClassCourse WITH CHECK ADD CONSTRAINT FK_ClassCourse_Term FOREIGN KEY(termID)REFERENCES dbo.Term (id)GOALTER TABLE dbo.ClassCourse CHECK CONSTRAINT FK_ClassCourse_Term用户信息 Class：CREATE TABLE dbo.ClassPassword(classID int NOT NULL,password nvarchar(100) NOT NULL) ON PRIMARY用户信息 Class：CREATE TABLE dbo.DepartCourse(id int IDENTITY(1,1) NOT NULL,departID int NULL,dcourse nvarchar(50) NULL,explanFashionID int NULL, CONSTRAINT PK_DepartCourse 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 PRIMARYGOALTER TABLE dbo.DepartCourse WITH CHECK ADD CONSTRAINT fk_DepartCourse_Department FOREIGN KEY(departID)REFERENCES dbo.Department (id)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.DepartCourse CHECK CONSTRAINT fk_DepartCourse_Department用户信息 Class：CREATE TABLE dbo.Department(id int IDENTITY(1,1) NOT NULL,departName nvarchar(50) NULL DEFAULT (),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 PRIMARY用户信息 Class：CREATE TABLE dbo.especialCourse(id int IDENTITY(1,1) NOT NULL,departCourseID int NOT NULL,teacherID int NOT NULL,courseName nvarchar(50) NOT NULL,explanFashionID int NULL,termID nchar(10) NULL, CONSTRAINT PK_especialCourse_02925FBF 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 PRIMARYGOALTER TABLE dbo.especialCourse WITH CHECK ADD CONSTRAINT FK_especialCourse_DepartCourse FOREIGN KEY(departCourseID)REFERENCES dbo.DepartCourse (id)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.especialCourse CHECK CONSTRAINT FK_especialCourse_DepartCourse用户信息 Class：CREATE TABLE dbo.ExpertAppraise(id int IDENTITY(1000,1) NOT FOR REPLICATION NOT NULL,teacherID int NULL,termID int NULL,mark int NULL CONSTRAINT DF_ExpertAppr_mark_32E0915F DEFAULT (0),departID int NULL,appraiseTeacherID int NULL, CONSTRAINT PK_ExpertAppraise 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 PRIMARYGOALTER TABLE dbo.ExpertAppraise WITH CHECK ADD CONSTRAINT FK_ExpertApp_teach_30F848ED FOREIGN KEY(teacherID)REFERENCES dbo.Teacher (id)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.ExpertAppraise CHECK CONSTRAINT FK_ExpertApp_teach_30F848EDGOALTER TABLE dbo.ExpertAppraise WITH CHECK ADD CONSTRAINT FK_ExpertApp_termI_31EC6D26 FOREIGN KEY(termID)REFERENCES dbo.Term (id)GOALTER TABLE dbo.ExpertAppraise CHECK CONSTRAINT FK_ExpertApp_termI_31EC6D26用户信息 Class：CREATE TABLE dbo.ExpertAppraised(teacherID int NOT NULL,termID int NOT NULL,appraiseTeacherID int NOT NULL,departID int NULL, CONSTRAINT PK_ExpertAppraised PRIMARY KEY CLUSTERED (teacherID ASC,termID ASC,appraiseTeacherID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.ExpertAppraised WITH CHECK ADD CONSTRAINT FK_ExpertApp_appra_38996AB5 FOREIGN KEY(appraiseTeacherID)REFERENCES dbo.Teacher (id)GOALTER TABLE dbo.ExpertAppraised CHECK CONSTRAINT FK_ExpertApp_appra_38996AB5GOALTER TABLE dbo.ExpertAppraised WITH CHECK ADD CONSTRAINT FK_ExpertApp_teach_36B12243 FOREIGN KEY(teacherID)REFERENCES dbo.Teacher (id)GOALTER TABLE dbo.ExpertAppraised CHECK CONSTRAINT FK_ExpertApp_teach_36B12243GOALTER TABLE dbo.ExpertAppraised WITH CHECK ADD CONSTRAINT FK_ExpertApp_termI_37A5467C FOREIGN KEY(termID)REFERENCES dbo.Term (id)GOALTER TABLE dbo.ExpertAppraised CHECK CONSTRAINT FK_ExpertApp_termI_37A5467C用户信息 Class：CREATE TABLE dbo.explanFashion(id int IDENTITY(1,1) NOT NULL,symbol int NOT NULL,fashion nvarchar(50) NOT NULL,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,UNIQUE NONCLUSTERED (fashion ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY,UNIQUE NONCLUSTERED (symbol ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY用户信息 Class：CREATE TABLE dbo.KeySet(dept nvarchar(100) NULL,child nvarchar(100) NULL,value nvarchar(100) NULL) ON PRIMARY用户信息 Class：CREATE TABLE dbo.password(username varchar(20) NOT NULL,password varchar(20) NOT NULL,departID int NOT NULL,popedom int NULL,teacherID int NULL,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 OFF用户信息 Class：CREATE TABLE visionalCourse(id int IDENTITY(1,1) NOT NULL,sessionid varchar(50) NOT NULL,teacherID int NULL,departCourseID int NOT NULL,teacherName nvarchar(50) NULL,departCourseName nvarchar(50) NULL,termID int NULL, CONSTRAINT PK_provisionalCours_7FB5F314 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 OFF用户信息 Class：CREATE TABLE visionalExpertAppraise(id int IDENTITY(1,1) NOT NULL,teacherID int NULL,termID int NULL,mark int NULL,sessionid nvarchar(50) NULL,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 PRIMARY用户信息 Class：CREATE TABLE dbo.Teacher(id int IDENTITY(1,1) NOT NULL,departID int NULL,teacherNum nvarchar(50) NULL CONSTRAINT DF_Teacher_teacher_1DE57479 DEFAULT (),teacherName nvarchar(50) NULL CONSTRAINT DF_Teacher_teacher_1ED998B2 DEFAULT (),dimission bit NULL CONSTRAINT DF_Teacher_dimission DEFAULT (0),workRoom nvarchar(50) NULL, CONSTRAINT PK_Teacher_1CF15040 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 PRIMARYGOALTER TABLE dbo.Teacher WITH CHECK ADD CONSTRAINT FK_Teacher_enableA_20C1E124 FOREIGN KEY(departID)REFERENCES dbo.Department (id)GOALTER TABLE dbo.Teacher CHECK CONSTRAINT FK_Teacher_enableA_20C1E124用户信息 Class：CREATE TABLE dbo.TeacherAvg(zzjID int IDENTITY(1,1) NOT NULL,teacherNum nvarchar(50) NULL,XiangDuiMark numeric(18, 4) NULL,termid int NULL,username_all varchar(50) NULL,departCourseID int NULL) ON PRIMARYGOSET ANSI_PADDING OFF用户信息 Class：CREATE TABLE dbo.Term(id int IDENTITY(1,1) NOT NULL,termName nvarchar(50) NULL DEFAULT (),isCurrAppraise bit NULL DEFAULT (0),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 PRIMARY用户信息 Class：CREATE TABLE dbo.TermClassAppraised(id bigint IDENTITY(1,1) NOT NULL,classID int NULL,termID int NULL,appraisedCount int NULL,clockAppraiseFirstTime datetime NULL,departID int NULL,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 PRIMARYGOALTER TABLE dbo.TermClassAppraised WITH CHECK ADD FOREIGN KEY(classID)REFERENCES dbo.Class (id)GOALTER TABLE dbo.TermClassAppraised WITH CHECK ADD FOREIGN KEY(termID)REFERENCES dbo.Term (id)用户信息 Class：CREATE TABLE dbo.zongXiangDuiMark(id int IDENTITY(1,1) NOT NULL,teacherNum nvarchar(50) NULL,teacherName nvarchar(50) NULL,departID int NULL,termID int NULL,zongXiangDui numeric(18, 4) NULL) ON PRIMARY用户信息 Class：CREATE TABLE dbo.zxuser(username varchar(20) NOT NULL,departID int NOT NULL,popedom int NOT NULL,sessionid varchar(50) NOT NULL, CONSTRAINT PK_zxuser_79FD19BE 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 OFF2.2 使用它的程序模块2.2.1、订票子模块查询：客户给出搜索条件，系统将航班信息表和折扣信息表中符合客户搜索条件的所有记录找出；客户还可以查询自己的订单信息。增加：客户可以向客户信息表中添加记录。修改：客户可以修改自己在客户信息表中的记录。定购：使用者给出定购信息和定购策略，定购信息将保留为以后定购成功和分析使用，定购成功以后，则向订单信息表中添加新记录，当票送出以后，则在订单信息表中修改相应记录。42.2.2、航班管理子模块查询：可以查询航班信息，根据起点和终点，或者航班号查询。增加：当新增航班时，管理者可以增加航班记录。修改：当航班信息有所改变时，管理者可以修改航班的记录。2.2.3、折扣管理系统查询：可以查询对应日期下折扣信息。修改：可以修改折扣信息。2.2.4、机票管理系统查询：可以查询对应日期下相应航班的座位信息。修改：当订票和退票时，可以修改座位信息。2.2.5 公用模块采用 Hibernate 实现持久化底层操作。root jdbc:SQL Server:/localhost:3306/netticket net.sf.hibernate.dialect.SQL ServerDialectaiya1314com.SQL Server.jdbc.Driver以上为配置文件，它将各个模型（Flight、Dayflight、Ticket、Customer 和 Order）映射5到相应文件。同时也配置了数据库连接的各种参数，如连接名（connetion.username）、连接url、数据库相关方言（Dialect）、用户验证信息和数据库 Driver 等等，隐藏了一般的 JDBC数据库连接操作。.hbm.xml 中定义了模型到数据库记录列之间的对应关系。class HibernateSessionFactory 封装了 Session 工厂，由它负责 session 的打开和关闭。如下为该部分的简约表示：public class Hibernate