Система бронирования железнодорожных билетов

Листинг 1_create_database.sql

create database db103; --Валиев М.Ф., гр. Р-45031

go

use db103;

if exists( select id from sysobjects where type='U' and name='clients')

drop table clients

create table dbo.clients( --список клиентов

clientid int identity(1, 1)

,login varchar(20) not null --логин

,passwd varchar(20) not null --пароль

,clname varchar(20) not null --имя

,surname varchar(30) not null --фамилия

,bday smalldatetime not null --дата рождения

,passport varchar(11) not null --паспорт: серия, номер

--Ограничения

,constraint pk_cl_login primary key(clientid)

,constraint cl_login_unique unique (login)

,constraint cl_check_passport check

(passport like '[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9][0-9]')

)

if exists( select id from sysobjects where type='U' and name='stations')

drop table stations

go

create table dbo.stations( --ж/д станции

stid int identity(1, 1) --id станции

,stname varchar(40) not null --название

--Ограничения

,constraint pk_st_stid primary key (stid)

,constraint st_stname_unique unique (stname)

)

if exists( select id from sysobjects where type='U' and name='trains')

drop table trains

go

create table dbo.trains( --поезда

trnum int not null --номер

,caption varchar(40) null --название поезда

--Ограничения

,constraint pk_tr_trid primary key (trnum)

,constraint tr_unique unique (trnum, caption)

)

if exists( select id from sysobjects where type='U' and name='routs')

drop table routs

go

create table dbo.routs( --маршруты

trnum int --номер поезда

,stid int --id станции

,stnum int not null --порядковый номер станции в пути следования данного поезда

,roadtime int null --время от предыдущей станции до текущей (в минутах)

,stoptime int null --время стоянки (в минутах)

--Ограничения

,constraint fk_r_routnum foreign key (trnum)

references trains(trnum) on delete cascade

,constraint fk_r_routstid foreign key (stid)

references stations(stid) on delete cascade

,constraint r_check_stnum check

(stnum > 0)

,constraint r_check_roadtime check

(roadtime > 0)

,constraint r_check_stoptime check

(stoptime > 0)

,constraint r_unique unique (trnum, stnum)

)

if exists( select id from sysobjects where type='U' and name='timetable')

drop table timetable

go

create table dbo.timetable( --расписание

trid int identity(1, 1) --id поезда

,trnum int --номер поезда

,send_date smalldatetime not null --дата отправления

--Ограничения

,constraint pk_tt_trid primary key (trid)

,constraint fk_ttnum foreign key (trnum)

references trains(trnum) on delete cascade

,constraint tt_unique unique (trnum, send_date)

)

if exists( select id from sysobjects where type='U' and name='vagons')

drop table vagons

go

create table dbo.vagons( --вагоны

vagid int identity(1,1) --id вагона

,trid int --id поезда

,vagnum int not null --номер вагона

,type tinyint not null --тип вагона: 3 - плацкарт, 2 - купе, 1 - СВ

,freenum tinyint not null --число свободных мест в вагоне (изначально будет рассчитываться как vagtype*18)

--Ограничения

,constraint pk_v_vagid primary key (vagid)

,constraint fk_v_vagtrid foreign key (trid)

references timetable(trid) on delete cascade

,constraint v_check_vagnum check

(vagnum > 0)

,constraint v_check_type check

(type in (1, 2, 3))

,constraint v_check_freenum check

(freenum >= 0)

)

if exists( select id from sysobjects where type='U' and name='current_orders')

drop table current_orders

go

create table dbo.current_orders( --текущие заказы

orderid int identity(1, 1) --id заказа

,clientid int --id клиента

,trid int --id поезда

,vagnum int not null --номер вагона

,type tinyint not null --тип вагона

,amount int not null --количество

,status tinyint not null --статус заказа 1 - забронирован, 2 - снят

,buy_date datetime not null --дата заказа

--Ограничения

,constraint pk_co_orderid primary key (orderid)

,constraint fk_co_clientid foreign key (clientid)

references clients(clientid) on delete cascade

,constraint fk_co_trid foreign key (trid)

references timetable(trid)

,constraint co_check_vagnum check

(vagnum > 0)

,constraint co_check_type check

(type in (1, 2, 3))

,constraint co_check_amount check

(amount > 0)

,constraint co_check_status check

(status in (1, 2))

)

if exists( select id from sysobjects where type='U' and name='history_orders')

drop table history_orders

go

create table dbo.history_orders( --история заказов

histid int not null --id заказа

,clientid int --логин клиента

,trid int --id поезда

,vagnum int not null --номер вагона

,type tinyint not null --тип вагона

,amount int not null --количество

,status tinyint not null --статус заказа 1 - забронирован, 2 - снят, 3 - куплен

,buy_date datetime not null --дата заказа

--Ограничения

,constraint fk_ho_clientid foreign key (clientid)

references clients(clientid)

,constraint fk_ho_trid foreign key (trid)

references timetable(trid)

,constraint ho_check_vagnum check

(vagnum > 0)

,constraint ho_check_type check

(type in (1, 2, 3))

,constraint ho_check_amount check

(amount > 0)

,constraint ho_check_status check

(status in (1, 2, 3))

)

if exists( select id from sysobjects where type='U' and name='history_trains')

drop table history_trains

go

create table dbo.history_trains( --история движения поездов

histid int not null --id поезда

,trnum int --номер поезда

,send_date smalldatetime --дата отправления

,amount_vagon int --число вагонов

,amount_places int --общее число мест в поезде

,free_number int --общее число свободных мест в поезде

--Ограничения

,constraint fk_ht_trnum foreign key (trnum)

references trains(trnum)

,constraint ht_check_amount_vagon check

(amount_vagon > 0)

)

--создание пользователя user

exec sp_addlogin 'dbuser', '', db103

exec sp_grantdbaccess 'dbuser'

--создание роли admins

exec sp_addrole 'admins'

grant select

on clients

to admins

grant all

on stations

to admins

grant all

on trains

to admins

grant all

on routs

to admins

grant all

on timetable

to admins

grant all

on vagons

to admins

grant all

on current_orders

to admins

grant all

on history_orders

to admins

grant all

on history_trains

to admins

Листинг 2_stored_procedures.sql

--1. просмотр списка поездов от станции1 до станции2 (для юзеров)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_viewtrainlist') and objectproperty (id,N'IsProcedure') = 1

)

drop procedure sp_viewtrainlist

go

create proc sp_viewtrainlist

@station1 varchar(40), @station2 varchar(40)

as

declare @stid1 int --id станции1

set @stid1 = (select stid from stations

where stname = @station1)

declare @stid2 int --id станции2

set @stid2 = (select stid from stations

where stname = @station2)

create table #tr(

trid int --id поезда (из timetable)

,trnum int --номер поезда

,st1_time int --время прибытия поезда к ст.1, в минутах

,st1_stoptime int --время стоянки поезда на ст.1, в минутах

,st2_time int --время прибытия поезда к ст.2, в минутах

,st2_stoptime int --время стоянки поезда на ст.2, в минутах

)

insert into #tr(trid, trnum) --вставляем id и номера нужных поездов

select trid, trnum

from timetable

where trnum in (select r1.trnum from routs r1 --выбираем такие поезда, кот. проходят

where (r1.stid = @stid1) --сначала через ст.1, а потом через ст.2

and exists (select * from routs r2

where (r2.trnum = r1.trnum) and (r2.stid = @stid2)

and (r2.stnum >= r1.stnum)

)

)

update #tr --находим время прибытия к ст. 1

set st1_time = (select case

when sum(r.roadtime) is not null and sum(r.stoptime) is null then sum(r.roadtime)

else sum(r.roadtime) + sum(r.stoptime)

end

from routs r

where (r.trnum = #tr.trnum)

and r.stnum <= (select r1.stnum from routs r1 --суммируем время до ст.1

where (r1.trnum = r.trnum) and (r1.stid = @stid1))

group by r.trnum)

update #tr --находим время стоянки на ст.1

set st1_stoptime = (select r.stoptime

from routs r

where (r.trnum = #tr.trnum) and (r.stid = @stid1))

update #tr --вычитаем из времени прибытия время стоянки на самой ст.1

set st1_time = (select case

when #tr.st1_stoptime is null then #tr.st1_time

else #tr.st1_time - #tr.st1_stoptime

end)

update #tr --находим время прибытия к ст.2

set st2_time = (select case

when sum(r.roadtime) is null and sum(r.stoptime) is null then 0

when sum(r.roadtime) is not null and sum(r.stoptime) is null then sum(r.roadtime)

else sum(r.roadtime) + sum(r.stoptime)

end

from routs r

where (r.trnum = #tr.trnum)

and r.stnum <= (select r1.stnum from routs r1

where (r1.trnum = r.trnum) and (r1.stid = @stid2))

group by r.trnum)

update #tr --находим время стоянки на ст.2

set st2_stoptime = (select r.stoptime

from routs r

where (r.trnum = #tr.trnum) and (r.stid = @stid2))

update #tr --вычитаем из времени прибытия время стоянки на самой ст.2

set st2_time = (select case

when #tr.st2_stoptime is null then #tr.st2_time

else #tr.st2_time - #tr.st2_stoptime

end)

select tt.trnum, tt.send_date, t.caption

,case

when #tr.st1_time is null then null --если эта станция - первая в маршруте поезда

else dateadd(minute, #tr.st1_time, tt.send_date) --переводим в дату

end as [ст.1 прибытие]

,#tr.st1_stoptime as [ст.1 стоянка]

,case

when #tr.st1_time is null then tt.send_date --если эта станция - первая в маршруте поезда

else dateadd(minute, #tr.st1_time+#tr.st1_stoptime, tt.send_date)

end as [ст.1 отправление]

,dateadd(minute, #tr.st2_time, tt.send_date) as [ст.2 прибытие]

,#tr.st2_stoptime as [ст.2 стоянка]

,case

when #tr.st2_stoptime is null then null --если станция - последняя в маршруте поезда

else dateadd(minute, #tr.st2_time+#tr.st2_stoptime, tt.send_date)

end as [ст.2 отправление]

from #tr inner join timetable tt

on #tr.trid = tt.trid

inner join trains t

on tt.trnum = t.trnum

order by tt.trnum, tt.send_date

drop table #tr

go

--система безопасности

grant execute

on sp_viewtrainlist

to admins

grant execute

on sp_viewtrainlist

to dbuser

--2. просмотр списка свободных мест в указанном поезде (для юзеров)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_viewtrain') and objectproperty(id,N'IsProcedure') = 1 )

drop procedure sp_viewtrain

go

create proc sp_viewtrain

@number int, @send_date smalldatetime --номер и дата отправления поезда

as

declare @trid int --id поезда

set @trid = (select trid from timetable

where (trnum = @number) and (send_date = @send_date))

select t.trnum, tt.send_date, t.caption --выводим инфо о поезде

from timetable tt inner join trains t

on tt.trnum = t.trnum

where (tt.trid = @trid)

select vagnum, case type --выбираем в табличку номер вагона, тип, число свободных мест

when 3 then 'плацкарт'

when 2 then 'купе'

when 1 then 'СВ'

end, freenum

from vagons

where (trid = @trid)

order by vagnum

go

--система безопасности

grant execute

on sp_viewtrain

to admins

grant execute

on sp_viewtrain

to dbuser

--3. бронирование билета (для юзеров)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_buyticket') and objectproperty(id,N'IsProcedure') = 1)

drop procedure sp_buyticket

go

create proc sp_buyticket

@number int, @send_date smalldatetime, @vagnum int, @amount int --поезд, номер вагона,

,@login varchar(20) --число билетов

,@result tinyint output --результат: 1- успех, 0 -неудача

,@orderid int = null output

as

if exists (select * from timetable --если есть такой поезд

where trnum = @number and send_date = @send_date )

and exists (select * from vagons --и есть такой вагон

where trid = (select trid from timetable

where trnum = @number and send_date = @send_date )

and vagnum = @vagnum

and freenum >= @amount

and @amount > 0

)

begin

declare @trid int --id поезда

set @trid = (select trid from timetable

where (trnum = @number) and (send_date = @send_date))

update vagons --изменяем число своб. мест

set freenum = vagons.freenum -@amount

where (trid = @trid) and (vagnum = @vagnum)

declare @type tinyint --тип вагона, нужен для записи в таблицу текущих заказов

set @type = (select type from vagons

where (trid = @trid) and (vagnum = @vagnum))

--вставляем в таблицу текущих заказов

declare @logid int --clientid

set @logid = (select clientid from clients

where login = @login)

declare @instime datetime --время вставки в таблицу

set @instime = getdate();

insert into current_orders(clientid, trid, vagnum, type,amount, status, buy_date)

values(@logid, @trid, @vagnum, @type, @amount, 1, @instime);

set @orderid = (select orderid from current_orders --значение OrderId (для юзеров)

where clientid = @logid

and buy_date = @instime)

set @result = 1

end

else

set @result = 0

go

--система безопасности

grant execute

on sp_buyticket

to dbuser

--4. просмотр своей истории (для юзеров)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_viewhistory') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_viewhistory

go

create proc sp_viewhistory

@login varchar(20)

as

select * from clients where login = @login

select co.orderid, tt.trnum, tt.send_date, co.vagnum, case co.type

when 1 then 'СВ'

when 2 then 'Купе'

when 3 then 'Плацкарт'

end as type

,co.amount, case co.status

when 1 then 'забронирован'

when 2 then 'снят'

end as status, co.buy_date

from current_orders co inner join timetable tt

on co.trid = tt.trid

where clientid = (select clientid from clients

where login = @login)

union all

select ho.histid, ht.trnum, ht.send_date, ho.vagnum, case ho.type

when 1 then 'СВ'

when 2 then 'Купе'

when 3 then 'Плацкарт'

end as type

,ho.amount, case ho.status

when 1 then 'забронирован'

when 2 then 'снят'

when 3 then 'куплен'

end as status, ho.buy_date

from history_orders ho inner join history_trains ht

on ho.trid = ht.histid

where clientid = (select clientid from clients

where login = @login)

union all

select ho.histid, tt.trnum, tt.send_date, ho.vagnum, case ho.type

when 1 then 'СВ'

when 2 then 'Купе'

when 3 then 'Плацкарт'

end as type

,ho.amount, case ho.status

when 1 then 'забронирован'

when 2 then 'снят'

when 3 then 'куплен'

end as status, ho.buy_date

from history_orders ho inner join timetable tt

on ho.trid =tt.trid

where clientid = (select clientid from clients

where login = @login)

go

--система безопасности

grant execute

on sp_viewhistory

to admins

grant execute

on sp_viewhistory

to dbuser

--5. отмена заказа (для юзеров)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_cancelorder') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_cancelorder

go

create proc sp_cancelorder

@orderid int --значение OrderID

,@login varchar(20) --логин юзера

,@result tinyint output --результат: 1- успех, 0 -неудача

as

if exists (select orderid from current_orders --выполняется,это его заказ

where orderid = @orderid

and clientid = (select clientid from clients

where login = @login))

begin

select * into #curorders --текущие заказы юзера

from current_orders

where orderid = @orderid

delete --удаляем из current_orders

from current_orders

where orderid = @orderid

insert into history_orders(histid, clientid, trid, vagnum, type, amount, status, buy_date)

select orderid, clientid, trid, vagnum, type, amount, 2, getdate()

from #curorders --вставка в историю

declare @amount int --число заказанных мест

set @amount = (select amount from #curorders)

update vagons --вставляем места обратно

set freenum = vagons.freenum + @amount

where (trid = (select trid from #curorders))

and (vagnum = (select vagnum from #curorders))

drop table #curorders

set @result = 1

end

else

set @result = 0

go

--система безопасности

grant execute

on sp_cancelorder

to dbuser

--6. добавление нового поезда (только для админов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_addnewtrain') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_addnewtrain

go

create proc sp_addnewtrain

@trnum int, @caption varchar(40), @station_list varchar(8000)

--номер поезда, название, список названий станций: название,время от предыдущей станции,

as -- время стоянки через запятую,

insert into trains(trnum, caption) --вставляем в trains

values(@trnum, @caption)

--вставляем в routs

declare @list varchar(8000)

set @list = @station_list --список названий станций, через зпт

declare @stnum int

set @stnum = 1 --номера станций в маршруте данного поезда

while (len(@list) > 0) --пока еще есть станции

begin

declare @i int

set @i = charindex(',', @list)-1

declare @stname varchar(40) --название станции

set @stname = substring(@list, 1, @i)

declare @stid int --id станции

set @stid = (select stid from stations where stname = @stname)

declare @roadtime int --время от предыдущей станции

declare @stoptime int --время стоянки

set @list = substring(@list, @i+2, len(@list)-@i-1)

set @i = charindex(',', @list)-1

set @roadtime = cast(substring(@list, 1, @i) as int)

set @list = substring(@list, @i+2, len(@list)-@i-1)

set @i = charindex(',', @list)-1

if (@i = -1)

set @i = len(@list)

set @stoptime = cast(substring(@list, 1, @i) as int)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --вставляем новую запись для данного поезда и станции

select @trnum, @stid, @stnum, case

when @roadtime = 0 then null

else @roadtime

end, case

when @stoptime = 0 then null

else @stoptime

end

if charindex(',', @list) = 0

set @list = ''

else

set @list = substring(@list, @i+2, len(@list)-@i-1)

set @stnum = @stnum + 1

end

go

--система безопасности

grant execute

on sp_addnewtrain

to admins

--7. добавление нового поезда в расписание (только для админов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_addnewrealtrain') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_addnewrealtrain

go

create proc sp_addnewrealtrain

@trnum int, @send_date smalldatetime --номер поезда, время отправления

,@vagon_list varchar(8000) --список типов вагонов через зпт

as

insert into timetable(trnum, send_date) --вставляем в timetable

values(@trnum, @send_date)

declare @trid int

set @trid = (select trid from timetable

where trnum = @trnum

and send_date = @send_date)

--вставляем в vagons

declare @list varchar(8000)

set @list = @vagon_list --список вагонов с указанием их типа через запятую

declare @stnum int

set @stnum = 1 --номер вагона

while (len(@list) > 0) --пока еще есть вагоны

begin

declare @i int

set @i = charindex(',', @list)-1

if (@i = -1)

set @i = len(@list)

declare @type tinyint --тип вагона

set @type = cast(substring(@list, 1, @i) as tinyint)

insert into vagons(trid, vagnum, type, freenum) --вставляем запись про новый вагон поезда

values(@trid, @stnum, @type, 18*@type)

if charindex(',', @list) = 0

set @list = ''

else

set @list = substring(@list, @i+2, len(@list)-@i-1)

set @stnum = @stnum + 1

end

go

--система безопасности

grant execute

on sp_addnewrealtrain

to admins

--8. добавление новой станции в существующий поезд (только для админов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_addstationtotrain') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_addstationtotrain

go

create proc sp_addstationtotrain

@trnum int, @stname varchar(40)

,@roadtime int = null, @stoptime int = null, @stnum int = 1, @roadnexttime int

,@result tinyint output --результат: 1 - успех, 0 - неудача

as

declare @stid int

set @stid = (select stid from stations where stname = @stname)

if exists (select * from stations where stname = @stname)

and exists (select * from trains where trnum = @trnum)

and not exists (select * from routs where trnum = @trnum

and stid = @stid

and stnum = @stnum)

begin

update routs --увеличиваем номера станций, которые после новой

set stnum = routs.stnum + 1

where (trnum = @trnum) and (stnum >= @stnum)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --вставляем новую станцию

values(@trnum, @stid, @stnum, @roadtime, @stoptime)

update routs --изменяем время движения от введенной станции до следующей

set roadtime = @roadnexttime

where stnum = (@stnum + 1)

set @result = 1

end

else

set @result = 0

go

--система безопасности

grant execute

on sp_addstationtotrain

to admins

--9. удаление поезда из расписания (только для админов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_deletetrain') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_deletetrain

go

create proc sp_deletetrain

@trnum int, @send_date smalldatetime

as

delete from timetable --удаляем из timetable

where (trnum = @trnum) and (send_date = @send_date)

go

--система безопасности

grant execute

on sp_deletetrain

to admins

--10. просмотр отчетов: 1 - маркетинговый (истории заказов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_showmarketingreport') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_showmarketingreport

go

create proc sp_showmarketingreport

@startdate smalldatetime = null

,@enddate smalldatetime = null

as

declare @start smalldatetime

declare @end smalldatetime

if @startdate is null

set @start = '1900-01-01 00:00'

else

set @start = @startdate

if @enddate is null

set @end = getdate()

else

set @end = @enddate

select * from history_orders

where buy_date between @start and @end

order by histid

go

--система безопасности

grant execute

on sp_showmarketingreport

to admins

--11. просмотр отчетов: 2 - по поездам (только для админов)

if exists (select * from dbo.sysobjects

where id=object_id(N'sp_showtrainreport') and objectproperty(id,N'IsProcedure') = 1

)

drop procedure sp_showtrainreport

go

create proc sp_showtrainreport

@startdate smalldatetime = null

,@enddate smalldatetime = null

as

declare @start smalldatetime

declare @end smalldatetime

if @startdate is null

set @start = '1900-01-01 00:00'

else

set @start = @startdate

if @enddate is null

set @end = getdate()

else

set @end = @enddate

select * from history_trains

where send_date between @start and @end

order by histid

go

--система безопасности

grant execute

on sp_showtrainreport

to admins

go

--Триггеры

create trigger d_timetable on timetable

for delete

as

insert into history_trains(histid, trnum, send_date, amount_vagon, amount_places, free_number)

select d.trid, d.trnum, d.send_date, count(*), sum(v.type)*18, sum(v.freenum)

from vagons v inner join deleted d

on v.trid = d.trid

group by v.trid, d.trid, d.trnum, d.send_date

insert into history_orders

select c.orderid, c.clientid, c.trid, c.vagnum

,c.type, c.amount, c.status, c.buy_date

from current_orders c inner join deleted d

on c.trid = d.trid

delete

from current_orders

where trid = (select trid from deleted)

delete

from vagons

where trid = (select trid from deleted)

go

--Job

use msdb

go

exec sp_add_job @job_name = 'change_timetable'

,@enabled = 1

,@description = 'каждый день все расписание сдвигается на 1 день'

,@notify_level_eventlog = 0

,@notify_level_email = 0

,@notify_level_netsend = 0

,@notify_level_page = 0

,@delete_level = 0

exec sp_add_jobstep

@job_name = 'change_timetable'

,@step_name = 'удаление старых поездов'

,@command = 'delete from timetable

where (send_date < getdate())'

,@database_name = 'db103'

exec sp_apply_job_to_targets

@job_name = 'change_timetable'

,@target_servers = 'MICHMAN'

,@operation = apply

exec sp_add_jobschedule

@job_name = 'change_timetable'

,@name = 'план выполнения задания )'

,@enabled = 1 --включено

,@freq_type = 4 --ежедневно

,@freq_interval = 1 --каждый день

,@active_start_date = 20081208 -- начиная с 08-12-2008

,@active_start_time = 073700 --в 07:37

go

use db103

Листинг 3_default_fill_tables.sql

--clients

insert into clients(login, passwd, clname, surname, bday, passport)

values ('123', '123', 'Дмитрий', 'Медведев', '1965-04-12', '6040 123123');

insert into clients(login, passwd, clname, surname, bday, passport)

values ('bar', 'bar', 'Барак', 'Обама', '1970-01-24', '6234 341234');

--stations

insert into stations(stname)

values('Свердловск');

insert into stations(stname)

values('Палкино');

insert into stations(stname)

values('Ромкино');

insert into stations(stname)

values('Москва');

insert into stations(stname)

values('Пермь');

--trains

insert into trains (trnum, caption)

values (1, 'Свердловск - Москва')

insert into trains (trnum, caption)

values (2, 'Свердловск - Москва')

insert into trains (trnum, caption)

values (3, 'Свердловск - Ромкино')

insert into trains (trnum, caption)

values (4, 'Пермь - Москва')

insert into trains (trnum, caption)

values (5, 'Палкино')

--routs

insert into routs(trnum, stid, stnum, roadtime, stoptime) --Поезд №1: Свердловск,Палкино,Ромкино,Москва

values(1, 1, 1, null, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(1, 2, 2, 400, 10)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(1, 3, 3, 240, 2)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(1, 4, 4, 480, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --Поезд №2: Свердловск,Палкино,Москва

values(2, 1, 1, null, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(2, 2, 2, 400, 10)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(2, 4, 3, 700, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --Поезд №3: Свердловск,Ромкино

values(3, 1, 1, null, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(3, 3, 2, 600, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --Поезд №4: Пермь,Москва

values(4, 5, 1, null, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime)

values(4, 4, 2, 300, null)

insert into routs(trnum, stid, stnum, roadtime, stoptime) --Поезд №5: Палкино

values(5, 2, 1, null, null)

--timetable

insert into timetable(trnum, send_date)

values(1, '2008-12-17 14:53');

insert into timetable(trnum, send_date)

values(2, '2008-12-16 10:00');

insert into timetable(trnum, send_date)

values(3, '2008-12-16 9:40');

insert into timetable(trnum, send_date)

values(4, '2008-12-17 16:12');

insert into timetable(trnum, send_date)

values(5, '2008-12-16 17:39');

insert into timetable(trnum, send_date)

values(1, '2008-12-18 14:53');

--vagons

insert into vagons(trid, vagnum, type, freenum)

values(1, 1, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(1, 2, 2, 36)

insert into vagons(trid, vagnum, type, freenum)

values(1, 3, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(1, 4, 3, 54)

insert into vagons(trid, vagnum, type, freenum)

values(2, 1, 2, 36)

insert into vagons(trid, vagnum, type, freenum)

values(2, 2, 3, 54)

insert into vagons(trid, vagnum, type, freenum)

values(2, 3, 2, 36)

insert into vagons(trid, vagnum, type, freenum)

values(2, 4, 3, 54)

insert into vagons(trid, vagnum, type, freenum)

values(3, 1, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(3, 2, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(3, 3, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(4, 1, 3, 54)

insert into vagons(trid, vagnum, type, freenum)

values(4, 2, 3, 54)

insert into vagons(trid, vagnum, type, freenum)

values(4, 3, 2, 36)

insert into vagons(trid, vagnum, type, freenum)

values(5, 1, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(5, 2, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(5, 3, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(6, 1, 2, 36)

insert into vagons(trid, vagnum, type, freenum)

values(6, 2, 1, 18)

insert into vagons(trid, vagnum, type, freenum)

values(6, 3, 1, 18)

exec sp_addlogin '1', '1', db103

exec sp_grantdbaccess '1'

exec sp_addrolemember'admins', '1'

Листинг 4_drop_database.sql

use master;

drop database db103; --удаляем БД

use msdb; --удаляем Джоб

exec sp_delete_jobschedule

@job_name = 'change_timetable'

,@name = 'план выполнения задания )'

exec sp_delete_job @job_name = 'change_timetable'

use master;

exec sp_droplogin 'dbuser'

exec sp_droplogin '1'

Размещено на Allbest.ru