数据库实验报告-学校教学管理数据库设计(代吗)Word下载.doc

1、（2）创建后在database节点查看是否已包含了刚创建的数据库teaching；（3）展开teaching 节点，以下的所有对象均创建在teaching 数据库中；2 创建用户定义的数据类型练习按要求创建以下数据类型数据类型名称数据描述student_no长度为10且不允许空值的字符型；member_no整数，且存储的值不超过30000shortstring最多15个字符的可变长字符型USE teaching1EXEC sp_addtype student_no,char（10） ,not nullEXEC sp_addtype member_no,int EXEC sp_addtype s

2、hortstring,varchar（15） 3 创建表根据上述数据库存储的信息，决定创建5个表，并给每一个表指定一个有意义的表名；CREATE DATABASE teachingON（NAME=teaching_data,FILENAME=C:teachdata.mdf,SIZE=3,MAXSIZE=5,FILEGROWTH=10%）LOG ON（NAME=teaching_log,teachinglog.ldfSIZE=1,MAXSIZE=2,FILEGROWTH=10%） 根据每个表中每列存储的数据的情况，为每个列指定列名，数据类型，数据的长度，是否允许为空等列的属性；drop tabl

3、e studentCREATE TABLE student （ stno student_no CONSTRAINT student_Prim PRIMARY KEY , stn VARCHAR（50） UNIQUE, stcl CHAR（10） default unknown sex CHAR（10） DEFAULT 男 birthday int（4） default zzmm CHAR（10） default home VARCHAR（50） default enttime CHAR（50） default tel VARCHAR（50） default jianli CHAR（10）de

4、fault ）drop table coursecreate table course（ cono char（10） constraint course_prim primary key, coname char（10）, coleixing char（10） default coshihenianji char（10）default cotime char（10）default cocredict float（8）, cohour char（10）drop table classcreate table class（ cname char（10）, czy char（10）, cgr cha

5、r（10）, cmemno member_no default clename varchar（50） default drop table kechenganpaicreate table kechenganpai cono char（8） not null constraint course_fore foreign key references course（cono）, coyear int（4）, cocl varchar（10）not null constraint class1_fore foreign key references class（cname）, coteacher

6、 varchar（50） default drop table stxuankecreate table stxuanke cono char（8） , stno student_no , stgrade intr（4） default 4 实现数据完整性（1） 针对每一个表分析并定义主码（Primary Key）（2） 定义UIQUE约束用来规定一个列中的两行不能有相同的值；例如：希望学生的姓名是唯一的；（3） 针对每一个表分析外部码，并利用“关系图”定义外部码（Foreign Key），建立表之间的参照关系；（4） 定义缺省值方法1：直接在表设计时，定义列的default属性；例如：练习在

7、定义“性别”列时，定义它的缺省值位“男”；方法2：创建一个缺省值对象，然后绑定到任何一个需要的列；练习创建并绑定一个缺省值到学生的联系电话，缺省值为“unkown”；三、 使用查询分析器实现以下查询1练习课堂上举例介绍的几类查询；（1） 检索选修了课程号为01或02课程，且成绩高于或等于60分的学生的姓名，课程名和成绩。Select stn,cono,stgradeFrom teaching.dbo.student,teaching.dbo.stxuankeWhere teaching.dbo.student.stno = teaching.dbo.stxuanke.stno and （tea

8、ching.dbo.stxuanke.cono = 01 or teaching.dbo.stxuanke.cono = 02 ）and（ stgrade 60 or stgrade = 60）（2） 检索姓“王”的所有学生的姓名和年龄。select stn,birthdayfrom teaching.dbo.studentwhere teaching.dbo.student.stn like王%;（3） 检索没有考试成绩的学生姓名和课程名。select stn,teaching.dbo.stxuanke.stno,stgradefrom teaching.dbo.student,teachi

9、ng.dbo.stxuankewhere teaching.dbo.student.stno=teaching.dbo.stxuanke.stno and teaching.dbo.stxuanke.stgrade 16;五 创建和管理视图这是这一部分建好的四个视图文件名称1创建视图： 使用企业管理器或使用CREATE VIEW命令例1：创建视图，包含所有通信工程专业的学生的信息；create view IS_StudentInformationasselect *例2：创建视图，包含所有学生的学号，姓名，选课的课程名和成绩；create view IS_Student（stno,stna,c

10、oname,stgrade）Select teaching.dbo.student.stno,teaching.dbo.student.stn,teaching.dbo.course.coname,teaching.dbo.stxuanke.stgrade from teaching.dbo.stxuanke,teaching.dbo.student,teaching.dbo.course where teaching.dbo.stxuanke.cono=teaching.dbo.course.cono and teaching.dbo.student.stno=teaching.dbo.st

11、xuanke.stno2练习修改视图的定义第一个视图，包括with check option 选项create view IS_StudentInformation1with check option第二个视图，包括with encryption选项；create view IS_Student14利用已经创建的视图修改数据（观察是否所有通过视图的修改都能实现？update IS_Studentset stgrade=88where stno=10210323 and stna=李丽insert -这种修改不能实现,因为该试图的修改会影响到多个基本表into IS_Student values

12、（10210558张飞）;insert into IS_StudentInformation11110000更新20102222221992团员山东2010delete -这种修改不能实现,因为该试图的修改会影响到多个基本表删除了刚才插入的姓名为“更新”的行，数据由17条变回16条from IS_Studentwhere stno=10201001 and stn=王二deletefrom IS_Studentinformation 11110000 and sex=5练习删除视图drop view IS_StudentInformation;drop view IS_Student;drop

13、 view IS_StudentInformation1;drop view IS_Student1;实验二SQL Server数据库设计高级内容一、使用查询分析器实现以下查询（1） 统计有学生选修的课程门数。select count （distinct coname ）as courses teaching.dbo.coursewhere teaching.dbo.stxuanke.cono = teaching.dbo.course.cono;（2） 求选课在二门以上的学生所选课程的平均成绩（不统计不及格的课程）。最后按降序列出平均成绩名次名单来。select stno,avg （ tea

14、ching.dbo.stxuanke.stgrade ） as stgr_avgfrom teaching.dbo.stxuanke where teaching.dbo.stxuanke.stgrade 59 group by stno having count （ teaching.dbo.stxuanke.stgrade ）1ORDER BY SUM（stgrade） DESC （3） 统计每门指选课程的学生选修人数（超过2人的课程才统计）,要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。select cono ,count （ distinct

15、teaching.dbo.stxuanke.stno ）as xuankenofrom teaching.dbo.stxuanke group by conohaving count （ teaching.dbo.stxuanke.stno ）2order by xuankeno desc,cono asc（4） 检索所学课程包含了学号为10210323所选所有课程的学生姓名。select distinct stnteaching.dbo.student where teaching.dbo.stxuanke.cono=（select conofrom teaching.dbo.stxuank

16、ewhere teaching.dbo.stxuanke.stno=） and （teaching.dbo.student.stno=teaching.dbo.stxuanke.stno ）二、 实现数据完整性（1）定义check约束check约束用来限制用户输入的某一列数据；成绩输入的值应该限制为0-100之间的数值create table teaching.dbo.stxuanke（stno char（9） not null,cono char（3） not null,stgrade smallint check （grade=0 and grade=100）primary key （st

17、no） ）;（2）定义规则（rule）rule也可以用来限制用户输入的数据，但它只定义一次，可以绑定到一列或多列；创建一个规则，保证只允许输入指定的课程类别：“必修”，“任选”，“指选”，然后把此规则绑定到“课程类别”；alter table teaching.dbo.courseconstraint c1 check （teaching.dbo.course.coleixing in （必修任选指选）;三 存储过程和触发器的实现1存储过程（1）使用CREATE PROCEDURE命令创建存储过程例1：定义存储过程，实现学生学号，姓名，课程名和成绩的查询；CREATE PROC student

18、_queryASSELECT teaching.dbo.stxuanke.stno,stn,coname,stgradeFROM teaching.dbo.student,teaching.dbo.course,teaching.dbo.stxuankeWHERE teaching.dbo.student.stno = teaching.dbo.stxuanke.stno and teaching.dbo.course.cono = teaching.dbo.stxuanke.conoEXEC student_query定义存储过程，实现按某人指定课程的成绩；CREATE PROC stude

19、nt_query1sname varchar（20）,cname varchar（30）SELECT teaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgradeWHERE teaching.dbo.student.stno = teaching.dbo.stxuanke.stno AND teaching.dbo.course.cono = teaching.dbo.stxuanke.conoAND teaching.dbo.student.stn = sname AND teaching.dbo.course.conam

20、e = cnameexec student_query1 信号与系统例3：定义存储过程，在查询某人所选修的课程和成绩，指定姓名时，可以只给出姓；CREATE PROC student_query2sname varchar（20） = %SELECT teaching.dbo.student.stno,teaching.dbo.student.stn,coname,stgrade WHERE teaching.dbo.student.stno = teaching.dbo.stxuanke.stno AND teaching.dbo.course.cono = teaching.dbo.stx

21、uanke.conoand teaching.dbo.student.stn LIKE snameEXEC student_query2 张%例4：定义存储过程，计算并查看指定学生的总学分drop proc student_query3CREATE PROC student_query3sname varchar（20）, total integer OUTPUTSELECT total = SUM（teaching.dbo.course.cocredict） WHERE teaching.dbo.student.stno = teaching.dbo.stxuanke.stno AND an

22、d teaching.dbo.student.stn = sname-alter table teaching.dbo.course alter column teaching.dbo.course.cocredict floatdeclare total floatexec student_query3 苏菲, total OUTPUTselect total as total2触发器（1）使用CREATE TRIGGER命令对学生选课信息表创建插入触发器，实现的功能是：当向学生选课信息表中插入一记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程编号是否在课程表中存在，若有一项为否

23、，则提示“违背数据的一致性”错误信息，并且不允许插入。drop trigger check_insuse teachingCREATE TRIGGER check_ins ON dbo.stxuanke FOR INSERT if not exists（select * from teaching.dbo.student,teaching.dbo.insertedwhere teaching.dbo.student.stno=teaching.dbo.inserted.sno and teaching.dbo.course.cono=teaching.dbo.inserted.cono RAISERROR （违背数据的一致性,16,1）elsebegin PRINT 数据已插入endinsert into teaching.dbo.stxuanke values（202008105181,80）（2）使用CREATE TRIGGER 命令对学生信息表创建删除触发器，实现的功