Ирина Эланс

Автор который поможет с любыми образовательными и учебными заданиями

База данных Расписание пассажирского транспорта

1 Анализ предметной области

1.1 Деловой регламент

Понятие “предметная область” (ПО) соответствует точке зрения потребителей информации на объекты, при которой выделяются только те свойства объектов и взаимосвязи между ними, которые представляют определенную прагматическую ценность и должны фиксироваться в базе данных. Таким образом, предметная область – это абстрактная картина реального мира, определенная часть которого фиксируется в качестве модели фрагмента действительности. В каждый момент времени ПО находится в одном из состояний, которое характеризуется совокупностью объектов и их взаимосвязей. Если объекты образуют объектное ядро, то совокупность взаимосвязей отражает структуру фрагмента действительности.

Конструирование базы данных «Расписание пассажирского транспорта» начинается с исследования и описания предметной области.

Главная цель создания базы данных «Расписание пассажирского транспорта» состоит в том, чтобы хранить и выдавать информацию о транспорте, о расписании движения этого транспорта и о водителях различных категорий транспорта.

С помощью базы данных «Расписание пассажирского транспорта» можно будет получать следующую информацию:

о транспорте (тип транспорта, № автобуса или маршрутного такси, модель транспорта, а так же количество работающего транспорта);
о расписании (№ маршрута, пункт отправления, пункт назначения);
о водителях транспорта (Ф.И.О., стаж работы, адрес, оклад, категорию транспорта, № маршрута, на котором работает).

1.2 Функциональная структура

Кратко функции БД изображены на функциональной структуре (Рисунок 1.1)

Как видно из рисунка 1.1, «Расписание пассажирского транспорта» имеет возможности:

- получение подробной информации о водителях;

- выдача зарплаты водителям;

- получение подробной информации о маршрутах и остановках;

- получение информации о нужном клиенту маршруте;

1.3 Диаграмма потоков данных

Функциональная структура системы представляет собой набор диаграмм потоков данных (далее - ДПД), которые описывают смысл операций и ограничений. ДПД отражает функциональные зависимости значений, вычисляемых в системе, включая входные значения, выходные значения и внутренние хранилища данных. ДПД - это граф, на котором показано движение значений данных от их источников через преобразующие их процессы к их потребителям в других объектах. ДПД содержит процессы, которые преобразуют данные. Потоки данных, которые переносят данные. Активные объекты, которые производят и потребляют данные, и хранилища данных, которые пассивно хранят данные.

Процесс преобразует значения данных. Процессы самого нижнего уровня представляют собой функции без побочных эффектов. Процесс может иметь побочные эффекты, если он содержит нефункциональные компоненты, такие как хранилища данных или внешние объекты.

Поток данных соединяет выход объекта (или процесса) со входом другого объекта (или процесса). Он представляет промежуточные данные вычислений. Поток данных изображается в виде стрелки между производителем и потребителем данных, помеченной именами соответствующих данных; примеры стрелок, изображающих потоки данных, представлены на рисунке ДПД.

Хранилище данных - это пассивный объект в составе ДПД, в котором данные сохраняются для последующего доступа. Хранилище данных допускает доступ к хранимым в нем данным в порядке, отличном от того, в котором они были туда помещены. Агрегатные хранилища данных, как например, списки и таблицы, обеспечивают доступ к данным в порядке их поступления, либо по ключам. Диаграмма ПД курсового проекта представлена на рисунке 1.2.

На функциональной диаграмме листовыми будут являться функции:

Получения информации о маршрутах;
Получение информации о водителях;
Получение информации о транспорте;
Поучение информации о зарплате;
Получение информации об остановках.

Рисунок 1.1 Функциональная структура

Рисунок 2. Диаграмма потоков данных

1.4 Выделение информационных объектов и их атрибутов

В результате детального анализа предметной области, построения функциональной структуры и схемы потоков данных были выделены следующие объекты:

1 Drivers – водители, управляющие пассажирским транспортом;

driver_id (индикационный номер);
familia (фамилия водителя)
ima (имя водителя);
otchestvo (отчество водителя);
stag_raboty (количество лет работы водителя);
adress (адрес, по которому проживает водитель);
zarplata_id (зарплата водителя);
cat_id (категория транспорта, которым управляет водитель);
marshrut_id (маршрут, по которому ездит водитель).

2 Marshryti – маршруты по городу;

marshryt_id (номер маршрута);
pynkt_otpravleniya (пункт отправления);
pynkt_naznacheniya (пункт прибытия).

3 Транспорт – имеющийся транспорт;

transport_id (уникальный индификатор модели);
model (модель транспорта);
cat_id (категория, к которой относится транспорт);
kolvo (количество транспорта).

4 Raspisanie – расписание прибытия транспорта на остановки.

rasp_id (уникальный индификатор строки расписания);
number_ost (номер остановки);
marshryt_id (номер маршрута, по которому работает пассажирский транспорт).
time (время прибытия транспорта на остановку)

5 Zarplata – зарплата выдаваемая водителям.

zarplata_id – уникальный индификатор зарплаты;
oklad – зарплата.

6 Ostanovki – остановки транспорта на маршруте.

numder_ost (номер остановки);
nazvanie_ost (название остановки).

7 Category – категории, имеющегося транспорта.

cat_id (уникальный индификатор категории);
catname (название категории).

2 Концептуальная модель

При построении модели сущность-связь на начальном этапе каждый информационный объект заменяем сущностью, при этом каждое свойство объекта становится атрибутом сущности.

Концептуальная модель базы данных “Расписание пассажирского транспорта” иллюстрирует влияние объектов друг на друга и представлена на рисунке 2.1. Прямоугольникам соответствуют экземпляры объектов (сущностей), а стрелкам – связи, которые представлены основными элементами информации:

- тип связи;

- родительская и дочерняя (зависимая) сущности;

- мощность связи.

Мощность связи представляет собой отношение количества экземпляров родительской сущности к соответствующему количеству экземпляров дочерней сущности.

На рисунке 2.1 Буква «N»показывает максимальное количество экземпляров сущности, с которым может быть связан один экземпляр другой сущности.

Рисунок 2.1 Концептуальная модель

Между объектами drivers и category мощность связи N:1, то есть один водитель может управлять только одной категорией пассажирского транспорта, но к одной категории может принадлежать много водителей;

Между объектами drivers и zarplata мощность связи N:1, то есть один водитель может получать только одну зарплату, но одна зарплата может быть у разных водителей;

Между объектами drivers и marshruti мощность связи N:1, то есть один водитель может работать только на одном маршруте, но на маршруте могут работать много водителей;

Между объектами raspisanie и marshruti мощность связи N:1, то есть по одну расписанию ездит только один маршрут, но один маршрут может ездить по нескольким расписаниям (имеется введу расписание для каждой остановки);

Между объектами transport и category мощность связи N:1, то есть один маршрут может принадлежать только одной категории, но к одной категории будут относиться много транспортных средств;

Между объектами raspisanie и ostanovki мощность связи N:1, то есть у одной остановки может быть много расписаний, но у одного расписания может быть только одна остановка.

3 Логическое моделирование

3.1 Построение логической модели

Логическая модель – это взаимосвязанные реляционные таблицы. Одна из задач проектирования базы данных состоит в обеспечении способа идентификации отдельных строк таблицы. Строки отличаются друг от друга по значению первичного ключа таблицы. Первичный ключ – это столбец или набор столбцов, однозначно определяющий строку.

Первичные ключи: для таблиц drivers, category, transport, raspisanie, zarplata, ostanovki, marshryti первичными ключами являются соответственно: driver_id, cat_id, transport_id, rasp_id, zarplata_id, numder_ost, marshrut_id.

На рисунке 3.1 логическая модель базы данных «Расписание пассажирского транспорта»:

Рисунок 3.1 Логическая модель

3.2 Целостность данных

3.2.1 Целостность объекта

Ограничения целостности объектов защищают отношения (таблицы-объекты) от неправильного внесения изменений, связанных с удалением существующих картежей и вставкой новых.

Например, в базе данных в отношение «Расписание» при вставке информации о новой остановки, необходимо сначала вставить значение в поле, являющееся первичным ключом («id»), а затем уже заносить информацию в остальные поля. Аналогично и с удалением при удалении картежа из таблицы, необходимо сначала удалить информацию из вторичных атрибутов, а затем уже удалять значение первичного ключа. Целостность объекта реализуется самой СУБД, и обычно пользователю нет необходимости об этом беспокоиться.

3.2.2 Целостность приложения

Ограничения целостности приложения определяют отношения, в которые пользователь может вносить изменения, связанные с удалением, обновлением и вставкой. Ведь в базе данных существуют и такие отношения, в которые изменения вноситься не должны (по крайней мере, пользователем) – эти отношения формируются один раз при создании базы данных и далее в течение долгого времени данные в них не меняются. Эти отношения называются «справочниками» (точнее, некоторые из них).

Ограничения ссылочной целостности формируются при проектировании приложения (клиентской части) к базе данных.

4 Выбор СУБД

Для реализации базы данных «Расписание пассажирского транспорта» была выбрана СУБД Oracle 11g . Это объясняется следующими возможностями данной СУБД:

- Поддержка языка SQL, который достаточно прост в обращении и позволяет без особых затрат времени извлекать любую информацию из базы данных;

- Real Application Cluster (RAC) обеспечивает работу одного экземпляра базы данных на нескольких узлах grid, позволяя управлять нагрузкой и гибко масштабировать систему в случае необходимости;

- Automatic Storage Management (ASM) позволяет автоматически распределять данные между имеющимися ресурсами систем хранения данных, что повышает отказоустойчивость системы и снижает общую стоимость владения (TCO);

- Производительность. Oracle Database 11g позволяет автоматически управлять уровнями сервиса и тиражировать эталонные конфигурации в рамках всей сети;

- Простые средства разработки. Новый инструмент разработки приложений HTML DB позволяет простым пользователям создавать эффективные приложения для работы с базами данных в короткие сроки;

- Самоуправление. Специальные механизмы Oracle Database 11g позволяют самостоятельно перераспределять нагрузку на систему, оптимизировать и корректировать SQL-запросы, выявлять и прогнозировать ошибки;

- Большие базы данных. Максимальный размер экземпляра базы данных Oracle может достигать 8 экзабайт;

- Недорогие серверные системы. Oracle Database 11g может использовать недорогие однопроцессорные компьютеры или модульные системы из "серверов-лезвий";

- Обеспечение транзакционной целостности, связана с организацией одновременного доступа значительного количества пользователей к данным размещенным на физическом носителе. Доступ производится в процессе выполнения транзакций представляющей собой логическую связь последовательных запросов и операций манипулирования данных;

- Буферирование. На обработку первого запроса уходит больше времени, чем на все последующие запросы это характеризуется тем, что их повторное использование позволяет исключить физический ввод данных с дискового устройства и уменьшает время выполнения запроса, ограничение объема буфера приводит к перманентному вытеснению из него раннее считанных блоков.

- Ряд вышеперечисленных возможностей, выделяет СУБД Oracle 11g как наиболее подходящую для реализации нашей базы данных по предоставляемым возможностям.

5 Физическая модель

Физическая модель данных представлена реляционными таблицами, в которых в виде кортежей реляционных отношений хранится информация. Для хранения информации выбраны Oracle 10g таблицы, как удобные в работе и распространенные. Таблицы Oracle 10g поддерживают многие приложения, что обеспечивает более высокую универсальность системы.

Таблица 5.1 Типы данных

Тип	Наименование типа	Размер (байты)	Содержание
Текстовый	Varchar2	каждый символ по 1	Буквы, цифры, спец. символы(%, &, #)
Числовой	Integer	4	Планируется выполнять арифме тические операции над значениями из этого поля
Денежный	Number	8	Планируется выполнять арифме тические операции над значениями из этого поля

Для создания таблиц использовались следующие скрипты:

create table zarplata

(zarplata_id integer primary key not null,

oklad number)

create table ostanovki

(number_ost integer primary key not null,

nazvanie_ost varchar2 (200) )

create table category

(cat_id integer primary key not null,

catname varchar2 (200) )

create table marshruti

(marshrut_id integer primary key not null,

pynkt_otpravleniya varchar2 (200),

pynkt_naznacheniya varchar2 (200))

create table raspisanie

(rasp_id integer primary key not null,

number_ost integer,

time varchar2 (200)

foreign key (number_ost) references ostanovki (number_ost),

marshrut_id integer,

foreign key (marshrut_id) references marshruti (marshrut_id))

create table transport

(transport_id integer primary key not null,

model varchar2 (200),

cat_id integer,

foreign key (cat_id) references category (cat_id))

create table drivers

(driver_id integer primary key not null,

familia varchar2 (200),

ima varchar2 (200),

otchestvo varchar2 (200),

stag_raboty varchar2 (200),

adress varchar2 (400),

zarplata_id integer,

foreign key (zarplata_id) references zarplata (zarplata_id),

cat_id integer,

foreign key (cat_id) references category (cat_id),

marshrut_id integer,

foreign key (marshrut_id) references marshruti (marshrut_id)))

5.1 Нормализация

Первая нормальная форма (1NF)

Отношение находится в первой нормальной форме тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.

В реляционной модели отношение всегда находится в первой нормальной форме по определению понятия отношение. Что же касается таблиц в существующих реляционных СУБД (SQL-СУБД), то они могут не быть правильными отношениями и, соответственно, не находиться в 1NF.

Вторая нормальная форма (2NF)

Отношение находится во второй нормальной форме, если оно находится в первой нормальной форме, и при этом любой его атрибут, не входящий в состав потенциального ключа, функционально полно зависит от каждого возможного ключа. Функционально полная зависимость означает, что атрибут функционально зависит от всего составного потенциального ключа, но при этом не находится в функциональной зависимости от какой-либо из входящих в него частей. Или другими словами: в 2NF нет неключевых атрибутов, зависящих от части составного ключа.

Третья нормальная форма (3NF)

Согласно определению Кодда, таблица находится в 3НФ тогда и только тогда, когда выполняются следующие условия:

Отношение R (таблица) находится во второй нормальной форме;

Каждый непервичный атрибут R находится в нетранзитивной (то есть прямой) зависимости от каждого ключа R.

Таким образом, отношение находится в 3NF тогда и только тогда, когда оно находится во 2NF и отсутствуют транзитивные зависимости неключевых атрибутов от ключевых. Транзитивной зависимостью неключевых атрибутов от ключевых называется следующая: A → B и B → C, где A — набор ключевых атрибутов (ключ), B и С — различные множества неключевых атрибутов.

При решении практических задач в большинстве случаев третья нормальная форма является достаточной. Процесс проектирования реляционной базы данных, как правило, заканчивается приведением к 3NF.

В текущем проекте база не требует нормализации. Так как нет ни транизитивных зависимостей, ни не полных функциональных зависимостей, а также все поля имеют только одно значение.

6 Проектирование и реализация Sql-запросов

6.1 Описание средств, использованных при реализации

Для создания базы данных, сначала создаются таблицы с помощью средств, предлагаемых СУБД Oracle 11g :

Создание таблицы помощью графического инструментального средства конфигурирования Oracle Database Configuration Assistant (DBCA);
Создание таблицы шаблонов баз данных (database templates),;
Создание таблицы путём ввода данных.

При создании таблиц был использован 1-й вариант, как наиболее удобный из всех предлагаемых. Сначала создаётся каркас таблицы, отмечаются ключевые поля, устанавливаются типы данных для каждого поля, а уже потом таблицы заполняются макетными данными (приведены в приложении A).

После того, как таблицы созданы, необходимо их связать по ключевым полям.

Уже на данном этапе можно сформулировать достаточно большое количество запросов, которые понадобятся в дальнейшем при создании форменного приложения.

При создании запросов в СУБД Oracle 11g был использован режим SQL. Это объясняется удобством и простотой языка SQL, который представляет достаточно богатый набор средств для формирования запросов.

6.2 Тексты SQL-запросов и результаты их выполнения

Ниже приведены все SQL-запросы, использованные в процессе создания информационной системы.

Запрос 1: вывести всех водителей, работающих по 1 маршруту;

SELECT *
FROM drivers
WHERE marshrut_id ='1'

Рис.6.2.1 Результат запроса 1

Запрос 2: вывести фамилии водителей, у которых стаж работы больше 5-ти лет:

SELECT familia
FROM drivers
WHERE stag_raboty >'5'

Рис. 6.2.2 Результат запроса 2

Запрос 3: вывести всех водителей, имена которых начинаются на букву «С»:

SELECT *
FROM drivers
WHERE ima Like 'C%'

Рис. 6.2.3 Результат запроса 3

Запрос 4: вывести всех водителей со стажем работы от 3 до 6 лет:

SELECT *

FROM drivers
WHERE stag_raboty between '3' and '6'

Рис. 6.2.4 Результат запроса 4

Запрос 5: вывести все маршруты, идущие на поселок Донской:

SELECT *
FROM marshruti
WHERE pynkt_naznacheniya = 'п. Донской'

Рис. 6.2.5 Результат запроса 5

Запрос 6: вывести все автобусы:

SELECT *
FROM transport
WHERE cat_id = '1'

Рис. 6.2.6 Результат запроса 6

Запрос 7: удалить водителя с фамилией Тимофеева

DELETE FROM drivers WHERE familia='Алолоев'

Рис. 6.2.7 Результат запроса 7

Запрос 8: вывести все маршруты с автовокзала:

select *
from marshruti
where pynkt_otpravleniya = 'Автовокзал'

Рис. 6.2.8 Результат запроса 8

Запрос 9: вывести модели транспортных средств, которых в наличии больше одного:

select *
from transport
where kolvo > '1'

Рис. 6.2.9 Результат запроса 9

Запрос 10: вывести водителей с зарплатой 11000:

select *
from drivers
where zarplata_id = (select zarplata_id from zarplata where oklad = 11000)

Рис. 6.2 10 Результат запроса 10

Запрос 11: вывести пункт отправления и пункт назначения маршрута через дефис:

SELECT pynkt_otpravleniya||' - '||pynkt_naznacheniya
FROM marshruti

Рис. 6.2.11 Результат запроса 11

Запрос 12: вывести суммарную зарплату всех водителей:

select sum(zarplata.oklad)
from drivers, zarplata

Рис. 6.2.12 Результат запроса 12

Запрос 13: вывести откуда отправляются маршруты и их количество:

select count(*) AS kolvo, pynkt_otpravleniya
from marshruti
group by pynkt_otpravleniya

Рис. 6.2.13 Результат запроса 13

Запрос 14: вывести работников которые зарабатывают больше 8000 руб:

Select *
from drivers
where zarplata > 1

Рис. 6.2.14 Результат запроса 14

Запрос 15: вывести среднюю зарплату водителей:

select (sum (oklad)/count(*))
from drivers, zarplata

Рис. 6.2.15 Результат запроса 15

Запрос 16: вывести всех водителей, чья зарплата превышает зарплату Туниядцева:

Select familia
from drivers
where zarplata_id = ALL (

select zarplata_id
from zarplata
where oklad > '11000')

Рис. 6.2.16 Результат запроса 16

Запрос 17: вывести всех водителей транспортных средств по алфавиту:

SELECT familia
FROM drivers
ORDER BY familia

Рис. 6.2.17 Результат запроса 17

Запрос 18: вывести водителей, у которых зарплата больше 11000 и стаж работы больше 5-ти лет:

select *
from drivers
where zarplata_id > 2 and stag_raboty > '5'

Рис. 6.2.18 Результат запроса 18

Запрос 19: вывести все пункты отправления:

select distinct pynkt_otpravleniya
from marshruti

Рис. 6.2.19 Результат запроса 19

Запрос 20: подсчитать количество транспорта, относящегося к каждой категории :

select model, count (category.cat_id)
from transport, category
where transport.cat_id = category.cat_id
group by model

Рис. 6.2.20 Результат запроса 20

Запрос 21:вывести фамилию и имя водителей, которые отправляются с Автовокзала:

SELECT familia, ima
from drivers
where marshrut_id IN(

select marshrut_id
from marshruti
where pynkt_otpravleniya='Автовокзал')

Рис. 6.2 21 Результат запроса 21

Запрос 22:вывести остановки, через которые проходит хотя бы один маршрут:

SELECT ostanovki.nazvanie_ost
FROM ostanovki
WHERE EXISTS(

SELECT *

FROM raspisanie
WHERE ostanovki.number_ost = raspisanie.number_ost)

Рис. 6.2.22 Результат запроса 22

Запрос 23: вывести номер маршрута, на котором больше двух остановок:

SELECT COUNT (number_ost), m.marshrut_id, pynkt_otpravleniya||' - '||pynkt_naznacheniya
FROM marshruti m
JOIN raspisanie r ON m.marshrut_id = r.marshrut_id
GROUP BY m.marshrut_id, pynkt_otpravleniya, pynkt_naznacheniya
HAVING COUNT (number_ost) >2

Рис. 6.2.23 Результат запроса 23

Запрос 24: вывести остановки, через которые не проходит ни одного маршрута:

SELECT ostanovki.nazvanie_ost
FROM ostanovki
WHERE NOT EXISTS(SELECT * FROM raspisanie
WHERE ostanovki.number_ost = raspisanie.number_ost)

Рис. 6.2.24 Результат запроса 24

Запрос 26:вывести все маршруты в порядке убывания номера:

SELECT *
FROM marshruti
ORDER BY marshrut_id DESC

Рис. 6.2.26 Результат запроса 26

Запрос 27:вывести маршруты, на которых больше всего остановок:

SELECT marshrut_id,

pynkt_otpravleniya||' - '||pynkt_naznacheniya

FROM marshruti

NATURAL JOIN raspisanie
GROUP BY marshrut_id, pynkt_otpravleniya, pynkt_naznacheniya

Рис. 6.2.27 Результат запроса 27

Запрос 28:вывести фамилию, номер маршрута и адрес сотрудника, живущего на Донском:

SELECT m.marshrut_id,

d.familia,

d.adress

FROM marshruti m
JOIN drivers d ON m.marshrut_id = d.marshrut_id
WHERE d.adress IN ('п. Донской')

Рис. 6.2.28 Результат запроса 28

Запрос 29: подсчитать количество водителей и остановок:

SELECT COUNT( driver_id ) as summa
FROM drivers
UNION
SELECT COUNT( number_ost ) as summa
FROM ostanovki
ORDER BY summa

Рис. 6.2.29 Результат запроса 29

Запрос 30: вывести ID и фамилию водителя, работающего на первом маршруте, а также названия и номера остановок этого маршрута:

SELECT d.driver_id AS ID, d.familia as nazvanie, m.marshrut_id
FROM drivers d, marshruti m
WHERE d.marshrut_id=m.marshrut_id
AND m.marshrut_id = 1
UNION
SELECT o.number_ost AS Nazvanie, nazvanie_ost as nazvanie, m.marshrut_id
FROM ostanovki o, marshruti m, raspisanie r
WHERE o.number_ost=r.number_ost
AND r.marshrut_id=m.marshrut_id
AND m.marshrut_id = 1

Рис. 6.2.30 Результат запроса 30

6.3 Клиентское приложение

Данное приложение было написано с помощью среды программирования Visual Studio 2012

База данных Расписание пассажирского транспорта 📙 Курсовая → 🆔 33395