Модуль1_Организация и управление БД

задействованными серверами. Для обработки распределенной БД в каждом узле с MS SQL Server должна быть запущена служба MS DTC.

4)MS SEARCH – служба полнотекстового поиска. Является средством индексного поиска по полям типа TEXT, недоступным для поиска обычным оператором SELECT.

5)MS SQL ServerOLAPService – служба для создания, использования хранилищ данных – специальной организации БД для быстрой эффективной реализации запросов на больших базах данных. В отличие от БД, предназначенных для хранения информационной модели и оперативной обработки данных, хранилища создаются в виде избыточной базы, обычно по не изменяющимся (ретроспективным) данным. Для быстрого анализа влияния различных параметров хранилище строится в виде многомерного куба (гиперкуба) данных, каждое измерение которого является параметром будущих запросов.

Перечисленными службами сервера обеспечиваются следующие режимы использования MS SQL Server 2000 в информационных системах:

1)OLTP (online transaction processing) – онлайновая обработка транзакций.

Этот режим предназначен для создания оперативных баз и характеризуется:

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

−одновременным обращением к данным многих пользователей с разными правами и задачами в базе данных.

2)OLAP (online analytical processing) – оперативный анализ данных. Режим обеспечивается службой ServerOLAPService и характеризуется:

−неизменяемостью БД. Обычно OLAP используется для анализа данных за прошлые периоды;

72

−большим, в сравнении с оперативными базами, объемом данных. Обычно такие базы представляют многолетние данные о деятельности предприятия;

−усиленными ограничениями прав доступа к информации.

Утилиты администрирования MS SQL Server

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

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

SQL Server Enterprise Manager предоставляет администратору БД графические (диалоговые) средства управления серверами. Enterprise Manager (EM) реализован в виде модуля для Microsoft Management Console (MMC). В

дереве объектов окна MMC администратор получает доступ ко всем MS SQL серверам и их объектам, базам данных и объектам баз данных. Имеется возможность в диалоге просматривать состояние и настройки объектов сервера, конфигурировать систему безопасности, выполнять типовые задачи создания баз и управления данными. Enterprise Manager позволяет автоматизировать процессы администрирования путем создания заданий, которые вручную или автоматически по событиям или расписанию исполняются на сервере.

SQL Query Analyzer реализует интерактивный режим создания и исполнения наборов операторов SQL (скриптов) на сервере. Query Analyzer (QA) имеет встроенный редактор, который создает текстовые файлы операционной системы (ОС) Windows. Набранные в окне редактора операторы SQL могут

73

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

Performance Monitor. Программа установки MS SQL Server модифицирует набор объектов и счетчиков, имеющихся в Performance Monitor ОС Windows. Для сбора дополнительной статистики добавляются счетчики числа соединений

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

кпамяти и т.д.

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

Server Network Utility и Client Network Utility обеспечивают управление сетевыми библиотеками, являющимися надстройками над сетевыми протоколами для связи с SQL сервером. Для создания связи необходимо, чтобы на клиенте и сервере работали одинаковые сетевые протоколы и соответствующие им сетевые библиотеки SQL Server.

Исходные пользователи и БД в MS SQL Server

Для обеспечения контролируемого доступа на сервере создаются учетные записи (logins) пользователей. При попытке соединения сервер проверяет

74

(авторизует) учетную запись по паролю. В момент установки сервера создаются две начальные учетные записи с именами sa и BUILTIN\Administrators. Учетная запись sa обеспечивает возможность работы в стандартном режиме авторизации, при котором проверку пароля выполняет сам SQL SERVER. Учетная запись BUILTIN\Administrators

является представителем группы Administrators в домене ОС WINDOWS. Таким образом, администраторы WINDOWS автоматически становятся пользователями MS SQL SERVER без дополнительной авторизации сервером. Обе учетные записи включаются в роль Administrators MS SQL SERVER и таким образом получают все права для работы с сервером;

Основным объектом, определяющим назначение MS SQL SERVER, являются базы данных. Особую роль в работе сервера играют системные БД. Системные базы автоматически создаются во время установки сервера и используются для управления сервером и его объектами. Для выполнения собственных задач сервером используются следующие системные базы.

1.База MASTER в своих таблицах хранит информацию о конфигурации сервера, обо всех объектах сервера: учетных записях пользователей, существующих проблемных базах и их первичных файлах и т.д. Разрушение базы MASTER делает недоступными проблемные базы, поэтому для базы MASTER должны создаваться копии при каждом изменении состава или свойств объектов сервера. База MASTER размещается в двух файлах операционной системы: MASTER.MDF – файл данных и MASTER.LDF – файл журнала транзакций.

2.Системная база MODEL хранит набор пустых системных таблиц, образующих словарь любой новой базы. При создании новой базы системные таблицы из базы MODEL копируются в новую базу, определяя ее опции и создавая пустой словарь, который будет заполняться по мере создания новых объектов в базе. Таким образом, база MODEL необходима для создания на сервере новых баз. Изменяя параметры БД MODEL или

75

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

хранится в файлах MODEL.MDF и MODEL.LDF.

3.MSDB хранит информацию, необходимую службе SQL Server Agent для обработки событий сервера, регистрации операторов, выполнения заданий по расписанию. Для хранения базы используются файлы MSDBDAT.MDF и MSDBLOG.LDF.

4.TEMPDB – база для хранения временных объектов (таблиц, процедур, курсоров и т.д.), создаваемых сервером при выполнении SQL операторов.

Файлами базы являются TEMPDB.MDF и TEMPDB.LDF.

Кроме перечисленных системных баз, при установке SQL Server 2000

создаются две учебные базы данных: Pubs и Northwind. Они предназначены для обучения пользователей и экспериментов с операторами Transact SQL и не имеют значения для функционирования сервера.

Язык управления данными в MS SQL Server

Общение любой программы с базами данных MS SQL Server осуществляется с помощью операторов языка, называемого Transact-SQL. Ранее рассмотренные операторы Select, Insert, Update и Delete являются представителями данного языка.

Операторы языка Transact-SQL используется для управления параметрами конфигурации сервера, его базами данных, создания запросов и программ для обработки данных. Программы обработки данных на сервере записываются в виде обычных текстовых файлов операционной системы, имеют по умолчанию расширение .sql и исполняются в помощью утилиты SQL Query Analyzer. Такие программы часто называют скриптами (сценариями) для работы с данными.

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

76

Правила именования объектов баз данных в программах на TransactSQL

В операторах Transact-SQL, предназначенных для	извлечения	или
обработки данных MS SQL SERVER, необходимо обращаться к объектам		баз и

временным объектам, созданным во время выполнения программы. Объекты баз существуют на сервере независимо от исполнения программы, поэтому такие объекты следует считать постоянными. Программа создает свои объекты, существующие только во время ее выполнения, например, переменные и курсоры. Такие объекты назовем временными.

Постоянные объекты базы

Эти объекты становятся доступны в любой утилите или приложении пользователя после соединения с сервером. Полное наименование таких объектов имеет вид:

		имя			имя
		сервера	. [имя БД	].	владельца
					объекта
		(1)	(2)		(3)

	имя		имя
.	объекта	.	столбца	.
	(4)		(5)

Вобозначении имени объекта базы используются:

1)<имя сервера> – содержит имя сервера, на котором находится объект. Если имя сервера не задано, используется сервер, на котором выполняется оператор, содержащий данный объект;

2)<имя БД> – содержит имя базы, в которой находится требуемый объект. Если имя базы не введено, используется контекстно-заданная база. Задать базу по умолчанию (контекстною) можно с помощью оператора USE <имя БД>. Эта база будет использоваться по умолчанию во всех последующих операторах до ее смены новым оператором USE. В утилите SQL Query Analyzer, из которой обычно выполняются наборы операторов (скрипты), имя умалчиваемой базы можно задать также с помощью выпадающего списка, находящегося в основной панели инструментов и содержащего все доступные пользователю базы;

77

3)<имя владельца объекта> – указывается имя пользователя в базе (USER), создавшего объект. Наличие имени владельца в имени объекта позволяет разным пользователям создавать объекты, не согласуя их имена между собой. Если при создании объекта имя владельца не было задано, автоматически подставляется имя dbo, обозначающее Database owner (владелец БД), которое принадлежит пользователю, создавшему базу данных. Если задано имя базы, но пропускается имя владельца, то в соответствии с синтаксисом, точки вокруг элемента <имя владельца объекта> должны быть заданы. Например, User_database..authors;

4)<имя объекта> – содержит идентификатор объекта (таблицы, процедуры и т.д.) базы.

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

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

Временные объекты

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

–локальная временная таблица, которая создаётся и сохраняется в сеансе связи (соединении) и недоступна в других соединениях. После разрыва соединения с сервером таблица автоматически удаляется. Локальная временная таблица должна иметь имя в виде #<идентификатор>,

–глобальная временная таблица, в отличие от локальной, доступна в других соединениях, но только до разрыва соединения, создавшего таблицу. Имя глобальной временной таблицы имеет вид: ##<идентификатор>.

78

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

В программе на Transact SQL могут использоваться переменные. Имена переменных задаются идентификатором, начинающимся символом @.

Переменные бывают двух типов: локальные и глобальные.

Локальные переменные. Имя локальной переменной имеет вид: @<идентификатор>, где @ - признак переменной. Перед использованием локальной переменной в скрипте или в процедуре ее необходимо определить с помощью оператора

DECLARE @<идентификатор> <тип данных для переменной> [, …]

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

Сохранение значений, вычисляемых оператором SELECT в локальных переменных

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

@<имя локальной переменной> = <выражение>.

Например, операторы

DECLARE @n int

SELECT @n = COUNT(*) FROM authors SELECT @n

79

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

Последовательность операторов

DECLARE @au_ln varchar(40), @au_fn varchar(20)

SELECT @au_ln = au_lname, @au_fn = au_fname FROM authors SELECT @au_ln, @au_fn

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

80

Поставки (Продукция, Количество, Дата, Потребитель, Адрес)

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

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

Введем понятие декомпозиции схемы отношения.

Пусть задана С(А1, А2,…, Аn) – схема произвольно отношения (шапка таблицы), где С - имя отношения,

А1, А2, . . . , Аn – имена атрибутов в отношении.

Тогда, декомпозицией схемы С называется множество новых схем отношений

S = { С1, С2, …, Cn },
где Сi, i =		1, n - схемы отношений, удовлетворяющие условиям:
Ci C	,
			= n
C 1 C 2		... C n		C i	= C
			i =	1	.

Таким образом, декомпозиция схемы – это разложение множества ее

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

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

81