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).
• Implement a database using a DBMS like LibreOffice Base or MS Access.
• Write basic SQL queries (SELECT, FROM, WHERE).
• Use comparison operators (=, <, >, <>) in SQL queries.
• Understand and apply data types (text, number, date).

Lesson summary

Databases are fundamental to modern information management. From large corporations to small spaza shops, databases are used to store, organize, and retrieve information efficiently. In South Africa, understanding database concepts is crucial for individuals entering the ICT sector, entrepreneurs managing customer data, and even citizens interacting with government services online. This week, we focus on the basics of database design and querying, empowering you to create and interact with databases effectively. Consider how a database could help a local farmer track livestock and sales, or how a school could manage student records efficiently.

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. It's designed to allow efficient storage, retrieval, modification, and deletion of data. Think of it like a well-organized filing cabinet compared to a messy pile of papers. 2.2 Relational Databases We will primarily focus on relational databases. These databases organize data into one or more tables. Each table has rows (records) and columns (fields). The relationships between these tables are defined using keys. 2.3 Key Concepts Entity: A real-world object or concept about which we want to store information (e.g., Students, Teachers, Courses, Products, Customers). In the context of a university, an entity is a student.

Attribute: A characteristic or property of an entity (e.g., StudentID, Name, Surname, CourseCode). The attributes of a student are StudentID, Name, Surname, and CourseCode.

Record (Row): A single instance of an entity, representing a specific entry in the table (e.g., a specific student with all their details).

Example: `(2023001, 'Thabo', 'Mbeki', 'INF101')`.

Field (Column): A category of information within a table, representing an attribute (e.g., StudentID, Name).

Primary Key: A unique identifier for each record in a table. It ensures that each record can be easily identified and distinguished from others (e.g., StudentID). Crucially, a primary key CANNOT be null (empty).

Foreign Key: A field in one table that refers to the primary key in another table. It establishes a relationship between the two tables (e.g., CourseCode in the Students table, referring to the primary key CourseCode in the Courses table). Foreign keys are essential for maintaining data integrity and ensuring relationships between tables are valid.

Data Types: The type of data that a field can hold.

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

Number (Integer/Decimal): For storing numerical data (e.g., age, price). Integer is for whole numbers, decimal allows for fractions.

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

Boolean: For storing true/false values. 2.4 Database Design (Schema) Designing a database involves defining the tables, fields, data types, primary keys, and foreign keys. A well-designed database is efficient, avoids redundancy, and ensures data integrity.

Example: School Database Let's design a database for a school to manage students, teachers, and courses.

Entities: Students, Teachers, Courses Tables: Students: `StudentID` (Integer, Primary Key) `Name` (Text) `Surname` (Text) `DateOfBirth` (Date) `CourseCode` (Text, Foreign Key referencing Courses.CourseCode)

Teachers: `TeacherID` (Integer, Primary Key) `Name` (Text) `Surname` (Text) `Subject` (Text)

Courses: `CourseCode` (Text, Primary Key) `CourseName` (Text) `TeacherID` (Integer, Foreign Key referencing Teachers.TeacherID) 2.5 SQL (Structured Query Language) SQL is the standard language for interacting with databases. We will focus on the basic `SELECT` statement for retrieving data. `SELECT`: Specifies the columns to retrieve. Use `` to select all columns. `FROM`: Specifies the table to retrieve data from. `WHERE`: Specifies the condition(s) to filter the data. Example Queries (Based on the School Database): Retrieve all data from the Students table: ```sql SELECT * FROM Students; ``` Retrieve the Name and Surname of all students: ```sql SELECT Name, Surname FROM Students; ``` Retrieve the Name and Surname of students born after 2005-01-01: ```sql SELECT Name, Surname FROM Students WHERE DateOfBirth > '2005-01-01'; ``` Retrieve all information about courses taught by TeacherID 123: ```sql SELECT * FROM Courses WHERE TeacherID = 123; ``` 2.6 Comparison Operators SQL uses comparison operators to filter data in the `WHERE` clause: `=`: Equal to `>`: Greater than ` =`: Greater than or equal to ` ` or `!=`: Not equal to

Example: Using Comparison Operators To find all students with a StudentID greater than 2023005: ```sql SELECT * FROM Students WHERE StudentID > 2023005; ``` Guided Practice (With Solutions)

Question 1: Design a database schema for a library system. Identify the entities, attributes, primary keys, and foreign keys. Assume the library needs to track books, members, and borrowing information.

Solution: Entities: Books, Members, Borrowing Tables: Books: `BookID` (Integer, Primary Key) `Title` (Text) `Author` (Text) `ISBN` (Text, Unique Index)

Members: `MemberID` (Integer, Primary Key) `Name` (Text) `Surname` (Text) `Address` (Text) `ContactNumber` (Text)

Borrowing: `BorrowID` (Integer, Primary Key) `BookID` (Integer, Foreign Key referencing Books.BookID) `MemberID` (Integer, Foreign Key referencing Members.MemberID) `BorrowDate` (Date) `ReturnDate` (Date)

Commentary: We have identified three key entities: Books, Members, and Borrowing.