unisystem1

create table institutions (
    id int,
    original_name text,
    english_name text
);

create table country (
    id int,
    country_name text
);

create table county (
    id int,
    county_name text
);

create table city (
    id int,
    city_name text
);

create table quarter (
    id int,
    quarter text
);

insert into institutions (id, original_name, english_name) values
    (1, 'Albert-Ludwigs-Universität Freiburg', 'University of Freiburg'),
    (2, 'Eötvös Loránd Tudományegyetem', 'Eötvös Loránd University'),
    (3, 'Budapesti Műszaki és Gazdaságtudományi Egyetem', 'Budapest University of Technology and Economics')
    ;

insert into country (id, country_name) values
    (1, 'Hungary'),
    (2, 'Germany'),
    (3, 'Finnland'),
    (4, 'England'),
    (5, 'USA')
    ;

insert into county (id, county_name) values
    (1, 'Pest'),
    (2, 'Baden-Württemberg')
    ;

insert into city (id, city_name) values
    (1, 'Budapest'),
    (2, 'Freiburg')
    ;

insert into quarter (id, quarter) values
    (1, '-')
    ;

create table inst_country (
    id int,
    inst_id int,
    country_id int
);

create table inst_county (
    id int,
    inst_id int,
    county_id int
);

create table inst_city (
    id int,
    inst_id int,
    city_id int
);

create table inst_quarter (
    id int,
    inst_id int,
    quarter_id int
);

insert into inst_country (id, inst_id, country_id) values
    (1, 1, 2),
    (2, 2, 1),
    (3, 3, 1)
    ;

insert into inst_county (id, inst_id, county_id) values
    (1, 1, 2),
    (2, 2, 1),
    (3, 3, 1)
    ;

insert into inst_city (id, inst_id, city_id) values
    (1, 1, 2),
    (2, 2, 1),
    (3, 3, 1)
    ;

insert into inst_quarter (id, inst_id, quarter_id) values
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1)
    ;