Фаза 2: DDL, податоци, погледи

Оваа фаза опфаќа генерирање на DDL за релациониот модел, полнење на базата со реалистични податоци и креирање на погледи (views) за потребите на апликацијата.

Фаза 2А: DDL и ограничувања

Истакнати ограничувања (Constraints):

• Додадени се проверки за исправност на форматот на email адресите со користење на регуларни изрази: CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$').
• Поставени се логички ограничувања на ниво на колони, како што се проверка на возраст кај изведувачите CHECK (date_of_birth <= CURRENT_DATE) и проверка на лимитот за попуст CHECK (percent >= 0 AND percent <= 100).
• Дефинирани се DEFAULT вредности ('Anonymous') за корисничките имиња и презимиња во случај на недостаток на внес.

Фаза 2Б: Податоци и погледи

Податоци: Базата е наполнета со реалистични податоци со користење на Python скрипта и Faker библиотека. Големината на табелите го одразува реалното користење на продукциската апликација. Некои од најголемите табели во системот се:

• Табела Ticket: ~8.900.000 редици
• Табела Reservation: ~5.000.000 редици

Погледи (Views): Креирани се погледи кои ги спојуваат клучните табели за приказ во апликацијата. SQL кодот за креирање на овие погледи се наоѓа во базата.

1. new_event_ticket_sales_status

• Опис: Овој поглед врши агрегација на продадени билети по настан и сектор. Се користи за генерирање извештаи за пополнетост на капацитетот на објектите.

  CREATE OR REPLACE VIEW public.new_event_ticket_sales_status
  AS WITH preaggregatedtickets AS (
           SELECT ticket.event_event_id,
              ticket.section_section_id,
              count(ticket.ticket_id) AS sold_per_section
             FROM ticket
            WHERE ticket.refund_refund_id IS NULL
            GROUP BY ticket.event_event_id, ticket.section_section_id
          )
   SELECT e.event_id,
      e.title,
      v.capacity AS total_capacity,
      sum(pat.sold_per_section) OVER (PARTITION BY e.event_id)::bigint AS total_sold,
      (v.capacity::numeric - sum(pat.sold_per_section) OVER (PARTITION BY e.event_id))::bigint AS tickets_remaining,
      s.section_name,
      COALESCE(pat.sold_per_section, 0::bigint) AS sold_per_section
     FROM event e
       JOIN venue v ON e.venue_venue_id = v.venue_id
       LEFT JOIN preaggregatedtickets pat ON e.event_id = pat.event_event_id
       LEFT JOIN section s ON pat.section_section_id = s.section_id;
  

2. new_user_ticket_history

• Опис: Погледот ги поврзува корисниците, резервациите, билетите и настаните за да прикаже детална историја на купени билети, цени и статус на настаните. Клучен е за приказ на корисничкиот профил.

  CREATE OR REPLACE VIEW public.new_user_ticket_history
  AS SELECT u.user_id,
      u.username,
      t.ticket_id,
      t.price,
      t.is_used,
      e.title AS event_title,
      e.date AS event_date,
      r.status AS reservation_status,
          CASE
              WHEN e.date < CURRENT_DATE THEN 'Past Event'::text
              ELSE 'Upcoming Event'::text
          END AS event_status
     FROM "User" u
       JOIN reservation r ON u.user_id = r.user_user_id
       JOIN ticket t ON r.reservation_id = t.reservation_reservation_id
       JOIN event e ON t.event_event_id = e.event_id;
  

3. new_active_events_3_months

• Опис: Погледот служи за брзо дофаќање на сите активни настани во наредните 3 месеци и се користи за приказ на претстојните случувања на почетната страна.

  CREATE OR REPLACE VIEW public.new_active_events_3_months
  AS SELECT e.event_id,
      e.title,
      e.event_type,
      e.date,
      v.venue_name,
      c.city_name
     FROM event e
       JOIN venue v ON e.venue_venue_id = v.venue_id
       JOIN city c ON v.city_city_id = c.city_id
    WHERE e.date >= CURRENT_DATE AND e.date <= (CURRENT_DATE + '3 mons'::interval)
    ORDER BY e.date;