Changes between Version 15 and Version 16 of P2

Timestamp:

02/04/26 20:35:55 (5 days ago)

Author:

193284

Comment:

--

P2

@@ -1 @@
-= Logical and Physical Design
+= Phase P2: Logical and Physical Design (DDL & DML)
 
-== Relational Schema (Mapping Transformation)
+== Relational Schema
 
-== Diagram
+The relational schema was derived directly from the ER model using standard
+ER-to-relational mapping rules.
+
+Each strong entity is mapped to a separate table.
+Each relationship is mapped based on its cardinality.
+Associative tables are introduced for all many-to-many relationships.
+
+The resulting schema consists of the following relations:
+
+USER(user_id, first_name, last_name, email, phone_number, gender, birthday)
+
+WEDDING(wedding_id, date, budget, notes, type, status, user_id*)
+
+EVENT(event_id, event_type, date, start_time, end_time, status, wedding_id*)
+
+GUEST(guest_id, first_name, last_name, email, wedding_id*)
+
+EVENT_RSVP(response_id, status, response_date, guest_id*, event_id*)
+
+ATTENDANCE(attendance_id, status, table_number, role, guest_id*, event_id*)
+
+VENUE_TYPE(type_id, type_name)
+
+VENUE(venue_id, name, location, city, address, capacity, menu,
+      phone_number, price_per_guest, type_id*)
+
+VENUE_BOOKING(booking_id, date, start_time, end_time, status, price,
+              venue_id*, wedding_id*)
+
+PHOTOGRAPHER(photographer_id, name, email, phone_number, price_per_hour)
+
+PHOTOGRAPHER_BOOKING(booking_id, date, start_time, end_time, status,
+                     photographer_id*, wedding_id*)
+
+BAND(band_id, band_name, genre, equipment, phone_number, price_per_hour)
+
+BAND_BOOKING(booking_id, date, start_time, end_time, status,
+             band_id*, wedding_id*)
+
+CHURCH(church_id, name, location, contact, wedding_id*)
+
+PRIEST(priest_id, name, contact, church_id*)
+
+REGISTRAR(registrar_id, name, contact, location, working_hours)
+
+REGISTRAR_BOOKING(booking_id, date, start_time, end_time, status, registrar_id*)
+
+(* denotes foreign keys)
+
+== Relational Diagram
+
+The relational diagram was generated directly from the implemented database schema
+using DBeaver, ensuring full consistency with the DDL implementation.
+
 [[Image(final_version.png, width=100%)]]
 
-=== Notation
+== DDL Script
 
-* 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
+The DDL script defines the complete database structure, including all tables,
+primary keys, foreign keys, and integrity constraints.
 
-=== Tables
+[[attachment:schema_creation.sql]]
 
-* USER (**__user_id__**, **first_name**, **last_name**, **email**, phone_number, gender, birthday)
+== DML Script
 
-* WEDDING (**__wedding_id__**, **date**, budget, notes, type, status, **user_id*** (USER))
+The DML script populates the database with sample data that respects all defined
+constraints and demonstrates correct relationships between entities.
 
-* 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)]]
+[[attachment:data_load.sql]]