Question
Create the TIMES star schema dimension table via SQL. This is TIME table in the star schema figure. The Sale_Day primary key column values should
Create the TIMES star schema dimension table via SQL. This is TIME table in the star schema figure. The Sale_Day primary key column values should be all dates from your first sale date through and including your last sale date from your SALES table. The Day_Type values should be 'Weekday', 'Weekend', or 'Holiday' (this trumps Weekday and Weekend). Set all occurrences of the following days for your date range to be holidays: New Years Day, Martin Luther King Jrs Birthday, Presidents Day, Memorial Day, 4th of July, Labor Day, Columbus Day, Veterans Day, Thanksgiving, and Christmas. Use a PL/SQL block to populate the TIMES table. After populating your TIMES table execute the SQL statement "SELECT day_type, COUNT(*),MIN(sale_day),MAX(sale_day) FROM time GROUP BY day_type ORDER BY day_type" to show the summarized contents of your table. Show all your SQL and PL/SQL code for this step and the results.
CREATE TABLE Sales (
sale_ID VARCHAR2(10) NOT NULL,
salesperson_ID VARCHAR2(10) NOT NULL,
cust_ID VARCHAR2(10) NOT NULL,
sale_date DATE,
VIN VARCHAR2(20) NOT NULL,
mileage INT,
vehicle_status VARCHAR2(15),
gross_sale_price NUMBER(8,2) NOT NULL,
PRIMARY KEY (sale_ID),
CONSTRAINT FK_Customer_ID FOREIGN KEY (cust_ID) REFERENCES Customers(cust_ID),
CONSTRAINT FK_VIN_ID FOREIGN KEY (VIN) REFERENCES Sale_Vehicles(VIN));
INSERT INTO Sales VALUES ('3000001010', '6000000600', '1000100000', to_date('010717','YYMMDD'), '96587453GH45772535C1', '3', 'New', '50000.00'); INSERT INTO Sales VALUES ('3000001011', '6000000601', '1000100100', to_date('030515','YYMMDD'), '27857687DV45278654X5', '25500', 'Used', '20000.00'); INSERT INTO Sales VALUES ('3000001012', '6000000602', '1000100200', to_date('040116','YYMMDD'), '96875488GJ45778455V5', '17000', 'Used', '40000.00'); INSERT INTO Sales VALUES ('3000001013', '6000000603', '1000100300', to_date('120714','YYMMDD'), '68785798GH56773277BV', '35', 'New', '55000.00'); INSERT INTO Sales VALUES ('3000001014', '6000000604', '1000100400', to_date('110814','YYMMDD'), '67855997GT12788534G6', '58', 'New', '40000.00'); INSERT INTO Sales VALUES ('3000001015', '6000000605', '1000100500', to_date('050613','YYMMDD'), '67845899HT45577885H2', '37', 'New', '40000.00'); INSERT INTO Sales VALUES ('3000001016', '6000000606', '1000100600', to_date('060216','YYMMDD'), '98767885GF55663485GV', '5000', 'Used', '35000.00'); INSERT INTO Sales VALUES ('3000001017', '6000000607', '1000100700', to_date('020618','YYMMDD'), '96354882GJ56566899G3', '4', 'New', '30000.00'); INSERT INTO Sales VALUES ('3000001018', '6000000608', '1000100800', to_date('060717','YYMMDD'), '98678853GB45567895GT', '15', 'New', '45000.00'); INSERT INTO Sales VALUES ('3000001019', '6000000609', '1000100900', to_date('080614','YYMMDD'), '25789678VF56578995B5', '25', 'New', '35000.00');
CREATE TABLE Times (
sale_day DATE NOT NULL,
day_type VARCHAR2(50) NOT NULL,
PRIMARY KEY (sale_day));
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started