1488_5x5

-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- Link to schema: https://app.quickdatabasediagrams.com/#/d/6MgXHU
-- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here.


CREATE TABLE "user" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "surname" VARCHAR(255)   NOT NULL,
    "studyYear" NUMBER(10)   NOT NULL,
    "login" VARCHAR(255)   NOT NULL,
    "password" VARCHAR(255)   NOT NULL,
    "email" VARCHAR(255)   NOT NULL,
    "titleBefore" VARCHAR(255)   NOT NULL,
    "titleAfter" VARCHAR(255)   NOT NULL,
    "blocked" NUMBER(1)   NOT NULL,
    "roleId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "message" (
    "id" NUMBER(10)   NOT NULL,
    "date" DATE   NOT NULL,
    "sender" NUMBER(10)   NOT NULL,
    "receiver" NUMBER(10)   NOT NULL,
    "text" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_message" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "group" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_group" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "user_group" (
    "userId" NUMBER(10)   NOT NULL,
    "groupId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user_group" PRIMARY KEY (
        "userId","groupId"
     )
);

CREATE TABLE "subject" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "shortName" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_subject" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "user_subject" (
    "userId" NUMBER(10)   NOT NULL,
    "subjectId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user_subject" PRIMARY KEY (
        "userId","subjectId"
     )
);

CREATE TABLE "comment" (
    "id" NUMBER(10)   NOT NULL,
    "content" VARCHAR(255)   NOT NULL,
    "date" DATE   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_comment" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "user_comment" (
    "userId" NUMBER(10)   NOT NULL,
    "commentId" NUMBER(10)   NOT NULL
);

CREATE TABLE "post_like" (
    "userId" NUMBER(10)   NOT NULL,
    "postId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_post_like" PRIMARY KEY (
        "userId","postId"
     )
);

CREATE TABLE "post" (
    "id" NUMBER(10)   NOT NULL,
    "text" VARCHAR(255)   NOT NULL,
    "title" VARCHAR(255)   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_post" PRIMARY KEY (
        "id","userId"
     )
);

CREATE TABLE "file" (
    "id" NUMBER(10)   NOT NULL,
    "creationDate" DATE   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "data" blob   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    "fileExtId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_file" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "role" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_role" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "address" (
    "id" NUMBER(10)   NOT NULL,
    "city" VARCHAR(255)   NULL,
    "country" VARCHAR(255)   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_address" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "file_ext" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_file_ext" PRIMARY KEY (
        "id"
     )
);

ALTER TABLE "user" ADD CONSTRAINT "fk_user_roleId" FOREIGN KEY("roleId")
REFERENCES "role" ("id");

ALTER TABLE "message" ADD CONSTRAINT "fk_message_sender" FOREIGN KEY("sender")
REFERENCES "user" ("id");

ALTER TABLE "message" ADD CONSTRAINT "fk_message_receiver" FOREIGN KEY("receiver")
REFERENCES "user" ("id");

ALTER TABLE "user_group" ADD CONSTRAINT "fk_user_group_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_group" ADD CONSTRAINT "fk_user_group_groupId" FOREIGN KEY("groupId")
REFERENCES "group" ("id");

ALTER TABLE "user_subject" ADD CONSTRAINT "fk_user_subject_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_subject" ADD CONSTRAINT "fk_user_subject_subjectId" FOREIGN KEY("subjectId")
REFERENCES "subject" ("id");

ALTER TABLE "comment" ADD CONSTRAINT "fk_comment_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_comment" ADD CONSTRAINT "fk_user_comment_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_comment" ADD CONSTRAINT "fk_user_comment_commentId" FOREIGN KEY("commentId")
REFERENCES "comment" ("id");

ALTER TABLE "post_like" ADD CONSTRAINT "fk_post_like_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "post_like" ADD CONSTRAINT "fk_post_like_postId" FOREIGN KEY("postId")
REFERENCES "post" ("id");

ALTER TABLE "post" ADD CONSTRAINT "fk_post_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "file" ADD CONSTRAINT "fk_file_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "file" ADD CONSTRAINT "fk_file_fileExtId" FOREIGN KEY("fileExtId")
REFERENCES "file_ext" ("id");

ALTER TABLE "address" ADD CONSTRAINT "fk_address_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");