Lesson Notes By Weeks and Term v5 - Grade 12

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

• Demonstrate proficiency in Databases (SQL, Reports).
• Confidently apply advanced Spreadsheet functions.
• Effectively utilize advanced Word Processing features.
• Integrate different software applications.
• Apply critical thinking to solve problems.

Lesson summary

This week marks a crucial stage in your Grade 12 CAT journey: focused revision and examination preparation. We're moving from learning new material to consolidating our understanding and developing the skills needed to confidently tackle the final exam. CAT isn’t just about knowing software; it’s about applying your knowledge to solve real-world problems, which is a highly valuable skill in today’s digital South Africa. Whether you aspire to be a programmer, designer, entrepreneur, or any other profession, the skills you learn in CAT – problem-solving, critical thinking, and effective communication – will be essential.

Lesson notes

This week, we'll focus on the most challenging and often tested areas of the CAT curriculum.

This includes:

A. Database Management Systems (DBMS)

Relational Database Model: What: A way to structure data into tables (relations) with rows (records) and columns (fields). Think of it like an organised filing cabinet. Each table represents a specific entity (e.g., Students, Courses), and the relationships between tables are defined using keys.

Why: Ensures data integrity, reduces redundancy, and allows for efficient querying and reporting.

Key Concepts: Tables: Structures containing related data.

Fields (Columns): Attributes that describe each record (e.g., StudentID, Name, Surname).

Records (Rows): A collection of related fields representing a single entity instance (e.g., a specific student).

Primary Key: A unique identifier for each record in a table (e.g., StudentID). Cannot be null (empty).

Foreign Key: A field in one table that refers to the primary key of another table. Establishes relationships between tables (e.g., CourseID in the Enrollment table referencing the CourseID in the Courses table).

Relationships: Connections between tables (One-to-One, One-to-Many, Many-to-Many).

SQL (Structured Query Language): What: The standard language for interacting with databases. Used to retrieve, insert, update, and delete data.

Why: Allows you to manipulate and analyze data stored in the database.

Key Commands: `SELECT`: Retrieves data from one or more tables. `FROM`: Specifies the table(s) to retrieve data from. `WHERE`: Filters data based on specific conditions. `INSERT INTO`: Adds new records to a table. `UPDATE`: Modifies existing records in a table. `DELETE FROM`: Removes records from a table. `JOIN`: Combines data from multiple tables based on a related column. `ORDER BY`: Sorts the results. `GROUP BY`: Groups rows with the same value in one or more columns into a summary row. `HAVING`: Filters groups created by the `GROUP BY` clause.

Example (MS Access SQL): Imagine a database with `Students` (StudentID, Name, Surname) and `Courses` (CourseID, CourseName, Credits) tables. We also have an `Enrollment` table (EnrollmentID, StudentID, CourseID, DateEnrolled).

Scenario 1: Retrieve the names and surnames of all students enrolled in the course "Maths 101". ```sql SELECT s.Name, s.Surname FROM Students AS s INNER JOIN Enrollment AS e ON s.StudentID = e.StudentID INNER JOIN Courses AS c ON e.CourseID = c.CourseID WHERE c.CourseName = "Maths 101"; ``` Explanation: `SELECT s.Name, s.Surname`: Selects the `Name` and `Surname` columns from the `Students` table (aliased as `s`). `FROM Students AS s INNER JOIN Enrollment AS e ON s.StudentID = e.StudentID`: Joins the `Students` table with the `Enrollment` table based on the matching `StudentID`. The `INNER JOIN` ensures we only get students who are actually enrolled in a course. `INNER JOIN Courses AS c ON e.CourseID = c.CourseID`: Joins the `Enrollment` table with the `Courses` table based on matching `CourseID`. `WHERE c.CourseName = "Maths 101";`: Filters the results to include only students enrolled in courses where the `CourseName` is "Maths 101".

B. Advanced Spreadsheet Functions LOOKUP Functions (VLOOKUP, HLOOKUP, INDEX, MATCH): What: Used to find and retrieve data from tables based on a search value. VLOOKUP searches vertically, HLOOKUP searches horizontally. INDEX and MATCH provide more flexible lookup capabilities.

Why: Automates data retrieval, eliminates manual searching, and ensures accuracy.

Example (VLOOKUP): Suppose you have a spreadsheet with student IDs and their corresponding names in columns A and B respectively. You want to find the name of a student with ID 1234. ```excel =VLOOKUP(1234, A1:B100, 2, FALSE) ``` Explanation: `1234`: The lookup value (the student ID we're searching for). `A1:B100`: The range containing the student IDs and names. `2`: The column number in the range that contains the value you want to return (in this case, the name is in the second column). `FALSE`: Specifies an exact match. Using `TRUE` will return an approximate match (only appropriate if the lookup column is sorted). Statistical Functions (AVERAGE, MEDIAN, MODE, STDEV, COUNT, COUNTIF, SUMIF): What: Used to perform statistical calculations on data sets.

Why: Enables data analysis and interpretation.

Example (COUNTIF): You want to count the number of students who scored above 70% in a test. Column C contains the test scores. ```excel =COUNTIF(C1:C100, ">70%") ``` Explanation: `C1:C100`: The range containing the test scores. `">70%"`: The criteria. The `COUNTIF` function counts the number of cells in the range that meet this criteria (i.e., the score is greater than 70%).

Pivot Tables: What: A powerful tool for summarizing and analyzing large amounts of data. Allows you to quickly rearrange and filter data to identify trends and patterns.

Why: Simplifies data analysis and reporting.