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 and use primary and foreign keys.
• Write SQL statements using JOIN clauses.
• Filter and sort results using WHERE and ORDER BY.
• Describe one-to-one, one-to-many, and many-to-many relationships.
• Create basic queries to manage data (CRUD operations).

Lesson summary

This week, we delve deeper into relational databases and Structured Query Language (SQL), the language used to interact with them. Databases are fundamental to how information is stored, managed, and accessed in nearly every aspect of modern life, from banking systems to social media platforms and government services. Think about how Home Affairs manages ID numbers or how your school keeps track of your marks - all powered by databases. Understanding relational databases and SQL is not just a valuable IT skill; it's a crucial understanding of how information flows in the digital world.

Lesson notes

2.1 Primary Keys A primary key is a column or a set of columns in a database table that uniquely identifies each row in that table. It's like your ID number – no two people in South Africa have the same I

D. Uniqueness: Each value in the primary key column(s) must be unique.

Not Null: A primary key cannot contain null values (empty or missing values).

One per table: Each table can have only one primary key.

Example: Imagine a table called `Students` to store information about students in your class: | Column Name | Data Type | Constraints | |-------------|-----------|-------------| | `StudentID` | `INT` | `PRIMARY KEY, AUTO_INCREMENT` | | `FirstName` | `VARCHAR(50)` | `NOT NULL` | | `LastName` | `VARCHAR(50)` | `NOT NULL` | | `Grade` | `INT` | | Here, `StudentID` is the primary key. `AUTO_INCREMENT` means the database will automatically generate a unique number for each new student.

SQL Syntax (using MySQL as an example): ```sql CREATE TABLE Students ( StudentID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Grade INT ); ``` 2.2 Foreign Keys A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It establishes a link between the two tables. It's how we implement relationships between entities.

Referential Integrity: The foreign key value must either match a value in the referenced primary key column, or it must be null (if allowed). This ensures data consistency.

Relationships: Foreign keys are the foundation of relational database relationships (one-to-one, one-to-many, many-to-many).

Example: Let's add a `Classes` table and link it to the `Students` table: | Column Name | Data Type | Constraints | |-------------|-----------|-------------| | `ClassID` | `INT` | `PRIMARY KEY, AUTO_INCREMENT` | | `ClassName` | `VARCHAR(50)` | `NOT NULL` | And modify the `Students` table to include a `ClassID` column: | Column Name | Data Type | Constraints | |-------------|-----------|-------------| | `StudentID` | `INT` | `PRIMARY KEY, AUTO_INCREMENT` | | `FirstName` | `VARCHAR(50)` | `NOT NULL` | | `LastName` | `VARCHAR(50)` | `NOT NULL` | | `Grade` | `INT` | | | `ClassID` | `INT` | `FOREIGN KEY REFERENCES Classes(ClassID)` | Now, `ClassID` in the `Students` table is a foreign key that references the `ClassID` in the `Classes` table. This means each student is associated with a specific class.

This is a one-to-many relationship: one class can have many students.

SQL Syntax (MySQL): ```sql CREATE TABLE Classes ( ClassID INT PRIMARY KEY AUTO_INCREMENT, ClassName VARCHAR(50) NOT NULL ); ALTER TABLE Students ADD ClassID INT, ADD FOREIGN KEY (ClassID) REFERENCES Classes(ClassID); ``` 2.3 Types of Relationships One-to-One: One record in table A is related to one record in table B, and vice-versa. (e.g., One citizen has one ID number, managed by Home Affairs.)

One-to-Many: One record in table A can be related to multiple records in table B, but one record in table B is related to only one record in table A. (e.g., One class has many students, but each student belongs to only one class.)

Many-to-Many: Multiple records in table A can be related to multiple records in table B. (e.g., Students can enroll in many courses, and each course can have many students. This is often implemented using a "junction table" or "linking table.") 2.4 JOIN Clauses `JOIN` clauses are used to combine rows from two or more tables based on a related column (usually a foreign key relationship).

INNER JOIN: Returns only the rows where there is a match in both tables.

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table (the table listed first) and the matching rows from the right table. If there is no match in the right table, it returns `NULL` values for the right table's columns.

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there is no match in the left table, it returns `NULL` values for the left table's columns.

Example: Let's retrieve the first name of each student along with their class name: ```sql SELECT Students.FirstName, Classes.ClassName FROM Students INNER JOIN Classes ON Students.ClassID = Classes.ClassID; ``` This query joins the `Students` and `Classes` tables based on the `ClassID` column.

It will return a table with two columns: `FirstName` (from the `Students` table) and `ClassName` (from the `Classes` table), only for students who have a valid `ClassID`. 2.5 Filtering and Sorting `WHERE` Clause: Filters the results based on a condition.

Example: `SELECT FROM Students WHERE Grade > 10;` (Selects all students with a grade greater than 10.) `ORDER BY` Clause: Sorts the results based on one or more columns.

Example: `SELECT FROM Students ORDER BY LastName ASC, FirstName DESC;` (Sorts students by last name in ascending order and then by first name in descending order.) `LIMIT` Clause: Limits the number of rows returned.