Danielle 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 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.

o HINT: Consider creating $1$ table that has current positions and $1$ table with

past information $($use job history$)$ and then join the tables together as a

union.

$($g$)$ Your tutor should be able to run your SQL statements without having to change

any syntax.