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