Jot down your ideas. The following table is an amalgamation of a cut down version of CUSTOMER and INVOICEITEM tables from the INVOICE database. It has some serious design issues:
| CustABN | CustName | CustAddress | CustSuburb | InvoiceNo | InvDate | ItemNo | ItemDesc | ItemPrice | Quantity |
|---|---|---|---|---|---|---|---|---|---|
| 31002054803 | Mellow Yellow Lighting | 546 Mountain Highway | Pokolbin | 3 | 16-Sept-2004 | 1300-2202 | Wide Screen | $455.00 | 4 |
| 31002054803 | Mellow Yellow Lighting | 546 Mountain Highway | Pokolbin | 3 | 16-Sept-2004 | 1200-2236 | FrostFreeze Deluxe | $523.00 | 5 |
| 31002054803 | Mellow Yellow Lighting | 546 Mountain Highway | Pokolbin | 4 | 6-Oct-2004 | 1200-2236 | FrostFreeze Deluxe | $523.00 | 3 |
| 15234556678 | Victor Panels | 56a Beatrice St | Cannon Hill | 5 | 13-Apr-2005 | 1500-2100 | SpinWash | $760.00 | 3 |
For example, what happens in each of the following situations?
Jot down your ideas.
1. A new customer, say Genna Bear, needs to be added.
Genna Bear cannot be sensibly added without an invoice. The database has no "easy" way to record the new customer's details. This is an insertion anomaly.
2. Mellow Yellow Lighting moves to a new location
The CustAddress and CustSuburb fields for each of the Mellow Yellow Lighting rows needs to be updated. If by accident only some of the rows updated then the database will be inconsistent. Two different addresses will be recorded, which is the correct address? This is a modification anomaly.
3. Invoice number 5 was a mistake and needs to be removed from the database
If invoice number 5 is deleted, the details for Victor Panels will be lost. This is a deletion anomaly.
4. Consider why none of these problems arise in the original INVOICE database.
None of these problems arise in the original INVOICE database because it is properly designed. In particular unlike the sample table given here, in the INVOICE database there is no redundancy.