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.
• Create a database using a DBMS like MS Access.
• Write basic SQL queries to find and sort data.
• Choose correct data types for fields.
• Explain the importance of data integrity.

Lesson summary

Databases are the backbone of information management in today's digital world. From keeping track of learner records in schools to managing inventory in local spaza shops, databases help us store, organize, and retrieve information efficiently. Understanding database design and queries is crucial for anyone pursuing a career in IT or even for effectively managing data in personal or professional contexts. Imagine, for instance, a community clinic needing to track patient information securely and efficiently – a well-designed database is indispensable. This week, we will focus on the fundamental principles of database design and how to retrieve specific information using queries.

Lesson notes

What is a Database? A database is an organized collection of structured information, or data, typically stored electronically in a computer system. Databases are designed to make it easy to access, manage, and update data.

Database Management System (DBMS): A DBMS is software that allows users to define, create, maintain, and control access to the database. Examples include MS Access, LibreOffice Base, MySQL, PostgreSQL, and Oracle. For this course, we can focus on MS Access or LibreOffice Base due to their accessibility.

Relational Database: The most common type of database. Data is organized into tables with rows and columns. Relationships are established between tables using keys.

Tables: A table is a collection of related data entries and consists of columns (fields) and rows (records). Think of it like a spreadsheet.

Fields (Columns): A field represents a single piece of information about an item in the table.

Examples: Name, Surname, ID Number, Date of Birth. Each field should have a specific data type.

Records (Rows): A record represents a single item or entity in the table. It contains the data for all the fields in that table.

Data Types: Data types define the kind of data that a field can hold.

Common data types include: Text (String): For storing text characters (e.g., names, addresses). Number (Integer, Decimal): For storing numerical values (e.g., age, quantity).

Date/Time: For storing dates and times (e.g., birth date, order date).

Boolean (Yes/No): For storing true/false values (e.g., IsActive, HasPaid).

Currency: For storing monetary values.

AutoNumber (AutoIncrement): Automatically generates a unique number for each new record.

Primary Key: A field (or combination of fields) that uniquely identifies each record in a table. A primary key cannot be NULL (empty) and must be unique.

Example: Learner ID in a `Learners` table. The primary key enforces entity integrity.

Foreign Key: A field in one table that refers to the primary key of another table. Used to establish relationships between tables.

Example: Class ID in a `Learners` table referring to the primary key (Class ID) in the `Classes` table. The foreign key enforces referential integrity.

Relationships: Relationships define how tables are related to each other.

Common types of relationships: One-to-Many: One record in table A can be related to many records in table B. (e.g., One Class can have many Learners).

One-to-One: One record in table A is related to only one record in table B. (Less common).

Many-to-Many: Many records in table A can be related to many records in table B (requires a junction table).

SQL (Structured Query Language): The standard language for interacting with databases. We will focus on the `SELECT` statement.

SELECT: Used to retrieve data from the database.

FROM: Specifies the table(s) from which to retrieve data.

WHERE: Specifies conditions that records must meet to be included in the result set.

ORDER BY: Specifies the order in which the result set should be sorted.

Example: `SELECT Name, Surname FROM Learners WHERE ClassID = 1 ORDER BY Surname;` This query retrieves the Name and Surname of all learners in Class 1, sorted alphabetically by Surname.

Database Design Process: Identify Entities: What are the main things we need to store information about? (e.g., Learners, Classes, Teachers)

Identify Attributes: What information do we need to store about each entity? (e.g., Learner Name, Class Name, Teacher Contact Number)

Choose Primary Keys: What uniquely identifies each entity?

Establish Relationships: How are the entities related to each other?

Create Tables: Create tables based on the entities and their attributes.

Define Data Types: Choose appropriate data types for each field.

Implement Relationships: Create foreign keys to link tables.