SQL

DDL (데이터 정의 언어, Data Definition Language)

• 데이터베이스 객체라고도 함

• 목적
• 데이터 저장 : 테이블, 인덱스, 뷰
• 데이터 조작 : 트리거, 프로시저, 함수 등

• DDL의 종류
• CREATE : 객체 생성
• ALTER : 객체 수정(구조 변경)
• 일반적으로 데이터베이스의 스키마는 수정이 불가능하고 수정하고 싶으면 스키마를 삭제하고 다시 만들어야 합니다. 그러므로 ALTER SCHEMA... 구문은 불가능합니다.
• DROP : 객체 삭제(테이블 삭제, 프로시저 삭제 등)

• 제약조건
• NOT NULL : 컬럼이 NULL이 될 수 없음
• UNIQUE : 테이블 내에서 동일한 값을 가질 수 없음
• PRIMARY EKY (PK) : 기본키, UNIQUE and NOT NULL
• FOREIGN KEY (FK) : 외래키, 다른 테이블 참조 컬럼

DML (데이터 조작 언어, Data Manipulation Language)

• DDL에 의해 정의된 테이블의 데이터를 조작하는 명령어입니다.

• 종류
• INSERT : 테이블 객체에 레코드를 삽입
• UPDATE : 테이블 레코드의 컬럼 값을 수정
• DELETE : 테이블의 특정 레코드를 삭제(행 삭제)
• SELECT : 테이블의 레코드를 검색 (선택)

데이터 검색

• SELECT문의 기본구조

SELECT ...
FROM ...
WHERE ...
GROUP BY  ... (생략 가능)
HAVING ... (생략 가능)
ORDER BY .. (생략 가능)

• 실행순서

• 집계 함수
• 집계 함수란 통계 연산을 하는 함수로 SELECT, HAVING 절에 사용 가능합니다
• 종류
• COUNT : 컬럼에 있는 값의 개수
• SUM : 컬럼에 있는 값의 합
• AVG : 컬럼에 있는 값의 평균
• MAX : 컬럼의 값 중 최댓값
• MIN : 컬럼의 값 중 최솟값

• 그룹 질의 (GROUP BY)
• 그룹화의 목적은 위에서 설명한 집계 함수를 적용하는 것입니다. 따라서 그룹화를 했으면 SELECT절에는 그룹화된 기준과 집계 함수만 사용 가능합니다.
• HAVING은 그룹 질의에 대한 조건을 명시하는 절입니다.
• WHERE : 레코드 전체에 대한 조건
• HAVING : 그룹화 이후 레코드에 대한 조건
• 예시 = 직원 (EPLOYEE) 테이블에서 부서원이 20명 이상인 것만 보여준다면 (DEPT_NO(column) = 부서 번호)

SELECT DEPT_NO, COUNT(*)

FROM EMPLOYEE

GROUP BY DEPT_NO

HAVING COUNT(*) >= 20;

• 중첩 질의
• SELECT 문 안에 다른 SELECT문이 들어가 있는 질의입니다
• 예 = 평균학점 이상을 받은 학생들의 이름을 구하는 질의는 아래와 같다.

/* 학생 김철수와 같은 학과에 속하는 학생의 성명을 검색하라*/
SELECT 성명

FROM 학생

WHERE 학과 = (SELECT 학과 FROM 학생 WHERE 성명 = '김철수');
/*괄호에서 반환되는 결과는 '컴퓨터' 단일 값임*/

 

• 조인 질의
• 종류



 
• 내부 조인 (INNER JOIN)

--ANSI SQL 표준
SELECT E.NAME AS "이름", D.DEPT_NAME AS "부서"
FROM EMPLOYEE E
         INNER JOIN DEPARTMENT D ON E.DEPT_NO = D.DEPT_NO;

--ORACLE 표준
SELECT E.NAME AS "이름", D.DEPT_NAME AS "부서"
FROM EMPLOYEE E,
     DEPARTMENT D
WHERE E.DEPT_NO = D.DEPT_NO;

 
• 외부 조인 (OUTER JOIN)
• LEFT OUTER JOIN (LEFT JOIN) : 조인 왼쪽 테이블의 데이터는 전부 출력
• RIGHT OUTER JOIN (RIGHT JOIN) : 조인 오른쪽 테이블의 데이터는 전부 출력
• FULL OUTER JOIN : 양쪽 테이블의 데이터 전부 출력

--ANSI SQL 표준
SELECT E.NAME AS "이름", D.DEPT_NAME AS "부서"
FROM EMPLOYEE E
         LEFT OUTER JOIN DEPARTMENT D ON E.DEPT_NO = D.DEPT_NO;

--ORACLE 표준
SELECT E.NAME AS "이름", D.DEPT_NAME AS "부서"
FROM EMPLOYEE E,
     DEPARTMENT D
WHERE E.DEPT_NO = D.DEPT_NO(+);

 

정규화 목적

• 데이터의 일관성 유지 (무결성)
• 저장공간 확보 (중복 데이터 제거)
• 갱신 이상 해소
• 갱신 이상
• 삽입 이상 : 레코드를 추가하려고 할 때, 필요치 않은 컬럼 값까지 포함하여 추가해야 하는 경우
• 삭제 이상 : 레코드 삭제 시 의도하지 않은 다른 컬럼의 값까지 삭제되는 경우
• 수정 이상 : 레코드 수정을 위한 컬럼 값이 중복 데이터인 경우 그 중 일부만 반영되어 일관성이 깨지는 경우
 

함수적 종속성

함수적 종속성이란 릴레이션을 분석하여 속성들 간의 연관관계를 표현하는 것입니다. 속성 간의 연관관계를 분석하여 더 효율적인 릴레이션으로 변환할 수 있습니다.
• 함수적 종속성 다이어그램, Functional Dependency Diagram, FDD= 함수적 종속성 관계를 직관적으로 이해할 수 있도록 직사각형과 화살표를 사용하여 표현한 다이어그램

정규화

이상현상(Anomaly)이 있는 릴레이션을 분해하여 이상현상을 없애는 과정

정규형의 분류

• 제1 정규화 = 모든 속성의 도메인이 원자 값을 갖는 상태
• 제2 정규화= 기본키가 아닌 속성들은 모두 기본키에 종속되어야 하는 상태



• 제3 정규화 = 제2 정규형을 만족하는 전제 하에 기본키가 아닌 속성이 기본키에 의해 이행적함수 종속 상태가 아닌 것
• <소속팀, 리그> 속성의 데이터가 반복되고 있습니다.
• 그 원인으로 기본키 <번호> 속성이 소속팀을 결정하고, 다시 <소속팀> 속성이 <리그>를 결정하는 것을 볼 수 있습니다.
• 이행적함수 종속 상태 = 종속자가 다시 결정자가 되는 것



• BC 정규화 (보통 실무에서는 여기까지만 진행)
• 후보키가 아닌 <감독> 속성이 <소속팀> 속성을 결정하고 있는 경우



 
https://code-lab1.tistory.com/270
• 제4 정규화



1. BCNF를 만족해야 한다.
2. 다치 종속(Multi-valued Dependency)이 없어야 한다.
1. A->B 일 때 하나의 A값에 여러 개의 B값이 존재하면 다치 종속성을 가진다고 하고 A↠B라고 표시한다
2. 최소 3개의 칼럼이 존재한다.
3. R(A, B, C)가 있을 때 A와 B 사이에 다치 종속성이 있을 때 B와 C가 독립적이다.
• 다치종속의 경우 = 학생 번호 하나에 과목 여러 개와 취미 여러 개가 종속된다. 이런 경우 학생 번호를 토대로 값을 조회하면 아래와 같이 중복이 발생하게 된다.



• 아래 2개의 테이블은 여전히 다치 종속성을 가지지만, 2개 이상의 칼럼이 하나의 칼럼에 다치 종속되지는 않기 때문에 제4 정규형을 만족한다.



• 제5 정규화 = Project Join Normal Form(PJNF)
1. 4NF를 만족해야 한다.
2. 조인 종속(Join dependency)이 없어야 한다.
• 조인 종속= 다치 종속의 좀 더 일반화된 형태이다. 만약 하나의 릴레이션을 여러 개의 릴레이션으로 무손실 분해했다가 다시 결합할 수 있다면 조인 종속이라고 한다.



3. 조인 연산을 했을 때 손실이 없어야 한다.
 
https://dodo000.tistory.com/21

역정규화

정규화를 통해 분리되었던 릴레이션에서 중복을 허용하고 다시 통합하거나 분할하여 구조를 재조정하는 과정입니다.
정규화된 릴레이션은 하나의 릴레이션을 분해하기 때문에 원하는 자료가 하나의 릴레이션에 존재하지 않아 외래키를 이용해 참조해야하는 상황이 잦아집니다. 이는 데이터베이스에 저장된 자료를 검색하는 시간을 증가시키며 성능을 저하시킬 수 있습니다. 따라서 데이터베이스의 물리적 설계 과정에서 성능을 향상시키기 위해 역정규화를 실행합니다.