Wedding Planner Database Normalization

Denormalized Form

A single flat relation initially stores all entities and relationships of the Wedding Planner system in one table, without structural separation. This causes repeating groups, redundancy, update anomalies, insertion anomalies, and deletion anomalies.

Initial Denormalized Relation

R( user_id, first_name, last_name, email, phone_number, gender, birthday, wedding_id, date, budget, notes, venue_booking_id, vb_date, vb_start, vb_end, vb_status, vb_price, venue_id, venue_name, location, city, capacity, price_per_guest, venue_type, photographer_id, photographer_name, photo_price_per_hour, band_id, band_name, band_genre, band_price_per_hour, registrar_id, registrar_name, registrar_location, church_id, church_name, priest_name, guest_id, guest_first_name, guest_last_name, rsvp_status, attendance_status, table_number, event_type )

Functional Dependencies

The following functional dependencies hold in the Wedding Planner system:

user_id → first_name, last_name, email, phone_number, gender, birthday wedding_id → date, budget, notes, user_id event_id → event_type, date, start_time, end_time, status, wedding_id guest_id → guest_first_name, guest_last_name, email, wedding_id response_id → status, response_date, guest_id, event_id attendance_id → status, table_number, role, guest_id, event_id venue_booking_id → vb_date, vb_start, vb_end, vb_status, vb_price, venue_id, wedding_id venue_id → venue_name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id type_id → type_name photographer_booking_id → date, start_time, end_time, status, photographer_id, wedding_id photographer_id → photographer_name, email, phone_number, price_per_hour band_booking_id → date, start_time, end_time, status, band_id, wedding_id band_id → band_name, band_genre, equipment, phone_number, price_per_hour registrar_booking_id → start_time, end_time, price, status, registrar_id, wedding_id registrar_id → registrar_name, contact, location, working_hours church_id → church_name, location, contact, wedding_id priest_id → priest_name, contact, church_id

Additional Business Constraints

The following additional dependencies also hold:

{guest_id, event_id} → {response_id, rsvp_status, response_date} {guest_id, event_id} → {attendance_id, attendance_status, table_number} {venue_id, vb_date, vb_start} → {venue_booking_id} {wedding_id} → {venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, church_id}

Candidate Key Determination

To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity branches are required.

Candidate Key

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id }

Please review the detailed proof here → Proof of Candidate Key

Minimality Verification

Each attribute in K is necessary:

Removing attendance_id loses the guest/event attendance branch Removing venue_booking_id loses venue reservation information Removing photographer_booking_id loses photographer booking information Removing band_booking_id loses band booking information Removing registrar_booking_id loses registrar booking information Removing priest_id loses priest information because church_id is derived through priest_id

Since removing any attribute prevents determining all attributes of R, the key is minimal.

First Normal Form (1NF)

Definition

A relation is in 1NF if:

Every attribute contains atomic values No repeating groups exist Each tuple is uniquely identifiable Columns contain consistent data types

Transformation to 1NF

The original denormalized relation contains multiple conceptual entities combined into a single table. Repeating groups are eliminated by separating each entity occurrence into individual tuples.

All attributes now contain atomic values.

Superkey Construction

A superkey for the denormalized relation is:

K = { user_id, wedding_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, church_id, event_id, guest_id, response_id, attendance_id }

Minimality Check

Removing any identifier causes loss of at least one independent entity branch.

Examples:

Removing venue_booking_id removes venue booking information Removing photographer_booking_id removes photographer booking information Removing attendance_id removes attendance information

Therefore the superkey is necessary for uniqueness in the denormalized structure.

Second Normal Form (2NF)

Definition

A relation is in 2NF if:

It is already in 1NF Every non-key attribute is fully functionally dependent on the entire primary key No partial dependencies exist

Partial Dependency Violations

The original relation violates 2NF because:

user_id → user attributes wedding_id → wedding attributes venue_id → venue attributes

These dependencies rely only on part of the composite key.

Decomposition into 2NF Relations

Relation	Primary Key
USER	user_id
WEDDING	wedding_id
VENUE_TYPE	type_id
VENUE	venue_id
VENUE_BOOKING	booking_id
PHOTOGRAPHER	photographer_id
PHOTOGRAPHER_BOOKING	booking_id
BAND	band_id
BAND_BOOKING	booking_id
REGISTRAR	registrar_id
REGISTRAR_BOOKING	booking_id
CHURCH	church_id
PRIEST	priest_id
EVENT	event_id
GUEST	guest_id
EVENT_RSVP	response_id
ATTENDANCE	attendance_id

Each resulting relation contains attributes fully dependent on its primary key.

Lossless Join Verification

Every decomposition satisfies the lossless join condition:

(Ri ∩ Rj) → Ri OR (Ri ∩ Rj) → Rj

Examples:

Join	Common Attribute	Proof
USER ⋈ WEDDING	user_id	user_id is PK in USER
VENUE_BOOKING ⋈ VENUE	venue_id	venue_id is PK in VENUE
EVENT_RSVP ⋈ EVENT	event_id	event_id is PK in EVENT

Therefore all decompositions are lossless.

Third Normal Form (3NF)

Definition

A relation is in 3NF if:

It is already in 2NF No transitive dependencies exist Every non-key attribute depends only on the primary key

Transitive Dependency Example

venue_id → type_id → type_name

Here, type_name depends transitively on venue_id through type_id.

Resolution

To eliminate the transitive dependency:

VENUE_TYPE(type_id, type_name) is created VENUE stores type_id as a foreign key

This removes the transitive dependency while preserving all functional dependencies.

Final 3NF Relations

Relation	Primary Key	Foreign Keys
USER	user_id	—
WEDDING	wedding_id	user_id → USER
VENUE_TYPE	type_id	—
VENUE	venue_id	type_id → VENUE_TYPE
VENUE_BOOKING	booking_id	venue_id → VENUE, wedding_id → WEDDING
PHOTOGRAPHER	photographer_id	—
PHOTOGRAPHER_BOOKING	booking_id	photographer_id → PHOTOGRAPHER, wedding_id → WEDDING
BAND	band_id	—
BAND_BOOKING	booking_id	band_id → BAND, wedding_id → WEDDING
REGISTRAR	registrar_id	—
REGISTRAR_BOOKING	booking_id	registrar_id → REGISTRAR, wedding_id → WEDDING
CHURCH	church_id	wedding_id → WEDDING
PRIEST	priest_id	church_id → CHURCH
EVENT	event_id	wedding_id → WEDDING
GUEST	guest_id	wedding_id → WEDDING
EVENT_RSVP	response_id	guest_id → GUEST, event_id → EVENT
ATTENDANCE	attendance_id	guest_id → GUEST, event_id → EVENT

BCNF Verification

A relation is in BCNF if for every non-trivial dependency:

X → Y

X is a superkey.

All final relations satisfy this condition because every determinant is a candidate key or superkey.

Final Conclusion

The Wedding Planner database schema has been formally normalized to Third Normal Form (3NF) and BCNF.

The normalization process:

Eliminates redundancy Prevents update anomalies Prevents insertion anomalies Prevents deletion anomalies Preserves functional dependencies Guarantees lossless joins Ensures entity integrity through verified primary keys

The final schema consists of 17 fully normalized relations.

Proof of Candidate Key

Definition

A set of attributes X is a candidate key if:

X⁺ = R X is minimal

The closure X⁺ is computed by repeatedly applying functional dependencies until no additional attributes can be derived.

Step 1 – Testing Single Attribute Candidate Keys

Attempt 1

K = {attendance_id}

Closure

(attendance_id)⁺ gives:

attendance_status table_number guest_id event_id

Using additional dependencies:

event_id → event_type, wedding_id guest_id → guest_first_name, guest_last_name, rsvp_status, wedding_id wedding_id → date, budget, notes, user_id user_id → first_name, last_name, email, phone_number, gender, birthday

Missing Dependencies

The closure still does NOT determine:

venue booking information photographer booking information band booking information registrar booking information priest and church information

Therefore:

(attendance_id)⁺ ≠ R

Conclusion:

attendance_id is NOT a candidate key.

Step 2 – Testing Two Attributes

K = {attendance_id, priest_id}

Closure

Additional attributes derived:

priest_name priest_contact church_id church_name church_location

Missing Dependencies

Still missing:

venue booking information photographer booking information band booking information registrar booking information

Therefore:

(attendance_id, priest_id)⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 3 – Testing Three Attributes

K = { attendance_id, venue_booking_id, photographer_booking_id }

Closure

Additional attributes derived:

venue information photographer information wedding information user information

Missing Dependencies

Still missing:

band booking information registrar booking information priest/church information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 4 – Testing Larger Attribute Sets

K = { attendance_id, priest_id, venue_booking_id, band_booking_id }

Missing Dependencies

Still missing:

photographer booking information registrar booking information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 5 – Including All Booking Branches

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id }

Missing Dependencies

Still missing:

priest information church information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 6 – Final Candidate Key

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id }

Closure

Using all functional dependencies:

attendance branch is determined event branch is determined guest branch is determined wedding branch is determined venue booking branch is determined photographer booking branch is determined band booking branch is determined registrar booking branch is determined priest branch is determined church branch is determined

No attributes remain undetermined.

Therefore:

K⁺ = R

Conclusion:

K is a candidate key.

Minimality Proof

A candidate key must be minimal.

Removing any attribute from K causes loss of at least one independent entity branch:

Removed Attribute	Lost Information
attendance_id	attendance branch
venue_booking_id	venue booking branch
photographer_booking_id	photographer booking branch
band_booking_id	band booking branch
registrar_booking_id	registrar booking branch
priest_id	priest/church branch

Therefore every attribute in K is necessary.

Why church_id Is Not Included

Suppose we define:

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id, church_id }

This violates minimality because:

priest_id → church_id

church_id is already derivable from priest_id.

Therefore church_id is redundant.

The resulting set would be a superkey, NOT a candidate key.

Final Candidate Key

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id }

This key:

Determines all attributes of R Is minimal Satisfies the formal definition of a candidate key