数据库实验三：完整性语言实验

实验三 完整性语言实验

实验 3.1 实体完整性实验

1.实验目的

​ 掌握实体完整性的定义和维护方法。

2.实验内容和要求

​ 定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的SQL语句；创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。

3.实验重点和难点

​ 实验重点：创建表时定义实体完整性。
​ 实验难点：有多个候选码时实体完整性的定义。

4.实验过程

(1)创建表时定义完整性(列级实体完整性)

​ 定义学生表的实体完整性。

CREATE TABLE student(
	Sno CHAR(9) PRIMARY KEY,	/*MYSQL中实体完整性约束不可以命名*/
    Sname CHAR(20),
    Ssex CHAR(2),
    Ssage SMALLINT,
	Sdept CHAR(10)
);

(2)创建表时定义实体完整性(表级实体完整性)

​ 定义学生表的实体完整性。

CREATE TABLE student(
	Sno CHAR(9),	
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
	Sdept CHAR(10),
    PRIMARY KEY(Sno)
);

(3)创建表后定义实体完整性

​ 定义课程表。

CREATE TABLE Course(	
	Cno CHAR(9),
	Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT
);	
ALTER TABLE Course
ADD PRIMARY KEY(Cno);

(4)定义实体完整性(主码由多个属性组成)

​ 定义选课表的实体完整性。

CREATE TABLE SC(
	Sno CHAR(9),
	Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno)		/*主码多个属性组成，必须在表级定义*/
);

(5)有多个候选码时定义实体完整性

​ 定义院系表的实体完整性，其中院系编码Dno和Dname，DeanNo都是候选码，选择Dno作为主码，Dname上定义唯一性约束。

CREATE TABLE department(
	Dno CHAR(9) PRIMARY KEY,
    Dname CHAR(25) UNIQUE,
    DeanNo CHAR(9) UNIQUE
);

(6)删除实体完整性

​ 删除院系实体的主码。

ALTER TABLE department DROP PRIMARY KEY;

(7)增加两条相同记录，验证实体完整性是否起作用

​ 插入两条主码相同的记录，会违反实体完整性约束。

INSERT INTO SC
VALUES("202004061","2",80);
INSERT INTO SC
VALUES("202004061","2",81);

5.实验总结

​ 通过实验练习了定义实体完整性的列级定义，表级定义等方法。加深了对实体完整性的理解。

6.思考题

(1)所有列级完整性约束都可以改写为表级完整性约束，而表级完整性约束不一定能改写成列级完整性约束。举例说明。

​ 当主码由多个属性构成时，就只能使用表级完整性约束，而不能改写为列级完整性约束，因为列级完整性约束只能对一个属性进行完整性约束。例如实验过程(4)中的完整性约束定义。

(2)什么情况下会违反实体完整性约束，DBMS将做何种违约处理？用实验验证。

​ 检查主码的各个属性是否为空，有一个为空就拒绝插入或修改。

​ 使用实验过程(5)中定义的表进行验证，这个表中的每个属性都是候选码，其中Dno定义为主码。进行以下验证：

INSERT INTO department(Dname,DeanNo)
VALUES("计算机系","215486210");
/*主码为空，拒绝执行*/
INSERT INTO department(Dno,DeanNo)
VALUES("20","215486210");
/*主码不为空，候选码Dname为空，可以插入*/
INSERT INTO department(Dno,DeanNo)
VALUES("20","215486212");
/*主码不唯一，拒绝执行*/
INSERT INTO department(Dno,DeanNo)
VALUES("21","215486210");
/*候选码DeanNo不唯一，拒绝执行*/

实验 3.2 参照完整性实验

1.实验目的

​ 掌握参照完整性的定义和维护方法。

2.实验内容和要求

​ 定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的SQL语句；创建表时定义参照完整性，创建表后定义参照完整性。

3.实验重点和难点

​ 实验重点：创建表时定义参照完整性。

​ 实验难点：参照完整性的违约处理定义。

4.实验过程

(1)创建表时定义参照完整性

​ 定义教师表的实体完整性，再定义课程表的参照完整性。

/*教师表的主键为Tno*/
CREATE TABLE teacher(
	Tno CHAR(9),
    Tname CHAR(25),
    Tsex CHAR(2),
    Tage SMALLINT,
    Tdept CHAR(20),
    Ttitle CHAR(20),
    PRIMARY KEY(Tno)
)
/*列级定义参照完整性*/
CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Cpno CHAR(9),
    Ctno CHAR(9) REFERENCES teacher(tno
);
/*表级定义参照完整性*/
CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno)
);

(2)创建表后定义参照完整性

​ 定义课程表Course的参照完整性，被参照关系是表自身。

/*Cpno参照于Cno*/
ALTER TABLE Course
ADD CONSTRAINT FK_Course_cpno
FOREIGN KEY(Cpno) REFERENCES Course(Cno);

(3)定义参照完整性(外码由多个属性组成)

​ 定义一个成绩申诉记录表，主码为记录号，外码为Sno和Cno。

CREATE TABLE appeal_record(
	Aidx CHAR(10),
    Asno CHAR(9),
    Acno CHAR(9),
    CONSTRAINT FK_appeal_record FOREIGN KEY(Asno,Acno) REFERENCES SC(Sno,Cno)
);

(4)定义参照完整性的违约处理

​ 定义课程表的参照完整性，当被参照表被修改或删除时，设置参照表中相应记录的值为空值。

CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno) ON DELETE SET NULL ON UPDATE SET NULL
);

(5)删除参照完整性

​ 删除课程表参照自身的外码的参照完整性。

ALTER TABLE Course DROP CONSTRAINT FK_course_cpno;

(6)验证参照完整性

​ 插入一个课程，tno为"000000000"的记录在教师表中不存在，违反参照完整性约束。

INSERT INTO Course(Cno,Ctno)
VALUES("105","000000000");

​ 因为违反参照完整性约束，拒绝执行该语句。

5.实验总结

​ 参照完整性类似实体完整性，可以在列级进行定义，也可以在表级进行定义。如果插入的元组违反参照完整性约束，默认将拒绝执行。也可以在定义参照完整性时定义违约处理。

6.思考题

​ 对于自引用表，例如课程表中的先修课程号引用该表的课程号，完成如下任务：

(1)写出课程表上的实体完整性和参照完整性。

​ 在实验过程(1)(2)中已完成了课程表的实体完整性和参照完整性的定义。对于自身的引用，需要在建立表之后添加参照完整性约束。

CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno)
);
/*Cpno参照于Cno*/
ALTER TABLE Course
ADD CONSTRAINT FK_Course_cpno
FOREIGN KEY(Cpno) REFERENCES Course(Cno);

(2)在考虑实体完整性约束的情况下，试举出几种录入课程数据的方法。

​ 可以按照顺序插入课程数据，保证先修课程号存在。或者先插入课程数据，先修课程号将为空值，最后再修改先修课程号。

实验3.3 用户自定义完整性实验

1.实验目的

​ 掌握用户自定义完整性的定义和维护方法。

2.实验内容和要求

​ 针对具体应用语义，选择NULL/NOT NULL、DEFAULT、UNIQUE、CHECK等，定义属性上的约束条件。

3.实验重点和难点

​ 实验重点：NULL/NOT NULL，DEFAULT。

​ 实验难点：CHECK。

4.实验过程

(1)定义属性NULL/NOT NULL约束

​ 定义课程表各属性的NULL/NOT NULL属性。

CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20) NOT NULL,	/*课程名非空*/
    Cpno CHAR(9) NULL,			/*先修课可为空*/
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno)
);

(2)定义属性DEFAULT约束

​ 定义课程表的Ctype缺省属性值为为通识选修课。

CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20) NOT NULL,	/*课程名非空*/
    Cpno CHAR(9) NULL,			/*先修课可为空*/
    Ctype CHAR(10) DEFAULT "通识选修",
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno)
);

(3)定义属性UNIQUE约束

​ 定义院系表的名称属性Dname和院长工号属性DeanNo必须唯一的完整性约束。

CREATE TABLE department(
	Dno CHAR(9) PRIMARY KEY,
    Dname CHAR(25) UNIQUE,
    DeanNo CHAR(9) UNIQUE
);

(4)使用CHECK

​ 使用CHECK定义课程表中某些属性应该满足的约束。

CREATE TABLE Course(	
	Cno CHAR(9) PRIMARY KEY,
	Cname CHAR(20) NOT NULL,	/*课程名非空*/
    Cpno CHAR(9) NULL,			/*先修课可为空*/
    Ctype CHAR(10) DEFAULT "通识选修",
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),
    Constraint FK_Course FOREIGN KEY(Ctno) REFERENCES teacher(tno),
    CHECK(Ctype IN ("通识选修","学类核心","学门核心","专业核心","专业选修")),		/*课程只有这几个类型*/
    CHECK(Ccredit>=0 AND Ccredit<=5)										 /*课程学分在0到5之间*/
);

(5)修改课程表的一条记录验证违反CHECK约束

​ 向课程表插入一条记录，不指定学分，该元组插入后将在Ccredit属性取空值，再修改ccredit为6。

INSERT INTO Course(Cno,Cname)
VALUES("1001","COURSE1");
UPDATE Course
SET Ccredit=6
WHERE Cno="1001";

​ 违反CHECK约束，拒绝执行修改语句。

5.实验总结

​ 用户自定义完整性主要包括NULL，NOT NULL，DEFAULT，UNIQUE，CHECK。创建表时，如果没有指定NOT NULL，属性默认可为NULL。使用CHECK约束后，违反约束的语句将拒绝执行。

6.思考题

(1)请分析哪些完整性约束只针对单个属性，哪些完整性约束可以针对多个属性？哪些只针对一个表，哪些针对多个表？

​ 在用户自定义完整性中，NULL/NOT NULL，DEFAULT，只针对单个属性，UNIQUE和CHECK可以针对单个属性，也可以针对多个属性。而实体完整性和参照完整性，既可以针对单个属性，也可以针对多个属性。用户自定义完整性和实体完整性针对一个表，参照完整性针对的是多个表。

(2)对表中某一列数据类型进行修改时，要修改的列是否必须为空列？

​ 在MYSQL中，修改数据类型，如果可以将数据转换到修改的类型，例如将SMALLINT修改到足够长度的CHAR，列就不必须为空。如果不能转换数据到修改的类型，列就必须为空。
UNIQUE，CHECK。创建表时，如果没有指定NOT NULL，属性默认可为NULL。使用CHECK约束后，违反约束的语句将拒绝执行。

6.思考题

(1)请分析哪些完整性约束只针对单个属性，哪些完整性约束可以针对多个属性？哪些只针对一个表，哪些针对多个表？

​ 在用户自定义完整性中，NULL/NOT NULL，DEFAULT，只针对单个属性，UNIQUE和CHECK可以针对单个属性，也可以针对多个属性。而实体完整性和参照完整性，既可以针对单个属性，也可以针对多个属性。用户自定义完整性和实体完整性针对一个表，参照完整性针对的是多个表。

(2)对表中某一列数据类型进行修改时，要修改的列是否必须为空列？

​ 在MYSQL中，修改数据类型，如果可以将数据转换到修改的类型，例如将SMALLINT修改到足够长度的CHAR，列就不必须为空。如果不能转换数据到修改的类型，列就必须为空。