8. Advantages of a relational database

Splitting data into a number of related tables brings many advantages over a flat file database. These include:

1. Data is only stored once. In the previous example, the city data was gathered into one table so now there is only one record per city. The advantages of this are

• No multiple record changes needed
• More efficient storage
• Simple to delete or modify details.
• All records in other tables having a link to that entry will show the change.

2. Complex queries can be carried out. A language called SQL has been developed to allow programmers to 'Insert', 'Update', 'Delete', 'Create', 'Drop' table records. These actions are further refined by a 'Where' clause. For example

SELECT * FROM Customer WHERE ID = 2

This SQL statement will extract record number 2 from the Customer table. Far more complicated queries can be written that can extract data from many tables at once.

3. Better security. By splitting data into tables, certain tables can be made confidential. When a person logs on with their username and password, the system can then limit access only to those tables whose records they are authorised to view. For example, a receptionist would be able to view employee location and contact details but not their salary. A salesman may see his team's sales performance but not competing teams.

4. Cater for future requirements. By having data held in separate tables, it is simple to add records that are not yet needed but may be in the future. For example, the city table could be expanded to include every city and town in the country, even though no other records are using them all as yet. A flat file database cannot do this.

Summary - advantages of a relational database over flat file

• Avoids data duplication
• Avoids inconsistent records
• Easier to change data
• Easier to change data format
• Data can be added and removed easily
• Easier to maintain security.