Индивидуальное задание

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

Лабораторная работа № 3

Тема: "Создание отношений базы данных"

Цель работы: усвоить способы создания индексов, отношений и схемы отношений (диаграммы) базы данных средствами СУБД MS SQL Server 2008;

Краткие теоретические сведения Создание и использование индекса

Индекс - это вспомогательная структура данных, используемая системой SQL Server для доступа к данным. В зависимости от типа индекса он хранится вместе с данными или отдельно от данных.

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

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

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

Уникальный индекс содержит только одну строку данных для каждого индексного ключа; иными словами, значения индексного ключа не могут присутствовать в индексе более одного раза. SQL Server обеспечивает уникальность индекса по колонкам или комбинации колонок, образующих ключ индекса. SQL Server не допускает занесения дублированных значений ключа в базу данных. SQL Server создает уникальные индексы, если задали по таблице ограничение PRIMARY KEY или ограничение UNIQUE.

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

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

Существует два типа индексов: кластеризованные индексы и некластеризованные индексы. Кластеризованный индекс хранит в своих узлах-листьях реальные строки данных. Поскольку данные кластеризованного индекса хранятся в узлах-листьях, то данные становятся доступны, как только найден определенный узел-лист, что может сокращать количество операций ввода-вывода. Еще одним преимуществом кластеризованных индексов является то, что считываемые данные получаются в отсортированном по индексу виде. Недостатком использования кластеризованного индекса является то, что доступ к таблице всегда происходит через индекс, что может приводить к дополнительной нагрузке на систему. Поскольку в кластеризованном индексе хранятся реальные данные, вы не можете создать более одного кластеризованного индекса по таблице.

Некластеризованный индекс является вспомогательной структурой и не содержит реальных данных таблицы в своих узлах-листьях. В некластеризованном индексе узел-лист содержит значение ключа, а также идентификатор строки (Row ID), указывающий нужную строку в таблице. Это значение обеспечивает быстрый доступ к реальным данным, указывая точное местоположение этих данных. На практике используется несколько некластеризованных индексов по различным колонкам таблицам

Кластеризованные и некластеризованные индексы создаются с помощью мастеров в Management Studio или с помощью команды SQL CREATE INDEX.

1) Создание индекса с помощью Management Studio

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

1) Редактирование индекса с помощью Management Studio

Убедитесь, что окно Management Studio открыто и что вы развернули узлы древовидного списка так, чтобы видеть узлы внутри вашей базы данных
Найдите таблицу, которую необходимо снабдить индексом и щелкните на ней правой кнопкой мыши и выберите команду Design table. В результате откроется диалог Properties, в котором следует выбрать вкладку Indexes/Keys. Появится следующее окно, позволяющее создавать, редактировать и удалять ограничения, связанные с индексами. Установите в нем нужные вам параметры индекса.

Создание индекса с помощью T-SQL

Используя T-SQL для создания индекса, вы можете генерировать сценарий для соответствующей команды и запускать его многократно. Кроме того, этот метод создания индекса дает вам больше гибкости, поскольку вы имеете доступ к большему числу параметров. Вы можете также выполнять этот сценарий с помощью редактора запросов Query Editor.

Для создания индекса с помощью T-SQL вы должны использовать оператор CREATE INDEX. Эта команда имеет следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX имя индекса ON имя_таблицы

(имя_колонки[, имя_колонки, имя_колонки,... ])

[ WITH параметры ]

[ ON имя_группы_файлов]

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

Запустите Query и введите следующий код в панель Query:

USE MyDB

CREATE CLUSTERED INDEX имя индекса ON имя_таблицы (имя_колонки)

ON [PRIMARY]

Выполните этот код нажатием клавиш F5 или Ctrl+E.

Отношения базы данных

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

Существует три основных вида отношений между таблицами, которые могут существовать в базе данных:

Один-к-одному (один-один).
Один-ко-многим (один-множество).
Многие-ко-многим (множество-множество).

Отношение "один-к-одному" - это самое простое для понимания отношение, хотя используется оно сравнительно редко - одна запись в одной таблице должна соответствовать ровно одной записи в другой таблице. Такая ситуация может возникнуть лишь в случаях, например, если вам требуется разбить одну очень большую таблицу на две. СУБД SQL Server 2008 поддерживает также связь «нуль или один к одному». По существу такая связь аналогична связи «один к одному», за исключением того, что с одной из сторон связи допускается наличие или отсутствие строки, соответствующей другой строке.

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

Отношение "многие-ко-многим". Иногда встречается ситуация, когда ни одной, одной или нескольким записям в главной таблице соответствует ни одна, одна или несколько записей в таблице подчиненной. В качестве иллюстрации можно привести пример компании, которая располагает несколькими складами (их состояние отражено в главной таблице), причем склады эти снабжают товарами несколько магазинов (подчиненная таблица). Если склады организованы таким образом, что несколько складов могут снабжать один и тот же магазин (например, каждый склад специализируется на определенном виде товаров), то получится, что каждый склад снабжает несколько магазинов, а каждый магазин снабжается несколькими складами. В СУБД SQL Server 2008 не предусмотрен способ непосредственного определения связи "многие-ко-многим", поэтому для организации подобной связи применяется способ, основынный на использовании промежуточной таблицы.

Внешний ключ - это ключ в подчиненной таблице (таблице-потомке), в которой столбец или набор столбцов прямо соответствует аналогичной информации в главной таблице. Внешний ключ существует только тогда, когда существует связь между главной и подчиненной таблицами. После задания внешнего ключа на таблице устанавливаются зависимость между таблицей для которой определяется внешний ключ и таблицей на которую ссылается внешний ключ. Столбцы внешнего ключа могут содержать значение NULL, однако проверка на ограничение FOREIGN KEY игнорируется. Внешний ключ может быть проиндексирован, тогда сервер будет быстрее отыскивать нужные данные. Внешний ключ определяется как при создании, так и при изменении таблиц. Внешний ключ может ссылаться только на те колонки, которые содержат в ссылочной таблице ограничение PRIMARY KEY или UNIQUE. Если вы попытаетесь создать внешний ключ, который ссылается на колонку, не являющуюся частью одного из этих ограничений, то SQL Server возвратит сообщение об ошибке. Кроме того, тип данных и размер колонки или колонок внешнего ключа должны совпадать со ссылочной колонкой или колонками.

Ограничение FOREIGN KEY определяет внешний ключ, который задает связь между двумя таблицами. Колонка или колонки внешнего ключа одной таблицы ссылаются на потенциальный ключ (одна или несколько колонок) в другой таблице. При вставке строки в таблицу с ограничением FOREIGN KEY значения, которые должны быть внесены в колонку или колонки, определенные как внешний ключ, сравниваются со значениями в потенциальном ключе ссылочной таблицы. Если ни одна из строк ссылочной таблицы не соответствует значениям во внешнем ключе, то вставка новой строки не выполняется. Но если значения внешнего ключа, которые нужно внести в таблицу, все же имеются в потенциальном ключе другой таблицы, то вставка новой строки будет выполнена. Если значение, которое должно быть занесено в таблицу с ограничением FOREIGN KEY, равно NULL, то это тоже допустимо.

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

<имя колонки> <тип данных> <допустимость Null-значений>

FOREIGN KEY REFERENCES <имя таблицы> (<имя колонки>)

[ON DELETE {CASCADE| ON ACTION| SET NULL| SET DEFAULT}]

[ON UPDATE {CASCADE| ON ACTION| SET NULL| SET DEFAULT}]

При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

CASCADE - выполняется удаление|модификация строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;
SET NULL - выполняется удаление|модификаия строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;
SET DEFAULT - выполняется удаление|модификация строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
NO ACTION - операция удаления|модификации строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

Чтобы получить более ясное представление о внешних ключах, рассмотрим некоторые примеры. Сначала мы создадим таблицу с именем items (товары), которая содержит ограничение PRIMARY KEY по колонке itemid (идентификатор товара), как в следующем операторе:

CREATE TABLE items

(itemname char(15) NOT NULL,

itemid smallint NOT NULL IDENTITY(1,1),

price smallmoney NULL,

item_desc varchar(30) NOT NULL DEFAULT "none",

CONSTRAINT PK_itemid PRIMARY KEY (item id))

Затем мы создадим таблицу с именем inventory, содержащую ограничение FOREIGN KEY с именем FK_itemid, которое ссылается на колонку itemid в таблице items, как в следующем операторе:

CREATE TABLE inventory

(store_id tinyint NOT NULL,

itemid smallint NOT NULL,

item_quantity tinyint NOT NULL, CONSTRAINT

FK_itemid FOREIGN KEY (itemid) REFERENCES items(itemid))

Диаграмма базы данных в простейшей форме отображает таблицы (с перечислением атрибутов этих таблиц) и отношения между таблицами.

Создание схемы базы данных (диаграммы)

Чтобы создать схему для базы данных с таблицами, выполните следующие шаги:

Раскройте требуемую БД в левой панели Management Studio и затем щелкните правой кнопкой мыши на Database Diagrams (Схемы). Выберите из контекстного меню пункт New Database Diagram (Выбор новой схемы базы данных), чтобы появилось окно добавления таблиц Add table.

Щелкните на кнопке Add. Выделите таблицы, которые хотите включить в вашу схему, в списке Available Tables (Имеющиеся таблицы) и затем щелкните на кнопке Add. После щелчка на кнопке вы увидите схему базы данных.
Сохраните вашу схему, указав описательное имя (введите имя, когда появится соответствующий запрос).

Создание отношений на схеме базы данных

Раскройте требуемую БД в левой панели Management Studio и затем найдите нужную диаграмму. Щелкните на ней правой кнопкой мыши и выберите команду Modifi. В результате откроется диаграмма
Для создания отношения найдите столбец в родительской таблице по которому будут связаны два отношения, поместив в этот столбец указатель мыши, нажмите левую кнопку мыши и не отпуская кнопки, переместите указатель до другой таблицы. Отпустите кнопку, когда указатель будут находиться на столбце по которому вы хотите связать два отношения. Нажимать кнопку следует на сером маркере слева от имени столбца.

Такое перетаскивание между столбцами двух таблиц приведет к открытию диалогового окна Tables and Columns, позволяющее подтвердить, действительно ли должна быть установлена связь между столбцами соответствующих таблиц. Подтвердите либо измените имя связи и другие данные и затем щелкните на «Ok».

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

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

Изменение макета диаграммы базы данных

Часто расположение объектов схемы БД на диаграмме после ее создания SQL Server не очень удобно для восприятия и его лучше изменить.

Откройте окно диаграммы БД. Вначале следует изменить масштаб отображения, для чего на панели инструментов выберите масштаб под размер диаграммы.
Следующее изменение может заключаться в перемещении таблиц. Чтобы выделить таблицу для ее перемещения, достаточно поместить указатель мыши в левый верхний угол той области, в которой необходимо выделить таблицы, нажать левую кнопку и, не отпуская кнопки, растянуть область так, чтобы она покрыла необходимые таблицы. Затем отпустите кнопку, и таблицы будут выделены. Захватив мышью одну из выделенных таблиц, продолжая удерживать кнопку мыши нажатой, вы можете перетащить таблицы в другое место на диаграмме и затем отпустите кнопку мыши.
При отображении таблицы, показываются только имена столбцов. Если такой информации недостаточно, то при помощи кнопки Table Niew на панели инструментов можно изменить режим отображения таблиц. Такое меню можно раскрыть, щелкнув на таблице правой кнопкой мыши и выбрав в контекстном меню команду Table View.
Диаграмму можно снабдить этикеткой. Для этого используют кнопку a½b (New Text Annotation), которая позволяет добавить текст в область диаграммы. Щелкните на этой кнопке и на диаграмме появится текстовое поле, в которое можно ввести описание диаграммы. После этого найдите для текста на диаграмме подходящее место.
При необходимости добавить на диаграмму новую таблицу (вы ее только что создали и хотите обновить диаграмму), то воспользуйтесь на панели инструментов кнопкой Add table on diagram.
При помощи диаграммы можно создать и новую таблицу в составе БД. Для этого на панели инструментов выберите кнопку New table. В результате появится окно с запросом имени таблицы, введите это имя и нажмите «Ok». После чего откроется диалоговое окно конструирования таблицы. Заполнив это окно, щелкните на нем правой кнопкой мыши. В появившемся контекстном меню вы увидите среди прочих команды Delete table from database и Remove table from diagram. Первую команду выполнить пока невозможно, так как таблица храниться только в ОЗУ, то удалить ее из БД нельзя. При выборе второй команды система вам предложит выбор – сохранить созданную таблицу вне данной диаграммы. При выборе NO, таблица будет удалена без сохранения. После этого сохраните диаграмму, щелкнув на кнопке Save на панели инструментов.

Лабораторная работа рассчитана на 2 часа аудиторных занятий и состоит в изучении теоретического материала и получении практических навыков по созданию отношений между таблицами базы данных, построению и модификации схемы БД. Сдача лабораторной работы заключается в ответе на контрольные вопросы и демонстрации индивидуального задания на ПК.

Содержание отчета:

Название и цель работы
Индивидуальное задание
Схема (диаграмма) БД с аннотацией
Описание зависимостей между таблицами БД, включая правила ограничений целостности и созданные индексы

Контрольные вопросы