[Database] 데이터베이스 설계
목차
데이터베이스 설계 정의
데이터베이스 설계 단계
개념적 설계
논리적 설계 : E-R to Relational
논리적 설계 : 제약 조건
데이터베이스 설계 정의
사용자의 요구사향으로부터 현실세계를 반영한 데이터베이스 구조를 도출해내는 과정
어떤한 필드로 구성된 테이블을 어떠한 물리적 형태의 데이터베이스로 구성할 것인가를 결정
데이터베이스 설계 단계
데이터베이스 생명 주기 (Database Life Cycle)
요구조건 분석
설계
구현
운영
감시 및 개선
요구사항 분석
- DB 사용환경 분석 후 대상 및 제한 조건 도출
개념적 설계 (Conceptual Schema)
- 분석 결과를 추상화된 표현 방식으로 기술
- DBMS에 독립적 (어떤 DBMS인지 상관 X)
- 개념적 스키마 생성
- ex) E-R 다이어그램
논리적 설계 (Logical Schema)
- 목표 DBMS 구조에 맞는 스키마 생성
- 논리적 스키마 생성
- ex) Relation(Table)
물리적 설계 (Physical Schema)
- 목표 DBMS에 맞게 실제 컴퓨터에 저장되는 방식 설계
- 물리적 스키마 생성
구현
- 목표 DBMS의 SQL(DDL)로 데이터베이스 생성, 트랜잭션 작성
데이터베이스 설계 시 고려사항
충실성 (Faithfulness)
- 필요로 하는 모든 데이터를 표현
단순성 (Simplicity)
- 단순하고 이해하기 쉬운 구조로 표현
중복의 최소화 (Rebundancy Minimization)
- 저장공간의 효율적 사용, 데이터 일관성 유지
제약조건의 표현 (Constraints Representation)
- 데이터가 갖추어야 할 조건을 표현
개념적 설계
개념적 설계 시 고려사항
- What are the entities and relationships in the enterprise?
- What information about these entities and relationships should we store in the database?
- What are the integrity constraints or business rules that hold?
E-R Model is used at conceptual design stage
- A database schema in the E-R Model can be represented pictorially.
- Can map an E-R diagram into a relational schema
Design Choices
- Should a concept be modeled as an entity or an attribute?
- Should a concept be modeled as an entity or a relationship?
- Identifying relationships: binary or n-ary?
Entity vs Attribute
Q. Should address be an attribute of Employees or an entity?
A. Depends upon the use we want to make of address information, and the semantics of the data.
→ 사용하는 의미에 따라 주소를 속성으로도, 개체로도 볼 수 있다!
If we have several addresses per employee?
→ Address must be an entity
Since attributes cannot be set-valued
If the structure (city, street, etc.) is important?
→ Address must be an entity
Since attributes are atomic value
논리적 설계 : E-R to Relational
Entity sets to Relations
Relationship sets to Relations
in translating a relationship set to a relation, attributes of the relation must include
- keys for each participating entity set (as foreign keys)
- All descriptive attributes
1:1
4가지 방법으로 설계 가능
방법1
- E1의 기본키를 E2의 외래키로 추가
방법2
- E2의 기본 키를 E1의 외래키로 추가
방법3
- 관계를 별도의 릴레이션으로 만드는 것
방법4
- 하나의 릴레이션으로 만드는 것
Q. 방법 1 vs 방법 2
사원 - 부서장 - 부서 (1:1)
A.
사원과 부서 사이에 부서장이라는 1:1 관계에서 부서가 전체 참여
방법2
(부서의 기본 키를 사원의 외래키로 추가)
-> 사원 중에는 부서장이 아닌 사람이 더 많으므로 대부분의 did 가 null 값을 가짐 → 낭비!!
방법1
(사원의 기본 키를 부서의 외래키로 추가)
-> 모든 부서에 부서장이 있으므로 ssn은 Null 값을 가질 수 없음
결론
방법 1이 더 좋음!
1:1 관계에서
한 쪽은 전체 참여, 다른 쪽은 부분 참여 할 경우,
전체 참여하는 릴레이션에 부분 참여하는 릴레이션의 기본 키를 외래키로 포함하는 것이 효율적
방법3
- 관계를 별도의 릴레이션으로 만드는 것
- 필요 이상으로 릴레이션 수를 늘려서 조인으로 인한 연산의 양이 많아지므로 비효율적
방법4
- 하나의 릴레이션으로 만드는 것
- E1 과 E2 모두가 전체 참여일 때 가장 효율적
- ex) 남자 - 혼인 -여자
1:M
3가지 방법으로 설계 가능
방법1
- 관계가 M인 방향의 릴레이션에 관계가 1인 방향 릴레이션의 기본 키를 외래 키로 추가
방법2
- 관계가 1인 방향의 릴레이션에 관계가 M인 방향 릴레이션의 기본 키를 외래 키로 추가
방법3
- 별도의 릴레이션을 만드는 것
Q. 3가지 방법 중 가장 효율적인 방법은?
교수 - 지도 - 학생 (1:M)
A.
방법 2
- 교수 릴레이션에 학생의 기본 키를 외래키로 추가
→ 지도하는 학생의 수 (M) 만큼 교수의 정보가 중복됨 (한 명의 교수가 여러명의 학생 지도하므로)
방법3
- 릴레이션 수를 늘려서 조인이 많아져 비효율적
방법1
- 학생 릴레이션에 교수의 기본 키를 외래키로 추가
결론
관계가 M인 방향의 릴레이션에 관계가 1인 방향 릴레이션의 기본 키를 외래 키로 추가하는
방법 1이 가장 효율적
N:M
ex) 고객 - 주문 - 상품 (N:M 관계)
- 고객이 여러 상품 주문할 수 있는데
- 그 상품도 여러 고객이 주문할 수 있음
1:M 에서 관계가 1 쪽인 릴레이션에 관계가 M 쪽인 릴레이션의 기본 키를 외래 키로 두면 많은 중복 발생
(교수 쪽에 학생 기본키 추가하는 경우)
→ N:M 인 경우 양방향으로 1:M이므로
관계를 별도의 릴레이션으로 설계하는 것이 가장 효율적
Composite Attribute to Relations
- 복합 속성인 주소에 포함된 우편번호, 기본주소, 상세주소를 릴레이션의 속성으로 설계
Self Relationships to Relations
Multivalued Attribute to Relations
부하직원 : 다중 값 속성 (E-R 다이어그램에서 동그라미 두 개)
→ 릴레이션의 특성을 위배 (’원자값을 갖는다’ 위배)
Q. 중복을 최소화 할 수 있는 방법은?
→ 두 개의 릴레이션으로 분리하여 설계
(관리자 사원 + 사원 부하직원)
사원과 부하직원의 관계를 검색하는 것은 두 릴레이션의 공통속성인 사원번호를 통해 조인하면 됨
→ 두 개의 릴레이션으로 나누는 경우 조인을 해야한다는 단점이 있음
BUT,
사원에 대한 모든 정보의 중복보다는 사원 번호만 중복되는 것이 효율적
조인 검색 연산의 부하 vs 속성의 중복 중 더 효율적인 방법을 선택한 것이
조인 검색 연산의 부하이다,,,
논리적 설계 : 제약 조건
Key Constraints to Relations
각 부서마다 고유한 한 명의 관리자가 있기 때문에 관리와 부서를 결합해 Dept_Mgr 테이블 생성
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
since DATE,
ssn CHAR(11),
PRIMARY KEY(did),
FOREIGN KEY(ssn) REFERENCES Employees(ssn)
);
외래 키를 지정하지 않으면 해당 회사에 다니지 않는 사원이 부서장이 될 수 있음
Participation Constraints
굵은 선 or 이중선
→ 전체 참여 제약 조건 (모든 부서에 반드시 부서장이 있다는 것)
모든 부서에 부서장이 있다는 것
= 모든 테이블에 ssn 값이 있어야 한다는 것
전체 참여 제약 조건
→ SQL에서 “NOT NULL” 로 표현
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
since DATE,
ssn CHAR(11) NOT NULL, #전체 참여 제약 조건
PRIMARY KEY(did),
FOREIGN KEY(ssn) REFERENCES Employees(ssn)
);
Weak Entities
- 약한 개체의 기본 키 : {오너 개체의 기본 키, 약한 개체의 부분 키}
- 전체 참여 제약 조건은 굵은 선으로 표현
- Weak entity set must have total participation in this identifying relationship set
Weak Entities to Relations
- 오너 개체 별도 설계
- 약한 개체와 관계를 합쳐 설계하고 + 오너 개체의 기본 키를 외래 키로 추가
CREATE TABLE Dep_Policy(
pname CHAR(20),
age INTEGER,
sex INTEGER, //약한 개체의 속성
ssn CHAR(11), //오너 개체의 기본 키
PRIMARY KEY(pname,ssn), //오너 개체의 기본 키 + 약한 개체의 부분 키
FOREIGN KEY(ssn) REFERENCES Employees(ssn)
);
Q. 오너 개체가 삭제될 때 약한 개체도 삭제되게 하려면?
A. ON DELETE CASCADE 추가
CREATE TABLE Dep_Policy(
pname CHAR(20),
age INTEGER,
sex INTEGER, //약한 개체의 속성
ssn CHAR(11), //오너 개체의 기본 키
PRIMARY KEY(pname,ssn), //오너 개체의 기본 키 + 약한 개체의 부분 키
FOREIGN KEY(ssn) REFERENCES Employees(ssn)
ON DELETE CASCADE
);
IS-A Hierarchies
C++ 또는 다른 객체지향 프로그래밍 언어처럼 속성이 상속됨
- 서브 클래스에 슈퍼 클래스의 특정 속성을 추가하기 위해 IS-A Relationship 을 사용
- 시간제 사원과 계약직 사원은 슈퍼 클래스인 사원의 속성 (ssn, name, lot) 을 상속받음
IS-A Hierarchies to Relations
방법 1
사원, 시간제 사원, 계약직 사원 3개의 릴레이션으로 만드는 방법
방법 2
시간제 사원 + 사원, 계약직 사원 + 사원 2개의 릴레이션으로 만드는 방법
방법 1
- 상위 클래스의 기본 키를 외래 키가 아닌 기본 키로 추가
- ssn이 시간제 사원을 유일하게 구별하는 속성이기 때문
IS-A Hierarchies를 3개의 릴레이션으로 설계할 때 서브 클래스에 반드시 상위 클래스의 기본 키를 추가
-> 시간제 사원과 사원 릴레이션의 조인 속성
Q. 시간제 사원, 계약직 사원 릴레이션이 참조하는 사원 릴레이션의 사원 A가 삭제되면?
A. 시간제 사원, 계약직 사원 릴레이션의 사원 A도 연쇄 삭제 (cascade)
장점 : 최소화된 중복성
단점 : 시간제 사원 ,계약직 사원의 name이나 lot을 검색하기 위해 사원 릴레이션과 조인 필요
방법 2
- 사원의 속성 3가지를 두 릴레이션에 중복해서 포함함
모든 사원은 두 개의 서브 클래스 중 하나에 속해야 함
장점 : 조인 불필요
단점 : 사원의 많은 속성이 중복됨