0. DataBase 생성

CREATE DATABASE CEM_Community COLLATE Korean_Wansung_CI_AS;

1. 회원가입 / 로그인

CREATE TABLE Member(
	id VARCHAR(50) PRIMARY KEY,
	password VARCHAR(512) NOT NULL,
  salt VARCHAR(255) NOT NULL,
	name VARCHAR(20) NOT NULL,
	std_no INT NOT NULL,
	grade INT NOT NULL,
	nickname VARCHAR(40) NOT NULL,
	role INT NOT NULL
);
SELECT * FROM Member;

1. 게시판 그룹 생성

CREATE TABLE Board_Group(
	g_no INT IDENTITY (1, 1) NOT NULL,
	g_name VARCHAR(50) NOT NULL,
	PRIMARY KEY(g_no)
);
SELECT * FROM Board_Group;

INSERT INTO Board_Group(g_name) VALUES('1학년 게시판');
INSERT INTO Board_Group(g_name) VALUES('2학년 게시판');
INSERT INTO Board_Group(g_name) VALUES('3학년 게시판');
INSERT INTO Board_Group(g_name) VALUES('4학년 게시판');
INSERT INTO Board_Group(g_name) VALUES('랩 소개 게시판');
INSERT INTO Board_Group(g_name) VALUES('졸업 논문 게시판');
INSERT INTO Board_Group(g_name) VALUES('과목 소개 게시판');
SELECT * FROM Board_Group;

1. 게시판 목록 / 게시글 작성 및 수정 & 삭제 / 좋아요

CREATE TABLE Board (
    b_no INT DEFAULT 1 NOT NULL,
    g_no INT NOT NULL,
    id VARCHAR(50) NOT NULL,
    b_title VARCHAR(50) NOT NULL,
    b_content TEXT NOT NULL,
    b_date DATE NOT NULL,
		likes INT DEFAULT 0 NULL,
    PRIMARY KEY (b_no, g_no),
    FOREIGN KEY (g_no) REFERENCES Board_Group (g_no),
    FOREIGN KEY (id) REFERENCES Member (id)
);
SELECT * FROM Board;

INSERT INTO Board values(g_no, session.user.id, b_title, content, date)

1. 댓글

CREATE TABLE Comment(
	c_no INT IDENTITY (1, 1) NOT NULL,
	b_no INT NOT NULL,
  g_no INT NOT NULL,
	id VARCHAR(50) NOT NULL,
	c_content TEXT NOT NULL,
	c_date DATE NOT NULL,
	PRIMARY KEY(c_no),
	FOREIGN KEY (id) REFERENCES Member(id),
	FOREIGN KEY (b_no, g_no) REFERENCES Board(b_no, g_no)
);

1. 학사일정

CREATE TABLE Academic_Calendar(
	ac_no INT IDENTITY (1, 1) NOT NULL,
	id VARCHAR(50) NOT NULL,
	ac_category INT NOT NULL,
	ac_title VARCHAR(50) NOT NULL,
	ac_start_date DATE NOT NULL,
	ac_end_date DATE NOT NULL,
	PRIMARY KEY(ac_no, ac_category),
	FOREIGN KEY (id) REFERENCES Member(id),
);

현재까지 구현 목록

• Create Database and Tables