Initial Unnormalized Relation (UNF)

The system initially stores all information in a single relation:

USER_WEDDING_VENUE(user_id, user_first_name, user_last_name, user_email, wedding_id, wedding_date, wedding_budget, venue_id, venue_name, venue_type, venue_capacity, booking_id, booking_date, start_time, end_time)

Example of UNF Data

user_id

user_first_name

user_last_name

user_email

wedding_id

wedding_date

wedding_budget

venue_id

venue_name

venue_type

venue_capacity

booking_id

booking_date

start_time

end_time

U01

Ana

Petrova

ana@…

W01

2026-06-12

15000

V01

Grand Hall

Ballroom

300

B01

2026-06-12

18:00

23:00

U01

Ana

Petrova

ana@…

W01

2026-06-12

15000

V01

Garden Venue

Outdoor

200

B02

2026-06-12

12:00

16:00

U02

Marko

Iliev

marko@…

W01

2026-09-20

20000

V01

Grand Hall

Ballroom

300

B03

2026-09-20

19:00

01:00

Why This Relation Is UNF

-Repeating groups exist:

--User and wedding data are repeated for each venue booking

-Redundancy:

--Venue details (name, type, capacity) are duplicated across multiple rows --User information appears multiple times

-Update anomalies:

--Changing a venue name requires updating multiple rows --Changing a user email risks inconsistent data

-Insertion anomalies:

--A venue cannot be stored unless it is booked --A user cannot exist without a wedding

-Deletion anomalies:

--Deleting a booking may remove venue or wedding information

This relation contains attributes describing multiple entities (User, Wedding, Venue, Booking). Storing all data together leads to redundancy and update anomalies.

Identified Functional Dependencies (FDs):

-user_id → user_first_name, user_last_name, user_email

-wedding_id → wedding_date, wedding_budget, user_id

-venue_id → venue_name, venue_type, venue_capacity

-booking_id → venue_id, wedding_id, booking_date, start_time, end_time

Step 1: Decomposition to 1NF (First Normal Form)

Definition of 1NF:

-A relation is in 1NF if all attributes contain atomic values

-No repeating groups or multivalued attributes exist

-Each row is uniquely identifiable by a key

The relation is decomposed based on entity responsibility while preserving all data.

R1: USER

Table	Attributes
R1	user_id, user_first_name, user_last_name, user_email

Primary Key: -user_id

Functional Dependency: -user_id → user_first_name, user_last_name, user_email

Justification:

-All attributes are atomic

-All attributes depend only on user_id

-user_id uniquely identifies a system user

-No repeating groups exist

Conclusion: -R1 satisfies 1NF

R2: WEDDING

Table	Attributes
R2	wedding_id, wedding_date, wedding_budget, user_id

Primary Key: -wedding_id

Foreign Key: -user_id → USER(user_id)

Functional Dependency: -wedding_id → wedding_date, wedding_budget, user_id

Justification:

-Each wedding is uniquely identified by wedding_id

-user_id represents ownership of the wedding

-All non-key attributes depend solely on wedding_id

Conclusion: -R2 satisfies 1NF

R3: VENUE

Table	Attributes
R3	venue_id, venue_name, venue_type, venue_capacity

Primary Key: -venue_id

Functional Dependency: -venue_id → venue_name, venue_type, venue_capacity

Justification:

-Venue attributes describe a single independent entity

-Attributes are static and not dependent on other relations

-venue_id uniquely identifies a venue

Conclusion: -R3 satisfies 1NF

R4: VENUE_BOOKING

Table	Attributes
R4	booking_id, venue_id, wedding_id, booking_date, start_time, end_time

Primary Key: -booking_id

Foreign Keys: -venue_id → VENUE(venue_id) -wedding_id → WEDDING(wedding_id)

Functional Dependency: -booking_id → venue_id, wedding_id, booking_date, start_time, end_time

Justification:

-A booking represents a reservation of a venue for a specific wedding

-booking_id uniquely identifies each reservation

-All attributes depend directly on booking_id

Conclusion: -R4 satisfies 1NF