erdver5.sql

-- MySQL Script generated by MySQL Workbench
-- Mon Nov 24 16:21:44 2025
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering
DROP DATABASE IF EXISTS board_test;
CREATE DATABASE board_test;
USE board_test;

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema board_test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema board_test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `board_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE board_test;


-- -----------------------------------------------------
-- Table `board_test`.`member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`member` (
  `member_id` BIGINT  AUTO_INCREMENT,
  `ID` VARCHAR(30) NOT NULL,
  `password` CHAR(64) NOT NULL,
  `nickname` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `signup_date` TIMESTAMP NOT NULL,
  `status` VARCHAR(10) NOT NULL,
  `deleted_date` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- SELECT DATABASE();     -- 지금 선택된 DB가 뭔지
SHOW TABLES;           -- 그 DB에 어떤 테이블이 있는지
SHOW CREATE TABLE member;  -- 진짜 member 테이블 정의
-- SELECT * FROM member;  -- 진짜 데이터 조회




INSERT INTO member (
    ID, password, nickname, email, signup_date, status, deleted_date
) VALUES ('user1', '1111111111111111', '닉네임1', 'user1@example.com', NOW(), 'ACTIVE', NULL),
('user2', '2222', '닉네임2', 'user2@example.com', NOW(), 'ACTIVE', NULL),
('user3', '33333333333', '닉네임3', 'user3@example.com', NOW(), 'INACTIVE', NULL),
('user4', '444444444444444', '닉네임4', 'user4@example.com', NOW(), 'ACTIVE', NULL),
('user5', '55555555555555', '닉네임5', 'user5@example.com', NOW(), 'INACTIVE', NULL);

-- SELECT * FROM member;  -- 진짜 데이터 조회


-- -----------------------------------------------------
-- Table `board_test`.`user_problem_set`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`user_problem_set` (
  `user_problem_set_id` BIGINT NOT NULL AUTO_INCREMENT,
  `member_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_problem_set_id`),
  INDEX `FK_member_TO_user_problem_set_1` (`member_id` ASC) VISIBLE,
  CONSTRAINT `FK_member_TO_user_problem_set_1`
    FOREIGN KEY (`member_id`)
    REFERENCES `board_test`.`member` (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `board_test`.`comment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`comment` (
  `comment_id` BIGINT NOT NULL AUTO_INCREMENT,
  `content` VARCHAR(500) NOT NULL,
  `comment_date` TIMESTAMP NOT NULL,
  `member_id` BIGINT NOT NULL,
  `user_problem_set_id` BIGINT NOT NULL,
  PRIMARY KEY (`comment_id`),
  INDEX `FK_member_TO_comment_1` (`member_id` ASC) VISIBLE,
  INDEX `FK_user_problem_set_TO_comment_1` (`user_problem_set_id` ASC) VISIBLE,
  CONSTRAINT `FK_member_TO_comment_1`
    FOREIGN KEY (`member_id`)
    REFERENCES `board_test`.`member` (`member_id`),
  CONSTRAINT `FK_user_problem_set_TO_comment_1`
    FOREIGN KEY (`user_problem_set_id`)
    REFERENCES `board_test`.`user_problem_set` (`user_problem_set_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `board_test`.`problem`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`problem` (
  `problem_id` BIGINT NOT NULL AUTO_INCREMENT,
  `problem_description` VARCHAR(300) NOT NULL,
  `choice_1` VARCHAR(255) NOT NULL,
  `choice_2` VARCHAR(255) NOT NULL,
  `choice_3` VARCHAR(255) NOT NULL,
  `choice_4` VARCHAR(255) NOT NULL,
  `answer` VARCHAR(10) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`problem_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

INSERT INTO problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category)
VALUES 
('문제1?', 
 'SELECT', 'WHERE', 'ORDER BY', 'GROUP BY', 'WHERE', 'SQLD');

INSERT INTO problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category)
VALUES 
('문제2', 
 'class', 'extends', 'new', 'this', 'new', 'SQLD');

INSERT INTO problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category)
VALUES 
('문제3', 
 'GET', 'POST', 'PUT', 'DELETE', 'POST', 'INFOENGINEERING');

  SELECT* FROM problem;

-- -----------------------------------------------------
-- Table `board_test`.`incorrect_note`
-- -----------------------------------------------------
-- DROP TABLE IF EXISTS incorrect_note;

CREATE TABLE IF NOT EXISTS `board_test`.`incorrect_note` (
  `incorrect_note_id` BIGINT NOT NULL AUTO_INCREMENT,
  `member_id` BIGINT NOT NULL,
  `problem_id` BIGINT,
  `user_problem_id` BIGINT,
  `is_user_problem` TINYINT NOT NULL,

  PRIMARY KEY (`incorrect_note_id`),
  INDEX `FK_member_TO_incorrect_note_1` (`member_id` ASC) VISIBLE,
  INDEX `FK_problem_TO_incorrect_note_1` (`problem_id` ASC) VISIBLE,
  INDEX `FK_user_problem_TO_incorrect_note_1` (`user_problem_id` ASC) VISIBLE,

  CONSTRAINT `FK_member_TO_incorrect_note_1`
    FOREIGN KEY (`member_id`)
    REFERENCES `board_test`.`member` (`member_id`),

  CONSTRAINT `FK_problem_TO_incorrect_note_1`
    FOREIGN KEY (`problem_id`)
    REFERENCES `board_test`.`problem` (`problem_id`),

  CONSTRAINT `FK_user_problem_TO_incorrect_note_1`
    FOREIGN KEY (`user_problem_id`)
    REFERENCES `board_test`.`user_problem` (`user_problem_id`),
    
    CHECK (
        (is_user_problem= 0 AND problem_id IS NOT NULL AND user_problem_id IS NULL) OR
        (is_user_problem= 1 AND user_problem_id IS NOT NULL AND problem_id IS NULL)
    )
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

-- INSERT 예시

 INSERT INTO incorrect_note (member_id, problem_id, user_problem_id, is_user_problem)
	VALUES (1, 1, null, 0);
INSERT INTO incorrect_note (member_id, problem_id, user_problem_id, is_user_problem)
	VALUES (1, null, 2, 1);

-- 조회
 SELECT * FROM incorrect_note;



-- -----------------------------------------------------
-- Table `board_test`.`quiz_room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`quiz_room` (
  `room_id` BIGINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`room_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

-- INSERT INTO
-- quiz_room()
-- VALUES();
--         
-- SELECT * FROM quiz_room;-- 


-- -----------------------------------------------------
-- Table `board_test`.`quiz_room_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`quiz_room_member` (
  `quiz_room_member_id` BIGINT NOT NULL AUTO_INCREMENT,
  `member_id` BIGINT NOT NULL,
  `room_id` BIGINT NOT NULL,
  `is_host` TINYINT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`quiz_room_member_id`),
  INDEX `FK_member_TO_quiz_room_member_1` (`member_id` ASC) VISIBLE,
  INDEX `FK_quiz_room_TO_quiz_room_member_1` (`room_id` ASC) VISIBLE,
  UNIQUE INDEX `member_id_UNIQUE` (`member_id` ASC) VISIBLE,
  CONSTRAINT `FK_member_TO_quiz_room_member_1`
    FOREIGN KEY (`member_id`)
    REFERENCES `board_test`.`member` (`member_id`),
  CONSTRAINT `FK_quiz_room_TO_quiz_room_member_1`
    FOREIGN KEY (`room_id`)
    REFERENCES `board_test`.`quiz_room` (`room_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `board_test`.`user_problem`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`user_problem` (
  `user_problem_id` BIGINT NOT NULL AUTO_INCREMENT,
  `problem_description` VARCHAR(300) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `choice_1` VARCHAR(255) NOT NULL,
  `choice_2` VARCHAR(255) NOT NULL,
  `choice_3` VARCHAR(255) NOT NULL,
  `choice_4` VARCHAR(255) NOT NULL,
  `answer` VARCHAR(10) NOT NULL,
  `comment_count` INT NOT NULL DEFAULT 0,
  `user_problem_set_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_problem_id`),
  INDEX `FK_user_problem_set_TO_user_problem_1` (`user_problem_set_id` ASC) VISIBLE,
  CONSTRAINT `FK_user_problem_set_TO_user_problem_1`
    FOREIGN KEY (`user_problem_set_id`)
    REFERENCES `board_test`.`user_problem_set` (`user_problem_set_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

INSERT INTO user_problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category, user_problem_set_id)
VALUES 
('유저 문제1', 
 'SELECT', 'WHERE', 'ORDER BY', 'GROUP BY', 'WHERE', 'INFOENGINEERING', 1);

INSERT INTO user_problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category,  user_problem_set_id)
VALUES 
('유저 문제2', 
 'class', 'extends', 'new', 'this', 'new', 'INFOENGINEERING', 1);

INSERT INTO user_problem (problem_description, choice_1, choice_2, choice_3, choice_4, answer, category,  user_problem_set_id)
VALUES 
('유저 문제3', 
 'GET', 'POST', 'PUT', 'DELETE', 'POST', 'SQLD', 1);

--   SELECT* FROM problem;
  --   SELECT * FROM user_problem;


-- -----------------------------------------------------
-- Table `board_test`.`user_score`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `board_test`.`user_score` (
  `member_id` BIGINT NOT NULL AUTO_INCREMENT,
  `score` INT NOT NULL,
  PRIMARY KEY (`member_id`),
  CONSTRAINT `FK_member_TO_user_score_1`
    FOREIGN KEY (`member_id`)
    REFERENCES `board_test`.`member` (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;