= 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 ==

This section contains the SQL script files used during the implementation of the advanced database development phase.

The scripts are separated by functionality in order to improve organization, readability, maintenance, and deployment of the database objects.

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

=== Additional Notes ===

* All SQL scripts are compatible with PostgreSQL 15.
* The scripts are intended to be executed using pgAdmin 4 or PostgreSQL command-line tools.
* SQL files are organized by database functionality for easier maintenance and testing.

== 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.