0
0

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

实验三 完整性语言实验

实验 3.1 实体完整性实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

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

4.实验过程

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

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

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

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

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

CREATETABLE student(
    Sno CHAR(9),    
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(10),PRIMARYKEY(Sno));

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

​ 定义课程表。

CREATETABLE Course(    
    Cno CHAR(9),
    Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT);ALTERTABLE Course
ADDPRIMARYKEY(Cno);

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

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

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

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

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

CREATETABLE department(
    Dno CHAR(9)PRIMARYKEY,
    Dname CHAR(25)UNIQUE,
    DeanNo CHAR(9)UNIQUE);

(6)删除实体完整性

​ 删除院系实体的主码。

ALTERTABLE department DROPPRIMARYKEY;

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

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

INSERTINTO SC
VALUES("202004061","2",80);INSERTINTO SC
VALUES("202004061","2",81);

在这里插入图片描述

5.实验总结

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

6.思考题

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

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

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

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

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

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

实验 3.2 参照完整性实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

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

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

4.实验过程

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

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

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

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

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

/*Cpno参照于Cno*/ALTERTABLE Course
ADDCONSTRAINT FK_Course_cpno
FOREIGNKEY(Cpno)REFERENCES Course(Cno);

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

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

CREATETABLE appeal_record(
    Aidx CHAR(10),
    Asno CHAR(9),
    Acno CHAR(9),CONSTRAINT FK_appeal_record FOREIGNKEY(Asno,Acno)REFERENCES SC(Sno,Cno));

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

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

CREATETABLE Course(    
    Cno CHAR(9)PRIMARYKEY,
    Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),Constraint FK_Course FOREIGNKEY(Ctno)REFERENCES teacher(tno)ONDELETESETNULLONUPDATESETNULL);

(5)删除参照完整性

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

ALTERTABLE Course DROPCONSTRAINT FK_course_cpno;

(6)验证参照完整性

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

INSERTINTO Course(Cno,Ctno)VALUES("105","000000000");

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

5.实验总结

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

6.思考题

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

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

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

CREATETABLE Course(    
    Cno CHAR(9)PRIMARYKEY,
    Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),Constraint FK_Course FOREIGNKEY(Ctno)REFERENCES teacher(tno));/*Cpno参照于Cno*/ALTERTABLE Course
ADDCONSTRAINT FK_Course_cpno
FOREIGNKEY(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属性。

CREATETABLE Course(    
    Cno CHAR(9)PRIMARYKEY,
    Cname CHAR(20)NOTNULL,/*课程名非空*/
    Cpno CHAR(9)NULL,/*先修课可为空*/
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT,
    Ctno CHAR(9),Constraint FK_Course FOREIGNKEY(Ctno)REFERENCES teacher(tno));

(2)定义属性DEFAULT约束

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

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

(3)定义属性UNIQUE约束

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

CREATETABLE department(
    Dno CHAR(9)PRIMARYKEY,
    Dname CHAR(25)UNIQUE,
    DeanNo CHAR(9)UNIQUE);

(4)使用CHECK

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

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

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

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

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

标签： 数据库 sql mysql
本文转载自: https://blog.csdn.net/Aaron503/article/details/128280123
版权归原作者 A橙_ 所有， 如有侵权，请联系我们删除。
登录后发布评论

“数据库实验三：完整性语言实验”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航