学习

Lab04 Bridge Table · Jimmy Huang's Blog

sql

Create Table Warehouse
(WarehouseID Varchar2(10) Not Null,
Location Varchar2(10) Not Null,
Primary Key (WarehouseID)
);


Create Table Truck
(TruckID Varchar2(10) Not Null,
VolCapacity Number(5,2),
WeightCategory Varchar2(10),
CostPerKm Number(5,2),
Primary Key (TruckID)
);


Create Table Trip
(TripID Varchar2(10) Not Null,
TripDate Date,
TotalKm Number(5),
TruckID Varchar2(10),
Primary Key (TripID),
Foreign Key (TruckID) References Truck(TruckID)
);


Create Table TripFrom
(TripID Varchar2(10) Not Null,
WarehouseID Varchar2(10) Not Null,
Primary Key (TripID, WarehouseID),
Foreign Key (TripID) References Trip(TripID),
Foreign Key (WarehouseID) References Warehouse(WarehouseID)
);


Create Table Store
(StoreID Varchar2(10) Not Null,
StoreName Varchar2(20),
StoreAddress Varchar2(20),
Primary Key (StoreID)
);


Create Table Destination
(TripID Varchar2(10) Not Null,
StoreID Varchar2(10) Not Null,
Primary Key (TripID, StoreID),
Foreign Key (TripID) References Trip(TripID),
Foreign Key (StoreID) References Store(StoreID)
);


-- Insert Records to Operational Database
Insert Into Warehouse Values ('W1','Warehouse1');
Insert Into Warehouse Values ('W2','Warehouse2');
Insert Into Warehouse Values ('W3','Warehouse3');
Insert Into Warehouse Values ('W4','Warehouse4');
Insert Into Warehouse Values ('W5','Warehouse5');


Insert Into Truck Values ('Truck1', 250, 'Medium', 1.2);
Insert Into Truck Values ('Truck2', 300, 'Medium', 1.5);
Insert Into Truck Values ('Truck3', 100, 'Small', 0.8);
Insert Into Truck Values ('Truck4', 550, 'Large', 2.3);
Insert Into Truck Values ('Truck5', 650, 'Large', 2.5);


Insert Into Trip Values ('Trip1', to_date('14-Apr-2013', 'DD-MON-YYYY'), 370, 'Truck1');
Insert Into Trip Values ('Trip2', to_date('14-Apr-2013', 'DD-MON-YYYY'), 570, 'Truck2');
Insert Into Trip Values ('Trip3', to_date('14-Apr-2013', 'DD-MON-YYYY'), 250, 'Truck3');
Insert Into Trip Values ('Trip4', to_date('15-Jul-2013', 'DD-MON-YYYY'), 450, 'Truck1');
Insert Into Trip Values ('Trip5', to_date('15-Jul-2013', 'DD-MON-YYYY'), 175, 'Truck2');


Insert Into TripFrom Values ('Trip1', 'W1');
Insert Into TripFrom Values ('Trip1', 'W4');
Insert Into Destination Values ('Trip2', 'M2');

sql

CREATE TABLE TripSeason1 (
  SeasonName VARCHAR2(10) PRIMARY KEY
);

INSERT INTO TripSeason1 (SeasonName) VALUES ('Summer');
INSERT INTO TripSeason1 (SeasonName) VALUES ('Autumn');
INSERT INTO TripSeason1 (SeasonName) VALUES ('Winter');
INSERT INTO TripSeason1 (SeasonName) VALUES ('Spring');
COMMIT;

sql

CREATE TABLE TripDim1 AS SELECT * FROM trip;

CREATE TABLE TripDim1 AS
SELECT
  t.TripID,
  t.TripDate,
  t.TotalKm,
  CAST(
    CASE
      WHEN TO_CHAR(t.TripDate,'MM') IN ('12','01','02') THEN 'Summer'
      WHEN TO_CHAR(t.TripDate,'MM') IN ('03','04','05') THEN 'Autumn'
      WHEN TO_CHAR(t.TripDate,'MM') IN ('06','07','08') THEN 'Winter'
      ELSE 'Spring'
    END
    AS VARCHAR2(10)
  ) AS SeasonName
FROM Trip t;


ALTER TABLE TripDim1 ADD CONSTRAINT PK_TripDim1 PRIMARY KEY (TripID);
ALTER TABLE TripDim1 ADD CONSTRAINT FK_TripDim1_Season
  FOREIGN KEY (SeasonName) REFERENCES TripSeason1(SeasonName);

sql

CREATE TABLE BridgeTableDim1 AS SELECT * FROM destination;

CREATE TABLE BridgeTableDim1 AS
SELECT TripID, StoreID
FROM Destination;        -- 操作库中记录每次行程送到哪些门店

ALTER TABLE BridgeTableDim1
  ADD CONSTRAINT PK_BridgeDim1 PRIMARY KEY (TripID, StoreID);

ALTER TABLE BridgeTableDim1
  ADD CONSTRAINT FK_BridgeDim1_Trip
    FOREIGN KEY (TripID)  REFERENCES TripDim1(TripID);

ALTER TABLE BridgeTableDim1
  ADD CONSTRAINT FK_BridgeDim1_Store
    FOREIGN KEY (StoreID) REFERENCES StoreDim1(StoreID);

sql

CREATE TABLE StoreDim1 AS SELECT * FROM store;

CREATE TABLE StoreDim1 AS
SELECT StoreID, StoreName, StoreAddress
FROM Store;

ALTER TABLE StoreDim1
  ADD CONSTRAINT PK_StoreDim1 PRIMARY KEY (StoreID);

sql

DROP TABLE tempfact PURGE;

TruckID, TripID, CostPerKm, TotalKm, TripDate, SeasonID
CREATE TABLE tempfact AS
SELECT
  d.TripID,
  t.TruckID,               -- 直接来自操作库 Trip
  d.SeasonName,            -- 来自 TripDim1
  d.TotalKm    AS BaseTotalKm,
  tr.CostPerKm AS BaseCostPerKm
FROM Trip t
JOIN TripDim1  d   ON d.TripID   = t.TripID
JOIN TruckDim1 tr  ON tr.TruckID = t.TruckID;

// ALTER tempfact ADD (s)

ALTER TABLE tempfact ADD (TotalDeliveryCost NUMBER(12,2));

UPDATE tempfact
SET TotalDeliveryCost = ROUND(BaseTotalKm * BaseCostPerKm, 2);
COMMIT;

-- 最终事实表 TruckFact1：只保留分析需要的列
BEGIN EXECUTE IMMEDIATE 'DROP TABLE TruckFact1 PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/

SeasonID costperkm*totaldeliverycost
CREATE TABLE TruckFact1 AS
SELECT TripID, TruckID, SeasonName, TotalDeliveryCost
FROM tempfact;

ALTER TABLE TruckFact1 ADD CONSTRAINT PK_TruckFact1 PRIMARY KEY (TripID);

ALTER TABLE TruckFact1
  ADD CONSTRAINT FK_Fact1_Trip
    FOREIGN KEY (TripID)    REFERENCES TripDim1(TripID);

ALTER TABLE TruckFact1
  ADD CONSTRAINT FK_Fact1_Truck
    FOREIGN KEY (TruckID)   REFERENCES TruckDim1(TruckID);

ALTER TABLE TruckFact1
  ADD CONSTRAINT FK_Fact1_Season
    FOREIGN KEY (SeasonName) REFERENCES TripSeason1(SeasonName);

sql

-- 观察事实表
SELECT * FROM TruckFact1 ORDER BY TripID;

-- 抽样核对：期望值 = TotalKm * CostPerKm
SELECT f.TripID, d.TotalKm, tr.CostPerKm,
       ROUND(d.TotalKm * tr.CostPerKm, 2) AS Expected,
       f.TotalDeliveryCost
FROM TruckFact1 f
JOIN TripDim1  d   ON d.TripID = f.TripID
JOIN TruckDim1 tr  ON tr.TruckID = f.TruckID
ORDER BY f.TripID;

学习

学习

学习

Lab04 Bridge Table

本页目录

学习

Lab04 Bridge Table

本页目录

Summary

1. A Truck Delivery Case Study – Description

2. Solution Model 1 – using a Bridge Table

a. 创建名为 TruckDim1 的维度表

b. 创建名为 TripSeason1 的维度表。该表将包含 4 条记录（Summer、Autumn、Winter 和 Spring）

c. 创建名为 TripDim1 的维度表

d. 创建名为 BridgeTableDim1 的桥接表

e. 创建名为 StoreDim1 的维度表

f. 创建一个 tempfact（并执行必要的 alter 和 update 操作），然后创建最终的事实表（将其命名为 TruckFact1）

g. 显示（并观察）事实表（TruckFact1）的内容

3. Solution Model 2 – add a Weight attribute in the Bridge

a. 创建名为 TruckDim2 的维度表。

b. 创建名为 TripSeason2 的维度表。该表将包含 4 条记录（Summer、Autumn、Winter 和 Spring）。

c. 创建名为 StoreDim2 的维度表。

d. 创建名为 BridgeTableDim2 的桥接表。

e. 创建名为 TripDim2 的维度表（注意 TripDim2 与 TripDim1 不同）。

提示（Hints）：

f. 创建一个 tempfact（并执行必要的 alter 和 update 操作），然后创建最终的事实表（将其命名为 TruckFact2）。

g. 显示（并观察）事实表（TruckFact2）的内容。

h. 每家门店的总配送成本是多少？

4. Solution Model 3 – A ListAGG version

a. 创建名为 TruckDim3 的维度表。

b. 创建名为 TripSeason3 的维度表。该表将包含 4 条记录（Summer、Autumn、Winter 和 Spring）。

c. 创建名为 StoreDim3 的维度表。

d. 创建名为 BridgeTableDim3 的桥接表。

e. 创建名为 TripDim3 的维度表（注意 TripDim3 与 TripDim1 和 TripDim2 不同）。

提示（Hints）：

f. 创建一个 tempfact（并执行必要的 alter 和 update 操作），然后创建最终的事实表（将其命名为 TruckFact3）。

g. 显示（并观察）事实表（TruckFact3）的内容。