数据库的各对象的分析

文档供参考，可复制、编制，期待您的好评与关注！ 视图、存储过程、函数、触发器、事务和锁等数据库高级对象的使用。要求是：完善系统的需求分析和业务流程，设计一些视图、存储过程、触发器等数据库高级对象，说明这些对象的应用场合和使用方法。要求提供创建对象的SQL脚本。（附带创建数据库和数据表的脚本）使用事务，要求提供SQL脚本。1、创建数据库create database student -创建数据库：studenton primary /创建一个主数据库文件，逻辑文件名为student，物理文件名为student.mdf，存放在C:Program FilesMicrosoft SQL ServerMSSQLData目录下，初始大小为10MB，没有指定最大长度，即可以自由增长直到充满整个硬盘空间，自动增长时的递增量为5MB。(name=student,filename=C:Program FilesMicrosoft SQL ServerMSSQLDatastudent.mdf,size=10,maxsize=unlimited,filegrowth=5)log on /创建一个事务日志文件，其逻辑文件名为student_log，物理文件名为student_log.LDF，存放在C:Program FilesMicrosoft SQL ServerMSSQLData目录下，初始大小为5MB，最大为100MB，自动增长速度为10%。(name=student_log,filename=C:Program FilesMicrosoft SQL ServerMSSQLDatastudent_log.LDFsize=5,maxsize=100,filegrowth=10%)2、建表create table StudentInformation -学生信息表(cStuId char(8) not null constraint pkStuId primary key, -学生学号，设置为主键，使用了主关键字约束cStuName char(8) not null, -学生姓名cSex char(2) check(cSex=男or cSex=女), -性别，性别只能取男或者女，使用了检查约束cStuNative char(20),-籍贯sStuBirthday smalldatetime,-出生日期cDepartmentId char(6) not null references Department(cDepartmentId),-院系编号,设置为外键，使用了外关键字约束cSpecialityId char(8) not null references Speciality(cSpecialityId),-专业编号,设置为外键，使用了外关键字约束cClassNum char(4),-班号sEnterTime smalldatetime,-入学时间vStuAdrress varchar(40),-学生家庭地址vStuTel varchar(20)-学生电话号码)create table Course -课程信息表(cCourseId char(10) not null constraint pkCourseId primary key,-课程编号，设置为主键，使用了主关键字约束cSpecialityId char(8) not null references Speciality(cSpecialityId),-专业编号,设置为外键，使用了外关键字约束cCourseName char(20) not null unique,-课程名称，使用unique进行唯一性约束cCourseTypeId char(5) not null references CourseType(cCourseTypeId),-课程类型编号,设置为外键，使用了外关键字约束iLecture int,-授课学时tSemester tinyint,-开课学期fCredit float,-课程学分)create table CourseType -课程类型表(cCourseTypeId char(5) not null constraint pkCourseTypeId primary key,-类型编号，设置为主键，使用了主关键字约束cCourseTypeName char(20) not null,-类型名称)create table Student_Course -学生选课成绩表(cStuId char(8) not null references StudentInformation(cStuId),-学生学号使用外关键字约束sTeacherCourseId smallint not null references Teacher_Course(sTeacherCourseId),-教师上课编号,使用了外关键字约束fStuScore float default 0,-学生成绩，使用了defaul进行缺省约束primary key(cStuId,sTeacherCourseId) -学生学号和教师上课编号组合起来是学生选课成绩表的主键) create table TeacherInformation -教师信息表(cTeacherId char(8) not null constraint pkTeacherId primary key,-教师编号，设置为主键，使用了主关键字约束cTeacherName char(8) not null,-教师姓名cSex char(2) check(cSex=男or cSex=女), -性别，性别只能取男或者女,使用了检查约束sTeacherBirthday smalldatetime,-出生日期cDepartmentId char(6) not null references Department(cDepartmentId),-院系编号,设置为外键，使用了外关键字约束tProfessionId tinyint,-职称编号(外键)vTeacherAdrress varchar(40),-教师家庭地址cCityCode char(6),-邮政编码vTeacherTel varchar(20), -教师电话号码vTeacherEmail varchar(20) -教师电子邮件)create table Teacher_Course -教师上课课表(sTeacherCourseId smallint not null constraint pkTeacherCourseId primary key,-教师上课编号,设置为主键，使用了主关键字约束cTeacherId char(8) not null references TeacherInformation(cTeacherId),-教师编号,设置为外键，使用了外关键字约束cSpecialityId char(8) not null references Speciality(cSpecialityId),-专业编号,设置为外键，使用了外关键字约束cClassNum char(4) not null,-班号cCourseId char(10) not null references Course(cCourseId),-课程编号,设置为外键，使用了外关键字约束cSemester char(6),-学期cSchoolYear char(10),-学年vClassTime varchar(20),-上课时间vClassRoom varchar(20),-上课地点tWeekTime tinyint,-每周课时)create table Profession -职称表(tProfessionId tinyint not null constraint pkProfessionId primary key,-职称编号，设置为主键，使用了主关键字约束cProfessionName char(20) not null,-职称名称)create table Department-院系信息表(cDepartmentId char(6) not null constraint pkDepartmentId primary key,-院系编号，设置为主键，使用了主关键字约束cDepartmentName char(20) not null,-院系名称cDepartmentHeader char(8),-院系负责人vDepartmentAddress varchar(40),-院系办公地址vDepartmentTel varchar(20),-院系联系电话)create table Speciality -专业信息表(cSpecialityId char(8) not null constraint pkSpecialityId primary key, -专业编号,设置为主键，使用了主关键字约束cDepartmentId char(6) not null,-院系编号(外键)cSpecialityName char(20) -专业名称)create table Class -班级信息表(cClassNum char(4) not null,-班号cSpecialityId char(8) not null references Speciality(cSpecialityId),-专业编号,使用了外关键字约束cClassHeader char(8),-班级负责人primary key(cClassNum,cSpecialityId)create table Change -学籍变更记录信息表(sChangeId smallint not null constraint pkChangeId primary key,-学籍变更记录号,设置为主键，使用了主关键字约束cStuId char(8) not null references StudentInformation(cStuId),-学生学号,设置为外键，使用了外关键字约束cChangeCode char(8) not null references Change_Code(cChangeCode),-变更代码,设置为外键，使用了外关键字约束dRectime datetime not null,-记录时间vDescription varchar(100),-描述)create table Change_Code -学籍变更信息表(cChangeCode char(8) not null constraint pkChangeCode primary key,-变更代码，设置为主键，使用了主关键字约束vDescription varchar(100),-描述)create table Reward -奖励记录信息表(cRewardId char(8) not null constraint pkRewardId primary key,-奖励信息记录号,设置为主键，使用了主关键字约束cStuId char(8) not null references StudentInformation(cStuId),-学生学号,设置为外键，使用了外关键字约束cLevels char(5) not null,-级别代码(外键)dRectime datetime not null,-记录时间vDescription varchar(100),-描述)create table Reward_Levels -奖励信息表(cLevels char(5) not null constraint pkRewardLevelsId primary key,-级别代码，设置为主键，使用了主关键字约束vDescription varchar(100),-描述)create table Publishment -处罚记录信息表(cPublishmentId char(8) not null constraint pkPublishmentId primary key,-处罚信息记录号，设置为主键，使用了主关键字约束cStuId char(8) not null references StudentInformation(cStuId),-学生学号,设置为外键，使用了外关键字约束cLevels char(5) not null,-级别代码(外键)dRectime datetime not null,-记录时间cEnable char(1) not null check(cEnable=是or cEnable=否),-是否生效（T-是，F-否),只取是或者否,使用了检查约束vDescription varchar(100),-描述)create table Publishment_Levels -处罚信息表(cLevels char(5) not null constraint pkLevels primary key,-级别代码，设置为主键，使用了主关键字约束vDescription varchar(100),-描述)create table Users -用户信息表(cUserName char(8) not null primary key,-用户名cUserPassword char(8),-用户密码iUserRights int,-操作权限)3、视图(1)创建视图-建立学院代码为610000教师的视图，包括教师的教师编号、姓名、性别和出生年月.使用with check option可选项，是对视图进行更新操作（增加、删除、修改）时，要保证更新操作的行满足视图定义中的条件表达式create view TeacherInformation_viewasselect cTeacherId,cTeacherName,sTeacherBirthdayfrom TeacherInformation where cDepartmentId=610000with check optionselect * from TeacherInformation_view-建立课程1（上课编号）的学生花名册的视图，该花名册包括学生的学号、姓名、专业名称和这门课程的成绩create view Course1_StudentInformation_viewas select Student_Course.cStuId,cStuName,cSpecialityName,fStuScore fromStudentInformation,Speciality,Student_Course where Student_Course .sTeacherCourseId=1 and Student_Course.cStuId=StudentInformation.cStuIdand StudentInformation.cSpecialityId=Speciality.cSpecialityIdwith check optionselect * from Course1_StudentInformation_view-建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码create view NoPass_StudentInformation_viewas select StudentInformation.cStuId,cStuName,Teacher_Course.cCourseIdfrom StudentInformation,Student_Course,Teacher_Coursewhere Student_Course.fStuScore60 and Student_Course.cStuId=StudentInformation.cStuId andStudent_Course.sTeacherCourseId=Student_Course.sTeacherCourseIdselect * from NoPass_StudentInformation_view（2）修改视图-修改视图TeacherInformation_view，使其包括教师的职称这一列alter view TeacherInformation_viewas select cTeacherId,cTeacherName,cSex,sTeacherBirthday,cProfessionNamefrom TeacherInformation,Profession where cDepartmentId=610000and TeacherInformation.tProfessionId=Profession.tProfessionIdselect * from TeacherInformation_view（3）删除视图-删除视图TeacherInformation_viewdrop view TeacherInformation_view4、存储过程(1)创建和执行存储过程-创建一个无参的存储过程，完成的功能是在表StudentInformation、Course、 Student_Course、Teacher_Course中查询以下字段：班级、学号、姓名、性别、课程名称、分数create procedure StuScoreInfoasbeginselect StudentInformation.cClassNum as 班级,StudentInformation.cStuId as 学号,cStuName as 姓名,cSex as 性别,cCourseName as 课程名称,fStuScore as 分数from StudentInformation,Course,Student_Course,Teacher_Coursewhere StudentInformation.cStuId=Student_Course.cStuId and Student_Course.sTeacherCourseId=Teacher_Course.sTeacherCourseIdand Teacher_Course.cCourseId=Course.cCourseIdend-执行存储过程exec StuScoreInfo-创建一个接受学院代码作为参数，列出数据库student中某个院系学生的全部信息create procedure list_student_departmentdepartment char(6)asbeginselect * from StudentInformation where cDepartmentId=departmentend-执行list_student_department存储过程exec list_student_department 610000-创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生学号，在StudentInformation中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功返回整数0，如果执行出错则返回错误号-Error:当 Microsoft SQL Server 完成 Transact-SQL 语句的执行时，如果语句执行成功，则 ERROR 设置为 0。若出现一个错误，则返回一条错误信息。ERROR 返回此错误信息代码，直到另一条 Transact-SQL 语句被执行。create procedure Stu_AgeStudentId char(8),Age int outputas declare ErrorValue int -定义幷初始化局部变量，用于保存返回值set ErrorValue=0beginselect Age=YEAR(getdate()-YEAR(sStuBirthday)-求此学生的年龄from StudentInformation where StudentId=cStuIdif(Error0)-根据程序的执行结果返回不同的值set ErrorValue=Errorreturn ErrorValueend（2）删除存储过程-删除存储过程StuScoreInfodrop procedure StuScoreInfo-删除存储过程Stu_Agedrop procedure Stu_Age-删除存储过程list_student_departmentdrop procedure list_student_department5、触发器(1)-创建一个after触发器，要求实现以下功能：在StudentInformation表上创建一个删除类型的触发器Stu_Delete,当在StudentInformation表中删除某一条记录后，触发该触发器，在Student_Course表中删除与此学号对应的记录create trigger Stu_Deleteon StudentInformationfor deleteas begindeclare StuId char (8)select StuId=cStuId from deleteddelete from Student_Course where cStuId=StuIdend-创建了Stu_Delete触发器之后，输入以下语句delete from StudentInformation where cStuId=02080001delete from StudentInformation where cStuId=02080002-删除Stu_Delete触发器drop trigger Stu_Delete(2) -创建一个instead of触发器，要求实现以下功能，在Student_Course表上创建一个删除类型的触发器NotAllowDelete,当在Student_Course表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息create trigger NotAllowDeleteon Student_Courseinstead of deleteas beginprint 本表中的数据不允许被删除！不能执行删除操作！end-创建了NotAllowDelete触发器之后，执行以下语句delete from Student_Course where cStuId=02080002-删除NotAllowDelete触发器drop trigger NotAllowDelete(3) -创建一个after触发器，要求实现以下功能：在Student_Course表上创建一个插入、更新类型的触发器ScoreCheck,当在fStuScore字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间create trigger ScoreCheckon Student_Coursefor insert,updateasif update(fStuScore)begindeclare ScoreValue realselect ScoreValue=(select fStuScore from inserted)if ScoreValue100 or ScoreValue0print 输入的分数有误，请确认输入的考试分数！end-创建了ScoreCheck触发器之后，输入以下语句insert into Student_Coursevalues(02080002,5,-50)insert into Student_Coursevalues(02080003,7,105)-删除ScoreCheck触发器drop trigger ScoreCheck6、事务-事务的使用。删除学号为02080001的学生的信息declare tran_name varchar(20)select tran_name=my_tran_deletebegin tran tran_namedelete from StudentInformation where cStuId=02080001delete from Student_Course where cStuId=02080001commit tran tran_name-事务的回滚,删除学号为02080002的学生的信息，让事务回滚到保存点save pointdeclare tran_name varchar(20)select tran_name=mytranbegin tran tran_namedelete from StudentInformation where cStuId=02080002save tran save_pointdelete from Student_Course where cStuId=02080002if error=0 beginrollback tran save_pointcommit tran tran_nameendelsecommit tran tran_name8 / 8