Skip to main content

Course Introduction

  • Welcome to Intermediate Database Concepts, the second course in your journey toward understanding how applications store, manage, and work with data. This course deepens students’ understanding of how databases maintain efficiency, consistency, and reliability. It covers constraints and integrity rules that enforce data correctness, and introduces indexing, a critical technique for improving query performance.

  • Students will explore intermediate data modeling concepts to design more robust schemas, and learn how to use views and materialized views to simplify complex queries and optimize reporting.

  • The course also explains how databases handle transactions and concurrency, ensuring safe multi-user operations, and introduces Stored Procedures, Functions, and Triggers for encapsulating reusable logic within the database. This level bridges fundamentals with more intermediate SQL and database architecture skills.

What You'll Learn​

Intermediate SQL​

  • This module builds on basic SQL skills and introduces more powerful tools for analyzing and manipulating data. Students will learn how to use GROUP BY and HAVING to summarize data, along with essential aggregate functions like SUM, COUNT, AVG, MIN, and MAX.

  • The module also covers writing conditional logic using CASE expressions, enabling dynamic values within queries. Finally, students will work with subqueries, including IN and EXISTS, to handle more complex filtering and multi-step data retrieval. This section prepares learners for real-world reporting, analytics, and advanced data querying.

Constraints and Integrity​

  • This topic focuses on the rules that ensure data stored in a database remains accurate, valid, and reliable. Students will learn how the UNIQUE constraint prevents duplicate values in a column, how CHECK enforces custom conditions on data such as value ranges, and how NOT NULL ensures that essential fields always contain a value. These constraints form the foundation of maintaining strong data integrity within any relational database.

Indexing​

  • This topic introduces students to how indexes improve database performance by allowing faster data retrieval. It explains what indexes are and how they work, along with common types such as B-Tree, Hash, and Partial indexes. Students will learn when indexing is beneficial, such as on frequently searched columns and when it can be harmful due to extra storage and slower write operations.

  • The topic also covers the basics of using EXPLAIN and ANALYZE to understand query execution plans and evaluate whether indexes are being used effectively.

Intermediate Data Modeling​

  • This topic covers how to properly model many-to-many relationships using junction (bridge) tables and how composite primary keys are often used to uniquely identify paired records in these tables. Students will also learn about surrogate keys such as UUIDs, sequences, and serial columns and when they are preferred over natural or composite keys.

  • The section introduces higher-level data modeling concepts like Star and Snowflake schemas, helping learners understand how relational structures are organized in analytical and reporting systems.

Views and Materialized Views​

  • This topic introduces the use of views as virtual tables that simplify complex queries and improve readability, along with materialized views that store query results for faster access. Students will learn when and why to use each type, including common scenarios such as security filtering, abstraction, and reporting.

  • The module also covers key performance considerations such as refresh strategies for materialized views and the impact of complex underlying queries to help learners choose the right approach for different use cases.

Transactions and Concurrency​

  • This topic explains how databases maintain reliability when multiple operations or users interact with the system at the same time. Students will learn the ACID properties that ensure transactions are processed safely, along with the basics of locking and how it prevents conflicting updates.

  • The module also introduces key isolation levels (Read Committed, Repeatable Read, and Serializable) to show how databases manage concurrent access and balance consistency with performance.

  • This section builds a strong foundation for understanding safe, predictable multi-user database behavior.

Stored Procedures, Functions, and Triggers​

  • This topic introduces how to extend database functionality using server-side programming. Students will learn how to create stored procedures to encapsulate reusable logic and automate complex operations, and how to build user-defined functions that return values for use in queries.

  • The module also covers the basics of triggers, which allow automatic execution of actions in response to database events such as inserts or updates. Together, these tools help streamline workflows, enforce rules, and optimize application-database interactions.

How To Go Through The Course​

  • It is recommended you go through each module in the course, in the order in which it is presented. Each module in the course is set up very much the same.

Grading​

  • The course is graded by completing a post-assessment test and lab at the end of each module. You will only have one attempt at completing the post-assessment test. Your final grade will be determined by the total score for each post-assessment test and lab.

  • When you have completed all the modules, post-assessment tests, and labs, you are required to email your Instructor: roderick.bernardo@georgebrown.ca who will process your grade. In your email, please include the CRN #.

Assistance​

  • We wish you well as you go through the course and hope this course meets your needs. If you require any assistance while you go through this course, you can always contact your instructor: roderick.bernardo@georgebrown.ca please indicate which module you require assistance in.