Relational Database Design(1) - anomaly(이상 현상)

  이번 시간에는 DB schema 설계를 잘못하면 어떤 문제가 발생할 수 있는지 배워 보겠습니다. 그리고 DB 설계의 중요성에 대해서 알아보겠습니다. 먼저 이상 현상(anomaly)이 무엇인지 보면, 불필요한 데이터 중복으로 인해 테이블에 대한 데이터 삽입, 수정, 삭제 연산을 수행할 때 발생할 수 있는 부작용을 의미합니다. 그래서 이상 현상을 제거해 나가면서 데이터베이스를 올바르게 구축 혹은 설계해 나가는 과정을 정규화 과정이라고 합니다. 

1. 중복 데이터 문제

Insertion anomalies

위 학생수강 테이블에서 튜플마다 강좌 이름과 강의실이 중복된 데이터가 있는 것을 확인할 수 있습니다. 이는 학생 정보와 강의 정보가 한 테이블에 있기 때문입니다. 이를 해결하기 위해서 테이블을 분리 시켜줘야합니다. 

예를들어, IT 회사의 데이터베이스를 구축하는데 다음과 같이 테이블을 만들었다고 가정해 보겠습니다. 이렇게 임직원의 정보와 부서정보를 한 스키마를 설계했습니다. 이때 데이터를 write(insert, update, delete) 하게 되면 어떤 문제가 발생할 수 있을까요?

출처: 쉬운코드

이렇게 중복된 데이터가 발생하면 다음과 같은 문제가 발생합니다. 데이터 중복을 줄여주는 것이 중요하다.

• 저장 공간 낭비
• 실수로 인한 데이터 불일치 가능성 존재 (ex, DEV -> DEB 등)

위 테이블에서 아직 부서 배치를 받지 못한 임직원을 추가(insertion)하게 되면? NULL 값을 입력해야하는 현상 발생!

출처: 쉬운코드

? 에는 어떤 값 을 넣어줘야할까요? 우선은 null 값을 많이 쓰게 됩니다. 하지만 null 값은 적게 쓰는게 좋다.

• 할 수 있는 한 null 값은 적게 쓰는 것이 좋음 -> 불필요한 storage 발생

출처: 쉬운코드

또, 임직원이 한명도 없는 부서 정보를 입력하고 싶은 경우는?

출처: 쉬운코드

하지만 임직원 정보는 아까 처럼 null로 넣어줘야한다. 하지만 empl_id는 primary key이기 때문에 null 값을 넣어야합니다. 그래서 QA 부서 저장 용 row를 만들어줘야한다. 그러면 매끄럽지도 않고 null  값도 많이쓰게 됩니다. 

출처: 쉬운코드

그런데 여기서 QA 부서에 새로운 임직원이 들어왔다고 가정하면?

출처: 쉬운코드

그래서 새로운 임직원이 들어왔기 때문에 임시로 저장해 두었던 QA 부서 저장용 row를 삭제해 줘야합니다. 이는 번거로운 작업으로 이어집니다. 그래서 테이블 설계를 잘못하면 이렇게 번거로운 작업을 하게 됩니다. 그러면 테이블 스키마를 왜 잘못 설계한 것일까요? 그 이유는 바로 

• 별개의 관심사가 한 테이블에 있는 때문, 즉 EMPLOYEE와 DEPARTMENT 관심사게 한 곳에 있기 때문입니다.
• 그래서 테이블을 설계할 때 관심사를 분리하는 것이 좋다.

출처: 쉬운코드

이렇게 분리를 하게 되면, null 값도 줄어들고 중복된 데이터가 줄어들게 되어 간결하고 안전하게 데이터를 관리할 수 있습니다.

Deletion anomalies

출처: 쉬운코드

나는 유진에 대한 정보를 지우고 싶은데 유진이 속한 부서정보까지 사라지게 됩니다. 즉, 연쇄 삭제 발생!

출처: 쉬운코드

그러면 또 다른 방법으로 부서정보는 남기고 유진의 정보를 null 값으로 바꾸면 되지 않을까? 하는 의문이 생길 수 있는데 그러게 되면 null 값을 많이 사용하므로 매끄럽지도 않습니다.

출처: 쉬운코드

그래서 아래와 같이 테이블을 분리시키면 다음과 같이 해결할 수 있습니다.

출처: 쉬운코드

Update anomalies

출처: 쉬운코드

예를들어, 최근에 DEV 부서 이름이 DEV1로 변경됐는데 JINHO만 업데이탁 됐다고 해봅시다. 

출처: 쉬운코드

이렇게 되면 데이터 불일치가 발생합니다.

출처: 쉬운코드

  그래서 이렇게 관심사를 분리시켜 테이블을 설계하면 데이터 불일치 현상을 막을 수 있습니다. 따라서 지금까지 insertion, deletion, update anomalies 에 대해서 살펴보았습니다. 결국 테이블 설계를 잘못하면 이렇게 이상 현상이 발생하기 때문에 테이블 설계의 중요성을 알 수 있었습니다.

Spurious Tuples

테이블 설계를 잘못했을 때 발생할 수 있는 문제점 중에 또 하나가 있습니다.

출처: 쉬운코드

이제 두 개의 테이블을 natural join을 해보겠습니다. natural join은 서로 다른 두개의 테이블에서 똑같은 이름의 attribute를 갖고 있다면 그 속성을 기준으로 equality join을 하게 됩니다. 

출처: 쉬운코드

이렇게 쿼리문을 실행하게 되면 다음과 같습니다.

출처: 쉬운코드

이는 이상한 결과입니다. 왜냐하면 primary key 는 튜플을 unique하게 식별할 수 있는 key인데 dept_id가 같으면 dept_name도 같아야 하는데 지금 아래를 살펴보면 다른 값을 지니고 있기 때문입니다. 

출처: 쉬운코드

이러한 현상은 다른 튜플에서도 발생합니다. 

출처: 쉬운코드

그러면 이러한 현상을 왜 발생한 것일까요?

출처: 쉬운코드

출처: 쉬운코드

이렇게 join을 하면서 가짜 튜플이 발생하게 됩니다. 그렇다면 join할 때 왜 가짜 정보가 발생하게 되는 것일까요? 그 이유는 테이블 설계를 잘못했기 때문입니다. 다음은 올바르게 작성한 테이블 입니다. 

출처: 쉬운코드

null 값이 많아짐으로 인한 문제점들

• null 값이 있는 column으로 join하는 경우 상황에 따라 예상과 다른 결과가 발생할 수 있습니다.
• 그리고 null 값이 있는 column에 aggregate function을 사용했을 때 주의 필요!
• 불필요한 storage 낭비

올바른 DB schema 설계

1. 의미적으로 관련있는 속성들끼리 테이블을 구성 -> 관심사 분리
2. 중복 데이터를 최대한 허용하지 않도록 설계
3. join 수행 시 가짜 데이터가 생기지 않도록 설계 -> primary key or foregin key 활용 중요성
4. null 값을 줄일 수 있는 방향으로

지금까지는 테이블을 설계할 때 올바른 설계를 위해 테이블을 분리시키는 경우가 많았는데  때로는 성능 향상을 위해서 테이블을 나누지 않는 경우도 있습니다. 그래서 무조건 적인 테이블을 나누는 것이 좋은 것만은 아닙니다. 

https://www.youtube.com/watch?v=JwfQ8ouhAzA&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=21