Создать базу данных

 -- Часть 1: Создание основной таблицы "Students" 

CREATE TABLE Students ( 

    StudentID INT PRIMARY KEY, 

    TicketNumber INT, 

    FullName VARCHAR(100), 

    GroupName VARCHAR(50), 

    Faculty VARCHAR(50), 

    Department VARCHAR(50), 

    Supervisor VARCHAR(100) 

); 

 

-- Часть 2: Создание дополнительной таблицы "Faculties" 

CREATE TABLE Faculties ( 

    FacultyID INT PRIMARY KEY, 

    FacultyName VARCHAR(50) 

); 

 

-- Часть 2: Создание дополнительной таблицы "Departments" 

CREATE TABLE Departments ( 

    DepartmentID INT PRIMARY KEY, 

    DepartmentName VARCHAR(50), 

    FacultyID INT, 

    FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID) 

); 

 

-- Часть 2: Создание дополнительной таблицы "Supervisors" 

CREATE TABLE Supervisors ( 

    SupervisorID INT PRIMARY KEY, 

    SupervisorName VARCHAR(100), 

    DepartmentID INT, 

    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) 

); 

 

-- Часть 2: Добавление внешних ключей в основную таблицу "Students" 

ALTER TABLE Students 

ADD FOREIGN KEY (Faculty) REFERENCES Faculties(FacultyName); 

 

ALTER TABLE Students 

ADD FOREIGN KEY (Department) REFERENCES Departments(DepartmentName); 

 

ALTER TABLE Students 

ADD FOREIGN KEY (Supervisor) REFERENCES Supervisors(SupervisorName); 

 

-- Создание представления "StudentsView" на основной таблице "Students" 

CREATE VIEW StudentsView AS 

SELECT StudentID, TicketNumber, FullName, GroupName, FacultyName, DepartmentName, SupervisorName 

FROM Students 

JOIN Faculties ON Students.Faculty = Faculties.FacultyName 

JOIN Departments ON Students.Department = Departments.DepartmentName 

JOIN Supervisors ON Students.Supervisor = Supervisors.SupervisorName; 

 

-- Часть 3: Создание таблицы "Logs" для записи удаленных студентов 

CREATE TABLE Logs ( 

    LogID INT PRIMARY KEY, 

    StudentID INT, 

    TicketNumber INT, 

    FullName VARCHAR(100), 

    GroupName VARCHAR(50), 

    Faculty VARCHAR(50), 

    Department VARCHAR(50), 

    Supervisor VARCHAR(100), 

    Action VARCHAR(50), 

    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP 

); 

 

-- Часть 3: Создание триггера для записи удаленных студентов в таблицу "Logs" 

CREATE TRIGGER DeleteStudentTrigger 

AFTER DELETE ON Students 

FOR EACH ROW 

BEGIN 

    INSERT INTO Logs (StudentID, TicketNumber, FullName, GroupName, Faculty, Department, Supervisor, Action) 

    VALUES (OLD.StudentID, OLD.TicketNumber, OLD.FullName, OLD.GroupName, OLD.Faculty, OLD.Department, OLD.Supervisor, 'Deleted'); 

END; 

 Часть 1: 

 

Таблица "students": 

 

| student_id | full_name | group_name | faculty | department | supervisor | 

|------------|-----------|------------|---------|------------|------------| 

| INT        | VARCHAR   | VARCHAR    | VARCHAR | VARCHAR    | VARCHAR    | 

 

student_id - уникальный идентификатор студента (PRIMARY KEY) 

 

Часть 2: 

 

Таблица "groups": 

 

| group_id | group_name | faculty_id | 

|----------|------------|-----------| 

| INT      | VARCHAR    | INT       | 

 

group_id - уникальный идентификатор группы (PRIMARY KEY) 

 

faculty_id - идентификатор факультета (FOREIGN KEY) 

 

Таблица "faculties": 

 

| faculty_id | faculty_name | 

|------------|--------------| 

| INT        | VARCHAR      | 

 

faculty_id - уникальный идентификатор факультета (PRIMARY KEY) 

 

Таблица "departments": 

 

| department_id | department_name | faculty_id | 

|---------------|-----------------|-----------| 

| INT           | VARCHAR         | INT       | 

 

department_id - уникальный идентификатор кафедры (PRIMARY KEY) 

 

faculty_id - идентификатор факультета (FOREIGN KEY) 

 

Часть 3: 

 

Таблица "logs": 

 

| log_id | student_id | action | timestamp | 

|--------|------------|--------|-----------| 

| INT    | INT        | VARCHAR| TIMESTAMP | 

 

log_id - уникальный идентификатор записи (PRIMARY KEY) 

 

student_id - идентификатор студента (FOREIGN KEY) 

 

action - действие (удаление или обновление) 

 

timestamp - время действия 

 

Представление на основной таблице: 

 

CREATE VIEW students_view AS 

SELECT student_id, full_name, group_name, faculty_name, department_name, supervisor 

FROM students 

JOIN groups ON students.group_name = groups.group_name 

JOIN faculties ON students.faculty = faculties.faculty_name 

JOIN departments ON students.department = departments.department_name; 

 

Триггер на удаление: 

 

CREATE TRIGGER delete_student_trigger 

AFTER DELETE ON students 

FOR EACH ROW 

INSERT INTO logs (student_id, action, timestamp) 

VALUES (OLD.student_id, 'delete', NOW()); 

 

Триггер на обновление: 

 

CREATE TRIGGER update_student_trigger 

AFTER UPDATE ON students 

FOR EACH ROW 

INSERT INTO logs (student_id, action, timestamp) 

VALUES (NEW.student_id, 'update', NOW());