Home  Back Topics CD Orders  INDEX 
Databases
A database allows information to be stored in a systematic way.

 A database program will allow a user to...

  • create and edit the data structure.
  • add, delete and modify data.
  • format the data
  • import data from other sources (and export it)
  • use queries to search for information
  • produce reports about the data
  • set security on the data

Flat file / Relational Database

All organisations need to store and retrieve information easily and quickly. A database not only stores the data, but also organises the data and controls access to it with a program called the DBMS (DataBase Management System).

The earliest data storage systems used flat files. A flat file is like information stored in a grid.

A flat file used by a video shop....

Name Type Certificate Cost of Hire
Clueless Comedy 12 £2.00
Goldeneye Action 12 £2.00
To Die For Drama 15 £2.50
Apollo 13 Drama PG £2.50

Each row is a record.

Each column contains information on one field.

No two records in a file can be the same or confusion will result, so each record has a primary key field, which uniquely identifies each record. Usually a special field is added for this....

Code Name Type Certificate Cost of Hire
V0001 Clueless Comedy 12 £2.00
V0002 Goldeneye Action 12 £2.00
V0003 To Die For Drama 15 £2.50
V0004 Apollo 13 Drama PG £2.50

The Code field is the primary key field.

Flat files are inefficient....

The video shop wants to add fields to record who hires out the videos.

Code Name Type Certificate Cost of Hire Name Address Date
V0001 Clueless Comedy 12 £2.00 John Moses 5 Deane Road 23/11/99
V0002 Goldeneye Action 12 £2.00      
V0003 To Die For Drama 15 £2.50 John Moses 5 Dean Road 23/11/99
V0004 Apollo 13 Drama PG £2.50      

This file is inefficient because -

  • some of the data is repeated which wastes disc space.
  • there could be possible inconsistencies in the data (as above)

...so use a database with two tables...

A VIDEO table :

Code Name Type Certificate Cost of Hire Mem No Date
V0001 Clueless Comedy 12 £2.00 M0001 23/11/99
V0002 Goldeneye Action 12 £2.00    
V0003 To Die For Drama 15 £2.50 M0001 23/11/99
V0004 Apollo 13 Drama PG £2.50    

A MEMBER table...

Mem No Name Address
M0001 John Moses 5 Dean Road
M0002 Jane Good 27 The Avenue
M0003 Amrit Patel 8 Glenn Drive

Each table in a database contains information about an entity eg Member, Video etc

There is a link between the 'Mem No' field of the VIDEO table and the 'Mem No' field of the MEMBER table. A field common to two different tables is called a foreign field. If there are links between tables then we have a....

 
 

Relational Database

There may be many links between tables in a relational database.

These links (relationships) may be...

  • one-to-one
    Products and bar-codes in a supermarket.
  • one-to-many
    One video club member may loan a number of videos.
  • many-to-many
    Pupils and Teachers in a school.

Advantages of relational databases over flat-file :

  • Data consistency.
    Each data item is stored only once. There is no danger of data being updated in one place and not in another.
  • No data redundancy
    ie no data is unnecessarily duplicated..thus wasting storage space.
  • Greater security
    The DBMS will ensure that only authorised users have access to the data.

Disadvantages....

  • complexity of setting up and maintaining the database.
  • if failure occurs, all applications using the data are affected.

Data in a database should be independent of the applications which use it. For example, it should be possible to add a field to a table without affected existing applications.