Lesson Notes By Weeks and Term v5 - Grade 12

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

• Explain database normalization up to Third Normal Form (3NF).
• Use subqueries and joins (INNER, LEFT, RIGHT) in SQL.
• Describe the purpose of database indexes for performance.
• Discuss database transaction management and ACID properties.
• Apply appropriate SQL data types for data integrity.

Lesson summary

This week, we delve into advanced database concepts and the Structured Query Language (SQL) used to interact with these databases within applications. Understanding advanced database techniques is crucial for building robust, efficient, and scalable applications that are vital for South Africa's growing digital economy. Think about applications used in banking (Absa, FNB), retail (Shoprite, Pick n Pay), healthcare (electronic patient records), and government services (ID systems, SASSA payments). All these rely on advanced database systems to function effectively and securely.

Lesson notes

2.1 Database Normalization (Up to 3NF) Database normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing larger tables into smaller, more manageable tables and defining relationships between them. The goal is to eliminate data anomalies that can occur during insertion, update, and deletion operations. We'll focus on normalization up to the Third Normal Form (3NF).

First Normal Form (1NF): A table is in 1NF if each column contains only atomic (indivisible) values. This means no repeating groups or multi-valued attributes.

Example: Unnormalized Table: `Student (StudentID, Name, Courses)` where Courses can contain multiple values (e.g., "Maths, Science, English"). 1NF Table: `Student (StudentID, Name, Course)` where each row represents one course taken by a student.

Second Normal Form (2NF): A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key. This is only relevant for tables with composite primary keys (keys made up of multiple columns). If a non-key attribute depends only on part of the composite key, it violates 2N

F. Example: Table: `OrderItems (OrderID, ProductID, ProductName, Quantity, Price)` with composite primary key (OrderID, ProductID). Assume ProductName and Price depend only on ProductID, not the entire (OrderID, ProductID) key. 2NF Solution: Create two tables: `OrderItems (OrderID, ProductID, Quantity)` `Products (ProductID, ProductName, Price)` Third Normal Form (3NF): A table is in 3NF if it is in 2NF and all non-key attributes are non-transitively dependent on the primary key. This means that no non-key attribute should depend on another non-key attribute.

Example: Table: `Employee (EmployeeID, Name, DepartmentID, DepartmentName)` where DepartmentName depends on DepartmentID, which in turn depends on EmployeeID. 3NF Solution: Create two tables: `Employee (EmployeeID, Name, DepartmentID)` `Department (DepartmentID, DepartmentName)` Why is Normalization Important?

Reduces Data Redundancy: Minimizes storage space and avoids inconsistencies.

Improves Data Integrity: Ensures data is accurate and consistent.

Simplifies Data Modification: Makes it easier to update, insert, and delete data without causing anomalies.

Enhances Query Performance: Well-structured tables can lead to faster query execution. 2.2 Subqueries and Joins Subqueries: A subquery (or inner query) is a query nested inside another SQL query. It is used to retrieve data that will be used in the main query.

Example: Find all students who are enrolled in courses taught by a specific lecturer. ```sql SELECT StudentName FROM Students WHERE CourseID IN (SELECT CourseID FROM Courses WHERE LecturerID = 'L001'); ``` Here, the subquery `(SELECT CourseID FROM Courses WHERE LecturerID = 'L001')` retrieves the `CourseID`s taught by lecturer 'L001'. The main query then selects the `StudentName` from the `Students` table where the `CourseID` matches one of those returned by the subquery.

Joins: Joins are used to combine rows from two or more tables based on a related column between them.

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

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there is no match, the right side will contain NULL values.

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If there is no match, the left side will contain NULL values.

Example: Retrieve the names of all students and the courses they are enrolled in. ```sql SELECT Students.StudentName, Courses.CourseName FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID INNER JOIN Courses ON Enrolments.CourseID = Courses.CourseID; ``` This query joins the `Students`, `Enrolments`, and `Courses` tables based on the `StudentID` and `CourseID` columns, respectively. It returns the `StudentName` from the `Students` table and the `CourseName` from the `Courses` table for all students enrolled in courses. LEFT JOIN

Example: To find all students and the courses they're enrolled in, even if they're not currently enrolled in any courses: ```sql SELECT Students.StudentName, Courses.CourseName FROM Students LEFT JOIN Enrolments ON Students.StudentID = Enrolments.StudentID LEFT JOIN Courses ON Enrolments.CourseID = Courses.CourseID; ``` This returns all students. If a student isn't in the Enrolments table, the `CourseName` will be NULL. 2.3 Database Indexes A database index is a data structure that improves the speed of data retrieval operations on a database table. It's similar to an index in a book, allowing the database to quickly locate specific rows based on the indexed column(s). Why Use Indexes?

Faster Queries: Significantly reduces query execution time, especially for large tables.

Improved Performance: Enhances overall database performance.