Database Modeling 2: 논리모델, 물리모델 및 관계

업데이트: November 13, 2020
15 분 소요

비트캠프 서초본원 엄진영 강사님의 수업을 듣고 정리했습니다.

DB 모델링

3. 논리모델

특정 DBMS를 고려하지 않고 수행하는 개념적인 모델링

(1) 엔티티 식별 및 속성 식별

특정 값들의 집합? 시스템에서 다루는 데이터를 식별한다.
다른 말로 “테이블”이라고 한다.
테이블을 구성하는 값 ⇒ 속성(attribute) = 컬럼(column)
예)
- 학생(이름,전화,이메일,주소,…)
- 강의(강의명,설명,시작일,종료일,강의료,…)

DB모델링 엔티티 식별 및 속성 식별

(2) 주 키 선정(Primary Key; PK)

데이터를 구분할 때 사용할 식별자를 지정한다.
만약 PK로 지정할 적절한 컬럼이 없거나, 있더라도 여러 개의 컬럼을 묶어서 사용해야 하는 경우 surrogate key(대리 키=인공 키) 사용을 고려하라!

DB모델링 주 키 선정

(3) 포함 관계 및 배타적 관계 추가

여러 테이블에 공통으로 포함되는 컬럼이 있는 경우, 별도의 테이블로 정의한다. 다음 중 하나의 관계로 설정한다.
기본 데이터를 저장하고 있는 테이블 쪽을 부모 테이블로 한다.
포함 관계
- 여러 테이블에서 동시에 포함할 수 있는 관계이다.
배타적 관계
- 여러 테이블 중에서 오직 한 개의 테이블만 포함할 수 있는 관계다.
예) 학생, 강사, 매니저에서 공통되는 데이터를 ‘사용자’라는 별도의 테이블로 정의했다. 이 경우 사용자 테이블이 부모 테이블이다. 학생, 강사, 매니저 테이블은 자식 테이블이다.

DB모델링 포함 관계 및 배타적 관계 추가

(4) 제1정규화

정규화? 데이터 중복을 찾아내어 별도의 테이블로 데이터를 분리시키는 것.
중복 데이터 또는 중복 컬럼을 별도의 테이블로 분리하여 부모-자식 관계를 맺는다.
데이터를 참조 하는 테이블이 자식 테이블이고, 데이터를 갖고 있는 테이블이 부모 테이블이다.
- 한 부모 아래 여러 자식이 있을 수는 있지만, 여러 부모 아래에 한 자식이 있을 수는 없다. 따라서 여러 테이블에서 중복된 데이터를 뽑아서 만든 테이블이 부모 테이블이 된다.
자식 테이블에서는 부모 테이블의 데이터를 가리키기 위해 부모$ 데이터의 pk값을 보관해야 한다.
이렇게 부모 테이블의 데이터에 대해 PK값을 저장하는 컬럼을 외부키(FK)라 부른다.
중복 데이터? 수강생 데이터, 강의 데이터, 강사 데이터, …
중복 컬럼? 사진1, 사진2, 사진3

DB모델링 제1정규화

DB모델링

(5) 제2정규화

PK가 여러 컬럼으로 이루어진 경우에 수행
모든 일반 컬럼은 반드시 PK 컬럼에 종속되어야 한다. 그렇지 않은 일반 컬럼이 있다면 별도의 테이블로 분리하여 부모-자식 관계를 맺는다.

(6) 제3정규화

어떤 컬럼이 PK가 아닌 다른 일반 컬럼에 종속되는 경우가 있다면, 별도 테이블로 분리하여 부모-자식 관계를 맺는다.
예) 사용자 테이블의 우편번호와 기본주소의 경우, 각각의 컬럼처럼 보이지만 사실 기본주소는 우편 번호에 종속된다. 따라서 별도의 테이블로 분리하는데, 이 때 주소 테이블이 부모 테이블이 된다.

DB모델링

(7) 다 대 다 관계의 해소

테이블과 테이블 사이에 다 대 다 관계를 형성한다면, 일 대 다의 관계로 변경해야 한다.
왜? DBMS는 물리적으로 다 대 다 관계의 데이터를 저장할 수 없다.
해결책? 두 테이블의 관계를 저장할 테이블을 만든다. “관계 테이블” 이라 부른다. 관계 테이블은 각 테이블과 일 대 다의 관계를 맺는다. 관계 테이블은 연결 테이블이라고 불리기도 한다.
위의 예에서는 수강신청 테이블과 교육과정강사 테이블이 관계테이블이다. 강사 테이블과 교육과정 테이블은 다 대 다 관계를 형성한다. 여러 명의 강사가 한 교육과정을 담당할 수도 있고, 여러 교육과정을 한 강사가 담당할 수도 있기 때문이다.

DB모델링 다 대 다 관계

DBMS는 다 대 다 관계의 데이터를 저장할 수 없기 때문에 다 대 다 관계를 일 대 다 관계로 변경하기 위해 ‘교육과정강사’라는 테이블을 새로 만들었다. 그리고 이 테이블에 강사 테이블과 교육과정 테이블의 PK값인 교육과정 번호와 강사 번호를 저장하였다.

DB모델링 다 대 다 관계 해소

(8) 관계의 차수 지정

데이터 끼리 상호 관계의 개수를 지정한다.
관계 차수의 종류
- 1 : * (0이상) ⇒ FK 컬럼이 not null 이다.
- 1 : 1..* (1이상) ⇒ FK 컬럼이 not null 이다.
- 0..1 : * (0이상) ⇒ FK 컬럼이 null 허용이다.
- 0..1 : 1..* (1이상) ⇒ FK 컬럼이 null 허용이다.
관계 차수를 그림으로 표현
- 일 대 일
- 일 대 다
- 다 대 다
예) 교실과 교실 사진의 관계에서, 교실 사진에 null을 허용한다면 zero or more의 관계차수를 지정하고, null을 허용하지 않는다면(not null이라면) one more more의 관계 차수를 지정한다.

DB모델링 관계 차수 예

DB모델링 관계 차수

(9) 유니크(Unique) 컬럼 지정

PK는 아니지만 PK처럼 중복되어서는 안되는 컬럼이다.
대체 키(alternate key) 컬럼이 유니크 컬럼이 된다.
즉 PK로 선정되지 않은 나머지 후보 키는 유니크 컬럼으로 지정하여 데이터가 중복되지 않도록 한다.
예) 사용자 테이블에서 이메일을 유니크 컬럼으로 지정한다면, 사용자 정보를 저장할 때 이메일 정보를 중복으로 저장할 수 없다.

DB모델링 유니크 컬럼 지정

주의) 이메일과 주민등록번호를 유니크로 지정하는 경우, 각각을 유니크로 지정해야 한다. (이메일, 주민등록번호)를 한 세트로 유니크로 지정하면 안된다.

(10) null 허용 여부 지정

필수 입력 컬럼(not null)인지 선택 입력 컬럼(null)인지 지정한다.

DB모델링 null 허용 여부 지정

(11) 인덱스 컬럼 지정

데이터를 찾을 때 검색 조건으로 사용할 컬럼을 지정한다.
조회 컬럼으로 지정하면 그 컬럼의 값으로 색인표가 자동으로 생성되어 데이터를 찾는 속도가 빨라진다.
장점: select 속도가 빨라진다.
단점: insert,update,delete 할 때 마다 색인표를 갱신해야하므로 속도가 느리다.
예) 사용자 테이블의 사용자 번호를 인덱스로 지정하였다.

DB모델링 인덱스 컬럼 지정 방법

DB모델링 인덱스 컬럼 지정

4. 물리모델

특정 DBMS에 맞춘 물리적인 모델링

(21) DBMS에 맞춰서 테이블명과 컬럼명을 설정한다.

DBMS에서 테이블명과 컬럼명을 작성할 때 보통 다음의 규칙에 따라 작성한다.
예) first name(FST_NM), regist date(REG_DT), teacher assignment(TCH_ASN)
단어는 알파벳 3자 또는 4자로 축약해서 표현한다.
단어와 단어 사이는 밑 줄( _ )로 표현한다.

DB모델링 테이블명과 컬럼명 설정

(22) 도메인(domain) 정의 및 적용

비슷한 종류의 컬럼들을 묶어 새 타입으로 정의한다.
이점: 타입을 변경할 때 한 번에 여러 컬럼을 변경할 수 있어서 유지보수에 좋다.

DB모델링 도메인 정의

DB모델링 도메인 적용

(23) 번호가 자동 증가하는 컬럼을 지정

테이블의 PK 중에서 자동으로 증가해야 하는 컬럼을 지정한다.

(24) 기본 값 및 제약 조건 설정

일부 컬럼에 대해 기본 값을 설정한다.
일부 컬럼의 값의 범위를 지정한다.

포워드 엔지니어링(forward engineering)

모델 → 코드
참고: 리버스 엔지니어링(reverse engineering) 코드 → 모델
포워드 엔지니어링을 얻은 코드

-- 학생
DROP TABLE IF EXISTS lms_stud RESTRICT;

-- 강사
DROP TABLE IF EXISTS lms_tchr RESTRICT;

-- 매니저
DROP TABLE IF EXISTS lms_mgr RESTRICT;

-- 교육과정
DROP TABLE IF EXISTS lms_lect RESTRICT;

-- 수강신청
DROP TABLE IF EXISTS lms_appl RESTRICT;

-- 교실
DROP TABLE IF EXISTS lms_room RESTRICT;

-- 사용자
DROP TABLE IF EXISTS lms_users RESTRICT;

-- 학력
DROP TABLE IF EXISTS lms_edu_level RESTRICT;

-- 학교
DROP TABLE IF EXISTS lms_school RESTRICT;

-- 전공
DROP TABLE IF EXISTS lms_major RESTRICT;

-- 지점
DROP TABLE IF EXISTS lms_center RESTRICT;

-- 은행
DROP TABLE IF EXISTS lms_bank RESTRICT;

-- 교육과정강사
DROP TABLE IF EXISTS lms_tchr_lect RESTRICT;

-- 교실사진
DROP TABLE IF EXISTS lms_room_photo RESTRICT;

-- 직급
DROP TABLE IF EXISTS lms_posi RESTRICT;

-- 부서
DROP TABLE IF EXISTS lms_dept RESTRICT;

-- 주소
DROP TABLE IF EXISTS lms_addr RESTRICT;

-- 학생
CREATE TABLE lms_stud (
  sno   INTEGER     NOT NULL COMMENT '학생번호', -- 학생번호
  work  INTEGER     NOT NULL COMMENT '재직여부', -- 재직여부
  elno  INTEGER     NOT NULL COMMENT '학력번호', -- 학력번호
  schno INTEGER     NOT NULL COMMENT '학교번호', -- 학교번호
  majno INTEGER     NOT NULL COMMENT '전공번호', -- 전공번호
  banno INTEGER     NULL     COMMENT '은행번호', -- 은행번호
  acnt  VARCHAR(20) NULL     COMMENT '계좌번호' -- 계좌번호
)
COMMENT '학생';

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT PK_lms_stud -- 학생 기본키
    PRIMARY KEY (
      sno -- 학생번호
    );

-- 강사
CREATE TABLE lms_tchr (
  tno   INTEGER     NOT NULL COMMENT '강사번호', -- 강사번호
  wage  INTEGER     NOT NULL COMMENT '시급', -- 시급
  elno  INTEGER     NOT NULL COMMENT '학력번호', -- 학력번호
  schno INTEGER     NOT NULL COMMENT '학교번호', -- 학교번호
  majno INTEGER     NOT NULL COMMENT '전공번호', -- 전공번호
  banno INTEGER     NULL     COMMENT '은행번호', -- 은행번호
  acnt  VARCHAR(20) NULL     COMMENT '계좌번호' -- 계좌번호
)
COMMENT '강사';

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT PK_lms_tchr -- 강사 기본키
    PRIMARY KEY (
      tno -- 강사번호
    );

-- 매니저
CREATE TABLE lms_mgr (
  mno INTEGER     NOT NULL COMMENT '매니저번호', -- 매니저번호
  pno INTEGER     NOT NULL COMMENT '직급번호', -- 직급번호
  dno INTEGER     NOT NULL COMMENT '부서번호', -- 부서번호
  fax VARCHAR(30) NULL     COMMENT '팩스' -- 팩스
)
COMMENT '매니저';

-- 매니저
ALTER TABLE lms_mgr
  ADD CONSTRAINT PK_lms_mgr -- 매니저 기본키
    PRIMARY KEY (
      mno -- 매니저번호
    );

-- 교육과정
CREATE TABLE lms_lect (
  lno     INTEGER      NOT NULL COMMENT '교육과정번호', -- 교육과정번호
  title   VARCHAR(255) NOT NULL COMMENT '과정명', -- 과정명
  content MEDIUMTEXT   NOT NULL COMMENT '내용', -- 내용
  sdt     DATE         NOT NULL COMMENT '시작일', -- 시작일
  edt     DATE         NOT NULL COMMENT '종료일', -- 종료일
  hrs     INTEGER      NOT NULL COMMENT '총강의시간', -- 총강의시간
  rno     INTEGER      NULL     COMMENT '교실번호', -- 교실번호
  mno     INTEGER      NULL     COMMENT '매니저번호' -- 매니저번호
)
COMMENT '교육과정';

-- 교육과정
ALTER TABLE lms_lect
  ADD CONSTRAINT PK_lms_lect -- 교육과정 기본키
    PRIMARY KEY (
      lno -- 교육과정번호
    );

ALTER TABLE lms_lect
  MODIFY COLUMN lno INTEGER NOT NULL AUTO_INCREMENT COMMENT '교육과정번호';

-- 수강신청
CREATE TABLE lms_appl (
  ano  INTEGER  NOT NULL COMMENT '수강신청번호', -- 수강신청번호
  sno  INTEGER  NOT NULL COMMENT '학생번호', -- 학생번호
  lno  INTEGER  NOT NULL COMMENT '교육과정번호', -- 교육과정번호
  cdt  DATETIME NOT NULL DEFAULT now() COMMENT '신청일', -- 신청일
  stat INTEGER  NULL     DEFAULT 0 COMMENT '상태' -- 상태
)
COMMENT '수강신청';

-- 수강신청
ALTER TABLE lms_appl
  ADD CONSTRAINT PK_lms_appl -- 수강신청 기본키
    PRIMARY KEY (
      ano -- 수강신청번호
    );

-- 수강신청 유니크 인덱스
CREATE UNIQUE INDEX UIX_lms_appl
  ON lms_appl ( -- 수강신청
    sno ASC, -- 학생번호
    lno ASC  -- 교육과정번호
  );

ALTER TABLE lms_appl
  MODIFY COLUMN ano INTEGER NOT NULL AUTO_INCREMENT COMMENT '수강신청번호';

-- 교실
CREATE TABLE lms_room (
  rno   INTEGER     NOT NULL COMMENT '교실번호', -- 교실번호
  cenno INTEGER     NOT NULL COMMENT '지점번호', -- 지점번호
  title VARCHAR(50) NOT NULL COMMENT '교실명', -- 교실명
  capa  INTEGER     NOT NULL COMMENT '수용인원' -- 수용인원
)
COMMENT '교실';

-- 교실
ALTER TABLE lms_room
  ADD CONSTRAINT PK_lms_room -- 교실 기본키
    PRIMARY KEY (
      rno -- 교실번호
    );

ALTER TABLE lms_room
  MODIFY COLUMN rno INTEGER NOT NULL AUTO_INCREMENT COMMENT '교실번호';

-- 사용자
CREATE TABLE lms_users (
  uno      INTEGER      NOT NULL COMMENT '사용자번호', -- 사용자번호
  name     VARCHAR(50)  NOT NULL COMMENT '이름', -- 이름
  email    VARCHAR(40)  NOT NULL COMMENT '이메일', -- 이메일
  pwd      VARCHAR(50)  NOT NULL COMMENT '암호', -- 암호
  jumin    VARCHAR(13)  NOT NULL COMMENT '주민번호', -- 주민번호
  tel      VARCHAR(30)  NOT NULL COMMENT '전화', -- 전화
  ano      INTEGER      NOT NULL COMMENT '주소번호', -- 주소번호
  det_addr VARCHAR(255) NULL     COMMENT '상세주소', -- 상세주소
  photo    VARCHAR(255) NULL     COMMENT '사진' -- 사진
)
COMMENT '사용자';

-- 사용자
ALTER TABLE lms_users
  ADD CONSTRAINT PK_lms_users -- 사용자 기본키
    PRIMARY KEY (
      uno -- 사용자번호
    );

-- 사용자 유니크 인덱스
CREATE UNIQUE INDEX UIX_lms_users
  ON lms_users ( -- 사용자
    email ASC -- 이메일
  );

-- 사용자 유니크 인덱스2
CREATE UNIQUE INDEX UIX_lms_users2
  ON lms_users ( -- 사용자
    ano ASC -- 주소번호
  );

-- 사용자 인덱스
CREATE INDEX IX_lms_users
  ON lms_users( -- 사용자
    name ASC -- 이름
  );

ALTER TABLE lms_users
  MODIFY COLUMN uno INTEGER NOT NULL AUTO_INCREMENT COMMENT '사용자번호';

-- 학력
CREATE TABLE lms_edu_level (
  elno  INTEGER     NOT NULL COMMENT '학력번호', -- 학력번호
  title VARCHAR(50) NOT NULL COMMENT '학력명' -- 학력명
)
COMMENT '학력';

-- 학력
ALTER TABLE lms_edu_level
  ADD CONSTRAINT PK_lms_edu_level -- 학력 기본키
    PRIMARY KEY (
      elno -- 학력번호
    );

-- 학력 인덱스
CREATE INDEX IX_lms_edu_level
  ON lms_edu_level( -- 학력
    title ASC -- 학력명
  );

ALTER TABLE lms_edu_level
  MODIFY COLUMN elno INTEGER NOT NULL AUTO_INCREMENT COMMENT '학력번호';

-- 학교
CREATE TABLE lms_school (
  schno INTEGER     NOT NULL COMMENT '학교번호', -- 학교번호
  title VARCHAR(50) NOT NULL COMMENT '학교명' -- 학교명
)
COMMENT '학교';

-- 학교
ALTER TABLE lms_school
  ADD CONSTRAINT PK_lms_school -- 학교 기본키
    PRIMARY KEY (
      schno -- 학교번호
    );

-- 학교 인덱스
CREATE INDEX IX_lms_school
  ON lms_school( -- 학교
    title ASC -- 학교명
  );

ALTER TABLE lms_school
  MODIFY COLUMN schno INTEGER NOT NULL AUTO_INCREMENT COMMENT '학교번호';

-- 전공
CREATE TABLE lms_major (
  majno INTEGER     NOT NULL COMMENT '전공번호', -- 전공번호
  title VARCHAR(50) NOT NULL COMMENT '전공명' -- 전공명
)
COMMENT '전공';

-- 전공
ALTER TABLE lms_major
  ADD CONSTRAINT PK_lms_major -- 전공 기본키
    PRIMARY KEY (
      majno -- 전공번호
    );

-- 전공 인덱스
CREATE INDEX IX_lms_major
  ON lms_major( -- 전공
    title ASC -- 전공명
  );

ALTER TABLE lms_major
  MODIFY COLUMN majno INTEGER NOT NULL AUTO_INCREMENT COMMENT '전공번호';

-- 지점
CREATE TABLE lms_center (
  cenno INTEGER     NOT NULL COMMENT '지점번호', -- 지점번호
  title VARCHAR(50) NOT NULL COMMENT '지점명' -- 지점명
)
COMMENT '지점';

-- 지점
ALTER TABLE lms_center
  ADD CONSTRAINT PK_lms_center -- 지점 기본키
    PRIMARY KEY (
      cenno -- 지점번호
    );

-- 지점 인덱스
CREATE INDEX IX_lms_center
  ON lms_center( -- 지점
    title ASC -- 지점명
  );

ALTER TABLE lms_center
  MODIFY COLUMN cenno INTEGER NOT NULL AUTO_INCREMENT COMMENT '지점번호';

-- 은행
CREATE TABLE lms_bank (
  banno INTEGER     NOT NULL COMMENT '은행번호', -- 은행번호
  title VARCHAR(50) NOT NULL COMMENT '은행명' -- 은행명
)
COMMENT '은행';

-- 은행
ALTER TABLE lms_bank
  ADD CONSTRAINT PK_lms_bank -- 은행 기본키
    PRIMARY KEY (
      banno -- 은행번호
    );

-- 은행 인덱스
CREATE INDEX IX_lms_bank
  ON lms_bank( -- 은행
    title ASC -- 은행명
  );

ALTER TABLE lms_bank
  MODIFY COLUMN banno INTEGER NOT NULL AUTO_INCREMENT COMMENT '은행번호';

-- 교육과정강사
CREATE TABLE lms_tchr_lect (
  lno INTEGER NOT NULL COMMENT '교육과정번호', -- 교육과정번호
  tno INTEGER NOT NULL COMMENT '강사번호' -- 강사번호
)
COMMENT '교육과정강사';

-- 교육과정강사
ALTER TABLE lms_tchr_lect
  ADD CONSTRAINT PK_lms_tchr_lect -- 교육과정강사 기본키
    PRIMARY KEY (
      lno, -- 교육과정번호
      tno  -- 강사번호
    );

-- 교실사진
CREATE TABLE lms_room_photo (
  rpno     INTEGER      NOT NULL COMMENT '교실사진번호', -- 교실사진번호
  filepath VARCHAR(255) NOT NULL COMMENT '사진파일', -- 사진파일
  rno      INTEGER      NOT NULL COMMENT '교실번호' -- 교실번호
)
COMMENT '교실사진';

-- 교실사진
ALTER TABLE lms_room_photo
  ADD CONSTRAINT PK_lms_room_photo -- 교실사진 기본키
    PRIMARY KEY (
      rpno -- 교실사진번호
    );

ALTER TABLE lms_room_photo
  MODIFY COLUMN rpno INTEGER NOT NULL AUTO_INCREMENT COMMENT '교실사진번호';

-- 직급
CREATE TABLE lms_posi (
  pno   INTEGER     NOT NULL COMMENT '직급번호', -- 직급번호
  title VARCHAR(50) NOT NULL COMMENT '직급명' -- 직급명
)
COMMENT '직급';

-- 직급
ALTER TABLE lms_posi
  ADD CONSTRAINT PK_lms_posi -- 직급 기본키
    PRIMARY KEY (
      pno -- 직급번호
    );

-- 직급 인덱스
CREATE INDEX IX_lms_posi
  ON lms_posi( -- 직급
    title ASC -- 직급명
  );

ALTER TABLE lms_posi
  MODIFY COLUMN pno INTEGER NOT NULL AUTO_INCREMENT COMMENT '직급번호';

-- 부서
CREATE TABLE lms_dept (
  dno   INTEGER     NOT NULL COMMENT '부서번호', -- 부서번호
  cenno INTEGER     NOT NULL COMMENT '지점번호', -- 지점번호
  title VARCHAR(50) NOT NULL COMMENT '부서명' -- 부서명
)
COMMENT '부서';

-- 부서
ALTER TABLE lms_dept
  ADD CONSTRAINT PK_lms_dept -- 부서 기본키
    PRIMARY KEY (
      dno -- 부서번호
    );

ALTER TABLE lms_dept
  MODIFY COLUMN dno INTEGER NOT NULL AUTO_INCREMENT COMMENT '부서번호';

-- 주소
CREATE TABLE lms_addr (
  ano      INTEGER      NOT NULL COMMENT '주소번호', -- 주소번호
  pstno    VARCHAR(6)   NOT NULL COMMENT '우편번호', -- 우편번호
  bas_addr VARCHAR(255) NULL     COMMENT '기본주소' -- 기본주소
)
COMMENT '주소';

-- 주소
ALTER TABLE lms_addr
  ADD CONSTRAINT PK_lms_addr -- 주소 기본키
    PRIMARY KEY (
      ano -- 주소번호
    );

ALTER TABLE lms_addr
  MODIFY COLUMN ano INTEGER NOT NULL AUTO_INCREMENT COMMENT '주소번호';

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT FK_lms_users_TO_lms_stud -- 사용자 -> 학생
    FOREIGN KEY (
      sno -- 학생번호
    )
    REFERENCES lms_users ( -- 사용자
      uno -- 사용자번호
    );

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT FK_lms_edu_level_TO_lms_stud -- 학력 -> 학생
    FOREIGN KEY (
      elno -- 학력번호
    )
    REFERENCES lms_edu_level ( -- 학력
      elno -- 학력번호
    );

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT FK_lms_school_TO_lms_stud -- 학교 -> 학생
    FOREIGN KEY (
      schno -- 학교번호
    )
    REFERENCES lms_school ( -- 학교
      schno -- 학교번호
    );

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT FK_lms_major_TO_lms_stud -- 전공 -> 학생
    FOREIGN KEY (
      majno -- 전공번호
    )
    REFERENCES lms_major ( -- 전공
      majno -- 전공번호
    );

-- 학생
ALTER TABLE lms_stud
  ADD CONSTRAINT FK_lms_bank_TO_lms_stud -- 은행 -> 학생
    FOREIGN KEY (
      banno -- 은행번호
    )
    REFERENCES lms_bank ( -- 은행
      banno -- 은행번호
    );

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT FK_lms_users_TO_lms_tchr -- 사용자 -> 강사
    FOREIGN KEY (
      tno -- 강사번호
    )
    REFERENCES lms_users ( -- 사용자
      uno -- 사용자번호
    );

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT FK_lms_edu_level_TO_lms_tchr -- 학력 -> 강사
    FOREIGN KEY (
      elno -- 학력번호
    )
    REFERENCES lms_edu_level ( -- 학력
      elno -- 학력번호
    );

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT FK_lms_school_TO_lms_tchr -- 학교 -> 강사
    FOREIGN KEY (
      schno -- 학교번호
    )
    REFERENCES lms_school ( -- 학교
      schno -- 학교번호
    );

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT FK_lms_major_TO_lms_tchr -- 전공 -> 강사
    FOREIGN KEY (
      majno -- 전공번호
    )
    REFERENCES lms_major ( -- 전공
      majno -- 전공번호
    );

-- 강사
ALTER TABLE lms_tchr
  ADD CONSTRAINT FK_lms_bank_TO_lms_tchr -- 은행 -> 강사
    FOREIGN KEY (
      banno -- 은행번호
    )
    REFERENCES lms_bank ( -- 은행
      banno -- 은행번호
    );

-- 매니저
ALTER TABLE lms_mgr
  ADD CONSTRAINT FK_lms_users_TO_lms_mgr -- 사용자 -> 매니저
    FOREIGN KEY (
      mno -- 매니저번호
    )
    REFERENCES lms_users ( -- 사용자
      uno -- 사용자번호
    );

-- 매니저
ALTER TABLE lms_mgr
  ADD CONSTRAINT FK_lms_posi_TO_lms_mgr -- 직급 -> 매니저
    FOREIGN KEY (
      pno -- 직급번호
    )
    REFERENCES lms_posi ( -- 직급
      pno -- 직급번호
    );

-- 매니저
ALTER TABLE lms_mgr
  ADD CONSTRAINT FK_lms_dept_TO_lms_mgr -- 부서 -> 매니저
    FOREIGN KEY (
      dno -- 부서번호
    )
    REFERENCES lms_dept ( -- 부서
      dno -- 부서번호
    );

-- 교육과정
ALTER TABLE lms_lect
  ADD CONSTRAINT FK_lms_mgr_TO_lms_lect -- 매니저 -> 교육과정
    FOREIGN KEY (
      mno -- 매니저번호
    )
    REFERENCES lms_mgr ( -- 매니저
      mno -- 매니저번호
    );

-- 교육과정
ALTER TABLE lms_lect
  ADD CONSTRAINT FK_lms_room_TO_lms_lect -- 교실 -> 교육과정
    FOREIGN KEY (
      rno -- 교실번호
    )
    REFERENCES lms_room ( -- 교실
      rno -- 교실번호
    );

-- 수강신청
ALTER TABLE lms_appl
  ADD CONSTRAINT FK_lms_stud_TO_lms_appl -- 학생 -> 수강신청
    FOREIGN KEY (
      sno -- 학생번호
    )
    REFERENCES lms_stud ( -- 학생
      sno -- 학생번호
    );

-- 수강신청
ALTER TABLE lms_appl
  ADD CONSTRAINT FK_lms_lect_TO_lms_appl -- 교육과정 -> 수강신청
    FOREIGN KEY (
      lno -- 교육과정번호
    )
    REFERENCES lms_lect ( -- 교육과정
      lno -- 교육과정번호
    );

-- 교실
ALTER TABLE lms_room
  ADD CONSTRAINT FK_lms_center_TO_lms_room -- 지점 -> 교실
    FOREIGN KEY (
      cenno -- 지점번호
    )
    REFERENCES lms_center ( -- 지점
      cenno -- 지점번호
    );

-- 사용자
ALTER TABLE lms_users
  ADD CONSTRAINT FK_lms_addr_TO_lms_users -- 주소 -> 사용자
    FOREIGN KEY (
      ano -- 주소번호
    )
    REFERENCES lms_addr ( -- 주소
      ano -- 주소번호
    );

-- 교육과정강사
ALTER TABLE lms_tchr_lect
  ADD CONSTRAINT FK_lms_lect_TO_lms_tchr_lect -- 교육과정 -> 교육과정강사
    FOREIGN KEY (
      lno -- 교육과정번호
    )
    REFERENCES lms_lect ( -- 교육과정
      lno -- 교육과정번호
    );

-- 교육과정강사
ALTER TABLE lms_tchr_lect
  ADD CONSTRAINT FK_lms_tchr_TO_lms_tchr_lect -- 강사 -> 교육과정강사
    FOREIGN KEY (
      tno -- 강사번호
    )
    REFERENCES lms_tchr ( -- 강사
      tno -- 강사번호
    );

-- 교실사진
ALTER TABLE lms_room_photo
  ADD CONSTRAINT FK_lms_room_TO_lms_room_photo -- 교실 -> 교실사진
    FOREIGN KEY (
      rno -- 교실번호
    )
    REFERENCES lms_room ( -- 교실
      rno -- 교실번호
    );

-- 부서
ALTER TABLE lms_dept
  ADD CONSTRAINT FK_lms_center_TO_lms_dept -- 지점 -> 부서
    FOREIGN KEY (
      cenno -- 지점번호
    )
    REFERENCES lms_center ( -- 지점
      cenno -- 지점번호
    );

정규화

데이터가 중복되지 않도록 구조화시키는 것.
참고: 역정규화 (실행 속도를 높이기 위해 데이터 중복을 허용하는 것)

5. 관계: 부모 테이블과 자식 테이블

부모 테이블

자식 테이블이 참조하는 데이터를 갖고 있는 테이블 (참조 당하는 테이블)

자식 테이블

부모 테이블의 데이터를 참조하기 위해 그 데이터의 PK 값을 갖고 있는 테이블
이렇게 부모 테이블의 PK를 저장하는 컬럼을 FK(Foreign Key)라 부른다.

외부키(Foreign Key; FK)

자식 테이블에서 부모 테이블의 특정 데이터를 가리키는 컬럼이다.
반드시 부모 테이블의 PK 컬럼 값을 저장해야 한다. 다른 일반 컬럼의 값은 사용할 수 없다.

6. 식별 관계와 비식별 관계

비식별 관계(non-identifying)

자식 테이블의 외부키(FK)가 그 테이블에서 일반 컬럼으로 사용될 때
즉 관계를 표현하는 외부키가 그 테이블에서 식별자로 사용되지 않는 것을 말한다.
FK != PK

식별 관계(identifying)

자식 테이블의 외부키(FK)가 그 테이블에서 PK 컬럼으로 사용될 때
즉 관계를 표현하는 외부키가 그 테이블에서 식별자로 사용되는 것을 말한다.
FK == PK (FK이면서 PK이다.)

Twitter Facebook LinkedIn

유지연