42588e1afd88c7b81255c3057fd44e2f2507b0fe.svn-base 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350
  1. CREATE TABLE BemsServiceType
  2. (
  3. ServiceTypeId smallint NOT NULL, -- 서비스 종류
  4. Name nvarchar(48) NULL, -- 이름
  5. PRIMARY KEY (ServiceTypeId ASC)
  6. );
  7. CREATE TABLE BemsFuelType
  8. (
  9. FuelTypeId smallint NOT NULL, -- 연료 종류
  10. Name nvarchar(48) NULL, -- 이름
  11. PRIMARY KEY (FuelTypeId ASC)
  12. );
  13. CREATE TABLE BemsFacilityType
  14. (
  15. FacilityTypeId int NOT NULL, -- 설비종류
  16. Name nvarchar(48) NULL, -- 설비이름
  17. PRIMARY KEY (FacilityTypeId ASC)
  18. );
  19. CREATE TABLE BemsAnalysisType
  20. (
  21. SiteId int NOT NULL , -- 현장 고유번호
  22. AnalysisType int identity(1,1) NOT NULL, -- 분석 알고리즘 종류
  23. Name nvarchar(48) NULL, -- 분석 알고리즘 이름
  24. PRIMARY KEY (SiteId ASC , AnalysisType ASC)
  25. );
  26. CREATE TABLE BemsMonitoringPoint
  27. (
  28. SiteId int NOT NULL , -- 현장 고유번호
  29. FacilityTypeId int NOT NULL, -- 설비종류
  30. FacilityCode int NOT NULL, -- 설비코드
  31. PropertyId int NOT NULL, -- 설비속성 고유번호
  32. ValueType int NOT NULL, -- 설비값 유형
  33. ServiceTypeId smallint NULL, -- 서비스 유형 고유번호
  34. FuelTypeId smallint NULL, -- 설비연료 유형 고유번호
  35. IsAccumulated bit NOT NULL, -- 누적 유무
  36. Name nvarchar(64) NULL, -- 설비이름
  37. [Description] nvarchar(256) NULL,
  38. BuildingId int NULL, -- 건물 고유번호
  39. FloorId int NULL, -- 층 고유번호
  40. ZoneId int NULL, -- 존 고유번호
  41. TagInput nvarchar(100) NULL, -- 태그 입력
  42. TagOutput nvarchar(100) NULL, -- 태그 출력
  43. PRIMARY KEY NONCLUSTERED( SiteId , FacilityCode , PropertyId )
  44. );
  45. CREATE TABLE BemsMonitoringPointBaseData
  46. (
  47. SiteId int NOT NULL , -- 현장 고유번호
  48. FacilityTypeId int NOT NULL, -- 설비종류
  49. PropertyId int NOT NULL, -- 설비속성 고유번호
  50. ValueType int NOT NULL, -- 설비값 유형
  51. IsAccumulated bit NOT NULL, -- 누적 유무
  52. ServiceType smallint NULL, -- 서비스 유형 고유번호
  53. FuelType smallint NULL, -- 설비연료 유형 고유번호
  54. Name nvarchar(64) NULL, -- 설비이름
  55. Description nvarchar(256) NULL,
  56. PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC , PropertyId ASC )
  57. );
  58. CREATE TABLE BemsMonitoringPointHistory15min
  59. (
  60. SiteId int NOT NULL , -- 현장 고유번호
  61. FacilityTypeId int NOT NULL, -- 설비종류
  62. FacilityCode int NOT NULL, -- 설비코드
  63. PropertyId int NOT NULL, -- 설비속성 고유번호
  64. CreatedDateTime datetime NOT NULL, -- 데이터 생성시간
  65. CurrentValue float NOT NULL, -- 현재값
  66. PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC )
  67. );
  68. CREATE TABLE BemsMonitoringPointHistoryHourly
  69. (
  70. SiteId int NOT NULL , -- 현장 고유번호
  71. FacilityTypeId int NOT NULL, -- 설비종류
  72. FacilityCode int NOT NULL, -- 설비코드
  73. PropertyId int NOT NULL, -- 설비속성 고유번호
  74. CreatedDateTime datetime NOT NULL, -- 데이터 생성시간
  75. CurrentValue float NOT NULL, -- 현재값
  76. PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC )
  77. );
  78. CREATE TABLE BemsMonitoringPointHistoryDaily
  79. (
  80. SiteId int NOT NULL , -- 현장 고유번호
  81. FacilityTypeId int NOT NULL, -- 설비종류
  82. FacilityCode int NOT NULL, -- 설비코드
  83. PropertyId int NOT NULL, -- 설비속성 고유번호
  84. CreatedDateTime datetime NOT NULL, -- 데이터 생성시간
  85. CurrentValue float NOT NULL, -- 현재값
  86. PRIMARY KEY ( SiteId ASC , FacilityCode ASC , PropertyId ASC , CreatedDateTime ASC )
  87. );
  88. CREATE TABLE BemsMonitoringPointToAnlaysisType
  89. (
  90. SiteId int NOT NULL , -- 현장 고유번호
  91. FacilityTypeId int NOT NULL, -- 설비종류
  92. FacilityCode int NOT NULL, -- 설비코드
  93. PropertyId int NOT NULL, -- 설비속성 고유번호
  94. AnalysisType int NOT NULL -- 분석 알고리즘 종류
  95. PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC , FacilityCode ASC , PropertyId ASC , AnalysisType ASC )
  96. );
  97. CREATE TABLE BemsFormulaBase
  98. (
  99. FacilityTypeId int NOT NULL, -- 설비종류
  100. FormulaId int NOT NULL, -- 설비공식 고유번호
  101. Name nvarchar(32) NOT NULL, -- 설비공식 이름
  102. PRIMARY KEY ( FacilityTypeId ASC , FormulaId ASC )
  103. );
  104. CREATE TABLE BemsFormula
  105. (
  106. SiteId int NOT NULL, -- 현장 고유번호
  107. FacilityTypeId int NOT NULL, -- 설비종류
  108. FacilityCode int NOT NULL, -- 설비코드
  109. FormulaId int NOT NULL, -- 설비공식 고유번호
  110. Formula nvarchar(120) NOT NULL, -- 설비공식
  111. PRIMARY KEY ( SiteId ASC , FacilityTypeId ASC, FacilityCode ASC, FormulaId ASC )
  112. );
  113. ALTER TABLE BemsFormula
  114. ADD CONSTRAINT FK__BemsFormula__BemsFormulaBase
  115. FOREIGN KEY(FacilityTypeId, FormulaId) REFERENCES BemsFormulaBase (FacilityTypeId, FormulaId)
  116. ;
  117. CREATE TABLE BemsFormulaTable
  118. (
  119. TableId int NOT NULL, -- 설비공식 테이블 고유번호
  120. FunctionName nvarchar(20) NOT NULL, -- 기능 이름
  121. [Description] nvarchar(80) NOT NULL,
  122. PRIMARY KEY ( TableId ASC )
  123. );
  124. CREATE TABLE BemsFormulaTableValue
  125. (
  126. TableId int NOT NULL, -- 설비공식 테이블 고유번호
  127. XValue float NOT NULL, -- X값
  128. YValue float NOT NULL, -- Y값
  129. PRIMARY KEY ( TableId ASC , XValue ASC )
  130. );
  131. ALTER TABLE BemsFormulaTableValue
  132. ADD CONSTRAINT FK__BemsFormulaTableValue__BemsFormulaTable
  133. FOREIGN KEY(TableId) REFERENCES BemsFormulaTable (TableId)
  134. ;
  135. CREATE TABLE BemsFormulaParameter
  136. (
  137. SiteId int NOT NULL, -- 현장 고유번호
  138. FacilityTypeId int NOT NULL, -- 설비종류 고유번호
  139. FacilityCode int NOT NULL, -- 설비코드
  140. FormulaId int NOT NULL, -- 설비공식 고유번호
  141. ParameterId nchar NOT NULL, -- 한계점 고유번호
  142. ParameterFacilityCode int NOT NULL, -- 설비 한계점 코드
  143. ParameterPropertyId int NOT NULL, -- 설비 한계점 고유번호
  144. PRIMARY KEY ( SiteId ASC , FacilityCode ASC, FormulaId ASC , ParameterId ASC )
  145. );
  146. -- TOE (전기, 가스, 용수)
  147. CREATE TABLE BemsFactorToe
  148. (
  149. FuelTypeId smallint NOT NULL, -- 설비연료 유형 고유번호
  150. TotalFactor float NOT NULL, -- 총 발열량 계수
  151. PureFactor float NOT NULL, -- 순 발열량 계수
  152. PRIMARY KEY ( FuelTypeId ASC )
  153. );
  154. -- CO2 (전기, 가스, 용수)
  155. CREATE TABLE BemsFactorCo2
  156. (
  157. FuelTypeId smallint NOT NULL, -- 설비연료 유형 고유번호
  158. Factor float NOT NULL, -- 탄소배출게수(Ton C/TOE)
  159. PRIMARY KEY ( FuelTypeId ASC )
  160. );
  161. -- BemsFormula에 연동되어 있으면서도 CmFacility를 FK로 연결한 이유는 파리미터 간 시설 종류를 가져와야 하기 때문이다.
  162. ALTER TABLE BemsFormulaParameter
  163. ADD CONSTRAINT FK__BemsFormulaParameter__CmFacility
  164. FOREIGN KEY(SiteId, ParameterFacilityCode) REFERENCES CmFacility (SiteId, FacilityCode)
  165. ;
  166. ALTER TABLE BemsFormulaParameter
  167. ADD CONSTRAINT FK__BemsFormulaParameter__BemsFormula
  168. FOREIGN KEY(SiteId, FacilityTypeId, FacilityCode, FormulaId) REFERENCES BemsFormula (SiteId, FacilityTypeId, FacilityCode, FormulaId)
  169. ;
  170. ALTER TABLE BemsFormulaParameter
  171. ADD CONSTRAINT FK__BemsFormulaParameter__BemsMonitoringPoint
  172. FOREIGN KEY(SiteId, ParameterFacilityCode, ParameterPropertyId) REFERENCES BemsMonitoringPoint (SiteId, FacilityCode, PropertyId)
  173. ;
  174. ALTER TABLE BemsFactorToe
  175. ADD CONSTRAINT FK__BemsFactorToe__BemsFuelType
  176. FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId)
  177. ;
  178. ALTER TABLE BemsFactorCo2
  179. ADD CONSTRAINT FK__BemsFactorCo2__BemsFuelType
  180. FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId)
  181. ;
  182. /*
  183. CREATE TABLE BemsMonitoringPointToLocation
  184. (
  185. SiteId int NOT NULL,
  186. FacilityCode nvarchar(32) NOT NULL,
  187. PropertyId int NOT NULL,
  188. BuildingId int NULL,
  189. FloorId int NULL,
  190. ZoneId int NULL,
  191. PRIMARY KEY NONCLUSTERED (SiteId , FacilityCode , PropertyId )
  192. );
  193. */
  194. ALTER TABLE BemsMonitoringPoint
  195. ADD CONSTRAINT FK__BemsMonitoringPoint__CmFacility FOREIGN KEY(SiteId , FacilityCode) REFERENCES CmFacility (SiteId , FacilityCode)
  196. ;
  197. ALTER TABLE BemsMonitoringPoint
  198. ADD CONSTRAINT FK__BemsMonitoringPoint__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  199. ;
  200. ALTER TABLE BemsMonitoringPoint
  201. ADD CONSTRAINT FK__BemsMonitoringPoint__CmBuilding
  202. FOREIGN KEY(SiteId , BuildingId) REFERENCES CmBuilding (SiteId , BuildingId)
  203. ;
  204. ALTER TABLE BemsMonitoringPoint
  205. ADD CONSTRAINT FK__BemsMonitoringPoint__CmFloor
  206. FOREIGN KEY(SiteId , BuildingId, FloorId) REFERENCES CmFloor (SiteId , BuildingId, FloorId)
  207. ;
  208. ALTER TABLE BemsMonitoringPoint
  209. ADD CONSTRAINT FK__BemsMonitoringPoint__CmZone
  210. FOREIGN KEY(SiteId ,BuildingId, FloorId, ZoneId) REFERENCES CmZone (SiteId ,BuildingId, FloorId, ZoneId)
  211. ;
  212. ALTER TABLE BemsMonitoringPoint
  213. ADD CONSTRAINT FK__BemsMonitoringPoint__BemsServiceType FOREIGN KEY(ServiceTypeId) REFERENCES BemsServiceType (ServiceTypeId)
  214. ;
  215. ALTER TABLE BemsMonitoringPoint
  216. ADD CONSTRAINT FK__BemsMonitoringPoint__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId)
  217. ;
  218. CREATE CLUSTERED INDEX IX__BemsMonitoringPoint__Location
  219. ON BemsMonitoringPoint (SiteId ASC, BuildingId ASC, FloorId ASC, ZoneId ASC)
  220. ;
  221. ALTER TABLE BemsMonitoringPointBaseData
  222. ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsFacilityType FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  223. ;
  224. --ALTER TABLE BemsMonitoringPointBaseData
  225. -- ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsServiceType
  226. -- FOREIGN KEY(ServiceTypeId) REFERENCES BemsServiceType (ServiceTypeId)
  227. --;
  228. --ALTER TABLE BemsMonitoringPointBaseData
  229. -- ADD CONSTRAINT FK__BemsMonitoringPointBaseData__BemsFuelType FOREIGN KEY(FuelTypeId) REFERENCES BemsFuelType (FuelTypeId)
  230. --;
  231. ALTER TABLE BemsMonitoringPointHistory15min
  232. ADD CONSTRAINT FK__BemsMonitoringPointHistory15min__BemsMonitoringPoint
  233. FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)
  234. ;
  235. ALTER TABLE BemsMonitoringPointHistory15min
  236. ADD CONSTRAINT FK__BemsMonitoringPointHistory15min__BemsFacilityType
  237. FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  238. ;
  239. ALTER TABLE BemsMonitoringPointHistoryHourly
  240. ADD CONSTRAINT FK__BemsMonitoringPointHistoryHourly__BemsMonitoringPoint
  241. FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)
  242. ;
  243. ALTER TABLE BemsMonitoringPointHistoryHourly
  244. ADD CONSTRAINT FK__BemsMonitoringPointHistoryHourly__BemsFacilityType
  245. FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  246. ;
  247. ALTER TABLE BemsMonitoringPointHistoryDaily
  248. ADD CONSTRAINT FK__BemsMonitoringPointHistoryDaily__BemsMonitoringPoint
  249. FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)
  250. ;
  251. ALTER TABLE BemsMonitoringPointHistoryDaily
  252. ADD CONSTRAINT FK__BemsMonitoringPointHistoryDaily__BemsFacilityType
  253. FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  254. ;
  255. ALTER TABLE BemsMonitoringPointToAnlaysisType
  256. ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsAnalysisType
  257. FOREIGN KEY(SiteId , AnalysisType) REFERENCES BemsAnalysisType (SiteId , AnalysisType)
  258. ;
  259. ALTER TABLE BemsMonitoringPointToAnlaysisType
  260. ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsFacilityType
  261. FOREIGN KEY(FacilityTypeId) REFERENCES BemsFacilityType (FacilityTypeId)
  262. ;
  263. ALTER TABLE BemsMonitoringPointToAnlaysisType
  264. ADD CONSTRAINT FK__BemsMonitoringPointToAnlaysisType__BemsMonitoringPoint
  265. FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)
  266. ;
  267. --ALTER TABLE BemsMonitoringPointToLocation
  268. -- ADD CONSTRAINT FK__BemsMonitoringPointToLocation__CmFacility
  269. -- FOREIGN KEY(SiteId , FacilityCode) REFERENCES CmFacility(SiteId, FacilityCode)
  270. --ALTER TABLE BemsMonitoringPointToLocation
  271. -- ADD CONSTRAINT FK__BemsMonitoringPointToLocation__BemsMonitoringPoint
  272. -- FOREIGN KEY(SiteId , FacilityCode , PropertyId) REFERENCES BemsMonitoringPoint (SiteId , FacilityCode , PropertyId)