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.

---