2주 합격 도전 - Day4

3과목 - 데이터 베이스

데이터 베이스 개요

데이터베이스 정의

• 시스템이 공용으로 사용하고 관리하는 데이터 집합
• db 도입 배경
  독립되 파일 단위로 데이터를 저장하게 되면 데이터 종속성 및 중복성이 높아 무결성 위배 가능성 높을 수 있음 이러한 단점을 보안하고자 여러 시스템이 공용으로 데이터를 모아 관리하는데 db를 구축하게 됨
• DB 장단점
  장점 : 종속성 및 중복성 최소화, 일관성 및 무결성 유지, 공유 및 보안성 강화, 실시간 처리 가능
  단점 : 전문가 부족, 전산화 비용 증가, 시스템이 복잡하고 느림, 파일 회복이 어려움

데이터 베이스 관리 시스템 DBMS

• 사용자와 db사이에서 사용자의 요구에 따라 DB생성, 관리해주는 S/W
• 사용자 ←→ DBMS ←→ DB
• 사용자 : DBA(DB관리자), 일반 사용자, 응용 프로그램
• DBMS종류 : 계층형, 네트워크형, 객체지향형, 관계형
• DBMS 프로그램 : Oracle, MySQL, Access, My SQL 등

DBMS 기능 및 언어

• DDL(정의어) : DB를 생성하거나 자료 형태와 구조 등을 수정하여 데이터를 이용하는 방식을 정의하는 기능 (create, alter, drop)
• DML(조작어) : 데이터의 검색, 삽입, 삭제, 변경 등을 처리하는 기능
  (select, insert, delete, update)
• DCL(제어어) : 데이터의 무결성을 유지하기 위한 보안 및 권한 검사, 병행 제어 등의 기능을 정의하는 기능 (commit, rollback, grant, revoke)

관계형 Relational-DBMS = RDBMS

• 테이블을 이용한 구조
• 테이블 : 표, 개체 또는 릴레이션
• 필드 : 속성, 열
• 레코드 : 튜플, 행
• 도메인 : 하나의 속성에서 취할 수 있는 원자값의 범위
• 필드의 개수 : 차수(degree)
• 레코드의 개수 : 기수(cardinality)

테이블의 특징

• 속성(필드)과 튜플(레코드)들은 유일하며 순서가 무관함
• 속성은 값은 분해할 수 없음
• 속성의 값은 동일할 수 있음
• 튜플은 삽입, 삭제 등에 의해 계속 변함
• 튜플은 식별하기 위해 속성(필드)의 일부를 key로 설정함

데이터 베이스 설계

키 Key

• 후보키 (candidate key) : 유일성, 최소성이 모두 만족되는 속성
• 기본키 (primary key) : 후보키 중 선정(중복, null 안됨 : 개체 무결성)
  기본키는 반드시 설정할 필요는 없음
  OLE 개체, 첨부 파일의 데이터 형식은 기본키 불가
  여러 개의 필드에 설정 가능 (슈퍼키)
• 대체키 (alternate key) : 기본키가 아닌 후보키
• 슈퍼키 (super key) : 두 개 이상의 속성을 연결하여 유일성 만족
  유일성 만족, 최소성 x
• 외래키 (foreign key) : 관계가 정의되어 참조된 다른 테이블의 기본키

데이터베이스의 설계

• 1단계 개념적 설계 : 요구사항을 분석하여 E-RD(Entity-Relationship Diagram)와 같은 개념적 구조 설계
• 2단계 논리적 설계 : DBMS에 적합한 테이블의 구조로 설계
• 3단계 물리적 설계 : DBMS로 구현 가능한 물리적 구조 설계 (H/W)

ERD(개체 관계 다이어그램)

• 개체와 관계를 중심으로 시각적인 모델링을 함

정규화

• 이상 현상이 일어나지 않도록 분해하는 과정
• 중복, 종속성을 최소화하기 위한 작업
• 논리적 설계 단계에서 수행
• 속성 수가 적은 테이블로 분할되어 관리가 쉬어짐
• 분할이 많으면 데이터 연결이 복잡해져 효율이 떨어짐
• 완벽하게 중복을 제거할 수 없음
• 이상현상
  삽입 이상 : 불필요한 데이터가 함께 삽입되는 현상
  삭제 이상 : 필요한 데이터가 함께 삭제되는 현상
  갱신 이상 : 일부만 수정되어 데이터의 불일치 발생

데이테베이스의 구조 (스키마)

• 외부 스키마 : 일반 사용자나 응용프로그래머의 관점에서 본 스키마
  (서브 스키마= 사용자 뷰)
• 개념 스키마 : DB의 전체적인 논리적 구조이며 접근 권한, 보안정책, 무결성 규칙에 대한 정의 (스키마)
• 내부 스키마 : DB 전체적인 물리적 구조 시스템 설계자의 관점에서 본 스키마 (물리스키마)

테이블 디자인

테이블 보기

• 디자인 보기 : 테이블의 구조 및 필드 속성 변경
  한 개 이상의 필드를 선택하여 기본키로 설정할 수 있음
  필드의 설명에 입력한 내용은 테이블 구조에 영향을 미치지 않고 상태 표시줄에 표시
  컨트롤 표시 속성은 텍스트상자, 목록상자, 콤보상자 중 선택 가능
• 데이터시트 보기 : 레코드 입력 및 수정

데이터 형식

• 짧은 텍스트 : 텍스트 + 숫자 = 255자
• 긴 텍스트 : 텍스트 + 숫자 = 63999자
• 숫자
  바이트 1byte
  정수 2byte
  정수(long) 4byte
  정수(single) 4byte
  실수(double) 8byte
• 날짜/시간 : 100년 ~9999년 까지 입력 가능 8byte
• 통화 : 정수 15, 소수 4자리까지 입력 가능 8byte
• 일련 번호 : 자동으로 번호가 부여됨, 데이터가 입력된 후에는 선택 불가, 삭제된 일련 번호는 다시 부여되지 않음, 정수(long) 4byte, 복제ID 16byte
• YES/NO : YES/No. True/False, On/Off 선택 가능 1bit
• OLE 개체 : 개체 연결 포함, 기본키 설정 불가능 1GB
• 첨부 파일 : 파일 첨부(기본키 설정 불가)

조회 속성

• 짧은 텍스트, 숫자, YES/NO만 설정 가능
• 콤보 상자 및 목록상자 적용 가능
  행 원본 유형 : 테이블/쿼리, 값 목록, 필드 목록
  행 원본 : 원본의 값 입력
  바운드 열 : 실제로 저장되는 열
  열 개수 : 열 개수
  열 이름 : 열 이름 표현 여부
  열 너비 : 열 너비 설정
  행 수 : 행 개수
  목록 너비 : 전체 너비
  목록 값만 허용 : 목록 값만 입력할 수 있도록 설정

필드 속성

필드 속성

• 형식 : 엑셀의 표시 형식과 같음
• 캡션 : 데이터 시트에서 보여지는 레이블(제목)
• 기본값 : 자동으로 입력되어 있는 기본값
• 필수 : 필수로 입력하는 속성
• 빈 문자열 허용 : 빈 문자열을 허용하는 속성
• IME 모드 : 자료입력형태(한글, 영어)를 설정
• 소수 자릿수 : 소수점 이하 자릿수 결정

유효성 검사

• 입력된 데이터가 유효한지 검사하는 기능
• 유효성 검사 텍스트 를 통해 오류 메시지 표현 가능
• 유효성 검사할 때 입력이 저장되지는 않음

인덱스

• 중복 가능, 중복 불가능 등을 지정 가능
• 여러 필드에 사용 가능
• OLE, 첨부 파일 형식은 인덱스 설정 불가
• 정렬 및 검색 속도가 빨라지지만 전체적 업데이트 속도가 느려짐

입력마스크

• 정확한 데이터가 입력될 수 있도록 해주는 틀
• 입력마스크; 저장방법; 표시문자
• 숫자 : 필수 = 0 / 선택 = 9
  문자 : 필수 = L / 선택 = ?
  문자+숫자 : 필수 = A / 선택 = a
  숫자+기호 : #(선택)
  대문자 : >
  소문자 : <
  암호 : Password

SQL 언어

외부 데이터

• 가져오기
  엑셀, 엑세스, ODBC, 텍스트, XML, HTML 파일 지원
  새 테이블, 레코드 추가, 연결 테이블로 가져오기 가능
  테이블 형태로만 가져올 수 있음
  레코드를 수정하며 가져올 수 없음
• 내보내기
  엑셀, 엑세스, ODBC, 텍스트, XML, HTML, 전자메일, PDF파일 지원

SQL (Structed Query Language)

데이터 베이스를 이용할 때 사용하는 언어 (질의어)

데이터 제어어 DCL

• commit(완료, 승인), rollback(취소), grant(권한부여), revoke(권한회수)

데이터 정의어 DDL

• create(생성), create table 테이블명(필드 형식);
• alter(구조 변경)
  alter table 테이블명 add 필드 형식;
  alter table 테이블명 drop 필드;
• drop(삭제)
  drop table 테이블명;

데이터 조작어 DML

- SELECT 선택, 조회

select 필드 from 테이블 where 조건;
as(alias 약자로) 별칭 붙일 때 사용
자료를 묶을 때는 group by 사용
group by 의 조건은 having 사용
order by 정렬 asc : 오름차순(생략) / desc : 내림차순

• select option 필드 from 테이블 where 조건 group by 그룹 having 조건 order by 정렬;
  option : distinct(중복제거) / top10(상위 10개 항목)
  avg : 평균 / count (들어있는 개수)
  or / and
  날짜는 #날짜# 형식으로 작성해야 함
  not : 부정문 / is null : 빈칸 검색
  매개변수 : 사용자에게 입력 받는 값, [] 로 묶어 표현

INSERT 삽입

INSERT INTO 테이블 (필드, 필드) VALUES (값, 값);

UPDATE 수정

UPDATE 테이블 SET 필드 WHERE 조건;

DELETE 삭제

DELETE FROM 테이블 WHERE 조건;

쿼리 마법사

• 단순 쿼리 마법사
• 중복 데이터 쿼리 마법사
• 불일치 검색 쿼리 마법사 (테이블이 2개 이상 필요)
• 크로스탭 쿼리 마법사
  엑셀의 피벗테이블과 유사
  열 머리글, 행 머리글, 값 등으로 구성
  열 머리글은 1개만 가능, 행 머리글은 3개까지 가능

폼 속성

폼 개요

• 테이블, 쿼리, SQL을 원본으로 하여 데이터의 입력, 편집 등을 편리하게 해주는 개체
• 컨트롤 마법사를 활용해 쉽게 컨트롤 생성 가능
• 컨트롤 이름은 중복 불가
• 폼과 원본을 바운드하고 폼에서 데이터를 수정하면 원본데이터도 수정됨

폼 속성

• 팝업 : 액세스 창 위에 팝업 형태로 폼이 열림
• 모달 : 폼이 열려있는 경우 다른 창을 선택할 수 없음
• 레코드 잠금 : 여러 사용자가 동시에 레코드를 편집할 수 없도록 잠금
• 탐색 단추 : 폼 아래 표시된 탐색 단추 표시
• 기본 보기
  단일 폼 : 하나의 레코드만 표현
  연속 폼 : 여러개의 레코드 표현
  데이터 시트 : 엑셀 화면과 비슷하게 표현
  분할 표시 폼 : 폼 보기와 데이터시트 보기를 동시에 표시하고 폼과 데이터시트가 동기화되며, 위/아래/왼/오른쪽 설정 가능

컨트롤 속성

• 탭 인덱스
  컨트롤이 만들어진 순서대로 탭 지정
  자동순서 : 위에서 아래, 왼쪽에서 오른쪽 지정
  탭 정지 속성이 '예'로 되어있어야만 포커스가 이동

하위 폼, 보고서

• 폼 및 보고서에 들어있는 또 하나의 폼 및 보고서
• 하위 폼, 보고서는 독립된 개체로 열거나 편집 가능
• 일대다 관계에서 효과적으로 표현
• 연결할 필드의 형식은 같거나 호환되어야 함 (관계 참고)

보고서 속성

보고서 개요

• 테이블, 쿼리, SQL을 원본으로 하여 데이터의 조회, 출력 등을 편리하게 해주는 개체
• 보고서 도구 및 마법사를 이용해 쉽게 보고서 생성 가능
• 레코드 원본에서 쿼리 작성기를 이용해 여러 개의 테이블을 원본으로 사용 가능
• 폼과 동일하게 컨트롤을 표현할 수 있으나 데이터의 입력, 편집은 불가능
• 보고서 마법사를 통해 원하는 필드들을 손쉽게 선택하여 레코드 원본으로 지정할 수 있음
• 속성 시트의 레코드 원본 드롭다운 목록에서 테이블이나 쿼리를 선택하여 지정할 수 있음
• 보기 상태
  인쇄 미리보기 : 인쇄 전 보는 화면, 페이지 구분됨 (편집 안됨)
  레이아웃 보기 : 컨트롤의 레이아웃(배치)만 하는 화면
  디자인 보기 : 내용 및 속성 등을 변경하기 위한 화면
  보고서 보기 : 보고서 내용 확인, 페이지 구분 안됨
• 인쇄미리보기에서는 출력되는 모양을 표시, 인쇄를 위한 페이지 설정이 용이
• 레이아웃보기는 출력될 보고서의 레이아웃을 보여주며 컨트롤 크기 위치를 변경할 수 있음
• 디자인 보기는 보고서에 삽입된 컨트롤의 속성, 맞춤, 위치 등을 설정할 수 있음

컨트롤 속성

• 폼 및 보고서에서 레코드 원본의 테이블 및 쿼리의 필드를 추가하면 텍스트 상자로 바운드됨
• 수식 컨트롤로 사용하려면 =로 시작함
• 누적 합계 : 누계 표시 가능(순번 표현 시 '=1'로 표시)
• 중복 내용 숨기기 : 이전 레코드와 같으면 숨기기

그룹화 및 정렬

• 그룹화 기준이 되는 필드는 자동으로 정렬되어 표시됨
• 그룹 머리글 또는 그룹 바닥글 중 하나 이상 선택 가능
• 그룹 머리글은 첫 번째 레코드 위에 표시되고, 그룹 바닥글은 마지막 레코드 아래 표시
• 필드나 식을 기준으로 10개까지 그룹화 가능
• 날짜 시간 : 연도, 분기, 월, 주, 일, 시간, 분 별로 그룹 가능
• 문자 : 첫 문자, 처음 두 문자, 사용자 지정으로 그룹 가능
• 숫자 : 일정한 간격으로 그룹 가능

구역과 수식 활용

폼, 보고서 구역

• 보고서 머리글 : 보고서 처음 한 번 출력
• 페이지 머리글 : 매 페이지 상단 출력
• 그룹 머리글 : 그룹 첫 번째 레코드 위 출력
• 본문 : 모든 레코드 출력
• 그룹 바닥글 : 그룹 마지막 레코드 아래 출력
• 페이지 바닥글 : 매 페이지 하단 출력
• 보고서 바닥글 : 보고서 마지막 한 번 출력

액세스 함수

• AVG(인수) : average함수와 같음
• COUNT(인수) : counta 와 같음
• IIF(조건, 참, 거짓) = IF 와 같음 (false 0 / true 1)
• 피제수 MOD 제수 : MOD(피제수, 제수)와 같음
• FORMAT(인수, 표시형식) : TEXT와 같음
• D함수("필드", "테이블", "조건") : D함수와 같음

페이지 번호

• [page] : 현재 페이지 번호
• [pages] : 전체 페이지 번호

조건부 서식

• 조건을 만족하면 지정된 서식을 적용하는 기능
• 필드 값, 식, 다른 레코드 비교 등으로 생성 가능
• 조건부 서식의 순서를 바꿀 수 있고, 상위 항목의 우선 순위가 높음
• 텍스트 및 콤보 컨트롤에만 50개까지 조건부 서식 가능
• 다른 파일 형식으로 내보내기 시 조건부 서식 유지 안됨
• 규칙 유형에서 다른 레코드와 비교 선택 시 막대 형식으로 설정 가능
• 지정한 조건 중 두 개 이상이 true면 true인 첫 번째 조건의 서식만 적용됨

매크로

액세스의 매크로

• 반복적인 명령, 작업을 자동화하여 빠르게 수행하는 기능
• 엑셀과 다르게 기록이 아닌 매크로 함수를 추가하여 생성
• 여러개의 매크로 함수 포함 가능
• 폼 및 보고서 컨트롤 이벤트에 포함하여 실행 가능
• 폼은 테이블이나 쿼리 데이터의 입출력 화면을 작성함
• 매크로는 복잡한 작업을 처리하기 위해 VB 방식으로 처리한다
• 특정 조건이 참일 때만 매크로 함수를 실행하도록 설정할 수 있음
• 매크로 함수 또는 매크로 함수 집합으로 구성되며 각 매크로 함수의 수행 방식을 제어하는 인수를 추가할 수 있음
• 매크로를 이용해 폼을 열고 닫거나 메시지 박스를 표시할 수 있음

매크로 함수

• IF : 조건을 만족하면 실행
• ApplyFilter : 조건에 맞게 필터
• FindRecord : 레코드 찾기
• GoToRecord : 특정 레코드로 이동
• MessageBox : 메시지 박스 표시
• ExportWithFormatting : 형식을 바꿔 보내기
• OpenForm : 폼 열기
• OpenQuery : 쿼리 열기
• OpenReport : 보고서 열기
• OpenTable : 테이블 열기
• RunMacro : 매크로 실행
• CloseWindow : 창 닫기
• QuitAccess : 액세스 종료