CREATE TABLE BemsServiceType ( ServiceTypeId smallint NOT NULL, -- ¼­ºñ½º Á¾·ù Name nvarchar(48) NULL, -- À̸§ PRIMARY KEY (ServiceTypeId ASC) ); CREATE TABLE BemsFuelType ( FuelTypeId smallint NOT NULL, -- ¿¬·á Á¾·ù Name nvarchar(48) NULL, -- À̸§ PRIMARY KEY (FuelTypeId ASC) ); CREATE TABLE BemsFacilityType ( FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù Name nvarchar(48) NULL, -- ¼³ºñÀ̸§ PRIMARY KEY (FacilityTypeId ASC) ); CREATE TABLE BemsAnalysisType ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ AnalysisType int identity(1,1) NOT NULL, -- ºÐ¼® ¾Ë°í¸®Áò Á¾·ù Name nvarchar(48) NULL, -- ºÐ¼® ¾Ë°í¸®Áò À̸§ PRIMARY KEY (SiteId ASC , AnalysisType ASC) ); CREATE TABLE BemsMonitoringPoint ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ ValueType int NOT NULL, -- ¼³ºñ°ª À¯Çü ServiceTypeId smallint NULL, -- ¼­ºñ½º À¯Çü °íÀ¯¹øÈ£ FuelTypeId smallint NULL, -- ¼³ºñ¿¬·á À¯Çü °íÀ¯¹øÈ£ IsAccumulated bit NOT NULL, -- ´©Àû À¯¹« Name nvarchar(64) NULL, -- ¼³ºñÀ̸§ [Description] nvarchar(256) NULL, BuildingId int NULL, -- °Ç¹° °íÀ¯¹øÈ£ FloorId int NULL, -- Ãþ °íÀ¯¹øÈ£ ZoneId int NULL, -- Á¸ °íÀ¯¹øÈ£ TagInput nvarchar(100) NULL, -- ÅÂ±× ÀÔ·Â TagOutput nvarchar(100) NULL, -- ÅÂ±× Ãâ·Â PRIMARY KEY NONCLUSTERED( SiteId , FacilityCode , PropertyId ) ); CREATE TABLE BemsMonitoringPointBaseData ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ ValueType int NOT NULL, -- ¼³ºñ°ª À¯Çü IsAccumulated bit NOT NULL, -- ´©Àû À¯¹« ServiceType smallint NULL, -- ¼­ºñ½º À¯Çü °íÀ¯¹øÈ£ FuelType smallint NULL, -- ¼³ºñ¿¬·á À¯Çü °íÀ¯¹øÈ£ Name nvarchar(64) NULL, -- ¼³ºñÀ̸§ Description nvarchar(256) NULL, PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC , PropertyId ASC ) ); CREATE TABLE BemsMonitoringPointHistory15min ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ CreatedDateTime datetime NOT NULL, -- µ¥ÀÌÅÍ »ý¼º½Ã°£ CurrentValue float NOT NULL, -- ÇöÀç°ª PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC ) ); CREATE TABLE BemsMonitoringPointHistoryHourly ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ CreatedDateTime datetime NOT NULL, -- µ¥ÀÌÅÍ »ý¼º½Ã°£ CurrentValue float NOT NULL, -- ÇöÀç°ª PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC ) ); CREATE TABLE BemsMonitoringPointHistoryDaily ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ CreatedDateTime datetime NOT NULL, -- µ¥ÀÌÅÍ »ý¼º½Ã°£ CurrentValue float NOT NULL, -- ÇöÀç°ª PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC ) ); CREATE TABLE BemsMonitoringPointToAnlaysisType ( SiteId int NOT NULL , -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå PropertyId int NOT NULL, -- ¼³ºñ¼Ó¼º °íÀ¯¹øÈ£ AnalysisType int NOT NULL -- ºÐ¼® ¾Ë°í¸®Áò Á¾·ù PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC , FacilityCode ASC , PropertyId ASC , AnalysisType ASC ) ); CREATE TABLE BemsFormulaBase ( FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FormulaId int NOT NULL, -- ¼³ºñ°ø½Ä °íÀ¯¹øÈ£ Name nvarchar(32) NOT NULL, -- ¼³ºñ°ø½Ä À̸§ PRIMARY KEY ( FacilityTypeId ASC , FormulaId ASC ) ); CREATE TABLE BemsFormula ( SiteId int NOT NULL, -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù FacilityCode int NOT NULL, -- ¼³ºñÄÚµå FormulaId int NOT NULL, -- ¼³ºñ°ø½Ä °íÀ¯¹øÈ£ Formula nvarchar(120) NOT NULL, -- ¼³ºñ°ø½Ä PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC, FacilityCode ASC, FormulaId ASC ) ); ALTER TABLE BemsFormula ADD CONSTRAINT FK__BemsFormula__BemsFormulaBase FOREIGN KEY(FacilityTypeId, FormulaId) REFERENCES BemsFormulaBase (FacilityTypeId, FormulaId) ; CREATE TABLE BemsFormulaTable ( TableId int NOT NULL, -- ¼³ºñ°ø½Ä Å×ÀÌºí °íÀ¯¹øÈ£ FunctionName nvarchar(20) NOT NULL, -- ±â´É À̸§ [Description] nvarchar(80) NOT NULL, PRIMARY KEY ( TableId ASC ) ); CREATE TABLE BemsFormulaTableValue ( TableId int NOT NULL, -- ¼³ºñ°ø½Ä Å×ÀÌºí °íÀ¯¹øÈ£ XValue float NOT NULL, -- X°ª YValue float NOT NULL, -- Y°ª PRIMARY KEY ( TableId ASC , XValue ASC ) ); ALTER TABLE BemsFormulaTableValue ADD CONSTRAINT FK__BemsFormulaTableValue__BemsFormulaTable FOREIGN KEY(TableId) REFERENCES BemsFormulaTable (TableId) ; CREATE TABLE BemsFormulaParameter ( SiteId int NOT NULL, -- ÇöÀå °íÀ¯¹øÈ£ FacilityTypeId int NOT NULL, -- ¼³ºñÁ¾·ù °íÀ¯¹øÈ£ FacilityCode int NOT NULL, -- ¼³ºñÄÚµå FormulaId int NOT NULL, -- ¼³ºñ°ø½Ä °íÀ¯¹øÈ£ ParameterId nchar NOT NULL, -- ÇѰèÁ¡ °íÀ¯¹øÈ£ ParameterFacilityCode int NOT NULL, -- ¼³ºñ ÇѰèÁ¡ ÄÚµå ParameterPropertyId int NOT NULL, -- ¼³ºñ ÇѰèÁ¡ °íÀ¯¹øÈ£ PRIMARY KEY ( SiteId ASC , FacilityCode ASC, FormulaId ASC , ParameterId ASC ) ); -- TOE (Àü±â, °¡½º, ¿ë¼ö) CREATE TABLE BemsFactorToe ( FuelTypeId smallint NOT NULL, -- ¼³ºñ¿¬·á À¯Çü °íÀ¯¹øÈ£ TotalFactor float NOT NULL, -- ÃÑ ¹ß¿­·® °è¼ö PureFactor float NOT NULL, -- ¼ø ¹ß¿­·® °è¼ö PRIMARY KEY ( FuelTypeId ASC ) ); -- CO2 (Àü±â, °¡½º, ¿ë¼ö) CREATE TABLE BemsFactorCo2 ( FuelTypeId smallint NOT NULL, -- ¼³ºñ¿¬·á À¯Çü °íÀ¯¹øÈ£ Factor float NOT NULL, -- ź¼Ò¹èÃâ°Ô¼ö(Ton C/TOE) PRIMARY KEY ( FuelTypeId ASC ) ); -- BemsFormula¿¡ ¿¬µ¿µÇ¾î ÀÖÀ¸¸é¼­µµ CmFacility¸¦ FK·Î ¿¬°áÇÑ ÀÌÀ¯´Â ÆÄ¸®¹ÌÅÍ °£ ½Ã¼³ Á¾·ù¸¦ °¡Á®¿Í¾ß Çϱ⠶§¹®ÀÌ´Ù. ALTER TABLE BemsFormulaParameter ADD CONSTRAINT FK__BemsFormulaParameter__CmFacility FOREIGN KEY(SiteId, ParameterFacilityCode) REFERENCES CmFacility (SiteId, FacilityCode) ; ALTER TABLE BemsFormulaParameter ADD CONSTRAINT FK__BemsFormulaParameter__BemsFormula FOREIGN KEY(SiteId, FacilityTypeId, FacilityCode, FormulaId) REFERENCES BemsFormula (SiteId, FacilityTypeId, FacilityCode, FormulaId) ; ALTER TABLE BemsFormulaParameter ADD CONSTRAINT FK__BemsFormulaParameter__BemsMonitoringPoint FOREIGN KEY(SiteId, ParameterFacilityCode, ParameterPropertyId) REFERENCES BemsMonitoringPoint (SiteId, FacilityCode, PropertyId) ; ALTER TABLE BemsFactorToe ADD CONSTRAINT FK__BemsFactorToe__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId) ; ALTER TABLE BemsFactorCo2 ADD CONSTRAINT FK__BemsFactorCo2__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId) ; /* CREATE TABLE BemsMonitoringPointToLocation ( SiteId int NOT NULL, FacilityCode nvarchar(32) NOT NULL, PropertyId int NOT NULL, BuildingId int NULL, FloorId int NULL, ZoneId int NULL, PRIMARY KEY NONCLUSTERED (SiteId , FacilityCode , PropertyId ) ); */ ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__CmFacility FOREIGN KEY(SiteId , FacilityCode) REFERENCES CmFacility (SiteId , FacilityCode) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__CmBuilding FOREIGN KEY(SiteId , BuildingId) REFERENCES CmBuilding (SiteId , BuildingId) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__CmFloor FOREIGN KEY(SiteId , BuildingId, FloorId) REFERENCES CmFloor (SiteId , BuildingId, FloorId) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__CmZone FOREIGN KEY(SiteId ,BuildingId, FloorId, ZoneId) REFERENCES CmZone (SiteId ,BuildingId, FloorId, ZoneId) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__BemsServiceType FOREIGN KEY(ServiceTypeId) REFERENCES BemsServiceType (ServiceTypeId) ; ALTER TABLE BemsMonitoringPoint ADD CONSTRAINT FK__BemsMonitoringPoint__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId) ; CREATE CLUSTERED INDEX IX__BemsMonitoringPoint__Location ON BemsMonitoringPoint (SiteId ASC, BuildingId ASC, FloorId ASC, ZoneId ASC) ; ALTER TABLE BemsMonitoringPointBaseData ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; --ALTER TABLE BemsMonitoringPointBaseData -- ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsServiceType -- FOREIGN KEY(ServiceTypeId) REFERENCES BemsServiceType (ServiceTypeId) --; --ALTER TABLE BemsMonitoringPointBaseData -- ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId) --; ALTER TABLE BemsMonitoringPointHistory15min ADD CONSTRAINT FK__BemsMonitoringPointHistory15min__BemsMonitoringPoint FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId) ; ALTER TABLE BemsMonitoringPointHistory15min ADD CONSTRAINT FK__BemsMonitoringPointHistory15min__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; ALTER TABLE BemsMonitoringPointHistoryHourly ADD CONSTRAINT FK__BemsMonitoringPointHistoryHourly__BemsMonitoringPoint FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId) ; ALTER TABLE BemsMonitoringPointHistoryHourly ADD CONSTRAINT FK__BemsMonitoringPointHistoryHourly__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; ALTER TABLE BemsMonitoringPointHistoryDaily ADD CONSTRAINT FK__BemsMonitoringPointHistoryDaily__BemsMonitoringPoint FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId) ; ALTER TABLE BemsMonitoringPointHistoryDaily ADD CONSTRAINT FK__BemsMonitoringPointHistoryDaily__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; ALTER TABLE BemsMonitoringPointToAnlaysisType ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsAnalysisType FOREIGN KEY(SiteId , AnalysisType) REFERENCES BemsAnalysisType (SiteId , AnalysisType) ; ALTER TABLE BemsMonitoringPointToAnlaysisType ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId) ; ALTER TABLE BemsMonitoringPointToAnlaysisType ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsMonitoringPoint FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId) ; --ALTER TABLE BemsMonitoringPointToLocation -- ADD CONSTRAINT FK__BemsMonitoringPointToLocation__CmFacility -- FOREIGN KEY(SiteId , FacilityCode) REFERENCES CmFacility(SiteId, FacilityCode) --ALTER TABLE BemsMonitoringPointToLocation -- ADD CONSTRAINT FK__BemsMonitoringPointToLocation__BemsMonitoringPoint -- FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)