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

• Design and query relational databases.
• Use advanced spreadsheet functions for data analysis.
• Develop basic websites using HTML and CSS.
• Apply principles of information management.
• Solve complex CAT-related problems.

Lesson summary

This week focuses on consolidating key concepts across multiple areas of the Grade 12 Computer Applications Technology (CAT) curriculum, with a specific emphasis on areas often encountered in examinations. We will be revisiting database management, spreadsheet skills (advanced functions and data analysis), and website development basics. Proficiency in these areas is critical not only for examination success but also for practical application in future studies and careers. Understanding databases enables efficient data management in various sectors, from healthcare (managing patient records) to business (tracking sales and inventory).

Lesson notes

2.1 Database Management: Relational Databases and SQL Relational Databases: A relational database is a database structured to recognize relationships between stored items of information. It is organized into tables, which contain rows (records) and columns (fields). Each table represents a specific entity (e.g., Students, Courses), and relationships between tables are established through primary keys and foreign keys.

Normalization: Database normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Common normalization forms include 1NF, 2NF, and 3NF. 1NF (First Normal Form): Eliminate repeating groups of data within a table. Each column should contain atomic values (indivisible units of data). 2NF (Second Normal Form): Be in 1NF and eliminate redundant data that depends on only part of the primary key. This applies when the primary key is a composite key (made up of multiple columns). 3NF (Third Normal Form): Be in 2NF and eliminate columns that are not dependent on the primary key. Transitive dependency (A -> B -> C, where A is the primary key) should be removed.

SQL (Structured Query Language): SQL is the standard language for interacting with relational databases.

Common SQL commands include: SELECT: Retrieves data from a table. `SELECT FROM Students;` (Selects all columns from the Students table) `SELECT StudentID, Name FROM Students WHERE City = 'Cape Town';` (Selects StudentID and Name from Students who live in Cape Town)

INSERT: Adds new data into a table. `INSERT INTO Students (StudentID, Name, City) VALUES (123, 'Zanele Dlamini', 'Durban');` UPDATE: Modifies existing data in a table. `UPDATE Students SET City = 'Johannesburg' WHERE StudentID = 123;` DELETE: Removes data from a table. `DELETE FROM Students WHERE StudentID = 123;` CREATE TABLE: Creates a new table in the database. `CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(255), City VARCHAR(255));` ALTER TABLE: Modifies the structure of an existing table. `ALTER TABLE Students ADD COLUMN Age INT;`

Worked example

Consider a database for a school. We need to store information about students and courses.

Tables:

`Students`: `StudentID` (Primary Key), `Name`, `Surname`, `DateOfBirth`, `Address`, `ContactNumber`

`Courses`: `CourseID` (Primary Key), `CourseName`, `Credits`

`Enrollments`: `EnrollmentID` (Primary Key), `StudentID` (Foreign Key referencing Students), `CourseID` (Foreign Key referencing Courses), `EnrollmentDate`, `Grade`

SQL Queries:

Retrieve all students enrolled in a specific course (e.g., 'Mathematics'):

```sql

SELECT S.Name, S.Surname

FROM Students AS S

INNER JOIN Enrollments AS E ON S.StudentID = E.StudentID

INNER JOIN Courses AS C ON E.CourseID = C.CourseID

WHERE C.CourseName = 'Mathematics';

```

Explanation: This query joins the `Students`, `Enrollments`, and `Courses` tables based on their respective keys. It then filters the results to show only students enrolled in the 'Mathematics' course.

Calculate the average grade for a specific course (e.g., 'Mathematics'):

```sql

SELECT AVG(E.Grade) AS AverageGrade

FROM Enrollments AS E

INNER JOIN Courses AS C ON E.CourseID = C.CourseID

WHERE C.CourseName = 'Mathematics';

```