Создание объектов баз данных
Логическая структура БД [21] определяет структуру таблиц, взаимоотношения между ними, список пользователей, хранимые процедуры, правила, умолчания и другие объекты БД.
Логически данные в SQL организованы в виде объектов; в табл. 3.5 представлены основные объекты.
Рассмотрим теперь каждый объект подробнее.
Таблица— основной объект для хранения информации в реляционной БД. Она состоит из содержащих данные строк и столбцов, занимает в БД физическое пространство и может быть постоянной или временной [1, 21].
Поле, также называемое в реляционной БД столбцом, является частью таблицы, за которой закреплен определенный тип данных. Каждая таблица БД должна содержать хотя бы один столбец. Строка данных — это запись в таблице БД, она включает поля, содержащие данные из одной записи таблицы. Прежде чем создавать таблицу, разработчик должен задаться следующими вопросами:
1. Как будет называться таблица?
2. Как будут называться столбцы (поля) таблицы?
3. Какие типы данных будут закреплены за каждым столбцом?
4. Какой размер памяти необходимо выделить для хранения каждого столбца?
5. Какие столбцы таблицы требуют обязательного ввода?
6. Из каких столбцов будет состоять первичный ключ?
Базовый синтаксис оператора создания таблицы (упрощенная версия) имеет следующий вид:
Таблица 3.5. Основные объекты базы данных SQL
Приведенный стандарт совпадает с реализацией оператора создания таблицы в среде MS SQL Server.
Главное в команде создания таблицы — определение имени таблицы и описание набора имен полей, которые указываются в соответствующем порядке. Кроме того, этой командой оговариваются типы данных и размеры полей таблицы.
Ключевое слово NULL используется для указания того, что в данном столбце могут содержаться значения NULL. Значение NULL отличается от пробела или нуля — к нему прибегают, когда необходимо указать, что данные недоступны, опущены или недопустимы. Если указано ключевое слово NOT NULL, то будут отклонены любые попытки поместить значение NULL в данный столбец. Если указан параметр NULL, помещение зна чений NULL в столбец разрешено. По умолчанию стандарт SQL предполагает наличие ключевого слова NULL.
Представления, или просмотры (VIEW) представляют собой временные производные (иначе — виртуальные) таблицы и являются объектами БД, информация в которых не хранится постоянно, а формируется динамически при обращении к ним. Обычные таблицы — это базовые таблицы, т. е. такие, которые содержат данные и постоянно находятся на устройстве хранения информации. Представление не существует само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику БД обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, в целях удобства и безопасности. Содержимое представлений выбирается из других таблиц с помощью запроса, причем изменение значений в таблицах данных приводит к изменению в представлении. Представление — фактически тот же запрос, который выполняется всякий раз с какой-либо командой. Результат выполнения запроса в любой момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.
В СУБД есть две возможности реализации представлений. В простейшем случае система формирует каждую запись представления по мере необходимости, постепенно считывая исходные данные из базовых таблиц. В более сложных случаях СУБД сначала выполняет операцию, как материализации представления, т. е. сохраняет информацию, из которой состоит представление, во временной таблице, а затем приступает к выполнению пользовательской команды и формированию ее результатов. После этого временная таблица удаляется.
Фактически, представление — это предопределенный запрос, хранящийся в БД, который выглядит подобно обычной таблице и не требует для хранения дисковой памяти (используется только оперативная память). Создания и изменения представлений в стандарте языка и реализации в MS SQL Server совпадают и представлены следующей командой:
Обращение к представлению осуществляется с помощью оператора SELECT как к обычной таблице.
Представление используют так же, как и любую другую таблицу: представление можно запрашивать, модифицировать (если оно отвечает определенным требованиям), соединять с другими таблицами. Содержание представления не фиксировано и обновляется каждый раз, по ссылке в команде. Представления значительно расширяют возможности управления данными. В частности, это прекрасный способ разрешить доступ к информации в таблице, скрыв часть данных.
Преимущества и недостатки представлений.Механизм представления — мощное средство СУБД, позволяющее скрыть реальную структуру БД от некоторых пользователей за счет определения представлений. Любая реализация представления должна точно соответствовать состоянию данных, которые определяют это представление. Обычно вычисление представления производится каждый раз при его использовании. Уже в процессе создания представления информация о нем записывается в каталог БД под собственным именем. Любые изменения в данных адекватно отобразятся в представлении — что отличает его от очень похожего на него запроса к БД. В то же время запрос представляет собой как бы «мгновенную фотографию» данных и при изменении последних запрос к БД необходимо повторить. Наличие представлений в БД необходимо для обеспечения логической независимости данных, т. е. изменение логической структуры данных не приводит к изменению пользовательских программ. Очевидно, что с увеличением количества данных, хранимых в БД, возникает необходимость ее расширения за счет добавления новых атрибутов или отношений (рост БД). При реструктуризации данных информация сохраняется, но изменяется ее расположение, например за счет перегруппировки атрибутов в отношениях. В случае применения СУБД на отдельном персональном компьютере использование представлений обычно имеет целью лишь упрощение структуры запросов к БД. В случае многопользовательской сетевой СУБД представления играют ключевую роль в определении структуры БД и организации защиты информации. Рассмотрим основные преимущества применения представлений в подобной среде.
Независимость от данных. С помощью представлений можно создать согласованную, неизменную картину структуры БД, которая будет оставаться стабильной даже в случае изменения формата исходных таблиц (например, добавления или удаления столбцов, изменения связей, разделения таблиц, их реструктуризации или переименования). Если в таблицу добавляются или из нее удаляются не используемые в представлении столбцы, то изменять определение этого представления не потребуется. Если структура исходной таблицы переупорядочивается или таблица разделяется, можно создать представление, позволяющее работать с виртуальной таблицей прежнего формата.
Актуальность. Изменения данных в любой из таблиц базы данных, указанных в определяющем запросе, немедленно отображаются на содержимом представления.
return false">ссылка скрытаПовышение защищенности данных. Права доступа к данным могут быть предоставлены исключительно через ограниченный набор представлений, содержащих только то подмножество данных, которое необходимо пользователю. Подобный подход позволяет существенно ужесточить контроль за доступом отдельных категорий пользователей к информации в базе данных.
Снижение стоимости. Представления позволяют упростить структуру запросов за счет объединения данных из нескольких таблиц в единственную виртуальную таблицу. В результате многотабличные запросы сводятся к простым запросам к одному представлению.
Дополнительные удобства. Создание представлений может обеспечивать пользователей дополнительными удобствами, например возможностью работы только с действительно нужной частью данных. В результате можно добиться максимального упрощения той модели данных, которая понадобится каждому конечному пользователю.
Возможность настройки. Представления являются удобным средством настройки индивидуального образа базы данных. В результате одни и те же таблицы могут быть предъявлены пользователям в совершенно разном виде.
Обеспечение целостности данных. Если оператор CREATE VIEW дополнить фразой WITH CHECK OPTION, то СУБД станет осуществлять контроль за тем, чтобы в исходных таблицах БД не появилась ни одна строка, не удовлетворяющая предложению WHERE в определяющем запросе. Этот механизм гарантирует целостность данных в представлении, что является одним из значимых преимуществ специализированных представлений в среде SQL.
Несмотря на довольно внушительный перечень преимуществ, представления не лишены недостатков. Отметим лишь основные.
Ограниченные возможности обновления. В некоторых случаях представления не позволяют вносить изменения в содержащиеся в них данные.
Структурные ограничения. Структура представления устанавливается в момент его создания. Если определяющий запрос представлен в форме SELECT * FROM_, то символ * ссылается на все столбцы, существующие в исходной таблице на момент создания представления. Если впоследствии в исходную таблицу базы данных добавятся новые столбцы, то они не появятся в данном представлении до тех пор, пока это представление не будет удалено и вновь создано.
Снижение производительности. Использование представлений связано с определенным снижением производительности. В одних случаях влияние этого фактора совершенно незначительно, тогда как в других оно может послужить источником существенных проблем. Например, представление, определенное с помощью сложного многотабличного запроса, может потребовать значительных затрат времени на обработку, поскольку при его разрешении потребуется выполнять соединение таблиц всякий раз, когда понадобится доступ к данному представлению. Выполнение разрешения представлений связано с использованием дополнительных вычислительных ресурсов.
Хранимые процедурыпредставляют собой группы связанных между собой операторов SQL, предусмотренных для облегчения работы программиста. Хранимые процедуры — это набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в БД в откомпилированном виде. Выполнение в БД хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества [21]:
1. необходимые операторы уже содержатся в БД;
2. все они прошли этап синтаксического анализа и находятся в исполняемом формате;
3. перед выполнением хранимой процедуры SQL-сервер генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию;
4. хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
5. хранимые процедуры могут вызывать другие хранимые процедуры и функции;
6. хранимые процедуры могут быть вызваны из прикладных программ других типов;
7. как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;
8. хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры - это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.
Хранение процедур в том же месте, где они исполняются, уменьшает объем передаваемых по сети данных и повышает общую производительность системы. Применение хранимых процедур упрощает сопровождение программных комплексов и внесение изменений в них. Обычно все ограничения целостности в виде правил и алгоритмов обработки данных реализуются на сервере БД и доступны конечному приложению в виде набора хранимых процедур, которые и представляют интерфейс обработки данных. Для обеспечения целостности данных, а также в целях безопасности приложение обычно не получает прямого доступа к данным — вся работа с ними ведется путем вызова тех или иных хранимых процедур.
Подобный подход значительно упрощает модификацию алгоритмов обработки данных и обеспечивает возможность расширения системы без внесения изменений в само приложение: достаточно только изменить хранимую процедуру на сервере БД. Разработчику не нужно перекомпилировать приложение, создавать его копии, а также инструктировать пользователей о необходимости работы с новой версией. Пользователи вообще могут не подозревать о том, что в систему внесены изменения.
Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов БД. Они вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение. Изменять код хранимой процедуры вправе только ее владелец; при необходимости можно передать права владения ею от одного пользователя к другому.
При работе с SQL [21] пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами БД, а потому каждая из них хранится в конкретной БД. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той БД, где находится процедура.
В SQL предусмотрено несколько типов хранимых процедур. Системные хранимые процедуры предназначены для выполнения различных административных действий (практически все, что касается администрирования сервера). Проще говоря, системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая в конечном счете сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных БД. Системные хранимые процедуры имеют префикс sp_, хранятся в системной БД и могут быть вызваны в контексте любой другой БД.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры — полноценный объект БД. Вследствие этого каждая хранимая процедура располагается в конкретной БД, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после этого автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
Создание хранимой процедуры предполагает решение следующих задач [21]:
1. определение типа создаваемой хранимой процедуры — временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp__ и поместив ее в системную БД. Такая процедура будет доступна в контексте любой БД локального сервера;
2. планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам БД, что и создавший ее пользователь;
3. определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами;
4. разработку кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.
Триггерыявляются одной из разновидностей хранимых процедур; они исполняются при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций. Другими словами, триггер — это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной БД. Применение триггеров весьма удобно для пользователей БД, но связано зачастую с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.
Триггеры — особый инструмент SQL, используемый для поддержания целостности данных в БД. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности — часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т. д.
Триггер представляет собой специальный тип хранимых процедур, запускаемых сервером автоматически при попытке изменения данных в таблицах, с которыми связаны триггеры. Каждый триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции, тем самым внесение изменений запрещается. Отменяются также вес изменения, уже сделанные триггером.
Создает триггер только владелец БД. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т. п.
Триггер представляет собой весьма полезное и в то же время опасное средство. Так, при неправильной логике работы легко уничтожить целую БД, поэтому триггеры необходимо очень тщательно отлаживать.
В отличие от обычной подпрограммы, триггер выполняется неявно в каждом случае возникновения триггерного события, к тому же он не имеет аргументов. Приведение его в действие иногда называют запуском триггера. С помощью триггеров выполняются следующие функции:
1. проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
2. выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;
3. накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
4. поддержка репликации.
Функции пользователя.При реализации на языке SQL сложных алгоритмов, которые могут потребоваться более одного раза, встает вопрос о сохранении разработанного кода для дальнейшего применения. Эта задача просто решается с помощью хранимых процедур, однако их архитектура не позволяет использовать процедуры непосредственно в выражениях, так как они требуют промежуточного присвоения возвращенного значения переменной, которая затем и указывается в выражении [21].
Возможность создания пользовательских функций предоставлена в среде MS SQL Server 2000. В других реализациях SQL в распоряжении пользователя имеются только встроенные функции, которые обеспечивают выполнение наиболее распространенных алгоритмов: поиск максимального или минимального значения и др.
Функции пользователя представляют собой самостоятельные объекты БД, такие, например, как хранимые процедуры или триггеры. Функция пользователя располагается в определенной БД и доступна только в ее контексте.
В SQL Server имеются следующие классы функций пользователя:
1. Scalar — функции возвращают обычное скалярное значение, каждая может включать множество команд, объединяемых в один блок с помощью конструкции BEGIN...END;
2. Inline — функции содержат всего одну команду SELECT и возвращают пользователю набор данных в виде значения типа данных TABLE;
3. Multi-statement — функции также возвращают пользователю значение типа данных TABLE, содержащее набор данных, однако в теле функции находится множество команд SQL (INSERT, UPDATE и т. д.). Именно с их помощью и формируется набор данных, который должен быть возвращен после выполнения функции.
Пользовательские функции сходны с хранимыми процедурами, но, в отличие от них, могут применяться в запросах так же, как и системные встроенные функции. Пользовательские функции, возвращающие таблицы, являются альтернативой представлениям. Последние ограничены одним выражением SELECT, a пользовательские функции способны включать дополнительные выражения, что позволяет создавать более сложные и мощные конструкции.
Встроенные функции SQL, условно разбивают на следующие группы:
1. математические функции;
2. строковые функции;
3. функции для работы с датой и временем;
4. функции конфигурирования;
5. функции системы безопасности;
6. функции управления метаданными;
7. статистические функции.
Подробная информация об особенностях каждой группы функций с примерами и заданиями для самопроверки содержится в специальной литературе.
Индексыпредставляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов. Наличие индекса существенно повышает скорость выполнения некоторых запросов и сокращает время поиска необходимых данных за счет физического или логического их упорядочивания. Индекс — это набор ссылок, упорядоченных по определенному столбцу таблицы, который в данном случае называется индексированным столбцом. Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом БД.
Физически индекс — всего лишь упорядоченный набор значений из индексированного столбца с указателями на места физического размещения исходных строк в структуре БД. Когда пользователь выполняет обращающийся к индексированному столбцу запрос, СУБД автоматически анализирует индекс для поиска требуемых значений.
Однако, поскольку индексы должны обновляться системой при каждом внесении изменений в их базовую таблицу, они создают дополнительную нагрузку на систему.
Индексы обычно создаются с целью удовлетворения определенных критериев поиска после того, как таблица уже находилась некоторое время в работе и увеличилась в размерах. Создание индексов не предусмотрено стандартом SQL, однако большинство диалектов поддерживают как минимум следующий оператор:
Указанные в операторе столбцы составляют ключ индекса. Индексы могут создаваться только для базовых таблиц, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться системой. Требование уникальности значений обязательно для первичных ключей, а также возможно и для других столбцов таблицы (например, для альтернативных ключей). Хотя создание индекса допускается в любой момент, при его построении для уже заполненной данными таблицы могут возникнуть проблемы, связанные с дублированием данных в различных строках. Следовательно, уникальные индексы (по крайней мере, для первичного ключа) имеет смысл создавать непосредственно при формировании таблицы. В результате система сразу возьмет на себя контроль за уникальностью значений данных в соответствующих столбцах.
Если созданный индекс впоследствии окажется ненужным, его можно удалить с помощью оператора:
DROP INDEX имя_индекса
В среде MS SQL Server индексы расположены в самой таблице и являются удобным внутренним механизмом системы SQL-сервера, с помощью которого осуществляется доступ к данным наиболее оптимальным способом. В среде SQL Server реализованы эффективные алгоритмы поиска нужного значения в строго определенной последовательности данных. Ускорение поиска достигается именно за счет того, что данные представляются упорядоченными (хотя физически, в зависимости от типа индекса, они могут храниться в соответствии с очередностью их добавления в таблицу). К настоящему времени разработаны эффективные математические алгоритмы поиска данных в упорядоченной последовательности. Наиболее эффективной структурой для поиска данных в машинном представлении являются В-деревья — многоуровневая иерархическая структура с переменным количеством элементов в каждом узле.
Создание индекса. Если выборка данных из таблицы требует значительного времени, это означает, что для нее необходимо создать индекс. Индексы существенно повышают производительность выполнения операций поиска и выборки данных. При выборе столбца для индекса следует проанализировать, какие типы запросов чаще всего выполняются пользователями и какие столбцы являются ключевыми, т. е. задающими критерии выборки данных (например, порядок сортировки). В среде SQL Server реализовано несколько типов индексов:
1. некластерные ;
2. кластерные;
3. уникальные.
Некластерные индексы. В отличие от кластерных, не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Для идентификации нужной строки в таблице некластерный индекс организует специальные указатели, включающие:
1. информацию об идентификационном номере файла, в котором хранится строка;
2. идентификационный номер страницы соответствующих данных;
3. номер искомой строки на соответствующей странице;
4. содержимое столбца.
В большинстве случаев следует ограничиваться 4—5 индексами.
Кластерный индекс. Принципиальным отличием кластерного индекса от индексов других типов является то, что при его определении в таблице физическое расположение данных перестраивается в соответствии со структурой индекса. Логическая структура таблицы в этом случае представляет собой, скорее, словарь, чем индекс. Данные в словаре физически упорядочены (например, по алфавиту).
Кластерные индексы приводят к существенному увеличению производительности поиска данных даже по сравнению с обычными индексами (это особенно заметно при работе с последовательными данными). Если в таблице определен некластерный индекс, то сервер сначала должен обратиться к индексу, а затем найти нужную строку в таблице. При использовании кластерных индексов следующая запись данных располагается сразу после найденных ранее данных. В результате отпадают лишние операции, связанные с обращением к индексу и новым поиском нужной строки в таблице.
Естественно, в таблице определяют только один кластерный индекс. В качестве такового следует выбирать наиболее часто используемые столбцы. При этом необходимо выполнять общие рекомендации создания индексов и не индексировать слишком длинные столбцы.
Кластерный индекс может включать несколько столбцов, однако количество таких столбцов рекомендуется по возможности свести к минимуму.
Необходимо избегать создания кластерного индекса для часто изменяемых столбцов, поскольку серверу придется физически перемещать все данные в таблице с целью их упорядочения. Для часто изменяемых столбцов больше подходит некластерный индекс.
При создании в таблице первичного ключа (PRIMARY KEY) сервер автоматически создает для него кластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой тип индекса.
В случае, когда в таблице определен и некластерный индекс, его указатель ссылается не на физическое положение строки в БД, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру некластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице.
Уникальный индекс гарантирует уникальность значений в индексируемом столбце. При наличии уникальных индексов сервер не разрешит вставить новое или изменить существующее значение так, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и реализуется как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов. Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в БД. Кроме того, на их поддержание тратится и процессорное время.
Средства языка SQL предлагают несколько способов определения индекса:
1. автоматическое создание индекса при создании первичного ключа;
2. автоматическое создание индекса при определении ограничения целостности unique;
3. создание индекса с помощью команды CREATE INDEX.
Удаление индекса выполняется командой
Пользовательские типы данных— это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения; причем столбцы в таблице должны иметь одинаковый размер, тип данных и чувствительность к значениям NULL.
Создание пользовательского типа данных осуществляется выполнением системной процедуры:
Тип данных system datatype выбирается из табл. 3.6.
Таблица 3.6. Типы данных
Ограничения целостности— механизм обеспечения автоматического контроля соответствия данных установленным условиям (или ограничениям). Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся: ограничение на значение NULL, проверочные ограничения, ограничение уникальности (уникальный ключ), ограничение первичного ключа и ограничение внешнего ключа. Последние три ограничения тесно связаны с понятием ключей.
Обязательные данные. Для некоторых столбцов требуется наличие в каждой строке таблицы конкретного и допустимого значения, отличного от опущенного значения или значения NULL. Для заданий ограничений подобного типа стандарт SQL предусматривает использование спецификации NOT NULL.
Требования конкретного предприятия. Обновления данных в таблицах могут быть ограничены существующими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE.
Ограничения для доменов полей. Каждый столбец имеет собственный домен — некоторый набор допустимых значений. Стандарт SQL предусматривает два различных механизма определения доменов. Первый состоит в использовании предложения CHECK, позволяющего задать требуемые ограничения для столбца или таблицы в целом, а второй предполагает применение оператора CREATE DOMAIN.
Целостность сущностей. Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке. Стандарт SQL позволяет задавать подобные требования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы ее можно указывать только один раз. Тем не менее для любых альтернативных ключей таблицы существует возможность гарантировать уникальность значений, что с успехом обеспечивает ключевое слово UNIQUE. Кроме того, при определении альтернативных ключей рекомендуется использовать спецификаторы NOT NULL.
Ссылочная целостность. Внешние ключи представляют собой столбцы (или наборы столбцов), предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а ссылка REFERENCES определяет имя родительской таблицы, (там находится соответствующий потенциальный ключ). При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. При попытке удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:
1. CASCADE — выполняется удаление строки из родительской таблицы и автоматическое удаление всех ссылающихся на нее строк дочерней таблицы;
2. SET NULL — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;
3. SET DEFAULT — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
4. NO ACTION — операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опушена.
Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется.
Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе.
При определении таблицы предложение FOREIGN KEY можно применять произвольное количество раз.
В операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.
Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.
Умолчание— самостоятельный объект БД, представляющий значение, которое будет присвоено элементу таблицы при вставке строки, если в команде вставки явно не указано значение для этого столбца.
Умолчания создаются и удаляются в любой момент времени, до или после введения данных в таблицу. Значения по умолчанию создаются командой create default (создать умолчание), а удаляются с командой drop default (удалить умолчание).
Умолчание может быть связано с определенным столбцом таблицы, с несколькими столбцами или со всеми столбцами таблиц БД, имеющими заданный пользователем тип данных. Для связывания умолчания со столбцом или типом данных используется системная процедура sp_bindefault (присоединить умолчание); в противном случае — процедура sp_unbindefault (отсоединить умолчание).
Создание правил.Правила создаются с помощью команды create rule (создать правило), а затем присоединяются к столбцу или пользовательскому типу данных с помощью системной процедуры sp_binderule (присоединить правило). Отсоединение правила от столбца или пользовательских типа данных проводится с помощью системной процедуры sp_unbindrule (отсоединить пра вило) или путем присоединения нового правила к столбцу или пользовательскому типу [25].
Названия правил должны соответствовать правилам, установленным для идентификаторов. Правила можно создавать только в текущей базе данных.
Названия правил должны быть уникальными для каждого пользователя внутри одной базы данных. Например, один пользователь не может создать два правила с названием socsecrule. Однако два различных пользователя могут создать правила с названием socsecrule, поскольку имена пользователей позволяют их различить.
Правила, связанные со столбцами, всегда сильнее правил, связанных с типами данных. Присоединение правила к столбцу заменяет правило, которое связано с типом данных этого столбца, но присоединение правила к типу данных не заменяет правила, связанного со столбцом этого типа.
Правило, связанное с типом данных, начинает действовать только при попытке обновить или вставить значения в столбец данных этого типа. Поскольку правила не проверяют значения переменных, то пользователь должен стараться не присваивать переменной пользовательского типа значения, которое не допускается правилом для этого типа данных.
Контрольные вопросы
1. Изложите основы современных СУБД.
2. Перечислите виды архитектурных решений баз данных.
3. Каковы критерии выбора СУБД при создании АИС?
4. Назовите концептуальные модели данных.
5. Какие понятия реляционных баз данных относятся к базовым?
6. Что такое нормализация?
7. Что значит диаграммное представление баз данных?
8. Перечислите известные виды нотаций.
9. Какие средства относятся к средствам автоматизированного проектирования структур баз данных?
10. Каковы основные действия для создания таблиц?
11. Что означают представления?
12. Что означают умолчания?
13. Что представляют собой внешние ключи?
14. Что такое кластерный и некластерный индексы?
15. Перечислите основные функции для работы с датой и временем.
Литература
1. Диго С. М. Базы данных: проектирование и использование. М.: Финансы и статистика. 2005. 592 с.
2. Карпова И. П. Введение в базы данных. Ч. 1. М.: МЭСИ, 2003.
3. Головина О. С. Программирование в корпоративных СУБД. Московский международный институт эконометрики, информатики, финансов и права. М., 2002. 125 с.
4. Кузнецов С. Д. Основы современных баз данных, информационно-аналитические материалы ЦИТ. http//www.citmgu.ru.
5. Каратыгин С. И. Базы данных: простейшие средства обработки информации: системы управления базами данных. ABF, 1995.
6. Аносо А. Критерии выбора СУБД при создании информационных систем. http://www.nwsta.com.
7. Емельянов А. А., Власов Е. А. Информационное моделирование в экономических системах: учеб. пособие. М.: МЭСИ, 1996.
8. Сычев Ю. Н. Информационная безопасность: учеб. пособие / Московский государственный университет экономики, статистики и информатики. М., 2004. 240 с.
9. Мельников В. И. Защита информации в компьютерных системах. М.: Финансы и статистика, 1997.
10. Павловский Ю. Н. Имитационные модели и системы. М.: Фазис, 2000.
11. Малыхина М. П. Базы данных: основы, проектирование, использование. СПб.: БХВ-Петербург, 2004. 512 с.
12. Кузнецов С. Д. Введение в модель данных SQL. htpp://intuit.ru.
13. Смирнова Г. Н., Тельнов Ю. Ф. Проектирование экономических информационных систем. М.: Финансы и статистика, 2004.
14. Вендров А. М. CASE-технологии. Современные методы и средства проектирования информационных систем. http:// citforum.ru.
15. Верников Г. Основы методологии IDEF1. http://www.itrealty.ru.
16. Верников Г. Основы методологии IDEF1X. http://www.itrealty.ru.
17. http://www.embarcadero.com/products/Design/erdatasheet.htm.
18. http://www.popkin.com/products/sa2001/data/data.htm.
19. http://www.visible.com/dataapp/daprods.html.
20. http://www.microsoft.com/office/visio/.
21. Полякова Л. Н. Основы SQL. http://www.intuit.ru.
22. Кузнецов С. Д. Введение в модель данных SQL. http://www.intuit.ru.
23. Карпова И. П. Введение в базы данных. Ч. 1. М.: МЭСИ, 2003.
24. Баженова И. Ю. SQL и процедурно-ориентированные языки, http:// www.intuit.ru.
25. http://palien.narod.ru.