Станция
|
Станции,
по которым проходят маршруты поездов
|
Рейс
|
Точный
факт рейса, который будет проходить по определенному маршруту, на
определенном поезде
|
Локомотив
|
Транспорт,
принадлежащий определенной станции, который будут осуществлять тягу
несамоходных вагонов
|
Вагон
|
Состав
вагонов
|
Группа
сцепленных между собой вагонов, приводящихся в движение при помощи локомотива
|
Поезд
|
Сформированный
состав, состоящий из группы вагонов, с одним действующим локомотивом
|
3.1 Атрибуты
Для разработанных сущностей логической модели
«ЖД вокзал» были добавлены поля, описание которых представлено в табл. 2.
Таблица 2 - Поля сущностей модели
«Железнодорожный вокзал»
Имя
|
Тип
данных
|
Размер
|
Определение
|
Сущность
|
ID_STATION
|
NUMBER
|
-
|
Идентифицирующий
номер станции
|
STATIONS
|
NAME_STATION
|
VARCHAR2
|
25
|
Название
станции
|
STATIONS
|
REGION
|
VARCHAR2
|
25
|
Регион,
в котором находиться станция
|
STATIONS
|
ROAD
|
VARCHAR2
|
25
|
Название
дороги, к которой относиться станция
|
STATIONS
|
KEY_ECP
|
NUMBER
|
6
|
Код
ECP
|
STATIONS
|
ID_TRAVEL
|
NUMBER
|
-
|
Идентифицирующий
номер рейса
|
TRAVEL
|
ID_START_STATION
|
NUMBER
|
-
|
Номер
станции отправления
|
TRAVEL
|
ID_FINISH_STATION
|
NUMBER
|
-
|
Номер
станции прибытия
|
TRAVEL
|
DISTANCE
|
NUMBER
|
-
|
Расстояние
между станциями отправления и прибытия
|
TRAVEL
|
ID_TRAIN
|
NUMBER
|
-
|
Номер
поезда на маршруте
|
TRAVEL
|
DATE_OF_ARRIVAL
|
DATE
|
-
|
Дата
отправления поезда
|
TRAVEL
|
DATE_OF_DEPARTURE
|
DATE
|
-
|
TRAVEL
|
ID_WAGON
|
NUMBER
|
-
|
Идентифицирующий
номер вагона
|
WAGON
|
SERIAL_NUMBER
|
NUMBER
|
8
|
Серийный
номер вагона/локомотива
|
WAGON/LOCOMOTIVE
|
TYPE
|
VARCHAR2
|
15
|
Тип
вагона по классу
|
WAGON
|
NUMBER_OF_SEATS
|
NUMBER
|
2
|
Количество
мест в вагоне
|
WAGON
|
MANUFACTURER
|
VARCHAR2
|
20
|
Производитель
вагона/поезда
|
WAGON/LOCOMOTIVE
|
YEAR_OF_PRODUCTION
|
NUMBER
|
4
|
Год
производства вагона/поезда
|
WAGON/LOCOMOTIVE
|
ID_STATION
|
NUMBER
|
-
|
Станция
привязки вагона/локомотива
|
WAGON/LOCOMOTIVE
|
ID_LOCOMOTIVE
|
NUMBER
|
-
|
Идентифицирующий
номер локомотива
|
LOCOMOTIVE
|
CATEGORY
|
VARCHAR2
|
15
|
Категория
локомотива
|
LOCOMOTIVE
|
NUMBER_OF_SECTIONS
|
NUMBER
|
1
|
Количество
секций в локомотиве
|
LOCOMOTIVE
|
MOTIVE_POWER
|
VARCHAR2
|
2
|
Тип
питания локомотива
|
LOCOMOTIVE
|
ID_GROUP_OF_WAGON
|
NUMBER
|
-
|
Идентифицирующий
номер группы вагонов
|
GROUP_OF_WAGON
|
ID_WAGON
|
NUMBER
|
-
|
Номер
вагона, входящего в состав
|
GROUP_OF_WAGON
|
ID_TRAIN
|
NUMBER
|
-
|
Номер
состава
|
ID_TRAIN
|
NUMBER
|
-
|
Идентифицирующий
номер состава
|
TRAIN
|
ID_LOCOMOTIVE
|
NUMBER
|
-
|
Номер
локомотива, входящего в состав
|
TRAIN
|
3.2 Типы полей
Поля таблицы предназначены для
хранения в них данных. Это могут быть числа, текстовая информация, даты,
графические файлы и т. д. Для определения типа данных, размещаемых в поле,
используются тип поля, его ширина и количество знаков после запятой. Для их
ввода предназначены столбцы Туре (Тип), Length
<javascript:popupFieldHelp('32148220791946691','3086740732699440','Close')>
(Ширина) и Precision
<javascript:popupFieldHelp('32001221150504058','3086740732699440','Close')>
(Точность) вкладки Add Column (Поля) конструктора таблицы, либо указывается при
определении типа, например, NUMBER(8,2).
4. Первичные, внешние и
альтернативные ключи
При определении первичных и внешних ключей,
также как и при создании таблиц, необходимо учитывать связи типа
«предок-потомок» между таблицами. Так как внешний ключ - это атрибут первичного
ключа таблицы предка, то сначала надо определить все первичные ключи предков,
затем внешние и первичные ключи потомков, и так далее по иерархии. Ключи любого
типа определяются при помощи индексов различного типа.
В соответствии с правилом определения типа
ключа, для таблиц БД “ЖД вокзал” были определены следующие первичные, внешние и
альтернативные ключи:
. Сущность Stations содержит два ключа (рис.
4.1), первый из которых является первичный ключ и содержит суррогатный ключ
станции, вторым ключом является альтернативный ключ, содержащий атрибуты: имя
станции, регион расположения станции.
Рисунок 4.1 - Ключи сущности Stations
. Сущность Locomotive содержит два ключа (рис.
4.2), первый из которых является первичный ключ и содержит суррогатный ключ
локомотива, вторым ключом является альтернативный ключ, содержащий атрибуты:
серийный номер локомотива, год его производства.
Рисунок 4.2 - Ключи сущности Locomotive
. Сущность Wagon содержит два ключа (рис. 4.3),
первый из которых является первичный ключ и содержит суррогатный ключ вагона,
вторым ключом является альтернативный ключ, содержащий атрибуты: серийный номер
вагона, год его производства.
Рисунок 4.3 - Ключи сущности Wagon
. Сущность Group of wagons является сущностью
введенной для построения связи «многие ко многим», она содержит один первичный
ключ (рис 4.4)
Рисунок 4.3 - Ключи сущности Group of wagons
. В сущности Train определен только один
первичный ключ (рис 5.4).
Рисунок 4.5 - Ключи сущности Train
. В сущности Travel определены два ключа, первый
- первичный ключ, второй - альтернативный, образованный при помощи полей:
станция отправления, поезд маршрута, дата отправления.
Рисунок 4.5 - Ключи сущности Travel
Также в сущностях определены внешние ключи,
которые не представлены на рисунках выше. Сущности разрабатываемой БД имеют
следующие внешние ключи:
:_START_STATION - номер станции отправления;_FINISH_STATION
- номер станции прибытия;_TRAIN - поезд на данном маршруте.и
locomotive:_STATION - номер станции привязки локомотива, вагона.
Group of wagon:_WAGON - номер
вагона;
ID_TRAIN - номер поезда, в который входят
вагоны._LOCOMOTIVE - номер локомотива в данном поезде.
5. Определение правил ограничения
ввода
Задание. В соответствии с описанием модели ПО
установить правила, ограничивающие ввод значений. Проверить работу
установленных правил.
5.1 Ограничение сущности wagon
Атрибуту type в сущности wagon задано такое
условие правильности ввода, что тип вагона определяется одной из введенной
комбинацией букв 'СВ', 'СВМ', 'М', 'К', 'КБ', 'Пл', 'О', 'П', 'Б'.
CONSTRAINT "TYPEWAGON"
CHECK (TYPE IN ('СВ',
'СВМ',
'М',
'К',
'КБ',
'Пл',
'О',
'П',
'Б'))
ENABLE
Атрибуту number_of_seats в сущности wagon задано
такое условие правильности ввода, что количество мест в вагоне может задаваться
в промежутке 0 - 81.
CONSTRAINT "NUMBEROFSEATS"
CHECK (NUMBER_OF_SEATS BETWEEN 0 and 81) ENABLE
Проверка работы определённых в сущностях
ограничений, при вводе в таблицу значений, представленных на рисунке 5.1.
Рисунок 5.1 - Вводимые данные для тестирования
ограничений
будет выведено сообщение об ошибке (рис. 5.2),
т.к. количество мест не удовлетворяет ограничению:
Рисунок 5.2 - Ошибка, вызванная при обработке
ограничения
5.2 Ограничения, реализованные при
помощи триггера
С помощью триггеров были реализованы два
ограничения:
Ограничение, накладываемое на поле
YEAR_OF_PRODUCTION в сущностях Wagon и Locomotive, назначение этого триггера
заключается в том, чтобы год производства вагона находился в интервале от
1950г. до текущего года. Листинг триггера
для
сущности
Wagon:
CREATE OR REPLACECHECK_YEAR_WAGONINSERT
OR UPDATE OF YEAR_OF_PRODUCTION ON WAGONEACH ROW:NEW.YEAR_OF_PRODUCTION >=
EXTRACT(YEAR FROM SYSDATE) OR :NEW.YEAR_OF_PRODUCTION <= 1950
THEN_APPLICATION_ERROR(-20000, 'YEAR OF PRODUCTION FAILED TO SUCCESSFULLY
UPDATE');
END IF;;
Ограничение, накладываемое на поля
DATE_OF_ARRIVAL DATE_OF_DEPARTURE в сущности Travel, необходимое для контроля
даты отправления и прибытия поездов, т.е. дата отправления поезда не должна
быть позднее даты прибытия поезда и наоборот:
CREATE OR REPLACECHECK_DATE_OF_ARRIVALINSERT
OR UPDATE OF DATE_OF_ARRIVAL ON TRAVELEACH ROW:NEW.DATE_OF_ARRIVAL <=
:NEW.DATE_OF_DEPARTURE THEN_APPLICATION_ERROR(-20001, 'ERROR DATE');
END IF;;
6. Объект - последовательность
Задание: Создать последовательность для
автоматической генерации первичных ключей в соответствии с правилами.
Для сущности stations была определена
последовательность, первым генерируемым значением которой является 11, а шаг
инкрементирования составляет 1:
CREATE SEQUENCE
"STATIONS_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 11 NOCACHE NOORDER NOCYCLE
Для организации автоматической генерации ключей
создан триггер, который выполняются каждый раз перед добавлением данных в
сущность. Пример триггера
для
сущности
Stations:
CREATE OR REPLACE TRIGGER
"BI_STATIONS"insert on "STATIONS"each
row"STATIONS_SEQ".nextval into :NEW.ID_STATION from dual;;
/TRIGGER "BI_STATIONS"
ENABLE
/
Подобные триггеры определены для всех сущностей
базы данных.
7. Организация ввода данных
Задание: Заполнить файлы БД записями. При
формировании записей соблюдать следующие требования:
. Данные не должны носить фривольный характер
. Обязательно соблюдать ограничения ссылочной целостности.
Это означает, что атрибуты, отмеченные в подчиненной таблице спецификатором FK
(внешний ключ), не могут принимать значения, которые не существуют среди
значений соответствующего атрибута родительской таблицы. На диаграмме линия
связи «предок-потомок» отмечается точкой со стороны подчиненной таблицы.
. Подчиненная таблица должна содержать несколько
записей, соответствующих одной записи родительской таблицы.
Для соблюдения правил ссылочной целостности
рекомендуется такая же последовательность заполнения таблиц от предков к
потомкам.
Соблюдая заданные требования были введены
следующие данные в таблицы:
7.1 Данные сущности Stations
Рисунок 7.1 - Данные сущности Stations
7.2 Данные сущности Locomotive
Рисунок 7.2 - Данные сущности Locomotive
7.3 Данные сущности Wagon
Рисунок 7.3 - Данные сущности Wagon
7.4 Данные сущности Travel
Рисунок 7.4 - Данные сущности Travel
7.5 Данные сущности Train
Рисунок 7.5 - Данные сущности Train
7.6 Данные сущности Group of wagon
Рисунок 7.6 - Данные сущности Group of wagon
8. Запросы
Когда требуется просмотреть, добавить, изменить
или удалить данные из базы данных, удобно использовать запросы. Запросы можно
использовать для фильтрации данных, выполнения расчетов на основе данных и
отображения сводных данных. Кроме того, запросы позволяют автоматизировать
выполнение многих задач управления данными и просматривать изменения в данных
перед их использованием.
Запрос представляет собой обращение к данным для
получения информации и выполнения действий с данными. Запрос можно использовать
для получения ответа на простой вопрос, выполнения расчетов, объединения данных
из разных таблиц или даже добавления, изменения или удаления данных в таблице.
Запросы, используемые для извлечения данных из таблицы или выполнения расчетов,
называются запросами на выборку. Запросы, используемые для добавления,
изменения или удаления данных, называются запросами на изменение.
Запрос 1. Найти вагоны типа «П».
SELECT ID_WAGON, SERIAL_NUMBER,
YEAR_OF_PRODUCTIONWAGONTYPE = 'К';
Рисунок 8.1 - Результат работы Запроса 1
Запрос 2. Найти вагоны произведенные с 1977 по
1981.
SELECT ID_WAGON, SERIAL_NUMBER,
YEAR_OF_PRODUCTIONWAGON(YEAR_OF_PRODUCTION BETWEEN 1977 AND 1981)NOT
YEAR_OF_PRODUCTION IN (1977, 1981);
Рисунок 8.2 - Результат работы Запроса 2
Запрос 3. Найти сведения о вагонах с количеством
пассажирских мест 26 и 12.
SELECT ID_WAGON, SERIAL_NUMBER,
NUMBER_OF_SEATSWAGONNUMBER_OF_SEATS IN (26, 12);
Рисунок 8.3 - Результат работы Запроса 3
Запрос 4. Найти названия производителей
локомотивов и количество произведенных ими локомотивов.
SELECT MANUFACTURER,
COUNT(ID_LOCOMOTIVE)LOCOMOTIVEBY MANUFACTURER;
Рисунок 8.4 - Результат работы Запроса 4
Запрос 5. Найти названия станций и количество
рейсов для этих станций.
SELECT STATIONS.NAME_STATION, COUNT
(TRAVEL.ID_START_STATION)STATIONS, TRAVELSTATIONS.ID_STATION = TRAVEL.ID_START_STATION
GROUP BY NAME_STATION;
Рисунок 8.5 - Результат работы Запроса 5
Запрос 6. Найти поезда, которые совершили более
2 рейсов за текущий месяц.
SELECT TRAIN.ID_LOCOMOTIVE, count
(TRAVEL.ID_START_STATION)TRAVEL,TRAINTRAVEL.ID_TRAIN =
TRAIN.ID_TRAINextract(month from TRAVEL.DATE_OF_ARRIVAL) = extract(month from
sysdate)BY ID_LOCOMOTIVECOUNT(*)>2;
Рисунок 8.6 - Результат работы Запроса 6
Запрос 7. Найти станцию, в которую пришло больше
всего пездов.
SELECT
STATIONS.NAME_STATIONSTATIONS,
(SELECT ID_STATION,
SUM(TRAVEL.ID_FINISH_STATION) MAX_FINISHSTATIONS, TRAVELSTATIONS.ID_STATION =
TRAVEL.ID_FINISH_STATIONBY ID_STATION)
tt.ID_STATION=STATIONS.ID_STATIONMAX_FINISH=(SELECT
MAX(MAX_FINISH)(ID_STATION,SUM(TRAVEL.ID_FINISH_STATION) AS MAX_FINISH FROM
STATIONS, TRAVELSTATIONS.ID_STATION = TRAVEL.ID_FINISH_STATION
GROUP BY ID_STATION))
Рисунок 8.7 - Результат работы Запроса 7
Запрос 8. Найти локомотив, который не входит ни
в один состав.
SELECT SERIAL_NUMBER FROM
LOCOMOTIVESERIAL_NUMBER FROM LOCOMOTIVEID_LOCOMOTIVE IN
(SELECT DISTINCT ID_LOCOMOTIVETRAIN)
Рисунок 8.8 - Результат работы Запроса 8
Запрос 9. Найти станции к которым привязан
хотябы один вагон.
SELECT STATIONS.NAME_STATION,
count(WAGON.ID_WAGON)STATIONS,WAGONSTATIONS.ID_STATION = WAGON.ID_STATIONBY
NAME_STATIONCOUNT(*)>=1;
Рисунок 8.9 - Результат работы Запроса 9
Заключение
В результате выполнения курсового проекта была
построена концептуальная модель процесса «Железнодорожный вокзал» и описана
структура базы данных.
Также были построены таблицы с детальным
описанием всех сущностей, атрибутов и доменов, созданы ключи, ограничения,
последовательности, запросы. По данной предметной области и ее модели возможна
реализация этой модели в СУБД и реализация клиентского приложения.
Список использованных источников
1. А.С.
Деревянко. Язык SQL в диалектах Oracle и IBM DB2. Учебное пособие, 2003г.
. ГОСТ
Р ИСО МЭК ТО 10032-2007: Эталонная модель управления данными (идентичен ISO/IEC
TR 10032:2003 Information technology - Reference model of data management)
. ОС
ТУСУР 6.1-97 Система образовательных стандартов. Работы студенческие учебные и
выпускные квалификационные.
. Базы
данных. МФПА. [Электронный ресурс] <http://inftis.narod.ru/db/db-10.htm>
Приложение
запросы, реализующие сущности
проектируемой БД
база данная запрос сущность
Сущность локомотив:TABLE
LOCOMOTIVE
(ID_LOCOMOTIVE
NUMBER(*,0),VARCHAR2(15) NOT NULL ENABLE,_OF_SECTIONS NUMBER(1,0) NOT NULL
ENABLE,_POWER VARCHAR2(17) NOT NULL ENABLE,VARCHAR2(10) NOT NULL
ENABLE,_OF_PRODUCTION DATE,_STATION NUMBER(*,0) NOT NULL ENABLE,_NUMBER
NUMBER(8,0) NOT NULL ENABLE,KEY (ID_LOCOMOTIVE) ENABLE,ID_STATION_FK FOREIGN
KEY (ID_STATION)STATIONS (ID_STATION) ENABLE
)
Сущность вагон:TABLE
WAGON
(ID_WAGON NUMBER(*,0),_NUMBER
NUMBER(8,0) NOT NULL ENABLE,VARCHAR2(15) NOT NULL ENABLE,VARCHAR2(15) NOT NULL
ENABLE,_OF_SEATS NUMBER(2,0) NOT NULL ENABLE,VARCHAR2(10) NOT NULL
ENABLE,_OF_PRODUCTION DATE NOT NULL ENABLE,_STATION" NUMBER(*,0) NOT NULL
ENABLE,KEY (ID_WAGON) ENABLE,WAGON_AK UNIQUE (SERIAL_NUMBER,
YEAR_OF_PRODUCTION) ENABLE,ID_STATION_FK_WAGON FOREIGN KEY (ID_STATION)STATIONS
(ID_STATION) ENABLE
)
Сущность поездTABLE
TRAIN
(ID_LOCOMOTIVE NUMBER(*,0) NOT NULL
ENABLE,_TRAIN NUMBER(*,0) NOT NULL ENABLE,KEY (ID_TRAIN) ENABLE,LOCOMOTIVE_FK
FOREIGN KEY (ID_LOCOMOTIVE)LOCOMOTIVE (ID_LOCOMOTIVE) ENABLE
)
Сущность станцияTABLE
"STATIONS"
("ID_STATION" NUMBER(*,0)
NOT NULL ENABLE,
"NAME_STATION" VARCHAR2(25)
NOT NULL ENABLE,
"REGION" VARCHAR2(25) NOT
NULL ENABLE,
"ROAD" VARCHAR2(25) NOT
NULL ENABLE,
"KEY_ECP" NUMBER(6,0) NOT
NULL ENABLE,KEY ("ID_STATION") ENABLE,"STATION_AK" UNIQUE
("NAME_STATION", "KEY_ECP") ENABLE
)
Сущность группа
вагонов:TABLE
GROUP_OF_WAGON
(ID_GROUP_OF_WAGON
NUMBER(*,0),_WAGON NUMBER(*,0) NOT NULL ENABLE,_TRAIN NUMBER(*,0),KEY
(ID_GROUP_OF_WAGON) ENABLE,ID_WAGON_FK FOREIGN KEY (ID_WAGON)WAGON (ID_WAGON)
ENABLE,ID_TRAIN_FK FOREIGN KEY (ID_TRAIN)TRAIN (ID_TRAIN) ENABLE
)
Сущность рейс:TABLE
TRAVEL
(ID_TRAVEL
NUMBER(*,0),_START_STATION NUMBER(*,0) NOT NULL ENABLE,_FINISH_STATION
NUMBER(*,0) NOT NULL ENABLE,_TRAIN NUMBER(*,0) NOT NULL ENABLE,NUMBER(4,1) NOT
NULL ENABLE,_OF_DEPARTURE DATE NOT NULL ENABLE,_OF_ARRIVAL DATE NOT NULL
ENABLE,KEY (ID_TRAVEL) ENABLE,START_STATION_FK FOREIGN KEY
(ID_START_STATION)STATIONS (ID_STATION) ENABLE,FINISH_STATION_FK FOREIGN KEY
(ID_FINISH_STATION)STATIONS (ID_STATION) ENABLE,TRAIN_FK FOREIGN KEY
(ID_TRAIN)TRAIN (ID_TRAIN) ENABLE
)