How do I set up primary keys for a relationship?

Position

Any practice that is not based on a solid theory is not worth considering. I am a strong relational model with a strong theoretical foundation. The relational model is based on solid theory and has never been refuted ¹ . There is nothing solid about what goes for "relational theory", I took them and refuted my concepts in my space. Also, relational database design is science, not magic, not art ² so I can provide evidence for any of the suggestions or accusations I make. My answers are taken from this position.

^{1. These are articles that are not related to science, and a lot of opinions from those who do not understand science, yes, but there is no scientific refutation. Just as the pygmies claim that man cannot fly, he is "true" for them, but not suitable for humanity, he is based on a complete inability to understand the principle of flight.}

^{2. Of course, there is art in the presentations of high-profile practitioners, but that does not make science art. It is science, and only science, and beyond that, it can be skillfully delivered in models and databases.}

"Relationship Theory"

I want to know how to properly set up primary keys in a relation. For example. we have an ER diagram containing the elements:

If it were ERD, then you would not be looking at "relationships", you would be looking at entities (if the diagram was early) or tables (if they were progressed). "Relationships" is a beautiful abstraction that has nothing to do with implementation. ERD or data model means implementation (not abstract, real), the intention of the physical leaves the abstract world of theory and enters the physical world, where idiotic abstractions are destroyed.

Further, "theorists" who claim to serve in the database space cannot distinguish between base relations and derived relations: while this may be acceptable in an abstract context, it is not true in an implementation context. For example. the underlying relationships are tables and should be normalized; derived relations are, well, derived, representations of the underlying relationship, which by definition are flattened representations (rather than "denormalized", which means something slightly different) of the underlying relationship. Therefore, they should not be normalized.

• But "theorists" are trying to "normalize" derived relations. And the most damaged two are trying to define the definition of 1NF, which we had for forty-five years, which is fundamental and solid, that they themselves maintained, changed, so that their derivative relations, which do not need to be "normalized", "can be classified as “normalized.” It would be fun if it weren't so sad.

One wonderful quality of the objective truth of science is that it does not change. Subjective "truth", non-science, changes all the time. What you can rely on must be understood before you start practicing, and other things are not worth reading.

Highlighting

They live in a world of their own, isolated from the reality of relational databases, in particular the relational model, and the industry they claim in ministry. Forty-five years after RM came out, they have done nothing to promote the RM or Relational databases.

• Keep in mind that they develop all sorts of concepts that are outside of the relational model.

• RM's progress (completing what the Neanderthals suggested was "incomplete") was only due to standardization (R Brown and others working with Codd, leading to the IDEF1X standard for relational database modeling) and the efforts of high-performance SQL vendors and their customers ...

• These are commercial RDBMS vendors that were already established in the 1980s, not the Free-free / shareware / vapourware Non-sql groups of the last decade, which are streaming their wares as "sql", which gives you well and glued to yours. " platform "are not portable.

Worst of all, they publish books about their non-relational concepts and fraudulently call them "relational." And the "professors" blindly "teach" this nonsense, like parrots, without understanding either the nonsense or the relational model that it should investigate.

• If you are trying to find answers to some kind of "educational" project, sorry, I cannot explain it, because "education", as you can see, is completely confused and has non-relational requirements.

• I can, however, provide direct answers to a question driven by science, the relational model, the laws of physics, etc.

The point to be drawn from this is that Relational Theory and Practice were very close after Dr. EF Codd published his original work, and around the time that SQL platforms were developed by vendors in the post-Codd era. that the transitions for the "theory of relativity" are completely separate from this original relational theory.

• I can list the differences, but not here. Please note: if you read my posts that touch on this topic, you can collect this data and list it yourself. Or ask a new question.

Question

I want to know how to properly set up primary keys in a relation. For example. we have an ER diagram containing the elements:

No ERD to check. Ok, in the update you have an example. Perfect for your questions because this is a set of custom data views and modeling can now begin. But please note that this is not an ERD or a model. We rely on understanding the data; analyzing it; classifying it, rather than looking at the data with a microscope. I understand that you were taught to do this.

To translate it into a relational model

Yes, this is the stated goal. The word "translate" is incorrect because RM is not just a flat or fixed set of criteria that "meets" or fits (as "theorists" know), it also provides specific Methods and Rules . Therefore, we will Model according to the relational model .

we have to do some tricks.

We don't need tricks, we use science and only science. The "theorists" and the "professors" who follow them need tricks and practice non-science. I cannot help in this regard. Also, the tricks they use tend to bypass and undermine the relational model, so watch out for them.

Surrogate

All of the items above deal with the primary keys of the relationship, but they are all natural keys, so we can leave them as they are or replace them with surrogate keys.

Well, here it is, the first trick of your "teacher".

• Surrogates are physical record pointers (not strings), they are illogical.

• There is no such thing as a "surrogate key", these two words contradict each other.

  • The key has a specific definition in RM, it must be composed of data . The surrogate is not made up of data, it is fabricated, a meaningless number generated by the system. Therefore, it is not a key or a "key".

  • A key in RM has a number of relational qualities that make Keys very powerful. Since the surrogate is not a Key, it does not have any of these qualities, it has no relational power.

  • Hence, "surrogate" and "key" have specific meanings, and they are quite good as separate terms, but together they contradict each other because they are opposite.

  • When people use their term "surrogate key," they naturally expect some, if not all, of the quality of the key. But they won't get any of them. Therefore they are deceived.

• The relational model (one that theorists know nothing about) has a definite rule of path independence . As long as relational keys are used, this rule persists. It provides relational integrity ¹ .

  • Using a surrogate violates this rule. Corollary ² is that relational integrity and relational navigation ³ are lost.

  • The consequence of this is that many more connections are required to obtain the same data (no less, since the lovers of mythology and magic continue to soar).

  • Therefore surrogates are not allowed and the other has a separate account.

• Since you are in the modeling phase, either conceptual or logical, and Keys are logical and surrogates are physical, surrogates don't have to fit into the picture. (They only come into the picture, if at all, for consideration, only when the logical model is complete and the physical model is considered.) You are nowhere close to completing the logic, so the introduction of a surrogate should raise a red flag.

  The "teacher" and the author of the "textbook" he uses are fraudulent in two separate counts:

  • They introduce a physical field into a Logic Exercise that shouldn't touch the physical aspects of the database.

  • But at the same time, the effect they have is that they establish a surrogate, a physical thing, as a logical thing. Thus, they poison the mind.

It's straight science, pure logic, uncontaminated by mad thinking and thus immune to fraud. No surrogates at the logical stage.

^{1. Relational integrity (which the relational model provides) is clearly different from referential integrity (which is provided by SQL and data writing systems). If you don't understand this, please open a new question "What's the difference ..." and ping me.}

^{2. Violation of any rule always has undesirable consequences other than the act itself.}

^{3. If you don't understand this, please open a new question "What is relational navigation ..." and ping me.}

So the final answer to your question is:

All of the items above deal with the primary keys of the relationship, but they are all natural keys, so we can leave them as they are or replace them with surrogate keys.

In the conceptual and logical exercise, we only deal with logical keys. Physical concepts like surrogate are illegal. Replacing the logical key with a physical creature in the Logic exercise is rejected. Use the keys you have that are natural.

Not a "replacement"

There is one more point. The term "replacement" is incorrect. A surrogate never replaces or replaces a natural key.

• One of the many qualities that a natural key provides is the uniqueness of a row , and this is also required in the relational model, duplicate rows are not allowed.

• Since the surrogate is not a key to a string (it is a physical pointer to a record), it cannot provide the required string uniqueness. If you don't fully understand what I am talking about, read this answer , from top to False Teachers . Check out the test exercises.

• Hence, a surrogate, even if considered during the physical modeling phase, is always an optional column and index . It is not a replacement for a natural relational key.

• Conversely, if the surrogate is implemented as a replacement, the consequence is duplicate rows, a non-relational file, not a relational table.

Case 1

The key attribute is a name, so it must be of type CHAR or VARCHAR. Usually names become key attributes.

Yes.

They are often codes (users do use codes). Often the codes will come out to you (you have a very good example in One More Update ). {D | R | B} will do the same {<| ^ | >}. This is, of course, towards the end of the logical model stage, when the model is stable and one shuts down the keys and optimizes them. At any stage before this, there are wide Natural keys.

The idea is to keep it meaningful.

• Keys make sense (surrogates don't make sense). One of the qualities of a relational key is that this meaning carries over wherever the key is carried as a foreign key.

• And according to your example, wherever it is used. Including program code. Record:

   IF CrewType = "Backup"  -- meaningful but fixes a value
   IF CrewType = 1         -- meaningless
  
        
  
          
          
          
        
  
      

just wrong. Because (a) it is not really Key, and (b) the user can change the value of this database from Backup

to Reserve

, etc. Never write code that accesses a data value, a descriptor. Thus, the fact: Backup

is the projection of the Key, the statement, and the code is the key. This resolves CrewType.Name and Key is CrewTypeCode.

     IF CrewTypeCode = "B"   -- Key, meaningful, not fixed

      

        
        
        
      

    

While we're on the keys, note:

• In the relational model, we have primary keys, alternate keys, and foreign keys (migrated primary keys).

• We don't have candidate keys, there is no such thing in RM. It is something made outside of RM. Therefore, it is non-relational.

  Even worse, they are used by people who implement surrogates as "primary keys" ^a .

• Physical consideration ^b but which must be understood and applied during the exercise. When the data is understood and known, the columns will be of fixed length. When they are unknown, they can be variables. For Keys, given that they will be indexed, at least on the Primary side, they should never be variables, because this requires unpacking on every access.

<sup>and. Using the SQL keyword PRIMARY KEY

doesn't magically turn a surrogate into a PC. Following RM, one (a) identifies possible Keys (no surrogates) and then (b) chooses one as Primary, which (c) means the election is over, so (d) nominated candidates can no longer be called "candidates ", the event is history, so (e) remainder, non-primary keys, are alternate keys.</sup>

^{"Candidate Key" is the rejection of RM compliance and the purpose of the PC, so it itself has nothing to do with relationships. Apart from the fact that they have a surrogate as "primary key" which is the second non-relational item.}

^{b. For those non-technical people who believe that no technical knowledge and foresight, no physical considerations at all should be evaluated during the logical, this penalty, evaluated at the physical level. Since I am not covering the physical here, I am just taking a note for Umbra.}

Magicians rely on their tricks to make rabbit bunnies look like lions. They don't need scientists.

Case 2

Two (or more) identifying relationships become the composite primary key of the relationship (which is made from foreign keys).

I think you have the correct idea, but the wording is incorrect for the general case.

• This statement is correct for an Associative Table that has two foreign keys. Yes, in this case two FKs form a PK, that's all that is needed to make the strings unique. Nothing can improve this. Adding a record ID is redundant.

• For the general case for any table:

  • Relationship Identification ¹ makes the FK (Ported Parent PC) to be part of the PK in the child. Hence name, parent id.

  • This makes the child table dependent ^1, which means that child rows can only exist in the context of the parent row. Such tables form intermediate and leaf nodes in data hierarchies, they are the majority of tables in a relational database.

  • If the row can exist independently, the table is Independent ¹ . Such tables make up the top of every data hierarchy, very few in a relational database.

  • A Non-identifying relationship ¹ is one where the FK (migrated parent PK) is not used to form the child PK.

  • Composite or composite keys simply have more than one column and are standard rates in relational databases. Every table other than the top of every data hierarchy will have a composite key. If you don't have them, the database is not relational.

Please read the IDEF1X Introduction carefully .

^{1. "Theorists" do not differentiate Identity versus Non-Identifier or Dependent versus Independent: all of their files are independent; all their "relationships" between record pointers are not identified. This is a regression for pre-1970 ISAM record storage systems lacking relational integrity, power, and speed. That's all they understand, that's all they can teach. Fraudulently referred to as "relational".}

Case 3

Identifying relationships with weak key attributes also becomes a composite master key.

The term "weak" with or without reference to "key" is not defined in the relational model. This is the fiction of the "theoreticians". Therefore, I cannot answer this question.

• I note that some of the "theoretical" works contain strong Keys (a normal English word describing the fact that a Key has been installed earlier) as "weak" and weak "Keys" (a normal English word describing the fact that a "key "not previously established) as" strong ". This is the nature of schizophrenia.

• Therefore, I suspect that this is part and parcel of their proven attempt to confuse science with non-science and undermine the relational model. In the old days, when such people were locked up, humanity was healthy. They now write books and teach in colleges.

Case 4

Associative objects usually have two or more identification relationships

Yes. Two are correct.

If you have more than two, then it is not fully normalized. Codd provides an explicit method to normalize this, so that there will be two (or more) associative objects, out of two exactly identifying relationships.

• "... therefore all n-ary (more than two) relations ... can be ... and must be solved by binary (two) relations."
  (paraphrased for this context)

to be Junction Tables.

Not. Join and join table relationships are not defined in the relational model, so they are not relational.

Associative entities in logical become associative tables in physical.

Answer Too Long

Answer completion exceeded limit for SO answers. So I put the answer in one document and provided a link. Splitting the answer at this point turned out to be a sin, so the document contains the entire answer with consistent formatting, etc .:

Full answer

• To continue from that point (i.e. the SO answer text, above), simply scroll down to the heading Case 4 .

• There is value in keeping the above SO answer text not only for historical purposes but also for text queries, etc.