PolyU | Database Principles

This lesson introduces digital data as the foundational representation of real-world information, explaining how discrete values are processed into meaningful insights. It further explores the architecture of modern software, detailing how data flows from user interactions through application logic to persistent backend storage.

This lesson explores how to bridge the gap between physical business operations and digital databases through structural abstraction and domain analysis. Students learn to translate real-world entities, attributes, and relationships into conceptual Entity-Relationship (ER) models to create organized, efficient data structures.

This lesson introduces data selection as a fundamental process for retrieving relevant information from large datasets using queries to bridge the gap between user needs and stored records. It further distinguishes between selection, which filters rows based on specific conditions, and projection, which isolates specific columns to refine query results.

This lesson explores how databases function as dynamic systems that transition between states through managed operations like inserts and updates. Students will learn how to maintain data integrity and consistency during these transformations to ensure that database records accurately reflect real-world events.

This lesson introduces data summarization in SQL, focusing on using aggregate functions like SUM(), AVG(), and COUNT() to transform granular row-level data into meaningful high-level metrics. Students will learn the importance of execution order, specifically how the WHERE clause filters data before aggregation, to effectively convert raw operational records into actionable insights.

This lesson explores the principles of relational database design, focusing on how separating data into distinct, linked tables eliminates redundancy and ensures data integrity. By using unique identifiers to connect related entities, students learn how to prevent maintenance anomalies and create more efficient, scalable data structures.

This lesson introduces relational reasoning as the logical framework for connecting normalized data across multiple tables using primary and foreign keys. It also explores the mechanics and limitations of inner joins, highlighting how they can cause data loss by omitting records that lack matching keys.

This lesson introduces query composition and decomposition as essential techniques for breaking complex data retrieval tasks into smaller, manageable, and sequential logical steps. By adopting a modular approach, developers can improve the readability, maintainability, and testability of their database scripts.

This lesson explores the importance of relational database design and normalization in eliminating data redundancy and preventing update anomalies. Students will learn how to organize information into connected tables using primary and foreign keys to ensure long-term data integrity and system efficiency.

This lesson explores how database execution environments and vendor-specific dialects impact SQL compatibility, emphasizing that syntactically correct queries may fail or behave differently when moved between systems. It also introduces transaction management as a vital mechanism for ensuring data integrity by grouping operations into atomic, all-or-nothing units of work.

This lesson emphasizes that database developers must look beyond logical correctness to ensure queries are optimized for performance and scalability. By understanding the query execution pipeline—from parsing and optimization to physical plan generation—developers can identify and resolve resource-heavy bottlenecks like inefficient full table scans.

This lesson explores how AI-driven natural language processing enables users to query databases without SQL expertise by translating conversational requests into structured commands. It emphasizes the importance of implementing security guardrails and verification steps to mitigate the operational risks associated with automated database interactions.

This lesson explores the architectural limitations of relational databases, specifically how their rigid, table-based structures struggle to accommodate the diverse and rapidly changing data found in modern applications. Students will learn to identify when these constraints create performance bottlenecks and how to evaluate alternative storage models to better support complex, semi-structured workloads.

This lab introduces the fundamentals of PostgreSQL, guiding students through the installation of the database server and the pgAdmin 4 management tool. Students will learn how to connect to their local database, execute basic SQL queries, and understand the core architecture of a relational database management system.

This lesson explains why flat spreadsheets are inefficient for managing complex data due to issues like redundancy and update anomalies. It introduces relational database design as a solution, teaching students how to identify entities and structure tables to ensure data integrity and consistency.

This lab teaches students how to identify and filter student-progress data in PostgreSQL by categorizing fields into identity, risk evidence, and decision status. Students will learn to construct queries that select specific columns and apply logical conditions to prioritize support for at-risk students.

This lab focuses on safely maintaining a student database in PostgreSQL by planning data changes, verifying table constraints, and confirming row counts before execution. Students learn to perform precise INSERT and UPDATE operations while ensuring that database integrity is preserved through careful validation of expected outcomes.