关系数据库中对于层次结构数据的处理分析全文.docx
- 文档编号:9753479
- 上传时间:2023-05-21
- 格式:DOCX
- 页数:8
- 大小:20.48KB
关系数据库中对于层次结构数据的处理分析全文.docx
《关系数据库中对于层次结构数据的处理分析全文.docx》由会员分享,可在线阅读,更多相关《关系数据库中对于层次结构数据的处理分析全文.docx(8页珍藏版)》请在冰点文库上搜索。
关系数据库中对于层次结构数据的处理分析全文
关系数据库中对于层次结构数据的处理分析(全文)
AXX:
1009-3044(2021)28-6821-04
AnalysisofhowtoManageHierarchicalStructureDatainRelationalDatabase
ZHAGNShen-yong1,WUFang1,LIAOJi-yong2,XIAOYou-qing1,LEXiao-yan1
(1.SchoolofComputing,BeijingInstituteofTechnology,Zhuhai519085,China;2.AnhuiTelecomPlanningandDesigningCO.LTD,Hefei230031,China)
Abstract:
Indevelopofdatabaseapplicationsystem,oftenmanagesthehierarchicalstructuredata,therearemanymethods,analysestheadvantagesanddisadvantagesofeachmethodbycomparing.ImplementsrecursivequerywiththecommontableexpressionthenewfunctionofMicrosoftSQLServer2021,ontheexampleoforganizationsmanagement,providesthegeneralwayofhowtomanageorganizationsthroughrecursivequeryusingCTE.
Keywords:
relationaldatabase;hierarchicalstructuredata;recursivequery;commontableexpression(CTE);organizationsmanagement
1绪论
在数据库应用系统开发中,经常要对层次结构的数据对象进行治理。
例如,产品的类别和分类、组织机构、行政辖区和人事治理等等。
现阶段,主流的数据库治理系统都是基于关系模型的关系数据库治理系统。
如何对这些具有层次结构的数据进行建表和治理,是一个必须要解决的实际问题。
通过比较,分析了两种建表方式的优缺点;结合组织机构治理的实例,利用CTE进行递归查询,给出一种通用的解决方案。
2层次模型和关系模型的比较
数据库治理系统总是基于一定的数据模型的,目前,数据库领域常用的逻辑数据模型有:
层次模型、XX状模型、关系模型、面向对象模型和对象关系模型。
层次模型和XX状模型统称为格式化模型,该模型的数据库治理系统在20世纪70年代至80年代初非常流行,在数据库治理系统中占据主导地位,现在已经逐渐被关系模型的数据库治理系统所取代[1]。
20世纪80年代以来,面向对象的方法和技术在计算机领域的应用和进展,也促使了面向对象数据模型的研究和进展,但是,现阶段主流的数据库治理系统都是基于关系模型。
2.1层次模型
层次模型是数据库治理系统中最早出现的数据模型,层次数据库治理系统采纳层次模型作为数据的组织方式。
层次模型用树形结构来表示各类实体以及实体之间的联系,现实世界中许多实体之间的联系本身就是一种自然的层次关系,如行政机构、家族关系等。
图1为层次模型的示例图,可以看出层次模型像一棵倒立的树,结点的双亲是唯一的。
层次模型的特点是,任何一个给定的记录值只有按其路径查看时,才能显示出它的全部意义,没有一个子女记录值能够脱离双亲记录值而独立存在。
层次模型的优点是:
1)数据结构比较简单;2)层次数据库的查询效率高;3)层次数据模型提供了良好的数据完整性支持;
缺点主要是:
1)查询子女结点必须通过双亲结点;2)由于结构严密,数据的存取操作是在真正的物理层次上进行操作,随着应用环境的扩大,程序开发的代码非常复杂,不容易掌握[2]。
但是它对于具有一对多的层次联系的组织机构的描述非常自然、直观、容易理解。
2.2关系模型
关系模型是当今最为重要的一种数据模型,关系数据库系统采纳关系模型作为数据的组织方式。
从用户角度看,关系模型由一组关系组成,每个关系的数据结构就是一张二维表,图2为关系模型的数据结构。
与层次模型不同,关系模型是建立在严格的数学概念的基础上的,它的理论基础就是关系代数。
在关系模型中,实体和实体之间的联系都用二维表来表示。
关系模型的优点:
1)关系模型与格式化模型不同,它有严格的数学理论基础;2)关系模型概念单一,无论是实体还是实体之间的联系都用关系来表示;对数据的检索和更新结果也是关系,所以数据结构简单、清楚,用户易懂易用。
3)数据的存取路径对用户透明,简化了程序员的工作和数据库开发工作。
所以,关系数据模型自诞生以来,进展迅速,现在主流的数据库系统都是基于关系模型的。
3关系模型中对于层次结构数据的处理
关系数据库治理系统因为其自身的优点,成了现在主流的数据库系统,但是,如何用关系数据库来治理具有层次结构的数据是一个现实的问题。
例如企业的组织机构,就是一个典型的层次结构数据。
图3为XX集团的组织机构图。
关系数据库系统中,对于这种组织机构数据如何建立二维表进行数据治理,有两种不同的解决方法,一种是分级建表,另一种是集中建立。
对于上图中的组织机构,使用分级建表的方式,就是每一级部门建立一个表。
如果把集团总公司作为第一级部分,那么服装公司、贸易公司、食品公司就是二级部分,下面的生产部、采购部等就是三级部门,依次类推,最下面的广州办事处等就是5级部门,分别建立下面5个表,表结构分别如表1―表5所示。
表2二级部门信息表(tbl_SecondDeptInfo)数据字典表3三级部门信息表(tbl_ThirdDeptInfo)数据字典
■
表4四级部门信息表(tbl_FourthDeptInfo)数据字典表5五级部门信息表(tbl_FifthDeptInfo)数据字典
■
在上面这5个表中,除了第一个表没有外键,其他四个表都是上一个表的子表,建立了主外键引用关系。
而且后面四个表的结构相似。
这种分级建表的方式,优点是表结构清楚,直接显示了部门所属的级别,对于数据的增、删、改、查都比较方便,如果要修改某个级别的部门,直接修改该级别的部门表,如果要查询部门信息以及上级或者下级部门的信息,只需要根据外键做相应的连接查询。
但是这种分级建表的方式,最大的缺点是可扩展性差,在建表之前,必须确定该组织机构一共有多少个级别,以此来确定应该建几个表,所以,如果将来部门的分级增加了,系统将不能适应这个变化,因而系统的可扩展性很差。
在实际应用中,往往对组织机构的分级会越来越细,为了能够适应这种变化,应该考虑使用另外一种方式建表,即集中式建表,对于组织机构只建立一个表,在这个表里反映出部门之间的上下级隶属关系。
表结构如表6所示。
部门信息表,通过定义自参照外键,实现部门之间的隶属关系。
对于上面的一级部门,由于不存在上级部门了,所以它的上级部门编号就是空值(NULL)。
这种建表方式的最大优点是可扩展性好,将来不论组织机构的级别是增加还是减少,都不需要增加新的表,也不需要修改表的结构。
缺点是要想查询某个部门的上级或者下级部门信息以及确定部门所属的级别,往往要通过递归查询,在MicrosSQLServer2021以前版本的数据库治理系统中,一般要通过创建临时表、游标或者视图来实现这种递归查询,而且查询的语句比较复杂,编程相对比较困难[3];在SQLServer2021版中由于采纳了公用表表达式(commontableexpression,CTE)技术,使得实现递归查询的查询语句变得非常简单。
4公用表表达式(CTE)
微软公司在SQLServer2021中引入了公用表表达式技术。
CTE可以认为是在单个SELECT、INSERT、UPDATE、DELETE或CREATEVIEW语句的执行范围内定义的临时结果集。
CTE与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。
与派生表的不同之处在于,CTE可以自引用,也可以在同一查询中引用多次[4]。
在CTE中可以包括对自身的引用,从而创建递归CTE。
递归CTE是一个重复执行初始CTE以返回数据子集直到猎取完整结果集的公用表表达式。
当某个查询引用递归CTE时,它即被称为递归查询,递归查询通常用于返回分层数据。
递归CTE可以极大地简化在SELECT、INSERT、UPDATE、DELETE或CREATEVIEW语句中运行递归查询所需的代码[5]。
4.1CTE的结构
CTE由表示CTE的表达式名称、可选列列表和定义CTE的查询组成。
定义CTE后,可以在SELECT、INSERT、UPDATE或DELETE语句中对其进行引用,就像引用表或视图一样。
CTE也可用于CREATEVIEW语句,作为定义SELECT语句的一部分。
CTE的基本语法结构如下:
WITHexpression_name[(column_name[,...n])]
AS
(CTE_query_definition)
只有在查询定义CTE_query_definition中为所有结果列都提供了不同的名称时,列名称列表[(column_name[,...n])]才是可选的。
调用CTE的语句为:
SELECTFROMexpression_name;
4.2CTE的递归查询
Transact-SQL中的递归CTE的结构与其他编程语言中的递归程序相似。
其他语言中的递归程序可能返回标量值,但递归CTE可以返回多行结果集。
递归CTE由下列三个元素组成:
1)程序的调用
递归CTE的第一个调用包括一个或多个由UNIONALL、UNION、EXCEPT或INTERSECT运算符联接的CTE_query_definitions。
由于这些查询定义形成了CTE结构的基准结果集,所以它们被称为“定位点成员”。
CTE_query_definitions被视为定位点成员,除非它们引用了CTE本身,所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用UNIONALL运算符联接最后一个定位点成员和第一个递归成员。
2)程序的递归调用
递归调用包括一个或多个由引用CTE本身的UNIONALL运算符联接的CTE_query_definitions,这些查询定义被称为“递归成员”。
3)终止检查
终止检查是隐式的;当上一个调用中未返回行时,递归将停止。
递归CTE的基本语法结构如下:
WITHcte_name(column_name[,...n])
AS
(
CTE_query_definition--定位点的定义
UNIONALL
CTE_query_definition--调用了cte_name的递归查询。
下面使用CTE,演示对上面组织机构治理的具体实现。
5具体案例
对于上图3.所示的组织机构,使用集中建表的方式,根据表6.所示的数据字典,使用如下的代码创建部门信息表(tbl_DepartmentInfo)。
CreateTabletbl_DepartmentInfo
(DeptIDVarChar(12)primarykey,
DeptNameNvarchar(50)notnull,
ParDIDVarChar(12)referencestbl_DepartmentInfo(DeptID),
DispOrderVarchar
(2),
MemoryNvarchar(50),
Unique(DeptName,ParDID));
其中上级部门编号(PraDID)是外键,参照了本表的部门编号(DeptID)列,部门名称(DeptName)和上级部门编号(ParDID)满足唯一键约束,即假设不同部门的下级部门名称可以相同,但是同一个部门的下级部门名称是不能相同的。
对于部门编号(DeptID),在实际的应用系统开发中,可以使用自定义函数产生部门编号,也可以使用uniqueidentifier数据类型定义该列,使用NEWID函数产生相应的编号;因此,这些编号一般只作为主键的一个唯一标识,不需要实际的意义,一般也不会显示在应用程序的界面上,记录可以通过应用系统进行录入,录入时可以在应用程序的界面上选择相应的上一级部门。
为了能够按一定的顺序显示部门信息,因而设置了显示顺序(DispOrder)列来操纵部门信息显示的先后,例如,可以通过设置字母A-Z来标识同一上级部门的下级部门的显示顺序。
向表里插入图3.中的所有部门的信息,为了便于演示,直接指定了部门编号。
插入数据的部分Insert语句如下所示。
Insertintotbl_DepartmentInfovalues('1J001','XX集团总公司',NULL,'A',NULL);
Insertintotbl_DepartmentInfovalues('2J001','XX服装公司','1J001','A',NULL);
Insertintotbl_DepartmentInfovalues('3J002','生产部','2J001','B',NULL);
Insertintotbl_DepartmentInfovalues('4J003','华东分部','3J003','A',NULL);
Insertintotbl_DepartmentInfovalues('5J003','武汉办事处','4J005','A',NULL);
插入数据后,表里的记录如图4所示。
对于上表中的记录,很难直观的观察到部门之间的隶属关系,也不能确定部门所处的级别。
所以,在应用系统开发时,希望能分级显示部门信息,而且直观显示每个部门所处的级别,这样才能根据用户的要求显示相关部门的信息。
为了能直观显示部门之间的隶属关系,以及每个部门所处的级别,要对部门信息表(tbl_DepartmentInfo)进行递归查询,使用CTE,创建一个可以直观显示组织机构层次关系的视图(vi_Dept)。
定义视图的代码如下。
CreateViewvi_Dept
as
Withd_CTE(DepartmentID,ParentDepartmentID,部门名称,上级部门名称,部门级别,显示顺序,备注)AS
(SELECTDeptID,convert(varchar(max),ParDID)asParDID,DeptName,convert(nvarchar(max),NULL)asParDName,0ASDeptLevel,convert(varchar(max),DispOrder),MemoryFROMtbl_DepartmentInfoWHEREParDIDISNULL--定义定位成员
UNIONALL
SELECTd.DeptID,casewhencte.ParentDepartmentIDISNULLthend.ParDIDelsecte.ParentDepartmentID+'/'+d.ParDIDend,d.DeptName,casewhencte.上级部门名称ISNULLthencte.部门名称elsecte.上级部门名称+'/'+cte.部门名称end,部门级别+1,cte.显示顺序+d.DispOrder,d.memoryFROMtbl_DepartmentInfodINNERJOINd_CTEcteONd.ParDID=cte.DepartmentID)--定义引用了d_CTE自身的递归成员
SELECTDepartmentID部门编号,ParentDepartmentID上级部门编号,部门名称,上级部门名称,部门级别,显示顺序,备注FROMd_CTE
在上面创建视图的代码里,首先,创建了一个公用表表达式d_CTE,它有DepartmentID、ParentDepartmentID、部门名称、上级部门名称、部门级别、显示顺序、备注这七列构成,第一个Select语句定义了定位点成员,把tbl_DepartmentInfo表中上级部门编号为空值(NULL)作为递归执行的结束,同时指定该部门的级别为0。
第二个Select语句定义了递归成员,它把tbl_DepartmentInfo表和公用表表达式d_CTE进行内连接,连接的条件是tbl_DepartmentInfo表里的上级部门编号等于d_CTE的部门编号,每执行一次连接就把部门级别在上个部门级别的基础上加1,并且把每个部门的上级部门编号和名称跟上上一级部门编号和名称分别用“/”联接起来,显示顺序也进行了联接。
连接后的结果集又和tbl_DepartmentInfo表进行连接,如此循环下去,直到没有满足连接条件的结果集了,递归将停止,通过UNIONALL运算,把满足连接条件的结果集和定位点的结果集联接起来,由于UNIONALL运算要求列的数据结构要完全一样,包括数据类型、长度与精确,因此在代码中使用convert(varchar(max),ParDID)将上级部门编号等列转换成varchar(max)数据类型。
最后,根据d_CTE创建了视图vi_Dept。
执行查询“select*fromvi_Deptorderby显示顺序”,结果集内容如图5所示。
对于上面通过视图vi_Dept查询到的结果,可以直观看出各部门之间的层次关系,也能确定部门所在的级别,上面图5的记录内容在应用程序界面中可以用TreeView等控件显示出来。
通过查询视图vi_Dept,可以直接把某一级别的所有部门列出,也可以查询某一个部门以及它的所有下级部门的信息。
例如“select*fromvi_Deptwhere部门编号='3J003'or上级部门编号like'%3J003%'orderby显示顺序”,查询的结果集如图6所示。
所以,根据视图vi_Dept,可以很容易得到用户应用程序界面中需要显示的各种信息。
6总结
虽然现在主流的数据库治理系统都是基于关系模型的,但是经常要处理有层次结构的数据对象。
通过建立一张具有自参照完整性的表,利用MicrosoftSQLServer2021中的公用表表达式的递归查询功能,生成一个能够呈现对象之间的层次关系的视图,可以满足应用系统开发中的各种查询需要;不但简化了编程工作,提高项目开发效率;而且提高了系统的可扩展性,改善程序的性能。
案例中具体实现的SQL脚本,对于层次结构数据的治理具有通用性,可以为同类型的应用提供参考。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 关系 数据库 对于 层次 结构 数据 处理 分析 全文