数据库实验报告-学校教学管理数据库设计(代吗).doc
- 文档编号:7398256
- 上传时间:2023-05-11
- 格式:DOC
- 页数:50
- 大小:3.27MB
数据库实验报告-学校教学管理数据库设计(代吗).doc
《数据库实验报告-学校教学管理数据库设计(代吗).doc》由会员分享,可在线阅读,更多相关《数据库实验报告-学校教学管理数据库设计(代吗).doc(50页珍藏版)》请在冰点文库上搜索。
实验一 用SQLServer实现数据库设计
实验目的:
1.掌握使用企业管理器创建和管理SQLServer数据库及相关数据库对象。
2.掌握使用SQL语言中的SELECT命令实现查询功能。
实验内容:
本实验将建立一个学校教学管理数据库,在这个数据库中,存储以下信息:
1.有关学生的信息,包括学生的学号,姓名,班级,性别,出生时间,政治面貌,籍贯,入学时间,联系电话,简历;
2.有关班级的信息,包括班级的班名,专业,年级,人数,班主任;
3.有关课程的信息,包括课程编号,课程名称,课程类别(是指该课程为必修,指选还是任选),适用专业,适用年级,开课时间(是指在每个学年的第一学期,还是第二学期),学分,周学时;
4.有关学年课程安排的信息,包括课程编号,开设课程的学年(例如:
2002学年),开设课程的班级,任课教师;
5.有关学生选课的信息,包括课程编号,学生的学号,成绩;
一使用企业管理器完成数据库及数据库对象的创建和管理
实验步骤:
1.创建数据库
(1)要求参数:
数据库名称:
teaching
数据库逻辑文件名:
teaching_data 日志逻辑文件名:
teaching_log
操作系统数据文件名:
e:
\xxx\teaching.mdf 操作系统日志文件名:
e:
\xxx\teaching.ldf
数据文件初始大小:
1MB 日志文件初始大小:
1MB
数据文件最大大小:
5MB 日志文件最大大小:
2MB
数据文件增长增量:
10% 日志文件增长增量:
10%
(2)创建后在database节点查看是否已包含了刚创建的数据库teaching;
(3)展开teaching节点,以下的所有对象均创建在teaching数据库中;
2.创建用户定义的数据类型
练习按要求创建以下数据类型
数据类型名称 数据描述
student_no 长度为10且不允许空值的字符型;
member_no 整数,且存储的值不超过30000
shortstring 最多15个字符的可变长字符型
USEteaching1
EXECsp_addtypestudent_no,'char(10)','notnull'
USEteaching1
EXECsp_addtypemember_no,'int','notnull'
USEteaching1
EXECsp_addtypeshortstring,'varchar(15)','notnull'
3.创建表
根据上述数据库存储的信息,决定创建5个表,并给每一个表指定一个有意义的表名;
CREATEDATABASEteaching
ON
( NAME=teaching_data,
FILENAME='C:
\teachdata.mdf',
SIZE=3,
MAXSIZE=5,
FILEGROWTH=10%)
LOGON
( NAME=teaching_log,
FILENAME='C:
\teachinglog.ldf',
SIZE=1,
MAXSIZE=2,
FILEGROWTH=10%)
根据每个表中每列存储的数据的情况,为每个列指定列名,数据类型,数据的长度,是否允许为空等列的属性;
droptablestudent
CREATETABLEstudent
(stnostudent_noCONSTRAINTstudent_PrimPRIMARYKEY,
stnVARCHAR(50)UNIQUE,
stclCHAR(10)default'unknown',
sexCHAR(10)DEFAULT'男',
birthdayint(4))default'unknown',
zzmmCHAR(10)default'unknown',
homeVARCHAR(50)default'unknown',
enttimeCHAR(50)default'unknown',
telVARCHAR(50)default'unknown',
jianliCHAR(10)default'unknown')
droptablecourse
createtablecourse
(
conochar(10)constraintcourse_primprimarykey,
conamechar(10),
coleixingchar(10)default'unknown',
coshihenianjichar(10)default'unknown',
cotimechar(10)default'unknown',
cocredictfloat(8),
cohourchar(10)
)
droptableclass
createtableclass
(
cnamechar(10),
czychar(10),
cgrchar(10),
cmemnomember_nodefault'unknown',
clenamevarchar(50)default'unknown'
)
droptablekechenganpai
createtablekechenganpai
(
conochar(8)notnullconstraintcourse_foreforeignkeyreferencescourse(cono),
coyearint(4),
coclvarchar(10)notnullconstraintclass1_foreforeignkeyreferencesclass(cname),
coteachervarchar(50)default'unknown')
droptablestxuanke
createtablestxuanke
(
conochar(8),
stnostudent_no,
stgradeintr(4)default'unknown'
)
4.实现数据完整性
(1)针对每一个表分析并定义主码(PrimaryKey)
(2)定义UIQUE约束
用来规定一个列中的两行不能有相同的值;
例如:
希望学生的姓名是唯一的;
(3)针对每一个表分析外部码,并利用“关系图”定义外部码(ForeignKey),建立表之间的参照关系;
(4)定义缺省值
方法1:
直接在表设计时,定义列的default属性;
例如:
练习在定义“性别”列时,定义它的缺省值位“男”;
方法2:
创建一个缺省值对象,然后绑定到任何一个需要的列;
例如:
练习创建并绑定一个缺省值到学生的联系电话,缺省值为“unkown”;
三、使用查询分析器实现以下查询
1.练习课堂上举例介绍的几类查询;
(1)检索选修了课程号为01或02课程,且成绩高于或等于60分的学生的姓名,课程名和成绩。
Selectstn,cono,stgrade
Fromteaching.dbo.student,teaching.dbo.stxuanke
Whereteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoand(teaching.dbo.stxuanke.cono=01orteaching.dbo.stxuanke.cono=02)and(stgrade>60orstgrade=60)
(2)检索姓“王”的所有学生的姓名和年龄。
selectstn,birthday
fromteaching.dbo.student
whereteaching.dbo.student.stnlike'王%';
(3)检索没有考试成绩的学生姓名和课程名。
selectstn,teaching.dbo.stxuanke.stno,stgrade
fromteaching.dbo.student,teaching.dbo.stxuanke
whereteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoandteaching.dbo.stxuanke.stgrade<0;
(4)检索年龄大于女同学平均年龄的男学生姓名和年龄。
--注:
上述查询中所用的课程号和学号的值,可以根据自己表中的数据作修改;为了验证查询的正确,可能还需要输入或修改表中的示例数据;
selectstn,(2012-birthday)
fromteaching.dbo.student
wheresex='男'and(2012-birthday)>16;
五创建和管理视图
这是这一部分建好的四个视图文件名称
1.创建视图:
使用企业管理器或使用CREATEVIEW命令
例1:
创建视图,包含所有通信工程专业的学生的信息;
createviewIS_StudentInformation
as
select*
fromteaching.dbo.student
例2:
创建视图,包含所有学生的学号,姓名,选课的课程名和成绩;
createviewIS_Student(stno,stna,coname,stgrade)
as
Selectteaching.dbo.student.stno,
teaching.dbo.student.stn,
teaching.dbo.course.coname,
teaching.dbo.stxuanke.stgrade
fromteaching.dbo.stxuanke,
teaching.dbo.student,
teaching.dbo.course
whereteaching.dbo.stxuanke.cono=teaching.dbo.course.conoand
teaching.dbo.student.stno=teaching.dbo.stxuanke.stno
2.练习修改视图的定义
第一个视图,包括withcheckoption选项
createviewIS_StudentInformation1
as
select*
fromteaching.dbo.student
withcheckoption
第二个视图,包括withencryption选项;
createviewIS_Student1
as
Selectteaching.dbo.student.stno,
teaching.dbo.student.stn,
teaching.dbo.course.coname,
teaching.dbo.stxuanke.stgrade
fromteaching.dbo.stxuanke,
teaching.dbo.student,
teaching.dbo.course
whereteaching.dbo.stxuanke.cono=teaching.dbo.course.conoand
teaching.dbo.student.stno=teaching.dbo.stxuanke.stno
4.利用已经创建的视图修改数据(观察是否所有通过视图的修改都能实现?
)
updateIS_Student
setstgrade='88'
wherestno='10210323'andstna='李丽';
insert--这种修改不能实现,因为该试图的修改会影响到多个基本表
intoIS_Student
values('10210558','张飞','','');
insert
intoIS_StudentInformation
values('11110000','更新','2010222222','男','1992','团员','山东','2010','','')
delete --这种修改不能实现,因为该试图的修改会影响到多个基本表
删除了刚才插入的姓名为“更新”的行,数据由17条变回16条
fromIS_Student
wherestno=10201001andstn='王二';
delete
fromIS_Studentinformation
wherestno='11110000'andsex='男';
5.练习删除视图
dropviewIS_StudentInformation;
dropviewIS_Student;
dropviewIS_StudentInformation1;
dropviewIS_Student1;
实验二 SQLServer数据库设计高级内容
一、使用查询分析器实现以下查询
(1)统计有学生选修的课程门数。
selectcount(distinctconame)
ascourses
fromteaching.dbo.stxuanke,
teaching.dbo.course
whereteaching.dbo.stxuanke.cono=teaching.dbo.course.cono;
(2)求选课在二门以上的学生所选课程的平均成绩(不统计不及格的课程)。
最后按降序列出平均成绩名次名单来。
selectstno,avg(teaching.dbo.stxuanke.stgrade)asstgr_avg
fromteaching.dbo.stxuankewhereteaching.dbo.stxuanke.stgrade>59
groupbystno
havingcount(teaching.dbo.stxuanke.stgrade)>1
ORDERBYSUM(stgrade)DESC
(3)统计每门指选课程的学生选修人数(超过2人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
selectcono,count(distinctteaching.dbo.stxuanke.stno)asxuankeno
fromteaching.dbo.stxuanke
groupbycono
havingcount(teaching.dbo.stxuanke.stno)>2
orderbyxuankenodesc,conoasc
(4)检索所学课程包含了学号为10210323所选所有课程的学生姓名。
selectdistinctstn
fromteaching.dbo.stxuanke,
teaching.dbo.student
where
teaching.dbo.stxuanke.cono=(
selectcono
fromteaching.dbo.stxuanke
whereteaching.dbo.stxuanke.stno='10210323'
)
and(teaching.dbo.student.stno=teaching.dbo.stxuanke.stno)
二、实现数据完整性
(1) 定义check约束
check约束用来限制用户输入的某一列数据;
例如:
成绩输入的值应该限制为0-100之间的数值
createtableteaching.dbo.stxuanke
(stnochar(9)notnull,
conochar(3)notnull,
stgradesmallintcheck(grade>=0andgrade<=100)
primarykey(stno));
(2) 定义规则(rule)
rule也可以用来限制用户输入的数据,但它只定义一次,可以绑定到一列或多列;
例如:
创建一个规则,保证只允许输入指定的课程类别:
“必修”,“任选”,“指选”,然后把此规则绑定到“课程类别”;
altertableteaching.dbo.course
constraintc1check(teaching.dbo.course.coleixingin('必修','任选','指选'));
三存储过程和触发器的实现
1. 存储过程
(1)使用CREATEPROCEDURE命令创建存储过程
例1:
定义存储过程,实现学生学号,姓名,课程名和成绩的查询;
CREATEPROCstudent_query
AS
SELECTteaching.dbo.stxuanke.stno,stn,coname,stgrade
FROMteaching.dbo.student,
teaching.dbo.course,
teaching.dbo.stxuanke
WHEREteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoand
teaching.dbo.course.cono=teaching.dbo.stxuanke.cono
EXECstudent_query
例2:
定义存储过程,实现按某人指定课程的成绩;
CREATEPROCstudent_query1
@snamevarchar(20),
@cnamevarchar(30)
AS
SELECTteaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgrade
FROMteaching.dbo.student,
teaching.dbo.course,
teaching.dbo.stxuanke
WHEREteaching.dbo.student.stno=teaching.dbo.stxuanke.stno
ANDteaching.dbo.course.cono=teaching.dbo.stxuanke.cono
ANDteaching.dbo.student.stn=@sname
ANDteaching.dbo.course.coname=@cname
execstudent_query1'李丽','信号与系统'
例3:
定义存储过程,在查询某人所选修的课程和成绩,指定姓名时,可以只给出姓;
CREATEPROCstudent_query2
@snamevarchar(20)='%'
AS
SELECTteaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgrade
FROMteaching.dbo.student,
teaching.dbo.course,
teaching.dbo.stxuanke
WHEREteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoANDteaching.dbo.course.cono=teaching.dbo.stxuanke.cono
andteaching.dbo.student.stnLIKE@sname
EXECstudent_query2'张%'
例4:
定义存储过程,计算并查看指定学生的总学分
dropprocstudent_query3
CREATEPROCstudent_query3
@snamevarchar(20),
@totalintegerOUTPUT
AS
SELECT@total=SUM(teaching.dbo.course.cocredict)
FROMteaching.dbo.student,
teaching.dbo.course,
teaching.dbo.stxuanke
WHEREteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoAND
teaching.dbo.course.cono=teaching.dbo.stxuanke.cono
andteaching.dbo.student.stn=@sname
--altertableteaching.dbo.coursealtercolumnteaching.dbo.course.cocredictfloat
declare@totalfloat
execstudent_query3'苏菲',@totalOUTPUT
select@totalastotal
2.触发器
(1)使用CREATETRIGGER命令对学生选课信息表创建插入触发器,实现的功能是:
当向学生选课信息表中插入一记录时,检查该记录的学号在学生表中是否存在,检查该记录的课程编号是否在课程表中存在,若有一项为否,则提示“违背数据的一致性”错误信息,并且不允许插入。
droptriggercheck_ins
useteaching
CREATETRIGGERcheck_insON[dbo].[stxuanke]
FORINSERT
AS
ifnotexists(
select*
fromteaching.dbo.student,
teaching.dbo.course,
teaching.dbo.inserted
whereteaching.dbo.student.stno=teaching.dbo.inserted.snoand
teaching.dbo.course.cono=teaching.dbo.inserted.cono
)
RAISERROR('违背数据的一致性',16,1)
else
begin
PRINT'数据已插入'
end
insertintoteaching.dbo.stxuankevalues('20','2008105181',80)
(2)使用CREATETRIGGER命令对学生信息表创建删除触发器,实现的功
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告 学校 教学管理 设计
![提示](https://static.bingdoc.com/images/bang_tan.gif)