SQL13일차[논리모델링][정규화]

면접 질문 

정규화 : 이상현상 제거
삽입 , 삭제, 수정, 문제 이상현상

1NF : 원자(중복성) 제거
2NF : 부분함수 종속제거
3NF : 이행함수 종속제거

 

답변: 간결하게 더이상추가질문 못하게 물어본것만 대답 추가질문하면 그거에 대해서 답변

 

 

정규화를 왜 해야할까?

정규화를 안하게 되면 insert delete update에 대해서 불편함이 발생한다.

insert 할때 불필요한 값까지 넣어 줘야 한다. delete할때는 만약 하나의 과목을 한명의 수강생만 수강하고 있다면 수강생을 삭제하면 과목도 사라지게 된다 update 할때는 학생한건에 대해서만 수정하면 되는 부분을 수강하고 있는 과목까지 포함해서 바꿔야하니까 300건씩 수정해야한다

 

정규화 안한거의 장점

조회시 조인을 안하기 때문에 모든 컬럼을 가져올때 속도가 빠르다

정규화의 단점

조인을 통해서 검색해야하기 때문에 어느정도 성능 저하를 감수 해야한다.

 

정규화 QUIZ_14

 

아래의 테이블을 정규화 하시오

위의 데이터가 삽입된 형태를 보니 회원번호 이름 주소 핸드폰번호 주민번호가 회원의 자격증 정보를 추가하기 위해서 반복되고 있다. 이를 분리하기 위해 회원 테이블과 자격증 테이블로 나눠준다. 

 

비식별로 들어갈 경우 자격증 하나는 한명의 회원만 딸 수있게되므로 식별로 들어가야한다.

 

제 2정규화

기본키가 두개일 경우 제 2정규화를 통해 부분함수적종속을 제거할 생각을 해야한다. 자격증 번호에 자격증이 종속되므로 자격증테이블로 분리해준 다음에 회원이 딴 자격증을 테이블로 관리해서 회원이 여러개의 자격증을 따고 자격증은 여러명의 회원이 취득할 수있는 N:M관계를 해소해준다.

 

 

 

 

프로젝트할때는 이미 비슷하게 해놓은 DB 설계를 참고해서 살짝씩만 수정해서 사용하자!

DB를 수정하면 자바코드도 다 수정해야하기때문에 한번 해놓을 때 잘 해 놓는게 낫다

 

 

Ex15_QUiz

 

과정마다 정해진 교재가 여러개이므로 중복되는 교재 컬럼을 테이블을 따로 빼서 관리한다.

과정코드가 식별관계로 들어가야지 비식별관계로 들어가면 과정하나에 교재하나만 사용해야한다

아래와 같이 과정과 교재의 N:M관계를 해소하기 위해서 관계 테이블을 생성하면 조인을 통해서 검색을 해야한다 만약 자주 조인해서 검색하는 컬럼이 과정코드와 교재번호 과정명, 교재 라면 강의 교재 테이블에 교재와 과정명을 추가해주는 것이 검색속도 향상에 좋다. 하지만 나중에 관리 차원에서 데이터를 추가,삽입,삭제 할때 무결성이 깨질 수있음에 주의해야한다

 

 

Ex16_QUiz

 

 

 

주문에 상품이하나 추가되면 무조건 결제를 하고 넘어가야한다

하나의 주문만 하는게 가능하다

상품과 주문의 N:M관계를 해소하고 회원의 정보가 중복되어 들어가는 부분을 정규화하기 위해서 상품테이블과 주문상세, 주문,회원테이블을 만든다

주문상세는 주문테이블과 상품테이블의 N:M관계를 해소하기 위한 테이블로 이는 회원이 주문을 하기전 어떤 항목을 몇개 담았는지 확인 할 수있는 테이블이 된다.

그리고 주문을 하게 되면 주문테이블에 주문 ID와 함께 주문총금액과 날짜 , 받을 회원정보를 넣어 회원이 배달을 받아볼수 있도록한다. 주문에 대한 상품항목에대한 정보는 주문상세 테이블과 조인해서 알 수있다. 주문테이블의 주문총금액과 날짜는 이걸 실무에서 쇼핑몰 웹페이지로 쓴다는 가정하에서 추가해준 컬럼이다.

 

 

데이터베이스 성능 모델링 하기

1. 정규화를 통해서 

2. 반정규화를 통해서

3. 테이블 통합 및 분할을 통해서

4. 데이터 조인을 통해서

5. PK, FK 설정을 통해서

--------------------------------------------------------------------------------------------------

정규화(주요 관심사별로 테이블을 구성해라) : 고객, 상품

사적) OOP - > 작은 부품들을 만들고 그 부품들을 조립해서 만드는것

AOP (관점지향프로그래밍) .. 주관심, 보조관심(공통관심) 

 

계산기(주관심 >> 연산 +, - ) >> 시간이 얼마나(보조관심,,,공통관심) >> 분리

DB설계 > 관심사 별로 테이블을 분리한다 (관점을 분산한다.)

---------------------------------------------------------------------------------------------------

DB용량 산정

(실시간 데이터 변경, 수정, 삭제 >> 트랜잭션을 일으키는 작업)

프로그래머가 세면 데이터 저장소 늘리는거고 DBA가 세면 프로그래머가 코드 수정하는거임

(일정한 주기를 가지고 데이터 조회)

자주 조회되는 컬럼들을 합쳐 테이블을 놓는다.

------------------------------------------------------------------------------------------------------

정규화

1. 데이터중복감소

2. 데이터가 관심사 별로 관리된다

3. 조인질의(정규화를 하면 할수록 정능이저하된다.)

 

정규화 수행후 조회 성능은 처리 조건에 따라 달라질 수있다. -> 관심사별로 테이블을 조회하면 성능향상을 불러 일으킨다.

관심사별로 나눠놓은 테이블 각각을 조인을 안하고 조회를 하면 성능이 향상된다.

                                                                                               -> 반대로 나누어진 정보를 모아서 조회하면 성능이 저하된다.

 

그대신 정규화를 하면 입력, 수정, 삭제성능은 향상된다.

-------------------------------------------------------------------------------------------------------------------------------------------------------------

 

반정규화

 

중복컬럼 추가

사원정보를 조회할 때 항상 지점명도 같이 조회한다면 지점명을 사원정보에 추가 할 것을 고민해봐야한다.

그래서 테이블에 중복컬럼으로 지점명을 추가해줬다. 이제는 join을 하지 않고도 지점명을 찾을 수 있어 조회성능은 좋아졌지만 업무지점 테이블에서 지점명이 수정된다면 사원정보에 있는 지점명도 수정해서 무결성을 지켜줘하는 관리의 불편함이 생겼다. 근데 이렇게 나누면 제 3 정규화 대상이 된다. 기본키는 하나이고 기본키가 아닌것에 종속되는 지점명이 있기때문이다.

파생컬럼추가

 

주문 테이블에서 주문한 총금액을 주문제품목록과의 조인을 통해서 제품테이블의 단가를 가지고 계산해서 합쳐 가져올수도 있겠지만 자주 join해서 검색해 오는 값이라면 차라리 속성을 하나 둬서 관리하는 편이 낫다.

주문 총금액 컬럼 추가를 통해 조회성능은 높였다. 주문총금액은 수량과 단가가 달라질때마다 업데이트 되도록 계산된 컬럼이나 트리거를 사용해야한다.

DB는 누적합은 구하지 못한다. 만약에 누적합을 구해놨는데 앞쪽의 튜플 삭제로 그 공사에 대한 공사비도 없어졋다면 공사비 누적은 다시 계산되어야한다. 하지만 DB는 합계를 다시 구하는 기능이 없다 그래서 무결성문제가 해결되지 않기 때문에 누적합은 불가능하다.

 

 

 

이력테이블 추가

직원의 차량을 관리하는테이블에 직원의 번호를 1:N로 연결하면 차량번호별로 직원번호가 중복해서들어갈수있다. 이러면 직원차량 테이블은 직원의 이전 차량까지 전부 기록하고 관리할 수있다. 하지만 이 회사에서는 입출차 차량은 1대만 할수있고 최근에 등록한 차량이어야 한다고 한다. 이때 챠랑의 등록일자가 있으므로 직원번호로 묶고 차량의 등록일자 순으로 정렬하고 rownum을 매겨서 현재 입출차하려는 차량이 1번의 차량과 일치하는지 확인하면된다. 하지만 이러면 매번 입출차 할때마다 정렬하고 rownum매기고 맨처음의 row를 갖고 오는 과정을 반복하게 된다.

이를 해결하기 위해서 컬럼을 추가한다 최근등록여부라는 컬럼을 통해 새로운 차량이 insert 되면 기존의 차량은 N으로 초기화되고 새로 추가된 차량은 Y로 초기화 되도록 update한다. 그러면 update하는 과정이 매번 차량을  insert할때마다 필요하다는 단점이 있지만 장점은 입출차할때 where절에서 최근등록여부가 Y값을 가지는 컬럼을 가져와서 최근차량임을 인식하는 조회를 할 수있으므로 조회성능은 향상된다.