毕业设计管理系统

步骤：

新建查询

粘贴代码、保存

执行

建库建表代码

SQL server版本

索引和性能优化

-- 为Students表添加索引
CREATE INDEX idx_students_name ON Students (Name);
CREATE INDEX idx_students_major ON Students (Major);

-- 为Instructors表添加索引
CREATE INDEX idx_instructors_name ON Instructors (Name);
CREATE INDEX idx_instructors_department ON Instructors (Department);

-- 为ThesisTopics表添加索引
CREATE INDEX idx_thesistopics_title ON ThesisTopics (Title);
CREATE INDEX idx_thesistopics_instructorid ON ThesisTopics (InstructorID);

-- 为StudentThesis表添加索引
CREATE INDEX idx_studentthesis_studentid ON StudentThesis (StudentID);
CREATE INDEX idx_studentthesis_topicid ON StudentThesis (TopicID);

-- 为ProgressLogs表添加索引
CREATE INDEX idx_progresslogs_studentid ON ProgressLogs (StudentID);
CREATE INDEX idx_progresslogs_topicid ON ProgressLogs (TopicID);

-- 为ThesisReviews表添加索引
CREATE INDEX idx_thesisreviews_studentid ON ThesisReviews (StudentID);

-- 为StudentFeedback表添加索引
CREATE INDEX idx_studentfeedback_studentid ON StudentFeedback (StudentID);
CREATE INDEX idx_studentfeedback_topicid ON StudentFeedback (TopicID);

用户子模式

学生视图

CREATE VIEW StudentView AS
SELECT 
    s.StudentID, 
    s.Name, 
    s.Gender, 
    s.Major, 
    s.Class, 
    s.Phone, 
    st.TopicID, 
    tt.Title AS TopicTitle, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    r.ReviewComments, 
    r.ReviewResult, 
    r.ReviewDate, 
    f.FeedbackContent, 
    f.FeedbackDate
FROM 
    Students s
LEFT JOIN 
    StudentThesis st ON s.StudentID = st.StudentID
LEFT JOIN 
    ThesisTopics tt ON st.TopicID = tt.TopicID
LEFT JOIN 
    ThesisReviews r ON s.StudentID = r.StudentID
LEFT JOIN 
    StudentFeedback f ON s.StudentID = f.StudentID;

教师视图

CREATE VIEW InstructorView AS
SELECT 
    i.InstructorID, 
    i.Name AS InstructorName, 
    i.Department, 
    tt.TopicID, 
    tt.Title AS TopicTitle, 
    tt.Description AS TopicDescription, 
    tt.MaxStudents, 
    tt.RegistrationDeadline, 
    tt.Status, 
    st.StudentID, 
    s.Name AS StudentName, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    p.LogID, 
    p.ProgressDescription, 
    p.RecordDate
FROM 
    Instructors i
LEFT JOIN 
    ThesisTopics tt ON i.InstructorID = tt.InstructorID
LEFT JOIN 
    StudentThesis st ON tt.TopicID = st.TopicID
LEFT JOIN 
    Students s ON st.StudentID = s.StudentID
LEFT JOIN 
    ProgressLogs p ON st.StudentID = p.StudentID AND st.TopicID = p.TopicID;

管理员视图

CREATE VIEW AdminView AS
SELECT 
    a.AdminID, 
    a.Name AS AdminName, 
    a.Username, 
    a.PermissionLevel, 
    s.StudentID, 
    s.Name AS StudentName, 
    s.Gender, 
    s.Major, 
    s.Class, 
    s.Phone, 
    i.InstructorID, 
    i.Name AS InstructorName, 
    i.Department, 
    i.ResearchDirection, 
    i.Phone AS InstructorPhone, 
    tt.TopicID, 
    tt.Title AS TopicTitle, 
    tt.Description AS TopicDescription, 
    tt.MaxStudents, 
    tt.RegistrationDeadline, 
    tt.Status, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    p.LogID, 
    p.ProgressDescription, 
    p.RecordDate, 
    r.ReviewID, 
    r.ReviewComments, 
    r.ReviewResult, 
    r.ReviewDate, 
    f.FeedbackID, 
    f.FeedbackContent, 
    f.FeedbackDate
FROM 
    Administrators a
LEFT JOIN 
    Students s ON 1=1
LEFT JOIN 
    Instructors i ON 1=1
LEFT JOIN 
    ThesisTopics tt ON 1=1
LEFT JOIN 
    StudentThesis st ON 1=1
LEFT JOIN 
    ProgressLogs p ON 1=1
LEFT JOIN 
    ThesisReviews r ON 1=1
LEFT JOIN 
    StudentFeedback f ON 1=1;

最终代码

-- 创建数据库
CREATE DATABASE GraduationProjectManagement;
GO

-- 使用数据库
USE GraduationProjectManagement;
GO

-- 创建学生表
CREATE TABLE Students (
    StudentID NVARCHAR(20) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Gender CHAR(1) CHECK (Gender IN ('M', 'F')),
    Major NVARCHAR(50),
    Class NVARCHAR(50),
    Phone NVARCHAR(20)
);
GO

-- 创建指导教师表
CREATE TABLE Instructors (
    InstructorID NVARCHAR(20) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50),
    ResearchDirection NVARCHAR(100),
    Phone NVARCHAR(20)
);
GO

-- 创建管理员信息表
CREATE TABLE Administrators (
    AdminID NVARCHAR(20) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Username NVARCHAR(50) UNIQUE NOT NULL,
    Password NVARCHAR(50) NOT NULL,
    PermissionLevel INT CHECK (PermissionLevel BETWEEN 1 AND 10)
);
GO

-- 创建毕业设计题目信息表
CREATE TABLE ThesisTopics (
    TopicID NVARCHAR(20) PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL,
    Description NVARCHAR(MAX),
    MaxStudents INT CHECK (MaxStudents > 0),
    RegistrationDeadline DATE,
    Status NVARCHAR(20) CHECK (Status IN ('Open', 'Closed', 'Completed')),
    InstructorID NVARCHAR(20),
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
GO

-- 创建学生毕业设计信息表
CREATE TABLE StudentThesis (
    StudentID NVARCHAR(20),
    TopicID NVARCHAR(20),
    StartDate DATE,
    EndDate DATE,
    CurrentProgress NVARCHAR(100),
    PRIMARY KEY (StudentID, TopicID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (TopicID) REFERENCES ThesisTopics(TopicID)
);
GO

-- 创建进度信息表
CREATE TABLE ProgressLogs (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID NVARCHAR(20),
    TopicID NVARCHAR(20),
    ProgressDescription NVARCHAR(MAX),
    RecordDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (TopicID) REFERENCES ThesisTopics(TopicID)
);
GO

-- 创建论文审核表
CREATE TABLE ThesisReviews (
    ReviewID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID NVARCHAR(20),
    ReviewComments NVARCHAR(MAX),
    ReviewResult NVARCHAR(20) CHECK (ReviewResult IN ('Pass', 'Fail', 'Pending')),
    ReviewDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
GO

-- 创建学生反馈表
CREATE TABLE StudentFeedback (
    FeedbackID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID NVARCHAR(20),
    TopicID NVARCHAR(20),
    FeedbackContent NVARCHAR(MAX),
    FeedbackDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (TopicID) REFERENCES ThesisTopics(TopicID)
);
GO

-- 创建序列用于生成唯一学生编号
CREATE SEQUENCE StudentID_Seq
    START WITH 1
    INCREMENT BY 1;
GO

-- 创建触发器：插入学生记录时自动生成学生编号
CREATE TRIGGER trg_InsertStudentID
ON Students
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @NewStudentID NVARCHAR(20);
    SELECT @NewStudentID = 'S' + RIGHT('00000' + CAST(NEXT VALUE FOR StudentID_Seq AS NVARCHAR(5)), 5)
    FROM inserted;

    INSERT INTO Students (StudentID, Name, Gender, Major, Class, Phone)
    SELECT @NewStudentID, Name, Gender, Major, Class, Phone
    FROM inserted;
END;
GO

-- 创建触发器：更新学生毕业设计信息时记录日志
CREATE TRIGGER trg_UpdateStudentThesis
ON StudentThesis
AFTER UPDATE
AS
BEGIN
    DECLARE @StudentID NVARCHAR(20);
    DECLARE @TopicID NVARCHAR(20);
    DECLARE @CurrentProgress NVARCHAR(100);
    DECLARE @ChangeDate DATETIME;

    SELECT @StudentID = inserted.StudentID,
           @TopicID = inserted.TopicID,
           @CurrentProgress = inserted.CurrentProgress,
           @ChangeDate = GETDATE()
    FROM inserted;

    INSERT INTO ProgressLogs (StudentID, TopicID, ProgressDescription, RecordDate)
    VALUES (@StudentID, @TopicID, @CurrentProgress, @ChangeDate);
END;
GO

-- 创建触发器：删除学生时删除相关联的毕业设计信息
CREATE TRIGGER trg_DeleteStudent
ON Students
AFTER DELETE
AS
BEGIN
    DELETE FROM StudentThesis
    WHERE StudentID IN (SELECT StudentID FROM deleted);
END;
GO

-- 为Students表添加索引
CREATE INDEX idx_students_name ON Students (Name);
CREATE INDEX idx_students_major ON Students (Major);
GO

-- 为Instructors表添加索引
CREATE INDEX idx_instructors_name ON Instructors (Name);
CREATE INDEX idx_instructors_department ON Instructors (Department);
GO

-- 为ThesisTopics表添加索引
CREATE INDEX idx_thesistopics_title ON ThesisTopics (Title);
CREATE INDEX idx_thesistopics_instructorid ON ThesisTopics (InstructorID);
GO

-- 为StudentThesis表添加索引
CREATE INDEX idx_studentthesis_studentid ON StudentThesis (StudentID);
CREATE INDEX idx_studentthesis_topicid ON StudentThesis (TopicID);
GO

-- 为ProgressLogs表添加索引
CREATE INDEX idx_progresslogs_studentid ON ProgressLogs (StudentID);
CREATE INDEX idx_progresslogs_topicid ON ProgressLogs (TopicID);
GO

-- 为ThesisReviews表添加索引
CREATE INDEX idx_thesisreviews_studentid ON ThesisReviews (StudentID);
GO

-- 为StudentFeedback表添加索引
CREATE INDEX idx_studentfeedback_studentid ON StudentFeedback (StudentID);
CREATE INDEX idx_studentfeedback_topicid ON StudentFeedback (TopicID);
GO

-- 创建学生视图
CREATE VIEW StudentView AS
SELECT 
    s.StudentID, 
    s.Name, 
    s.Gender, 
    s.Major, 
    s.Class, 
    s.Phone, 
    st.TopicID, 
    tt.Title AS TopicTitle, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    r.ReviewComments, 
    r.ReviewResult, 
    r.ReviewDate, 
    f.FeedbackContent, 
    f.FeedbackDate
FROM 
    Students s
LEFT JOIN 
    StudentThesis st ON s.StudentID = st.StudentID
LEFT JOIN 
    ThesisTopics tt ON st.TopicID = tt.TopicID
LEFT JOIN 
    ThesisReviews r ON s.StudentID = r.StudentID
LEFT JOIN 
    StudentFeedback f ON s.StudentID = f.StudentID;
GO

-- 创建教师视图
CREATE VIEW InstructorView AS
SELECT 
    i.InstructorID, 
    i.Name AS InstructorName, 
    i.Department, 
    tt.TopicID, 
    tt.Title AS TopicTitle, 
    tt.Description AS TopicDescription, 
    tt.MaxStudents, 
    tt.RegistrationDeadline, 
    tt.Status, 
    st.StudentID, 
    s.Name AS StudentName, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    p.LogID, 
    p.ProgressDescription, 
    p.RecordDate
FROM 
    Instructors i
LEFT JOIN 
    ThesisTopics tt ON i.InstructorID = tt.InstructorID
LEFT JOIN 
    StudentThesis st ON tt.TopicID = st.TopicID
LEFT JOIN 
    Students s ON st.StudentID = s.StudentID
LEFT JOIN 
    ProgressLogs p ON st.StudentID = p.StudentID AND st.TopicID = p.TopicID;
GO

-- 创建管理员视图
CREATE VIEW AdminView AS
SELECT 
    a.AdminID, 
    a.Name AS AdminName, 
    a.Username, 
    a.PermissionLevel, 
    s.StudentID, 
    s.Name AS StudentName, 
    s.Gender, 
    s.Major, 
    s.Class, 
    s.Phone, 
    i.InstructorID, 
    i.Name AS InstructorName, 
    i.Department, 
    i.ResearchDirection, 
    i.Phone AS InstructorPhone, 
    tt.TopicID, 
    tt.Title AS TopicTitle, 
    tt.Description AS TopicDescription, 
    tt.MaxStudents, 
    tt.RegistrationDeadline, 
    tt.Status, 
    st.StartDate, 
    st.EndDate, 
    st.CurrentProgress, 
    p.LogID, 
    p.ProgressDescription, 
    p.RecordDate, 
    r.ReviewID, 
    r.ReviewComments, 
    r.ReviewResult, 
    r.ReviewDate, 
    f.FeedbackID, 
    f.FeedbackContent, 
    f.FeedbackDate
FROM 
    Administrators a
LEFT JOIN 
    Students s ON 1=1
LEFT JOIN 
    Instructors i ON 1=1
LEFT JOIN 
    ThesisTopics tt ON 1=1
LEFT JOIN 
    StudentThesis st ON 1=1
LEFT JOIN 
    ProgressLogs p ON 1=1
LEFT JOIN 
    ThesisReviews r ON 1=1
LEFT JOIN 
    StudentFeedback f ON 1=1;
GO

功能测试代码

-- 插入指导教师数据
INSERT INTO Instructors (InstructorID, Name, Department, ResearchDirection, Phone)
VALUES 
('T001', 'Dr. Smith', 'Computer Science', 'Artificial Intelligence', '1234567890'),
('T002', 'Dr. Johnson', 'Mathematics', 'Statistics', '0987654321');

-- 插入管理员数据
INSERT INTO Administrators (AdminID, Name, Username, Password, PermissionLevel)
VALUES 
('A001', 'Admin One', 'admin1', 'password1', 5),
('A002', 'Admin Two', 'admin2', 'password2', 7);

-- 插入毕业设计题目数据
INSERT INTO ThesisTopics (TopicID, Title, Description, MaxStudents, RegistrationDeadline, Status, InstructorID)
VALUES 
('TP001', 'AI in Healthcare', 'Research on AI applications in healthcare.', 3, '2024-08-31', 'Open', 'T001'),
('TP002', 'Statistical Methods in Big Data', 'Exploring statistical methods for big data.', 2, '2024-09-15', 'Open', 'T002');

-- 插入学生数据（触发器自动生成StudentID）
INSERT INTO Students (Name, Gender, Major, Class, Phone)
VALUES 
('John Doe', 'M', 'Computer Science', 'CS2024', '1112223333'),
('Jane Doe', 'F', 'Mathematics', 'Math2024', '4445556666');

-- 验证指导教师数据插入
SELECT * FROM Instructors;

-- 验证管理员数据插入
SELECT * FROM Administrators;

-- 验证毕业设计题目数据插入
SELECT * FROM ThesisTopics;

-- 验证学生数据插入
SELECT * FROM Students;

-- 插入学生毕业设计信息
INSERT INTO StudentThesis (StudentID, TopicID, StartDate, EndDate, CurrentProgress)
VALUES 
((SELECT StudentID FROM Students WHERE Name = 'John Doe'), 'TP001', '2024-06-01', '2025-05-31', 'Initial Research'),
((SELECT StudentID FROM Students WHERE Name = 'Jane Doe'), 'TP002', '2024-06-01', '2025-05-31', 'Initial Research');

-- 验证学生毕业设计信息插入
SELECT * FROM StudentThesis;

-- 更新学生毕业设计信息
UPDATE StudentThesis
SET CurrentProgress = 'Literature Review'
WHERE StudentID = (SELECT StudentID FROM Students WHERE Name = 'John Doe') AND TopicID = 'TP001';

-- 验证进度日志
SELECT * FROM ProgressLogs;

-- 删除学生数据
DELETE FROM Students WHERE Name = 'Jane Doe';

-- 验证学生删除及其相关毕业设计信息删除
SELECT * FROM Students;
SELECT * FROM StudentThesis WHERE StudentID = (SELECT StudentID FROM Students WHERE Name = 'Jane Doe');

-- 插入论文审核数据
INSERT INTO ThesisReviews (StudentID, ReviewComments, ReviewResult, ReviewDate)
VALUES 
((SELECT StudentID FROM Students WHERE Name = 'John Doe'), 'Good progress', 'Pass', '2024-06-30');

-- 插入学生反馈数据
INSERT INTO StudentFeedback (StudentID, TopicID, FeedbackContent, FeedbackDate)
VALUES 
((SELECT StudentID FROM Students WHERE Name = 'John Doe'), 'TP001', 'Need more resources', '2024-06-25');

-- 验证论文审核数据
SELECT * FROM ThesisReviews;

-- 验证学生反馈数据
SELECT * FROM StudentFeedback;

-- 更新管理员密码
UPDATE Administrators
SET Password = 'newpassword1'
WHERE AdminID = 'A001';

-- 验证管理员更新
SELECT * FROM Administrators WHERE AdminID = 'A001';

-- 验证所有表的数据
SELECT * FROM Students;
SELECT * FROM Instructors;
SELECT * FROM Administrators;
SELECT * FROM ThesisTopics;
SELECT * FROM StudentThesis;
SELECT * FROM ProgressLogs;
SELECT * FROM ThesisReviews;
SELECT * FROM StudentFeedback;

ER图

关系模式：

Students (学生表)

• StudentID (主键): NVARCHAR(20)
• Name: NVARCHAR(50)
• Gender: CHAR(1)
• Major: NVARCHAR(50)
• Class: NVARCHAR(50)
• Phone: NVARCHAR(20)

Instructors (指导教师表)

• InstructorID (主键): NVARCHAR(20)
• Name: NVARCHAR(50)
• Department: NVARCHAR(50)
• ResearchDirection: NVARCHAR(100)
• Phone: NVARCHAR(20)

Administrators (管理员信息表)

• AdminID (主键): NVARCHAR(20)
• Name: NVARCHAR(50)
• Username: NVARCHAR(50) (唯一)
• Password: NVARCHAR(50)
• PermissionLevel: INT

ThesisTopics (毕业设计题目信息表)

• TopicID (主键): NVARCHAR(20)
• Title: NVARCHAR(100)
• Description: NVARCHAR(MAX)
• MaxStudents: INT
• RegistrationDeadline: DATE
• Status: NVARCHAR(20)
• InstructorID (外键): NVARCHAR(20)

StudentThesis (学生毕业设计信息表)

• StudentID (主键，外键): NVARCHAR(20)
• TopicID (主键，外键): NVARCHAR(20)
• StartDate: DATE
• EndDate: DATE
• CurrentProgress: NVARCHAR(100)

ProgressLogs (进度信息表)

• LogID (主键): INT
• StudentID (外键): NVARCHAR(20)
• TopicID (外键): NVARCHAR(20)
• ProgressDescription: NVARCHAR(MAX)
• RecordDate: DATE

ThesisReviews (论文审核表)

• ReviewID (主键): INT
• StudentID (外键): NVARCHAR(20)
• ReviewComments: NVARCHAR(MAX)
• ReviewResult: NVARCHAR(20)
• ReviewDate: DATE

StudentFeedback (学生反馈表)

• FeedbackID (主键): INT
• StudentID (外键): NVARCHAR(20)
• TopicID (外键): NVARCHAR(20)
• FeedbackContent: NVARCHAR(MAX)
• FeedbackDate: DATE

数据字典

1. Students
   • StudentID: NVARCHAR(20), 主键，学生唯一标识，由触发器自动生成。
   • Name: NVARCHAR(50), 学生姓名，非空。
   • Gender: CHAR(1), 性别，检查约束限制为’M’或’F’。
   • Major: NVARCHAR(50), 专业。
   • Class: NVARCHAR(50), 班级。
   • Phone: NVARCHAR(20), 联系电话。
2. Instructors
   • InstructorID: NVARCHAR(20), 主键，教师唯一标识。
   • Name: NVARCHAR(50), 教师姓名，非空。
   • Department: NVARCHAR(50), 所属部门。
   • ResearchDirection: NVARCHAR(100), 研究方向。
   • Phone: NVARCHAR(20), 联系电话。
3. Administrators
   • AdminID: NVARCHAR(20), 主键，管理员唯一标识。
   • Name: NVARCHAR(50), 管理员姓名，非空。
   • Username: NVARCHAR(50), 用户名，唯一且非空。
   • Password: NVARCHAR(50), 密码，非空。
   • PermissionLevel: INT, 权限级别，检查约束限制在1到10之间。
4. ThesisTopics
   • TopicID: NVARCHAR(20), 主键，毕业设计题目ID。
   • Title: NVARCHAR(100), 题目标题，非空。
   • Description: NVARCHAR(MAX), 题目描述。
   • MaxStudents: INT, 最大可选此题目的学生数，检查约束确保大于0。
   • RegistrationDeadline: DATE, 报名截止日期。
   • Status: NVARCHAR(20), 题目状态，检查约束限制为’Open’, ‘Closed’, ‘Completed’。
   • InstructorID: NVARCHAR(20), 外键，关联至Instructors表。
5. StudentThesis
   • StudentID: NVARCHAR(20), 组合主键部分，关联至Students表。
   • TopicID: NVARCHAR(20), 组合主键部分，关联至ThesisTopics表。
   • StartDate: DATE, 开始日期。
   • EndDate: DATE, 结束日期。
   • CurrentProgress: NVARCHAR(100), 当前进度。
6. ProgressLogs
   • LogID: INT, 主键，自增。
   • StudentID: NVARCHAR(20), 外键，关联至Students表。
   • TopicID: NVARCHAR(20), 外键，关联至ThesisTopics表。
   • ProgressDescription: NVARCHAR(MAX), 进度描述。
   • RecordDate: DATE, 记录日期。
7. ThesisReviews
   • ReviewID: INT, 主键，自增。
   • StudentID: NVARCHAR(20), 外键，关联至Students表。
   • ReviewComments: NVARCHAR(MAX), 审核评论。
   • ReviewResult: NVARCHAR(20), 审核结果，检查约束限制为’Pass’, ‘Fail’, ‘Pending’。
   • ReviewDate: DATE, 审核日期。
8. StudentFeedback
   • FeedbackID: INT, 主键，自增。
   • StudentID: NVARCHAR(20), 外键，关联至Students表。
   • TopicID: NVARCHAR(20), 外键，关联至ThesisTopics表。
   • FeedbackContent: NVARCHAR(MAX), 反馈内容。
   • FeedbackDate: DATE, 反馈日期。
9. Sequence
   • StudentID_Seq: 序列，用于生成唯一的StudentID。

触发器

• trg_InsertStudentID: 在Students表插入数据时，自动分配学生ID。
• trg_UpdateStudentThesis: 更新StudentThesis表时，自动记录进度日志。
• trg_DeleteStudent: 删除Students表中的学生时，同时删除关联的StudentThesis记录。

索引

分别为各表的关键字段创建了索引，以提高查询效率。