[데이터베이스] 8. 논리적 데이터베이스 설계

이번 글에서는 개념적 데이터베이스 설계를 통해 작성한 ERD를 관계 모델(테이블)로 구현해본다.

 

엔티티 셋

엔티티 셋은 테이블로 만든다.

 

그림과 같은 엔티티 셋을 테이블로 만든다면 아래와 같이 SQL을 작성할 수 있다.

 

CREATE TABLE Employees (
    ssn  char(11),
    name char(20),
    lot  integer,
    
    PRIMARY KEY (ssn)
);

 

 

관계 집합 (relationship set)

이진 관계

관계 집합은 하나의 테이블로 만들 수 있다.

이때 관계집합은 자신이 갖는 어트리뷰트 외에, 관계를 맺는 두 엔티티 셋의 PK 를 FK로 가져야 한다.

(그리고 두 엔티티 셋의 PK 조합은 관계 집합의 슈퍼키가 된다.)

 

 

그림과 같은 ERD가 있을 때, Works_In 관계 집합은 다음과 같은 테이블로 만들어질 수 있다.

 

CREATE TABLE Works_In (
    ssn   char(11),
    did   char(11),
    since date,
    
    PRIMARY KEY (ssn, did),
    FOREIGN KEY (ssn) REFERENCES Employees,
    FOREIGN KEY (did) REFERENCES Departments
);

 

외래키로 사용되는 ssn, did 는 Employees, Departments 에서 PK 로 사용되므로 암묵적으로 Not Null 제약조건이 걸려있다.

 

 

삼진 관계

 

삼진 관계도 Foreign Key 가 하나 더 늘어나는 것에 불과하다.

CREATE TABLE Works_In2 (
    ssn     char(11),
    did     char(11),
    address char(20),
    
    PRIMARY KEY (ssn, did, address),
    FOREIGN KEY (ssn) REFERENCES Employees,
    FOREIGN KEY (did) REFERENCES Departments,
    FOREIGN KEY (address) REFERENCES Location
)

 

 

 

같은 엔티티  셋 안에서의 관계

 

같은 엔티티 셋 안에서 관계를 맺는 경우에도 비슷하게 작성하면 된다.

이때 외래키 필드의 이름과 참조하는 테이블의 기본키 필드 이름이 다르기 때문에 꼭 필드 이름을 명시해주어야 한다.

 

CREATE TABLE Reports_To (
    supervisor_ssn  char(20),
    subordinate_ssn char(20),
    
    PRIMARY KEY (supervisor_ssn, subordinate_ssn),
    FOREIGN KEY (supervisor_ssn) REFERENCES Employees(ssn),
    FOREIGN KEY (subordinate_ssn) REFERENCES Employees(ssn)
)

 

References 이후에 테이블 이름과 함께 기본키 필드의 이름을 명시적으로 지정한 것에 유의하자.

 

 

 

키 제약조건이 있는 관계

 

기존에 하던 것처럼 관계 집합을 테이블로 만들면 아래와 같다.

 

CREATE TABLE Manages (
    ssn    char(11),
    did    char(11),
    since  date
    
    PRIMARY KEY (did),
    FOREIGN KEY (ssn) REFERENCES Employees,
    FOREIGN KEY (did) REFERENCES Departments
)

 

 

이때 이 관계 테이블의 기본키는 did 하나로 충분하다.

키 제약조건에 의해 부서는 관계에 최대 1번만 참여하기 때문이다.

 

그래서 키 제약조건이 있는 관계는 꼭 테이블로 만들 필요가 없다.

Departments 가 관계에 최대 한번만 참여하기 때문에, Department 엔티티에 자신이 관계를 맺고 있는 Employees 엔티티를 명시할 수 있기 때문이다.

 

CREATE TABLE Department_Mng (
    did    char(11),
    dname  char(20),
    budget Integer,
    ssn    char(11),
    since  date
    
    PRIMARY KEY (did),
    FOREIGN KEY (ssn) REFERENCES Employees
)

 

따라서 이렇게 테이블로 만들 수도 있다.

그리고 이렇게 만들면 위 방식보다 더 빠르다는 장점이 있다.

테이블을 넘나들면서 찾는 것은 하드디스크 영역을 넘나들면서 찾는 것과 같기 때문에 오래 걸릴 수 있으며,

기존 방법은 조회를 하기 위해서 두 어트리뷰트의 조합을 통해 레코드를 식별해야 하기 때문이다.

 

기존에는 부서 입장에서 자신이 관계를 맺고 있는 관리자 정보를 알기 위해서 Manage 테이블을 타고 들어가서 관리자 ssn 을 확인한 뒤, 그 ssn 으로 다시 관리자를 조회해야 했지만, 이 방식은 바로 관리자의 ssn을 알 수 있기에 더 빠르다.

 

하지만 이렇게 저장하는 경우에는 관리자가 없는 부서의 경우에는 null 값을 저장해야 하는 문제가 있다.

(ssn은 직원 테이블에서는 기본키이므로 null 이 될 수 없지만, 부서 테이블에서는 외래키이므로 null 이 될 수 있다.)

 

각 설계마다 장단점이 있으므로 벤치마크를 통해 DBMS 성능을 측정하고 데이터 조회가 많은지, 수정이 많은지를 따져가며 설계를 해야 한다.

 

 

참여 제약조건이 있는 관계

 

현재 ERD 에서 Manages 관계를 먼저 생각해보자.

모든 부서는 관리자가 반드시 최소 1명은 존재해야 한다.

 

참여 제약조건을 명시하려면 기존의 Manages 관계집합을 테이블을 만드는 방법으로는 명시할 수 없다.

이 테이블에 레코드로 저장되지 않으면 그 부서는 관리자가 없을 수 있는데, '레코드가 존재해야 한다' 와 같은 제약 조건은 테이블에 걸 수 없기 때문이다.

 

따라서 두번째 방법을 사용해서 다음과 같이 테이블을 만들어야 한다.

CREATE TABLE Department_Mng (
    did    char(11),
    dname  char(20),
    budget Integer,
    ssn    char(11) NOT NULL,
    since  date
    
    PRIMARY KEY (did),
    FOREIGN KEY (ssn) REFERENCES Employees
)

 

전체 참여조건은 부서가 존재한다면, 그 부서는 반드시 관리자 직원이 존재해야 한다는 것과 같다.

따라서 관리자 직원 정보를 갖는 필드에 NOT NULL 제약조건을 추가하면 전체 참여 제약조건을 명시할 수 있다.

 

하지만 실제 비즈니스 로직을 고려하면 부서가 신설되었을 때 한동안 관리자가 없을 수도 있다.

이런 경우는 어떻게 처리할까?

Check 제약조건을 걸어서 일정기간 동안은 null 을 허용하는 방식으로 제약을 걸 수도 있지만 이런 제약조건은 SQL 자체의 핵심 요소라기 보다는 SQL과 연동되는 프로그램의 로직 느낌이라 복잡하다. (이런 점에서도 SQL이 튜링 컴플리트 하지 않다는 것을 알 수 있다.)

따라서 지금은 이런 경우를 처리할 수 없고, 누구라도 임시로 관리자를 지정해야 한다고 생각하자.

 

 

하지만 모든 참여 제약조건을 테이블로 표현할 수 있는 것은 아니다.

 

 

그림과 같은 예시를 보자.

직원과 부서는 다대다 관계인데, 둘 다 전체참여 조건을 갖고있다.

다대다 관계는 Works_In 관계집합 자체를 테이블로 표현하여 나타낼 수 밖에 없는데

이 테이블에 모든 직원과 부서 튜플이 존재하도록 강제할 수 없다.

 

 

 

약개체 집합

 

약개체는 관계를 맺고있는 상대 엔티티에 의해 식별이 되는 엔티티이다.

따라서 약개체를 테이블로 나타낼 때는 Policy 테이블에 함께 포함하여 나타내야 한다.

SQL로는 다음과 같이 정의할 수 있다.

 

CREATE TABLE Dep_Policy (
    pname  char(20),
    age    integer,
    cost   real,
    ssn    char(11) NOT NULL,
    
    PRIMARY KEY (pname, ssn),
    FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE
);

 

 

약개체를 정의할 때는 반드시 on delete 옵션을 cascade 로 설정해야 한다.

위 다이어그램에서 부양가족은 직원이 존재할 때만 존재할 수 있는, 직원에 의해 존재하는 개체이기 때문이다.

 

 

ISA 계층 구조

 

ISA 계층 구조를 테이블로 나타낼 때는 시간제 직원과 계약직 직원의 테이블을 한 테이블로 표현하면 null 이 너무 많이 들어가게 된다.

따라서 이때는 크게 2가지 방법으로 테이블을 분리해서 구현한다.

 

첫 번째는 Employees, Hourly_Emps, Contract_Emps 3개의 테이블을 사용하여 나타내는 방법이다.

이때는 모든 직원이 Employees 테이블에 존재하고, Hourly_Emps, Contract_Emps 가 Employees 테이블에 대한 참조를 갖고 있을 것이다. 그리고 이때 삭제시에는 CASCADE 설정을 해주어야 한다.

(하지만 이렇게 구현하면 overlap constraints 를 제한하는 경우를 처리하기가 힘들 것 같다.)

 

두 번째는 Hourly Emps, Contract Emps 테이블 2개만 만드는 것이다.

이때는 Employees 테이블이 갖는 속성을 두 테이블 모두 가지게 될 것이다.

이렇게 설계하면 모든 직원이 두 테이블 중 한 테이블에는 존재해야 하므로 covering 제약조건을 지키도록 할 수 있다.

 

---

예제

 

다음 ERD에서 Policy와 Dependents 테이블을 생성하는 SQL을 작성해보자.

 

CREATE TABLE Policies (
    policy_id   char(11),
    cost        integer,
    ssn         char(11) NOT NULL,
    
    PRIMARY KEY (policy_id),
    FOREIGN KEY (ssn) REFERENCES Employees
);

CREATE TABLE Dependents (
    pname     char(20),
    age       integer,
    policy_id char(11) NOT NULL,
    
    PRIMARY KEY (policy_id, pname),
    FOREIGN KEY (policy_id) REFERENCES Policies ON DELETE CASCADE
)

 

Policies 를 먼저보면, 전체 참여 조건과 키 제약조건이 있으므로 이 테이블에 직원 테이블과의 관계를 명시할 수 있고, Not Null 을 걸어두었다. (책에서는 Policies 테이블에서 외래키 제약조건에 ON DELETE CASCADE 를 추가하였다. 이 경우 직원이 삭제되면 보험 정책과 피부양자 데이터가 함께 사라질 것이다.)

 

부양가족의 경우 약개체이므로 Policy 엔티티가 삭제될 때 같이 삭제되도록 해야한다.