Schemas

Question 1

Create a time dimension with only char_date (dw. charter) for ID, Year and Month

Answer 1

Create table timeDIM as select distinct to_char(char_date, ‘YYYYMM’) as TimeID, to_char(char_date, ‘Year’) as Year, to_char(char_date, ‘Month’) as Month from dw.charter

Question 2

create a dimension for semester and year using (OSem and OYear from Offering2)

Answer 2

Create table Sem_Year DIM AS Select distinct OYear ll OSen AS Sem_ID, OYear, OSem From Offering2

Question 3

Create a semester table for SemID, SemDesc, BeginDate and EndDate

Answer 3

Create table semesterDIM (SemID varchar2(10), SemDesc varchar2(10), BeginDate date, EndDate date)

Question 4

The semester dimension should show that Semester 1 runs between 01-Jan and 18-July. Semester 2 runs between 16-July and 31 December.

Answer 4

Insert into semesterDIM values (‘s1’, ‘Semester1’, to_date(‘01-Jan’, ‘DD-MON’), to_date(‘15-JUL’, ‘DD-MON’)) then Insert into semesterDIM values (‘s2’, ‘Semester2’, to_date(‘16-JUL’, ‘DD-MON’), to_date(‘31-DEC’, ‘DD-MON’))

Question 5

Create a lab dimension for timeID, time description, start and end time.

Answer 5

Create table labTimeDIM (timeID number, time_desc varchar2(15), beginTime date, endTime date)

Question 6

Insert into the labTimeDim to show that morning is from 6.01 and 12.00. Then to show afternoon from 12.01 and 18.00. From evening from 18.01 and 6.00.

Answer 6

Insert into labTimeDIM values (1, ‘morning’, to_date(‘06:01’, ‘HH24:MI’), to_date(‘12:00’, ‘HH24:MI’)) and Insert into labTimeDIM values (2, ‘afternoon’, to_date(‘12:01’, ‘HH24:MI’), to_date(‘18:00’, ‘HH24:MI’)) and Insert into labTimeDIM values (3, ‘evening’, to_date(‘12:01’, ‘HH24:MI’), to_date(‘06:00’, ‘HH24:MI’))