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/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 |
