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 non-zero `amount`). 2. Write a query: - to get the current account balance by `account_id` (sum of amount). 3. Harder: support debit/credit (double entry). Cleanup.

sql -- 1. Creating schema 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. Query to get current balance by account_id SELECT COALESCE(SUM(amount), 0) AS balance FROM transactions WHERE account_id = $1;

-- 3. Complexity: support for debit/credit (double entry) -- It is assumed that each transaction consists of two entries: debit and credit -- For this, a ledger_entries table can be added with fields: 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 );

-- The transactions table now stores general information about the transfer (e.g., description, date) -- The account balance is calculated as the sum of amount from ledger_entries: SELECT COALESCE(SUM(amount), 0) AS balance FROM ledger_entries WHERE account_id = $1;

Thus, double entry is ensured by two records in ledger_entries: one with a negative amount (debit), and another with a positive amount (credit). This is a classic approach to accounting in banking systems.