Міністерство освіти і науки України
Національний університет „Львівська політехніка”
Інститут Комп’ютерних Технологій, Автоматики та Метрології
Кафедра КСА
/
Лабораторна робота №4
з дисципліни:
«Бази даних та знань»
Варіант: 4
Мета роботи
Ознайомитися зі структурою БД, написати базу даних без зовнішніх ключів, реалізувавши замість них тригери, функції та процедури.
Завдання
Згідно варіанту завдання реалізувати базу даних без зовнішніх ключів, наповнити таблиці даними, створити вказані збережувані програмні конструкції (збережувані процедури, користувацькі функції, тригери) та перевірити їхню функціо-нальність та правильність їхньої роботи.
БД студентів, що навчаються на кафедрі (рис. 3).
ЗБЕРЕЖУВАНІ ПРОЦЕДУРИ
1. Забезпечити параметризовану вставку нових значень у таблицю Студенти.
2. Вивести дані зі стикувальної таблиці зв’язку М:М між таблицями Студенти та Заборгованості, замінивши числові значення реальними назвами. Як додаткову опцію передбачити можливість фільтрування даних для назв таблиці Студенти, тобто можемо виводити або все, або щось конкретне.
3. Використовуючи курсор, забезпечити динамічне створення 2х таблиць з іменами що містять штамп часу, структура таблиць ідентична структурі таблиці Студенти. Після чого випадковим чином пострічково скопіювати стрічки таблиці Студенти або в одну, або в іншу додаткові таблиці. Повторний запуск процедури знову створює нові аналогічні таблиці, в яких випадковим чином знову будуть розкинуті дані з таблиці Студенти .
КОРИСТУВАЦЬКІ ФУНКЦІЇ
1. Для таблиці Студенти написати функцію як буде шукати AVG стовпця Загальний рейтинг . Потім зробити вибірку даних (SELECT) більших за середнє значення, використовуючи дану функцію.
2. Написати функцію, яка витягує за ключем між таблицями Місто та Область об’єднане значення полів Область та Код області . Потім зробити вибірку усіх даних (SELECT) з таблиці Місто, використовуючи дану функцію.
ТРИГЕРИ
1. Забезпечити цілісність значень для структури БД.
2. для Студенти→Номер студентського квитка забезпечити формат: 'A' + 8 цифр + довільна буква, окрім S ;
3. різниця між Студенти→Дата поступлення та Студенти→Дата народження повинна бути не меншою за 16 років.
4. Забезпечити максимальну кардинальність 6 стрічок для таблиці Групи.
ER-діаграма
/
Таблиці із заповненими даними
/
/
/
/
/
/
/
Код
CREATE PROCEDURE InsertParameter
(
@ID int = 0,
@surname VARCHAR(MAX)='?',
@name VARCHAR (MAX)='?',
@middleName VARCHAR (MAX)='?',
@rating INT=0,
@borthday datetime = '01/01/2000',
@arrival datetime = '01/01/2018',
@number VARCHAR(MAX) = '?',
@mail VARCHAR(MAX)= '?',
@ID_group int = 0,
@ID_institution int = 0,
@ID_city int = 0
)
AS
BEGIN
INSERT INTO Student VALUES (@ID, @surname, @name, @middleName, @rating, @borthday, @arrival, @number, @mail, @ID_group, @ID_institution, @ID_city)
END
GO
CREATE PROCEDURE ArrearsNew
(
@where VARCHAR(MAX) = 'All',
@what VARCHAR(MAX) = ''
)
AS
BEGIN
IF (@where='All') SELECT S.surname, A.name FROM Student S, Arrears A, Present P WHERE P.ID_student=S.ID AND P.ID_arrears=A.ID;
IF (@where='Student') SELECT S.surname, A.name FROM Student S, Arrears A, Present P WHERE P.ID_student=S.ID AND P.ID_arrears=A.ID AND @what=S.surname;
IF (@where='Arrears') SELECT S.surname, A.name FROM Student S, Arrears A, Present P WHERE P.ID_student=S.ID AND P.ID_arrears=A.ID AND @what=A.name;
END
GO
CREATE PROCEDURE TableX2
AS
BEGIN
CREATE TABLE Student1(
ID INT,
surname VARCHAR(MAX),
name VARCHAR (MAX),
middleName VARCHAR (MAX),
rating INT,
borthday datetime,
arrival datetime,
number VARCHAR(MAX),
mail VARCHAR(MAX),
ID_group int,
ID_institution int,
ID_city int
);
CREATE TABLE Student2(
ID INT,
surname VARCHAR(MAX),
name VARCHAR (MAX),
middleName VARCHAR (MAX),
rating INT,
borthday datetime,
arrival datetime,
number VARCHAR(MAX),
mail VARCHAR(MAX),
ID_group int,
ID_institution int,
ID_city int
);
DECLARE @ID1 int,
@surname1 VARCHAR(MAX),
@name1 VARCHAR (MAX),
@middleName1 VARCHAR (MAX),
...
