通过逻辑设计，进行物理结构的设计
分析后达到三范式后建立以下五个表：
TICKET_INFO 表存储火车车票各种信息PASSENGER_INFO 表存储火车乘客信息BOOK_INFO 表存储乘客订票退票信息TR_CONVERT 表存储火车票的折算信息TRAIN_INFO 表存储火车信息
一．车票信息（TICKET_INFO表）：
该表主键：车票号（TICKET_NO）
二：乘客信息(PASSENGER_INFO 表)
三：火车票折算信息（TR_CONVERT 表）
四．火车信息（TRAIN_INFO 表）
五．订票退票信息(BOOK_INFO表)
2.在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 V ARCHAR(15)NOT NULL,
MID_STA V ARCHAR(15) ,
END_STA V ARCHAR(15)NOT NULL,
START_TIME SMALLDATETIME NOT NULL,
SPEND_TIME FLOAT NOT NULL,
SORT V ARCHAR(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 V ARCHAR(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 V ARCHAR(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 FLOA T,
PAID FLOAT,
IN_OUT_TIME DATETIME,
STATE V ARCHAR(5)CHECK(STA TE 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 LEA VE_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
