BranchDim (...) —— 分店维度（沿用 Part 2：由 opdb.Branch 复制）
ProdCategoryDim (...) —— 类别维度（沿用 Part 2：由 opdb.Category 复制）
2. Define the dimensions and attributes for the Sales star schema.
TimeDimMonth
MonthNo (NUMBER(2))：时间键（1–12）
MonthName (VARCHAR2(20))：如 ‘Jan’, ‘Feb’, … ‘Dec’
BranchDim（复用 Part 2，从 opdb.Branch 复制全部列）
BranchID（分店键，其他列原样保留以便报表显示）
ProdCategoryDim（复用 Part 2，从 opdb.Category 复制全部列）
CategoryID（类别键，含 CategoryDesc 等）
SalesFact_Month
MonthNo, BranchID, CategoryID（外键）
Total_Sales（度量，来源 Sales.TotalPrice 的汇总）
3. Write the SQL statements for the implementation of the star schema.
4. Write the SQL statements to produce the following reports:
a. Show the total sales by different months.
b. Show the total sales by different branches and product categories.
sql
-- 可选清理 DROP TABLE AgentDim PURGE; DROP TABLE CountryDim PURGE; DROP TABLE CourseDim PURGE; DROP TABLE YearDim PURGE; DROP TABLE CollegeFact PURGE;
sql
-- AgentDIM CREATE TABLE AgentDim AS SELECT * FROM opdb.Agent;
-- CountryDIM CREATE TABLE CountryDim AS SELECT DISTINCT Country FROM opdb.Student;
-- CourseDIM CREATE TABLE CourseDim AS SELECT CourseCode, CourseName, Duration, CourseLevel FROM opdb.Course;
-- YearDIM CREATE TABLE YearDim AS SELECT DISTINCT EnrolmentYear FROM opdb.Enrolment;
sql
CREATE TABLE CollegeFact AS SELECT S.Country, E.AgentNo, E.CourseCode, E.EnrolmentYear, COUNT(P.PaymentNo) AS Number_of_payments, SUM(P.Amount) AS Total_Income FROM opdb.Student S JOIN opdb.Enrolment E ON E.StudentID = S.StudentID JOIN opdb.Payment P ON E.EnrolmentNo = P.EnrolmentNo GROUP BY S.Country, E.AgentNo, E.CourseCode, E.EnrolmentYear;
sql
SELECT SUM(Total_Income) AS total_income_australia FROM CollegeFact WHERE Country = 'Australia';
sql
SELECT cf.CourseCode, c.CourseName, SUM(cf.Total_Income) AS total_income, SUM(cf.Number_of_payments) AS num_payments FROM CollegeFact cf JOIN CourseDim c ON c.CourseCode = cf.CourseCode GROUP BY cf.CourseCode, c.CourseName ORDER BY total_income DESC;
sql
SELECT SUM(Total_Income) AS total_income_c6003_2019, SUM(Number_of_payments) AS num_payments FROM CollegeFact WHERE CourseCode = 'C6003' AND EnrolmentYear = 2019;
sql
SELECT SUM(cf.Total_Income) AS total_income_new_star, SUM(cf.Number_of_payments) AS num_payments FROM CollegeFact cf JOIN AgentDim a ON a.AgentNo = cf.AgentNo WHERE a.AgentName = 'New Star Agent';
sql
create table ProdCategoryDim as select * from opdb.Category;
select * from ProdCategoryDim;
sql
create table BranchDim as select * from opdb.Branch;
insert into TimeDim values (1, 'Jan-Mar'); insert into TimeDim values (2, 'Apr-Jun'); insert into TimeDim values (3, 'Jul-Sep'); insert into TimeDim values (4, 'Oct-Dec');
select * from TimeDim;
sql
create table TempFact as select S.SalesDate, B.BranchID, C.CategoryID, S.TotalPrice from opdb.Branch B, opdb.Sales S, opdb.Product P, opdb.Category C where B.BranchID = S.BranchID and S.ProductNo = P.ProductNo and P.CategoryID = C.CategoryID;
sql
alter table TempFact add (Quarter number(1));
sql
update TempFact set Quarter = 1 where to_char(SalesDate, 'MM') >= '01' and to_char(SalesDate, 'MM') <= '03';
update TempFact set Quarter = 2 where to_char(SalesDate, 'MM') >= '04' and to_char(SalesDate, 'MM') <= '06';
update TempFact set Quarter = 3 where to_char(SalesDate, 'MM') >= '07' and to_char(SalesDate, 'MM') <= '09';
update TempFact set Quarter = 4 where Quarter is null;
select * from TempFact fetch first 10 rows only;
sql
create table SalesFact as select Quarter, BranchID, CategoryID, sum(TotalPrice) as Total_Sales from TempFact group by Quarter, BranchID, CategoryID;
select * from SalesFact fetch first 20 rows only;
sql
select f.Quarter, t.Description, sum(f.Total_Sales) as Total_Sales from SalesFact f join TimeDim t on t.Quarter = f.Quarter group by f.Quarter, t.Description order by f.Quarter;
sql
select b.BranchID, /* b.BranchName, */ c.CategoryID, /* c.CategoryName, */ sum(f.Total_Sales) as Total_Sales from SalesFact f join BranchDim b on b.BranchID = f.BranchID join ProdCategoryDim c on c.CategoryID = f.CategoryID group by b.BranchID, /* b.BranchName, */ c.CategoryID /*, c.CategoryName */ order by b.BranchID, c.CategoryID;
sql
select sum(f.Total_Sales) as Total_Sales_Q1_Kitchen from SalesFact f where f.Quarter = 1 and f.CategoryID = 2;
sql
/* ——[可选清理] 如需重建，请按需取消注释 —— drop table SalesFact_Month; drop table TempFact_Month; drop table TimeDimMonth; -- BranchDim / ProdCategoryDim 为 Part 2 产物，通常无需删除 */
------------------------------------------------------------ -- 2.1 维度（复用 Part 2 的 BranchDim / ProdCategoryDim） -- 若缺失，可用下列语句创建（与 Part 2 一致的 CTAS 风格） -- create table BranchDim as select * from opdb.Branch; -- create table ProdCategoryDim as select * from opdb.Category;
insert into TimeDimMonth values (1, 'Jan'); insert into TimeDimMonth values (2, 'Feb'); insert into TimeDimMonth values (3, 'Mar'); insert into TimeDimMonth values (4, 'Apr'); insert into TimeDimMonth values (5, 'May'); insert into TimeDimMonth values (6, 'Jun'); insert into TimeDimMonth values (7, 'Jul'); insert into TimeDimMonth values (8, 'Aug'); insert into TimeDimMonth values (9, 'Sep'); insert into TimeDimMonth values (10, 'Oct'); insert into TimeDimMonth values (11, 'Nov'); insert into TimeDimMonth values (12, 'Dec');
------------------------------------------------------------ -- 2.3 临时事实：从 OPDB 抽取所需列 create table TempFact_Month as select S.SalesDate, B.BranchID, C.CategoryID, S.TotalPrice from opdb.Branch B join opdb.Sales S on B.BranchID = S.BranchID join opdb.Product P on S.ProductNo = P.ProductNo join opdb.Category C on P.CategoryID = C.CategoryID;
update TempFact_Month set MonthNo = to_number(to_char(SalesDate, 'MM'));
------------------------------------------------------------ -- 2.4 事实表：按 月×分店×类别 聚合 create table SalesFact_Month as select MonthNo, BranchID, CategoryID, sum(TotalPrice) as Total_Sales from TempFact_Month group by MonthNo, BranchID, CategoryID;
sql
select f.MonthNo, t.MonthName, sum(f.Total_Sales) as Total_Sales from SalesFact_Month f join TimeDimMonth t on t.MonthNo = f.MonthNo group by f.MonthNo, t.MonthName order by f.MonthNo;
sql
-- 通用：按分店×类别汇总（跨所有月份） select f.BranchID, f.CategoryID, sum(f.Total_Sales) as Total_Sales from SalesFact_Month f group by f.BranchID, f.CategoryID order by f.BranchID, f.CategoryID;