DTA (M) Database Theory & Applications
实验5任务
任务1:设置数据库帐户
1.1 连接到PostgreSQL服务器
PostgreSQL系统是一个客户机-服务器系统，即:
许多数据库，每个都有多个表，驻留在一台机器上。
这台机器运行一个管理所有数据库访问的服务器进程。
其他机器(可能包括服务器机器)运行客户机进程，这是将用户连接到数据库的会话。
客户端进程使用pgAdmin4运行(https://www.pgadmin.org/):一个开源的数据库前端产品，能
够运行在PostgreSQL数据库系统之上。您可以通过几个工具来访问数据库，特别是查询工具
和模式管理器，我们将在本实验的后面部分了解到这一点。
1.2 登录WVD并设置pgAdmin
通过访问以下链接，使用您的 GUID凭据(即，您的GUID电子邮件地址作为用户名，您的 GUID
密 码 作 为 密 码 ) 登 录 到 选 择 COSE 桌 面 的 远 程 桌 面 网 络 客 户 端 (WVD):https://
rdweb.wvd.microsoft.com/arm/webclient/index.html
您将看到图1，并点击COSE桌面(科学与工程学院)。
图1
图 3 密码是:9991234t
都是小写的！！
注意:如果您已经在本地(例如，在笔记本电脑中)安装了PostgreSQL服务器，则可以按如下方式添加数据库服务器(有关安装，请参见任务1.3):名称:localhost
主机:本地主机端口:5432用户名:postgres密码:postgres
图 4
DTA (M) Database Theory & Applications
图2
登录到远程桌面后，您需要启动
pgAdmin4，如图2所示。键入以下内
容:pgadmin 4 v4(pgadmin 4 版本取
决于 COSE/实验室电脑)并通过选择
工具栏按钮(左上角)连接到数据库
服务器，以添加到服务器的连接。
在pgAdmin的一些实现中，您可能需
要使用登录到这个应用程序(图3):
用户名:后缀密码:
后缀
如果您在学校网络内的实验室电脑中
或通过WVD连接，那么您需要输入您
的详细信息来添加 socs-db . DCS .
gla . AC . uk PostgreSQL 服务器
(见图4)。服务器连接的细节如下(见
图5):
名称:socs-
db.dcs.gla.ac.uk主
机:socs-
db.dcs.gla.ac.uk端
口:5432
用户名:m_21_
密码:< GUIDusername >
例如，用户名:m_21_9991234t和
DTA (M) Database Theory & Applications
当您没有连接到时，建议您本地连接到本地PostgreSQL服务器
学校的网络或COSE桌面。
注意:您可以使用“文件”菜单中的“更改
密码”选项来更改密码并记住它。
现在，您将看到您的帐户被添加到本地数
据库服务器。一旦您展开它，您将可以访
问数据库列表，包括一个登录数据库(图
6)。
图5
图6
1.3 PostgreSQL & pgAdmin安装指
南(仅在您自己的设备上)
注意:只有当您希望使用自己的笔记本电
脑/个人电脑在本地进行练习时，才会提供
此任务。下面，您可以找到关于在 Linux和
Windows上安装PostgreSQL的一般说明。
注意:我们不能为使用自己安装有困难的学
生提供支持。
PostgreSQL可以安装在几个
操作系统。这将向您展示如何在您的 Ubuntu 和窗口上安装 PostgreSQL。要在 Ubuntu 上安装
PostgreSQL，首先打开一个命令行终端。然后键入以下内容:apt-get install postgresql-
9.2
注意:您可能需要root用户权限才能安装。要在Windows上安装PostgreSQL，有一个图形安装
程序，其中包括 PostgreSQL 服务器、 pgAdmin 和 StackBuilder，用于下载和安装其他
PostgreSQL 应 用 程 序 和 驱 动 程 序 。 您 可 以 从 以 下 网 址 下 载 安 装 文 件 :http://
www.postgresql.org/download/windows/
要在 Windows 上安装 pgAdmin，您需要从下载一个图形安装程序 https://
www.pgadmin.org/download/
DTA (M) Database Theory & Applications
任务2:定义 SQL创建语句
任务:基于如下所示的“狗”模式，定义所有的 SQL CREATE TABLE语句，这些语句也可以在
Moodle 的 DOG_CREATE_SCRIPTS 文件中找到。下面提供了关系模式和 SQL创建语句的属性规
范。此外，您可以定义约束和主键/FK定义。
狗窝(狗窝名称、地址、电话)
所有者(所有者 id、姓名、电
话)
BREAD(BREED name)
DOG (dogid、姓名、ownerid、kennelname、breedname、母亲姓名、父亲姓
名)
出席情况(dogid、showname、opendate、place)
显示(显示名称、开始日期、结束日期)
CREATE语句的规范如下:
• 品种只存储它们的名字[VARCAR(64)]；品种名称是唯一的。
• 业主有他们的名字[VARCAR(32)]和联系电话[VARCAR(16)]，如果知道的话；每个所有
者都有一个唯一的整数作为标识符。
• 饲养狗的犬舍，如果知道的话，有它们的名字[VARCHAR(64)]，它们的地址
[VARCHAR(64)]，和它们的联系电话[VARCHAR(16)]；狗舍名称是唯一的标识符。
• 如果知道的话，狗有它们的名字[VARCAR(32)]，它们的母亲和父亲的名字[VARCAR(64)]，
以及对它们的主人、品种名称和相关狗舍的参考。所有狗的名字都是唯一的，每只狗都有
一个唯一的整数作为标识符。
• 一场演出有一个演出名称[VARCHAR(64)]，和它的开始和结束日期[VARCHAR(12)]，如果
知道的话。这场演出由它的名字和开幕日期的组合来确定。
• 出席状态表示特定的狗是否参加过特定的表演，通过表演名称和出席的开始日期
(VARCAR(12))来识别，以及狗在表演期间获得的等级/位置[整数]，如果知道的话。
执行
DTA (M) Database Theory & Applications
任务3:在 pgAdmin中创建数据库
步骤 1:要创建您的表，首先使用安装过程中配置的用户帐户详细信息连接到数据库。双击服
务器的帐户图标。
步骤 2:通过工具使用 SQL编辑器…查询工具(图 7)从菜单(在 pgAdmin 4 中)您可以编写您的
SQL语句，例如，CREATE TABLE，SELECT，…
图 7
让我们首先创建表OWNER。在提供的DOG_CREATE_SCRIPTS文件中，复制并粘贴到 QueryTool区
域，如下图 8所示，CREATE TABLE OWNER语句，然后按下图标‘Execute/Refresh(F5)’，这
是一个黑色的小三角形(见图 7)。
创建表所有者(
ownerid整数，名称
varchar(32)，电话
varchar(16)，
主键(所有者标识))
DTA (M) Database Theory & Applications
图 8
步骤 3:创建的表可以在Schemas -> public -> Tables下找到(参见图 8；左)。如果表没有显
示，请右键单击表，然后单击刷新。
现在先创建没有可传递依赖关系的表，即按照以下顺序复制和粘贴 CREATE TABLE语句:
Table BREED
Table KENNEL
表所有者(您已经这样做了，因此，跳过这个create-语句● v)
台夹
表格显示
table CAVISION
休息一下，探索 pgAdmin，看看主键和外键的定义！
DTA (M) Database Theory & Applications
任务4:从脚本文件填充表
任务 :在从任务 2 和 3 创建了六个表之后，您必须填充它们。在 Moodle 的文件
DOG_INSERT_SCRIPTS 文件中，有插入到 SQL语句，将元组插入到每个表中。请注意，我们将
在后面的实验/讲座中讨论插入修改查询。我们现在就使用它，因为我们需要将元组填充到我
们的表● v中。
步骤 0:假设您已经使用 SQL编辑器工具创建了表所有者。然后，将脚本中的 INSERT INTO
OWNER SQL语句复制到QueryTool区域，如图9所示。然后，按下“执行”图标。
图9
第一步:清理QueryTool区域(避免重新插入相同的元组；但是，这是不允许的，因为系统会识
别任何完整性约束违规)，并键入:
从所有者中选择*；
检查并查看表所有者的元组，如图10所示。
DTA (M) Database Theory & Applications
图10
第二步:重复同样的步骤，将所有元组以与SQL CREATE语句相同的顺序插入到所有表中，也就
是说，将元组插入到 BREED、KENNEL、OWNER(如果还没有)、DOG、SHOW和 CARTION中。
每次在表中插入元组时，一定要检查元组是否已经成功插入。下面是供您检查的 SQL SELECT
语句:
从品种中选择*；从狗窝中选
择*；从狗中选择*；从节目
中选择*；
从出席者中选择*；
重要提示:我们不能为那些在使用自己的数据填充安装时有困难的学生提供支持。
DTA (M) Database Theory & Applications
任务5:通过添加级联触发器来创建具有可传递依赖关系的SQL语句
[高级&可选]
在本任务中，我们将逐步讨论如何为 SQL CREATE TABLE语句提供具有可传递依赖关系的关
系。具体来说，让我们假设我们想要创建“著名”表:
员工(姓名，SSN，超级 SSN，DNO)
部门(国家编号、国家名称、经理_SSN)
这两种关系通过某些外键相互关联:
从员工到部门的DNO(部门编号)
经理_SSN(经理SSN)从部门到员工
SUPER_SSN(主管SSN)从员工(主管)到员工(主管)。
由于这些相互依赖关系，我们应该建立一系列步骤来定义这些表。这是必需的，因为例如，在
超级 SSN的定义中，员工关系应该已经提前创建。但是，超级 SSN包含在员工定义中。因此，
在定义雇员关系时，我们不能定义 SUPER _ SSN●v。在这种情况下，我们处理 SQL ALTER
TABLE1命令，在该命令中，我们可以在定义表之后添加/修改/删除列和约束。该命令用于处
理这些情况。让我们开始以下步骤/场景:
步骤 1:让我们定义员工关系，只包括这些不需要预先存在员工和部门关系的列。
创建员工表(
名称可变字符(20)默认“克里斯”，SSN整数
不为空
);
第 2步:现在，让我们使用ALTER TABLE命令更改Employee以添加主键约束(标签:EMP_PK):
更改员工表
添加约束电磁脉冲主键(SSN)；
步骤 3:我们现在可以将 SUPER_SSN 属性作为新列添加到员工关系中。然后，我们将添加一个
约束(标签:EMP_FK1)，以说明该属性是 Employee中的一个(递归)外键。通过这些步骤，我们
现在已经处理了递归外键定义。为了将 DNO属性添加到员工关系中，我们需要首先创建部门关
系。
1 https://www.postgresql.org/docs/9.1/sql-altertable.html
DTA (M) Database Theory & Applications
更改员工表
添加列超级 SSN整数；
更改员工表
添加约束电磁脉冲_FK1
外键(超级 SSN)参考员工(SSN)；
步骤 4:让我们创建部门关系。在这个定义中，我们现在可以添加外键
经理_SSN参考已定义的员工关系。
创建表部门(数字整数不为空，数
字整数(20)唯一，经理_SSN整
数，
约束部门主键(数字)，
约束部门_FK外键(经理_SSN)参考员工(SSN))；
步骤 5:由于现在已经创建了部门关系，我们返回到雇员关系的定义，并将 DNO属性添加为外
键。显然，我们首先需要定义这个属性。
更改员工表
DNO国际机场；
更改员工表
添加约束电磁脉冲_FK2
外键(DNO)参考系(数字)；
第六步:我们完了！。哎呀。我们忘了提到EMP_FK2约束的一些触发器。例如，
每当我们更改关系部门中的 DNUMBER属性时，我们都希望我们的 Employee元组也得到更新，
以确保一致性。因此，我们需要更新 EMP_FK2约束的定义。在 PostgreSQL 版本中，我们需
要“删除”约束，并使用ON UPDATE CASCADE触发器将其添加回来。那就是:
更改表员工删除约束 EMP _ FK2更改员工表
添加约束电磁脉冲_FK2
国外关键(DNO)参考系(数据编号)更新级联；
第七步:现在，我们都很幸福！让我们在两个关系中插入一些元组(使用SQL INSERT，
这将在下一堂课中讲授。然而，让我们简单地试验一下，以便
DTA (M) Database Theory & Applications
演示在更改 DNUMBER属性后，数据库系统会自动更新关联的 Employee元组，以确保基于我们
的EMP_FK2定义的一致性。
第八步:我们插入三名员工:克里斯、斯特拉和菲利普。克里斯没有主管，而斯特拉和菲利普由
克里斯监管。在这一步中，员工还没有被分配到部门，因为……显然，我们还没有插入任何部
门。
插入员工值(“克里斯”，1，空，空)；插入员工值(“菲利
普”，2，1，空)；插入员工值(“斯特拉”，3，1，空)；
让我们看看员工通过简单而温和的提问:
从员工中选择*
第九步:我们现在增加两个部门:1号研究部门和 2号开发部门。斯特拉(SSN = 3)是研究部的
经理，菲利普(SSN = 2)是开发部的经理。那就是:
插入部门价值观(1，“研究”，3)插入部门价值观(2，“发展”，2)
第十步:我们现在想把员工分配到各个部门。我们需要更新它们的元组(SQL UPDATE将在下一
节课中再次讨论)；我们在这里使用它来检查 EMP_FK2触发器的性能。具体来说，克里斯
(SSN=1)在研究部门工作(DNO = 1)。菲利普(SSN = 2)和斯特拉(SSN = 3)在发展部工作(DNO =
2)。
更新员工集 DNO = 1，其中 SSN = 1；更新员工集 DNO =
2，其中SSN = 2；更新员工集 DNO = 2，其中SSN = 3；
现在让我们看看我们的数据库:
从员工中选择*从部门中选择*
第 11步:我们现在来看看 ON UPDATE CASCADE触发器是如何工作的。在这种情况下，我们更改
部门研究对应的 dnnumber值(dnnumber = 1)；新值是 DNUMBER = 100。这是克里斯工作的部
门。我们还期望克里斯的元组，特别是DNO值自动变为100，以避免不一致。让我们这样做:
DTA (M) Database Theory & Applications
更新部门设置数字= 100，
其中数字= 1；
我们现在来看看员工关系:
从员工中选择*
你能在克里斯的元组中看到任何更新吗？在更新研究部门元组之后，更新也传播到在这个部门
工作的相关员工(在我们的例子中，这是克里斯)。因此，现在没有不一致。
任务1:设置数据库帐户
1.1 连接到PostgreSQL服务器
1.2 登录WVD并设置pgAdmin
1.3 PostgreSQL & pgAdmin安装指南(仅在您自己的设备上)
任务2:定义SQL创建语句
任务3:在pgAdmin中创建数据库
任务4:从脚本文件填充表
从所有者中选择*；
从品种中选择*；从狗窝中选择*；从狗中选择*；从节目中选择*；
任务5:通过添加级联触发器来创建具有可传递依赖关系的SQL语句[高级&可选]