吉林大学列车信息查询数据库实验报告.doc

数据库实验报告 数据库实验报告列车信息查询一、实验内容设计一个数据库，实现列车车次信息、车站信息、列车员信息的基本管理，为列车运行时刻信息的录入、修改、查询与统计等功能提供数据库支撑。二、列车信息查询表ER图发出时间行驶时间到达时间终点站列车编号车种类始发站到站时间开车时间停留时间车站名字城市省份车站经过列车153、 将E-R模型转换为关系模型列车（列车编号，车种类，始发站，发出时间，终点站，到达时间，行驶时间，经过车站）车站（车站名字，省份，城市）经过（列车编号，车站名，到站时间，开车时间，停留时间）4、 规范化关系模型1、 语义：（1） 、每一列火车有唯一的列车编号和自己的车种类，始发站，发车时间，终点站，到达时间，行驶的时间，经过车站。（2） 、每个车站有一个名字，对应的所在的城市省份。（3） 、一辆火车可以经过很多车站。2、找出函数依赖集：列车表最小函数依赖集如下：列车编号 车种，列车编号 始发站，列车编号 发出时间， 列车编号 终点站，列车编号 到达时间，列车编号 行驶时间， 列车编号 经过车站不存在传递依赖关系。车站表最小函数依赖集如下：车站名字 省份，车站名字 城市，城市 省份由于车站名字 城市，城市 省份，所以车站名字和省分之间存在传递依赖。经过表的最小函数依赖集如下:（列车编号，车站名字） 到站时间，（列车编号，车站名字） 开车时间， （列车编号，车站名字） 停留时间不存在函数依赖关系。3、 函数依赖图：列车表：为方便表示，下面用T表示列车表，T-no表示列车编号，T-kd表示列车种类，T-st表示始发站，T-stime表示发出时间，T-en表示终点站，T-etime表示到达时间，T-time表示行驶时间，S-name经过车站。T-etimeT-kdT-stT-timeT-noT-stimeS-nameT-en车站表：用S表示车站表，S-name表示车站名字，S-p表示省份，S-c表示城市。S-cS-nameS-p经过表：用P表示经过表，P-retime表示到站时间，P-stime表示开车时间，stopt表停留时间。P-retimeT-nostoptP-stimeS-name4、以上存在传递关系，所以需要优化：S-cS-nameS-p5、 规范化结果：列车（列车编号，车种类，始发站，发出时间，终点站，到达时间，行驶时间，剩余票数，经过车站）车站（车站名字，城市）地区（城市，省份）经过（列车编号，车站名字，到站时间，开车时间，停留时间）5、 数据库表格1、列车表:字段名数据类型长度约束列车编号Char20Unique车种类Char10Not null始发站Char60Not null发出时间TimeNot null终点站Char60Not null到达时间TimeNot null行驶时间Time Not null2、 车站表：字段名数据类型长度约束车站名字Char60Unique 城市Char20Not null3、 地区表字段名数据类型长度约束城市Char20Unique省份Char204、经过表字段名数据类型长度约束列车编号Char 20Unique Foreign key车站名字Char 60UniqueForeign key到站时间Time 开车时间Time 停留时间Time 6、 SQL语句建表1、建立数据库：create database 列车信息查询系统;2、 建表：create table 车站(车站名字 char (60) unique, 城市 char (20) not null); create table 地区 (城市 char(20) unique, 省份 char(20) );create table 列车(列车编号 char (20) unique, 车种类 char (10) not null, 始发站 char(60)not null, 发出时间 time not null, 终点站 char(60) not null, 到达时间 time not null,行驶时间 time not null ); create table 经过 (列车编号 char (20) unique foreign key (列车编号) references 列车(列车编号), 车站名字 char (60) unique foreign key (车站名字) references 车站(车站名字), 到站时间 time , 开车时间 time , 停留时间 time ); 3、 插入数据： insert into 车站 values ('北京南','北京'); insert into 车站 values ('廊坊','廊坊'); insert into 车站 values ('天津南','天津'); insert into 车站 values ('沧州西','沧州'); insert into 车站 values ('德州东','德州'); insert into 车站 values ('济南西','济南'); insert into 车站 values ('北京','北京'); insert into 车站 values ('天津西','天津'); insert into 车站 values ('沧州','沧州'); insert into 车站 values ('德州','德州'); insert into 车站 values ('济南东','济南'); insert into 车站 values ('淄博','淄博'); insert into 车站 values ('潍坊','潍坊'); insert into 车站 values ('胶州','胶州'); insert into 车站 values ('莱阳','莱阳'); insert into 车站 values ('桃村','栖霞'); insert into 车站 values ('乳山','乳山'); insert into 车站 values ('文登','文登'); insert into 车站 values ('威海','威海'); insert into 车站 values ('济南','济南'); insert into 车站 values ('泰山','泰安'); insert into 车站 values ('泗水','高州'); insert into 车站 values ('平邑','临沂市'); insert into 车站 values ('日照','日照'); insert into 车站 values ('枣庄西','枣庄'); insert into 车站 values ('徐州','徐州'); insert into 车站 values ('南京','南京'); insert into 车站 values ('马鞍山','马鞍山'); insert into 车站 values ('芜湖','芜湖'); insert into 车站 values ('宣城','宣城'); insert into 车站 values ('长兴南','湖州'); insert into 车站 values ('德清西','湖州'); insert into 车站 values ('义乌','义乌'); insert into 车站 values ('金华西','金华'); insert into 车站 values ('武义','金华'); insert into 车站 values ('永康','永康'); insert into 车站 values ('丽水','丽水'); insert into 车站 values ('温州','温州'); insert into 车站 values ('青田','丽水'); insert into 地区 values ('北京',null); insert into 地区 values ('廊坊','河北'); insert into 地区 values ('天津',null); insert into 地区 values ('沧州','河北'); insert into 地区 values ('德州','山东'); insert into 地区 values ('济南','山东'); insert into 地区 values ('淄博','山东'); insert into 地区 values ('德州','山东'); insert into 地区 values ('潍坊','山东'); insert into 地区 values ('胶州','山东'); insert into 地区 values ('莱阳','山东'); insert into 地区 values ('栖霞','山东'); insert into 地区 values ('乳山','山东'); insert into 地区 values ('文登','山东'); insert into 地区 values ('威海','山东'); insert into 地区 values ('德州','山东'); insert into 地区 values ('泰安','山东'); insert into 地区 values ('临沂','山东'); insert into 地区 values ('日照','山东'); insert into 地区 values ('枣庄','山东'); insert into 地区 values ('高州','广东'); insert into 地区 values ('徐州','江苏'); insert into 地区 values ('南京','江苏'); insert into 地区 values ('马鞍山','安徽'); insert into 地区 values ('芜湖','安徽'); insert into 地区 values ('宣城','安徽'); insert into 地区 values ('湖州','浙江'); insert into 地区 values ('义乌','浙江'); insert into 地区 values ('金华','浙江'); insert into 地区 values ('永康','浙江'); insert into 地区 values ('丽水','浙江'); insert into 地区 values ('温州','浙江'); insert into 列车 values ('K411','快速','北京','22:32:00','威海','12:56:00','14h24min'); insert into 列车 values ('D403','动车','北京南','21:16:00','济南西','23:16:00','2h'); insert into 列车 values ('K51','快速','北京','23:08:00','日照','11:11:00','12h3min'); insert into 列车 values ('K101','快速','北京','23:20:00','温州','05:20:00','30h'); insert into 经过 values ('D403','北京南',null,'21:16:00',null); insert into 经过 values ('D403','廊坊','21:37:00','21:39:00','00:02:00'); insert into 经过 values ('D403','天津南','21:57:00','21:59:00','00:02:00'); insert into 经过 values ('D403','沧州西','22:21:00','22:23:00','00:02:00'); insert into 经过 values ('D403','德州东','22:50:00','22:52:00','00:02:00'); insert into 经过 values ('D403','济南西','23:16:00',null,null); insert into 经过 values ('K411','北京',null,'22:32:00',null); insert into 经过 values ('K411','天津西','00:15:00','00:24:00','00:09:00'); insert into 经过 values ('K411','沧州','01:29:00','01:33:00','00:04:00'); insert into 经过 values ('K411','德州','02:57:00','03:01:00','00:04:00'); insert into 经过 values ('K411','济南东','04:41:00','04:53:00','00:12:00'); insert into 经过 values ('K411','淄博','05:55:00','05:58:00','00:03:00'); insert into 经过 values ('K411','潍坊','07:01:00','07:04:00','00:03:00'); insert into 经过 values ('K411','胶州','08:05:00','08:08:00','00:03:00'); insert into 经过 values ('K411','莱阳','09:24:00','09:28:00','00:04:00'); insert into 经过 values ('K411','桃村','10:07:00','10:19:00','00:12:00'); insert into 经过 values ('K411','乳山','11:15:00','11:18:00','00:03:00'); insert into 经过 values ('K411','文登','12:14:00','12:13:00','00:02:00'); insert into 经过 values ('K411','威海','12:56:00',null,null); insert into 经过 values ('K51','北京',null,'23:08:00',null); insert into 经过 values ('K51','天津西','00:42:00','00:44:00','00:02:00'); insert into 经过 values ('K51','沧州','01:50:00','01:53:00','00:03:00'); insert into 经过 values ('K51','德州','03:28:00','03:31:00','00:03:00'); insert into 经过 values ('K51','济南','04:58:00','05:13:00','00:15:00'); insert into 经过 values ('K51','泰山','06:03:00','06:05:00','00:02:00'); insert into 经过 values ('K51','泗水','07:59:00','08:01:00','00:02:00'); insert into 经过 values ('K51','平邑','08:27:00','08:30:00','00:03:00'); insert into 经过 values ('K51','日照','11:11:00',null,null); insert into 经过 values ('K101','北京',null,'23:20:00',null); insert into 经过 values ('K101','天津西','00:54:00','00:58:00','00:04:00'); insert into 经过 values ('K101','沧州','02:09:00','02:23:00','00:14:00'); insert into 经过 values ('K101','德州','04:01:00','04:04:00','00:03:00'); insert into 经过 values ('K101','济南','05:33:00','05:45:00','00:12:00'); insert into 经过 values ('K101','泰山','06:35:00','06:38:00','00:03:00'); insert into 经过 values ('K101','枣庄西','08:39:00','08:49:00','00:10:00'); insert into 经过 values ('K101','徐州','09:48:00','09:55:00','00:07:00'); insert into 经过 values ('K101','南京','13:52:00','14:00:00','00:08:00'); insert into 经过 values ('K101','马鞍山','15:38:00','15:42:00','00:04:00'); insert into 经过 values ('K101','芜湖','16:19:00','16:25:00','00:06:00'); insert into 经过 values ('K101','宣城','17:14:00','17:22:00','00:08:00'); insert into 经过 values ('K101','长兴南','18:30:00','18:35:00','00:05:00'); insert into 经过 values ('K101','德清西','19:19:00','19:23:00','00:04:00'); insert into 经过 values ('K101','杭州','20:21:00','20:33:00','00:12:00'); insert into 经过 values ('K101','义乌','21:27:00','22:02:00','00:05:00'); insert into 经过 values ('K101','金华西','22:39:00','23:02:00','00:23:00'); insert into 经过 values ('K101','武义','00:12:00','00:16:00','00:04:00'); insert into 经过 values ('K101','永康','00:36:00','00:39:00','00:03:00'); insert into 经过 values ('K101','丽水','01:44:00','01:51:00','00:07:00'); insert into 经过 values ('K101','青田','03:20:00','03:34:00','00:14:00'); insert into 经过 values ('K101','温州','05:20:00',null,null);七、用SQL语句查询1、查询某车站火车到站出站信息。查询经过泰山车站的火车到站时间，出站时间和停留时间 select 列车编号,到站时间,开车时间 出站时间,停留时间 from 车站,经过 where 车站.车站名字='泰山'and 车站.车站名字=经过.车站名字;查询经过沧州车站的火车到站时间，出站时间和停留时间 select 列车编号,到站时间,开车时间 出站时间,停留时间 from 车站,经过 where 车站.车站名字='沧州'and 车站.车站名字=经过.车站名字;2、 查询某列火车沿途经过站点信息查询K51经过的站点信息select 车站.车站名字,车站.城市 车站所在城市,省份 from 列车,经过,车站,地区 where 列车.列车编号='K51' and 列车.列车编号=经过.列车编号 and 经过.车站名字=车站.车站名字 and 车站.城市=地区.城市;查询K101经过的站点信息select 车站.车站名字,车站.城市 车站所在城市,省份 from 列车,经过,车站,地区 where 列车.列车编号='K101' and 列车.列车编号=经过.列车编号 and 经过.车站名字=车站.车站名字 and 车站.城市=地区.城市;3、 查询从某站点上车，到达某站点，共有哪些列车可供乘坐查询从北京站到济南站，可选的列车 select aa.列车编号 from 经过 aa,经过 bb where aa.车站名字='北京' and bb.车站名字='济南' and aa.列车编号=bb.列车编号; 4、 统计某段时间经过某站点的列车共有多少统计在00:00:00到02:00:00之间经过天津西站的列车个数select COUNT (*)from 经过where 到站时间>='00:00:00' and 到站时间<='02:00:00' and 车站名字='天津西'