CREATE TABLE groups(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    gr_num TEXT NOT NULL UNIQUE,
    departement_name TEXT,
    begin_year INTEGER CHECK (begin_year >= 1990)
);

CREATE TABLE students(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    group_id INTEGER REFERENCES groups(id),
    UNIQUE(first_name, last_name)
);

CREATE TABLE subjects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name text
);

CREATE TABLE learning (
    group_id INTEGER REFERENCES groups(id),
    subject_id INTEGER REFERENCES subjects(id),
    PRIMARY KEY(group_id, subject_id)
);

CREATE TABLE schedule (
    group_id INTEGER,
    subject_id INTEGER,
    day_num INTEGER,
    lesson_num INTEGER,
    PRIMARY KEY(group_id, day_num, lesson_num),
    FOREIGN KEY(group_id, subject_id) REFERENCES learning(group_id, subject_id)
);

INSERT INTO groups VALUES (1, 'A-10', 'MMD', 1999);
INSERT INTO groups VALUES (2, 'A-11', 'MMD', 2007);

INSERT INTO students VALUES(1, 'Ivan', 'Ivanov', 5);
INSERT INTO students VALUES(2, 'Ivan', 'Petrov', NULL);  --awful
INSERT INTO students VALUES(3, 'Petr', 'Petrov', 2);

SELECT * FROM groups;
SELECT '--------------';
SELECT * FROM students;