一、题目
(1)任务描述
《课程管理》模块的E-R 图如图2.8.1 所示,逻辑数据模型如图2.8.2 所示,物理数据模型如图2.8.3 所示, 数据表字段名定义见表2.8.1。请按要求完成数据库创建、数据表创建和数据操作任务:
图2.8.1 E-R 图
图2.8.2 逻辑数据模型
图2.8.3 物理数据模型
表2.8.1 字段名定义表
字段名 | 字段说明 | 字段名 | 字段说明 |
student_no | 学号 | credit | 学分 |
student_name | 姓名 | classhour | 学时 |
sex | 性别 | student_no | 学号 |
birth | 出生年月 | course_no | 课程编号 |
enter_date | 入学年份 | ordinary_scores | 平时成绩 |
address | 家庭住址 | end_scores | 期末成绩 |
course_no | 课程编号 | total_scores | 总评成绩 |
course_name | 课程名 |
任务一:创建数据库(10 分)
创建数据库Student。
任务二:创建数据表(25 分)
根据图2.8.2 和表2.8.1,创建数据表T_student_info、T_course_info 、T_student_scores。
任务三:创建数据表间的关系及约束(15 分)
根据物理数据原型,创建数据关系。
任务四:数据操作(30 分)
用SQL 语句完成如下操作:
• 向T_student_info 表插入数据:“102011,刘德华,男,03/23/1991,2009,湖南长沙劳动东路168 号”;
• 查询出1991 年出生的学生信息;
• 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩;
• 创建视图查询学生的学号、姓名、课程名、总评成绩;
• 统计每门课程的选课人数、最高分、平均分,并将统计结果保存在表T_total 中。
(2)实施条件
实施条件要求见本模块附录1
(3)考核时量
考核时长为180分钟
(4)评分细则
评分细则见本模块附录2
(5)作品提交要求
作品提交要求见本模块附录3
二、答案
Create Database Student
on
(name='wudi',
filename='E:\xzq\db5\wudi.mdf'
)
use Student
go
Create table T_student_info
(
student_no int primary key,
student_name char(20),
sex char(2),
birth datetime,
enter_date int,
adress varchar(50),
)
Create table T_course_info
(
course_no int not null primary key,
course_name char(50) not null,
credit int not null,
classhour int not null,
)
Create table T_student_scores
(
course_no int foreign key references T_course_info(course_no),
student_no int foreign key references T_student_info(student_no),
ordinary_scores float not null,
end_scores float not null,
total_scores float not null,
)
insert into T_student_info
values('102011','刘德华','男','03/23/1991','2009','湖南长沙劳动东路 168 号')
insert into T_course_info
values('001','数据结构','3','12')
insert into T_student_scores
values('001','102011','88','86','88')
select a.*
from T_student_info a
where a.birth between'01/01/1991'and'12/31/1991'
select a.student_name,b.ordinary_scores,b.end_scores,b.total_scores
from T_student_info a,T_student_scores b,T_course_info c
where c.course_name='数据结构'
and a.student_no=b.student_no
and b.course_no=c.course_no
create view V_student_info_course_info_student_scores
as
select a.student_no'学号',a.student_name'姓名',b.course_name'课程名',c.total_scores'总评成绩'
from T_student_info a,T_course_info b,T_student_scores c
where a.student_no=c.student_no and b.course_no=c.course_no
select count(course_no)'选课人数',max(total_scores)'最高分',avg(total_scores)'平均分'
into T_total
from T_student_scores