Phase P6: Advanced Database Development (SQL DDL)

Description

This phase extends the Wedding Planner Management System with advanced PostgreSQL database functionality implemented using SQL and PL/pgSQL.

The purpose of this phase is to enforce business rules directly at the database level through trigger functions, stored procedures, SQL functions, views, and additional integrity constraints.

The implementation focuses on:

• preventing booking conflicts
• validating RSVP and attendance consistency
• improving financial calculations
• simplifying reporting and analytics
• ensuring data integrity independently from the application layer

The advanced database objects are implemented on top of the normalized relational schema developed in Phase P2 and validated through the prototype application in Phase P4.

Technical Environment

Component	Technology
Database Engine	PostgreSQL 15
SQL Language	PostgreSQL SQL / PLpgSQL
Database Management	pgAdmin 4
Application Layer	Flask REST API
Character Encoding	UTF-8

Objectives

• enforce advanced business rules directly inside the database
• prevent resource booking conflicts
• improve database consistency and integrity
• support analytical reporting through SQL views
• centralize validation logic in PostgreSQL

Development History

• v0.1 – Initial trigger implementation
• v0.2 – Added stored functions and procedures
• v0.3 – Added analytical SQL views
• v0.4 – Added testing and validation scenarios
• v0.5 – Final cleanup and documentation

Database Objects Summary

Object Type	Implemented Objects
Trigger Functions	4
Triggers	4
SQL Functions	3
Procedures	1
SQL Views	4
Additional Constraints	Multiple

SQL Scripts

Script Name	Description
advanced_triggers.sql	Contains trigger functions and trigger definitions for booking validation and attendance consistency
advanced_functions.sql	Contains reusable SQL functions and stored procedures for calculations and reporting
advanced_views.sql	Contains analytical SQL views used for financial and operational summaries
advanced_constraints.sql	Contains additional CHECK and UNIQUE constraints
advanced_testing.sql	Contains SQL testing scenarios for validating triggers and constraints

Contents

Trigger Functions

This section contains trigger functions and triggers used for automatic validation and conflict prevention inside the database.

Stored Functions and Procedures

This section contains reusable SQL functions and procedures used for calculations, validation, and reporting.

Analytical SQL Views

This section contains SQL views used for reporting and analytical summaries.

Additional Integrity Constraints

This section documents additional CHECK and UNIQUE constraints implemented during the advanced database phase.

Testing and Validation

This section contains example SQL testing scenarios used to validate the advanced database functionality.

Summary

This phase successfully introduced advanced PostgreSQL database functionality into the Wedding Planner Management System.

The implementation includes trigger-based validation, reusable SQL functions, analytical views, and additional integrity constraints used to enforce business rules directly inside the database layer.

The advanced SQL functionality improves data consistency, reduces application-level complexity, and supports analytical reporting for wedding management operations.

Additional Notes

• All trigger functions are implemented using PL/pgSQL.
• Validation logic is executed automatically during INSERT and UPDATE operations.
• SQL views are used to simplify analytical reporting.
• Constraints are implemented directly at database level for stronger integrity enforcement.