DROP TABLE IF EXISTS detailed_ticket_revenue_fact_table,
officer_dim,
calendar_dim;



CREATE TABLE calendar_dim (
  CalendarKey INT NOT NULL AUTO_INCREMENT,
  Full_Date INT NOT NULL,
  Month VARCHAR(15) NOT NULL,
  Year VARCHAR(15) NOT NULL,
  PRIMARY KEY (CalendarKey)
);



CREATE TABLE officer_dim (
  OfficerKey INT NOT NULL AUTO_INCREMENT,
  OfficerID INT NOT NULL,
  OfficerName VARCHAR(15) NOT NULL,
  OfficerRank VARCHAR(15) NOT NULL,
  PRIMARY KEY (OfficerKey)
);


CREATE TABLE detailed_ticket_revenue_fact_table
(
  CalendarKey INT NOT NULL,
  OfficerKey INT NOT NULL,
  Amount INT NOT NULL,
  PRIMARY KEY (CalendarKey, OfficerKey),
  FOREIGN KEY (CalendarKey) REFERENCES Calendar_dim(CalendarKey),
  FOREIGN KEY (OfficerKey) REFERENCES Officer_dim(OfficerKey)
  );