火车票订购系统数据库的物理设计

通过逻辑 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 ，进行物理结构的设计 分析后达到三范式后建立以下五个 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ： TICKET_INFO 表 存储火车车票各种信息 PASSENGER_INFO 表 存储火车乘客信息 BOOK_INFO 表 存储乘客订票退票信息 TR_CONVERT 表 存储火车票的折算信息 TRAIN_INFO 表 存储火车信息 一．车票信息（TICKET_INFO表）： 该表主键： 车票号 （TICKET_NO） 属性名 表中存储名 变量类型 完整性约束条件 补充说明 车票号 TICKET_NO Char(12) 主键 无 始发站 START_STA VARCHAR(15) 非空 无 中转站 MID_STA VARCHAR(15) 无 无 终点站 END_STA VARCHAR(15) 非空 无 出发时间 START_TIME SMALLDATETIME 非空 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 ：2010-3-23 9:00 估计所需时间 SPEND_TIME FLOAT 非空 是按小时算的 车票类型 SORT VARCHAR(4) 非空（软卧铺，硬座） 只有硬软卧铺三种 列车名 TRAIN_NAME CHAR(8) 无 格式：k569 价格 PRICE FLOAT 非空 无 发行量 AMOUNT INT 无 无 检票口 CHECK_NO INT 无 无 剩余票数 LEAVE_COUNT INT 无 无 二：乘客信息(PASSENGER_INFO 表) 属性名 表中存储名 变量类型 完整性约束条件 补充说明 身份证号 ID CHAR(20) 主键 位数确定 乘客类型 PASS_SORT VARCHAR(4) check(学生 幼儿 残疾 军人 一般) 只有左边四种 外键： 乘客类型 连接到CONVERT 表 三： 火车票折算信息（TR_CONVERT 表） 属性名 表中存储名 变量类型 完整性约束条件 补充说明 乘客类型 PASS_SORT CHAR(20) 主键 check(学生 幼儿 残疾 军人 一般) (只有四种) 折算 TRANS FLOAT check(0.0~1.0) 四．火车信息（TRAIN_INFO 表 ） 属性名 表中存储名 变量类型 完整性约束条件 补充说明 列车名 TRAIN_NAME CHAR(8) 主键 格式：k569 软卧位数 SOFT_COUNT INT 无 无 卧铺位数 SLEEP_COUNT INT 无 无 硬座车厢数 CARRI_COUNT INT 无 无 硬座数（每节） SEAT_COUNT INT 无 无 软卧车厢号 SOFT_NO INT 无 无 卧铺车厢号 SLEEP_NO INT 无 无 五．订票退票信息(BOOK_INFO表) 属性名 表中存储名 变量类型 完整性约束条件 补充说明 车票号 TICKET_NO CHAR(12) 主键，外键 无 身份证号 ID CHAR(20) 主键，外键 位数确定 车厢号 COACH_NO INT 非空 1-100 座位号 SEAT_NO INT 非空 无 应付 DEAL FLOAT 无 无 已付 PAID FLOAT 无 无 订票退票时间 IN_OUT_TIME DATETIME 无 无 状态 STATE VARCHAR(5) CHECK(已订，退票) 两种状态 在sqlserver2005 中建立数据库Train_Ticket_System ,建立以上各表 Sql语句为： 列车信息： CREATE TABLE TRAIN_INFO ( TRAIN_NAME CHAR(8) PRIMARY KEY, SOFT_COUNT INT, SOFT_NO INT, SLEEP_COUNT INT, SLEEP_NO INT, CARRI_COUNT INT SEAT_COUNT INT ); 车票信息： CREATE TABLE TICKET_INFO ( TICKET_NO CHAR(12) PRIMARY KEY, START_STA VARCHAR(15) NOT NULL, MID_STA VARCHAR(15) , END_STA VARCHAR(15) NOT NULL, START_TIME SMALLDATETIME NOT NULL, SPEND_TIME FLOAT NOT NULL, SORT VARCHAR(4) CHECK(SORT IN('软卧','卧铺','硬座')) NOT NULL, TRAIN_NAME CHAR(8) NOT NULL, PRICE FLOAT NOT NULL, AMOUNT INT, CHECK_NO INT LEAVE_COUNT INT, CONSTRAINT TRNAME_FK FOREIGN KEY(TRAIN_NAME) REFERENCES TRAIN_INFO(TRAIN_NAME); ); 折算信息： CREATE TABLE TR_CONVERT ( PASS_SORT VARCHAR(4) CHECK(PASS_SORT IN('学生','幼儿','残疾','军人','一般')) PRIMARY KEY, TRANS FLOAT CHECK(TRANS>=0.0 AND TRANS<=1.0) ); 乘客信息： CREATE TABLE PASSENGER_INFO ( ID CHAR(20), PASS_SORT VARCHAR(4) CHECK(PASS_SORT IN('学生','幼儿','残疾','军人','一般')), CONSTRAINT PINFO_PK PRIMARY KEY(ID), CONSTRAINT PINFO_FK FOREIGN KEY(PASS_SORT) REFERENCES TR_CONVERT(PASS_SORT) ); 订票信息： CREATE TABLE BOOK_INFO ( TICKET_NO CHAR(12), ID CHAR(20), COACH_NO INT NOT NULL, SEAT_NO INT NOT NULL, DEAL FLOAT, PAID FLOAT, IN_OUT_TIME DATETIME, STATE VARCHAR(5) CHECK(STATE IN ('已订','退票')), CONSTRAINT BINFO_PK PRIMARY KEY(TRAIN_NO,ID), CONSTRAINT TR_BINFO_FK FOREIGN KEY(TRAIN_NO) REFERENCES TICKET_INFO(TRAIN_NO), CONSTRAINT ID_BINFO_FK FOREIGN KEY(ID) REFERENCES PASSENGER_INFO(ID) ); 触发器：（如果一个车票订票，会自动更新 剩余车票数属性组（数据值减一）,如果更新的车票是之前退票的车票，则自动删除那个退票信息） CREATE TRIGGER BOOK_COUNT ON BOOK_INFO AFTER INSERT AS BEGIN DECLARE @TR_NO CHAR(12); DECLARE @ID_NO CHAR(20); SELECT @TR_NO=TICKET_NO FROM INSERTED; UPDATE TICKET_INFO SET LEAVE_COUNT = LEAVE_COUNT-1 WHERE TRAIN_NO=@TR_NO; SELECT @ID_NO=ID FROM DELETED WHERE TICKET_NO=@TR_NO AND STATE='退票'; IF(@ID_NO IS NOT NULL) BEGIN DELETE FROM BOOK_INFO WHERE ID=@ID_NO AND TICKET_NO=@TR_NO; END END 查询顾客需要付的车费：（更新语句） UPDATE BOOK_INFO SET DEAL= (SELECT TRANS*PRICE FROM TICKET_INFO TI,PASSENGER_INFO P,TR_CONVERT TR,BOOK_INFO B WHERE TI.TICKET_NO=B.TICKET_NO AND P.PASS_SORT=TR.PASS_SORT AND P.ID=B.ID AND B.ID='340111003' AND B.TICKET_NO='11108120002 ' ) WHERE ID='340111003' AND TICKET_NO='11108120002 ' 说明： 在对订票信息更新操作时 如果是对状态更新，那么需要对车票的信息也进行相应的操作，更新剩余票数。 查询在某段时间内： select * from TICKET_INFO WHERE START_TIME <'2010-11-16 9:00:00' and START_TIME>'2010-11-15 9:10:00' 建立售票情况视图：（sql语句） CREATE VIEW BOOK_COUNT_INFO(TIC_NO,BOOK_COUNT) AS SELECT T.TICKET_NO,COUNT(*) FROM TICKET_INFO T,BOOK_INFO B WHERE T.TICKET_NO=B.TICKET_NO AND STATE='已订' GROUP BY T.TICKET_NO 程序中 查看订票情况的查询语句： SELECT T.TICKET_NO,START_STA,MID_STA,END_STA,START_TIME ,SORT,PRICE,BOOK_COUNT FROM TICKET_INFO T,BOOK_COUNT_INFO B WHERE T.TICKET_NO=B.TIC_NO ORDER BY BOOK_COUNT DESC