Database - How To Movies
 
1. Setting up and creating tables and fields 2. Formatting Fields. 3. Creating Primary Key and Relationships 4. Searching and Creating Reports 5. Editing Reports
 
Home DatabaseUsing Information Internet NAB/Coursework Revision Legal Responsibilities

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.

 

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


 

 



 

Intermediate II Home
Database | Using Information | Internet | NAB/Coursework| Revision
Log into Glow | School Website | Contact Us