Logical and Physical Design

Relational Schema (Mapping Transformation)

Diagram

Notation

• primary keys – bold and underlined
• NOT NULL attributes – bold
• foreign keys – marked with * after the attribute name and the referenced table in parentheses
• other attributes – no special marking

Tables

• USER (user_id, first_name, last_name, email, phone_number, gender, birthday)

• WEDDING (wedding_id, date, budget, notes, type, status, user_id* (USER))

• CHURCH (church_id, name, location, contact, wedding_id* (WEDDING))

• PRIEST (priest_id, name, contact, church_id* (CHURCH))

• EVENT (event_id, event_type, date, start_time, end_time, status, wedding_id* (WEDDING))

• GUEST (guest_id, first_name, last_name, email, wedding_id* (WEDDING))

• EVENT_RSVP (response_id, status, response_date, guest_id* (GUEST), event_id* (EVENT))

• ATTENDANCE (attendance_id, status, table_number, role, guest_id* (GUEST), event_id* (EVENT))

• VENUE_TYPE (type_id, type_name)

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

• VENUE_BOOKING (booking_id, date, start_time, end_time, status, price, venue_id* (VENUE), wedding_id* (WEDDING))

• PHOTOGRAPHER (photographer_id, name, email, phone_number, price_per_hour)

• PHOTOGRAPHER_BOOKING (booking_id, date, start_time, end_time, status, photographer_id* (PHOTOGRAPHER), wedding_id* (WEDDING))

• BAND (band_id, band_name, genre, equipment, phone_number, price_per_hour)

• BAND_BOOKING (booking_id, date, start_time, end_time, status, band_id* (BAND), wedding_id* (WEDDING))

• REGISTRAR (registrar_id, name, contact, location, working_hours)

• REGISTRAR_BOOKING (booking_id, date, start_time, end_time, status, registrar_id* (REGISTRAR))

DDL Script for Table Creation and Deletion

The DDL script implements the relational schema described above. It defines all tables, primary and foreign keys, as well as constraints required to ensure data integrity.

Attachment(schema_creation.sql)

DML Script for Data Population

The DML script contains sample data used for testing the system and demonstrating the relationships between the tables.

Attachment(data_load.sql)