SQL多表连接求助：基于OWNER、TYPE、PERSON表实现5NF去伪行

Got it, let's walk through this step by step. You're trying to show how 5NF eliminates spurious rows when recombining decomposed tables, but you're stuck on getting the three-table join right. First, let's recap your data to make sure we're aligned:

• Table1 (OWNER, TYPE): (O1, T1), (O1, T2), (O2, T1)
• Table2 (OWNER, PERSON): (O1, P1), (O1, P2), (O2, P1)
• Table3 (TYPE, PERSON): (T1, P1), (T2, P2), (T1, P2)

Why Pairwise Joins Create Spurious Rows #

When you join just two of these tables, you're only enforcing one relationship (e.g., owner-type or owner-person), which leads to invalid combinations. For example:

• Joining Table1 and Table2 gives you all TYPE + PERSON pairs for each OWNER, even if that type-person pair doesn't exist in Table3. That's how you get spurious rows like (O1, T2, P1)—there's no link between T2 and P1 in Table3, so this row shouldn't exist in your final result.

The Correct Three-Table Join #

To fix this, you need to enforce all three relationships at once. That means joining all three tables such that every (OWNER, TYPE, PERSON) triplet is valid across all three tables. This is exactly what 5NF ensures: when you decompose a table into smaller relations (like your three binary tables), joining them back should only produce valid rows—no spurious ones.

Here's a standard SQL query that does this with INNER JOIN:

SELECT 
    t1.OWNER,
    t1.TYPE,
    t2.PERSON
FROM 
    Table1 t1
INNER JOIN 
    Table2 t2 ON t1.OWNER = t2.OWNER
INNER JOIN 
    Table3 t3 ON t1.TYPE = t3.TYPE AND t2.PERSON = t3.PERSON;

Let's Break Down the Logic #

1. First, we link Table1 and Table2 on OWNER—this gives us all combinations of TYPE and PERSON for each owner, but includes those spurious rows we don't want.
2. Then, we join that result to Table3 using both TYPE and PERSON as the join keys. This filters out any rows where the type-person pair isn't present in Table3, eliminating the spurious entries.

Your Final Result Set (FinalTable) #

Running this query will give you only the valid triplets:

• (O1, T1, P1)
• (O1, T1, P2)
• (O1, T2, P2)
• (O2, T1, P1)

How This Demonstrates 5NF #

5NF (Fifth Normal Form) is all about removing join dependencies that can't be split into smaller, independent dependencies. Your three original tables represent the three binary join dependencies of the full (OWNER, TYPE, PERSON) relation. By joining all three tables with the correct keys, you're reconstructing the original valid relation without introducing invalid rows—proving that the decomposition into these three tables is 5NF-compliant, and that 5NF prevents spurious rows from popping up when recombining the data.

内容的提问来源于stack exchange，提问作者JamMan9