实验 5： 综合实验 --数据库应用系统的数据库设计

实验 5： 综合实验 --数据库应用系统的数据库设计

一、实验名称

学生成绩管理系统的数据库设计和数据操作

二、实验目的

掌握需求分析的方法，数据库概念结构设计、逻辑结构设计方法、物理结构设计的方法，
熟练掌握在 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 环境下建立数据库，包括建立基本表、索
引。要求建立基本表时必须确定完整性约束条件。

create database XX
go
--教学系
use XX
create table Teach
(
tno int primary key ,--教学编号
tna char(20) not null --系名称
)
insert into Teach(tno,tna)
select'001','数学与计算机科学'union
select'002','经济与管理学院'union
select'003','人文'
--班级
create table Class
(
cno int primary key,--班级编号
cna char(20) not null, --班级名称
mno int not null,--每个班级只属于一个专业
tno int not null,--每个教学系只属于一个班级
)
insert into Class(cno,cna,mno,tno)
select'1','计算机2101','01','001'union
select'2','计算机2102','01','001'union
select'3','计算机2103','01','001'union
select'4','数学2101','02','001'union
select'5','数学2102','02','001'union
select'6','网络2101','03','001'union
select'7','网络2102','03','001'union
select'8','经济2101','04','002'union
select'9','经济2102','04','002'union
select'10','工商管理2101','05','002'union
select'11','汉语言文学2101','06','003'union
select'12','汉语言文学2102','06','003'
--专业
create table Major
(
mno int primary key,--专业编号
mna char(20)not null,--专业名称
tno int not null,--每个专业只能由一个教学系开设
)
insert into Major(mno,mna,tno)
select'01','计算机','001'union
select'02','数学','001'union
select'03','网络','001'union
select'04','经济学','002'union
select'05','工商管理','002'union
select'06','汉语言文学','003'
--学生
create table Student 
(
sno char(20) primary key,--学生编号
sna char(20) not null,--学生姓名
ssex char(10)  not null check(ssex='男'or ssex='女'),--学生性别
sage int not null,--学生年龄
cno int not null,--班级编号
cna char(20) not null,--班级名称
mno int not null,--专业编号
mna char(20),--专业名称
tno int not null,--系别
tna char(20)--系名称
)
insert into Student(sno,sna,ssex,sage,cno,cna,mno,mna,tno,tna)
select '2101','张铭','女',19,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2102','刘晨','男',20,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2103','刘文','女',19,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2104','马嘉祺','男',20,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2105','刘耀文','男',18,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2106','宋亚轩','男',19,'1','计算机2101','01','计算机','001','数学与计算机科学'union
select '2107','严浩翔','男',19,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2108','朱志鑫','男',18,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2109','张云夏','女',19,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2110','章丘月','男',20,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2111','郭明鑫','男',19,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2112','宋慧乔','女',20,'2','计算机2102','01','计算机','001','数学与计算机科学'union
select '2113','李佳怡','女',20,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2114','蔡慧如','男',22,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2115','姜超鹏','男',19,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2116','车闯梁','男',22,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2117','马文辛','男',19,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2118','周雨彤','女',21,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2119','鹿晗','男',18,'3','计算机2103','01','计算机','001','数学与计算机科学'union
select '2120','关晓彤','女',18,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2121','郑凯','男',23,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2122','朱墨含','男',21,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2123','李欣睿','女',21,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2124','李佳奇','男',20,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2125','马伯谦','男',20,'4','数学2101','02','数学','001','数学与计算机科学'union
select '2126','周树人','男',22,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2127','彭然然','男',21,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2128','王启元','男',20,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2129','张挺','男',19,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2130','芮琛','男',21,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2131','姜铭','女',18,'5','数学2102','02','数学','001','数学与计算机科学'union
select '2132','马言超','男',19,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2133','李想瑞','男',17,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2134','庞心如','女',23,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2135','林品如','女',24,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2136','刘文静','女',21,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2137','王世国','男',22,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2138','王礼记','男',21,'6','网络2101','03','网络','001','数学与计算机科学'union
select '2139','郭梦雪','女',21,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2140','王雪','女',21,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2141','段佳婿','男',21,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2142','周期问','男',21,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2143','张迪然','女',19,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2144','李文蒂','女',21,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2145','彭博燃','男',22,'7','网络2102','03','网络','001','数学与计算机科学'union
select '2146','刘文茜','女',22,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2147','赵丽颖','女',23,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2148','范冰冰','女',23,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2149','邓超','男',21,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2150','马继光','男',22,'8','经济2101','04','经济学','002','经济与管理学院'
select '2151','张金龙','男',23,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2152','吴京','男',23,'8','经济2101','04','经济学','002','经济与管理学院'union
select '2153','吴奇隆','男',25,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2154','朱元璋','男',26,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2155','马云','男',19,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2156','马化腾','男',19,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2157','张文涛','男',21,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2158','张鹏鲲','男',17,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2159','张晓亮','男',19,'9','经济2102','04','经济学','002','经济与管理学院'union
select '2160','马明心','女',22,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2161','马明宇','男',23,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2162','马天宇','男',22,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2163','周期为','男',21,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2164','邢晨羊','男',21,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2165','华晨宇','男',22,'10','工商管理2101','05','工商管理','002','经济与管理学院'union
select '2166','王俊凯','男',21,'11','汉语言文学2101','06','汉语言文学','003','人文学院'union
select '2167','王源','男',21,'11','汉语言文学2101','06','汉语言文学','003','人文学院'union
select '2168','易烊千玺','男',21,'11','汉语言文学2101','06','汉语言文学','003','人文学院'union
select '2169','彭于晏','男',22,'11','汉语言文学2101','06','汉语言文学','003','人文学院'union
select '2170','白敬亭','男',19,'11','汉语言文学2101','06','汉语言文学','003','人文学院'union
select '2171','陈净明','男',21,'12','汉语言文学2102','06','汉语言文学','003','人文学院'union
select '2172','陈长生','男',19,'12','汉语言文学2102','06','汉语言文学','003','人文学院'union
select '2173','陈宫菲','女',19,'12','汉语言文学2102','06','汉语言文学','003','人文学院'union
select '2174','秦始皇','男',19,'12','汉语言文学2102','06','汉语言文学','003','人文学院'union
select '2175','李世民','男',21,'12','汉语言文学2102','06','汉语言文学','003','人文学院'

--公共必修课
create table Class_pb
(
pbno char(20) primary key,--课程号
pbname char(20) not null,--课程名
pbtype char(20) not null check(pbtype='公共必修课'),--课程性质
pbTime int not null,--学时,单位为小时
pbCredit int not null,--学分
pbTerm char(5) not null check(pbTerm='春'or pbTerm='秋'),--开课学期
)
go
insert into Class_pb(pbno,pbname,pbtype,pbTime,pbCredit,pbTerm)
select'201','高数I','公共必修课',20,5,'春'union
select'202','高数II','公共必修课',20,5,'秋'union
select'203','大学英语I','公共必修课',16,3,'春'union
select'204','大学英语II','公共必修课',18,3,'秋'union
select'205','中国共产党党史','公共必修课',14,2,'春'
go
--公共选修课
create table Class_px
(
pxno char(20) primary key,--课程号
pxname char(20) not null,--课程名
pxtype char(20) not null check(pxtype ='公共选修课'),--课程性质
pxTime int not null,--学时,单位为小时
pxCredit int not null,--学分
pxTerm char(5) not null check(pxTerm='春'or pxTerm='秋'),--开课学期
)
go
insert into Class_px(pxno,pxname,pxtype,pxTime,pxCredit,pxTerm)
select'206','中国书法','公共选修课',13,2,'春'union
select'207','中国建筑','公共选修课',13,1,'春'
go
--专业必修课
create table Class_mb
(
mbno int primary key,--课程号
mbname char(20) not null,--课程名
mbtype char(20) not null check(mbtype='专业必修课'),--课程性质
mbTime int not null,--学时,单位为小时
mbCredit int not null,--学分
mbTerm char(5) not null check(mbTerm='春'or mbTerm='秋'),--开课学期
mno int not null ,--一门课程只属于一个专业
)
go
insert into Class_mb(mbno,mbname,mbtype,mbTime,mbCredit,mbTerm,mno)
select'101','数据库原理与应用','专业必修课',20,3,'春','01'union
select'102','JAVA','专业必修课',40,4,'秋','01'union
select'103','数学I','专业必修课',40,5,'春','02'union
select'104','网络I','专业必修课',30,3,'春','03'union
select'106','经济I','专业必修课',20,2,'春','04'union
select'107','管理','专业必修课',20,2,'春','05'union
select'108','汉语言','专业必修课',20,3,'春','06'
go
--专业选修课
create table Class_mx
(
mxno int primary key,--课程号
mxname char(20) not null,--课程名
mxtype char(20) not null check(mxtype ='专业选修课'),--课程性质
mxTime int not null,--学时,单位为小时
mxCredit int not null,--学分
mxTerm char(5) not null check(mxTerm='春'or mxTerm='秋'),--开课学期
mno int not null ,--一门课程只属于一个专业
)
go
insert into Class_mx(mxno,mxname,mxtype,mxTime,mxCredit,mxTerm,mno)
values('105','网络II','专业选修课',30,3,'秋','03')
go
--公共必修课成绩
create table Score_pb
(
sno char(20),
pbno char(20),
primary key(sno,pbno),--以公共课程号和学生编号为主码
pbgrade int,--公共课成绩
pbfj int,--学分绩点
)
go
insert into Score_pb(sno,pbno,pbgrade,pbfj)
select'2101','201',88,17union
select'2101','202',77,13union
select'2101','203',66,4union
select'2101','204',99,3union
select'2101','205',67,3union

select'2102','201',88,17union
select'2102','202',77,13union
select'2102','203',66,4union
select'2102','204',99,3union
select'2102','205',67,3union
select'2103','201',88,17union
select'2103','202',77,13union
select'2103','203',66,4union
select'2103','204',99,3union
select'2103','205',67,3union

select'2104','201',88,17union
select'2104','202',77,13union
select'2104','203',66,3union
select'2104','204',99,3union
select'2104','205',67,3union
select'2105','201',88,17union
select'2105','202',77,13union
select'2105','203',66,4union
select'2105','204',99,3union
select'2105','205',67,3union
select'2106','201',88,17union
select'2106','202',77,13union
select'2106','203',66,4union
select'2106','204',39,0union
select'2106','205',67,3union
select'2107','201',88,17union
select'2107','202',77,13union
select'2107','203',66,4union
select'2107','204',99,3union
select'2107','205',67,3union
select'2108','201',88,17union
select'2108','202',77,13union
select'2108','203',66,4union
select'2108','204',99,3union
select'2108','205',67,3union
select'2109','201',88,17union
select'2109','202',77,13union
select'2109','203',66,4union
select'2109','204',99,3union
select'2109','205',67,3union
select'2110','201',88,17union
select'2110','202',77,13union
select'2110','203',66,4union
select'2110','204',99,3union
select'2110','205',67,3union
select'2111','201',88,17union
select'2111','202',77,13union
select'2111','203',66,4union
select'2111','204',99,3union
select'2111','205',67,3union
select'2112','201',88,17union
select'2112','202',77,13union
select'2112','203',66,4union
select'2112','204',99,3union
select'2112','205',67,3union
select'2113','201',88,17union
select'2113','202',77,13union
select'2113','203',66,4union
select'2113','204',99,3union
select'2113','205',67,3union
select'2114','201',88,17union
select'2114','202',77,13union
select'2114','203',66,4union
select'2114','204',99,3union
select'2114','205',67,3union
select'2115','201',88,17union
select'2115','202',77,13union
select'2115','203',66,4union
select'2115','204',99,3union
select'2115','205',67,3union
select'2116','201',88,17union
select'2116','202',47,13union
select'2116','203',66,3union
select'2116','204',99,3union
select'2116','205',67,3union
select'2117','201',88,17union
select'2117','202',77,13union
select'2117','203',66,4union
select'2117','204',99,3union
select'2117','205',67,3union
select'2118','201',88,17union
select'2118','202',77,13union
select'2118','203',66,3union
select'2118','204',99,3union
select'2118','205',67,3union
select'2119','201',88,17union
select'2119','202',77,13union
select'2119','203',66,4union
select'2119','204',99,3union
select'2119','205',67,3union
select'2120','201',88,17union
select'2120','202',77,13union
select'2120','203',66,4union
select'2120','204',99,3union
select'2120','205',67,3union
select'2121','201',88,17union
select'2121','202',77,13union
select'2121','203',66,4union
select'2121','204',99,3union
select'2121','205',67,3union
select'2122','201',88,17union
select'2122','202',37,0union
select'2122','203',66,3union
select'2122','204',99,3union
select'2122','205',67,3union
select'2123','201',88,17union
select'2123','202',77,13union
select'2123','203',66,3union
select'2123','204',99,3union
select'2123','205',67,3union
select'2124','201',88,17union
select'2124','202',77,13union
select'2124','203',66,3union
select'2124','204',99,3union
select'2124','205',67,3union
select'2125','201',88,17union
select'2125','202',77,13union
select'2125','203',66,3union
select'2125','204',99,3union
select'2125','205',37,0union
select'2126','201',88,17union
select'2126','202',77,13union
select'2126','203',66,4union
select'2126','204',99,3union
select'2126','205',67,3union
select'2127','201',88,17union
select'2127','202',77,13union
select'2127','203',66,4union
select'2127','204',99,3union
select'2127','205',67,3union
select'2128','201',88,17union
select'2128','202',77,13union
select'2128','203',66,3union
select'2128','204',99,3union
select'2128','205',67,3union
select'2129','201',88,17union
select'2129','202',77,13union
select'2129','203',66,4union
select'2129','204',99,3union
select'2129','205',67,3union
select'2130','201',88,17union
select'2130','202',77,13union
select'2130','203',66,3union
select'2130','204',39,0union
select'2130','205',67,3union
select'2131','201',88,17union
select'2131','202',77,13union
select'2131','203',66,3union
select'2131','204',99,3union
select'2131','205',67,3union
select'2132','201',88,17union
select'2132','202',77,13union
select'2132','203',66,3union
select'2132','204',99,3union
select'2132','205',67,3union
select'2133','201',88,17union
select'2133','202',77,13union
select'2133','203',66,3union
select'2133','204',99,3union
select'2133','205',67,3union
select'2134','201',88,17union
select'2134','202',57,0union
select'2134','203',66,3union
select'2134','204',99,3union
select'2134','205',67,3union
select'2135','201',88,17union
select'2135','202',77,13union
select'2135','203',66,3union
select'2135','204',99,3union
select'2135','205',67,3union
select'2136','201',88,17union
select'2136','202',77,13union
select'2136','203',66,3union
select'2136','204',99,4union
select'2136','205',67,3union
select'2137','201',88,17union
select'2137','202',77,13union
select'2137','203',66,3union
select'2137','204',99,3union
select'2137','205',67,3union
select'2138','201',88,17union
select'2138','202',77,13union
select'2138','203',46,0union
select'2138','204',99,3union
select'2138','205',67,3union
select'2139','201',38,0union
select'2139','202',37,0union
select'2139','203',46,0union
select'2139','204',99,3union
select'2139','205',67,3union
select'2140','201',88,17union
select'2140','202',77,13union
select'2140','203',66,3union
select'2140','204',99,3union
select'2140','205',67,3union
select'2141','201',88,17union
select'2141','202',77,13union
select'2141','203',66,3union
select'2141','204',99,17union
select'2141','205',67,3union
select'2142','201',88,17union
select'2142','202',77,13union
select'2142','203',66,3union
select'2142','204',99,3union
select'2142','205',67,3union
select'2143','201',88,17union
select'2143','202',77,13union
select'2143','203',66,3union
select'2143','204',99,3union
select'2143','205',67,3union
select'2144','201',88,17union
select'2144','202',77,13union
select'2144','203',66,3union
select'2144','204',99,3union
select'2144','205',67,3union
select'2145','201',48,0union
select'2145','202',77,13union
select'2145','203',66,3union
select'2145','204',99,3union
select'2145','205',67,3union
select'2146','201',88,17union
select'2146','202',77,13union
select'2146','203',66,3union
select'2146','204',99,3union
select'2146','205',67,3union
select'2147','201',88,17union
select'2147','202',77,13union
select'2147','203',66,3union
select'2147','204',99,3union
select'2147','205',67,3union
select'2148','201',88,17union
select'2148','202',77,13union
select'2148','203',66,3union
select'2148','204',49,0union
select'2148','205',67,3union
select'2149','201',88,17union
select'2149','202',77,13union
select'219','203',66,3union
select'2149','204',99,3union
select'2149','205',67,3union
select'2150','201',88,17union
select'2150','202',77,13union
select'2150','203',66,3union
select'2150','204',99,3union
select'2150','205',67,3union
select'2151','201',88,17union
select'2151','202',77,13union
select'2151','203',66,3union
select'2151','204',99,3union
select'2151','205',67,3union
select'2152','201',88,17union
select'2152','202',77,13union
select'2152','203',66,3union
select'2152','204',99,3union
select'2152','205',67,3union
select'2153','201',88,17union
select'2153','202',77,13union
select'2153','203',26,0union
select'2153','204',99,3union
select'2153','205',67,3union
select'2154','201',88,17union
select'2154','202',77,13union
select'2154','203',66,3union
select'2154','204',99,3union
select'2154','205',67,3union
select'2155','201',88,17union
select'2155','202',77,13union
select'2155','203',66,3union
select'2155','204',99,3union
select'2155','205',67,3union
select'2156','201',88,17union
select'2156','202',77,13union
select'2156','203',36,0union
select'2156','204',99,3union
select'2156','205',67,3union
select'2157','201',88,17union
select'2157','202',77,13union
select'2157','203',66,3union
select'2157','204',99,3union
select'2157','205',67,3union
select'2158','201',88,17union
select'2158','202',77,13union
select'2158','203',66,3union
select'2158','204',99,3union
select'2158','205',67,3union
select'2159','201',88,3union
select'2159','202',77,3union
select'2159','203',46,0union
select'2159','204',99,3union
select'2159','205',67,3union
select'2160','201',88,17union
select'2160','202',77,3union
select'2160','203',66,3union
select'2160','204',99,3union
select'2160','205',67,3union
select'2161','201',88,17union
select'2161','202',77,13union
select'2161','203',66,3union
select'2161','204',99,3union
select'2161','205',57,3union
select'2162','201',88,17union
select'2162','202',77,13union
select'2162','203',66,3union
select'2162','204',99,3union
select'2162','205',67,3union
select'2163','201',88,17union
select'2163','202',77,13union
select'2163','203',66,3union
select'2163','204',99,3union
select'2163','205',67,3union
select'2164','201',88,17union
select'2164','202',57,0union
select'2164','203',66,3union
select'2164','204',99,3union
select'2164','205',67,3union
select'2165','201',88,17union
select'2165','202',77,13union
select'2165','203',66,3union
select'2165','204',99,3union
select'2165','205',67,3union
select'2166','201',88,17union
select'2166','202',77,13union
select'2166','203',66,3union
select'2166','204',59,0union
select'2166','205',67,3union
select'2167','201',88,17union
select'2167','202',77,13union
select'2167','203',66,3union
select'2167','204',99,3union
select'2167','205',67,3union
select'2168','201',88,17union
select'2168','202',77,13union
select'2168','203',66,3union
select'2168','204',99,3union
select'2168','205',67,3union
select'2169','201',88,17union
select'2169','202',77,13union
select'2169','203',66,13union
select'2169','204',39,0union
select'2169','205',67,3union
select'2160','201',88,17union
select'2170','202',77,13union
select'2170','203',66,3union
select'2170','204',99,3union
select'2170','205',67,3union
select'2171','201',88,17union
select'2171','202',77,13union
select'2171','203',66,3union
select'2171','204',99,3union
select'2171','205',67,3union
select'2172','201',88,17union
select'2172','202',45,0union
select'2172','203',66,3union
select'2172','204',99,3union
select'2172','205',67,3union
select'2173','201',88,17union
select'2173','202',77,13union
select'2173','203',66,3union
select'2173','204',99,3union
select'2173','205',67,3union
select'2174','201',88,17union
select'2174','202',77,13union
select'2174','203',66,3union
select'2174','204',99,3union
select'2174','205',67,3union
select'2175','201',88,17union
select'2175','202',34,0union
select'2175','203',66,3union
select'2175','204',99,3union
select'2175','205',67,3
go
--公共选修课成绩
create table Score_px
(
sno char(20),
pxno char(20),
primary key(sno,pxno),--以公共课程号和学生编号为主码
pxgrade int,--公共课成绩
pxfj int,--学分绩点
)
go
insert into Score_px(sno,pxno,pxgrade,pxfj)
select'2101','206',88,17union
select'2103','207',77,2union
select'2114','206',88,17union
select'2114','207',77,13
go
--专业必修课成绩
create table Score_mb
(
sno char(20),
mbno char(20),
primary key(sno,mbno),--以专业课程号和学生编号为主码
mbgrade int,--专业课成绩
mbfj int,--学分绩点
)
go
insert into Score_mb(sno,mbno,mbgrade,mbfj)
select'2101','101',87,11union
select'2101','102',78,11union
select'2102','101',87,11union
select'2102','102',84,13union
select'2103','101',83,9union
select'2103','102',82,12union
select'2104','101',82,9union
select'2104','102',81,12union
select'2105','101',83,9union
select'2105','102',45,0union
select'2106','101',87,11union
select'2106','102',83,13union
select'2107','101',82,9union
select'2107','102',81,12union
select'2108','101',82,9union
select'2108','102',78,11union
select'2109','101',77,8union
select'2109','102',68,7union
select'2110','101',67,5union
select'2110','102',68,7union
select'2111','101',77,8union
select'2111','102',58,0union
select'2112','101',97,12union
select'2112','102',58,0union
select'2113','101',77,8union
select'2113','102',88,15union
select'2114','101',97,12union
select'2114','102',38,0union
select'2115','101',57,0union
select'2115','102',48,0union
select'2116','101',67,5union
select'2116','102',78,11union
select'2117','101',87,11union
select'2117','102',98,19union
select'2118','101',57,0union
select'2118','102',78,11union
select'2119','101',87,11union
select'2119','102',58,0union
select'2120','103',87,18union
select'2121','103',88,19union
select'2122','103',87,19union
select'2123','103',88,19union
select'2124','103',87,19union
select'2125','103',88,19union
select'2126','103',87,19union
select'2127','103',88,19union
select'2128','103',87,19union
select'2129','103',88,19union
select'2130','103',87,19union
select'2131','103',88,19union
select'2132','104',87,11union
select'2133','104',78,8union
select'2134','104',67,5union
select'2135','104',78,8union
select'2136','104',89,11union
select'2137','104',84,10union
select'2138','104',83,10union
select'2139','104',82,9union
select'2140','104',83,10union
select'2141','104',84,10union
select'2142','104',85,10union
select'2143','104',87,11union
select'2144','104',88,11union
select'2145','104',89,11union
select'2146','106',87,7union
select'2147','106',88,7union
select'2148','106',87,7union
select'2149','106',88,7union
select'2150','106',87,7union
select'2151','106',78,5union
select'2152','106',68,3union
select'2153','106',67,3union
select'2154','106',78,5union
select'2155','106',88,7union
select'2156','106',89,7union
select'2157','106',84,6union
select'2158','106',83,6union
select'2159','106',72,4union
select'2160','107',82,6union
select'2161','107',83,6union
select'2162','107',84,6union
select'2163','107',85,7union
select'2164','107',86,7union
select'2165','107',87,7union
select'2166','108',87,11union
select'2167','108',88,11union
select'2168','108',87,11union
select'2169','108',88,11union
select'2170','108',87,11union
select'2171','108',78,8union
select'2172','108',68,3union
select'2173','108',67,3union
select'2174','108',78,5union
select'2175','108',88,11
go
--专业选修课成绩
create table Score_mx
(
sno char(20),
mxno char(20),
primary key(sno,mxno),--以专业课程号和学生编号为主码
mxgrade int,--专业课成绩
mxfj int,--学分绩点
)
go
insert into Score_mx(sno,mxno,mxgrade,mxfj)
select'2138','105',72,6union
select'2133','105',68,8union
select'2135','105',88,11union
select'2142','105',86,10
go
--按学号删除指定学生的基本信息及修课信息
--注意删除某一个学生的基本信息后，该学生的修课信息也要删除。
go
create trigger delete_stu0 on Student for delete
as
declare @sno char(20)
select @sno=sno from deleted
delete from Score_pb where sno=@sno
delete from Score_px where sno=@sno
delete from Score_mb where sno=@sno
delete from Score_mx where sno=@sno
go

--按课程号删除指定课程的基本信息。注意删除某一门课程的基本信息后，所有选修该课程的信息都必须删除
create trigger delete_pc0 on Class_pb for delete
as
declare @pbno char(20)
select @pbno=pbno from deleted
delete from Score_pb where pbno=@pbno
go
create trigger delete_px0 on Class_px for delete
as
declare @pxno char(20)
select @pxno=pxno from deleted
delete from Score_px where pxno=@pxno
go
create trigger delete_mb0 on Class_mb for delete
as
declare @mbno char(20)
select @mbno=mbno from deleted
delete from Score_mb where mbno=@mbno
go
create trigger delete_mx0 on Class_mx for delete
as
declare @mxno char(20)
select @mxno=mxno from deleted
delete from Score_mx where mxno=@mxno
go

6、建立视图

建立视图，视图名为“学生成绩”，包含有：学号、姓名、班级、课程名、成绩、学分绩
点。

go
create view 学生成绩
as
select Student.sno 学号,sna 学生,cna 班级,pbname 课程,pbgrade 成绩,pbfj 学分绩点
from Student ,Class_pb,Score_pb where
Student.sno=Score_pb.sno and Class_pb.pbno=Score_pb.pbno
union
select Student.sno,sna,cna,pxname,pxgrade,pxfj
from Student ,Class_px,Score_px where
Student.sno=Score_px.sno and Class_px.pxno=Score_px.pxno
union
select Student.sno,sna,cna,mbname,mbgrade,mbfj
from Student ,Class_mb,Score_mb where
Student.sno=Score_mb.sno and Class_mb.mbno=Score_mb.mbno
union
select Student.sno,sna,cna,mxname,mxgrade,mxfj
from Student ,Class_mx,Score_mx where
Student.sno=Score_mx.sno and Class_mx.mxno=Score_mx.mxno
go

7、录入数据

在每个基本表中录入一批数据。

建表时已经录入过,代码如上

8、数据查询

（1）给定学号，按学号查询指定学生的基本信息。

create procedure p_01 @a char(20)
as
	select*from Student
	where sno=@a
	go
	execute p_01 '2101'

（2）给定姓名，按姓名查询指定学生的基本信息。

go
create procedure p_02 @b char(20)
as
	select*from Student
	where sna=@b
	go
	execute p_02 '刘晨'

（3）给定课程号，按课程号查询指定课程的基本信息。

go
create procedure p_03 @c char(20)
as
	select*from Class_pb
	where pbno=@c
	union
	select*from Class_px
	where pxno=@c
	union
	--使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
	--构造的专业课程中比公共课程多了一个专业编号,故罗列其属性
	select mbno,mbname,mbtype,mbTime,mbCredit,mbTerm from Class_mb
	where mbno=@c
	union
	select mxno,mxname,mxtype,mxTime,mxCredit,mxTerm from Class_mx
	where mxno=@c
go
p_03 '203'

（4）给定课程名，按课程名查询指定课程的基本信息。

go
create procedure p_080 @d varchar(20)
--使用char类型转化为int失败
as
	select*from Class_pb
	where pbname=@d
	union
	select*from Class_px
	where pxname=@d
	union
	--使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
	--构造的专业课程中比公共课程多了一个专业编号,故罗列其属性
	select mbno,mbname,mbtype,mbTime,mbCredit,mbTerm from Class_mb
	where mbname=@d
	union
	select mxno,mxname,mxtype,mxTime,mxCredit,mxTerm from Class_mx
	where mxname=@d
go
p_080 '数据库原理与应用'

（5）给定学号和课程名，按学号和课程号查询指定学生所修指定课程的成绩和学分绩点。

go
create procedure p_05 @e char(20),@f char(20)
as
	select pbgrade,pbfj from Score_pb
	where sno=@e and pbno=@f
	union
	select pxgrade,pxfj from Score_px
	where sno=@e and pxno=@f
	union
	select mbgrade,mbfj from Score_mb
	where sno=@e and mbno=@f
	union
	select mxgrade,mxfj from Score_mx
	where sno=@e and mxno=@f
go
p_05 '2105','202'

（6）给定学号，按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使
用所建立的“学生成绩” 视图。

go
create procedure p_08 @g char(20)
as
	select 学号,学生,班级,课程,成绩,学分绩点
	from 学生成绩 where 学号=@g
go
p_08 '2111'

(7）给定班级和课程名，按班级和课程号查询指定班级所有学生选修指定课程的成绩，
查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩” 视图。

go
create procedure p_9 @h varchar(20),@i varchar(20)
as
	select 学号,学生,成绩,学分绩点
	from 学生成绩 
	 where 班级=@h and 课程=@i
go

（8）查询每个学生的学分绩点的总和及平均学分绩点。

select 学生,sum(学分绩点)学分绩点总和,avg(学分绩点)平均学分绩点
from 学生成绩 group by 学生

9、数据更新

（1）插入一个学生的基本信息。

insert into Student(sno,sna,ssex,sage,cno,cna,mno,mna,tno,tna)
values('2152','林俊杰','男',23,'8','经济2101','04','经济学','002','经济与管理学院')

（2）插入一门课程的基本信息。

insert into Class_mx(mxno,mxname,mxtype,mxTime,mxCredit,mxTerm,mno)
values('111','网络III','专业选修课',31,3,'秋','03')

（3）插入一个学生某一门课的成绩。

insert into Score_mx(sno,mxno,mxgrade,mxfj)
values('2138','111',73,6)

（4）给定学号，按学号修改指定学生的基本信息。

go
create procedure p_21 @j char(20)
as
	update Student set ssex='女'
	where sno=@j
go
p_21 '2106'

（5）给定课程号，按课程号修改指定课程的基本信息。

go
create procedure p_22 @k char(20)
as
	update Class_pb set pbTime =pbTime+1
	where pbno=@k
	
	update Class_px set pxTime =pxTime+1
	where pxno=@k
	
	update Class_mb set mbTime =mbTime+1
	where mbno=@k
	
	update Class_mx set mxTime =mxTime+1
	where mxno=@k
go
p_22 '105'

（6）给定学号和课程名，按学号和课程名修改指定学生所修指定课程的成绩。

go
create procedure p_26 @m char(20),@n varchar(20)
as
	update Score_pb set  pbgrade=pbgrade+1
	from Class_pb 
	where sno=@m and pbname=@n
	
	update Score_px set pxgrade=pxgrade+1
	from Class_px
	where sno=@m and pxname=@n

	update Score_mb set  mbgrade=mbgrade+1
	from Class_mb
	where sno=@m and mbname=@n

	update Score_mx set  mxgrade=mxgrade+1
	from Class_mx
	where sno=@m and mxname=@n
go
p_26 '2101','数据库原理与应用'

（7）给定学号，按学号删除指定学生的基本信息及修课信息。

go
create procedure p_27 @o char(20)
as 
	delete from Student where sno=@o
go
p_27 '2122'

（8）给定学号和课程名，按学号和课程名删除指定学生所修指定课程及成绩信息。

go
create procedure p_28 @p char(20),@q varchar(20)
as
	delete from Score_pb where sno=@p and pbno=(select pbno from Class_pb where pbname=@q)
	delete from Score_px where sno=@p and pxno=(select pxno from Class_px where pxname=@q)
	delete from Score_mb where sno=@p and mbno=(select mbno from Class_mb where mbname=@q)
	delete from Score_mx where sno=@p and mxno=(select mxno from Class_mx where mxname=@q)
go
p_28 '2123','数学I'

10、数据控制

先新建几个不同的数据库用户，给他们赋予不同的权限，然后自己以这些不同的用户登
录 SQL Server，比较操作权限的不同。

go
exec sp_addlogin'Qingshan','1234'
go
grant select on Student to Qingshan with grant option

–用户Qingshan登录,只对Student表有查询功能,对其他表没有查询,以及删改功能,
–对Student表也没有除查询之外的其他功能

go
exec sp_addlogin'LiuWen','1234'
go
grant update on Student to Qingshan with grant option

–用户Liuwen登录,只对Student表有改功能,对其他表没有查询,以及删改功能,
–对Student表也没有除改之外的其他功能

五、实验报告要求

1、格式要求
实验报告必须有：实验名称、实验目的、实验内容、实验步骤、总结。
2、内容要求
实验步骤清楚，SQL 代码完整，每个步骤的内容必须充实，能反映出该步骤的结果。
3、书写要求
图文并茂，图片必须要有图号和图题，图号和图题在图片正下方，表格必须要有表号和
表题，表号和表题必须位于表格正上方。