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

实验三 完整性语言实验

实验 3.1 实体完整性实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

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

4.实验过程

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

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

CREATE TABLE student(Sno CHAR(9) PRIMARY KEY,    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 CourseADD 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 SCVALUES("202004061","2",80);INSERT INTO SCVALUES("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");INSERT INTO department(Dno,DeanNo)VALUES("20","215486212");INSERT INTO department(Dno,DeanNo)VALUES("21","215486210");

实验 3.2 参照完整性实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

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

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

4.实验过程

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

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

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的参照完整性，被参照关系是表自身。

ALTER TABLE CourseADD CONSTRAINT FK_Course_cpnoFOREIGN 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));ALTER TABLE CourseADD CONSTRAINT FK_Course_cpnoFOREIGN 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) );

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

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

INSERT INTO Course(Cno,Cname)VALUES("1001","COURSE1");UPDATE CourseSET Ccredit=6WHERE 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，列就不必须为空。如果不能转换数据到修改的类型，列就必须为空。

来源地址：https://blog.csdn.net/Aaron503/article/details/128280123