SQL數(shù)據(jù)庫(kù)課程設(shè)計(jì),火車票

1、. SQL上機(jī)實(shí)驗(yàn)項(xiàng)目 設(shè)計(jì)列車信息查詢數(shù)據(jù)庫(kù)學(xué)號(hào)：24110121姓名：王若龍 1、實(shí)驗(yàn)項(xiàng)目名稱：數(shù)據(jù)模型的建立過(guò)程與方法2、實(shí)驗(yàn)項(xiàng)目編號(hào)：ab08224203053、實(shí)驗(yàn)性質(zhì)：必做4、實(shí)驗(yàn)類型：設(shè)計(jì)5、實(shí)驗(yàn)?zāi)康模菏炀氄莆諗?shù)據(jù)模型的設(shè)計(jì)、建立的過(guò)程與方法。 6、實(shí)驗(yàn)內(nèi)容：設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)，實(shí)現(xiàn)列車車次信息和車站信息的基本管理，為列車運(yùn)行時(shí)刻信息的錄入、修改、查詢與統(tǒng)計(jì)等功能提供數(shù)據(jù)庫(kù)支撐。要求： （1）設(shè)計(jì)該數(shù)據(jù)庫(kù)E-R圖（2）將E-R模型轉(zhuǎn)換為關(guān)系模型，并規(guī)范化（3）用SQL語(yǔ)句定義并建立數(shù)據(jù)庫(kù)表結(jié)構(gòu)（4）提供以下SQL語(yǔ)句，并加以說(shuō)明。 查詢某車站火車到站出站信息。 查詢某列火車沿途經(jīng)過(guò)站

2、點(diǎn)信息 查詢從某站點(diǎn)上車，到達(dá)某站點(diǎn)，共有哪些列車可供乘坐。 統(tǒng)計(jì)某段時(shí)間經(jīng)過(guò)某站點(diǎn)的列車共有多少。（1） 設(shè)計(jì)E-R 車次 車次圖 （2） 將E-R模型轉(zhuǎn)換為關(guān)系模型，并規(guī)范化車站信息（車站號(hào)，車站名）列車信息（車次，始發(fā)站，終點(diǎn)站，出發(fā)時(shí)間，到達(dá)時(shí)間）乘務(wù)人員（車次，姓名，職務(wù)）經(jīng)由信息（車次，車站名，到站時(shí)間，出站時(shí)間）（3） 用SQL語(yǔ)句定義并建立數(shù)據(jù)庫(kù)表結(jié)構(gòu)車站信息表（station），采用命令行創(chuàng)建方式。字段名代碼類型約束車站號(hào)snochar（5）主鍵車站名snamechar（20） 列車信息表（train），采用命令行創(chuàng)建方式。字段名代碼類型約束車次tnochar（5）主鍵始發(fā)站

3、sfchar(20)Not null終點(diǎn)站zdchar(20)Not null出發(fā)時(shí)間cftime到達(dá)時(shí)間ddtime 乘務(wù)人員表（worker），采用命令行創(chuàng)建方式。字段名代碼類型約束車次 tno char(5)與列車信息表里的車次外鍵關(guān)聯(lián)姓名 namechar（10）主鍵職務(wù) zhiwuchar（10）經(jīng)由表（pass），采用命令行創(chuàng)建方式。字段名代碼類型約束車次tno char(5)與列車信息表里的車次外鍵關(guān)聯(lián)車站名snochar（20）與車站信息表里的車站名外鍵關(guān)聯(lián)到站時(shí)間 dztime出站時(shí)間cztime停留時(shí)間tlchar(5)相關(guān)建表代碼實(shí)現(xiàn)如下：create database

4、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),nam

5、e char(20) primary key,zhiwu char(10) not null,);create table pass-建立經(jīng)由信息表(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

6、values (T298,長(zhǎng)春,北京,7:32,14:17);insert into train values (G382,長(zhǎng)春西,北京南,17:03,23:13);insert into train values (D30,長(zhǎng)春西,北京,15:17,22:22);insert into train values (Z62,長(zhǎng)春,北京,20:15,06:08);insert into station values (1,哈爾濱)-添加車站信息insert into station values (2,扶余)insert into station values (3,長(zhǎng)春)insert into

7、 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,長(zhǎng)春)insert into station values (02,北京)insert into station values (11,長(zhǎng)春)insert into station values (12,沈陽(yáng)北)insert

8、into station values (13,錦州)insert into station values (14,葫蘆島)insert into station values (15,山海關(guān))insert into station values (16,秦皇島)insert into station values (17,北京)insert into station values (21,長(zhǎng)春西)insert into station values (22,沈陽(yáng)北)insert into station values (23,葫蘆島)insert into station values (2

9、4,秦皇島)insert into station values (25,唐山)insert into station values (26,北京)insert into station values (31,長(zhǎng)春西)insert into station values (32,四平)insert into station values (33,沈陽(yáng)北)insert into station values (34,山海關(guān))insert into station values (35,秦皇島)insert into station values (36,北京)select *from stati

10、oninsert into worker values (K1384,鄧鑫磊,列車長(zhǎng))-添加職工信息表insert into worker values (K1384,陳繼軍,服務(wù)員)insert into worker values (K1384,賀鵬飛,乘警)insert into worker values (Z62,汪洋,列車長(zhǎng))insert into worker values (Z62,張志凱,服務(wù)員)insert into worker values (Z62,李進(jìn)鵬,乘警)insert into worker values (T298,陸冠豪,列車長(zhǎng))insert into w

11、orker values (T298,郭子維,服務(wù)員)insert into worker values (T298,任延富,乘警)insert into worker values (G382,俞鴻濤,列車長(zhǎng))insert into worker values (G382,李德鴻,服務(wù)員)insert into worker values (G382,王若龍,乘警)insert into worker values (D30,銀鑫,列車長(zhǎng))insert into worker values (D30,李博,服務(wù)員)insert into worker values (D30,金成龍,乘警)i

12、nsert into pass values(K1384,1,16:28,16:28)-添加經(jīng)由信息表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

13、,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

14、(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 pas

15、s 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

16、 pass values(D30,35,20:05,20:06)insert into pass values(D30,36,22:22,22:22)（4）提供以下SQL語(yǔ)句，并加以說(shuō)明-查詢長(zhǎng)春站到站出站信息SELECT sname 車站名,tno 車次,dz 到站時(shí)間,cz 出站時(shí)間from pass,stationwherepass.sno = station.sno andsname=長(zhǎng)春-查詢K1384列車沿途車站信息select train.tno 車次,sname 車站名,dz 到站時(shí)間,cz 出站時(shí)間from pass,train,stationwhere pass.sno=station.snoandtrain.tno=pass.tnoandtrain.tno=K1384-從長(zhǎng)春站到四平站的可選擇的車次,使用自連接select distinct train.tno 車次,station.sname 出發(fā),aa.sname 到達(dá),dz 到站時(shí)間,cz 出站時(shí)間 from pass,train,station, station as aawhere train.tno = pass.tnoand pass.sno = statio