Databases

Paper 2

Database Fundamentals

A database is a structured collection of data stored electronically. In Cambridge Computer Science, we focus on relational databases, where data is organized into tables (also called relations).

Building on the concept of file systems, a relational database allows for efficient querying and management of large datasets without redundancy.

A table consists of:

Fields (Columns): Vertical categories that define the type of data stored (e.g., Name, Age). Each field has a specific data type.
Records (Rows): Horizontal entries that contain the actual data for a single entity. Each record represents one item in the dataset.

Term	Definition	Analogy
Table	A collection of related data held in a structured format within a database.	A single sheet in a ledger.
Field (Column)	A specific attribute or category of data within a table. All fields in a column must have the same data type.	A column header like 'Price' or 'Date'.
Record (Row)	A complete set of related data items for one specific entity. Each record has a unique identifier.	One line entry in the ledger.
Primary Key	A field (or combination of fields) that uniquely identifies each record in a table. It cannot be null or duplicate.	A unique ID number for each entry.

Data Types define what kind of data can be stored in a field. Using the correct data type is crucial for storage efficiency and data integrity.

Data Type	Description	Example
Text / Alphanumeric	Stores characters, letters, and numbers. Cannot perform mathematical calculations on it.	'Smith', 'A123'
Integer	Stores whole numbers (no decimals).	25, -10
Real / Float	Stores decimal numbers (floating point).	98.6, 3.14
Boolean	Stores only two possible values: True/False or Yes/No.	TRUE, FALSE
Date/Time	Stores dates and/or times in a specific format.	2023-10-05

Note: Cambridge syllabus uses 'Text' rather than 'String'. Always use the term Text.

SQL (Structured Query Language)

SQL is the standard language used to communicate with relational databases. It allows users to retrieve, insert, update, and delete data.

The most common SQL command you will encounter in exams is SELECT, which retrieves data from a table.

SQL SELECT Statement Structure

The SELECT statement is used to query data. It has three main parts:

SELECT: Specifies which fields (columns) to retrieve.
FROM: Specifies the table name where the data is stored.
WHERE: (Optional) Specifies a condition to filter the records. Only records meeting this condition are returned.
ORDER BY: (Optional) Specifies how to sort the results.

Syntax Rules:

Field names and table names are not case-sensitive in standard SQL, but it is good practice to write them as they appear in the schema.
Commas separate multiple fields in the SELECT clause.
Semicolons (;) terminate the statement (though often omitted in exam answers unless specified).
Quotation marks (' ') are required for text strings in the WHERE clause.

Writing a SQL Statement

Scenario:
You have a table named Students with the following fields:

StudentID (Integer, Primary Key)
Name (Text)
Age (Integer)
Grade (Text)

Task 1: Retrieve all names and ages.

SELECT Name, Age FROM Students;

Explanation: We list the fields we want after SELECT, separated by a comma. We specify the table after FROM.

Task 2: Retrieve names of students aged over 16, sorted alphabetically.

SELECT Name FROM Students WHERE Age > 16 ORDER BY Name;

Explanation:

WHERE Age > 16 filters the records.
ORDER BY Name sorts the output. Default is ascending (A-Z). For descending, use DESC.

Task 3: Retrieve details for a specific student named 'Smith'.

SELECT * FROM Students WHERE Name = 'Smith';

Explanation:

* means 'all fields'.
'Smith' is in single quotes because it is text. Without quotes, the database might look for a column named Smith.

⚠︎ Common Errors in SQL and Database Concepts

1. Missing Quotation Marks for Text

Error: WHERE Name = Smith
Correct: WHERE Name = 'Smith'
Why: Without quotes, the database engine interprets 'Smith' as a column name or variable, not a text value. This causes a syntax error.

2. Confusing Fields and Records

Error: Stating a table has 10 records and 5 fields when it actually has 5 records and 10 fields.
Correct: Count the columns for fields and the rows (excluding the header) for records.

3. Incorrect Data Types

Error: Using 'String' or 'Float' as a data type name.
Correct: Use Text (not String) and Real (or Float, depending on specific syllabus version, but Text is critical). Do not use 'Integer' for decimal numbers.

4. Missing Commas in SELECT Clause

Error: SELECT Name Age FROM Students
Correct: SELECT Name, Age FROM Students
Why: SQL requires commas to separate multiple column names.

Examiner Tips for High Marks

Tip 1: Precision in WHERE Clauses
When writing a WHERE clause for text data, you must use single quotation marks around the search term. For example, WHERE Country = 'France'. If you omit the quotes, the database will not recognize 'France' as a literal string value, leading to an incorrect query or error. This is accepted because it demonstrates understanding of data typing in SQL syntax.

Tip 2: Handling Multiple Conditions
If a question asks for records that meet two criteria (e.g., Age > 18 AND Grade = 'A'), you must use the logical operator AND between the conditions. Do not write two separate WHERE clauses or use commas to separate conditions in the WHERE clause. The correct syntax is WHERE Age > 18 AND Grade = 'A'. This directly addresses the logical structure required for filtering data.

Tip 3: Output Formatting
When asked to provide the output of a SQL statement, ensure you:

List only the fields specified in the SELECT clause.
Maintain the correct order of columns.
Include all records that match the WHERE condition.
Do not include extra punctuation (like commas between rows) unless explicitly asked for a CSV format. Just list the data clearly.

Practice Questions

A table 'Books' has fields: BookID (Integer), Title (Text), Price (Real). Write a SQL statement to display the Title and Price of all books priced over 20.00.

SELECT Title, Price FROM Books WHERE Price > 20.00;

Identify the appropriate data type for the following fields in a 'Employees' table:

EmployeeID
DepartmentName
Salary
IsManager (Yes/No)

Integer
Text
Real
Boolean

Explain the purpose of a Primary Key in a database table.

A Primary Key uniquely identifies each record in a table. It ensures that no two records have the same value for this field, preventing duplication and allowing efficient data retrieval.

Given the table 'Students' with fields Name (Text) and Age (Integer), write a SQL statement to list all names sorted by age in descending order.

SELECT Name FROM Students ORDER BY Age DESC;