需求分析和数据采集

2007-03-31 16:13:42

需求分析和数据采集

（1） 给定学生的学号和姓名，对学生的基本情况进行查询，基本情况包括学生的年龄，性别，籍贯，所在班级等等。并形成统计表。

（2） 给定课名或课号，查询课程所使用的教材，课时，上课教室，上课时间，担任课程的教师，选修本课程的学生等情况，并形成统计表。

（3） 给定学生的姓名或学号，查询该生所选课程及各门课程的成绩；

（4） 给定教师名或教师号，查询该教师所担任的所有课程及上课时间安排，使用的教材，课时，上课教室，课后评价等。

（5） 查询给定专业，给定学期的课程安排情况，并形成统计表。

（6） 查询给定专业，给定学期的课程安排情况，并形成统计表。

（7） 为每个任课教师生成课表，并进行打印。

（8） 为每个班级生成课表，并进行打印。

（9） 为给定班级的每个学生统计给定学期的所有课程的综合成绩，要求按成绩排序，并形成统计表。

增加了以下需求：

（1） 要求在了解每个学生每门课程的成绩的同时，还要求给出该成绩在此门课程的所有成绩中的排序。其库结构应做那些改动？

（2） 要求在了解每门课程所用教材的同时，还要求给出该教材的编著者，出版社和出版时间。其库结构应做那些改动？

（3） 要求在了解每位教师每学期授课量的同时，还要求给出该教师的科研学术和带研究生的情况，职称评定的时间，在某个时间段所获得的各种奖励。其库结构应做那些修改？

独立完成教学管理数据库系统中库及表的建立。对表的附加要求如下：

（1） “学生”表中，“班号”列的值应参考“班级”表中“班号”列的值。

（2） “成绩”表中，“学号”列，“课号”列的值应当分别参考“学生”表中的“学号”列和“课程”表中“课号”列的值。

（3） “授课”表中，“教师号”列，“课号”列的值应当分别参考“教师”表中“教师号”列和“课程”表中“课号”列的值。

数据库的操作

（1） 使用sql语句向教学管理数据库中的表插入数据，并进行修改及删除数据操作。

（2） 使用sql语句在学生表中建立出生日期索引，并通过Enterprise Manager将其删除。

数据的查询与统计

（1） 查询来自012011班的学生的姓名，学号和出生日期。结果按出生日期的降序排列。

（2） 查询成绩在86——90分之间的课名和学号。

（3） 按课号统计课程的平均成绩。

（4） 查询课号，课名和学生姓名。

（5） 查询所有学生的课程情况，包括学生姓名，课号和成绩。

视图，存储过程和触发器

（1） 以教学管理系统为例，创建学生成绩单视图。

（2） 创建update触发器，防止用户修改学生表中的学号字段，当有操作修改学号字段时，触发器通过回滚事务来取消操作。

（3） 创建insert触发器，以确定当向成绩表中插入记录时，判断学生表是否存在相对应的学号，如果没有，则拒绝插入相应学号的成绩记录。

（4） 创建一个存储过程，用于统计指定课程的平均成绩。

设计实例5 ：为数据库管理系统创建视图，存储过程和触发器

1.利用Transact-SQL创建视图：

以教学管理数据库中的学生，班级和课程为基表，创建班级成绩单视图，具体操作步骤如下：

（1） 在Enterprise Manager中的工具菜单中启动查询分析器。

（2） 在查询分析器中输入下列语句：

CREATE VIEW 学生成绩单

AS

SELECT 成绩.学号,学生.姓名，课程.课名，成绩.成绩

FROM 成绩INNER JOIN

学生 ON 成绩.学号=学生.学号 INNER JOIN

班级表 ON 学生.班号=班级.班号 INNER JOIN

课程 ON 成绩.课号=课程.课号

(3) 单击运行按钮创建视图。

2. 利用Transact-SQL创建存储过程

本例在教学管理数据库中创建一个使用OUTPUT参数的存储过程GetSpecial Teacher Rank 用于获取指定教师的职称。具体操作步骤如下：

（1） 在Enterprise Manager中的工具菜单中启动查询分析器。

（2） 在查询分析器中输入下列语句：

USE 教学管理数据库

IF EXISTS

(SELECT name

FROM systemobjects

WHERE name=’GetSpecialTeacherRank’AND type=’p’)

DROP PROCEDURE GetSpecialTeacherRank

GO

CREATE PROCEDURE GetSpecialTeacherRank

@TeacherID varchar(15),

@Rank varchar(10) OUTPUT

AS

SELECT @Rank=职称 FROM 教师

WHERE 教师号=@TeacherID

GO

（3） 单击运行按钮创建存储过程。

3. 利用Transact-SQL创建触发器

以教学管理系统为例，分别介绍Insert,Delete和Update三种触发器的具体创建方法。

（1） 插入型触发器：为学生表创建一个INSERT除法器。当在学生表插入一行数据（表明新增一名学生）时，自动更新班级表的人数字段的数值。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Insert

ON 学生表

FOR INSERT

AS

Update 班级表 Set 人数=人数+@@ROWCOUNT

FROM 班级表 b INNER JOIN

Inserted i ON b.班号=i.班号

GO

(2) 删除型触发器：为学生表创建一个DELETE触发器。当在学生表上删除一行数据（表明减少一名学生）时，自动删除其成绩表中该学生的成绩信息。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Delete

ON 学生表

FOR DELETE

AS

Delete

From 成绩表

Where 成绩表.学号 in （Select 学号 From Deleted）

(2) 更新型触发器：为学生表创建一个UPDATE触发器。当在学生表上修改学生的班号时，自动修改班级表中对应班号的人数。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Update

ON 学生表

FOR UPDATE

AS

IF update(班号)

BEGIN

Update 班级表 Set 人数=人数-@@ROWCOUNT

FROM 班级表 b INNER JOIN Delete d ON b.班号=d.班号

Update 班级表 Set 人数=人数+@@ROWCOUNT

FROM 班级表 b INNER JOIN Inserted i ON b.班号=i.班号

END

GO

设计实例4 ：对教学管理系统的数据库进行查询

基于教学管理数据库，对其进行的各种查询如下：

1.简单查询

（1） 查询班号为012011的学生情况。

SELECT *

FROM 学生

WHERE 班号=’ 012011’

(2) 查询成绩高于86分的学号和课名。

SELECT 学号，课号

FROM 成绩

WHERE 成绩<86

(3) 查询成绩，学号，和课号，结果按成绩的降序排列。

SELECT *

FROM成绩

ORDER BY 成绩 DESC

2.统计查询

（1）查询总成绩和平均成绩。

SELECT SUM(成绩) AS ‘总成绩’，AVG(成绩) AS’平均成绩’

FROM 成绩

（2）查询各课程的平均成绩。

SELECT 课号，AVG(成绩) AS’平均成绩’

FROM 成绩

GROUP BY 课号

（3）查询分别按学号和课号分组的平均成绩。

SELECT 学号，课号，AVG(成绩) AS ‘平均成绩’

FROM 成绩

GROUP BY 学号，课号

WITH CUBE

（4）查询按学号分组的平均成绩

SELECT 学号，课号，AVG(成绩) AS ‘平均成绩’

FROM 成绩

GROUP BY 学号，课号

WITH ROLLUP

(5)查询学号，课号和成绩，并计算总成绩。

SELECT 学号，课号,成绩

FROM 成绩

ORDER BY 课号

COMPUTE SUM(成绩)

（6）查询学号，班号和成绩，并按课号分组计算总成绩

SELECT 学号，班号，成绩

FROM 成绩

ORDER BY 课号

COMPUTE SUM(成绩) BY 课号

3.内连接查询

查询学生的学号，姓名，课名，及成绩情况。

SELECT student.学号,student.姓名,course.课名,scores.成绩

FROM 学生 student INNER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON scores.课号=course.课号

4.左连接查询

查询所有学生的上课情况，包括学号，姓名及课号。

SELECT student.学号,student.姓名,course.课名

FROM 学生 student LEFT OUTER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON student.学号=scores.学号

5.右连接查询

SELECT student.学号,student.姓名,course.课名

FROM 成绩 scores RIGHT OUTER JOIN 学生 student

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON scores.学号=student.学号

6.自连接

查询在同一个班的学生的学号和姓名

SELECT student1.学号,student1.姓名

FROM 学生 student1 INNER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 学生 student2

ON student1.班号=student2.班号

WHERE student1.学号> 学号

7.子查询

查询成绩低于86分的学生姓名

SELECT 姓名

FROM 学生

WHERE 学号 IN

(SELECT 学号 FROM 成绩 WHERE 成绩>86 )

8.集合查询

查询学生姓名和教师姓名

SELECT 姓名

FROM 学生

UNION

SELECT 教师名

FROM 教师

设计实例3 :教学管理系统数据库的操作处理

1.插入数据

执行以下语句，向学生表插入数据：

INSERT INTO 学生

VALUES(‘ 01201101’ ,’张会英’,’男’,’陕西.西安’，’ 01/12/1975 ’,’ 012011’ )

2.修改数据

执行以下数据，将姓名为“张会英”的学生性别改为“女”：

UPDATE 学生

SET 性别=’女’

WHERE 姓名=’张会英’

3.删除数据

执行以下语句，将学号为01201101的学生记录删除。

DELETE 学生

WHERE 学号=’ 01201101’

4.建立索引

执行以下语句，对学生表建立班号索引

CREATE NONCLUSTERED

INDEX 班号索引 ON 学生（班号）

设计实例2 ：教学管理数据库的建立和维护

1. 建立教学管理物理数据库

2. 建立教学管理数据库中的各种表

设计实例1：教学管理系统逻辑数据库的建立

1.数据需求和数据采集

经过与用户充分接触，我们了解到用户对该系统的功能需求如下：

（1） 给定学生的学号和姓名，对学生的基本情况进行查询，基本情况包括学生的年龄，性别，籍贯，所在班级等等。并形成统计表。

（2） 给定课名或课号，查询课程所使用的教材，课时，上课教室，上课时间，担任课程的教师，选修本课程的学生等情况，并形成统计表。

（3） 给定学生的姓名或学号，查询该生所选课程及各门课程的成绩；

（4） 给定教师名或教师号，查询该教师所担任的所有课程及上课时间安排，使用的教材，课时，上课教室，课后评价等。

（5） 查询给定专业，给定学期的课程安排情况，并形成统计表。

（6） 查询给定专业，给定学期的课程安排情况，并形成统计表。

（7） 为每个任课教师生成课表，并进行打印。

（8） 为每个班级生成课表，并进行打印。

（9） 为给定班级的每个学生统计给定学期的所有课程的综合成绩，要求按成绩排序，并形成统计表。

同时还收集到上述各种统计表（统计表的内容略），经过讨论和了解得到了与上述各种需求有关的数据情况，例如，学号，姓名，性别，等的类型,长度等等。

3.逻辑数据库设计

根据对用户需求的分析和获得的各类数据资料及各类数据之间的关系，下面设计了能满足用户要求的各种表及每个表的构成。

学生表
*学号	姓名	性别	籍贯	出生日期	班号
S200603010	华峥	女	内蒙古.呼和浩特	01/02/1980	S200603
S200601001	乔峰	男	辽宁.沈阳	06/01/1983	S200601
S200601002	虚竹子	男	宁夏.银川	01/08/1982	S200601
S200602003	令狐冲	男	陕西.华山	04/01/1980	S200602
S200602004	任盈盈	女	河南.登封	03/04/1982	S200602
S200602005	岳灵珊	女	陕西.华山	01/01/1985	S200602
S200603006	黄蓉	女	福建.泉州	04/07/1988	S200603
S200603007	郭靖	男	内蒙古.呼和浩特	03/01/1984	S200603
S200603008	穆念慈	女	江苏.南京	09/05/1984	S200603
S200603009	杨康	男	山东.青岛	07/01/1983	S200603

3 学号 nchar 10 0

0 姓名 char 10 0

0 性别 nchar 1 0

0 籍贯 varchar 50 1

0 出生日期 datetime 8 0

0 班号 nchar 7 1