OilSupply.sql in activefacts-examples-1.8.0

- old
+ new

@@ -33,15 +33,14 @@
 	-- Production Forecast involves Quantity,
 	Quantity                                int NOT NULL,
 	-- Production Forecast involves Refinery and Refinery has Refinery Name,
 	RefineryName                            varchar(80) NOT NULL,
 	-- Production Forecast involves Supply Period and Supply Period is in Month and Month has Month Nr,
-	SupplyPeriodMonthNr                     int NOT NULL CHECK((SupplyPeriodMonthNr >= 1 AND SupplyPeriodMonthNr <= 12)),
+	SupplyPeriodMonthNr                     int NOT NULL,
 	-- Production Forecast involves Supply Period and Supply Period is in Year and Year has Year Nr,
 	SupplyPeriodYearNr                      int NOT NULL,
 	PRIMARY KEY(RefineryName, SupplyPeriodYearNr, SupplyPeriodMonthNr, ProductName),
-	FOREIGN KEY (SupplyPeriodMonthNr) REFERENCES Month (MonthNr),
 	FOREIGN KEY (ProductName) REFERENCES Product (ProductName)
 )
 GO
 
 CREATE TABLE Refinery (
@@ -64,20 +63,29 @@
 	-- Regional Demand involves Quantity,
 	Quantity                                int NOT NULL,
 	-- Regional Demand involves Region and Region has Region Name,
 	RegionName                              varchar NOT NULL,
 	-- Regional Demand involves Supply Period and Supply Period is in Month and Month has Month Nr,
-	SupplyPeriodMonthNr                     int NOT NULL CHECK((SupplyPeriodMonthNr >= 1 AND SupplyPeriodMonthNr <= 12)),
+	SupplyPeriodMonthNr                     int NOT NULL,
 	-- Regional Demand involves Supply Period and Supply Period is in Year and Year has Year Nr,
 	SupplyPeriodYearNr                      int NOT NULL,
 	PRIMARY KEY(RegionName, SupplyPeriodYearNr, SupplyPeriodMonthNr, ProductName),
-	FOREIGN KEY (SupplyPeriodMonthNr) REFERENCES Month (MonthNr),
 	FOREIGN KEY (ProductName) REFERENCES Product (ProductName),
 	FOREIGN KEY (RegionName) REFERENCES Region (RegionName)
 )
 GO
 
+CREATE TABLE SupplyPeriod (
+	-- Supply Period is in Month and Month has Month Nr,
+	MonthNr                                 int NOT NULL,
+	-- Supply Period is in Year and Year has Year Nr,
+	YearNr                                  int NOT NULL,
+	PRIMARY KEY(YearNr, MonthNr),
+	FOREIGN KEY (MonthNr) REFERENCES Month (MonthNr)
+)
+GO
+
 CREATE TABLE TransportRoute (
 	-- maybe Transport Route incurs Cost per kl,
 	Cost                                    decimal NULL,
 	-- Transport Route involves Refinery and Refinery has Refinery Name,
 	RefineryName                            varchar(80) NOT NULL,
@@ -99,7 +107,15 @@
 	ADD FOREIGN KEY (ProductName) REFERENCES Product (ProductName)
 GO
 
 ALTER TABLE ProductionForecast
 	ADD FOREIGN KEY (RefineryName) REFERENCES Refinery (RefineryName)
+GO
+
+ALTER TABLE ProductionForecast
+	ADD FOREIGN KEY (SupplyPeriodYearNr, SupplyPeriodMonthNr) REFERENCES SupplyPeriod (YearNr, MonthNr)
+GO
+
+ALTER TABLE RegionalDemand
+	ADD FOREIGN KEY (SupplyPeriodYearNr, SupplyPeriodMonthNr) REFERENCES SupplyPeriod (YearNr, MonthNr)
 GO
 