Реализация базы данных системы учета клиентов ООО "Корпорация развития республики Мордовия" под управлением СУБД MySQL

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Реализация базы данных системы учета клиентов ООО «Корпорация развития республики Мордовия» под управлением СУБД MySQL»



Введение

запрос тестирование учет управление

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

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

В информационной системе выделяется ряд процессов:

? ввод информации из внешних и внутренних источников;

? обработка входящей информации;

? хранение информации для последующего ее использования;

? вывод информации в удобном для пользователя виде;

? обратная связь, то есть предоставление информации, переработанной в определенной организации, для корректировки входящей информации.

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

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

1) извлечение информации с помощью программного модуля (необходимо знать точную структуру базы данных);

2) использование языка запросов SQL.

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

- быстрый поиск определенных данных путем фильтрации с применением определенных критериев (условий);

? вычисление данных;

? автоматизированное управление данными, например регулярный просмотр актуальных данных.

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

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

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

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

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



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

1.1 Техническое задание

Система предназначена для хранения и обработки сведений о клиентах компании в течение определенного сотрудниками срока. Обработанные сведения о клиентах могут быть использованы для анализа сотрудниками показателей клиентов, введения отчетности, изменения показателей после обращения в компанию ООО «Корпорация развития республика Мордовия».

База данных системы учета клиентов ООО «Корпорация развития республики Мордовия» должна обеспечивать возможность выполнения следующих функций:

- ввод и коррекцию текущей информации о клиентах;

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

- получение сведений о клиентах;

Исходными данными являются:

- списки юридических лиц (клиентов);

- финансовое положение юридического лица;

- данные, включающие в себя характеристику юридического лица.

Результаты:

- список клиентов компании на текущий момент;

1.2 Применение СУБД MySQL

MySQL - свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компанию MySQL AB. Продукт распространяется как под GNU General Public License, так и под собственной коммерческой лицензией. Помимо этого, разработчики создают функциональность по заказу лицензионных пользователей. Именно благодаря такому заказу почти в самых ранних версиях появился механизм репликации.

MySQL является решением для малых и средних приложений. Входит в состав серверов WAMP, AppServ, LAMP и в портативные сборки серверов Денвер, XAMPP, VertrigoServ. Обычно MySQL используется в качестве сервера, к которому обращаются локальные или удалённые клиенты, однако в дистрибутив входит библиотека внутреннего сервера, позволяющая включать MySQL в автономные программы.

Гибкость СУБД MySQL обеспечивается поддержкой большого количества типов таблиц: пользователи могут выбрать как таблицы типа MyISAM, поддерживающие полнотекстовый поиск, так и таблицы InnoDB, поддерживающие транзакции на уровне отдельных записей. Более того, СУБД MySQL поставляется со специальным типом таблиц EXAMPLE, демонстрирующим принципы создания новых типов таблиц. Благодаря открытой архитектуре и GPL-лицензированию, в СУБД MySQL постоянно появляются новые типы таблиц.

Особенности языка SQL у СУБД «MySQL»

MySQL - это разветвление системы управления базами данных MySQL. Благодаря этому она поддерживает с MySQL высокую совместимость, что обеспечивает точное соответствие с API и командами MySQL.

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

Так как СУБД «MySQL» тесно связана с СУБД «MySQL» и использует аналогичные команды, ее основной целью является быстродействие и ошибкоустойчивость. Она предоставляет команды языка SQL, которые соответствуют ANSI SQL 92.

Все команды языка запросов SQL в СУБД «MySQL» можно разделить на 4 основные группы:

? Операторы определения данных (DataDefinitionLanguage, DDL). Данные операторы используются для создания, изменения, а также удаления объектов в базе данных.

? Операторы манипуляции данными (DataManipulationLanguage, DML). Отвечают за перемещение данных в базе данных.

? Операторы определения доступа (DataControlLanguage, DCL). Это операторы, направленные на управление доступом к базе данных.

? Операторы управления транзакциями (TransactionControlLanguage, TCL). Операторы используются для определения исхода транзакций в базе данных.

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

Не менее используемой командой определения данных является команда ALTER, которая служит для изменения или добавления объекта базы данных. Данная команда может добавлять (удалять) столбцы, изменять в них тип данных или размер, также устанавливать (удалять) ограничения целостности.

Если возникает необходимость удаления некого объекта существующей базы данных можно воспользоваться командой DROP, которая также относится к операторам DML.

К операторам манипуляции данными можно отнести такие команды как SELECT, INSERT, UPDATE, DELETE. Спецификация представленных команд достаточно понятна при переводе их на русский язык. Данные операторы будут более детально рассмотрены в рамках проекта ниже.

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

? GRANT - команда используется для разрешения на определенные манипуляции с объектом.

? REVOKE - команда используется для отзыва ранее выданных разрешений.

? DENY - команда используется для определения некого запрета, который имеет приоритет над разрешением.

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

? COMMIT - команда используется для применения транзакции;

? ROLLBACK - команда используется для отмены всех изменений, сделанных в контексте текущей транзакции;

? SAVEPOINT - команда используется для деления транзакции на более мелкие участки.

1.3 Методы написания запросов

Все запросы, которые пишутся на структурированном языке SQL можно разделить на две большие группы:

? Простые;

? Сложные.

Простые запросы - это зачастую запросы, где используется только одна или две (не более) таблицы базы данных.

Сложные запросы - к сложным запросам относятся запросы, в которых используется более двух таблиц базы данных.

Для создания таблицы вам необходимо иметь базу данных, которая будет ее содержать. Если базы данных нет, то создать ее можно набрав команду CREATE DATABASE. Имя базы данных не может содержать пробелы.

Синтаксис данной команды:

CREATE DATABASE Имя_базы_данных, причем имя БД не может содержать пробелы.

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

CREATE TABLE Имя_Таблицы

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

Синтаксис данной команды:

SELECT Имя_атрибута FROM Имя_Таблицы, где

Имя_атрибута - это любое выбранное поле из таблицы реляционной базы данных. Также позволяется выводить сразу несколько полей таблицы, перечисляя их через запятую Для выведения сразу всех полей данной таблицы можно использовать символ «*».

Имя_Таблицы - название таблицы, из которой будут выводиться данные.

Иногда для более детального отбора требуется воспользоваться дополнительными критериями. В языке SQL существует параметр WHERE, с помощью которого задается условие отбора. Данный оператор используется в синтаксисе команды SELECT:

SELECT Имя_атрибута FROM Имя_Таблицы

WHERE Условие

Зачастую данные, выведенные запросом нужно отсортировать по определенному полю (столбцу). Для этого можно воспользоваться командой данного языка OREDERBY. Эта команда также прописывается в синтаксисе команды SELECT:

SELECT Имя_атрибута FROM Имя_Таблицы

WHERE Условие

OREDERBY Поле_сортировки

ORDER BY имеет два параметра отбора «ASK» (по умолчанию) и «DESC». Параметр «ASK» устанавливает порядок сортировки по возрастанию, то есть от меньшего значения к большему. Параметр «DESC» наоборот - от большего значения к меньшему.

Синтаксис данного оператора:

ORDER BY Значение_атрибута (ASC|DESC).

Для добавления строки в таблицу используется оператор INSERT. Добавить строку можно несколькими способами:

? добавление новой строки;

? добавление части строки;

? добавление результатов запроса.

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

Синтаксис команды:

INSER TINTO Имя_Таблицы VALUES (`Значение_поля', `Значение_поля',…), где

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

Если необходимо добавить только часть строки, нужно указать имена столбцов, в которые заносятся данные.

Структура команды выглядит следующим образом:

INSERT INTO Имя_Таблицы (Имя_атрибута)

VALUES (`Значение_поля')

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

Оператор INSERT в основном используется для добавления строк в таблицы базы данных, но также может использоваться и для добавления результатов запроса SELECT.

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

В самом простом случае синтаксис команды выглядит так:

UPDATE Имя_Таблицы

SET Имя_атрибута = Значение_атрибута, где

Имя_Таблицы - таблица, в которой необходимо обновить поле(поля).

Имя_атрибута - само поле, которое нужно обновить, если полей несколько они перечисляются через запятую.

Значение_атрибута - новое значение, которое нужно присвоить данному столбцу.

Также команда UPDATE допускает применение оператораWHERE для уточнения поля выбора обновления.

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

Синтаксис команды для удаления одной строки из таблицы:

DELETE FROM Имя_Таблицы WHERE Условие_выбора

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

Также для удаления всех строк из таблицы можно воспользоваться командой TRUNCATE TABLE.

Синтаксис данной команды:

TRUNCATE TABLE Имя_таблицы.

В языке SQL достаточно часто используются функции. Их подразделяют на несколько видов:

? агрегатные

? скалярные

Агрегатные функции выполняют вычисление на наборе значений (группе значений) и возвращают одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL. Они используются в выражении GROUP BY конструкции SELECT.

Скалярные функции выполняют вычисление над одним значением или списком значений. Они в свою очередь подразделяются:

? числовые;

? функции даты;

? строковые;

? системные;

? функции методанных.

При выполнении данной курсовой работы будут использованы агрегатная функция COUNT и системная функция CONVERT.

Функция COUNT подсчитывает количество всех значений в столбце отличных от NULL.

Синтаксис данной функции:

COUNT (Значение_атрибута), где

Значение_атрибута - наименование столбца, значения которого нужно подсчитать.

Возвращаемое значение всегда имеет тип данных int.

Функция CONVERT преобразует выражение из одного типа данных в другой тип данных.

Синтаксис функции:

CONVERT (Тип_данных, Выражение, Стиль_значения), где

Тип_данных - задание типа данных с возможностью указания длины.

Выражение - это переменная, которую нужно преобразовать.

При написании SQL-запросов часто используется оператор выбора BETWEEN, который определяет диапазон заданных значений.

Синтаксис данного оператора:

Значение_атрибута BETWEEN Начальное_значение AND Конечное_значение.

Для объединения двух и более таблиц при написании запроса используется оператор INNER JOIN. Это часто используемый оператор в языке SQL, который возвращает пересечение двух множеств, то есть записи из двух таблиц, удовлетворяющих заданному условию.

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

1.4 Тестирование

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

Выявить данные ошибки может сам разработчик при написании своего кода, а также тестировщик при непосредственном тестировании запросов в определенной СУБД.

Для правильной организации процесса тестирования базы данных тестировщик должен иметь явное представление о внутренней структуре базы данных, а также обладать хорошими знаниями SQL и DML. Но если приложение является достаточно сложным, наилучшим вариантом будет обращение тестировщика к обработчику. Это самый надежный способ тестирование кода. Но если тестировщик не знаком с языком запросов SQL, он может проверить сам результат выполнения операции, написанной разработчиком. Данный способ не требует ни конкретных знаний языка, ни структуры базы данных.

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

Методы оптимизации запроса

Существует несколько способов оптимизации, написанного кода:

1. При написании запроса, пользователю СУБД MySQL не рекомендуется использовать символ «*» для выведения сразу всех полей, желательно перечислять их через запятую, так как могут возникнуть некоторые проблемы. Они возникают в основном при объединении нескольких таблиц, так как у разных таблиц базы данных могут быть одинаковые атрибуты.

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

3. Пользователю СУБД MySQL не рекомендуется использовать в условии функции, которые вычисляют определенное значение на основе задаваемого поля, потому что это будет сделано для каждой строки, а значит увеличится нагрузка, и, следовательно, уменьшится скорость выполнения запроса.

При использовании сортировки ORDERBY лучше использовать наименование столбца, а не его номер, так как могут возникнуть проблемы.

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

5. При создании таблицы желательно задавать значения по умолчанию, чтобы избавиться от NULL, так как могут возникать значения типа «null», которые являются состоянием ячейки, а не ее значением.

6. При создании запроса на массовое обновление таблицы базы данных желательно протестировать этот запрос на тестовой базе данных прежде чем запускать на рабочей. Та как последствия могут быть необратимыми. ?



2. Разработка SQL-запросов для СУБД «MySQL»

2.1 Написание запросов SQL к СУБД «MySQL»

Для реализации поставленной цели необходимо создать базу данных под управлением СУБД «MySQL».

Для создания базы данных используется оператор CREATE DATABASE.

Код скрипта на создание базы данных: CREATE DATABASE Invest_RM_2017

После чего требуется создание таблицы данных, для чего используется оператор CREATE TABLE. Код скрипта на создание таблицы данных: CREATE TABLE Clients (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, organization VARCHAR(50), lastname VARCHAR(20), firstname VARCHAR(20), phone VARCHAR(20), dateofcall DATE, dohod int, rabochie int)

При написании запроса на добавление данных в таблицу базы данных используется оператор манипуляции данными (DataManipulationLanguage, DML) INSERT.

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

INSERT INTO Clients ([id], [organization], [lastname], [firstname], [phone], [dateofcall], [dohod], [rabochie]) VALUES («1», «Барбер_Шоп», «Орлов», «Никита», «72-04-90», «2017-01-15»,» 300000», «10»)

Аналогичный запрос на заполнение таблицы клиенты (Сlients_call):

INSERT INTO Clients ([id], [organization], [lastname], [firstname], [phone], [dateofcall], [dohod], [rabochie]) VALUES («50», «ФДП», «Учкин», «Илья», «89276435439», «2017-04-01»,» 500000», «82»)

Данный скрипт написан для добавления одной записи в таблицу.

Аналогичным способом заполняется вся таблица.

Для определения клиентов, обратившихся в ООО «Корпорация развития республики Мордовия» за определенный период времени, например, с 20.03.2017 по 1.04.2017, можно использовать следующий запрос:

SELECT COUNT (id, organization)

FROM Clients

WHERE dateofcall>=convert (datetime, `2017-03-20`, 120)

В данном запросе просчитывается количество клиентов, у которых дата обращение в компанию находится в промежутке между 20 марта по 1 апреля 2017 года.

Результат:

48, Милано

49, Магнит

50, ФДП

Используя оператор SELECT COUNT () можно произвести выборку на основании необходимых данных.

Создадим еще одну таблицу внутри базы данных Invest_RM_2017 под названием Clients_support, которая будет содержать в себе данные о финансовой поддержке клиентов, дате выдачи финансовой поддержки, Ф.И.О. ответственного лица, а также два поля из предыдущей таблицы, а именно id и organization.

CREATE TABLE Clients_support (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, organization VARCHAR(50), date DATE, finance int, otvetstvenniy VARCHAR (30))

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

INSERT INTO Clients_support ([id], [organization], [date], [finance], [otvetstvenniy])

VALUES (`1', `Барбер_Шоп', `2017-01-16', `300000', `Орешкин')

Аналогично заполняется вся таблица.

Применение оператора FULL JOIN:

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

SELECT Clients.id as id, Clients.organization as organization

FROM Clients FULL JOIN Clients_support ON Clients.id=Clients_support.id, Clients.organization=Clients_support.organization



Заключение

В ходе написания данной курсовой работы была разработана база данных клиентов в СУБД «MySQL», которые обеспечивают потребности пользователей автоматизированной информационной системы в информации о клиентах.

При написании запросов были закреплены и усовершенствованы навыки их написания.

В итоге цель, поставленная в начале курсового проекта, достигнута.



Список использованных источников

1 Грофф Дж.Р., Вайнберг П.Н., Оппелъ Э.Дж. SQL: The Complete Reference-2015

2 Мартин Фаулер, Прамодкумар Дж. Садаладж. NoSQL DISTILLED-2013

3 Харрингтон Д. Object-oriented database design clearly explained-2012

4 Кевин Янк. PHP & MySQL-2013

5 Kalen Delaney. SQL Server MVP Deep Dives, Volume 2-2011

6 [Электронный ресурс] / http://www.tadviser.ru/index.php/%D0% 9F % D1% 80% D0% BE % D0% B4% D1% 83% D0% BA % D1% 82: MySQL;

7 [Электронный ресурс] / Режим доступа: http://www.intuit.ru/studies/courses/614/470/lecture/20877;

8 [Электронный ресурс] / Режим доступа: http://progopedia.ru/language/sql/-

9 [Электронный ресурс] / Режим доступа: https://msdn.microsoft.com/ru-ru/library/ms181765.aspx

10 Электронный ресурс] / Режим доступа: http://kursach.com/! inforactehnolog/4.5.2.htm

Размещено на Allbest.ru