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

• Define a relational database and its key components.
• Explain the concept of data normalization.
• Write basic SQL SELECT statements.
• Use the WHERE clause to filter data.
• Use the ORDER BY clause to sort results.

Lesson summary

In today's digital age, data is everywhere. From the spaza shop down the street tracking stock to large corporations managing customer information, the ability to manage and analyze data is crucial. Understanding relational databases and the SQL language is a fundamental skill for anyone pursuing a career in IT or even simply wanting to understand how the world around them functions. Imagine a large hospital – patient records, doctor schedules, medication details, all meticulously organized in a relational database. Or consider the Department of Home Affairs managing citizen information. These are real-world examples highlighting the power and importance of efficient data management.

Lesson notes

What is a Relational Database? A relational database is a type of database that organizes data into one or more tables in which data types are related to each other. Tables are made up of rows and columns. Each row represents a record, and each column represents an attribute of that record. The relationship between tables is established through the use of primary and foreign keys. Think of it like a well-organized filing cabinet where each drawer is a table, each file is a row, and each label on the file represents a column.

Table: A collection of related data organized in rows and columns. For example, a "Students" table might store information about each student in a school.

Row (Record/Tuple): A single instance of data in a table. For example, one row in the "Students" table would represent one student.

Column (Attribute/Field): A characteristic or property of the data in a table. For example, "StudentID," "Name," "Surname," and "DateOfBirth" could be columns in the "Students" table.

Primary Key: A column (or set of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it cannot be null (empty).

Example: StudentID in the Students table.

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

Example: A "Class" table might have a "StudentID" column as a foreign key, referencing the "Students" table.

Data Types: Define the kind of data a column can hold (e.g., text, numbers, dates).

Common data types include: `INT` (Integer): Whole numbers. `VARCHAR(n)` (Variable Character): Text strings of up to n characters. `DATE`: Dates (e.g., YYYY-MM-DD). `BOOLEAN`: True/False values.

Example: A School Database Let's consider a simplified database for a school: Table: Students | StudentID (PK) | Name | Surname | DateOfBirth | Grade | | :------------- | :----- | :------ | :---------- | :---- | | 101 | Aisha | Dlamini | 2005-08-15 | 11 | | 102 | Sipho | Nkosi | 2006-03-22 | 10 | | 103 | Zanele | Mbeki | 2005-11-01 | 11 | Table: Subjects | SubjectID (PK) | SubjectName | | :------------- | :---------------- | | S101 | Mathematics | | S102 | Physical Sciences | | S103 | Information Tech | Table: StudentSubjects (Linking Table) | StudentID (FK) | SubjectID (FK) | Mark | | :------------- | :------------- | :--- | | 101 | S101 | 75 | | 101 | S103 | 82 | | 102 | S102 | 68 | | 103 | S101 | 90 | In this example: `StudentID` is the primary key in the `Students` table. `SubjectID` is the primary key in the `Subjects` table. `StudentID` and `SubjectID` are foreign keys in the `StudentSubjects` table, linking students to the subjects they take. Data Normalization Data normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing databases into two or more tables and defining relationships between the tables. This prevents data anomalies (inconsistencies) that can arise during data insertion, update, and deletion. Why is normalization important? Imagine if the `Subjects` table was combined with the `Students` table, and every student's record included the subject they were taking. If a subject name changed (e.g., "Information Technology" became "Computer Applications Technology"), you would have to update every student record taking that subject. With normalization, you only need to update the `Subjects` table once. SQL Basics SQL (Structured Query Language) is the standard language for interacting with relational databases. We use SQL to retrieve, insert, update, and delete data. For Week 1, we will focus on the `SELECT` statement, which is used to retrieve data. Basic `SELECT` Statement: ```sql SELECT column1, column2, ... FROM table_name; ``` This statement retrieves the specified columns from the specified table. If you want to retrieve all columns, you can use the asterisk (*): ```sql SELECT * FROM table_name; ```

Example: To retrieve the names and surnames of all students from the `Students` table: ```sql SELECT Name, Surname FROM Students; ``` This will return a result set containing only the `Name` and `Surname` columns for each student. `WHERE` Clause: The `WHERE` clause is used to filter the data based on specific conditions. ```sql SELECT column1, column2, ... FROM table_name WHERE condition; ``` The `condition` can involve comparison operators (e.g., `=`, `>`, ` =`, ` = 80; ```

Commentary: This query selects the `StudentID` and `Mark` columns from the `StudentSubjects` table. The `WHERE` clause uses the `>=` operator to filter for records where the `Mark` is 80 or higher.

Question 3: Write an SQL query to retrieve the `Name` and `Surname` from the `Students` table, ordered alphabetically by `Name`.

Solution: ```sql SELECT Name, Surname FROM Students ORDER BY Name ASC; ```

Commentary: This query retrieves the `Name` and `Surname` columns from the `Students` table.