[Project] DB설계 - 관리자페이지 중 직원 상세정보

이슬기·2024년 1월 24일

목록 보기

15/42

테이블

직원 (Employee) 테이블:
직원코드(PK)
직원명
부서번호(FK)
입사일
퇴사일
전화번호
주소
성별
권한
이메일
현황
직급
비밀번호
계약기간
직종id(FK)
사원번호
학력 (Education) 테이블:
학력코드(PK, 시퀀스)
기간
학교명
학과명
졸업구분
직원코드(FK)
학력구분코드(FK)
학력구분 (AcademicHistory) 테이블:
학력구분코드(PK)
학력구분명(UNIQE)
정렬순서
경력 (Career) 테이블:
경력코드(PK, 시퀀스)
회사명
부서
직급
담당업무
재직기간(개월)
직원코드(FK)
자격증 (Certification) 테이블:
자격증코드(PK, 시퀀스)
자격종류
자격증번호
발급기관명
취득일자
직원코드(FK)

시퀀스를 사용하면 유일성을 보장하면서도 중복 순서를 허용할 수 있다. 즉, 동일한 직원의 학력, 경력, 자격증 데이터가 여러 개 있더라도 각각의 순서는 유일하게 구분된다.

학력 테이블 생성

CREATE TABLE 학력 (
    학력코드 NUMBER PRIMARY KEY,
    기간 VARCHAR2(50),
    학교명 VARCHAR2(100),
    학과명 VARCHAR2(100),
    졸업구분 VARCHAR2(50),
    학력구분코드 NUMBER,
    직원코드 NUMBER,
    CONSTRAINT fk_학력_직원 FOREIGN KEY (직원코드) REFERENCES 직원(직원코드),
    CONSTRAINT fk_학력_학력구분 FOREIGN KEY (학력구분코드) REFERENCES 학력구분(학력구분코드)
);

학력코드 시퀀스 생성

CREATE SEQUENCE 학력코드_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

특정 직원의 여러 학력 정보 조회하기

<직원과 학력 테이블 조인하여 학력 정보 조회>
특정 직원코드에 해당하는 직원의 정보와 그에 따른 학력 정보를 함께 가져온다.

SELECT E.*, 
       A.학력코드, A.기간, A.학교명, A.학과명, A.졸업구분, B.학력구분명
FROM 직원 E
LEFT JOIN 학력 A ON E.직원코드 = A.직원코드
LEFT JOIN 학력구분 B ON A.학력구분코드 = B.학력구분코드
WHERE E.직원코드 = '특정 직원의 코드'
ORDER BY B.정렬순서; -- 학력구분의 정렬 순서로 정렬

학력 추가

INSERT INTO 학력 (학력코드, 기간, 학교명, 학과명, 졸업구분, 학력구분코드, 직원코드)
VALUES (학력코드_seq.NEXTVAL, '추가할 기간', '추가할 학교명', '추가할 학과명', '추가할 졸업구분', '추가할 학력구분코드', '특정 직원의 코드');

학력 삭제

DELETE FROM 학력
WHERE 학력코드 = '특정 학력코드' AND 직원코드 = '특정 직원의 코드';

학력 수정

UPDATE 학력
SET 기간 = '수정된 기간', 학교명 = '수정된 학교명', 학과명 = '수정된 학과명', 졸업구분 = '수정된 졸업구분', 학력구분코드 = '수정된 학력구분코드'
WHERE 학력코드 = '특정 학력코드' AND 직원코드 = '특정 직원의 코드';

학력 구분 테이블 생성

CREATE TABLE 학력구분 (
    학력구분코드 NUMBER PRIMARY KEY,
    학력구분명 VARCHAR2(20) UNIQUE,
    정렬순서 NUMBER
);

INSERT INTO 학력구분 VALUES (1, '고등학교', 1);
INSERT INTO 학력구분 VALUES (2, '대학교', 2);
INSERT INTO 학력구분 VALUES (3, '대학원', 3);

식별자 (학력구분코드): 각 학력구분을 고유하게 식별하기 위해 사용된다. 주로 Primary Key로 설정되어 각 학력구분에 대해 고유한 식별자를 부여한다.
정렬 순서 (정렬순서): 데이터를 특정 기준에 따라 정렬할 때 사용된다. 예를 들어, '고등학교', '대학교', '대학원'과 같은 학력구분이 순서대로 나열되기를 원할 때 사용된다. 이것은 특히 특정 순서로 나열되어야 하는 경우에 유용하다.
학력구분명에 UNIQUE 제약 조건을 부여한 이유는 동일한 학력구분명이 중복되지 않도록 강제하여 각 학력구분이 명확하게 구분되고 데이터의 일관성을 유지할 수 있다.

학력 정보를 조회할 때 학력구분코드를 사용하면 각 학력구분을 식별할 수 있고 정렬순서를 사용하면 특정 순서로 결과를 정렬할 수 있다.

이슬기

다음 포스트

[Project] DB설계 - 관리자페이지 중 직원 상세정보

project

테이블