That Blue Square Thing

Applied ICT A Level Unit 3 - Database Design

Note: this page applies to the AQA Applied ICT A level specification. This exam was withdrawn in 2012, with final exams in 2013. The content will be retained as an archive and because it has some stuff that might be quite useful for someone or other

Circuit board imageMegan's Database - a worked example

The aim of this page is to demonstrate the development of a relational database from scratch, as well as show why a relational database is useful.

If you're looking for something a bit more complex, then try Emo's Painters Database. That assumes a bit more knowledge and understanding about databases, but is a sound example of what can be done quite nicely.

But let's start here with the basics...

Starting with the basics...

It's important to understand what databases do and how we can use them. The essential purpose is to organise information - often large amounts of it - and allow that information to be retrieved and presented when required. This involves the use of queries to search for information and reports to present it. Data entry forms might be used to help with these processes.

So we might have a database for a school or college where each student takes a class with a particular tutor. This can easily be modelled using a simple database:

PDF iconAn introduction to basic databases

But there are issues with such a simple database - especially when you need to update it.

The solution is to develop a relational database. Essentially this puts the information about each lot of data in a different table - so in our example, all the student information goes in one table and all the tutor information in another table.

This makes updating (and adding) information a lot easier, but it needs to be well designed and then linked together. That's the tricky bit...

PDF iconMoving to a relational database

Megans's Job Database

Now we know the basics, we can design a database which will work for lots of basic applications. As a first database to build it's not a bad thing.

Note that the type of job is never specified in the files. That's deliberate - you could apply this database design to a wide range of job types without too much alternation - you just need to decide what types of costs might be involved and add them as required.

PDF iconA specification for Megan's Job Database

You can build this database. It's not hard to do - just watch the data types.

Once you have it built you need to enter some basic data - four or five customers, four or five types of jobs and then decide on some jobs for specific customers for tblJob.

Now it's time to start to use the database.

PDF iconList of jobs for Megan's Database

Use queries to produce the lists. You can then use reports to produce outputs which are fit for purpose...

You should probably look at That Blue Square Blog for all the really good other A Level stuff.