Consider the foll. dependencies.
ABC->D and BCE->F
For the above dependencies, is it better to have a surrogate key (no practical use whatsoever), S and create the foll. tables:-
SBC, ASD and SEF
(Since ASBCD or ABCEF would have a dependency which prevents it from being in 2NF).
Or will DB2 automatically take care, i mean, will it have no effect on the efficiency of the DB if the foll. tables:-
ABCD and BCEF
are used instead?
The no. of tables created in case surrogate keys are used is also more. Besides that, how do we depict the surrogate key table in the ER diagram in case surrogate keys are used? If they are depicted in the normal way, they won't make any sense. So what do we do? Do we design the ER diagram in the non-optimized form (without surrogate keys)? I don't think that is acceptable.