针对试验 1 给定的教学管理数据库中的 8 个表, 完成如下视图，存储过程，触发器，索引，用户，角色，创建，以及权限的管理。

由于没有给出具体的需求和要求，以下是一些常见的视图、存储过程、触发器、索引、用户、角色和权限的管理。

视图：

1. 学生信息视图

CREATE VIEW vw_student_info AS
SELECT s.stu_id, s.stu_name, s.stu_gender, s.stu_birthdate, c.class_name, t.teacher_name
FROM tb_student s
LEFT JOIN tb_class c ON s.class_id = c.class_id
LEFT JOIN tb_teacher t ON c.teacher_id = t.teacher_id;

2. 成绩信息视图

CREATE VIEW vw_score_info AS
SELECT s.stu_id, s.stu_name, c.class_name, sc.course_name, sc.score
FROM tb_student s
JOIN tb_class c ON s.class_id = c.class_id
JOIN tb_score sc ON s.stu_id = sc.stu_id;

存储过程：

1. 添加班级存储过程

CREATE PROCEDURE sp_add_class
@teacher_id INT,
@class_name VARCHAR(50)
AS
BEGIN
INSERT INTO tb_class (teacher_id, class_name)
VALUES (@teacher_id, @class_name)
END

2. 更新学生信息存储过程

CREATE PROCEDURE sp_update_student_info
@stu_id INT,
@stu_name VARCHAR(50),
@stu_gender VARCHAR(10),
@stu_birthdate DATE,
@class_id INT
AS
BEGIN
UPDATE tb_student
SET stu_name = @stu_name, stu_gender = @stu_gender, stu_birthdate = @stu_birthdate, class_id = @class_id
WHERE stu_id = @stu_id
END

触发器：

1. 添加学生触发器

CREATE TRIGGER tr_add_student
ON tb_student
AFTER INSERT
AS
BEGIN
DECLARE @class_id INT
SELECT @class_id = class_id FROM inserted
UPDATE tb_class SET student_num = student_num + 1 WHERE class_id = @class_id
END

2. 删除学生触发器

CREATE TRIGGER tr_delete_student
ON tb_student
AFTER DELETE
AS
BEGIN
DECLARE @class_id INT
SELECT @class_id = class_id FROM deleted
UPDATE tb_class SET student_num = student_num - 1 WHERE class_id = @class_id
END

索引：

1. 学生表 stu_id 索引

CREATE INDEX idx_stu_id ON tb_student (stu_id);

2. 成绩表 stu_id 索引

CREATE INDEX idx_score_stu_id ON tb_score (stu_id);

用户和角色：

1. 创建用户和角色

CREATE LOGIN test_login WITH PASSWORD = 'test123'
CREATE USER test_user FOR LOGIN test_login
CREATE ROLE test_role
GRANT SELECT, INSERT, UPDATE, DELETE ON tb_student TO test_role
GRANT SELECT, INSERT, UPDATE, DELETE ON tb_class TO test_role
GRANT SELECT, INSERT, UPDATE, DELETE ON tb_score TO test_role
EXEC sp_addrolemember 'test_role', 'test_user'

2. 删除用户和角色

USE master
DROP LOGIN test_login
USE test_db
DROP USER test_user
DROP ROLE test_role

权限管理：

1. 授权给角色

GRANT SELECT, INSERT, UPDATE, DELETE ON tb_student TO test_role
GRANT SELECT, INSERT, UPDATE, DELETE ON tb_class TO test_role
GRANT SELECT, INSERT, UPDATE, DELETE ON tb_score TO test_role

2. 撤销角色的权限

REVOKE SELECT, INSERT, UPDATE, DELETE ON tb_score FROM test_role