IGCSE Computer Science
0478
Digitized Notes
9 Databases
1. Define a Single-Table Database from Given Data Storage Requirements
A single-table database is a database that stores all related information in one table. It is a
simplified version of a database where data about a particular entity is stored in one table rather
than multiple tables. Each record (or row) represents an individual entry, and each column
represents a specific attribute or field of the entity.
For example, a student database may store student information in a single table with columns
for student_id, name, age, class, and grade.
• Example Table: Student Table
student_id name age class grade
1 John 15 10 A
2 Mary 16 10 B
3 Alex 14 9 A
4 Sarah 15 10 C
In this table, each student’s information is stored as a single row.
2. Suggest Suitable Basic Data Types
When designing a database table, it is important to select appropriate data types for each field to
ensure data is stored efficiently and accurately. The choice of data type depends on the nature of
the data to be stored.
Common basic data types used in databases are:
• Integer (INT): Used for whole numbers.
o Example: age, student_id
• Varchar (VARCHAR): Used for text (string) data of varying lengths.
o Example: name, address
• Char (CHAR): Used for fixed-length text data.
o Example: gender (e.g., 'M' or 'F')
• Decimal (DECIMAL): Used for numbers with a fixed number of decimal places.
o Example: price, salary
• Date (DATE): Used for storing dates.
o Example: date_of_birth, enrollment_date
• Boolean (BOOL): Used for true/false or yes/no values.
o Example: is_active (whether the student is currently enrolled)
• Text (TEXT): Used for longer strings of text, such as descriptions or notes.
o Example: comments, bio
3. Understand the Purpose of a Primary Key and Identify a Suitable Primary Key for a
Given Database Table
A primary key is a unique identifier for each record in a database table. It ensures that each
record can be uniquely identified and prevents duplication. The primary key must satisfy the
following conditions:
• Uniqueness: No two rows can have the same primary key value.
• Not Null: A primary key field cannot be empty (null).
• Stable: The primary key value should not change over time.
A suitable primary key is often a field or combination of fields that uniquely identifies each
record in the table. Typically, this is the most unique field (e.g., an ID number or student
number).
• Example: In a student table, the student_id field could be a suitable primary key
because each student has a unique ID number.
• Primary Key Example: Student Table
student_id (PK) name age class grade
1 John 15 10 A
2 Mary 16 10 B
3 Alex 14 9 A
4 Sarah 15 10 C
In this example, the student_id field is the primary key because it uniquely identifies each
student.
4. Read, Understand, and Complete Structured Query Language (SQL) Scripts to Query
Data Stored in a Single Database Table
SQL (Structured Query Language) is used to interact with databases. It allows you to query
(retrieve) and manipulate data in a database.
Here are some common SQL commands for querying data from a single-table database:
• SELECT: Used to retrieve data from a table.
o Syntax:
sql
Copy code
SELECT column1, column2 FROM table_name;
o Example: Select all students' names and grades from the student table:
sql
Copy code
SELECT name, grade FROM student;
• WHERE: Used to filter data based on a condition.
o Syntax:
sql
Copy code
SELECT column1, column2 FROM table_name WHERE condition;
o Example: Select students who are in class 10:
sql
Copy code
SELECT name, class FROM student WHERE class = 10;
• ORDER BY: Used to sort the result set.
o Syntax:
sql
Copy code
SELECT column1, column2 FROM table_name ORDER BY column1
ASC|DESC;
o Example: Select students and order them by age:
sql
Copy code
SELECT name, age FROM student ORDER BY age DESC;
• INSERT INTO: Used to add new data into the table.
o Syntax:
sql
Copy code
INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);
o Example: Insert a new student record into the student table:
sql
Copy code
INSERT INTO student (student_id, name, age, class, grade)
VALUES (5, 'David', 14, 9, 'B');
• UPDATE: Used to modify existing data in the table.
o Syntax:
sql
Copy code
UPDATE table_name SET column1 = value1, column2 = value2 WHERE
condition;
o Example: Update the grade of student John:
sql
Copy code
UPDATE student SET grade = 'A+' WHERE student_id = 1;
• DELETE: Used to delete data from the table.
o Syntax:
sql
Copy code
DELETE FROM table_name WHERE condition;
o Example: Delete the record of the student with student_id = 4:
sql
Copy code
DELETE FROM student WHERE student_id = 4;
• Example SQL Query: Suppose we have the following student table:
student_id name age class grade
1 John 15 10 A
2 Mary 16 10 B
3 Alex 14 9 A
o To find all students in class 10 who have grade 'A':
sql
Copy code
SELECT name, grade FROM student WHERE class = 10 AND grade = 'A';
Summary:
• Single-table database: Stores all related data in one table.
• Data Types: Choose appropriate data types for each field (e.g., INT, VARCHAR, DATE).
• Primary Key: A unique identifier for each record in the table (e.g., student_id).
• SQL Queries:
o SELECT: Retrieve data.
o WHERE: Filter data based on conditions.
o ORDER BY: Sort data.
o INSERT INTO: Add new records.
o UPDATE: Modify existing records.
o DELETE: Remove records.