It puts all seven data items into a single table. For an invoice with several line items, the same slip number, date, business partner code and department name must be stored again for every row. The repeating items are not separated out, so the data is not normalized, and it invites inconsistency when data is updated (update anomalies).
×Incorrectイ
Separating out the line-item part is fine, but the line-item table [product code, quantity, unit price] has no slip number, so there is no way to tell which line item belongs to which slip. The tables cannot be joined back into the original invoice, so this way of splitting is inappropriate.
○ウ
正解One invoice mixes two kinds of data items: slip number, date, business partner code and department name are items that take exactly one value per invoice, while product code, quantity and unit price repeat once for every line item. The first step of normalization is to separate this repeating part into another table (first normal form). Choice ウ splits the data into a slip header table [slip number, date, business partner code, department name] and a line-item table [slip number, product code, quantity, unit price]. Because the line-item table carries the slip number, you can tell which slip each line belongs to; the primary key of the line-item table is [slip number + product code], and the slip number works as a foreign key pointing to the slip header table. Even after the split the original invoice can be reconstructed, and the date and business partner code are no longer recorded redundantly, so ウ is appropriate.
×Incorrectエ
It splits the data into three tables — slip number with date, business partner code with department name, and the line items — but none of the tables has enough keys to link it to the others. You cannot tell which slip the business partner code and department name belong to, nor which slip the line items belong to, so again the original invoice cannot be reconstructed. This is an example of splitting too far and cutting the relationships.
ここがポイント
The first step of normalization is to move the repeating items (the line items) into a separate table. Splitting a slip into a header part and a line-item part is the basic pattern.
Once you split a table, always keep in the separated table an item that points back to the original table (a foreign key, here the slip number). Without it the tables cannot be joined and the original data cannot be reconstructed.