VIII Международная студенческая научная конференция Студенческий научный форум - 2016

ВВЕДЕНИЕ

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

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

Система управления базами данных – это популярное инструментальное средство, применяемое для создания и работы с БД больших объемов.

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

В курсовой работе будет разработана базы данных организации, которая занимается ремонтом автомобилей.

1 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

В качестве предметной области базы данных выбрана организация «Авто ремонт», занимается ремонтом автомобилей различных видов.

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

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

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

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

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

- информация, характеризующая клиентов, в частности: фамилия, имя, отчество, телефон, адрес;

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

- информация, характеризующая процесс ремонта, в частности: название, стоимость;

- информация, характеризующая специалистов, в частности: фамилия, имя, отчество, квалификация.

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

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

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

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

2 ПОСТРОЕНИЕ ИНФОЛОГИЧЕСКОЙ МОДЕЛИ БД

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

Концептуальная модель строиться либо в виде диаграммы «Сущность-Связь» (Entity-Relationship-диаграммы, ER-диаграммы), либо записывается на языке концептуального (инфологического) моделирования (ЯКМ, ЯИМ).

2.1 Построение ER-диаграммы

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

Концептуальная модель строиться либо в виде диаграммы «Сущность-Связь» (Entity-Relationship-диаграммы, ER-диаграммы), либо записывается на языке концептуального(инфологического) моделирования (ЯКМ, ЯИМ).

1.Построение ER-диаграммы.

Сущность Израсходованные запчасти имеет следующие основные атрибуты:

- Код израсходованных запчастей

- Код договора

- Детали

Сущность Клиенты имеет следующие основные атрибуты:

- Код клиента

- Фамилия

- Имя

- Отчество

- Телефон

- Адрес

- Паспортные данные

Сущность Ремонт имеет следующие основные атрибуты:

- Код договора

- Код ремонта

- Название

- Стоимость

Сущность Специалисты имеет следующие основные атрибуты:

- Код специалиста

- Фамилия

- Имя

- Отчество

- Квалификация

Сущность Договор имеет следующие атрибуты (свойства):

- Код договора

- Код специалиста

- Код клиента

- Дата ремонта

- Время ремонта

- Стоимость ремонта

- Вид неисправности

Изобразим это в виде диаграммы «Сущность-Связь» (ER-диаграммы),

представленной на рисунке 1:

 

Паспортные

данные

 

 

Код клиента

Фамилия

Имя

Отчество

 

 

Фамилия

 

 

Имя

Код

специалиста

Клиенты

Специалисты

 

 

Отчество

1

Квалификация

1

 

 

М

М

Телефон

Адрес

 

 

Код договора

Договор

 

 

Время ремонта

 

 

1

1

Стоимость ремонта

Код специалиста

 

 

Код клиента

 

 

Вид неисправности

 

 

М

М

Израсходованные запчасти

Ремонт

Дата ремонта

 

 

Код договора

 

 

Код израсходованных

запчастей

Код договора

 

 

Стоимость

 

 

Детали

Название

Код ремонта

 

Рисунок 1 – Диаграмма «Сущность-Связь»

3 ПОСТРОЕНИЕ ДАТОЛОГИЧЕСКОЙ МОДЕЛИ БД

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

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

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

Вторая нормальная форма (2НФ) требует, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно идентифицировал запись.

Для третьей нормальной формы (ЗНФ) требуется, чтобы все не ключевые столбцы таблицы зависели от первичного ключа таблицы, но были независимы друг от друга.

Для четвертой нормальной формы (4НФ) требуется, чтобы в одной таблице не содержались независимые элементы данных, если между ними существует отношение "многие-ко-многим".

Исходя из вышесказанного, база данных «Авто ремонт» будет представлять собой пять связанных таблиц: Договор, Клиенты, Специалисты, Ремонт, Израсходованные запчасти.

Таблица 1 содержит информацию о таблицах, находящихся в составе БД «Авто ремонт».

Таблица 1 - Атрибуты и отношения в составе таблиц БД «Авто ремонт»

Таблица	Имя столбца	Тип данных	Ключ таблицы по полю			Ограничения
			Первичный ключ	Внешний ключ
1	2	3	4	5	6
Договор	КодДоговора	int	+		identity, not null
	КодСпециалиста	int	−			not null
	КодКлиента	int		+	Not Null
	ДатаРемонта	datetime		+
	ВремяРемонта	text	−
	СтоимостьРемонта	money		+
	ВидНеисправности	text
Израсходованные запчати	КодИзрасходованныхЗапчастей	int	+		identity, not null
	КодДоговора	int	−			not null
	Детали	varchar(30)	−
Специалисты	КодСпециалиста	int	+		identity, not null
	Фамилия	varchar(30)	−			not null
	Имя	text
	Отчество	text	−			not null
	Квалификация	char(100)	−			not null
Клиенты	КодКлиента	int	+		identity, not null
	ИмяК	text	−			not null
	ОтчествоК	text	−			not null
	Телефон	int	−
	Адрес	text	−			not null
	ПаспортныеДанные	int	−			not null
Ремонт	КодДоговора	int	+		identity, not null
	Код ремонта	int	−			not null
	Название	char(30)	−			not null
	Стоимость	money	−			not null

4 ПОСТРОЕНИЕ ФИЗИЧЕСКОЙ МОДЕЛИ БД

Физическое проектирование — создание базы данных в среде SQL Server Management Studio 2008 средствами универсального компьютерного языка SQL, применяемого для создания, модификации и управления данными в реляционных базах данных.

1. Запрос на создание базы данных

create database Авто ремонт

1. Запрос на создание таблиц

Запрос на создание таблицы Договор (рисунок 2):

CREATE TABLE Договор (

КодДоговора Int PRIMARY KEY,

КодСпециалиста Int Not Null

КодКлиента Int Not Null

ДатаРемонта DateTime,

ВремяРемонта Text,

СтоимостьРемонта Money,

ВидНеисправности Text );

Из этой инструкции следует следующее описание структуры таблицы:

- КодДоговора - Отражает код текущего договора. Данное поле является первичным ключом;

- КодСпециалиста - тип Int. Это поле в которое вводится

кодспециалиста;

- КодКлиента - тип Int. Поле, в которое вводится кодКлиента в договоре.

- ДатаРемонта – тип DateTime. Здесь необходимо поставить дату

заключения договора.

- ВремяРемонта – тип Text. Необходимо указать время необходимое для ремонта неисправности.

- СтоимостьРемонта – тип Money. Поле для введения стоимости

ремонта.

- ВидНеисправности – тип Text. Оставляются данные о виде

неисправности.

Рисунок 2 – Создание таблицы Договор

Запрос на создание таблицы Израсходованные запчасти (рисунок 3):

CREATE TABLE ИзрасходованныеЗапчасти (

КодИзрасходованныхЗапчастей Int PRIMARY KEY,

КодДоговора Int Not Null

Детали Text

Отсюда следует описание структуры таблицы:

- КодИзрасходованныхЗапчастей. Поле, в которое вводится номера компонентов. Данное поле является первичным ключом;

- КодДоговора. Указывается код Договора, в котором необходимо использовать запчасти.

- Детали – тип Text. Список запчастей в данном договоре.

Рисунок 3 – Создание таблицы Израсходованные запчасти

Запрос на создание таблицы Клиенты (рисунок 4):

CREATE TABLE Клиенты (

КодКлиента Int PRIMARY KEY,

ФамилияК Text Not Null,

ИмяК Text Not Null,

ОтчествоК Text Not Null,

Телефон Int,

Адрес Text,

ПаспортныеДанные Int Not Null

Из этой инструкции следует следующее описание структуры таблицы:

- КодКлиента. Поле, в которое вводится кодКлиента в договоре. Данное поле является первичным ключом;

- ФамилияК – тип Text. Указывается фамилия нового клиента.

- ИмяК – тип Text. Указывается Имя нового клиента.

- ОтчествоК – тип Text. Указывается Отчество нового клиента.

- Телефон – тип Int. Указывается телефон клиента.

- Адрес – тип Text. Указывается место проживание клиента.

- ПаспортныеДанные – тип Int. Указываются паспортные данные.

Рисунок 4 – Создание таблицы Клиенты

Запрос на создание таблицы Ремонт (рисунок 5):

CREATE TABLE Ремонт (

КодРемонта Int PRIMARY KEY,

КодДоговора Int Not Null

Название Text,

ПредварительнаяСтоимость Money

Отсюда следует описание структуры таблицы:

- КодРемонта. Поле, в которое вводится номер ремонта. Данное поле является первичным ключом;

- Название – тип Text. Указывается название компонентов.

- ПредварительнаяСтоимость – тип Money. Указывается стоимость на используемые компоненты.

Рисунок 5 – Создание таблицы Ремонт

Запрос на создание таблицы Специалисты (рисунок 6):

CREATE TABLE Специалисты (

КодСпециалиста Int PRIMARY KEY,

Фамилия Varchar(50) Not Null,

Имя Text Not Null,

Отчество Text Not Null,

Квалификация Char(100),

Отсюда следует описание структуры таблицы:

- КодСпециалиста. Поле, в которое вводится кодспециалиста в договоре. Данное поле является первичным ключом;

- Фамилия – тип Varchar(50). Указывается фамилия Специалиста.

- Имя – тип Text. Указывается Имя Специалиста.

- Отчество – тип Text. Указывается Отчество Специалиста.

- Квалификация – тип Char(100). Указывается квалификация специалиста.

Рисунок 6 – Создание таблицы Специалисты

1. Запрос на создание внешних ключей

alter table Договор add constraint fk foreign key ([Код специалиста]) references Специалисты ([Код специалиста])

alter table Запчасти add constraint fq foreign key ([Код договора]) references Договор ([Код договора])

alter table Ремонт add constraint fw foreign key ([Код договора]) references Договор ([Код договора])

alter table Договор add constraint fe foreign key ([Код клиента]) references Клиенты ([Код клиента])

alter table Ремонт drop constraint fw

alter table Запчасти drop constraint fq

alter table Договор add constraint fq foreign key ([Код запчасти]) references Запчасти ([Код запчасти])

alter table Договор add constraint fw foreign key ([Код ремонта]) references Ремонт ([Код ремонта])

1. Следующим этапом создания базы данных будет заполнение таблиц.

Заполняем таблицу Договор:

Рисунок 7 – табличное представление таблицы «Договор»

Заполняем таблицу Клиенты:

Рисунок 8 – табличное представление таблицы «Клиенты»

Заполняем таблицу Специалисты:

Рисунок 9 – табличное представление таблицы «Специалисты»

Заполняем таблицу Ремонт:

Рисунок 10 – табличное представление таблицы «Ремонт»

Заполняем таблицу Израсходованные запчасти:

Рисунок 11 – табличное представление таблицы «Израсходованные

запчасти»

Следующим за разработкой физической модели БД этапом является создание запросов для облегчения работы с БД.

Запрос на вывод информации о специалистах:

SELECT КодСпециалиста, Фамилия, Имя, Отчество, Квалификация

FROM Специалисты;

Рисунок 12 – Информация о специалистах

Запрос информации о договорах:

SELECT Фамилия, КодДоговора, ДатаРемонта, ВремяРемонта,

СтоимостьРемонта

FROM Специалисты INNER JOIN Договор ON Специалисты.

КодСпециалиста=Договор.КодСпециалиста;

Рисунок 13 – Информация о договорах

Запрос на сортировку специалистов по квалификации Автослесарь:

SELECT Фамилия, Имя, Отчество FROM Специалисты WHERE Квалификация = 'Автослесарь'

Рисунок 14 – Сортировка по квалификации

Запрос информации о клиентах:

SELECT ФамилияК, ИмяК, ОтчествоК,ДатаРемонта FROM Клиенты INNER JOIN Договор ON Договор.КодКлиента = Клиенты.КодКлиента

Рисунок 15 – Информация о клиентах

Запрос на предоставление информации о полном заработке специалиста:

select Фамилия,SUM (СтоимостьРемонта)as [Общая стоимость] from Договор inner join Специалисты on Договор.Кодспециалиста=Специалисты.КодСпециалиста group by (Фамилия)

Рисунок 16 – Заработок специалиста

Запрос на вывод ремонтов в ценовом диапазоне 1000-3000:

SELECT Фамилия, Имя, Отчество,СтоимостьРемонта FROM Специалисты INNER JOIN Договор ON Договор.КодСпециалиста = Специалисты.КодСпециалиста where СтоимостьРемонта>1000 and СтоимостьРемонта

Код для цитирования: Скопировать