Paste this to create or re-create the database tables.
-- VetOffice 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 charges;
drop table if exists payments;
DROP TABLE IF EXISTS animal;
DROP TABLE IF EXISTS ownerbalance;
DROP TABLE IF EXISTS owner;
DROP TABLE IF EXISTS gender;
DROP TABLE IF EXISTS species;
CREATE TABLE species (species_id VARCHAR(10) PRIMARY KEY,
description VARCHAR(40)
);
CREATE TABLE gender (gender_id VARCHAR(10) PRIMARY KEY,
description VARCHAR(40)
);
CREATE TABLE owner (owner_id INTEGER PRIMARY KEY,
last_name VARCHAR(40),
first_name VARCHAR(40),
phone VARCHAR(20),
email VARCHAR(40)
);
CREATE TABLE ownerbalance (owner_id INTEGER PRIMARY KEY,
balance DECIMAL(12,2),
FOREIGN KEY (owner_id) REFERENCES owner(owner_id)
);
CREATE TABLE animal (animal_id INTEGER PRIMARY KEY,
name VARCHAR(40),
owner_id INTEGER,
weight REAL,
color VARCHAR(20),
gender_id VARCHAR(10),
species_id VARCHAR(10),
birth_datetime DATETIME,
death_datetime DATETIME,
FOREIGN KEY (owner_id) REFERENCES owner(owner_id),
FOREIGN KEY (gender_id) REFERENCES gender(gender_id),
FOREIGN KEY (species_id) REFERENCES species(species_id)
);
CREATE TABLE charges (charge_uuid VARCHAR(48) PRIMARY KEY,
animal_id INTEGER,
charge_datetime DATETIME,
description VARCHAR(50),
amount DECIMAL(12,2),
FOREIGN KEY (animal_id) REFERENCES animal(animal_id)
);
CREATE TABLE payments (payment_uuid VARCHAR(48) PRIMARY KEY,
owner_id INTEGER,
payment_datetime DATETIME,
description VARCHAR(50),
amount DECIMAL(12,2),
FOREIGN KEY (owner_id) REFERENCES owner(owner_id)
);
Paste this to insert the data and build the balances.
-- VetOffice 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 species VALUES ('C','Feline');
INSERT INTO species VALUES ('D','Canine');
INSERT INTO species VALUES ('H','Equine');
INSERT INTO gender VALUES ('M','Male');
INSERT INTO gender VALUES ('NM','Neutered Male');
INSERT INTO gender VALUES ('F','Female');
INSERT INTO gender VALUES ('NF','Neutered Female');
INSERT INTO gender VALUES ('U','Unknown');
INSERT INTO owner VALUES (1, 'Smithson', 'Amy', '1-555-555-3467', '[email protected]');
INSERT INTO owner VALUES (2, 'Ralston', 'Howard', '1-555-555-6712', '[email protected]');
INSERT INTO owner VALUES (3, 'Greene', 'Susan', '1-555-555-5543', '[email protected]');
INSERT INTO owner VALUES (4, 'Luton', 'Lex', '1-555-555-9988', '[email protected]');
INSERT INTO owner VALUES (5, 'Clark', 'John', '1-555-555-8111', '[email protected]');
INSERT INTO animal
VALUES (1, 'Kitty', 1, 17, 'Ginger', 'NM', 'C', '2020-01-23', NULL);
INSERT INTO animal VALUES (2, 'Bobo', 1, 23, 'Brown', 'NM', 'D', '2010-03-21 12:30', '2024-01-10');
INSERT INTO animal VALUES (3, 'Daisy', 3, 7, 'Callico', 'NF', 'C', '2021-07-10', NULL);
INSERT INTO animal VALUES (4, 'Bonnie', 3, 9, 'Black', 'NF', 'C', '2019-09-05', '2024-04-03 15:39');
INSERT INTO animal VALUES (5, 'Cookie', 3, 12, 'Ginger', 'NM', 'C', '2019-01-30 06:15', NULL);
INSERT INTO animal VALUES (6, 'Cookie', 4, 12, 'Light Brown', 'NF', 'D', '2020-11-11', NULL);
INSERT INTO animal VALUES (7, 'Penny', 5, 15, 'Ginger', 'NF', 'C', '2018-07-07', NULL);
INSERT INTO animal VALUES (8, 'Holly', 5, 4, 'Black', 'F', 'C', '2021-06-12', NULL);
INSERT INTO animal VALUES (9, 'Rosie', 5, 5, 'Black', 'M', 'C', '2021-06-12', NULL);
insert into charges VALUES ('f280ed203c7c618e5459a3b11852f4c3', 1, '2025-01-03 12:30', 'new patient visit', 60.00);
insert into charges VALUES ('f9f464576c009ed0c72c1eec7bdfec0f', 1, '2025-01-03 12:30', 'vaccine', 23.45);
insert into charges VALUES ('603351db1381bdbd6d1b037012981004', 1, '2025-01-03 12:30', 'grooming', 40.00);
insert into charges VALUES ('f118812126a396a32732d87dbe1108ee', 3, '2025-01-03 14:45', 'checkup', 50.00);
insert into charges VALUES ('66c6085abcbb98836ea5709dd72ed15e', 3, '2025-01-03 14:45', 'boarding 10 days', 300.00);
insert into charges VALUES ('b85edb7b9cb7f1bcebb481107d70a90e', 5, '2025-01-03 16:00', 'new patient visit', 60.00);
insert into payments VALUES ('f2a2b56b26c9688210cf026456a35618', 3, '2025-01-03 16:00', 'gift card', 50.00);
insert into payments VALUES ('ab9ade325a0b4e9fa57cabc5670ba10c', 3, '2025-01-03 16:00', 'credit card', 14.02);
INSERT INTO ownerbalance
select owner_id, sum(amount) from (
select owner_id, amount from charges join animal on animal.animal_id = charges.animal_id
union
select owner_id, amount*-1 from payments) candp
group by owner_id;