--As SYSADM
create ident mobile as user identified by 'mobile';
grant databank to mobile;
disconnect;

--As devuser

connect to 'devmobile' USER 'mobile' USING 'mobile';

create databank mobileDatabank of 100 pages in 'mobileDatabank.dbf' with trans option;
create unique sequence address_id_seq;

create table person(
pnr int,
name nchar varying(50),
surname nchar varying(50),
primary key(pnr));

create table person_address(
address_id int default next_value of address_id_seq,
pnr int,
phone nchar(10),
zipcode nchar(10),
address nchar varying(100),
city nchar varying(50),
primary key(address_id),
foreign key(pnr) references person(pnr));


create table audit(
logts timestamp,
action nchar varying(50));

insert into person(pnr, name, surname) values(1, n'Sven', n'Svensson');
insert into person(pnr, name, surname) values(2, n'Johan', n'Johansson');
insert into person(pnr, name, surname) values(3, n'Eva', n'Göransson');

insert into person_address(pnr, phone, zipcode, address, city) 
values(1, n'018-12345', n'751 47', n'Kungsgatan 64', n'Uppsala');
insert into person_address(pnr, phone, zipcode, address, city) 
values(2, n'018-23456', n'753 00', n'Storgatan 1', n'Uppsala'); 
insert into person_address(pnr, phone, zipcode, address, city) 
values(3, n'08-654321', n'121 27', n'Globentorget 2', n'Stockholm');

create statement select_persons select pnr, name, surname from person;
create statement select_address select address_id, phone, zipcode, address, city from person_address where pnr=:pnr_value;
create statement insert_audit insert into audit(logts, action) values(current_timestamp, :action_value);
create statement select_audit select logts, action from audit;
create statement clear_audit delete from audit;
create statement insert_person insert into person(pnr, name, surname) values(:pnr,:name,:surname);
create statement insert_address insert into person_address(pnr, phone,zipcode,address,city) values(:pnr, :phone, :zipcode,:address, :city);