Информационная система таксопарка

Общая длина строки: 12 байт

Число строк: ~ 2500

Общий объем требуемой  памяти: ~ 30000  байт

 

Таблица 16. Схема отношения Улицы

Имя поля	Тип, длина	Примечание
Street_id	int	4
Street_name	varchar (22)	22

Общая длина строки: 26 байт

Число строк: ~ 150

Общий объем требуемой  памяти: ~ 3900  байт

 

Таблица 17. Схема отношения Материальное средство

Имя поля	Тип, длина	Примечание
Material_id	int	4
Sotr_id	int	4
stoimost	money	8
Kol_vo	int	4
Opisanie	Varchar(20)	20
naznach	Varchar(50)	50

Общая длина строки: 90 байт

Число строк: ~ 30

Общий объем требуемой  памяти: ~ 2700  байт

 

Таким образом, из полученных данным можно рассчитать приблизительный  максимальный объем базы данных. Объем  будет равен сумме объемов  всех таблиц:

3060+260+130+1440+2760+288+3360+144+144+600+618675+320+146000+960+3000+3900+2700=785 041 (байт)

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

Логическое проектирование

 

Задача логического проектирования состоит в создании реляционных  табличных структур на языке DDL.

Меры по обеспечению безопасности.

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

 

Создание пользователей, логинов и задание им паролей.

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

go

CREATE LOGIN Admin WITH Password='admin'

go

Create User administrator for login Admin

Go

 

CREATE LOGIN Operator WITH Password='operator'

go

Create User operator for login Operator

go

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

Администратор БД: имеет доступ ко всем данным, может изменять структуру  базы данных и связи между отношениями. Устанавливает права доступа  для всех остальных групп.

 

sp_addrolemember  'db_owner', 'administrator'

 

Операторы имеют права на работу с данными в некоторых таблицах, а также выполнение некоторых процедур:

Create ROLE op

go

sp_addrolemember  'op', 'operator'

go

GRANT Select, Insert, Update, Delete ON Call TO op

GRANT Select, Insert, Update, Delete ON List TO op

GRANT Select, Insert, Update, Delete ON Avto TO op

GRANT Select ON Streets TO op

GRANT Select ON Tarif TO op

GRANT Select ON Discount TO op

GRANT Execute ON new_call TO op

GRANT Execute ON new_list TO op

GRANT Execute ON end_list TO op

GRANT Execute ON end_call TO op

GRANT Execute ON search_call TO op

GRANT Execute ON search_date TO op

 

Схемы отношений составленные на языке  определения данных (DDL, Data Definition Language)

Создание таблиц

 

/* Образование */

Create Table Obrazov (

 Obraz_id int NOT NULL PRIMARY KEY,

 Obraz_name varchar(20));

 

/* Семья */

Create Table Semya (

 Semya_id int not NULL PRIMARY KEY,

 Semya_name varchar(22));

 

/* Сотрудники */

CREATE TABLE Sotrudnik (

 Sotr_ID int NOT NULL ,

 Sotr_Fam varchar(20) not null,

 Sotr_imya varchar(15),

 Sotr_otch varchar(15),

 Sotr_date date,

 Sotr_adr varchar(25),

 Sotr_tel varchar(20),

 Sotr_doljn varchar(15),

 Sotr_prava varchar(20),

 Sotr_date_prava datetime,

 Semya_id int,

 Sotr_auto varchar(10),

 Obraz_id int,

 CONSTRAINT PK_Sotr PRIMARY KEY (Sotr_id),

 CONSTRAINT FK_semya_sotr FOReign key (Semya_id) REFERENCES Semya (Semya_id) ,

 CONSTRAINT FK_obraz_sotr FOREIGN KEY (Obraz_id) REFERENCES Obrazov (Obraz_id));

 

/* Паспорт */

Create Table pasport (

 Sotr_id int PRIMARY KEY,

 Pass_id varchar(8),

 kem_vidan varchar(10),

 data_vidan datetime

 CONSTRAINT FK_pass_sotr FOREIGN KEY (Sotr_id) REFERENCES Sotrudnik (Sotr_id));

 

/* Цвета */

CREATE TABLE Color (

 Color_id int not null PRIMARY KEY,

 Color_name varchar(22));

 

/* Категории */

CREATE TABLE Category (

 Categ_id int not null PRIMARY KEY,

 Categ_name varchar(22));

 

/* Марки */

CREATE TABLE Marka (

 Marka_id int PRIMARY KEY,

Marka varchar(20))

 

/* Модели */

CREATE TABLE Model (

 Model_id int not null PRIMARY KEY,

 Model varchar(20),

 Marka_id int REFERENCES Marka (marka_id));

 

/* Автомобили */

CREATE TABLE Avto (

 Avto_id int not null,

 Sotr_id int REFERENCES Sotrudnik (Sotr_id),

 Model_id int REFERENCES Model (Model_id),

 Categ_id int REFERENCES Category (categ_id),

 Avto_date varchar(22),

 Avto_dvig_v varchar(24),

 Avto_dvig_n varchar(25) unique,

 Avto_status varchar(20),

 Color_id int REFERENCES Color (color_id)

 CONSTRAINT PK_auto PRIMARY KEY (avto_id, Sotr_id));

 

/* Материальные средства */

   CREATE TABLE Material (

 Material_id int not null PRIMARY KEY,

 Sotr_id int not null,

 stoimost money,

 kol_vo int,

 opisanie varchar(20),

 naznach varchar(50)

 CONSTRAINT FK_material_sotr FOREIGN KEY (Sotr_id) REFERENCES Sotrudnik (Sotr_id));

 

/* Оборудование*/

CREATE TABLE Oborud (

 Avto_id int not null,

 sotr_id int , 

 material_id int not null,

 Constraint PK_oborud Primary key (avto_id, material_id),

 CONSTRAINT FK_oborud_avto FOREIGN KEY (Avto_id, Sotr_id) REFERENCES Avto (avto_id, sotr_id),

 CONSTRAINT FK_mater_oborud FOREIGN KEY (Material_id) REFERENCES Material (Material_id));

 

/* Поставщики */

CREATE TABLE Postavshik (

 Postav_id int not null PRIMARY KEY,

 Material_id int not null,

 Name varchar(25),

 Ur_adr varchar(20),

 Fax varchar(22),

 dogovor varchar(21),

 Dogovor_date datetime,

 comment varchar(80)

 CONSTRAINT FK_Postav_sredstvo FOREIGN KEY (material_id) REFERENCES MAterial (material_id));

 

/* Маршрутный лист */

CREATE TABLE List (

 List_id int not null,

 Avto_id int,

 Sotr_id int,

 List_date smalldatetime,

 List_probeg int,

 List_probeg_off int,

 List_status varchar(20),

 Constraint PK_list Primary key (List_id, sotr_id, avto_id),

 CONSTRAINT FK_list_avto FOREIGN KEY (Avto_id, Sotr_id) REFERENCES Avto (Avto_id, Sotr_id));

 

 

 

 

 /* Дисконтные карты */

CREATE TABLE Discount (

 card_id int not null PRIMARY KEY,

 skidka real);

 

/* Тарифы */

Create table Tarif (

 Tarif_id int not null PRIMARY KEY,

 Tarif_name varchar(21),

 Tarif_price money);

/* Улицы */

CREATE TABLE Streets (

 Street_id int not null PRIMARY KEY,

 Street_name varchar(22));

 

/* Вызовы */

Create table Call (

 Call_id int not null,

 Sotr_id int not Null,

 Voditel int NULL,

 Tarif_id int not null,

 date_to_client date,

 Time_to_client time ,

 Telefon varchar(20),

 punkt_otpr int REFERENCES Streets (Street_id),

 Dom int,

 pod_ezd varchar(22),

 card_id int REFERENCES Discount (card_id),

 killometr int,

 punkt_naznach int REFERENCES Streets (Street_id),

 Call_price money,

 call_status varchar(20) ,

 CONSTRAINT PK_Call PRIMARY KEY (Sotr_id, Call_id),

 CONSTRAINT FK_Call_Sotr FOREIGN KEY (Sotr_id) REFERENCES Sotrudnik (Sotr_id),

 CONSTRAINT FK_Call_voditel FOREIGN KEY (Voditel) REFERENCES Sotrudnik (Sotr_id),

 CONSTRAINT FK_CALL_Tarif FOREIGN KEY (TARIF_ID) REFERENCES Tarif (Tarif_id));

 

 

Физическое проектирование

Обоснование выбора СУБД

Для разработки базы данных была выбрана СУБД SQL Server 2008. SQL Server является всеобъемлющим, интегрированным сквозным решением, которое наделяет пользователей организации безопасной, надежной, и продуктивной платформой для обработки промышленной информации и приложений, касающихся интеллектуальных ресурсов предприятия. SQL Server 2008 предоставляет мощные, знакомые инструменты для профессионалов информационных технологий так же, как и для работников информационной сферы, уменьшая сложность создания, развёртывания, управления и использования данных предприятия и аналитических приложений на платформах от мобильных устройств до информационных систем предприятия. Благодаря исчерпывающему набору функций, взаимодействию с существующими системами и автоматизации типовых задач, SQL Server 2008 предоставляет полное решение в области хранения данных для предприятий всех масштабов.

Создание базы на СУБД MS SQL Server 2008

CREATE DATABASE Taxi

 ON PRIMARY

(NAME = taxi_data, FILENAME='D:\SQLDB\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\taxi_data.mdf',  size = 4, maxsize =25, filegrowth = 1 mb)

 LOG ON

(NAME = taxi_log, FILENAME='D:\SQLDB\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\taxi_log.ldf', size = 4, maxsize = 20,

 filegrowth =1 mb);

 

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

Рисунок 15. Диаграмма физической модели данной базы данных

Заполнение созданной  БД

Вновь созданная БД содержит все еще пустые табличные структуры. Для заполнения базы данных был использован SQL скрипт написанный вручную:

 

• Заполнение таблицы Категории

 

INSERT INTO Category (Categ_id, Categ_name)

VALUES

(1, 'À'),

(2, 'B'),

(3, 'C'),

(4, 'D'),

(5, 'E');

 

 

 

 

 

 

 

• Заполнение таблицы Цвета

 

• Заполнение таблицы Образование

• Заполнение таблицы Тарифы

• Заполнение таблицы Улицы

• Заполнение таблицы Семейное положение

• Заполнение таблицы Сотрудники

• Заполнение таблицы Паспорт

• Заполнение таблиц Марки, Модели

 

 

 

 

 

 

 

 

 

 

• Заполнение таблицы Транспортные средства

• Заполнение таблицы Материальные средства

• Заполнение таблицы Поставщики

• Заполнение таблицы Оборудование

• Заполнение таблицы Дисконтные карты

Запросы базы данных. Представления, процедуры и триггеры.

Представления.

Для запроса информации из БД были созданы специальные представления.

1. Служит для представления полной информации о выполняемых вызовах

2. Служит для представления полной информации о завершенных вызовах

3. Служит для представления полной информации о водителях, вышедших на смену

4. Служит для представления полной информации о сотрудниках

5. Служит для вывода полной информации о поставщиках

6. Служит для списка заказов поставленных в очередь

7. Служит для полного списка автомашин

 

 

8. Служит для отображения статистики

Процедуры

1. Начинает новую смену водителю

 

2. Добавляет новый вызов в базу

 

 

3.  Завершает смену водителя

 

 

 

 

 

4. Завершает вызов при этом срабатывает триггер завершения и высчитывает стоимость поездки

 

5. Поиск по завершенным вызовам. Критерий поиска, телефон абонента.

 

 

 

6. Служит для добавления нового автомобиля

 

7. Служит для добавления нового сотрудника

 

8.  Служит для поиска завершенных вызовов за определенный промежуток времени

 

 

Функции

1. Функция определения возраста

 

CREATE FUNCTION old (@uid date)

RETURNS int

AS

BEGIN

DECLARE

@year int

SELECT

@year=CAST((CAST((GetDate() - @uid) AS Bigint)/365.25)AS int)