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 image Emo's Painters - an example database

Emo's Painters and Decorators is a small decorating business based in Ballamory. As I'm sure you can imagine, with all those different coloured houses there's always going to be plenty of work for a good painter and decorator who can charge competitive rates.

What's the story?

Emo needs an easy to use database to keep track of the bills she has to produce. In particular there are problems with the number of holiday cottages she decorates. These are popular in Ballamory - several owners have more than one cottage.

In particular, Emo wants to be able to generate quotations and invoices. Her list of transactions goes something like:

There are other things that Emo's database might do, but these will do for now.

Woodwork - making the tables

The tables which will make up Emo's database aren't too complex. Each table contains data about one thing - one entity. That's the way it should be.

Tables for emos database

Notice that every link has referential integrity enforced. The links are all One to Many links - for example, each owner can have many properties, but each property can only have one owner.

The links are from the Primary Key in one table to a field with the same name in another table. I've kept it simple and used an autonumber for each primary key. This means that whenever the same field name appears in another table it needs to have the data type number - and will be a foreign key.

Moving House - specifying the property table

As an example of a table specification, here's how I would look to design tblProperty in Emo's database.

PDF iconspecification for tblProperty

Note again that ownerID is data type number - it's used as a foreign key to link owners to their properties. Looking at the data in the tables, you can see that some owners have more than one property. The one to many relationship makes this easy to deal with in the database.

Emo's implemented tblJob shows the range of data types which are being used

tblJobs for emos database

Each type of data is matched to the right field for the right purpose. Notice the Yes/No fields in particular - these are well worth thinking about in your specifications.

Obstacle Course - specifying the queries

Let's look at how Emo might specify some queries.

PDF iconqryOwnersList - a query to produce a list of owners with their contact details. This is a simple query - the data is all found in tblOwner and all the data is selected by the query. The only search criteria is that the owners must currently own houses - the database allows for the fact that Emo might want to keep the details of owners who don't currently own a house in Balamory.

Note that the report which comes from this query sorts the data alphabetically by the name of the owner - this is specified on the query design grid as well. That counts as an element of processing - which allows access to makes in AO1 row 2 and AO3 row 4 of the markgrid. There are other, more important, types of processing, but sorting a list is an easy way to get some of the marks for processing. Just make sure you evidence it.

PDF iconqryJobDetails - produces a set of details for one specific property. A multi-table query using a parameter - a value the user enters. In this case the parameter is the property ID code - the primary key for the property table.

Note that this query is more advanced. In the context of the markgrids it allows access beyond 1 mark in AO1 row 3. It does this because it's a multi-table query and because it uses a parameter.

PDF iconqryInvoice - produces an invoice to send to the owner after the job is done. This is a multi-table query again and uses a parameter. It also calculates the total bill within the query - this is what's going on in the red type rows of the specification.

This is very clear evidence of processing data. This allows access to marks in AO1 row 2 and AO3 row 4 of the markgrid.

The way you produce the calculations in an actual query grid is shown below. It's not that hard to do, but be careful exactly what you type as it's easy to make a simple typo!

screenshot of qryInvoice

The criteria make sure that the query produces results for jobs that are done but that haven't been paid yet. The jobID is then the parameter.

Notice that the calculations are conducted step by step. This makes it easier to check for errors and to lay the details out nicely on the invoice itself (see below).

The Lost Letter - dealing with concatenation

An excellent way of adding to your processing marks is to use concatenation in a report.

Concatenation joins two (or more) sets of data together to form one piece of data on a report. It's helpful to use when you're addressing something to join names together and avoid ugly gaps on letters or invoices and the like.

You can concatenate in two ways - in the query or in the final report.

In a query, you simply pull in the different fields required and space them using ampersands (the & has a proper name!) and gaps to make sure there are spaces.

concatenation in a query

In a report you use an unbound textbox and join the fields in a similar way:

concatenating in a report

Paper Chase - producing the reports

Emo's database produces a range of reports. These have been formatted to make sure that they produce helpful outputs which meet the needs of the client. This allows good access to makes in AO1 row 2 and row 4 and AO3 row 4 and row 5.

These are all actual Access 2007 reports, produced in an hour or so. They can be a bit tricky to start with, but if you start by designing a sensible output that keep to a grid like pattern then you'll probably find you can produce it quite easily in Access.

PDF iconrptJobDetails - based on qryJobDetails. This produces a job sheet to give to Emo's workmen when they go to a job.

PDF iconrptOwnersList - based on qryOwnersList, this is a report showing all the current owners. Notice that it also counts the number of owners on the list. This is easy to do - the =Count(*) unbound textbox on the screenshot below shows how it's done.

screenshot showing counting on a report

This screenshot also shows you how you can, if you want, reduce someone's forename to just their first initial. See how it's done - check the difference with the screenshot showing concatenation above.

PDF iconrptInvoice - based on qryInvoice, this produces a full invoice.

Image iconIf you compare it with an example of an actual invoice, you'll see it's not too different at all. I'd probably want to add a logo for Emo's business on the report, but otherwise it's exactly the sort of thing required for full marks on the outputs sections.

Likes and Don't Likes - Yes/No fields on reports

Yes/No fields will show up as tickboxes on reports. But sometimes tickboxes aren't what you want. Wouldn't it be good if there was an easy way of turning tickboxes into words of some kind?

So, what you need to use are iif statements. Yes, that's two i's in that iif.

Put these in textboxes on the report. The code, which you can see in the screenshot, is saying: if this field (in the square brackets) is Yes then do the first thing, otherwise do the second thing. And, yes, that's a minus 1 representing "Yes" in the Yes/No field.

Iif statement screenshot

Note that in the second example, I want nothing to be displayed if the box isn't ticked, so I've used an "empty string" which is shown as "". All this means is "show nothing"

As usual with any sort of code, the syntax is absolutely important - get the brackets right and get the commas in the right place!

It should go without saying that the rather odd subtitles on this page are all episodes titles from Balamory. You see how useful Wikipedia can be?

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