Re: Equivalence of hierarchical and flat data formats

On Thu, Jul 28, 2022 at 7:09 AM Roger L Costello <[email protected]> wrote:


> Suppose I want to update the list of authors. Specifically, I want to add
> “John Doe” to the beginning of the AUT01 author list:
>
>
>
> AUT01   John Doe, Alfred V. Aho, Ravi Sethi, Jeffrey Ullman
>
>
>
> That update operation seems pretty straightforward to me. What is the
> issue that I’m not seeing?
>

That if you have any other books by Aho, Sethi, and Ullman, they will all
become authored by Doe, Aho, Sethi, and Ullman, which is probably not what
you want.  Furthermore, if you want to find all books by Sethi, you are out
of luck, because "Aho, Sethi, and Ullman" doesn't match "Sethi".

This is what normalization resolves.  You need a third table with three
columns: book id, author id, and author sequence number, with rows
{(BOOK01, AUTH04, 1), (BOOK01, AUTH05, 2), (BOOK01, AUTH06, 3), ...]},
where AUTH04-06 are Aho, Sethi, and Ullman respectively, and AUTH01 can be
removed altogether.