--You must create a database for train scheduling management.
--The purpose of the database is to contain all the information about the routes of all trains.
--a) The entities of interest for the problem domain are: Trains, Train Types, Stations and Routes.
--b) Each train has a name and belongs to a type. The train type has only a description.
--c) Each route has a name, an associated train and a list of stations with the arrival and departure time in each station.
--The arrival and departure times are represented as an hour/minute pair (ex.: train arrives at 5pm and leaves at 5:10pm).
--Your tasks are:
--1) Write an SQL script to create a relational data model in order to represent the required data. (4 points)
--2) Create a stored procedure that receives a route, a station, arrival time, departure time and adds the new station to the route.
--If the station already exists, the arrival and departure times are updated (2 points)
--3) Create a view that shows name of routes which contain all stations (1 point)
--4) Create a function that list all stations that have more that 2 trains at a given time in the day. (2 points)
这是题目的要求但是我的英文不是很好有些地方我不太懂
1.第三行的domain指的是什么我需要创建domain吗
2.我输入的时间应该是int还是varchar类型的 如果是int那么怎么输入5:10这样的格式如果是varchar的 最后一个问题应该怎么把
departure time和arrive time 相减
3.我是建立了route和train两个table一直在纠结他们之间要不要建立foreign key关系
4.task 2里面说只要 输入a route, a station, arrival time, departure time就好但是我的table里面还有一个参数是train_name
5.然后那个view和function也不会写
CREATE TABLE route
(
name varchar(20),
train_name varchar(20),
station_name varchar(20),
r_desc varchar(50)
);
CREATE TABLE train
(
name varchar(10),
train_type varchar(20)
);
GO
CREATE PROCEDURE add_route
@name varchar(20), @station_name varchar(20), @arrivaltime varchar(10), @departuretime varchar
AS
declare @station_name_ varchar(20)
declare @judge int = 0
--check
declare stationcursor Cursor For
select station_name
from route
open stationcursor
Fetch Next From stationcursor into @Station_name
While @@FETCH_STATUS = 0
Begin
--compare
If @station_name_ = @station_name --charindex(@Station_name,@station_name) = 0
Begin
update route
Set station_name = @station_name, r_desc = concat(N'train arrived at ', @arrivaltime, N' and leaves at ', @departuretime), @judge = 1
where station_name = @station_name
print N'Successfully Update'
End
If @judge = 0
Begin
Insert Into route
Values (@name, @train_name, @station_name, concat(N'train arrived at ', @arrivaltime, N' and leaves at ', @departuretime))
print N'Successfully Add'
End
Fetch Next From staioncursor into @station_name_
END
Close stationcursor
Deallocate stationcursor
Go
这是我写好的代码请过目然后请帮我指出有问题的地方！！！江湖救急啊！！！！！这是感激不尽了 还有能否给我提供function和view的思路
------解决思路----------------------
1. domain是告诉你解决问题的范围，而且已经描述清楚这道题目里面的实体就是Trains, Train Types, Stations and Routes
2. 时间应该选择日期类型，Oracle中使用Date类型，SQL server中使用Datetime类型。
2.5. departure time和arrive time直接用减号相减就好，Oracle中可以直接用减号。SQL server你也可以用datediff函数。
3.最好设置一个外键，因为题目中提到每条路线关联一列火车。
4.train_name可以用default null表示。
------解决思路----------------------
--你的数据库设计火车站点到站和出站时间没有体现吧
--一点建议，参考下:
--路线表
CREATE TABLE route
(
route_id varchar(10) , --路线编号(可作为主键)
route_name varchar(20), --路线名
train_type varchar(20), --火车型号(可作为外键)
);
--火车名车表
CREATE TABLE train --觉得一两火车属性应该很多，可以按需求增加
(
train_name varchar(10),
train_type varchar(20) unique --由于一辆火车对应只有一个类型，应该是唯一键(可作为主键)
);
GO
--站点表
create table station
(
station_id varchar(20), --站点编号
route_id varchar(10), --路线编号(外键)
arrival_time datetime ,
departurer_time datetime
)