How do I ensure I have split my tables without loss of data?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.