Lesson Notes By Weeks and Term v5 - Grade 11

Lesson Video

This page supports the lesson note with a companion video and a short classroom-ready summary.

For class groups and homework, share this lesson page so learners also get the summary, objectives, and full lesson context.

Performance objectives

• Design a simple database with tables, fields, and keys.
• Write SQL queries to filter data using a WHERE clause.
• Construct SQL queries to sort data using ORDER BY.
• Use aggregate functions like COUNT and SUM to get summary data.
• Understand the importance of data integrity and validation.

Lesson summary

Databases are at the heart of almost everything we do online and increasingly offline. From managing stock levels in a local spaza shop to tracking learner performance at school, databases provide a structured way to store, organize, and retrieve information efficiently. This week, we're diving into the basics of database design and querying. This is vital because understanding databases empowers you to develop solutions that can solve real-world problems in your communities and beyond. Imagine being able to build a system for a local clinic to manage patient records, reducing errors and improving healthcare access. That's the power you gain by understanding databases.

Lesson notes

Let's break down the core concepts of database design and querying. We'll focus on relational databases, the most common type used today. 2.1 Relational Database Fundamentals: A relational database organizes data into tables. Each table represents a specific entity, such as Learners, Subjects, or Teachers.

Table: A collection of related data organized in rows and columns. Think of it like a spreadsheet.

Record (Row): Represents a single instance of the entity. For example, one row in the Learners table would represent one specific learner.

Field (Column): Represents an attribute of the entity. For example, LearnerID, FirstName, LastName, and DateOfBirth could be fields in the Learners table.

Primary Key: A unique identifier for each record in a table. No two records can have the same primary key value. Examples include LearnerID, ProductID, or InvoiceNumber.

Foreign Key: A field in one table that refers to the primary key of another table. This establishes a relationship between the two tables.

Example: Imagine a database for a school.

Table: Learners Fields: LearnerID (Primary Key), FirstName, LastName, Grade, DateOfBirth Table: Subjects Fields: SubjectID (Primary Key), SubjectName, Credits Table: LearnerSubjects (A linking table to show which learners are taking which subjects)

Fields: LearnerID (Foreign Key referencing Learners.LearnerID), SubjectID (Foreign Key referencing Subjects.SubjectID), Mark 2.2 Database Design Principles: Normalization: Organizing data to reduce redundancy and improve data integrity. This is a complex topic, but the basic idea is to avoid storing the same information in multiple places. For example, instead of storing a learner's address in multiple tables, store it in a separate Addresses table and link it using a foreign key.

Data Types: Choosing the correct data type for each field is crucial.

Common data types include: Integer (INT): Whole numbers (e.g., 1, 10, -5).

Decimal (DECIMAL/NUMERIC): Numbers with decimal points (e.g., 3.14, 2.50). Specify the precision and scale (e.g., DECIMAL(5,2) allows 5 digits total, with 2 after the decimal).

Text (VARCHAR/TEXT): Strings of characters (e.g., "John", "Cape Town"). VARCHAR requires you to specify the maximum length (e.g., VARCHAR(50)).

Date (DATE): Dates (e.g., 2023-10-27).

Boolean (BOOLEAN): True/False values. 2.3 SQL Queries: Retrieving Data SQL (Structured Query Language) is the language used to interact with databases. The most common command is `SELECT`, used to retrieve data.

Basic SELECT statement: ```sql SELECT field1, field2, ... FROM table_name; ``` This retrieves the specified fields from the specified table. `SELECT *` retrieves all fields.

Example: ```sql SELECT FirstName, LastName FROM Learners; ``` This would retrieve the first name and last name of all learners in the Learners table. 2.4 Filtering Data with WHERE: The `WHERE` clause allows you to filter the data based on specific conditions. ```sql SELECT field1, field2, ... FROM table_name WHERE condition; ```

Example: ```sql SELECT FirstName, LastName FROM Learners WHERE Grade = 11; ``` This would retrieve the first name and last name of all learners in grade

1

1. Common comparison operators used in the `WHERE` clause: `=`: Equal to `>`: Greater than ` =`: Greater than or equal to ` `: Not equal to `LIKE`: Used for pattern matching (e.g., `WHERE FirstName LIKE 'J%'` finds all first names starting with "J"). `BETWEEN`: Used to specify a range of values (e.g., `WHERE Mark BETWEEN 70 AND 100`). `IN`: Used to specify a list of values (e.g., `WHERE Grade IN (10, 11, 12)`). 2.5 Sorting Data with ORDER BY: The `ORDER BY` clause allows you to sort the retrieved data based on one or more fields. ```sql SELECT field1, field2, ... FROM table_name ORDER BY field_name [ASC | DESC]; ``` `ASC` specifies ascending order (default), and `DESC` specifies descending order.

Example: ```sql SELECT FirstName, LastName, Grade FROM Learners ORDER BY Grade DESC, LastName ASC; ``` This would retrieve the first name, last name, and grade of all learners, sorted first by grade in descending order (highest grade first) and then by last name in ascending order within each grade. 2.6 Aggregate Functions: Aggregate functions calculate summary statistics on a set of values. `COUNT()`: Counts the number of rows. `SUM()`: Calculates the sum of values. `AVG()`: Calculates the average of values. `MIN()`: Finds the minimum value. `MAX()`: Finds the maximum value.

Example: ```sql SELECT COUNT(*) AS TotalLearners FROM Learners; ``` This would count the total number of learners in the Learners table and display the result as "TotalLearners". ```sql SELECT AVG(Mark) AS AverageMark FROM LearnerSubjects WHERE SubjectID = 1; -- Assuming SubjectID 1 represents Mathematics ``` This would calculate the average mark for all learners in the Mathematics subject. 2.7 Data Integrity and Validation Rules: Data integrity ensures that the data in the database is accurate, consistent, and reliable.