《数据库实验3&实验4》由会员分享，可在线阅读，更多相关《数据库实验3&实验4（4页珍藏版）》请在金锄头文库上搜索。
1、实验3：数据完整性和安全性管理3.1数据完整性管理4.1、写出与上述任务相对应的SQL语句 4.1.1、建立课程的实体完整性, 和课程号cno的参照完整性;课程实体完整性：alter table course add constraint pk_cno primary key (cno)课程号的参照完整性：alter table sc add constraint FK_cno foreign key (cno) references course(cno ) 4. 1.2、对HRM数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束（参考HRM数据库表定义图中说明）主键
2、约束：Departments:alter table departments add constraint FK_DEPARTMENTID primary key (departmentid)Employee: alter table employee add constraint FK_EMPLOYEEID primary key (employeeid)外键约束:Salary:alter table salary add constraint FK_EmployeeID foreign key (EmployeeID) references Employee(Employeeid)Empl
3、oyee:alter table employee add constraintFK_EMPLOYEE_DEPARTMENTforeign key (DepartmentID) references Departments(DepartmentID)唯一约束：Departments：alter table Departments add constraint UNIQUE_Department UNIQUE (DepartmentID)Employee:alter table Employee add constraint UNIQUE_Employee UNIQUE (DepartmentI
4、D)Salary:alter table Salary add constraint UNIQUE_Salary UNIQUE (EmployeeID)非空约束：表Employee：alter table Employee alter column EmployeeID char(6) not nullalter table Employee alter column Namechar(10) not nullalter table Employee alter column BirthdayDatetime not nullalter table Employee alter column
5、Sex Bit not nullalter table Employee alter column DepartmentID char(3) not null表Departments:alter table Departments alter column DepartmentID char(3) not null表Salary:alter table Salary alter column EmployeeID char(6) not nullalter table Salary alter column Income Float(8) not nullalter table Salary
6、alter column OutCome Float(8) not null用户自定义约束：alter table Employee add constraint CK_Sex check (sex in (男 ,女) 4. 1.3、建立salary表的Income字段限定在0-9999之间。alter table Salary add constraint CK_Salary_INCOME check (Income between 0 and 9999) 4.2、实验心得 数据完整性包含参照完整性，实体完整性，用户自定义完整性，这些完整性约束能更好的保证数据的规范性以及合法性，并提高数据的
7、安全性，尤其在设计主外键关系的时候，要注意其内在逻辑，肯能很容易就把外间关系弄掉了，或者逻辑考虑不周，导致最后数据插入不进去。所以数据的逻辑关系在数据库设计时十分重要。3.2数据库安全性管理 4.1、写出与上述任务相对应的SQL语句 4.1.1、创建角色ProgramerRole，拥有创建表，存储过程，视图权限，拥有对Salary表的查询、修改、插入权限 创建角色： CREATE ROLE ProgramerRole 分配创建表，存储过程，视图权限： grant create table,create procedure,create view to ProgramerRole 分配对表Sal
8、ary的查询、修改、插入的权限： grant select ,insert , update on Salary to ProgramerRole 4.1.2、创建一个登录账号Testlogin CREATE LOGIN Testlogin with password = 4.1.3、创建对应于这个登录账号的数据库用户TestUser create user TestUser for login Testlogin 4.1.4、将用户TestUser添加到TestRole角色中 exec sp_addrolemember TestRole,TestUser 4.2实验心得 存储过程以及视图，在
9、数据库系统开发中经常用到，巧妙的视图设计能帮助开发者减轻很大的工作量，视图是一张基于基础表的虚表，同样可以进行增删改查等操作，非常实用。实验4：触发器和存储过程实验4.1触发器实验 4.1、写出与上述任务相对应的SQL语句 4.1.1、在Student表中编写insert的触发器，假如每个班的学生不能超过30个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现。CREATE TRIGGER STUDENT_INSERT ON STUDENT INSTEAD OF INSERT AS BEGIN DECLARE NUM INT SELECT NUM=COUNT(DISTINCT SNO
10、)FROM STUDENT IF (NUM30) INSERT INTO STUDENT(SNO,SNAME) SELECT SNO,SNAME FROM INSERTED ELSE PRINT FAIL END 4.1.2、在SC表上编写update触发器，当修改SC表中的grade字段时将其修改前后的信息保存在SC_log表中。CREATE TRIGGER SC_UPDATE ON SC FOR UPDATEAS IF UPDATE(GRADE)BEGININSERT INTO SCLOG SELECT INSERTED.SNO,INSERTED.CNO,DELETED.GRADE,INS
11、ERTED.GRADE FROM DELETED,INSERTED WHERE INSERTED.SNO=INSERTED.SNO 4.2、实验心得 建立触发器的时候要注意参数的传递效果，不然很容易弄错。 4.2存储过程实验（对应于student数据库） 4.1、写出与上述任务相对应的SQL语句 4.1.1、创建一个无参存储过程StuScoreInfo,查询以下信息:学号,姓名,性别,课程名称,考试成绩.CREATE PROCEDURE RAYSHAW AS SELECT STUDENT.SNO,STUDENT.SNAME,STUDENT.SSEX,COURSE.CNAME,SC.GRADE
12、FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO; 4.1.2、创建一个带参数的存储过程stu_info,该存储过程根据传入的学生编号在student表中查询此学生的信息.CREATE PROCEDURE RAY_SHAW SNO NVARCHAR(20) AS SELECT * FROM STUDENT WHERE SNO=SNO; 4.1.3、创建一个带参数的存储过程StuScoreInfo2,该存储过程根据传入的学生编号和课程名称查询以下信息:姓名,课程名称,考试成绩. CREATE PROCEDU
13、RE STUSCOREINFO2(SNO NVARCHAR(20),CNO NVARCHAR(10)AS SELECT STUDENT.SNAME,COURSE.CNAME,SC.GRADE FROM STUDENT,SC,COURSEWHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND STUDENT.SNO=SNO AND COURSE.CNO=CNO; 4.1.4、编写带参数的存储过程,根据传入的课程名称统计该课程的平均成绩.代码： CREATE PROCEDURE SELECTGRADE(CNAME NVARCHAR(50)AS SELE
14、CT COUNT(SC.GRADE) FROM SC,COURSE WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME=CNAME; 4.1.5、编写存储过程,根据传入的课程名统计这门课的成绩分布情况,即按照各分数段统计人数. CREATE PROCEDURE GROUPGRADEBYCOURSE(CNAME NVARCHAR(10)AS SELECT SC.GRADE,COUNT(*) AS 人数 FROM SC,COURSE WHERE COURSE.CNAME=CNAME AND SC.CNO=COURSE.CNO GROUP BY SC.GRADE;4.2、实验心的 存储过程的设计，由于之前在学习数据库基础的时候没有接触到，所以对我们而言也算一个新的知识点，经过实验后发现，存储过程并不难，耐心去设计就可以。
