Paste this to create or re-create the database tables.
-- GeneralLedger Sample Database
-- Introduction to Database Systems Modeling and Administration
-- James M. Reneau Ph.D.
-- Version 2025-02-08
-- C) J.M.Reneau Ph.D. - All Rights Reserved
-- Create Database Tables Script
DROP TABLE IF EXISTS ledgertransactiondetail;
DROP TABLE IF EXISTS ledgertransaction;
DROP TABLE IF EXISTS accountbalance;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS accounttype;
CREATE TABLE accounttype (accounttype_id VARCHAR(10) PRIMARY KEY,
description VARCHAR(40));
CREATE TABLE account (account_id INTEGER PRIMARY KEY,
description VARCHAR(40),
accounttype_id VARCHAR(10),
FOREIGN KEY (accounttype_id) REFERENCES accounttype(accounttype_id)
);
CREATE TABLE company (company_id INTEGER PRIMARY KEY,
description VARCHAR(40),
taxid VARCHAR(20)
);
CREATE TABLE accountbalance (company_id INTEGER,
account_id INTEGER,
balance DECIMAL(12,2),
PRIMARY KEY (company_id, account_id),
FOREIGN KEY (account_id) REFERENCES account(account_id),
FOREIGN KEY (company_id) REFERENCES company(company_id)
);
CREATE TABLE ledgertransaction (transaction_uuid VARCHAR(36) PRIMARY KEY,
transaction_datetime DATETIME,
company_id INTEGER,
description VARCHAR(40),
FOREIGN KEY (company_id) REFERENCES company(company_id)
);
CREATE TABLE ledgertransactiondetail (transaction_uuid VARCHAR(36),
account_id INTEGER,
amount DECIMAL(12,2),
PRIMARY KEY (transaction_uuid, account_id),
FOREIGN KEY (account_id) REFERENCES account(account_id),
FOREIGN KEY (transaction_uuid) REFERENCES ledgertransaction(transaction_uuid)
);
Paste this to insert the data and build the balances.
-- GeneralLedger Sample Database
-- Introduction to Database Systems Modeling and Administration
-- James M. Reneau Ph.D.
-- Version 2025-02-08
-- C) J.M.Reneau Ph.D. - All Rights Reserved
-- Insert Sample Data and Update Script
insert into accounttype values ('A','Asset');
insert into accounttype values ('L','Liability');
insert into accounttype values ('C','Capital');
insert into accounttype values ('I','Income');
insert into accounttype values ('E','Expense');
insert into account values (100, 'Cash', 'A');
insert into account values (110, 'Accounts Receivable', 'A');
insert into account values (190, 'Inventory', 'A');
insert into account values (200, 'Accounts Payable', 'L');
insert into account values (300, 'Shareholder Equity', 'C');
insert into account values (390, 'Retained Earnings', 'C');
insert into account values (400, 'Income', 'I');
insert into account values (500, 'Supplies', 'E');
insert into account values (510, 'Utilities', 'E');
insert into account values (590, 'Cost of Goods Sold', 'E');
INSERT INTO company VALUES (1, 'FOO Consolidated', '55-5559999');
INSERT INTO company VALUES (2, 'BAR Exploration', '55-5559988');
INSERT INTO company VALUES (99, 'XYZ Sales', '55-5559977');
INSERT INTO ledgertransaction VALUES ('9e755caf-e250-11ef-8487-047c16fd53a0', '2024-09-22 09:34:56', 1, 'Initial Stock Purchase');
insert into ledgertransactiondetail VALUES('9e755caf-e250-11ef-8487-047c16fd53a0',100,1000);
insert into ledgertransactiondetail VALUES('9e755caf-e250-11ef-8487-047c16fd53a0',300,-1000);
INSERT INTO ledgertransaction VALUES ('afde0b67-e250-11ef-8487-047c16fd53a0', '2024-09-22 11:33:30', 1, 'Purchase Inventory');
insert into ledgertransactiondetail VALUES('afde0b67-e250-11ef-8487-047c16fd53a0',100,-500);
insert into ledgertransactiondetail VALUES('afde0b67-e250-11ef-8487-047c16fd53a0',190,500);
INSERT INTO ledgertransaction VALUES ('de7e08ef-e250-11ef-8487-047c16fd53a0', '2024-10-01 09:00', 1, 'Sale of 5 Thingies');
insert into ledgertransactiondetail VALUES('de7e08ef-e250-11ef-8487-047c16fd53a0',100,100);
insert into ledgertransactiondetail VALUES('de7e08ef-e250-11ef-8487-047c16fd53a0',400,-100);
insert into ledgertransactiondetail VALUES('de7e08ef-e250-11ef-8487-047c16fd53a0',590,55);
insert into ledgertransactiondetail VALUES('de7e08ef-e250-11ef-8487-047c16fd53a0',190,-55);
INSERT INTO ledgertransaction VALUES ('eac90c6c-e250-11ef-8487-047c16fd53a0', '2024-10-01 09:30', 1, 'Sale of 1 Bobblehead');
insert into ledgertransactiondetail VALUES('eac90c6c-e250-11ef-8487-047c16fd53a0',100,12);
insert into ledgertransactiondetail VALUES('eac90c6c-e250-11ef-8487-047c16fd53a0',400,-12);
insert into ledgertransactiondetail VALUES('eac90c6c-e250-11ef-8487-047c16fd53a0',590,4);
insert into ledgertransactiondetail VALUES('eac90c6c-e250-11ef-8487-047c16fd53a0',190,-4);
INSERT INTO ledgertransaction VALUES ('f6f095ec-e250-11ef-8487-047c16fd53a0', '2024-10-01 10:00', 1, 'Electric Company Invoice');
insert into ledgertransactiondetail VALUES('f6f095ec-e250-11ef-8487-047c16fd53a0',100,-123);
insert into ledgertransactiondetail VALUES('f6f095ec-e250-11ef-8487-047c16fd53a0',510,123);
INSERT INTO ledgertransaction VALUES ('0216a40f-e251-11ef-8487-047c16fd53a0', '2024-10-01 11:00', 1, 'Sale of 1 Thingies on account');
insert into ledgertransactiondetail VALUES('0216a40f-e251-11ef-8487-047c16fd53a0',110,23);
insert into ledgertransactiondetail VALUES('0216a40f-e251-11ef-8487-047c16fd53a0',400,-23);
insert into ledgertransactiondetail VALUES('0216a40f-e251-11ef-8487-047c16fd53a0',590,11);
insert into ledgertransactiondetail VALUES('0216a40f-e251-11ef-8487-047c16fd53a0',190,-11);
INSERT INTO ledgertransaction VALUES ('0ec0f21b-e251-11ef-8487-047c16fd53a0', '2024-10-01 15:00', 1, 'Purchase Inventory on Credit');
insert into ledgertransactiondetail VALUES('0ec0f21b-e251-11ef-8487-047c16fd53a0',190,750);
insert into ledgertransactiondetail VALUES('0ec0f21b-e251-11ef-8487-047c16fd53a0',200,-750);
insert into accountbalance select company_id, account_id, sum(amount) as balance
from ledgertransactiondetail
join ledgertransaction on ledgertransactiondetail.transaction_uuid = ledgertransaction.transaction_uuid
group by company_id, account_id;