Database

Multiple tables

The table below shows the flat file database we previously discussed, which could have been used to store the data about members and DVD rentals.

DVD Code	Title	Cost	Date Out	Date In	Member Number	Name	Telephone Number
002	Finding Nemo	£2.50	03/09/04	04/09/04	1034	John Silver	142536
003	American Pie	£2.50	27/08/04	28/08/04	1056	Fred Flinstone	817263
003	American Pie	£2.50	01/09/04	02/09/04	1012	Isobel Ringer	293847
008	The Pianist	£2.50	04/09/04	06/09/04	1097	Annette Kirton	384756
011	Notting Hill	£2.50	27/08/04	28/08/04	1012	Isobel Ringe	293847
014	Prime Suspect	£2.00	27/08/04	28/08/04	1097	Annette Kirkton	384756
015	Shrek	£1.50	10/09/04	11/09/04	1034	Joan Silver	142536

 

Using a single table is not the best way of storing the data.

• There is unnecessary duplication of data. For example, Annette Kirton’s name and telephone number have been duplicated for every DVD she has rented.

 

• There is the possibility of data inconsistency. For example, the member with code 1034 has rented two DVDs. But who is the member? Is it John Silver or Joan Silver?

In the example above, the data inconsistency is a direct result of data duplication. If we can prevent duplicating data unnecessarily, then we can eliminate the possibility of data inconsistency.

The solution to the problem of duplication is to store the details in two separate tables, corresponding to the entities MEMBER and DVD RENTAL.

Tables for DVD RENTAL and MEMBER

DVD Code	Title	Cost	Date Out	Date In
002	Finding Nemo	£2.50	03/09/04	04/09/04
003	American Pie	£2.50	27/08/04	28/08/04
003	American Pie	£2.50	01/09/04	02/09/04
008	The Pianist	£2.50	04/09/04	06/09/04
011	Notting Hill	£2.50	27/08/04	28/08/04
014	Prime Suspect	£2.00	27/08/04	28/08/04
015	Shrek	£1.50	10/09/04	11/09/04

 

Member Number	Name	Telephone Number
1034	John Silver	142536
1056	Fred Flinstone	817263
1012	Isobel Ringer	293847
1097	Annette Kirton	384756
1012	Isobel Ringe	293847
1097	Annette Kirkton	384756
1034	Joan Silver	142536

Notice that the member details for John Silver and Annette Kirton now appear only once, removing the problem of unnecessary duplication and data inconsistency. However, simply splitting the original table in two means the link between DVD rentals and members has now been broken—we can no longer tell who has rented which DVD!

To restore the link, an extra field must be added to one of the tables. There are two possibilities to consider:

1. Add the DVD code to the MEMBER table, as shown:

Member Number	Name	Telephone Number	DVD Code
1034	John Silver	142536	?
1056	Fred Flinstone	817263	011
1012	Isobel Ringer	293847	003
1097	Annette Kirton	384756	?

This solution has a problem, however. What value should be entered into DVD Code for members 1034 and 1097? Because multi-valued attributes are not permitted, there is only space for one value. The only way to store information about another rented DVD is to add another row which means duplicating the member details again!

2. Add the member number to the DVD RENTAL table, as shown:

DVD Code	Title	Cost	Date Out	Date In	Member Number
002	Finding Nemo	£2.50	03/09/04	04/09/04	1034
003	American Pie	£2.50	27/08/04	28/08/04	1056
003	American Pie	£2.50	01/09/04	02/09/04	1012
008	The Pianist	£2.50	04/09/04	06/09/04	1097
011	Notting Hill	£2.50	27/08/04	28/08/04	1012
014	Prime Suspect	£2.00	27/08/04	28/08/04	1097
015	Shrek	£1.50	10/09/04	11/09/04	1034

This solution is better, as each DVD can have only one member renting it at a time. Notice that Member Number is now duplicated – it appears in both the MEMBER table and the DVD RENTAL table – but this time the duplication is necessary