【数据库原理 实验报告8 大作业】选修课管理系统数据库实现

上一篇【数据库原理 实验报告7】视图和存储过程的应用目录2需求分析2.1 需求分析2.2 系统功能结构3概念模型设计31 局部 E-R 图32 全局 E-R 图4逻辑模型设计4.1 由ER图转换成关系模式4.2 关系模式的优化满足第三范式5. 用SQL语言建立数据库及定义表5.1 建立数据库5.2 建立表正确设定列的数据类型、取值范围、主外键等所有表数据库值的录入6.1 采用直接输入方式并进行正常录入和失败录入违反规则的测试6.2 采用SQL语句方式并进行正常录入和失败录入违反规则的测试。增删改功能7.1修改表结构 插入列、删除列、修改列7.2 修改表中数据删除元组、修改元组8.对表进行查询8.1单表查询多表查询分组查询嵌套查询集合查询收获与体会数据库介绍数据库是一个系统用来存储、管理和处理数据的软件。在选修课管理系统中数据库用于存储课程信息、学生信息、选课记录等数据以便于进行有效的数据管理和查询。2需求分析2.1 需求分析- 存储学生信息包括学生ID、姓名、年级等。- 存储课程信息包括课程ID、课程名称、教师、课程时间等。- 记录学生的选课信息包括学生ID、课程ID、选课时间等。- 提供查询功能如查询某学生的选课记录查询某课程的选课学生等。- 提供管理功能如添加或删除课程修改学生信息等。2.2 系统功能结构- 学生信息管理添加、删除、修改、查询学生信息。- 课程信息管理添加、删除、修改、查询课程信息。- 选课管理添加、删除选课记录查询选课信息。- 查询功能按条件查询学生和课程信息。3概念模型设计31 局部 E-R 图- 学生学生ID姓名年级- 课程课程ID课程名称教师课程时间- 选课学生ID课程ID选课时间32 全局 E-R 图- 学生和课程之间存在多对多的关系通过选课关系来实现。4逻辑模型设计4.1 由ER图转换成关系模式- 学生学生ID姓名年级- 课程课程ID课程名称教师课程时间- 选课学生ID课程ID选课时间4.2 关系模式的优化满足第三范式- 确保每个表都有主键消除重复的列确保每列与主键的依赖性。- 学生表的主键是学生ID课程表的主键是课程ID选课表的主键是学生ID和课程ID的组合。- 检查并消除传递依赖和部分依赖。结果如下学生(学生ID(PK), 姓名, 年级)课程(课程ID(PK), 课程名称)教师(教师ID(PK), 教师姓名)课程时间(课程时间ID(PK), 课程时间)教授课程(教师ID(FK), 课程ID(FK), 课程时间ID(FK))选课(学生ID(FK), 课程ID(FK), 选课时间)5. 用SQL语言建立数据库及定义表5.1 建立数据库截图代码CREATEDATABASEElectiveCoursesManagement;5.2 建立表正确设定列的数据类型、取值范围、主外键等所有表创建学生表截图代码CREATE TABLE Students ( StudentID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50), Grade NVARCHAR(20), Email NVARCHAR(100) UNIQUE );创建课程表截图代码CREATE TABLE Courses ( CourseID INT IDENTITY(1,1) PRIMARY KEY, CourseName NVARCHAR(100), Teacher NVARCHAR(50), Credits INT );创建选课表截图代码CREATE TABLE CourseSelection ( SelectionID INT IDENTITY(1,1) PRIMARY KEY, StudentID INT, CourseID INT, SelectionTime DATETIME, FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) );数据库值的录入6.1 采用直接输入方式并进行正常录入和失败录入违反规则的测试正常录入截图失败录入截图6.2 采用SQL语句方式并进行正常录入和失败录入违反规则的测试。实例正常录入截图代码-- 插入 Students 表数据 INSERT INTO Students (Name, Grade, Email) VALUES (张三, Grade 1, zhangsan1example.com), (李四, Grade 2, lisi2example.com), (王五, Grade 3, wangwu3example.com), (赵六, Grade 4, zhaoliu4example.com), (钱七, Grade 1, qianqi5example.com); -- 批量插入剩余的 Students 数据 DECLARE i INT 6; WHILE i 105 BEGIN INSERT INTO Students (Name, Grade, Email) VALUES (学生CAST(i AS NVARCHAR(10)), Grade CAST((i % 4 1) AS NVARCHAR(10)), studentCAST(i AS NVARCHAR(10))example.com); SET i i 1; END失败录入截图代码BEGIN TRY INSERT INTO Courses (CourseName, Teacher, Credits) VALUES (, 王, 一); END TRY BEGIN CATCH PRINT 插入空课程名时出错 ERROR_MESSAGE(); END CATCH;批量录入大量数据完整录入截图代码-- 插入 Students 表数据 INSERT INTO Students (Name, Grade, Email) VALUES (张三, Grade 1, zhangsan1example.com), (李四, Grade 2, lisi2example.com), (王五, Grade 3, wangwu3example.com), (赵六, Grade 4, zhaoliu4example.com), (钱七, Grade 1, qianqi5example.com); -- 批量插入剩余的 Students 数据 DECLARE i INT 6; WHILE i 105 BEGIN INSERT INTO Students (Name, Grade, Email) VALUES (学生CAST(i AS NVARCHAR(10)), Grade CAST((i % 4 1) AS NVARCHAR(10)), studentCAST(i AS NVARCHAR(10))example.com); SET i i 1; END -- 插入 Courses 表数据 INSERT INTO Courses (CourseName, Teacher, Credits) VALUES (数学, 王老师, 3), (物理, 李老师, 4), (化学, 张老师, 3), (英语, 赵老师, 2), (历史, 钱老师, 2); -- 插入 CourseSelection 表数据 INSERT INTO CourseSelection (StudentID, CourseID, SelectionTime) VALUES (1, 1, GETDATE()), (1, 2, GETDATE()), (2, 2, GETDATE()), (3, 1, GETDATE()), (3, 3, GETDATE()), (4, 4, GETDATE()), (5, 5, GETDATE()); -- 批量插入剩余的 CourseSelection 数据 DECLARE j INT 6; DECLARE k INT 1; WHILE j 105 BEGIN INSERT INTO CourseSelection (StudentID, CourseID, SelectionTime) VALUES (j, k, GETDATE()); SET k k 1; IF k 5 SET k 1; SET j j 1; END增删改功能7.1修改表结构 插入列、删除列、修改列插入列代码ALTER TABLE Students ADD MainEmail NVARCHAR(255);插入列截图删除列代码ALTER TABLE Students DROP COLUMN MainEmail;删除列截图修改列代码ALTER TABLE Courses ALTER COLUMN Teacher NVARCHAR(150);修改列截图最终展示7.2 修改表中数据删除元组、修改元组删除元组代码DELETE FROM CourseSelection WHERE SelectionID1;删除元组效果截图修改元组代码UPDATE Students SET Name 张三三, Email zhangsansanexample.com WHERE StudentID 1;修改元组效果截图8.对表进行查询8.1单表查询查询学生表中所有学生的信息截图代码SELECT * FROM Students;查询课程表中所有课程的名称和教师名截图代码SELECT CourseName, Teacher FROM Courses;查询学生表中所有学生的信息并按照姓名进行排序同时计算每个学生的选课数量假设有一个 CourseSelection表记录学生选课信息截图代码SELECT s.StudentID, s.Name, s.Grade, s.Email, COUNT(cs.CourseID) AS CourseCount FROM Students s LEFT JOIN CourseSelection cs ON s.StudentID cs.StudentID GROUP BY s.StudentID, s.Name, s.Grade, s.Email ORDER BY s.Name;查询 Students表中所有学生的详细信息按照 Grade和 Name进行排序并计算每个学生的选课数量。截图代码SELECT s.StudentID, s.Name, s.Grade, s.Email, (SELECT COUNT(*) FROM CourseSelection cs WHERE cs.StudentID s.StudentID) AS CourseCount FROM Students s ORDER BY s.Grade, s.Name;查询 Students表中每个年级学生的平均选课数量并显示每个年级中选课最多的学生姓名。截图代码SELECT s.Grade, AVG(cs.CourseCount) AS AvgCourseCount, MAX(s.Name) AS TopStudent FROM Students s LEFT JOIN ( SELECT StudentID, COUNT(*) AS CourseCount FROM CourseSelection GROUP BY StudentID ) cs ON s.StudentID cs.StudentID GROUP BY s.Grade;查询 Courses表中每个课程的总选课人数和总学分并显示选课人数最多的课程。截图代码SELECT c.CourseName, COUNT(cs.StudentID) AS TotalStudents, SUM(c.Credits) AS TotalCredits FROM Courses c LEFT JOIN CourseSelection cs ON c.CourseID cs.CourseID GROUP BY c.CourseName ORDER BY TotalStudents DESC, TotalCredits DESC;多表查询查询所有学生及其选课信息联合查询截图代码SELECT Students.Name, Courses.CourseName FROM Students JOIN CourseSelection ON Students.StudentID CourseSelection.StudentID JOIN Courses ON CourseSelection.CourseID Courses.CourseID;查询所有学生及其选课信息并包含课程的详细信息同时计算每个学生的总学分假设每门课程有学分字段截图代码SELECT s.StudentID, s.Name, c.CourseName, c.Teacher, c.Credits, cs.SelectionTime, SUM(c.Credits) OVER (PARTITION BY s.StudentID) AS TotalCredits FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID ORDER BY s.Name, cs.SelectionTime;查询每个学生的选课详情包括课程名称、教师、学分和选课时间同时计算每个学生的总学分和总课程数。截图代码SELECT s.StudentID, s.Name, c.CourseName, c.Teacher, c.Credits, cs.SelectionTime, SUM(c.Credits) OVER (PARTITION BY s.StudentID) AS TotalCredits, COUNT(c.CourseID) OVER (PARTITION BY s.StudentID) AS TotalCourses FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID ORDER BY s.Name, cs.SelectionTime;查询每个课程的学生信息包括学生姓名、年级和选课时间同时显示课程的总选课人数和平均选课时间。截图代码SELECT c.CourseName, s.StudentID, s.Name, s.Grade, cs.SelectionTime, COUNT(cs.StudentID) OVER (PARTITION BY c.CourseName) AS TotalStudents, AVG(DATEDIFF(MINUTE, 0, cs.SelectionTime)) OVER (PARTITION BY c.CourseName) AS AvgSelectionTime FROM Courses c JOIN CourseSelection cs ON c.CourseID cs.CourseID JOIN Students s ON cs.StudentID s.StudentID ORDER BY c.CourseName, s.Name;分组查询查询每个课程的选课人数截图代码SELECT Courses.CourseName, COUNT(*) AS NumberOfStudents FROM Courses JOIN CourseSelection ON Courses.CourseID CourseSelection.CourseID GROUP BY Courses.CourseName;查询每个课程的选课人数和平均选课时间按照选课人数降序排序。截图代码SELECT c.CourseName, COUNT(cs.StudentID) AS NumberOfStudents, AVG(DATEDIFF(MINUTE, 0, cs.SelectionTime)) AS AvgSelectionTime FROM Courses c JOIN CourseSelection cs ON c.CourseID cs.CourseID GROUP BY c.CourseName ORDER BY NumberOfStudents DESC;查询每个年级学生选课的总学分和平均学分同时显示每个年级选课总学分最高的学生信息。截图代码SELECT s.Grade, SUM(c.Credits) AS TotalCredits, AVG(c.Credits) AS AvgCredits, MAX(s.Name) AS TopStudent FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID GROUP BY s.Grade ORDER BY TotalCredits DESC, AvgCredits DESC;查询每个教师所教课程的总选课人数和平均选课时间并显示选课人数最多的教师信息。截图代码SELECT c.Teacher, COUNT(cs.StudentID) AS TotalStudents, AVG(DATEDIFF(MINUTE, 0, cs.SelectionTime)) AS AvgSelectionTime, MAX(c.CourseName) AS TopCourse FROM Courses c JOIN CourseSelection cs ON c.CourseID cs.CourseID GROUP BY c.Teacher ORDER BY TotalStudents DESC, AvgSelectionTime DESC;嵌套查询查询选课人数超过1的课程名称截图代码SELECT CourseName FROM Courses WHERE CourseID IN ( SELECT CourseID FROM CourseSelection GROUP BY CourseID HAVING COUNT(StudentID) 1 );查询选课人数超过1的课程名称及其教师信息同时显示这些课程的选课学生信息截图代码SELECT c.CourseName, c.Teacher, s.StudentID, s.Name FROM Courses c JOIN CourseSelection cs ON c.CourseID cs.CourseID JOIN Students s ON cs.StudentID s.StudentID WHERE c.CourseID IN ( SELECT CourseID FROM CourseSelection GROUP BY CourseID HAVING COUNT(StudentID) 1 ) ORDER BY c.CourseName, s.Name;查询每个年级选课人数最多的学生及其详细选课信息包括课程名称、教师和选课时间。截图代码SELECT s.StudentID, s.Name, s.Grade, c.CourseName, c.Teacher, cs.SelectionTime FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID WHERE s.StudentID IN ( SELECT TOP 1 WITH TIES s1.StudentID FROM Students s1 JOIN CourseSelection cs1 ON s1.StudentID cs1.StudentID GROUP BY s1.StudentID, s1.Grade ORDER BY COUNT(cs1.CourseID) DESC ) ORDER BY s.Grade, s.Name;查询每个课程选课人数超过平均选课人数的学生及其详细选课信息包括学生姓名、年级和选课时间。截图代码SELECT c.CourseName, s.StudentID, s.Name, s.Grade, cs.SelectionTime FROM Courses c JOIN CourseSelection cs ON c.CourseID cs.CourseID JOIN Students s ON cs.StudentID s.StudentID WHERE c.CourseID IN ( SELECT cs1.CourseID FROM CourseSelection cs1 GROUP BY cs1.CourseID HAVING COUNT(cs1.StudentID) ( SELECT AVG(StudentCount) FROM ( SELECT COUNT(cs2.StudentID) AS StudentCount FROM CourseSelection cs2 GROUP BY cs2.CourseID ) avgStudents ) ) ORDER BY c.CourseName, s.Name;集合查询查询同时选了数学和物理课程的学生ID截图代码SELECT a.StudentID FROM CourseSelection a JOIN Courses b ON a.CourseID b.CourseID AND b.CourseName 数学 INTERSECT SELECT c.StudentID FROM CourseSelection c JOIN Courses d ON c.CourseID d.CourseID AND d.CourseName 物理;查询选了数学但没选物理的学生ID截图代码SELECT a.StudentID FROM CourseSelection a JOIN Courses b ON a.CourseID b.CourseID AND b.CourseName 数学 EXCEPT SELECT c.StudentID FROM CourseSelection c JOIN Courses d ON c.CourseID d.CourseID AND d.CourseName 物理;查询选了 数学和 物理课程的学生及其选课详细信息包括学生姓名、年级和选课时间。截图代码SELECT s.StudentID, s.Name, s.Grade, cs.CourseID, c.CourseName, cs.SelectionTime FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID WHERE s.StudentID IN ( SELECT cs1.StudentID FROM CourseSelection cs1 JOIN Courses c1 ON cs1.CourseID c1.CourseID AND c1.CourseName 数学 INTERSECT SELECT cs2.StudentID FROM CourseSelection cs2 JOIN Courses c2 ON cs2.CourseID c2.CourseID AND c2.CourseName 物理 ) ORDER BY s.Name, cs.SelectionTime;查询选了 数学但没选 物理的学生及其选课详细信息包括学生姓名、年级和选课时间。截图代码SELECT s.StudentID, s.Name, s.Grade, cs.CourseID, c.CourseName, cs.SelectionTime FROM Students s JOIN CourseSelection cs ON s.StudentID cs.StudentID JOIN Courses c ON cs.CourseID c.CourseID WHERE s.StudentID IN ( SELECT cs1.StudentID FROM CourseSelection cs1 JOIN Courses c1 ON cs1.CourseID c1.CourseID AND c1.CourseName 数学 EXCEPT SELECT cs2.StudentID FROM CourseSelection cs2 JOIN Courses c2 ON cs2.CourseID c2.CourseID AND c2.CourseName 物理 ) ORDER BY s.Name, cs.SelectionTime;收获与体会在进行复杂的SQL查询实验中我深入探索了单表查询、多表查询、分组查询、嵌套查询以及集合查询等多种高级查询技术。这一过程不仅加深了我对SQL语言的理解也让我体会到了其在实际应用中的强大和灵活性。在单表查询中通过对学生表和课程表的深入分析和操作我认识到单表查询虽然相对简单但其应用却十分广泛。通过查询学生的平均选课数量、每个年级中选课最多的学生姓名等信息我学会了如何在同一个表中利用聚合函数和子查询来获取更为详细和有用的数据。通过这个过程我意识到在设计查询时需要充分考虑表结构和数据分布这样才能设计出高效且有针对性的SQL语句。在多表查询中我进一步理解了关系数据库的核心思想——表之间的关联。在处理学生和课程表的联合查询时我使用了JOIN操作来关联学生的选课信息。这一过程中我不仅学习了INNER JOIN、LEFT JOIN等不同类型的连接方式还体会到合理的表连接可以极大地简化查询操作并提高查询效率。例如通过查询每个学生的选课详情和总学分我学会了如何在多表连接中使用窗口函数如SUM() OVER()来进行复杂的数据计算。这不仅提高了查询的灵活性也使得查询结果更加直观和有用。分组查询则让我理解了如何对数据进行分类和聚合。在这一部分我通过GROUP BY语句对不同年级的学生进行分组并计算他们的总学分和平均学分。这个过程中我使用了HAVING子句来过滤分组后的数据确保查询结果更符合实际需求。通过这样的实践我体会到分组查询在数据分析中的重要性尤其是在需要对大量数据进行汇总和统计时分组查询是非常高效和实用的工具。嵌套查询部分是我认为最具挑战性的部分之一。在这里我使用子查询来实现更加复杂的数据筛选和处理。例如通过查询每个年级选课人数最多的学生及其详细选课信息我学会了如何在主查询中嵌套子查询来筛选特定的数据集。嵌套查询的使用虽然增加了查询的复杂度但也使得查询更加灵活和强大能够解决一些常规查询无法处理的问题。这让我意识到在面对复杂的数据分析任务时嵌套查询是不可或缺的利器。最后集合查询部分让我深入理解了SQL中的集合操作。在这一部分我通过INTERSECT和EXCEPT操作来查询同时选了“数学”和“物理”课程的学生或者选了“数学”但没选“物理”的学生。这种查询方式不仅简化了复杂的逻辑条件也使得查询结果更加明确和直观。在实际应用中集合查询可以有效解决多个数据集之间的交集和差集问题是一种非常实用的高级查询技巧。通过这次SQL查询实验我对SQL语言有了更加全面和深入的理解。SQL不仅是一种查询语言更是数据处理和分析的强大工具。不同的查询方式各有其特点和优势通过灵活运用这些查询技巧可以解决各种复杂的数据处理和分析问题。在实际应用中我们需要根据具体需求选择合适的查询方式既要保证查询的准确性和完整性又要考虑查询的效率和性能。这次实验还让我认识到熟练掌握SQL查询技巧需要不断地实践和积累经验。每一次查询操作都是对SQL知识的巩固和深化。面对不断变化的数据和需求我们需要保持学习和探索的态度不断提升自己的SQL技能以应对各种复杂的数据处理任务。总的来说这次SQL查询实验不仅提高了我的SQL技能也增强了我对数据库系统的整体理解。通过实践我认识到SQL的强大和灵活性同时也体会到设计高效查询的重要性。在未来的工作中我将继续深入学习和应用SQL努力提升自己的数据处理和分析能力为实际问题的解决提供更加高效和有力的支持。