Model a simple banking system: - `accounts` table: - `id`, - `owner_id`, - `currency`, - `created_at`. - `transactions` table: - `id`, - `account_id`, - `amount`, - `created_at`. **Requirements:** 1. Write SQL to create the schema with necessary constraints (NOT NULL, FK, CHECK on `amount` not null). 2. Write a query: - to get the current account balance by `account_id` (sum of `amount`). 3. Extra challenge: support debit/credit (double entry). Cleanup.

sql -- 1. Tabeli loomine CREATE TABLE accounts ( id SERIAL PRIMARY KEY, owner_id INT NOT NULL, currency VARCHAR(3) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() );

CREATE TABLE transactions ( id SERIAL PRIMARY KEY, account_id INT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, amount NUMERIC NOT NULL CHECK (amount <> 0), created_at TIMESTAMP NOT NULL DEFAULT NOW() );

-- 2. Päring konto saldo saamiseks konto_id järgi SELECT COALESCE(SUM(amount), 0) AS balance FROM transactions WHERE account_id = $1;

-- 3. Raskendamine: deebet/krediidi tugi (topeltarvestus) -- Eeldatakse, et iga tehing koosneb kahest kirjest: deebet ja krediit -- Selleks võib lisada tabeli ledger_entries, mille väljad on: CREATE TABLE ledger_entries ( id SERIAL PRIMARY KEY, transaction_id INT NOT NULL, account_id INT NOT NULL REFERENCES accounts(id), amount NUMERIC NOT NULL CHECK (amount <> 0), created_at TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT fk_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE );

-- Tabel transactions nüüd salvestab ülemaailmse teabe tehingu kohta (näiteks kirjeldus, kuupäev) -- Konto saldo arvutatakse ledger_entries tabeli amount väljade summast: SELECT COALESCE(SUM(amount), 0) AS balance FROM ledger_entries WHERE account_id = $1;

Seega tagab topeltarvestus kaks kirjet ledger_entries tabelis: üks negatiivse (väljamakse), teine positiivse (laekumine) summaga. See on klassikaline lähenemine raamatupidamissüsteemides.