Question

Populate the tables created in E8.1b with the data. Use the data that you used to populate the tables shown in Figure 8.42 as the basis for aggregation.

1. use CREATE commands to create tables (Fact(s) and Dimensions tables
2. use INSERT INTO to populate the above tables
3. use SELECT * for each table to display the information you entered in step 2
4. use DROP commands to erase the tables from the data warehouse
   CALENDAR Dimension Calendar DayOf FullDate DayOfWeek Month 1/1/2013 Tuesday 1/2/2013 Wednesday 2 Month Qtr Janua Janua Q1 Q1 Year 2013 2013 OFFICER Dimension Officer Officer OfficeriD OfficerName Rank Joe Mike Bob Sergeant Patrolman Patrolman 2 TICKETREVENUE Fact table Calendar Officer Amount 1 $300 2 $200 3 $250 2 $150 2 2
   

Answer 1

1. CREATE CALENDAR (

CalendarKey INT PRIMARY KEY,

FullDate DATE,

DayOfWork VARCHAR(20),

DayOfMonth INT,

Month VARCHAR(20),

Qtr VARCHAR(2),

Year INT

);

CREATE TABLE OFFICER (

OfficerKey INT PRIMARY KEY,

OfficerID INT,

OfficerName VARCHAR(40),

OfficerRank VARCHAR(40)

);

CREATE TABLE TICKETREVENUE (

CalendarKey INT,

OfficerKey INT,

Amount DECIMAL(10, 2),

PRIMARY KEY (CalendarKey, OfficerKey),

FOREIGN KEY (CalendarKey) REFERENCES CALENDAR (CalendarKey),

FOREIGN KEY (OfficerKey) REFERENCES OFFICER (OfficerKey)

);

2. INSERT INTO CALENDAR VALUES (1, '1/1/2013', 'Tuesday', 1, 'January', 'Q1', 2013);

INSERT INTO OFFICER VALUES (1, 1, 'Joe', 'Sergeant');

INSERT INTO TICKETREVENUE (1, 1, 300);

NOTE: I have inserted only row in each table. You can refer it to insert other rows as required.

3.

SELECT * FROM CALENDAR;

SELECT * FROM OFFICER;

SELECT * FROM TICKETREVENUE;

4.

DROP TABLE CALENDAR;

DROP TABLE OFFICER;

DROP TABLE TICKETREVENUE;