网上火车票订票系统的数据库设计doc.doc

网上火车票订票系统的数据库设计与实现班级：10级工程管理系组成员：2班 李霞 30102092582班 陆静 30102092611班 孙隆祥 3010209233系统名称：网上火车票订票系统一、系统需求(孙隆祥)A. 该“网上订票系统”只是对数据库应用技术的一个样本数据库的实例，重在对数据库一些方法的熟悉与掌握，其中包括从数据库的概念模型到ER图的绘制，再到数据库模式的建立（即为数据库及其基本表的建立），而后数据的录入（在此从略），最后完成一些简单的关系运算表达式的表达和相关的Select 查询语句的使用。B. 系统结构简述：本系统包括六个实体：客户、订票单、退票单、车票、票存系统实体之间的关系转换为其他一个表：记录（注：车票和票存系统之间为多对多的关系）C. 该数据库要进行如下数据处理1）要求查询客户的所有信息：输出：身份证号，姓名，用户名，联系电话，银行卡类型，银行卡号，籍贯，类型2）要求查询车票的所有信息：输出：车次，出发站，目的站，座位类型，座位号，车票价格，发车时间，到站时间3） 要求查询某一客户订票单的信息： 输入：姓名输出：订票单号，订票时间，乘车日期，订票数量4）要求查询客户所订购的车票信息输入：姓名输出：车次，出发站，目的站，发车时间，到站时间，车票价格5）客户要查询自己的退票信息输入：姓名输出：退票单号，退票时间，退票数量，手续费6）查询某一票存系统的记录信息输入：票存系统编号输出：记录数量，记录时间，出入账金额，出入账类型7）插入数据输入：要插入数据的各个属性8）修改某一客户的联系电话输入：要修改客户的身份证号，新的电话二、ER图(陆静)客户订票订票单退票单转换1退票车票记录票存系统n1n11mn记录时间出入账金额出入账类型记录数量1用户名类型身份证号#银行卡类型籍贯银行卡号联系电话订票时间订票数量订票单号#乘车日期退票时间退票单号#退票数量手续费票存系统编号#票存系统地址发车时间到站时间类型车票价格车票编号#座位号车次出发站目的地座位类型三、关系模式(李霞，陆静)系统中实体有：客户，订票单，改签单，退票单，车票，票存系统关系模式：客户（身份证号，姓名，用户名，联系电话，银行卡类型，银行卡号，籍贯，类型）说明：属性中“身份证号”是主键，“类型”的取值为（学生票，普通票）订票单（订票单号，订票时间，乘车日期，订票数量，身份证号）说明：属性中 “乘车日期”的格式为：_ _ _ _ - _ _ - _ _（_ 代表数字）退票单（退票单号，退票时间，退票数量，手续费，车票编号）车票（车票编号，车次，出发站，目的站，座位类型，座位号，车票价格，发车时间，到站时间，类型，订票单号）说明：属性中“座位类型”取值为（软卧，硬卧，软座，硬座，无座），“类型”的取值为（学生票，普通票）票存系统（票存系统编号，票存系统地址）记录（车票编号，票存系统编号，记录日期，记录时间，出入账金额，出入账类型，记录数量）说明：属性中“票存系统编号”和“票存系统地址”是主键，也是外键。四、 物理设计（李霞）表1.客户表结构字段名类型特殊属性身份证号char(18)主健姓名char(10)not null联系电话char(10)用户名char(10)not null银行卡类型char(10)not null银行卡号char(20)not null籍贯char(10)类型char(10)表2.订票单表结构字段名类型特殊属性订票单号char(10)主健订票时间char(10)not null乘车日期char(10)not null订票数量intnot null身份证号char(18)not null表3.退票单表结构字段名类型特殊属性退票单号char(10)主健退票时间char(10)not null退票数量intnot null手续费char(6)not null表4车票表结构字段名类型特殊属性车票编号char(10)主健车次char(10)not null出发站char(10)not null目的站char(10)not null座位类型char(10)not null座位号char(10)not null车票价格char(10)not null发车时间char(10)not null到站时间char(10)not null类型char(10)订票单号char(10)表5.票存系统表结构字段名类型特殊属性票存系统编号char(10)主健票存系统地址char(10)not null表6记录表结构字段名类型特殊属性车票编号char(10)主健票存系统编号char(10)not null记录日期char(10)not null记录时间char(10)not null出入账金额char(6)not null出入账类型char(10)not null记录数量intnot null五、系统实现（陆静，李霞，孙隆祥）1、数据库及其基本表的建立数据库的建立用企业管理器，基本表的建立用TSQL语言：数据库名称为：TO _System建立数据库的TSQL代码如下：-创建数据库Create database to_systemOn (name=to_system_data,filename=e:llsto_system_data.mdf,size=4,maxsize=10,filegrowth=1)log on(name=sm_system_log,filename=e:llsto_system_log.ldf)-建立基本表的TSQL代码Use to_Systemcreate table 客户(身份证号 char(18) primary key,姓名 char(10) not null,联系电话 char(10),用户名 char(10) not null,银行卡类型 char(10) not null,银行卡号 char(20) not null,籍贯 char(10),类型 char(10),check (类型 in (学生票,普通票)create table 订票单(订票单号 char(10) primary key,订票时间 char(10) not null,乘车日期 char(10) not null,订票数量 int not null,身份证号 char(18) not null,foreign key(身份证号) references 客户(身份证号) create table 车票(车票编号 char(10) primary key,车次 char(10) not null,出发站 char(10) not null,目的站 char(10) not null,座位类型 char(10) not null,座位号 char(10) not null,车票价格 int not null,发车时间 char(20) not null,到站时间 char(20) not null,类型 char(10),订票单号 char(10),foreign key(订票单号) references 订票单(订票单号),check (座位类型 in(硬座,硬卧,软卧,软座,无座),check (类型 in (学生票,普通票)create table 退票单(退票单号 char(10) primary key,车票编号 char(10) not null,退票时间 char(10) not null, 退票数量 int not null,手续费 char(6) not null,foreign key(车票编号) references 车票(车票编号)create table 票存系统(票存系统编号 char(10) primary key,票存系统地址 char(10) not null)create table 记录(车票编号 char(10),票存系统编号 char(10),记录日期 char(10) not null,记录时间 char(10) not null,出入账金额 char(6) not null,出入账类型 char(10) not null,记录数量 int not null,primary key(车票编号, 票存系统编号),foreign key(车票编号) references 车票(车票编号),foreign key(票存系统编号) references 票存系统(票存系统编号)输入数据：表一、客户表Insert into 客户values李丽,月亮建设银行,4728472291872783297,河北,学生票)Insert into 客户 values李月,小星星交通银行,3572364838493728274,河南,学生票)Insert into 客户 values李美,小太阳中国银行,3572364838493728275,广东,普通票)Insert into 客户 values张三,小草工商银行,3572364838493726382,浙江,普通票)Insert into 客户 values张思,凤姐建设银行,3572364838493737281,河南,学生票)Insert into 客户 values张武,大树工商银行,3572364838493724728,湖南,普通票)表二、订票单Insert into订票单values(E123456789,2012-5-20,2012-6-26,1,Insert into订票单values(E123456788,2012-5-20,2012-6-27,1,Insert into订票单values(E123456787,2012-5-21,2012-6-26,1,Insert into订票单values(E123456786,2012-5-21,2012-6-27,1,Insert into订票单values(E123456785,2012-5-22,2012-6-28,1,Insert into订票单values(E123456784,2012-5-23,2012-6-26,1, 3572364838493724728)表三、车票Insert into车票values(123456789,K123,天津,河北,硬座,01,50,00：00,01：00,学生票, E123456789)Insert into车票values(123456788,K234,天津,河南,无座,02,60,01：00,02：00,学生票, E123456788)Insert into车票values(123456787,K345,北京,广东,硬卧,03,70,02：00,03：00,普通票, E123456787)Insert into车票values(123456786,1111,山西,浙江,软卧,04,80,03：00,04：00, 普通票, E123456786)Insert into车票values(123456785,T123,北京,河南,硬座,05,90,04：00,05：00,学生票, E123456785)Insert into车票values(123456784,K123,天津,湖南,五座,06,100,05：00,06：00, 普通票, E123456784)表四、退票单Insert into退票单values(D123456789,2012-5-21,1,5, 123456789)Insert into退票单values(D123456788,2012-5-21,1,10, 123456788)Insert into退票单values(D123456787,2012-5-22,1,5, 123456787)表五、票存系统Insert into票存系统values(000000001,地址一)Insert into票存系统values(000000002,地址二)Insert into票存系统values(000000003,地址三)Insert into票存系统values(000000004,地址四)Insert into票存系统values(000000005,地址五)Insert into票存系统values(000000006,地址六)表六、记录Insert into记录 values(123456786, 000000001,2012-5-21,08：00,80,网银,1)Insert into记录 values(123456785, 000000002,2012-5-22,09：00,90,网银,1)Insert into记录 values(123456784, 000000003,2012-5-23,10：00,100,网银,1)2、创建索引Create index index_id on 客户（身份证号）Create index index_订票 on 订票单（订票单号）Create index index_票号 on 车票（车票编号）Create index index_退票 on 退票单（车票编号,退票单号）Create index index_记录 on 记录（车票编号）3、创建视图-创建视图1Create view 客户信息AsSelect 姓名,联系电话,银行卡类型,银行卡号,籍贯,类型From 客户sp_helptext 客户信息select * from 客户信息-创建视图2Create view 客户所订购的车票AsSelect 姓名,客户.身份证号,订票单.订票单号,车次,座位号,车票价格,发车时间,到站时间From 车票 inner join 订票单 on 车票.订票单号=订票单.订票单号 inner join 客户 on 客户.身份证号=订票单.身份证号select * from 客户所订购的车票-创建视图3Create view 李美退的车票信息AsSelect 手续费,车次,车票价格,发车时间,到站时间From 退票单 inner join 车票 on 车票.车票编号=退票单.车票编号 inner join 订票单 on 订票单.订票单号=车票.订票单号 inner join 客户 on 客户.身份证号=订票单.身份证号Where 姓名=李美select * from 李美退的车票信息-创建视图4Create view 记录表asSelect 车票编号,记录日期,记录时间,出入账金额 From 记录-创建视图5 create view 票存信息as select 车票.车票编号,票存系统.票存系统编号,记录时间from 车票,记录,票存系统where 车票.车票编号=记录.车票编号 and 记录.票存系统编号=票存系统.票存系统编号select * from 票存信息4、创建存储过程1、要求查询客户的所有信息(1)知道客户姓名Create procedure 客户信息客户姓名 char(10)AsSelect身份证号，姓名，用户名，联系电话，银行卡类型，银行卡号，籍贯，类型From 客户Where 姓名=客户姓名（2）知道身份证号Create procedure 客户信息身份证号 char(18)AsSelect身份证号，姓名，用户名，联系电话，银行卡类型，银行卡号，籍贯，类型From 客户Where 身份证号=身份证号2、 要求查询已知车次车票的所有信息Create procedure 车票信息车次 char(10)AsSelect车次，出发站，目的站，座位类型，座位号，车票价格，发车时间，到站时间from 车票Where 车次=车次3、 查询某一客户订票单的信息Create procedure 订票单信息姓名 char(10)AsSelect订票单号，订票时间，乘车日期，订票数量From订票单Where 身份证号 in （select 身份证号 from 客户Where姓名=姓名 ）4、查询客户所订购的车票信息Create procedure 客户的车票信息姓名 char （10）AsSelect车次，出发站，目的站，发车时间，到站时间，车票价格From 车票Where 订票单号 in(select 订票单号 from 订票单Where 身份证号 in(Select 身份证号 from 客户Where 姓名=姓名)5客户要查询自己的退票信息Create procedure 客户退票信息姓名 char （10）AsSelect退票单号，退票时间，退票数量，手续费From 退票单Where 车票编号 in (select 车票编号 from 车票Where 订票单号