Welcome to Database Applications!
Have you ever wondered how Spotify instantly finds your favourite song among millions of tracks, or how your school keeps track of thousands of attendance marks, grades, and emergency contacts? The answer is simple: Databases!
In this chapter for CCEA GCSE Digital Technology (Unit 1), we will explore what databases are, how they work behind the scenes, and how they store and organise data safely and accurately. Don't worry if this seems a bit technical at first—we will break down every single concept into small, easy-to-digest pieces with real-world examples!
1. What is a Database?
A database is an organised collection of related data stored electronically on a computer system. Because the data is structured, computers can search, filter, update, and retrieve information in a fraction of a second.
Flat File vs. Relational Databases
There are two main types of databases you need to know for your exam:
1. Flat File Database:
A flat file database stores all its data in a single table or file (think of a simple spreadsheet in Microsoft Excel).
Analogy: Imagine writing everyone's contact details, orders, and addresses on one giant sheet of paper.
Problems with Flat Files:
• Data Redundancy: Information (like a customer's address) has to be retyped over and over every time they make an order.
• Data Inconsistency: If a customer changes their phone number, you might update it in one row but forget another, leading to conflicting records.
• Storage Waste: Repeating data wastes valuable storage space.
2. Relational Database:
A relational database stores data across multiple linked tables. Relationships are created between the tables using special matching fields.
Analogy: Having one list for Customers and another list for Orders, then connecting them using a shared ID number.
Benefits: Eliminates duplicate data, keeps data consistent, saves space, and improves data security.
Key Takeaway: Flat file databases store everything in one single table, causing duplication and errors. Relational databases use multiple linked tables to keep data tidy, accurate, and efficient.
2. The Anatomy of a Database Table
To understand databases, you need to master the basic building blocks. Let's look at how data is structured inside a table.
Tables, Records, and Fields
• Table (or Entity): A collection of data organised into rows and columns about a single subject (for example, a Student table or a Product table).
• Record (or Row / Tuple): All the details about one single item or person across the table. For example, all the details about John Smith (his ID, Name, Date of Birth, and Class) make up one record.
• Field (or Column / Attribute): A single piece of information within a record. For example, Surname or Postcode is a field.
Memory Trick: Fields go From top to bottom (columns), Records go Right across (rows)!
Standard Data Types
Every field must be assigned a specific data type so the computer knows what kind of values can be stored in it:
• Text / Alphanumeric: Stores words, letters, symbols, and numbers that are not used in calculations (e.g., Names, Postcodes like BT48 7NN, Telephone numbers).
• Number / Integer / Real: Stores numbers used for mathematical calculations or quantities (e.g., Age: 15, Quantity in stock: 42).
• Date / Time: Stores calendar dates and times in standard formats (e.g., 14/06/2008, 09:30).
• Currency: Stores monetary values and formats them with currency symbols (e.g., £19.99).
• Boolean / Yes/No: Stores only one of two possible values (e.g., True/False, Yes/No, Paid/Unpaid).
Exam Tip: Why are phone numbers stored as Text instead of Number? Because they start with a leading zero (e.g., 07123...) which a number field would automatically delete, and we never do math on phone numbers!
Key Takeaway: Tables contain records (rows for individuals) and fields (columns for specific details). Each field must have a correct data type.
3. Keys and Relationships
How do tables talk to each other without mixing up data? They use Keys!
Types of Keys
• Primary Key: A unique identifier for every record in a table. No two records can ever have the same primary key value (e.g., StudentID, National Insurance Number, Passport Number).
• Foreign Key: A primary key from one table that is placed into another table to link the two tables together.
• Composite Key: A primary key made by combining two or more fields together when no single field is unique on its own.
Types of Relationships
When connecting tables using Primary and Foreign keys, we create relationships:
• One-to-One (1:1): One record in Table A is linked to exactly one record in Table B (e.g., Each Country has exactly one Capital City).
• One-to-Many (1:M): One record in Table A can be linked to many records in Table B (e.g., One Customer can place many Orders). This is the most common relationship in relational databases!
• Many-to-Many (M:N): Many records in Table A link to many records in Table B (e.g., Many Students take many Subjects). In a relational database, this is usually broken down into two One-to-Many relationships using a link table.
Key Takeaway: A Primary Key uniquely identifies a record. A Foreign Key links tables together by referring to a Primary Key in another table.
4. Database Features: Forms, Queries, and Reports
Databases provide built-in tools that allow users to interact with, search, and present stored data easily.
1. Forms (Data Input)
A Form provides an attractive, user-friendly graphical interface on screen to enter, edit, or view records.
• Makes data entry faster and easier for non-technical users.
• Can include buttons, drop-down menus, and checkboxes to reduce typing mistakes.
• Hides the complex underlying tables from the user.
2. Queries (Searching & Filtering)
A Query is a question asked of the database to find specific records that meet certain rules (called criteria).
We use comparison operators and logical operators to set query criteria:
• = (Equal to)
• > (Greater than) and < (Less than)
• >= (Greater than or equal to) and <= (Less than or equal to)
• <> or != (Not equal to)
• AND: Returns records where all conditions are true (e.g., Town = 'Belfast' AND Age > 16).
• OR: Returns records where at least one condition is true (e.g., Grade = 'A*' OR Grade = 'A').
• NOT: Excludes specific values (e.g., NOT Town = 'Derry').
3. Reports (Data Output)
A Report is formatted output designed to present data from queries or tables in an easy-to-read, professional layout suitable for printing or sharing.
• Can group data logically (e.g., grouping sales by department).
• Can calculate totals, averages, and counts (e.g., Total Sales Revenue).
• Includes professional headers, footers, page numbers, and logos.
Key Takeaway: Forms are for putting data in, Queries are for searching data, and Reports are for printing/presenting data out.
5. Ensuring Data Quality: Validation vs. Verification
Computers follow the rule "Garbage In, Garbage Out" (GIGO): if you enter incorrect data, you will get incorrect results! Databases use validation and verification to keep data accurate.
Data Validation
Validation is an automatic check performed by the computer software to ensure that data entered is reasonable, sensible, and acceptable according to set rules.
Common Validation Checks:
• Presence Check: Ensures a required field cannot be left blank (e.g., Surname must be entered).
• Range Check: Ensures a number or date falls between specific minimum and maximum limits (e.g., Exam mark must be between \(0\) and \(100\)).
• Length Check: Checks that data is not too short or too long (e.g., A UK mobile number must be exactly 11 digits).
• Type / Character Check: Ensures only the correct type of characters are typed (e.g., Age contains only digits, Name contains no numbers).
• Format Check (Picture Check): Ensures data matches a set pattern (e.g., Postcode pattern: LLNN NLL, National Insurance: LL NN NN NN L).
• Lookup Check: Gives a list of allowed options from a drop-down list to avoid typos (e.g., choosing a Title from Mr, Mrs, Miss, Ms, Dr).
Data Verification
Verification checks whether data entered matches the original source document accurately.
Two Main Verification Methods:
• Double Entry: The same data is entered twice by two different people (or the same person), and the computer compares both entries to check for mismatches (e.g., typing a new password twice).
• Visual / Proofreading Check: A human user reads the entered data on the screen and compares it directly against the original paper document.
Crucial Difference: Validation checks if data is allowable (e.g., Age = 25 is sensible). Verification checks if data is correct (e.g., did the applicant actually write 25 or 52 on their paper form?).
Key Takeaway: Validation is automatic software rule-checking; Verification is checking against the original source for human copying mistakes.
6. Summary of Key Advantages of Relational Databases
When answering exam questions on why organisations use relational databases instead of paper or flat files, remember these core benefits:
• Reduced Data Redundancy: Data is stored once, preventing duplicate records.
• Improved Data Consistency / Integrity: Changing an address once updates it across the entire system automatically.
• Better Data Security: Permissions can be set so different users only see specific tables (e.g., a receptionist sees student contacts, but not confidential medical notes).
• Fast & Complex Searching: Powerful queries can extract precise information from millions of records in seconds.
• Simultaneous Multi-User Access: Multiple employees can access and update records at the same time without conflicts.
Quick Review Checklist
Before sitting your exam, make sure you can:
• Define flat file and relational databases.
• Identify records, fields, and correct data types.
• Explain the role of Primary, Foreign, and Composite keys.
• Describe the purpose of Forms, Queries, and Reports.
• Distinguish clearly between Validation and Verification.
• Name at least four validation checks with examples.