Applied ICT A Level Unit 3 - Database Design
Designing Queries
This is one of the areas people seem to find the most difficult. You might know what a query is (hey, it's just a search for some data out of the database) but it's how the heck you design on that tends to throw people.
I'm going to use an example database sometimes in this. It's one for a set of evening classes. There are some notes you might want to have handy about it.
the example evening class database notes
You might want to use the Query Designer drag and drop tool to help you get the hang of this (try having it open in another window and then following through the examples by dragging field around the window).
A simple approach
Step 1: Start by choosing a really simple output your database need to produce. Make it the easiest one you can think of.
exempli gratia: I want a print out of the phone and e-mail contact details for all the tutors who run evening classes.
Hint: I'd probably go for a printed output first.
Step 2: Look at your tables - your data dictionary is a good place to check. Exactly what bits of data do you want on the output? Choose all the data items that you'd want to have on the output. Write them down.
exempli gratia: I want the tutor's names, their phone number, their mobile number and their e-mail address. Nothing else.
Step 3: This is a good opportunity to design what the output might look like. A fairly rough sort of sketch will work nicely here. Show what data items you want to include on the output and roughly where you want them to be. Don't forget headings, addresses, logos and so on.
Step 4: Now, how can you get the database to select the right data for your output? What can you search for? This is the query design.
exempli gratia: I need each tutor's contact details so I don't actually have to search for anything in this case to get the output I want, simply select the right data. That's absolutely fine - it's a query without any search criteria. The other examples show you how I might need to search for stuff.
Step 5: Get a query design grid and fill it in. You'll need to include all the data items you chose in step 2. You then need to decide exactly what to search for from step 4 and add that to the criteria box. For more complex queries you may need to do calculations as well. The examples on the Emo's Painters database Page show you how you can do this.
query design grid - PDF version
query design grid - Word 2007 version
example query design grid - the tutor's contact details sheet query
Other Examples:
Example 1 - A register: Tutor's need a register to mark attendance at sessions. I need to be able to create a register based on the course code - the course ID field. I want to include: student names, the course ID code and name, the start and finish dates, the course night and the room. I'll also want the tutor's names on it as well. These are the fields I need to include in my query.
To get the right data up I'll need to have a parameter search for the course ID code. I can enter the ID code and the right register will be printed off.
Example 2 - An invoice: I need to send an invoice to students. Sometimes people pay when they register, so I need to only print invoices for students who haven't already paid.
On the print out I'll need: the students title and names and their address fields (studAddress1, studAddress2, studTown and studPostcode). I'll then need to include the course title, it's start and finish dates, the night of the week it runs and the fees (possibly the room depending on what the client decides they want me to produce) and the tutor's names and title.
I'll need to search for students who haven't paid yet, so this will mean searching the assignPaid field, so I'll need to have that in the query as well (but not on the printed report). I'll search for anyone who hasn't paid, so this means searching for False values for assignPaid (or 'N' values - basically the box hasn't been ticked).
You could add many other types of queries. The Emo's Painters database example shows some of these. There are others again. They all depend on what outputs your client wants - which is why this process starts with that.