РАЗРАБОТКА БАЗЫ ДАННЫХ ДЛЯ «РАСПИСАНИЕ ДВИЖЕНИЯ ПОЕЗДОВ»

В данной работе разрабатывается база данных для расписания движения поездов. Имеются UML-диаграммы бизнес-целей предметной области, бизнес-прецедентов. ...

1 Постановка задачи 4
2 Анализ предметной области 6
3 Проектирование базы данных 10
4 Реализация базы данных в формате MS SQL Server 24
5 Хранимые процедуры для вставки данных 50
Список литературы 51
Приложение 52

Предметная область, подлежащая изучению – «расписание движения поездов». Цель этой предметной области – предоставление информации о движении поездов. Расписание движения поездов является основной организацией всей перевозочной работы на железнодорожном транспорте. Оно обязательно для всех подразделений железных дорог: станций, локомотивных депо, пунктов технического обслуживания и ремонта вагонов, тяговых подстанций, дистанций пути, сигнализации и связи так далее. Расписание организует работу всех подразделений в единое целое. На его основе согласовывается деятельность железных дорог с предприятиями-грузоотправителями, определяются показатели использования вагонов и локомотивов, осуществляется своевременная и безопасная пере-возка пассажиров. Соблюдение расписания движения поездов и предуп реждение его нарушений является главным условием для всех работников, связанных с организацией движения.
Целью курсовой работы является создание информационно-программной системы для решения следующих задач:
1. автоматизации учёта расписания, его:
a. разработки,
b. согласования,
c. выдачи,
d. реализации;
2. обработка данных о движении поездов;
3. ведение базы данных об отправлении и прибытии поезда;
4. поиск и выборка данных по различным критериям;
5. формирование отчетов;
6. ведение электронного каталога.
К разрабатываемой системе предъявлены следующие требования.
1. наличие централизованного хранилища данных для всех объектов автоматизированного учета,
2. ведение операций информационного учета с компьютеров локальной сети пред-приятия,
3. создание унифицированного доступа к данным с использованием специализированного программного интерфейса пользователя, который бы отражал специфику этой предметной области.
Для решения поставленных задач планируется выполнение следующих действий:
1. проектирование данных для отражения специфики данной предметной области,
2. разработка и создание базы данных для хранения информации на едином сервере данных под управлением MS SQL Server.
и т.д

nvarchar(30)
@имя
Имя или название организации
входной
нет
nvarchar(30)
@отчество
Отчество (у организации отсутствует)
входной
null
nvarchar(30)
@фамилия
Фамилия (у организации отсутствует)
входной
null
nvarchar(30)
Возвращаемый результат процедуры:
'Ошибка: источника не существует'
'Ошибка: страны не существует'
'Ошибка: ФИО не должно полностью совпадать'
'Ошибка: имя (название) не должно быть пустым'
'Запись успешно вставлена'
Листинг 4 – SQL код создания и использования процедуры «добавить_источник»
create procedure добавить_источник
@result nvarchar(50) output,@название_типа_источника nvarchar(50),
@название_страны nvarchar(30), @имя nvarchar(30), @отчество nvarchar(30) = '',
@фамилия nvarchar(30) = ''
as
begin
--проверка существования значения @название_типа_источника в таблице тип_источника
if not exists(select название_типа_источника from тип_источника where название_типа_источника = @название_типа_источника )
begin
select'Ошибка: источника не существует'
return
end
--проверка существования значения @название_страны в таблице страна
if not exists(select название_страны from страна where название_страны = @название_страны )
begin
select'Ошибка: страны не существует'
return
end
if exists(select фамилия,имя,отчество from источник where фамилия = @фамилия and имя = @имя and отчество = @отчество )
begin
select'Ошибка: ФИО не должно полностью совпадать'
return
end
-- Имя (название) не должно быть пустым
if (@имя='')
begin
select'Ошибка: имя (название) не должно быть пустым'
return
end
insert into источник( название_типа_источника,
название_страны, имя, отчество, фамилия )
values(@название_типа_источника, @название_страны, @имя, @отчество, @фамилия
)
if @@rowcount>0 select'Запись успешно вставлена'
end
go
--Пример использования:
declare @res nvarchar(50)
exec добавить_источник @res output, 'Государственный художественный музей', 'Россия','Третьяковская галлерея'
select @res
5.4 Хранимая процедура для таблицы «автор»
Имя процедуры: добавить_автора
Назначение процедуры: добавление информации об авторе экспоната.
Требования к целостности данных: Название страны должно существовать в таблице «страна», год рождения быть раньше года смерти.
Таблица 34 – Аргументы процедуры «добавить_автора»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_страны
Название страны (значение из поля название_страны таблицы «страна»)
входной
нет
nvarchar(30)
@имя
Имя
входной
нет
nvarchar(30)
@отчество
Отчество
входной
null
nvarchar(30)
@фамилия
Фамилия
входной
null
nvarchar(30)
@год_рождения
Год рождения
входной
null
int
@год_смерти
Год смерти
входной
null
int
Возвращаемый результат процедуры:
'Ошибка: страны не существует'
'Ошибка: год рождения больше, чем год смерти'
'Ошибка: ФИО не должно полностью совпадать'
'Ошибка: имя не должно быть пустым'
'Запись успешно вставлена'
Листинг 5 – SQL код создания и использования процедуры «добавить_автора»
create procedure добавить_автора
@result nvarchar(50) output,
@название_страны nvarchar(30),
@имя nvarchar(30),
@отчество nvarchar(30) = '',
@фамилия nvarchar(30) = '',
@год_рождения smallint = null,
@год_смерти smallint = null
as
begin
--проверка существования значения @название_страны в таблице страна
if not exists(select название_страны from страна where название_страны = @название_страны )
begin
select'Ошибка: страны не существует'
return
end
--год смерти должен быть позже года рождения
if((@год_смерти!=0 or @год_рождения!=0) and @год_смерти<@год_рождения)
begin
select'Ошибка: год рождения больше, чем год смерти'
return
end
--ФИО совпадает
if exists(select фамилия,имя,отчество from автор where фамилия = @фамилия and имя = @имя and отчество = @отчество )
begin
select'Ошибка: ФИО не должно полностью совпадать'
return
end
-- Имя не должно быть пустым
if (@имя='')
begin
select'Ошибка: имя не должно быть пустым'
return
end
insert into автор(
название_страны,
имя,
отчество,
фамилия,
год_рождения,
год_смерти
)
values(
@название_страны,
@имя,
@отчество,
@фамилия,
@год_рождения,
@год_смерти
)
if @@rowcount>0 select'Запись успешно вставлена'
end
go
--Пример использования:
declare @res nvarchar(50)
exec добавить_автора @res output, 'Россия','Сергей','Петрович','Кистин',1941,2006
select @res
5.5 Хранимая процедура для таблицы «стиль»
Имя процедуры: добавить_стиль
Назначение процедуры: добавление информации о стиле.
Таблица 35 – Аргументы процедуры «добавить_стиль»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_стиля
Название стиля
входной
нет
nvarchar(30)
@описание
Описание стиля
входной
Пустая строка
ntext
@год_появления
Год появления стиля
входной
null
int
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: нельзя присвоить стилю пустую строку'
'Запись успешно вставлена'
Листинг 6 – SQL код создания и использования процедуры «добавить_стиль»
create procedure добавить_стиль
@result nvarchar(50) output,
@название_стиля nvarchar(30),
@описание ntext = '',
@год_появления smallint = null
as
begin
-- Проверка на одинаковую запись
if exists(select * from стиль
where
название_стиля = @название_стиля)
begin
select'Ошибка: запись уже существует'
return
end
if (@название_стиля='')
begin
select'Ошибка: нельзя присвоить стилю пустую строку'
return
end
insert into стиль
(
название_стиля,
описание,
год_появления
)
Values
(
@название_стиля,
@описание,
@год_появления
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
--Пример использования:
declare @res nvarchar(50)
exec добавить_стиль @res output,
'Модернизм',
'В широком смысле модернизм — «другое искусство», главной целью которого является создание
оригинальных произведений, основанных на внутренней свободе и особом видении мира автором и
несущих новые выразительные средства изобразительного языка.',
1863
select @res
5.6 Хранимая процедура для таблицы «представитель_стиля»
Имя процедуры: добавить_представителя_стиля
Назначение процедуры: связывание таблиц «стиль» и «автор».
Требования к целостности данных: Соответствующие стиль и автор должны существовать.
Таблица 36 – Аргументы процедуры «добавить_представителя_стиля»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@ид_автора
Ид автора (значение из поля ид_автора таблицы «автор»)
входной
нет
int
@название_стиля
Название стиля (значение из поля название_стиля таблицы «стиль»)
входной
нет
nvarchar(30)
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: стиль не существует'
'Ошибка: автор не существует'
'Запись успешно вставлена'
Листинг 7 – SQL код создания и использования процедуры «добавить_представителя_стиля»
create procedure добавить_представителя_стиля
@result nvarchar(50) output,
@ид_автора int,
@название_стиля nvarchar(30)
as
begin
-- Проверка на одинаковую запись
if exists(select * from представитель_стиля where
ид_автора = @ид_автора and название_стиля = @название_стиля)
begin
select'Ошибка: запись уже существует'
return
end
--проверка существования значения @название_стиля в таблице стиль
if not exists(select название_стиля from стиль where название_стиля = @название_стиля )
begin
select'Ошибка: стиль не существует'
return
end
--проверка существования значения @ид_автора в таблице автор
if not exists(select ид_автора from автор where ид_автора = @ид_автора )
begin
select'Ошибка: автор не существует'
return
end
insert into представитель_стиля(
ид_автора,
название_стиля
)
values(
@ид_автора,
@название_стиля
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
Пример использования:
declare @res nvarchar(50)
exec добавить_представителя_стиля @res output,1,'Модернизм'
select @res
5.7 Хранимая процедура для таблицы «категория»
Имя процедуры: добавить_категорию
Назначение процедуры: добавление категории экспоната
Таблица 37 – Аргументы процедуры «добавить_категорию»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_категории
Название категории экспоната
входной
нет
nvarchar(30)
@описание
Описание категории экспоната
входной
null
ntext
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: название не должно быть пустым'
'Запись успешно вставлена'
Листинг 8 – SQL код создания и использования процедуры
«добавить_категорию»
create procedure добавить_категорию
@result nvarchar(50) output,
@название_категории nvarchar(30),
@описание ntext = null
as
begin
-- Проверка на одинаковую запись
if exists(select * from категория where
название_категории = @название_категории)
begin
select'Ошибка: запись уже существует'
return
end
-- Название не должно быть пустым
if (@название_категории='')
begin
select'Ошибка: название не должно быть пустым'
return
end
insert into категория(
название_категории,
описание
)
values(
@название_категории,
@описание
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
Пример использования:
declare @res nvarchar(50)
exec добавить_категорию @res output,'Картина','Состоит из основы (холста, деревянной или металлической доски, картона, бумаги, камня), грунта и красочного слоя.'
select @res
5.8 Хранимая процедура для таблицы «Материал»
Имя процедуры: добавить_материал
Назначение процедуры: Добавление материала, из которого сделан экспонат
Таблица 38 – Аргументы процедуры «добавить_материал»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_материала
Название материала
входной
нет
nvarchar(30)
@описание
Описание материала
входной
null
ntext
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: название не должно быть пустым'
'Запись успешно вставлена'
Листинг 9 – SQL код создания и использования процедуры «добавить_материал»
create procedure добавить_материал
@result nvarchar(50) output,
@название_материала nvarchar(50),
@описание ntext = null
as begin
if exists(select * from материал where -- Проверка на одинаковую запись
название_материала = @название_материала)
begin
select'Ошибка: запись уже существует'
return
end
if (@название_материала='') -- Название не должно быть пустым
begin
select'Ошибка: название не должно быть пустым'
return
end
insert into материал(
название_материала,
описание
)
values(
@название_материала,
@описание
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
Пример использования:
declare @res nvarchar(50)
exec добавить_материал @res output,'Холст, масло','Материал для картин с одним из наиболее длительных сроков хранения'
select @res
5.9 Хранимая процедура для таблицы «Должность»
Имя процедуры: добавить_должность
Назначение процедуры: Добавление должности сотрудников музея
Таблица 39 – Аргументы процедуры «добавить_должность»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_должности
Название должности
входной
нет
nvarchar(30)
@разряд
Разряд
входной
нет
smallint
@базовый_оклад
Базовый оклад
входной
нет
smallmoney
@долж_инструкция
Должностная инструкция
входной
Пуст. стр
ntext
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: название не должно быть пустым'
'Запись успешно вставлена'
Листинг 10 – SQL код создания и использования процедуры «добавить_должность»
create procedure добавить_должность
@result nvarchar(50) output,
@название_должности nvarchar(30),
@разряд smallint,
@базовый_оклад smallmoney,
@долж_инструкция ntext=''
as
begin
-- Проверка на одинаковую запись
if exists(select * from должность where
название_должности = @название_должности)
begin
select'Ошибка: запись уже существует'
return
end
-- Название не должно быть пустым
if (@название_должности='')
begin
select'Ошибка: название не должно быть пустым'
return
end
begin
insert into должность(
название_должности,
разряд,
базовый_оклад,
долж_инструкция
)
values(
@название_должности,
@разряд,
@базовый_оклад,
@долж_инструкция
)
if @@rowcount>0
select'Запись успешно вставлена'
end
end
go
--Пример использования:
declare @res nvarchar(50)
exec добавить_должность @res output,'Завхоз',8,30000,'Наряду с другими обязанностями принимает экспонаты'
select @res
5.10 Хранимая процедура для таблицы «Сотрудник»
Имя процедуры: добавить_сотрудника
Назначение процедуры: Добавление сотрудника музея
Требования к целостности данных: Должность должна существовать, дата найма и дата рождения должны быть не больше текущей даты, в случае нулевой даты найма в таблицу вставляется текущая.
Таблица 40 – Аргументы процедуры «добавить_сотрудника»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@табельный_номер_сотрудника
Табельный номер сотрудника
входной
нет
nvarchar(10)
@дата_рождения
Дата рождения
входной
нет
nvarchar(10)
@название_должности
Название должности (значение из поля название_должности таблицы «должность»)
входной
нет
nvarchar(30)
@фамилия
Фамилия
входной
нет
nvarchar(30)
@имя
Имя
входной
нет
nvarchar(30)
@отчество
Отчество
входной
null
nvarchar(30)
@дата_найма
Дата найма
входной
null
nvarchar(10)
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: название должности не существует'
'Ошибка: формат даты рождения неверный'
'Ошибка: дата рождения больше текущей'
'Ошибка: имя не должно быть пустым'
'Ошибка: табельный номер сотрудника не должен быть пустым'
'Ошибка: фамилия не должна быть пустой'
'Ошибка: формат даты найма неверный'
'Ошибка: дата найма больше текущей'
'Запись успешно вставлена'
Листинг 11 – SQL код создания и использования процедуры «добавить_сотрудника»
create procedure добавить_сотрудника
@result nvarchar(50) output,
@табельный_номер_сотрудника nvarchar(10),
@дата_рождения nvarchar(10),
@название_должности nvarchar(30),
@фамилия nvarchar(30),
@имя nvarchar(30),
@отчество nvarchar(30)='',
@дата_найма nvarchar(10)=''
as
begin
-- Проверка на одинаковую запись
if exists(select * from сотрудник where
табельный_номер_сотрудника = @табельный_номер_сотрудника)
begin
select'Ошибка: запись уже существует'
return
end
--проверка существования значения @название_должности в таблице должность
if not exists(select название_должности from должность where название_должности = @название_должности )
begin
select'Ошибка: название должности не существует'
return
end
if (isdate(@дата_рождения)=0)
begin
select'Ошибка: формат даты рождения неверный'
return
end
if(@дата_рождения>getdate())
begin
select'Ошибка: дата рождения больше текущей'
return
end
-- Имя не должно быть пустым
if (@имя='')
begin
select'Ошибка: имя не должно быть пустым'
return
end
-- Табельный номер не должно быть пустым
if (@табельный_номер_сотрудника='')
begin
select'Ошибка: табельный номер сотрудника не должен быть пустым'
return
end
-- Фамилия не должна быть пустой
if (@фамилия='')
begin
select'Ошибка: фамилия не должна быть пустой'
return
end
--Проверка формата даты
if(@дата_найма!=NULL)
begin
if (isdate(@дата_найма)=0)
begin
select'Ошибка: формат даты найма неверный'
return
end
if(@дата_найма>getdate())
begin
select'Ошибка: дата найма больше текущей'
return
end
insert into сотрудник( табельный_номер_сотрудника, дата_рождения,
название_должности,
фамилия,
имя,
отчество,
дата_найма
)
values(
@табельный_номер_сотрудника,
cast(@дата_рождения as datetime),
@название_должности,
@фамилия,
@имя,
@отчество,
cast(@дата_найма as datetime)
)
if @@rowcount>0
select'Запись успешно вставлена'
end
else
begin
insert into сотрудник(
табельный_номер_сотрудника,
дата_рождения,
название_должности,
фамилия,
имя,
отчество,
дата_найма
)
values(
@табельный_номер_сотрудника,
cast(@дата_рождения as datetime),
@название_должности,
@фамилия,
@имя,
@отчество,
getdate()
)
if @@rowcount>0
select'Запись успешно вставлена'
end
end
go
--Пример использования (нулевая дата найма):
declare @res nvarchar(50)
exec добавить_сотрудника @res output,'ЗХ01','19790212','Завхоз','Иванов','Иннокентий'
select @res
5.11 Хранимая процедура для таблицы «Галерея»
Имя процедуры: добавить_галерею
Назначение процедуры: Добавление выставки
Требования к целостности данных: При некорректных датах, датам в таблице присваивается нулевое значение, не приводя к ошибке. Однако, если даты корректны, но дата начала позже даты конца, это приводит к ошибке.
Таблица 41 – Аргументы процедуры «добавить_галерею»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
выходной
nvarchar(50)
@название_галереи
Название галереи
входной
нет
nvarchar(10)
@описание
Описание галереи
входной
нет
nvarchar(10)
@дата_начала
Дата начала
входной
нет
nvarchar(30)
@дата_конца
Дата конца
входной
нет
nvarchar(30)
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: дата начала больше даты окончания'
'Ошибка: название не должно быть пустым'
'Запись успешно вставлена'
Листинг 12 – SQL код создания и использования процедуры «добавить_галерею»
create procedure добавить_галерею
@result nvarchar(50) output,
@название_галереи nvarchar(30),
@описание ntext='',
@дата_начала nchar(10)='',
@дата_конца nchar(10)=''
as
begin
-- Проверка на одинаковую запись
if exists(select * from галерея where
название_галереи = @название_галереи)
begin
select'Ошибка: запись уже существует'
return
end
declare @startdate datetime
if (isdate(@дата_начала)=0)
set @startdate = null
else
set @startdate = cast(@дата_начала as datetime)
declare @enddate datetime
if (isdate(@дата_конца)=0)
set @enddate = null
else
set @enddate = cast(@дата_конца as datetime)
if((@startdate!=null or @enddate!=null) and @enddate<@startdate)
begin
select'Ошибка: дата начала больше даты окончания'
return
end
-- Название не должно быть пустым
if (@название_галереи='')
begin
select'Ошибка: название не должно быть пустым'
return
end
insert into галерея(
название_галереи,
описание,
дата_начала,
дата_конца
)
values(
@название_галереи,
@описание,
@startdate,
@enddate
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
--Пример использования:
declare @res nvarchar(50)
exec добавить_галерею @res output,'Историческая','Галерея важных исторических объектов','20100620','20100721'
select @res
5.12 Хранимая процедура для таблицы «составитель_галереи»
Имя процедуры: добавить_составителя_галереи
Назначение процедуры: Связывание таблиц «галерея» и «сотрудник»
Требования к целостности данных: Сотрудник и галерея должны существовать
Таблица 42 – Аргументы процедуры «добавить_составителя_галереи»
Аргумент
Смысл
Вид аргумента
Значение
по умолчанию
Тип
@result
результат работы
процедуры
выходной
nvarchar(50)
@название_галереи
Название галереи (значение поля название_галереи таблицы «галерея»)
входной
нет
nvarchar(30)
@табельный_номер_
сотрудника
Название галереи (значение поля табельный_номер_сотрудника таблицы «сотрудник»)
входной
нет
nvarchar(10)
Возвращаемый результат процедуры:
'Ошибка: запись уже существует'
'Ошибка: галерея не существует'
'Ошибка: сотрудник не существует'
'Запись успешно вставлена'
Листинг 13 – SQL код создания и использования процедуры «добавить_составителя_галереи»
create procedure добавить_составителя_галереи
@result nvarchar(50) output,
@название_галереи nvarchar(30),
@табельный_номер_сотрудника nvarchar(10)
as
begin
-- Проверка на одинаковую запись
if exists(select * from составитель_галереи where
название_галереи = @название_галереи and табельный_номер_сотрудника = @табельный_номер_сотрудника)
begin
select'Ошибка: запись уже существует'
return
end
--проверка существования значения @название_галереи в таблице галерея
if not exists(select название_галереи from галерея where название_галереи = @название_галереи )
begin
select'Ошибка: галерея не существует'
return
end
--проверка существования значения @табельный_номер_сотрудника в таблице сотрудник
if not exists(select @табельный_номер_сотрудника from сотрудник where табельный_номер_сотрудника = @табельный_номер_сотрудника )
begin
select'Ошибка: сотрудник не существует'
return
end
insert into составитель_галереи(
название_галереи,
табельный_номер_сотрудника
)
values(
@название_галереи,
@табельный_номер_сотрудника
)
if @@rowcount>0
select'Запись успешно вставлена'
end
go
Пример использования:
declare @res nvarchar(50)