Разработка прикладного программного обеспечения деятельности предприятия в системе клиент-сервер
Министерство транспорта РФ
Федеральное агентство железнодорожного транспорта
ГОУ ВПО «Дальневосточный государственный университет путей сообщения»
Кафедра «Информационные системы и технологии»
Курсовая работа:
«Разработка прикладного программного обеспечения деятельности предприятия в системе клиент-сервер»
По дисциплине: «Современные серверы баз данных»
Вариант 23
Выполнил: Шолков И.Д.
группа 230
Проверил: Гурвиц Г.А.
Хабаровск 2009
Оглавление
Введение
Постановка задачи
Выбор редакции SQL Server 2005
Установка SQL Server 2005
Нормализация данных
Реализация базы данных
1.Создание новой базы данных
2.Создание таблиц
3. Создание ограничений для столбцов таблицы
4.Создание схемы данных
Схема данных
Триггеры
1.Триггеры на обновление данных
2.Триггеры на добавление данных
3.Триггеры на удаление данных
Хранимые процедуры
Представления
Безопасность базы данных
1.Создание логинов
2. Создание пользователей
Заключение
Список литературы
Введение
Microsoft SQL Server — система управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для небольших и средних по размеру баз данных, и в последние 5 лет — для крупных баз данных масштаба предприятия, конкурирует с другими СУБД в этом сегменте рынка.
Microsoft SQL Server в качестве языка запросов использует версию SQL, получившую название Transact-SQL (сокращённо T-SQL), являющуюся реализацией SQL-92 (стандарт ISO для SQL) с множественными расширениями. T-SQL позволяет использовать дополнительный синтаксис для хранимых процедур и обеспечивает поддержку транзакций (взаимодействие базы данных с управляющим приложением). Microsoft SQL Server и Sybase ASE для взаимодействия с сетью используют протокол уровня приложения под названием Tabular Data Stream (TDS, протокол передачи табличных данных). Протокол TDS также был реализован в проекте FreeTDS с целью обеспечить различным приложениям возможность взаимодействия с базами данных Microsoft SQL Server и Sybase.
Microsoft SQL Server также поддерживает Open Database Connectivity (ODBC) — интерфейс взаимодействия приложений с СУБД. Версия SQL Server 2005 обеспечивает возможность подключения пользователей через веб-сервисы, использующие протокол SOAP. Это позволяет клиентским программам, не предназначенным для Windows, кроссплатформенно соединяться с SQL Server. Microsoft также выпустила сертифицированный драйвер JDBC, позволяющий приложениям под управлением Java (таким как BEA и IBM WebSphere) соединяться с Microsoft SQL Server 2000 и 2005.
SQL Server поддерживает зеркалирование и кластеризацию баз данных. Кластер сервера SQL — это совокупность одинаково конфигурированных серверов; такая схема помогает распределить рабочую нагрузку между несколькими серверами. Все сервера имеют одно виртуальное имя, и данные распределяются по IP адресам машин кластера в течение рабочего цикла. Также в случае отказа или сбоя на одном из серверов кластера доступен автоматический перенос нагрузки на другой сервер.
SQL Server поддерживает избыточное дублирование данных по трем сценариям:
Снимок: Производится «снимок» базы данных, который сервер отправляет получателям.
История изменений: Все изменения базы данных непрерывно передаются пользователям.
Синхронизация с другими серверами: Базы данных нескольких серверов синхронизируются между собой. Изменения всех баз данных происходят независимо друг от друга на каждом сервере, а при синхронизации происходит сверка данных. Данный тип дублирования предусматривает возможность разрешения противоречий между БД.
В SQL Server 2008 встроена поддержка .NET Framework. Благодаря этому, хранимые процедуры БД могут быть написаны на любом языке платформы .NET, используя полный набор библиотек, доступных для .NET Framework, включая Common Type System (система обращения с типами данных в Microsoft .NET Framework). Однако, в отличие от других процессов, .NET Framework, будучи базисной системой для SQL Server 2008, выделяет дополнительную память и выстраивает средства управления SQL Server вместо того, чтобы использовать встроенные средства Windows. Это повышает производительность в сравнении с общими алгоритмами Windows, так как алгоритмы распределения ресурсов специально настроены для использования в структурах SQL Server.
Постановка задачи
Разработать прикладное программное обеспечение деятельности регистратуры ведомственной поликлиники.
Работники регистратуры организуют запись пациентов на прием к врачам поликлиники. Так как поликлиника ведомственная, медицинское обслуживание работников предприятия – бесплатное (за счет средств предприятия).
«Посторонние» пациенты также могут воспользоваться услугами поликлиники, полностью оплатив затраты на лечение. Определение стоимости лечения и выдача платежных документов для таких больных входит в круг обязанностей работников регистратуры. Врач ведет прием всегда в одном кабинете. Приемные дни занесены в расписание работы поликлиники.
На каждого пациента в регистратуре заводится карточка. В начале приема карточки больных, записавшихся на прием, доставляются работником регистратуры в кабинет врача.
Выбор редакции SQL Server 2005
На сегодняшний день существуют несколько редакций MS SQL Server2005:
Enterprise Edition-самая мощная и самая дорогая версия SQL Server. Предназначена для корпораций. Работает на кластерных системах;
Developer Edition-обладает возможностями Enterprise Edition и значительно меньшей стоимостью. Имеет ограничение на число одновременных подключений;
Enterprise Edition 120-day Evaluation-бесплатная версия, работает только 4 месяца;
Standard Edition-несколько менее функциональная версия, чем Enterprise Edition;
Workgroup Edition-предназначена для размещения БД уровня отдела предприятия;
Express Edition-имеет ограничения на размер БД(4Гб). Полностью бесплатная версия;
Mobile Edition-для смартфонов и Tablet PC.
Все версии MS SQL Server 2005, кроме Mobile Edition, имеют одинаковый интерфейс и отличаются только функциональными возможностями.
На этапе разработки лучше всего будет обойтись и бесплатной версией продукта- Express Edition.
Установка SQL Server 2005
Выбор компонентов
SQL Server Database Services-представляет ядро БД.
Workstation components, Books Online and development tools-компоненты для рабочей станции и документации по SQL Server с примерами.
Выбор экземпляра MS SQL Server 2005
На одном компьютере можно установить несколько копий SQL Server. Первый экземпляр- экземпляр по умолчанию. Его имя совпадает с именем компьютера.
Выбор учетной записи для служб SQL Server
Use the built-in System account-Local System;
Start services at the end of setup-SQL Server.
Выбор режима аутентификации сервера
Windows Authentication Mode-Для подключения к MS SQL Server будут использоваться учетные записи ОС.
Кодовая страница и порядок сортировки
Collation Settings:
Case-sensitive-заставит SQL Server учитывать регистр символов;
Accent-sensitive.
Нормализация данных
Приступая к созданию приложения, немаловажно тщательным образом спроектировать структуру его таблиц. Этот фактор в конечном итоге влияет на качество работы всего программного комплекса в целом. Поэтому целесообразно привести таблицы базы данных к третьей нормальной форме.
1.Значения всех полей таблицы должны быть атомарные, и в ней не должно быть повторяющихся групп полей. Например, следует поместить названия улиц в отдельную таблицу, поскольку про вводе этих значений вручную не исключены грамматические ошибки и различные варианты написания, что приведет к полной неработоспособности программы.
2. Любое неключевое поле должно однозначно идентифицироваться полным набором ключевых полей.
3.Ни одно из неключевых полей таблицы не должно идентифицироваться с помощью другого неключевого поля.
После нормализации данных получим следующие таблицы:
Таблица улиц.
Таблица записей к врачам.
Таблица районов.
Таблица докторов.
5.Таблица льгот.
6.Таблица пациентов.
7.Таблица университетов.
Реализация базы данных
1.Создание новой базы данных
Для создание новой базы данных необходимо:
Щелкнуть правой клавишей мыши по строке Databases.
В появившемся контекстном меню выбрать пункт New Database.
В появившемся окне ввести имя новой базы данных.
2.Создание таблиц
Находим свою БД, раскрываем ее, щелкаем правой кнопкой на вкладку «Tables» и нажимаем «New Table»
Таблица открывается в режиме Конструктора в котором мы можем задать название и тип полей. Также здесь мы можем создать ключевое поле, для этого щелкнуть правой кнопкой на нужное поле и выбрать «Set primary key»
Нажимаем кнопку сохранить, вводим название таблицы и жмем ОК. Таблица создана. Таким образом, создаем все таблицы нашей БД
3. Создание ограничений для столбцов таблицы
Также в создаваемой базе данных SQL Server даёт нам возможность контролировать правильность вводимой информации. Для этого применяются ограничения на значение отдельных полей создаваемых таблиц.
Так для таблицы «tblDoctor» можно отследить, что идентификатор доктора я будет всегда числом положительным. Для этого находим нужную таблицу, раскрываем ее, щелкаем правой клавишей мыши по вкладке «Constraint» и нажимаем «New Constraint» Указываем имя ограничения.
Находим строчку «Expression», нажимаем на многоточие и в появившемся окне пишем текст ограничения
4.Создание схемы данных
Для обеспечения правильной работы приложения, необходимо разработать схему базы данных, обеспечивающую необходимые связи между таблицами. Также необходимо избежать повторяемости данных
Для создания диаграммы находим свою БД, раскрываем ее, щелкаем правой кнопкой на вкладку «Database Diagrams» и нажимаем «New Database Diagram»
После появиться форма со списком таблиц нашей базы данных, которые можно добавить в диаграмму. Получим диаграмму с таблицами, но без связей. Связи создаются аналогично Access т.е. нажимаем ЛКМ на поле, которое необходимо связать и не отпуская ее, ведем указатель на поле с которым требуется связать, после чего увидим:
Далее обеспечиваем целостность данных, делаем каскадным обновление и удаление записей в таблицах:
После создания всех связей, которые мы выделили при нормализации, мы получим:
Схема данных
Триггеры
Триггеры это еще одна хорошая отличительная черта SQL Server. По своим функциям они напоминают события, но уникальны они тем, что содержаться на сервере, уникальны для каждой таблицы и выходят на исполнение при изменении, добавлении или удалении записей в таблицу. Они являются «последним» барьером между клиентом и таблицами на сервере, поэтому с их помощью можно проконтролировать изменение таблиц.
Для создания тригера находим нужную нам таблицу, раскрываем ее, щелкаем правой кнопкой на вкладку «Triggers» и нажимаем «New Trigger»:
1.Триггеры на обновление данных
Для моей базы данных триггеры на обновление были написаны для всех таблиц . Синтаксис их похож, поэтому приведу пример на основе таблицы district:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dbo].[T_tblDistrict_UTrig] ON [dbo].[tblDistrict] FOR UPDATE AS
SET NOCOUNT ON
/* * Каскадные обновления в 'tblPacient' */
IF UPDATE(DistrictID)
BEGIN
UPDATE tblPacient
SET tblPacient.DistrictID = inserted.DistrictID
FROM tblPacient, deleted, inserted
WHERE deleted.DistrictID = tblPacient.DistrictID
END
update District set
UserName=user_name(),
[DateTime]=GetDate(),
HostName=host_name(),
ClientName=app_name()
from inserted Join District
on inserted.DistrictID=District.DistrictID
2.Триггеры на добавление данных
Для моей базы данных триггеры на добавление были написаны для таблиц tblDoctor, tblPacient и tblPriem.
Пример:
Для таблицы tblDoctor:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dbo].[T_tblDoctor_ITrig] ON [dbo].[tblDoctor] FOR INSERT AS
SET NOCOUNT ON
/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblUnivers' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblUnivers, inserted WHERE (tblUnivers.UniversityID = inserted.UniversityID))
BEGIN
RAISERROR 44447 ‘Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице “tblUnivers".'
ROLLBACK TRANSACTION
END
Для таблицы tblPriem:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dbo].[T_tblPriem_ITrig] ON [dbo].[tblPriem] FOR INSERT AS
SET NOCOUNT ON
/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblDoctor' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDoctor, inserted WHERE (tblDoctor.DoctorID = inserted.DoctorID))
BEGIN
RAISERROR 44447 Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице "tblDoctor".'
ROLLBACK TRANSACTION
END
/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblPacient' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblPacient, inserted WHERE (tblPacient.Number = inserted.Number))
BEGIN
RAISERROR 44447 Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице "tblPacient".'
ROLLBACK TRANSACTION
END
3.Триггеры на удаление данных
Для моей базы данных были написаны триггеры на удаление для всех таблиц .
Пример:
Для таблицы tblUniversity:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dbo].[T_tblUnivers_DTrig] ON [dbo].[tblUnivers] FOR DELETE AS
SET NOCOUNT ON
/* * Каскадные удаления в 'tblDoctor' */
DELETE tblDoctor FROM deleted, tblDoctor WHERE deleted.UniversityID = tblDoctor.UniversityID
Хранимые процедуры
При создании приложения при помощи Microsoft SQL Server язык программирования Transact-SQL является основным программным интерфейсом между приложениями и базой данных Microsoft SQL Server. Использование программ Transact-SQL подразумевает два способа хранения и выполнения.
Можно хранить программы локально и создавать приложения, отправляющие команды SQL Server и обрабатывающие результаты.
Можно хранить программы в виде хранимых процедур в SQL Server и создавать приложения, исполняющие хранимые процедуры и обрабатывающие результаты.
Хранимые процедуры в Microsoft SQL Server аналогичны процедурам в других языках программирования:
они обрабатывают входные аргументы и возвращают вызывающей процедуре или пакету значения в виде выходных аргументов;
они содержат программные инструкции, которые выполняют операции в базе данных, в том числе вызывающие другие процедуры;
они возвращают значение состояния вызывающей процедуре или пакету, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).
Хранимые процедуры можно выполнять с помощью инструкции Transact-SQL EXECUTE. Хранимые процедуры отличаются от функций тем, что они не возвращают значения на месте своих имен, и их нельзя непосредственно использовать в выражениях.
По сравнению с программами Transact-SQL, которые хранятся локально на клиентских компьютерах, хранимые процедуры SQL Server имеют следующие преимущества.
Хранимые процедуры регистрируются на сервере.
Хранимые процедуры могут иметь атрибуты безопасности (например, разрешения) и цепочки владения, кроме того, к ним можно прикреплять сертификаты. Пользователи могут обладать разрешениями на выполнение хранимых процедур вместо прямых разрешений для работы с объектами, на которые ссылаются эти процедуры.
Хранимые процедуры позволяют сделать защиту приложений более надежной. Параметризованные хранимые процедуры могут защитить приложения от атак, осуществляемых путем инжекции кода SQL.
Хранимые процедуры поддерживают модульное программирование. Процедуру можно создать один раз и потом по мере необходимости вызывать ее в программе любое число раз. Это делает обслуживание приложения более удобным и позволяет унифицировать доступ приложений к базе данных.
Хранимые процедуры представляют собой именованный код, дающий возможность отсроченного связывания. Это обеспечивает уровень косвенности, упрощающий развитие программного кода.
Хранимые процедуры позволяют уменьшить сетевой трафик. Операцию, занимающую сотни строк программного кода Transact-SQL, можно выполнить в одной инструкции, которая обрабатывает процедуру, а не отправляет этот код по сети.
Для создания хранимой процедуры находим нужную нам базу данных, раскрываем ее, щелкаем правой кнопкой на вкладку «Programmability»,там находим вкладку «Stored Procedures» и нажимаем «New Stored Procedure»:
Для Выполнения процедуры необходимо найти ее во вкладке «Stored Procedures» , нажать ПКМ и выбрать «Execute Stored Procedure»
Примеры хранимых процедур:
1) set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Запрос для вывода списка пациентов проживающих в определенном районе
ALTER PROCEDURE [dbo].[DistrPacient]
AS
BEGIN
SELECT tblDistrict.District, tblPacient.PacientSurname, tblPacient.PacientName
FROM tblDistrict INNER JOIN
tblPacient ON tblDistrict.DistrictID = tblPacient.DistrictID
WHERE tblDistrict.DistrictID = tblPacient.DistrictID
END
2) set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Запрос для вывода врачей и обслуживаемых ими пациентов.
ALTER PROCEDURE [dbo].[DoctorPacient]
AS
BEGIN
SELECT dbo.tblPacient.PacientSurname, dbo.tblPacient.PacientName, dbo.tblPacient.PolicyNumber, dbo.tblDoctor.LastName, dbo.tblDoctor.FirstName
FROM dbo.tblPacient, dbo.tblDoctor
WHERE dbo.tblPacient.DoctorID = dbo.tblDoctor.DoctorID
END
3) set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Запрос для вывода льшот у конкретных пациентов.
ALTER PROCEDURE [dbo].[ExcemptPacient]
AS
BEGIN
SELECT tblLgota.ExemptType, tblLgota.Exempt, tblPacient.PacientSurname, tblPacient.PacientName
FROM tblPacient INNER JOIN
tblLgota ON tblPacient.ExemptID = tblLgota.ExemptID
END
Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом. Как и настоящая таблица, представление состоит из совокупности именованных столбцов и строк данных. Пока представление не будет проиндексировано, оно не существует в базе данных как хранимая совокупность значений. Строки и столбцы данных извлекаются из таблиц, указанных в определяющем представление запросе и динамически создаваемых при обращениях к представлению.
Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. Определяющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлениях текущей или других баз данных.
Для создания представления находим нужную нам базу данных, раскрываем ее, щелкаем правой кнопкой на вкладку «Views» и нажимаем «New View»:
Затем выбираем таблицы, которые будут участвовать в представлении. Галочками выбираем нужные поля и получим.
В результате выполнения представления получим:
Аналогичным образом создаются другие представления, которые можно просмотреть на диске прилагаемому к курсовой работе.
Безопасность базы данных
Очень важно при создании программного комплекса обеспечить должную безопасность, чтобы у каждого пользователя был только необходимый ему для работы набор прав.
1.Создание логинов
Создадим два логина. Первый будет для администратора, со всеми правами к нашей базе данных, второй для обычный пользователь с ограниченным набором прав.
Для создания логинов необходимо в Object Explorer найти вкладку «Security», раскрыть её, щелкнуть правой кнопкой мыши по вкладке «Logins» и нажимаем «New Login»:
В открывшемся окне необходимо ввести имя логина и пароль на вход. Так же рекомендую снять галку «Enforce password expiration» ( Задать срок окончания действия пароля).
Аналогичным образом создадим второй логин с именем user.
2. Создание пользователей
Теперь перейдем в пункт «Security» созданной нами базы данных в пункт «Users» и создадим двух пользователей.
Пользователю ILYA разрешим просмотр и выполнение хранимых процедур и запретим удаление и изменение. Пользователю Admin разрешены любые действия.
Заключение
Прежде всего, SQL Server является сервером баз данных. Сама по себе СУБД не может удовлетворить потребности конечного пользователя (если, конечно, не рассматривать редактор запросов как интерфейс пользователя). Если вы мало знакомы с моделью "клиент/сервер", ее нужно понять, в противном случае будет сложно понять и саму СУБД.
С технической точки зрения термин клиент/сервер связан с двумя взаимодействующими процессами. Клиентский процесс запрашивает у серверного процесса некую службу, которая, в свою очередь, обрабатывает запрос клиента. Клиентский и серверный процессы могут быть запущены на разных компьютерах или на одном. В данном вопросе важно само взаимодействие процессов, а не их физическое размещение. В противоположность настольным базам данных, таким как Microsoft Access, выполняющим всю работу на компьютере клиента, базы данных с архитектурой "клиент/сервер" подобны библиотекарю, который принимает запрос клиента, ищет запрошенную информацию и возвращает фотокопию найденных материалов. Содержащиеся в библиотеке реальные материалы никогда не выходят из поля зрения библиотекаря.
В базах данных с архитектурой "клиент/сервер" клиент подготавливает запрос на языке SQL (небольшое текстовое сообщение) и отсылает его на сервер баз данных, который читает и обрабатывает его. В сервере поддерживается система безопасности, индексируются хранящиеся материалы, заносятся и обрабатываются данные, выполняются серверные программы, и выполняется доставка результатов запросов клиенту.
Вся работа с базой данных выполняется на сервере. Если клиент запрашивает какой-либо набор данных, он подготавливается на сервере, и его копия доставляется клиенту. Реальные данные и индексы никогда не покидают пределы сервера. Когда клиент запрашивает выполнение операции вставки, обновления или удаления, сервер получает этот запрос и сам обрабатывает его.
Клиент-серверная модель базы данных обладает рядом преимуществ по сравнению с настольной моделью.
■ Повышена достоверность данных, поскольку они не разбросаны по всей сети и разным приложениям. Данные обслуживает только один процесс.
■ Ограничения целостности данных и бизнес-правила могут поддерживаться на уровне сервера, в результате чего они строго соблюдаются.
■ Повышена безопасность данных, поскольку база данных хранит их в пределах одного сервера. Открыть файл данных, защищаемый сервером, гораздо сложнее, чем файл на рабочей станции.
■ Повышена производительность и лучше сбалансированы рабочие станции, поскольку большая часть работы (обработка базы данных) выполняется на сервере, а рабочие станции берут на себя только обслуживание интерфейса пользователя. Поскольку серверный процесс обеспечивает быстрый доступ пользователя к файлам данных, а большая часть данных кэширована в памяти, операции с базой данных выполняются быстрее, чем в многопользовательской настольной среде. Сервер баз данных обслуживает всех пользователей, работающих с приложениями баз данных, таким образом, гораздо проще оценить стоимость устанавливаемого сервера.
■ В значительной мере сокращаются сетевые потоки. По сравнению с сетевыми потоками, создаваемыми многопользовательскими настольными системами, потоки в архитектуре "клиент/сервер" можно сравнить с одиноким мотоциклистом, несущимся по свободной 10-полосной автостраде. Без преувеличения! Замена перегруженной настольной системы базой данных "клиент/сервер" способна сократить сетевые потоки больше чем на 95%.
■ Снижение сетевых потоков в системах "клиент/сервер" приводит к тому, что приложения хорошо работают даже в распределенной среде и даже при наличии медленных соединений. Такие маленькие сетевые потоки позволяют уравнять в производительности локальную сеть со скоростью 100 Мбит/с с модемным подключением со скоростью 56 Кбит/с для клиентских приложений, использующих .NET технологии и подключенных к базе данных SQL Server.
Список литературы
Гурвиц Г.А. Microsoft Access 2007. Разработка приложений на реальном примере.- СПб.: БХВ-Петербург, 2007. – 672 с.: ил. + CD-ROM
Михеев Р.Н. MS SQL Server 2005 для администраторов. – СПб.: БХВ – Петербург, 2007 – 544 с.: ил.
Станек Уильям Р. MS SQL Server 2005. Справочник администратора. – М.: Издательство «Русская Редакция», 2006. – 544с.: ил.