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

_ 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），采用命令行创建方式。字段名代码类型约束车站号snochar（5）主键车站名snamechar（20） 列车信息表（train），采用命令行创建方式。字段名代码类型约束车次tnochar（5）主键始发站sfchar(20)Not null终点站zdchar(20)Not null出发时间cftime到达时间ddtime 乘务人员表（worker），采用命令行创建方式。字段名代码类型约束车次 tno char(5)与列车信息表里的车次外键关联姓名 namechar（10）主键职务 zhiwuchar（10）经由表（pass），采用命令行创建方式。字段名代码类型约束车次tno char(5)与列车信息表里的车次外键关联车站名snochar（20）与车站信息表里的车站名外键关联到站时间 dztime出站时间cztime停留时间tlchar(5)相关建表代码实现如下：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.sno andsname=长春-查询K1384列车沿途车站信息select train.tno 车次,sname 车站名,dz 到站时间,cz 出站时间from pass,train,stationwhere pass.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.s