1. -- Tworzenie tabeli urządzeń OZE
  2.  
  3. CREATE TABLE Renewable_Energy_Devices (
  4.     device_id NVARCHAR(26) PRIMARY KEY,
  5.     device_status INT CHECK (device_status >= 0 AND device_status <= 6),
  6.     device_power DECIMAL(18,0),
  7.     company_id INT
  8. );
  9.  
  10. -- Wstawianie danych do tabeli urządzeń OZE
  11. INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id)
  12. VALUES
  13. ('00000000000000000000000000',0,    1000,1),
  14. ('00000000000000000000000001',0,    1000,1),
  15. ('00000000000000000000000002',0,    1000,1),
  16. ('00000000000000000000000003',1,    2000,1),
  17. ('00000000000000000000000004',0,    1000,1),
  18. ('00000000000000000000000005',0,    1000,1),
  19. ('00000000000000000000000006',0,    1000,1),
  20. ('00000000000000000000000007',3,    1000,1),
  21. ('00000000000000000000000008',0,    5000,1),
  22. ('00000000000000000000000009',0,    1000,1),
  23. ('00000000000000000000000010',2,    1000,1),
  24. ('00000000000000000000000011',0,    1000,1),
  25. ('00000000000000000000000012',4,    1000,1),
  26. ('00000000000000000000000013',0,    1000,1),
  27. ('00000000000000000000000014',0,    1000,1),
  28. ('00000000000000000000000015',2,    4000,1),
  29. ('00000000000000000000000016',1,    1000,1),
  30. ('00000000000000000000000017',0,    1000,1),
  31. ('00000000000000000000000018',0,    1000,1),
  32. ('00000000000000000000000019',0,    1000,1),
  33. ('00000000000000000000000020',6,    7000,1),
  34. ('00000000000000000000000021',0,    1000,1),
  35. ('00000000000000000000000022',0,    1000,1),
  36. ('00000000000000000000000023',0,    1000,1),
  37. ('00000000000000000000000024',4,    1000,1),
  38. ('00000000000000000000000025',0,    1000,1),
  39. ('00000000000000000000000026',0,    1000,1),
  40. ('00000000000000000000000027',0,    1000,1),
  41. ('00000000000000000000000028',0,    1000,1),
  42. ('00000000000000000000000029',0,    1000,1),
  43. ('00000000000000000000000030',0,    1000,1),
  44. ('00000000000000000000000031',2,    8000,1),
  45. ('00000000000000000000000032',0,    1000,1),
  46. ('00000000000000000000000033',3,    1000,1),
  47. ('00000000000000000000000034',0,    1000,1),
  48. ('00000000000000000000000035',0,    1000,1),
  49. ('00000000000000000000000036',4,    1000,1),
  50. ('00000000000000000000000037',0,    1000,1),
  51. ('00000000000000000000000038',0,    1000,1),
  52. ('00000000000000000000000039',0,    1000,1),
  53. ('00000000000000000000000040',0,    1000,1),
  54. ('00000000000000000000000041',5,    1000,1),
  55. ('00000000000000000000000042',0,    1000,1),
  56. ('00000000000000000000000043',0,    1000,1),
  57. ('00000000000000000000000044',4,    1000,1),
  58. ('00000000000000000000000045',0,    1000,1),
  59. ('00000000000000000000000046',3,    9000,1),
  60. ('00000000000000000000000047',0,    1000,1),
  61. ('00000000000000000000000048',2,    1000,1),
  62. ('00000000000000000000000049',0,    1000,1),
  63. ('00000000000000000000000050',3,    7000,1);
  64.  
  65.  
  66. -- Tworzenie tabeli zgłoszeń awarii
  67.  
  68. CREATE TABLE Device_Failures (
  69.     failure_id INT PRIMARY KEY IDENTITY,
  70.     device_id NVARCHAR(26),
  71.     shift_number INT,
  72.     failure_start DATETIME,
  73.     failure_end DATETIME,
  74.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  75. );
  76.  
  77. -- Wstawianie danych do tabeli zgłoszeń awarii
  78.  
  79. DECLARE @i INT = 0
  80. WHILE @i <= 9
  81. BEGIN
  82.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  83.     VALUES (CONCAT('0000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
  84.     SET @i = @i + 1
  85. END
  86.  
  87. DECLARE @j INT = 10
  88. WHILE @j <= 50
  89. BEGIN
  90.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  91.     VALUES (CONCAT('000000000000000000000000', @j), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()));
  92.     SET @j = @j + 1
  93. END
  94.  
  95.  
  96. -- Tworzenie tabeli klientów
  97.  
  98. CREATE TABLE Customers (
  99.     customer_id INT PRIMARY KEY,
  100.     name VARCHAR(255),
  101.     email VARCHAR(255),
  102.     phone VARCHAR(20),
  103.     address VARCHAR(255)
  104. );
  105.  
  106. -- Wstawianie danych do tabeli klientów
  107. INSERT INTO Customers (customer_id, name, email, phone, address)
  108. VALUES
  109. (1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
  110. (2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St'),
  111. (3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
  112. (4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
  113. (5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
  114. (6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
  115. (7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
  116. (8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
  117. (9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
  118. (10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
  119. (11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
  120. (12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
  121. (13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
  122. (14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
  123. (15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
  124. (16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
  125. (17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
  126. (18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
  127. (19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
  128. (20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
  129. (21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
  130. (22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
  131. (23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
  132. (24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
  133. (25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');
  134.  
  135. INSERT INTO Customers (customer_id, name, email, phone, address)
  136. VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
  137.        (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');
  138.  
  139.  
  140. -- Tworzenie tabeli zamówień
  141.  
  142. CREATE TABLE Customers_Orders (
  143.     customer_order_id INT PRIMARY KEY,
  144.     customer_id INT,
  145.     customer_order_date_start DATETIME,
  146.     customer_order_date_end DATETIME,
  147.     customer_order_amount_of_energy DECIMAL(18,0),
  148.     FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  149. );
  150.  
  151. -- Wstawianie danych do tabeli zamówień
  152.  
  153. INSERT INTO Customers_Orders (customer_order_id, customer_id, customer_order_date_start, customer_order_date_end, customer_order_amount_of_energy)
  154. VALUES
  155. (1, 1, '2024-04-01 08:00:00', '2024-04-01 12:00:00', 100),
  156. (2, 1, '2024-04-02 10:00:00', '2024-04-02 14:00:00', 120),
  157. (3, 2, '2024-04-03 12:00:00', '2024-04-03 16:00:00', 150),
  158. (4, 2, '2024-04-04 09:00:00', '2024-04-04 13:00:00', 130),
  159. (5, 3, '2024-04-05 11:00:00', '2024-04-05 15:00:00', 110),
  160. (6, 3, '2024-04-06 08:00:00', '2024-04-06 12:00:00', 140),
  161. (7, 4, '2024-04-07 10:00:00', '2024-04-07 14:00:00', 125),
  162. (8, 4, '2024-04-08 12:00:00', '2024-04-08 16:00:00', 105),
  163. (9, 5, '2024-04-09 07:00:00', '2024-04-09 11:00:00', 115),
  164. (10, 5, '2024-04-10 11:00:00', '2024-04-10 15:00:00', 135),
  165. (11, 6, '2024-04-11 09:00:00', '2024-04-11 13:00:00', 155),
  166. (12, 6, '2024-04-12 08:00:00', '2024-04-12 12:00:00', 125),
  167. (13, 7, '2024-04-13 10:00:00', '2024-04-13 14:00:00', 145),
  168. (14, 7, '2024-04-14 11:00:00', '2024-04-14 15:00:00', 120),
  169. (15, 8, '2024-04-15 12:00:00', '2024-04-15 16:00:00', 130),
  170. (16, 8, '2024-04-16 08:00:00', '2024-04-16 12:00:00', 110),
  171. (17, 9, '2024-04-17 09:00:00', '2024-04-17 13:00:00', 140),
  172. (18, 9, '2024-04-18 10:00:00', '2024-04-18 14:00:00', 150),
  173. (19, 10, '2024-04-19 08:00:00', '2024-04-19 12:00:00', 120),
  174. (20, 10, '2024-04-20 11:00:00', '2024-04-20 15:00:00', 130),
  175. (21, 11, '2024-04-21 10:00:00', '2024-04-21 14:00:00', 140),
  176. (22, 11, '2024-04-22 12:00:00', '2024-04-22 16:00:00', 160),
  177. (23, 12, '2024-04-23 09:00:00', '2024-04-23 13:00:00', 125),
  178. (24, 12, '2024-04-24 07:00:00', '2024-04-24 11:00:00', 135),
  179. (25, 13, '2024-04-25 11:00:00', '2024-04-25 15:00:00', 115),
  180. (26, 13, '2024-04-26 12:00:00', '2024-04-26 16:00:00', 105),
  181. (27, 14, '2024-04-27 10:00:00', '2024-04-27 14:00:00', 145),
  182. (28, 14, '2024-04-28 08:00:00', '2024-04-28 12:00:00', 125),
  183. (29, 15, '2024-04-29 11:00:00', '2024-04-29 15:00:00', 135),
  184. (30, 15, '2024-04-30 09:00:00', '2024-04-30 13:00:00', 155),
  185. (31, 16, '2024-05-01 08:00:00', '2024-05-01 12:00:00', 125),
  186. (32, 16, '2024-05-02 10:00:00', '2024-05-02 14:00:00', 145),
  187. (33, 17, '2024-05-03 12:00:00', '2024-05-03 16:00:00', 130),
  188. (34, 17, '2024-05-04 09:00:00', '2024-05-04 13:00:00', 140),
  189. (35, 18, '2024-05-05 11:00:00', '2024-05-05 15:00:00', 150),
  190. (36, 18, '2024-05-06 08:00:00', '2024-05-06 12:00:00', 120),
  191. (37, 19, '2024-05-07 10:00:00', '2024-05-07 14:00:00', 110),
  192. (38, 19, '2024-05-08 12:00:00', '2024-05-08 16:00:00', 130),
  193. (39, 20, '2024-05-09 07:00:00', '2024-05-09 11:00:00', 140),
  194. (40, 20, '2024-05-10 11:00:00', '2024-05-10 15:00:00', 150),
  195. (41, 21, '2024-05-11 09:00:00', '2024-05-11 13:00:00', 125),
  196. (42, 21, '2024-05-12 08:00:00', '2024-05-12 12:00:00', 135),
  197. (43, 22, '2024-05-13 10:00:00', '2024-05-13 14:00:00', 115),
  198. (44, 22, '2024-05-14 11:00:00', '2024-05-14 15:00:00', 105),
  199. (45, 23, '2024-05-15 12:00:00', '2024-05-15 16:00:00', 145),
  200. (46, 23, '2024-05-16 08:00:00', '2024-05-16 12:00:00', 125),
  201. (47, 24, '2024-05-17 09:00:00', '2024-05-17 13:00:00', 135),
  202. (48, 24, '2024-05-18 10:00:00', '2024-05-18 14:00:00', 145),
  203. (49, 25, '2024-05-19 08:00:00', '2024-05-19 12:00:00', 155),
  204. (50, 25, '2024-05-20 11:00:00', '2024-05-20 15:00:00', 125);
  205.  
  206.  
  207.  
  208. -- Tworzenie tabeli firm serwisowych
  209.  
  210. CREATE TABLE Servicing_Companies (
  211.     company_id INT PRIMARY KEY,
  212.     name VARCHAR(255),
  213.     working_hours VARCHAR(255)
  214. );
  215.  
  216. -- Wstawianie danych do tabeli firm serwisowych
  217.  
  218. INSERT INTO Servicing_Companies (company_id, name, working_hours)
  219. VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');
  220.  
  221.  
  222. ----------
  223. ALTER TABLE dbo.Renewable_Energy_Devices
  224. ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);
  225. ----------
  226.  
  227. -- Tworzenie tabeli zmian serwisowych
  228.  
  229. CREATE TABLE Servicing_Shifts (
  230.     shift_number INT PRIMARY KEY IDENTITY,
  231.     shift_date DATE,
  232.     shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
  233.     start_time TIME,
  234.     end_time TIME
  235. );
  236.  
  237. -- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
  238. INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
  239. ('2024-04-01', 1, '06:00:00', '14:00:00'),
  240. ('2024-04-01', 2, '14:00:00', '22:00:00'),
  241. ('2024-04-01', 3, '22:00:00', '06:00:00'),
  242. ('2024-04-02', 1, '06:00:00', '14:00:00'),
  243. ('2024-04-02', 2, '14:00:00', '22:00:00'),
  244. ('2024-04-02', 3, '22:00:00', '06:00:00'),
  245. ('2024-04-03', 1, '06:00:00', '14:00:00'),
  246. ('2024-04-03', 2, '14:00:00', '22:00:00'),
  247. ('2024-04-03', 3, '22:00:00', '06:00:00'),
  248. ('2024-04-04', 1, '06:00:00', '14:00:00'),
  249. ('2024-04-04', 2, '14:00:00', '22:00:00'),
  250. ('2024-04-04', 3, '22:00:00', '06:00:00'),
  251. ('2024-04-05', 1, '06:00:00', '14:00:00'),
  252. ('2024-04-05', 2, '14:00:00', '22:00:00'),
  253. ('2024-04-05', 3, '22:00:00', '06:00:00'),
  254. ('2024-04-06', 1, '06:00:00', '14:00:00'),
  255. ('2024-04-06', 2, '14:00:00', '22:00:00'),
  256. ('2024-04-06', 3, '22:00:00', '06:00:00');
  257.  
  258.  
  259.  
  260. -- Tworzenie tabeli napraw
  261.  
  262.  CREATE TABLE Repairs (
  263.     repair_id INT PRIMARY KEY IDENTITY,
  264.     device_id NVARCHAR(26),
  265.     repair_start DATETIME,
  266.     repair_end DATETIME,
  267.     repair_description NVARCHAR(255),
  268.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  269. );
  270.  
  271. -- Wstawianie danych do tabeli napraw  (można kilka razy)
  272.  
  273. DECLARE @i INT = 0
  274. WHILE @i <= 9
  275. BEGIN
  276.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  277.     VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
  278.     SET @i = @i + 1
  279. END
  280.  
  281. DECLARE @j INT = 10
  282. WHILE @j <= 50
  283. BEGIN
  284.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  285.     VALUES (CONCAT('000000000000000000000000', @j), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()), 'Naprawa urządzenia');
  286.     SET @j = @j + 1
  287. END