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 relational database schema (entities, attributes, keys).
• Create tables in a database using SQL.
• Write basic SQL queries to retrieve data (SELECT, FROM, WHERE).
• Sort query results using ORDER BY.
• Use comparison operators to filter data precisely.

Lesson summary

Databases are fundamental to organizing and managing information in the modern world. From storing student records at your school to tracking stock levels at your local Shoprite, databases are used everywhere. Understanding database design and queries is crucial for any computer user, and especially important for a Computer Applications Technology student. In a South African context, mastering these skills will enable you to contribute to developing efficient and effective data management systems for businesses, government entities, and community organizations.

Lesson notes

2.1 Introduction to Databases A database is an organized collection of structured information, or data, typically stored electronically in a computer system. Databases allow for efficient data retrieval, modification, and deletion. Relational databases are the most common type, organizing data into tables with rows (records) and columns (fields/attributes).

Key Concepts: Database: A structured collection of data.

Table: A collection of related data organized in rows and columns.

Record (Row): A single entry in a table, representing a specific instance of the entity.

Field (Column/Attribute): A characteristic or property of an entity.

Primary Key: A unique identifier for each record in a table. It cannot be NULL (empty) and must be unique across all records.

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

Entity: A real-world object or concept about which you want to store information (e.g., student, book, product).

Relationship: A connection between two entities (e.g., a student enrolls in a course). 2.2 Database Design (Schema) Designing a database involves determining the entities, attributes, primary keys, and foreign keys. This process is crucial for creating an efficient and well-structured database.

Example: School Database Let's consider a simple database for a school.

We might have the following entities: Student: Information about students.

Course: Information about courses offered.

Teacher: Information about teachers.

Attributes: Student: StudentID (Primary Key), Name, Surname, DateOfBirth, Grade, Address, ContactNumber Course: CourseID (Primary Key), CourseName, CourseCode, Credits Teacher: TeacherID (Primary Key), Name, Surname, Qualification, SubjectTaught, ContactNumber Relationships: A student enrolls in a course. A teacher teaches a course. To represent the "enrolls in" relationship, we would add a foreign key `StudentID` and `CourseID` to an Enrollment table. To represent the "teaches" relationship, we would add a foreign key `TeacherID` and `CourseID` to a TeacherCourse table. 2.3 SQL (Structured Query Language) SQL is the standard language for interacting with relational databases. We use SQL to create tables, insert data, update data, delete data, and retrieve data.

Basic SQL Commands: CREATE TABLE: Creates a new table in the database. ```sql CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(255), Surname VARCHAR(255), DateOfBirth DATE, Grade INT ); ``` INSERT INTO: Inserts new data into a table. ```sql INSERT INTO Students (StudentID, Name, Surname, DateOfBirth, Grade) VALUES (1, 'Thando', 'Mbeki', '2005-05-10', 11); ``` SELECT: Retrieves data from a table. ```sql SELECT * FROM Students; -- Selects all columns and rows ``` FROM: Specifies the table to retrieve data from.

WHERE: Filters the data based on a condition. ```sql SELECT * FROM Students WHERE Grade = 11; ``` ORDER BY: Sorts the data based on one or more columns. ```sql SELECT * FROM Students ORDER BY Surname; -- Ascending order by Surname SELECT * FROM Students ORDER BY Surname DESC; -- Descending order by Surname ``` Comparison Operators: `=`: Equal to `>`: Greater than ` =`: Greater than or equal to ` `: Not equal to `LIKE`: Used for pattern matching (e.g., `WHERE Name LIKE 'T%'` finds names starting with "T"). '%' represents any number of characters, '_' represents a single character.