实验五数据库的完整性实验（含代码）
实验5 索引和数据完整性_MySQL数据库教程_[共3页]
![实验5 索引和数据完整性_MySQL数据库教程_[共3页]](https://img.taocdn.com/s3/m/c5a53b2c770bf78a6429545d.png)
177
实验5
索引和数据完整性
目的与要求
（1）掌握索引的使用方法；
（2）掌握数据完整性的实现方法。
实验准备
（1）了解索引的作用与分类；
（2）掌握索引的创建方法；
（3）理解数据完整性的概念及分类；
（4）掌握各种数据完整性的实现方法。
实验内容
1．创建索引
（1）使用CREATE INDEX 语句创建索引
① 对YGGL 数据库的Employees 表中的DepartmentID 列建立索引。
在MySQL 客户端输入如下命令并执行：
create index depart_ind
on Employees(DepartmentID);
② 在Employees 表的Name 列和Address 列上建立复合索引。
create index Ad_ind
on Employees(Name, Address);
③ 对Departments 表上的DepartmentName 列建立唯一性索引。
create unique index Dep_ind
on Departments(DepartmentName);
【思考与练习】
a ．索引创建完后可以使用SHOW INDEX FROM tbl_name 语句查看表中的索引。
b ．对Employees 表的Address 列进行前缀索引。
c ．使用CREATE INDEX 语句能创建主键吗？
（2）使用ALTER TABLE 语句向表中添加索引
① 向Employees 表中的出生日期列添加一个唯一性索引，姓名列和性别列上添加一个复合索引。
使用如下SQL 语句：。
实验五 数据完整性
实验六数据完整性1.实验目的（1）掌握SQL Server管理平台和Transact-SQL语句（CREATE RULE、DROP RULE）创建和删除规则的方法。
（2）掌握系统存储过程sp_bindrule、sp_unbindrule绑定和解除绑定规则的操作方法，以及sp_helptext查询规则信息、sp_rename更名规则的方法。
（3）掌握SQL Server管理平台和Transact-SQL语句（CREATE DEFAULT、DROP DEFAULT）创建和删除默认对象的方法。
（4）掌握系统存储过程sp_bindefault、sp_unbindefault绑定和解除绑定默认对象的操作方法，以及sp_helptext查询默认对象信息。
（5）掌握SQL Server管理平台和Transact-SQL语句（CREATE TABLE、ALTER TABLE）定义和删除约束的方法，并了解约束的类型。
2．实验内容（1）为xskc数据库中student表的创建一个规则stu_nj_rule，限制“年级”列所输入的数据为2位0~9的数字。
（2）创建一个规则stusex_rule，将其绑定到student_info表的“性别”列上，保证输入的性别值只能是“男”或“女”。
（3）使用系统存储过程sp_help查询stusex_rule规则列表，使用sp_helptext查询stusex_rule 规则的文本，使用sp_rename将stusex_rule规则更名为stu_s_rule。
删除stu_nj_rule规则。
（因为stu_s_rule是已存在的，所有这里改成stu_ss_rule）（4）在student_db数据库中，建立日期、货币等数据类型的默认值对象。
①创建默认对象df_date、df_money。
②输入以下代码，在xskc数据库中创建stu_fee数据表。
CREATE TABLE stu_fee(学号char(10) NOT NULL,姓名char(8) NOT NULL,学费money,交费日期datetime,)③使用系统存储过程sp_bindefault将默认对象df_money、df_date分别绑定在stu_fee表的“学费”、“交费日期”列上。
5数据完整性和数据安全性实验
实验5 数据完整性和数据安全性实验课时安排：2课时一、实验目的和要求使学生加深对数据安全性和完整性的理解．并掌握SQL Server中有关用户、角色及操作权限的管理方法．学会创建和使用规则、缺省和触发器。
二、实验内容1 数据库的安全性实验、在SQL Server企业管理器中，设置SQL Server的安全认证模式．实现对SQL Server的用户和角色管理，设置和管理数据操作权限．2数据库的完整性实验。
使用Transact-SQL设计规则、缺省、约束和触发器．通过SQLServer企业管理器定义它们。
三、实验方法1.设置SQL Server的安全认证模式1）在企业管理器中展开SQL服务器组。
用鼠标右键单击需要设置的SQL服务器，在弹出的菜单中选择“属性”项。
2）在弹出的SQL服务器属性对话框中，选择“安全性”选项卡。
3）在安全性选项卡中有一个安全性栏，它包括两个单选钮：单击“SQL Server和Windows(S)为选择混合安全认证模式；单击“仅Windows(W)”则为选择集成安全认证模式。
2.登录的管理(1)查看安全性文件夹的内容使用企业管理器可以创建、查看和管理登陆，登录文件夹存放在SQL服务器的安全性文件夹中。
当执行了进入企业管理器，打开指定的SQL服务器组和SQL服务器，并选择安全性文件夹的系列操作。
通过该窗口可以看出，安全性文件夹包括4个文件夹；登录文件夹。
服务器角色文件夹、链接服务器文件夹和远程服务器文件夹。
其中：登录文件夹用于存储和管理登录用户，服务器角色文件夹用于存储和管理角色：链接服务器文件夹用于存储和管理连接的服务器．远程服务器文件夹用于存储管理远程服务器信息。
（2）创建一个登录用户1）用鼠标右键单击登录文件夹，在弹出的菜单中选择“新建登录”选项后就会出现一个登录属性对话框。
在对话框中有常规选项卡、服务器角色选项卡和数据库访问选项卡。
2）选择常规选项卡输入用户的一般特征、常规选项卡界面。
数据库完整性代码
--②CREDIT取值为至以内的数字；
--? 参照完整性：TNO为外码，并实现级联更新和级联删除操作；
CREATETABLECourse
(CNOCHAR(4),
TNOCHAR(3),
CNAMECHAR(20)NOTNULL,
CREDITINTcheck(CREDIT>=1andCREDIT<=10),
--1、Dept表
--DEPTNO CHAR（）
--E VARCHAR（）DNAM
--实体完整性：DEPTNO为主码；
--用户定义完整性
--①DEPTNO的取值为至；
--②DNAME取值非空且唯一；
CREATETABLEDEPT
(DEPTNOCHAR(2)PRIMARYKEY
constraintc1check(DEPTNObetween01and99),
PRIMARYKEY(SNO,CNO),
FOREIGNKEY(SNO)REFERENCESStudent(SNO)
ondeletecascade
onupdatecascade,
FOREIGNKEY(CNO)REFERENCESCourse(CNO)
ondeletenoaction
onupdatenoaction,
DNAMEVARCHAR(20)
constraintc2NOTNULLunique,
);
--2、Student表
--? SNO CHAR（）
--? SNAME VARCHAR（）
--? SEX CHAR（）
--? DEPTNO CHAR（）
--
--? 实体完整性：SNO为主码；
实验五 数据库完整性与安全性实验
实验五数据库完整性与安全性实验实验内容完整性实验与要求：1分别定义数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；该操作在实验2中已完成2向学生表插入具有相同学号的数据，验证其实体完整性约束；insertinto student values('g9940201','wangyu','1','9/1/1998','g99401','1999-9-1','背景');3向学生表中插入一条数据，班级号是学生表的外键，验证参照完整性约束；insertinto student values('g9940305','wangyu','1','9/1/1998','g99408','1999-9-1','背景');4删除教师表中的所有数据，验证参照完整性约束；deletefrom teacher5创建一个规则，将其绑定到Student表的Sex列上，保证输入的性别值是0或1Createrule value_sex1as@value in('0','1')GoExec sp_bindrule'value_sex1','student.sex'Go6为Student表增加一列，命名为dept,创建一个默认对象，将其绑定到Student表的dept列上，使其默认值为电子院。
Createdefault系默认as'电子院'GoExec sp_bindefault'系默认1','student.dept'Go安全性实验内容与要求：1定义一新的登录帐号、数据库用户，并授予其访问学生选课数据库的读权限；create login aaawith password='123',default_database=shiyancreate user wangyufor login aaawith default_schema=dbo2分别采用Windows NT/2000验证方式和Windows NT/2000及混合验证方式用不同的用户进行登录连接；3分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；insertinto student(student_id,entrance_date)values('g9940215','1999-9-1');4创建数据库角色，并授予访问数据库的读、写权限；sp_addrole'db_read_write1'gograntselecton student to db_read_write1gogrant update on student to db_read_write1gograntinserton student to db_read_write15将角色赋予（1）中定义的用户，建立用户和角色联系；exec sp_addrolemember'db_read_write','wangyu'6再次用此用户访问学生数据库，并对其中的学生表数据进行修改。
实验五：数据完整性实验
实验五：数据完整性实验一、实验目的：掌握使用SQL中的PRIMARY KEY、CHECK、FOREIGN KEY……REFERENCES、NOT NULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。
二、实验步骤：（一）验证分析部分1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性。
在查询分析器窗体下键入如下命令：CREATE TABLE Student(sno CHAR(5) NOT NULL UNIQUE,sname CHAR(8),ssex CHAR(1),sage INT,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno));UPDATE Student SET sno=’ ‘ WHERE sdept=’CS’;UPDATE Student SET sno=’200215121 ‘WHERE sname=’王敏’;运行并观察结果。
2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证实体完整性。
在查询分析器窗体下键入如下命令：(1)先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON update CASCADE;然后执行以下语句Update Student SET sno=’200215128 ‘ WHERE sno=’200215121’;(2) 先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON delete CASCADE;然后执行以下语句delete from student WHERE sno=’200215122’;运行并观察结果。
实验五 数据库完整性与安全性实验
实验五数据库完整性与安全性实验1、实验目的1.通过对完整性规则的定义实现，熟悉了解kingbase中完整性保证的规则和实现方法，加深对数据完整性的理解。
2.通过对安全性相关内容的定义，熟悉了解kingbase中安全性的内容和实现方法，加深对数据库安全性的理解2、实验环境操作系统：Microsoft Windows 7旗舰版（32位）。
数据库版本：MySQL 6.23、实验内容3.1完整性实验：（1）分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；在create table 时已经定义了各个基表的主键。
因此，首先对每个基表删除主键，再添加主键。
实现实体完整性约束。
如下图。
删除主键：添加主键：sc表中的cno和sno分别是course表和student表的外关键字。
下图为给sc表添加student和course的外键，实现参照完整性约束。
alter table sc add constraint fk_student foreign key(sno) references student(sno);alter table sc add constraint fk_course foreign key(cno) references course(cno);主键和外键均添加成功，输入show create table 命令来查看各基表信息。
（2）分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；向课程表中插入课程号为C01的课程。
向学生表中插入学号为30201的学生。
由上面两个实验来看，分别对student和course表插入具有相同学号和相同课程号的学生信息和课程信息，都显示sql语句错误信息为：Duplicate entry *** for key ‘PRIMARY’。
验证了实体完整性约束。
（3）向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；插入成绩信息是学号为31428（student中存在），课程号为C07（course中不存在）的学生成绩信息。
数据库实验报告：实验五
数据库实验报告：实验五一、实验目的本次数据库实验五的主要目的是深入了解和掌握数据库的高级操作，包括存储过程的创建与使用、视图的定义和应用、以及事务处理的原理和实践。
通过这些实验内容，提高我们对数据库系统的综合运用能力，为解决实际的数据库管理问题打下坚实的基础。
二、实验环境本次实验使用的数据库管理系统是 MySQL 80，开发工具为 Navicat Premium 12。
操作系统为 Windows 10 专业版。
三、实验内容及步骤（一）存储过程的创建与使用1、创建一个名为｀get_student_info` 的存储过程，用于根据学生学号查询学生的基本信息（包括学号、姓名、年龄、性别和专业）。
｀｀｀sqlDELIMITER ／／CREATE PROCEDURE get_student_info(IN student_id INT)BEGINSELECT FROM students WHERE student_id ＝ student_id;END ／／DELIMITER ；｀｀｀2、调用上述存储过程，查询学号为 1001 的学生信息。
｀｀｀sqlCALL get_student_info(1001)；｀｀｀（二）视图的定义和应用1、创建一个名为｀student_grade_view` 的视图，用于显示学生的学号、姓名和平均成绩。
｀｀｀sqlCREATE VIEW student_grade_view ASSELECT sstudent_id, sname, AVG(ggrade) AS average_gradeFROM students sJOIN grades g ON sstudent_id ＝ gstudent_idGROUP BY sstudent_id, sname;｀｀｀2、查询上述视图，获取所有学生的平均成绩信息。
｀｀｀sqlSELECT FROM student_grade_view;｀｀｀（三）事务处理1、开启一个事务，向学生表中插入一条新的学生记录（学号：1005，姓名：＿____，年龄：20，性别：男，专业：计算机科学）。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验五数据库的完整性实验（含代码）实验五数据库的完整性与安全性实验一、实验目的使学生了解SQL Server 2005的完整性与安全性机制，并掌握SQL Server 2005中完整性控制和安全管理的方法。
二、实验内容1、在“图书读者数据库”（Book_Reader_DB）中完成完整性控制的相关操作；2、“图书读者数据库”（Book_Reader_DB）中完成安全性控制的相关操作。
三、实验环境与实验准备工作实验环境：Microsoft Windows操作系统，Microsoft SQL Server 2005数据库管理系统标准版或企业版。
实验准备工作：在开始本实验之前，请回顾教科书的相关内容（数据库的完整性）。
四、实验方法与步骤1、使用sql脚本方式删除Book_Reader_DB数据库内的所有键和约束。
Sql脚本示例：Use Book_Reader_DB;Goalter table表名drop constraint键名|约束名;2、使用sql脚本定义刚刚删除的所有键和约束（实体完整性、参照完整性和用户自定义完整性约束）。
Sql脚本示例：alter table 表名add constraint 键名|约束名（三类完整性约束）要求：参照完整性要定义违约处理策略。
3、定义Defaul约束将Read表中“性别”的默认值改为“男”，然后输入数据验证。
（1）在企业管理器中完成。
（2）在查询分析器中完成（sql脚本完成）。
4、Unique 约束将Book表中“作者”加上唯一性约束，然后输入数据验证,并分析唯一性约束与主键约束的区别。
（1）在企业管理器中完成。
（2）在查询分析器中完成。
5、使用sql语句操作数据库，验证以上定义的所有约束，掌握DBMS的完整性控制机制。
上述每项实验内容相应的实验步骤必须进行详细的记录，并将其整理后写在实验报告中。
五、实验报告要求1、根据实验内容和每一步骤实验的结果，按课程实验报告的撰写规范完成实验报告。
2、实验报告至少包括以下内容：①实验目的；②实验环境；③实验内容、步骤、结果和实验过程中出现的问题；④实验过程中出现的问题。
--创建表时没有写约束名，怎么删除约束条件--not null约束条件不能用这种方法删除select * from bookselect * from borrowselect * from readeralter table studentdrop constraint afalter table student alter column sno nvarchar(50) nullcreate table student(sno nvarchar(50)constraint af unique,)drop table studentalter table studentadd--创建三张带有约束名的表Create table Book(bno nvarchar(50)constraint c1 primary key(bno), bclass nvarchar(50) constraint c2 not null, publisher nvarchar(50) constraint c3 not null,author nvarchar(50) constraint c4 not null, bname nvarchar(50) constraint c6 unique(bname), price floatconstraint c5 not null, remark nvarchar(50),num nvarchar(50),date datetime,)Create table Reader(rno nvarchar(50)constraint ca primary key(rno), rname nvarchar(50) constraint cb not null, department nvarchar(50) constraint cc not null,sex varchar(2)constraint cd check(sex in('男','女')),telephone nvarchar(50)constraint ce not null)Create table Borrow(bno nvarchar(50),rno nvarchar(50),bdata datetime,Back_date datetime,constraint cq primary key(bno,rno),constraint cw foreign key(bno) references Book(bno), constraint ce foreign key(rno) references Reader(rno) ) --第一题--删除book表的约束条件alter table book drop constraint c1alter table book alter column bclass nvarchar(50) nullalter table book alter column publisher nvarchar(50) nullalter table book alter column author nvarchar(50) nullalter table book alter column price float nullalter table book drop constraint c6--删除borro表的约束条件,必须先删除borrow表,alter table borrow drop constraint cqalter table borrow drop constraint cwalter table borrow drop constraint ce--删除reader表的约束条件alter table reader drop constraint caalter table reader alter column rname nvarchar(50) nullalter table reader alter column department nvarchar(50) nullalter table reader alter column telephone nvarchar(50) null alter table reader drop constraint cd--第二题--添加book的约束条件alter table book add constraint c1 primary key(bno);alter table book add constraint c6 unique(bname);alter table book alter column bclass nvarchar(50) not nullalter table book alter column publisher nvarchar(50) not null alter table book alter column author nvarchar(50) not nullalter table book alter column price float not null--添加borrow表的约束条件,alter table borrow add constraint cq primary key(bno,rno);alter table borrow add constraint cw foreign key(bno) references Book(bno); alter table borrow add constraint ce foreign key(rno) references Reader(rno);--添加的reader约束条件alter table reader add constraint ca primary key(rno);alter table reader alter column rname nvarchar(50) not null alter table reader alter column department nvarchar(50) not nullalter table reader alter column telephone nvarchar(50) not nullalter table reader add constraint cd check(sex in('男','女'));alter table reader add constraint cf default '男' for sex;--ADDCONSTRAINTDE_性别DEFAULT'男'FOR性别--ALTER TABLE wfwangTable DRO PCONSTRAINTDE_性别--第三题alter table reader add constraint cf default '男' for sex;--第四题alter table book add constraint c7 unique(author);/**在“图书”表中，增加两个字段，分别为“数量”和“购买日期”和在“借阅”表中增加一个“还书日期”字段*/Alter table Reader add constraint cons_sex_chk check(sex in ('男','女'))Alter table Book add num int not nullAlter table Book add date datetime not nullAlter table Borrow add Back_date datetime not nullinsert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0001','计算机','清华大学出版社','严蔚敏','数据结构','15.00','','10','1999-05-08')insert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0002','计算机','清华大学出版社','单建魁','数据库系统实验指导','15','','30','2000-03-01') insert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0003','环境','南京大学出版社','王小容','环境化学','32.00','','20','2000-06-09')insert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0004','环境','清华大学出版社','何强','环境学导论','35.00','','20','2000-06-12')insert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0005','计算机','电子工业出版社','傅清祥','算法与数据结构','36.70','','10','2000-07-15') insert into Book(bno,bclass,publisher,author,bname,price,remark,num,date) values('0006','计算机','清华大学出版社','蔡自兴','人工智能及其应用','28.00','','10','2000-09-17')insert into Reader(rno,rname,department,sex,telephone)values('0001','张红','电气学院','女','010********')insert into Reader(rno,rname,department,sex,telephone) values('0002','杨小鹏','信息学院','男','010********') insert into Reader(rno,rname,department,sex,telephone) values('0003','王俊','信息学院','男','010********') insert into Reader(rno,rname,department,sex,telephone) values('0004','刘建','建筑学院','男','010********')insert into Borrow(bno,rno,bdata,Back_date)values('0001','0002','2001-9-2','2001-10-1')insert into Borrow(bno,rno,bdata,Back_date)values('0002','0002','2001-9-12','2001-10-11')insert into Borrow(bno,rno,bdata,Back_date)values('0006','0002','2002-1-12','2002-2-1')insert into Borrow(bno,rno,bdata,Back_date)values('0003','0001','2001-7-10','2001-8-7')insert into Borrow(bno,rno,bdata,Back_date)values('0004','0001','2001-7-2','2001-7-22')insert into Borrow(bno,rno,bdata,Back_date)values('0005','0001','2001-9-15','2001-9-15')insert into Borrow(bno,rno,bdata,Back_date)values('0002','0003','2001-10-15','2001-11-1')insert into Borrow(bno,rno,bdata,Back_date)values('0006','0003','2002-2-10','2002-2-20')insert into Borrow(bno,rno,bdata,Back_date)values('0003','0004','2001-8-17','2001-8-29')insert into Borrow(bno,rno,bdata,Back_date)values('0004','0004','2001-7-25','2001-8-15')drop table book restrictdrop table borrowdrop table bookdrop table reader select *from Book select *from reader select *from borrow。