Создание пользовательских представлений

Проектирование и модификация таблиц командами SQL

Создание серверного приложения преобразованием проекта базы данных формата Microsoft Access в формат SQL Server

Для переноса некоторых или всех объектов базы данных Microsoft Access (.mdb) в новую или существующую базу данных Microsoft SQL Server версий 2000 и 7.0 или 6.5 либо в новый проект Microsoft Access (.adp) используют мастер преобразования базы данных.

Мастер преобразования в формат Microsoft SQL Server позво­ляет:

• преобразовать все объекты базы данных Microsoft Access в формат проекта Microsoft Access, что обеспечит создание прило­жения типа клиент—сервер;

• преобразовать только данные или определения данных из формата базы данных Microsoft Access в формат базы данных Microsoft SQL Server;

• создать клиентскую часть базы данных в формате Microsoft Access для серверной части базы данных в формате Microsoft SQL Server, что потребует небольших изменений в приложениях, по­скольку программы будут по-прежнему использовать ядро базы данных Microsoft Jet.

Перед преобразованием базы данных Microsoft Access в формат базы данных Microsoft SQL Server или проекта Microsoft Access предварительно рекомендуется выполнить следующие действия.

1. Создать резервную копию базы данных. Хотя мастер преобразо­вания и не удаляет из базы данных Access данные или объекты базы данных, перед преобразованием рекомендуется создать ре­зервную копию базы данных Microsoft Access.

2. Убедиться, что на диске достаточно места. На диске, где бу­дет храниться преобразованная база данных Microsoft SQL Server, должно быть достаточно свободного места, так как мастер преоб­разования лучше работает, когда на диске имеется свободное про­странство. Система Microsoft SQL Server автоматически увеличи­вает размер базы данных Microsoft SQL Server 7.0 или более позд­ней версии по мере ее создания.

3. Создать уникальные индексы. Для обновления в Microsoft Access связанная таблица должна иметь уникальный индекс. Мастер пре­образования в формат Microsoft SQL Server может преобразовать существующий уникальный индекс, но не может его создать, поэтому если требуется наличие возможности обновлять табли­цы, следует перед преобразованием добавить уникальные индек­сы в каждую таблицу Microsoft Access.

4. Установить принтер по умолчанию. Это требуется для исполь­зования мастера преобразования в формат Microsoft SQL Server.

5. Присвоить необходимые разрешения на доступ к базе данных Microsoft Access. Для выполнения преобразования всех объектов базы данных разработчик должен иметь разрешения на считывание и изменение макета.

6. Присвоить необходимые разрешения на доступ к базе данных Microsoft SQL Server. Для преобразования существующей базы данных необходимы разрешения CREATE TABLE и CREATE DEFAULT. Для построения новой базы данных необходимо разрешение CREATE DATABASE, а также разрешение SELECT на доступ к системным таблицам в главной базе данных. Для создания новых устройств необходимо быть системным администратором.

7. Создать при необходимости несколько дисковых устройств. При
выполнении преобразования к формату базы данных Microsoft SQL
Server 6.5 перед запуском мастера преобразования может потре-
боваться создание новых устройств. Мастер создает все новые уст-
ройства на том же физическом диске, на котором находится глав-
ная база данных. Если на сервере установлено несколько дисков, можно поместить базу данных на одном из них, а журнал транз­акций — на другом. В этом случае после сбоя диска базу данных можно восстановить.

В Microsoft SQL Server 6.5 базы данных и журналы транзакций могут распределяться по нескольким дискам, а мастер преобразо­вания позволяет указать только один диск для базы данных и один диск для журнала транзакций. Чтобы указать для базы данных или журнала транзакций несколько устройств, следует сделать эти ус­тройства используемыми по умолчанию.

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

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

Отчет мастера преобразования в формат Microsoft SQL Server содержит:

• сведения о базе данных, включая ее размер, а также журнал транзакций и имена и размеры устройств для базы данных Microsoft SQL Server 6.5;

• параметры преобразования в формат Microsoft SQL Server, включая атрибуты таблиц, выбранных для преобразования, и спо­соб преобразования;

• сведения о таблицах, включая сравнение значений в Microsoft Access и Microsoft SQL Server для имен, типов данных, индексов, условий на значение, значений по умолчанию, триггеров, а так­же режим добавления штампов времени;

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

Тексты баз данных Microsoft SQL Server 7.0 или более поздней версии мастер преобразует в юникод, добавив идентификатор стро­ки во все строковые значения и префикс «п» во все типы данных.

Все типы данных Microsoft Access преобразуются в эквивалент­ные типы Microsoft SQL Server.

Рассмотрев технологии создания таблиц (основы серверной части приложения управления удаленными базами данных) сред­ствами визуального проектирования СУБД Microsoft Access и пре­образование базы данных формата Microsoft Access в приложение типа клиент—сервер формата Microsoft SQL Server, можно сде­лать следующий вывод: особенность предлагаемых технологий сво­дится к широкому привлечению специалистов конкретной предмет­ной области к разработке проекта баз данных.

В гл. 3 рассматривались операторы языка SQL. В данном подраз­деле рассмотрим подробнее операторы создания и модификации таблиц.

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

Как правило, право создания таблиц обычно закрепляется за администратором базы данных (АБД).

В соответствии с стандартом ISO/EC 9075:2003 таблицы и дру­гие объекты базы данных существуют в некоторой среде (environment). Среда состоит из одного или нескольких каталогов (catalog). В свою очередь, каталог состоит из некоторого количе­ства схем (schema). Схема представляет собой поименованный на­бор объектов базы данных, которые определенным образом свя­заны друг с другом (таблицы с формами или запросами, запросы с формами или отчетами и т. п.). Все объекты схемы имеют опре­деленного владельца — разработчика.

Стандарт также регламентирует механизм создания и удаления схем. Оператор создания схемы имеет следующий формат:

create shema [Name {имя схемы} | AUTORAZIATION Creator Identifier {имя пользователя}]

Следовательно, если создателем схемы под именем CAPR TP является Cidoroff, то данный оператор будет выглядеть следующим образом:

create shema CAPR TP AUTORAZIATION Cidoroff;

Схему можно удалить с помощью оператора DROP SHEMA, который имеет следующий формат:

drop shema Name [RESTRICT | CASCADE]

Ключевое слово RESTRICT (принимается по умолчанию) оз­начает, что изначально схема должна быть пустой, иначе выпол­нение операции будет отменено. Если указано ключевое слово CASCADE, то при выполнении оператора будут удалены все свя­занные с удаляемой схемой объекты.

После создания общей структуры базы данных можно присту­пить к созданию таблиц с помощью оператора CREATE TABLE, который в общем виде имеет следующий формат:

В результате выполнения оператора CREATE TABLE будет со­здана таблица, имя которой задается параметром TableName, со­стоящая из одного или нескольких столбцов типа dataType.

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

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

Конструкция CHECK позволяет задать список значений для ввода в соответствующее поле таблицы.

Конструкция PRIMARY KEY определяет один или несколько столбцов, которые образуют первичный ключ таблицы. Если эта конструкция предусмотрена в конкретной версии SQL, то она должна применятся при создании каждой таблицы. По умолча­нию для всех столбцов, представляющих собой первичный ключ, предусмотрено применение ограничения Not NULL.

При создании таблицы разрешено использование только од­ной конструкции PRIMARY KEY. В этом случае база данных от­вергает все попытки выполнения операции INSERT или UPDATE, которые могут повлечь за собой создание строки с повторяющим­ся значением в столбце (столбцах), определенном конструкцией PRIMARY KEY. Таким образом, в базе данных гарантируется уни­кальность значений первичного ключа.

В конструкции FOREIGN KEY определяются внешний ключ (дочерней) таблицы и ее связь с другой (родительской) таблицей. Эта конструкция позволяет реализовать ограничения ссылочной целостности и состоит из следующих частей.

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

• Вспомогательная конструкция REFERENCES, указывающая на родительскую таблицу (т. е. таблицу, в которой определен соот-ветствующий потенциальный ключ). Если список UstOfCandidateKeyColumns опущен, предполагается, что определе­ние внешнего ключа совпадает с определением первичного ключа родительской таблицы. В этом случае родительская таблица долж­на иметь в своем операторе CREATE TABLE конструкцию PRIMARY KEY.

• Необязательное правило обновления ON UPDATE для опре­деления взаимосвязи между таблицами, указывающее, какое дей­ствие (referentialAction) должно выполняться при обновлении в родительской таблице потенциального ключа, соответствующего внешнему ключу дочерней таблицы. В качестве параметра referentialAction можно указать CASCADE, SET NULL, SET DEFAULT или NO ACTION. Если конструкция ON UPDATE опу­щена, то по умолчанию подразумевается, что в соответствии с значением NO ACTION никакие действия не выполняются.

• Необязательное правило удаления ON DELETE для опреде­ления взаимосвязи между таблицами, указывающее, какое дей­ствие {referentialAction) должно выполняться при удалении строки из родительской таблицы, которая содержит потенциальный ключ, соответствующий внешнему ключу дочерней таблицы. Определе­ние параметра referentialAction совпадает с определением такого же параметра для правила ON UPDATE.

• Опция MATCH, позволяющая ввести дополнительные огра­ничения, касающиеся применения значений NULL в внешнем ключе. По умолчанию ограничение ссылочной целостности удов­летворяется, если любой компонент внешнего ключа имеет зна­чение NULL или если в родительской таблице есть соответству­ющая строка. Если задана опция MATCH NULL, то либо все ком­поненты внешнего ключа должны быть пусты (NULL), либо все должны иметь непустые значения. Если задана опция MATCH PARTIAL, то либо все компоненты внешнего ключа должны быть пусты (NULL), либо в родительской таблице должна существовать хотя бы одна строка, способная удовлетворить это ограничение, если все остальные значения NULL были подставлены правильно.

В операторе создания таблицы может быть задано любое число конструкций FOREIGN KEY. Конструкция CHECK позволяет определять дополнительные ограничения. Если конструкция

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

Модификация таблиц. Для изменения структуры таблицы стан­дартом ISO/EC 9075:2003 предусмотрено применение оператора ALTER TABLE, который позволяет:

• ввести новый столбец в таблицу;

• удалить столбец из таблицы;

• ввести новое ограничение таблицы;

• удалить ограничение таблицы;

• задать для столбца значение, применяемое по умолчанию;

• удалить опцию, предусматривающую применение для столб­ца значения, заданного по умолчанию.

Рассмотрим основной формат оператора ALTER TABLE.

Как видим, многие параметры оператора ALTER TABLE со­впадают с параметрами оператора GREATE TABLE.

Конструкция ADD COLUMN добавляет столбцы в базу дан­ных.

Конструкция DROP COLUMN задает имя столбца, удаляемо­го из таблицы.

Ключевое слово RESTRICT указывает, что операция DROP не выполняется, если на столбец имеется ссылка в другом объек­те базы данных. Это значение предусмотрено по умолчанию.

Ключевое слово CASCADE указывает, что выполнение опера­ции DROP продолжается, и ссылки на столбец исключаются из всех объектов. Причем операция выполняется каскадно, т.е. если столбец удаляется из объекта, содержащего ссылку, то в базе дан­ных выполняется проверка — имеются ли ссылки на удаляемый столбец в другом объекте базы данных, и такие ссылки также удаляются. Данная процедура выполняется для всех столбцов таб­лицы.

Удаление таблиц. Для удаления таблицы стандартом ISO/EC 9075:2003 предусмотрено применение оператора DROP TABLE, имеющего следующий формат:

В данном формате ключевое слово RESTRICT указывает, что операция DROP не выполняется, если в базе данных имеются другие объекты, существование которых зависит от наличия уда­ляемой таблицы, a CASCADE означает, что выполнение опера­ции DROP продолжается и из базы данных удаляются все зависи­мые объекты и объекты, зависимые от этих объектов.

В реляционных базах данных кроме объекта таблица (relation) существует объект представление (view).

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

С точки зрения пользователя представление является отноше­нием, которое существует постоянно и с которым можно рабо­тать точно так же, как с базовым отношением. Однако представ­ление не хранится в базе данных так, как базовое отношение: в базе данных (системном каталоге базы данных) хранится лишь его определение.

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

В то же время, если пользователи вносят в представление неко­торые допустимые изменения, последние немедленно заносятся в базовые отношения представления.

Рассмотрим кратко назначение представлений.

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

• Представление позволяет организовать одновременный мно­гопользовательский доступ к базовым отношениям.

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

Для создания представлений предназначен оператор GREATE VIEW, имеющий следующий формат записи:

Представление определяется с помощью подзапроса subselect, оформленного в виде оператора SELECT языка SQL. Заданный параметром subselect подзапрос принято называть определяющим. Если указана конструкция

WITH [CASCADED | LOCAL] СНЕС OPTION

гарантируется, что в тех случаях, когда строка данных не удовлет­воряет условию, указанному в конструкции WHERE определяю­щего запроса представления, она не будет добавлена в его базо­вую таблицу.

Приведем текст представления, предназначенного для выбора из базы данных «Извещение» всех полей таблицы № извещения по заданному диапазону времени (начальной и конечной датам):

Для удаления представлений служит оператор DROP VIEW, имеющий следующий формат записи:

drop view ViewName [RESTRICT | CASCADE]

Если в операторе задано ключевое слово RESTRICT, а в базе данных существуют объекты, зависящие от удаляемого представ­ления, то выполнение оператора блокируется.

Если в операторе задано ключевое слово CASCADE, то при выполнении оператора будут удалены все связанные с удаляемым представлением объекты.

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

Дата добавления: 2014-01-07