SQL 뽀개기 - 7 | 모델링

1. 모델링

모델링의 목적 : 데이터 구조를 중복없이 효율적으로 디자인 하는 것. 이를위해 테이블을 여러개로 분할한다

 

- 폭포수(Waterfall) 모델

기획 -> 분석 -> 설계 -> 구현 -> 테스트 <-> 수정

 

실세계의 사물을 테이블로 정의하고 관계를 설정하는 작업 = 모델링

엔티티 정의 -> 속성 정의 -> 식별자 선정 -> 관계 설정 -> 정규화

 

# 엔티티(Entity)

모델링의 첫 단계는 데이터베이스에 저장할 대상인 엔티티를 정의하고 추출하는 것.

엔티티란 정보에 해당하는 모든 실체이며 전산화의 대상이다.

고객, 상품, 날짜같은 명사 뿐만 아니라 구매, 가입, 대여같은 추상적인 동사도 모두 엔티티에 속한다

엔티티는 보통 하나의 테이블로 구체화한다.

 

"고객은 상품을 주문하고 쇼핑몰은 주문받은 상품을 배송한다"

 

에서 고객, 상품, 주문, 배송과 같은걸 엔티티로 추출할 수 있다

 

# 엔티티 속성 정의

추출한 엔티티의 속성을 정의한다

상품 : 이름, 가격, 재고, 할인율 등..

고객 : ID, 주소, 전화번호, 마일리지 등..

 

# 기본키 선정

레코드끼리 구분 가능한 고유성이 있고 자주 쓰이는 속성을 선정한다

 

# 관계(Relation) 설정

엔티티는 독립적으로 존재하지 않고 서로 연관된다

관계는 엔티티간의 연결 형태이며, 관계형 DB의 특징을 결정하는 핵심.

관계는 양쪽 엔티티의 인스턴스 개수에 따라 1:1, 1:다, 다:다 관계로 분류하며,

반드시 연결해야하는 필수관계, 필요할때만 연결하는 선택 관계가 있다.

 

# 관계형 데이터베이스에서는 다:다 관계를 표현할 수가 없다

- 따라서 1:다 관계와 1:다 관계 두개를 이어붙여 다:다 관계로 표현한다

 

# 정규화 : 테이블을 재구성하여 논리적 구조를 개선하는 절차

 

제1 정규화

- 필드의 데이터를 원자화 한다. 

- 필드에는 단일값을 저장하고 필드끼리는 서로 독립적이어야 한다. 여러개의 값을 한 필드에 모아서 저장하거나 유사 필드가 있다면 다른 테이블로 나누는 것이 좋다.

- 양쪽 테이블에는 각자의 기본키가 있다. 이때 외부 테이블에 대한 참조를 가지는 키를 외래키(Foregin Key)라고 한다

- 외래키는 양 테이블의 연결고리 역할을 한다

- 관계를 맺는 테이블은 누가 업무의 중심인가에 따라 부모-자식 혹은 마스터-디테일 관계가 성립한다.

- 분할된 테이블에서 정보를 얻기 위해서 서브쿼리, 조인과 같은 방법을 사용한다

 

제2 정규화

- 일반 필드를 모두 기본키에 종속시킨다. 종속시킨다는 것은 기본키를 알면 나머지 정보도 알 수 있다는 뜻.

- 다만 일반 필드끼리는 서로 종속관계가 없으며, 상호 독립적이다.

 

서울이면 면적은 605이고, 경기도 지역에 있고, 광역시이다 (서울이라는 기본키에 일반필드가 종속)

경기도 지역에 있다고 광역시인것은 아니고, 면적을 알 수 없고, 인구도 알 수 없다(일반 필드간은 서로 독립적)

 

제3 정규화

- 기본키가 아닌 일반필드 끼리는 서로 종속되지 않도록 한다

 

+) 역정규화 : 테이블이 너무 많아져 처리속도가 느려지는 경우 의도적으로 데이터를 중복시켜 속도향상을 꾀하는 방법.

 

# 외래키 제약

CREATE TABLE tEmployee
(
    name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT NULL
);

CREATE TABLE tProject
(
    projectID INT PRIMARY KEY,
    employee CHAR(10) NOT NULL,
    project VARCHAR(30) NOT NULL,
    cost INT,
    CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name) -- 외래키 제약
);
----------------------------------------------------
CREATE TABLE tProject
(
	...
	employee CHAR(10) NOT NULL REFERENCES tEmployee(name) -- 컬럼제약으로 걸어도 된다
	...
)

CONSTRAINT [제약 이름] FOREIGN KEY(외래키) REFERENCES 테이블(키)

-> 외래키가 테이블의 키를 참조한다

(컬럼 제약으로 걸어도 된다)

ex) employee CHAR(10) NOT NULL REFERENCES tEmployee(employee)

 

- 외래키 제약은 참조 무결성을 지키는 강력한 수단. 

-> 무결성을 해치는 모든 명령을 원천적으로 차단한다

ex) 직원을 삭제하려면 이 직원이 담당하는 프로젝트를 먼저 삭제해야 한다

 

# 연계 참조 무결성 제약

- 일반 외래키 제약은 너무 번거로워.

- 부모 테이블을 변경하면 자식 테이블까지 자동으로 수정될 수 있도록 하는 제약

 

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } -- 오라클 지원 X

 

NO ACTION : 아무것도 하지 않고 실패하도록 내버려 둔다. 기본값

CASCADE : 수정 또는 삭제되면 자식테이블의 데이터도 동시에 수정

SET NULL, SET DEFAULT : 외래키를 NULL이나 기본값으로 변경.

-> 외래키가 NULL 허용이거나 기본값이 미리 설정되있어야 함

 

CREATE TABLE tEmployee -- 부모 테이블
(
    ....
    name CHAR(10) PRIMARY KEY,
    ...
)

CREATE TABLE tProject -- 자식 테이블
(
    ....
    employee ...,
    CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
    ON DELETE CASCADE
)

 

룩업 테이블 : 자주 바뀌지 않고 읽기만 하는 테이블