PolyU | Database Principles

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 teaches how to safely maintain a PostgreSQL student database by handling four requests—adding, updating, standardizing, and deleting records—while ensuring only the intended rows are affected. You'll learn to inspect table constraints, restore a known starting state, and use INSERT, UPDATE, and DELETE statements with correct targets and expected row counts.

In Lab 5, students learn how to group and summarise data in PostgreSQL using a university course performance dataset. Key topics include writing aggregate functions like COUNT, SUM, AVG, MIN, and MAX, handling NULL values, and using DISTINCT to generate accurate registrar reports.

本实验旨在学习如何使用 PostgreSQL 中的 INNER JOIN 将学生、课程和选课表关联起来，从而生成包含完整信息的选课报告。通过实践，学生将掌握如何利用外键匹配相关数据，并理解连接操作如何根据匹配条件合并多表记录。

This lab explores how to use different SQL join types—INNER, LEFT, RIGHT, FULL, and CROSS—to control which rows are retained when reconciling data between a student registry and an LMS roster. Students will learn to manage unmatched records and generate specific reports by selecting the appropriate join based on the desired output requirements.

Students learn how to compose data operations in PostgreSQL by investigating student performance declines across different academic periods using scalar subqueries, filters, and baseline comparisons.

This lesson introduces database transactions as single, indivisible units of work that ensure data integrity through atomicity, durability, and visibility control. Students learn how transactions manage failure handling and state boundaries using commit and rollback operations.