2.3 PostgreSQL数据库关系模型应用

在第1章中，熟悉了PostgreSQL数据库管理工具软件及其数据库基本对象。本节进一步介绍PostgreSQL数据库的关系操作，讲解在数据库中如何创建关系表，如何定义关系表的实体完整性约束，如何实施参照完整性约束和用户自定义完整性约束。为了深刻理解关系模型原理，本节将围绕“选课管理系统”项目案例，给出在PostgreSQL数据库中创建关系表及其完整性约束的实践操作。

2.3.1 项目案例——选课管理系统

在大学选课管理系统中，需要对大学的开课计划、学院、课程、教师、学生、选课注册等信息进行数据管理。为实现选课管理系统的数据管理功能，首先需要创建选课管理数据库及其关系表，然后实现选课管理系统的功能逻辑，如添加、更新、查询、报表等应用程序功能。

在本项目案例中，定义选课管理数据库名称为CurriculaDB。它主要由课程信息表（Course）、教师信息表（Teacher）、开课计划表（Plan）、学生信息表（Student）、选课注册表（Register）、学院信息表（College）组成，其表结构分别见表2-11～表2-16。

表2-11～表2-16中，定义了各关系表的结构组成，包括属性列的字段名称、字段编码、数据类型、是否必填（是否允许空值），以及是否为键。此外，它们也定义了一些表中属性列为外键，从而建立相关表之间的联系。表2-13和表2-15还定义了专门的代理键作为该表的主键。

为什么需要代理键呢？由第1章可知，每个关系表必须从其属性中选出一个列或若干个列作为主键。理想的主键是单列，并且其取值为简单的数值类型。但一些关系表只能使用由多个列组合而成的复合键。在表2-13中，若没有使用代理键，就必须使用由课程编号、教师编号、时间组成的复合主键。在数据库中，通过复合主键访问关系表的方案不是理想的方案。为了简化数据库表访问操作和提高处理性能，通常用代理键替代复合主键。代理键取值为自动增量的数字序列，由DBMS提供。在PostgreSQL数据库中，需要将代理键定义为Serial数据类型。例如，在开课计划表（Plan）中CoursePlanID代理键列设置为Serial（序列）类型。此后，PostgreSQL的DBMS就会在Plan表中插入第1行元组时，将CoursePlanID值置为1，在插入第2行元组时，将CoursePlanID值置为2，依次类推。

2.3.2 关系数据库创建

在开发选课管理系统时，使用PostgreSQL数据库软件创建一个关系数据库，并使用该数据库组织与管理数据。首先，使用数据库管理工具pgAdmin 4连接到PostgreSQL数据库服务器。然后，在pgAdmin 4主界面的左边列表窗口目录树中，选取“Databases”节点，单击右键菜单栏中的“Object→Create→Database”菜单命令，进入数据库创建对话框，如图2-18所示。

在数据库创建对话框中，输入新建数据库名称（Database）“CurriculaDB”，以及数据库备注（Comment）等信息，其他参数暂且采用默认值。在单击“Save”按钮后，该工具完成数据库的创建，并将新建的数据库显示在目录树中，如图2-19所示。

在以上数据库创建中，除数据库名称参数外，其他参数均采用模板默认值。在实际应用中，可根据需求具体设定各个参数值。

2.3.3 关系表创建

在创建选课管理系统数据库CurriculaDB后，接下来要在该数据库中创建各个关系表。这里以创建学院信息表（College）为例，介绍pgAdmin 4数据库管理工具创建关系表的具体方法。

在pgAdmin 4数据库管理工具的主界面左边列表窗口目录树中，进入schema节点的public目录，先选取“Table”节点，然后单击右键菜单栏中的“Object→Create→Table”菜单命令，进入数据库表创建对话框。

在数据库表创建对话框中，填写表名称（Name），如图2-20所示，并切换标签页定义列属性，如列名（Name）、数据类型（Data type）、是否允许空值（Not NULL?）、是否为主键（Primary key?）等属性，具体如图2-21所示。

当表结构的列属性定义结束后，单击“Save”按钮，College表创建完成，并出现在左边Table节点的目录中。类似地，在pgAdmin 4工具中，采用GUI操作方式，可以成功创建其他表。当选课管理系统数据库CurriculaDB的所有表都创建后，在Schemas的Tables目录中可以看到这些表，如图2-22所示。

2.3.4 实体完整性应用

在PostgreSQL数据库中创建关系表时，除了定义表结构的列属性外，还需要定义实体完整性约束，以确保关系表中数据的正确性。具体来讲，就是在定义各表主键或代理键时，实施实体完整性约束。例如，在定义学生信息表（Student）的主键列StudentID时，首先限定该列为非空，即主键列不允许空值（“Not NULL?”选择“Yes”），如图2-23所示。

同时，还需要对该主键列StudentID建立唯一性“索引”约束（“Unique?”选择“Yes”），如图2-24所示。这样可确保主键取值唯一，从而实现本表数据遵从实体完整性约束。

在定义开课计划表（Plan）时，需要定义一个代理键CoursePlanID来替代复合主键（CourseID,TeacherID,CourseTime）作为Plan表的主键。其实体完整性约束定义界面如图2-25和图2-26所示。

代理键定义方法：在本表中，通过增加一个CoursePlanID属性列，数据类型为Serial类型，并定义它为主键，同时还需要对该代理键列创建唯一性“索引”。与此类似，在选课注册表（Register）中，使用同样的方法定义该表的代理键。

2.3.5 参照完整性应用

在选课管理系统数据库CurriculaDB中，课程信息表（Course）、教师信息表（Teacher）、开课计划表（Plan）、学生信息表（Student）、选课注册表（Register）、学院信息表（College）之间存在主键与外键的关联。因此，需要定义这些表之间的外键与主键之间的参照完整性约束。例如，Teacher表与College表存在关联，它们之间的参照完整性约束如下：在定义Teacher表的结构时，需要定义一个外键约束（Foreign Key）——Teacher_FK，该约束将本表的CollegeID列定义为外键，并参照College表的主键列CollegeID。在pgAdmin中，操作界面如图2-27所示。

同时，还需要定义该外键约束的相关操作规则。例如，College表修改CollegeID主键值，需要同时级联（CASCADE）修改Teacher表中CollegeID外键列值。当College表删除某主键值时，若Teacher表中有对应数据，则需要限制（RESTRICT）删除，即不允许直接删除College表的该主键值。除非在Teacher表中先删除该学院所有教师，才允许在College表中删除该学院数据。在pgAdmin中，操作界面如图2-28所示。

通过在Teacher表与College表之间建立参照完整性约束，可以实现如下业务规则：

1）Teacher表中的所属学院编号CollegeID应与College表中的学院编号CollegeID保持一致。

2）当修改College表的某学院编号CollegeID时，级联修改Teacher表中的该学院编号CollegeID。

3）当某学院还有教师时，不允许直接删除该学院信息。

类似地，可以建立选课管理系统数据库的其他表之间的参照完整性约束。当这些表间的联系都建立完后，表之间的关联结构如图2-29所示。

2.3.6 自定义完整性应用

在创建关系表时，用户可根据应用业务需求，创建用户自定义完整性约束。例如，教务管理制度规定课程学分最少为1，最多为5。这可在Course表的CourseCredit列上实施用户自定义完整性的列值有效性检查（Check）约束。具体可以定义其检查规则约束“＂CourseCredit＂＞=1 AND＂CourseCredit＂＜=5”，如图2-30所示。

此外，若教务规定课程考核默认为“闭卷考试”。在Course表中，可为列TestMethod设置默认值（Default Value）为“'闭卷考试'”，其操作界面如图2-31所示。

总之，在数据库中，通过对关系表的列值唯一性、取值范围、是否允许空值、默认值等参数进行自定义设置，可以实现用户自定义完整性约束。