-- tabulka pobociek
CREATE TABLE mijvbranch
  (branch_id SMALLINT NOT NULL WITH DEFAULT,
   branch_city CHAR(20) NOT NULL WITH DEFAULT,
   branch_mgr CHAR(25) NOT NULL WITH DEFAULT,
   branch_addr VARCHAR(25) NOT NULL WITH DEFAULT,
   PRIMARY KEY(branch_id));
INSERT INTO mijvbranch
 VALUES(12,'NEW YORK','HIRO TANAKA','43 WEST 57 ST.');
INSERT INTO mijvbranch
 VALUES(300,'YONKERS','BRUCE HOFFMAN','7 MORANI STREET');
INSERT INTO mijvbranch
 VALUES(450,'ROCHESTER','ANGELA VALENTINO','351 TEAL PLAZA');
INSERT INTO mijvbranch
 VALUES(670,'NEW YORK','MATTHEW GREEN','900 WALL STREET');
INSERT INTO mijvbranch
 VALUES(890,'YONKERS','ZILYA KOLKER','610 OSLO LANE');
INSERT INTO mijvbranch
 VALUES(13,'CORNING','JOE GROSSMAN','ONE FEDERAL PLAZA');

-- tabulka zakaznikov
CREATE TABLE mijvcustomer
  (cust_soc_sec INTEGER NOT NULL WITH DEFAULT,
   cust_first_name CHAR(10),
   cust_last_name CHAR(15),
   cust_date_of_birth DATE,
   cust_branch SMALLINT NOT NULL WITH DEFAULT,
   cust_rating SMALLINT NOT NULL WITH DEFAULT,
   PRIMARY KEY(cust_soc_sec), 
   FOREIGN KEY rc1br(cust_branch) REFERENCES mijvbranch
       ON DELETE RESTRICT);
INSERT INTO mijvcustomer     VALUES(111111111,'PHYLLIS','ELKIND','29.03.1962',12,4);
INSERT INTO mijvcustomer     VALUES(222222222,'JAY','RANADE','21.09.1960',890,8);
INSERT INTO mijvcustomer     VALUES(333333333,'RICHARD','GREEN','15.08.1947',12,7);
INSERT INTO mijvcustomer     VALUES(444444444,NULL,NULL,'25.12.1965',670,9);
INSERT INTO mijvcustomer   VALUES(555555555,'HEIDI','SCHMIDT','04.06.1942',890,2);
INSERT INTO mijvcustomer     VALUES(666666666,'MUKESH','SEHGAL','24.10.1957',300,5);
INSERT INTO mijvcustomer  VALUES(777777777,'LAI-HA','NG','01.01.1970',450,7);
INSERT INTO mijvcustomer   VALUES(888888888,'GEORGE','SCHMIDT','04.07.1950',12,2);
-- tabulka uctov
CREATE TABLE mijvaccount(
 acct_no CHAR(4) NOT NULL WITH DEFAULT,
 acct_balance DECIMAL(11,2) NOT NULL WITH DEFAULT,
 acct_credit_bal DECIMAL(11,2),
 acct_cust_id INTEGER NOT NULL WITH DEFAULT,
 acct_start_date DATE NOT NULL WITH DEFAULT, PRIMARY KEY(acct_no), FOREIGN KEY rcacct(acct_cust_id) REFERENCES mijvcustomer);
INSERT INTO mijvaccount 
 VALUES('AE11',12000.94, 500,111111111,'17.03.1987');
INSERT INTO mijvaccount    VALUES('AE12',500,500,555555555,'25.12.1979');
INSERT INTO mijvaccount    VALUES('AE21',77.83,2000,888888888,'01.01.1980');
INSERT INTO mijvaccount    VALUES('AB21',0,180.55,444444444,'12.02.1989');
INSERT INTO mijvaccount    VALUES('BB12',12001.35,3300,222222222,'14.02.1989');
INSERT INTO mijvaccount    VALUES('BB13',646.01,25,666666666,'31.10.1986');
INSERT INTO mijvaccount    VALUES('CE21',35100.98,100,333333333,'14.06.1985');
INSERT INTO mijvaccount
  VALUES('DE11',20,25,777777777,'20.05.1979');
INSERT INTO mijvaccount
  VALUES('DB11',75,0,555555555,'11.11.1980');

-- tabulka transakcii
CREATE TABLE mijvtxn
 (TXN_TYPE CHAR(8) NOT NULL WITH DEFAULT,
  TXN_TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT,
  TXN_AMOUNT DECIMAL(11,2),
  TXN_ACCT_NO CHAR(4) NOT NULL WITH DEFAULT,
 PRIMARY KEY(TXN_ACCT_NO, TXN_TYPE,TXN_TIMESTAMP),
 FOREIGN KEY RCTXN (TXN_ACCT_NO) REFERENCES mijvaccount);
 INSERT INTO mijvtxn
   VALUES('CREDIT','01.01.1989-12.12.12',200.50,'BB12');
 INSERT INTO mijvtxn         VALUES('DEBIT','1989-02-03-18.18.18',100,'AE21');