| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350 | 
							
- 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)
 
 
  |