Создание базы данных для ветеринарной клиники

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

1

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

1. Основы баз данных

2. Описание и анализ предметной области

2.1 Разработка модели данных

2.2 Инфологическая (концептуальная) модель предметной области

2.3 Логическая модель данных

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

3. Описание программного продукта

3.1 Физическая модель базы данных

3.2 Описание реализации системы

3.3 Описание контрольного примера

ВЫВОДЫ

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ



ВВЕДЕНИЕ

база данный информация программный

В наше время сложно представить себе жизнь человека без новейших технологий, таких как мобильные телефоны, персональные компьютеры, гаджеты и т.д. Они проникли уже во все сферы производства и торговли. Раньше, когда не было компьютеров, приходилось работать с бумажными документами, и это было малоэффективно, особенно в производственных сферах и бизнесе, так как поиск и обработка этих документов затрачивали довольно большое количество времени. Но прогресс не стоит на месте. Сейчас современные программные средства, такие как Microsoft Access, Microsoft SQL Server, 1С:Предприятие и другие обеспечивают ввод, обработку, хранение, изменение данных и позволяет сэкономить достаточно большое количество времени.

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

База данных (БД) - это хранилище для большого количества систематизированных данных, с которыми можно производить определённые действия: добавления, удаления, изменения, копирования, упорядочивание. Эти базы данных образовываются и функционируют под управлением специальных программных комплексов (совокупностей языков программирования и программных средств), называемых системами управления базами данных (СУБД). По существу, система управления базами данных служит посредником между пользователями и базой данных [1].

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

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

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

В данной работе необходимо спроектировать базу данных ветеринарной клиники в Microsoft SQL Serever.

Входные данные:

1) информация по больным-животным;

2) информация по врачам;

3) диагноз;

4) назначенное лечение;

5) длительность;

6) стоимость лечения.

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

Выходные данные:

1) заполнение и редактирование таблиц базы данных;

2) вывод сведений о врачах, животных и хозяевах животных;

3) вывод сведений о поставленных диагнозах и назначенному курсу лечения для каждого животного;

4) формирование отчета по врачам, по животным и по заболеваемости.

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

1. Основы баз данных

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

К основным функциям СУБД принято относить следующие:

1) управление данными во внешней памяти;

2) управление буферами оперативной памяти;

3) управление транзакциями;

4) журнализация и восстановление БД после сбоев;

5) поддержка языков БД [1].

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

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

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

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

Поддержка языков БД. Для работы с базами данных используются специальные языки, в целом называемые языками баз данных. В ранних СУБД поддерживалось несколько специализированных по своим функциям языков. Чаще всего выделялись два языка - язык определения схемы БД (SDL - Schema Definition Language) и язык манипулирования данными (DML - Data Manipulation Language). SDL служил, главным образом, для определения логической структуры БД, какой она представляется пользователям. DML содержал набор операторов манипулирования данными, позволяющих вводить, удалять, модифицировать и выбирать данные. В современных СУБД, обычно, поддерживается единый интегрированный язык, содержащий все необходимые средства для работы с БД и обеспечивающий базовый пользовательский интерфейс. Стандартным языком наиболее распространенных в настоящее время реляционных СУБД является язык SQL (Structured Query Language) [2].

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

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

Наконец, авторизация доступа к объектам БД производится на основе специального набора операторов SQL. Идея состоит в том, что для выполнения операторов SQL разного вида пользователь должен обладать различными полномочиями. Пользователь, создавший таблицу БД, обладает полным набором полномочий для работы с этой таблицей. В число таких полномочий входит право на передачу всех или части полномочий другим пользователям, включая полномочие на передачу полномочий. Полномочия пользователей описываются в специальных таблицах-каталогах, а контроль полномочий поддерживается на языковом уровне [1,2].

2. Описание и анализ предметной области

2.1 Разработка модели данных

Учитывая задачи создания БД, целесообразно выделить шесть информационных объекта (отношения, таблицы): животное, хозяин животного, врач, диагноз и лечение.

Рассмотрим структуру таблиц, описывающих эти сущности, и разработаем модель данных «сущность-связь».

Таблица «Животное» содержит необходимые сведения о каждом животном, хозяева которых обращались в ветеринарную клинику.

В таблице «Животное» должны быть такие поля:

1) идентификатор животного;

2) кличка;

3) дата рождения;

4) порода;

5) пол;

6) идентификатор хозяина животного;

7) идентификатор лечащего врача;

8) идентификатор поставленного диагноза.

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

Таблица «Хозяин животного» содержит необходимые сведения о хозяине животного, а таблица «Врач» - информацию о лечащем враче. Атрибуты этих таблиц будут: «ФИО», «Адрес», «Телефон», а также «Идентификатор хозяина животного» и «Идентификатор врача», соответственно.

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

В таблице «Лечение» будут следующие атрибуты: «Идентификатор лечения» как первичный ключ, «Наименование», «Дата», «Длительность», «Стоимость». Внешними ключами будут первичные ключи из таблиц «Врач», «Животное» и «Диагноз».

Также для входа в систему создадим таблицу «User» с такими полями: «user_name», «password» и «id_user». Внешним ключом будет атрибут «Идентификатор врача» из таблицы «Врач».

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

2.2 Инфологическая (концептуальная) модель предметной области

После определения типов данных инфологическая модель будет выглядеть как на рисунке 2.1.

Рисунок 2.1 - Инфологическая модель



Выделено шесть сущностей: «Животное», «Хозяин животного», «Врач», «Диагноз», «Лечение» и «User». Таблицы связаны семью отношениями типа «один ко многим». Отмечены первичные и внешние ключи.

Можно сформулировать следующие бизнес-правила:

1) хозяин может владеть несколькими животными;

2) врач может лечить нескольких животных;

3) врач ставит много диагнозов;

4) по одному диагнозу может быть назначено несколько лечений;

5) одному животному может быть назначено несколько лечений;

6) один диагноз может быть поставлен нескольким животным;

7) врач может войти в систему под соответствующим именем пользователя.

2.3 Логическая модель данных

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

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

Тип данных - обозначение типа данных в соответствии с конкретной программной системой.

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

Ключ - указание, что данное поле является ключевым.

Данный состав свойств является минимально необходимым для описания данных, хранимых в таблице [3].

Логическая модель данных для таблицы «Животное» представлена в табл. 2.1.

Таблица 2.1 - Таблица «Животное»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_животное	INT	*
ID_врач	INT		*	*
ID_хозяин_животного	INT		*	*
ID_диагноз	INT		*	*
Кличка	VARCHAR(50)			*
Дата_рождения	SMALLDATETIME			*
Порода	VARCHAR(50)			*
Пол	VARCHAR(50)			*

Логическая модель данных для таблицы «Хозяин животного» представлена в табл. 2.2.

Таблица 2.2 - Таблица «Хозяин_животного»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_хозяин_животного	INT	*
ФИО	VARCHAR(50)			*
Адрес	VARCHAR(50)			*
Телефон	VARCHAR(50)			*

Логическая модель данных для таблицы «Врач» представлена в табл. 2.3.

Таблица 2.3 - Таблица «Врач»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_врач	INT	*
ФИО	VARCHAR(50)			*
Адрес	VARCHAR(50)			*
Телефон	VARCHAR(50)			*

Логическая модель данных для таблицы «Диагноз» представлена в табл. 2.4.



Таблица 2.4 - Таблица «Диагноз»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_диагноз	INT	*
ID_врач	INT		*	*
Наименование	VARCHAR(50)			*
Дата	SMALLDATETIME			*

Логическая модель данных для таблицы «Лечение» представлена в табл. 2.5.

Таблица 2.5 - Таблица «Лечение»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_лечение	INT	*
ID_врач	INT		*	*
ID_животное	INT		*	*
ID_диагноз	INT		*	*
Наименование	VARCHAR(200)			*
Дата	SMALLDATETIME			*
Длительность	INT			*
Стоимость	REAL			*

Логическая модель данных для таблицы «user» представлена в табл. 2.6.

Таблица 2.6 - Таблица «User»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_user	INT	*
ID_врач	INT		*	*
Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
user_name	VARCHAR(MAX)			*
password	VARCHAR(MAX)			*



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

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

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

Таблица 2.7 - Исходная таблица

Код предмета	Предмет	Преподаватель	Код студента	Фамилия студента
1	Базы данных	Иванов	30	Грачев
1	Базы данных	Иванов	31	Сидоров
1	Базы данных	Иванов	32	Курочкин
2	Математика	Некрасов	31	Сидоров
2	Математика	Некрасов	32	Курочкин

При использовании универсального отношения возникают две проблемы:

1) избыточность данных;

2) потенциальная противоречивость (аномалии).

Под избыточностью понимают повторение данных в разных строках одной таблицы или в разных таблицах БД. Так, для каждого студента повторяются данные «1, Базы данных» и «2, Математика»

Аномалии - это проблемы, возникающие в данных из-за дефектов проектирования БД. Существуют три вида аномалий: вставки, удаления и модификации.

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

Аномалии удаления возникают при удалении данных из дефектной схемы.

Аномалии модификации возникают при изменении данных дефектной схемы.

Решение перечисленных проблем состоит в разделении данных и связей, что обеспечивается процедурой нормализации. Концепции и методы нормализации были разработаны Э. Ф. Коддом [4].

Нормализация отношений - это формальный аппарат ограничений на формирование отношений, который позволяет устранить дублирование и потенциальную противоречивость хранимых данных, уменьшает трудозатраты на ведение БД. Процесс нормализации заключается в декомпозиции исходных отношений на более простые отношения. Цель нормализации - получение такого проекта БД, в котором «каждый факт появляется лишь в одном месте».

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

первая нормальная форма (1НФ);

вторая нормальная форма (2НФ);

третья нормальная форма (3НФ);

нормальная форма Бойса-Кодда (БКНФ);

четвертая нормальная форма (4НФ);

пятая нормальная форма (5НФ).

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

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

Для приведения нашей таблицы в 2НФ необходимо декомпозировать исходное отношение на два (табл. 2.8, 2.9), в которых все неключевые атрибуты будут полностью функционально зависеть от ключа [5].

Таблица 2.8 - Таблица «Занятия»

Предмет	Преподаватель
Базы данных	Иванов
Математика	Некрасов

Декомпозиция исходной таблицы (табл 2.9).

Таблица 2.9 - Таблица «Студенты»

Код предмета	Код студента	Фамилия студента
1	30	Грачев
1	31	Сидоров
1	32	Курочкин
2	31	Сидоров
2	32	Курочкин

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

Нормальная форма Бойса-Кодда (БКНФ) является развитием ЗНФ и требует, чтобы в отношении были только такие функциональные зависимости, левая часть которых является потенциальным ключом отношения. Потенциальный ключ представляет собой атрибут (или множество атрибутов), который может быть использован для данного отношения в качестве первичного ключа. Фактически первичный ключ - это один из потенциальных ключей, назначенный в качестве первичного. Детерминантом называется левая часть функциональной зависимости. Отношение находится в БКНФ тогда и только тогда, когда каждый детерминант отношения является потенциальным ключом [4,5].

Теперь проведем нормализацию относительно наших таблиц. Рассмотрим таблицу «Врач» (табл. 2.10).

Таблица 2.10 - Таблица «Врач»

Атрибут	Тип данных	PKEY	FKEY	NOT_NULL
ID_врач	INT	*
ФИО	VARCHAR(50)			*
Адрес	VARCHAR(50)			*
Телефон	VARCHAR(50)			*

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

Таблицы «Животное», «Хозяин_животного», «Диагноз», «Лечение» и «user» также нормализованы и находятся в БКНФ.

3. Описание программного продукта

Данный программный продукт написан в Microsoft Visual Studio 2010 на языке программирования C#. Для создания базы данных использовался Microsoft SQL Server Express Edition.

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

1) операционная система Windows;

2) разрешение экрана 1000*700;

3) процессор 1600 Hz;

4) RAM 256 MB;

5) Free HDD space - 100 MB;

6) дисплей, манипулятор «мышь», клавиатура.

Для создания и хранения базы данных необходимо установить Microsoft SQL Server Express Edition.

Назначение программы: организация простой и понятной работы с созданной базой данных.

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

3.1 Физическая модель базы данных

Создадим таблицу «Врач» (рис. 3.1) со следующими полями и соответствующими ограничениями: «ID_Врач» (PK), «Фамилия», «Имя», «Отчество», «Адрес», «Телефон».

CREATE TABLE Врач

( [ID_Врач] INT NOT NULL,

Фамилия VARCHAR(30) NOT NULL,

Имя VARCHAR(30) NOT NULL,

Отчество VARCHAR(30) NOT NULL,

Адрес VARCHAR(30) NOT NULL,

Телефон VARCHAR(30) NOT NULL,

PRIMARY KEY ([ID_врач]

)

Рисунок 3.1 - Определение таблицы «Врач»

В таблицу «Хозяин животного» (рис. 3.2) необходимо добавить следующие атрибуты: «ID_Хозяин_животного» (PK), «Фамилия», «Имя», «Отчество», «Адрес», «Телефон».

CREATE TABLE Хозяин_животного

( [ID_Хозяин_животного] INT NOT NULL,

Фамилия VARCHAR(30) NOT NULL,

Имя VARCHAR(30) NOT NULL,

Отчество VARCHAR(30) NOT NULL,

Адрес VARCHAR(30) NOT NULL,

Телефон VARCHAR(30) NOT NULL,

PRIMARY KEY ([ID_Хозяин_животного]

)

Рисунок 3.2 - Определение таблицы «Хозяин животного»

Таблица «Животное» (рис. 3.3) будет состоять из таких полей: «ID_Животное» (PK), «Кличка», «Дата_рождения», «Порода», «Пол», а также внешние ключи (FK): «Врач», «Хозяин_животного» и «Диагноз».

CREATE TABLE Животное

( [ID_Животное] INT NOT NULL,

Кличка VARCHAR(30) NOT NULL,

Дата_рождения SMALLDATETIME NOT NULL,

Порода VARCHAR(30) NOT NULL,

Пол VARCHAR(30) NOT NULL,

[Врач] INT NOT NULL,

[Хозяин_животного] INT NOT NULL,

[Диагноз] INT NOT NULL,

PRIMARY KEY ([ID_Животное],

FOREIGN KEY ([Врач]) REFERENCES Врач([Врач]),

FOREIGN KEY ([Хозяин_животного]) REFERENCES Хозяин_животного([Хозяин_животного]),

FOREIGN KEY ([Диагноз]) REFERENCES Диагноз([Диагноз])

)

Рисунок 3.3 - Определение таблицы «Животное»

Создадим таблицу «Диагноз» (рис. 3.4) с атрибутами: «ID_Диагноз» (PK), «Наименование», «Дата». Внешний ключ (FK): «Врач»

CREATE TABLE Диагноз

( [ID_Диагноз] INT NOT NULL,

Наименование VARCHAR(30) NOT NULL,

Дата SMALLDATETIME NOT NULL,

[Врач] INT NOT NULL,

PRIMARY KEY ([ID_Диагноз],

FOREIGN KEY ([Врач]) REFERENCES Врач([Врач])

)

Рисунок 3.4 - Определение таблицы «Диагноз»

В таблицу «Лечение» (рис.3.5) необходимо добавить следующие поля: «ID_Лечение» (PK), «Наименование», «Дата», «Длительность», «Стоимость». Внешние ключи: «Врач», «Животное» и «Диагноз».

CREATE TABLE Лечение

( [ID_Лечение] INT NOT NULL,

Наименование VARCHAR(200) NOT NULL,

Дата SMALLDATETIME NOT NULL,

Длительность INT NOT NULL,

Стоимость REAL NOT NULL,

[Врач] INT NOT NULL,

[Животное] INT NOT NULL,

[Диагноз] INT NOT NULL,

PRIMARY KEY ([ID_Лечение],

FOREIGN KEY ([Врач]) REFERENCES Врач([Врач]),

FOREIGN KEY ([Животное]) REFERENCES Животное([Животное]),

FOREIGN KEY ([Диагноз]) REFERENCES Диагноз([Диагноз])

)

Рисунок 3.5 - Определение таблицы «Лечение»

В таблице «user» (рис. 3.6) будут следующие атрибуты: «ID_user» (PK), «username», «password», а также внешний ключ «Врач».

CREATE TABLE user

( [ID_user] INT NOT NULL,

username VARCHAR(MAX) NOT NULL,

password VARCHAR(MAX) NOT NULL,

[Врач] INT NOT NULL,

PRIMARY KEY ([ID_user],

FOREIGN KEY ([Врач]) REFERENCES Врач([Врач])

)

Рисунок 3.6 - Определение таблицы «user»

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

Заполнение таблицы «user» (рис. 3.7).

INSERT INTO user(ID_user,Врач,username,password,Квалификация)VALUES (1,10,'Николенко','c4ca4238a0b923820dcc509a6f75849b');

INSERT INTO user(ID_user,Врач,username,password,Квалификация)VALUES (3,11,'Петров','6512bd43d9caa6e02c990b0a82652dca');

INSERT INTO user(ID_user,Врач,username,password,Квалификация)VALUES (4,12,'Назаренко','c20ad4d76fe97759aa27a0c99bff6710');

INSERT INTO user(ID_user,Врач,username,password,Квалификация)VALUES (5,13,'Кислая','c51ce410c124a10e0db5e4b97fc2af39');

INSERT INTO user(ID_user,Врач,username,password,Квалификация)VALUES (6,14,'Григорьев','aab3238922bcc25a6f606eb525ffdc56');

Рисунок 3.7 - Заполнение таблицы «user»

Данные в столбце «password» зашифрованы алгоритмом шифрования md5. Это нужно для того, чтобы ограничить доступ в систему.

3.2 Описание реализации системы

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

При запуске программы открывается окно входа в систему (рис. 3.8), в котором нужно ввести свои данные (логин или пароль) и нажать «Вход».

Рисунок 3.8 - Вход в систему

Соответствующий код программы

// Строка соединения

string MyConString = "Data Source=MARISHKA-ПК;Initial Catalog=msdb;Integrated Security=True";

private SqlConnection connection;

// таблица с данными в памяти

private DataTable data = new DataTable();

// Вход пользователя (врача) в систему

private void btn_EnterUser_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку соединения

SqlCommand com = connection.CreateCommand();

com.CommandText = "SELECT * FROM user WHERE username='" + tb_Login.Text + "' AND password='" + GetHashString(mtb_Password.Text) + "'";// текст запроса

if (data.Rows.Count == 1)

{

MessageBox.Show("Логин/пароль неверны, либо такого пользователя не существует!");

return;

}

connection.Close();

this.Hide();

Form1 f1 = new Form1();

f1.ShowDialog();

}

Если данные не соответствуют существующей учетной записи, то следует нажать кнопку «Регистрация». Откроется окно для регистрации пользователя (рис. 3.9).

Рисунок 3.9 - Окно регистрации пользователя

Код программы:

// Строка соединения

string MyConString = "Data Source=MARISHKA-ПК;Initial Catalog=msdb;Integrated Security=True";

private SqlConnection connection;

// Добавить новые данные пользователя, который регистрируется в БД

private void btn_Add_NewInfo_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

com.CommandText = @"SELECT MAX (ID_Врач) FROM [user];";

int id = (int)com.ExecuteScalar() + 1;

com.CommandText = @"Insert into [user] Values (" + id.ToString() + "," + "'" + tb_Login.Text + "'," + "'" + GetHashString(tb_Password.Text) + "');";

com.ExecuteNonQuery();

connection.Close();

this.Hide();

Form_Login fl = new Form_Login();

fl.Show();

}

// Добавить логин и пароль нового пользователя в БД

private void btn_Add_NewUser_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

com.CommandText = @"SELECT MAX (ID_Врач) FROM [Врач];";

int id = (int)com.ExecuteScalar() + 1;

com.CommandText = @"Insert into [Врач] Values (" + id.ToString() + "," + "'" + tb_FirstName.Text + "'," + "'" + tb_Name.Text + "'," + "'" + tb_LastName.Text + "'," + "'" + tb_Adres.Text + "'," + " '" + tb_Telefon.Text + "');";

com.ExecuteNonQuery();

connection.Close();

}

Код шифрования пароля алгоритмом md5

// шифрование md5

string GetHashString(string s)

{

//переводим строку в байт-массим

byte[] bytes = Encoding.Unicode.GetBytes(s);

//создаем объект для получения средст шифрования

MD5CryptoServiceProvider CSP =

new MD5CryptoServiceProvider();

//вычисляем хеш-представление в байтах

byte[] byteHash = CSP.ComputeHash(bytes);

string hash = string.Empty;

//формируем одну цельную строку из массива

foreach (byte b in byteHash)

hash += string.Format("{0:x2}", b);

return hash;

}

Если данные соответствуют существующей учетной записи, то открывается главное окно для работы (рис. 3.10).

Рисунок 3.10 - Главное окно для работы

В первой вкладке мы можем просмотреть все таблицы в нашей базе данных (рис. 3.11).

Рисунок 3.11 - Просмотр всех таблиц

Код программы:

#region Вывести все таблицы через comboBox

private void Show_AllTable()

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";// текст запроса

SqlDataReader read = com.ExecuteReader();//получаем данные запроса

//обработка данных:

while (read.Read())

{

cb_AllTable.Items.Add(read.GetString(0));

}

read.Close();

connection.Close();// закрыли соединение

}

private void cb_AllTable_SelectedIndexChanged(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

com.CommandText = "Select * from " + cb_AllTable.SelectedItem.ToString();

sda = new SqlDataAdapter(com);

table = new DataTable();

sda.Fill(table);

dgv_AllTable.DataSource = table;

connection.Close();// закрыли соединение

}

#endregion

Перейдем на вкладку «Хозяин животного» (рис. 3.12). Здесь мы можем добавлять, удалять и изменять дынные, а также при нажатии кнопки «Сохранить изменения» данные в БД будут обновляться.

Рисунок 3.12 - Результат работы программы при нажатии кнопки «Добавить»

Код программы для события «Добавить».

// Добавить

private void btn_Add_Xoz_wivotnogo_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

com.CommandText = @"SELECT MAX (ID_Хозяин_животного) FROM [Хозяин_животного];";

int id = (int)com.ExecuteScalar() + 1;

com.CommandText = @"Insert into [Хозяин_животного] Values (" + id.ToString() + "," + "'" + tb_Familiya.Text + "'," + "'" + tb_Imya.Text + "'," + "'" + tb_Otchestvo.Text + "'," + "'" + tb_Adres.Text + "'," + " '" + tb_Telefon.Text + "');";

com.ExecuteNonQuery();

this.хозяин_животногоTableAdapter.Fill(this.msdbDataSet4.Хозяин_животного);

connection.Close();

}

Результат работы программы при нажатии кнопки «Удалить» показан на рисунке 3.13.

Рисунок 3.13 - Результат работы программы при нажатии кнопки «Удалить»

Код программы для события «Удалить».

// Удалить

private void btn_Delete_Xoz_wivotnogo_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

com.CommandText = @"Delete from [Хозяин_животного] where (ID_Хозяин_животного = " + "'" + tb_IDXoz.Text.ToString() + "')";

com.ExecuteNonQuery();

this.хозяин_животногоTableAdapter.Fill(this.msdbDataSet4.Хозяин_животного);

connection.Close();

}

Результат работы программы при нажатии кнопки «Изменить» показан на рисунке 3.14.

Рисунок 3.14 - Результат работы программы при нажатии кнопки «Изменить»

Код программы для события «Изменить».

// Изменить

private void btn_Change_Xoz_wivotnogo_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();// открыли строку

SqlCommand com = connection.CreateCommand();

string columnName = dtv_Xozyain.Columns[column].HeaderText;

string value = dtv_Xozyain.SelectedCells[0].Value.ToString();

string newValue = tb_NewXoz.Text;

com.CommandText = @"Update [Хозяин_животного] set " + columnName + " = '" + newValue + "' where (" + columnName + " = '" + value + "' );";

com.ExecuteNonQuery();

this.хозяин_животногоTableAdapter.Fill(this.msdbDataSet4.Хозяин_животного);

connection.Close();

}

Код программы для события «Сохранить результат»

// Сохранить изменения в БД

private void btn_Save_Rezult_Click(object sender, EventArgs e)

{

try

{

this.Validate();

this.врачBindingSource1.EndEdit();

this.врачTableAdapter.Update(this.msdbDataSet.Врач);

MessageBox.Show("Данные oбновлены!");

}

catch (System.Exception)

{

MessageBox.Show("Ошибка обновления!");

}

}

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

Для получения информации из таблицы «Диагноз» по полю «Кличка» из таблицы «Животное» нужно написать соответствующий запрос.

Но в первую очередь передадим в comboBox через запрос все значения в поле «Кличка» из таблицы «Животное».

// Вывод клички животного в comboBox

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = @"SELECT Кличка FROM [Животное]";// текст запроса

SqlDataReader read = com.ExecuteReader();//получаем данные запроса

cb_Klichka.Items.Clear();

//обработка данных:

while (read.Read())

{

cb_Klichka.Items.Add(read.GetString(0));

}

read.Close();

connection.Close();// закрыли соединение

Код запроса на вывод диагноза по кличке животного.

// Запрос (вывести диагноз по кличке животного)

private void btn_diagnoz_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT Диагноз.Наименование FROM Диагноз INNER JOIN Животное ON Диагноз.ID_Диагноз = Животное.Диагноз WHERE (Животное.Кличка = '" + cb_Klichka.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

sda.Fill(table);

dgv_Rezult.DataSource = table;

dgv_Rezult.Sort(dgv_Rezult.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение

}

Выбрав в comboBox кличку животного и при нажатии кнопки «Вывести поставленный диагноз», в dataGridView отобразится таблица с результатом работы запроса (рис. 3.15).

Рисунок 3.15 - Результат работы запроса

Напишем запрос, который по нажатию кнопки «Вывести назначенный курс лечения», будет выводить назначенный курс лечения по кличке животного (рис. 3.16).

// Запрос (вывести назначенный курс лечение по кличке животного)

private void btn_lechenie_wivotnoe_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT Лечение.Наименование, Лечение.Дата FROM Животное INNER JOIN Лечение ON Животное.ID_Животное = Лечение.Животное WHERE (Животное.Кличка = '" + cb_Klichka.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

table.Clear();

sda.Fill(table);

dgv_Rezult.DataSource = table;

dgv_Rezult.Sort(dgv_Rezult.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение

}

Рисунок 3.16 - Результат работы запроса

Также можно вывести итоговую сумму лечения (рис. 3.17). Код запроса будет соответствующим.

// Запрос (вывести итоговую сумму лечения по кличке животного)

private void btn_Sum_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT SUM(Лечение.Стоимость) FROM Животное INNER JOIN Лечение ON Животное.ID_Животное = Лечение.Животное WHERE (Животное.Кличка = '" + cb_Klichka.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

table.Clear();

sda.Fill(table);

dgv_Rezult.DataSource = table;

dgv_Rezult.Sort(dgv_Rezult.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение

}

Рисунок 3.17 - Результат работы запроса

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

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

Отчет по «Животным» выводит информацию о животном (кличку и дату рождения), а также данные о поставленном диагнозе и назначенном курсе лечения (рис. 3.18).

// Вывести отчет по животным в dataGridView

private void btn_Otchet2_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT Животное.Кличка, Животное.Дата_рождения, Диагноз.Наименование, Диагноз.Дата, Лечение.Наименование AS Назначенное_лечение, Лечение.Дата AS Дата FROM Диагноз INNER JOIN Животное ON Диагноз.ID_Диагноз = Животное.Диагноз INNER JOIN Лечение ON Диагноз.ID_Диагноз = Лечение.Диагноз AND Животное.ID_Животное = Лечение.Животное WHERE (Животное.Кличка = '" + cb_Klichka.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

table.Clear();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

sda.Fill(table);

dgv_Otchet.DataSource = table;

dgv_Otchet.Sort(dgv_Otchet.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение }

Рисунок 3.18 - Результат работы запроса

При нажатии на кнопку «Вывести отчет в документ EXCEL» вся информация из dataGridView отобразится в документе Excel (рис. 3.19).

// Вывод отчета в документ EXCEL

private void btn_Otchet_Click(object sender, EventArgs e)

{

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;

Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;

//Книга

ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);

//Таблица

ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);

for (int i = 1; i < dgv_Otchet.Columns.Count + 1; i++)

{

ExcelWorkSheet.Cells[1, i] = dgv_Otchet.Columns[i - 1].HeaderText;

}

for (int i = 0; i < dgv_Otchet.Rows.Count; i++)

{

for (int j = 0; j < dgv_Otchet.Columns.Count; j++)

{

ExcelWorkSheet.Cells[i + 2, j + 1] = dgv_Otchet.Rows[i].Cells[j].Value;

}

}

//Вызываем созданный файл EXCEL

ExcelApp.Visible = true;

ExcelApp.UserControl = true; }

Рисунок 3.19 - Отчет по животным

Отчет по «врачам» выводит данные о враче, а также данные о поставленном им диагнозе (рис. 3.20).

// Вывести отчет по врачам в dataGridView

private void btn_Otchet1_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT Врач.Фамилия, Врач.Имя, Врач.Отчество, Диагноз.Наименование, Диагноз.Дата FROM Врач INNER JOIN Диагноз ON Врач.ID_Врач = Диагноз.Врач WHERE (Врач.Фамилия = '" + cb_FamiliyaVrach.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

table.Clear();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

sda.Fill(table);

dgv_Otchet.DataSource = table;

dgv_Otchet.Sort(dgv_Otchet.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение

}

Рисунок 3.20 - Отчет по врачам

Отчет по заболеваниям будет состоять из названия диагноза, даты его постановления, клички животного, которому поставили диагноз и назначили лечение и фамилии врача (рис. 3.21).

// Вывести отчет по заболеваниям в dataGridView

private void btn_Otchet3_Click(object sender, EventArgs e)

{

connection = new SqlConnection(MyConString);

connection.Open();

SqlCommand com = connection.CreateCommand();// подключили запрос к базе данных

com.CommandText = "SELECT Диагноз.Наименование, Диагноз.Дата, Животное.Кличка, Врач.Фамилия FROM Врач INNER JOIN Диагноз ON Врач.ID_Врач = Диагноз.Врач INNER JOIN Животное ON Врач.ID_Врач = Животное.Врач AND Диагноз.ID_Диагноз = Животное.Диагноз WHERE (Диагноз.Наименование = '" + cb_Zabolevanie.SelectedItem.ToString() + "');";// текст запроса

table = new DataTable();

table.Clear();

sda = new SqlDataAdapter(com);

cb = new SqlCommandBuilder(sda);

sda.Fill(table);

dgv_Otchet.DataSource = table;

dgv_Otchet.Sort(dgv_Otchet.Columns[0], ListSortDirection.Ascending);

connection.Close();// закрыли соединение

}

Рисунок 3.21 - Отчет по заболеваниям

3.3 Описание контрольного примера

При первом запуске программы перед пользователем возникнет окно авторизации (рис. 3.22).

Рисунок 3.22 - Окно авторизации

Если пользователь не зарегистрирован в системе, то ему следует нажать кнопку «Регистрация». Откроется новое окно как на рисунке 3.23. Зарегистрируем нового пользователя (врача).

Рисунок 3.23 - Окно регистрации

После нажатия кнопки «Добавить нового пользователя» и «Внести данные пользователя» происходит переход в окно «Вход в систему». Пользователь должен ввести свой новый логин и пароль (рис. 3.24).

Рисунок 3.24 - Окно авторизации

После нажатия на кнопку «Вход» производится обращение к БД. В случае правильного ввода логина и пароля, появляется сообщение «Подключение произошло успешно», что означает о разрешении дальнейшей работы.

После корректного подключения пользователь может переходить по всем вкладкам. Во вкладке «Таблицы» можно просмотреть все таблицы и содержащиеся в них данные (рис. 3.25).

Рисунок 3.25 - Вкладка «Таблицы»

На вкладке «Животное» (рис. 3.26) можно просмотреть всю информацию о животном, а также добавить, изменить или удалить данные.

Для добавления информации в таблицу нужно заполнить соответствующие поля и нажать кнопку «Добавить».

Рисунок 3.26 - Добавление данных в таблицу «Животное»

Для удаления данных необходимо указать идентификатор животного и нажать кнопку «Удалить».

Для изменения данных выделить ячейку, которую нужно изменить. Появится окно «Вы уверены, что хотите изменить/удалить значения?». При нажатии на кнопку «Ок», ввести в textBox новое значение и нажать кнопку «Изменить».

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

Во вкладке «Запросы/Отчеты» (рис. 3.27) отображён пример работы с запросами и отчетами.

Чтобы вывести итоговую сумму, затраченную на лечение животного, нужно в comboBox выбрать кличку животного и нажать кнопку «Вывести итоговую сумму лечения».

Рисунок 3.27 - Запрос на вывод итоговой суммы лечения

Чтобы вывести отчет по врачам, следует в comboBox выбрать фамилию врача и нажать кнопку «Отчет по врачам». В dataGridView отобразится результат запроса (рис. 3.28).

Рисунок 3.28 - Отчет по врачам

При нажатии кнопки «Вывести отчет в документе Excel» данные из dataGridView отобразятся в документе Excel.

ВЫВОДЫ

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

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

В результате проделанной работы получилась реляционная база данных, поддерживаемая СУБД Microsoft SQL Server Express Edition, в которой содержится 6 таблиц. В этих таблицах содержатся сведения о сущностях предметной области «Предоставление услуг ветеринарной клиникой»: животных, их хозяевах, врачах, поставленных диагнозах, назначенном курсе лечения и т.д. В каждой из этих таблиц содержится некоторое количество записей, необходимых для проверки работоспособности приложения, разработанного на С# специально для администрирования созданной базы данных.

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

На текущий момент эта цель достигнута, поэтому выполнение данной работы завершено.

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

1. Дейт, К. Дж. Введение в системы баз данных [Текст]: Пер. с англ. 7-е изд. / К. Дж. Дейт - М.: Издательский дом Вильямс, 2001. - 1072 с.

2. Ульман, Дж. Д. Введение в системы баз данных [Текст]: Пер. с англ / Дж. Ульман, Дж. Уидом; изд. Лори, 2004. - 374 с.

3. Оленчикова, Т.Ю. Проектирование баз данных [Текст]: метод. указания к курсовому проектированию для студ. очной и заочной формы обучения / Т.Ю. Оленчикова; Южно-уральский гос. ун-т., М.: Москва, 2011. - 37 с.

4. Интернет и базы данных. Часть 02. Нормализация базы данных [Электронный ресурс] - Режим доступа: - 13.04.2013 г.

5. Реляционная модель данных, нормализация баз данных [Электронный ресурс] - Режим доступа: 13.04.2013 г.

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