1、针对以上的需求运用面向对象的方法进行需求分析。
2、设计数据库的概念结构。
先设计初步的 E-R 图，再对初步的 E-R 图进行优化，得到基本的 E-R 图。要求所建立的
概念结构能真实反映上述的应用语义。
3、设计数据库的逻辑结构
先将基本的 E-R 图转换为关系模型，在将能合并的关系模式进行合并。要求所建立的关
系模式至少达到 3NF 要求。
4、设计数据库的物理结构
① 确定关系
确定每个关系模式的关系，包括关系名、字段名、字段的数据类型、字段的宽度。
②确定索引
根据系统的功能需求，确定合适的索引。
③确定存储安排
确定数据库文件、日志文件、数据库备份的存储位置。
5、建立数据库
根据设计的数据库物理结构，在 SQL Server 环境下建立数据库，包括建立基本表、索
引。要求建立基本表时必须确定完整性约束条件。
create table 系(
系编号 varchar(20),
系名称 varchar(20)
)
create table 专业(
专业编号 varchar(20),
专业名称 varchar(20),
系编号 varchar(20)
)
create table 班级(
班级编号 varchar(20),
班级名称 varchar(20),
专业编号 varchar(20)
)
create table 学生(
学号 varchar(20),
姓名 varchar(6),
性别 varchar(2),
年龄 tinyint,
班级编号 varchar(20)
)
create table 课程(
课程号 varchar(20),
课程名 varchar(20),
课程性质 varchar(6),
学时 varchar(3),
学分 varchar(3),
开课学期 varchar(20)
)
create table 选课(
学号 varchar(20),
课程号 varchar(20),
课程性质 varchar(6),
成绩 tinyint
)
6、建立视图
建立视图，视图名为“学生成绩”，包含有：学号、姓名、班级、课程名、成绩、学分绩
点。
create view stu_grade as
select a.学号,a.姓名,b.课程号,b.课程名,c.课程性质,(case when c.成绩 > 90 then 4
when c.成绩 > 80 and c.成绩 < 90 then 3
when c.成绩 > 70 and c.成绩 < 80 then 2
when c.成绩 > 60 and c.成绩 < 70 then 1
else 0 end
) 学分绩点,c.成绩 from 学生 a,课程 b,选课 c where
a.学号 = c.学号 and b.课程号 = c.课程号
7、录入数据
在每个基本表中录入一批数据。
insert into 系 values('1001','a'),('1002','b'),('1003','c')
insert into 专业
values('20010','aa1','1001'),('20011','aa2','1001'),
('20020','bb1','1002'),
('20030','cc1','1003'),('20031','cc2','1003')
insert into 班级
values('200100','aaa1','20010'),('200110','aaa2','20011'),
('200200','bbb1','20020'),
('200300','ccc1','20030'),('200310','ccc2','20031')
insert into 学生
values('2001000','hr1','男',18,'200100'),('2001001','hr2','女',19,'200100'),('2001002','hr3','男',24,'200100'),
('2001100','bob1','男',21,'200110'),('2001101','bob2','女',22,'200110'),('2001102','bob3','男',23,'200110'),
('2002000','dog1','男',21,'200200'),('2002001','dog2','女',21,'200200'),('2002002','dog3','男',23,'200200'),
('2003000','cat1','男',22,'200300'),('2003001','cat2','女',21,'200300'),('2003002','cat3','男',22,'200300'),
('2003100','green1','男',23,'200310'),('2003101','green2','女',21,'200310'),('2003102','green3','男',23,'200310')
insert into 课程
values('c0001','c1','必修','24','3','2020-03-06'),('c0002','c2','必修','22','4','2020-03-07'),
('c0003','c3','必修','23','2','2020-03-03'),('c0004','c4','必修','20','2','2020-03-02'),
('c0005','c5','必修','23','3','2020-04-06'),('d0001','d1','选修','22','1','2020-03-12'),
('d0002','d2','选修','19','0.5','2020-03-13'),('d0003','d3','选修','21','3','2020-03-15'),
('d0004','d4','选修','25','4','2020-03-15')
insert into 选课 values('2001000','c0001','必修',85),('2001000','c0002','必修',86),('2001000','c0003','必修',90),('2001000','c0004','必修',95),('2001000','c0005','必修',75),
('2001000','d0001','选修',85),('2001000','d0002','选修',75),('2001000','d0003','选修',86),
('2001001','c0001','必修',85),('2001001','c0002','必修',87),('2001001','c0003','必修',91),('2001001','c0004','必修',92),('2001001','c0005','必修',73),
('2001001','d0001','选修',84),
('2001002','c0001','必修',83),('2001002','c0002','必修',87),('2001002','c0003','必修',89),('2001002','c0004','必修',91),('2001002','c0005','必修',71),
('2001002','d0001','选修',84),('2001002','d0004','选修',83),
('2001100','c0001','必修',82),('2001100','c0002','必修',86),('2001100','c0003','必修',88),('2001100','c0004','必修',90),('2001100','c0005','必修',70),
('2001100','d0001','选修',81),('2001100','d0002','选修',81),
('2001101','c0001','必修',83),('2001101','c0002','必修',85),('2001101','c0003','必修',83),('2001101','c0004','必修',93),('2001101','c0005','必修',74),
('2001101','d0002','选修',71),('2001101','d0003','选修',82),
('2001102','c0001','必修',81),('2001102','c0002','必修',82),('2001102','c0003','必修',82),('2001102','c0004','必修',94),('2001102','c0005','必修',75),
('2001102','d0003','选修',61),('2001102','d0004','选修',71),
('2002000','c0001','必修',84),('2002000','c0002','必修',81),('2002000','c0003','必修',92),('2002000','c0004','必修',96),('2002000','c0005','必修',76),
('2002000','d0001','选修',64),('2002000','d0002','选修',74),
('2002001','c0001','必修',86),('2002001','c0002','必修',88),('2002001','c0003','必修',93),('2002001','c0004','必修',95),('2002001','c0005','必修',77),
('2002001','d0002','选修',64),('2002001','d0003','选修',74),
('2002002','c0001','必修',87),('2002002','c0002','必修',89),('2002002','c0003','必修',94),('2002002','c0004','必修',97),('2002002','c0005','必修',78),
('2002002','d0003','选修',65),('2002002','d0004','选修',76),
('2003000','c0001','必修',81),('2003000','c0002','必修',82),('2003000','c0003','必修',91),('2003000','c0004','必修',98),('2003000','c0005','必修',79),
('2003000','d0001','选修',81),('2003000','d0002','选修',61),
('2003001','c0001','必修',82),('2003001','c0002','必修',88),('2003001','c0003','必修',92),('2003001','c0004','必修',97),('2003001','c0005','必修',80),
('2003001','d0002','选修',85),('2003001','d0003','选修',66),
('2003002','c0001','必修',86),('2003002','c0002','必修',85),('2003002','c0003','必修',93),('2003002','c0004','必修',98),('2003002','c0005','必修',81),
('2003002','d0003','选修',81),('2003002','d0004','选修',65),
('2003100','c0001','必修',87),('2003100','c0002','必修',82),('2003100','c0003','必修',95),('2003100','c0004','必修',91),('2003100','c0005','必修',82),
('2003100','d0001','选修',88),('2003100','d0002','选修',62),
('2003101','c0001','必修',81),('2003101','c0002','必修',83),('2003101','c0003','必修',96),('2003101','c0004','必修',92),('2003101','c0005','必修',83),
('2003101','d0002','选修',84),('2003101','d0003','选修',64),
('2003102','c0001','必修',83),('2003102','c0002','必修',84),('2003102','c0003','必修',94),('2003102','c0004','必修',93),('2003102','c0005','必修',84),
('2003102','d0003','选修',87),('2003102','d0004','选修',68)
8、数据查询
（1）给定学号，按学号查询指定学生的基本信息。
select * from 学生 where 学号 = '2001000'
（2）给定姓名，按姓名查询指定学生的基本信息。
select * from 学生 where 姓名 = '2001000'
（3）给定课程号，按课程号查询指定课程的基本信息。
select * from 课程 where 课程号 = 'c0001'
（4）给定课程名，按课程名查询指定课程的基本信息。
select * from 课程 where 课程名 = 'c1'
（5）给定学号和课程名，按学号和课程号查询指定学生所修指定课程的成绩和学分绩点。
select * from 学生 a,课程 b,选课 c where
a.学号 = c.学号 and b.课程号 = c.课程号 and a.学号 = '2001000' and b.课程名 = 'c1'
select 成绩,(case when c.成绩 > 90 then 4
when c.成绩 > 80 and c.成绩 < 90 then 3
when c.成绩 > 70 and c.成绩 < 80 then 2
when c.成绩 > 60 and c.成绩 < 70 then 1
else 0 end
) 学分绩点 from 学生 a,课程 b,选课 c where
a.学号 = c.学号 and b.课程号 = c.课程号 and
a.学号 = '2001000' and
b.课程号 = (select 课程号 from 课程 where 课程名 = 'c1' )
（6）给定学号，按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使
用所建立的“学生成绩” 视图。
create view stu_grade as
select a.学号,a.姓名,b.课程号,b.课程名,c.课程性质,(case when c.成绩 > 90 then 4
when c.成绩 > 80 and c.成绩 < 90 then 3
when c.成绩 > 70 and c.成绩 < 80 then 2
when c.成绩 > 60 and c.成绩 < 70 then 1
else 0 end
) 学分绩点,c.成绩 from 学生 a,课程 b,选课 c where
a.学号 = c.学号 and b.课程号 = c.课程号
select * from stu_grade where stu_grade.学号 = '2001000'
（7）给定班级和课程名，按班级和课程号查询指定班级所有学生选修指定课程的成绩，
查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩” 视图。
select c.学号,c.姓名,c.成绩,c.学分绩点 from stu_grade c where c.课程性质 = '选修'
（8）查询每个学生的学分绩点的总和和平均学分绩点。
select s.学号,s.姓名,sum(s.学分绩点) 学分绩点的总和,avg(s.学分绩点) 平均学分绩点
from stu_grade s group by s.姓名,s.学号
9、数据更新
（1）插入一个学生的基本信息。
insert into 学生 values('2001003','小明','男',18,'200100')
（2）插入一门课程的基本信息。
insert into 课程 values('f0001','f1','选修','24','3','2020-03-06')
（3）插入一个学生某一门课的成绩。
insert into 选课 values('2001003','f0001','选修','83')
（4）给定学号，按学号修改指定学生的基本信息。
update 学生 set 姓名 = '张三' where 学号 = '2001000'
（5）给定课程号，按课程号修改指定课程的基本信息。
update 课程 set 课程名 = 'python' where 课程号 = 'c0001'
（6）给定学号和课程名，按学号和课程名修改指定学生所修指定课程的成绩。
update 选课 set 成绩 = '100' where 学号 = '2001000' and 课程号 = 'c0001'
（7）给定学号，按学号删除指定学生的基本信息及修课信息。
delete from 选课 where 学号 = '2001003'
delete from 学生 where 学号 = '2001003'
（8）给定学号和课程名，按学号和课程名删除指定学生所修指定课程及成绩信息。
delete from 选课 where 学号 = '2003102' and 课程号 = 'd0004'
delete from 课程 where 课程号 = 'd0004'
10、数据控制
先新建几个不同的数据库用户，给他们赋予不同的权限，然后自己以这些不同的用户登
录 SQL Server，比较操作权限的不同
SP_addlogin 'student','123','webTest'
SP_adduser 'student'
grant select on 学生 to student with grant option
grant select on 班级 to student with grant option
grant select on 课程 to student with grant option
SP_addlogin 'teacher','123','webTest'
SP_adduser 'teacher'
grant select on 系 to teacher with grant option
grant select,update on 选课 to teacher with grant option
grant select,update on 学生 to teacher with grant option
扫一扫
354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
