2021.05.26

ex28_modeling.sql

정규화, Normalization

• 모델링 작업 > ERD > 정규화 > 안정성 높고, 작업하기 편한 ERD
• ERD 수정하는 작업(공식)
• 자료의 손실이나, 불필요한 정보를 없애고, 데이터의 일관성을 유지하고(중복x), 데이터 종속을 최소화하기 위해 자료구조를(ERD)를 수정하는 작업
• 우리가 만든 테이블(ERD) = 비정형, 비정규형 상태 -> 정규화 -> 정규화된 구조(정규화된 상태)
• 제 1 정규화, 제 2 정규화, 제 3 정규화 등
• 1 ~ 3 정규화 + 역정규화

관계형 데이터베이스 시스템이 지향하는 데이터베이스 상태

1. 되도록 셀이 null 상태를 가지지 않는다. null 상태의 셀이 많으면 좋지 않다.(공간차지 안좋음)

2. 중복값을 저장하지 않는다. 》 동일한 성격의 데이터 1개 이상의 테이블에 저장하지 않는다.

   》 같은 데이터를 여기저기 동시에 저장하지 않는다. 》공간 낭비, 관리 불편(수정 불편)

3. 하나의 셀 > 반드시 원자값 저장(단일값, 분리될 수 없는 값(Scaler)) -> ex) 성적 : 100,80,90 X

정규화 목적

1. null 제거

2. 중복값 제거

3. 자료 삽입, 갱식, 삭제에 따른 이상 현상 제거 》 데이터 무결성 보장

4. 삽입 이상(Insertion Anormaly)

   • 특정 테이블에 데이터를 삽입할 때 원하지 않는 데이터까지 같이 넣어야 하는 상황

5. 갱신 이상(Update Anormaly)

   • 동일한 데이터가 2개 이상의 데이블에 존재 》 그 중 1개는 수정했지만, 다른 1개를 수정 못했을 때

     》 두 데이터간의 이상 오류가 발생 》 2개 데이터 중 어떤 데이터가 올바른 데이터인지 구분 못하는 상황 발생

6. 삭제 이상(Deletion Anormaly)

   • 특정 테이블에서 데이터를 삭제할 때 원하지 않는 데이터까지 같이 지워야 하는 상황

함수 종속(Functional Dependency)

• 하나의 테이블내의 컬럼끼리의 관계 표현
• 정규화는 '부분 함수 종속'이나 '이행 함수 종속'을 모두 없애고, 모든 컬럼의 관계를 '완전 함수 종속'으로 만드는 작업이다.

1. 완전 함수 종속, Full Functional Dependency
2. 부분 함수 종속, Partial Functional Dependency
3. 이행 함수 종속, Transitive Functional Dependency

정규화

• 1NF ~ 3NF(Normal Form)
• 비정규형 -> (정규화) -> 정규형
• 1개 테이블 -> (정규화) -> 보통 2개 이상의 테이블로 쪼개진다.

제 1 정규화, 1NF

• 모든 컬럼(속성)은 원자값을 가진다.
• 여러 개로 분리 가능한 값을 1개의 컬럼 안에 넣지 말 것

제 2 정규화, 2NF

• 기본 키가 아닌 모든 컬럼은 기본키에 완전 함수 종속이어야 한다.
  • ex) 학번이 1001번에 자바를 들은 학생의 성적은 A+(성적은 학번+과목명에 종속적이다.)
• 부분 함수 종속 컬럼 발견!! 》 부분 함수 종속 제거
  • ex) 학생명은 학번에만 부분 함수 종속이다... (둘 중 하나의 컬럼에만 종속)
• 일부 컬럼이 복합키 모두에게 종속이 아니라, 복합키 일부에만 종속되는 현상

제 3 정규화, 3NF

• 기본 키가 아닌 모든 컬럼은, 기본키가 아닌 다른 컬럼에 종속되면 안된다.
• 이행 함수 종속 컬럼 발견!! 》 이행 함수 종속 제거

역정규화

• 정규화된 결과를 다시 원래대로 되돌리는 작업
• 2개 테이블 > 1개 테이블
• 수업 중 사용 금지

데이터 모델링 분야에서 개체-관계 모델이란 구조화된 데이터에 대한 일련의 표현이다. (테이블의 관계)
"구조"화된 데이터를 저장하기 위해 데이터베이스를 쓴다. (오라클 같은거 쓴다.)
이 데이터의 "구조" 및 그에 수반한 제약 조건들은 다양한 기법에 의해 설계될 수 있다.
그 기법 중 하나가 개체-관계 모델링(Entity-Relationship Modelling)이다.
줄여서 ERM이라고 한다.
ERM 프로세스의 산출물을 가리켜 개체-관계 다이어그램(Entity-Relationship Diagram)이라 한다.
줄여서 ERD라 일컫는다.

---

ex29_plsql.sql

ANSI-SQL

• 비 절차성 언어(명령어간의 순서가 없다. 흐름이 없다.) -> 제어 흐름이 없다. 문장 단위의 독립적인 언어

PL/SQL

• Procedural Language Extensions to SQL

• 절차성 언어

• 흐름과 제어를 추가

• ANSI-SQL 모두 지원

• PL/SQL = ANSI-SQL + 확장(제어구조)

프로시저, Procedure

• PL/SQL의 구성요소

• 메소드, 함수, 서브루틴 등..

• 순서가 있는 코드의 집합

프로시저의 종류

1. 익명 프로시저

   • 1회용
   • 오라클에 저장 X

2. 실명 프로시저

   • 반복용
   • 저장 프로시저
   • DB Object

PL/SQL 프로시저 블럭 구조(골격)

1. 4개의 키워드로 구성

   • declare
   • begin
   • exception
   • end

2. declare

   • 선언부
   • 프로시저에 사용할 변수, 객체 등을 선언하는 영역
   • 자바 생성자같은 역할
   • 생략 가능

3. begin

   • 실행부(구현부)
   • begin ~ end : 블럭{} 역할
   • 프로시저 구현 코드(구현부)를 작성하는 영역(메소드의 body 역할)
   • 생략 불가능
   • 업무 관련 코드 작성 : ANSI-SQL + 연산, 제어 추가(PL/SQL 구문)
   • try절 역할, 비지니스 코드

4. exception

   • 예외 처리부
   • catch절 역할
   • 예외 처리 코드를 작성하는 영역
   • 생략 가능

5. end

   • 실행부(구현부)
   • 생략 불가능
   • begin을 닫는 역할

PL/SQL

1. 자료형

   • ANSI-SQL과 동일(+확장)

2. 변수 선언하기

   • 변수명 자료형 [not null][default 값];
   • name varchar2(100) not null => ANSI-SQL 컬럼 선언
   • ANSI-SQL에서 테이블의 컬럼을 선언하는 방식과 유사
   • 변수는 주로 질의의 결과(★★★)나 인자값을 저장하는 용도로 사용
     • select를 변수에 저장할 수 있다.

3. 대입 연산자

   • 컬럼명 = 값 //ANSI. update
   • 변수명 := 값 //PL/SQL

dbms_output.put_line -- 기본적으로 꺼진 상태

set serveroutput on;  -- 켜기 (매번 로그인할때마다 켜야함)
set serveroutput off; -- 끄기

set serveroutput on;

declare
	-- 선언
    num number; 		-- 숫자형
    name varchar2(30);  -- 문자형
    today date; 		-- 날짜형
begin
	-- 숫자형
    num := 10;
    dbms_output.put_line(num); -- syso과 같은 역할(화면출력)
    
    -- 문자형
    name := '홍길동';
    dbms_output.put_line(name);
    
    -- 날짜형
    today := '2021-05-26'; -- 리터럴 -> 암시적 형변환O
    dbms_output.put_line(today);
    
    today := to_date('2021-05-26', 'yyyy-mm-dd'); -- FM(권장)
    dbms_output.put_line(today);
    
    today := sysdate;
    dbms_output.put_line(today);
    
end;

declare
    num1 number;
    num2 number default 200;
    num3 number not null := 300; -- 변수선언 + 값대입
    num4 number not null default 400;
begin

    dbms_output.put_line('num1: ' || num1); -- null 출력 
    dbms_output.put_line('num2: ' || num2); -- default 출력
    dbms_output.put_line('num3: ' || num3); -- 값 출력
    dbms_output.put_line('num4: ' || num4); -- default 출력

end;

질의의 결과? (select)

• 여태까지(ANSI-SQL만 사용) -> select 결과 -> 1차 소비(눈으로만 봄) + 폐기 => 제어가 존재하지 않아서..
• 지금부터 -> select 결과 -> 변수 저장 -> 1,2,3차 소비...가능

1. 1행 1열

   • 단일값
   • 1:1
   • 컬럼 1개 -> 변수 1개에 저장

2. 1행 N열

   • 복합값
   • N:N
   • 컬럼 N개 -> 변수 N개에 저장

3. N행 1열(레코드가 여러개)

   • 다중값 -> 커서 사용

4. N행 N열

   • 다중 + 복합값 -> 커서 사용

select into절

• select의 결과값을 변수에 저장하는 구문

-- 홍길동 어느 부서? -- 기획부
select buseo from tblInsa where name = '홍길동'; -- ANSI-SQL, 결과값 단일값


declare
    vbuseo varchar2(15); -- 부서를 저장할 변수 1개
begin

    -- PLS-00428: an INTO clause is expected in this SELECT statement
    -- PL/SQL 블럭내부에서는 select의 결과를 반드시 변수에 저장해야 한다.
    -- 만약 변수에 저장하지 않는 일반 ANSI-SQL의 select문을 그냥 사용하면 에러가 발생한다.
    -- select buseo from tblInsa where name = '홍길동';
    
    -- select into 사용
    -- select 컬럼 into 변수
    -- ANSI-SQL의 결과를 PL/SQL로 옮겨담는 작업 중 하나(★★★)
    select buseo into vbuseo from tblInsa where name = '홍길동';
    
    dbms_output.put_line('결과: ' || vbuseo); -- PL/SQL 변수
    
    -- PLS-00201: identifier 'BUSEO' must be declared
    -- PL/SQL 블럭내부에서는 ANSI-SQL의 식별자(테이블명, 컬럼명 등)를 직접 사용할 수 없다.
    -- dbms_output.put_line('결과: ' || buseo); -- ANSI-SQL 식별자

end;

declare
    vname varchar2(15); -- 이름을 담을 변수
    vbuseo varchar2(15); -- 부서를 담을 변수
begin
    
    vname := '이순신';
    
    -- 기존의 ANSI-SQL을 구성하면서 PL/SQL 값을 상수로 사용할 수 있다.
    select buseo into vbuseo from tblInsa where name = vname;
    
    dbms_output.put_line(vbuseo);
    
end;

-- 총무부 몇명?
declare
    vcount number;
begin
    
    select count(*) into vcount from tblInsa where buseo = '총무부';
    
    dbms_output.put_line('총무부 직원수: ' || vcount || '명');
    
end;

-- select into
-- 1. 컬럼의 갯수 = 변수의 갯수 일치
-- 2. 자료형 일치
-- 3. 순서 일치

-- '홍길동'의 직위, 부서, 직위, 급여...
declare
    vname varchar2(15);
    vbuseo varchar2(15);
    vjikwi varchar2(15);
    vbasicpay number;
begin
	-- 순서와 갯수가 맞아야한다.
    select name, buseo, jikwi, basicpay into vname, vbuseo, vjikwi, vbasicpay from tblInsa 
        where name = '홍길동';
    
    dbms_output.put_line(vbuseo);
    dbms_output.put_line(vjikwi);
    dbms_output.put_line(vbasicpay);

end;