Создание прикладной системы управления базами данных (в объеме создания и связывания таблиц)

1 ОБЩИЕ МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ РАБОТЫ

Целью лабораторной работы является ознакомление с РСУБД SQL Server 2000 и создание прикладной системы управления базами данных (в объеме создания и связывания таблиц). Для достижения поставленной цели необходимо решить следующие задачи:

изучить состав и назначение объектов реляционной системы управления базами данных (РСУБД) SQL Server 2000 (запуск и остановка соответствующих служб, управление БД при помощи Enterprise Manager, создание запросов при помощи Query Analyzer, выполнение трассировки при помощи SQL Profiler);

изучить этапы проектирования баз данных (БД);

провести проектирование БД для определенной предметной области (согласно заданию);

создать базу данных с таблицами.

1.2. В результате выполнения работы студенты должны знать:

назначение и основные этапы проектирования баз данных;

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

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

назначение и основные возможности СУБД;

состав и назначение объектов, входящих в БД;

создание таблиц в SQL Server.

1.3. Используемые программно-аппаратные средства: персональный компьютер стандартной конфигурации; операционная система MS Windows 2000/XP; система управления базами данных Microsoft SQL Server 2000 Developer Edition.

1.4. В процессе выполнения лабораторных работ студент должен:

научиться производить постановку задачи и формализованное описание предметной области для построений системы управления базами данных;

ознакомиться с назначением и основными возможностями СУБД;

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

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

1.6. Указания по оформлению отчета

Отчет должен содержать постановку задачи, описание приемов работы с СУБД SQL Server 2000, результаты выполнения работы, выводы.

1.7. Указания по сдаче зачета преподавателю

Для сдачи зачета необходимо

предъявить отчет;

ответить на контрольные вопросы.

2 ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ ДЛЯ ДОМАШНЕГО ИЗУЧЕНИЯ

2.1 Службы SQL Server и способы управления ими.

Служба – это программа или процесс, выполняющие специфические функции поддержки других программ. При запуске SQL Server, в операционной системе Windows NT или Windows 2000 запускается служба SQL Server. Эта служба управляет файлами баз данных, исполняет операторы Transact-SQL, распределяет ресурсы среди пользовательских соединений, исполняющихся одновременно, проверяет непротиворечивость данных и выполняет еще много других задач. При инсталляции одного или нескольких экземпляров SQL Server, службы отдельных экземпляров SQL Server будут иметь имена MSSQL$ИмяЭкземпляра, где ИмяЭкземпляра - имя экземпляра, назначенное вами при инсталляции.

Инсталляция службы SQL Server Agent производится по умолчанию при инсталляции SQL Server.

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

Службы Microsoft Distributed Transaction Coordinator и Microsoft Search могут быть не инсталлированы. Чтобы их установить нужно снова запустить инсталляционную программу SQL Server. Они будут иметь названия DTC Client Support и Full-Text Search, соответственно.

Microsoft Distributed Transaction Coordinator - это администратор транзакций (transaction manager), при помощи которого в транзакции приложений можно включать данные из различных источников, в том числе данные из баз данных с удаленных компьютеров. Это значит, что при помощи одной транзакции можно обновлять данные на многих серверах, доступных через сеть. Администратор транзакций гарантирует, что все обновления станут постоянными для всех источников данных (если транзакция зафиксирована) или, в случае ошибки, что для всех источников данных будет произведен откат всех изменений.

Служба Microsoft Search нужна для полнотекстового индексирования и поиска. Благодаря полнотекстовому индексированию возможно выполнение более сложного поиска среди данных, содержащих текстовые строки. Например, можно искать слова, близкие к заданному слову, или можно искать определенную фразу.

Существует несколько инструментальных средств для остановки и запуска служб SQL Server: SQL Server Service Manager, SQL Server Enterprise Manager и Windows 2000 Service Control Manager.

2.1.1 Применение SQL Server Service Manager

Для запуска или остановки служб SQL Server при помощи SQL Server Service Manager, нужно выполнить следующие действия:

1. Нажать на экранную кнопку Пуск, выбрать Программы/Microsoft SQL Server/Service Manager или ввести в командной строке sqlmangr.exe. Появится приложение Service Manager (рис.1).

2. В выпадающих списках Server и Services будут показаны локальное имя сервера и название службы SQL Server. В ниспадающем списке Server нужно выбрать имя сервера, службами которого нужно управлять. В ниспадающем списке Services выбрать службу, которой нужно управлять – SQL Server (MSSQLSERVER), Microsoft Distributed Transaction Coordinator (Distributed Transaction Coordinator), Microsoft Search (Microsoft Search) или SQL Server Agent (SQLSERVERAGENT).

Рис.1 Окно SQL Server Service Manager.

3. Нажимая на соответствующие экранные кнопки, можно запустить или остановить выбранную службу. Символ в кружочке, несколько левее и ниже центра диалогового окна, показывает текущее состояние выбранной службы. Если служба SQL Server находится в приостановленном состоянии, то для её возобновления нажмите на кнопку Start/Continue (Запустить/Продолжить). Приостановка (pausing) SQL Server запрещает пользователям входить в систему. Если остановить SQL Server без приостановки, то все процессы SQL Server будут завершены немедленно. Остановка (stopping) запрещает новые соединения и отсоединяет пользователей, которые соединены в данный момент.

4. Когда Service Manager запущен, его окно обновляется через каждые 5 секунд. Чтобы изменить интервал обновления, нажмите на маленький значок-иконку в левом верхнем углу диалогового окна, тогда появится меню System, в котором нужно выбрать Options, в результате чего появится диалоговое окно SQL Server Service Manager Options (рис.2).

Рис. 2. Диалоговое окно SOL Server Service Manager Options

В поле Polling interval (Интервал опроса) можно задать другой интервал опроса для служб (в секундах). Если установить флажок Verify service control action (Подтверждать действия по управлению службой), то Service Manager будет проверять все действия по запуску, остановке и приостановке служб, запрашивая в диалоговом окне подтверждение на выполнение действия. Настройки интервала опроса и подтверждения действий задаются одинаковыми для всех четырех служб.

2.1.2 Применение Windows 2000 Service Control Manager

Службы SQL Server можно запускать и останавливать также при помощи Windows 2000 Service Control Manager, как локально, так и через сеть. Можно сконфигурировать службы SQL Server на автоматический запуск при каждом запуске компьютера. Чтобы службы SQL Server запускались автоматически из Windows 2000 Service Control Manager, выполните следующие действия.

1. Выбрать Пуск/Программы/Администрирование/Службы или ввести в командной строке services.msc /s чтобы запустить Service Control Manager.

2. Прокрутить список служб и найти в нем Distributed Transaction Coordinator, Microsoft Search, MSSQLSERVER и SQLSERVERAGENT. Нажать правой кнопкой мыши на ту службу, настройки запуска которой нужно конфигурировать, а затем выбрать Properties в контекстном меню, в результате чего появится окно Properties (Свойства) (рис. 3).

Рис. 3. Окно свойств службы MSSQLSERVER

3. В ниспадающем списке Startup type (Тип запуска) выбрать Automatic, Manual (Вручную) или Disabled (Выключена). Если выбрать Automatic, то служба будет запускаться автоматически всякий раз при включении компьютера. При выборе Manual потребуется запускать эту службу вручную всякий раз, когда нужно ее использовать. Выбор Disabled служить для предотвращения запуска службы (как автоматического, так и ручного). Для сохранения выбранной конфигурации нажмите на ОК.

4. В окне Properties имеются еще три вкладки. Вкладка Log On позволяет задать учетную запись, под которой данная служба будет входить в систему. Вкладка Recovery позволяет задать настройки на случай отказа выбранной службы. При помощи вкладки Dependencies (Зависимости) можно посмотреть, от каких служб зависит выбранная служба и какие службы зависят от нее (если таковые имеются). Например, служба SQL Server Agent зависит от службы SQL Server. Служба SQL Server Agent не может быть запущена, если служба SQL Server остановлена.

2.2 SQL Server Enterprise Manager

Enterprise Manager - это часть Microsoft Management Console (MMC), «Консоли управления Microsoft». MMC является основным приложением, служащим для управления всеми аспектами работы компьютера под управлением Windows 2000 Server.

2.2.1 Управление SQL Server

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

Регистрировать сервер.
Конфигурировать локальные и удаленные серверы.
Конфигурировать многосерверные инсталляции и управлять ими.
Выполнять настройку входа в систему и добавлять новых пользователей, системных администраторов и операторов.
Назначать пароль системного администратора (sa).
Создавать и планировать задания.
Создавать оповещения и конфигурировать SQL Server для общения с системными администраторами через электронную почту.
Устанавливать базы данных, таблицы, индексы, представления, хранимые процедуры, правила, триггеры, настройки по умолчанию, устройства для резервного копирования, журналы ошибок и управлять ими.
Управлять другими службами SQL Server.

Ниже перечислены четыре задачи, которые можно выполнять при помощи Enterprise Manager, при первой инсталляции SQL Server.

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

Регистрация сервера. Прежде чем начать управлять сервером, нужно зарегистрировать его с ММС.

Доступ к свойствам сервера. После регистрации сервера можно будет просматривать и конфигурировать множество его свойств. Если работа осуществляется в многосерверном окружении, то можно применять Enterprise Manager для управления всеми серверами и конфигурирования всех серверов из одного места.

2.2.2 Создание групп сервера

При помощи Enterprise Manager можно создавать группы серверов, которые окажутся полезными для решения ваших административных задач. Группы серверов позволяют организовать наборы взаимосвязанных серверов для удобного доступа, подобно тому, как папки позволяют организовывать наборы взаимосвязанных файлов. После этого вы сможете одной командой выполнять действия, которые будут сказывать влияние на все серверы группы, а не повторять одну и ту же команду для каждого сервера. По умолчанию, при инсталляции SQL Server, создается группа с названием SQL Server Group. Чтобы создать группу серверов, нужно выполнить следующие действия:

1. Выбрать Пуск/Программы/ Microsoft SQL Server 2000/Enterprise Manager или ввести в командной строке “SQL Server Enterprise Manager.MSC”, чтобы запустить приложение Enterprise Manager.

2. В левой части окна Enterprise Manager, будут показаны папки групп серверов (как подпапки Microsoft SQL Server), а в правой части окна будут показаны значки-иконки групп серверов. Чтобы создать группу серверов SQL Server, нажать правой кнопкой мыши на папку Microsoft SQL Server, а затем выбрать New SQL Server Group в появившемся контекстном меню.

3. Появится диалоговое окно Server Groups, ввести в него с клавиатуры имя новой группы серверов. Если нажать на селективную экранную кнопку Sub-group of (Подгруппа в ...), то можно выбрать группу, для которой новая группа серверов будет подгруппой. Если нажать на Top level group (Группа высшего уровня), то новая группа серверов будет группой SQL Server самого высшего уровня, того же уровня, что и группа SQL Server Group. Чтобы сохранить свою новую группу, нажать на ОК.

2.2.3 Регистрация сервера

После создания группы SQL Server, надо будет зарегистрировать локальные или удаленные серверы в качестве членов этой группы. Чтобы зарегистрировать сервер, выполните следующие действия.

1. Нажмите правой кнопкой мыши на значок-иконку группы серверов в правой панели окна Enterprise Manager. (Если заголовок Microsoft SQL Servers раскрыт, то можно также нажать правой кнопкой мыши на имя папки группы в левой панели окна.) В появившемся контекстном меню выберите New SQL Server Registration.

2. Появится стартовый экран мастера Register SQL Server Wizard (Мастер регистрации SQL Server). Для продолжения регистрации сервера нажмите на Next. Появится экран Select a SQL Server (Выберите SQL Server). В списковом поле Available Servers (Доступные серверы) будут показаны инсталляции SQL Server, доступные через сеть. Выберите серверы, которые хотите зарегистрировать (или наберите с клавиатуры имя сервера в текстовом поле), а затем нажмите на Add, чтобы переместить ими сервера в списковое поле Added Servers (Добавленные серверы). Завершив действия по выбору, нажмите на Next.

3. Появится экран Select An Authentication Mode (Выберите режим аутентификации). Выберите требуемый режим аутентификации. Для продолжения нажмите на Next.

4. Появится экран Select SQL Server Group (Выберите группу SQL Server). Вы можете выбрать уже существующую группу, в которую добавите свой сервер, а можете создать для своего сервера группу высшего уровня. Если вы хотите добавить свой сервер в существующую группу, то нажмите на первую селективную кнопку экрана, а затем выберите имя группы в выпадающем списке. А если вы хотите создать группу, то нажмите на вторую, а затем введите с клавиатуры имя группы в текстовое поле. Для продолжения нажмите на Next.

5. Появится экран Completing The Register SQL Server Wizard (Завершение мастера регистрации SQL Server). Серверы, показанные в списке, будут зарегистрированы. Если вы хотите внести какие-либо изменения, то нажмите на Back, а если изменения не нужны, то нажмите на Finish, и тогда запустится процесс регистрации.

6. Появится диалоговое окно Register SQL Server Messages (Сообщения регистрации SQL Server), являющееся подтверждением успешности вашей регистрации. Чтобы закрыть это окно, нажмите на Close.

2.2.4 Доступ к свойствам сервера.

После регистрации сервера с помощью Enterprise Manager можно получить доступ ко всем его свойствам, базам данных и объектам. Чтобы посмотреть свойства и объекты зарегистрированного сервера, раскройте имя группы сервера в левой панели окна Enterprise Manager. Появится список всех серверов из этой группы. Затем раскройте имя сервера, чтобы стали видны его свойства.

2.2.5 Управление службами.

Enterprise Manager можно применять для управления другими службами-компонентами SQL Server: SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search. Для доступа к свойствам службы SQL Server Agent выполните следующие действия.

1. Находясь внутри Enterprise Manager, раскройте обозначение сервера, доступ к которому вы осуществляете, а затем раскройте папку Management (рис.4).

Рис. 4. Папка Management в Enterprise Manager

2. Нажмите правой кнопкой мыши на SQL Server Agent в левой панели или на значок-иконку SQL Server Agent в правой панели, в результате чего появится контекстное меню. При помощи этого меню вы можете: останавливать или запускать службу SQL Server Agent; просматривать журнал ошибок; запускать мастеры, чтобы данный сервер был основным (master) либо целевым (target) для выполнения заданий; создавать задания, оповещения и операторы; просматривать окно свойств.

3. Через папку Support Services осуществляется доступ к службам Microsoft Distributed Transaction Coordinator и Microsoft Search.

2.3 Query Analyzer

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

1. Запустите Query Analyzer каким-либо из следующих трех способов:

введите isqlw в командной строке;
откройте Enterprise Manager и выберите SQL Query Analyzer в меню Tools;
выберите Пуск/Программы/Microsoft SQL Server/Query Analyzer.

Если вы не соединены с сервером, то появится диалоговое окно Connect to SQL Server (Соединиться с SQL Server) (рис. 5).

Рис. 5. Диалоговое окно Connect to SQL Server

2. В ниспадающем списке SQL Server выберите сервер, с которым вы хотите соединяться. Точка, стоящая в этом поле, означает соединение с локальным сервером. Введите информацию для входа в систему и, если вы желаете, чтобы в случаях, когда SQL Server не запущен, он запускался бы автоматически, установите флажок Start SQL Server if it is stopped. Затем нажмите на экранную кнопку ОК. Появится стартовое окно Query Analyzer (рис. 13-2).

3. В окне запросов введите с клавиатуры любой оператор T-SQL или вызов хранимой процедуры Analyzer. Чтобы выполнить введенный оператор, нажмите на кнопку Execute Query (Выполнить запрос), находящуюся на панели инструментов и выглядящую как зелёный треугольник, указывающий острием вправо, либо нажмите Ctrl+E, либо F5. Результаты исполнения запроса появятся в панели результатов.

Структура простого запроса:

SELECT список_полей

FROM имена_таблиц

[WHERE критерий_отбора]

2.4 Использование SQL Profiler

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

Одним из полезных дополнений SQL Profiler является шаблон трассировки (trace template), который можно использовать для создания файлов трассировки. Трассировку нужно создавать до того, как использовать её для мониторинга операций SQL Server. Для вызова утилиты Profiler и запуска трассировки выполните следующие шаги:

1. Выберите Пуск/Программы/Microsoft SQL Server/Profiler или наберите в командной строке profiler.exe. При первоначальном открытии окна Profiler оно будет пустым. Не будет открыто ни одной панели, и не будет выполняться никакого профилирования в SQL Server.

2. Чтобы начать создание профилирование, вы должны выбрать для выполнения существующий шаблон трассировки или создать новый шаблон трассировки для выполнения. (Процесс запуска описан на шаге 4.) SQL Server 2000 Profiler предоставляет для выбора целый ряд шаблонов трассировки. Чтобы увидеть список шаблонов трассировки, щелкните на меню File (Файл), укажите команду Open (Открыть) и выберите пункт Trace Templates (Шаблоны трассировки), чтобы появилось диалоговое окно Open (рис. 6).

Рис. 6. Диалоговое окно Open со списком шаблонов трассировки

Имеются следующие шаблоны трассировки, поставляемые вместе с SQL Server.

SQLServerProfiterSP_Counts.tdf. Подсчитывает количество запущенных хранимых процедур. Результаты группируются по именам хранимых процедур и содержат количество запусков соответствующей процедуры.
SQLServerProfilerStandard.tdf. Собирает общую информацию о соединениях, выполненных хранимых процедурах и пакетах SQL в порядке их выполнения.
SQLServerProfilerTSQL.tdf. Собирает информацию обо всех операторах T-SQL в порядке их поступления в SQL Server от пользователей. Эта трассировка содержит просто операторы T-SQL и моменты времени их запуска.
SQLServerPronlerTSQL_Duratiоn.tdf. Выводит запущенные операторы T-SQL, а также время (в миллисекундах), которое потребовалось для выполнения этих операторов.
SQLServerProfilerTSQL_Grouped.tdf. Собирает данные, аналогичные тому, что собирает SQLServerProfilerTSQL, но группирует операторы по пользователям, запустившим эти операторы.
SQLServerProfilerTSQLReplay.tdf. Предоставляет подробную информацию о запускавшихся операторах T-SQL. Эта трассировка содержит данные, которые можно использовать для воспроизведения операторов T-SQL в Query Analyzer.
SQLServerProfilerTSQL_SPs.tdf. Выводит указанные хранимые процедуры, а также команды T-SQL внутри этих процедур. Результаты выводятся в порядке выполнения. SQLServerProfilerProfilerTuning.tdf. Собирает данные о хранимой процедуре и выполнении пакета SQL.

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

3. Для запуска трассировки щелкните на File, укажите команду New (Создать) и затем выберите пункт Trace (Трассировка). Появится диалоговое окно Connect to SQL Server. В этом диалоговом окне выберите систему SQL Server для трассировки и затем щелкните на кнопке ОК.

4. Появится окно Trace Properties (Свойства трассировки) (рис. 7). Во вкладке General (Общие) вы можете ввести имя трассировки (поле Trace name) и выбрать шаблон трассировки (trace template), чтобы использовать его как отправную точку. В нижней части вкладки вы можете указать, где хотите сохранять трассировку - в файле (Save in file) и/или в таблице SQL Server (Save in table). Если не установлен ни один из этих флажков, то результаты трассировки будут выводиться только на экран. Кроме того, вы можете задать время окончания трассировки (флажок и поле Enable trace stop time). Это может оказаться очень полезным для долговременных трассировок.

Рис. 7. Вкладка General окна Trace Properties (Свойства трассировки)

5. Далее щелкните на вкладке Events (События). В этой вкладке вы можете выбрать одно или несколько событий, которые будут отслеживаться в данной трассировке. Можно отслеживать целый ряд классов (категорий) событий и конкретных событий. В окне списка Available event classes (Имеющиеся классы событий) содержатся такие классы событий, как Cursors (Курсоры), Errors and Warnings (Ошибки и предупреждения), Locks (Блокировки), Objects (Объекты), Scans (Сканирования). SQL Operators (Операторы SQL), Stored Procedures (Хранимые процедуры), Transactions (Транзакции) и TSQL.

6. После выбора событий, трассировку которых хотите выполнять, щелкните на вкладке Data Columns (Колонки данных). В этой вкладке укажите, сбор каких данных будет выполняться во время данной трассировки. В эти данные можно включать время окончания, идентификатор объекта и т.д.

7. Щелкните на вкладке Filters (Фильтры). В этой вкладке можно указывать, нужно ли, чтобы утилита Profiler включала или исключала определенные события. Например, вам следует исключить трассировку самой утилиты Profiler. (Это установка по умолчанию). Исключая процессы SQL Server, вы делаете окно Profiler менее насыщенным и более удобным для чтения.

8. По окончании установки параметров щелкните на кнопке Run для запуска данной трассировки. Если вы внесли какие-либо изменения в шаблон трассировки, то рекомендуется сохранить этот модифицированный шаблон трассировки под другим именем с помощью команды Save As меню File. После запуска трассировки в окне Profiler будут выводиться события по мере их возникновения. В соответствии с шаблоном трассировки, выбранным в этом примере, события будут сортироваться по длительности (в миллисекундах).

2.5 Проектирование базы данных