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 imagePart 2 - Designing the system

In Part 2 you need to design - or specify - the database system that you will produce.

Most of the marks here refer to AO3 of the markgrid. This section is a bit strange because you can get some of the marks for showing what you actually produce - so if you look at row 4, for example, the first two marks are got through having actually processed some data, but for the last two you have to have actually specified (designed) what you're going to process before you start.

The key things you need to specify are:

  1. the data structures - AO3 row 6
  2. the data types - AO3 row 7
  3. the outputs (reports on paper and forms on screen) - AO3 row 5
  4. the processing, mainly through queries - AO3 row 4 (this also links back to the last two marks in AO2 row 2)

Data Structures - AO3 row 6:

What tables are you going to use? How are they related? What types of relationship are you using? What fields will go in each table? Why are you doing all of this?

A diagram showing the tables (with their names), what fields are going to be in each table and how they will be linked together is suitable here. Primary and foreign keys need to be specified as well. For the fourth mark you need to explain why you're organising it this way.

Here's an example of a data structure diagram I produced for Dave's Financial Advisers. Dave is going to use this database to keep track of meetings that his advisers have with clients. It's a very simple three table design.

entity relationship diagram

This sort of diagram is sometimes called an Entity Relationship Diagram.

Things to note:

Note - you'd have to explain these sorts of points to get that fourth mark...

There is another example of table design on the Emo's Painters database page.

Important: this is a really important section to design well. If you launch straight in to making the database and get the data structure wrong then it'll be a pain to sort it out again afterwards. It would probably be a good idea to get a rough idea of what the tables will look like and then run it past someone before you go much further.
Markgrid A)3 Row 6
Note: this markscheme is a copy of one available freely on the AQA Applied ICT webpages. It is copyright AQA and reproduced here simply to make access easier for students. No attempt to claim copyright is being made, although I could have copied the text into my own interpretation...

Data Types - AO3 row 7:

Next you need to design the detail for each table. We need to see:

This is best presented in a data dictionary:

PDF iconexample data dictionary (for Dave's Financial Advisors)

Word icondata dictionary grid

There's another example of a data dictionary on the Emo's Painters database page.

For the fourth mark you need to have explained why what you're planning is suitable. You can include this in a data dictionary table, but you'll need to make sure that your justifications are detailed. It's useful to link them clearly to discussions with your client rather than simply use generic points which could apply to any database.

PDF iconsome important notes about different data types to use. It is highly recommended that you read these carefully and make use of as wide a range of appropriate data types as is sensible.

PDF iconMake sure you've read the notes about how to name database items as well. This is important!

markgrid AO3 Row 7
Note: this markscheme is a copy of one available freely on the AQA Applied ICT webpages. It is copyright AQA and reproduced here simply to make access easier for students. No attempt to claim copyright is being made, although I could have copied the text into my own interpretation...

Outputs - AO3 row 5:

What outputs are going to be produced - either to the screen or to paper?

Start by looking back at the tasks your client wants the database to do. You need to include

These need to be designed - sketches are a good idea here.

You need to show that you're adapting standard outputs, perhaps by including custom colour schemes and logos on reports and forms. There are some example reports on the Emo's Painters database page that might be worth a look.

Once you have designs you might want to check them with your client to make sure that they meet their needs. It's a good idea to show how you've done this - perhaps by getting some written feedback from your client at this point.

Make sure that your sketches show the layout of reports and forms and exactly what data items are going to be included on them. If there are going to be buttons then we need to know what they'll do.

Again, the fourth mark involves explaining why you're doing all this.

Markgrid AO3 row 5
Note: this markscheme is a copy of one available freely on the AQA Applied ICT webpages. It is copyright AQA and reproduced here simply to make access easier for students. No attempt to claim copyright is being made, although I could have copied the text into my own interpretation...

Processing (Query Design)- AO3 row 4:

Your database will need to use queries to process data.

You need to specify each query in detail - what type of query it is and which fields are going to be selected from which tables. Using a version of a Query Grid is a good way to do this:

PDF iconquery design grid - PDF version to print and complete by hand

Word iconquery design grid - Word 2007 version

The important thing here is to go back to the client's needs and remember what they need to produce. Most reports or screen outputs will need some kind of query to produce them, especially if data is going to come from more than one table. Client needs are the place to look for this - if necessary you may need to go back to your client and meet with them (or exchange e -mails) to check what they want.

Try using the Database Query Designer Tool as a way of getting your head around how databases can be queired to produce useful outputs.

You need to make sure you're processing data so that it forms useful outputs. As well as queries you might also process data by:

There are example query grids and examples to show how you can include processing on the Emo's Painters database page.

For the fourth mark this all needs to be explained as well.

Markgrid AO3 row 4
Note: this markscheme is a copy of one available freely on the AQA Applied ICT webpages. It is copyright AQA and reproduced here simply to make access easier for students. No attempt to claim copyright is being made, although I could have copied the text into my own interpretation...

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