数据库的建立及数据维护（北方工业大学）

实验一： 数据库的建立及数据维护（4学时） 

【实验目的】

1. 学会使用对象资源管理器创建数据库、创建基本表和查看数据库属性。
2. 学会使用对象资源管理器向数据库输入数据，修改数据，删除数据的操作。
3. 在SQL Server查询编辑器中完成复杂查询及视图定义。  

【实验环境】

实验环境：Windows 7 、SQL Server 2008    

【实验任务】

1. 根据以上数据字典，画出该数据库的ER图，完成数据库的概念结构设计；
2. 将ER图转换成关系模型，判断关系模型中的各个关系（表）是第几范式，如果没有达到第三范式或BC范式，请进行规范化。完成数据库的逻辑结构设计。
3. 通过对象资源管理器实现关系模式的存储，包括确定主码、外部码等。
4. 按照给出的示例数据完成数据的录入。
5. 打开数据库SQL Server的查询编辑器，用SQL语句完成以下查询。并通过实验结果验证查询语句的正确性，将每个SQL语句及结果存盘，以备老师检查。

1. 求全体学生的学号、姓名和出生年份。
2. 求每个系的学生总人数并按降序排列。
3. 求选修了课程号为002或003的学生的学号、课程名和成绩。
4. 检索选修某课程的学生人数多于3人的教师姓名。
5. 查询所有未选课程的学生姓名和所在系。
6. 求每个同学的课程成绩的最高分，查询结果项包括：学生姓名、课程号及最高分。
7. 求所有讲授数据结构课程的教师姓名
8. 查询所有选修了李正科老师的课程的学生信息
9. 新建一个关系表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。将表Student中在计算机系‘CS’的学生数据插入到表S1中。
10. 建立计算机系的学生的视图STUDENT_CS。利用视图STUDENT_CS，将学生的001号课程的成绩提高5分。
11. 创建一个带参数的存储过程，将数据结构课程的成绩在Low与Hign分数段的学生信息提取出来，查询后的结果包括：姓名、所在的系、成绩。

各关系表的结构和示例数据如下：  

1. 学生情况表 Student

1. 求每个系的学生总人数并按降序排列。

select Sdept,count(*) Zongshu from Student

group by Sdept

order by count(*) desc;

1. 求选修了课程号为002或003的学生的学号、课程名和成绩。

Select Score.Sno ,Course.Cname,Score.Grade

from Course,score

where(Score.Cno=003orScore.Cno=002)

and(Course.Cno=Score.Cno)

1. 检索选修某课程的学生人数多于3人的教师姓名。

select Tname

from Teacher,Score

where Teacher.Cno=Score.Cno

group by Tname

having COUNT (Score.Sno)>3

1. 查询所有未选课程的学生姓名和所在系。

select distinct Sname,Sdept

from Student,Course

where Sno not in (select  Sno from score)

1. 求每个同学的课程成绩的最高分，查询结果项包括：学生姓名、课程号及最高分。

select Sname,Cno,Grade最高分

from Score x,Student

where (Student.Sno=x.Sno)

and x.Grade in 

(select max(Grade)

from Score

where Score.Sno=x.Sno

group by Sno)

1. 求所有讲授数据结构课程的教师姓名

select Tname

from Teacher

where Cno=004

1. 查询所有选修了李正科老师的课程的学生信息

select Student.*

from Student,Teacher,Score

where Student.Sno=Score.Sno

and Teacher.Cno=Score.Cno

and Teacher.Tname='李正科'

1. 新建一个关系表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。将表Student中在计算机系‘CS’的学生数据插入到表S1中。

go

create table S1

(Sno varchar(50),

      Sname varchar(50),

  Sd varchar(50),

  Sa varchar(50))

go

insert into S1

select Sno,Sname,Sdept,Sage

from Student

1. 建立计算机系的学生的视图STUDENT_CS。利用视图STUDENT_CS，将学生的001号课程的成绩提高5分。

 

go

create view Student_CS

as

select*

from Student

where Student.Sdept='CS'

go

update Score

set Grade = Grade+5

where Score.Sno in

(select Sno from Student_CS)

 

1. 创建一个带参数的存储过程，将数据结构课程的成绩在Low与Hign分数段的学生信息提取出来，查询后的结果包括：姓名、所在的系、成绩。
2.