What is being done elsewhere? – using a database

Save Your Planet is an energy conservation charity. It exists to encourage people to use energy efficiently and to start using alternative energy sources. It has started to collect data about energy use in homes around the country including:

• the type and size of homes
• the type and amount of energy being consumed
• measures being taken to conserve energy
• use of alternative energy sources.

All the people who provided data said that the charity could contact them by email in the future. The charity wants to send targetted mailshots to encourage people to save energy and may want to ask some people for further information.

Have a look at the data collected so far. It has been stored as a text file and it is not very useful in its present form. You must find out what each item of data is by looking at the list provided by the charity. You will see that codes have been used for some data items.

Design and build a suitable database structure to store the data. Make sure that each field is a suitable data type and length. There will be thousands more records added to the database in the future so you must create simple validation rules to protect the integrity of the data.

Once you are satisfied with your database structure, import the data set.

Design and create a data entry form for adding new records. It must be clear and easy for other people to use, especially when codes are used or when there are a limited number of valid options.

Use your data entry form to add two more records to the database. One of these should be for your own home. Test your form by asking someone else to use it to enter data for their own home.

Your database can now be used to extract lists of households for targetted mailshots. The charity committee will decide which groups of people to target but you need to provide some examples.

You need to design queries to extract the information and then produce the sample lists in a suitable report format.

Here are some examples:

• to identify households who appear to be using more energy than they need so that they can be encouraged to use less
• to identify households that may be eligible for grants because they are not taking simple steps to conserve energy
• to identify households who use alternative energy so that they can be contacted for further information
• to identify households who are making the most effort to conserve energy so that they can be given an award

You might want to try some of these before you think of some useful lists of your own.