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