бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине
 Скачать 0.74 Mb.
|
|
CREATE VIEW PrepodChair2 AS SELECT left(Teach_IMA,1) +'.'+left(Teach_OTCH,1) +'.'+ left(Teach_FAM,15)+'('+left(teach_position,10)+')' AS [Teacher] , chair_name AS [Chair] FROM Teacher, chair WHERE chair.chair_id=teacher.chair_id 3.5 Представление на базе представлений Поскольку представлнения представляют собой виртуальные таблицы, то на их базе также можно создавать другие представления. Допустим, нам требуется создать представление отображающее количество грантников и платников на специальностях. Данное представление, конечно, можно сделать на основе сложных запросов, однако, сделаем его на базе двух представлений Grantn (грантники) и Platn (платники). Сначала необходимо создать и заполить таблицу Speciality (Специальности): CREATE TABLE Speciality ( Spec_ID int not null PRIMARY KEY, Spec_NAME char (20) not null,) Далее необходимо добавить атрибут spec_id в таблицу Students, создать внешний ключ на spec_id в таблице Spec: ALTER TABLE stud ADD spec_id int foreign key references Spec (spec_id) Создадим представление Grantn, отражающее специальность и количество грантов на этой специальности: CREATE VIEW Grantn AS SELECT spec.spec_name, count (distinct stud_id) as 'grant' FROM students, Spec WHERE stud.spec_id=spec.spec_id and Stud_stip is not null GROUP BY spec_name Создадим представление Plat, отражающее специальность и количество платников на этой специальности: CREATE VIEW plat AS SELECT spec.spec_name, count (distinct stud_id) as 'plat' FROM stud, Spec WHERE stud.spec_id=speciality.spec_id and Stud_stip is null GROUP BY spec_name Теперь создадим собствено само представление StudSpecGP, отражающее специальность и количество платников, грантников на базе представлений Grantn и Plat: CREATE VIEW plat AS SELECT p.spec_name AS ' Speciality' , (SELECT [grant] FROM Grantn WHERE grantn.spec_name= g.spec_name) AS 'Gratniki', (SELECT [plat] FROM plat WHERE plat.spec_name= p.spec_name) AS 'Platniki' FROM grantn g, plat p WHERE g.spec_name=p.spec_name GROUP BY g.spec_name, p.spec_name Контрольные вопросы 1. Что такое представление? 2. Как называются таблицы, на основе которых создается представление? 3. Что означают понятия «Вертикальный», «Горизонтальный» фильтры? 4. Что случится, если таблица, на основе которой строится представление, будет удалена? 5. Как представление можно использовать для защиты данных? 6. Содержит ли данные представление? 7. Для достижения каких целей используют представления? Лабораторная работа № 7. Хранимые процедуры, функции и триггеры 1 Цель работы: научить студентов создавать хранимые процедуры, функции и триггеры к базе данных скриптом и в графической среде СУБД MS SQL Server 2008. 2 Задание на лабораторную работу Выполните упражнения, приведенные в п.3.1.-3.3. Создайте процедуры, позволяющие выполнить следующие действия: 1. Отчислить/Зачислить студента. 2. Увеличить суммы стипендий всех студентов на 15%. 3. Ставить студентам оценки за различные виды работ(практика, контрольная, семестровая, курсовой проект, экзамен) по различным предметам. 4. Найти неуспевающих студентов. 5. Объединить две группы в одну. 6. Закрепление преподавателя по предмету за определенными группами, у которых преподаватель ведет предмет (Ввод информации в таблицу Study). 7. В вузы на очную форму обучения принимаются абитуриенты моложе 35 лет. Создайте триггер, позволяющий контролировать возраст студента при выполнении зачисления студента (ввода данных в таблицу Students). 8. Теоретически в БД можно ошибочно внести оценку студенту по предмету, который он не изучает вовсе. Задача: разработать триггер, контролирующий соответствие информации об оценках по предметам (таблицы Progress и Subject) с информацией о предметах изучаемых студентами(таблица Study). 9. Создать триггер, который бы журналировал действия определенного пользователя БД производимые над какой-либо таблицей в определенный промежуток времени. 10. Теоретически в БД можно ошибочно ввести стипендию студенту, который закрыл сессию с удовлетворительными оценками. Задача: разработать триггер, контролирующий оценки полученные студентом и наличие его стипендии. 11. Создать функцию, возвращающую количество студентов в конкретной группе. 12. Создать функцию, возвращающую количество грантников на конкретной специальности. 3 Методические указания к выполнению лабораторной работы 3.1 Использование процедур Команды SQL (CREATE TABLE, INSERT, UPDATE, SELECT) дают возможность сообщить базе данных, что делать, но не как делать. Сообщить ядру базы данных, каким образом следует обрабатывать данные, можно посредством составления процедур. Хранимые процедуры – это набор операторов SQL, созданный для удобства использования в программах. Сохраненную процедуру использовать проще, чем каждый раз записывать весь набор входящих в нее операторов SQL. Сохраненные процедуры можно вкладывать одну в другую (уровень вложенности не может превышать 16). Сохраненная процедура может возвращать значения, выполнять сравнения вводимых пользователем значений с заранее установленными условиями, вычислить какие-либо результаты и т.п. Некоторые из преимуществ использования сохраненных процедур: операторы процедуры уже сохранены в базе данных; операторы процедуры уже проверены и находятся в готовом для использования виде; при использовании процедур результат получается быстрее; возможность сохранения процедур позволяет использовать модульное программирование; сохраненные процедуры могут вызывать другие процедуры; сохраненные процедуры могут вызываться другими программами. В SQL Server процедуры создаются с помощью оператора следующего вида: CREATE PROCEDURE имя_процедуры [ [ ( ] @имя_параметра ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ]) [=DEFAULT][OUTPUT] ] [, @ИМЯ_ПАРАМЕТРА ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ]) [=DEFAULT][OUTPUT] ] [WITH RECOMPILE] AS операторы SQL Сохраненные процедуры используются следующим образом: EXECUTE [ @ = ] имя_процедуры [ [ @ имя_параметра =] значение | [ @ имя_параметра = ] @ переменная [ OUTPUT ] ] [WITH RECOMPILE] Например, мы хотим увеличить на единицу значения номеров курсов (в поле Grup_COURSE) в таблице GRUPPA: CREATE PROCEDURE new_course AS UPDATE GRUPPA SET Grup_COURSE = Grup_COURSE +1 Проверим работу процедуры: EXECnew_course SELECT * FROM GRUPPA Вернем таблицу GRUPPA в первоначальное состояние: CREATE PROCEDURE old_course AS UPDATE GRUPPA SET Grup_COURSE = Grup_COURSE -1 Проверим работу процедуры: EXEC old_course SELECT * FROMGRUPPA Пример процедуры, которую можно использовать для добавления новых групп: CREATE PROCEDURE new_gruppa ( @Grup_ID int , @Grup_NAME char (9), @Grup_KOLSTUD int, @Grup_COURSE int ) AS INSERT INTO Gruppa VALUES (@Grup_NAME, @Grup_KOLSTUD, @Grup_COURSE); Столбец Grup_ID таблицы GRUPPA имеет тип identity, поэтому определяется только при вводе. Работа этой процедуры проверяется следующим образом: EXEC new_gruppa 18, 'ECT-04-5', 25, 1 Обратите внимание, что при вводе данных система автоматически присваивает полю Grup_ID очередное значение независимо от того, какое значение определил пользователь. Простая процедура по использованию оператора SELECT: CREATE PROCEDURE spisok_stud AS SELECT * from Students Процедура для добавления преподавателя: CREATE PROCEDURE new_teacher ( @Teach_ID bigint, @Teach_FAM char (20), @Teach_IMA char (10), @Teach_OTCH char (15), @Teach_POSITION char (18), @Teach_STEPEN char (12), @Chair_ID integer) AS INSERT INTO Teacher VALUES(@Teach_ID,@Teach_FAM,@Teach_IMA, @Teach_OTCH,@Teach_POSITION,@Teach_STEPEN, @Chair_ID) Работа этой процедуры проверяется следующим образом: EXEC new_teacher <список параметров> Процедура для добавления нового предмета: СREATE PROCEDURE new_subject ( @subj_name char(20), @lection_hours int, @practice_hours int, @labor_hours int) AS declare @subj_id int declare @total_hours int IF not exists (select subj_name from subject WHERE subj_name=@subj_name) begin SET @subj_id = (select max(subj_id) from subject)+1 SET @total_hours = @lection_hours + @practice_hours + @labor_hours INSERT INTO subject VALUES (@subj_id, @subj_name, @total_hours, @lection_hours, @practice_hours, @labor_hours) end Работа этой процедуры проверяется следующим образом: EXEC new_subject '3',20,20,30 Данная процедура прежде чем добавить предмет проверяет существование такого предмета в таблице. Т.о., используя условные операторы, можно выполнять те или иные действия по проверке. Процедура добавления нового судента: CREATE PROCEDURE new_stud (@stud_id int, @stud_fam char(20), @stud_ima char(20), @stud_otch char(20), @stud_date datetime, @stud_addr char(20), @grup_name char(20), @grup_kurs int, @stip smallmoney, @spec char(20)) AS declare @grup_id int; declare @spec_id int; declare @kolvo int; INSERT INTO gruppa VALUES(@grup_name,0,@grup_kurs); INSERT INTO spec VALUES(@spec); SELECT @grup_id=(SELECT grup_id FROM gruppa WHERE grup_name=@grup_name); SELECT @spec_id=(SELECT id_spec FROM spec WHERE nazv=@spec); INSERT INTO students VALUES(@stud_id,@stud_fam,@stud_ima,@stud_otch,@stud_date,@stud_addr,@grup_id,@stud_id,@stip,@spec_id); SELECT @kolvo=(select count(stud_id) from students where grup_id=@grup_id); UPDATE gruppa SET grup_kolstud=@kolvo WHERE grup_id=@grup_id Работа этой процедуры проверяется следующим образом: EXEC new_stud 65785,'Иванов','Иван','Иванович', '08/05/89', 'Almaty', 'BVT-77-7',2,1000,'ВЧиПО' 3.2 Функции Для расширения возможностей Transact-SQL по обработке данных в SQL Server реализован ряд встроенных функций. Существующее многообразие встроенных функций SQL Server можно условно разделить на несколько категорий: - математические функции; - строковые функции; - функции для работы с датами; - статистические функции; - криптографические функции; - ранжирующие функции; - функции приведения типов; - системные функции. Каждая функция имеет ряд свойств, определяющих возможность использования ее в вычисляемых столбцах. Функция представляет собой именованный блок кода, возвращающий по результатам своего выполнения некоторое значение. Возвращаемое значение может быть как величиной, так и набором строк. Функции могут участвовать в выражениях, вызываться в теле хранимых процедур, а также в теле других функций. Помимо встроенных функций, присутствующих в системе по умолчанию, пользователи могут создавать собственные функции. Поскольку их можно включать в любое выражение, пользовательские функции представляют собой удобный и гибкий механизм обработки данных. Для создания функции следует использовать оператор CREATE FUNCTION следующего формата: CREATE FUNCTION имя_функции ([ < @имя_параметра> AS <тип_данных> [=<значение_по_умолчанию>]]) RETURNS <возвращаемый_тип_данных> AS операторы SQL Функция определения возраста студента (например, мы ходим узнать возраст студента по id_stud): CREATE FUNCTION GetStudentAge(@uid int) RETURNS varchar(200) AS BEGIN declare @age datetime SELECT @age=Stud_DATE FROM Students WHERE Stud_ID=@uid SET @age=GETDATE()-@age; RETURN convert(varchar(20),YEAR(@age)-1900)+' лет, '+convert(varchar(20),MONTH(@age)-1)+' месяцев и '+convert(varchar(20),DAY(@age)-1)+' дней' END Данную пользовательскую функцию можно использовать в следующем запросе: SELECT Stud_FAM as [Фамилия], Stud_IMA as [Имя], Stud_DATE as [Дата рождения], dbo.GetStudentAge(Stud_ID) as [Возраст] FROM Students ORDER BY Stud_DATE ASC Т.о., создавая пользовательские функции, можно значительно упростить сложность запросов. |