data_entry_query.sql

-- 1. Currency Table (Strong Entity)
CREATE TABLE currency (
    currency_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    exchange_rate DECIMAL(15, 6)
);

-- 2. Country Table (Main Hub)
-- Note: currency_id is a Foreign Key as per your relational diagram
CREATE TABLE country (
    country_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    continent VARCHAR(50),
    gdp DECIMAL(18, 2),
    population BIGINT,
    currency_id INT REFERENCES currency(currency_id) ON DELETE SET NULL
);

-- 3. Alliance Table (Strong Entity)
CREATE TABLE alliance (
    alliance_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50)
);

-- 4. Conflict Table (Strong Entity)
CREATE TABLE conflict (
    conflict_id SERIAL PRIMARY KEY,
    status VARCHAR(50),
    start_date DATE
);

-- 5. Trade Table (Associative Entity for Exporter/Importer)
CREATE TABLE trade (
    trade_id SERIAL PRIMARY KEY,
    value DECIMAL(18, 2),
    volume DECIMAL(18, 2),
    duration VARCHAR(50),
    country_id_ex INT REFERENCES country(country_id) ON DELETE CASCADE,
    country_id_im INT REFERENCES country(country_id) ON DELETE CASCADE,
    CONSTRAINT different_nations CHECK (country_id_ex <> country_id_im)
);

-- 6. Commodity Table 
-- Based on your diagram, commodity links to trade via trade_id (FK)
CREATE TABLE commodity (
    commodity_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50),
    DependencyScore INT CHECK (DependencyScore BETWEEN 1 AND 10),
    trade_id INT REFERENCES trade(trade_id) ON DELETE CASCADE
);

--- JUNCTION TABLES FOR MANY-TO-MANY RELATIONSHIPS ---

-- 7. Allies_With (Recursive relationship)
CREATE TABLE allies_with (
    country_id_a INT REFERENCES country(country_id) ON DELETE CASCADE,
    country_id_b INT REFERENCES country(country_id) ON DELETE CASCADE,
    PRIMARY KEY (country_id_a, country_id_b)
);

-- 8. Membership (Country <-> Alliance)
CREATE TABLE membership (
    country_id INT REFERENCES country(country_id) ON DELETE CASCADE,
    alliance_id INT REFERENCES alliance(alliance_id) ON DELETE CASCADE,
    PRIMARY KEY (country_id, alliance_id)
);

-- 9. Involved_In (Country <-> Conflict)
CREATE TABLE involved_in (
    country_id INT REFERENCES country(country_id) ON DELETE CASCADE,
    conflict_id INT REFERENCES conflict(conflict_id) ON DELETE CASCADE,
    PRIMARY KEY (country_id, conflict_id)
);

-- 10. Languages (Multivalued attribute for Country)
CREATE TABLE languages (
    country_id INT REFERENCES country(country_id) ON DELETE CASCADE,
    language VARCHAR(50),
    PRIMARY KEY (country_id, language)
);


INSERT INTO currency (name, exchange_rate) VALUES 
('US Dollar', 1.00),
('Euro', 0.92),
('Chinese Yuan', 7.24),
('Indian Rupee', 83.35),
('Japanese Yen', 151.80),
('British Pound', 0.79),
('Russian Ruble', 92.50),
('Canadian Dollar', 1.36),
('Saudi Riyal', 3.75),
('Ukrainian Hryvnia', 39.20),
('Brazilian Real', 5.06),
('Australian Dollar', 1.53),
('UAE Dirham', 3.67),
('Norwegian Krone', 10.80),
('Indonesian Rupiah', 15880.00),
('South African Rand', 18.60),
('South Korean Won', 1350.00),
('New Taiwan Dollar', 32.10),
('Singapore Dollar', 1.35),
('Mexican Peso', 16.50),
('Swiss Franc', 0.90),
('Vietnamese Dong', 24900.00),
('Turkish Lira', 32.20),
('Thai Baht', 36.60),
('Israeli Shekel', 3.70),
('Argentine Peso', 860.00);


INSERT INTO country (name, continent, gdp, population, currency_id) VALUES 
('United States', 'North America', 31821.29, 342000000, 1),
('China', 'Asia', 20650.75, 1409000000, 3),
('Germany', 'Europe', 5328.18, 83200000, 2),
('India', 'Asia', 4505.63, 1445000000, 4),
('Japan', 'Asia', 4463.63, 122000000, 5),
('United Kingdom', 'Europe', 4225.64, 68000000, 6),
('France', 'Europe', 3558.56, 66000000, 2),
('Italy', 'Europe', 2701.54, 59000000, 2),
('Russia', 'Europe', 2509.42, 144000000, 7),
('Canada', 'North America', 2420.84, 40000000, 8),
('Brazil', 'South America', 2292.69, 216000000, 11),
('Spain', 'Europe', 2041.83, 48000000, 2),
('Mexico', 'North America', 2030.99, 129000000, 20),
('Australia', 'Oceania', 1948.23, 27000000, 12),
('South Korea', 'Asia', 1936.62, 51000000, 17),
('Turkey', 'Asia', 1576.11, 86000000, 23),
('Indonesia', 'Asia', 1550.24, 279000000, 15),
('Netherlands', 'Europe', 1413.08, 18000000, 2),
('Saudi Arabia', 'Asia', 1316.25, 37000000, 9),
('Poland', 'Europe', 1109.96, 37000000, 2),
('Switzerland', 'Europe', 1074.59, 89000000, 21),
('Taiwan', 'Asia', 971.45, 23900000, 18),
('Argentina', 'South America', 667.92, 46000000, 26),
('Israel', 'Asia', 666.41, 98000000, 25),
('Singapore', 'Asia', 606.23, 6000000, 19),
('Austria', 'Europe', 604.20, 9000000, 2),
('United Arab Emirates', 'Asia', 601.16, 10000000, 13),
('Thailand', 'Asia', 561.51, 71000000, 24),
('Norway', 'Europe', 547.69, 5500000, 14),
('Vietnam', 'Asia', 511.06, 100000000, 22);


ALTER TABLE country 
ADD CONSTRAINT unique_country_name UNIQUE (name);

INSERT INTO alliance (name, type) VALUES 
('NATO', 'Military'),
('European Union', 'Economic/Political'),
('BRICS+', 'Economic'),
('G7', 'Economic'),
('ASEAN', 'Economic/Political'),
('QUAD', 'Strategic/Security'),
('G20', 'Economic');

INSERT INTO country (name, continent, gdp, population, currency_id) 
VALUES ('Ukraine', 'Europe', 224.26, 33380000, 10);


-- TRADE DATA (Value in Millions, Volume in Tons)
INSERT INTO trade (value, volume, duration, country_id_ex, country_id_im) VALUES 
-- 1. Russia (9) Exports Crude Oil to India (4) - Massive dependency
(44970.00, 85000000, 'Annual', 9, 4),

-- 2. Russia (9) Exports Crude Oil to China (2)
(55000.00, 105000000, 'Annual', 9, 2),

-- 3. Taiwan (22) Exports Semiconductors to USA (1) - High Tech dependency
(198300.00, 50000, 'Annual', 22, 1),

-- 4. Taiwan (22) Exports Semiconductors to China (2)
(150000.00, 80000, 'Annual', 22, 2),

-- 5. Ukraine (31) Exports Wheat to India (4) - Food Security (Assumed ID 31 for Ukraine)
(850.00, 1500000, 'Annual', 31, 4),

-- 6. China (2) Exports Consumer Electronics to USA (1)
(530000.00, 12000000, 'Annual', 2, 1),

-- 7. Germany (3) Exports Automobiles to USA (1)
(35000.00, 800000, 'Annual', 3, 1),

-- 8. India (4) Exports Pharmaceuticals to USA (1)
(8000.00, 250000, 'Annual', 4, 1),

-- 9. Saudi Arabia (19) Exports Oil to Japan (5)
(32000.00, 60000000, 'Annual', 19, 5),

-- 10. Australia (14) Exports Iron Ore to China (2)
(85000.00, 700000000, 'Annual', 14, 2);


INSERT INTO commodity (name, type, dependencyscore, trade_id) VALUES 
('Crude Oil', 'Energy', 10, 1),       -- Russia to India
('Crude Oil', 'Energy', 9, 2),        -- Russia to China
('Semiconductors', 'Tech', 10, 3),    -- Taiwan to USA
('Semiconductors', 'Tech', 10, 4),    -- Taiwan to China
('Wheat', 'Food', 8, 5),              -- Ukraine to India
('Smartphones', 'Consumer', 6, 6),    -- China to USA
('Luxury Cars', 'Auto', 4, 7),        -- Germany to USA
('Generic Drugs', 'Health', 7, 8),    -- India to USA
('Refined Petroleum', 'Energy', 9, 9),-- Saudi to Japan
('Iron Ore', 'Raw Material', 9, 10);  -- Australia to China

INSERT INTO conflict (status, start_date) VALUES 
('Active', '2026-03-01'), -- Iran-US/Israel War (Strait of Hormuz Crisis)
('Active', '2022-02-24'), -- Russia-Ukraine War (Donbas Offensive)
('Active', '2026-02-15'), -- Afghanistan-Pakistan Border War
('Active', '2023-04-15'), -- Sudan Civil War (Khartoum Siege)
('Active', '2026-01-20'), -- US-Venezuela Naval Conflict (Oil Embargo)
('Resolved', '2020-09-27'), -- Nagorno-Karabakh (Peace Treaty 2023)
('Resolved', '2020-11-03'), -- Ethiopia-Tigray (Pretoria Agreement)
('Resolved', '2025-01-10'), -- Ecuador Gang War (Internal State of Emergency Ended)
('Resolved', '1992-06-23'), -- Colombia-FARC Peace Process
('Resolved', '2020-10-23'), -- Libyan Civil War (Ceasefire Agreement)
('Potential', '2026-04-15'), -- South China Sea Standoff (China-Philippines)
('Potential', '2026-05-01'), -- Guyana-Venezuela (Essequibo Oil Dispute)
('Potential', '2026-06-12'), -- Arctic Resource Scramble (NATO-Russia)
('Potential', '2026-07-04'), -- Ethiopia-Eritrea (Red Sea Port Access Dispute)
('Potential', '2026-08-20'), -- Iran-Israel Nuclear Flashpoint
('Cold', '1953-07-27'), -- Korean Peninsula (DMZ Line)
('Cold', '1992-07-21'), -- Transnistria (Moldova-Russia)
('Cold', '1974-08-16'), -- Cyprus Green Line (Turkey-Greece)
('Cold', '2008-08-12'), -- Georgia (Abkhazia/South Ossetia)
('Cold', '1947-10-22'); -- Kashmir (Line of Control)

INSERT INTO languages (country_id, language) VALUES 
(4, 'Assamese'), (4, 'Bengali'), (4, 'Bodo'), (4, 'Dogri'), (4, 'Gujarati'), 
(4, 'Hindi'), (4, 'Kannada'), (4, 'Kashmiri'), (4, 'Konkani'), (4, 'Maithili'), 
(4, 'Malayalam'), (4, 'Manipuri'), (4, 'Marathi'), (4, 'Nepali'), (4, 'Odia'), 
(4, 'Punjabi'), (4, 'Sanskrit'), (4, 'Santali'), (4, 'Sindhi'), (4, 'Tamil'), 
(4, 'Telugu'), (4, 'Urdu'),(1, 'English'), (1, 'Spanish'), -- USA
(10, 'English'), (10, 'French'), -- Canada
(13, 'Spanish'), -- Mexico
(2, 'Standard Chinese'), -- China
(5, 'Japanese'), -- Japan
(15, 'Korean'), -- South Korea
(17, 'Indonesian'), -- Indonesia
(19, 'Arabic'), -- Saudi Arabia
(22, 'Standard Chinese'), -- Taiwan
(24, 'Hebrew'), (24, 'Arabic'), -- Israel
(25, 'English'), (25, 'Mandarin'), (25, 'Malay'), (25, 'Tamil'), -- Singapore
(27, 'Arabic'), (27, 'English'), -- UAE
(28, 'Thai'), -- Thailand
(30, 'Vietnamese'), -- Vietnam
(3, 'German'), -- Germany
(6, 'English'), -- UK
(7, 'French'), -- France
(8, 'Italian'), -- Italy
(9, 'Russian'), -- Russia
(12, 'Spanish'), -- Spain
(18, 'Dutch'), -- Netherlands
(20, 'Polish'), -- Poland
(21, 'German'), (21, 'French'), (21, 'Italian'), (21, 'Romansh'), -- Switzerland
(26, 'German'), -- Austria
(29, 'Norwegian'), -- Norway
(31, 'Ukrainian'), (31, 'Russian'), -- Ukraine

-- South America & Oceania
(11, 'Portuguese'), -- Brazil
(23, 'Spanish'), -- Argentina
(14, 'English'); -- Australia


-- USA (1), Germany (3), UK (6), France (7), Poland (20), Norway (29), Spain (12), Italy (8), Canada (10)
INSERT INTO membership (country_id, alliance_id) VALUES 
(1, 1), (3, 1), (6, 1), (7, 1), (20, 1), (29, 1), (12, 1), (8, 1), (10, 1);


-- Germany (3), France (7), Italy (8), Spain (12), Netherlands (18), Poland (20), Austria (26)
INSERT INTO membership (country_id, alliance_id) VALUES 
(3, 2), (7, 2), (8, 2), (12, 2), (18, 2), (20, 2), (26, 2);

-- China (2), India (4), Russia (9), Brazil (11), UAE (27), Saudi Arabia (19), South Africa (added as Partner/Member)
INSERT INTO membership (country_id, alliance_id) VALUES 
(2, 3), (4, 3), (9, 3), (11, 3), (27, 3), (19, 3);

-- USA (1), Japan (5), Germany (3), UK (6), France (7), Italy (8), Canada (10)
INSERT INTO membership (country_id, alliance_id) VALUES 
(1, 4), (5, 4), (3, 4), (6, 4), (7, 4), (8, 4), (10, 4);

-- Indonesia (17), Singapore (25), Thailand (28), Vietnam (30)
INSERT INTO membership (country_id, alliance_id) VALUES 
(17, 5), (25, 5), (28, 5), (30, 5);

-- USA (1), India (4), Japan (5), Australia (14)
INSERT INTO membership (country_id, alliance_id) VALUES 
(1, 6), (4, 6), (5, 6), (14, 6);


INSERT INTO country (name, continent, gdp, population, currency_id) VALUES 
('Iran', 'Asia', 465.00, 89000000, 1),        -- Primary Actor (Using USD for now)
('Philippines', 'Asia', 404.00, 115000000, 1), -- South China Sea Claimant
('Qatar', 'Asia', 237.00, 2700000, 1),         -- Diplomatic/Financial Support
('Hezbollah-Lebanon', 'Asia', 20.00, 5000000, 1), -- Proxy Actor
('Houthi-Yemen', 'Asia', 15.00, 33000000, 1);


ALTER TABLE involved_in DROP CONSTRAINT IF EXISTS involved_in_pkey;
ALTER TABLE involved_in ADD PRIMARY KEY (country_id, conflict_id);

-- Middle East Crisis (Conflict ID: 1)
-- Adding Iran as a direct combatant and others as support/proxies
INSERT INTO involved_in (country_id, conflict_id) VALUES 
(32, 1), -- Iran (Direct)
(34, 1), -- Qatar (Diplomatic Support)
(35, 1), -- Hezbollah (Proxy)
(36, 1); -- Houthi (Proxy/Shipping Blockade)

-- South China Sea Standoff (Conflict ID: 3)
-- Adding the regional claimants and security partners
INSERT INTO involved_in (country_id, conflict_id) VALUES 
(33, 3), -- Philippines (Primary Claimant)
(5, 3),  -- Japan (Security Support)
(14, 3); -- Australia (Security Support)

-- Russia-Ukraine War (Conflict ID: 2)
-- Adding the 2026 'International Legion' and contractor support
INSERT INTO involved_in (country_id, conflict_id) VALUES 
(20, 2), -- Poland (Logistics Support for Ukraine)
(6, 2);  -- UK (Intelligence Support for Ukraine)

INSERT INTO involved_in (country_id, conflict_id) VALUES 
(2, 11),  -- China
(33, 11), -- Philippines (Add as ID 33)
(30, 11), -- Vietnam
(22, 11), -- Taiwan
(1, 11),  -- USA (Security Partner)
(14, 11); -- Australia (Security Partner)

-- Conflict ID: 1 (Iran-Israel War)
INSERT INTO involved_in (country_id, conflict_id) VALUES 
(1, 1),   -- USA (Direct)
(24, 1),  -- Israel (Direct)
(19, 1),  -- Saudi Arabia (Defensive/Intercepting)
(27, 1);  -- UAE (Impacted/Defensive)

-- Conflict ID: 2 (Russia-Ukraine)
INSERT INTO involved_in (country_id, conflict_id) VALUES 
(7, 2),   -- Russia
(8, 2),   -- Ukraine
(34, 2),  -- Cameroon (Contractors - Add as ID 34)
(35, 2);  -- Kenya (Contractors - Add as ID 35)


DELETE FROM involved_in a
USING involved_in b
WHERE a.ctid > b.ctid
  AND a.country_id = b.country_id
  AND a.conflict_id = b.conflict_id;


ALTER TABLE alliance ADD CONSTRAINT unique_alliance_name UNIQUE (name);
ALTER TABLE currency ADD CONSTRAINT unique_currency_name UNIQUE (name);

ALTER TABLE languages DROP CONSTRAINT IF EXISTS languages_pkey;
ALTER TABLE membership DROP CONSTRAINT IF EXISTS membership_pkey;

ALTER TABLE languages ADD PRIMARY KEY (country_id, language);
-- Ensure a country can't join the same alliance twice
ALTER TABLE membership ADD PRIMARY KEY (country_id, alliance_id);


ALTER TABLE allies_with DROP CONSTRAINT IF EXISTS allies_with_pkey;
ALTER TABLE allies_with ADD CONSTRAINT no_self_ally CHECK (country_id_a <> country_id_b);

-- 2. Ensure the relationship pair is unique
ALTER TABLE allies_with ADD PRIMARY KEY (country_id_a, country_id_b);

-- Format: (Country_ID_A, Country_ID_B)
INSERT INTO allies_with (country_id_a, country_id_b) VALUES 
-- The "Ironclad" Allies
(1, 6), (6, 1),   -- USA <-> UK
(1, 5), (5, 1),   -- USA <-> Japan
(1, 15), (15, 1), -- USA <-> South Korea
(1, 24), (24, 1), -- USA <-> Israel
(1, 14), (14, 1), -- USA <-> Australia

-- Strategic Neighbors
(3, 7), (7, 3),   -- Germany <-> France
(2, 9), (9, 2),   -- China <-> Russia

-- India's Multipolar Alliances (Very important for your logic)
(4, 9), (9, 4),   -- India <-> Russia (Defense/Energy)
(4, 1), (1, 4),   -- India <-> USA (Tech/Security)
(4, 7), (7, 4),   -- India <-> France (Defense)
(4, 14), (14, 4); -- India <-> Australia (Indo-Pacific)


-- Format: (Country_ID_A, Country_ID_B)
INSERT INTO allies_with (country_id_a, country_id_b) VALUES 
-- Nordic-Baltic Defense (Countering Russia-id 9)
(29, 20), (20, 29), -- Norway <-> Poland
(3, 29), (29, 3),   -- Germany <-> Norway

-- Middle East Strategic Hub
(19, 27), (27, 19), -- Saudi Arabia <-> UAE
(24, 27), (27, 24), -- Israel <-> UAE (Abraham Accords continuation)

-- South American Security
(11, 23), (23, 11), -- Brazil <-> Argentina

-- Southeast Asia & Oceania
(25, 14), (14, 25), -- Singapore <-> Australia
(5, 33), (33, 5),   -- Japan <-> Philippines (2026 Defense Pact)
(1, 33), (33, 1),   -- USA <-> Philippines

-- UK-Ukraine "Ironclad" (Conflict ID 2 link)
(6, 31), (31, 6);   -- UK <-> Ukraine

INSERT INTO allies_with (country_id_a, country_id_b) VALUES
-- India's Regional Stability Allies
(4, 25), (25, 4), -- India <-> Singapore (Economic/Naval)
(4, 5), (5, 4),   -- India <-> Japan (Indo-Pacific)
(4, 17), (17, 4); -- India <-> Indonesia