A good deal of this post deals with concepts that are outside of the DBE CAPS CAT curriculum, however, understanding these concepts will enable better teaching and a better grasp of what IS in the syllabus. This article attempts to explain the concepts in the simplest (and possibly not even technically correct) language possible, with links to more in-depth articles covering important concepts as well as further examples.
In this post:
1. Types of databases
Databases store data. Some are more structured than others. Microsoft Access is an example of a Relational Database Management System (“RDBMS”).
There are a number of other well-known RDBMSs available: MySQL, PostgreSQL, Microsoft SQL Server, MariaDB, SQLite.
These databases use Structured Query Language (“SQL”) to create and run queries.
In recent years, so-called NoSQL databases like MongoDB have become popular.
2. GIGO
Garbage In, Garbage Out (“GIGO”) simply means that if you allow invalid, incorrect data to be inserted into your database, your results will be poor and even inaccurate. Many of the techniques that we will cover (for example validation) are employed to ensure that data is captured as easily and accurately as possible.
3. Bloat
Avoid bloat. The smaller the database tables in terms of the amount of data being stored on disk the faster you can expect the database to run. Access has a Tool to assist you in this, the Compact and Repair Database tool.
4. Store Data Once
Store data once. This concept is crucial in almost all instances of data storage in ICT and helps maintain the integrity of the data. Storing the same data in multiple places creates the risk of editing or deleting one instance (copy) of the data but not the other(s).
5. When you must repeat data, repeat the smallest possible value possible.
A table with many blank fields and a table where long strings or values are repeated are both often indications that something could be improved.
6. Do not store data that can be deduced from existing data
Do not store data that can be calculated or deduced from existing data.
For example, if you store a person’s birth date in your database, it is unnecessary to store their age as well, as their age can easily be calculated using their date of birth.
7. Validation
Validation can also be applied in Excel. See the Data validation in Excel tutorial for more.
Validating data involves preventing the wrong type of data from ending up in your database. Validation in Access is achieved using several features: Data Types, Field Length, Validation Rules and Masks. See the following posts:
8. Primary Key
The Primary Key (“PK“) of a table is the field in a table that stores the value that uniquely identifies the record.
Read more about the PK in the post: The Primary Key in a database table.
9. Index
The index property is only mentioned in the DBE CAPS CAT Curriculum as far as it can be used to prevent duplicate values in a field.
The concept of a table index is complex. Most importantly, the database engine uses indexed fields to speed up searching and sorting operations and as you might already have guessed, the Primary Key is always an indexed field. If indexed fields speed querying up, it might seem like a good idea to simply index all your fields. This is not true, as the index comes at a speed cost of its own. See the Benchmark testing tutorial (coming soon…).
We also come across the term index when we work with Excel’s VLOOKUP function.
10. Next steps
Move on to the Create an Access database tutorial.
References:
- Wikimedia Foundation. (2023) NoSQL. Available at: https://en.wikipedia.org/wiki/NoSQL (Accessed: 17 October 2023).