/
arseyfullin
/
lab7
Обзор
Документация
Войти
/
arseyfullin
/
lab7
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
master
lab7.sql
187 строк
6 KB
arseyfullin
upload files
18 дек 2025, 13:15
18 дек 2025, 13:15
0e9fe8e
Код
Авторство
О чём код?
-- ���������/������� ���� ������ IF DB_ID('RDB') IS NOT NULL USE RDB; ELSE BEGIN CREATE DATABASE RDB; USE RDB; END GO -- ������� ������������ ������� ���� ��� ���� (� ���������� ������� ��-�� ������� ������) IF OBJECT_ID('StudentResearch', 'U') IS NOT NULL DROP TABLE StudentResearch; IF OBJECT_ID('Articles', 'U') IS NOT NULL DROP TABLE Articles; IF OBJECT_ID('Conferences', 'U') IS NOT NULL DROP TABLE Conferences; IF OBJECT_ID('Students', 'U') IS NOT NULL DROP TABLE Students; IF OBJECT_ID('Groups', 'U') IS NOT NULL DROP TABLE Groups; GO -- ������� ������� (���������� ���������) CREATE TABLE Groups ( group_id INT PRIMARY KEY, group_name NVARCHAR(50) NOT NULL, faculty NVARCHAR(100) NOT NULL ); CREATE TABLE Students ( student_id INT PRIMARY KEY, first_name NVARCHAR(50) NOT NULL, last_name NVARCHAR(50) NOT NULL, group_id INT, FOREIGN KEY (group_id) REFERENCES Groups(group_id) ); CREATE TABLE Articles ( article_id INT PRIMARY KEY, title NVARCHAR(200) NOT NULL, topic NVARCHAR(100), publication_date DATE, student_id INT, FOREIGN KEY (student_id) REFERENCES Students(student_id), CONSTRAINT CHK_PublicationDate CHECK (publication_date <= GETDATE()) ); CREATE TABLE Conferences ( conference_id INT PRIMARY KEY, conference_name NVARCHAR(200) NOT NULL, location NVARCHAR(100), conference_date DATE, organizer NVARCHAR(100) ); CREATE TABLE StudentResearch ( research_id INT PRIMARY KEY, student_id INT, conference_id INT, presentation_topic NVARCHAR(200), FOREIGN KEY (student_id) REFERENCES Students(student_id), FOREIGN KEY (conference_id) REFERENCES Conferences(conference_id) ); GO -- �������� ������ (������� ������, ��������� �����) DELETE FROM StudentResearch; DELETE FROM Articles; DELETE FROM Conferences; DELETE FROM Students; DELETE FROM Groups; INSERT INTO Groups VALUES (1,'��-21','��'),(2,'��-22','��'),(3,'��-23','��'); INSERT INTO Students VALUES (1,'����','������',1),(2,'����','��������',2),(3,'������','������',1); INSERT INTO Conferences VALUES (1,'������� �����','������','2024-03-15','���'), (2,'���������','���','2024-05-20','����'), (3,'������� ������','������','2024-04-10','����'); INSERT INTO Articles VALUES (1,'������ ��','��','2024-01-15',1); INSERT INTO StudentResearch VALUES (1,1,1,'�������� ��������'), (2,2,3,'IoT ������������'); -- ���� � ������������ (� ���������� �������������) IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ResearchCoordinator' AND type = 'R') CREATE ROLE ResearchCoordinator; -- ������� ������ ����� ���� ���� REVOKE ALL ON Articles FROM ResearchCoordinator; REVOKE ALL ON Conferences FROM ResearchCoordinator; REVOKE ALL ON Students FROM ResearchCoordinator; REVOKE ALL ON StudentResearch FROM ResearchCoordinator; REVOKE ALL ON Groups FROM ResearchCoordinator; -- ��������� ����� ����� GRANT SELECT, INSERT, UPDATE ON Articles TO ResearchCoordinator; GRANT SELECT, INSERT, UPDATE ON Conferences TO ResearchCoordinator; GRANT SELECT ON Students TO ResearchCoordinator; GRANT SELECT ON StudentResearch TO ResearchCoordinator; GRANT SELECT ON Groups TO ResearchCoordinator; -- ������� ����� ���� �� ���������� IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'research_login') CREATE LOGIN research_login WITH PASSWORD = '123'; -- ������� ������������ ���� �� ���������� IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'research_user' AND type = 'S') BEGIN CREATE USER research_user FOR LOGIN research_login; ALTER ROLE ResearchCoordinator ADD MEMBER research_user; END ELSE BEGIN -- ���� ������������ ��� ����������, ������ ��������� ��� � ���� ALTER ROLE ResearchCoordinator ADD MEMBER research_user; END -- ������������ EXECUTE AS USER = 'research_user'; PRINT '=== ���� 1: ���������� �������� + ������ ==='; BEGIN TRY INSERT INTO Students VALUES (4,'�����','�������',2); PRINT '������ 1.1: ���������� �������� �� ������ ��������!'; END TRY BEGIN CATCH PRINT '����� 1.1: ' + ERROR_MESSAGE(); END CATCH; BEGIN TRY INSERT INTO Articles VALUES (2,'����� �� ��','��','2024-05-10',1); PRINT '����� 1.2: ������ ���������'; END TRY BEGIN CATCH PRINT '������ 1.2: ' + ERROR_MESSAGE(); END CATCH; PRINT '=== ���� 2: ����������� � ������ ==='; SELECT s.first_name, s.last_name, c.conference_name FROM Students s JOIN StudentResearch sr ON s.student_id = sr.student_id JOIN Conferences c ON sr.conference_id = c.conference_id WHERE c.location LIKE '%������%'; PRINT '=== ���� 3: ����� �� ������� ==='; SELECT g.group_name, COUNT(DISTINCT a.article_id) ������, COUNT(DISTINCT sr.research_id) �������� FROM Groups g LEFT JOIN Students s ON g.group_id = s.group_id LEFT JOIN Articles a ON s.student_id = a.student_id AND YEAR(a.publication_date) = 2024 LEFT JOIN StudentResearch sr ON s.student_id = sr.student_id GROUP BY g.group_name; PRINT '=== ���� 4: ����������� ==='; BEGIN TRY INSERT INTO Articles VALUES (3,'�������','�����������','2025-12-31',2); PRINT '������ 4.1: ������ � ������� ����� ���������!'; END TRY BEGIN CATCH PRINT '����� 4.1: ' + ERROR_MESSAGE(); END CATCH; BEGIN TRY DELETE FROM Students WHERE student_id = 1; PRINT '������ 4.2: �������� �������� �� ������ ��������!'; END TRY BEGIN CATCH PRINT '����� 4.2: ' + ERROR_MESSAGE(); END CATCH; BEGIN TRY INSERT INTO Groups VALUES (5,'�����','���'); PRINT '������ 4.3: ���������� ������ �� ������ ��������!'; END TRY BEGIN CATCH PRINT '����� 4.3: ' + ERROR_MESSAGE(); END CATCH; REVERT; PRINT '=== ������������ ��������� ==='; PRINT '=== ��� ����� �������� �������! ===';