精华内容
下载资源
问答
  • [MySQL]关系规范化中的操作异常理解

    千次阅读 2019-01-02 19:25:58
    插入失败:该插入的没插入; 插入异常:不该插入的被插入; 删除失败:该删除的没删除; 删除异常:不该删除的被删除; 简单地说:失败:有心栽花花不开,异常:无心插柳柳成荫...

    插入失败:该插入的没插入;

    插入异常:不该插入的被插入;

    插入异常:该插入的没插入;

    删除失败:该删除的没删除;

    删除异常:不该删除的被删除;

    简单地说 对于删除来说: 失败:有心栽花花不开,异常:无心插柳柳成荫

    展开全文
  • 数据库设计中关系规范化理论总结

    千次阅读 多人点赞 2020-07-31 11:08:14
    数据库是一门对数据进行有效管理的技术,它研究信息资源如何被安全地储存和如何被高效地利用,它是现代计算机科学的一个重要分支。...本文通过例举具体事例来探讨关系规范化理论在数据库逻辑设计的形成和方法。

    写在前面:大家好K。首先为你点进这篇有趣的文章点赞👍!文章在撰写过程中难免有疏漏和错误,欢迎你在下方留言指出文章的不足之处;如果觉得这篇文章对你有用,也欢迎你点赞和留下你的评论。更多内容请点进👉我的博客K。👈阅览。

    本文亮点:本文尽量使用通俗易懂的语言,避免教材式语言描述。本文较长,请耐心阅读。

    摘要:数据库是一门对数据进行有效管理的技术,它研究信息资源如何被安全地储存和如何被高效地利用,它是现代计算机科学的一个重要分支。其中关系数据库是目前被应用最广泛的数据库类型,它看起来类似于一张二维表,通过应用数学的方法来处理数据库中的数据。在关系数据库的设计过程中,最重要的莫过于对数据库的逻辑设计,即针对一个具体的问题,我们应该如何去构造一个适合它的数据库模式。经过科学家的讨论研究,最终形成我们今天所看到的关系数据库的规范化理论。本文通过例举具体事例来探讨关系规范化理论在数据库逻辑设计中的形成和方法。
    关键词:数据库;关系规范化理论;范式;函数依赖;属性

    1 关系规范化理论的几个相关概念

    1.1 数据依赖

    数据库的一张表中,数据之间存在着某种相互关系,也就是数据依赖,是各属性之间的相互约束的关系。把真实世界某一实体的属性的语义抽象出来,换句话说就是对某事物现实属性含义的数字化。研究者到目前为止已经提出了各种类型的许多种的数据依赖,函数依赖(Functional Dependency,FD)和多值依赖(Multi-Valued Dependency,MVD)是其中需要我们重点了解和学习的。

    1.1.1 函数依赖

    假设当前有个关系R(U),如有以下学生关系,属性有学生姓名、学号、学生年龄、科目和科目成绩,即用关系模型符号语言描述为Students(Sname, Sno, Sage, Subject, Grade),再假设属性集合有这两个子集,如X=Sno、Y=Sage。函数依赖是指,两个元组的Sno相同,则Sage一定相同,此时称Sno函数确定Sage或Sage函数依赖于Sno。
    只能根据对真实世界的某一具体关系的描述(语义)来确定一个函数依赖。例如如果说Sname函数确定Sage(两个相同的学生姓名,各自对应的年龄也一定相同),那么就一定要事先说明,在这个关系中,不能存在同名同姓的两同学,否则就会出现两个相同的学生姓名,各自对应的年龄不同的情况,这就不是Sname函数确定Sage。
    同理,此例中就不能说Subject函数确定Grade,因为通常学生选修相同的课程,最后的成绩是不相同的,即同一Subject对应了多个Grade的值,而前一个例子中Sno学号就只对应了该学生自己的Sage年龄。

    1.1.1.1 非平凡函数依赖

    如果X=(Sno, Sname)、Y=Sage,Sage是函数依赖于(Sno, Sname)这个属性集合的,类似这样Y不包含于X的函数依赖,称之为Y非平凡函数依赖于X。如果没有明确说明,一般是只在非平凡函数依赖的范围中讨论。

    1.1.1.2 平凡函数依赖

    如果X=(Sno, Sname, Sage)、Y=Sage,Sage是函数依赖于(Sno, Sname, Sage)这个属性集合的,可以看到Y是X的一个子集,X包含了Y,类似的函数依赖被称为平凡函数依赖。平凡函数依赖在所有的关系模式中都是一定成立的,它是固有的一种函数依赖,并不生成新的语义。

    1.1.1.3 完全函数依赖

    如果存在同名同姓的情况,且X=(Sname, Sage)、Y=Grade,X的真子集有空集∅、Sname和Sage,它们各自都不能函数确定Grade,显然空集∅不能函数确定科目成绩Grade,学生姓名Sname也不能函数确定科目成绩Grade,因为存在同名同姓的情况,学生年龄Sage也不能函数确定科目成绩Grade。类似于这样的,X的任何一个真子集都不能函数确定Y,那么称这样的函数依赖为Y对X的完全函数依赖。

    1.1.1.4 部分函数依赖

    如果存在同名同姓的情况,且X=(Sname, Sage, Sno, Subject)、Y=Grade,X的真子集有空集∅、Sname、Sage、Sno、(Sname, Sage)、(Sno, Subject)等15个,经过前面的讨论,空集∅、Sname和Sage等14个真子集都不能函数确定Grade,但是X的(Sno, Subject)这个子集可以函数确定Grade,因为根据实际语义来说,一个学生有唯一的一个学号,并且本学期只选修一次这门课程,所以学号Sno和课程Subject确定下来时,成绩Grade也将被确定。类似于这样的,X的存在一个真子集能函数确定Y,那么称这样的函数依赖为Y对X的部分函数依赖。

    1.1.1.5 传递函数依赖

    假设有如下学生-系别信息关系,属性有学号、系别、系主任,记为R(Sno, Sdept, Mname)。在这个关系中Sno函数确定Sdept(反之不是,因为一个系别有很多学生),Sdept函数确定Mname(反之不是,因为一个管理人员可能管理多个系别),可以推导出Sno函数确定Mname,类似关系R(U)中U的子集X、Y、Z存在X函数确定Y,Y不函数确定X,Y函数确定Z,Z不函数确定Y这样得出X函数确定Z的,称之为传递函数依赖。如果去掉“Y不函数确定X”、“Z不函数确定Y”这两个限制,那么可以看到X实际上是一般的直接函数确定Z的,就不能称之为传递函数依赖。

    1.1.2 多值依赖

    表1 多值依赖例题表格

    科目C 教练T 参考书B
    科目一 托尼 交通标志讲解
    科目一 托尼 交通处罚讲解
    科目一 托尼 科目一练习题
    科目一 凯文 交通标志讲解
    科目一 凯文 交通处罚讲解
    科目一 凯文 科目一练习题
    科目四 托尼 现场急救讲解
    科目四 托尼 文明驾驶讲解
    科目四 托尼 科目四练习题
    科目四 露西 现场急救讲解
    科目四 露西 文明驾驶讲解
    科目四 露西 科目四练习题

    在上面的关系模型DTeaching(C, T, B)中,当需要给一个科目(例科目一)添加一名教练时(例艾伦),这里必须插入三个元组:(科目一, 艾伦, 交通标志讲解)、(科目一, 艾伦, 交通处罚讲解)和(科目一, 艾伦, 科目一练习题)。同样在去掉一个科目(例科目四)的参考书(例现场急救讲解)时,必须要删除两个元组:(科目四, 托尼, 现场急救讲解)和(科目四, 露西, 现场急救讲解)。
    像这样增删改相关数据是非常不方便的,有非常大的数据冗余。对于(T, B)对应一个科目C,而实际上参考书B只与科目C有关,与教练T无关,这说的就是多值依赖。令DTeaching关系中所有属性为U,那么T=U-B-C。这时关系模式DTeaching(U)中多值依赖B→→C成立。即C的值只是取决于B,而与T无关。
    例如对于DTeaching关系,(科目一, 交通标志讲解)对应了有两个教练T{托尼, 凯文}的一个组,这一组的值只是取决于科目C的值。即对(科目一, 科目一练习题)来说,对应的教练T也是{托尼, 凯文}这一组,可以发现,即使参考书B变了,科目C也还是对应{托尼, 凯文}这一组教练,说明与参考书B无关。

    1.2 码

    码是数据库概念模型和关系模式中一个非常重要的概念。

    1.2.1 候选码

    如果能用最少的几个属性可以唯一地确定一个元组,换句话说,几个属性的集合K,能够完全函数确定一个元祖,那么这个属性集合K,就是关系R的候选码。例如在上文学生关系Students(Sname, Sno, Sage, Subject, Grade)中,属性集合{Sno, Subject}可以完全函数确定一个学生,例如通过Sno、Subject可以确定某个学生的信息和他这个科目的成绩,则(Sno, Subject)是候选码。

    1.2.2 超码

    通过候选码的介绍可知,候选码是最少的几个属性,集合K是完全函数确定一个元组的。超码与之不同,超码的属性集合J是部分函数确定一个属性。超码的属性集合元素个数比候选码的多,超码的某些真子集可能是候选码。例如上一个例子,候选码是(Sno, Subject),超码可以是(Sno, Subject, Sage),其中Sage属性对于确定一个元组是不必要的一个属性。

    1.2.3 主属性与非主属性(非码属性)

    候选码可能有很多个,例如学生关系Students(Sname, Sno, Sage, Subject, Grade)中,如果不考虑同名同姓的情况,那么通过候选码(Sno, Subject)、(Sname, Subject)都可以唯一确定一个元组。这时候选码有多个,就需要人为选定一个主码来供数据库操作使用。几个候选码中所有的属性都称为主属性,反之为非主属性或非码属性。

    1.2.4 全码

    在某些特殊情况下,某些关系的候选码的就是整个属性组,这称为全码。全码包含的属性数量是做多的;最简单的码是只有单个属性。假设存在一个课程关系,一个任课老师可能教授不同的科目,一个科目可能由多课老师来教,该关系表示为Course(Subject, Teacher),如果想要唯一确定一个元组,则必须要提供两个属性,所以该Course关系的码是全码。

    1.2.5 外部码(外码)

    如果一个关系模式R的某个属性或属性组K不是它的码,但是K是另外一个关系模式的码,那么K就是关系模式R的外部码。例如上文的学生关系Students(Sname, Sno, Sage, Subject, Grade)的码是(Sno, Subject),单独的属性Sno不能作为Students关系的码,但是Sno可以作为学生信息关系模式SInfo(Sno, Sname, Sage, Sex)的码。

    2 关系数据库的规范化

    关系数据库的形式是一张二维表,关系数据库的关系必须要满足一定的要求,最基本的一定要满足第一范式,满足的范式越高级,则该关系数据库的规范化程度就越高。最早E.F.Codd研究范式理论,并里提出了第一范式、第二范式、第三范式,后来他和Boyce提出来更高级的BC范式,随后Fagin提出来第四范式,后面又有一些关系数据库研究人员提出了第五范式。所有范式的级别由高到低是5NF>4NF>BCNF>3NF>2NF>1NF,规范化的过程就是由一个第一级的范式的关系模式,通过模式分解,转化成更高一级范式的关系模式。

    2.1 1NF(第一范式)

    第一范式是关系数据库设计必须要满足的最基本要求,如果没有满足第一范式,那么这个数据库设计就是错误的。第一范式要求关系的每一个分量或称属性必须是不可以再分的。如果把关系型数据库看成一张普通二维表,那么就不能存在一个属性再包含多个子属性。
    例如假设存在一个错误的老师学生的关系,属性有老师姓名、专业和学生姓名,表示为Relationship(Tname, Sdept, Students),如果Students是另一个关系Students(Sname1, Sname2…),这里Students被分为Sname1,Sname2…, 那么就是错误的。所以换句话说,不能使两个关系有嵌套联系。

    2.2 2NF(第二范式)

    2.2.1 定义

    首先某关系R符合第一范式,如果关系R的任何一个候选码能完全函数确定每一个非主属性,那么关系R就符合第二范式。
    假设存在一个集团员工的考核和住处信息关系,每个公司的员工住在一个地方,属性有工号WNum、所在公司WCom、住处WLoc、考核项目Project和考核成绩Grade,表示为W-L-P(WNum, WCom, WLoc, Project, Grade)。
    显然W-L-P关系的候选码是(WNum, Project)。(WNum, Project)能完全函数确定Grade;WCom能函数确定WLoc(因为每个公司的员工住在一个地方);因为Wnum能函数确定WCom,所以(WNum, Project)是部分函数确定WCom;因为Wnum能函数确定WLoc,所以(WNum, Project)是部分函数确定WLoc。可以看到存在两个候选码部分函数确定非主属性,所以关系W-L-P是不符合第二范式的。

    2.2.2 问题提出和解决

    如果某关系不符合第二范式,那么就会产生一些问题。
    插入异常。如果在W-L-P关系中,新插入WNum=123,WCom=AliPay,WLoc=10B303,但是该员工还没设置考核项目,即没有Project,缺少主属性的值,所以就无法插入到该关系中。
    删除异常。如果某员工要删除他的考核项目,但是Project是主属性,一旦删除了,该员工的所有信息都会被删除,这就造成了删除异常。
    修改复杂。如果某员工要转到该集团下的其他公司,那么就要修改该元组的WCom值,那么住处WLoc也需要修改。如果这个员工的考核项目Project有多个,那么WCom和WLoc的值也会被存储多个,转公司时也需要全部修改。这就是数据冗余度大导致数据修改无比复杂。
    显然,我们可以把关系模式W-L-P分解成两个关系模式:WP(WNum, Project, Grade)和WL(WNum, WCom, WLoc)。关系WP的码是(WNum, Project),关系WL的码是WNum,这样各自的码就能完全函数确定各自的非主属性了。

    2.3 3NF(第三范式)

    2.3.1 定义

    上文关系模式WL(WNum, WCom, WLoc)存在传递依赖。WNum能函数确定WCom(反之不能),WCom能函数确定WLoc,所以WNum是传递函数确定WLoc。这不符合第三范式。类似这样的某关系模式R,首先符合第一范式,并且不存在码X,能函数确定任意属性组Y(反之不能),Y能函数确定任意非主属性Z,就符合第三范式。上文中WP是符合第三范式的。

    2.3.2 问题和解决方法

    如果某关系模式不符合第三范式,就会产生类似于不满足第二范式时的问题。以WL关系为例,分解成WC(WNum, WCom)和CL(WCom, WLoc)。这样就不存在传递依赖了,分解结果符合第三范式。

    2.4 BC(Boyce-codd)范式

    BC范式有时被称为扩充的第三范式。符合第三范式的关系有些符合BC范式,有些不符合BC范式。

    2.4.1 定义

    假设一个关系模式R满足第一范式,其中一个属性或属性组X能函数确定一个属性或属性组Y,X不包含Y且X中一定含有码,那么这个关系模式R是符合BC范式的。
    假设存在一个员工、领导和部门的关系WMD(W, M, D),一个领导M只管理一个部门D,一个部门D有多个领导M,一个员工W加入一个部门D,就对应了一个固定的领导M。通过语义可以得出:(W, D)能函数确定M,(W, M)能函数确定D,M能函数确定D。所以该关系的候选码有两个,分别是(W, D)和(W, M)。没有非主属性对码的传递函数依赖或部分函数依赖,该关系是符合第三范式的。但是M能函数确定D,M在这里是决定因素,而M不包含码,所以该关系不符合BC范式。

    2.4.2 问题和解决方法

    关系模式WMB是不符合BC范式的,可以通过把该关系分解成WM(W, M)和MD(M, D),这下它们都满足了BC范式。
    如果某关系模式R不属于BC范式,那么它仍然可能有数据修改复杂的特点。第三范式和BC范式是函数依赖范围内模式分解的最高程度。但是还没有完全解决插入和删除异常。

    2.5 4NF(第四范式)

    第四范式就是对于给定任意关系模式R,R符合第一范式,当任意的属性或属性组X和Y,X→→Y,且X不包含Y、X都含有码,那么这个关系模式R是符合第四范式的。
    例如上文1.1.2节多值依赖的DTeaching关系模式,一个科目如果是有m个教练n本参考书,那么每个科目的元组就一定有m×n个。每个教练被重复存储n次,中参考书被重复存储m次,数据量一多时,数据冗余度非常大,因此即使满足了BC范式,还应该继续规范化使该关系模式达到第四范式。
    如果只考虑函数依赖,BC范式是规范化程度最高的;如果考虑多值依赖,第四范式是规范化程度最高的。还有其他的数据依赖例如连接依赖,会在关系的连接运算中体现出异常问题。满足了第四范式但可能会存在连接依赖,需要用到第五范式来解决,因作者水平有限,这里不再讨论第五范式。

    2.6 小结:关系规范化理论的必要性和重要性

    规范化理论的中心思想是逐渐分步消除数据间依赖中的不妥当部分,使其能够在操作效率上有所提高。模式中的各个关系模式能够变得更纯粹,让一个关系只联系一个概念,使一个具体问题中的概念单一化,来解决更新复杂、删除异常、数据冗余高以及插入异常等问题。2NF、3NF、BCNF、4NF是对于这一认识的逐步深化。数据库设计人员对具体问题设计的规范化的程度直接影响了数据库逻辑设计的成功与否,所以我们研究关系规范化理论对数据库的逻辑设计是非常有必要和重要的。

    3 总结

    关系数据库的规范化理论是数据库逻辑设计的一个强有力的工具,为数据库设计提供了一个理论的指南。 经过了规范化处理的模式通常结构都变得比较简单,数据间的联系也变得更清晰。但是在这里必须要明确的一点是,评价一个数据库设计的是否“得体”,规范化并不是唯一的标准,如果某关系模式在一些应用上不必要地被分解得太高级,极有可能消耗数据库查询的性能,会花太多时间在表的连接操作上。根据具体的问题,数据库的设计者在规范化程度与操作数据库时应有良好的性能之间找到一个恰到好处的平衡点,这时设计质量才是比较高的。而不是单纯地理解为规范化程度越高设计就越好。

    参考文献

    [1] 王珊,萨师煊.数据库系统概论(第5版)[M].高等教育出版社,2014。
    [2] 田进华,杨志强.关系规范化理论在数据库设计中的重要性[J].电脑知识与技术,2009,(24):6616-6617+6624.
    [3] 梅红.浅析规范化理论在数据库设计中的重要作用[J].数字技术与应用,2019,(10):217-218.
    [4] 李志强,苗振青,刘丽萍.关系规范化理论在MIS系统数据库设计中的应用[J].郑州纺织工学院学报,2000,(01):75-78.

    展开全文
  • 关系规范化理论

    千次阅读 2013-06-12 14:59:24
    关系规范化理论 1 规范化 1.1 为什么要规范化? 关系模式用五元组表示,R(U,D,DOM,F),U表示属性,D表示域,DOM表示属性到域上的映射。一般不用研究关系的域以及属性到域上的映射,但是需要研究内部属性的...

    关系规范化理论

    1  规范化

    1.1 为什么要规范化?

    关系模式用五元组表示,R(U,D,DOM,F)U表示属性,D表示域,DOM表示属性到域上的映射。一般不用研究关系的域以及属性到域上的映射,但是需要研究内部属性的依赖关系-函数依赖R(U,F)

    不好的关系模式会引发数据冗余,插入,更新,删除的异常,所以,需要研究如何解决这些问题。

    1.2 非平凡的函数依赖

    实际上就是定义了两部分属性(x,y,如果x分量上相等,那么y上必须相等,例如资产表,ip地址和mac,序列号,实际上是相互函数依赖的关系。

    1.3 完全函数依赖和部分函数依赖

    完全函数依赖指的是Y函数依赖X,但是不函数依赖X的真子集,那么Y完全函数依赖于X。如果Y还函数依赖X的某个真子集,那么叫做部分函数依赖。

    1.4 

    码的概念其实就是,如果R(U,F)对与某组属性XU完全函数依赖X,那么X叫做关系的候选码,如果有多个候选码,可以选取一个码作为主码。如果R中某个属性是另外一个关系的主码,那么这个属性叫做这个关系的外码。

    2范式理论

    关系数据库中的关系要满足于一定的要求,满足不同程度的要求叫做满足不同范式。

    一个低一级的范式通过模式分解,转换为若干高一级的范式,叫做规范化。

    2.1 1NF:

    关系中每个属性都是不能再分的数据项,也就是不允许表中有表

    例如,学生成绩一列,不能再分为期中成绩和期末成绩。

    2.2 2NF

    所谓2范式,指的是关系中每个非主属性都要完全函数依赖主属性。

    再如:

    员工效率表:

    员工   零件          工时       零件存放仓库  仓库负责人

    在这个关系中,(员工,零件)是组合主属性。同时,由于零件只能在一个仓库,所以仓库也函数依赖零件,也就是说仓库对(员工,零件)来说,是部分函数决定,这就不满足二范式。

    异常情况:增加仓库,这个仓库并没有零件,由于没有主键,所以没法添加

    修改某个零件的仓库时,需要修改一大堆记录;

    删除零件时候,把对应的仓库都删掉了;

    需要分解成两个关系,消除部分函数依赖:

    (员工,零件,工时),(零件,仓库 仓库负责人)

    2.3 3NF

    考虑上面分解后的关系:(零件,仓库 仓库负责人)

    零件只属于一个仓库,一个仓库只能有一个负责人,所以零件就间接决定了仓库负责人。也就是说,这个关系存在非主属性对主属性的传递函数依赖。

    异常情况:

    增加了一个仓库,但是还没有放零件,那么这个仓库就添加不进去。

    删除了一个零件,把仓库都删没了

    修改一个仓库的负责人,由于数据冗余严重,可能需要修改很多行。

    使用属性投影分解法进行规范化:

    (零件,仓库),(仓库,仓库负责人)

    这样,分解后的3范式就消除了传递函数依赖,也就是消除了非主属性之间的完全函数依赖。

    2.4 BC范式

    每一个决定因素都必须包含码!

    考察表(学生姓名 课程 教师)

    每个学生可以选多课程:(学生-课程)--》教师

    每个老师教一门课,每一门课程可以由多个老师教授:教师》课程

    学生      课程    教师

    李四      数学    黎明

    王五      数学     刘德华

    这个关系的主属性是(学生-课程)

    这个关系满足1NF,2NF,3NF,因为没有非主属性对码传递依赖或者部分依赖。这里需要注意的是,教师虽然可以函数决定课程,但是它不能决定学生,所以它并不是码。这不满足决定因素必须包含码的规范。

    一般而言,满足3范式就ok了。

    展开全文
  • 关系数据库设计:谈谈规范化技术

    千次阅读 多人点赞 2020-08-19 21:42:31
    通过实际案例介绍关系数据库设计规范化技术(Normalization),为什么需要规范化,常见的第一范式、第二范式和第三范式,反规范化应用的场景以及外键的取舍问题。

    在这里插入图片描述


    大家好,我是只谈技术不剪发的 Tony 老师。今天我们来聊聊关系数据库的规范化设计问题。本文不涉及数据库教材上晦涩难懂的各种公式,而是从实际应用出发,通过简单直白的方式介绍规范化的设计过程和常见范式。

    为什么需要规范化?

    很多教材和文章都是直接从第一范式开始介绍如何进行数据库设计,完全忽略了对事物前因后果的分析;从而导致我们看完之后,只知道要关系数据库要进行规范设计,但却不知道为什么要这么做。因此,我们首先来给大家介绍一下规范化之前发生了什么。

    假设我们需要为某公司设计一个数据库,用于管理员工、部门、职位等相关的信息。首先从直观上考虑,可以将员工信息、所在部门以及职位信息存储到一个表中,如下图所示:

    0nf
    每一行数据对应一个员工的信息,包括他/她所在的部门、职位等。如果真的这么设计,我们在实际应用中很快就会发现以下各种问题:

    • 数据冗余,同一个部门的信息存储了多份,这就需要占用更多的磁盘空间。另外,数据冗余有时候也可能是指在不同的表中存储了重复的信息;
    • 插入异常,假如现在需要成立一个新的部门,由于还没有增加新的员工,因此无法录入这个部门的信息;
    • 更新异常,如果需要修改某个部门信息,需要更新多行数据,效率低下;不小心忽略了某些记录的话,还会会导致数据不一致,尤其是当一个信息存储到多个表中时更容易出现这种情况。
    • 删除异常,如果某个部门的所有员工都被删除,将会导致这个部门的信息也将不复存在;

    关系数据库之父 Edgar F. Codd 显然意识到了这些问题,并且为此引入了规范化(Normalization)的设计过程。规范化使用范式(normal form)来定义和衡量,范式就是数据库设计时遵循的一种标准级别。Codd 最早提出了第一范式(1NF)、第二范式(2NF)以及第三范式(3NF),每个范式都基于前面的范式定义,例如第二范式需要先满足第一范式。

    📝更高级别的范式包括 BC 范式(BCNF)、第四范式(4NF)、基本元组范式(ETNF)、第五范式(5NF)、DK 范式(DKNF)以及第六范式(6NF);一般来说,满足第三范式的数据库就可以避免数据冗余和操作异常问题。

    通过以上介绍,我们知道了规范化是数据库设计过程中的一系列原理和技术,使用范式来定义和衡量,主要用于减少表中数据的冗余,消除异常,提高数据完整性和一致性

    下面我们基上面的非规范化数据库结构,逐步介绍第一范式到第三范式的实现过程。

    第一范式

    第一范式(First Normal Form)要求满足以下条件:

    • 表中的字段都是不可再分的单一属性;
    • 表需要定义主键(PRIMARY KEY)。

    简单来说,首先就是每个属性要有单独的字段。在上面的不规范设计中,员工的个人电话和工作电话存储在一个字段中,破坏了原子性。另外,还需要为表定义一个主键,用于唯一识别表中的每一行数据;假设每个部门中的员工不会同名,可以使用部门名称加员工姓名作为主键。

    将上面的示例修改成以下结构就可以满足第一范式:

    1nf
    第一范式要求表中的字段具有不可分割的原子特性;不过我们知道,原子是化学反应不可再分的基本微粒,但在物理状态中可以分割,它是由原子核和绕核运动的电子组成。因此,我们同样需要考虑字段不可分割到底是针对什么而言。

    例如,上面的“姓名”字段,实际上也可以拆分成两个字段:姓氏和名字。那么到达要不要拆分呢?显然这个取决于应用程序如何使用这些信息,一般我们将姓名作为一个字段存储;有些应用可能需要拆分,这样在给客户发送消息时可以方便地显示为“尊敬的刘先生/女生”。

    另一个类似的情况是地址信息,例如“XX省XX市XX区XX小区”,存储到一个字段还是拆分成多个字段?大部分情况下,应用程序可能需要统计不同地区的用户情况,拆分成多个字段便于分析。不过这时候需要注意的是如何确保数据的标准化,因为不同的用户虽然住在相同的小区,但会输入不一致的数据;所以最好提供一组标准的数据,提供下拉列表给用于进行选择。

    除了基本的数字、字符、日期等数据类型之外,SQL 还提供了一些复杂的类型,例如数组、XML、JSON 以及自定义类型等。假如我们使用一个 JSON 字段存储电话号码,数据如下所示:

    {
      "phoneNumbers": [
        {
          "type": "office",
          "number": "61238888"
        },
        {
          "type": "mobile",
          "number": "13612345678"
        }
      ]
    }
    

    那么这种设计算不算违反第一范式?从定义来说这显然不属于第一范式,因为这个字段中包含了多个可以分割的属性。

    但是,从 SQL 标准来说这些类型都属于原生类型,而且提供了对这种数据进行处理和查询的内置函数和方法;如果从应用程序的角度来看,例如电商平台中的产品信息、博客文章中的评论信息,可以将它们看作一个原子数据存储在 XML 或者 JSON 字段中,因为没有进行分割处理的需求。

    📝SQL 是关系数据库的标准语言,但 SQL 远远不只能够存储和处理关系模型,XML 或者 JSON 文档、多维数组、图形存储以及流数据处理已经成为了 SQL 标准中的一部分,具体可以参考这篇文章

    以上表结构满足第一范式,但仍然存在数据冗余(例如部门信息),可能导致插入异常、删除异常、修改异常等问题;所以我们还需要进一步规范化。

    第二范式

    第二范式(Second Normal Form)要求满足以下条件:

    • 满足第一范式;
    • 非主键字段必须完全依赖于主键或者候选键,不能只依赖于主键或者候选键的一部分。

    上面表结构中的“部门地址”取决于“部门名称”,也就是主键的一部分;这种依赖关系称为部分函数依赖(partial functional dependency)。显然,此时表中的部门信息存在冗余,可能导致各种操作异常。

    为此我们可以将部门信息单独存储到一张部门表中,并且在部门表和员工表之间维护一个一对多的关系。我们继续将表的结构修改如下:

    2nf
    我们将员工表拆成了 3 个表,员工表中的部门编号和职位编号是外键,分别引用了部门表的主键和职位表的主键。另外,我们为每个表增加了一个 id 主键字段(工号、部门编号、职位编号)。因为部门名称、职位名称等信息并不适合作为主键;如果使用部门名称作为主键,当需要修改某个部门的名称,员工表中可能需要相应修改多条记录。

    如果考虑到同一个部门中可能存在同名的员工,直接在员工表中增加一个 id 主键字段也可以满足第二范式的要求。

    2nf
    以上表结构满足第二范式,但仍然存在数据冗余(例如部门信息),可能导致插入异常、删除异常、修改异常等问题;所以我们还需要进一步规范化。

    第三范式

    第三范式要求满足以下条件:

    • 满足第二范式;
    • 属性不依赖于其它的非主属性,也就是非关键字段不依赖于其他非关键字段。

    当主键决定字段 A,字段 A 又决定字段 B 时,称为传递函数依赖(transitive functional dependency)。例如员工编号决定了部门编号,部门编号决定了部门名称;如果将部门信息和员工信息放在一张表中,就存在这种依赖。显然,在上一节中将员工表拆分成三个表之后就不存在这种问题,因此满足第三范式。

    最终,我们设计的公司数据库结构(ER 图)如下:

    erd
    其中,部门和员工的关系是一对多的关系;职位和员工的关系也是一对多的关系。

    现在我们来回顾一下非规范化设计时的几个问题:

    • 部门、员工以及职位信息分别存储一份,通过外键保持它们之间的联系。因此,不存在数据冗余的问题;
    • 如果想要成立一个新的部门,直接录入部门信息即可,解决了插入异常的问题;
    • 如果某个部门的所有员工都被删除,该部门的信息不会受到影响,不存在删除异常;
    • 如果需要修改部门信息,直接更新部门表即可,不会导致数据不一致。

    对于前三个范式而言,只需要将不同的实体/对象单独存储到一张表中,并且通过外键建立它们之间的联系即可满足。这也是大多数在线交易系统数据库理想的设计方法。

    反规范化

    简单来说,规范化就是将大表拆分成多个小表,并且通过外键建立它们之间的联系。但是,规范化可能导致连接查询(JOIN)过多。例如,为了查看员工所在的部门名称和职位名称,我们需要关联查询 3 个表:

    SELECT e.emp_name, e.hire_date, d.dept_name, j.job_title
    FROM employee e 
    JOIN department d ON (d.dept_id = e.dept_id)
    JOIN job j ON (j.job_id = e.job_id)
    WHERE e.emp_name = '孙尚香';
    
    emp_name|hire_date |dept_name|job_title|
    --------|----------|---------|---------|
    孙尚香   |2002-08-08|财务部    |财务经理  |
    

    如果表中的数据量很大,过多的表连接查询会增加数据库的 IO 操作,从而降低数据库的性能。因此,有时候为了提高某些查询或者应用的性能而故意降低规范反的程度,也就是反规范化(denormalization)。一般来说,数据仓库(Data Warehouse)和在线分析系统(OLAP)会使用到反规范化的技术,因为它们以复杂查询和报表分析为主。

    常用的反规范化方法包括增加冗余字段、增加计算列、将小表合成大表等。例如想要知道每个部门的员工数量的话,需要同时连接部门表和员工表;可以在部门表中增加一个字段(emp_numbers),查询时就不需要再连接员工表,但是每次增加或者删除员工时需要更新该字段。

    需要注意的是,反规范化会增加更新和修改数据的开销,导致数据存在冗余,可能带来数据完整性和一致性的问题;因此,通常我们应该先进行规范化设计,再根据实际情况考虑是否需要反规范化

    关于外键

    在数据库结构设计时,还有一个经常争论的问题就是需不需要使用外键(FOREIGN KEY)。外键是数据库用于实现参照完整型的约束,利用数据库的外键可以保证数据的完整性和一致性;外键的级联操作可以方便数据的自动处理,减少了程序出错的可能性。

    例如,员工属于部门,员工的部门字段上可以创建一个外键引用部门表的主键。此时,我们必须先创建部门,然后才能为该部门创建员工;不会出现员工属于一个不存在的部门的情况,保证了数据的完整性。同时,如果要删除一个部门的话,必须同时处理该部门下的员工;可以选择级联删除员工或者将员工的部门修改为其他部门等操作。

    既然外键拥有这么多好处,为什么我们还要讨论是否需要使用外键呢?主要是性能问题。因为任何事情都是有代价的,数据库为了维护外键需要牺牲一定的性能,尤其是在大数据量高并发的情况下。因此出现了另一种解决方案,就是将完整性检查放到应用层去实现,而应用程序相对比较容易扩展。

    不过,在应用端实现约束也可能导致一些问题。首先,无法百分之百保证不会出现问题,尤其是多个应用同时共享一个数据库时。缺失外键可能导致数据库的结构不明确,需要依赖相应的文档进行说明。

    总之,在系统的设计之初应该尽量使用外键确保完整性。如果随着业务增长出现性能问题,可以考虑在应用中实现约束。


    总结

    本文从非规范化数据库结构可能导致的问题出发,介绍了关系数据库为什么应该进行规范化设计以及常用的各种范式。同时,我们还讨论了特殊应用场景下的反规范化问题和外键的取舍。

    如果觉得文章对你有用,欢迎关注❤️、评论📝、点赞👍

    展开全文
  • 概念模型与关系模型和关系规范化

    万次阅读 2017-05-20 16:18:34
    是实现现实世界到信息世界的第一层抽象,是数据库设计人员进行数据库设计的有力工具,也是数据库设计人员和用户之间进行交流的语言,因此概念模型一方面具有较强的语义表达能力,能够方便、直接地表达应用的各种...
  • 关系模式规范化

    千次阅读 2018-05-16 22:21:49
    关系模式:关系模式相当于一张二维表的框架,在这个框架下填入数据,称为关系模式的一个实例...•未经规范化的数据库一般都有下述缺点: 较大的数据冗余,数据一致性差,数据修改复杂,对表进行插入、删除、更新时会...
  • 关系模式的规范化

    万次阅读 多人点赞 2016-09-29 13:27:42
    原文路径:...了解关系模式规范化的作用 掌握第一范式-重点 掌握第二范式-重点 掌握第三范式-重点 回顾关系
  • 关系数据库规范化理论

    千次阅读 2018-03-01 22:54:50
    1、关系规范化的作用所谓规范化,就是用形式更为简洁、结构更加规范的关系模式取代原有关系的过程。2、函数依赖2.1、属性间的联系实体间的联系有两类:一类是实体与实体之间的联系;另一类是实体内部各属性间的联系...
  • 文章目录0.思维导图1.为什么要学习关系数据库规范化理论?(1)基本概念回顾(2)...规范化---改造关系模式,解决插入异常、删除异常、更新异常和数据冗余问题。(1)规范化研究什么?(2)函数依赖① 函数依赖② 平
  • 关系模式规范化(上)

    千次阅读 2013-03-19 13:45:11
    最近在学习数据库过程,发现几本教材大都是按照数据库系统概论->关系数据库基础->SQL语言->关系数据库理论(大都是介绍规范化)介绍,第二部分的关系数据库基础主要谈到了基本算术运算关系和域运算,比如交并差,...
  • 关系数据库的规范化

    千次阅读 2014-11-19 22:23:11
    关系数据理论 一、设计的问题 1、数据冗余大 数据冗余大的是数据会经常重复出现,浪费了大量的存储空间。 2、更新异常 ... 在数据冗余度大的时候,会导致更新异常,...3、删除异常 会导致数据丢失。 4、插入
  • 第三篇:更新异常规范化设计

    千次阅读 2018-07-21 15:16:53
    ER建模,关系建模与规范化设计 小结 回到顶部 前言  在前两篇,主要讲了ER建模和关系建模。在具体分析如何用数据库管理软件RDBMS(Relational Database Management System)实现这些关系前,我想有必要思考下面...
  • 关系数据库规范化理论---范式

    千次阅读 2017-11-08 15:27:02
    范式规范化关系模式,由于规范程度不同,产生了不同的范式、 一个低一级的关系范式通过模式分解可以转换成若干高一级范式的关系模式的集合。这个过程称为关系模式的规范化关系模式规范化的必要性:关系...
  • 关系数据库设计规范化流程

    千次阅读 2011-09-15 14:59:42
    规范化:确保数据正确地分布到数据库的表,防止操作异常及大量冗余信息的存储。数据冗余不仅占用物理空间,对数据的维护和一致性检查也带来了问题。   范式及举例:   第一范式:【数据库表
  • 关于好的数据库模式好的数据库模式是不会发生插入异常、删除异常、更新异常,同时数据冗余尽可能少的模式。产生不好模式的根本原因是数据之间存在着某些数据依赖。解决方法是通过分解关系来消除其中不合适的数据依赖...
  • 关系模式规范化(设计范式)

    千次阅读 2020-10-28 19:13:56
    关系数据库关系满足一定要求的,满足不同程度要求的为不同的范式。满足最低要求的叫第一范式,简称1NF;在第一范式的基础上满足进一步要求的称为第二范式,简称2NF,其余范式以此类推。对于各种范式之间有如下...
  • 规范化理论:范式等级

    千次阅读 2019-05-12 16:14:48
    关系模式规范化的基本思想是消除关系模式的数据冗余,消除数据依赖的不合适的部分,解决数据插人、删除时发生的异常现象。这就要求关系模式要满足一定的条件。我们把关系模式规范化过程中为不同程度的规范化要求...
  • 数据库规范化理论

    千次阅读 2018-06-24 09:57:39
    本文版权归作者AlvinZH和博客园所有,欢迎转载和商用,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律...其中D、DOM与模式设计关系不大,可以看作三元组R<U,F>...
  • 数据库题目之关系数据理论

    千次阅读 2019-01-10 15:14:46
    1、关系规范化中删除操作异常 ① ,插入操作异常 ② 。  A.不该删除的数据被删除 B.不该插入的数据被插入 C.应该删除的数据未被删除 D.应该插入的数据未被插入【答案:】①A ②D 2、设计性能较优...
  • 规范化理论:多值依赖

    千次阅读 2019-05-12 21:04:11
    假设学校一门课程可由多名教师讲授,教学他们使用相同的一套参考书,这样我们可用下图的非规范化关系来表示课程C、教师T和参考书B间的关系关系CTB 如果关系CIB转化成规范化关系,如图所示。 ...
  • 数据库规范化(范式)(一)

    千次阅读 2018-04-01 15:45:58
    ——转自:https://blog.csdn.net/hbrqlpf/article/details/1887204关系数据库规范化理论一个...1、关系规范化的作用所谓规范化,就是用形式更为简洁、结构更加规范的关系模式取代原有关系的过程。2、函数依赖2.1...
  • 规范化理论:模式分解

    千次阅读 2019-05-13 22:51:05
    什么是模式分解? 关系模式R<U, F>的一个分解是:={<...把低一级的关系模式分解为若干个高一级的关系模式,分解方法并不是唯一的,在这些分解方法,只有能够保证分解后的关系模式与原关...
  • 数据库的规范化——让你读懂什么是范式

    千次阅读 多人点赞 2019-11-18 14:36:23
    参考资料:Wiki百科、百度百科、Google、博客园等,定义性的内容,直接引用了官方介绍。 本文参照以下目录进行内容组织: 什么是好的数据库设计?...关系模型的分解特性 模式分解存在的问题...
  • 关系数据理论

    千次阅读 2019-09-03 23:03:47
    一、关系规范化理论背景 二、规范化理论
  • **2018博客之星评选,如果喜欢我的文章,请投我一票,编号:No....(1)数据清理:填写空缺值,平滑噪声数据,识别,删除孤立点,解决不一致性 (2)数据集成:集成多个数据库,数据立方体,文件 (3)数据变换:...
  • 数据库规范化三个范式应用实例

    千次阅读 2004-10-14 22:16:00
    本文解释了其中一些原因,并用不同形式的范式(normal form)规范化了一个保险公司的理赔表。在这个过程表的改变以及添加的一些附加表使数据库效率更高、错误更少、更容易维护。 数据库的规范化是优化表的结构...
  • 数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟,不仅给数据库的编程人员...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,931
精华内容 17,172
关键字:

关系规范化中的删除异常是指