0

This is w.r.t Database Normalisation, when I have a redundant table that causes anomalies, I would want to consider splitting it in smaller tables as per the NF rules.

BUT

Say I split a big table into two eg. Student and Marks Table my questions is ---

How do I know I've done the right split?

Or to put it in another way,

How can I cross check if I haven't lost any information after the split i.e. I can still do a join on both tables and get my un-normalized table back?

This is easy to inspect visually when you have a small number of attributes, but what if the table is really huge.

How can I ensure that I've split the table in a right manner?

When I was in college my professor spoke of some equation which must always be true whenever you split tables, that ensures that the split is done without loss of any information. And by doing the right joins we can get the big dirty redundant table back.

1
  • See my comment on Renzo's (currently accepted) answer.
    – philipxy
    Sep 12, 2020 at 17:44

3 Answers 3

2

A classical theorem in normalization theory says that you have a lossless join (that is a decomposition of a relation such that when you perform the natural join of the decomposed relations you obtain the original relation) in the case of two relations if and only if the intersection of the attributes of the relations contains a candidate key for one of the two relations.

This result can be generalized to a decomposition with any number of relations.

1
  • The theorem tells you that the join is a lossless combination of the 2. It doesn't tell you that the original design is. Unless you already know that the 2 are lossless decompositions of the original--which begs the question.
    – philipxy
    Sep 12, 2020 at 17:53
0

Using joins, attempt to rebuild the original data exactly. If you DBMS supports it, I suggest the use of EXCEPT:

IF EXISTS (
        SELECT * FROM A EXCEPT SELECT * FROM B
        UNION ALL
        SELECT * FROM B EXCEPT SELECT * FROM A
    ) 
    PRINT 'There is a problem'

Note: the columns must match and, in this case NULL will equal NULL

2
  • This requires a visual check. What if its not possible to visually inspect the table... Like if a table with many columns is split
    – Oliver
    Sep 12, 2020 at 6:39
  • You replace the print with whatever mechanism that works for your infrastructure This could be an insert, a raiseserror/throw, calling another procedure, basically anything. It's also worth noting that you can, in fact many consumers can read print statements,depending on infrastructure.
    – Graham
    Sep 13, 2020 at 18:04
0

It seems like your asking for the practical side of this problem. f you want true verification there are orders confidence:

  • Easiest: Perform a row count on the original and also the join. Even in large datasets, this really should take that much time.

  • Moderate: Compare unique identifiers between the two and look for differences.

  • Hardest: You could create a hash of the entire table to verify all data is the same. Join the new tables back and create another hash - and compare.

On another note, it's very unlikely that a modern and established RDBMS would lose data unless there is some very funky and weird complex conditional split (logical issue).

However, if this verification is that important then time really shouldn't be a constraint. If time is still that important, then I consider optimizing the database and tables and/or the underlying memory, compute, and storage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.