仕様書：RDB仕様（ER図）

ER図を以下に示します。

ER図描画には時間がかかります

ER図は、ブラウザ上でレンダリングするため、描画に時間がかかります。
数秒たってもレンダリングされないときは、ページをリロードしてください。

ER図

レンダリングされるまで、数秒かかります。（レンダリング前にコードっぽいものが表示される場合があります）

erDiagram
    LECTURES_MASTER {
        integer lecture_id PK
        boolean lecture_visible
        string lecture_name
        string lecture_owner
        string lecture_description
        timestamp lecture_date
        boolean lecture_permit_practice
        boolean lecture_permit_exam
        boolean lecture_shuffle
        boolean lecture_ichimon
        json lecture_raw_data
    }

    ASSIGNMENT_MASTER {
        integer assignment_id PK
        string assignment_name
        string assignment_author
        timestamp assignment_created_ts
        timestamp assignment_deadline
        double pass_criteria
        integer lecture_id FK
        integer waittime
        double pass_cbm_score
    }

    ASSIGNMENT_STUDENTS {
        integer assignment_id PK, FK
        string user_id PK, FK
        integer assignment_attempt PK
        timestamp attempt_start_ts
        timestamp attempt_end_ts
        integer lecture_attempt
        timestamp waittime_limit
        boolean passed
    }

    CERTAINTY_SCORE {
        integer certainty PK
        boolean passed PK
        integer score
    }

    PROBLEMS {
        integer category PK
        integer year PK
        integer month PK
        integer problem_number PK
        integer answer
        string problem_ocr
        jsonb similar_samecat
        jsonb gemini_answer
        jsonb similar_allcat
    }

    REACTIONS {
        integer reaction_id PK
        string user_id FK
        timestamp timestamp
        integer category FK
        integer year FK
        integer month FK
        integer problem_number FK
        integer choose
        integer lecture_id FK
        integer attempt FK
        string teacher_name
        integer certainty
    }

    USER_CERTAINTY_SCORES {
        integer certainty_id PK
        string user_id FK
        integer category
        integer correct
        integer failed
        real certainty_average_incbb
        timestamp start_timestamp
        timestamp end_timestamp
        integer c3_count
        integer c2_count
        integer c1_count
        integer c3_correct
        integer c2_correct
        integer c1_correct
        integer lecture_id FK
        integer attempt FK
        real certainty_average_nocbb
    }

    USER_LECTURES {
        string user_id PK, FK
        integer lecture_id PK, FK
        integer attempt PK
        timestamp start_timestamp
        timestamp end_timestamp
        integer total
        integer correct
        string verify_code
    }

    USERS {
        string user_id PK
        string hashed_password
        boolean is_teacher
        string department
        integer entrance_year
        integer class_number
        string last_name
        json ai_growth
        jsonb tags
        string tenancy
    }

    TENANCIES {
        string tenancy PK
        string join_passhash
        string user_regex
        jsonb departments
        string tenancy_name
    }

    ASSIGNMENT_MASTER ||--o{ LECTURES_MASTER : "lecture_id"
    ASSIGNMENT_STUDENTS ||--o| ASSIGNMENT_MASTER : "assignment_id"
    ASSIGNMENT_STUDENTS ||--o| USERS : "user_id"
    REACTIONS ||--o| PROBLEMS : "category, year, month, problem_number"
    USER_LECTURES ||--o| LECTURES_MASTER : "lecture_id"
    USER_LECTURES ||--o| USERS : "user_id"
    REACTIONS ||--o| USER_LECTURES : "user_id, lecture_id, attempt"
    REACTIONS ||--o| USERS : "user_id"
    USER_CERTAINTY_SCORES ||--o| USER_LECTURES : "user_id, lecture_id, attempt"
    USER_CERTAINTY_SCORES ||--o| USERS : "user_id"

詳細

category, year, month, problem_number カラム

それぞれの問題を追跡するために、category, year, month, problem_numberの4カラムを複合することで、一意の問題を識別します。（試験区分、年、月、問題番号がわかればよい）

PROBLEMSテーブルに定義する、以下4カラムからなる複合主キーは、問題の一意性を保証するために使用します。
REACTIONSテーブルにおいて、category, year, month, problem_numberの4カラムを外部キーとして参照します。

なお、制約のレベルとしてNO ACTIONを指定しているため、強力な参照整合性は保証されません。

category, year, month, problem_numberの意味

• category：問題のカテゴリ（例：基本情報技術者試験の「情報基礎」）
• year：問題が出題された年
• month：問題が出題された月
• problem_number：問題の番号

LECTURES_MASTER.lecture_raw_dataの登録内容は、RDB側で保証しません！

本システムでは、LECTURES_MASTER.lecture_raw_dataに登録される登録問題について、その正確性を保証しません。 これは、このカラムはJSON型で格納されていて、フロントエンド側での処理に最適化されているためです。

SQL1文で、この登録問題を処理することは想定していません。

user_id カラム

USERSテーブルにおいて、user_idカラムは、ユーザーを一意に識別するための主キーです。
REACTIONSテーブルにおいて、user_idカラムを外部キーとして参照します。

lecture_id カラム

LECTURES_MASTERテーブルにおいて、lecture_idカラムは、問題集を一意に識別するための主キーです。 それぞれの問題集が追跡できます。

user_id、lecture_id 、 attempt カラム

user_id、lecture_id、attempt`の3カラムを複合することで、一意のユーザの問題集への取り組みをそれぞれ追跡できます。（学籍番号と、試験番号・挑戦回数がわかればよい）

USER_LECTURESテーブルにおいて、user_id、lecture_id、attemptの3カラムを複合主キーとして使用します。
REACTIONSテーブルにおいて、lecture_id、attemptの2カラムを外部キーとして参照し、ユーザの回答を追跡します。

こちらも、制約のレベルとしてNO ACTIONを指定しているため、強力な参照整合性は保証されません。