Wedding Planner Database Normalization

Denormalized Form

A single flat table containing all entities and their relationships, with no structural constraints. Multi-valued attributes and repeating groups are present.

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 Functional Dependencies

The following additional functional dependencies also hold in R due to business constraints:

• {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

Attribute set X is a candidate key if:

• X⁺ = R
• X is minimal

Candidate Key

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

The detailed proof is provided here → Proof of Candidate Key

Minimality Check

• Removing attendance_id loses the guest/event branch
• Removing venue_booking_id loses the venue booking branch
• Removing photographer_booking_id loses the photographer booking branch
• Removing band_booking_id loses the band booking branch
• Removing registrar_booking_id loses the registrar booking branch
• Removing priest_id loses priest information because a church may contain multiple priests

Therefore every attribute is required and the key is minimal.

Sample Denormalized Data

user_id	first_name	wedding_id	date	budget	venue_name	photographer_name	band_name	church_name	guest_fname	rsvp_status
1	Ana	1	2026-06-20	8500.00	Lakeside Garden	Luna Studio	Wedding Vibes	St. Clement	Daniel	accepted
2	Stefan	2	2026-09-05	12000.00	Royal Hall	Golden Frame	Balkan Groove	St. Panteleimon	none	none

First Normal Form (1NF)

Definition

A relation is in 1NF when:

• Row ordering carries no meaning
• Every attribute contains atomic values
• Data types are consistent
• A primary key uniquely identifies each tuple
• No repeating groups exist

Transformation to 1NF

The original denormalized relation contains multiple conceptual entities combined into one structure.

Examples include:

• User information
• Wedding information
• Guest information
• Event information
• Venue booking information
• Photographer booking information
• Band booking information
• Registrar booking information
• Church and priest information

Repeating groups are eliminated by separating repeating occurrences into individual tuples.

All attributes become atomic, satisfying the requirements of 1NF.

Superkey Construction

To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity groups must be included.

A valid superkey 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 Verification

Removing any identifier causes at least one branch of the relation to become undetermined.

Examples:

• Removing venue_booking_id loses venue booking information
• Removing photographer_booking_id loses photographer booking information
• Removing attendance_id loses attendance information
• Removing church_id loses church information

Therefore the superkey is necessary for uniqueness.

Transition to Second Normal Form (2NF)

To achieve 2NF:

• Partial dependencies must be removed
• Attributes depending only on part of a composite key must be separated
• Each resulting relation must contain attributes fully dependent on its own primary key

This results in decomposition into multiple smaller relations.

Second Normal Form (2NF)

Definition

A relation is in 2NF if:

• It is already in 1NF
• Every non-key attribute depends on the entire primary key
• No partial dependencies exist

Partial Dependency Violations

Examples of partial dependencies in the original relation:

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

Therefore the original relation violates 2NF.

2NF Decomposition

R1 – USER

{user_id} → {first_name, last_name, email, phone_number, gender, birthday}

Candidate Key Verification

user_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

user_id	first_name	last_name	email	phone_number	gender	birthday
1	Ana	Trajkovska	ana.trajkovska@…	+38970111222	Female	2004-07-12
2	Stefan	Petrovski	stefan.petrovski@…	+38970333444	Male	2003-11-20

Lossless Join Test

R ∩ R1 = {user_id, first_name, last_name, email, phone_number, gender, birthday}

Since:

user_id → R1

The decomposition is lossless.

---

R2 – WEDDING

{wedding_id} → {date, budget, notes, user_id}

Candidate Key Verification

wedding_id is the surrogate primary key.

Sample Data

wedding_id	date	budget	notes	user_id
1	2026-06-20	8500.00	Summer wedding	1
2	2026-09-05	12000.00	Elegant indoor wedding	2

Lossless Join Test

R1.1 ∩ R2 = {wedding_id, date, budget, notes, user_id}

Since:

wedding_id → R2

The decomposition is lossless.

---

R3 – VENUE_TYPE

{type_id} → {type_name}

Candidate Key Verification

type_id is the surrogate primary key.

Sample Data

type_id	type_name
1	Restaurant
2	Wedding Hall
3	Outdoor Garden

Lossless Join Test

R2.1 ∩ R3 = {type_id, type_name}

Since:

type_id → type_name

The decomposition is lossless.

---

R4 – VENUE

VENUE( venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id )

Candidate Key Verification

venue_id is the surrogate primary key.

Sample Data

venue_id	name	location	city	address	capacity	menu	phone_number	price_per_guest	type_id
1	Lakeside Garden	Matka	Skopje	Matka 12	200	Garden menu	+38971123456	35	3
2	Royal Hall	Centar	Skopje	Main St 5	350	Full menu	+38972234567	45	2

Lossless Join Test

R3.1 ∩ R4 = {venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id}

Since:

venue_id → R4

The decomposition is lossless.

---

R5 – VENUE_BOOKING

{booking_id} → {date, start_time, end_time, status, price, venue_id, wedding_id}

Candidate Key Verification

booking_id is the surrogate primary key.

Sample Data

booking_id	date	start_time	end_time	status	price	venue_id	wedding_id
1	2026-06-20	16:00	23:00	confirmed	7000	1	1
2	2026-09-05	17:00	23:30	confirmed	9500	2	2

Lossless Join Test

R4.1 ∩ R5 = {venue_booking_id, date, start_time, end_time, status, price, venue_id, wedding_id}

Since:

venue_booking_id → R5

The decomposition is lossless.

---

R6 – PHOTOGRAPHER

{photographer_id} → {name, email, phone_number, price_per_hour}

Candidate Key Verification

photographer_id is the surrogate primary key.

Sample Data

photographer_id	name	email	phone_number	price_per_hour
1	Luna Studio	luna@…	+38970101010	55
2	Golden Frame	golden@…	+38970202020	65

Lossless Join Test

R5.1 ∩ R6 = {photographer_id, name, email, phone_number, price_per_hour}

Since:

photographer_id → R6

The decomposition is lossless.

---

R7 – PHOTOGRAPHER_BOOKING

{booking_id} → {date, start_time, end_time, status, photographer_id, wedding_id}

Candidate Key Verification

booking_id is the surrogate primary key.

Sample Data

booking_id	date	start_time	end_time	status	photographer_id	wedding_id
1	2026-06-20	14:00	22:00	confirmed	1	1
2	2026-09-05	12:00	21:00	pending	2	2

Lossless Join Test

R6.1 ∩ R7 = {photographer_booking_id, date, start_time, end_time, status, photographer_id, wedding_id}

Since:

photographer_booking_id → R7

The decomposition is lossless.

---

R8 – BAND

{band_id} → {band_name, genre, equipment, phone_number, price_per_hour}

Candidate Key Verification

band_id is the surrogate primary key.

Sample Data

band_id	band_name	genre	equipment	phone_number	price_per_hour
1	Wedding Vibes	Pop	Sound + lights	+38970909090	80
2	Balkan Groove	Traditional	Full instruments	+38970707070	95

Lossless Join Test

R7.1 ∩ R8 = {band_id, band_name, genre, equipment, phone_number, price_per_hour}

Since:

band_id → R8

The decomposition is lossless.

---

R9 – BAND_BOOKING

{booking_id} → {date, start_time, end_time, status, band_id, wedding_id}

Candidate Key Verification

booking_id is the surrogate primary key.

Sample Data

booking_id	date	start_time	end_time	status	band_id	wedding_id
1	2026-06-20	18:00	23:00	confirmed	1	1
2	2026-09-05	19:00	23:30	confirmed	2	2

Lossless Join Test

R8.1 ∩ R9 = {band_booking_id, date, start_time, end_time, status, band_id, wedding_id}

Since:

band_booking_id → R9

The decomposition is lossless.

---

R10 – REGISTRAR

{registrar_id} → {name, contact, location, working_hours}

Candidate Key Verification

registrar_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

registrar_id	name	contact	location	working_hours
1	Skopje Civil Registry	+38970123456	Skopje	08:00-16:00
2	Centar Registry	+38970222333	Skopje	09:00-17:00

Lossless Join Test

R9.1 ∩ R10 = {registrar_id, name, contact, location, working_hours}

Since:

registrar_id → R10

The decomposition is lossless.

---

R11 – REGISTRAR_BOOKING

{booking_id} → {date, start_time, end_time, status, registrar_id, wedding_id}

Candidate Key Verification

booking_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

booking_id	date	start_time	end_time	status	registrar_id	wedding_id
1	2026-06-20	10:00	10:30	confirmed	1	1
2	2026-09-05	11:00	11:30	confirmed	2	2

Lossless Join Test

R10.1 ∩ R11 = {registrar_booking_id, date, start_time, end_time, status, registrar_id, wedding_id}

Since:

registrar_booking_id → R11

The decomposition is lossless.

---

R12 – CHURCH

{church_id} → {name, location, contact, wedding_id}

Candidate Key Verification

church_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

church_id	name	location	contact	wedding_id
1	St. Clement Church	Skopje	contact@…	1
2	St. Panteleimon	Nerezi	info@…	2

Lossless Join Test

R11.1 ∩ R12 = {church_id, church_name, location, contact, wedding_id}

Since:

church_id → R12

The decomposition is lossless.

---

R13 – PRIEST

{priest_id} → {name, contact, church_id}

Candidate Key Verification

priest_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

priest_id	name	contact	church_id
1	Father Nikola	+38970123456	1
2	Father Petar	+38970222333	2

Lossless Join Test

R12.1 ∩ R13 = {priest_id, priest_name, priest_contact, church_id}

Since:

priest_id → R13

The decomposition is lossless.

---

R14 – EVENT

{event_id} → {event_type, date, start_time, end_time, status, wedding_id}

Candidate Key Verification

event_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

event_id	event_type	date	start_time	end_time	status	wedding_id
1	Ceremony	2026-06-20	12:00	13:00	scheduled	1
2	Reception	2026-06-20	16:00	23:00	scheduled	1

Lossless Join Test

R13.1 ∩ R14 = {event_id, event_type, date, start_time, end_time, status, wedding_id}

Since:

event_id → R14

The decomposition is lossless.

---

R15 – GUEST

{guest_id} → {first_name, last_name, email, wedding_id}

Candidate Key Verification

guest_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

guest_id	first_name	last_name	email	wedding_id
1	Ana	Markovska	ana.m@…	1
2	Daniel	Stojanov	daniel.s@…	1

Lossless Join Test

R14.1 ∩ R15 = {guest_id, first_name, last_name, email, wedding_id}

Since:

guest_id → R15

The decomposition is lossless.

---

R16 – EVENT_RSVP

{response_id} → {status, response_date, guest_id, event_id}

Candidate Key Verification

response_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

response_id	status	response_date	guest_id	event_id
1	accepted	2026-05-15	1	1
2	accepted	2026-05-15	2	2

Lossless Join Test

R15.1 ∩ R16 = {response_id, status, response_date, guest_id, event_id}

Since:

response_id → R16

The decomposition is lossless.

---

R17 – ATTENDANCE

{attendance_id} → {status, table_number, role, guest_id, event_id}

Candidate Key Verification

attendance_id is the surrogate primary key.

No partial dependencies exist.

Sample Data

attendance_id	status	table_number	role	guest_id	event_id
1	attending	5	Guest	1	2
2	attending	7	Guest	2	2

Lossless Join Test

R16.1 ∩ R17 = {attendance_id, status, table_number, role, guest_id, event_id}

Since:

attendance_id → R17

The decomposition is 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

Transition from 2NF to 3NF

After decomposition into 2NF, each relation is examined for transitive dependencies.

A transitive dependency exists when a non-key attribute depends on another non-key attribute.

Example:

venue_id → type_id → type_name

Here:

• type_name depends on type_id
• type_id depends on venue_id

Therefore type_name does not directly depend on the primary key venue_id.

This violates 3NF.

Resolution

To eliminate the transitive dependency:

• VENUE_TYPE(type_id, type_name) is extracted as a separate relation
• VENUE stores type_id as a foreign key

This preserves all functional dependencies while eliminating transitivity.

Verification of Remaining Relations

All remaining relations are examined and found to contain:

• No partial dependencies
• No transitive dependencies
• Only direct dependencies on the primary key

Therefore all final relations satisfy 3NF.

Boyce-Codd Normal Form (BCNF)

Definition

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

X → Y

X is a superkey.

BCNF Verification

Each final relation satisfies BCNF because:

• Every determinant is a candidate key or superkey
• No dependency violates BCNF conditions

Final Schema Summary

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

Final Conclusion

Through formal normalization analysis, verified functional dependencies, candidate key analysis, and lossless join decomposition tests, the Wedding Planner database schema has been fully normalized.

The normalization process successfully:

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

The final schema consists of 17 relations, all satisfying Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF).