8주차 배운점 느낀점 - 저장 프로그램, PROCEDURE, CURSOR, FUNCTION, 데이터 모델링

8주차

 

8주차가 되면 슬슬 홀로서기 할 수 있어야한다고 하셨다. 내가 모르는게 있으면 도움많이 받았었다. 뒤로 갈 수록 , 본인 스스로 자기 혼자 해내갈 수 잇는 힘이 생긴다고하셨다. 혼자 해내는 습관을 기르자.

 

해야할게 있으면 밤좀 새고 주말에 자면되지 라고 하셨다. 맞다. 그날 할일을 다 못하면 잠좀줄여 봐야겠다.

 

많이 배운다고 중요한게 아니다. 어차피배울꺼 하나하나 잘 정리해라고 하셨다. 꾸준히만 하자.

 

저장프로그램을 배웠다. 프로시저를 보고 sql에서 쓰는 함수라고 생각했지만 더 함수같은게 있었다. 프로시저, 커서, 트리거를 배웠다.

 

데이터 모델링의 각각 단계별 개념과, 개체간의 관계를 표현하는 것을 배웠다.

 

배운점

 

저장 프로그램

 

저장 프로그램은 로직을 프로시저로 구현하여 객체형태로 사용한다.
저장 프로그램은 일반 프로그래밍에서 사용하는 함수와 비슷한 개념이다.
작업 순서가 정해진 독립된 프로그램 수행 단위이다.
프로시저가 정의된 다음 MySQL(DBMS)에 저장되어 저장프로그램이라고 명칭한다.
저장 프로그램은 저장 루틴(routine), 트리거(trigger), 이벤트(event)로 구성된다.
저장 루틴은 프로시저(procedure) , 함수 (function) 분류된다.

 

sinsaegebookdb.Book 테이블에 한개의 투플을 삽입하는 프로시저 : insertbook.sql

delimiter //
create procedure insertbook(
IN mybookid integer, in mybookname varchar(40), 
in mypublisher varchar(40), in myprice integer ) -- 변수선언, 외부 인자값 받겠다.
BEGIN
	insert into Book(bookid, bookname, publisher, price)values (mybookid, mybookname, mypublisher, myprice);
END;
// delimiter ;

call insertBook(13,'스포츠 과학','과학사',25000); -- 인자 

select * from book where bookid = 13 ;

 

제어문

 

delimiter : 구문 종료 기호를 설정
begin - end : 프로그램 문을 블록으로 묶음. {} , 중첩가능
조건의 검사 결과에 따라 문장을 선택적으로 수행 : IF - ELSE
LEAVE문을 만나기 전까지 반복 : LOOP 라벨: LOOP SQL문 (LEAVE) END LOOP;
WHILE문 : 조건이 참일 경우 WHILE 문의 블록을 수행 : WHILE(조건) DO SQL문 END WHILE;
REPEAT : 조건이 참일 경우 REPEAT의 블럭을 수행 REPEAT SQL문 UNTIL(조건) END REPEAT;
RETURN : 프로시저를 종료함, 상태값을 반환 RETURN [식]

 

동일한 도서가 있는지 점검한 후 삽입하는 프로시저 : bookinsertupdate.sql
show variables like 'sql_%'; 로 safe 꺼졌는지 확인가능하다

delimiter // -- 프로시저 시작과 종료 

create procedure bookinsertUpdate(
	IN mybookid integer, in mybookname varchar(40), 
	in mypublisher varchar(40), in myprice integer
)
begin
# 이름으로 해당 도서가 존재하는지 검사 
  DECLARE mycount integer; -- mybookname과 같은 책이 몇권이 있는지 확인하여 mycount변수에 저장(into)
  SELECT count(*) into mycount from book where bookname like mybookname; --
  
  if mycount != 0 then -- 동일한 거 갯수 if
	set sql_safe_updates = 0; -- safe 해제
	  update book set price = myprice
      where bookname like mybookname;
  else
    insert into book(bookid,bookname,publisher,price) values (mybookid, mybookname, mypublisher, myprice);
  end if;
END;
//
delimiter ; 

 set sql_safe_updates = 0;
 -- 축구의 역사 책의 가격을 10000으로 변경하세요
 call bookInsertUpdate(1, '축구의 역사', '굿스포츠', 10000);
 select * from book where bookname = '축구의 역사';
 -- 18, '최강야구', '야구나라', 15000 책을 저장하세요
 call bookInsertUpdate(18, '최강야구', '야구나라', 15000);
 select * from book where bookname = '최강야구';
 -- 새롭게 적용된 book 테이블을 조회하세요
 select * from book;

 

Book테이블에 저장된 도서의 평균 가격을 반환하는 프로시저 : averageprice.sql
out return 값
in 받는 매개변수

delimiter //
create procedure AveragePrice(out AverageVal integer) -- 출력값 out
begin
	select avg(price) into AverageVal
		from book where price is not null;
end
// delimiter ;

call AveragePrice(@myVal);
select @myVal;

 

커서 CURSOR

 

1씩증가하면서 다음행을 가리킨다.
SQL문의 실행 결과가 다중행일 경우 프로그램에서는 한 행씩 처리한다.
커서는 실행결과 테이블을 한번에 한 행씩 처리하기 위해 테이블의 행을 순서대로 가리키는 데 사용된다.
1.커서 생성 : CURSOR 커서이름 IS 커서 정의 , DECLARE 커서이름 CURSOR FOR
2.커서 사용 시작: OPEN 커서이름
3.행 데이터를 가져오기 : FETCH 커서 이름 INTO 변수
4.커서 사용 끝냄 : CLOSE 커서이름

 

커서를 사용하여 Orders테이블의 판매 도서에 대한 이익금을 계산하는 프로시저 : interest.sql
조건 : 도서 가격이 30,000원 이상이면 이익이 10%이고, 30,000 미만이면 5%
handler 예외 처리 같은거 continue, exit 옵션 쓸 수 있다.
CONTINUE 선택시 제일 뒤의 ' 처리할 문장'이 처리된다.

 

delimiter //
create procedure interest()
begin
# 변수 선언
    DECLARE myInterest integer default 0.0;  // 결과 값
    declare price integer;  // 
    declare endofRow BOOLEAN DEFAULT FALSE; // 행의 끝 여부
  
# 커서 생성
declare InterestC CURSOR FOR SELECT saleprice from orders;
// orders의 saleprice를 한행씩 커서로 가리킬 것
declare continue handler for not found set endofRow = TRUE;
// handler가 찾을게 없으면 endofRow TRUE로 할당

# 커서 오픈
open InterestC;

# 커서 Looping 하면서 Fetch 
cursor_loop: Loop // Loop 이름 cursor_loop 설정
	FETCH InterestC INTO price; // 커서 내용을 price에 담는다(saleprice)
	IF endofRow then leave cursor_loop;  //행이끝나면 loop를 break;
    end if;
    if price >= 30000 then set myInterest = myInterest + price * 0.1;
    // price가 30000 이면 myInterest(결과값)에 price *0.1 후 더해줌
    else 
		set myInterest = myInterest + price * 0.05;
        // price *0.05 를 결과값에 더해줌
	end if;


end loop cursor_loop; #Loop 종료
CLOSE InterestC; # 커서 종료
select concat('전체 이익금액 = ',myInterest); // 결과 출력

end;
// delimiter ;

CALL Interest(); //호출

 

트리거

 

데이터의 변경(insert,delete,update) 문이 실행될때 자동으로 같이 실행되는 프로시저이다. 보통의 트리거는 데이터의 변경문이 처리되는 세 가지 시점, 실행전(BEFROE), 대신하여(INSTEAD OF), 실행 후(AFTER) 동작

 

root 계정에서 트리거 작동에 필요한 문장을 실행 하여 야 한다.
SET global log_bin_trust_function_creators=ON;

 

BOOK_LOG테이블을 생성하고 트리거를 생성해준다. AFTER 옵션이기때문에 insert into가 실행된 후 트리거 문이 실행된다. BOOK_LOG테이블에 로그가 쌓인다.

SET global log_bin_trust_function_creators=ON; 

create table BOOK_LOG(bookid_log integer, bookname_log varchar(40), publisher_log varchar(40),price__log integer);
// 테이블 생성

delimiter //
Create trigger AfterInsertBook AFTER INSERT ON Book FOR EACH ROW
// Book 테이블에 insert가 발생하면 그 이후 each row 즉 각 행에 해당 트리거(Trigger)를 적용한다
BEGIN
	declare average INTEGER;
    INSERT INTO BOOK_LOG VALUES(new.bookid_log, new.bookname_log, new.publisher, new.price);
    
END;
// delimiter ;

insert into Book values(20,'아시안컵 축구 우승','대한민국축구왕',25000);
select * from BOOK_LOG where bookid_log = 20;

 

프로시저는 CALL 명령에 의해 실행되는 독립적인 프로그램

 

사용자 정의 함수
SELECT 문이나 프로시저내에서 호출되어 SQL문이나 프로시저에 값을 제공하는 용도로 사용
스칼라 함수 : Built-in 함수 (단일 값을 돌려주는 함수)
재사용성, 만들면 계속 쓸 수 있음

 

판매된 도서의 이익을 계산을 위해서, 각 주문 건별로 실제 판매가격인 saleprice를 입력 받아 가격에 맞는 이익 (30000이상 도서는 10%, 미만은 5%) 계산하여 반환하는 함수

SET global log_bin_trust_function_creators=ON;
-- 설정 후 function 생성 해야함

delimiter //
CREATE FUNCTION fn_Interest(price INTEGER) RETURNS INTEGER 
BEGIN
	DECLARE myInterest integer;
    if price >= 30000 then set myInterest = price*0.1;
    else set myInterest = price * 0.5;
    end if;
	RETURN myInterest;
RETURN myInterest;
END
// delimiter ;

SELECT custid, orderid, saleprice, fn_Interest(saleprice) interest from orders;

---

데이터 모델링

 

데이터 현실세계의 복잡한 개념들을 단순화한 내용을 추상화시켜서 데이터베이스에 저장하는 과정
개념적 모델 결과: 다이어그램
논리적 모델 결과: 테이블
물리적 테이블 : 하드웨어적인 내용
논리적 테이블 : CREATE 테이블

 

데이터베이스 생명주기(database life cycle)
데이터베이스의 생성과 운영에 관련된 특징

 

1.요구사항 수집 및 분석
사용자들의 요구사항을 듣고 분석하여 데이터베이스 구축의 범위를 정함

 

2.설계
분석된 요구사항을 기초로 주요 개념과 업무 프로세스 등을 식별하고(개념적 설계), 사용하는 DBMS의 종류에 맞게 변환(논리적 설계)한 후, 데이터베이스 스키마를 도출(물리적 설계)함

 

3.구현
설계 단계에서 생성한 스키마를 실제 DBMS에 적용하여 테이블 및 관련 객체(뷰, 인덱스 등)를 만듦

 

4.운영
구현된 데이터베이스를 기반으로 소프트웨어를 구축하여 서비스를 제공함

 

5.감시 및 개선
데이터베이스 운영에 따른 시스템의 문제를 관찰하고 데이터베이스 자체의 문제점을 파악하여 개선함

 

개념적 모델링
요구사항을 수집하고 분석한 결과를 토대로 업무의 핵심적인 개념을 구분하고 전체적인 뼈대를 만드는 과정

개체(entity)를 추출하고 각 개체들 간의 관계를 정의하여 ER 다이어그램(ERD, Entity Relationship Diagram)을 만드는 과정까지를 말함

 

업무의 (핵심개념)을 구분 전체적인 레이아웃을 만드는 과정
핵심개념
1) 개체를 추출
2) 개체들 사이의 관계 정의 - PK 표현
===================================> ER 다이어그램
ER 다이어그램

 

논리적 모델링
개념적 모델링에서 만든 ER 다이어그램을 사용하려는DBMS에 맞게 사상(매핑,mapping)하여 실제 데이터베이스로 구현하기 위한 모델을 만드는 과정
속성명 , PK

논리적 모델링 과정
1. 개념적 모델링에서 추출하지 않았던 상세 속성들을 모두 추출
2. 정규화 수행
3. 데이터 표준화 수행

 

물리적 모델링
워크밴치같은 툴을이용해서 들여다봄
작성된 논리적 모델을 실제 컴퓨터의 저장 장치에 저장하기 위한 물리적 구조를 정의하고 구현하는 과정
DBMS 특성에 맞게 저장 구조를 정의해야 데이터베이스가 최적의 성능을 낼 수 있음

물리적 모델링 시 트랜잭션, 저장 공간 설계 측면에서 고려할 사항
1. 응답시간을 최소화해야 한다.
2. 얼마나 많은 트랜잭션을 동시에 발생시킬 수 있는지 검토해야 한다.
3. 데이터가 저장될 공간을 효율적으로 배치해야 한다.

 

ER(Entity Relationship) 모델
세상의 사물을 개체(entity)와 개체 간의 관계(relationship)로 표현함

 

개체
독립적인 의미를 지니고 있는 유무형의 사람 또는 사물
개체의 특성을 나타내는 속성(attribute)에 의해 식별됨. 개체끼리 서로 관계를 가짐

 

ER 다이어그램
ER 모델은 개체와 개체 간의 관계를 표준화된 그림으로 나타냄

 

개체(entity)

사람, 사물, 장소, 개념, 사건과 같이 유무형의 정보를 가지고 있는 독립적인 실체
데이터베이스에서 주로 다루는 개체는 낱개로 구성된 것, 낱개가 각각 데이터 값을 가지는 것,
데이터 값이 변하는 것 등이 있음.
비슷한 속성의 개체 타입(entity type)을 구성하며, 개체 집합(entity set)으로 묶임.

 

 

개체 타입의 ER 다이어그램 표현
ER 다이어그램상에서 개체 타입은 직사각형으로 나타냄

 

 

개체 타입의 유형
강한 개체(strong entity) : 다른 개체의 도움 없이 독자적으로 존재할 수 있는 개체
약한 개체(weak entity) : 독자적으로는 존재할 수 없고 반드시 상위 개체 타입을 가짐, 부양가족은 회사에 직원이 존재해야함

 

속성(attribute) : 개체가 가진 성질

 

속성의 ER 다이어그램 표현
속성은 기본적으로 타원으로 표현. 개체 타입을 나타내는 직사각형과 실선으로 연결됨
속성의 이름은 타원의 중앙에 표기함
속성이 개체를 유일하게 식별할 수 있는 키일 경우 속성 이름에 밑줄을 그음

속성의 유형

 

관계(relationship)
개체 사이의 연관성을 나타내는 개념

 

관계 타입(relationship type)
개체 타입과 개체 타입 간의 연결 가능한 관계를
정의한 것이며, 관계 집합(relationship set)은 관계로 연결된 집합을 의미함
도서
고객을 주문이라는 관계로 연결함
고객은 주문이라는 관계를 통해서 같은 도서를 여러번 주문할 수 있다.
고객은 여러권의 주문을 통해서 도서를 주문할 수 있다.

학생은 한 개의 학과에 소속될 수 있다.
학과는 학생이 없어도 만들어질 수 있다. 등 고려
학생은 학기마다 강좌를 수강할 수 있다.

 

차수에 따른 유형
관계 집합에 참가하는 개체 타입의 수를 관계 타입의 차수(degree)라고 함

 

차수에 따른 관계 타입의 유형

 

1진 관계(recursive relationship) : 한 개의 개체가 자기 자신과 관계를 맺는 경우

 

2진 관계(binary relationship) : 두 개의 개체가 관계를 맺는 경우

 

3진 관계(ternary relationship) : 세 개의 개체가 관계를 맺는 경우
직원은 부품을 가지고 프로젝트를 진행한다.

 

관계 대응수(cardinality) : 두 개체 타입의 관계에 실제로 참여하는 개별 개체 수

 

 

일대일(1:1)관계
좌측 개체 타입에 포함된 개체가 우측 개체 타입에 포함된 개체와 일대일로 대응하는 관계
사원은 할당된 컴퓨터(아이피)를 사용한다.

 

 

일대다(1:N), 다대일(N:1) 관계
실제 일상생활에서 가장 많이 볼 수 있는 관계로, 한쪽 개체 타입의 개체 하나가 다른 쪽 개체
타입의 여러 개체와 관계를 맺음

 

다대다(N:M) 관계
각 개체 타입의 개체들이 서로 임의의 개수의 개체들과 서로 복합적인 관계를 맺고 있는 관계
한 학생은 여러개의 강좌를 수강할 수 있고
한 강좌는 여러명의 학생이 수강할 수 있다.

 

관계 대응수의 최솟값과 최댓값

 

관계 대응수 1:1, 1:N, M:N에서 1, N, M은 각 개체가 관계에 참여하는 최댓값을 의미함
관계에 참여하는 개체의 최솟값을 표시하지 않는다는 단점을 보완하기 위해 다이어그램에서
는 대응수 외에 최솟값과 최댓값을 관계실선 위에 (최솟값, 최댓값)으로 표기함

 

min: 관계에 참여하는 개체수가 적어도 이만큼 있다. max 넘길 수 없다.

 

관계 대응수의 최솟값과 최댓값의 표기
개체들이 참여하는 최대값

 

관계 대응수에 따른 관계 타입의 유형
학과와 학생 1:N이다. 학과는 학생을 볼때 (0,*) 학과는 학생이 없어도 됨
(1,1) 학생은 한개의 학과에 소속되어 있다. 1개 보다 많이 안된다.

 

ISA 관계
상위 개체 타입의 특성에 따라 하위 개체 타입이 결정되는 형태
promotion 관계, 실선 사용, 서브클래스 슈퍼클래스

 

참여 제약 조건
개체 집합 내 모든 개체가 관계에 참여하는지 유무에 따라 전체 참여와 부분 참여로 구분 가능
전체 참여는 개체 집합의 모든 개체가, 부분 참여는 일부만 참여함
전체 참여를 (최솟값, 최댓값)으로 표현할 경우 최솟값이 1 이상으로 모두 참여한다는 뜻이고, 부분 참여는 최솟값이 0 이상임
전체참여: 2줄, 부분 참여: 1줄

 

역할
개체 타입 간의 관계를 표현할 때 각 개체들은 고유한 역할(role) 담당

 

순환적 관계
무한루프
순환적 관계(recursive relationship) : 하나의 개체 타입이 동일한 개체 타입(자기자신)과 순환적으로 관계를 가지는 형태.

 

약한 개체(weak entity) 타입 : 상위 개체 타입이 결정되지 않으면 개별 개체를 식별할 수 없는 종속된 개체 타입
약한 개체 타입은 독립적인 키로는 존재할 수 없지만 상위 개체 타입의 키와 결합하여 약한 개체 타입의 개별 개체를 고유하게 식별하는 속성을 식별자(discriminator) 혹은 부분키(partial key)라고 함

 

1.직원객체는 직원번호 이름 직책을 가진다. 직원번호로 직원을 구분한다.
2.가족객체는 이름, 관계라는 속성을 갖는다. 직원은 부양이라는 관계로 가족을 등록시킬 수 있다.
단 직원번호 가족을 구분한다.PK를 넣지 않는다.

 

IE 표기법
ER 다이어그램을 더 축약하여 쉽게 표현하면 Erwin 등 소프트웨어에서 사용함
IE(Information Engineering) 표기법에서 개체 타입과 속성은 직사각형으로 표현함

IE 표기법에서 관계는 실선 혹은 점선으로 표기함
IE 표기법 – 관계와 관계 대응수
크로우풋 = 세발 표기법
점선: 강한개체 연결할 때, 실선: 약한개체 연결할 때
새발 : N , 동그라미: 0 선택참여, 1끄여있는거: 필수참여

 

 

IE 표기법에서 관계(강한관계, 비식별자 관계)는 점선으로 표기함
비식별자 관계
동그라미 : 부서는 직원을 0명이상 포함할 수 있다., 강한 개체 ,비식별자관계

식별자 관계
가족: 약한개체 , 실선 연결 / 하나의 직원 여러명의 가족을 포함할 수 있다. , 식별자관계

실습

1. 서점은 출판사에서 도서를 공급받아 판매한다.
2. 판매내역은 매일 기록으로 남겨야 한다. ==> 고객서비스와 매출관리에 활용
3. 서점은 판매하는 도서의 도서번호, 도서이름, 출판사, 도서단가를 갖는다.
4. 구매한 도서들은 납품한 출판사와 출판사 담당자이름, 전화번호를 갖는다.
5. 고객이 책을 주문할때마다 주문번호를 기록해야 한다.
6. 고객에게 판매한 도서는 판매한 날, 고객별 주문금액 추가로 관리해야한다.
7. 고객들은 여러 번에 걸쳐 다양한 도서를 구매할 수 있다.

개념설계/논리설계 (개체와 관계를 구분하여 정리한다.)
도서 (도서번호, 도서이름, 출판사, 도서단가) - E
고객 (고객번호, 고객이름, 주소, 전화번호) - E

출판사(출판사 이름, 담당자 이름, 전화번호) - E

 

출판사1 : 도서 N (서점은 출판사로부터 공급한 도서만 등록하여 관리) - 비식별자
고객 N: 도서 M - 식별관계 , 도서는 여러명의 고객에게 판매될 수 도 있고 , 실질적으로 표현하려면 식별관계를 만들어줘야한다. 주문이라는 관계에 대한 표현되어야한다.(도서와 고객의 PK)
주문관계 (교차 테이블):
고객이 도서를 구입한 날(주문일자), 구매한 가격(주문금액)을 따로 저장한다. 고객과 도서의 관계에 속성이 존재

 

 

 

회고

프로시저, 커서, 트리거를 배웠다. 자바에서 봤던 기능을 sql에도 구현되어있어서 신기했다. 이터레이터 처럼 커서를 이용해 select 에서 출력된 행들을 가리키고 루프를 돌면서 if를 사용해 원하는 작업을 수행할 수 있다.
트리거를 개념적으로

데이터의 변경문이 실행될때 자동으로 같이 실행되는 프로시저 정도로 알고 있었다. 직접 정의해서 원하는 문을 넣어 사용할 수 있음을 알았다. 배울게 얼마나 깊을게 더 있을까 싶다.
데이터 모델링에 대해서 정처기 공부할때 많이 봤지만 적혀있던 개념만 읽었을 뿐 자세히 알지는 못했다. 수업을 통해 각각 모델링개념과 관계를 잇는 예시를 많이 볼 수있어서 이해가 잘되었다.