제1정규형(1NF)

• 릴레이션의 모든 속성이 원자 값을 가진다.

• 릴레이션에서 속성의 값은 속성의 도메인에 속하는 단일 값이어야 한다는 제약을 의미한다.

  | 학번 | 이름 | 학과 |
  | ---------- | ------ | -------------------------- |
  | 2019100100 | 홍길동 | {컴퓨터공학과, 전기공학과} |
  | 2019100051 | 임꺽정 | 컴퓨터공학과 |
  | 2019155015 | 논개 | 전기공학과 |

• 학생은 복수 전공을 통해 최대 두 학과에 소속될 수 있기 때문에 학과 속성은 다치값을 갖는다.

• 이 릴레이션을 제1정규형으로 만드는 방법은 2가지가 있다.

  • 학과 속성의 다치값을 위해서 하나의 레코드를 생성하여 새 릴레이션을 만든다.
  • 제1정규형으로 변한된 학과 릴레이션

  | 학번 | 이름 | 학과 |
  | ---------- | ------ | ------------ |
  | 2019100100 | 홍길동 | 컴퓨터공학과 |
  | 2019100100 | 홍길동 | 전기공학과 |
  | 2019100051 | 임꺽정 | 컴퓨터공학과 |
  | 2019155015 | 논개 | 전기공학과 |

  • 기존의 릴레이션을 학생 릴레이션과 학과 릴레이션으로 분리한다.
  • 제1정규형으로 두개의 테이블로 분리된 학생 릴레이션

  | 학번 | 이름 |
  | ---------- | ------ |
  | 2019100100 | 홍길동 |
  | 2019100051 | 임꺽정 |
  | 2019155015 | 논개 |

  • 제1정규형으로 두개의 테이블로 분리된 학과 릴레이션

  | 학번 | 학과 |
  | ---------- | ------------ |
  | 2019100100 | 컴퓨터공학과 |
  | 2019100100 | 전기공학과 |
  | 2019100051 | 컴퓨터공학과 |
  | 2019155015 | 전기공학과 |

제2정규형(2NF)

• 완전 함수 종속성 개념에 기반을 둔다.

• 아래 예시 릴레이션은 공급자번호+부품번호가 기본키이다.

• 부품 공급 릴레이션

  | 공급자번호 | 부품번호 | 운송거리 | 소재지 | 수량 |
  | ---------- | -------- | -------- | ------ | ---- |
  | S1 | P1 | 46 | 수원 | 300 |
  | S1 | P2 | 46 | 수원 | 200 |
  | S1 | P3 | 46 | 수원 | 400 |
  | S1 | P4 | 46 | 수원 | 200 |
  | S1 | P5 | 46 | 수원 | 100 |
  | S1 | P6 | 46 | 수원 | 100 |
  | S2 | P1 | 164 | 대전 | 300 |
  | S2 | P2 | 164 | 대전 | 400 |
  | S3 | P2 | 302 | 대구 | 200 |
  | S4 | P2 | 329 | 광주 | 200 |
  | S4 | P4 | 329 | 광주 | 300 |
  | S4 | P6 | 329 | 광주 | 400 |

• 삽입이상

  • 공급자가 S5가 부산에 위치한다는 데이터를 저장하고 싶은 경우, 이 정보를 삽입할 수 없다.
  • 릴레이션의 기본키가 (공급자번호, 부품번호)로 구성되어 있는데 (S5, 부산) 데이터에는 부품번호가 포함되어 있지 않기 때문이다.

• 삭제이상

  • S3에 대한 레코드는 하나뿐인데 S3가 부품 P2를 공급한다는 사실을 삭제할 경우, S3가 대구에 위치하는 사실도 삭제된다.
  • 공급자 S3가 대구에 위치하는 사실은 S3의 부품공급 여부와 관계없이 사용자가 보존해야하는 필요한 정보이지만 삭제가 된다.

• 갱신이상

  • S1의 소재지는 수원이다. 만약 S1이 전주로 이주한다면 S1의 모든 레코드가 갱신되어야 한다.
  • 극단적인 경우 S1의 소재지가 수원과 전주 두곳이 되는 일관성 없는 데이터가 발생할 수 있다.

MySQL 트리거

• 참고 : 위키
• 트리거(Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다.
• 트리거를 사용하면 데이터 무결성을 지킬수 있다. => 연관된 테이블 간의 데이터 일관성을 유지할 수 있다.
• 트리거에는 크게 행 트리거와 문장 트리거의 두 종류가 있다.
  • 행 트리거 : 테이블 안의 영향을 받은 행 각각에 대해 실행된다. 변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다.
  • 문장 트리거 : INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다.
• 트리거의 속성
  • BEFORE 또는 AFTER : 트리거가 실행되는 시기를 지정
  • INSTEAD OF : 트리거를 원래 문장 대신 수행
  • WHEN : 트리거를 시작하는 조건식을 지정
• 트리거는 INSERT, UPDATE, UPDATE OF, DELETE의 경우에 시작되고 SELECT 문에 의한 데이터 검색에 영향을 미칠 수 없다.

MySQL 트리거

• INSERT, UPDATE, DELETE의 경우에 트리거 지원

• MySQL은 각 테이블에 각 형태의 다양한 트리거를 허용한다.

  • A 테이블에 INSERT를 처리하는 트리거를 여러개 생성할 수 있다

• 문법(참고 : MySQL)

•  

  DELIMITER //
  CREATE
      [DEFINER = user]
      TRIGGER trigger_name
      trigger_time trigger_event
      ON tbl_name FOR EACH ROW
      [trigger_order]
      BEGIN
      trigger_body
      END //
      /*
      trigger_time: { BEFORE | AFTER }
  
      trigger_event: { INSERT | UPDATE | DELETE }
  
      trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
      */
  DELIMITER ;
  

간단한 예제

• green 데이터베이스를 생성하여 class 테이블과 course 테이블을 생성하였다.

테이블 생성

• class 테이블 : 강의 테이블로 강의번호, 강의명, 총 수강생 수를 가지고 있다.

• CREATE TABLE `class` (
    `no` int(11) NOT NULL,
    `name` varchar(45) DEFAULT NULL,
    `total` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`no`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

• course 테이블 : 수강 테이블로 수강번호, 강의번호, 학생번호를 가지고 있으며 강의번호를 외래키로 지정하였다.

• CREATE TABLE `course` (
    `no` int(11) NOT NULL,
    `class_no` int(11) NOT NULL,
    `student_no` int(11) NOT NULL,
    KEY `class_no_idx` (`no`),
    CONSTRAINT `class_no` FOREIGN KEY (`no`) REFERENCES `class` (`no`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

• input_course 트리거 : 학생이 수강에 등록되면 해당 강의번호의 총 수강생 수가 증가해야 하기 때문에 이를 트리거로 생성하였다.

트리거 생성

• use green;
  DROP TRIGGER IF EXISTS input_course;
  DELIMITER //
  CREATE TRIGGER input_course AFTER INSERT ON course
  FOR EACH ROW 
  BEGIN
  declare _total int default 0;
  set _total = (select count(*) from course where new.class_no = class_no);
  update 
      class
  set
      total = _total
  where
      no = new.class_no;
  END//
  DELIMITER ;
  

트리거 확인

• show triggers;

테스트

• insert into class(no,name,total) values(1,'컴퓨터공학',0);
  insert into course(no,class_no,student_no) values(1, 1, 2019160135);
  SELECT * FROM green.class;

• class 테이블에 total에 1이 입력된 것을 확인 할 수 있다.

트리거와 프로시저

1. 트리거 이벤트(INSERT | UPDATE | DELETE)가 실행된 테이블을 트리거를 통해 수정하려 하면 에러 발생 > 이러한 경우에는 프로시저로 처리해야함
   • 예를 들어 A 테이블에 INSERT 트리거를 생성하는 과정에서 트리거 처리에서 A테이블을 UPDATE를 하게 되는 경우 에러가 발생한다. 이러한 경우에는 프로시저로 처리해야한다.
2. 트리거는 매 이벤트(INSERT | UPDATE | DELETE)마다 동일하게 처리하여 적용하는 경우 사용하고 프로시저는 그렇지 않은 경우 사용한다
   • 예를 들어 통신사가 회원 등급을 결정할 때 전년도 사용 비용을 기준으로 처리하기 때문에 회원 등급을 결정하는 처리를 트리거가 아닌 프로시저로 해야한다.

MySQL 프로시저

• 프로시저는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

• 프로시저는 다음과 같은 장점이 있다.

  1. 하나의 요청으로 여러 SQL문을 실행할 수 있다.(네트워크에 대한 부하를 줄일 수 있다.)
  2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
  3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다. 간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
  4. 보수성이 뛰어 나다.

• 프로시저는 다음과 같은 단점이 있다.

  1. 코드 자산으로서의 재사용성이 나쁘다.
  2. 업무의 사양 변경 시 외부 응용 프로그램과 할께 프로시저의 정의를 변경할 필요가 있다.

MySQL 프로시저 목록 확인

mysql> show procedure status;

MySQL 프로시저 스크립트 확인방법

mysql> show create procedure 프로시저명;

MySQL 프로시저 정의

• 예시 : 유저 정보 확인하는 프로시저

• 예시 : 강의를 수강하는 학생 정수를 입력시 학점을 계산하는 프로시저

프로시저 호출

CALL 프로시저명(매개변수들);

• 예시 : 학번이 2019160160이고 수강 과목 번호가 1번인 데이터가 있다고 가정

CALL calculate_grade(40.0, 30.0, 9,9,1,2019160160); SELECT * FROM course;

용어 정리

DELIMITER

• 프로시저나 트리거에서 사용된다.
• 프로시저를 생성할 때 프로시저 안에 있는 쿼리들이 ;으로 인해 실행되면 안되기 때문에 이를 막기 위해 DELIMITER를 이용하여 DELIMITER를 이용하여 지정된 문자가 나타나기 전까지는 ;을 만나도 실행되지 않게 막아준다.

IN

• 프로시저를 호출하기 위해 필요한 정보들로 함수의 매개변수(인자)에 해당한다.

DECLARE

• 프로시저 내부에서 사용하는 변수를 선언할 때 사용한다.

SET

• 변수의 값을 설정할 때 사용한다.

IF 조건식 THEN 실행문 ELSE 실행문 END IF;

• if문에 해당하며 유의사항으로 else if문을 처리할 땐 다음과 같이 처리해야 한다.