[1주차] 24.01.11 목요일

정규화
논리적 데이터베이스 모델링의 핵심

 

데이터의 중복을 방지하고 속성을 본래의 테이블에 위치시키고자 정규화를 진행한다.

 

정규화에 위배되는 테이블은 정규화를 진행해야 한다.

여러 정규화를 정해진 순서에 맞게 진행해야 하는 것은 아니고, 위반된 정규화에 해당하는 정규화만 진행하면 된다.

부모테이블에서 자식테이블로 전이된 외래키가 자식 테이블에서 기본키 역할을 한다면 두 테이블은 식별 관계이다.

 

• 제1정규화 : 반복되는 속성이나 그룹의 속성을 제거하고 새로운 실체를 추가한 뒤 기존의 실체와 1:n 관계를 형성한다.

개념 모델링 단계에서 위와 같은 E-R 다이어그램이 작성되었다면 다대다 형태를 축소하기 위해 제1정규화를 진행한다.

 

이렇게 다이어그램을 작성하면 RDBMS에서 표현할 수 없는 다대다 관계를 지울 수 있다.

 

• 제2정규화 : 기본키가 복합으로 이루어져 있는 복합키 전체에 의존하지 않는 일반속성을 제거 후 재위치 한다.

만약 다이어그램이 위와 같이 존재한다면 동아리등록 테이블에서 학생전화번호 속성은 해당 테이블의 기본키에 의존하는 성질을 갖지 않는다. 이는 제2정규화를 위배하므로 학생전화번호 속성은 동아리등록 테이블이 아닌 학생 테이블에 재위치 시켜야 한다. 이를 수정하면 아래와 같다.

 

이렇게 다이어그램을 수정하게 되면 동아리등록 테이블의 등록일자와 등록사유 속성은 모두 복합키인 학번과 동아리코드에 의존하므로 제2정규화가 올바르게 진행된 결과이다.

 

• 제3정규화 : 테이블에서 키가 아닌 모든 컬럼들은 기본키에 의존해야 하고 기본키가 아닌 컬럼에 종속되는 속성이 있다면 제거한다.

좌측에 위치한 원시테이블에서 우편번호, 시도명, 구군명, 읍면동명 속성은 기본키인 사원번호에 의존하는 속성이 아니다. 따라서 제3정규화를 진행하면 우측에 위치한 다이어그램과 같이 우편번호를 기본키로 갖는 새로운 테이블이 생성된다.

 

• 모델링 및 정규화 실습

주문ID	주문일	회원ID	회원명	회원등급	상품ID	단위	수량	단가
2301	940614	101	홍길동	일반	H360	NET	20	25600
					J142	PAKET	320	654000
					K101	3-PACK	16	32500
2302	940615	107	송종국	우수	A101	6-PACK	8	24200
2303	940616	110	박찬호	일반	4011	PAKET	10	65100
					4301	COVER	6	12900

 

제1정규화를 진행한 다이어그램의 모습은 아래와 같다.

 

주문테이블에 대해 제3정규화를 진행하면 아래와 같다.

 

주문테이블에서 기본키인 주문ID가 아닌 일반 속성인 회원ID에 종속하는 회원명, 회원등급 속성을 새로운 테이블을 생성하여 종속성을 제거한 결과이다.

 

주문상세테이블에 대해 제2정규화를 진행하면 아래와 같다.

 

SQL(Structure Query Language)
구조적 질의 언어

-- 한 줄 주석
/*
여러 줄 주석
*/

-- 데이터베이스 변경
USE employees;

-- 로컬 호스트의 데이터베이스 목록
SHOW DATABASES;

-- 테이블의 구조 확인
DESC titles;

/*
SELECT 문법 (데이터를 검색할 때 사용하는 구문)

SELECT distinct 컬럼명 | 컬럼명 | * | 표현식 또는 함수를 포함한 표현식 | [AS] 별칭
FROM TABLE_NAME
[WHERE ] -- 행의 제한
[GROUP BY ] -- 데이터를 그룹으로 묶을 때
[HAVING ] -- 그룹에 대한 조건
[ORDER BY ] -- 정렬(내림차순 : DESC, 오름차순 : ASC)

별칭은 공백을 포함하는 문자 및 특수문자를 사용할 수 없으며
사용하기 위해서는 '', ``을 사용해야한다.

키워드는 대소문자를 구분하지 않지만 데이터는 대소문자를 구분한다.
*/

SELECT	EMP_NAME,
	SALARY, 
	(SALARY + (SALARY + BONUS_PCT) * 12) AS 연봉 -- AS 뒤에 공백을 포함한 문자로 변경하고 싶은 경우에는 ''이나 ``로 감싸줘야한다.
FROM	employee;


-- null 처리를 위한 함수 ifnull(exp1, exp2), nullif(exp1, exp2) : exp1의 값이 null일 때 exp2로 대체되는 함수
SELECT IFNULL(NULL,'넌 누구냐?'), NULLIF(100, '널이 아니군요');

-- 더미컬럼
SELECT	EMP_ID,
	EMP_NAME,
	'재직' AS '근무여부'
FROM	employee;

-- distinct : 컬럼에 포함된 중복값을 한번씩만 출력하고자 할 때 사용한다.
SELECT distinct	DEPT_ID
FROM		employee;

/* 문자열 컬럼의 차이
char : 고정길이
varchar : 가변길이

내장함수로 문자열의 문자개수, 바이트 수를 반환하는 함수 - CHAR_LENGTH() : 문자 개수를 리턴. LENGTH() : 바이트 수를 리턴.

*/

-- where ''
-- 연산자(비교, 산술, 논리(and, or, not)), 연결연산자

-- 만약, 결과값을 컬럼이 하나인것처럼 연결해서 출력한다면?
-- 연결연산자

SELECT CONCAT('한선기', '님의 급여는', '0', '원입니다.')

SELECT	EMP_NAME,
	SALARY
FROM	employee;

SELECT	CONCAT_WS('//', EMP_NAME, '님의 급여는', salary, '원입니다.') AS '급여정보'
FROM    employee;

-- between ~ and : 상한값과 하한값을 포함하는 경우에 사용 가능. 초과, 미만 조건에는 사용할 수 없다.

-- like 비교연산자(패턴검색), %(0개 이상의 임의의 문자와 매칭됨), _(하나의 문자와 매칭됨)

-- 사원테이블에서 김씨 성을 가진 사원만 검색하고 싶다면?
SELECT	*
FROM    employee
WHERE	emp_name LIKE "김%";

SELECT	*
FROM    employee
WHERE	emp_name NOT LIKE "김%";

-- 메일아이디 중 _ 앞자리가 3자리인 직원만 검색하고 싶다면?
SELECT	*
FROM    employee
WHERE	email LIKE '___\_%';

SELECT	*
FROM    employee
WHERE	email NOT LIKE '___\_%'; -- not의 위치는 상관없음

-- 사원테이블에서 부서 배치를 받지 않은 사원을 검색한다면?
-- is null, is not null
SELECT	*
FROM    employee
WHERE	dept_id IS NULL;

SELECT	*
FROM    employee
WHERE	dept_id IS NOT NULL;

-- or, in
-- 부서번호가 60번이거나 90번인 사원만 검색한다면?
SELECT	*
FROM    employee
WHERE	dept_id = '60' OR dept_id = '90';

SELECT	*
FROM    employee
WHERE	dept_id IN ('60', '90');