SQL数据库课程设计,火车票

SQL上机实验项目设计列车信息查询数据库学号：24110121姓名：王若龙1、实验项目名称：数据模型的建立过程与方法2、实验项目编号：ab08224203053、实验性质：必做4、实验类型：设计5、实验目的：熟练掌握数据模型的设计、建立的过程与方法。

6、实验内容：设计一个数据库，实现列车车次信息和车站信息的基本管理，为列车运行时刻信息的录入、修改、查询与统计等功能提供数据库支撑。

要求：（1）设计该数据库E-R图（2）将E-R模型转换为关系模型，并规范化（3）用SQL语句定义并建立数据库表结构（4）提供以下SQL语句，并加以说明。

查询某车站火车到站出站信息。

查询某列火车沿途经过站点信息查询从某站点上车，到达某站点，共有哪些列车可供乘坐。

统计某段时间经过某站点的列车共有多少。

（1）设计E-R 图（2）将E-R 模型转换为关系模型，并规范化车站信息（车站号，车站名）列车信息（车次，始发站，终点站，出发时间，到达时间） 乘务人员（车次，姓名，职务）经由信息（车次，车站名，到站时间，出站时间）（3）用SQL 语句定义并建立数据库表结构车站信息表（station ），采用命令行创建方式。

字段名代码 类型 约束车站号 sno char （5） 主键 车站名 sname char （20）列车信息表（train ），采用命令行创建方式。

字段名 代码 类型 约束车次 tno char （5） 主键 始发站 sf char(20) Not null 终点站 zd char(20) Not null出发时间 cf time 到达时间 dd time乘务人员表（worker），采用命令行创建方式。

经由表（pass），采用命令行创建方式。

create database testuse testcreate table train--建立列车信息表(tno char(5)primary key,sf char(20)not null,zd char(20)not null,cf time(0)not null,dd time(0)not null,);create table station--建立车站信息表(sno char(5)primary key,sname char(20));create table worker--建立职工信息表(tno char(5)foreign key (tno)references train(tno), name char(20)primary key,zhiwu char(10)not null,);create table pass--建立经由信息表(tno char(5)foreign key (tno)references train(tno),sno char(5)foreign key (sno)references station(sno),dz time(0)not null,cz time(0)not null,);--添加列车信息insert into train values ('K1384','哈尔滨','包头','16:28','21:12'); insert into train values ('T298','长春','北京','7:32','14:17'); insert into train values ('G382','长春西','北京南','17:03','23:13'); insert into train values ('D30','长春西','北京','15:17','22:22'); insert into train values ('Z62','长春','北京','20:15','06:08'); insert into station values ('1','哈尔滨')--添加车站信息insert into station values ('2','扶余')insert into station values ('3','长春')insert into station values ('4','四平')insert into station values ('5','通辽')insert into station values ('6','商都')insert into station values ('7','呼和浩特')insert into station values ('8','包头')insert into station values ('01','长春')insert into station values ('02','北京')insert into station values ('11','长春')insert into station values ('12','沈阳北')insert into station values ('13','锦州'). insert into station values ('14','葫芦岛')insert into station values ('15','山海关')insert into station values ('16','秦皇岛')insert into station values ('17','北京')insert into station values ('21','长春西')insert into station values ('22','沈阳北')insert into station values ('23','葫芦岛')insert into station values ('24','秦皇岛')insert into station values ('25','唐山')insert into station values ('26','北京')insert into station values ('31','长春西')insert into station values ('32','四平')insert into station values ('33','沈阳北')insert into station values ('34','山海关')insert into station values ('35','秦皇岛')insert into station values ('36','北京')select*from stationinsert into worker values ('K1384','邓鑫磊','列车长')--添加职工信息表insert into worker values ('K1384','陈继军','服务员')insert into worker values ('K1384','贺鹏飞','乘警')insert into worker values ('Z62','汪洋','列车长')insert into worker values ('Z62','张志凯','服务员')insert into worker values ('Z62','李进鹏','乘警')insert into worker values ('T298','陆冠豪','列车长')insert into worker values ('T298','郭子维','服务员')insert into worker values ('T298','任延富','乘警')insert into worker values ('G382','俞鸿涛','列车长')insert into worker values ('G382','李德鸿','服务员')insert into worker values ('G382','王若龙','乘警')insert into worker values ('D30','银鑫','列车长')insert into worker values ('D30','李博','服务员')insert into worker values ('D30','金成龙','乘警')insert into pass values('K1384','1','16:28','16:28')--添加经由信息表insert into pass values('K1384','2','17:57','17:59')insert into pass values('K1384','3','19:34','19:44')insert into pass values('K1384','4','21:08','21:28')insert into pass values('K1384','5','00:15','00:30')insert into pass values('K1384','6','14:11','14:14')insert into pass values('K1384','7','19:10','19:18')insert into pass values('K1384','8','21:12','21:12')insert into pass values('Z62','01','20:15','20:15')insert into pass values('Z62','02','06:08','06:08')insert into pass values('T298','11','23:59','00:06')insert into pass values('T298','12','02:44','02:56')insert into pass values('T298','13','05:23','05:29')insert into pass values('T298','14','06:00','06:03')insert into pass values('T298','15','07:26','07:32')insert into pass values('T298','16','07:50','07:54')insert into pass values('T298','17','10:48','10:48')insert into pass values('G382','21','17:00','17:03')insert into pass values('G382','22','18:40','18:44')insert into pass values('G382','23','20:20','20:21')insert into pass values('G382','24','21:14','21:16')insert into pass values('G382','25','21:54','21:56')insert into pass values('G382','26','23:13','23:13')insert into pass values('D30','31','15:44','15:47')insert into pass values('D30','32','16:23','16:25')insert into pass values('D30','33','17:31','17:34')insert into pass values('D30','34','19:47','19:48')insert into pass values('D30','35','20:05','20:06')insert into pass values('D30','36','22:22','22:22')（4）提供以下SQL语句，并加以说明--查询长春站到站出站信息SELECT sname车站名,tno车次,dz到站时间,cz出站时间from pass,stationwherepass.sno=station.snoandsname='长春'--查询K1384列车沿途车站信息select train.tno车次,sname车站名,dz到站时间,cz出站时间from pass,train,stationwherepass.sno=station.snoandtrain.tno=pass.tnoandtrain.tno='K1384'--从长春站到四平站的可选择的车次,使用自连接select distinct train.tno车次,station.sname出发,aa.sname到达,dz到站时间,cz出站时间from pass,train,station,station as aawhere train.tno=pass.tnoand pass.sno=station.snoand station.sname='长春'and aa.sname='四平'--统计8:00-23:00经过秦皇岛的列车数,使用嵌套语句select count(*)列车数目from trainwhere train.tnoin(select train.tnofrom pass,train,stationwhere train.tno=pass.tnoand pass.sno=station.snoand dz between'8:00'and'23:00'and station.sname='秦皇岛')。