17231

Организация баз данных. Основы построения представлений и триггеров в СУБД SQL Server

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

Информатика, кибернетика и программирование

Лабораторная работа № 1. Тема: Организация баз данных. Основы построения представлений и триггеров в СУБД SQL Server. Цель работы: Изучение логической архитектуры сервера и клиента СУБД SQL Server возможностей создания модификация и выполнение запросов построение и испо...

Русский

2013-06-30

744 KB

6 чел.

Лабораторная работа № 1.

Тема: Организация баз данных. Основы построения представлений и триггеров в СУБД SQL Server.

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

Структура лабораторной работы

  1.  Рекомендации по выполнению работы
    1.  Работа с клиентом СУБД SQL Server
    2.  Создание БД
    3.  Создание и выполнение запросов
    4.  Создание и модификация представлений.
    5.  Создание и модификация триггеров.
  2.  Задание для самостоятельной работы.
    1.  Создание таблиц БД.
    2.  Создание и реализация запросов.
    3.  Создание и реализация представления.
    4.  Создание и реализация триггеров.
  3.  Оформление отчета.
  4.  Контрольные вопросы
  5.  Список рекомендуемой литературы.

Методические рекомендации по выполнению работы

(1)

Запуск клиентской части

В списке программ выберите Microsoft SQL Server 2005 и далее утилиту SQL Server Management Studio. В открывшемся основном окне утилиты имеются две рабочие панели. В левой панели находится дерево Проводника сервера (Object Explorer). Для подключения к серверу баз данных в Object Explorer необходимо выбрать ConnectDatabase Engine Далее необходимо выбрать название сервера (в лаборатории 229 – ALLA”). Для продолжения – выбрать тип соединения Windows Authentication (ввод учетных данных не требуется).

Соединение клиентской части с сервером

Щелкните по символу “+” возле пиктограммы сервера в дереве Проводника. При успешном соединении раскроется следующий уровень дерева, в котором видны доступные папки (в том числе Databases). Откройте папку Databases, в дереве появятся доступные системные базы данных (master, model, msdb, tempdb), а также, возможно, базы данных, ранее созданные под управлением данного сервера.

(2)

Создание БД

Щелкнув п/к по пункту Databases в дереве Проводника, выберите в появившемся меню пункт New Database. В появившемся окне Database Properties задайте имя БД. Обратите внимание на параметры файла (оставить предлагаемые установки). Закройте окно, подтвердив введенные параметры.

В списке баз данных дерева Проводника появится созданная БД.

Открытие БД

В дереве Проводника раскройте пункт, соответствующий вашей БД: появится список основных типов объектов БД и их пиктограмм. Выберите тип Tables (Таблицы) и щелкните по пиктограмме. В правой рабочей панели появится список таблиц БД. Первоначально в списке находятся только системные таблицы (Type - System).

Открытие таблицы

Щелкнув п/к в списке таблиц по нужной таблице, выберите во всплывающем меню команду Open Table (Открыть таблицу). Появится окно с данными таблицы, в котором можно производить изменение данных.

(Системные таблицы не корректировать!)

(3)

Создание таблицы (описание полей таблицы)

Щелкнув п/к по пункту Tables в дереве Проводника или в списке таблиц по любой таблице, выберите во всплывающем меню команду New Table (Новая таблица).В появившемся всплывающем окне задайте имя таблицы и, далее, в окне New Table осуществите описание ее полей в соответствии с предлагаемым шаблоном. При необходимости снимите флажок разрешения неопределенных значений (колонка Allow Nulls шаблона).

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

Установка первичного ключа

Нажатием на крайнюю левую ячейку шаблона описания полей таблицы в окне New Table (поле серого цвета) выделите нужное поле (при необходимости выделения нескольких полей одновременно удерживайте нажатой клавишу Ctrl). Далее снимите флажок разрешающий пустое значение в поле (Allow Nulls) и нажмите на пиктограмму Set Primary Key (Установить первичный ключ) : в соответствующих строках шаблона (в ячейках выделения строк) появится изображение ключа.

Установка индексов и ключей

В окне New Table нажмите пиктограмму Manage Indexes and Keys (Управление индексами и ключами ). Для создания нового ключа (индекса) нажмите кнопку Add, выберите необходимые поля из раскрывающегося списка полей, задайте свойства создаваемого объекта, установив флажки в нужное положение. Для модификации существующего ключа (индекса) вначале выберите его по имени из раскрывающегося списка.

Установка внешнего ключа

Там же нажмите пиктограмму Relationships (Связи ). Для создания новой связи нажмите кнопку Add; справа от параметра Tables and Columns Specification нажмите “…” для перехода к шаблону описания связи (он вначале пуст); задайте имя ссылочной таблицы и поля ее первичного ключа (колонка Primary key table шаблона) и соответствующие имена полей внешний ключа (колонка Foreign Key Table). (Каждая строка шаблона содержит пару соответственных полей таблиц. Выбор имени поля можно осуществить из списка, выпадающего при нажатии на указатель в ячейке заполняемой строки). Для определения свойств связи установите флажки, имеющиеся в окне, в нужное состояние.

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

Примечание. Установку внешнего ключа можно осуществить, построив соответствующую диаграмму (схему) данных (см. ниже).

Установка проверочных ограничений

Там же нажмите пиктограмму  Check Constraints (контроль ограничений ). Для создания нового условия нажмите кнопку Add, в рабочем поле Expression наберите проверочное условие (логическое выражение над полями таблицы, условия, определяемые для нескольких полей объединяются логической связкой AND). Для модификации существующего ограничения вначале выберите его по имени из раскрывающегося списка.

Модификация структуры таблицы

Модификация созданной таблицы осуществляется из окна Design, аналогичного по своему виду окну New Table. Окно вызывается одноименной командой всплывающего меню, активизированного для соответствующей таблицы в списке таблиц.

(4)

Создание диаграмм

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

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

В появившемся окне Foreign Key Relationship, отображающем создаваемую связь, укажите все соответственные поля ссылочной и зависимой таблиц; задайте характеристики связи, установив флажки в нужное состояние (см. выше пункт «Установка внешнего ключа»). После закрытия окна на диаграмме появится изображение связи.

После определения всех нужных связей сохраните созданную диаграмму, присвоив ей имя во всплывающем окне, появляющемся при выполнении команды сохранения.

Модификация диаграмм и связей

Для модификации имеющейся диаграммы выберите ее пиктограмму, появляющуюся в панели Object Explorer после щелчка по пункту Diagrams в дереве Проводника, после п/к выберите Modify. Появится окно Edit Diagram, аналогичное по своему виду окну New Diagram.

Для добавления таблицы в диаграмму воспользуйтесь кнопкой Add table контекстного меню поля диаграммы (щелчок п/к).

Для удаления таблицы из диаграммы выделите курсором мыши эту таблицу, активизируйте всплывающее меню (щелчок п/к), и используйте команду Remove from Diagram.

Для изменения существующей связи наведите курсор мыши на нее и активизируйте щелчком л/к, далее используйте правую боковую панель Properties.

Для удаления связи из БД активизируйте всплывающее меню щелчком п/к на ней и используйте команду Delete Relationships from Database.

(5)

Создание и выполнение запроса

Нажмите кнопку New Query на панели инструментов. Наберите текст запроса. Откомпилируйте запрос (Ctrl+F5 или пункт Parse на панели инструментов). Рабочая область окна будет разделена на две горизонтальные панели и в нижней панели появится сообщение о результатах компиляции. Если компиляция прошла неудачно, устраните в тексте запроса указанные в сообщении ошибки и повторно проведите компиляцию. При успешном завершении компиляции дайте команду на выполнение запроса (F5 или пункт Execute в меню Query). В нижней панели появится результат выполнения запроса. При необходимости выполните настройку панели на вывод результата в виде таблицы (Ctrl+D или пункт Results in Grid нижней панели инструментов ).

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

(6)

Создание и модификация представлений

В дереве Проводника для своей БД щелкнув п/к по пункту Views, активизируйте всплывающее меню, в котором дайте команду New View В появившейся вкладке имеются 4 горизонтальные рабочие панели: панель диаграммы (Diagram Pane), панель шаблона (Grid Pane), панель запроса (SQL Pane) и панель результатов (Results Pane). Составом отображаемых в окне рабочих панелей можно управлять, используя соответствующие кнопки-пиктограммы на панели инструментов.

Создание представления удобнее всего начинать с заполнения панели диаграммы, для чего следует воспользоваться кнопкой-пиктограммой Add Table (Добавить таблицу). Нажатие на кнопку активизирует одноименное окно, в котором имеются две закладки – Tables (Таблицы) и Views (Представления). Выберите необходимые объекты из списка в рабочей области окна и с помощью кнопки Add добавьте их в диаграмму. Закройте окно Add Table; в панели диаграммы появится схематическое изображение выбранных таблиц и связей, установленных при создании таблиц. В диаграмме отметьте флажками нужные поля таблиц.

Дальнейшую работу проводите в панели шаблона. При необходимости для каждого поля можно задайте альтернативное имя (колонка Alias шаблона), снять флажок вывода в представление (колонка Output), задать условие выборки (колонки Criteria и Or).

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

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

Для отладки запроса воспользуйтесь пиктограммой Execute SQL панели инструментов. Результаты обработки представления выводятся в рабочую панель результатов.

По завершении создания представления сохраните его, задав имя в окне, всплывающем по команде сохранения. В списке представлений (правая рабочая панель основного окна Object Explorer) появится созданное представление.

Для модификации представления выберите его в списке, активизируйте всплывающее меню (нажатие п/к), дайте команду Design. Появится вкладка, аналогичная по своему виду вкладке при выборе пункта New View.

Полный текст созданного Представления на SQL можно просмотреть и отредактировать, выбрав во всплывающим меню (нажатие п/к) пункт Edit.

Работа с данными представления

Чтобы открыть представление, выберите его в списке и выполните команду Open View всплывающего меню. Появится таблица с данными, соответствующими запросу. Работа с данными представления аналогична работе с данными базовой таблицы.

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

(7)

Создание и модификация триггера

В раскрывающемся списке существующей таблицы щелкните п/к на папке Triggers и выберите New Trigger В рабочем поле новой вкладки наберите текст триггера. Упрощенно, синтаксис триггера имеет вид:

CREATE TRIGGER имя-триггера ON имя-таблицы 

FOR INSERT [, UPDATE [, DELETE]]

AS

запрос

(Шаблон текста триггера выводится в рабочее поле окна).

Пример создания триггера.

Создать триггер на ввод и обновление для таблицы ЗАКАЗ, в котором устанавливается ограничение, состоящее в том, что заказы принимаются до 15 числа каждого месяца включительно.

CREATE TRIGGER CheckDate ON ZAKAZ

/* Триггер предназначен для контроля операций вставки и обновления: */

FOR INSERT, UPDATE 

AS

/* Объявляем переменную, которая будет использована для спецификации проверочного условия: */

DECLARE @nday int

/* Устанавливаем значение переменной равным значению дня (day – стандартный параметр встроенной функции DatePart, выделяющей нужную часть даты – число месяца, для выделения месяца используется – month, для года - year) поля date «вводимой» таблицы, т.е. тех строк таблицы, которые будут вставляться либо редактироваться. «Вводимая» таблица имеет ту же структуру, что и основная, и стандартно именуется как inserted. */

SELECT @nday=DatePart(day, i.data_zakaza) FROM ZAKAZ t, inserted i

/* Для идентификации строк «вводимой» таблицы в запросе задается условие равенства ключевых полей основной и «вводимой» таблиц: */

WHERE t.nomer_zakaza=i.nomer_zakaza

/* Ограничение состоит в том, что день вводимой даты не может быть  меньше 15: */

IF @nday<=15

/* При нарушении ограничения осуществляется откат транзакции обновления: */

BEGIN 

ROLLBACK TRANSACTION

/* Для вывода сообщений об ошибке обычно используется инструкция RAISERROR, параметрами которой являются (в порядке следования): текст сообщения, уровень опасности (обычно устанавливается в пределах от 11 до 16), состояние информации (в пределах от 10 до 20) */

RAISERROR ('Дата меньше требуемой',16,20)

END

После ввода текста триггера осуществите проверку синтаксиса (кнопка Parse), сохраните триггер (Ctrl+S или кнопка Save на панели инструментов).

Для модификации существующего триггера выберите его из раскрывающегося списка Triggers и в контекстном меню выберите Modify.

Задания на лабораторную работу

(I)

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

Учесть тот факт, что все БД хранятся на одном сервере и, следовательно, их имена не должны повторяться.

В целях корректной работы и выполнения полного объема заданий всех лабораторных работ не изменяйте содержимое других БД.

(II)

Создать и заполнить таблицы ИЗДЕЛИЕ, ЗАКАЗ, ПОСТАВКА.

ИЗДЕЛИЕ (Код_ модели, Наименование, Тех_характеристики, Цена)

ЗАКАЗ (Номер_заказа, Заказчик, Адрес(город), Дата_заказа)

ПОСТАВКА (Код_модели, Номер_заказа, Количество),

Типы и свойства полей

ИЗДЕЛИЕ

Код_модели – int, Primary Key;

Наименование – char(50);

Тех_характеристики – char(30);

Цена – money, Check: Цена < 10000.

ЗАКАЗ

Номер_заказа – int, Primary Key;

Заказчик – char(20);

Адрес(город) - char(15);

Дата_заказа – datetime.

ПОСТАВКА

Код_моделиint, Primary Key, Foreign Key;

Номер_заказаint, Primary Key, Foreign Key;

Количество – int, Check: Количество > 2.

Шаблон заполнение таблиц

ИЗДЕЛИЕ

Код_модели

Наименование

Тех_характеристика

Цена

1045

Эл. двигатель

220 V

300

2516

Труба

300 мм

100

3526

Печь электрическая

220 V

750

3657

Телефон многоканальный

10 каналов

350

ЗАКАЗ

Номер_заказа

Заказчик

Адрес(город)

Дата_заказа

335

ЖЭУ 177

Харьков

12/05/03

468

СМУ 1

Киев

05/10/04

526

ОАО «Турбоатом»

Харьков

26/03/03

872

НПО «Укртрасгаз»

Харьков

23/07/04

325

ООО «РОС»

Кривой рог

06/04/02

222

СМУ 1

Киев

12/05/05

ПОСТАВКА

Код_модели

Номер_заказа

Количество

1045

526

30

2516

468

25

2516

222

10

3526

325

4

3657

872

12

1045

335

3

(III)

Создать диаграмму данных по схеме связей между таблицами

ИЗДЕЛИЕ - ПОСТАВКА (1:M)

ЗАКАЗ - ПОСТАВКА (1:M).

(IV)

Для таблиц ИЗДЕЛИЕ, ЗАКАЗ, ПОСТАВКА реализовать запросы на выборку, используя SQL:

  •  Получить список изделий, поставляемых в Киев или Кривой рог (поля: код модели, заказчик, город);
  •  Получить значения общего количества заказанных изделий каждой модели, если это количество превышает 20 (поля: наименование, количество);
  •  Получить список суммарной стоимости каждого заказа (поля: наименование, сумма заказа (вычисляемое поле));
  •  Получить список заказчиков, не заказавших заданного изделия (код модели задается как константа). Причем эта модель должна существовать среди заказанных изделий в таблице ПОСТАВКА) (поля: номер заказа, заказчик).

(V)

Создать представление.

Представление должно содержать сведения о поставках изделий по каждому из заказов с расшифровкой наименования заказчика, наименования изделия и указанием стоимости всей поставки (Цена * Количество).

(VI)

1) Создать триггер запрещающий удалять информацию о поставках изделий “Эл. двигатель” и “Многоканальный телефон”.

2) Создать триггер, который запрещает производить заказы во втором полугодии текущего года. Если вводится любая дата второго полугодия текущего года, то поменять ее на 30/06/<текущий год>.

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

3) Создать триггер запрещающий изменять количество поставок, если новая стоимость не превышает 5000.

4) Создать триггер, который при изменении Цены контролирует, чтобы разница между новой и старой суммой поставок не превышал 300, в противном случае действие отменяется.

Примечание. Для проверки правильности работы триггера необходимо после удаления строки в режиме просмотра содержимого таблицы (Open Table  Return all rows) выполнить команду Run, нажав кнопку . Если удаленная строка содержащая “запрещенную” для удаления информацию осталась в таблице – триггер работает правильно.

Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все инструкции SQL реализованные в лабораторной работе. Условия заданий записывать не обязательно.
  4.  Записать текст реализации представления, включая оператор CREATE VIEW. Условия задания записывать не обязательно.
  5.  Вывод.

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

  1.  Структура СУБД.
  2.  Функции ядра СУБД.
  3.  В чем состоит отличие запросов на выборку от представлений?
  4.  Виды представлений.
  5.  Основные условия для построения модифицируемых представлений.
  6.  Понятие триггера.
  7.  Задачи, решаемые с помощью триггеров.
  8.  Недостатки использования триггеров.
  9.  Типы триггеров.
  10.  Использование таблиц inserted и deleted.

СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

  1.  Дейт К. Введение в системы баз данных.: Пер. с англ. – К: Диалектика, 1998. – 6-е издание – 848 с.
  2.  Шкарина Л. Язык SQL: учебный курс. – СПб.: Питер, 2001.
  3.  Тихомиров Ю. MS SQL Server в подлиннике. – СПб.: БХВ, 2000.


 

А также другие работы, которые могут Вас заинтересовать

84447. Налоги и сборы, зачисляемые в бюджет Омской области 119 KB
  Среди большого количества различных экономических рычагов с помощью которых происходит воздействие на рыночную экономику государством важное место занимают налоги. Налоги и вся налоговая система –- это мощный инструмент управления экономикой в условиях рынка.
84448. Правопис слів із суфіксом -ар. Закріплення поняття про суфікс та його словотворну функцію 59.5 KB
  Мета уроку: Формувати в учнів уміння утворювати нові слова за допомогою суфіксів орфографічні навички; збагачувати словниковий запас розвивати мовлення вміння аналізувати синтезувати; виховувати шанобливе ставлення до людей різних професій.
84449. Урок розвитку мовлення «Прийшла зима – чарівниця» 44.5 KB
  Мета: вчити учнів самостійно складати розповідь, казку на основі допоміжних слів та даного плану; вчити правильно будувати текст, речення; розвивати спостережливість, образне мислення; усне та писемне мовлення; виховувати любов до природи, прагнення до творчості.
84450. Правильна вимова слів із глухими приголосними. Складання казки за серією малюнків 72.5 KB
  Мета: працювати над засвоєнням правил вимови слів із глухими приголосними; розвивати вміння спостерігати аналізуватибудувати текст за малюнками розвивати мовлення; виховувати повагу до народних традицій. Обладнання: картки із словами та віршами прислів’’я сюжетні малюнки.
84451. Весна прийшла 52 KB
  Мета. Розвивати у дітей мислення, вміння передавати свої думки зв’язним мовленням (усним та писемним), робити узагальнення, висновки, розвивати фантазію. Виховувати любов до природи, бережливе ставлення до неї.
84452. Закріплення й узагальнення знань про прикметник 49.5 KB
  Мета: закріпити і узагальнити знання з теми, збагачувати словниковий запас учнів; розвивати творче мислення; виховувати бережне ставлення до природи. Обладнання: малюнок «Весна», квіти, ребуси, ілюстрації, індивідуальні картки, ігри, телеграма.
84453. Загальне поняття про іменник. Іменники,що означають назви істот та назви неістот 42.5 KB
  Мета: розширити знання про іменник;вдосконалювати вміння виділяти його серед інших частин мови через правильно поставлене запитання; ознайомити з термінами назви істот і назви неістот; розвивати словниковий запас учніввиховувати навички грамотного письма.
84454. Узагальнення вивченого про частини мови 33.5 KB
  Розвивати навички відрізняти частини мови за їх лексичним значенням. Сьогодні на аукціоні незвичайний товар На моєму столі лежать картки з написаними назвами частин мови. Довірена особа від кожної групи розповідає про частину мови іменник.
84455. Складання розповіді за власним спостереженням «Жовте листячко летить, під ногами шелестить» 49.5 KB
  В жовтий лист пофарбувала Урожай з полів зібрала Золотила вербам коси Чарівна цариця Осінь Такце осінь вхід під музику дівчинки-осені Читання вірша: А я ішла по жовтим килимам По золотим червоним і багряним. Я –- осінь. Я –- осінь золота з легким туманом. Таке високе Як не милуватись...