create table account(
    ID_account serial primary key,
    username varchar(50) unique not null,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    dateOfBirth date not null
    );

create table role(
    ID_role serial primary key,
    roleName varchar(50) unique not null
    );

create table accountRole(
  ID_account integer NOT NULL,
  ID_role integer NOT NULL,
  PRIMARY KEY (ID_account, ID_role),
  FOREIGN KEY (ID_role) REFERENCES role (ID_role),
  FOREIGN KEY (ID_account) REFERENCES account (ID_account)
    );

create table category(
    ID_category serial primary key,
    CategoryName varchar(100) not null
    );

create table collection(
    ID_collection serial primary key,
    CollectionName varchar(100) not null,
    foreign key (ID_account) references account (ID_account),
    foreign key (ID_category) references category (ID_category)
    );

create table deck(
    ID_deck serial primary key,
    DeckName varchar(100) not null,
    foreign key (ID_collection) references collection (ID_collection)
    );

create table results(
    ID_result serial primary key,
    result_value numeric not null,
    comment varchar(300),
    dateOfResult date not null,
    foreign key (ID_deck) references deck (ID_deck)
    );