Рассмотрим пошагово процесс создания таблицы Группа811
1. В окне «Обозреватель объектов» выберите созданную вами ранее базу данных
Project811 нажатием ЛКМ, она должна отобразиться (Рис. 5)
Рис. 5. Обозреватель объектов
2. Перейдите в папку «Таблицы» двойным нажатием ЛКМ на неё (Рис. 6)
Рис. 6. Обозреватель объектов
3. Нажатие ПКМ на папке «Создать таблицу» вызовет контекстное меню, из которого выберите «Создать таблицу» (Рис. 7)
Рис. 7. Контекстное меню
4. Появится окно как на рис. 8. Справа отобразится панель свойств, в которой напишите название таблицы (в примере Группа811). Остальные параметры остаются без изменения.
Рис. 8. Среда SQL Server Management Studio
5. Приступим к созданию столбцов в таблице, согласно логической концепции, разработанной ранее. Введите имя столбца G_ID811 и нажмите клавишу TAB, SQL server предложит вам тип данных по умолчанию nchar длиной 10 символов. Измените тип данных на int и программа автоматически изменит длину столбца на 4 эти изменения отслеживаются в окне «Свойств таблицы», расположенном ниже (Рис. 9,Рис. 10)
Рис. 9. Имя столбца
Рис. 10. Свойства столбца
В строке описание заполните «Идентификатор группы»
В окне свойств таблицы выберите «Спецификация идентификатора» нажмите на знак «+» рядом с ней и установите свойство «(Идентификатор)» значение «ДА» Ниже вы можете выбрать начальное значение этого поля и приращение его значения т.е. шаг (increment). (Рис. 11)
Рис. 11. Свойства столбца
Обратите внимание на то, что теперь значение NULL запрещено. Попробуйте снова нажать на галочку для разрешения. Вы должны увидеть следующее окно
Рис. 12 Окно предупреждения
Примечание
Описание столбца
Ряд расширенных свойств, таких как описание столбца, были созданы Microsoft как составная часть стандартной установки сервера, и вы можете создавать дополнительные расширенные свойства для хранения специфичной для приложения или для сайта информации об объектах базы данных.
Каждое расширенное свойство обладает задаваемым пользователем именем и значением. Значение расширенного свойства имеет тип sql_variant и может содержать до 7500 байт данных. Вы можете определить несколько расширенных свойств для любого объекта с использованием хранимых процедур.
Точность и масштаб
Использование полей точность (precision) и масштаб (scale) становится возможным при выборе типа данных Decimal.
Точность числового значения представляет собой максимальное количество десятичных разрядов, которые представляют значение как слева, так и справа от десятичной точки. Масштаб значения представляет собой количество знаков справа от десятичной точки. Например, значение 3647,311 имеет точность 7 (общее количество знаков) и масштаб 3 (количество знаков справа от десятичной точки).
Важно понимать, что точность и масштаб числового значения не влияют на длину столбца. Длина столбца определяется типом данных. Точность и масштаб указывают, как SQL Server будет интерпретировать данные, хранящиеся в столбце.
Нулевое значение (Null)
Значение Null представляет собой особый вид значений в реляционной технологии, которое используется для указания, что значение либо отсутствует (т.е. неизвестно), либо не существует. Использование значений типа Null в ряде случаев является сомнительным и спорным.
Определение первичного ключа. Для этого наведите мышкой на столбец который хотите сделать первичным ключом (Primary Key). Щелкните на нем ПКМ и выберите «Задать первичный ключ» (Рис. 13)
Рис. 13. Контекстное меню
6. Для примера рассмотрим добавление следующего столбца НГ811 в таблицу Группа811Для него в поле Тип данных установим тип данных text; в поле Разрешить значение NULLфлажок, выставленный по умолчанию можно оставить. В поле Description(Описание)введем описание столбца Номер группы. (Рис. 14)
Рис. 14. Значение Null для НГ811
Аналогично предыдущему создайте еще 2 столбца ПБАЛЛ811 и КОЛ811
Окончательный вид таблицы группа должен быть следующим (Рис. 15)
Рис. 15. Значение Null для ПБАЛЛ811
Список таблиц БД
По описанной инструкции создайте таблицы c указанными ниже столбцами, заменяя {XXX} на вашу уникальную кодировку.
КАФЕДРА{XXX}
Ккаф{XXX}(Код кафедры) – int, первичный ключ
Нкаф{XXX}(Название кафедры) – NVARCHAR(50)
Тел{XXX}(Телефон кафедры) – NVARCHAR (15)
Зав{XXX}(Заведующий кафедры) – NVARCHAR(30)
Фото{XXX}(Фотография заведующего кафедры) - image
ПРЕДМЕТ{XXX}
КП{XXX}(Код предмета) – int, первичный ключ
НП{XXX}(Название предмета) - NVARCHAR(30)
Часы{XXX}(Кол-во часов по предмету) - tinyint
Лек{XXX}(Часы, отведенные для лекций по предмету) - tinyint
Пр{XXX}(Часы, отведенные для практики по предмету) - tinyint
ЧС{XXX}(Число семестров) - tinyint
Прогр{XXX}(Описание программы предмета) - NVARCHAR(100)
Группа{XXX}
G_ID{XXX}(Идентификатор группы) - int
КолСтуд{XXX}(Количество студентов) - tinyint
СТУДЕНТ{XXX}
ID_ ST{XXX} (номер зачетки) – int, первичный ключ
G_ID{XXX}(Идентификатор группы) - int
НС{XXX}(Номер студента по списку) - tinyint
ФИО{XXX}(ФИО студента) - NVARCHAR(50)
Годр{XXX}(Дата рождения) - Date
Адрес{XXX}(Адрес студента) - NVARCHAR(50)
ПрохБалл{XXX}(Проходной балл студента) - tinyint
ПРЕПОДАВАТЕЛЬ{XXX}
Табн{XXX}(Табельный номер преподавателя) – int, первичный ключ
ФИО{XXX}(ФИО преподавателя) - NVARCHAR(50)
Ст{XXX}(Ученая степень преподавателя) - NVARCHAR(50)
Зв{XXX}(Ученое звание преподавателя) - NVARCHAR(50)
Ккаф{XXX}(Код кафедры) - int
ИЗУЧЕНИЕ{XXX}
ID{XXX}(Идентификатор изучения) – int, первичный ключ
G_ID{XXX}(Идентификатор группы) – int
КП{XXX}(Код предмета) – int
Табн{XXX}(Табельный номер преподавателя) – int
Видз{XXX}(Вид занятий) - NVARCHAR(10)
ЧАСЫ{XXX}(Часы) – tinyint
СбаллГр{XXX}(Средний балл группы) - decimal(Точность 3,Масштаб 2)
Так же для поля ID{XXX} необходимо задать следущие параметры в свойствах:
Идентификатор = «да»,
Начальное значение идентификатора = 1,
Шаг приращения = 1.
УСПЕВАЕМОСТЬ{XXX}
ID{XXX}(Идентификатор изучения) – int, первичный ключ
ID ST{XXX}(номер зачетки) – int, первичный ключ
Оценка{XXX}(Оценка) - NVARCHAR(5)
ДатаСдачи {XXX}(Дата сдачи) – Date.
Создание ограничений
Проверочные ограничения задают определенные значения в столбцах. Для создания ограничения таблицы нужно открыть «Таблицы», щелкнуть правой кнопкой мыши по таблице и выбрать «Проект», откроется окно структуры таблицы, в нем щелкните правой кнопкой мыши по нужному столбцу и выберите «проверочные ограничения». Появится окно создания проверочных ограничений, в нем надо нажать кнопку «Добавить» и появится окно свойств ограничения (Рисунок 5). Основные поля:
Ø Выражение - собственно ограничение.
Ø Имя - название ограничения.
Ø Описание – комментарии для ограничения.
Ø Проверка существующей информации на соответствие ограничению.
Ø Применять для вставки или обновления данных.
Создайте ограничение для оценки в таблице успеваемость, задав выражение «[Оценка] in ('3', '4', '5', 'зачет')» и оставив остальные параметры по умолчанию, как на рисунке 5.
Рисунок 16.
После чего закройте окно и ограничение вступит в силу – значения в столбце оценка могут быть только 3,4,5 или зачет. При прочих значениях, SQL Server не будет сохранять значение и будет выдавать ошибку.
Создание диаграммы БД
Для создания диаграммы базы данных нужно выбрать соответствующий раздел в базе данных и, щелкнув по нему правой кнопкой мыши, выбрать «Создать диаграмму базы данных». Нужно добавить все таблицы, после чего перетаскивать столбец одной таблицы на другую таблицу для создания отношений. В результате появится окно, в котором мы выбираем имя связи, таблицу первичного ключа (от которого зависит) и столбцы в таблицах первичного и внешнего ключа (который зависит). Пример показан на рисунке 6.
Рисунок 17.
Далее появляется окно свойств связи (Рисунок 7).
Рисунок 18.
Нам потребуются следующие свойства:
Проверка существующих данных при создании и возобновлении- проверяет все существующие данные в таблице перед созданием или возобновлением ограничения относительно этого ограничения.
Использование ограничения внешнего ключа - укажите, допустимы ли изменения данных столбцов связи, если при этом нарушится целостность связи внешнего ключа. Пункт «Да», если нужно запретить такие изменения, и «Нет», если нужно разрешить их.
Категория спецификаций INSERT и UPDATE- разверните, чтобы увидеть сведения о «Правиле удаления» и «Правиле обновления связи».
Ø Правило удаления - укажите, что произойдет при попытке пользователя удалить строку с данными, участвующую в связи внешнего ключа:
ü Нет действий. Сообщение об ошибке информирует пользователя, что удаление недопустимо, и удаление не происходит.
ü Каскад. Удаляет все строки, содержащие данные, участвующие в связи ключа.
ü Присвоить NULL. Задает значение, равное NULL, если все столбцы внешних ключей в таблице могут содержать значения NULL.
ü Присвоить значение по умолчанию. Задает значение по умолчанию, определенное для данного столбца, если все столбцы внешних ключей в таблице имеют значения по умолчанию.
Ø Правило обновления - укажите, что произойдет при попытке пользователя обновить строку с данными, участвующую в связи внешнего ключа.
ü Нет действий. Сообщение об ошибке информирует пользователя, что обновление недопустимо, и обновление не выполняется.
ü Каскад. Обновляет все строки, содержащие данные, участвующие в связи внешнего ключа.
ü Присвоить NULL.Задает значение, равное NULL, если все столбцы внешних ключей в таблице могут содержать значения NULL.
ü Присвоить значение по умолчанию. Задает значение по умолчанию, определенное для данного столбца, если все столбцы внешних ключей в таблице имеют значения по умолчанию.
Пример заполненных свойств связи представлен на рисунке 8.
Рисунок 19.
Диаграмма БД
Создайте диаграмму своей БД на основе ниже приведенной диаграммы (Рисунок 9).
Рисунок20.
Большинство параметров связей по умолчанию, остальные указаны ниже:
«правило обновления» = п.о.
«правило удаления» = п.у.
Группа – Студент: п.о. каскадно, п.у. каскадно.
Группа – Изучение: п.о. каскадно, п.у. NULL.
Студент – Успеваемость: п.о. каскадно, п.у. каскадно.
Успеваемость – Изучение: Использование ограничения внешнего ключа = нет, п.о. нет
действий, п.у. нет действий.
Преподаватель – Изучение: п.о. каскадно, п.у. присвоить NULL.
Предмет – Изучение: п.о. каскадно, п.у. присвоить NULL.
Преподаватель – Кафедра: п.о. каскадно, п.у. присвоить каскадно.
Благодаря подобной настройке связей в БД соблюдается целостность и удобно с ней работать.
Создание представлений
Представление – это виртуальная таблица, создаваемая запросом, содержащим выборку данных. Эта виртуальная таблица состоит из данных одной или нескольких реальных таблиц, а для пользователей представление выглядит, как реальная таблица. В отличие от обычных таблиц представление не является самостоятельным набором данных, хранящимся в базе. Содержимое представления динамически вычисляется на основании данных, находящихся в реальных таблицах. Изменение данных в реальной таблице БД немедленно отражается в содержимом всех представлений, построенных на этой таблице. Представление хранится просто как определенный код T-SQL.Так как представление - это выборка данных, то при помощи него нельзя изменять\добавлять\удалять данные. Для создания представления нужно в «базы данных», нажать правой кнопкой мыши по «представления» и выбрать «Создать представление». Появится окно выбора таблиц, из которых будут выбираться данные (Рисунок 10). Чтобы добавить нужные таблицы, нужно поочередно выбирать их и нажимать «Добавить». По завершению отобразится окно структуры представления (Рисунок 11).
Рисунок 21.
Рисунок 22.
На схеме автоматически отображены связи. Для добавления столбцов в представление нужно галочками выбрать необходимые из таблиц столбцы в запрос. Также внизу будет отображаться SQL код представления.
Чтобы сохранить созданное представление щелкните правой кнопкой мыши по вкладке представления и выберите «сохранить…» либо откройте «файл» и выберите «сохранить представление» либо закройте представление и SQL Server предложит сохранить представление.
Создайте представление «План занятий» из таблиц «Преподаватель», «Изучение» и «Предмет». Выберите столбцы G_ID, НП, ФИО, Видз, Часы, СбаллГр и ID. Сохраните представление.
Создайте представление «Студенты, сдавшие сессию» - новое представление без таблиц. Введите в окне SQL кода следующий код:
SELECT ФИО, G_ID
FROM dbo.Студент
WHERE (ФИО NOT IN
(SELECT Студент_1.ФИО
FROM dbo.Студент AS Студент_1 INNER JOIN
dbo.Успеваемость AS Успеваемость_1 ON Студент_1.[ID_ ST] = Успеваемость_1.[ID ST]
WHERE (Успеваемость_1.Оценка IS NULL)))
Сохраните представление. После сохранения отобразится окно как на Рисунке 12.
Рисунок 23.
Так же для того, чтобы добавлять\изменять\удалять данные в представлении «План занятий» при помощи триггеров представления, создание которых рассмотрено далее, необходимо указать для параметра представления «Спецификация обновления» значение «Да» (Рисунок 13). Данный параметр можно изменить, если нажать правой кнопкой мыши по представлению, выбрать пункт «Проект» и на панели инструментов в разделе «Вид» выбрать отображение «Окна свойств».
Рисунок 24.
Теперь с представлением можно будет работать как с таблицей. Только на данном этапе будет возникать ошибка или обработка данных будет происходить некорректно. Для того, чтобы исправить и сделать работу с данными корректной надо создать триггеры.
Создание триггеров
Триггер предназначен для автоматического запуска системой SQL Server при модифицировании данных какой-либо таблицы. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных выполняются в 1 транзакции с действием, вызвавшем срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции, т.е. ни действие триггера ни модификация данных будут не выполнены. Кроме того, триггеры могут быть привязаны не к таблице, а к представлению. В этом случае с их помощью реализуется механизм «обновляемого представления» (т.к. представление представляет собой выборку, то при помощи него нельзя изменять\добавлять\удалять данные, триггер это «исправляет»).
Шаблон создания триггера:
CREATE TRIGGER [Название триггера]
AFTER <Событие>
ON <Таблица>
BEGIN
<Выполняемые действия>
END;
Для создания триггера нужно открыть папку нужной таблицы и нажатием правой кнопкой мыши по «триггеры» вызвать меню, в котором выбрать «Создать триггер» (Рисунок 14).
Рисунок 25.
Далее надо ввести код триггера и запустить его - нажать на панели «Выполнить». При удачном выполнении триггера появится сообщение «Выполнение команд успешно завершено» (Рисунок 15).
Рисунок 26.
Создайте триггер для таблицы «Студент», который будет при добавлении студента в таблицу «Студент» добавлять строки в таблицу «Успеваемость» для этого студента с его предметами. При помощи этого триггера, при работе с БД не нужно будет вручную добавлять записи в успеваемость для каждого студента. Код триггера:
CREATE TRIGGER [Вставка в Успеваемость]
ON [dbo].[Студент]
AFTER INSERT
AS
BEGIN
Insert into [dbo].[Успеваемость] (ID, [ID ST])
select t1.ID, t2.[ID_ ST]
from [dbo].[Изучение] t1
cross join
[dbo].[Студент] t2
Left outer join
[dbo].[Успеваемость] t3
ON t1.ID = t3.ID and t2.[ID_ ST] = t3.[ID ST]
Where t1.G_ID = t2.G_ID and t3.ID is null
END
GO
Как он должен выглядеть отображено на рисунке 16.
Рисунок 27.
Создайте аналогичный триггер для таблицы «Изучение», который будет при добавлении записи о изучении в таблицу «Изучение» добавлять строки в таблицу «Успеваемость» для студентов группы, занимающихся по этому предмету. При помощи этого триггера, при работе с БД не нужно будет вручную добавлять записи в успеваемость для каждого студента. Код триггера:
CREATE TRIGGER [Вставка в Успеваемость 2]
ON [dbo].[Изучение]
AFTER INSERT
AS
BEGIN
Insert into [dbo].[Успеваемость] (ID, [ID ST])
select t1.ID, t2.[ID_ ST]
from [dbo].[Изучение] t1
cross join
[dbo].[Студент] t2
Left outer join
[dbo].[Успеваемость] t3
ON t1.ID = t3.ID and t2.[ID_ ST] = t3.[ID ST]
Where t1.G_ID = t2.G_ID and t3.ID is null
END
GO
Создайте триггер для таблицы «Изучение», который будет при удалении записи об изучении из таблицы «Изучение» удалять строки из таблицы «Успеваемость» для студентов группы, занимающихся по этому предмету. При помощи этого триггера будет поддерживаться целостность БД. Код триггера:
CREATE TRIGGER [Удаление из Успеваемости]
ON [dbo].[Изучение]
AFTER DELETE
AS
BEGIN
Delete [dbo].[Успеваемость]
where ID = (select ID from deleted)
END
GO
Так же востребованы триггеры для работы с представлением «План занятий». Соответственно далее представлены 3 триггера, заменяющие действия удаления, добавления, обновления.
CREATE TRIGGER [dbo].[Удаление из Изучения]
ON [dbo].[План занятий]
Instead of DELETE
AS
set nocount on
BEGIN
delete from [dbo].[Изучение] where ID = (select Id from deleted)
END
CREATE TRIGGER [dbo].[Обновление в Изучении]
ON [dbo].[План занятий]
Instead of UPDATE
AS
set nocount on
BEGIN
UPdate [dbo].[Изучение]
set [G_ID] = t1.[G_ID], [КП] = t2.[КП], [Табн] = t3.[Табн],
[Видз] = t1.[Видз], [Часы]=t1.[Часы], [СбаллГр]=t1.[СбаллГр]
From ([inserted] t1
left outer join [dbo].[Предмет] t2
ON t1.НП = t2.НП
left outer join [dbo].[Преподаватель] t3
ON t1.ФИО = t3.ФИО)
where [dbo].[Изучение].[ID] in (select t1.[ID] from inserted)
END
CREATE TRIGGER [dbo].[Вставка в Изучение]
ON [dbo].[План занятий]
Instead of INSERT
AS
set nocount on
BEGIN
Insert INTO [dbo].[Изучение]
(G_ID, КП, Табн, Видз, Часы, СбаллГр)
Select
t1.G_ID, t2.КП, t3.Табн, t1.Видз, t1.Часы, t1.СбаллГр
From [inserted] t1
left outer join [dbo].[Предмет] t2
ON t1.НП = t2.НП
left outer join [dbo].[Преподаватель] t3
ON t1.ФИО = t3.ФИО
END
Работа с данными БД
Для изменения содержимого таблицы откройте в БД «Таблицы» и щелкните правой кнопкой мыши по таблице и выберите «Изменить первые 200 строк», откроется окно с содержимым таблицы. Соответственно выбрав нужное поле, можно удалить, заменить значение поля или, выбрав пустую строчку и введя значение, добавить строчку в таблицу. Если значение в поле отсутствует, то в поле отображается «NULL» («NULL» не равно 0, т.к. 0 – это значение).
Работа с представлением «План занятий» осуществляется аналогично. Представление «Студенты, сдавшие сессию» доступно только для просмотра данных (т.к. необходимые триггеры отсутствуют).