데이터베이스 설계 단계
데이터베이스 설계는 사용자의 요구사항을 고려하여 데이터베이스를 생성하는 과정으로, 데이터베이스 설계 과정의 각 단계에서 수행하는 주요 작업은 다음과 같다.
- 요구사항 분석: 실제 업무를 처리하는 사용자에게서 필요한 데이터의 종류와 처리 방법과 같은 다양한 요구 사항을 수집한다. 그 후, 수집한 요구사항을 분석하여 그 결과를 요구사항 명세서로 작성한다.
- 개념적 설계: 요구사항 명세서를 바탕으로, 요구사항을 개념적 데이터 모델을 이용하여 표현한다. E-R 모델과 같은 개념적 데이터 모델은 DBMS의 종류에 독립적이면서 중요한 데이터 요소 간의 관계를 표현할 때 사용한다.
- 논리적 설계: 개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용하여 개념적 설계 단계에서 생성한 개념적 구조를 기반으로 논리적 구조를 설계한다. DBMS의 종류에 따라 다양한 논리적 데이터 모델로 사용할 수 있다.
- 물리적 설계: 논리적 설계 단계에서 생성된 논리적 구조를 기반으로 물리적 구조를 설계한다. 이때, 물리적 구조란 데이터베이스를 저장 정치에 실제로 저장하기 위한 내부 저장 구조와 접근 경로 등을 말한다.
- 구현: 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성한다.
요구사항 분석
요구사항 분석 단계에서는 사용자들이 데이터베이스에 원하는 것이 무엇인지 분석한다. 개발한 데이터베이스의 목적을 파악하고, 분석한 사용자 요구사항의 내용을 명세서로 작성한다. 이때 작성한 요구사항 명세서는 설계 단계의 기반이 된다.
요구사항 분석 단계에서는 데이터베이스를 사용할 주요 사용자의 범위부터 결정하고, 범위를 결정하여 불필요한 요구사항을 수집하지 않도록 한다. 사용자의 범위가 결정되면 해당 사용자가 조직에서 수행하는 업무를 분석한다. 이 분석 과정을 통해 필요한 데이터가 무엇인지 파악하고 데이터 처리 방식을 고려하여 요구사항들을 수집하고 분석한다.
요구사항 분석 단계는 사용자가 요구하는 데이터베이스 용도를 결정하는 단계이기에 데이터베이스를 잘못 설계하여 재개발하는 일이 없도록 신중하고 꼼꼼하게 작업이 수행되어야 한다.
개념적 설계 (Conceptual Design)
개념적 설계에서는 요구사항 분석 단계의 결과물을 개념적 데이터 모델을 이용하여 표현한다. 개념적 데이터 모델은 주로 E-R 모델을 사용한다. 즉, 요구사항 분석 결과를 기반으로 현실 세계의 중요한 데이터 요소인 개체를 추출하고 개체 간의 관계를 결정하여 E-R 다이어그램으로 표현한다.
요구사항을 개념적 데이터 모델로 변환하는 작업을 개념적 모델링이라 하며, E-R 다이어그램과 같이 개념적 데이터 모델로 표현된 개념적 설계물을 개념적 스키마라고 한다. E-R 모델의 핵심 요소인 개체를 추출하고, 주요 속성과 키 속성을 선별하고, 개체 간의 관계를 결정해야 한다.
개체와 속성 추출
개념적 설계 단계에서 기본 작업은 요구 사항 분석 단계의 결과물에서 개체를 추출하는 일이다. 이때, 개체란 저장할 만한 가치가 있는 중요 데이터를 지닌 구별되는 모든 것을 의미하며, 개념적 모델링을 함에 있어 가장 중요한 요소이다. 개체부터 결정해야 속성과 관계도 결정할 수 있다. 개체를 추출하는 방법은 다음과 같다.
- 제시된 요구사항의 문장들에서 명사를 찾는다.
- 업무 처리와 관련이 깊고 의미 있는 명사를 찾는다.
- 찾아낸 명사를 “개체”와 개체가 가진 고유한 특성인 “속성”으로 정확히 분류한다.
관계 추출
개체와 속성을 추출한 이후에는 개체 간 관계를 결정할 수 있다. 관계란 개체 간의 의미 있는 연관성으로 주로 동사로 표현된다.
- 제시된 요구사항의 문장들에서 동사를 찾는다.
- 개체 간의 연관성을 의미 있게 표현한 동사를 찾는다.
관계를 추출한 후에는, 추출한 관계에 대한 매핑 카디널리티와 참여 특성을 결정한다. 이때, 매핑 카디널리티란 각 개체 인스턴스가 관계를 맺는 상대 개체의 개체 인스턴스 개수를 말한다. 매핑 카디널리티를 기준으로 추출한 관계가 일대일(1:1), 일대다(1:N), 다대다(N:M) 중 하나로 분류한다. 그리고 개체가 관계에 필수적으로 참여하는지, 선택적으로 참여하는지를 의미하는 참여 특성을 결정한다.
E-R 다이어그램 작성
데이터베이스에 대한 요구사항 명세서에서 개체, 속성, 관계를 모두 추출하고 나면, 이를 하나의 E-R 다이어그램으로 표현한다. E-R 다이어그램은 데이터베이스에 대한 요구사항 명세서를 개념적으로 모델링하여 표현한 것이다.
논리적 설계 (Logical Design)
논리적 설계에서는 DBMS에 적합한 논리적 데이터 모델을 이용하여, 개념적 스키마를 기반으로 논리적 스키마를 설계한다. DBMS에 독립적인 개념적 스키마를 기반으로 DBMS가 처리 가능한 데이터베이스의 논리적 구조를 설계하는 단계이다. 논리적 설계 단계에서는 주로 관계 데이터 모델을 사용하며, E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마로 변환한다. 이러한 작업을 논리적 모델링이라고 하며, 생성되는 릴레이션 스키마를 논리적 스키마라고 한다.
E-R 모델에서는 개체와 관계를 구분하지만, 관계 데이터 모델에서는 개체와 관계를 구분하지 않고 릴레이션으로 표현한다. 또한 E-R 모델에서는 다중 값 속성이나 복합 속성의 표현을 허용하나, 관계 데이터 모델에서는 허용하지 않는다. 이로 인하여 E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마로 변환하는 과정에서 고려해야 할 사안이 많다.
또한 변환된 릴레이션 스키마에 대해 속성의 데이터 타입, 길이, Null 값 허용 어부, 기본값, 제약조건 등도 논리적 설계 단계에서 결정된다. 릴레이션 스키마에 대한 세부 사항들이 결정되면 결정된 내용을 문서화하는 것이 권장된다.
릴레이션 스키마 변환 규칙
E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마로 변환할 때, 다섯 가지 규칙을 순서대로 적용하면 쉽게 변환할 수 있다. 변환 규칙을 순서대로 적용하면서 해당되지 않는 규칙은 제외하고 넘어가면서 변환 작업을 수행한다. E-R 다이어그램을 릴레이션 스키마로 변환할 때 적용되는 다섯 가지 규칙은 다음과 같다.
규칙 1: 모든 개체는 릴레이션으로 변환한다.
E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환한다. 이때, 개체의 이름은 릴레이션의 이름으로 변환하고 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환하면 된다. 만약 개체가 가진 속성이 복합 속성이면 복합 속성을 구성하는 단순 속성만 릴레이션의 속성으로 변환한다. 개체의 키 속성은 릴레이션의 기본키로 변환한다.
규칙 2: 다대다(N:M) 관계는 릴레이션으로 변환한다.
E-R 다이어그램에 있는 다대다(N:M) 관계를 하나의 릴레이션으로 변환한다. 관계의 이름은 릴레이션의 이름으로, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다.
다만, 관계를 맺고 있는 개체들을 먼저 규칙 1에 따라 변환하고 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 저장한다. 개체를 변환한 릴레이션의 기본키를 외래키로 지정할 때, 가져온 기본키들의 이름이 같은 경우에는 하나의 이름을 변경해야 한다.
규칙 3: 일대다(1:N) 관계는 외래키로 표현한다.
E-R 다이어그램에 있는 일대다(1:N) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다. 약한 개체가 참여하는 일대다(1:N) 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다. 약한 개체가 참여하는 일대다(1:N) 관계에서는 외래키가 포함된 릴레이션에서 외래키를 포함하여 기본키를 지정해야 한다. 약한 개체는 강한 개체에 따라 존재 여부가 결정되기에 강한 개체의 기본키를 이용하여 식별한다.
규칙 4: 일대일(1:1) 관계도 외래키로 표현한다.
E-R 다이어그램에 있는 일대일(1:1) 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다. 데이터의 중복을 피하기 위해서 다음 세부 규칙이 정의된다.
- 일반적인 일대일 관계는 외래키를 주고 받는다.
- 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.
- 모든 개체가 일대일 관계에 필수적으로 참여하면 하나의 릴레이션으로 합친다.
규칙 5: 다중 값 속성은 릴레이션으로 변환한다.
관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않기에, 다중 값 속성은 그 속성을 가지는 개체에 해당하는 릴레이션이 아닌 별도의 릴레이션을 만들어 포함시킨다. 이때, 새롭게 만들어진 릴레이션의 이름은 자유롭게 정하고 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.
물리적 설계와 구현 (Physical Design & Implement)
논리적 설계 단계에서 릴레이션 스키마의 설계를 완료하면, 물리적 설계 단계에서는 인덱스 구조, 내부 저장 구조, 접근 경로 등 하드웨어나 운영체제의 특성을 구려한 물리적 구조를 설계한다. 그 후, DBMS를 이용하여 SQL 쿼리를 작성하고 실행시키고 데이터베이스를 실제로 생성하면 데이터베이스 개발 작업이 끝난다.
데이터베이스에서 가장 흔하게 쓰이는 예시 중 하나인 '직원(Employees)' 정보를 저장하는 테이블(릴레이션)을 생성하는 SQL문은 다음과 같다.
CREATE TABLE Employees (
employee_id INT PRIMARY KEY, -- 직원 고유 번호 (기본키)
first_name VARCHAR(50) NOT NULL, -- 이름
last_name VARCHAR(50) NOT NULL, -- 성
email VARCHAR(100) UNIQUE, -- 이메일 (중복 불가)
hire_date DATE DEFAULT CURRENT_DATE, -- 입사일 (기본값은 오늘 날짜)
salary DECIMAL(10, 2) -- 급여 (총 10자리, 소수점 2자리)
);
