database_create.py

#!/usr/bin/env python3

import sqlite3 as sqlite

conn = sqlite.connect('database.db')
conn.isolation_level = None  # so we don't have to use self.connection.commit()
curs = conn.cursor()

curs.execute(
    '''
    CREATE TABLE Groups (
    group_id INTEGER PRIMARY KEY,
    name     VARCHAR UNIQUE
                     NOT NULL
);
'''
)

curs.execute(
    '''
    CREATE TABLE Member (
    member_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name      VARCHAR,
    surname   VARCHAR,
    group_id  INTEGER REFERENCES Groups (group_id) ON DELETE CASCADE
);
'''
)

curs.execute(
    '''
    CREATE VIEW Member_history_view AS
    SELECT t1.member_id member_id,
           t2.member_id With_member_id,
           name,
           surname,
           group_id,
           count( * ) times_together
      FROM Member_to_Team t1
           JOIN
           Member_to_Team t2 ON t1.team_id = t2.team_id AND 
                                t1.member_id != t2.member_id
           JOIN
           Member ON t2.member_id = Member.member_id
     GROUP BY t1.member_id,
              t2.member_id;
'''
)

curs.execute(
    '''
    CREATE TABLE Distribution (
    distribution_id INTEGER  PRIMARY KEY AUTOINCREMENT,
    name            VARCHAR  NOT NULL,
    creation_time   DATETIME DEFAULT (CURRENT_TIMESTAMP),
    group_id                 REFERENCES Groups (group_id) ON DELETE CASCADE
);
'''
)

curs.execute(
    '''
    CREATE TABLE Team (
    team_id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name            VARCHAR,
    distribution_id INTEGER REFERENCES Distribution (distribution_id) ON DELETE CASCADE
                            NOT NULL
);'''
)

curs.execute(
    '''
    CREATE TABLE Member_to_Team (
    member_id INTEGER REFERENCES Member (member_id) ON DELETE CASCADE,
    team_id   INTEGER REFERENCES Team (team_id) ON DELETE CASCADE,
    PRIMARY KEY (
        team_id,
        member_id
    )
);
'''
)