UGzGBx9L

1DROP TABLE IF EXISTS detailed_ticket_revenue_fact_table,
2officer_dim, 
3calendar_dim;
4
5
6
7CREATE TABLE calendar_dim (
8  CalendarKey INT NOT NULL AUTO_INCREMENT,
9  Full_Date INT NOT NULL,
10  Month VARCHAR(15) NOT NULL,
11  Year VARCHAR(15) NOT NULL,
12  PRIMARY KEY (CalendarKey)
13); 
14
15
16
17CREATE TABLE officer_dim (
18  OfficerKey INT NOT NULL AUTO_INCREMENT,
19  OfficerID INT NOT NULL,
20  OfficerName VARCHAR(15) NOT NULL,
21  OfficerRank VARCHAR(15) NOT NULL,
22  PRIMARY KEY (OfficerKey)
23); 
24
25
26CREATE TABLE detailed_ticket_revenue_fact_table
27(
28  CalendarKey INT NOT NULL,
29  OfficerKey INT NOT NULL,
30  Amount INT NOT NULL,
31  PRIMARY KEY (CalendarKey, OfficerKey),
32  FOREIGN KEY (CalendarKey) REFERENCES Calendar_dim(CalendarKey),
33  FOREIGN KEY (OfficerKey) REFERENCES Officer_dim(OfficerKey)
34  );