AnalogSensor

AnalogSensor

关注
发信
关注(28)粉丝(399)

如何在子类型中实现参照完整性

本文介绍了如何在子类型中实现参照完整性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在关系数据库中有以下表格：

  [Sensor] 
 LocationId [PK / FK  - >位置] 
 SensorNo [PK] 
 
 [AnalogSensor] 
 LocationId [PK / FK  - 传感器] 
 SensorNo [PK / FK->传感器] 
 UpperLimit 
 LowerLimit 
 
 [SwitchSensor] 
 LocationId [PK / FK  - 传感器] 
 SensorNo [PK / FK->传感器] 
 OnTimeLimit 
 
 [读取] 
 LocationId [PK / FK->传感器] 
 SensorNo [PK / FK->传感器] 
 ReadingDtm [PK] 
 
 [ReadingSwitch] 
 LocationId [PK / FK  - 阅读] 
 SensorNo [PK / FK  - > Reading] 
 ReadingDtm [PK / FK  - >阅读] 
切换
 
 [ReadingValue] 
 LocationId [PK / FK  - >阅读] 
 SensorNo [PK / FK  - > Reading] 
 ReadingDtm [PK / FK  - >阅读] 
值
 
 [Alert] 
 LocationId [PK / FK  - >阅读] 
 SensorNo [PK / FK  - > Reading] 
 ReadingDtm [PK / FK  - >阅读]

基本上，ReadingSwitch和ReadingValue是Reading的子类型，SwitchSensor和AnalogSensor是Sensor的子类型。读数可以是一个SwitchReading或ValueReading值 - 它不能同时是一个，一个Sensor可以是一个AnalogSensor或一个SwitchSensor。



到目前为止这样做是。



这样做肯定是一个更好的方法。 / p>

我可以想到的唯一其他方法是没有子类型，但完全展开一切：

  [SwitchSensor] 
 LocationId [PK / FK  - >位置] 
 SensorNo [PK] 
 
 [AnalogSensor] 
 LocationId [PK / FK  - 位置] 
 SensorNo [PK] 
 
 [SwitchReading] 
 LocationId [PK / FK  - SwitchSensor] 
 SensorNo [PK / FK  - > SwitchSensor] 
 ReadingDtm 
 Switch 
 
 [AnalogReading] 
 LocationId [PK / FK  - > AnalogSensor] 
 SensorNo [PK / FK  - > AnalogSensor] 
 ReadingDtm 
 Value 
 
 [AnalogReadingAlert] 
 LocationId [PK / FK  - > AnalogReading] 
 SensorNo [PK / FK  - > AnalogReading] 
 ReadingDtm [PK / FK  - > AnalogReading] 
 
 [SwitchReadingAlert] 
 LocationId [PK / FK  - > SwitchReading] 
 SensorNo [PK / FK  - > SwitchReading] 
 ReadingDtm [PK / FK  - > SwitchReading]

这可能不太糟糕，但我也有引用Alert表的表，也必须重复：

  [AnalogReadingAlertAcknowledgement] 
 ... 
 [AnalogReadingAlertAction] 
 ... 
 [SwitchReadingAlartAcknowledgement] 
 ... 
 [SwitchReadingAlartAction]

等。



这个问题对任何人都有意义吗？

解决方案

这些都不是必要的，特别是不能使表格加倍。



简介



由于建模关系数据库标准（IDEF1X）已经使用超过25年（至少在高质量，高性能的市场），我使用这个术语。 Darwen， 与他为抑制关系模型做的伟大的工作一致，他不知道IDEF1X，直到我把它带到他的注意在2009年，因此有一个新的术语对于我们已经使用了几十年的标准术语。此外，新的术语不处理所有的情况，如IDEF1X那样。因此，我使用既定的标准术语，并避免使用新术语。




  • 即使分布式密钥的概念无法识别


  • 关系，因此IDEF1X的概念是标识符

    / strong>及其迁移。


  • 当然，供应商并不完全在球上，他们有奇怪的事情，如部分指数当理解基础时是完全不必要的。但是，着名的学者提出了不完全的新概念，当概念被标准化，并在25年前给予充分的治疗...这是意想不到的。




Caveat



IEC / ISO / ANSI SQL几乎不能充分处理5NF，并且不支持Supertype-Subtype结构;这里没有声明约束（和应该有）。




  • 因此，为了强制执行数据模型，SuperType :: Subtype和Subtype :: Supertype，我们必须有一点CHECK约束，等等（我避免使用触发器有很多原因）。



救济



但是，我考虑所有这些。为了使我能够有效地在StackOverflow上提供一个数据建模服务，而不必用一个完整的话语来说，我故意提供可以由有能力的人使用现有的SQL和现有的约束，无论他们需要什么程度实现的模型。它已经简化，并且包含常见的强制执行级别。如果有任何问题，只是问，你会收到。



我们可以使用链接文档中的示例图形和完全符合IDEF1X的



读者谁不熟悉关系建模标准可能会找到有用。
如果认为数据库可以映射到对象，类和子类的读者，建议进一步阅读可能会导致伤害。这是Fowler和Ambler已经阅读的。



Supertype-Subtype的参照完整性的实现



两种类型的超类型 - 子类型结构。



独占子类型



独占表示只能有一个子类型行每个超类型行。在IDEF1X术语中，Supertype中应该有一个Discriminator列，它标识Supertype行，并且存在它的Subtype行。




  • 对于多于两个子类型，这是需要的，我实现了一个Discriminator列。

  • 对于两个子类型，因为这很容易从现有数据派生（例如 Sensor.IsSwitch 是 Reading ），我不为 Reading 建立额外的显式Discriminator列。




我会把每个方面都放在详细信息。


  1. 歧视器列需要一个CHECK约束才能确保其在值的范围内，例如： IN（B，C，D）。 IsSwitch 是一个BIT，它是0或1，因此已经受到限制。


  2. 超类型的PK定义其唯一性，只允许一个超类型行;没有第二超类型行（并且因此没有第二子类型行）可以被插入。




    • 因此，实施索引（如PK，Discriminator）是过度的，完全冗余的，超级型，如你的链接建议。唯一性在PK中，因此PK加上任何东西都是唯一的）。


    • IDEF1X不需要子类型表中的鉴别符。在子类型中，它再次受到其PK的唯一性的约束，根据模型，如果判别器被实现为该表中的列，则其中的每一行将具有相同的值用于判别器（每本书将是 B;每个 ReadingSwitch 将是 IsSwitch ）。因此，将分类器实现为子类型中的列是荒谬的。再次，完全冗余，额外的不必要的索引，在子类型中实现诸如（PK，Discriminator）的索引：唯一性在PK中，因此PK加上任何东西将是唯一的）。

      li>

    • 链接中标识的方法是实现引用完整性的一种耻辱和。肿（无意义的大规模数据复制）方式。可能有一个很好的理由，作者没有看到这个结构在其他地方。这是一个基本的失败，理解SQL和使用它 有效。这些解决方案是遵循教条SQL不能做...的人的典型，因此不知道SQL可以做什么。 Fowler和Ambler的盲目方法所造成的恐怖更加糟糕。



  3. 子类型PK也是Supertype的FK，




    • 因此，对于任何给定的PK，无论先插入哪个Supertype-Subtype都会成功;并且之后尝试的任何Supertype-Subtype将失败。因此，在子类型表中没有什么可担心的（第二个超类型行或相同PK的第二个子类型行被阻止）。

      。


  4. SQL CHECK约束仅限于检查插入行。我们需要在相同的表格或另一个表格中检查插入的行对其他行。因此，需要一个用户定义的函数。




    • 写一个简单的UDF来检查PK / strong> SuperType中的Discriminator，如果EXISTS返回1，如果NOT EXISTS则返回0。


    • 在子类型中，实现一个CHECK约束，使用PK（它是


    • 我已经在几十个大型的现实世界数据库中实现了这一点，在不同的SQL平台上。以下是和。



      注意




      1. 我也是由CJ Date和Hugh Darwen对进一步关系模型的常用引用。基于一致的证据，经过多年的互动，我得出结论，他们的工作其实是一个贬义。


      2. 他们对关系术语有私人定义，当然严重的阻碍任何沟通。他们有我们自1970年以来的术语的新术语，以便看起来他们已经发明了它。典型的欺诈和小偷。



      I have the following tables in a relational database:

      [Sensor]
LocationId [PK / FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
UpperLimit
LowerLimit

[SwitchSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
OnTimeLimit

[Reading]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
ReadingDtm [PK]

[ReadingSwitch]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Switch

[ReadingValue]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Value

[Alert]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]

      Basically, ReadingSwitch and ReadingValue are subtypes of Reading and SwitchSensor and AnalogSensor are subtypes of Sensor. A reading can either be a SwitchReading or ValueReading value - it cannot be both, and a Sensor can either be an AnalogSensor or a SwitchSensor.

      The only way I've come across to do this so far is here.

      There surely must be a nicer way to do this sort of thing.

      The only other way I can think of is to not have sub types but completely expand everything:

      [SwitchSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[SwitchReading]
LocationId [PK/FK -> SwitchSensor]
SensorNo [PK/FK -> SwitchSensor]
ReadingDtm
Switch

[AnalogReading]
LocationId [PK/FK -> AnalogSensor]
SensorNo [PK/FK -> AnalogSensor]
ReadingDtm
Value

[AnalogReadingAlert]
LocationId [PK/FK -> AnalogReading]
SensorNo [PK/FK -> AnalogReading]
ReadingDtm [PK/FK -> AnalogReading]

[SwitchReadingAlert]
LocationId [PK/FK -> SwitchReading]
SensorNo [PK/FK -> SwitchReading]
ReadingDtm [PK/FK -> SwitchReading]

      Which might not be so bad but I also have tables that reference the Alert table, so they too would have to be duplicated:

      [AnalogReadingAlertAcknowledgement]
...
[AnalogReadingAlertAction]
...
[SwitchReadingAlartAcknowledgement]
...
[SwitchReadingAlartAction]

      etc.

      Does this problem make any sense to anyone??

      解决方案

      None of that is necessary, especially not doubling up the tables.

      Introduction

      Since the Standard for Modelling Relational Databases (IDEF1X) has been in common use for over 25 years (at least in the high quality, high performance end of the market), I use that terminology. Darwen, consistent with the great work he has done to suppress the Relation Model, he was unaware of IDEF1X until I brought it to his attention in 2009, and thus has a new terminology for the Standard terminology that we have been using for decades. Further, the new terminology does not deal with all the cases, as IDEF1X does. Therefore I use the established Standard terminology, and avoid new terminology.

      • even the concept of a "distributed key" fails to recognise the underlying ordinary PK::FK Relations, their iimplementation in SQL, and their power.

      • The Relational, and therefore IDEF1X, concept is Identifiers and Migration thereof.

      • Sure, the vendors are not exactly on the ball, and they have weird things such a "partial Indices" etc, which are completely unnecessary when the basics are understood. But famous academics coming up with incomplete new concepts when the concept was standardised and give full treatment 25 years ago ... that, is unexpected.

      Caveat

      IEC/ISO/ANSI SQL barely handles 5NF adequately, and it does not support Supertype-Subtype structures at all; there are no Declarative Constraints for this (and there should be).

      • Therefore , in order to enforce the full set of Rules expressed in the Data Model, both SuperType::Subtype and Subtype::Supertype, we have to fiddle a little with CHECK Constraints, etc (I avoid using Triggers for a number of reasons).

      Relief

      However, I take all that into account. In order for me to effectively provide a Data Modelling service on StackOverflow, without having to preface that with a full discourse, I purposely provide models that can be implemented by capable people, using existing SQL and existing Constraints, to whatever extent they require. It is already simplified, and contains the common level of enforcement. If there is any question, just ask, and you shall receive.

      We can use both the example graphic in the linked document and your fully IDEF1X-compliant ▶Sensor Data Model◀

      Readers who are not familiar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful.Readers who think a database can be mapped to objects, classes, and subclasses are advised that reading further may cause injury. This is further than Fowler and Ambler have read.

      Implementation of Referential Integrity for Supertype-Subtype

      There are two types of Supertype-Subtype structures.

      Exclusive Subtype

      Exclusive means there can be only one Subtype row for each Supertype row. In IDEF1X terms, there should be a Discriminator column in the Supertype, which identifies the Supertype row and which Subtype row exists for it.

      • For more than two Subtypes, this is demanded, and I implement a Discriminator column.

      • For two Subtypes, since this is easily derived from existing data (eg. Sensor.IsSwitch is the Discriminator for Reading), I do not model an additional explicit Discriminator column for Reading. However, you are free to follow the Standard to the letter and implement a Discriminator.

      I will take each aspect in detail.

      1. The Discriminator column needs a CHECK Constraint to ensure it is within the range of values, eg: IN ("B", "C", "D"). IsSwitch is a BIT, which is 0 or 1, so that is already constrained.

      2. Since the PK of the Supertype defines its uniqueness, only one Supertype row will be allowed; no second Supertype row (and thus no second Subtype row) can be inserted.

        • Therefore it is overkill, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Supertype, as your link advises. The uniqueness is in the PK, and therefore the PK plus anything will be unique).

        • IDEF1X does not require the Discriminator in the Subtype tables. In the Subtype, which is again constrained by the uniqueness of its PK, as per the model, if the Discriminator was implemented as a column in that table, every row in it will have the same value for the Discriminator (every Book will be "B"; every ReadingSwitch will be an IsSwitch). Therefore it is absurd to implement the Discriminator as a column in the Subtype. And again, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Subtype: the uniqueness is in the PK, and therefore the PK plus anything will be unique).

        • The method identified in the link is a hamfisted and bloated (massive data duplication for no purpose) way of implementing Referential Integrity. There is probably a good reason the author has not seen that construct anywhere else. It is a basic failure to understand SQL and to use it as it is effectively. These "solutions" are typical of people who follow a dogma "SQL can't do ..." and thus are blind to what SQL can do. The horrors that result from Fowler and Ambler's blind "methods" are even worse.

      3. The Subtype PK is also the FK to the Supertype, that is all that is required, to ensure that the Subtype does not exist without a parent Supertype.

        • Therefore for any given PK, whichever Supertype-Subtype is inserted first will succeed; and whichever Supertype-Subtype is attempted after that, will fail. Therefore there is nothing to worry about in the Subtype table (a second Supertype row or a second Subtype row for the same PK is prevented).
          .

      4. The SQL CHECK Constraint is limited to checking the inserted row. We need to check the inserted row against other rows, either in the same table, or in another table. Therefore a User Defined Function is required.

        • Write a simple UDF that will check for existence of the PK and the Discriminator in the SuperType, and return 1 if EXISTS or 0 if NOT EXISTS. You will need one UDF per Supertype (not per Subtype).

        • In the Subtype, implement a CHECK Constraint that calls the UDF, using the PK (which is both the Supertype and the Subtype) and the Discriminator value.

        • I have implemented this in scores of large, real world databases, on different SQL platforms. Here is the ▶User Defined Function Code◀, and the ▶DDL Code◀ for the objects it is based on.

        • This particular syntax and code is tested on Sybase ASE 15.0.2 (they are very conservative about SQL Standards compliance).

        • I am aware that the limitations on User Defined Functions are different for every SQL platform. However, this is the simplest of the simple, and AFAIK every platform allows this construct. (No idea what the Non-SQLs do.)

        • yes, of course this clever little technique can be used implement any non-trivial data rule that you can draw in a Data Model. In particular, to overcome the limitations of SQL. Note my caution to avoid two-way Constraints (circular references).

      5. Therefore the CHECK Constraint in the Subtype, ensures that the PK plus the correct Discriminator exists in Supertype. Which means that only that Subtype exists for the Supertype (the PK).

        • Any subsequent attempt to insert another Subtype (ie. break the Exclusive Rule) will fail because the PK+Discriminator does not exist in the Supertype.

        • Any subsequen attempt to insert another row of the same Subtype is prevented by the uniqueness of its PK Constraint.

      6. The only bit that is missing (not mentioned in the link) is the Rule "every Supertype must have at least one Subtype" is not enforced. This is easily covered in Transactional code (I do not advise Constraints going in two directions, or Triggers); use the right tool for the job.

      Non-exclusive Subtype

      The Supertype (parent) can host more than one Subtype (child)

      1. There is no one Subtype to be identified.

        • The Discriminator does not apply to Non-exclusive Subtypes.

        • The existence of a Subtype is identified by performing an existence check on the Subtype table, using the Supertype PK.

      2. Simply exclude the CHECK Constraint that calls the UDF above.

        • The PRIMARY KEY, FOREIGN KEY, and the usual Range CHECK Constraints, adequately support all requirements for Non-exclusive Subtypes.

      Reference

      For further detail; a diagrammatic overview including details; and the distinction between Subtypes and Optional Column tables, refer to this Subtype document.

      Note

      1. I, too, was taken in by C J Date's and Hugh Darwen's constant references to "furthering" the Relational Model. Based consistent evidence, after many years of interaction, I have concluded that their work is in fact, a debasement of it. They have done nothing to further Dr E F Codd's seminal work, and everything to damage and suppress it.

      2. They have private definitions for Relational terms, which of course severely hinders any communication. They have new terminology for terms we have had since 1970, in order to appear that they have "invented" it. Typical of frauds and thieves.

      这篇关于如何在子类型中实现参照完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-03 22:20
Powered by LMLPHP ©2024 AnalogSensor 0.042466