Database - THM-ATLAS/spring-backend GitHub Wiki

Overview

adminer 4.8.1 https://www.adminer.org/

Version PostgreSQL: 14.3 (Debian 14.3-1.pgdg110+1) with PHP extension PDO_PgSQL

DBMS ER Model

Entity Relationship Diagram Model:

DBMS ER

Tables

assets

Structure

column type comment
asset_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
asset bytea
public boolean
filename character varying

Indexes

  • PRIMARY asset_id

code_languages

Structure

column type comment
lang_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
name character varying

Indexes

  • PRIMARY lang_Id

exercise

Structure

column type comment
exercise_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
content character varying
public boolean
title character varying
module_id integer
description character varying
type_id integer NULL

Indexes

  • PRIMARY exercise_id

Foreign key

origin target ON DELETE ON UPDATE
module_id module(module_id) CASCADE CASCADE
type_id exercise_type(type_id) CASCADE CASCADE

exercise_tag

Structure

column type comment
exercise_id integer
tag_id integer

Indexes

  • PRIMARY exercise_id, tag_id
origin target ON DELETE ON UPDATE
exercise_id exercise(exercise_id) CASCADE CASCADE
tag_id tag(tag_id) CASCADE CASCADE

exercise_type

structure

column type comment
type_id integer Auto-Increment [nextval('exercise_type_type_id_seq')]
name character varying

Indexes

  • PRIMARY type_id

icon

structure

column type comment
icon_id integer Auto-Increment [nextval('icon_icon_id_seq')]
reference character varying

indexes

  • PRIMARY icon_id

mc_answer

structure

column type comment
answer_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
content character varying
correct boolean
question_id integer

indexes

  • PRIMARY answer_id

Foreign key

origin target ON DELETE ON UPDATE
question_id mc_question(question_id) CASCADE CASCADE

mc_question

structure

column type comment
question_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
content character varying
exercise_id integer

indexes

  • PRIMARY question_id

Foreign key

origin target ON DELETE ON UPDATE
exercise_id exercise(exercise_id) CASCADE CASCADE

mc_submission

structure

column type comment
submission_id integer
answer_id integer
marked boolean

indexes

  • PRIMARY submission_id, answer_id

Foreign keys

origin target ON DELETE ON UPDATE
submission_id user_exercise_submission(submission_id) CASCADE CASCADE
answer_id mc_answer(answer_id) CASCADE CASCADE

module

structure

column type comment
module_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
name character varying
description character varying
public boolean NULL
icon_id integer

Indexes

  • PRIMARY module_id

Foreign key

origin target ON DELETE ON UPDATE
icon_id icon(icon_id) CASCADE CASCADE

module_asset

structure

column type comment
module_id integer
asset_id integer
module_asset_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]

indexes

  • PRIMARY module_asset_id

Foreign keys

origin target ON DELETE ON UPDATE
module_id module(module_id) CASCADE CASCADE
asset_id assets(asset_id) CASCADE CASCADE

module_link

structure

column type comment
module_id integer
link character varying
module_link_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]

indexes

  • PRIMARY module_link_id

Foreign key

origin target ON DELETE ON UPDATE
module_id module(module_id) CASCADE CASCADE

module_tag

structure

column type comment
module_id integer
tag_id integer

Foreign keys

origin target ON DELETE ON UPDATE
module_id module(module_id) CASCADE CASCADE
tag_id tag(tag_id) CASCADE CASCADE

notification

structure

column type comment
notification_id integer Auto-Increment [nextval('user_notification_notification_id_seq')]
title character varying
content character varying
time timestamp
type_id integer
module_id integer NULL
exercise_id integer NULL
submission_id integer NULL

Indexes

  • PRIMARY notification_id

Foreign keys

origin target ON DELETE ON UPDATE
type_id notification_type(type_id) CASCADE CASCADE
module_id module(module_id) CASCADE CASCADE
exercise_id exercise(exercise_id) CASCADE CASCADE
submission_id user_exercise_submission(submission_id) CASCADE CASCADE

notification_type

structure

column type comment
type_id integer Auto-Increment [nextval('notification_type_id_seq')]
name character varying

Indexes

  • PRIMARY type_id

role

structure

column type comment
role_id integer Auto-Increment [nextval('role_name_seq')]
name character varying

Indexes

  • PRIMARY role_id

submission_code

structure

column type comment
submission_id integer
content character varying
language integer

indexes

  • PRIMARY submission_id

Foreign key

origin target ON DELETE ON UPDATE
submission_id user_exercise_submission(submission_id) CASCADE CASCADE
language code_language(lang_id) CASCADE CASCADE

submission_file

structure

column type comment
submission_id integer
file integer

indexes

  • PRIMARY submission_id

Foreign key

origin target ON DELETE ON UPDATE
submission_id user_exercise_submission(submission_id) CASCADE CASCADE
file assets(asset_id) CASCADE CASCADE

submission_free

structure

column type comment
submission_id integer
content character varying

indexes

  • PRIMARY submission_id

Foreign key

origin target ON DELETE ON UPDATE
submission_id user_exercise_submission(submission_id) CASCADE CASCADE

submission_type

structure

column type comment
type_id integer
name character varying

indexes

  • PRIMARY type_id

tag

structure

column type comment
role_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
name character varying
icon_id integer

Indexes

  • PRIMARY role_id

Foreign key

origin target ON DELETE ON UPDATE
icon_id icon(icon_id) CASCADE CASCADE

user

structure

column type comment
user_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
name character varying
username character varying
email character varying
password character varying NULL
last_login timestamp NULL

Indexes

  • PRIMARY user_id

user_exercise_rating

structure

column type comment
user_id integer
exercise_id integer
value integer
rating_id integer Auto-Increment [nextval('sequence')]

Indexes

  • PRIMARY rating_id

foreign key

origin target ON DELETE ON UPDATE
user_id user(user_id) CASCADE CASCADE
exercise_id exercise(exercise_id) CASCADE CASCADE

user_exercise_submission

structure

column type comment
submission_id integer Auto-Increment [GENERATED BY DEFAULT AS IDENTITY]
exercise_id integer
user_id integer
grade integer NULL
upload_time timestamp [CURRENT TIMESTAMP]
comment character varying NULL
teacher_id integer NULL
type integer

Indexes

  • PRIMARY submission_id
  • UNIQUE exercise_id, user_id

foreign key

origin target ON DELETE ON UPDATE
exercise_id exercise(exercise_id) CASCADE CASCADE
user_id user(user_id) CASCADE CASCADE
teacher_id user(user_id) CASCADE CASCADE
type_id submission_type(type_id) CASCADE CASCADE

user_module_role

structure

column type comment
user_id integer
module_id integer
tag_id integer [2]

Indexes

  • PRIMARY user_id, module_id

Foreign key

origin target ON DELETE ON UPDATE
user_id user(user_id) CASCADE CASCADE
module_id module(module_id) CASCADE CASCADE
role_id role(role_id) CASCADE CASCADE

user_notification

structure

column type comment
user_id integer
notification_id integer
read boolean

Indexes

  • PRIMARY user_id, notification_id

foreign key

origin target ON DELETE ON UPDATE
user_id user(user_id) CASCADE CASCADE
notification_id notification(notification_id) CASCADE CASCADE

user_role

structure

column type comment
user_id integer
role_id integer

Indexes

  • PRIMARY user_id, role_id

foreign key

origin target ON DELETE ON UPDATE
user_id user(user_id) CASCADE CASCADE
role_id role(role_id) CASCADE CASCADE

user_settings

structure

column type comment
user_id integer
language character varying
theme character varying

Indexes

  • PRIMARY user_id

foreign key

origin target ON DELETE ON UPDATE
user_id user(user_id) CASCADE CASCADE