数据库实验报告-学校教学管理数据库设计(代吗)Word下载.doc
《数据库实验报告-学校教学管理数据库设计(代吗)Word下载.doc》由会员分享，可在线阅读，更多相关《数据库实验报告-学校教学管理数据库设计(代吗)Word下载.doc（50页珍藏版）》请在冰点文库上搜索。
（2）创建后在database节点查看是否已包含了刚创建的数据库teaching；
（3）展开teaching节点，以下的所有对象均创建在teaching数据库中；
2．创建用户定义的数据类型
练习按要求创建以下数据类型
数据类型名称 数据描述
student_no 长度为10且不允许空值的字符型；
member_no 整数，且存储的值不超过30000
shortstring 最多15个字符的可变长字符型
USEteaching1
EXECsp_addtypestudent_no,'
char（10）'
'
notnull'
EXECsp_addtypemember_no,'
int'
EXECsp_addtypeshortstring,'
varchar（15）'
3．创建表
根据上述数据库存储的信息，决定创建5个表，并给每一个表指定一个有意义的表名；
CREATEDATABASEteaching
ON
（ NAME=teaching_data,
FILENAME='
C:
\teachdata.mdf'
SIZE=3,
MAXSIZE=5,
FILEGROWTH=10%）
LOGON
（ NAME=teaching_log,
\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'
zzmmCHAR（10）default'
homeVARCHAR（50）default'
enttimeCHAR（50）default'
telVARCHAR（50）default'
jianliCHAR（10）default'
）
droptablecourse
createtablecourse
（
conochar（10）constraintcourse_primprimarykey,
conamechar（10）,
coleixingchar（10）default'
coshihenianjichar（10）default'
cotimechar（10）default'
cocredictfloat（8）,
cohourchar（10）
droptableclass
createtableclass
（
cnamechar（10）,
czychar（10）,
cgrchar（10）,
cmemnomember_nodefault'
clenamevarchar（50）default'
droptablekechenganpai
createtablekechenganpai
conochar（8）notnullconstraintcourse_foreforeignkeyreferencescourse（cono）,
coyearint（4）,
coclvarchar（10）notnullconstraintclass1_foreforeignkeyreferencesclass（cname）,
coteachervarchar（50）default'
droptablestxuanke
createtablestxuanke
conochar（8）,
stnostudent_no,
stgradeintr（4）default'
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）
wheresex='
and（2012-birthday）>
16;
五创建和管理视图
这是这一部分建好的四个视图文件名称
1．创建视图：
使用企业管理器或使用CREATEVIEW命令
例1：
创建视图，包含所有通信工程专业的学生的信息；
createviewIS_StudentInformation
as
select*
例2：
创建视图，包含所有学生的学号，姓名，选课的课程名和成绩；
createviewIS_Student（stno,stna,coname,stgrade）
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
withcheckoption
第二个视图，包括withencryption选项；
createviewIS_Student1
4．利用已经创建的视图修改数据（观察是否所有通过视图的修改都能实现？
updateIS_Student
setstgrade='
88'
wherestno='
10210323'
andstna='
李丽'
insert--这种修改不能实现,因为该试图的修改会影响到多个基本表
intoIS_Student
values（'
10210558'
张飞'
'
）;
insert
intoIS_StudentInformation
11110000'
更新'
2010222222'
1992'
团员'
山东'
2010'
delete --这种修改不能实现,因为该试图的修改会影响到多个基本表
删除了刚才插入的姓名为“更新”的行，数据由17条变回16条
fromIS_Student
wherestno=10201001andstn='
王二'
delete
fromIS_Studentinformation
11110000'
andsex='
5．练习删除视图
dropviewIS_StudentInformation;
dropviewIS_Student;
dropviewIS_StudentInformation1;
dropviewIS_Student1;
实验二 SQLServer数据库设计高级内容
一、使用查询分析器实现以下查询
（1）统计有学生选修的课程门数。
selectcount（distinctconame）
ascourses
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
teaching.dbo.student
where
teaching.dbo.stxuanke.cono=（
selectcono
fromteaching.dbo.stxuanke
whereteaching.dbo.stxuanke.stno='
）
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
定义存储过程，实现按某人指定课程的成绩；
CREATEPROCstudent_query1
@snamevarchar（20）,
@cnamevarchar（30）
SELECTteaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgrade
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）='
%'
SELECTteaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgrade
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
SELECT@total=SUM（teaching.dbo.course.cocredict）
WHEREteaching.dbo.student.stno=teaching.dbo.stxuanke.stnoAND
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
ifnotexists（
select*
fromteaching.dbo.student,
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命令对学生信息表创建删除触发器，实现的功
升级会员 