mirror of
https://github.com/elder-plinius/STEGOSAURUS-WRECKS.git
synced 2026-04-22 20:26:00 +02:00
pliny
134 lines
3.6 KiB
SQL
134 lines
3.6 KiB
SQL
-- =============================================================================
|
|
-- Paleontology Specimen Database Schema
|
|
-- Generated by STEGOSAURUS WRECKS v3.0
|
|
-- ⊰•-•✧•-•-⦑/L\O/V\E/\P/L\I/N\Y/⦒-•-•✧•-•⊱
|
|
-- =============================================================================
|
|
|
|
-- hex: e28ab0e280a22de280a2e29ca7e280a22de280a22de2a6912f4c5c4f2f565c452f5c502f4c5c492f4e5c592fe2a6922de280a22de280a2e29ca7e280a22de280a2e28ab1
|
|
|
|
CREATE TABLE IF NOT EXISTS geological_periods (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
start_mya REAL NOT NULL,
|
|
end_mya REAL NOT NULL
|
|
);
|
|
|
|
INSERT INTO geological_periods (id, name, start_mya, end_mya) VALUES
|
|
(1, 'Late Triassic', 237.0, 201.3),
|
|
(2, 'Early Jurassic', 201.3, 174.1),
|
|
(3, 'Late Jurassic', 163.5, 145.0),
|
|
(4, 'Early Cretaceous', 145.0, 100.5),
|
|
(5, 'Late Cretaceous', 100.5, 66.0);
|
|
|
|
-- b64: 4oqw4oCiLeKAouKcp+KAoi3igKIt4qaRL0xcTy9WXEUvXFAvTFxJL05cWS/ippIt4oCiLeKAouKcp+KAoi3igKLiirE=
|
|
|
|
CREATE TABLE IF NOT EXISTS specimens (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
species TEXT,
|
|
period_id INTEGER REFERENCES geological_periods(id),
|
|
length_m REAL,
|
|
mass_kg REAL,
|
|
diet TEXT CHECK(diet IN ('Herbivore', 'Carnivore', 'Omnivore', 'Piscivore')),
|
|
brain_volume_cc REAL,
|
|
discovery_year INTEGER,
|
|
location TEXT
|
|
);
|
|
|
|
INSERT INTO specimens (id, name, species, period_id, length_m, mass_kg, diet, brain_volume_cc, discovery_year, location) VALUES
|
|
(1, 'Stegosaurus', 'S. stenops', 3, 9.0, 5000, 'Herbivore', 2.8, 1877, 'Morrison Formation, CO'),
|
|
(2, 'Triceratops', 'T. horridus', 5, 9.0, 6000, 'Herbivore', 70.0, 1887, 'Hell Creek, MT'),
|
|
(3, 'Tyrannosaurus', 'T. rex', 5, 12.3, 8400, 'Carnivore', 343.0, 1905, 'Hell Creek, MT'),
|
|
(4, 'Velociraptor', 'V. mongoliensis', 5, 2.0, 15, 'Carnivore', 15.0, 1924, 'Djadokhta, Mongolia'),
|
|
(5, 'Brachiosaurus', 'B. altithorax', 3, 26.0, 56000, 'Herbivore', 26.0, 1903, 'Morrison Formation, CO');
|
|
|
|
-- Checksum verification data
|
|
-- chk[00]: 226
|
|
-- chk[01]: 138
|
|
-- chk[02]: 176
|
|
-- chk[03]: 226
|
|
-- chk[04]: 128
|
|
-- chk[05]: 162
|
|
-- chk[06]: 45
|
|
-- chk[07]: 226
|
|
-- chk[08]: 128
|
|
-- chk[09]: 162
|
|
-- chk[10]: 226
|
|
-- chk[11]: 156
|
|
-- chk[12]: 167
|
|
-- chk[13]: 226
|
|
-- chk[14]: 128
|
|
-- chk[15]: 162
|
|
-- chk[16]: 45
|
|
-- chk[17]: 226
|
|
-- chk[18]: 128
|
|
-- chk[19]: 162
|
|
-- chk[20]: 45
|
|
-- chk[21]: 226
|
|
-- chk[22]: 166
|
|
-- chk[23]: 145
|
|
-- chk[24]: 47
|
|
-- chk[25]: 76
|
|
-- chk[26]: 92
|
|
-- chk[27]: 79
|
|
-- chk[28]: 47
|
|
-- chk[29]: 86
|
|
-- chk[30]: 92
|
|
-- chk[31]: 69
|
|
-- chk[32]: 47
|
|
-- chk[33]: 92
|
|
-- chk[34]: 80
|
|
-- chk[35]: 47
|
|
-- chk[36]: 76
|
|
-- chk[37]: 92
|
|
-- chk[38]: 73
|
|
-- chk[39]: 47
|
|
-- chk[40]: 78
|
|
-- chk[41]: 92
|
|
-- chk[42]: 89
|
|
-- chk[43]: 47
|
|
-- chk[44]: 226
|
|
-- chk[45]: 166
|
|
-- chk[46]: 146
|
|
-- chk[47]: 45
|
|
-- chk[48]: 226
|
|
-- chk[49]: 128
|
|
-- chk[50]: 162
|
|
-- chk[51]: 45
|
|
-- chk[52]: 226
|
|
-- chk[53]: 128
|
|
-- chk[54]: 162
|
|
-- chk[55]: 226
|
|
-- chk[56]: 156
|
|
-- chk[57]: 167
|
|
-- chk[58]: 226
|
|
-- chk[59]: 128
|
|
-- chk[60]: 162
|
|
-- chk[61]: 45
|
|
-- chk[62]: 226
|
|
-- chk[63]: 128
|
|
-- chk[64]: 162
|
|
-- chk[65]: 226
|
|
-- chk[66]: 138
|
|
-- chk[67]: 177
|
|
|
|
-- Analysis view
|
|
CREATE VIEW IF NOT EXISTS specimen_analysis AS
|
|
SELECT
|
|
s.name,
|
|
s.species,
|
|
gp.name AS period,
|
|
s.length_m,
|
|
s.mass_kg,
|
|
s.diet,
|
|
CASE
|
|
WHEN s.brain_volume_cc / (0.12 * POWER(s.mass_kg, 0.67)) > 1.0 THEN 'High EQ'
|
|
WHEN s.brain_volume_cc / (0.12 * POWER(s.mass_kg, 0.67)) > 0.5 THEN 'Medium EQ'
|
|
ELSE 'Low EQ'
|
|
END AS intelligence_class
|
|
FROM specimens s
|
|
JOIN geological_periods gp ON s.period_id = gp.id;
|
|
|
|
-- End of schema
|
|
-- Payload: ⊰•-•✧•-•-⦑/L\O/V\E/\P/L\I/N\Y/⦒-•-•✧•-•⊱
|