1、a.待开发软件系统的名称: 高校学籍管理系统b.本工程的任务提出者: c.本工程开发者:d.本工程用户: 大学学籍管理工作者、教师、学生1.3参考资料SQL Server 实验指导第三版马晓梅2构造设计2.1逻辑构造设计2.1.1学生实体E-R图2.1.2学院实体E-R图2.1.3教师实体E-R图2.1.4课程实体E-R图2.1.5专业实体E-R图2.1.6奖惩实体E-R图2.1.7课程实体E-R图2.1.8授课实体E-R图2.1.9班级实体E-R图2.1.10整体实体关系E-R图2.2关系图2.3物理构造设计Student表:列名说明类型备注sno学号varchar10不允空,主键sname
2、XX不允空ssex性别char(2)nation民族char10sage年龄tinyintsymbol政治面貌varchar8允空mno专业号char(10)idXX号varchar20birthplace籍贯homeaddr家庭地址varchar30intime入学时间class班级varchar10classid班级号intCourse表:o课程号varchar(5)ame课程名varchar(15)credit学分smallintctime课时Teacher表:tno教工号tnamevarchar(10)tsexprofess职称dmpno学院号telTc表:vatchar(5)char
3、(5)Class表:不允空,主键number人数Sc表:vatchar(10)grade成绩floatMajor表:mname专业名vatchar(20)Collage表:dname学院名dmphead院长名Award表:ano奖励号award奖励rank奖励等级time获奖时间3、存储过程use wddgocreateprocedure ppsno_in varchar(10),sname_out varchar(10)output,ame_out varchar(15)output,grade_out floatoutputasselect sname_out=sname,ame_out=
4、ame,grade_out=grade from student,sc,course1where student.sno=sno_in and student.sno=sc.sno and sc.o=course1.odeclare sno_in varchar(10), sname_out varchar(10), ame_out varchar(15), grade_out floatselect sno_in=2021508044exec pp sno_in,sname_out output,ame_out output,grade_out outputprint sname_outpr
5、int ame_outprint grade_out4、触发器4.1更新学生表createtrigger t3on student forupdateasdeclare class_old varchar(10), mno_old char(10), class_new varchar(10), mno_new char(10)select class_old=class from deletedselect mno_old=mno from deletedselect class_new=class from insertedselect mno_new=mno from insertedu
6、pdate class set number=number-1 from class where class=class_old and mno=mno_oldupdate class set number=number+1 from class where class=class_new and mno=mno_new4.2删除学生表createtrigger t2on student fordeletedeclare class_read varchar(10), mno_read char(10)select class_read=class from deletedselect mno
7、_read=mno from deletedupdate class set number=number-1 from class where class=class_read and mno=mno_read4.3插入学生表createtrigger t1on student forinsertselect class_read=class from insertedselect mno_read=mno from insertedupdate class set number=number+1 from class where class=class_read and mno=mno_read5、数据库的恢复与备份数据库的完全备份backup database wdd to disk =f:备份数据库wdd with initgo 数据库的恢复restore database wdd from disk = with recovery数据库差异备份 with norecovery with file=2
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1