drop table tranzakcio;
drop table automata;
drop table szamla;
drop table reszleg;
drop table ugyfel;

create table ugyfel (
	azon		varchar2(3),
	nev			varchar2(15),
	szuletes	date,
	keret		number(6) default 100000,
	constraint ugyf_kulcs primary key (azon));
	
create table reszleg (
	azon		varchar2(3),
	varos		varchar2(15),
	tartalek	number(9),
	constraint reszl_kulcs primary key (azon));

create table szamla (
	azon		varchar2(3),
	ugyf_azon	varchar2(3),
	reszl_azon	varchar2(3),
	osszeg		number(8),
	lejarat		date,
	constraint szaml_kulcs primary key (azon),
	constraint szaml_id_kulcs_ugyf foreign key (ugyf_azon) 
		references ugyfel(azon),
	constraint szaml_id_kulcs_reszl foreign key (reszl_azon)
		references reszleg(azon));
	
create table automata (
	azon		varchar2(3),
	penz		number(8),
	reszl_azon	varchar2(3),
	varos		varchar2(20),
	constraint aut_kulcs primary key (azon),
	constraint aut_id_kulcs foreign key (reszl_azon) 
		references reszleg(azon));
	
create table tranzakcio (
	azon		varchar2(3),
	aut_azon	varchar2(3),
	szaml_azon	varchar2(3),
	ugyf_azon	varchar2(3),
	datum		date,
	osszeg		number,
	constraint tranz_kulcs primary key (azon),
	constraint tanz_id_kulcs_aut foreign key (aut_azon) 
		references automata(azon),
	constraint tanz_id_kulcs_szaml foreign key (szaml_azon)
		references szamla(azon),
	constraint tanz_id_kulcs_ugyf foreign key (ugyf_azon)
		references ugyfel(azon));
	
insert into ugyfel values
('U00', 'Melak', date '1980-12-12', 100000);
insert into ugyfel values
('U01', 'Kismiska', date '1956-03-12', 100000);
insert into ugyfel values
('U02', 'Delila', date '1934-11-04', 100000);
insert into ugyfel values
('U03', 'Malvinka', date '1978-09-10', 100000);
insert into ugyfel values
('U04', 'Jancsimokus', date '1967-05-09', 100000);
insert into ugyfel values
('U05', 'Platini', date '1964-05-09', 100000);
	
insert into reszleg values
('B00', 'Budapest', 20000000);
insert into reszleg values
('B01', 'Budapest', 15000000);
insert into reszleg values
('B02', 'Kecskemet', 500000);
insert into reszleg values
('B03', 'Gyongyos', 15000000);

insert into automata values
('A00', 8000000, 'B03', 'Gyongyos');
insert into automata values
('A01', 5000000, 'B03', 'Gyongyostarjan');
insert into automata values
('A02', 1000000, 'B00', 'Budapest');
insert into automata values
('A03', 15000000, 'B00', 'Budapest');
insert into automata values
('A04', 5000000, 'B01', 'Budapest');
insert into automata values
('A05', 5000000, 'B01', 'Budapest');
insert into automata values
('A06', 7000000, 'B01', 'Budapest');
insert into automata values
('A07', 13000000, 'B02', 'Kecskemet');

insert into szamla values
('S00', 'U00', 'B03', 500000, date '2010-02-03');
insert into szamla values
('S01', 'U00', 'B03', 120000, date '2007-07-06');
insert into szamla values
('S02', 'U01', 'B00', 40000, date '2008-12-15');
insert into szamla values
('S03', 'U01', 'B01', 90000, date '2010-02-03');
insert into szamla values
('S04', 'U01', 'B01', 78000, date '2010-02-03');
insert into szamla values
('S05', 'U02', 'B02', 340000, date '2009-04-15');
insert into szamla values
('S06', 'U02', 'B02', 8000000, date '2008-07-07');
insert into szamla values
('S07', 'U03', 'B02', 460000, date '2011-06-06');
insert into szamla values
('S08', 'U03', 'B02', 50000, date '2011-06-06');
insert into szamla values
('S09', 'U03', 'B02', 50000, date '2011-06-06');
insert into szamla values
('S10', 'U04', 'B00', 467000, date '2008-01-23');
insert into szamla values
('S11', 'U04', 'B00', 1000000, date '2009-03-23');
insert into szamla values
('S12', 'U05', 'B03', 5000000, date '2112-05-29');


insert into tranzakcio values
('T00', 'A01', 'S00', 'U00', date '2007-04-21', 50000);
insert into tranzakcio values
('T01', 'A01', 'S00', 'U00', date '2007-04-21', 50000);
insert into tranzakcio values
('T02', 'A01', 'S01', 'U00', date '2007-04-21', -50000);
insert into tranzakcio values
('T03', 'A00', 'S02', 'U01', date '2007-04-21', -6000);
insert into tranzakcio values
('T04', 'A02', 'S02', 'U01', date '2007-04-21', -5000);
insert into tranzakcio values
('T05', 'A02', 'S02', 'U01', date '2007-04-21', -7500);
insert into tranzakcio values
('T06', 'A03', 'S05', 'U02', date '2007-04-21', -60000);
insert into tranzakcio values
('T07', 'A03', 'S06', 'U02', date '2007-04-21', -40000);
insert into tranzakcio values
('T08', 'A04', 'S07', 'U03', date '2007-04-21', -96000);
insert into tranzakcio values
('T09', 'A06', 'S10', 'U04', date '2007-04-21', 100000);
insert into tranzakcio values
('T10', 'A06', 'S11', 'U04', date '2007-04-21', 30000);
insert into tranzakcio values
('T11', 'A07', 'S12', 'U05', date '2007-04-21', -1000);
insert into tranzakcio values
('T12', 'A05', 'S00', 'U00', date '2007-04-23', 3000);
insert into tranzakcio values
('T13', 'A01', 'S01', 'U00', date '2007-04-23', -47000);
insert into tranzakcio values
('T14', 'A00', 'S02', 'U01', date '2007-04-23', -6000);
insert into tranzakcio values
('T15', 'A02', 'S02', 'U01', date '2007-04-23', -5000);
insert into tranzakcio values
('T16', 'A02', 'S02', 'U01', date '2007-04-23', -7500);
insert into tranzakcio values
('T17', 'A02', 'S05', 'U02', date '2007-04-23', -6000);
insert into tranzakcio values
('T18', 'A03', 'S06', 'U02', date '2007-04-23', -40000);
insert into tranzakcio values
('T19', 'A04', 'S07', 'U03', date '2007-04-23', -96000);
insert into tranzakcio values
('T20', 'A05', 'S01', 'U00', date '2007-04-23', -3600);
insert into tranzakcio values
('T21', 'A06', 'S10', 'U04', date '2007-04-23', -100000);
insert into tranzakcio values
('T22', 'A06', 'S11', 'U04', date '2007-04-23', -25000);
insert into tranzakcio values
('T23', 'A07', 'S12', 'U05', date '2007-04-23', -10000);
insert into tranzakcio values
('T24', 'A05', 'S01', 'U00', date '2007-04-23', -3600);