Preliminary

Good question, very thoughtful for a student. I think you are actually after this - discussion to get clarity, and this is a data modeling exercise.

• I understand your progression up to 3.3. What, how do you get 3.4 (after a phased progression to 3.3)? For me, the combination of all of the above does not equal Generic.

• Instead of following your progression and setting a model for each step, let me TRD answer the appropriate steps in your discussion.

• TRD Only tables that are identified by keys and relationships are relevant at this stage, I think you are well aware of the Attributes, if any, and what keys they will be expanded from. After you reach a stable TRD, you can expand it to full DM.

• After the model is set up from the previous one, and after evaluation, if it is clear that it is losing information, it can be safely discarded. There is value for such models, so this step is not wrong. But the ongoing discussion of this is a waste. I believe I demonstrated this in the previous question.

Consider this set of Table Link Diagrams .

1.x

From my point of view, A First will be the first sensible TRD worthy of contemplation.

• I don't see how or why Proton / Neutron / Electron are independent tables. They do not exist by themselves, their weights; etc. fixed. They only exist in the Atom context.

• Since each Atom contains at least one proton / neutron / electron, the Proton / Neutron / Electron columns can be expanded into an Atom. Not drawn. Later.

2.x

Your progression is fine except for one glaring error.

general attributes about particle composition are listed in ParticleComposition, while special attributes about particle composition go to special tables.

Not. General attributes about particles go to Particle. Relationship-specific attributes (i.e., not generic) refer to ParticleComposition. And then there are no "special attributes about the composition of particles", there are no "special tables".

3.x

Consider the B Subtype . Your [3.1] is mostly correct, except:

• I don't see Particle having kids like Proton / Neutron / Electron. Only Atom has it.

• I don't see how Particles are related to other Particles (i.e. what is it?). For the data discussed, a molecule is composed of atoms; An atom consists of a proton / neutron / electron; and Particle is either Molecule xor Atom (Exclusive subtype).

• Please correct me if this is not correct.

• See document subtype for details .

It can be C Decreased as you stated. This states that proton / neutron / electronics information is captured for the Atom: there is one entry for each entry. For example. each sheath / energy level is not differentiated; zero is acceptable for neutrons (instead of Null).

• I have already discussed the great value of Predicates. The main thing here is that the model identifies the predicates. and Predicates validate the model; this is a great feedback loop. I have given Predicates so you can evaluate them for yourself and check if the model is correct.

3.3

If it was completely D Normalized : Atom always has at least a Proton record; the Neutron record is optional; and each Shell / Energy level is differentiated.

• Note the difference in predicates.

• Note that while Reduction is a valid method, it does not conform to normalization.

3.4

This appears to be the sum of everything, laid out flat or flattened out (derived relations, perspective, result set). How good it is for understanding. But if you proposed it as a set of tables, then it is terribly incorrect due to various normalization errors. Which, if fixed, would go into [3.3] and mine [D Normalized].

Question

Does any of the above constructs break the rules of the relational model?

All of them, except [3.3], violate a number of rules. They mostly fall into the category of normalization errors. There would be associated identification errors if you provided the complete model or CREATE TABLE statements.

But it doesn't matter if the context is a data modeling exercise, to understand. If this exercise was serious, then the paragraph above.

^{This section is presented in accordance with SO guidelines, specifically: correct misinformation when you see it . I have commented on this thread, but they keep disappearing. So I posted it here.}

Erwin Smout:
  When shortened to its core, the relational data model has at most one "rule": all information in the database must be represented as attribute values ​​in tuples in relationships.

This is one of the rules, yes, but the supplied statement is clearly false.

First, there are many first or first order rules in the relational model. From memory I would say about forty.

Second, there are many second-order rules that are logically implied by first-order rules.

• People with technical qualifications and experience, who can understand RM, and who follow spirit and intent, follow all of them.

• Others may not recognize some first-order rules or most of the implied rules.

• And there is, as evidenced by books that claim to be about RM and others are people who are actively destroying and shrinking RM. They ignore second-order rules, and worse, they use pharisaic "logic" to undermine first-order rules.

• Here Erwin, well known for his RM efforts on comp.databases.theory and TTM, reduces RM to one correct rule and thus undermines the full ruleset, and RM himself. In particular, in response to your question, which, if not for my answer, would lead readers to believe that RM is what it does: just one rule that everything, attitude and non-attitude, "satisfies" ...

• The relational model is freely available, you can read it yourself. Let me know if you would like a copy. The caveat is that the terminology is out of date and needs to be explained.

Secondly, even if it was necessary to weld it down to one rule (impossible, too reductionist) or the most important rule (perhaps, but humiliating), this rule will not exist. It's one of forty or so first-order rules, but it certainly wasn't close to the top.

• However, I suggest that other people might have different rankings according to their own goals.

• What people who understand RM are discussing, as the main difference (not a rule) between RM and its predecessors is this:

  • He was the first to receive a complete mathematical definition (which forms its basis, and everything in it flows from this).

  • Whereas predecessors map records using physical record identifiers, RM requires (a) logical keys made up of data, and (b) linking strings (not records) with those logical keys.

It should be mentioned that the basis for systems that are characterized by record identifiers in each file, declared as "primary key" are completely non-relational, a regression to pre-1970 ISAM record systems that RM is obsolete. Also notice how you can make these primitive systems appear "relational" because, according to schizophrenic "logic," they "satisfy" one quoted rule. Honest logic destroys such nonsense.

Such identity-based systems have become a burrow at the bottom of the industry precisely because of disinformation. Hence my willingness to fix it.

^{Complete the section on correcting misinformation.}

What's the best approach?

Modeling formal data, including relational normalization. Method, science, principle, not fragments of NF definitions.

I don’t see sentences as different approaches, and this puts all of your thoughts into one modeling exercise. And the point at which the model starts to take on a serious, acceptable form is [3.3].

Does it depend on how we think about the data?

Sure. Your marriage will be successful or unsuccessful based on your wife's perception, because that perception is the seat of all your actions. The model will succeed or fail based on your perception of the data.

One of the great things about the relational model is that it teaches us to view (perceive, think, model) data as data and nothing but data. First, it forms the concept of a logical key.

Does it depend on the requirements?

The first answer is no, it shouldn't depend on requirements. It should take into account data that is limited by the enterprise (a requirement, yes, but not a functional requirement) and only data.

And, of course, for reasons that I have detailed elsewhere, the data model must match the real world, it must not be limited by the functional need for data analysis.

A massive error, a common reason for failure in the OO / ORM model, is that it takes data from the tiny lens of the OO / ORM model. It cannot decouple Data vs Process and treats data as simple "persistent" submission of objects. There are many other errors in this model that I will not list here, the point is that they start from the position of the requirement and ignore the data.

Second answer: the project is not commissioned until the requirement is established, reality if the funds are requirements based. In this way, a mature project manager ensures that the requirement contains sufficient justification for analyzing and modeling data as data separate from functions.

If it depends on the requirements, should we choose the simplest design first and then make it more versatile to meet the new requirements?

You could, but it will cost a lot. The mature consistency is to get the data model as soon as possible.

If the data model matches the real world, when changes and additions appear, it is easy to extend it. Conversely, if the data model was minimal for a functional requirement, or if it does not match the real world, then changes will be complex and costly.

While the resulting data models have a lot in common, the original design can influence the table / column naming and the key domains differ.

Sure.

If we chose to use one table for each type of thing, we could choose incompatible keys for Atom and Molecule, such as atomic atom for Atom and molecule name for Molecule.

It would be a terrible mistake. Never put anything in a container that doesn't match the label. Never put two different things in the same container (which has the same label). The correct method would be to use the common identifier name (which is Atom- or Molecule- or Particle-name) and use subtypes.

If we decide to use a generic approach, we can choose a common key for all particles.

Only if there is. And if this does not happen, it means that the entities do not match, that the universal model cannot be used.

Changing keys can have a greater impact on the system, so it can be difficult to transition from a simple design to a generic one.

Well, the idea is to select data items that are stable (not static) to form a key. And yes, key design is an important aspect of modeling. If you follow the Relational Model, Keys form the logical structure of the database. The domain is very important (I think you understand that). And yes, it's expensive.

This brings us back to the main question. That is why Keys must be modeled and selected correctly, for each table, as well as for all of its children.

Update 1 and 2

I noticed your two updates just now. This is not a complete answer, it is very short now.

• So far, I have understood Particle as a set of Atoms plus a set of Molecules. This is what I modeled in D Normalized . Both have a name, a common key. He is a subtype.

But now, given your hierarchical charts and data samples (thanks), I understand that what I thought you meant and what you meant are two different things. Consider the Updated TRD and hierarchy :

• Your part is a collection of Molecules plus many Atoms plus many subatomic particles.

  • This is not true

  • There is a hierarchy, yes, but it still exists in a sequence of tables, not as a hierarchy within a single table.

  • Otherwise, the two sets (atoms, molecules) are discrete, each with its own set of components that are different. There is no set that includes everything (except the theoretical universal set).

  Updated table mapping model E Normalized • Update 2 . Subtypes have been removed along with Particle. It provides all of the requirements in Update 2. Note the updated predicates.

• Your hierarchical diagram is wrong.

  • Your mistake is because you have combined the classifier hierarchy (structure, container) with data (classifier instances, content). You cannot do this. You need two separate charts, one for the container and one for the content.

  • This is a common mistake in OO / ORM thinking. Failure to adhere to scientific principle separates data and process . The exact error described in my answer to Hidders in the previous question. Results are complex objects that never work.

  • So your hierarchy diagram is illegal, they are two completely different diagrams combined into one.

  The hierarchy (classification) reflects this and only this.

  Hierarchy G (sample data) illustrates this and that alone.

  There is a difference in style between the way you depict hierarchies (organization chart) and the way you display them (explorer). One ends up very wide, the other more compact. I think you can figure it out.

• You had some clarity at the end of the previous question. The new concept of Type in this poisonous book has completely puzzled you. This problem, these problems, has nothing to do with the type.

More words are required, I will answer more fully as time permits.