数据库原理实践1
数据库原理实践(2024 年春)
目录
实验目的
数据库原理是一门理论和实践性很强的课程。 本课程要求学生不仅要掌握数据库的基本 原理，而且还要掌握一门数据库系统的应用技能。因此上机实验是数据库课程的重要环节。学生学习这门课程后要求掌握两方面内容：数据库设计和上机实践。
本课程实验的目的如下：
1. 通过上机实验，加深对数据库系统理论知识的理解，增强对大型数据库的感性认识和实际操作能力；
2. 通过使用具体的 RDBMS(如SQL Server或MySQL)，了解一种实际的数据库管理系统，并掌握其操作技术。
3. 学习使用 SQL 语言，加深对 SQL 语言的认识和理解，提高 SQL 语言的实际应用能力；
4. 通过给出一个具体数据库应用实例，使学生们了解各关系（表）间的联系，并判断其是否达到 3NF 模式.并对这个数据库编程以达到利用高级语言处理数据库的目的。
适用专业
信息安全专业
实验学时数
16 学时
实验总要求
某一个学校有若干个系，每个系有若干个学生，每个学生可以选修多门课程， 且每门 课程有相应的考试得分， 一门课程也可以由多名同学选修， 一个老师只讲授一门课程， 而 同一门课程可由不同老师讲授。为便于学生信息及学生成绩的有效管理，需要开发一个学生成绩管理系统。
此数据库的数据字典定义如下：
（1） 学生（Student）：学号（SNO） 、姓名（SNAME） 、性别（SSEX） 、年龄（SAGE）、系别（SDEPT）；
（2）课程（Course）：课程号（CNO）、课程名（CNAME）、学时（CTIME）、学分（CCREDIT）
（3）教师（Teacher）：教师编号（TNO）、教师名（TName）、性别（TSex）、所在系（TDept）、课程号（CNO）。
（4）成绩（Score）：学号（SNO）、课程号（CNO）、分数（Grade）；
实验内容及总要求如下：
1.根据以上数据字典，画出该数据库ER图，并将ER图转换成逻辑关系模式，保证每个关系均规范到 3NF,最后在SQL Server或MySQL中实现关系模式的存储，确定主码、外部码、索引等。
2.利用查询编辑器完成 SQL 语句操作。
3.实现 C/S 结构的数据库编程。
4.实现 B/S 结构的数据库编程。
实验室环境
数据库原理实验的实验室提供如下环境：
服务器端：Win11操作系统、数据库服务器（SQL Server和MySQL）、 Web 服务器、VS.NET 开发环境。客户端：IE 、VS.NET 开发环境。
实验一： 数据库的建立及数据维护（4 学时）
1.学会使用对象资源管理器创建数据库、 创建基本表和查看数据库属性。
2.学会使用对象资源管理器向数据库输入数据，修改数据， 删除数据的操作。
3.在 SQL Server 查询编辑器中完成复杂查询及视图定义。
实验环境： Windows 11、MySQL8.0、DATAGRIP
1 、 根据以上数据字典，画出该数据库的 ER 图，完成数据库的概念结构设计；
2 、 将 ER 图转换成关系模型，判断关系模型中的各个关系（表）是第几范式，如果没有达到第三范式或 BC 范式， 请进行规范化。完成数据库的逻辑结构设计。
3 、 通过对象资源管理器实现关系模式的存储，包括确定主码、外部码等。
Course表：
Score表：
Student表：
Teacher表：
4 、 按照给出的示例数据完成数据的录入。
Score表、Course表：
Teacher表、Student表：
5 、 打开数据库 SQL Server 的查询编辑器，用 SQL 语句完成以下查询。并通过实验结果验证查询语句的正确性， 将每个 SQL 语句及结果存盘，以备老师检查。
（1） 求全体学生的学号、姓名和出生年份。
# （1） 求全体学生的学号、姓名和出生年份。
select Sno, sname, year(current_date) - sage as '出生年份'
from student;
（2） 求每个系的学生总人数并按降序排列。
# （2） 求每个系的学生总人数并按降序排列。
select sdept, count(*) as count
from student
group by Sdept
order by count desc;
（3） 求选修了课程号为 002 或 003 的学生的学号、课程名和成绩。
# （3） 求选修了课程号为 002 或 003 的学生的学号、课程名和成绩。
select score.sno, course.cname, score.grade
from course,
score
where score.Cno = course.cno
and (score.Cno = '002' or score.Cno = '003');
（4） 检索选修某课程的学生人数多于 3 人的教师姓名。
# （4） 检索选修某课程的学生人数多于 3 人的教师姓名。
-- select Cno from score group by Cno having count(sno) > 3;
select Tname
from teaccher
where Cno in (select Cno from score group by Cno having count(sno) > 3);
（5） 查询所有未选课程的学生姓名和所在系。
# （5） 查询所有未选课程的学生姓名和所在系。
-- select distinct sno from score;
select Sname, Sdept
from student
where Sno not in (select distinct sno from score);
（6） 求每个同学的课程成绩的最高分，查询结果项包括：学生姓名、 课程号及最高分。
# （6） 求每个同学的课程成绩的最高分，查询结果项包括：学生姓名、课程号及最高分。
-- select sno, max(score.Grade)from score group by sno;
select student.Sname, score.Cno, score.Grade
from score,
student
where (score.Sno, score.Grade) in (select sno, max(score.Grade) from score group by sno)
and student.Sno = score.Sno;
（7） 求所有讲授数据结构课程的教师姓名
# （7） 求所有讲授数据结构课程的教师姓名
-- select cno from course where Cname = '数据结构';
select Tname
from teaccher
where Cno = (select cno from course where Cname = '数据结构');
- 查询所有选修了李正科老师的课程的学生信息
# （8） 查询所有选修了李正科老师的课程的学生信息
-- select Cno from teaccher where Tname = '李正科';
-- select Sno from score where Cno = ( select Cno from teaccher where Tname = '李正科' );
select *
from student
where Sno in (select Sno from score where Cno = (select Cno from teaccher where Tname = '李正科'));
（9） 新建一个关系表 S1(SNO,SNAME,SD,SA)，其字段类型定义与 Student 表中 的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。 将表 Student 中在计算机系‘CS’的学生数据插入到表 S1 中。
# （9） 新建一个关系表 S1(SNO,SNAME,SD,SA)，其字段类型定义与 Student 表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。将表 Student 中在计算机系‘CS’的学生数据插入到表 S1 中。
create table S1
(
Sno varchar(50) not null,
SNAME varchar(50) not null,
SD varchar(50) null,
SA int null,
constraint Sno
primary key (Sno)
);
-- select * from student where Sdept = 'CS';
insert into S1
select Sno, Sname, Sdept, Sage
from (select * from student where Sdept = 'CS') as CS_Student;
（10） 建立计算机系的学生的视图 STUDENT_CS。利用视图 STUDENT_CS ，将学生的 001 号课程的成绩提高 5 分。
# （10） 建立计算机系的学生的视图 STUDENT_CS。利用视图 STUDENT_CS，将学生的 001 号课程的成绩提高 5 分。
CREATE VIEW STUDENT_CS
AS
SELECT student.Sno , Sname , Cno , Grade
FROM Student,score
WHERE Sdept= 'CS'
and student.Sno = score.Sno;
update student_cs
set grade = grade + 5
where student_cs.cno = '001';
（11） 创建一个带参数的存储过程， 将数据结构课程的成绩在 Low 与 Hign 分数段的学生信息提取出来，查询后的结果包括：姓名、所在的系、成绩。
各关系表的结构和示例数据如下：
create
definer = root@localhost procedure `11`(IN LOW int, IN HIGH int)
BEGIN
select Sname, Sdept, Grade
from Student,
Score
where Score.Sno = Student.Sno
and Score.Cno = (select Cno
from Course
where Cname = '数据结构')
and Score.Grade between LOW and HIGH;
END;
浙公网安备 33010602011771号