Repository navigation
Expand file tree
/
Copy path02_seed_data.sql
More file actions
217 lines (205 loc) · 16.8 KB
/
Copy path02_seed_data.sql
File metadata and controls
217 lines (205 loc) · 16.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
-- =============================================================================
-- 02_seed_data.sql
--
-- A small, hand-readable data set. No generator: every number is written
-- here, and the whole file can be read in a few minutes.
--
-- * Analyses of the pure chemicals (boric acid, borax, carbonates) are close
-- to their theoretical composition; natural minerals (feldspars, kaolin,
-- talc) have invented but typical-looking impurities.
-- * Prices, exchange rates, forecast and offers are invented.
-- * Four problems are planted on purpose and found by later files:
-- - wollastonite: certificate without LOI (03)
-- - strontium carbonate: no analysis, no price, unused (03)
-- - talc: price contract expired, no current price (03, 06)
-- - zinc oxide: two price rows overlap for a month (06)
-- =============================================================================
USE glaze_bom;
-- ── Oxides ───────────────────────────────────────────────────────────────────
-- Molar masses from IUPAC standard atomic weights. Appen factors (10^-7/K per
-- mole fraction): A. A. Appen, Glass Chemistry, 2nd ed., Leningrad 1974, as
-- tabulated by A. Fluegel, glassproperties.com. SiO2, B2O3, TiO2 and K2O have
-- composition-dependent factors (NULL here), computed in file 05.
INSERT INTO oxides (oxide_code, molar_mass, seger_group, appen_factor) VALUES
('Li2O' , 29.879, 'R2O' , 270),
('Na2O' , 61.979, 'R2O' , 395),
('K2O' , 94.195, 'R2O' , NULL),
('MgO' , 40.304, 'RO' , 60),
('CaO' , 56.077, 'RO' , 130),
('SrO' , 103.619, 'RO' , 160),
('BaO' , 153.326, 'RO' , 200),
('ZnO' , 81.379, 'RO' , 50),
('Al2O3' , 101.961, 'R2O3', -30),
('B2O3' , 69.617, 'R2O3', NULL),
('Fe2O3' , 159.687, 'R2O3', 55),
('SiO2' , 60.083, 'RO2' , NULL),
('TiO2' , 79.865, 'RO2' , NULL),
('ZrO2' , 123.222, 'RO2' , -60);
-- ── Suppliers ────────────────────────────────────────────────────────────────
INSERT INTO suppliers (supplier_code, supplier_name) VALUES
('SUP-A', 'Supplier A'),
('SUP-B', 'Supplier B'),
('SUP-C', 'Supplier C'),
('SUP-D', 'Supplier D'),
('SUP-E', 'Supplier E'),
('SUP-F', 'Supplier F');
-- ── Items ────────────────────────────────────────────────────────────────────
-- Raw materials: LOI = loss on ignition (CO2, water) — the part of the weight
-- that leaves as gas when the material is melted or fired.
INSERT INTO items (item_code, item_name, item_type, family, loi_pct) VALUES
('QTZ-01', 'Quartz sand' , 'raw', NULL, 0.2),
('KAO-01', 'Kaolin' , 'raw', NULL, 12.5),
('FSK-01', 'Potash feldspar' , 'raw', NULL, 0.5),
('FSK-02', 'Potash feldspar, supplier B' , 'raw', NULL, 0.5),
('FSN-01', 'Soda feldspar, supplier C' , 'raw', NULL, 0.5),
('NEP-01', 'Nepheline syenite' , 'raw', NULL, 0.6),
('CAC-01', 'Calcium carbonate' , 'raw', NULL, 43.65),
('DOL-01', 'Dolomite' , 'raw', NULL, 47.1),
('WOL-01', 'Wollastonite' , 'raw', NULL, NULL),
('TAL-01', 'Talc' , 'raw', NULL, 5.9),
('ZNO-01', 'Zinc oxide' , 'raw', NULL, 0.3),
('ALU-01', 'Calcined alumina' , 'raw', NULL, 0.13),
('ZRS-01', 'Zircon flour' , 'raw', NULL, 0.57),
('BOA-01', 'Boric acid' , 'raw', NULL, 43.7),
('BRX-01', 'Borax pentahydrate' , 'raw', NULL, 30.9),
('BAC-01', 'Barium carbonate' , 'raw', NULL, 22.4),
('SRC-01', 'Strontium carbonate' , 'raw', NULL, NULL),
('TRN-101', 'Transparent frit, calcium-zinc' , 'frit', 'transparent', NULL),
('TRN-102', 'Transparent frit, borosilicate' , 'frit', 'transparent', NULL),
('OPQ-110', 'White opaque frit, zircon' , 'frit', 'opaque' , NULL),
('MAT-120', 'Matte frit, calcium-zinc' , 'frit', 'matte' , NULL),
('MAT-121', 'Matte frit, barium' , 'frit', 'matte' , NULL),
('GL-201', 'Glossy transparent glaze' , 'glaze', 'transparent', NULL),
('GL-202', 'Transparent wall-tile glaze' , 'glaze', 'transparent', NULL),
('GL-210', 'White opaque glaze' , 'glaze', 'opaque' , NULL),
('GL-211', 'White opaque glaze, high feldspar' , 'glaze', 'opaque' , NULL),
('GL-220', 'Matte glaze' , 'glaze', 'matte' , NULL),
('GL-221', 'Satin-matte barium glaze' , 'glaze', 'matte' , NULL),
('GL-230', 'White engobe' , 'glaze', 'engobe' , NULL),
('GL-240', 'Semi-opaque glaze, blend' , 'glaze', 'opaque' , NULL);
-- ── Analyses (weight %, without LOI) ─────────────────────────────────────────
INSERT INTO raw_analysis (item_code, oxide_code, wt_pct) VALUES
('QTZ-01','SiO2',99.4), ('QTZ-01','Al2O3',0.25), ('QTZ-01','Fe2O3',0.03), ('QTZ-01','TiO2',0.04), ('QTZ-01','K2O',0.05), ('QTZ-01','CaO',0.03),
('KAO-01','SiO2',48.2), ('KAO-01','Al2O3',36.4), ('KAO-01','Fe2O3',0.75), ('KAO-01','TiO2',0.3), ('KAO-01','K2O',1.3), ('KAO-01','Na2O',0.1), ('KAO-01','MgO',0.25), ('KAO-01','CaO',0.2),
('FSK-01','SiO2',65.8), ('FSK-01','Al2O3',18.6), ('FSK-01','K2O',11.6), ('FSK-01','Na2O',3), ('FSK-01','CaO',0.3), ('FSK-01','Fe2O3',0.1), ('FSK-01','TiO2',0.02), ('FSK-01','MgO',0.08), -- the material the offers in file 07 would replace
('FSK-02','SiO2',65.4), ('FSK-02','Al2O3',18.8), ('FSK-02','K2O',11.2), ('FSK-02','Na2O',3.3), ('FSK-02','CaO',0.4), ('FSK-02','Fe2O3',0.24), ('FSK-02','TiO2',0.06), ('FSK-02','MgO',0.1), -- offer OFR-1: same mineral, a little more iron
('FSN-01','SiO2',68.2), ('FSN-01','Al2O3',19.1), ('FSN-01','Na2O',10.9), ('FSN-01','K2O',0.5), ('FSN-01','CaO',0.6), ('FSN-01','Fe2O3',0.08), ('FSN-01','TiO2',0.07), ('FSN-01','MgO',0.05), -- offer OFR-2: sodium instead of potassium
('NEP-01','SiO2',60.3), ('NEP-01','Al2O3',23.4), ('NEP-01','Na2O',10.3), ('NEP-01','K2O',4.9), ('NEP-01','CaO',0.4), ('NEP-01','Fe2O3',0.08), ('NEP-01','MgO',0.02),
('CAC-01','CaO',55.4), ('CAC-01','MgO',0.5), ('CAC-01','SiO2',0.3), ('CAC-01','Al2O3',0.1), ('CAC-01','Fe2O3',0.05), -- CaCO3: 56.0 % CaO in theory
('DOL-01','CaO',30.6), ('DOL-01','MgO',21.2), ('DOL-01','SiO2',0.8), ('DOL-01','Al2O3',0.2), ('DOL-01','Fe2O3',0.1), -- CaMg(CO3)2
('WOL-01','SiO2',50.2), ('WOL-01','CaO',46.3), ('WOL-01','Al2O3',0.4), ('WOL-01','Fe2O3',0.25), ('WOL-01','MgO',0.6), -- certificate without LOI - see file 03
('TAL-01','SiO2',61.8), ('TAL-01','MgO',30.9), ('TAL-01','CaO',0.6), ('TAL-01','Al2O3',0.5), ('TAL-01','Fe2O3',0.3),
('ZNO-01','ZnO',99.7),
('ALU-01','Al2O3',99.5), ('ALU-01','Na2O',0.3), ('ALU-01','SiO2',0.05), ('ALU-01','Fe2O3',0.02),
('ZRS-01','ZrO2',65.6), ('ZRS-01','SiO2',33), ('ZRS-01','Al2O3',0.6), ('ZRS-01','TiO2',0.15), ('ZRS-01','Fe2O3',0.08), -- ZrSiO4; ZrO2 includes HfO2
('BOA-01','B2O3',56.3), -- H3BO3: 56.30 % B2O3 in theory
('BRX-01','Na2O',21.3), ('BRX-01','B2O3',47.8), -- Na2B4O7·5H2O: 21.28 % Na2O, 47.80 % B2O3 in theory
('BAC-01','BaO',77.3), ('BAC-01','SrO',0.3); -- BaCO3: 77.7 % BaO in theory
-- ── Recipes ──────────────────────────────────────────────────────────────────
-- Frits: batch recipes, parts by weight of raw materials going into the furnace.
INSERT INTO recipe_lines (parent_code, component_code, parts) VALUES
('TRN-101','QTZ-01',38), ('TRN-101','FSK-01',25), ('TRN-101','CAC-01',16), ('TRN-101','ZNO-01',7), ('TRN-101','DOL-01',5), ('TRN-101','KAO-01',3), ('TRN-101','BOA-01',4), ('TRN-101','BRX-01',2),
('TRN-102','QTZ-01',34), ('TRN-102','FSK-01',15), ('TRN-102','BRX-01',16), ('TRN-102','BOA-01',10), ('TRN-102','CAC-01',12), ('TRN-102','ZNO-01',3), ('TRN-102','KAO-01',5), ('TRN-102','DOL-01',5),
('OPQ-110','QTZ-01',29), ('OPQ-110','ZRS-01',15), ('OPQ-110','FSK-01',24), ('OPQ-110','CAC-01',13), ('OPQ-110','ZNO-01',6), ('OPQ-110','BOA-01',6), ('OPQ-110','BRX-01',3), ('OPQ-110','KAO-01',2), ('OPQ-110','DOL-01',2),
('MAT-120','QTZ-01',24), ('MAT-120','CAC-01',27), ('MAT-120','FSK-01',22), ('MAT-120','ZNO-01',9), ('MAT-120','KAO-01',9), ('MAT-120','ALU-01',4), ('MAT-120','BOA-01',3), ('MAT-120','WOL-01',2),
('MAT-121','QTZ-01',28), ('MAT-121','BAC-01',16), ('MAT-121','FSK-01',22), ('MAT-121','ZNO-01',8), ('MAT-121','CAC-01',8), ('MAT-121','KAO-01',7), ('MAT-121','ALU-01',3), ('MAT-121','BOA-01',4), ('MAT-121','TAL-01',2), ('MAT-121','BRX-01',2);
-- Glazes: mill recipes, parts by weight of dry material. GL-240 is a blend of
-- two finished glazes, which gives the BOM a third level.
INSERT INTO recipe_lines (parent_code, component_code, parts) VALUES
('GL-201','TRN-101',85), ('GL-201','KAO-01',8), ('GL-201','QTZ-01',4), ('GL-201','FSK-01',3),
('GL-202','TRN-102',90), ('GL-202','KAO-01',7), ('GL-202','FSK-01',3),
('GL-210','OPQ-110',75), ('GL-210','ZRS-01',8), ('GL-210','KAO-01',8), ('GL-210','FSK-01',6), ('GL-210','ALU-01',3),
('GL-211','OPQ-110',45), ('GL-211','FSK-01',25), ('GL-211','KAO-01',10), ('GL-211','ZRS-01',10), ('GL-211','QTZ-01',5), ('GL-211','WOL-01',5),
('GL-220','MAT-120',70), ('GL-220','FSK-01',12), ('GL-220','KAO-01',10), ('GL-220','WOL-01',5), ('GL-220','ALU-01',3),
('GL-221','MAT-121',60), ('GL-221','NEP-01',15), ('GL-221','KAO-01',10), ('GL-221','FSK-01',10), ('GL-221','QTZ-01',5),
('GL-230','FSK-01',30), ('GL-230','KAO-01',25), ('GL-230','QTZ-01',15), ('GL-230','ZRS-01',10), ('GL-230','TRN-102',20),
('GL-240','GL-210',70), ('GL-240','GL-201',30);
-- ── Prices ───────────────────────────────────────────────────────────────────
-- price_per_t in the currency of the row; valid_to inclusive, NULL = current.
INSERT INTO price_list (item_code, supplier_code, valid_from, valid_to, price_per_t, currency) VALUES
('QTZ-01', 'SUP-A', '2023-01-01', '2023-12-31', 1150.00, 'CZK'),
('QTZ-01', 'SUP-A', '2024-01-01', '2024-12-31', 1200.00, 'CZK'),
('QTZ-01', 'SUP-A', '2025-01-01', '2025-12-31', 1250.00, 'CZK'),
('QTZ-01', 'SUP-A', '2026-01-01', NULL , 1290.00, 'CZK'),
('KAO-01', 'SUP-A', '2023-01-01', '2023-12-31', 4300.00, 'CZK'),
('KAO-01', 'SUP-A', '2024-01-01', '2024-12-31', 4500.00, 'CZK'),
('KAO-01', 'SUP-A', '2025-01-01', '2025-12-31', 4650.00, 'CZK'),
('KAO-01', 'SUP-A', '2026-01-01', NULL , 4800.00, 'CZK'),
('FSK-01', 'SUP-A', '2023-01-01', '2023-12-31', 5200.00, 'CZK'),
('FSK-01', 'SUP-A', '2024-01-01', '2024-12-31', 5450.00, 'CZK'),
('FSK-01', 'SUP-A', '2025-01-01', '2025-12-31', 5600.00, 'CZK'),
('FSK-01', 'SUP-A', '2026-01-01', NULL , 5750.00, 'CZK'),
('CAC-01', 'SUP-A', '2023-01-01', '2023-12-31', 2250.00, 'CZK'),
('CAC-01', 'SUP-A', '2024-01-01', '2024-12-31', 2350.00, 'CZK'),
('CAC-01', 'SUP-A', '2025-01-01', '2025-12-31', 2400.00, 'CZK'),
('CAC-01', 'SUP-A', '2026-01-01', NULL , 2480.00, 'CZK'),
('DOL-01', 'SUP-A', '2023-01-01', '2023-12-31', 2600.00, 'CZK'),
('DOL-01', 'SUP-A', '2024-01-01', '2024-12-31', 2700.00, 'CZK'),
('DOL-01', 'SUP-A', '2025-01-01', '2025-12-31', 2750.00, 'CZK'),
('DOL-01', 'SUP-A', '2026-01-01', NULL , 2800.00, 'CZK'),
('NEP-01', 'SUP-D', '2023-01-01', '2024-03-31', 330.00, 'EUR'),
('NEP-01', 'SUP-D', '2024-04-01', '2025-06-30', 345.00, 'EUR'),
('NEP-01', 'SUP-D', '2025-07-01', NULL , 352.00, 'EUR'),
('WOL-01', 'SUP-D', '2023-01-01', '2024-06-30', 395.00, 'EUR'),
('WOL-01', 'SUP-D', '2024-07-01', NULL , 415.00, 'EUR'),
('TAL-01', 'SUP-D', '2023-01-01', '2024-06-30', 440.00, 'EUR'),
('TAL-01', 'SUP-D', '2024-07-01', '2025-12-31', 455.00, 'EUR'),
('ZNO-01', 'SUP-E', '2023-01-01', '2023-06-30', 3100.00, 'EUR'),
('ZNO-01', 'SUP-E', '2023-07-01', '2024-03-31', 2750.00, 'EUR'),
('ZNO-01', 'SUP-E', '2024-04-01', '2025-01-31', 2900.00, 'EUR'),
('ZNO-01', 'SUP-E', '2025-01-01', '2025-12-31', 3050.00, 'EUR'),
('ZNO-01', 'SUP-E', '2026-01-01', NULL , 2950.00, 'EUR'),
('ALU-01', 'SUP-E', '2023-01-01', '2023-12-31', 820.00, 'EUR'),
('ALU-01', 'SUP-E', '2024-01-01', '2025-06-30', 900.00, 'EUR'),
('ALU-01', 'SUP-E', '2025-07-01', NULL , 875.00, 'EUR'),
('ZRS-01', 'SUP-E', '2023-01-01', '2024-03-31', 2250.00, 'EUR'),
('ZRS-01', 'SUP-E', '2024-04-01', '2024-12-31', 2050.00, 'EUR'),
('ZRS-01', 'SUP-E', '2025-01-01', '2025-12-31', 1950.00, 'EUR'),
('ZRS-01', 'SUP-E', '2026-01-01', NULL , 2010.00, 'EUR'),
('BOA-01', 'SUP-F', '2023-01-01', '2023-09-30', 1050.00, 'EUR'),
('BOA-01', 'SUP-F', '2023-10-01', '2024-12-31', 1180.00, 'EUR'),
('BOA-01', 'SUP-F', '2025-01-01', NULL , 1140.00, 'EUR'),
('BRX-01', 'SUP-F', '2023-01-01', '2023-09-30', 880.00, 'EUR'),
('BRX-01', 'SUP-F', '2023-10-01', '2024-12-31', 960.00, 'EUR'),
('BRX-01', 'SUP-F', '2025-01-01', NULL , 935.00, 'EUR'),
('BAC-01', 'SUP-E', '2023-01-01', '2023-12-31', 960.00, 'EUR'),
('BAC-01', 'SUP-E', '2024-01-01', '2025-12-31', 990.00, 'EUR'),
('BAC-01', 'SUP-E', '2026-01-01', NULL , 1015.00, 'EUR');
-- Planted: ZNO-01's 2024 row should have ended on 2024-12-31. It runs to
-- 2025-01-31, so January 2025 has two valid prices. TAL-01 has no row after
-- 2025-12-31: the contract ran out and nobody entered the new one.
-- ── Exchange rates (CZK per EUR, monthly; illustrative, not official) ────────
INSERT INTO fx_rates (month_start, czk_per_eur) VALUES
('2023-01-01',24.100), ('2023-02-01',23.800), ('2023-03-01',23.600), ('2023-04-01',23.500), ('2023-05-01',23.600), ('2023-06-01',23.700),
('2023-07-01',24.000), ('2023-08-01',24.100), ('2023-09-01',24.300), ('2023-10-01',24.600), ('2023-11-01',24.500), ('2023-12-01',24.500),
('2024-01-01',24.600), ('2024-02-01',25.100), ('2024-03-01',25.300), ('2024-04-01',25.300), ('2024-05-01',24.800), ('2024-06-01',24.800),
('2024-07-01',25.100), ('2024-08-01',25.200), ('2024-09-01',25.100), ('2024-10-01',25.300), ('2024-11-01',25.300), ('2024-12-01',25.100),
('2025-01-01',25.200), ('2025-02-01',25.100), ('2025-03-01',24.900), ('2025-04-01',25.000), ('2025-05-01',24.900), ('2025-06-01',24.700),
('2025-07-01',24.600), ('2025-08-01',24.500), ('2025-09-01',24.400), ('2025-10-01',24.400), ('2025-11-01',24.300), ('2025-12-01',24.300),
('2026-01-01',24.300), ('2026-02-01',24.300), ('2026-03-01',24.400), ('2026-04-01',24.300), ('2026-05-01',24.300), ('2026-06-01',24.200),
('2026-07-01',24.300), ('2026-08-01',24.300), ('2026-09-01',24.400);
-- ── Sales forecast, tonnes per year ──────────────────────────────────────────
INSERT INTO sales_forecast (item_code, tonnes_per_year) VALUES
('TRN-101', 3200.0),
('TRN-102', 1800.0),
('OPQ-110', 2400.0),
('MAT-120', 900.0),
('MAT-121', 600.0),
('GL-201', 1500.0),
('GL-202', 1100.0),
('GL-210', 1300.0),
('GL-211', 700.0),
('GL-220', 500.0),
('GL-221', 400.0),
('GL-230', 900.0),
('GL-240', 300.0);
-- ── Supplier offers (file 07) ────────────────────────────────────────────────
INSERT INTO supplier_offers (offer_code, item_code, replaces_code, supplier_code, price_per_t, currency) VALUES
('OFR-1', 'FSK-02', 'FSK-01', 'SUP-B', 221.00, 'EUR'),
('OFR-2', 'FSN-01', 'FSK-01', 'SUP-C', 190.00, 'EUR');
-- ── Substitution rules (file 07) ─────────────────────────────────────────────
INSERT INTO substitution_rules (rule_code, applies_to, ok_max, trial_max, note) VALUES
('flux_shift', 'all', 0.020, 0.050, 'largest change of any flux oxide in the Seger formula, mol'),
('cte_shift', 'all', 1.000, 2.000, 'change of thermal expansion, 10^-7/K (Appen estimate)'),
('fe2o3_rise', 'opaque', 0.020, 0.040, 'more Fe2O3, wt %: a white glaze turns cream'),
('fe2o3_rise', 'engobe', 0.020, 0.040, 'more Fe2O3, wt %: an engobe has to hide the body colour');