Computer Science (0478) | Databases

📚 IGCSE Computer Science (0478): Study Notes - Databases (Topic 9)

Welcome to the world of Databases! Don't worry, this isn't just about massive server rooms; it's about making information useful and organized. Think of a database as the ultimate digital filing system for massive amounts of data—like all the product details on Amazon or all the student records in your school.

In this chapter, we will learn how to design a simple database table, define the types of data it holds, and, most importantly, use a powerful language called SQL (Structured Query Language) to ask the database smart questions and retrieve exactly the information we need.

---

9.1 & 9.2 Database Structure and Data Types

Analogy: The Database as a Spreadsheet

The easiest way to understand a simple database is to think of it like a very organized spreadsheet (a single table).

Imagine you have a class register:

• Each column header (*like Name, Age, or Grade*) is a Field.
• Each row (*like all the information for one student*) is a Record.
• The entire sheet is the Table (which makes up the single-table database).

1. Defining a Single-Table Database

When we define a database structure, we are listing all the fields we need and planning the rules for the data that goes into them.

Key Database Components:

• Fields (Attributes): These are the individual pieces of data stored for each item or person.
  Example: StudentID, StudentName, DateOfBirth.
• Records (Tuples): A complete set of fields for a single item.
  Example: The entire row of data for one student.
• Validation: Setting rules to ensure data is sensible and accurate when it is entered.
  Example: Making sure an Age field only accepts numbers between 5 and 18.

2. Suggesting Suitable Basic Data Types

Every field must have a data type defined. This tells the database what kind of information to expect, which helps save space and ensures data integrity (correctness).

Here are the fundamental data types you need to know:

Data Type	What it stores	Example Use
Integer	Whole numbers (no decimal places).	Age, NumberOfItems, ProductCode.
Real	Numbers that can contain fractional parts (decimals).	Price, Height, ScoreAverage.
Text / Alphanumeric	Any combination of letters, numbers, or symbols (including spaces). This is usually for longer entries.	Names, Addresses, ProductDescription.
Character	A single letter, number, or symbol.	Gender ('M' or 'F'), Grade ('A', 'B', 'C').
Boolean	A logical value that can only be TRUE or FALSE (sometimes stored as 1 or 0, or Yes or No).	IsPaid, IsActiveStudent.
Date/Time	Dates and/or times stored in a specific format.	DateOfRegistration, TimeOfOrder.

Quick Review: Choosing the right data type is crucial. If you try to put the text "Expensive" into a Real field, the database will stop you!

---

9.3 The Primary Key

Imagine two students in your school happen to have the exact same name, *Maria Khan*. How does the school computer system know which Maria Khan is which?

It uses a Primary Key!

Purpose of a Primary Key

A Primary Key is a field (or sometimes a combination of fields) that uniquely identifies every single record in the table.

• Uniqueness: No two records can have the same primary key value.
• Not Null: The primary key field cannot be left empty.

Analogy: Your passport number or student ID is your primary key in the real world—it is unique to you, even if you share a name with someone else.

Identifying a Suitable Primary Key

When asked to suggest a primary key, look for fields that are guaranteed to be unique and required for every record.

Good Primary Key Candidates:

• ID Numbers (*CustomerID, OrderID, ISBN*). These are usually generated by the system specifically to be unique.

Bad Primary Key Candidates (Why?):

• Name: Not unique (two people can have the same name).
• Address: Not unique (two people can live in the same house) and too long.
• Age: Definitely not unique.

Key Takeaway: The primary key is the digital fingerprint for a record. It must be unique and always present.

---

9.4 Structured Query Language (SQL)

SQL is the language we use to communicate with and manipulate relational databases. You need to know how to read, understand, and complete basic SQL queries for a single table.

Don't worry about complex programming—SQL uses simple, clear English keywords!

The Basic SQL Query Structure

Almost every query follows this structure:

SELECT [What data do I want to see?]
FROM [Which table is the data stored in?]
WHERE [What condition must the records meet?]

1. Retrieving Data (SELECT and FROM)

The SELECT and FROM clauses are essential for every query.

• SELECT *: Means "Select ALL fields."
• SELECT Field1, Field2: Means "Select only these specific fields."
• FROM TableName: Specifies the table containing the data.

Example: Show all data from the 'Students' table.
SELECT *
FROM Students;

Example: Show only the Name and Age fields from the 'Students' table.
SELECT Name, Age
FROM Students;

2. Filtering Records (WHERE)

The WHERE clause is used to filter (limit) the records returned based on specific criteria or conditions.

Relational Operators Used in WHERE:

• = (Equal to)
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)
• <> (Not equal to)

Example: Find all students who are 16 years old.
SELECT *
FROM Students
WHERE Age = 16;

Example: Find all products with a price greater than 50.00. (Note: Text must be enclosed in single quotes, ' ').
SELECT ProductName
FROM Products
WHERE Price > 50.00;

3. Combining Conditions (AND, OR)

We use AND or OR to link multiple conditions in the WHERE clause.

• AND: *Both* conditions must be TRUE for the record to be included.
• OR: *At least one* condition must be TRUE for the record to be included.

Example (AND): Find students who are 16 AND who live in London.
SELECT Name
FROM Students
WHERE Age = 16 AND City = 'London';

Example (OR): Find students who are either 15 OR 17 years old.
SELECT Name
FROM Students
WHERE Age = 15 OR Age = 17;

4. Sorting Results (ORDER BY)

The ORDER BY clause sorts the output based on one or more fields.

• ASC (Ascending): Sorts A to Z or 1 to 10 (Default sorting order).
• DESC (Descending): Sorts Z to A or 10 to 1.

Example: List all student names, ordered alphabetically (A-Z).
SELECT Name
FROM Students
ORDER BY Name ASC;

Example: List products from most expensive to least expensive.
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

5. Aggregate Functions (SUM, COUNT)

These functions perform calculations on sets of records and return a single summary value.

• COUNT(Field) or COUNT(*): Calculates the number of records that match the condition.
• SUM(Field): Calculates the total value of a numerical field for the matching records.

Example (COUNT): How many records are in the Students table?
SELECT COUNT(*)
FROM Students;

Example (SUM): What is the total cost of all items in stock?
SELECT SUM(StockValue)
FROM Products;

★ Identifying the Output (Crucial Exam Skill)

In the exam, you must be able to look at a database table and an SQL query, and then accurately state exactly what records and fields the query will output.

Step-by-step:

1. Check the FROM clause: What table are you looking at? (Usually given).
2. Apply the WHERE clause: Go through the table row by row. Which records meet the condition(s)? (Eliminate all others).
3. Apply the SELECT clause: Of the remaining records, which fields are you asked to display? (Ignore all others).
4. Apply the ORDER BY clause: If present, reorder the final output list as required (ASC or DESC).

Did You Know? Google, Facebook, and nearly every major digital company rely heavily on complex databases and fast SQL queries to manage your data!