Part 3. Write a stored procedure to populate the star schema database with the transaction data 
in the database provided for Assignment 10. Save the stored procedure in a file named A11P3.sql.
The stored procedure should be such that it can be 
rerun/tested whether or not the star schema database is populated. 


ALTER PROCEDURE A11P3
AS
BEGIN
	--FIRST DISABLE THE CONSTRAINTS SO THE TABLES CAN BE TRUNCATED
	--TRUNCATING THE TABLES ENABLES US TO REPOPULATE THE DATA WAREHOUSE TABLES
	--WE WANT TO BE ABLE TO REPOPULATE BECAUSE DEVELOPMENT AND TESTING ALWAYS TAKE MANY TRIES
	
	ALTER TABLE FACT
	DROP CONSTRAINT FK_FACT_PILOTDIM, FK_FACT_TIMEDIM, FK_FACT_MODELDIM

	--TRUNCATE THE TABLES
	TRUNCATE TABLE FACT
	TRUNCATE TABLE PILOTDIM
	TRUNCATE TABLE TIMEDIM
	TRUNCATE TABLE MODELDIM
	TRUNCATE TABLE STAGING

	--PUT THE FK CONSTRAINTS BACK ON
	ALTER TABLE FACT
	ADD	CONSTRAINT FK_FACT_PILOTDIM FOREIGN KEY (PILOT_KEY) REFERENCES PILOTDIM,
		CONSTRAINT FK_FACT_TIMEDIM FOREIGN KEY (TIME_KEY) REFERENCES TIMEDIM,
		CONSTRAINT FK_FACT_MODELDIM FOREIGN KEY (MODEL_KEY) REFERENCES MODELDIM

	--NOW WE ARE READY TO POPULATE THE DIM TABLES
	INSERT INTO PILOTDIM
	SELECT	E.EMP_NUM, E.EMP_LNAME, E.EMP_FNAME, P.PIL_LICENSE, P.PIL_RATINGS, P.PIL_MED_TYPE, P.PIL_MED_DATE, P.PIL_PT135_DATE
	FROM	EMPLOYEE E INNER JOIN PILOT P ON E.EMP_NUM = P.EMP_NUM

	INSERT INTO TIMEDIM
	SELECT	DISTINCT CHAR_DATE, YEAR(CHAR_DATE), MONTH(CHAR_DATE)
	FROM	CHARTER

	INSERT INTO MODELDIM
	SELECT	MOD_CODE, MOD_MANUFACTURER, MOD_NAME, MOD_SEATS
	FROM	MODEL

	--INSERT INTO THE STAGING TABLE. PLEASE NOTE YOU DON'T WANT TO JOIN WITH THE DIMENSION TABLES BECUASE
	--THAT CAN LEAD TO A HUGE PRODUCT. IN MORE REAL SITUATIONS YOU WILL HAVE VERY COMPLICATED BUSINESS LOGIC YOU 
	--HAVE TO EXECUTE SO AT LEAST ONE STAGING TABLE IS USED. YOU NEED TO UNDERSTAND THE IMPORTANCE OF 
	--THE STAGING TABLE. 
	INSERT	INTO STAGING (EMP_NUM, MOD_CODE, CHAR_DATE, CHAR_DISTANCE, CHAR_FUEL_GALLONS, MOD_CHG_MILE)
	
	SELECT	P.EMP_NUM, M.MOD_CODE, C.CHAR_DATE, C.CHAR_DISTANCE, C.CHAR_FUEL_GALLONS, M.MOD_CHG_MILE
	FROM	CHARTER C INNER JOIN PILOT P ON C.CHAR_PILOT = P.EMP_NUM
			INNER JOIN AIRCRAFT A ON C.AC_NUMBER = A.AC_NUMBER 
			INNER JOIN MODEL M ON A.MOD_CODE = M.MOD_CODE 

	--AT THIS POINT THE STAGING TABLE DOES NOT HAVE THE DATA WARESHOUSE KEYS AND THE FOLLOWING UPDATE STATEMENTS ARE
	--USED TO PROVIDE THAT. BUT THAT DATA WAREHOUSE KEY IS FOUND IN THE DIMENSION TABLES. SO THE NEXT STEP IS TO MAP
	--THESE DATA WAREHOUSE KEYS IN THE DIMENSION TABLES TO THE FACTS IN THE STAGING TABLE USING THE COMMON LINK BETWEEN
	--THE DIMENSION TABLES AND THE STAGING TABLE: PRODUCTION KEYS. 

	UPDATE	STAGING
	SET		TIME_KEY = T.TIME_KEY
	FROM	STAGING S INNER JOIN TIMEDIM T ON S.CHAR_DATE = T.CHAR_DATE

	UPDATE	STAGING
	SET		PILOT_KEY = P.PILOT_KEY
	FROM	STAGING S INNER JOIN PILOTDIM P ON S.EMP_NUM = P.EMP_NUM

	UPDATE	STAGING
	SET		MODEL_KEY = M.MODEL_KEY
	FROM	STAGING S INNER JOIN MODELDIM M ON S.MOD_CODE = M.MOD_CODE

	--NOW WE HAVE EVERYTHING WE NEED IN THE STAGING TO POPULATE THE FACT TABLE. IT IS JUST A MATTER OF 
	--COPYING IT OVER TO THE FACT TABLE FROM THE STAGING

	INSERT	INTO FACT (TIME_KEY, MODEL_KEY, PILOT_KEY, CHAR_DISTANCE, CHAR_FUEL_GALLONS, MOD_CHG_MILE)
	SELECT	TIME_KEY, MODEL_KEY, PILOT_KEY, CHAR_DISTANCE, CHAR_FUEL_GALLONS, MOD_CHG_MILE
	FROM	STAGING 

END