Is it possible to get rid of so-called 'Spurious Tuples' completely?

Is it possible to get rid of so-called 'Spurious Tuples' completely?

For example: In this example from a textbook, there is an original table:

I don't see anything wrong with the two projections of it:

But their join is still generating spurious tuples:

(These figures from ToddEverett's answer.]

This is a great question. Normalization beyond BCNF is extremely hard to understand. Hopefully I can provide an answer that makes sense. I struggled with these concepts for over 20 years before finally making sense of them thanks to Fabian Pascal's Practical Database Foundation Series.

The example provided is an EmpRoleProj R-table that looks like so:

It then goes on to show projections of the original EmpRoleProj R-table like so:

The reason you don't see anything wrong with the base tables Table 1 and Table 2 is that you aren't considering the dependency rules (in this case multivalued dependency (MVD) rules) defined in the business model describing the business rules. If we assume for the sake of example there are no MVDs defined in the business rules then EmpRoleProj is in 5NF despite the "appearance" of redundancies. It appears for example that the information that Smith is a designer is stored redundantly. It also appears that the information that a designer is needed on the Amazon project is stored redundantly. While this appears to be the case, by learning that in fact these are not MVDs, what is actually the case is that Smith happens to be a designer on a few projects, but it is not a fact that Smith is a designer and thus that fact should not be inferred. When table 1 and table 2 are joined, the result:

shows Jones to be a designer on the Nile project but we know that is not the case.

Let us assume instead the business model did say there were MVDs of empName-->>role and role-->>projName. In this case, what those MVDs mean is that if an employee plays a role, and if that role is on a project, by definition that employee plays that role on that project. In this example, that same EmpRoleProj table is now not in 5NF and now does suffer from redundancy. Now, the facts that Smith is a designer and there is a need for a designer on the Amazon project are stored redundantly as those facts could be inferred from joining Table 1 and Table 2! Likewise, taking the join of Table 1 and Table 2 now does not result in a spurious tuple as the inference that Jones is a designer on the Nile project is a fact now based upon the business rules defined by the MVDs.

This is why you cannot assess the normal form of any R-table without knowing the dependencies and the defined key. Making any assumption, even one that seems to you to make sense, can be dangerous. If you are ever asked what normal form an R-Table is in, you must ask for the dependencies to assess. In addition to Fabian's series of papers, Chris Date's works provide the best information available on normalization theory.

StackExchange Database Administrators Q#121519, answer score: 8