Unit 1: Software Applications for Business – Database Software
Welcome to your study notes for Database Software! In your CCEA GCSE Business and Communication Systems course, Unit 1 is worth a massive 40% of your final GCSE mark and is tested in a practical, computer-based exam. You will be using database software (such as Microsoft Access) to organize, search, calculate, and present business information.
Don't worry if databases seem a bit confusing at first. Think of a database simply as a powerful, super-fast digital filing cabinet that helps businesses find any piece of information in seconds. Let's break it down step-by-step!
---1. Database Building Blocks (Architecture & Core Concepts)
To understand how a database works, we start with four fundamental building blocks: Tables, Records, Fields, and Keys.
Tables, Fields, and Records
Imagine your school wants to store information about every student:
• Database: The entire organized collection of structured data held on the computer system.
• Table (Entity / File): A grid of rows and columns storing related information about one specific business topic (for example, a Customers table, a Products table, or an Orders table).
• Field (Attribute / Column): A single category or individual item of data within a table. Examples include Surname, DateOfBirth, UnitPrice, or StockQuantity.
• Record (Row / Tuple): A complete horizontal row containing all the field details for one single person, transaction, or item. For instance, one record might hold your specific Student ID, First Name, Surname, and Date of Birth.
Primary Key vs Foreign Key
How does a database keep track of thousands of records without getting confused?
• Primary Key (Key Field): A unique field that identifies each record in a table. No two records can ever have the same primary key value. Common examples include CustomerID, ProductCode, or OrderID. This prevents duplicate entries.
• Foreign Key: A field in one table that links directly to the primary key of another table. This creates a link (relationship) between different tables in a relational database.
Memory Aid: The Filing Cabinet Analogy
• The whole Filing Cabinet = Database
• One Drawer = Table
• One Folder inside the drawer = Record
• Each Line of Information inside the folder = Field
• The Unique Reference Number on the tab = Primary Key
Common Pitfall Alert!
Examiners frequently report that students mix up Fields and Records in written questions. Remember: Fields are the vertical columns (individual data categories), while Records are the horizontal rows (the full set of data for one item).
Key Takeaway: A database is made of tables, which contain records (rows), made up of individual fields (columns), identified uniquely by a primary key.
---2. Choosing Data Types and Field Properties
When setting up fields in software like Microsoft Access, you must tell the computer what type of data will be entered. Choosing the correct Data Type ensures data is stored efficiently and correctly formatted.
Standard Data Types
• Short Text (Text): Stores alphanumeric data (letters, symbols, and numbers that do not require mathematical calculations). Used for names, addresses, postcodes, and telephone numbers.
• Number: Stores numeric values used for mathematical calculations (e.g., QuantityOrdered, HoursWorked). Sub-types include Integer, Long Integer, Single, and Double.
• Currency: Stores financial data. In the UK, this automatically formats numbers with a pound sign (\(£\)) and to 2 decimal places (e.g., \(£14.50\)).
• Date/Time: Stores calendar dates and specific timestamps (e.g., DD/MM/YYYY or Short Date format).
• Yes/No (Boolean): Stores a choice between two states, such as Yes/No, True/False, or On/Off (e.g., Paid, Dispatched, NewsletterSubscriber).
• AutoNumber: Automatically generates a unique number sequentially or randomly whenever a new record is created. It is often used as the default primary key.
Did You Know? Why is a Telephone Number stored as Short Text?
You never add or multiply telephone numbers! If you set a telephone number as a Number data type, the software will delete the leading zero (e.g., \(02890\dots\) becomes \(2890\dots\)). Storing it as Short Text keeps the zero intact.
Validation and Field Properties
Validation checks that data entered into the database is reasonable, sensible, and within acceptable rules before the computer accepts it.
• Field Size: Restricts the maximum number of characters allowed (e.g., setting a Postcode field to a maximum of 8 characters).
• Required (Presence Check): Ensures that a field cannot be left blank. Setting Required = Yes prevents missing data.
• Default Value: Automatically fills in a standard value if nothing is typed (e.g., setting the default date to today's date using \(=\text{Date()}\)).
• Validation Rule & Validation Text: A logical condition that limits what can be typed into a field, paired with a custom error message that pops up if someone breaks the rule.
Examples:
- \(\ge 0\) (Value must be zero or positive)
- \(\text{Between } 1 \text{ And } 100\) (Value must be in the range \(1\) to \(100\))
- \(< \text{Date()}\) (Date entered must be in the past)
Key Takeaway: Selecting the correct data type prevents system errors, and applying validation rules ensures your database maintains high data integrity.
---3. Searching, Sorting, and Querying Data
Once data is in the database, businesses need to search through it, sort it, and ask specific questions. This is done using Sorting and Queries.
Sorting Records
• Ascending Order: Arranges data from A to Z, lowest number to highest number (\(0 \to 9\)), or earliest date to latest date.
• Descending Order: Arranges data from Z to A, highest number to lowest number (\(9 \to 0\)), or latest date to earliest date.
Designing Queries (Extracting Specific Subsets)
A Query is a search that extracts only the records and fields that meet specific criteria defined by the user.
1. Relational Comparison Operators
• \(=\) (Equal to)
• \(>\) (Greater than)
• \(<\) (Less than)
• \(\ge\) (Greater than or equal to)
• \(\le\) (Less than or equal to)
• \(<>\) (Not equal to)
2. Logical Operators
• AND: Both criteria must be true for a record to show (e.g., Town = "Belfast" AND Age \(\ge 18\)). In Query Design, place criteria on the same row.
• OR: At least one of the criteria must be true (e.g., Town = "Belfast" OR Town = "Lisburn"). In Query Design, place criteria on different rows.
• NOT: Excludes specific values (e.g., NOT "Dispatched").
3. Wildcards
Wildcards allow you to search when you only know part of a word or code:
• \(*\) (Asterisk): Matches any number of characters. For example, typing Like "B*" finds all surnames beginning with the letter B (such as Brown, Bell, or Black).
• \(?\) (Question Mark): Matches a single character.
4. Calculated Fields in Queries
You can create a new temporary field in a query that calculates a result using data from existing fields.
• Expression Syntax: Always write the new field name, followed by a colon, and place existing field names inside square brackets \([\dots]\).
• Example 1: \(\text{Total: } [\text{Price}] * [\text{Quantity}]\)
• Example 2: \(\text{VAT: } [\text{NetPrice}] * 0.2\)
Common Pitfall Alert!
Never use round brackets \(( )\) or curly brackets \(\{ \}\) when referencing field names in Access expressions. You must use square brackets \([ ]\). Also, remember to set the Format property of calculated financial fields to Currency (2 decimal places) so numbers don't show up unrounded!
Key Takeaway: Queries allow businesses to filter records using criteria, wildcards, logical operators, and custom calculated fields.
---4. Database Outputs: Forms and Reports
Raw tables and query grids are hard for most people to read. Databases provide two main user-facing tools to enter and present information: Forms and Reports.
Forms (Data Input & Viewing)
• A Form is an interactive screen interface designed for entering, editing, and viewing individual records one by one.
• Business Benefits: Makes data entry faster and simpler for non-technical staff, reduces data entry mistakes, and hides complex background database tables.
Reports (Structured Output & Printing)
A Report is a professional, formatted document designed for viewing on-screen, printing, or sending to management.
To score top marks in your practical exam, reports must include:
• Professional Layout: Clear main title, column headings, candidate name, date, and page numbers in headers or footers.
• Grouping: Organizing records under shared headings (for example, grouping all products by Category or grouping employees by Department).
• Sorting: Ordering records inside the report (e.g., sorting customers alphabetically by surname).
• Summary Calculations: Adding summary statistics at the end of groups or at the very end of the report (Report Footer) using functions:
1. \(= \text{Sum}([TotalCost])\) — Adds together all numerical values in that field.
2. \(= \text{Avg}([Salary])\) — Calculates the arithmetic mean average.
3. \(= \text{Count}([CustomerID])\) — Counts the total number of records.
Common Pitfall Alert: Truncation!
When you generate a report, check your column widths carefully! If a column is too narrow, text gets cut off (truncated) and numbers appear as ###. Expand the text boxes in Design View or Layout View so every letter and number is clearly readable.
Key Takeaway: Forms are designed for entering data record-by-record, while Reports are formatted documents used to summarize, calculate, and present data for management.
---5. Quick Practical Exam Checklist
Before finishing any database task in your Unit 1 exam, double check these critical points:
1. Field Types: Are telephone numbers and postcodes set to Short Text? Is price set to Currency (\(£\))?
2. Validation: Did you type both the Validation Rule and the user-friendly Validation Text prompt?
3. Calculated Fields: Did you use square brackets like \([\text{Field1}] * [\text{Field2}]\) and format the result as Currency if required?
4. Sort Order: Did you check if the exam brief asked for Ascending or Descending order?
5. No Truncation: Are all field headings and data values fully visible without any ### display errors?
6. Header/Footer Details: Have you included your Name, Candidate Number, and Center Number on the final report?