J2-12《学生管理信息系统》成绩管理模块

（1）任务描述

《成绩管理》模块的E-R图如图2.12.1所示，逻辑数据模型如图2.12.2所示，物理数据模型如图2.12.3所示，数据表字段名定义见表2.12.1。请按以下设计完成数据库创建、数据表创建和数据操作任务：

正在上传…重新上传取消图2.12.1 E-R图

正在上传…重新上传取消

图2.12.2 逻辑数据模型

正在上传…重新上传取消

图2.12.3 物理数据原型

表2.12.1 字段名定义表

字段名	字段说明	字段名	字段说明
stud_id	学号	reserve	备注
stud_name	姓名	course_id	课程编号
stud_sex	性别	course_name	课程名称
birth_date	出生日期	teacher_name	教师姓名
entry_date	入学日期	score_id	成绩编号
mobile	手机号码	score	成绩
birth_place	籍贯

任务一：创建数据库（10分）

创建数据库StudentDB。

任务二：创建数据表（25分）

根据图2.12.2和表2.12.1，创建数据表T_student、T_course、T_score。

任务三：创建数据表间的关系及约束（15分）

根据物理数据原型，创建数据关系。

任务四：数据操作（30分）

用SQL语句完成如下操作：

•在T_student表插入数据：“2009010001，张学友，男，1980-10-4，2009-9-1，15012345678，湖南长沙，无”；

•查询出籍贯为“湖南长沙”的学生基本信息；

•查询出姓名为“张学友”的学生所有课程的成绩；

•查询出“数据库应用”这门课的平均成绩；

•创建视图查询学生的姓名，手机号码和籍贯；

•创建存储过程，查询指定课程名称的最高成绩。

（2）实施条件

实施条件要求见本模块附录1

（3）考核时量

考核时长为180分钟

（4）评分细则

评分细则见本模块附录2

（5）作品提交要求

作品提交要求见本模块附录3

答案

create database StudentDB
on
(
	name = StudentDB,
	filename='J:\xzq\db6\StudentDB.mdf'
)
use StudentDB
go


Create table T_student
(
stud_id			varchar(10)  not Null primary key,
stud_name		varchar(20) not Null,
stud_sex		varchar(2) not Null,
birth_date		datetime,
entry_date		datetime,
mobile			varchar(11),
birth_place		varchar(30),
reserve			text
)
 
 
Create table T_course
(
course_id		char(10)  not Null primary key,
course_name		char(100) not Null,
teacher_name	char(30) not Null,
 
)
 
Create table T_score
(
score_id	bigint not Null		primary key,
course_id	char(10) 			foreign key references T_course(course_id),
stud_id		varchar(10)  			foreign key references T_student(stud_id),
score		decimal(5,2) ,
)

insert into T_student
values ('2009010001','张学友','男','1980-10-4','2009-9-1','15012345678','湖南长沙','无')
insert into T_course
values('001','数据库应用','张三')
insert into T_score
values('01','001','2009010001','200')

select *
from T_student
where birth_place='湖南长沙'

select  a.score
from	T_score a ,T_student b
where b.stud_name='张学友'
and a.stud_id=b.stud_id

select avg(score)
from T_score a,T_course b
where course_name='数据库应用'
and a.course_id=b.course_id

create view		V_student
as
		select stud_name,birth_place,mobile
		from	T_student

create proc P_score
@course_name	varchar(100)
as
begin
			select	max(score)'最高成绩'
			from	T_course a,	T_score b
			where	course_name=@course_name
			and		a.course_id=b.course_id
end