Определение структуры таблиц

Создадим в базе данных о студентах EDU три таблицы. Первая таблица, STUD, будет хранить информацию о студентах; вторая таблица PRED – справочник предметов; третья, VED – оценочная ведомость. Ниже представлены все поля этих таблиц и их основные свойства (см. табл.3.1).

Таблица 3.1 - Таблицы базы данных о студентах

Имя столбца	Тип данных	Разрешить null	Описание
STUD
Id	int, identity	нет	Уникальный идентификационный номер студента, на который можно ссылаться
Code	varchar(6)	нет	№ зач. книж­ки
LastName	varchar(64)	нет	Фамилия
FirstName	varchar(64)	нет	Имя
MiddleName	varchar(64)	нет	Отчество
BirthDay	int	да	Год рожде­ния
Sex	varchar(1)	нет	Пол
PRED
Id	int, identity	нет	Уникальный идентификационный номер предмета
Name	varchar(128)	нет	Название предмета
VED
IdStud	int	нет	Ссылка на номер студента
IdPred	int	нет	Ссылка на номер ведомости
Value	int	нет	Оценка

 

Таблицы можно создавать как в графическом интерфейсе (в среде Management Studio), так и с помощью кода T-SQL. Воспользуемся самым простым, графическим способом (рис. 3.1).

Сначала создадим таблицу STUD:

1. В дереве обозревателя объектов в базе данных EDU в контекстном меню узла «Таблицы» выберите команду «Создать таблицу…». В рабочей области должна появиться вкладка с конструктором таблиц.

2. В первую строку в столбце «Имя столбца» введите Id, в столбце «Тип данных» выберите int. Убедитесь, что параметр «Разрешить значения null» отключен.

3. В нижней половине экрана в разделе «Свойства столбцов» введите описание поля и измените значение параметра «Спецификация идентификатора / (Идентификатор)» на «Да» для того чтобы значения номера клиента формировались автоматически. Свойство «Идентифицирующий столбец» (Identity), обычно используемое совместно с типом данных int, предназначено для автоматического приращения значения на единицу при добавлении каждой новой записи. К примеру, клиент, добавленный в таблицу первым, будет иметь значение идентификатора 1, вторым – 2, третьим – 3, и т.д.

 

Рисунок 3.1- Окно ввода свойств таблицы

 

4. Аналогичным образом введите описания всех остальных полей и закройте окно конструктора таблиц. Введите в качестве имени таблицы STUD. Вновь созданная таблица должна появиться в дереве обозревателя объектов в папке «Таблицы».

Создание ограничений

Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных, т. е. ограничить возникновение в базе данных некорректных или противоречивых данных вследствие добавления, изменения или удаления какой-либо записи, например, ввод отрицательной цены или количества товара. Существует четыре типа целостности данных: доменная, сущностная, ссылочная и пользовательская (или бизнес-правила). Рассмотрим основные инструменты, предоставляемые в SQL Server для их реализации.

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

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

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

В качестве примера создадим первичный ключ для таблицы STUD. В данном случае идеальным кандидатом на роль первичного ключа выступает столбец Id, поскольку значения, содержащиеся в нем, являются уникальными по определению (для него установлено свойство identity). Для создания первичного ключа в таблице STUD выполните следующие шаги:

1. В контекстном меню таблицы STUD выберите команду «Проект».

2. В окне конструктора таблиц щелкните правой кнопкой мыши на поле Id и выберите команду «Задать первичный ключ» или нажмите кнопку  на панели инструментов (рис. 3.2). Обратите внимание на то, что слева от поля Id теперь отображается значок ключа, указывающий, что поле является первичным ключом.

3. Закройте конструктор таблиц с сохранением изменений

 

Рисунок 3.2 - Окно ввода первичного ключа

 

Использование ограничений на уникальность. Между ограничениями первичного ключа и ограничениями на уникальность существует два отличия. Первое состоит в том, что первичные ключи используются вместе с внешними ключами для обеспечения целостности ссылок (рассматривается в следующем разделе). Второе отличие заключается в том, что ограничения на уникальность позволяют вставлять в его поля пустые значения (null), чего нельзя делать с первичными ключами. Во всем остальном они служат одной цели – обеспечить уникальность данных, вставляемых в поле. Ограничение на уникальность следует использовать в тех случаях, когда нужно гарантировать, что дублирующие значения не будут добавляться в поле, не являющееся частью первичного ключа, в частности, все потенциальные ключи должны быть организованы в виде ограничений уникальности. Хорошим примером такого поля, требующего ограничение на уникальность, является поле Code, поскольку это поле должно быть уникальными у каждого студента.

1. Для открытия конструктора таблиц в контекстном меню таблицы STUD выберите команду «Проект». На панели инструментов нажмите на кнопку «Управление индексами и ключами» .

2. В открывшемся окне «Индексы и ключи» щелкните кнопку «Добавить» и введите следующие параметры для нового уникального ключа (рис. 3.3):

· Столбцы: Code

· Тип: Уникальный ключ

· (Имя): CK_Code

Рисунок 3.3 - Диалоговое окно создание ограничения на уникальность

 

3. Закройте конструктор таблиц с сохранением изменений.

  Обеспечение целостности ссылок. Сейчас в базе данных EDU имеются три таблицы, которые тесно взаимосвязаны между собой и соответственно данные содержащиеся в них должны быть согласованы и непротиворечивы. Например, в таблице VED не должно быть записей об оценках студентов, данные о которых отсутствуют в таблице STUD. Чтобы гарантировать отсутствия в базе данных таких записей необходимо обеспечить целостность ссылок.

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

Внешний ключ используется в комбинации с первичным для связывания двух таблиц по общему столбцу (столбцам). К примеру, можно связать таблицы STUD и VED по столбцу IdStud, который присутствует в обеих таблицах. Поскольку поле Id таблицы STUD является его первичным ключом можно использовать поле IdStud таблицы VED в качестве внешнего ключа, который свяжет эти две таблицы. После организации такого ограничения будет невозможно добавить запись в таблицу VED, если в таблице STUD нет записи с соответствующим значением Id. Кроме того, при отсутствии каскадирования (рассматривается в следующем разделе) невозможно удалить запись из таблицы STUD при наличии связанных с ней записей в таблице VED, поскольку нельзя закрыть ведомость без информации о студенте. Для создания описанного ограничения внешнего ключа в Management Studio выполните следующие шаги:

1. В контекстном меню для таблицы VED выберите команду «Отношения…» (рис. 3.4).

 

Рисунок 3.4 - Диалоговое окно связей таблицы VED

2. В открывшемся окне «Отношения внешнего ключа» заполните следующие поля (рис. 3.5):

·     (Имя): FK_VED_STUD

·     Спецификация таблиц и столбцов: для заполнения данного блока щелкните на кнопке с многоточием и в появившемся окне «Таблицы и столбцы» в качестве таблицы первичного ключа выберите STUD, а полей связи - IdStud.

 

Рисунок 3.5 - Диалоговое окно создание внешнего ключа

 

3. Закройте все открывшиеся окна с сохранением изменений.

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

Настроить правила каскадирования можно при создании ограничения внешнего ключа в окне «Связи по внешнему ключу» изменяя значения параметров «Правило обновления» и «Правило удаления» блока «Спецификация INSERT и UPDATE». Оба этих параметра могут содержать четыре значения, описанные в таблице 3.2.

 

 

Таблица 3.2 - Настройки правил каскадирования

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

 

    5. Использование диаграмм баз данных

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

1. В контекстном меню папки «Диаграммы базы данных» выберите команду «Создать диаграмму базы данных».

2. В диалоговом окне «Добавление таблиц» выберите все таблицы и нажмите на кнопку «Добавить».

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

 

Рисунок 3.6 - Диаграмма базы данных EDU

 

Используя диаграмму базы данных ограничения внешнего ключа можно создавать значительно быстрее: лишь перетаскивая поля из одной таблицы в другую. В качестве примера создадим внешний ключ в таблице VED по полю IdPred для связи с таблицей PRED:

1. Выделите в таблице PRED поле Id и, не отпуская кнопку мыши, перетащите его на поле IdPred таблицы VED.

2. В диалоговых окнах «Таблицы и столбцы» и «Связь по внешнему ключу» примите настройки по умолчанию.

3. Сохраните диаграмму базы данных под именем Main.

4. Расположите таблицы в канонической форме (главные таблицы выше подчиненных) в соответствии с вышеприведенным рисунком.

Цели лабораторной работы №3

1. Освоение методики обеспечения ссылочной целостности в базах данных, состоящей из 2-х и более таблиц.

2. Получение навыков построения структуры базы данных.

 

 Порядок выполнения работы

1. Изучить методические указания.

2. Определить структуру базы в соответствии с вариантом задания (см. методические указания к л/р №1), обеспечив связь с дополнительной таблицей (ее данные определяются студентами самостоятельно) типа «один-ко-многим».

3. Создать таблицы базы данных.

4. Обеспечить ссылочную целостность в базе данных.

5. Оформить отчёт.

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

1. Организация ссылочной целостности.

2. Формирование индексных полей (первичных, вторичных, внешних).

3. Ограничения на уникальность.

4. Использование каскадной ссылочной целостности.

5. Создание диаграммы базы данных.

Требования к отчёту

Отчёт оформляется на листах формата А4, на титульном листе указывается название работы, дисциплина, студент, выполнив­ший работу и преподаватель, принявший отчет.

Отчёт должен включать в себя следующие пункты:

1. Постановка задачи (формулировка задания).

2. Таблица параметров связей, таблица характеристик индексов.

3. Структура базы данных с определением типа связи.

4. Диаграмма базы данных.