• 数据库系统原理实验报告4 | 数据完整性

    整理自博主本科《数据库系统原理》专业课自己完成的实验报告，以便各位学习数据库系统概论的小伙伴们参考、学习。

    专业课本：

    ————

    本次实验使用到的图形化工具：Heidisql

    目录

    一、实验目的

    二、实验内容

    1、建表

       2、对1题中创建的Student表，增加以下约束：

    1）姓名不能为空

    2）性别默认为‘男’

    3）性别取值只能为‘男’、‘女’

    4）学生年龄大于10、小于40

    5）学生表中的所在院系sdname参照department表的dname，删除规则是级联，更新规则是拒绝；学生表中的专业spec参照speciality表的specno，删除规则是设置为空，更新规则是级联。

     3、完整性检验

    1）实体完整性

    2）用户定义完整性检验

    3）参照完整性检验

    4、完整SQL代码

    三、实验结果总结

    四、实验结果的运用

    一、实验目的

    1、熟练使用SQL语句创建表和修改表，巩固数据定义语句。

    2、通过SQL语句验证数据库的三类完整性约束，尤其是参照完整性，加深对于完整性的理解。

    3、初步掌握数据更新语句：插入，删除和更新记录。

    二、实验内容

    创建一个名为TEST数据库，要求如下：

    （下面三个表中属性的数据类型需要自己设计合适的数据类型）

    1、建表

    • 建立专业表speciality，它由专业号specno、专业名specname组成，其中专业号为主键，采用 列级定义主键，专业名不能为空。
    • 建立院系表department，它由院名dname、院长dean、院职工人数dnum组成。其中院名为主属性，采用表级定义主键。
    • 建立一个“学生”表Student，它由学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec六个属性组成。采用表级定义主键。

    代码：

    1. #建立专业表speciality，它由专业号specno、专业名specname组成，其中专业号为主键，采用列级定义主键，专业名不能为空。
    2. CREATE TABLE speciality(
    3. 	specno INT PRIMARY KEY,
    4. 	specname CHAR(20) NOT NULL
    5. );
    6.  
    7. #建立院系表department，它由院名dname、院长dean、院职工人数dnum组成。其中院名为主属性，采用表级定义主键。
    8. CREATE TABLE department(
    9. 	dname CHAR(20) , 
    10. 	dean CHAR(10) ,
    11. 	dnum INT ,
    12. 	PRIMARY KEY (dname)
    13. );
    14.  
    15. #建立一个“学生”表Student，它由学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec六个属性组成。采用表级定义主键。
    16. CREATE TABLE student(
    17. 	sno CHAR(9),
    18. 	sname CHAR(20),
    19. 	ssex CHAR(2),
    20. 	sage SMALLINT, 
    21. 	sdname CHAR(20),
    22. 	spec INT,
    23. 	PRIMARY KEY (sno)
    24. )

       2、对1题中创建的Student表，增加以下约束：

    1）姓名不能为空

    代码：

    1. #姓名不能为空
    2. ALTER TABLE student
    3. 		MODIFY COLUMN sname CHAR(20) NOT NULL;

    2）性别默认为‘男’

    代码：

    1. #性别默认为‘男’
    2. ALTER TABLE student
    3. 		MODIFY COLUMN ssex CHAR(2) DEFAULT '男';

    3）性别取值只能为‘男’、‘女’

     

    代码：

    1. #性别取值只能为‘男’、‘女’
    2. ALTER TABLE student
    3. 		ADD CONSTRAINT CHECK (ssex='男'OR ssex='女');

    4）学生年龄大于10、小于40

    代码：

    1. #学生年龄大于10、小于40
    2. ALTER TABLE student
    3. 		ADD CONSTRAINT CHECK (sage>10 AND sage<40);

    5）学生表中的所在院系sdname参照department表的dname，删除规则是级联，更新规则是拒绝；学生表中的专业spec参照speciality表的specno，删除规则是设置为空，更新规则是级联。

    代码：

    1. ALTER TABLE student
    2. 		ADD FOREIGN KEY (sdname) REFERENCES department(dname) ON DELETE CASCADE ON UPDATE NO ACTION ;

    代码：

    1. ALTER TABLE student
    2. 		ADD FOREIGN KEY (spec) REFERENCES speciality(specno) ON DELETE SET NULL ON UPDATE CASCADE ;

     3、完整性检验

    1）实体完整性

    （1）用SQL语句，在学生表中插入一条学号为空的记录和重复学号的记录，观察运行的情况。说明为什么。

    代码：

    1. #实体完整性检验
    2. INSERT INTO student (sno) VALUES (NULL);#用SQL语句，在学生表中插入一条学号为空的记录
    3. INSERT INTO student (sno) VALUES (493);
    4. INSERT INTO student (sno) VALUES (493);#插入重复学号的记录

    说明：关系模型的实体完整性用primary key定义，主码任一主属性不可为空且主码值必须唯一。定义了关系主码后，每当用户程序对基本表插入一条记录或对主码进行更新操作时，关系数据库按照实体完整性规则进行检查。若主码值不唯一，则拒绝插入或修改；若有一个主属性为空，则拒绝插入或修改。

    2）用户定义完整性检验

    （1）用SQL语句，在每个表中分别插入两条合法记录。

    代码：

    1. #用户定义完整性检验
    2. #用SQL语句，先在每个表中分别插入两条合法记录。
    3. INSERT INTO speciality VALUES (1001,'软件工程');
    4. INSERT INTO speciality VALUES (1002,'汉语言文学');
    5. #在专业表中插入两条记录  
    6.  
    7. #department，它由院名dname、院长dean、院职工人数dnum组成。其中院名为主属性，采用表级定义主键。
    8. INSERT INTO department VALUES ('软件学院','张三',50);
    9. INSERT INTO department VALUES ('文学院','李四',25);
    10. #在院系表中插入两条记录
    11.  
    12. #Student，它由学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec六个属性组成。采用表级定义主键
    13. INSERT INTO student VALUES (30001,'喜羊羊',DEFAULT,15,'软件学院',1001);
    14. INSERT INTO student VALUES (30002,'魔法少女小樱','女',17,'文学院',1002);

    （2）用SQL语句，非法的记录来检验第4题中的用户定义完整性约束条件。

    代码：

    1. #插入非法的记录来检验第4题中的用户定义完整性约束条件。
    2. INSERT INTO speciality(specname) VALUES (NULL);#令专业表专业名为空
    3. INSERT INTO student(ssex) VALUES ('未知');#令学生表性别为除“男”与“女”之外的“未知”
    4. INSERT INTO student(sage) VALUES (100);#令学生表年龄为100，不在预设范围内

    3）参照完整性检验

    （1）用SQL语句，插入：分别在三个表中插入若干条记录。在学生表中插入记录时，注意sdname要参照院系表，spec要参照专业表。

    代码：

    1. #参照完整性检验
    2. #分别在三个表中插入若干条记录。在学生表中插入记录时，注意sdname要参照院系表，spec要参照专业表。
    3. #学生表中的所在院系sdname参照department表的dname，删除规则是级联，更新规则是拒绝；
    4. #学生表中的专业spec参照speciality表的specno，删除规则是设置为空，更新规则是级联.
    5. INSERT INTO student(sdname,spec) VALUES ('外语学院',6666);#在参照表学生表中插入的元组的sdname,spec在被参照表中不存在

    （2）用SQL语句，删除和修改：删除专业表中的，被学生表参照的一个专业记录，观察学生表中发生了什么变化，为什么？

    代码：

    1. #用SQL语句，删除和修改：删除专业表中的，被学生表参照的一个专业记录，观察学生表中发生了什么变化，为什么？
    2. DELETE FROM speciality WHERE specno=1001;

    说明：

    学生表中所有包含专业号“1001”的元组都被删除。

    在参照完整性中定义了级联删除。学生表是参照表，专业表是被参照表。当删除被参照表的一个元组导致与参照表不一致时，删除参照表中所有导致不一致的元组。

    （3）修改专业表中的，被学生表参照的一个专业号，观察学生表中发生了什么变化，为什么？

    代码：

    1. #修改专业表中的，被学生表参照的一个专业号，观察学生表中发生了什么变化，为什么？
    2. UPDATE speciality SET specno=9999 WHERE specno=1001;

    说明：

    学生表中所有包含专业号“1001”的元组都被修改为“9999”。

    在参照完整性中定义了级联修改。学生表是参照表，专业表是被参照表。当修改被参照表的一个元组导致与参照表不一致时，修改参照表中所有导致不一致的元组。

    （4）用SQL语句，删除和修改：删除院系表中的，被学生表参照的一条记录，观察学生表中发生了什么变化，为什么？

    代码：

    1. #删除院系表中的，被学生表参照的一条记录，观察学生表中发生了什么变化，为什么？
    2. DELETE FROM department WHERE dname='软件学院';

    说明：

    院系表中所有包含院系名“软件学院”的元组都被删除。

    在参照完整性中定义了级联删除。学生表是参照表，专院系表是被参照表。当删除被参照表的一个元组导致与参照表不一致时，删除参照表中所有导致不一致的元组。

    （5）修改院系表中的，被学生表参照的一条记录的院系名，观察学生表中发生了什么变化，为什么？

    代码：

    1. #修改院系表中的，被学生表参照的一条记录的院系名，观察学生表中发生了什么变化，为什么？
    2. UPDATE department SET dname='历史学院' WHERE dname='文学院';

    说明：定义了拒绝执行。当修改导致参照表与被参照表不一致时，不允许该操作的执行。 

    4、完整SQL代码

    1. CREATE DATABASE test;
    2. USE test;
    3. SELECT DATABASE();  
    4.  
    5. #建立专业表speciality，它由专业号specno、专业名specname组成，其中专业号为主键，采用列级定义主键，专业名不能为空。
    6. CREATE TABLE speciality(
    7. 	specno INT PRIMARY KEY,
    8. 	specname CHAR(20) NOT NULL
    9. );
    10.  
    11. #建立院系表department，它由院名dname、院长dean、院职工人数dnum组成。其中院名为主属性，采用表级定义主键。
    12. CREATE TABLE department(
    13. 	dname CHAR(20) , 
    14. 	dean CHAR(10) ,
    15. 	dnum INT ,
    16. 	PRIMARY KEY (dname)
    17. );
    18.  
    19. #建立一个“学生”表Student，它由学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec六个属性组成。采用表级定义主键。
    20. CREATE TABLE student(
    21. 	sno CHAR(9),
    22. 	sname CHAR(20),
    23. 	ssex CHAR(2),
    24. 	sage SMALLINT, 
    25. 	sdname CHAR(20),
    26. 	spec INT,
    27. 	PRIMARY KEY (sno)
    28. )
    29.  
    30. #对3题中创建的Student表，增加以下约束：
    31. #姓名不能为空
    32. ALTER TABLE student
    33. 		MODIFY COLUMN sname CHAR(20) NOT NULL;
    34. #性别默认为‘男’
    35. ALTER TABLE student
    36. 		MODIFY COLUMN ssex CHAR(2) DEFAULT '男';
    37. #性别取值只能为‘男’、‘女’
    38. ALTER TABLE student
    39. 		ADD CONSTRAINT CHECK (ssex='男'OR ssex='女');
    40. #学生年龄大于10、小于40
    41. ALTER TABLE student
    42. 		ADD CONSTRAINT CHECK (sage>10 AND sage<40);
    43. #学生表中的所在院系sdname参照department表的dname，删除规则是级联，更新规则是拒绝；
    44. #学生表中的专业spec参照speciality表的specno，删除规则是设置为空，更新规则是级联.
    45. ALTER TABLE student
    46. 		ADD FOREIGN KEY (sdname) REFERENCES department(dname) ON DELETE CASCADE ON UPDATE NO ACTION ;
    47. ALTER TABLE student
    48. 		ADD FOREIGN KEY (spec) REFERENCES speciality(specno) ON DELETE SET NULL ON UPDATE CASCADE ; 
    49.  
    50. ALTER TABLE student
    51. 		MODIFY COLUMN sname CHAR(20) NOT NULL ;
    52.  
    53. #实体完整性检验
    54. INSERT INTO student (sno) VALUES (NULL);#用SQL语句，在学生表中插入一条学号为空的记录
    55. INSERT INTO student (sno) VALUES (493);
    56. INSERT INTO student (sno) VALUES (493);#插入重复学号的记录
    57.  
    58. #学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec
    59.  
    60. #用户定义完整性检验
    61. #用SQL语句，先在每个表中分别插入两条合法记录。
    62. INSERT INTO speciality VALUES (1001,'软件工程');
    63. INSERT INTO speciality VALUES (1002,'汉语言文学');
    64. #在专业表中插入两条记录  
    65.  
    66. #department，它由院名dname、院长dean、院职工人数dnum组成。其中院名为主属性，采用表级定义主键。
    67. INSERT INTO department VALUES ('软件学院','张三',50);
    68. INSERT INTO department VALUES ('文学院','李四',25);
    69. #在院系表中插入两条记录
    70.  
    71. #Student，它由学号sno、姓名sname、性别ssex、年龄sage、所在院系sdname、专业spec六个属性组成。采用表级定义主键
    72. INSERT INTO student VALUES (30001,'喜羊羊',DEFAULT,15,'软件学院',1001);
    73. INSERT INTO student VALUES (30002,'魔法少女小樱','女',17,'文学院',1002);
    74.  
    75. #插入非法的记录来检验第4题中的用户定义完整性约束条件。
    76. INSERT INTO speciality(specname) VALUES (NULL);#令专业表专业名为空
    77. INSERT INTO student(ssex) VALUES ('未知');#令学生表性别为除“男”与“女”之外的“未知”
    78. INSERT INTO student(sage) VALUES (100);#令学生表年龄为100，不在预设范围内		   
    79. 		
    80. #参照完整性检验
    81. #分别在三个表中插入若干条记录。在学生表中插入记录时，注意sdname要参照院系表，spec要参照专业表。
    82. #学生表中的所在院系sdname参照department表的dname，删除规则是级联，更新规则是拒绝；
    83. #学生表中的专业spec参照speciality表的specno，删除规则是设置为空，更新规则是级联.
    84. INSERT INTO student(sdname,spec) VALUES ('外语学院',6666);#在参照表学生表中插入的元组的sdname,spec在被参照表中不存在
    85.  
    86. #用SQL语句，删除和修改：删除专业表中的，被学生表参照的一个专业记录，观察学生表中发生了什么变化，为什么？
    87. DELETE FROM speciality WHERE specno=1001;
    88. #修改专业表中的，被学生表参照的一个专业号，观察学生表中发生了什么变化，为什么？
    89. UPDATE speciality SET specno=9999 WHERE specno=1001;
    90.  
    91. #删除院系表中的，被学生表参照的一条记录，观察学生表中发生了什么变化，为什么？
    92. DELETE FROM department WHERE dname='软件学院'; 
    93.  
    94. #修改院系表中的，被学生表参照的一条记录的院系名，观察学生表中发生了什么变化，为什么？
    95. UPDATE department SET dname='历史学院' WHERE dname='文学院';

    三、实验结果总结

    1. 本次实验过程中，我除了语法错误（如在创建表的倒数第二行加了逗号，把primary key写为primary）外，没有其它的错误。
    2. 巩固了数据定义语句，上机操作实验代码，加深对语句的熟悉，记得更牢了。基本能够熟练使用SQL语句进行create创建表和alter修改表。
    3. 通过SQL语句验证了数据库的三类完整性约束。结合前两个礼拜的理论部分和这礼拜学的SQL语言实践部分，对于完整性的理解更加深入。搞懂了参照完整性的概念和机制。
    4. 学会掌握数据更新语句插入，删除和更新记录，以及参照完整性中外键的更新规则级联、删除和设为空值的运用。

    四、实验结果的运用

    使用实验三中的Student、Course、SC表。

    基本建表代码：

    1. CREATE DATABASE educ;
    2. USE educ;
    3. SELECT DATABASE();
    4.  
    5. CREATE TABLE Student(
    6. 	Sno CHAR(9) PRIMARY KEY ,
    7. 	Sname CHAR(20) UNIQUE ,
    8. 	Ssex CHAR(2) DEFAULT'男',
    9. 	Sage SMALLINT,
    10. 	Sdept CHAR(20) 
    11. );
    12.  
    13. CREATE TABLE Course(
    14. 	Cno CHAR(4) NOT NULL PRIMARY KEY ,
    15. 	Cname CHAR(40) NOT NULL ,
    16. 	Cpno CHAR(4) ,
    17. 	Ccredit SMALLINT ,
    18. 	FOREIGN KEY (Cpno) REFERENCES Course(Cno)
    19. );
    20.  
    21. CREATE TABLE SC(
    22. 	Sno CHAR(9) ,
    23. 	Cno CHAR(4) ,
    24. 	Grade SMALLINT ,
    25. 	PRIMARY（Sno,Cno),
    26. 		FOREIGN KEY (Sno) REFERENCES Student(Sno),
    27. 		FOREIGN KEY (Cno) REFERENCES Course(Cno)
    28. );

    实验结果的运用： 

  • 相关阅读:
    ACM数论总结2
    【Vue】input框自动聚焦且输入验证码后跳至下一位
    轻量高效、灵活可扩展！了解下Alibaba QLExpress规则引擎的魅力
    视频美颜SDK原理与实践：从算法到应用
    YUVToRGB(CUDA Conversion)库的学习
    学习R语言第六天
    win10-cpu-Yolov7
    企业软件定制开发的重点是什么？|app小程序网站建设
    两轮市场红海，利尔达芯智行如何乘风破浪？
    C++的奇妙之旅
  • 原文地址：https://blog.csdn.net/wyd_333/article/details/138170431