DROP TABLE account_activity CASCADE CONSTRAINTS;

DROP TABLE account_type CASCADE CONSTRAINTS;

DROP TABLE customer CASCADE CONSTRAINTS;

CREATE TABLE customer(
	customer_num		numeric  -- no fractions
	, social_sec_num		numeric(9) NOT NULL -- upto 9 digits, no fractions
	, name			char varying(50) NOT NULL
	, city			char varying(50) NOT NULL
	, state			char(2) NOT NULL
	, PRIMARY KEY(customer_num)
	, UNIQUE(social_sec_num)
);

CREATE TABLE account_type(
	customer_num		numeric REFERENCES customer
	, account_type		char  -- same as char(1)
	, balance			numeric(*,2) NOT NULL -- 2 decimals pts allowed
	, PRIMARY KEY(customer_num, account_type)
	, CHECK(balance>=0)
	, CHECK(account_type in ('C', 'S'))
);

CREATE TABLE account_activity(
	customer_num		numeric NOT NULL
	, account_type		char NOT NULL
	, operation_type		char NOT NULL
	, operation_time		date NOT NULL -- date as well as time is stored
	, amount			numeric(*,2) NOT NULL
	, dest_account		char
	, CHECK(amount>0)
	, CHECK(operation_type in('W', 'D', 'M'))
	, FOREIGN KEY(customer_num, account_type) REFERENCES account_type
);

INSERT INTO customer VALUES(1, 222333444, 'Joe', 'New Orleans', 'LA');

INSERT INTO account_type VALUES(1, 'C', 1);

INSERT INTO account_type VALUES(1, 'S', 1);

INSERT INTO customer VALUES(2, 122333444, 'Joe2', 'Ann Arbor', 'MI');

INSERT INTO account_type VALUES(2, 'C', 1);  

INSERT INTO customer VALUES(3, 322333444, 'Joe3', 'New Orleans', 'LA');

INSERT INTO account_type VALUES(3, 'C', 1);  

INSERT INTO customer VALUES(4, 422333444, 'Joe4', 'San Francisco', 'CA');

INSERT INTO account_type VALUES(4, 'S', 1);  

INSERT INTO customer VALUES(5, 522333444, 'Joe5', 'Nashua', 'NH');

INSERT INTO account_type VALUES(5, 'S', 1);  

INSERT INTO customer VALUES(6, 622333444, 'Joe6', 'New Orleans', 'LA');

INSERT INTO account_type VALUES(6, 'S', 1);  

COMMIT;

INSERT INTO account_activity VALUES(1, 'C', 'D', sysdate, 50.02, null);

UPDATE account_type SET balance = balance + 50.02 
WHERE customer_num = 1 AND account_type = 'C';


INSERT INTO account_activity VALUES(1, 'C', 'W', sysdate, 30, null);

UPDATE account_type SET balance = balance - 30 
WHERE customer_num = 1 AND account_type = 'C';


INSERT INTO account_activity VALUES(1, 'C', 'M', sysdate, 10, 'S');

UPDATE account_type SET balance = balance - 10
WHERE customer_num = 1 AND account_type = 'C';

UPDATE account_type SET balance = balance + 10
WHERE customer_num = 1 AND account_type = 'S';


INSERT INTO account_activity VALUES(1, 'S', 'D', sysdate, 100.20, null);

UPDATE account_type SET balance = balance + 100.20
WHERE customer_num = 1 AND account_type = 'S';


INSERT INTO account_activity VALUES(2, 'C', 'D', sysdate, 150.02, null);

UPDATE account_type SET balance = balance + 150.02 
WHERE customer_num = 2 AND account_type = 'C';


INSERT INTO account_activity VALUES(3, 'C', 'D', sysdate, 50.33, null);

UPDATE account_type SET balance = balance + 50.33 
WHERE customer_num = 3 AND account_type = 'C';

INSERT INTO account_activity VALUES(4, 'S', 'D', sysdate, 9092.78, null);

UPDATE account_type SET balance = balance + 9092.78
WHERE customer_num = 4 AND account_type = 'S';

INSERT INTO account_activity VALUES(5, 'S', 'D', sysdate, 150.02, null);

UPDATE account_type SET balance = balance + 150.02 
WHERE customer_num = 5 AND account_type = 'S';

COMMIT;