LAB19 (Задания по бд), страница 5

2015-08-16СтудИзба

Описание файла

Файл "LAB19" внутри архива находится в папке "documentation к лаб.раб. и курсу БД". Документ из архива "Задания по бд", который расположен в категории "". Всё это находится в предмете "вычислительные машины, системы и сети (вмсис)" из 7 семестр, которые можно найти в файловом архиве НИУ «МЭИ» . Не смотря на прямую связь этого архива с НИУ «МЭИ» , его также можно найти и в других разделах. Архив можно найти в разделе "остальное", в предмете "вмсс" в общих файлах.

Онлайн просмотр документа "LAB19"

Текст 5 страницы из документа "LAB19"

gok char(2) check (gok between "00" and "99"),

constraint kat_obuch_const check

((kat_obuch_k in ("01", "02") and

(convert (integer, gok) - convert (integer, gp))=1) or

(kat_obuch_k in ("03", "04") and

(convert (integer, gok) - convert (integer, gp)) in (5, 6)) or

(kat_obuch_k in ("05", "17") and

(convert (integer, gok) - convert (integer, gp)) in (4, 5)) or

(kat_obuch_k="07" and (convert (integer, gok) -

convert (integer, gp))=3) or

(kat_obuch_k="08" and (convert(integer, gok) -

convert (integer, gp))=4) or

(kat_obuch_k in("10", "11") and

(convert (integer, gok) - convert (integer, gp)) in (1, 2)))) [Ctrl]+[Enter]

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

3) Создание правила

Если контрольное ограничение на уровне поля не было создано в команде create table или созданное контрольное ограничение не совсем удовлетворяет владельца БД, то это ограничение можно создать с помощью команды create rule.

Например, контрольное ограничение для поля kat_obuch_k было задано с помощью функции between ‘01’ and ‘17’ в команде create table poss1, а в текущем сеансе работы владельцу БД понадобилось уточнить конкретные значения этого поля, например задать следующий список: ‘01’, ’02’, ’03’, ’04’, ’05’, ‘07’,’08’,’10’,’11’,’17’. Это делается с помощью следующей команды:

create rule rule1 as @kat_obuch in (“01”, “02”, “03”, “04”, “05”, “07”, “08”, “10”, “11”, “17”) [Ctrl]+[Enter]

После создания правила rule1 (загрузки его в БД как объекта) необходимо связать его с таблицей БД poss1 с помощью процедуры sp_bindrule (см. конспект курса): sp_bindrule rule1, “poss1.kat_obuch_k”

Проверка действия правила осуществляется оператором insert (или update), т.е. при вводе записей в таблицу.

Задание:

  1. Создать контрольные ограничения на уровне поля, описанные выше, для вашей информационной таблицы в вашей БД;

  2. Создать следующие контрольные ограничения на уровне таблицы:

А) значение поля “Год рождения” меньше значения поля “Год приема”, которое, в свою очередь, меньше значения поля “Год окончания”;

Б) если значение поля “Код категории обучения” равно ‘07’, ‘08’ или ‘09’ (аспирант, аспирант-заочник или ординатура), то первые два символа в значении поля “Специальность” больше ‘00’; в остальных случаях (по всем другим значениям поля “Код категории обучения”) первые два символа значения поля “Специальность” равны ‘00’;

В) если значение поля “Пол” равно “Ж”, то фамилия в поле “Фамилия Имя Отчество” должна заканчиваться на букву “А”, иначе, если значение поля “Пол” равно “М”, то фамилия в поле “Фамилия Имя Отчество” не должна заканчиваться на букву “А”;

  1. Создать правило для вашей информационной таблицы, описанное выше.

ЛАБОРАТОРНАЯ РАБОТА № 8

Цель:

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

  1. Научиться использовать хранимые процедуры для выполнения двумерных и трехмерных статистических запросов.

Справочный материал:

  1. Конспект курса.

Используемые технические средства:

1. SQL-сервер, работающий на компьютере Pentium 120/32/2,1 под управлением операционной системы Windows NT.

2. Open-Client, работающий на компьютере PC 486/DX4/100/8/1,08/SVGA1MPCI под управлением операционной системы Windows95.

Упражнения

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему SYBASE for WINDOWS95 и диалоговый SQL, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

Вариант двумерного статистического запроса к БД

Выдать статистику по всем учебным заведениям и категориям обучения учащихся России, принятых на учебу в 1990 году.

В этом запросе требуется, во-первых: выдать общее количество учащихся, принятых на учебу в 1990 году; во-вторых: распределить это количество по разным учебным заведениям, т.е. для каждого учебного заведения определить количественные характеристики; в-третьих: каждую количественную характеристику каждого учебного заведения распределить по разным категориям обучения, т.е. определить количественные характеристики каждой категории обучения в каждом учебном заведении.

Другими словами, если использовать только SELECT - предложения, то необходимо выполнить три запроса (и получить три разных отчета):

1) запрос получения общего количества учащихся, принятых в 1990 году;

2) запрос с условием выборки по году приема (значение '90'), с предложением group by для группирования значений по полю "учебное заведение" и с агрегатной функцией count(*) в списке выборки для оценки количества значений учебных заведений в каждой группе;

3) запрос с условием выборки по году приема (значение '90'), с предложением group by для группирования значений по двум полям: "учебное заведение" и "категория обучения" и с агрегатной функцией count(*) в списке выборки для оценки количества значений категорий обучения в каждой группе.

Замечание. То, что функция count(*) подсчитывает количество значений в группах для последнего указанного в списке выборки поля, является ограничением языка SQL, поэтому для подсчета статистики по двум полям (атрибутам) требуется выполнять два отдельных запроса.

Перечисленные выше запросы выглядят следующим образом:

1) select count(*) from poss where gp='90'

2) select vuz.uch_zavedenie,

count(*)

from poss, vuz

where gp='90' and vuz_k=vuz.cod

group by vuz.uch_zavedenie

3) select vuz.uch_zavedenie, kat_obuch.kat_obucheniya,

count(*)

from poss, vuz, kat_obuch

where gp='90' and vuz_k=vuz.cod

and kat_obuch_k=kat_obuch.cod

group by vuz.uch_zavedenie, kat_obuch.kat_obucheniya

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

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

Для объединения всех количественных характеристик значений атрибутов и общего итога по запросу в один отчет необходимо в теле хранимой процедуры использовать курсор (см. конспект курса). В качестве предложения select для курсора нужно взять запрос номер 3), в котором определяются количества значений по последнему в списке выборки атрибуту (в данном запросе "категория обучения"). Суммируя количественные характеристики значений категорий обучения можно получить количественные характеристики соответствующих значений учебных заведений ("учебное заведение" - первый атрибут в списке выборки), а также общий итог по запросу (т.е. по приему 1990 года).

Для создания процедуры в верхней области окна необходимо ввести текст процедуры (команда create procedure):

create procedure zapr8 as

declare @yz char (25) /* переменная для названия учебного заведения */

declare @ko char (16) /* переменная для названия категории обучения */

declare @it1 int /* переменная для итога по полю категория обучения */

declare @it2 int /* переменная для итога по полю учебное заведение */

declare @itall int /* переменная для итога по всему запросу */

declare @yz1 char (25) /* переменная для названия учебного заведения */

/* объявление курсора для двумерного статистического запроса */

declare y cursor for

select vuz.uch_zavedenie, kat_obucheniya,

count (*)

from poss, vuz, kat_obuch

where gp='90' and vuz_k=vuz.cod

and kat_obuch_k=kat_obuch.cod

group by vuz.uch_zavedenie, kat_obuch.kat_obucheniya

select @it2=0 /* начальное значение итога по полю учебное заведение = 0 */

select @itall=0 /* начальное значение итога по запросу = 0 */

open y /* открытие курсора */

fetch y into @yz, @ko, @it1 /* считывание первого итога по полю категория обучения */

if (@@sqlstatus=1) begin

select "Ошибка при выполнении первого FETCH"

close y /* закрытие курсора и останов процедуры в случае ошибки */

return

end

if (@@sqlstatus=2) begin

select "Данные не найдены"

close y /* закрытие курсора и останов процедуры в случае отсутствия данных */

return /* по запросу */

end

select @yz1=@yz /* запоминание названия учебного заведения в @yz1 */

select @ko," - ", @it1 /* печать названия категории обучения и итога */

select @it2=@it2+@it1 /* подсчет итога по полю учебное заведение */

select @itall=@itall+@it1 /* подсчет общего итога по запросу */

/* цикл обработки запроса */

while (@@sqlstatus=0)

begin

fetch y into @yz, @ko, @it1 /* считывание очередного итога по полю категория обучения */

if (@yz1 !=@yz) begin /* если название учебного заведения поменялось, то печать */

select "учебное заведение", @yz1, " - ", @it2 /* старого названия учебного заведения и итога */

select @yz1=@yz /* присвоение нового названия учебного заведения */

select @it2=0 /* новый итог = 0 */

end

if (@@sqlstatus=2) break /* при окончании данных немедленный выход из цикла */

select @ko, " - ", @it1 /* печать очередных названия категории обучения и итога */

select @it2=@it2+@it1 /* подсчет итога по полю учебное заведение */

select @itall=@itall+@it1 /* подсчет общего итога по запросу */

end

close y /* закрытие курсора по окончании цикла */

if (@@sqlstatus=1) begin

select "Ошибка при выполнении FETCH"

return /* останов процедуры в случае ошибки */

end

select "учебное заведение ", @yz, “ - ", @it2 /* печать последнего итога по учебному заведению */

select "всего по запросу - ", @itall /* печать общего итога по запросу */

deallocate cursor y /* освобождение курсора */

return

[Ctrl]+[Enter]

Для выполнения запроса нужно запустить эту процедуру, т.е. в верхней области окна необходимо ввести:

zapr8 [Ctrl]+[Enter]

Отчет будет представлен в нижней области окна.

<окно>

Вариант трехмерного статистического запроса к БД

Выдать статистику по всем учебным заведениям, специальностям и категориям обучения учащихся России

Трехмерный статистический запрос выполняется аналогично двумерному, за исключением того, что в этом запросе и в списке выборки, и в предложении group by добавляется еще одно поле (атрибут "специальность"). Другими словами, в этом запросе требуется, во-первых: выдать общее количество учащихся в России; во-вторых: распределить это количество по разным учебным заведениям, т.е. определить количественные характеристики для каждого учебного заведения; в-третьих: каждую количественную характеристику каждого учебного заведения распределить по разным шифрам специальностей, т.е. определить количественные характеристики каждой специальности в каждом учебном заведении; в-четвертых: каждую количественную характеристику каждого шифра специальности (в каждом учебном заведении) распределить по разным категориям обучения, т.е. определить количественные характеристики каждой категории обучения для каждого шифра специальности (в каждом учебном заведении).

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

В качестве предложения select для курсора берется следующее предложение:

select vuz.uch_zavedenie, spec, kat_obuch.kat_obucheniya,

count(*)

from poss, vuz, kat_obuch

where vuz_k=vuz.cod and kat_obuch_k=kat_obuch.cod

group by vuz. uch_zavedenie, spec, kat_obuch. kat_obucheniya

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

Для создания процедуры в верхней области окна необходимо ввести текст процедуры (команда create procedure):

Свежие статьи
Популярно сейчас
Зачем заказывать выполнение своего задания, если оно уже было выполнено много много раз? Его можно просто купить или даже скачать бесплатно на СтудИзбе. Найдите нужный учебный материал у нас!
Ответы на популярные вопросы
Да! Наши авторы собирают и выкладывают те работы, которые сдаются в Вашем учебном заведении ежегодно и уже проверены преподавателями.
Да! У нас любой человек может выложить любую учебную работу и зарабатывать на её продажах! Но каждый учебный материал публикуется только после тщательной проверки администрацией.
Вернём деньги! А если быть более точными, то автору даётся немного времени на исправление, а если не исправит или выйдет время, то вернём деньги в полном объёме!
Да! На равне с готовыми студенческими работами у нас продаются услуги. Цены на услуги видны сразу, то есть Вам нужно только указать параметры и сразу можно оплачивать.
Отзывы студентов
Ставлю 10/10
Все нравится, очень удобный сайт, помогает в учебе. Кроме этого, можно заработать самому, выставляя готовые учебные материалы на продажу здесь. Рейтинги и отзывы на преподавателей очень помогают сориентироваться в начале нового семестра. Спасибо за такую функцию. Ставлю максимальную оценку.
Лучшая платформа для успешной сдачи сессии
Познакомился со СтудИзбой благодаря своему другу, очень нравится интерфейс, количество доступных файлов, цена, в общем, все прекрасно. Даже сам продаю какие-то свои работы.
Студизба ван лав ❤
Очень офигенный сайт для студентов. Много полезных учебных материалов. Пользуюсь студизбой с октября 2021 года. Серьёзных нареканий нет. Хотелось бы, что бы ввели подписочную модель и сделали материалы дешевле 300 рублей в рамках подписки бесплатными.
Отличный сайт
Лично меня всё устраивает - и покупка, и продажа; и цены, и возможность предпросмотра куска файла, и обилие бесплатных файлов (в подборках по авторам, читай, ВУЗам и факультетам). Есть определённые баги, но всё решаемо, да и администраторы реагируют в течение суток.
Маленький отзыв о большом помощнике!
Студизба спасает в те моменты, когда сроки горят, а работ накопилось достаточно. Довольно удобный сайт с простой навигацией и огромным количеством материалов.
Студ. Изба как крупнейший сборник работ для студентов
Тут дофига бывает всего полезного. Печально, что бывают предметы по которым даже одного бесплатного решения нет, но это скорее вопрос к студентам. В остальном всё здорово.
Спасательный островок
Если уже не успеваешь разобраться или застрял на каком-то задание поможет тебе быстро и недорого решить твою проблему.
Всё и так отлично
Всё очень удобно. Особенно круто, что есть система бонусов и можно выводить остатки денег. Очень много качественных бесплатных файлов.
Отзыв о системе "Студизба"
Отличная платформа для распространения работ, востребованных студентами. Хорошо налаженная и качественная работа сайта, огромная база заданий и аудитория.
Отличный помощник
Отличный сайт с кучей полезных файлов, позволяющий найти много методичек / учебников / отзывов о вузах и преподователях.
Отлично помогает студентам в любой момент для решения трудных и незамедлительных задач
Хотелось бы больше конкретной информации о преподавателях. А так в принципе хороший сайт, всегда им пользуюсь и ни разу не было желания прекратить. Хороший сайт для помощи студентам, удобный и приятный интерфейс. Из недостатков можно выделить только отсутствия небольшого количества файлов.
Спасибо за шикарный сайт
Великолепный сайт на котором студент за не большие деньги может найти помощь с дз, проектами курсовыми, лабораторными, а также узнать отзывы на преподавателей и бесплатно скачать пособия.
Популярные преподаватели
Добавляйте материалы
и зарабатывайте!
Продажи идут автоматически
5288
Авторов
на СтудИзбе
417
Средний доход
с одного платного файла
Обучение Подробнее