/* Город */
CREATE TABLE CITY (
    ID    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    NAME  VARCHAR(128));

/* Улица */
CREATE TABLE STREET (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    NAME     VARCHAR(128),
    CITY_ID  INTEGER);

/* Дом */
CREATE TABLE BUILDING (
    ID         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    NUM        VARCHAR(32),
    STREET_ID  INTEGER);

ALTER TABLE STREET ADD CONSTRAINT PK_STREET PRIMARY KEY (ID);
ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (ID);
ALTER TABLE BUILDING ADD CONSTRAINT PK_BUILDING PRIMARY KEY (ID);

ALTER TABLE STREET ADD CONSTRAINT FK_STREET_CITY FOREIGN KEY (CITY_ID) REFERENCES CITY (ID);
ALTER TABLE BUILDING ADD CONSTRAINT FK_BUILDING_STREET FOREIGN KEY (STREET_ID) REFERENCES STREET (ID);

INSERT INTO CITY (ID, NAME) VALUES (1, 'Москва');
INSERT INTO CITY (ID, NAME) VALUES (2, 'Киев');
INSERT INTO CITY (ID, NAME) VALUES (3, 'Саратов');

INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (1, 'Ленинский проспект', 1);
INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (2, 'Улица Лобачевского', 1);
INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (3, 'Улица Теплый стан', 1);

INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (4, 'Старонаводницкая улица', 2);
INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (5, 'Московская улица', 2);

INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (6, 'Амурская улица', 3);
INSERT INTO STREET (ID, NAME, CITY_ID) VALUES (7, 'Корсачья улица', 3);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (1, '1 ', 1);
INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (2, '2', 1);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (3, '2', 2);
INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (4, '3', 2);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (5, '1', 3);
INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (6, '22/1', 3);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (7, '33', 4);
INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (8, '44', 4);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (9, '5', 5);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (10, '33', 6);

INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (11, '22', 7);
INSERT INTO BUILDING (ID, NUM, STREET_ID) VALUES (12, '2/1 А', 7);

COMMIT;