Certificate IV in Information Technology

South Regional TAFE, Albany Campus, Western Australia


The database units we studied in Certificate IV were as follows:

I am not including the unit "Apply structured query language to extract and manipulate data" in this section of the website, since all the SQL subject matter we looked at this semester was confined to Web Design (not only MySQL but also--mainly--as Microsoft's T-SQL or Transact-SQL). As such, there was very little coding to do, and not an SQL statement to be seen in the entirety of the Database course I am presently writing about on this part of the Certificate IV website.

The small amount of coding we actually did was in Visual Basic (VBA) for Access formulae. The thing about MS Access is that it can produce some impressive results while at the same time being often frustrating to do things that should be easy, and it was also a frequent occurrence among us students to forget basic principles of Access like comboboxes on forms, how to run basic queries, etc. Fortunately, as our lecturer said, "I won't let anyone fail, if they put the effort in." We all worked hard, and all of us passed.

Since then I have obtained some very handy MS Office textbooks from a friend who just completed his Certificate II in IT, and to him I am grateful and can guarantee they will be put to good use.

We had two major assessments for our database units, both to be designed in Microsoft Access: the Grove Securities database, and the Great Bay Marina database. These switchboards are the final interfaces for the two assessments; my lecturer gave me some very positive feedback on my designs. Take a look:

GroveDB Switchboard

Grove Securities Database Switchboard

Great Bay Marina Switchboard

Great Bay Marina Database Switchboard

Not only did we cover the more advanced features of Microsoft Access in Cert IV, but we also explored concepts that will really help us ascertain how to build a database for a business or company.

So before even putting fingers to keyboards, our prerogative was to identify physical database requirements.

Design Principles

Industry Design Principles

There are certain principles involved in database design (cf. the diagram above). If you are a student and interested in learning more about it, I would recommend that you read as much as possible on the subject and experiment with your own database prototypes. If you are a businessperson, contact me if you want one built for you!

Here's a good couple of PDFs for educational purposes and my personal reference (the first is concise and excellent; the latter is larger, but also much more visually appealing with plenty of pictures, diagrams and illustrations):

Database Design Basics [249.9KB]

Creating and Using Databases with Microsoft Access [9.7MB]

Questions for the Client

So to find out what the client wants you to build them is the first stage of database design; if you have read the Home Page to this website you will see that I plan to specialise in this line of IT work.

Suppose I were to create a very simple database to keep track of golf tournament scores. I would need to keep records of the players, the tournaments, and the scores. During our "focus group" or class discussion we identified that this would make for a three-table database.

Now suppose I were to interview a client who wants to keep track of his or her employees including whether they have a police clearance. My line of questioning would go something like this:

  1. What is your proposed solution?
  2. What information do you want listed about your staff?
  3. Who will use the database?
  4. What system are the directors using?
  5. What information do you want the timetable to provide?
  6. What format do you want the police clearance field to be displayed in?
  7. In which location will the database be stored?
  8. Do you want only current staff to be listed in your records?
  9. What kind of reports do you want to be able to generate?

(That was actually a few sample questions from the Grove Securities interview, to which I will refer as necessary when I need to illustrate further points in database design.)

Now, let's have a nice long list of questions for, say, Albany World of Cars...

Let's just say the client did not answer all these questions thoroughly, thereby giving us a poor idea of where to start in building the database. Now we want to keep his or her contact info on hand so that we can confirm details pertaining to the design, as we go along building it. Suppose the client wants to be able to view a report to display the profit or loss made on each sale, and that they have dealerships in Denmark and Mt Barker. That means the database will need to be adjusted somewhat, costing you time, and time is money.

As my lecturer for this unit rightly pointed out, the more accurate information you can glean from the customer in the initial interview, the better!

For the Grove Securities Database, we gathered the following information:

The client wants a database created to capture staff performance and information, to keep a record of how bonuses are divided among staff, to keep a record of all performance notes she makes over time, and to execute specific reporting over that time. The database must be accessible by the two directors of Grove Securities and should be compatible with the version of Microsoft Access that they are using (Access 2007). It will be hosted on a terminal server which provides the intranet for the two locations of the company, Perth and Albany.

This was valuable information, and as we followed the specifics outlined in the initial brief (e.g. fonts and colour scheme), little further confirmation on design specifics was needed during the implementation of the database.

The Final Results

Each database took approximately eight to ten weeks from start to finish, and to give you a good overview of how they turned out, I am including below two User Guides, normally intended for the client or a novice database user.

Grove Logo

Grove Securities Database User Guide [287.9KB]

Great Bay Logo

Great Bay Marina Database User Guide [610KB]

ER Diagrams

Here's a peek for you behind the scenes of database design. There were two documents we had to create and submit for each of the Grove and the Great Bay assessments: an Entity-Relationship Diagram and a Data Dictionary. MS Access can create ER diagrams automatically, which meant that it was acceptable--though not preferable--for me to take a screenshot of it and just submit that. The latter, Great Bay Marina ER, was done in Draw.io (a very versatile online drawing program I also used for my European Luxury Hotels mockup in Programming).

Though they are very similar to each other, I feel that Draw.io ER diagram allows a better perspective from the view of foreign keys, unique keys etc. than the MS Access one.

Grove Entity Relationship diagram

Grove Securities Entity-Relationship diagram (MS Access)

Great Bay Marina Relationship diagram

Great Bay Marina Entity-Relationship diagram (Draw.io)

Data Dictionaries

A data dictionary is similar to an ER diagram in that it represents what will be contained within the database, only in a different format, generally as a spreadsheet with table names, field names, data types, field sizes, whether the field is a (primary, foreign or unique) key, and whether it is required to hold data, the alternative being that it can contain NULL values. A description of what the field is used for is helpful in reminding oneself of what to put in there, and, the bigger the database, the greater the probable utility of a description of the contents of the field.

We have been using Microsoft Excel to create data dictionaries. Here are images of the Grove DD and the Great Bay DD (note the absence of the Description column in the Grove DD and the presence of it in the Great Bay Marina DD):

Grove Securities Data Dictionary

Grove Securities Data Dictionary

Great Bay Marina Data Dictionary

Great Bay Marina Data Dictionary

Databases: My passion in IT

I love SQL

Databases are definitely "my thing" when it comes to IT. We had one student drop out of Certificate III last year because he hated SQL so much. Well, I am the complete opposite! I love how logical database design is, how structured, and how so incredibly useful it is in real-world applications; applications that are rich and varied.

I have been keeping track of the latest developments in big data (MongoDB, Apache Cassandra etc.) and could definitely envision myself making a career out of database design. If you share the same passion and would like to talk databases, email me and I will try to respond ASAP.