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

- Большие базы данных. Максимальный размер экземпляра базы данных 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