Changes between Version 53 and Version 54 of DatabaseCreation
- Timestamp:
- 06/30/26 21:54:08 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
DatabaseCreation
v53 v54 14 14 password VARCHAR(255) NOT NULL, 15 15 registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 16 is_active BOOLEAN NOT NULL DEFAULT TRUE, 16 17 17 18 CONSTRAINT user_email_check CHECK ( … … 44 45 last_name VARCHAR(255) NOT NULL, 45 46 date_of_birth DATE NOT NULL, 47 is_active BOOLEAN NOT NULL DEFAULT TRUE, 46 48 47 49 CONSTRAINT fk_regular_user FOREIGN KEY (user_id) REFERENCES "User" (user_id) 50 ON DELETE CASCADE 51 ON UPDATE CASCADE 52 ); 53 54 }}} 55 56 57 === `User_Card` 58 59 {{{ 60 61 CREATE TABLE "User_Card" ( 62 card_id BIGSERIAL PRIMARY KEY, 63 user_id BIGINT NOT NULL, 64 provider_token TEXT NOT NULL, 65 card_brand VARCHAR(255), 66 last_four VARCHAR(4), 67 68 CONSTRAINT uq_user_provider_token UNIQUE (user_id, provider_token), 69 CONSTRAINT fk_card_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id) 48 70 ON DELETE CASCADE 49 71 ON UPDATE CASCADE … … 85 107 min_age INTEGER NOT NULL, 86 108 created_by BIGINT NOT NULL, 109 is_active BOOLEAN NOT NULL DEFAULT TRUE, 87 110 88 111 CONSTRAINT fk_event_type FOREIGN KEY (type_id) REFERENCES "Event_Type" (type_id) … … 145 168 }}} 146 169 170 147 171 === `Event_Happening`, `Event_Period` 148 172 … … 188 212 }}} 189 213 214 190 215 === `Event_Happening_Performer` 191 216 … … 264 289 order_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0, 265 290 user_id BIGINT NOT NULL, 291 card_id BIGINT, 266 292 267 293 CONSTRAINT fk_order_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id) 294 ON DELETE RESTRICT 295 ON UPDATE CASCADE, 296 CONSTRAINT fk_order_card FOREIGN KEY (card_id) REFERENCES "User_Card" (card_id) 268 297 ON DELETE RESTRICT 269 298 ON UPDATE CASCADE … … 338 367 339 368 CREATE OR REPLACE VIEW "Venue_Layout" AS 340 SELECT v.venue_id, 341 v.name AS venue_name, 342 s.section_id, 343 s.name AS section_name, 344 st.seat_id, 345 st.row_number, 346 st.seat_number 369 SELECT v.venue_id, v.name AS venue_name, s.section_id, s.name AS section_name, st.seat_id, st.row_number, st.seat_number 347 370 FROM "Venue" v 348 JOIN "Section" s ON v.venue_id = s.venue_id 349 JOIN "Seat" st ON s.section_id = st.section_id; 350 351 }}} 371 JOIN "Section" s ON v.venue_id = s.venue_id 372 JOIN "Seat" st ON s.section_id = st.section_id; 373 374 }}} 375 352 376 353 377 === `User_Tickets` … … 358 382 359 383 CREATE OR REPLACE VIEW "User_Tickets" AS 360 SELECT u.user_id, 361 u.username, 362 toi.order_item_id, 363 t.ticket_id, 364 e.event_id, 365 e.name AS event_name, 366 eh.event_time, 367 toi.qr_code, 368 toi.item_price AS price_paid, 369 tri.refund_item_id, 370 tr.refund_time 384 SELECT u.user_id, u.username, toi.order_item_id, t.ticket_id, e.event_id, e.name AS event_name, eh.event_time, 385 toi.qr_code, toi.item_price AS price_paid, tri.refund_item_id, tr.refund_time 371 386 FROM "User" u 372 JOIN "Regular_User" ru ON u.user_id = ru.user_id 373 JOIN "Ticket_Order" o ON ru.user_id = o.user_id 374 JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id 375 JOIN "Ticket" t ON toi.ticket_id = t.ticket_id 376 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id 377 JOIN "Event" e ON eh.event_id = e.event_id 378 LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id 379 LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id; 380 381 }}} 387 JOIN "Regular_User" ru ON u.user_id = ru.user_id 388 JOIN "Ticket_Order" o ON ru.user_id = o.user_id 389 JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id 390 JOIN "Ticket" t ON toi.ticket_id = t.ticket_id 391 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id 392 JOIN "Event" e ON eh.event_id = e.event_id 393 LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id 394 LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id 395 WHERE u.is_active = TRUE; 396 397 }}} 398 382 399 383 400 === `Future_Events` … … 388 405 389 406 CREATE OR REPLACE VIEW "Future_Events" AS 390 SELECT 391 e.event_id, 392 e.name AS event_name, 393 eh.event_happening_id, 394 eh.event_time, 395 v.venue_id, 396 v.name AS venue_name, 397 v.address_street AS street, 398 v.address_city AS city, 399 v.address_country AS country 407 SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.venue_id, v.name AS venue_name, 408 v.address_street AS street, v.address_city AS city, v.address_country AS country 400 409 FROM "Event" e 401 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 402 JOIN "Venue" v ON eh.venue_id = v.venue_id 403 WHERE eh.event_time > CURRENT_TIMESTAMP; 404 405 }}} 410 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 411 JOIN "Venue" v ON eh.venue_id = v.venue_id 412 WHERE eh.event_time > CURRENT_TIMESTAMP AND e.is_active = TRUE; 413 414 }}} 415 406 416 407 417 === `Available_Tickets` … … 412 422 413 423 CREATE OR REPLACE VIEW "Available_Tickets" AS 414 SELECT 415 t.ticket_id, 416 ROUND( 417 t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0), 418 2 419 ) AS price, 420 e.event_id, 421 e.name AS event_name, 422 eh.event_happening_id, 423 eh.event_time, 424 v.name AS venue_name, 425 s.name AS section_name, 426 st.row_number, 427 st.seat_number 424 SELECT t.ticket_id, ROUND(t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0), 2) AS price, 425 e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name, 426 s.name AS section_name, st.row_number, st.seat_number 428 427 FROM "Ticket" t 429 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id 430 JOIN "Event" e ON eh.event_id = e.event_id 431 JOIN "Venue" v ON eh.venue_id = v.venue_id 432 JOIN "Seat" st ON t.seat_id = st.seat_id 433 JOIN "Section" s ON st.section_id = s.section_id 434 LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id 435 AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date 436 WHERE t.is_available = TRUE; 437 438 }}} 428 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id 429 JOIN "Event" e ON eh.event_id = e.event_id 430 JOIN "Venue" v ON eh.venue_id = v.venue_id 431 JOIN "Seat" st ON t.seat_id = st.seat_id 432 JOIN "Section" s ON st.section_id = s.section_id 433 LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id 434 AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date 435 WHERE t.is_available = TRUE AND e.is_active = TRUE; 436 437 }}} 438 439 439 440 440 === `Event_Overall_Ratings` … … 445 445 446 446 CREATE MATERIALIZED VIEW "Event_Overall_Ratings" AS 447 SELECT 448 e.event_id, 449 e.name AS event_name, 450 eh.event_happening_id, 451 eh.event_time, 452 COUNT(ehr.rating_id) AS total_reviews, 453 COALESCE(ROUND(AVG(ehr.rating), 2), 0.00) AS average_rating 447 SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, 448 COUNT(ehr.rating_id) AS total_reviews, COALESCE(ROUND(AVG(ehr.rating), 2), 0.00) AS average_rating 454 449 FROM "Event" e 455 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 456 LEFT JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id 450 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 451 LEFT JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id 452 WHERE e.is_active = TRUE 457 453 GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time; 458 454 459 455 }}} 460 456 457 461 458 === `User_Order_History` 462 459 … … 466 463 467 464 CREATE MATERIALIZED VIEW "User_Order_History" AS 468 SELECT u.user_id, 469 u.username, 470 o.order_id, 471 o.order_time, 472 o.order_amount, 465 SELECT u.user_id, u.username, o.order_id, o.order_time, o.order_amount, 473 466 COALESCE(items.total_items_ordered, 0) AS total_items_ordered, 474 467 COALESCE(items.total_items_refunded, 0) AS total_items_refunded, 475 468 COALESCE(items.total_amount_refunded, 0.00) AS total_amount_refunded 476 469 FROM "User" u 477 JOIN "Regular_User" ru ON u.user_id = ru.user_id478 JOIN "Ticket_Order" o ON ru.user_id = o.user_id479 LEFT JOIN (480 SELECT481 toi.order_id,482 COUNT(toi.order_item_id) AS total_items_ordered,483 COUNT(tri.refund_item_id) AS total_items_refunded,484 SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded485 FROM "Ticket_Order_Item" toi486 LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id487 GROUP BY toi.order_id 488 ) items ON o.order_id = items.order_id; 489 490 }}} 470 JOIN "Regular_User" ru ON u.user_id = ru.user_id 471 JOIN "Ticket_Order" o ON ru.user_id = o.user_id 472 LEFT JOIN ( 473 SELECT toi.order_id, COUNT(toi.order_item_id) AS total_items_ordered, 474 COUNT(tri.refund_item_id) AS total_items_refunded, 475 SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded 476 FROM "Ticket_Order_Item" toi 477 LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id 478 GROUP BY toi.order_id 479 ) items ON o.order_id = items.order_id 480 WHERE u.is_active = TRUE; 481 482 }}} 483 491 484 492 485 === `Venue_Occupancy_Report` … … 497 490 498 491 CREATE MATERIALIZED VIEW "Venue_Occupancy_Report" AS 499 SELECT e.event_id, 500 e.name AS event_name, 501 eh.event_happening_id, 502 eh.event_time, 503 v.name AS venue_name, 504 v.number_of_seats AS total_venue_capacity, 505 COALESCE(stats.sold_count, 0) AS tickets_sold, 492 SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name, 493 v.number_of_seats AS total_venue_capacity, COALESCE(stats.sold_count, 0) AS tickets_sold, 506 494 ROUND((COALESCE(stats.sold_count, 0)::NUMERIC / v.number_of_seats::NUMERIC) * 100, 2) AS occupancy_percentage 507 495 FROM "Event" e 508 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 509 JOIN "Venue" v ON eh.venue_id = v.venue_id 510 LEFT JOIN ( 511 SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count 512 FROM "Ticket_Order_Item" toi 513 JOIN "Ticket" t ON toi.ticket_id = t.ticket_id 514 GROUP BY t.event_happening_id 515 ) stats ON eh.event_happening_id = stats.event_happening_id 496 JOIN "Event_Happening" eh ON e.event_id = eh.event_id 497 JOIN "Venue" v ON eh.venue_id = v.venue_id 498 LEFT JOIN ( 499 SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count 500 FROM "Ticket_Order_Item" toi 501 JOIN "Ticket" t ON toi.ticket_id = t.ticket_id 502 GROUP BY t.event_happening_id 503 ) stats ON eh.event_happening_id = stats.event_happening_id 504 WHERE e.is_active = TRUE 516 505 GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time, v.name, v.number_of_seats, stats.sold_count; 517 506
