目录
一、实验名称
学生成绩管理系统的数据库设计和数据操作
二、实验目的
掌握需求分析的方法，数据库概念结构设计、逻辑结构设计方法、物理结构设计的方法，
熟练掌握在 SQL Server 环境下的数据定义、数据查询、数据更新、数据控制的实现。
三、实验内容
1）采用面向对象方法对系统进行需求分析。
2）设计数据库的概念结构。
3）设计数据库的逻辑结构。
4）设计数据库的物理结构。
5）建立数据库、录入数据，并做指定的数据操作。
四、实验任务
学生成绩管理系统涉及教学系、班级、专业、学生、课程几个实体集。教学系从系编号、
系名称两个方面刻画。班级从班级编号、班级名称两个方面刻画。专业从专业编号、专业名
称两个方面刻画。学生从学号、姓名、性别，年龄、班级、专业、系别几个方面刻画。课程
从课程号、课程名、课程性质（课程性质指公共必修课、公共选修课、专业必修课、专业选
修课）、学时、学分、开课学期几个方面刻画。应用语义为：每个教学系有多个班级，每个班
级只属于一个教学系；每个教学系开设多个专业，每个专业只由一个教学系开设；每个专业
有多个班级，每个班级只属于一个专业；每个班级有多名学生，每个学生只属于一个班级；
一个专业开设多门课程，一门课程只属于一个专业；一个班级开设本专业的所有必修课，但
选修课由学生自选，一个学生可修多门课，一门课可由多名学生所修。
该系统要具有如下功能：
（1）学生基本信息的输入
（2）课程基本信息的输入
（3）修课及成绩信息的输入
输入指定班级指定课程的成绩。
（4）学生基本信息的修改
按学号修改指定学生的基本信息。
（5）课程基本信息的修改
按课程号修改指定课程的基本信息。
（6）修课及成绩信息的修改
按学号和课程名修改指定学生所修指定课程的成绩。
（7）学生基本信息的删除
按学号删除指定学生的基本信息及修课信息。注意删除某一个学生的基本信息后，该学
生的修课信息也要删除。
（8）课程基本信息的删除
按课程号删除指定课程的基本信息。注意删除某一门课程的基本信息后，所有选修该课
程的信息都必须删除。
（9）修课及成绩信息的删除
按学号和课程名删除指定学生所修指定课程及成绩信息。
（10）学生基本信息的查询
① 按学号查询指定学生的基本信息。
② 按姓名查询指定学生的基本信息。
（11）课程基本信息的查询
① 按课程号查询指定课程的基本信息。
② 按课程名查询指定课程的基本信息。
（12）修课及成绩信息的查询
① 按学号和课程名查询指定学生所修指定课程的成绩和学分绩点。
② 按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。
③ 按班级和课程名查询指定班级所有学生选修指定课程的成绩，查询结果以学号、姓名、
成绩、学分绩点的形式显示。
（13）班级信息的设置
设置班级编号、班级名称，具有填加、删除、修改功能。
（14）专业信息的设置
设置专业编号、专业名称，具有填加、删除、修改功能。
（15）系别信息的设置
设置系别编号、系别名称，具有填加、删除、修改功能。
要求完成以下题目：
1、针对以上的需求运用面向对象的方法进行需求分析。
2、设计数据库的概念结构。
先设计初步的 E-R 图，再对初步的 E-R 图进行优化，得到基本的 E-R 图。要求所建立的
概念结构能真实反映上述的应用语义。
3、设计数据库的逻辑结构
先将基本的 E-R 图转换为关系模型，在将能合并的关系模式进行合并。要求所建立的关
系模式至少达到 3NF 要求。
4、设计数据库的物理结构
① 确定关系
确定每个关系模式的关系，包括关系名、字段名、字段的数据类型、字段的宽度。
②确定索引
根据系统的功能需求，确定合适的索引。
③确定存储安排
确定数据库文件、日志文件、数据库备份的存储位置。
5、建立数据库
根据设计的数据库物理结构，在 SQL Server 环境下建立数据库，包括建立基本表、索
引。要求建立基本表时必须确定完整性约束条件。
6、建立视图
建立视图，视图名为“学生成绩”，包含有：学号、姓名、班级、课程名、成绩、学分绩
点。
7、录入数据
在每个基本表中录入一批数据。
8、数据查询
（1）给定学号，按学号查询指定学生的基本信息。
（2）给定姓名，按姓名查询指定学生的基本信息。
（3）给定课程号，按课程号查询指定课程的基本信息。
（4）给定课程名，按课程名查询指定课程的基本信息。
（5）给定学号和课程名，按学号和课程号查询指定学生所修指定课程的成绩和学分绩点。
（6）给定学号，按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使
用所建立的“学生成绩” 视图。
（7）给定班级和课程名，按班级和课程号查询指定班级所有学生选修指定课程的成绩，
查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩” 视图。
（8）查询每个学生的学分绩点的总和及平均学分绩点。
9、数据更新
（1）插入一个学生的基本信息。
（2）插入一门课程的基本信息。
（3）插入一个学生某一门课的成绩。
（4）给定学号，按学号修改指定学生的基本信息。
（5）给定课程号，按课程号修改指定课程的基本信息。
（6）给定学号和课程名，按学号和课程名修改指定学生所修指定课程的成绩。
（7）给定学号，按学号删除指定学生的基本信息及修课信息。
（8）给定学号和课程名，按学号和课程名删除指定学生所修指定课程及成绩信息。
10、数据控制
先新建几个不同的数据库用户，给他们赋予不同的权限，然后自己以这些不同的用户登
录 SQL Server，比较操作权限的不同。
--局部E-R图
--整体E-R图
--关系模式
教学系（系编号、系名称）
班级（班级编号、班级名称）
专业（专业编号、专业名称）
学生（学号、姓名、性别、年龄、班级、专业、系别）
课程（课程号、课程名、学时、学分、开课学期 ）
选课（学号、课程号、成绩）
--创建学生管理系统
create database 学生管理系统;
--创建教学系表
create table 教学系 (
系编号 smallint not null,
系名称 char(10) null,
constraint PK_教学系 primary key (系编号)
)
go
--创建专业表
create table 专业 (
专业编号 smallint not null,
系编号 smallint null,
专业名称 char(10) null,
constraint PK_专业 primary key (专业编号)
)
go
--创建班级表
create table 班级 (
班级编号 smallint not null,
专业编号 smallint null,
班级名称 char(10) null,
constraint PK_班级 primary key (班级编号)
)
go
--创建学生表
create table 学生 (
学号 smallint not null,
姓名 char(10) null,
性别 char(2) null,
年龄 smallint null,
班级编号 smallint null,
专业编号 smallint null,
系编号 smallint null,
constraint PK_学生 primary key (学号)
)
go
--创建课程表
create table 课程 (
课程号 smallint not null,
课程名 char(10) null,
学时 smallint null,
学分 smallint null,
开课学期 char(10) null,
constraint PK_课程 primary key (课程号)
)
go
--创建选修课表
create table 选修课 (
学号 smallint not null,
课程号 smallint not null,
成绩 decimal(4,2) null,
绩点 decimal(4,2) null,
负责人 char(10) null,
constraint PK_选修课 primary key (学号, 课程号)
)
go
alter table 选修课 add constraint PK_选修课 primary key(学号, 课程号)
--创建必修课表
create table 必修课 (
学号 smallint not null,
课程号 smallint not null,
成绩 decimal(4,2) null,
绩点 decimal(4,2) null,
constraint PK_必修课 primary key (学号, 课程号)
)
go
alter table 必修课 add constraint PK_必修课 primary key(学号, 课程号)
--添加外键约束
alter table 专业
add constraint FK_专业_REFERENCE_教学系 foreign key (系编号)
references 教学系 (系编号)
go
alter table 学生
add constraint FK_学生_REFERENCE_班级 foreign key (班级编号)
references 班级 (班级编号)
go
alter table 必修课
add constraint FK_必修课_REFERENCE_课程 foreign key (课程号)
references 课程 (课程号)
go
alter table 必修课
add constraint FK_必修课_REFERENCE_学生 foreign key (学号)
references 学生 (学号)
go
alter table 班级
add constraint FK_班级_REFERENCE_专业 foreign key (专业编号)
references 专业 (专业编号)
go
alter table 选修课
add constraint FK_选修课_REFERENCE_课程 foreign key (课程号)
references 课程 (课程号)
go
alter table 选修课
add constraint FK_选修课_REFERENCE_学生 foreign key (学号)
references 学生 (学号)
go
--触发器
--删除学生信息同时删掉选课信息
create trigger tri_student
on [学生]
after delete
as
delete from [选修课]
where 学号 in
(select 学号 from deleted)
create trigger tri_student1
on [学生]
after delete
as
delete from [必修课]
where 学号 in
(select 学号 from deleted)
create trigger tri_course
on [课程]
after delete
as
delete from [选修课]
where 课程号 in
(select 课程号 from deleted)
create trigger tri_course1
on [课程]
after delete
as
delete from [必修课]
where 课程号 in
(select 课程号 from deleted)
--创建索引
create unique index c1 on 学生(学号 ASC);
create unique index c2 on 课程(课程号 ASC,学分 DESC);
--院系基本信息输入
insert into 教学系 values (01,'计算机系'),
(02,'中文系'),
(03,'体育教育系'),
(04,'影视传媒系'),
(05,'工程管理系')
--专业基本信息输入
insert into 专业 values(1,01,'计算机专业'),
(2,01,'信计专业')
insert into 专业 values(3,02,'汉语言专业'),
(4,02,'历史专业')
insert into 专业 values(5,03,'武术专业'),
(6,03,'运训专业')
insert into 专业 values(7,04,'播音专业'),
(8,04,'配音专业')
insert into 专业 values(9,05,'土木专业'),
(10,05,'测绘专业')
--班级基本信息输入
insert into 班级 values(2101,1,'计本2101'),
(2102,1,'计本2102'),
(2103,2,'信计2103'),
(2104,2,'信计2104')
insert into 班级 values(2105,3,'汉语言2105'),
(2106,3,'汉语言2106'),
(2107,4,'历史2107'),
(2108,4,'历史2108')
insert into 班级 values(2109,5,'武术2109'),
(2110,5,'武术2110'),
(2111,6,'运训2111'),
(2112,6,'运训2112')
insert into 班级 values(2113,7,'播音2113'),
(2114,7,'播音2114'),
(2115,8,'配音2115'),
(2116,8,'配音2116')
insert into 班级 values(2117,9,'土木2117'),
(2118,9,'土木2118'),
(2119,10,'测绘2119'),
(2120,10,'测绘2120')
--学生基本信息输入
insert into 学生 values(21001,'张一','男',21,2101,1,01),
(21002,'张二','女',19,2102,1,01),
(21003,'张三','男',21,2105,3,02),
(21004,'张四','男',22,2107,4,02),
(21005,'张五','女',19,2110,5,03),
(21006,'张六','女',20,2118,9,05),
(21007,'张七','男',21,2113,7,04),
(21008,'张八','男',18,2111,6,03),
(21009,'张九','女',20,2114,7,04),
(21010,'张十','男',22,2116,8,04)
insert into 学生 values(21011,'甜甜','女',20,2101,1,01),
(21012,'壮壮','男',21,2101,1,01),
(21013,'李丽','女',23,2101,1,01),
(21014,'王萍','女',19,2101,1,01)
--课程基本信息输入
insert into 课程 values(10001,'高等数学',20,5,'大一上学期'),
(10002,'高等数学',20,5,'大二上学期'),
(10003,'Java',30,5,'大二上学期'),
(10004,'中国近代史',35,4,'大一上学期'),
(10005,'心理健康',15,3,'大一上学期'),
(10006,'大学英语',30,4,'大一上学期'),
(10007,'大学英语',30,4,'大二下学期'),
(10008,'大学英语',30,3,'大一下学期'),
(10009,'大学英语',30,3,'大二上学期'),
(10010,'C语言',40,6,'大一上学期'),
(10011,'数据结构',30,5,'大一下学期'),
(10012,'数据库',30,4,'大二下学期'),
(10013,'工程建筑',40,5,'大一下学期'),
(10014,'工程测绘',30,3,'大一上学期'),
(10015,'播音主持',36,2,'大一上学期'),
(10016,'配音',20,4,'大一上学期'),
(10017,'汉语言文学',40,5,'大一上学期'),
(10018,'运动康复',20,1,'大一下学期'),
(10019,'中国武术',35,3,'大二上学期'),
(10020,'计算机组成',30,3,'大二下学期')
insert into 课程 values(10021,'心理健康',15,3,'大二上学期');
--选修课及成绩信息输入
insert into 选修课 values(21001,10005,95,'马'),
(21002,10001,90,'钱'),
(21003,10020,75,'孙'),
(21004,10006,70,'李'),
(21005,10007,80,'周'),
(21006,10002,85,'吴'),
(21007,10021,80,'郑'),
(21008,10005,78,'马'),
(21009,10019,73,'陈'),
(21010,10020,65,'王')
update 选修课 set 绩点 = 4 where 成绩>=90
update 选修课 set 绩点 = 3.5 where 成绩<90 and 成绩>=80
update 选修课 set 绩点 = 3 where 成绩<80 and 成绩>=70
update 选修课 set 绩点 = 2.5 where 成绩<70 and 成绩>=60
update 选修课 set 绩点 = 0 where 成绩<60
--必修课及成绩信息输入
insert into 必修课 values(21001,10003,75),
(21001,10010,85),
(21001,10012,65),
(21002,10020,79),
(21003,10017,59),
(21003,10004,79),
(21004,10004,89),
(21005,10019,90),
(21005,10018,80),
(21006,10013,75),
(21006,10014,60),
(21007,10016,86),
(21008,10018,79),
(21009,10015,49),
(21010,10016,70)
insert into 必修课 values(21011,10010,95,4),
(21012,10010,94,4),
(21013,10010,78,3),
(21014,10010,80,3.5)
update 必修课 set 绩点 = 4 where 成绩>=90
update 必修课 set 绩点 = 3.5 where 成绩<90 and 成绩>=80
update 必修课 set 绩点 = 3 where 成绩<80 and 成绩>=70
update 必修课 set 绩点 = 2.5 where 成绩<70 and 成绩>=60
update 必修课 set 绩点 = 0 where 成绩<60
--创建学生成绩视图学号、姓名、班级、课程名、成绩、学分、绩点
create view view_学生成绩1
AS
select 学生.学号,
学生.姓名,
班级.班级名称,
课程.课程名,
选修课.成绩 选修课成绩,
课程.学分,
选修课.绩点 as 选修课绩点
from 学生,选修课,课程,班级
where 学生.学号=选修课.学号 and 选修课.课程号 = 课程.课程号 and 班级.班级编号 = 学生.班级编号
create view view_学生成绩2
AS
select 学生.学号,
学生.姓名,
班级.班级名称,
课程.课程名,
必修课.成绩 必修课成绩,
课程.学分,
必修课.绩点 as 必修课绩点
from 学生,必修课,课程,班级
where 学生.学号=必修课.学号 and 必修课.课程号 = 课程.课程号 and 班级.班级编号 = 学生.班级编号
create view view_学生成绩
AS
select 学号,
姓名,
班级名称,
课程名,
选修课成绩 as 成绩,
学分,
选修课绩点 as 绩点,
学分*选修课绩点 as 学分绩点
from view_学生成绩1
union
select 学号,
姓名,
班级名称,
课程名,
必修课成绩 as 成绩,
学分,
必修课绩点 as 绩点,
学分*必修课绩点 as 学分绩点
from view_学生成绩2
select * from view_学生成绩
--数据查询
--给定学号21001，按学号21001查询指定学生的基本信息
select *
from 学生
where 学号 = 21001;
--给定姓名张九，按姓名张九查询指定学生的基本信息
select *
from 学生
where 姓名 = '张九';
--给定课程号10010，按课程号10010查询指定课程的基本信息
select *
from 课程
where 课程号 = 10010;
--给定课程名Java，按课程名Java查询指定课程的基本信息
select *
from 课程
where 课程名 = 'Java';
--给定学号21002和课程号10020，按照学生学号21002和课程号10020查询指定学生所修指定课程的成绩和学分绩点
select 成绩,学分绩点
from view_学生成绩
where 学号 = 21002 and view_学生成绩.课程名 = (select 课程名
from 课程
where 课程号 = 10020);
--给定学号21001，按照学号21001查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使用所建立的“学生成绩”视图
select 课程名,成绩,学分绩点
from view_学生成绩
where 学号 = 21001;
--给定班级编号2101和课程号10010，按照班级编号2101和课程号10010查询指定班级所有学生选修指定课程的成绩，
--查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩”视图
select 学号,姓名,成绩,学分绩点
from view_学生成绩
where view_学生成绩.课程名 = (select 课程名
from 课程
where 课程号 = 10010)
and
view_学生成绩.班级名称 = (select 班级名称
from 班级
where 班级编号 = 2101);
--查询每个学生的学分绩点的总和及平均学分绩点
select 姓名,sum(学分绩点)as 总学分绩点,avg(学分绩点) as 平均学分绩点
from view_学生成绩 group by 姓名
order by 总学分绩点 desc,平均学分绩点 desc;
--数据更新
--插入一个学生的基本信息
insert into 学生 values(21015,'娅娅','女',20,2102,1,01);
select * from 学生 where 学号 = 21015;
--插入一门课程的基本信息
insert into 课程 values(10022,'计网原理',20,5,'大二下学期');
select * from 课程 where 课程号 = 10022;
--插入一个学生某一门课的成绩
insert into 选修课 values(21015,10022,89,3.5,'高');
select * from 选修课 where 学号 = 21015 and 课程号 = 10022;
--给定学号21001，按学号21001修改指定学生的年龄基本信息
select * from 学生 where 学号 = 21001;
update 学生 set 年龄 = 18 where 学号 = 21001;
--给定课程号10001，按课程号10001修改指定课程10001的学时基本信息
select * from 课程 where 课程号 = 10001;
update 课程 set 学时 = 30 where 课程号 = 10001;
--给定学号21006和课程名工程测绘，按学号21006和课程名工程测绘修改指定学生21006所修指定工程测绘课程的成绩
select 成绩 from view_学生成绩 where 学号 = 21006 and 课程名 = '工程测绘';
update 必修课 set 成绩 = 69 where 学号 = 21006 and 课程号 = (select 课程号 from 课程 where 课程名 = '工程测绘');
--给定学号21015，按学号21015删除指定学生的基本信息及修课信息
select * from 学生 where 学号 = 21015;
select * from 必修课 where 学号 = 21015
union
select 学号,课程号,成绩,绩点 from 选修课 where 学号 = 21015;
delete from 学生 where 学号 = 21015;
select * from 学生
--给定学号21007和课程名心理健康，按学号21014和课程名心理健康删除指定学生所修指定课程及成绩信息
select * from 必修课 where 学号 = 21007
union
select 学号,课程号,成绩,绩点 from 选修课 where 学号 = 21007;
select * from 课程 where 课程号 in(10016,10021);
delete from 课程 where 课程名 = '心理健康';
--数据控制
--创建LaiPign用户赋予查学生表权限
SP_addlogin 'LaiPing','123456';
SP_adduser 'LaiPing';
grant select on 学生 to LaiPing with grant option;
--创建LiLi用户赋予删除选修课表权限
SP_addlogin 'LiLi','123456';
SP_adduser 'LiLi';
grant delete on 选修课 to LiLi with grant option;
grant select on 选修课 to LiLi with grant option;
--创建LiPign用户赋予插入选修课表权限
SP_addlogin 'LiPign','123456';
SP_adduser 'LiPign';
grant insert on 选修课 to LiPign ;
grant select on 选修课 to LiPign ;
select * from 学生;
select * from 选修课;
delete from 选修课 where 学号 = 21006
insert into 选修课 values (21001,10019,80,3,'五月')
select * from 选修课;
扫一扫
1257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
