drop table account;
drop table workplace;
drop table person;
drop table company;
drop table zipcode;

go;

create table zipcode
(
zip char(4) primary key,
postaldistrict varchar(25)
);
commit;

create table person
(
cpr char(10) primary key,
name varchar(25),
job varchar(25),
salary int,
zip char(4) not null,
foreign key (zip) references zipcode(zip)
);
commit;
create table company
(
companynr int primary key,
companyname varchar(25),
zip char(4) not null,
FOREIGN KEY(zip) references zipcode(zip)
);
commit;

create table workplace
(
cpr char(10),
constraint cprforeign foreign key(cpr) references person(cpr),
companynr int,
constraint companyforeign foreign key (companynr) references company(companynr),
primary key(cpr,companynr)
);
commit;

create table account
(
accountno int primary key,
belongsto char(10),
balance int
);
commit;



go;

insert into zipcode values('8000','Ĺrhus C');
insert into zipcode values('8200','Ĺrhus N');
insert into zipcode values('8210','Ĺrhus V');
insert into zipcode values('8220','Brabrand');
insert into zipcode values('8240','Risskov');
insert into zipcode values('8310','Tranbjerg J');
insert into zipcode values('8270','Hřjbjerg');
insert into zipcode values('8250','Egĺ');
insert into person values('1212121212','Ib Hansen','systemdeveloper',250000,'8000');
insert into person values('1313131313','Poul Ibsen','project manager',500000,'8310');
insert into person values('1414141414','Anna Poulsen','IT-manager',870000,'8250');
insert into person values('1515151515','Jette Olsen','systemdeveloper',200000,'8000');
insert into person values('1616161616','Roy Hurtigkoder','programmer',500000,'8210');
insert into company values(1,'Danske Data','8220');
insert into company values(2,'Kommunedata','8000');
insert into company values(3,'LEC','8240');
insert into company values(4,'Dansk Supermarked','8270');
insert into workplace values('1212121212',2);
insert into workplace values('1313131313',4);
insert into workplace values('1414141414',4);
insert into workplace values('1616161616',2);
insert into account values(1,'1212121212',1000);
insert into account values(2,'1414141414',100);
insert into account values(3,'1414141414',10000);
insert into account values(4,'1414141414',10);
commit;
select * from workplace;