12306-车

ylbtech-dbs:12306-车

 

A，数据库关系图(Database Diagram) 返回顶部

B，数据库设计脚本(Database Design Script)返回顶部

1，

-- =============================================
-- 12306-车
-- http://www.12306.cn
-- author:yuanbo
-- 16:25 2016/9/22
-- =============================================
use TrainTicket_Train
go

go
-- =============================================
-- table:车站表
-- ?地点同名：北京（北京南、北京西、北京）
-- author:yuanbo
-- =============================================
create table Station
(
stationId uniqueidentifier primary key,        --编号【PK，UQ】
station varchar(200),    --站名
diabled bit default(0)
)
insert Station(stationId,station) values('B3482AAC-087F-4565-AEB5-21EF14381EA1','菏泽')
insert Station(stationId,station) values('B3482AAC-087F-4565-AEB5-21EF14381EA2','定陶')
insert Station(stationId,station) values('B3482AAC-087F-4565-AEB5-21EF14381EA3','曹县')

--select * from Station

go
-- =============================================
-- table:列车表
-- author:yuanbo
-- =============================================
create table Train
(
trainId uniqueidentifier primary key,    --编号【PK，UQ】
trainCode varchar(200),        --列车编号
trainType varchar(200),        --列车类型K
diabled bit default(0)
)
insert Train(trainId,trainCode,trainType) values('DB79142B-0008-4D3C-9A80-6EA1A70C09AC','0001','客运列车')
--select * from Train
go
-- =============================================
-- table:列车厢表
-- author:yuanbo
-- =============================================
create table Carriage
(
carriageId uniqueidentifier primary key,    --编号【PK，UQ】
carriageCode varchar(200),    --列车厢(车厢类型决定车位类型)
carriageType varchar(200),    --列车类型
seatNumber int,        --座位数量
diabled bit default(0),
trainId uniqueidentifier references Train(trainId)    --列车ID【FK】
)
insert Carriage(carriageId,carriageCode,carriageType,seatNumber,trainId) values('A0D3706C-E37A-4A44-89D6-AA3E72E79831'
,'1','硬座车厢',10,'DB79142B-0008-4D3C-9A80-6EA1A70C09AC')
--select * from Carriage

go
-- =============================================
-- table:座位表
-- author:yuanbo
-- =============================================
create table Seat
(
seatId uniqueidentifier primary key,    --编号【PK，UQ】
seatCode varchar(200),    --座位编号
seatType varchar(200),    --座位类型
diabled bit default(0),
trainId uniqueidentifier references Train(trainId),    --列车ID【FK】
carriageId uniqueidentifier references Carriage(carriageId) --车厢ID【FK】
)
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'1','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'2','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'3','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'4','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'5','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
--,6
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'6','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'7','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'8','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'9','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')
insert Seat(seatId,seatCode,seatType,trainId,carriageId) values(NEWID(),'10','硬座','DB79142B-0008-4D3C-9A80-6EA1A70C09AC','A0D3706C-E37A-4A44-89D6-AA3E72E79831')


go
-- =============================================
-- table:车次表
-- author:yuanbo
-- =============================================
create table TrainNumber
(
trainNumberId uniqueidentifier primary key,    --编号【PK，UQ】
trainNumberCode varchar(200),    --列车代号
trainNumberType varchar(200),    --车次类型 GC-高铁/城际，D-动车，Z-直达，T-特快，K-快递，其他
--departureStation varchar(200),    --起始站
--destinationStation varchar(200),    --终点站
departureStationId uniqueidentifier references Station(stationId),    --起始站，车站ID【FK】
destinationStationId uniqueidentifier references Station(stationId),    --终点站，车站ID【FK】

departureTime datetime,        --出发时间
destinationTime datetime,    --到达时间
--startEndTime datetime,        --历时
--当日到达、次日到达、两日到达
diabled bit default(0),
trainId uniqueidentifier references Train(trainId),    --列车ID【FK】
)
--去，菏泽-->曹县
insert TrainNumber(trainNumberId,trainNumberCode,departureStationId,destinationStationId,departureTime
,destinationTime,trainId)
values('F681391E-8FBB-49FC-8159-AE7FA5FF5A36','Z01','B3482AAC-087F-4565-AEB5-21EF14381EA1','B3482AAC-087F-4565-AEB5-21EF14381EA3','2016-9-25 08:10'
,'2016-9-25 10:10','DB79142B-0008-4D3C-9A80-6EA1A70C09AC')
----回，曹县-->菏泽
--insert TrainNumber(trainNumberId,trainNumberCode,departureStationId,destinationStationId,departureTime
--,destinationTime,trainId)
--values(NEWID(),'Z02','B3482AAC-087F-4565-AEB5-21EF14381EA3','B3482AAC-087F-4565-AEB5-21EF14381EA1','2016-9-25 13:10'
--,'2016-9-25 15:10','DB79142B-0008-4D3C-9A80-6EA1A70C09AC')
--select * from TrainNumber

go
-- =============================================
-- table:时刻表
-- author:yuanbo
-- =============================================
create table Timetable
(
timetableId uniqueidentifier primary key,    --编号【PK，UQ】
sortCode varchar(200),    --站序
[type] varchar(200),    --类型 始、过、终；始发站、过路站、终点站
destinationTime datetime,    --到站时间
departureTime datetime,        --发车时间
--residenceTime datetime,        --停车时间=发车时间-发车时间
diabled bit default(0),
trainNumberId uniqueidentifier references TrainNumber(trainNumberId),    --车次ID【FK】
stationId uniqueidentifier references Station(stationId)    --站名，车站ID【FK】
)
--Z01，菏泽
insert Timetable(timetableId,sortCode,[type],departureTime,destinationTime
,trainNumberId,stationId) values(NEWID(),'1','始',null,'2016-9-25 08:10'
,'F681391E-8FBB-49FC-8159-AE7FA5FF5A36','B3482AAC-087F-4565-AEB5-21EF14381EA1')
--Z01，定陶
insert Timetable(timetableId,sortCode,[type],departureTime,destinationTime
,trainNumberId,stationId) values(NEWID(),'2','过','2016-9-25 09:10','2016-9-25 09:20'
,'F681391E-8FBB-49FC-8159-AE7FA5FF5A36','B3482AAC-087F-4565-AEB5-21EF14381EA2')
--Z01，曹县
insert Timetable(timetableId,sortCode,[type],departureTime,destinationTime
,trainNumberId,stationId) values(NEWID(),'3','终','2016-9-25 10:10',null
,'F681391E-8FBB-49FC-8159-AE7FA5FF5A36','B3482AAC-087F-4565-AEB5-21EF14381EA3')

--select * from Timetable
--drop table Timetable

0，

C，功能实现代码(Function Implementation Code)返回顶部

1，

2，

作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。