laba2.accdb

CREATE TABLE Country (
    ID AUTOINCREMENT PRIMARY KEY,
    Name TEXT(100),
    Population LONG
);

CREATE TABLE Nation (
    ID AUTOINCREMENT PRIMARY KEY,
    Name TEXT(100),
    Language TEXT(50)
);

CREATE TABLE CountryNation (
    ID AUTOINCREMENT PRIMARY KEY,
    CountryID LONG,
    NationID LONG,
    CONSTRAINT fk_country FOREIGN KEY (CountryID) REFERENCES Country(ID),
    CONSTRAINT fk_nation FOREIGN KEY (NationID) REFERENCES Nation(ID)
);

-- Страны
INSERT INTO Country (Name, Population) VALUES ('Россия', 146000000);
INSERT INTO Country (Name, Population) VALUES ('Казахстан', 19500000);
INSERT INTO Country (Name, Population) VALUES ('Киргизия', 7000000);

-- Народы
INSERT INTO Nation (Name, Language) VALUES ('Русские', 'Русский');
INSERT INTO Nation (Name, Language) VALUES ('Казахи', 'Казахский');
INSERT INTO Nation (Name, Language) VALUES ('Киргизы', 'Киргизский');
INSERT INTO Nation (Name, Language) VALUES ('Татары', 'Татарский');

-- Связи
INSERT INTO CountryNation (CountryID, NationID) VALUES (1, 1); -- Россия - Русские
INSERT INTO CountryNation (CountryID, NationID) VALUES (1, 4); -- Россия - Татары
INSERT INTO CountryNation (CountryID, NationID) VALUES (2, 2); -- Казахстан - Казахи
INSERT INTO CountryNation (CountryID, NationID) VALUES (2, 1); -- Казахстан - Русские
INSERT INTO CountryNation (CountryID, NationID) VALUES (3, 3); -- Киргизия - Киргизы
INSERT INTO CountryNation (CountryID, NationID) VALUES (3, 1); -- Киргизия - Русские