테이블 명세

구조 설계를 바탕으로 실제 DBMS에서 구현할 쿼리를 작성한다.

Table 소개

이를 통해 실제로 DBMS 내에 구현할 쿼리 및 해당 테이블에 대하여 설명한다.

대학 테이블

설명

세종대학교에 소속된 대학 정보를 관리하는 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입	비고
True				대학코드	college_code	TINYINT
				대학명	name	VARCHAR(20)

쿼리

CREATE TABLE IF NOT EXISTS college(
college_code TINYINT NOT NULL,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(college_code))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

학과 테이블

설명

세종대학교 각 대학에 소속된 학과 정보를 관리하는 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입
True				학과코드	major_code	TINYINT
				학과명	name	VARCHAR(20)
		True		소속대학	college_code	TINYINT
				학과 색코드	color	VARCHAR(10)	RGB code

• 복수의 학과는 하나의 대학에 소속된다.

• 각 학과는 대자보에서 표시하기 위한 각기 다른 고유의 색을 가지고 있다.

쿼리

CREATE TABLE IF NOT EXISTS major(
major_code TINYINT NOT NULL,
name VARCHAR(20) NOT NULL,
college_code TINYINT NOT NULL,
color VARCHAR(10) NOT NULL,
PRIMARY KEY(major_code),
FOREIGN KEY(college_code) REFERENCES college(college_code) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

회원 테이블

설명

세자보의 사용자를 관리하는 테이블이다. 모든 회원은 하나의 학과에 소속되어 있다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입
True				학번	student_id	INT
				비밀번호	pw	VARCHAR(100)
		True		소속학과	major_code	TINYINT
				이름	name	VARCHAR(10)

• 비밀번호는 SHA256 해쉬를 거친 데이터 값을 저장한다.

쿼리

CREATE TABLE IF NOT EXISTS user(
student_id INT NOT NULL,
pw VARCHAR(100) NOT NULL,
major_code TINYINT NOT NULL,
name VARCHAR(10) NOT NULL,
PRIMARY KEY(student_id),
FOREIGN KEY(major_code) REFERENCES major(major_code) ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

게시물 테이블

설명

세자보에 등록된 게시물을 관리하는 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입
True	True			게시글 아이디	post_id	INT
		True		작성자	author	INT
				게시일	reg_date	DATETIME
				만료일	exp_date	DATETIME
				제목	title	VARCHAR(500)
				내용	content	TEXT
			True	첨부 URL	url	TEXT	http, https로 시작할 것.
			True	첨부 이미지	img_url	VARCHAR(1000)	학번 + 현재날짜로 저장.
				조회수	view_count	INT	default 0
				블록 크기	size	TINYINT	1, 2, 3, 4 중 선택

• 게시일은 무조건 현재 날짜로 고정된다.

• 사용자는 하나의 게시물만을 작성할 수 있으며, 하나의 게시물은 한명의 사용자에게 소유될 수 있다.

• 첨부 URL 혹은 첨부 이미지의 경우, 선택적으로 제출할 수 있다.

• 조회수는 맨 처음 게시글 생성시 0으로 초기화한다.

• 블록 크기는 실제 세자보 웹 페이지에 나타낼 블록의 크기를 뜻한다. 크기를 나타내는 값은 1,2,3.. 3종류가 존재한다.

• 만료 날짜를 통한 쿼리 연산을 고려하여 별도의 인덱스를 생성한다.

쿼리

CREATE TABLE IF NOT EXISTS post(
post_id INT NOT NULL AUTO_INCREMENT,
author INT NOT NULL,
reg_date DATETIME NOT NULL,
exp_date DATETIME NOT NULL,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
url TEXT,
img_url VARCHAR(1000),
view_count INT NOT NULL,
size TINYINT NOT NULL,
INDEX(exp_date),
PRIMARY KEY(post_id),
FOREIGN KEY(author) REFERENCES user(student_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

건물 테이블

설명

해당 포스트가 게시될 건물을 관리하는 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입
True				건물코드	building_code	TINYINT
				건물명	name	VARCHAR(20)

쿼리

CREATE TABLE IF NOT EXISTS building(
building_code TINYINT NOT NULL,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(building_code)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

게시물-건물 관계 테이블

설명

건물과 게시물의 다대다 관계를 관리하기 위한 관계 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입
True		True		건물코드	building_code	TINYINT
True		True		게시글 아이디	post_id	INT

쿼리

CREATE TABLE IF NOT EXISTS post_building(
building_code TINYINT NOT NULL,
post_id INT NOT NULL,
PRIMARY KEY(building_code,post_id),
FOREIGN KEY(building_code) REFERENCES building(building_code) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(post_id) REFERENCES post(post_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

호감도 테이블

설명

사용자와 게시물간의 다대다 관계를 관리하기 위한 관계 테이블이다.

PK	AI	FK	NULL	논리적 명칭	물리적 명칭	타입	비고
True		True		사용자 학번	student_id	TINYINT
True		True		게시글 아이디	post_id	INT
				좋아요/싫어요	interest	TINYINT	1: 좋아요 0: 싫어요

• interest 값을 통해 해당 사용자가 게시물에 대한 호감 기능 여부를 알 수있다. 1일 경우, 좋아요이고, 0일 경우, 싫어요를 등록한 것이며, 데이터가 없을 경우 아무 기능도 수행하지 않은 것이다.

• interest 값을 통한 쿼리 연산을 고려하여 별도의 인덱스를 생성한다.

쿼리

CREATE TABLE IF NOT EXISTS like_dislike(
student_id INT NOT NULL,
post_id INT NOT NULL,
interest TINYINT NOT NULL,
INDEX(interest),
PRIMARY KEY(student_id,post_id),
FOREIGN KEY(student_id) REFERENCES user(student_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(post_id) REFERENCES post(post_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;