Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
написати базу даних без зовнішніх ключів, реалізувавши замість них тригери, функції та процедури
Інформація про навчальний заклад
ВУЗ:
Національний університет Львівська політехніка
Інститут:
Інститут комп’ютерних технологій, автоматики та метрології
Факультет:
СІ
Кафедра:
Кафедра КСА
Інформація про роботу
Рік:
2024
Тип роботи:
Лабораторна робота
Предмет:
Бази даних та знань
Частина тексту файла (без зображень, графіків і формул):
Міністерство освіти і науки України
Національний університет „Львівська політехніка”
Інститут Комп’ютерних Технологій, Автоматики та Метрології
Кафедра КСА
/
Лабораторна робота №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),
@rating1 INT,
@borthday1 datetime,
@arrival1 datetime,
@number1 VARCHAR(MAX),
@mail1 VARCHAR(MAX),
@ID_group1 int,
@ID_institution1 int,
@ID_city1 int,
@i int;
DECLARE @k int;
SELECT @k=COUNT(ID) FROM Student;
DECLARE cur SCROLL CURSOR FOR SELECT * FROM Student;
OPEN cur;
DECLARE @c int;
SET @c = 0;
WHILE @c<@k
BEGIN
SET @i = FLOOR(RAND()*(@k-1+1)+1);
IF (@i NOT IN (SELECT ID FROM Student2))
BEGIN
FETCH ABSOLUTE @i FROM cur INTO @ID1,@surname1,@name1,@middleName1,@rating1,@borthday1,@arrival1,@number1,@mail1,@ID_group1,@ID_institution1,@ID_city1;
INSERT INTO Student1 VALUES(@ID1,@surname1,@name1,@middleName1,@rating1,@borthday1,@arrival1,@number1,@mail1,@ID_group1,@ID_institution1,@ID_city1);
INSERT INTO Student2 VALUES(@ID1,@surname1,@name1,@middleName1,@rating1,@borthday1,@arrival1,@number1,@mail1,@ID_group1,@ID_institution1,@ID_city1);
SET @c = @c + 1;
END
END
SELECT * FROM Student1;
DROP TABLE Student1;
DROP TABLE Student2;
CLOSE cur;
DEALLOCATE cur;
END
GO
CREATE FUNCTION Average ()RETURNS INT
AS
BEGIN
DECLARE @wer INT;
SELECT @wer=AVG(rating) FROM Student;
RETURN @wer;
END;
GO
CREATE FUNCTION FullRegion(@ind int) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @qwe VARCHAR(MAX);
SELECT @qwe = [name]+' '+[code] FROM Region WHERE ID=@ind;
RETURN @qwe;
END;
GO
CREATE TRIGGER NewCity ON City AFTER INSERT AS
DECLARE @ID INT
SELECT @ID=ID_region FROM inserted
IF (@ID NOT IN (SELECT ID FROM Region))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpCity ON City AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE Student SET ID_city=@NewID WHERE ID_city=@OldID
UPDATE Institution SET ID_city=@NewID WHERE ID_city=@OldID
END
DECLARE @IDr INT
IF UPDATE(ID_region)
BEGIN
SELECT @IDr=ID_region FROM inserted
IF (@IDr NOT IN (SELECT ID FROM Region))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER DelCity ON City AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_city FROM Student))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END ELSE
IF (@ID IN (SELECT ID_city FROM Institution))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpRegion ON Region AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE City SET ID_region=@NewID WHERE ID_region=@OldID
END
GO
CREATE TRIGGER DelRegion ON Region AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_region FROM City))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER NewGroup ON [Group] AFTER INSERT AS
DECLARE @COL INT
SELECT @COL=COUNT(*) FROM [Group]
IF (@COL>6)
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpGroup ON [Group] AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE Student SET ID_group=@NewID WHERE ID_group=@OldID
END
GO
CREATE TRIGGER DelGroup ON [Group] AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_group FROM Student))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER NewInstitution ON Institution AFTER INSERT AS
DECLARE @ID INT
SELECT @ID=ID_city FROM inserted
IF (@ID NOT IN (SELECT ID FROM City))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpInstitution ON Institution AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE Student SET ID_institution=@NewID WHERE ID_institution=@OldID
END
DECLARE @IDr INT
IF UPDATE(ID_city)
BEGIN
SELECT @IDr=ID_city FROM inserted
IF (@IDr NOT IN (SELECT ID FROM City))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER DelInstitution ON Institution AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_institution FROM Student))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpArrears ON Arrears AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE Present SET ID_arrears=@NewID WHERE ID_arrears=@OldID
END
GO
CREATE TRIGGER DelArrears ON Arrears AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_arrears FROM Present))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER NewPresent ON Present AFTER INSERT AS
DECLARE @ID1 INT,
@ID2 INT
SELECT @ID1=ID_student FROM inserted
SELECT @ID2=ID_arrears FROM inserted
IF (@ID1 NOT IN (SELECT ID FROM Student))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END ELSE
IF (@ID2 NOT IN (SELECT ID FROM Arrears))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpPresent ON Present AFTER UPDATE AS
DECLARE @IDr INT
IF UPDATE(ID_student)
BEGIN
SELECT @IDr=ID_student FROM inserted
IF (@IDr NOT IN (SELECT ID FROM Student))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
IF UPDATE(ID_arrears)
BEGIN
SELECT @IDr=ID_arrears FROM inserted
IF (@IDr NOT IN (SELECT ID FROM Arrears))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER NewStudent ON Student AFTER INSERT AS
DECLARE @ID1 INT,
@ID2 INT,
@ID3 INT
SELECT @ID1=ID_city FROM inserted
SELECT @ID2=ID_institution FROM inserted
SELECT @ID3=ID_group FROM inserted
IF (@ID1 NOT IN (SELECT ID FROM City))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
IF (@ID2 NOT IN (SELECT ID FROM Institution))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
IF (@ID3 NOT IN (SELECT ID FROM [Group]))
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
DECLARE @num VARCHAR(MAX)
SELECT @num=number FROM Student
IF (@num NOT LIKE '[A][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-RT-Z]')
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
DECLARE @date1 DATETIME,
@date2 DATETIME
SELECT @date1=borthday FROM Student
SELECT @date2=arrival FROM Student
IF (dateadd(year,(16),@date1)>@date2)
BEGIN
PRINT'Error insert'
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER UpStudent ON Student AFTER UPDATE AS
DECLARE @OldID INT,
@NewID INT;
IF UPDATE(ID)
BEGIN
SELECT @OldID=ID FROM deleted
SELECT @NewID=ID FROM inserted
UPDATE Present SET ID_student=@NewID WHERE ID_student=@OldID
END
DECLARE @IDc INT
IF UPDATE(ID_city)
BEGIN
SELECT @IDc=ID_city FROM inserted
IF (@IDc NOT IN (SELECT ID FROM City))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
DECLARE @IDi INT
IF UPDATE(ID_institution)
BEGIN
SELECT @IDi=ID_institution FROM inserted
IF (@IDi NOT IN (SELECT ID FROM Institution))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
DECLARE @IDg INT
IF UPDATE(ID_group)
BEGIN
SELECT @IDg=ID_group FROM inserted
IF (@IDg NOT IN (SELECT ID FROM [Group]))
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
IF UPDATE(number)
BEGIN
DECLARE @num1 VARCHAR(MAX)
SELECT @num1=number FROM Student
IF (@num1 NOT LIKE '[A][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-RT-Z]')
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
IF UPDATE(borthday)
BEGIN
DECLARE @date11 DATETIME,
@date21 DATETIME
SELECT @date11=borthday FROM Student
SELECT @date21=arrival FROM Student
IF (dateadd(year,(16),@date11)>@date21)
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
IF UPDATE(arrival)
BEGIN
DECLARE @date12 DATETIME,
@date22 DATETIME
SELECT @date12=borthday FROM Student
SELECT @date22=arrival FROM Student
IF (dateadd(year,(16),@date12)>@date22)
BEGIN
PRINT'Error update'
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER DelStudent ON Student AFTER DELETE AS
DECLARE @ID INT
SELECT @ID=ID FROM deleted
IF (@ID IN (SELECT ID_student FROM Present))
BEGIN
PRINT'Error delete'
ROLLBACK TRANSACTION
END
GO
Перевірка процедур
/
/
/
/
/
/
/
/
Висновок
Отже, на цій лабораторній роботі я ознайомитися зі структурою БД, написав базу даних без зовнішніх ключів, реалізувавши замість них тригери, функції та процедури.
20.04.2018 21:04-
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора