超详细！学生成长管理系统SQL Server数据库设计全解析

# 学生成长管理系统SQL Server数据库设计 ## 数据库创建 ```sql CREATE DATABASE StudentGrowthManagement; GO USE StudentGrowthManagement; GO ``` ## 表结构设计 1. 学生信息表 ```sql CREATE TABLE Students ( StudentID INT PRIMARY KEY IDENTITY(1000,1), StudentName NVARCHAR(50) NOT NULL, Gender CHAR(1) CHECK (Gender IN ('M', 'F')), BirthDate DATE, IDCardNumber CHAR(18) UNIQUE, EnrollmentDate DATE NOT NULL, ClassID INT, ContactPhone VARCHAR(20), Address NVARCHAR(200), PhotoPath NVARCHAR(255), Status TINYINT DEFAULT 1 COMMENT '1-在读, 2-休学, 3-退学, 4-毕业' ); ``` 2. 班级信息表 ```sql CREATE TABLE Classes ( ClassID INT PRIMARY KEY IDENTITY(1,1), ClassName NVARCHAR(50) NOT NULL, Grade INT NOT NULL, MajorID INT, HeadTeacherID INT, StudentCount INT DEFAULT 0, CreateDate DATETIME DEFAULT GETDATE() ); ``` 3. 专业信息表 ```sql CREATE TABLE Majors ( MajorID INT PRIMARY KEY IDENTITY(1,1), MajorName NVARCHAR(50) NOT NULL, DepartmentID INT, Description NVARCHAR(500) ); ``` 4. 院系信息表 ```sql CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY IDENTITY(1,1), DepartmentName NVARCHAR(50) NOT NULL, Dean NVARCHAR(50), ContactPhone VARCHAR(20) ); ``` 5. 教师信息表 ```sql CREATE TABLE Teachers ( TeacherID INT PRIMARY KEY IDENTITY(100,1), TeacherName NVARCHAR(50) NOT NULL, Gender CHAR(1) CHECK (Gender IN ('M', 'F')), Title NVARCHAR(20), DepartmentID INT, ContactPhone VARCHAR(20), Email VARCHAR(100) ); ``` 6. 课程信息表 ```sql CREATE TABLE Courses ( CourseID INT PRIMARY KEY IDENTITY(1,1), CourseName NVARCHAR(100) NOT NULL, Credit DECIMAL(3,1) NOT NULL, Hours INT NOT NULL, CourseType TINYINT COMMENT '1-必修, 2-选修, 3-实践', DepartmentID INT ); ``` 7. 学生成绩表 ```sql CREATE TABLE Scores ( ScoreID INT PRIMARY KEY IDENTITY(1,1), StudentID INT NOT NULL, CourseID INT NOT NULL, Term VARCHAR(20) NOT NULL, RegularScore DECIMAL(5,2), ExamScore DECIMAL(5,2), FinalScore DECIMAL(5,2), CreditEarned DECIMAL(3,1), TeacherID INT, RecordTime DATETIME DEFAULT GETDATE(), FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID), FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID) ); ``` 8. 成长记录表 ```sql CREATE TABLE GrowthRecords ( RecordID INT PRIMARY KEY IDENTITY(1,1), StudentID INT NOT NULL, RecordType TINYINT NOT NULL COMMENT '1-奖励, 2-惩罚, 3-活动, 4-社会实践, 5-其他', Title NVARCHAR(100) NOT NULL, Content NVARCHAR(MAX), RecordDate DATE NOT NULL, AttachmentPath NVARCHAR(255), CreatorID INT, CreateTime DATETIME DEFAULT GETDATE(), FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ); ``` 9. 用户账户表 ```sql CREATE TABLE Users ( UserID INT PRIMARY KEY IDENTITY(1,1), Username VARCHAR(50) NOT NULL UNIQUE, Password VARCHAR(100) NOT NULL, UserType TINYINT NOT NULL COMMENT '1-管理员, 2-教师, 3-学生', RelatedID INT NOT NULL COMMENT '关联ID(学生ID/教师ID)', LastLoginTime DATETIME, Status TINYINT DEFAULT 1 COMMENT '1-正常, 0-禁用' ); ``` 10. 系统日志表 ```sql CREATE TABLE SystemLogs ( LogID INT PRIMARY KEY IDENTITY(1,1), UserID INT, ActionType VARCHAR(50) NOT NULL, ActionContent NVARCHAR(500), IPAddress VARCHAR(50), ActionTime DATETIME DEFAULT GETDATE() ); ``` ## 索引创建 ```sql -- 学生表索引 CREATE INDEX IX_Students_ClassID ON Students(ClassID); CREATE INDEX IX_Students_Name ON Students(StudentName); -- 成绩表索引 CREATE INDEX IX_Scores_StudentID ON Scores(StudentID); CREATE INDEX IX_Scores_CourseID ON Scores(CourseID); CREATE INDEX IX_Scores_Term ON Scores(Term); -- 成长记录索引 CREATE INDEX IX_GrowthRecords_StudentID ON GrowthRecords(StudentID); CREATE INDEX IX_GrowthRecords_RecordType ON GrowthRecords(RecordType); ``` ## 视图创建 学生综合信息视图 ```sql CREATE VIEW V_StudentInfo AS SELECT s.StudentID, s.StudentName, s.Gender, s.BirthDate, s.IDCardNumber, s.EnrollmentDate, s.ContactPhone, s.Address, s.PhotoPath, s.Status, c.ClassName, c.Grade, m.MajorName, d.DepartmentName FROM Students s LEFT JOIN Classes c ON s.ClassID = c.ClassID LEFT JOIN Majors m ON c.MajorID = m.MajorID LEFT JOIN Departments d ON m.DepartmentID = d.DepartmentID; ``` 学生成绩统计视图 ```sql CREATE VIEW V_StudentScoreSummary AS SELECT s.StudentID, s.StudentName, c.ClassName, COUNT(sc.CourseID) AS CourseCount, SUM(sc.CreditEarned) AS TotalCredits, AVG(sc.FinalScore) AS AverageScore FROM Students s LEFT JOIN Classes c ON s.ClassID = c.ClassID LEFT JOIN Scores sc ON s.StudentID = sc.StudentID GROUP BY s.StudentID, s.StudentName, c.ClassName; ``` ## 存储过程示例 添加学生信息 ```sql CREATE PROCEDURE sp_AddStudent @StudentName NVARCHAR(50), @Gender CHAR(1), @BirthDate DATE, @IDCardNumber CHAR(18), @EnrollmentDate DATE, @ClassID INT, @ContactPhone VARCHAR(20), @Address NVARCHAR(200) AS BEGIN INSERT INTO Students ( StudentName, Gender, BirthDate, IDCardNumber, EnrollmentDate, ClassID, ContactPhone, Address ) VALUES ( @StudentName, @Gender, @BirthDate, @IDCardNumber, @EnrollmentDate, @ClassID, @ContactPhone, @Address ); -- 更新班级学生人数 UPDATE Classes SET StudentCount = StudentCount + 1 WHERE ClassID = @ClassID; RETURN SCOPE_IDENTITY(); END; ``` 查询学生成长记录 ```sql CREATE PROCEDURE sp_GetStudentGrowthRecords @StudentID INT, @RecordType TINYINT = NULL, @StartDate DATE = NULL, @EndDate DATE = NULL AS BEGIN SELECT RecordID, RecordType, Title, Content, RecordDate, AttachmentPath FROM GrowthRecords WHERE StudentID = @StudentID AND (@RecordType IS NULL OR RecordType = @RecordType) AND (@StartDate IS NULL OR RecordDate >= @StartDate) AND (@EndDate IS NULL OR RecordDate <= @EndDate) ORDER BY RecordDate DESC; END; ``` 这个数据库设计涵盖了学生成长管理系统的主要功能需求，包括学生基本信息管理、成绩管理、成长记录管理等。可以根据实际需求进一步扩展或修改。 ######[AI问答 | 1793点数解答 | 2025-04-14 17:08:39]