Re: How to normalize this?

Op dinsdag 7 mei 2013 11:54:47 UTC+2 schreef nvitac..._at_gmail.com het volgende:
> The way I see it, the decomposition is not the end of the story. At that point, a much more complex process starts: you must discover how to keep the whole database schema together (in the most basic case, this means you have to discover which INDs hold). This is where normalization theory falls short, in my opinion. For example:
>
>
>
> S(A,B,C), {A -> BC}
>
> R(A,B), {}
>
>
>
> are each in BCNF. Now, suppose that you determine that R[A,B] ⊆ S[A,B]. Does that introduce any redundancy? Does it change the keys of R? Is it still a good database schema (in some sense to be defined)?

Am I allowed to interject my own tentative answers here ?

Does it [introducing the IND] introduce redundancy ?

Yes. Because the IND now causes the A->B FD to "carry over" to R.

Does it changes the keys of R ?

Yes. Because that IND has been "carried over".

Is it still a good database design ?

No, unless in the case when I very deliberately want to retain the redundancy in the database, as a sort of "four-eyes" check on the actual assertions that are being made in the database update process[es].

The constraint (the IND, that is) that is needed to "control this redundancy" is a proposition that says "for any A value in R, the corresponding B value found in R must be as can also be found in the corresponding tuple from S, which must exist."

An S updater might have asserted "A value is 3 and corresponding B value is 7".

An R updater (or, depending on how you look at it, the entire overall database update process) is now "double-checked" by forcing him to also specify the B value 7 whenever he inserts the A value 3. Forcing him to sort of "confirm his awareness of the nature of that IND".

In all other cases, I would remove the redundancy. Received on Tue May 07 2013 - 16:20:58 CEST