[DB] 데이터베이스 정규화란?

DB + SQL

[DB] 데이터베이스 정규화란?

dev_SiWoo 2021. 12. 9. 22:06

1. 정규화

정규화란 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 확보하기 위해서 데이터를 분해하는 과정을 말합니다. 이러한 정규화를 거치게되면 비즈니스(업무)에 변화가 발생하더라도 데이터 모델의 변경을 최소하 할 수 있습니다. 제5 정구화까지 존재하지만 실질적으로는 보통 제3 정규화까지만 수행하기 때문에 이 포스팅에서는 제3 정규화까지만 알아보도록 하겠습니다.

#정규화절차

정규화 절차	설명
제1 정규화	- 속성(Attibute)의 원자성을 확보한다 - 기본키를 설정한다
제2 정규화	- 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성을 제거한다
제3 정규화	- 기본키를 제외한 컬럼간의 종속성을 제거한다. - 즉, 이행 함수 종속성을 제거한다.
BCNF	- 기본키를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해한다.
제4 정규화	- 여러 칼럼들이 하나의 칼럼을 종속시키는 경우 분해하여 다중 값 속성을 제거한다
제5 정규화	- 조인에 의해서 종속성이 발생되는 경우 분해한다.

#제1정규화

: 제 1정규화는 기본키를 잡으면서 각 속성들이 원자성을 확보하는 것을 말합니다.

속성들이 원자성을 확보한다? 말이 좀 어렵죠?

간단합니다. 각 속성(열)에 대해서 하나의 셀에 하나의 값만 와야한다는 것이 바로 '원자성을 확보한다'라는 것입니다. 이렇게 원자성을 확보해준뒤 각 튜플들을 식별해줄 수 있는 유일성을 만족하는 기본키를 설정하는 것까지가 바로 제 1정규화 입니다.

예시를 확인하기 위해서 아래의 파일을 다운받아주세요. (제1 ~3 정규화에 대한 예시파일입니다.)

example_coursedata (1).xlsx

0.03MB

우리가 SQL실습 때 사용했던 실제 데이터파일이 아래 그림에서 보이는 것처럼 원래 수업명 컬럼에

수강신청여부,교수이름,메일주소,등등 여러개 값이 같이 있었다고 한다면, 이는 제 1정규화를 거치지 않은 테이블이기 때문에 제1 정규화를 통해서 밑에 보이는 것처럼 여러개 컬럼들로 나눠줄 수 있겠죠.( 그 상태가 SQL실습 때 나눠드린 상태입니다.)

#제2정규화

: 제2 정규화는 기본키가 2개이상의 컬럼들로 이루어진 경우, 부분함수 종속성을 제거하는 것이라고 나와있는데요.

위에 그림에서 보이는 것처럼 course_order(수강신청내역)이라는 테이블은 신청내역번호(order_id), 학번(Student_id), 교과번호(course_id)라는 3개의 컬럼들을 합성해서 만들어준 기본키입니다.

그리고 지금 보면 이수구분이라는 컬럼은 교과번호만 달라져도 그 값이 달라지는 것을 알 수 있죠.

만약 교과번호가 1이면 전공필수과목이고, 교과번호가 2이면 전공선택과목이니까요.

이렇듯 기본키는 3개의 속성들로 인해서 식별되는데 3개의 속성들중 한가지 값으로만 다른속성의 값이 정해지는 경우, 지금 예시에서는 order_id, student_id,course_id 이 3가지에 속성에 의해서 값이 결정되는 것이 아니라 course_id(교과번호)라는 한개의 속성에 의해서 comple_div(이수구분)가 결정되기 때문에 이를 "부분함수 종속성이 존재한다"라고 말합니다.

이러한 부분함수 종속성은 중복을 유발하기 때문에 조취를 취해서 제거해줘야하는데요

이런식으로 원래 속성이 있어야 할 곳에 테이블을 만들어주던가 별도의 테이블로 쪼개주는 것이 바람직합니다.

이렇게 되면 전공필수, 전공선택 이런 단어들이 중복되지 않는 것을 볼 수 있습니다.

결국 "제2 정규화란 2개 이상의 기본키를 가질때 기본키 전부가 바뀌어야 값이 달라지는게아니라 기본키중 1개만 바뀌어도 값이 달라지는 경우 이를 부분 함수 종속성이 존재한다고 하며 이 부분함수종속성을 제거하는 것이 제2정규화이다." 라고 말할 수 있겠습니다.

#제3정규화

제3 정규화는 제2 정규화처럼 종속성에 대한부분입니다.

제2 정규화는 복합키(기본키가 2개이상의 컬럼으로 구성된 키) 와 기본키가 아닌 속성간의

종속성문제에 의한 데이터 중복이 문제를 해결하기 위해서 제2 정규화를 실시했습니다.

그렇다면 제3 정규화란 무엇일까요?

제 3정규화란 기본키가 아닌 속성들끼리 종속성이 존재하여, 즉 이행 종속성이 존재하여 중복이 발생하는 것을 말합니다.

예시를 보면

DataAnalysis는 기본키는 아니지만 professor,department1,email,course_hour등을 종속하고 있습니다. 즉, DataAnalysis라는 과목이기 때문에 이 과목의 담당교수,담당교수소속,이메일,수업시간 값이 정해지고있고 DataAnalysis는 기본키도 아닙니다.

이렇듯 기본키가 아닌 컬럼이 다른컬럼을 종속하고 있는 경우를 이행함수 종속성이라고 합니다.

위 표를 보시면 이렇듯 이행함수의 종속성으로 인해서 쓸데 없이 professor,department,email,course_hour가 중복이 발생하고 있습니다. 따라서 이러한 이행 함수 종속성을 제거하는 것을 제3정규화라고 합니다.

방법은 간단합니다.

이는 우리가 ERD를 작성할 때, 엔터티 단위로 테이블을 쪼개주었던 방법을 통해서 해결할 수 있십니다.

이런식으로 말이죠.

결국 이렇게 제1~3 정규화를 마친 최종 상태를 확인하면 다음과 같습니다.

제1~3 정규화를 마친 최종상태

그럼 우리가 이전 포스팅에서 SQL 실습할 때, 제 2정규화를 해주는 것을 빼먹었으니

이번시간에 SQL실습과정으로 제 2정규화를 해보도록 하겠습니다.

2. SQL 실습 제 2정규화 적용하기

우선 제2 정규화를 위해서는 course_order 테이블에 있는 complie_div를 course테이블로 옮기기만 하면되는데, 이를 위해서는 course_order테이블에서 comple_div컬럼을 삭제하고 course테이블에 compe_div를 추가해야겠죠?

이렇게 테이블을 수정할 때 사용하는 구문이 ALTER 구문입니다.

-컬럼 추가 (ADD)

ALTER TABLE 테이블명 ADD COLUMN 추가할컬럼명 VARCHAR(20) NOT NULL;

-컬럼 삭제 (DROP)

ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;

위 2개 구문을 사용하면되겠습니다. 그럼 적용해볼까요?

ALTER TABLE course_oder DROP COLUMN comple_div; #컬럼삭제 ALTER TABLE course ADD COLUMN comple_div VARCHAR(30) NOT NULL; #컬럼추가

위 SQL문을 적용하면 다음과 같이 결과가 반영됩니다.

이렇게 컬럼을 추가,제거 해준다음 SELECT 구문을 통해서 확인해보면 아래 그림에서처럼 컬럼이 추가되고 삭제된 것을 확인 할 수 있죠.

자이제 comple_div 컬럼에 값을 넣어줘야 겠죠.

DataAnalysis라는 과목은 전공필수 였고, DataViz는 전공선택과목이였습니다. 그럼 해당값을 넣어줘야겠죠?

보면 일단 첫번째, 두번재 튜플은 이미 존재하지만 각 튜블에서 comple_div의 값만 없는 상태니까

UPDATE 구문을 통해서 값을 갱신해줘야합니다

INSERT로 덮어쓰기한다 뭐 그런 개념이 적용될 수 없습니다.

컬럼의 값을 변경하고 싶으시다면 무조건 UPDATE 구문을 쓰셔야합니다.

왜냐면 INSERT는 정말로 행을 추가하는 것이기 때문에 COURSE_ID는 기본키인데 중복되어서 값이 들어간다고 생각되기 때문에 INSERT문으로 덮어쓰기가 가능하다고 착각하시면 안됩니다. (제가그랬어요 ㅎ;)

그럼 UPDATE문을 통해서 값을 수정해주도록 하겠습니다.

UPDATE course SET comple_div='전공필수' WHERE course_id=1; UPDATE course SET comple_div='전공선택' WHERE course_id=2;

해당 두문장을 적용한뒤 SELECT 문으로 확인해보면 보시는 것처럼 적용된 것을 확인하실 수 있습니다.

결과적으로 우리가 원하는 제2 정규화가 완료된 상태와 MySQL에서 정규화가 완료상태를 보면 동일한 것을 볼 수 있습니다.

우리가 원하는 제2 정규화가 완료된 상태

MySQL에서 적용된 모습

2.간단한 JOIN구문

자 예를 들어서 학번이 101번인 Sona라는 학생이 자기가 수강신청 사이트에서 자기가 수강신청한 과목을 확인하고 싶어합니다. 그에 따라 우리는 그 Sona학생이 수강신청한 데이터를 조회처리를 해주어야 할텐데요.

이때, 학생이름,전공,학년,신청한 과목명,과목시수,이수구분,신청날짜 이런식으로 알려줘야한다고 가정해보겠습니다.

아 그럼 조회를 처리해주면 되니까 SELECT문을 사용하면 되겠구나하고 다음과 같은 구문을 적용하면

SELECT student.name,student.major,course.course_name,course.course_hour, course.comple_div,course_order.order_date FROM student,course,course_order WHERE student.student_id=101;

여기서 student.name 이라는건 'student테이블의 name컬럼'을 의미합니다. 이렇게 '.'을 사용해서 테이블의 컬럼을 지정할 수 있습니다.

무튼 위와 같은 SELECT 구문을 통해서 학생이름,전공,신청한 과목명,과목시수,이수구분,신청날짜를 조회처리 해주었는데 결과가 다음과 같이 나타납니다.

Sona라는 학생은 2개과목을 신청했으므로 2개과목만 출력해주면 되니까 2줄만 딱 띄어놓으면 될텐데

왜 쓸데없이 28줄이나 출력이 되었을까요??

그것은 바로 FROM 뒤에 3개의 테이블에서 각각각 모든 튜플에 대해서 조회처리를 하는 도중에

각 테이블 마다 SELECT로 추출하고자 컬럼의 일부분을 추출하고 나서도 추출하지 않고자 하는 컬럼들에대해서 조회를 처리하고 조회는 처리했으나 추출하고자 하는 대상이 아니므로 추출하고자 하는 대상의 값으로 넣어서 표현해버리기 때문에 이렇게 중복되어 출력이 되는 것입니다.

보시면 student 테이블은 where조건을 걸었으니 제외해서 생각하고

course 테이블에는 2개의 튜플이 존재하고, course_order에는 14개의 튜플이 존재하죠?

따라서 2*14= 28개의 행을 출력하게 되는 것입니다.

이러한 경우 바로 필요한 것이 JOIN 구문입니다.

해당 문제를 해결하기 위해서는 LEFT JOIN이라는 구문을 사용하면 되는데요.

Left 를 포함한 교집합 부분을 LEFT JOIN이라고 합니다.

LEFT 조인은 다음과 같은 형식으로 사용됩니다.

SELECT * FROM 테이블1 LEFT JOIN 테이블2 ON 조인기준;

EX) SELECT * FROM student LEFT JOIN course_order ON

student.student_id=course_order.student_id;

자 이제 우리가 해결해야하는 문제를 위해서 LEFT JOIN을 사용해볼까요?

우리가 지금 호출하는 테이블은 총 3개니까 LEFT JOIN을 2번사용하면 됩니다.

그럼 다음과 같이 SELECT 문을 작성하면 되겠습니다.

SELECT name,major,Grade,order_date,course_name,course_hour FROM student LEFT JOIN course_order ON student.student_id=course_order.student_id LEFT JOIN course ON course_order.course_id=course.course_id WHERE student.student_id=101;

자 위 SQL 문을 실행하면 다음과 같은 출력결과를 얻을 수 있습니다.

LEFT JOIN을 활용하여 SLECT문을 작성했더니 깔끔하게 학생이름,전공,학년,신청한 과목명,과목시수,이수구분,신청날짜를 조회처리를 깔끔하게 얻을 수 있게 되었습니다.

Sona라는 학생은 경영학과이고 3학년이면서 8월 1일날 DataAnalysis와 DataViz 2개과목을 신청했고 각각 수업은 3시간 2시간이네요.

어떤가요? JOIN 구문을 통해서 각 학생의 수강신청내역 조회업무를 간단하게 처리할 수 있게되었습니다.

JOIN구문은 LEFT JOIN 구문이 가장활용도가 높아서 이렇게 소개해드렸는데요, 그 외에도 inner 조인 right조인, union조인 등이 있습니다.

아 참고로 select * from 테이블1,테이블2 이런식으로 form 뒤에 여러개 테이블을 불러와서 조인하는 것을

크로스 조인이라고 한답니다.

이제 구글링을 통해서 더욱 다양한 join구문을 통해서 위와 같은 문제들을 해결 할 수 있는 능력이 생겼으니 더욱더 SQL사용자 다워짐을 느끼실 수 있을 것 같습니다.