Problems, Bad Database

Below you see a database with three tables. This database has been badly designed. Each table has a number of problems.

Table Subscription

City Name Newspaper Publisher
Gouda HJ Nootenboom Automatisering Gids Stam
Gouda H.J Nootenboom Computable VNU
Zevenhoven S Peuskens Computable Stam

Problems of the table Subscription

  • The publisher of the newspaper Computable is ambiguous (polyinterpretable). For one reader it's Stam, for another one it's VNU.
  • One and the same reader is registered with different spellings in the adminstration.

Delivery Address

Name Postcode Newspaper Address
HJ Nootenboom 2803 DA Autom. Gids Groenhovenweg 43
H.J. Nootenboom 12345ABC Computable Groenh. weg 43-II

Problems of the table Delivery Address

  • The postcode 12345ABC is wrong, does not even match the Dutch format. But there is no way to check the correctness of a postcode.
  • The address of one reader is recorded twice. That is not just cumbersome when the reader moves (dubble update), but it is unclear which one is the right version.
  • The subscription of S Peuskens to Computable lacks address data.

Invoice Data

Price Birthdate Name Postcode City
75.00 32 sep 1951 Henk Jan Nootenboom 00000 HHH Juinen
0.00 01-04-1572 Stefan Peuskens 2435xh Z. Hoven

Problems of the table Invoice Data

  • It's impossible to relate invoice data and subscriptions. It's unclear which price is valid for a which newspaper. Somebody can get an invoice bill, without subscription, or vice versa. There isn't a good foreign key.
  • The postcode and city of Henk Jan Nootenboom are not existent in the Netherlands. The invoice will never arrive. It's impossible for the computer to get the address data from a different table, as a result of a slightly different spelling of the name.
  • Stefan Peuskens will get a invoice of 0 guilders, but will not receive a newspaper, as the delivery address is unknown. Could it be that this € 0,- has some special meaning and as a result there will be no invoice sent, and no newspaper delivered?
  • The column birhtdate is irrelevant, is outside the scope. The dates 01-04-1572 and 32 Sep 1951 are not reliable, probably specified wronly on purpose.