Databases and Data (Grade 12 NSC Matric Computer Application Technology): Revision Notes
Databases and Data
Understanding databases in the modern world
Databases have become one of the most essential tools in our digital age. Think of them as highly organised digital filing systems that allow us to store, access, and analyse information quickly and efficiently. Unlike the old days when doctors kept patient records in physical filing cabinets, modern databases store vast amounts of information electronically whilst maintaining organisation and accessibility.
The beauty of a well-designed database lies in its ability to handle everything from simple tasks like maintaining a class register to complex operations such as tracking academic performance across an entire school. However, for a database to function effectively, it must be properly designed with its specific purpose in mind.
Modern database systems have revolutionised how organisations manage information. From small businesses tracking customer orders to hospitals managing patient records, databases provide the backbone for efficient data management in virtually every industry.
What makes data valuable
The foundation of good databases
A database is essentially a collection of related information that can be processed and analysed to provide meaningful insights. However, not all data is created equal. For your database to be truly useful, the information it contains must meet specific quality standards.
Data integrity refers to how accurate and consistent your information remains over time. For example, if a field is meant to store numerical values like test scores, it should never contain alphabetic characters. This consistency ensures that your database functions properly and produces reliable results.
Data redundancy occurs when the same information appears in multiple locations within your database. Whilst this might seem harmless, it can lead to serious problems. Imagine storing the same student's address in five different places - if they move house, you'd need to update all five locations, and missing even one creates inconsistency.
Avoiding Data Redundancy is Critical
Data redundancy is one of the most common causes of database errors. When the same information exists in multiple places, updates must be made everywhere the data appears. Missing even one location can lead to inconsistent information and unreliable results.
Essential characteristics of quality data
Good database information should always be:
-
Up to date: Information from several years ago may not reflect current reality. Student contact details, for instance, should be regularly updated to remain useful.
-
Complete: Missing information can lead to inaccurate conclusions. If half the responses in a survey are blank, your analysis won't provide reliable insights.
-
Relevant: Your database should only contain information that serves its intended purpose. If you're tracking student ages, storing their favourite meals would be irrelevant and wasteful.
How database components work together
Modern database systems like Microsoft Access organise information through four main components that work together seamlessly.
Tables serve as the foundation, storing your actual data in organised rows and columns. Think of them as digital spreadsheets where each row represents a complete record (like one student's information) and each column represents a specific field (like their surname or email address).
Forms provide user-friendly interfaces that make data entry and viewing much easier than working directly with tables. Instead of navigating complex spreadsheet-like views, forms present information in an organised, easy-to-read format.
Queries allow you to ask specific questions of your data and retrieve targeted results. For example, you might query a student database to find all learners from a particular city or those who achieved above a certain grade.
Reports present your data in professional, printable formats that are ideal for sharing information or creating summaries.
Worked Example: Database Workflow in Action
Consider a school's student management system:
- Tables store all student information (names, addresses, grades)
- Forms allow teachers to easily enter new student data or update existing records
- Queries help find all students in Grade 10 or those living in a specific area
- Reports generate class lists, grade summaries, or parent communication documents
This workflow demonstrates how each component serves a specific purpose while contributing to the overall system.
Understanding tables and data storage
In Access databases, information is stored in tables consisting of records (rows) and fields (columns). Each field is designed to organise data according to its type, ensuring consistency and enabling proper data entry.
The table structure demonstrates how customer information from Johannesburg is organised with fields for First Name, Surname, Email, and City. Notice how each record contains complete information for one customer, whilst each field contains a specific type of information across all records.
Think of database tables like a well-organised filing system. Each drawer (table) contains folders (records) with specific documents (fields) in the same position in every folder. This consistency makes finding and updating information much more efficient than searching through unorganised files.
Data types in Access databases
When designing database tables, you must specify what type of information each field will store. This ensures data consistency and enables proper processing.
| Data Type | Description |
|---|---|
| Short text | Text information such as names, surnames, ID numbers, cell phone numbers etc. |
| Long text | Very lengthy text such as memos, detailed product descriptions etc. |
| Number | Numerical data used in calculations |
| Date/Time | Date and time information, both past and present dates |
| Currency | Monetary data such as Rand, Dollar, etc. |
| Autonumber | Unique numbers automatically generated by Access |
| Yes/No | Binary choices that can be selected such as yes/no or true/false |
| OLE object | Links to objects such as tables or graphs from other applications, or for inserting pictures |
| Hyperlink | Links to documents on the internet or other locations |
| Attachment | Attaches files to the database |
| Calculated | Contains answers to calculations |
Primary keys deserve special attention as they serve a crucial function in database design. The primary key is a field selected by the database creator to uniquely identify each record in a table. No two records can have the same primary key value, ensuring that each entry remains distinct and traceable.
Primary Keys Must Be Unique
Every table should have a primary key field that contains unique values for each record. This prevents duplicate entries and ensures that every record can be precisely identified. Common primary keys include student ID numbers, employee codes, or customer reference numbers.
Working with database components
Forms for data collection
Forms transform the sometimes intimidating appearance of database tables into user-friendly interfaces. When collecting information through surveys or registrations, forms make the process more intuitive for users whilst ensuring data is entered correctly into the appropriate fields.
When designing forms, consider what information you need to collect before creating the interface. Ensure that your table fields correspond to the data you want to capture, using appropriate data types for each piece of information.
Queries for finding information
Once you've stored data in tables, queries become your primary tool for extracting specific information. Rather than manually searching through hundreds or thousands of records, queries allow you to ask targeted questions and receive precise answers.
Queries use logical operators to process your requests:
| Operator | Description |
|---|---|
| AND | Only returns results when both conditions are true |
| OR | Returns results when at least one condition is true |
| NOT | Returns results when the condition is false |
Worked Example: Using Query Operators
Searching a student database for specific criteria:
- Using AND: Find students who are "in Grade 12 AND live in Cape Town" - returns only students who meet both conditions
- Using OR: Find students who are "in Grade 11 OR Grade 12" - returns students from either grade
- Using NOT: Find students who are "NOT from Johannesburg" - returns all students except those from Johannesburg
Query criteria for precise searches
To make queries more specific, you can use various criteria to philtre your results:
| Criteria | Description | Example |
|---|---|---|
| Like | Finds exact matches for the specified value | Like "Pretoria" |
| Not (<>) | Excludes records with the specified value | Not "Johannesburg" |
| Between | Finds values within a specified range | Between 1 AND 100 |
| < | Finds values smaller than the specified amount | <100 |
| > | Finds values larger than the specified amount | >100 |
| <= | Finds values less than or equal to the specified amount | <=100 |
| >= | Finds values greater than or equal to the specified amount | >=100 |
Reports for presenting information
The final step in most database workflows involves presenting your data in an easily readable format. Reports take the information from your tables or queries and format it professionally for printing or sharing. This might include student performance summaries, customer lists, or any other data presentation that stakeholders need to review.
Key Points to Remember:
- Databases are digital filing systems that organise information more efficiently than traditional paper-based methods
- Quality data must be up-to-date, complete, and relevant to serve its intended purpose effectively
- Tables, forms, queries, and reports work together in a logical workflow to manage information from storage to presentation
- Data types ensure consistency by defining what kind of information each field can contain
- Queries use logical operators and criteria to help you find exactly the information you need from large datasets
- Primary keys uniquely identify each record and prevent duplicate entries in your database