DDL_queries.sql

--1 MOVIE TABLE>
CREATE TABLE MOVIES(
  movieId INTEGER PRIMARY KEY,
  movieName VARCHAR(100) NOT NULL,
  releaseDate Date NOT NULL,
  duration NUMBER(3) NOT NULL,
  genre NUMBER(2) NOT NULL,
  maturity VARCHAR(6) NOT NULL,
  summary VARCHAR(1000),
  trailer VARCHAR(100) UNIQUE,
  image VARCHAR(30) UNIQUE
  );


--2) MOVIES_IN_THEATER
  CREATE TABLE MOVIES_IN_THEATER(
    movieId INTEGER PRIMARY KEY,
    premereDate DATE NULL,
    now_playing NUMBER(1) NOT NULL,
    CONSTRAINT fk_PerMOVIES_IN_THEATERS FOREIGN KEY(movieId)
    REFERENCES MOVIES(movieId)
    ON DELETE CASCADE
  );


--3)NOW_SHOWING_MOVIES
CREATE TABLE NOW_SHOWING_MOVIES(
  movieId INTEGER NOT NULL,
  format NUMBER(1) NOT NULL,
  CONSTRAINT fk_PerNOW_SHOWING_MOVIES FOREIGN KEY(movieId)
  REFERENCES MOVIES_IN_THEATER(movieId)
  ON DELETE CASCADE,
	CONSTRAINT pk_NOW_SHOWING_MOVIES PRIMARY KEY(movieId, format)
);

--- || 'Changing' the DATATYPE of the column ||---
ALTER TABLE NOW_SHOWING_MOVIES ADD (FORMAT_ CHAR(1));
UPDATE NOW_SHOWING_MOVIES
  SET FORMAT_ = TO_CHAR(FORMAT);
ALTER TABLE NOW_SHOWING_MOVIES DROP COLUMN FORMAT CASCADE CONSTRAINTS;
ALTER TABLE NOW_SHOWING_MOVIES RENAME COLUMN FORMAT_ TO FORMAT;
---|| Making the new column PRIMARY KEY again ||---
ALTER TABLE NOW_SHOWING_MOVIES ADD 
CONSTRAINT pk_NOW_SHOWING_MOVIES PRIMARY KEY(movieId, format)
USING INDEX;
---|| ||---
--4) CINEMA_HALLS
  CREATE TABLE CINEMA_HALLS(
    hallNo NUMBER(2) PRIMARY KEY,
    capacity NUMBER(3) NOT NULL,
    upper_seats NUMBER(3) NOT NULL,
    lower_seats NUMBER(4) NOT NULL,
    category NUMBER(1) NOT NULL
  );

--5)SHOW_TIMES
CREATE TABLE SHOW_TIMES(
  showDateTime DATE NOT NULL,
  hallNo NUMBER(2) NOT NULL,
  movieId INTEGER NOT NULL,
  format NUMBER(1) NOT NULL,
	CONSTRAINT pk_SHOW_TIMES PRIMARY KEY(showDateTime, hallNo) ENABLE
);

ALTER TABLE SHOW_TIMES
ADD CONSTRAINT fk_PerSHOW_TIMES FOREIGN KEY(movieId, format)
  REFERENCES NOW_SHOWING_MOVIES(movieId, format)
  ON DELETE CASCADE;

ALTER TABLE SHOW_TIMES
ADD CONSTRAINT fk_Per1SHOW_TIMES FOREIGN KEY(hallNo)
  REFERENCES CINEMA_HALLS(hallNo)
  ON DELETE CASCADE;


--6)CUSTOMERS
CREATE TABLE CUSTOMERS(
  customerId INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  emailId VARCHAR(100) NOT NULL UNIQUE,
  phone VARCHAR(11) NOT NULL UNIQUE
  );




--7)TICKET_PRICES
CREATE TABLE TICKET_PRICES(
  category NUMBER(1) NOT NULL,
  format NUMBER(1) NOT NULL,
  price DOUBLE PRECISION NOT NULL,
	CONSTRAINT pk_TICKET_PRICES PRIMARY KEY(category, format)
);

--8)PURCHASE_TICKETS
CREATE TABLE PURCHASE_TICKETS(
  purchaseId INTEGER PRIMARY KEY,
  customerId INTEGER NOT NULL,
  showDateTime DATE NOT NULL,
  hallNo NUMBER(2) NOT NULL,
  category NUMBER(1) NOT NULL,
  format NUMBER(1) NOT NULL,
  totalTickets INTEGER NOT NULL,
  totalCost DOUBLE PRECISION NOT NULL,
  purchaseDate DATE NOT NULL
);

ALTER TABLE PURCHASE_TICKETS
ADD movieId INTEGER CONSTRAINT PURCHASE_TICKETS_movieName_fk REFERENCES MOVIES(movieId) ON DELETE CASCADE;

ALTER TABLE PURCHASE_TICKETS
ADD CONSTRAINT fk_PerPURCHASE_TICKETS FOREIGN KEY(customerId)
  REFERENCES CUSTOMERS(customerId)
  ON DELETE CASCADE;

ALTER TABLE PURCHASE_TICKETS
ADD CONSTRAINT fk_Per1PURCHASE_TICKETS FOREIGN KEY(showDateTime, hallNo)
  REFERENCES SHOW_TIMES(showDateTime, hallNo)
  ON DELETE CASCADE;

ALTER TABLE PURCHASE_TICKETS
ADD CONSTRAINT fk_Per2PURCHASE_TICKETS FOREIGN KEY(category, format)
  REFERENCES TICKET_PRICES(category, format)
  ON DELETE CASCADE;

--9)BOOKED_SEATS
CREATE TABLE BOOKED_SEATS(
  seatNo INTEGER NOT NULL,
  showDateTime DATE NOT NULL,
  hallNo NUMBER(2) NOT NULL,
  purchaseId INTEGER NOT NULL,
  CONSTRAINT fk_PerBOOKED_SEATS FOREIGN KEY(purchaseId)
  REFERENCES PURCHASE_TICKETS(purchaseId)
  ON DELETE CASCADE,
  CONSTRAINT fk_Per1BOOKED_SEATS FOREIGN KEY(showDateTime, hallNo)
  REFERENCES SHOW_TIMES(showDateTime, hallNo)
  ON DELETE CASCADE,
  CONSTRAINT pk_PerBOOKED_SEATS PRIMARY KEY(seatNo, showDateTime, hallNo)
);