DB 구축과 프로그래밍

1. DB 구축과 프로그래밍 CASE 도구인 ERwin을 설치하고 셋업하는 방법을 익힌다. Erwin으로 DDL 스크립트를 생성하고 수작업으로 수정하는 방법을 익힌다. 뷰, 사용자 정의 함수, 저장 프로시저 등의 데이터베이스 프로그래밍 방법을 익힌다. 초기 데이터를 입력하는 방법을 익힌다.

2. DB 구축과 프로그래밍 • DDL 스크립트 생성 및 수정 • 데이터베이스 구축 • 데이터베이스 프로그래밍 • 초기 데이터 입력과 조회

3. 1. DDL 스크립트 생성 및 수정 • 데이터베이스 스키마를 대화식으로 직접 구축해버리면 누가 언제 어떤 절차를 밟아 스키마를 생성했는지 전혀 알 수 없게 된다. • 따라서 스키마 생성과 변경을 위해 DDL 스크립트를 작성하는 것이 반드시 필요하다. • 이 절에서는 ERwin으로 초기 DDL 스크립트를 자동으로 생성하고, 이것을 수작업으로 수정하는 방법에 대해 알아본다. • 개요

4. 1. DDL 스크립트 생성 및 수정 • ERwin의 물리적 모델로부터 DDL 스크립트를 생성하기 위한 절차를 익혀보자. • Erwin의 물리적 모델에서 [Tools | Forward Engineer/Schema Generation...]을 선택한다. [그림 14-1]과 같은 Options 페이지가 나타난다. • DDL 스크립트 생성

5. 1. DDL 스크립트 생성 및 수정 • DDL 스크립트 생성

6. 1. DDL 스크립트 생성 및 수정 • 가운데 목록상자에서 DDL 스크립트를 생성하기 위한 옵션을 설정 하는데, DDL 스크립트를 정확하게 생성하기 위해 이 옵션 설정은 매우 중요하다. 왼쪽 목록상자에서 한 항목을 선택한 후, 오른쪽 목록상자에서 설정하고자 하는 옵션을 선택한다. • DDL 스크립트 생성

7. 1. DDL 스크립트 생성 및 수정 • DDL 스크립트 생성

8. 1. DDL 스크립트 생성 및 수정 • [Report...] 버튼을 누르면 [그림 14-2]와 같은 SQL 스키마 보고서 대화상자가 나타난다. 생성할 스크립트 파일의 경로를 지정한 후(파일 확장자는 .sql을 권장한다) [저장] 버튼을 누른다. • DDL 스크립트 생성

9. 1. DDL 스크립트 생성 및 수정 • 그러면 ERwin이 스크립트 파일을 생성하는데, 만약 오류가 발생 한다면 메시지 상자가 나타날 것이고(이때는 모델을 수정한 후 스키마를 다시 생성해야 한다), 오류가 없다면 [그림 14-1]의 SQL Server 스키마 생성 대화상자로 돌아올 때 [OK] 버튼을 누른다. 이때 [그림 14-3]과 같은 기본 옵션 변경 안내 메시지 상자가 나타난다. • DDL 스크립트 생성

10. 1. DDL 스크립트 생성 및 수정 • Erwin에 의해 생성된 DDL 스크립트는 일부를 수정해야 데이터 베이스 구축에 바로 사용될 수 있다. 이는 데이터베이스를 구축하기 위한 마지막 준비 단계에 해당된다. • [코드 14-1]은 ERwin에 의해 생성되고 필자가 일부를 수정한 약식 병원 관리 프로젝트의 DDL 스크립트다. • DDL 스크립트 수정

11. 1. DDL 스크립트 생성 및 수정 • DDL 스크립트를 수정할 때는 데이터베이스 생성 및 사용, 인덱스 생성 순서 조정이나 코멘트 추가 또는 수정 그리고 편집(예: 연속된 빈 행 삭제) 정도에서 그쳐야 하며, 스키마 자체를 바꿔서는 안 된다. • 스키마를 변경하는 작업은 반드시 ERwin으로 돌아가 논리적 또는 물리적 모델에 대해서 수행해야 한다. 또한, 수정 전후의 스크립트를 반드시 독자적인 파일로 저장하여 각각에 대한 기록을 남겨야 한다. • DDL 스크립트 수정

12. 2. 데이터베이스 구축 • 이 절에서는 앞 절에서 생성하고 수정한 DDL 스크립트를 이용하여 데이터베이스를 구축하는 방법과 테이블(뷰)의 열 명세표를 생성하는 방법에 대해 알아본다. • 수정한 DDL 스크립트 파일을 쿼리 편집기에 로드하여 Ctrl＋E (또는 F5)를 눌러 스크립트 전체를 실행한다. • 만약 여기에서 오류가 발생하면 ERwin으로 돌아가 물리적 또는 논리적 모델을 수정한 후 DDL 스크립트 생성 / 수정 및 데이터베이스 구축 절차를 반복해야 한다. • 데이터베이스 구축

13. 2. 데이터베이스 구축 • 오류 없이 스키마만으로 데이터베이스가 구축되었다면 즉시 데이터 베이스를 백업해야 한다. 왜냐하면, 뒤이어 뷰, 저장 프로시저 등의 정의를 데이터베이스에 추가할 것인데, 만약 그 과정에서 잘못될 경우 언제든지 스키마만으로 구성된 데이터베이스로 돌아갈 수 있어야 하기 때문이다. • 백업 파일의 확장자는 데이터 파일의 .mdf, 트랜잭션 로그 파일의 .ldf와 일관성이 있으므로 .bdf(Backup Data File의 약자)로 하는 것이 좋다. • 데이터베이스 구축

14. 2. 데이터베이스 구축 • 약식 병원 관리 프로젝트에서는 다음의 명령문을 실행하여 데이터베이스를 백업했다. BACKUP DATABASE HIS TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL \Backup\23-01_초기 스키마.bdf‘ • 데이터베이스 구축

15. 2. 데이터베이스 구축 • ‘C:\Program Files\Microsoft SQL Server\MSSQL10 .SQLEXPRESS\MSSQL\Backup’은 기본 백업 폴더로, 일단 여기에 백업을 한 후 (필요하면 .ZIP 등의 파일로 압축해서) C: 이외의 드라이브에 있는 작업 폴더에 저장하는 것이 바람직하다. • 데이터베이스 구축

16. 2. 데이터베이스 구축 • 스키마만으로 데이터베이스가 구축되면 테이블(뷰)의 열 명세표를 생성하는 것이 후속 작업(뷰, 저장 프로시저 등의 작성)에 도움이 된다. • [코드 14-2]는 테이블(뷰)의 열 명세표를 생성하는 스크립트다. • 테이블(뷰)의 열 명세표 생성

17. 2. 데이터베이스 구축 • [코드 14-2] • 테이블(뷰)의 열 명세표 생성

18. 2. 데이터베이스 구축 • 테이블(뷰)의 열 명세표 생성

19. 2. 데이터베이스 구축 • 그리드 모드(Ctrl＋D로 설정)에서 [코드 14-2]를 실행하면 결과가 [그림 14-4]와 같이 표시되는데, 결과 창에서 마우스의 오른쪽 버튼을 클릭하고 [다른 이름으로 결과 저장...]을 선택하면 [그림 14-5]와 같은 표 형태 결과 저장 대화상자가 나타난다. • 테이블(뷰)의 열 명세표 생성

20. 2. 데이터베이스 구축 • 테이블(뷰)의 열 명세표 생성

21. 2. 데이터베이스 구축 • 파일 형식을‘CSV(쉼표로 구분)(*.csv)’로 지정한 후 파일을 저장한다. 그리고 이 파일을 마이크로소프트 엑셀(또는 다른 스프레드시트 응용 프로그램)에서 연 후, 필드 폭 등을 포맷한 후 .XLS 형식으로 저장한다. • 테이블(뷰)의 열 명세표 생성

22. 3. 데이터베이스 프로그래밍 • 이 절에서는 뷰 작성, 저장 프로시저 작성, 개체 목록표와 매개변수 목록표 생성 방법에 대해 알아본다. • HIS 데이터베이스의 세 개의 테이블을 조인하여 모든 열들을 포함 시킨 뷰를 만들기로 한다. • 이 뷰는 모든 정보를 한 눈에 볼 수 있는 것으로,‘ 15장. 웹 보고서 작성’에서도 이용되는 것이다. • 뷰 작성

23. 3. 데이터베이스 프로그래밍 • 뷰 작성

24. 3. 데이터베이스 프로그래밍 • 뷰 작성

25. 3. 데이터베이스 프로그래밍 • 약식 병원 관리 프로젝트에서는 세 개의 저장 프로시저를 작성하기로 하자. • 하나는10장 2절(트랜잭션)에서 작성했던 usp_ErrorLog고, 나머지 두 개는 초기 데이터 입력에 필요한 usp_gen_treatment와 usp_updt_disease다. • 저장 프로시저 작성

26. 3. 데이터베이스 프로그래밍 • 앞에서 테이블(뷰)의 열 명세표를 생성했듯이, 이 시점에서 개체 목록표와 매개변수 목록표를 생성하는 것이 후속 작업(초기 데이터 삽입과 테스트)에 도움이 된다. • 개체 목록표 : 데이터베이스 내의 주요 개체들(사용자 테이블, 뷰, 사용자 정의 함수, 인라인 테이블 함수, 저장 프로시저, 트리거)의 목록을 표로 만든 것이다. • 매개변수 목록표 : 저장 프로시저와 그 매개변수들의 목록을 표로 만든 것이다(매개변수가 없는 저장 프로시저는 제외된다). • 개체 목록표 및 매개변수 목록표 생성

27. 4. 초기 데이터 입력과 조회 • 이제 데이터베이스 구축과 프로그래밍이 끝났으며, 운영 준비를 수행할 단계이다. • 이를 위해서 가장 중요한 것은 초기 데이터를 입력하는 것이며, 앞에서 작성해놓은 뷰를 이용하여 이 데이터들을 조회해보아야 한다. • 초기 데이터: 데이터베이스를 정상적으로 운영하기 전에 미리 입력해 놓는 데이터다. • 초기 데이터 입력

28. 4. 초기 데이터 입력과 조회 • 초기 데이터는 크게 두 가지로 구분된다. • 읽기 전용 데이터 : 반드시 초기 데이터로 입력해야 한다 (예: 우편번호부). • 개발 과정에 각 테이블에 소량(3∼10행 정도)을 넣어 두는 데이터 : 테스트 시간을 절감할 수 있을 뿐만 아니라 개발 기간도 단축할 수 있다. 개발이 완료되면 이와 관련된 초기 데이터는 없애고 납품(또는 운영을 시작)하면 된다. • 초기 데이터 입력

29. 4. 초기 데이터 입력과 조회 • 테이블에 초기 데이터를 입력하는 순서는 매우 중요하다. • [그림 14-8]의 ERD를 보자. • 초기 데이터 입력

30. 4. 초기 데이터 입력과 조회 • 초기 데이터를 아무렇게나 넣어서는 안 된다. - 예를 들어, 제일 먼저 E3 테이블에 넣을 수는 없다. 왜냐하면 E3에는 NOT NULL인 외래 키 E1_ID와 E2_ID가 있고, 여기에는 반드시 E1과 E2 테이블에 등록되어 있는 기본 키 값을 넣어야 하기 때문이다. • 초기 데이터를 넣어야 하는 순서는 다음과 같이 결정된다. { E1, E2 } → E3 → E5 → E4 • 초기 데이터 입력

31. 4. 초기 데이터 입력과 조회 • E1과 E2에는 특별한 우선순위가 없다. 결론적으로, 관계를 가지는 테이블들에서 부모 테이블에 먼저, 자식 테이블에 나중에 초기 데이터 를 넣으면 된다. • 만약 테이블이 많고 팀원 간에 업무 분담을 해서 초기 데이터를 넣는 경우라면, 자신의 부모 테이블에 어떤 기본 키 값이 있는지를 파악 한 후, 그 도메인에 있는 외래 키 값을 자신의 테이블에 넣으면 될 것이다(모든 기본 키에 IDENTITY 속성을 부여할 경우에는 도메인 파악이 더 쉽다). • 초기 데이터 입력

32. 4. 초기 데이터 입력과 조회 • 3절에서 테이블 세 개에서 최대한의 정보를 추출하는 vw_all 뷰를 작성하고 등록했었는데, 그 당시에는 초기 데이터가 없어서 결과에 열 제목들만 표시되었다([그림 14-6] 참고). • 이제는 초기 데이터가 모두 삽입되어 있으므로 vw_all 뷰를 조회하면 제대로 된 결과를 얻을 수 있다. • 2행의 조회문에서 ORDER BY 절을 사용하지 않으면 treat_id 키 값 순으로 정렬되는데, 필요하다면 ORDER BY 절을 사용하면 된다. 초기 데이터 입력까지 수행된 HIS 데이터베이스를‘28-01_InitData .bdf’로 백업한다. • 뷰 조회

33. 4. 초기 데이터 입력과 조회 • 초기 데이터 입력 1 USE HIS; 2 SELECT * FROM vw_all;