Faza2: R-DIAGRAM DDL FULL.txt

CREATE TABLE Address (
  address_id    SERIAL NOT NULL, 
  street        varchar(255) NOT NULL, 
  street_number int4 NOT NULL, 
  PRIMARY KEY (address_id),
  CHECK (street_number > 0)
);

CREATE TABLE City (
  city_id   SERIAL NOT NULL, 
  city_name varchar(255) NOT NULL, 
  zip_code  int4 NOT NULL UNIQUE, 
  PRIMARY KEY (city_id),
  CHECK (zip_code > 0)
);

CREATE TABLE Section (
  section_id    SERIAL NOT NULL, 
  section_name  varchar(255) NOT NULL, 
  section_price int4 NOT NULL, 
  PRIMARY KEY (section_id),
 CHECK (section_price >= 0)
);

CREATE TABLE Organizer (
  organizer_id   SERIAL NOT NULL, 
  organizer_name varchar(255) NOT NULL, 
  contact        int4 NOT NULL UNIQUE, 
  PRIMARY KEY (organizer_id)
);

CREATE TABLE Refund (
  refund_id     SERIAL NOT NULL, 
  refund_amount int4 NOT NULL , 
  refund_date   date NOT NULL, 
  reason        varchar(255), 
  PRIMARY KEY (refund_id),
  CHECK (refund_amount >= 0)
);

CREATE TABLE User (
  user_id    SERIAL NOT NULL, 
  first_name varchar(255) NOT NULL DEFAULT 'Anonymous', 
  last_name  varchar(255) NOT NULL DEFAULT 'Anonymous', 
  username   varchar(255) NOT NULL UNIQUE DEFAULT 'Anonymous', 
  email      varchar(255) NOT NULL UNIQUE, 
  password   varchar(255) NOT NULL, 
  PRIMARY KEY (user_id),
  
  CONSTRAINT email_check CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
CREATE TABLE Performer (
  pefromer_id    SERIAL NOT NULL, 
  genre          varchar(255) NOT NULL, 
  performer_name varchar(255) NOT NULL, 
  date_of_birth  date NOT NULL, 
  PRIMARY KEY (pefromer_id),
  CHECK (age > 0)
);

CREATE TABLE Sponsor (
  sponsor_id     SERIAL NOT NULL, 
  sponsor_amount int4 NOT NULL , 
  sponsor_name   varchar(255) NOT NULL, 
  PRIMARY KEY (sponsor_id),
  CHECK (sponsor_amount > 0)
);

CREATE TABLE Venue (
  venue_id           SERIAL NOT NULL, 
  venue_name         varchar(255) NOT NULL, 
  capacity           int4 NOT NULL, 
  City_city_id       int4 NOT NULL, 
  Address_address_id int4 NOT NULL, 
  PRIMARY KEY (venue_id),
  CONSTRAINT FKVenueCity FOREIGN KEY (City_city_id) REFERENCES City (city_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKVenueAddress FOREIGN KEY (Address_address_id) REFERENCES Address (address_id) ON DELETE CASCADE ON UPDATE CASCADE,
CHECK (capacity > 0)
);

CREATE TABLE Discount (
  discount_id        SERIAL NOT NULL, 
  "percent"          int4 CHECK ("percent" >= 0 AND "percent" <= 100), 
  Section_section_id varchar(255) NOT NULL, 
  Sectionsection_id  int4 NOT NULL, 
  PRIMARY KEY (discount_id),
  CONSTRAINT FKDiscountSection FOREIGN KEY (Sectionsection_id) REFERENCES Section (section_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE User_verification (
  user_verification_id SERIAL NOT NULL, 
  status               varchar(255) NOT NULL, 
  verification_date    date NOT NULL UNIQUE, 
  User_user_id         int4 NOT NULL, 
  PRIMARY KEY (user_verification_id),
  CONSTRAINT FKUser_verifUser FOREIGN KEY (User_user_id) REFERENCES User (user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Organizer_Venue (
  Organizer_organizer_id int4 NOT NULL, 
  Venue_venue_id         int4 NOT NULL, 
  PRIMARY KEY (Organizer_organizer_id, Venue_venue_id),
  CONSTRAINT FKOrgVenue_Org FOREIGN KEY (Organizer_organizer_id) REFERENCES Organizer (organizer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKOrgVenue_Venue FOREIGN KEY (Venue_venue_id) REFERENCES Venue (venue_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Parking (
  parking_id     SERIAL NOT NULL, 
  capacity       int4 NOT NULL CHECK (capacity > 0), 
  availability   int4 NOT NULL CHECK (availability >= 0 AND availability <= capacity), 
  reservation    varchar(255), 
  Venue_venue_id int4 NOT NULL, 
  PRIMARY KEY (parking_id),
  CONSTRAINT FKParkingVenue FOREIGN KEY (Venue_venue_id) REFERENCES Venue (venue_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Payment (
  payment_id           SERIAL NOT NULL, 
  payment_type         varchar(255) NOT NULL, 
  amount               int4 NOT NULL CHECK (amount >= 0), 
  Discount_discount_id int4 NOT NULL, 
  Refund_refund_id     int4 NOT NULL, 
  PRIMARY KEY (payment_id),
  CONSTRAINT FKPaymentDiscount FOREIGN KEY (Discount_discount_id) REFERENCES Discount (discount_id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT FKPaymentRefund FOREIGN KEY (Refund_refund_id) REFERENCES Refund (refund_id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Event (
  event_id               SERIAL NOT NULL, 
  title                  varchar(255) NOT NULL, 
  description            varchar(255), 
  "date"                 date NOT NULL CHECK ("date" >= CURRENT_DATE), 
  event_type             varchar(255) NOT NULL, 
  Organizer_organizer_id int4 NOT NULL, 
  Venue_venue_id         int4 NOT NULL, 
  PRIMARY KEY (event_id),
  CONSTRAINT FKEventOrganizer FOREIGN KEY (Organizer_organizer_id) REFERENCES Organizer (organizer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKEventVenue FOREIGN KEY (Venue_venue_id) REFERENCES Venue (venue_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Setlist (
  setlist_id            SERIAL NOT NULL, 
  songs                 varchar(255) NOT NULL, 
  songs_duration        int4 NOT NULL CHECK (songs_duration > 0), 
  Performer_pefromer_id int4 NOT NULL, 
  PRIMARY KEY (setlist_id),
  CONSTRAINT FKSetlistPerformer FOREIGN KEY (Performer_pefromer_id) REFERENCES Performer (pefromer_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Media (
  media_id       SERIAL NOT NULL, 
  media_type     varchar(255) NOT NULL, 
  url            varchar(255) NOT NULL UNIQUE, 
  Event_event_id int4 NOT NULL, 
  PRIMARY KEY (media_id),
  CONSTRAINT FKMediaEvent FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Staff (
  staff_id       SERIAL NOT NULL, 
  staff_name     varchar(255) NOT NULL,
  staff_surname  varchar(255) NOT NULL,
  role           varchar(255) NOT NULL, 
  phone          int4 NOT NULL UNIQUE, 
  experience     varchar(255) NOT NULL, 
  Event_event_id int4 NOT NULL, 
  PRIMARY KEY (staff_id),
  CONSTRAINT FKStaffEvent FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Performance (
  performance_id SERIAL NOT NULL, 
  start_time     int4 NOT NULL, 
  end_time       int4 NOT NULL CHECK (end_time > start_time), 
  Event_event_id int4 NOT NULL, 
  PRIMARY KEY (performance_id),
  CONSTRAINT FKPerformanceEvent FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Sponsor_Event (
  Sponsor_sponsor_id int4 NOT NULL, 
  Event_event_id     int4 NOT NULL, 
  PRIMARY KEY (Sponsor_sponsor_id, Event_event_id),
  CONSTRAINT FKSponsor_Ev_Sponsor FOREIGN KEY (Sponsor_sponsor_id) REFERENCES Sponsor (sponsor_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKSponsor_Ev_Event FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Review (
  review_id      SERIAL NOT NULL, 
  feedback       varchar(255), 
  grade          int4 CHECK (grade BETWEEN 1 AND 10), 
  User_user_id   int4 NOT NULL, 
  Event_event_id int4 NOT NULL, 
  PRIMARY KEY (review_id),
  CONSTRAINT FKReviewUser FOREIGN KEY (User_user_id) REFERENCES User (user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  CONSTRAINT FKReviewEvent FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Reservation (
  reservation_id     SERIAL NOT NULL, 
  reservation_time   varchar(255) NOT NULL, 
  status             varchar(255) NOT NULL, 
  User_user_id       int4 NOT NULL, 
  Payment_payment_id int4 NOT NULL, 
  PRIMARY KEY (reservation_id),
  CONSTRAINT FKReservatioUser FOREIGN KEY (User_user_id) REFERENCES User (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKReservatioPayment FOREIGN KEY (Payment_payment_id) REFERENCES Payment (payment_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Performer_Performance (
  Performer_pefromer_id      int4 NOT NULL, 
  Performance_performance_id int4 NOT NULL, 
  PRIMARY KEY (Performer_pefromer_id, Performance_performance_id),
  CONSTRAINT FKPerfPerf_Performer FOREIGN KEY (Performer_pefromer_id) REFERENCES Performer (pefromer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKPerfPerf_Performance FOREIGN KEY (Performance_performance_id) REFERENCES Performance (performance_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Ticket (
  ticket_id                  SERIAL NOT NULL, 
  serial_number              int4 NOT NULL UNIQUE, 
  is_used                    int4 NOT NULL CHECK (is_used IN (0, 1)), 
  price                      int4 NOT NULL CHECK (price > 0), 
  qr_code                    varchar(255) NOT NULL UNIQUE, 
  Event_event_id             int4 NOT NULL, 
  Reservation_reservation_id int4 NOT NULL, 
  Refund_refund_id           int4 NOT NULL, 
  Section_section_id         int4 NOT NULL, 
  PRIMARY KEY (ticket_id),
  CONSTRAINT FKTicketEvent FOREIGN KEY (Event_event_id) REFERENCES Event (event_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKTicketReservation FOREIGN KEY (Reservation_reservation_id) REFERENCES Reservation (reservation_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FKTicketRefund FOREIGN KEY (Refund_refund_id) REFERENCES Refund (refund_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  CONSTRAINT FKTicketSection FOREIGN KEY (Section_section_id) REFERENCES Section (section_id) ON DELETE CASCADE ON UPDATE CASCADE
);