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.

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	4
Procedures	1
SQL Views	4
Additional Constraints	Multiple

SQL Scripts

• advanced_triggers.sql
• advanced_functions.sql
• advanced_views.sql
• advanced_constraints.sql
• advanced_testing.sql

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.