IT회사원 신동갓

데이터베이스 정규화

정규화를 하는 이유?

한 릴레이션에 여러 엔티티의 애트리뷰트들을 혼합하게 되면 정보가 중복 저장되고, 이는 저장 공간을 낭비시킨다. 또한, 중복된 정보로 인해 갱신 이상이 발생되며, 동일한 정보를 한 릴레이션에는 변경하고, 나머지 릴레이션에서는 변경하지 않은 경우 어느 것이 정확한지 알 수 없다. 이 때문에 정규화 과정을 거쳐 해결한다.

갱신 이상의 종류

<회원> 테이블

학번(PK)	이름	클래스	강사	과목명
1	홍길동	A	신다람쥐	넥슨 취직 대비
2	신첨지	B	이노드	웹 어플리케이션
3	김아무	B	이노드	웹 어플리케이션
4	최태수	C	최디비	데이터베이스
5	이길투	C	최디비	데이터베이스

• 삽입 이상

  • 원하지 않는 자료가 삽입되거나, 삽입하는데 자료가 부족하여 삽입이 되지 않아 발생하는 문제이다.

  • 위 테이블에서 네트워크 과목을 신설할 때 학생이 한명도 없으므로, 이름과 학번이 null값을 갖는다. 학번은 PK이므로 null을 가질 수 없으므로 오류가 발생한다.

• 삭제 이상

  • 하나의 자료만 삭제하고 싶지만, 그 자료가 포함된 튜플 전체가 삭제됨으로 원하지 않는 정보 손실이 발생하는 문제점을 말한다.

  • 위 테이블에서는 "홍길동" 튜플을 삭제하면, (클래스 A, 신다람쥐, 넥슨 취직대비)가 삭제된다. 위에서 클래스 A 정보를 가진 튜플은 "홍길동"이 유일하므로 없어지면 안되는 A 클래스의 정보가 사라지게된다.

• 수정(갱신) 이상

  • 정확하지 않거나 일부의 튜플만 갱신되어 정보가 모호해지거나 일관성이 없어져 정확한 정보 파악이 되지 않는다.

  • 위 테이블에서 B클래스의 강사 이름을 "웹마스터"로 바꾸게 되면, 모든 튜플에서 B클래스의 강사 이름을 전부 수정해야한다.

정규화 과정

실무에서는 주로 1~3정규형만을 사용한다고 한다. 그러므로 3정규형까지만 정리해보겠다.

• 제 1 정규형

  • 애트리뷰트의 도메인이 오직 원자값만을 포함하고, 튜플의 모든 애트리뷰트가 도메인에 속하는 하나의 값을 가져야 한다.

  • 복합 애트리뷰트, 다중값 애트리뷰트, 중첩 릴레이션 등 비 원자적인 애트리뷰트들을 허용하지 않는 릴레이션 형태이다.

• 제 2 정규형

  • 모든 비주요 애트리뷰트들이 주요 애트리뷰트에 대해서 완전 함수적 종속이면 제 2 정규형을 만족한다고 볼 수 있다. 완전 함수적 종속이란 X->Y 라고 가정했을 때, X의 어떠한 애트리뷰트라도 제거하면 더 이상 함수적 종속성이 성립하지 않는 경우를 말한다. 즉, 키가 아닌 열들이 각각 후보키에 대해 결정되는 릴레이션 형태를 말한다.

• 제 3 정규형

  • 어떠한 비주요 애트리뷰트도 기본키에 대해서 이행적으로 종속되지 않으면 제 3 정규형을 만족한다고 볼 수 있다. 이행 함수적 종속이란 X->Y, Y->Z의 경우에 의해서 추론될 수 있는 X->Y의 종속관계를 말한다. 즉, 비주요 애트리뷰트가 비주요 애트리뷰트에 의해 종속되는 경우가 없는 릴레이션 형태를 말한다.(한 테이블에서 X->Y->Z이면, X->Y, Y->Z 두 테이블로 나눈다.)

정규화의 단점?

무작정 정규화를 하는 것은 좋지 않다. 테이블 간의 조인을 자주해야한다면 오히려 한 테이블에 있는 것이 퍼포먼스가 좋을 수도 있다는 이야기이다.

데이터베이스 트랜잭션

트랜잭션이란?

데이터베이스 내에서 한번에 수행되어야할 일련의 연산.

한번에 완료되면 성공적이므로 COMMIT하고 데이터베이스에 반영됨.

도중에 취소가 되면 ROLLBACK하여 작업 시작의 초기의 상태로 되돌림.(일련의 연산 안의 모든 작업이 취소되어 데이터베이스에 영향을 미치지 않음)

트랙잭션의 특성

• 원자성(Atomicity)

  분리 할수 없는 하나의 단위로 작업은 모두 완료되거나 모두 취소 되어야 합니다.

• 일관성(Consistency)

  사용되는 모든 데이터는 일관되어야 합니다.

• 격리성(Isolation)

  접근하고 있는 데이터는 다른 트랜잭션으로 부터 격리 되어야 합니다.

  트랜잭션이 진행되기전과 완료된 후에 상태를 볼수 있지만 트랜잭션이 진행되는 중간 데이터는 볼수 없습니다.

• 영속성(Durability)

  트랙잭션이 정상 종료되면 그 결과는 시스템에 영구적으로 적용되어야 합니다.

• 순차성(Sequentiality)

  데이터를 다시 로드하고 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌리는 것을 말합니다.

https://ko.wikipedia.org/wiki/Join_(SQL)

위키 잠고했습니다!

아래 쿼리문으로 테이블 생성하고 데이터 삽입해주세요!

CREATE TABLE department
(
 DepartmentID INT,
 DepartmentName VARCHAR(20)
);

CREATE TABLE employee
(
 LastName VARCHAR(20),
 DepartmentID INT
);

INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, '영업부');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, '기술부');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, '사무부');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, '마케팅');

INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);

그리고 크로스 조인을 해보겠습니다!!

크로스 조인은 아래의 쿼리와 결과가 같네요!

크로스 조인은

이 두 테이블의 곱을 나타내는 것 같습니다!

왼쪽 임플로이 테이블은 데이터가 6개 오른쪽 디퍼트먼트 테이블은 4개네요!

위에서 쿼리를 썼을 때 24행이라는 결과가 보이시죠!?

6 * 4 = 24 입니다.

Employee테이블에서 (Rafferty, 31)행에 대해서 Department의 (31, 영업), (32, 기술), (33, 사무), (34, 마케팅) 4개의 행이 대응됩니다.

이걸 Rafferty~John까지 반복하면 24개의 행이 생성되겠죠!

이제 내부 조인 차례입니다!!!!!!!!!!!!! 내부 조인은 영어로 Inner Join이라고 하네욧!

내부 조인의 특징!

내부 조인은 가장 흔한!!! 결합 방식이랍니다!!

그리고! 조인 구문(조인 쿼리문에서 ON 뒤의 부분을 말하는 것 같습니다!)에 충족하는 A테이블의 행과 B테이블의 행을 결합하여서 출력합니당!

아그리고 !! 조인에는 명시적 조인 표현과 암묵적 조인 표현이라는게 있습니다!!

위에서 크로스 조인이라고 언급하고 출력한것이 명시적 조인, 그 밑에 select * from 임플로이, 디파트먼트 이 것이 암묵적 조인입니다.!

cross join이라고 명시했기 떄문에! 명시적 조인이구요!, 암묵적 조인에는 join을 언급하진 않았지만 조인한 것과 같은 결과를 보여주기 때문에 암묵적 조인 같습니다!

내부 조인에서도 한번 해보겠습니다!

쿼리문을 해석하자면! department 테이블이 employee테이블에 내부 조인을 하네요!

On뒤에 조건이 나오죠! 임플로이 테이블의 departmentid와 디파트먼트 테이블의 departmentid가 같으면

데이터를 보여줘라 이런 내용입니다.

가운데 두 컬럼을 보시면 31 31, 33 33, ...으로 같은것을 볼 수 있습니다.

아래는 암묵적으로 내부조인을 하는 쿼리문입니다!

오옷!!!

위 두개의 결과가 같네요 신기하네요!@!

위에서 했던 크로스 조인의 결과에서 where을 이용하여 조건을 거네요!

크로스 조인의 결과 중에서! department아이디가 같은거만 출력하는 것입니다!

재밌네요 ㅎ..ㅎ

아아 참고로 매번 employee, department 이런식으로 쓰기보다는 별명을 붙여주면 편리하게 사용할 수 있습니다!

SELECT * 
FROM employee e INNER JOIN department d 
  ON e.DepartmentID = d.DepartmentID;

이렇게! 테이블명 뒤에다가 별명을 붙여주시면 됩니다! 실행보세욧

내부 조인을 세부적으로 분류하면

동일 조인(equi-join), 자연 조인(natural join), 교차 조인(cross-join)으로 나눌 수 있답니다!

동일 조인은 위에서 보여준 내부조인과 같은 쿼리네요!

자연조인은!!!!

자연 조인(natural join)은 동일 조인의 한 유형으로 조인 구문이 조인된 테이블에서 동일한 컬럼명을 가진 2개의 테이블에서 모든 컬럼들을 비교함으로써, 암시적으로 일어나는 구문이다. 결과적으로 나온 조인된 테이블은 동일한 이름을 가진 컬럼의 각 쌍에 대한 단 하나의 컬럼만 포함하고 있다.

위키에서 이렇게 설명하고있네요!!!

하지만 위험한 조인인가 봅니다!! 아래 이러한 설명도 붙어있습니다.

대부분의 전문가들은 NATURAL JOIN이 위험한 것이며, 그러므로 이것의 사용을 강력하게 비권장하고 있다.^[3] 그러한 위험은 다른 테이블에 다른 컬럼으로 동일한 이름을 가진 새로운 컬럼을 무심코 추가하는데서 오는 것이다.

현존하는 자연 조인은 자연스럽게 (다른 컬럼에서 온) 이전보다 다른 기준을 이용해서 비교를 위해 비교를 하거나 일치하는 것을 찾아서 새로운 컬럼을 이용할 것이다. 그리하여 테이블 내에 있는 데이터가 변경되지 않고, 증가만 해도 현존하는 질의어는 다른 결과물을 생성할 것이다.

이제 외부 조인을 해보겠습니다!!!!!!!

외부조인은 왜!?!? 왜쓸까욧!!!

equi join은 조인을 생성할 때 동일한 값이 없다면 데이터를 반환하지 못하는데요!!!!

이 때 동일한 값이 없는 행들도 포함하여 조회하기 위해서

외부 조인을 사용합니다!

left 외부 조인을 해보겠쑵니당!!!

위의 내부조인과 다르게 john이 표시되어있습니다!!!!

그리고 오른쪽 조인 왼쪽조인은 기능적으로 동일하다고 하네요!!!

아래의 설명을 보시죠!

오른쪽과 왼쪽 외부 조인은 기능적으로 동일하다. 양자 모두 다른 것들이 하지 않는 어떠한 기능도 제공하지 않는다. 그래서 오른쪽과 왼쪽 외부 조인은 테이블 순서가 변경되기만 하면, 서로 대체할 수 있다.

그렇다네요!!!!

right join도 어떻게쓰는지 보겠습니다!.

컬럼 순서만 바뀌었을 뿐이지 결과는 같네욧!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

이만 마치겠숩니당 뿅뿅

SELECT 구문 형식

select select_list(컬럼, cnt, avg 등등) [ into new_table ]

[ from table_source ] [ where search_condition ]

[ group by group_by_expression ]

[ having search_condition ]

[ order by order_expression [ asc | desc ] ]

GROUP BY : 특정 열이나 특정 열을 연산할 결과를 집계 키로 정의하여 그 집계 키의 Unique 값에 따라 그룹을 짓는 연산자.

DISTINCT : 단순히 unique값만을 추출하기 위해 사용.

위 둘의 차이 : group by는 집계 키(count(*), sum(), max(), min(), avg() 등) 기준으로 집합 연산, distinct는 unique만 뽑아냄.

ex)

1. select distinct player_id, team_id from player

2. select distinct player_id, team_id, count(*) from player

3. select team_id, count(*) from player group by team_id

1번 예시는 player_id, team_id 컬럼에서 중복제거가 되어 출력됨.

2번 예시는 오류가 난다. 이유는 distinct에 집계 함수를 썼기 때문이다.(count)

3번 예시는 team_id로 그룹화 되어 각 team_id당 몇개의 데이터가 있는지 출력된다.

select문을 계속 연습해보겠다.

이러한 데이터들이 있다.

위의 3번처럼 completed에 대해서 그룹화하고 count를 출력해보겠다.

INTO : INTO는 조건에 맞는 기존 테이블의 열 내용을 새 테이블을 만들어 가져온다.

(테스트해보니 지원안하는 DB도 있는 것 같군요.. H2로 하고 있는데 안되네용..또르르)

ex) select * into [새로운 테이블 명] from [데이터를 가져올 테이블] where 조건

having절 : where과 비슷한데 group에 대해서 적용되는 조건이라고 생각하면 된다.

WHERE절 조건

(실험 결과 not의 위치는 컬럼명 앞이나 뒤 아무데나 상관없는듯)

컬럼명 between a and b :  a와 b사이의 값을 가진 결과를 보여줌(a, b 포함)

not 컬럼명 between a and b : a와 b사이의 값을 제외하고 보여줌

컬럼명 is null : 컬럼의 value가null인 데이터를 찾음

컬럼명 is not null : 컬럼의 value가 null이 아닌 데이터를 찾음

컬럼명 like ''

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

%가나다 : 멍충이가나다, 바보가나다, 하가나다 등 뒤에 가나다가 붙는 데이터를 찾음

가나다% : 가나다멍충이, 가나다바보, 가나다라마바사아자차카타하 등 앞에 가나다가 붙는 데이터를 찾음

%가나다% : 가운데 가나다가 들어가는 데이터를 찾음.

_가나다 : 킼가나다, ㅎ가나다 등 앞에 한글자 불특정문자가 오는 가나다를 찾음.

컬럼명 in('', '') : in안의 데이터들이 포함되어있는 데이터를 보여줌.(or과 같음)

참고 블로그

http://hyeonstorage.tistory.com/292

테이블 수정

1. 컬럼 추가(맨 뒤에 추가됨)

alter table 테이블명

add 컬럼명 datatype;

ex)

alter table player

add (age int));

2. 컬럼 삭제

alter table 테이블명

dop column 컬럼명;

ex)

alter table player

drop column age;

3. 컬럼 수정

alter table 테이블명

modify (컬럼명1 데이터 유형 [default 식][not null],

컬럼명2 데이터 유형 [default 식][not null]);

ex)

alter table player   

modify (player_name varchar(30) default 'hahahaha' not null);

특징

- 컬럼의 크기를 늘릴 수는 있지만 줄이지는 못함. (기존의 데이터가 훼손될 수 있기 때문)

- 해당 컬럼이 null값만을 가지고 있으면 데이터 type을 바꿀 수 있음.

- 해당 컬럼의 default값을 바꾸면 변경 이후의 삽입에만 영향을 줌.(기존의 것이 바뀌지 않음을 의미)

- 해당 컬럼에 null 값이 없을 경우에만 not null 제약조건을 추가할 수 있음.

4. 컬럼명 수정

alter table 테이블명

rename column 변경할 컬럼명 to 새로운 컬럼명;

alter table player

rename column player_id to team_id;

5. 제약조건 추가

alter table 테이블명

add constraint 제약조건명 제약조건(컬럼명);

ex)

alter table player

add constraint player_fk

foreign key(team_id) references team(team_id);

해석

player 테이블을 바꾸겠다.

player_fk라는 이름의 제약조건을 추가하겠다.

team_id를 외래키로 하고 team테이블의 team_id를 참조하겠다.

6. 제약조건 삭제

alter table 테이블명

drop constraint 제약조건명;

ex)

alter table player

drop constraint player_fk;

테이블 삭제

drop table 테이블명

char의 특징

1. 고정 길이 문자열

2. 고정된 길이 만큼 채워지지 않으면 나머지 부분은 공백으로 채워짐.

varchar 특징

1. 가변적인 길이 문자열

두 문자열의 차이점은 저장 영역, 문자열 비교 방법이다.

varchar는 가변적인 길이이므로 필요한 영역은 실제 데이터 크기(들어오는 크기?).

길이가 다양한 컬럼에 장점을 가짐. char보다 능동적이다.

char은 고정된 길이(주민등록번호, 생일 등)에 char을 사용하는 것이 좋음

문자열 비교방법에서 char은 나머지가 공백으로 채워진다고 했다.

ex) char(20) 이면 10글자를 쓰면 나머지 10은 공백으로 채워짐.

abcdef공백 * 14 == abcdef 공백 * 4 같은 값을 가짐.(공백고 관계없이 문자열만 같으면 같음?)

반면에 varchar은 공백또한 문자로 취급하므로 공백이 들어가면 다른 문자열로 판단함

위의 예시일 경우 다른 문자열로 판단함.

테이블 생성

create table 테이블명(

컬럼명 DataType [Default 형식]

);

예시 1)

create table player(

player_id char(10) not null,

player_name varchar(20) not null,

team_id char(10) not null

//제약조건

constraint player_pk primary key(player_id),

//player_pk라는 제약조건을 만들겠다. 제약조건의 종류는 기본키(primary key)이며, 기본키로 할 컬럼은 player_id이다.

constraint player_fk foreign key(team_id) references team(team_id)

//player_fk라는 제약조건을 만들겠다. 제약조건의 종류는 외래키(foreign key)이며, 외래키로 할 컬럼은 team_id이고 team테이블의 team_id를 참조하겠다.

);

예시 2)

create table player(

player_id char(10) constraint player_pk primary key,

player_name varchar(20) not null,

team_id char(10) constraint player_fk foreign key(team_id) references team(team_id)

);

예시 3)

create table player(

player_id char(10) primary key,

player_name varchar(20) not null,

team_id char(10) foreign key references team(team_id)

);

제약 조건의 종류

1. not null : null 입력 불가

2. unique : 중복값 입력 불가

3. primary key : not null + unique(table당 하나만 가능)

4. foreign key : 다른 테이블을 참조

5. check : 뒤에 나오는 조건으로 설정된 값만 허용