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.
