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 basic relational database.
• Create tables in a Database Management System (DBMS).
• Write basic SQL queries to find and manage data.
• Understand the importance of data integrity.
• Identify appropriate data types for different information.

Lesson summary

This week, we delve into the crucial skill of database design and querying. Databases are the backbone of information management in today's world, underpinning everything from online shopping to government record-keeping. Understanding how to design and query databases is not just a computer skill; it's a powerful tool for organizing, analyzing, and making sense of data that surrounds us. In South Africa, effective data management is essential for addressing challenges like resource allocation, service delivery, and monitoring progress towards national development goals.

Lesson notes

2.1 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. Think of it as a highly organized digital filing cabinet. 2.2 Relational Databases: Most modern databases are relational databases. This means that data is stored in tables, and these tables are related to each other through common fields. This structure avoids redundancy and ensures data consistency. The relational model is based on mathematical set theory, but don't worry, we'll keep it practical. 2.3 Key Components of a Relational Database: Table: A collection of related data organized in rows and columns. Imagine an Excel spreadsheet but much more powerful.

Record (Row): A single entry in a table, representing a specific instance of the entity the table describes. For example, one row in a "Student" table would represent information about one student.

Field (Column): A specific attribute or characteristic of the entity the table describes. For example, in a "Student" table, fields might include "StudentID," "FirstName," "LastName," and "DateOfBirth." Primary Key: A field (or a combination of fields) that uniquely identifies each record in a table. It must be unique and cannot be empty (NULL). The "StudentID" field is a good candidate for a primary key.

Foreign Key: A field in one table that refers to the primary key of another table. Foreign keys establish relationships between tables. For example, a "CourseEnrollment" table might have a "StudentID" foreign key referencing the "Student" table.

Data Type: Specifies the type of data that can be stored in a field.

Common data types include: Text/VARCHAR: For storing strings of characters (e.g., names, addresses).

Integer/INT: For storing whole numbers (e.g., ages, quantities).

Real/Float/Double: For storing numbers with decimal points (e.g., prices, measurements).

Date/DateTime: For storing dates and times.

Boolean/Yes/No: For storing true/false values. 2.4 Database Design Process: Requirement Analysis: Understand the needs of the user and the data that needs to be stored.

Entity Identification: Identify the key entities (things) about which you need to store information (e.g., Students, Courses, Teachers).

Attribute Identification: Determine the attributes (properties) of each entity (e.g., StudentID, FirstName, LastName for the Student entity).

Relationship Identification: Determine how the entities are related to each other (e.g., a Student enrolls in a Course). This is often visualised with an ER (Entity-Relationship) diagram.

Normalization: Optimize the database structure to reduce data redundancy and improve data integrity. (This will be explored in more detail later, but at a basic level, it means avoiding repeating the same information in multiple places.)

Implementation: Create the database and tables in a DBMS. 2.5 Introduction to SQL (Structured Query Language): SQL is the standard language for interacting with relational databases. It allows you to retrieve, insert, update, and delete data.

SELECT: Used to retrieve data from a table. ```sql SELECT FirstName, LastName FROM Students; -- Retrieves the first and last names of all students. ``` WHERE: Used to filter data based on specific conditions. ```sql SELECT FirstName, LastName FROM Students WHERE City = 'Johannesburg'; -- Retrieves the first and last names of students living in Johannesburg. ``` ORDER BY: Used to sort the results. ```sql SELECT FirstName, LastName FROM Students ORDER BY LastName; -- Retrieves and sorts student names alphabetically by last name. ``` INSERT INTO: Used to add new data to a table. ```sql INSERT INTO Students (FirstName, LastName, City) VALUES ('Thabo', 'Mbeki', 'Pretoria'); -- Adds a new student to the table. ``` UPDATE: Used to modify existing data in a table. ```sql UPDATE Students SET City = 'Cape Town' WHERE StudentID = 123; -- Changes the city of student with ID 123 to Cape Town. ``` DELETE FROM: Used to remove data from a table. ```sql DELETE FROM Students WHERE StudentID = 456; -- Deletes the student with ID 456 from the table. ``` JOIN: Used to combine data from two or more tables based on a related column. This is crucial for relational databases. Imagine you have a table `Students` with `StudentID` and a table `Enrollments` with `StudentID` and `CourseID`. You can join these to find which courses each student is enrolled in. ```sql SELECT Students.FirstName, Students.LastName, Enrollments.CourseID FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID; ``` 2.6 Worked

Example: Creating a Database for a School Tuck Shop Let's design a database for a school tuck shop.

Requirement Analysis: The tuck shop needs to track products, prices, and sales transactions.