Week $03-$ Exercise

In Week $1$ & $2,$ we introduced the fast growing fashion store, New Jeans! New Jeans

originally engaged CwP Consulting to help assist them in transforming their data structures.

The scope of the work included improving the HR database reporting. YE Consulting

$($yourself$)$ has created a star schema which we can use for this weeks exercise. The sample

ERD has been attached below for your reference as well. $25209731.$JOB HISTORY

PF$*|$

EMPLOYEE$\_$ID

NUMBER $(6)$

P$.$

START$\_$DATE

DATE

$*$ END$\_$DATE

DAIE

F $"$ JOBLID

VARCHAR$2(10$ BYTE$)$

DEPARTMENT$\_$ID

NUMBER $(4)$

JHIST$\_$EMP$\_$ID$\_$ST$\_$DATE$\_$PK $($EMPLOYEE$\_$ID$,$ START$\_$DATE$)$

OZJHIST$\_$DEPT$\_$FK $($DEPARTMENT$\_$ID$)$

JHIST$\_$EMP$\_$FK $($EMPLOYEE$\_$ID$)$

$3$JHIST$\_$JOB$\_$FK $($JOB$\_$ID$)$

$$ JHIST$\_$DEPARTMENT$\_$IX $($DEPARTMENT$\_$ID$)$

$$ JHIST$\_$EMPLOYEE$\_$IX $($EMPLOYEE$\_$ID$)$

$$ JHIST$\_$EMP$\_$ID$\_$ST$\_$DATE$\_$PK $($EMPLOYEE$\_$ID$,$ START$\_$DATE$)$

$$ JHIST$\_$JOB$\_$IX $($JOB$\_$ID$)$

$25209731.$DEPARTMENTS

"DEPARTMENT$\_$ID

NUMBER $(4)$

$*$ DEPARTMENT$\_$NAME

VARCHAR$2(30$ BYTE$)$

MANAGER$\_$ID

NUMBER $(6)$

LOCATION$\_$ID

NUMBER $(4)$

$-$ DEPT$\_$ID$\_$PK $($DEPARTMENT$\_$ID$)$

$3$ DEPT$\_$LOC$\_$FK $($LOCATION$\_$ID$)$

$3$ DEPT$\_$MGR$\_$FK $($MANAGER$\_$ID$)$

$$ DEPT$\_$ID$\_$PK $($DEPARTMENT$\_$ID$)$

$$ DEPT LOCATION IX$.($LOCATION ID$$

$25209731.$LOCATIONS

P $*$ LOCATION ID

NUMBER $(4)$

STREET$\_$ADDRESS VARCHAR$2(40$ BYTE$)$

POSTAL$\_$CODE

VARCHAR$2(12$ BYTE$)|$

$*$ CITY

VARCHAR$2(30$ BYTE$)$

STATE$\_$PROVINCE

VARCHAR$2(25$ BYTE$)$

COUNTRY$\_$ID

CHAR $(2$ BYTE$)$

$$ LOC$\_$ID$\_$PK $($LOCATION$\_$ID$)$

$\backslash$deg LOC$\_$C$\_$ID$\_$FK $($COUNTRY$\_$ID$)$

$$ LOC$\_$CITY$\_$IX $($CITY$)$

$$ LOC$\_$COUNTRY$\_$IX $($COUNTRY$\_$ID$)$

$$ LOC$\_$ID$\_$PK $($LOCATION$\_$ID$)$

$$ LOC$\_$STATE$\_$PROVINCE$\_$IX $($STATE$\_$PROVINCE$)$

P $*$ COUNTRY ID

COUNTRY NAME

REGION ID

$25209731.$COUNTRIES

CHAR $(2$ BYTE$)$

VARCHAR$2(40$ BYTE$)$

NUMBER

$->$ COUNTRY$\_$C$\_$ID$\_$PK$($COUNTRY$\_$ID$)$

$*$ COUNTR$\_$REG$\_$FK $($REGION$\_$ID$)$

Z$5209731.$JOBS

P $^$ JOBLID

VARCHAR$2(10$ BYTE$)$

$*$ JOB$\_$TITLE

VARCHAR$2(35$ BYTE$)$

MIN$\_$SALARY NUMBER $(6)$

MAX$\_$ SALARY NUMBER $(6)$

$$ JOB$\_$ID$\_$PK $($JOB$\_$ID$)$

$$ JOB$\_$ID$\_$PK $($JOB$\_$ID$)$

$25209731.$EMPLOYEES

"EMPLOYEE$\_$ID

NUMBER $(8)$

FIRST$\_$NAME

VARCHAR$2(20$ BYTE$)$

$*$ LAST$\_$NAME

VARCHAR$2(25$ BYTE$)$

U $-$ EMAIL

VARCHAR$2(25$ BYTE$)$

PHONE NUMBER

VARCHAR$2(20$ BYTE$)$

$*$ HIRE$\_$DATE

DATE

F $*$ JOB ID

VARCHAR$2(10$ BYTE$)$

SALARY

NUMBER $(8,2)$

COMMISSION$\_$PCT

NUMBER $(2,2)$

MANAGER ID

NUMBER $(6)$

DEPARTMENT$\_$ID

NUMBER $(4)$

$$ EMP$\_$EMAIL$\_$UK $($EMAIL$)$

$$ EMP$\_$EMP$\_$ID$\_$PK $($EMPLOYEE$\_$ID$)$

$3$ EMP$\_$DEPT$\_$FK $($DEPARTMENT$\_$ID$)$

$3$ EMP$\_$JOB$\_$FK $($JOB$\_$ID$)$

$3$ EMP$\_$MANAGER$\_$FK $($MANAGER$\_$ID$)$

$$ EMP$\_$DEPARTMENT$\_$IX $($DEPARTMENT$\_$ID$)$

$$ EMP$\_$EMAIL$\_$UK $($EMAIL$)$

$$ EMP$\_$EMP$\_$ID$\_$PK $($EMPLOYEE$\_$ID$)$

$$ EMP$\_$JOB$\_$IX $($JOB$\_$ID$)$

$$ EMP$\_$MANAGER$\_$IX $($MANAGER$\_$ID$)$

$$ EMP$\_$NAME$\_$ IX $($LAST$\_$NAME, FIRST$\_$NAME$)$

$25209731$ REGIONS

P $*$ REGION$\_$ID

NUMBER

REGION NAME

VARCHAR$2(25$ BYTE$)$

$->$ REG $\_$ID$\_$PK$($REGION$\_$ID$)$

$$ REG$\_$ID$\_$PK $($REGION ID$)|$ star schema: employees$\_$dimension departments$\_$dimension

employee$\_$key department$\_$key

first$\_$name department$\_$name

last$\_$name

current$\_$emp$\_$flag

empfact

employee$\_$key

department$\_$key

location$\_$key

job$\_$key

position$\_$key

salary

jobs$\_$dimension commission$\_$pct locations$\_$dimension

job$\_$key location$\_$key

job$\_$title postal$\_$code

current$\_$job$\_$flag city

state$\_$province

region$\_$id

region$\_$name

country$\_$id

country$\_$nameDanielle was pleased with your results for the New Jeans fashion store DW and wants you to

have a go for the New Jeans HR database! This exercise is based on the Star Schema created

in Week $02$ for the New Jeans HR database.

$($a$)$ You might want to improve and update your star schema from Week $02.$

Otherwise, you can leave it if you think that is the best schema you produced.

$($b$)$ The ERD$/$tables for the HR Database are in the Oracle SQL Developer AWS

server already $$ Jobs, Job History, Employees etc NOT ZJLB

$($c$)$ Write SQL statements to create the necessary dimension tables and a fact table

$($for all the names for table and views, make sure they start with $$dwhr$\_)$ based

on the schema in $($a$).$ Step $3-7$ of Star Schema creation

$($d$)$ Write SQL statements to populate data from the HR Database $$ Example

provided in New Jeans Star Schema and Laksa Delight sample

Note: you can combine $($b$)$ and $($c$)$ together to create tables and populate the tables

at the same time. You just have to make sure you add data to the dimension tables

first before the fact table, otherwise you will violate the constraints on the fact

table.

$($e$)$ There are a number of ways to create dimension tables and a fact table. For this

exercise, it is recommended to create a current$\_$emp$\_$flag column in the

dwhr$\_$employees$\_$dimension table. If an employee has retired or resigned, then

current$\_$emp$\_$flag column becomes $$N$($No$),$ otherwise the column should be $$Y$$

$($Yes$).$

o When denormalising tables, join them together in SQL$.$ The New Jeans

star schema provides an example of this.

o For current$\_$emp$\_$flag, in the SQL code, add another line in the select

statement for employeesCREATE TABLE DAHR EMPLOYEES DIMENSION AS

$($SELECT employee$\_$id

as employee

key,

first$\_$name,

last$\_$name,

$"$y$"$

as current

$\_$emp

FROM EMPLOYEES$)$;$($f$)$ How do you populate the data for the fact table? The challenge here is how can

you put job$\_$history and employees tables together to form a fact table