#1. 데이터베이스 개요
데이터베이스 정의
- 시스템이 공용으로 사용하고 관리하는 데이터 집합
- DB의 도입 배경 - 독립된 파일 단위로 데이터를 저장하게 되면 데이터 종속성 및 중복성이 높아 무결성 위배 가능성이 높을 수 있으므로 이러한 단점을 보완하고자 여러 시스템이 공용으로 데이터를 모아 관리하는 데이터베이스(DB)를 구축하게 됨.
- DB의 장단점
장점 단점 종속성 및 중복성 최소화
일관성 및 무결성 유지
공유 및 보안성 강화
실시간 처리 가능전문가 부족
전산화 비용 증가
시스템이 복잡하고 느림
파일 회복이 어려움
데이터베이스 관리 시스템(DBMS)
- 사용자와 DB 사이에서 사용자의 요구에 따라 DB생성, 관리 해주는 S/W
- 사용자 <-> DBMS <-> DB
- 사용자: DBA(DB 관리자), 일반 사용자, 응용 프로그램
- DBMS 종류: 계층형, 네트워크형, 객체지향형, 관계형
- DBMS 프로그램: Oracle, MySQL, MS SQL, Access 등
DBMS 기능 및 언어
| 정의어(DDL) (DBA 사용) | DB를 생성하거나 자료 형태(Type)와 구조 등을 수정하여 데이터를 이용하는 방식을 정의 하는 기능 CREATE, ALTER, DROP |
| 조작어(DML) (사용자 사용) | 데이터의 검색, 삽입, 삭제, 변경 등을 처리하는 기능 SELECT, INSERT, DELETE, UPDATE |
| 제어어(DCL) (DBA 사용) | 데이터의 무결성을 유지하기 위한 보안 및 권한 검사, 병행 제어 등의 기능을 정의하는 기능 COMMIT, ROLLBACK, GRANT, REVOKE |
관계형(Relational-DBMS = RDBMS)
- 테이블(Table) = Relation을 이용한 구조
- 필드(Field) => 속성(Attribute) = 열(Column)
- 레코드(Record) => 튜플(Tuple) = 행(Row) - 테이블: 표, 개체 또는 릴레이션
- 필드: 속성(Attribute), 열
- 레코드: 튜플(Tuple), 행
- 도메인: 하나의 속성에서 취할 수 있는 원자값의 범위
- 필드의 개수: 차수(Degree)
- 레코드의 개수: 기수(Cardinality)
테이블의 특징
- 속성(필드)과 튜플(레코드)들은 유일하며 순서과 무관함.
- 속성(필드)의 값은 분해할 수 없다.
- 속성(필드)의 값은 동일 할 수 있다.
- 튜플(레코드)은 삽입, 삭제 등에 의해 계속 변한다.
- 튜플(레코드)를 식별하기 위해 속성(필드)의 일부를 Key로 설정한다.
- 속성(필드)는 Null 값을 가질 수 있으나, 기본키에 해당하는 속성(필드)는 Null값을 가질 수 없다.
#2. 데이터베이스 설계
키(Key)
- 후보키(Candidate Key): (학번, 주민번호)
- 유일성, 최소성이 모두 만족되는 속성 - 기본키(Primary Key): (학번 또는 주민번호)
- 후보키 중 선정(중복 X, Null X - 개체 무결성 )
- 반드시 설정해야 할 필요는 없음
- OLE 개체, 첨부 파일의 데이터 형식은 기본키 불가능
- 여러 개의 필드에 설정 가능(슈퍼키) - 대체키(Alternate Key): (기본키가 학번이라면 주민번호)
- 기본키가 아닌 후보키 - 슈퍼키(Super Key): (이름 + 주소) / 즉 기본키 여러개 필드 설정
- 두 개 이상의 속성을 연결하여 유일성 만족
- 유일성 만족, 최소성 X - 외래키(Foreign Key)
- 관계가 정의되어 참조된 다른 테이블의 기본키
데이터베이스의 설계_개.논.물
| 1단계 (개념적 설계) | 요구사항을 분석하여 E-R D(Entity(개체)-Relationship(관계) Diagram)와 같은 개념적 구조 설계 |
| 2단계 (논리적 설계) | DBMS에 적합한 테이블의 구조로 설계 |
| 3단계 (물리적 설계) | DBMS로 구현 가능한 물리적 구조(H/W) 설계 |
E-R D
- 개체와 관계를 중심으로 시각적인 모델링을 함
- 사각형 - 개체(Entity) 타입
- 마름모 - 관계(Relationship) 타입
- 타원 - 속성(Attribute) 타입
- 밑줄 타원 - 기본키(Primarty Key) 타입
정규화
- 이상(Anomaly) 현상이 일어나지 않도록 분해하는 과정
- 중복, 종속성을 최소화하기 위한 작업
- 논리적 설계 단계에서 수행
- 속성 수가 적은 테이블로 분할되어 관리가 쉬워짐
- 분할이 많으면 데이터 연결이 복잡해져 효율이 떨어짐
- 완벽하게 중복을 제거할 수는 없음
* 이상 현상
- 삽입 이상: 불필요한 데이터가 함께 삽입되는 현상
- 삭제 이상: 필요한 데이터가 함께 삭제되는 현상
- 갱신 이상: 일부만 수정되어 데이터의 불일치 발생
데이터베이스의 구조(스키마)_외.서.개.논.내.물
| 외부스키마 | 일반 사용자나 응용프로그래머의 관점에서 본 스키마 ( 서브 스키마 = 사용자 뷰 ) |
| 개념스키마 | DB의 전체적인 논리적 구조이며 접근 권한, 보안정책, 무결성 규칙에 대한 정의 (스키마) |
| 내부 스키마 | DB 전체적인 물리적 구조 시스템 설계자의 관점에서 본 스키마 (물리 스키마) |
#3. 테이블 디자인
테이블 보기
- [디자인 보기] - 테이블의 구조 및 필드 속성 변경
- [데이터시트 보기] - 레코드 입력 및 수정
데이터 형식
| 데이터 형식 | 설명 |
|---|---|
| 짧은 텍스트 | 텍스트 + 숫자 = 255자 |
| 긴 텍스트 | 텍스트 + 숫자 = 63999자 |
| 숫자 | 바이트 = 1 Byte |
| 정수 = 2 Byte | |
| 정수(Long) = 4 Byte | |
| 실수(Single) = 4 Byte | |
| 실수(Double) = 8 Byte | |
| 날짜 / 시간 | 100년 ~ 9999년까지 입력가능 = 8 Byte |
| 통화 | 정수 15, 소수 4자리까지 입력가능 = 8 Byte |
| 일련 번호 | 자동으로 번호가 부여 됨 데이터가 입력된 후에는 선택 불가능 삭제된 일련 번호는 다시 부여되지 않음 정수(Long) = 4 Byte, 복제 ID = 16 Byte |
| Yes/No | Yes/No, True/False,, On/Off 선택 가능 = 1 Bit |
| OLE 개체 | 개체 연결 포함, 기본키 설정 불가능 = 1 GB |
| 하이퍼링크 | 주소 데이터 입력 =2048자 |
| 첨부파일 | 이메일에 파일을 첨부하는 것처럼 파일 첨부 기본키 설정 불가능 |
조회 속성
- 짧은 텍스트, 숫자, Yes/No 만 설정 가능
- 콤보상자 및 목록상자 적용 가능
| 행 원본 유형 | 테이블 / 쿼리, 값 목록, 필드 목록 |
| 행 원본 | 원본의 값 입력 |
| 바운드 열 | 실제로 저장 되는 열 |
| 열 개수 | 열 개수 |
| 열 이름 | 열 이름의 표현 여부 |
| 열 너비 | 열 너비 설정 (예: 1cm ; 2cm) |
| 행 수 | 목록의 행 개수 설정 |
| 목록 너비 | 전체 너비 |
| 목록 값만 허용 | 목록 값만 입력할 수 있도록 설정 |
#4. 필드 속성
필드 속성
| 속성 | 설명 |
|---|---|
| 형식 | 엑셀의 표시형식과 같음 |
| 캡션 | 데이터시트에서 보여 지는 레이블 (제목) |
| 기본값 | 자동으로 입력되어 있는 기본값 |
| 필수 | 필수로 입력하는 속성 |
| 빈 문자열 허용 | 빈 문자열을 허용하는 속성 |
| IME 모드 | 자료 입력 형태(한글, 영어)를 설정함 |
| 소수 자릿수 | 소수점 이하 자릿수 결정 |
유효성 검사
- 입력된 데이터가 유효한지 검사하는 기능
- '유효성 검사 텍스트'를 통해 오류 메시지 표현 가능
인덱스
- '중복 가능', '중복 불가능' 등을 지정
- 여러 필드에 설정 가능
- OLE, 첨부 파일 형식은 인덱스 설정 불가능
- 정렬 및 검색 속도가 빨라지지만 전체적인 업데이트 속도가 느려짐
입력 마스크
- 정확한 데이터가 입력될 수 있또록 해주는 '틀'
- 입력마스크 ; 저장방법 ; 표시문자
| 필수 | 데이터 | 선택 |
|---|---|---|
| 0 | 숫자 | 9 |
| L | 문자 | ? |
| A | 문자+숫자 | a |
| 숫자+기호 | # | |
| 대문자 | > | |
| 소문자 | < | |
| 암호 | Password |
#5. 관계 정의
관계 편집
- 항상 참조 무결성 유지
- 외래키 값은 참조된 테이블의 필드의 값과 동일해야 함
* 외래키: 참조된 다른 테이블의 기본키 - 관련 필드 모두 업데이트
- 참조된 필드 값이 변경 되면 자동으로 외래키 값 변경 - 관련 레코드 모두 삭제
- 참조된 필드 값이 삭제 되면 자동으로 외래키 값 삭제
* 관계 설정 하려면 필드의 데이터 형식이 같아야 함
* 관계 설정 후 참조된 기본키를 삭제하려면 관계를 해제하고 기본키를 삭제해야 함
조인(JOIN) 유형
- 내부 조인 (INNER JOIN)_Inter Section
- 두 테이블의 조인된 필드가 일치하는 행만 포함 - 왼쪽 조인 (LEFT JOIN)
- '왼쪽'에서는 모든 레코드를 포함하고 '오른쪽'에서는 조인된 필드가 일치하는 레코드만 포함 - 오른쪽 조인 (RIHGT JOIN)
- '오른쪽'에서는 모든 레코드를 포함하고, '왼쪽'에서는 조인된 필드가 일치하는 레코드만 포함
#6. SQL_언어_1
외부 데이터
- 가져오기
- 엑셀, 엑세스, ODBC, 텍스트, XML, HTML 파일 지원
- 새 테이블, 레코드 추가, 연결 테이블로 가져오기 기능
- 테이블 형태로만 가져 올 수 있음
- 레코드를 수정하며 가져올 수 없음 - 내보내기
- 엑셀, 엑세스, ODBC, 텍스트, XML, HTML, 전자 메일, PDF파일 지원
SQL (Structured Query Language)
- 데이터베이스를 이용할 때 사용하는 언어 (질의어)
데이터 제어어(DCL)
데이터의 무결성을 유지하기 위한 보안 및 권한 검사, 병행 제어 등의 기능을 정의하는 기능
* COMMIT(완료, 승인), ROLLBACK(취소), GRANT(권한부여), REVOKE(권한회수)
데이터 정의어(DDL)
DB를 생성하거나 자료 형태(Type)와 구조 등을 수정하여 데이터를 이용하는 방식을 정의 하는 기능
CREATE(생성) - CREATE TABLE 테이블명( 필드 형식... );
| CREATE TABLE 과목 ( 코드 CHAR(5) NOT NULL, 과목명 CHAR(9), PRIMARY KEY(코드) ); | 과목 테이블 만들기( 코드 문자 5 널값X 과목명 문자 9 기본키 코드 ); |
| CREATE TABLE 학생( 순번 INTEGER, 신청일 DATETIME, 코드 CHAR(5), FOREIGN KEY(코드) REFERENCES 과목 ); | 학생 테이블 만들기( 순번 숫자 신청일 날짜 시간 코드 문자 5 외래키 코드 과목 테이블 ); |
ALTER(구조 변경)
| 학생 테이블을 이용한 SQL | 주석 |
| ALTER TABLE 학생 ADD 성적 INTEGER; |
학생 테이블 성적필드 숫자형식 추가; |
| ALTER TABLE 학생 ALTER 성적 CHAR(5); |
학생 테이블 성적필드 문자형식 변경; |
DROP(삭제)
| 학생 테이블을 이용한 SQL | 주석 |
| DROP TABLE 학생; | 학생 테이블 삭제 |
#7. SQL_언어_2
데이터 조작어(DML)
SELECT(선택, 조회)
| SQL | 주석 |
| SELECT 필드 FROM 테이블 | |
| SELECT * FROM 학과; |
모든 필드 검색 학과 테이블에서 |
| WHERE 조건, AS는 별칭(별명)을 뜻함 | |
| SELECT 이름 AS 성명, 점수 FROM 학과 WHERE 점수>=80; |
이름은 성명, 점수 검색 학과 테이블에서 점수가 80이상인 자료만 |
| 자료를 묶을땐(그룹) GROUP BY | |
| SELECT LEFT(이름, 1) AS 성, COUNT(학번) AS 인원 FROM 학과 GROUP BY LEFT(이름,1); |
이름 왼쪽 한글자는 성, 학번 수는 인원으로 검색 학과 테이블에서 이름 왼쪽 한글자를 그룹으로 묶어서; |
| GROUP BY의 조건은 HAVING으로 사용 | |
| SELECT LEFT(이름,1) AS 성, COUNT(학번) AS 인원 FROM 학과 GROUP BY LEFT(이름,1) HAVING LEFT(이름,1)='김'; |
이름 왼쪽 한글자는 성, 학번 수는 인원으로 검색 학과 테이블에서 이름 왼쪽 한글자를 그룹으로 묶어서 이름 왼쪽 한글자 = 김; |
| ORDER BY 정렬 (ASC,생략-오름차순) (DESC-내림차순) | |
| SELECT * FROM 학과 ORDER BY 생일 DESC; |
모든 필드 검색 학과 테이블에서 생일을 내림차순 정렬 |
SELECT OPTION 필드 FROM 테이블 WHERE 조건 GROUP BY 그룹 HAVING 조건 ORDER BY 정렬;
OPTION
- DISTINCT: 중복 제거한 조회 결과 출력
- TOP 10: 상위 10개의 항목
기본함수 중 변경된 함수
- AVG: 평균, COUNT: 들어있는 개수
조건 예제
- OR 사용법
- 주소='서울' OR 주소='경기'
- IN('서울', '경기') - AND와 #날짜# 사용법
- 생일>=#1998-1-1# AND 생일<=#1998-12-31#
- BETWEEN #1998-1-1# AND #1998-12-31# - 만능문자(*, ?) 사용법
- 김으로 시작하는 글자: LIKE '김*'
- 씨로 끝나는 2글자: LIKE '?씨' - 부정문 - NOT, 빈칸 검색 - IS NULL
- 매개 변수
- 사용자에게 입력 받는 값, [대괄호]로 묶음
#8. SQL_언어_3
데이터 조작어(DML)
INSERT(삽입)
| SQL | 주석 |
| INSERT INTO 교과(학번, 전공) VALUES ('2001', '컴활1급'); |
교과 테이블의 학번 전공 필드에 2001, 컴활1급 삽입 |
| INSERT INTO 교과 VALUES ('2002', '컴활2급') |
교과 테이블의 2002, 컴활2급 삽입 |
| INSERT INTO 교과(학번, 전공) SELECT * FROM 추가교과; |
교과 테이블의 학번, 전공 필드에 추가교과 테이블의 값 삽입; |
INSERT INTO 테이블명(필드1, 필드2) VALUES (값1, 값2)
UPDATE(수정)
| SQL | 주석 | |
| UPDATE 교과 SET 전공='지울것' | WHERE 학번>='2000'; | 교과 테이블의 전공을 '지울것'으로 수정 학번이 2000이상인 자료만 |
UPDATE 테이블 SET 필드=값 WHERE 조건;
DELETE(삭제)
| SQL | 주석 |
| DELETE FROM 교과 WHERE 전공='지울것'; |
교과 테이블의 전공이 지울것인 자료 삭제 |
DELETE FROM 테이블 WHERE 조건;
쿼리 마법사
- 단순 쿼리 마법사
- 중복 데이터 쿼리 마법사
- 불일치 검색 쿼리 마법사 - 2개 이상의 테이블이 필요함
- 크로스탭 쿼리 마법사
- 엑셀의 피벗테이블과 비슷함
- 열 머리글, 행 머리글, 값 등으로 구성
- 열 머리글은 1개만 가능, 행머리글은 3개까지 가능
* 크로스탭 쿼리행
머
리
글
1행
머
리
글
2행
머
리
글
3열 머리글 값
#9. 폼 속성
폼 개요
- 테이블, 쿼리, SQL를 원본으로 하여 데이터의 입력, 편집 등을 편리하게 해주는 개체
- 컨트롤 마법사를 활용해 쉽게 컨트롤 생성 가능
- 컨트롤의 이름은 중복 불가능
- 폼과 원본을 바운드(연결)하고 폼에서 데이터를 수정하면 원본데이터에도 수정됨
폼 속성
- 팝업: 엑세스 창 위에 팝업 형태로 폼이 열림
- 모달: 폼이 열려있는 경우 다른 창을 선택 할 수 없음
- 레코드 잠금: 여러 사용자가 동시에 레코드를 편집할 수 없도록 잠금
- 탐색 단추: 폼 아래에 표시된 탐색 단추 표시
- 기본 보기
- 단일 폼: 하나의 레코드만 표현
- 연속 폼: 여러개의 레코드 표현
- 데이터시트: 엑셀화면과 비슷하게 표현
- 분할 표시 폼: 폼 보기와 데이터시트 보기를 동시에 표시하고, 폼과 데이터시트가 동기화 되며, 위, 아래, 왼쪽, 오른쪽으로 설정 가능
컨트롤 속성
- 탭 인덱스
- 컨트롤이 만들어진 순서대로 탭 순서 지정
- 자동순서: 위에서 아래, 왼쪽에서 오른쪽 지정
- 탭 정지 속성이 '예'로 되어 있어야만 포커스가 이동
하위 폼, 보고서
- 폼 및 보고서에 들어있는 또 하나의 폼 및 보고서
- 하위 폼, 보고서는 독립된 개체로 열거나 편집 가능
- 일 대 다 관계에서 효과적으로 표현 (일: 기본, 다: 하위)
- 연결할 필드의 형식은 같거나 호환되어야 함 (관계)
#10. 보고서 속성
보고서 개요
- 테이블, 쿼리, SQL을 원본으로 하여 데이터의 조회, 출력 등을 편리하게 해주는 개체
- 보고서 도구 및 마법사를 이용하여 쉽게 보고서 생성가능
- 레코드 원본에서 쿼리 작성기를 이용하여 여러 개의 데이터를 원본으로 사용 가능
- 폼과 동일하게 컨트롤을 표현할 수 있으나 데이터의 입력, 편집은 불가능
- 보기 상태
- 인쇄 미리 보기: 인쇄 전 보는 화면, 페이지 구분 됨
- 레이아웃 보기: 컨트롤의 레이아웃(배치)만 하는 화면
- 디자인 보기: 내용 및 속성 등을 변경하기 위한 화면
- 보고서 보기: 보고서 내용 확인, 페이지 구분 안됨
컨트롤 속성
- 폼 및 보고서에서 레코드 원본의 테이블 및 쿼리의 필드를 추가하면 텍스트 상자로 바운드 됨
- 수식 (계산) 컨트롤로 사용하려면 '='으로 시작함
- 누적 합계 - 누계 표시 가능 (순번 표현 시 '=1'로 표시)
- 중복 내용 숨기기 - 이전 레코드와 같으면 숨기기
그룹화 및 정렬
- 그룹화 기준이 되는 필드는 자동으로 정렬되어 표시됨
- 그룹 머리글 또는 그룹 바닥글 중 하나이상 선택 가능
- 그룹 머리글은 첫 번째 레코드 위에 표시되고,
- 그룹 바닥글은 마지막 레코드 아래에 표시됨
필드나 식을 기준으로 10개까지 그룹화 가능 - 날짜시간: 연도, 분기, 월, 주, 일, 시간, 분 별로 그룹 가능
- 문자: 첫 문자, 처음 두 문자, 사용자지정으로 그룹 가능
- 숫자: 일정한 간격으로 그룹 가능
#11. 구역과 수식 활용
폼, 보고서 구역
| 보고서 머리글 | 보고서 처음 한번 출력 (일반적으로 로고, 제목 및 날짜 등의 정보를 표시) |
| 페이지 머리글 | 매 페이지 상단 출력 |
| 그룹 머리글 | 그룹 첫 번째 레코드 위 출력 |
| 본문 | 모든 레코드 출력 (레코드 원본의 모든 행에 대해 한 번씩 출력 됨, 컨트롤이 추가) |
| 그룹 바닥글 | 그룹 마지막 레코드 아래 출력 |
| 페이지 바닥글 | 매 페이지 하단 출력 |
| 보고서 바닥글 | 보고서 마지막 한번 출력 (보고서 총합계 또는 전체 보고서에 대한 기타 요약 정보를 표시) |
| * 모든 영역에 함수 사용 가능 |
엑세스 함수
- AVG(인수) = AVERAGE와 같음
- COUNT(인수) = COUNTA와 같음
- IIF(조건, 참, 거짓) = IF와 같음 (FALSE=0, TRUE=1)
- 피제수 MOD 제수 = MOD(피제수, 제수)와 같음
- FORMAT(인수, 표시형식) = TEXT와 같음
- D함수("필드", "테이블", "조건") = D함수와 같음
페이지 번호
- [Page] = 현재 페이지 번호
- [Pages} = 전체 페이지 번호
조건부 서식
- 조건을 만족하면 지정된 서식을 적용하는 기능
- 필드 값, 식, 다른 레코드 비교 등으로 생성 가능
- 조건부 서식의 순서를 바꿀 수 있고, 상위 항목의 우선 순위가 높음
- 텍스트 및 콤보 컨트롤에만 50개까지 조건부 서식 가능
- 다른 파일 형식으로 내보내기 시 조건부서식 유지 안됨
#12. 매크로
엑세스의 매크로
- 반복적인 명령, 작업을 자동화하여 빠르게 수행하는 기능
- 엑셀과 다르게 기록이 아닌 매크로 함수를 추가하여 생성
- 여러개의 매크로 함수 포함 가능
- 폼 및 보고서 컨트롤 이벤트에 포함하여 실행 가능
매크로 함수
- IF: 조건을 만족하면 실행
- ApplyFilter: 조건에 맞게 필터
- FindRecord: 레코드 찾기
- GoToRecord: 특정 레코드로 이동
- MessageBox: 메시지 박스 표시
- ExportWithFormatting: 형식을 바꿔 내보내기
- OpenForm: 폼 열기
- OpenQuery: 쿼리 열기
- OpenReport: 보고서 열기
- OpenTable: 테이블 열기
- RunMacro: 매크로 실행
- CloseWindow: 창 닫기
- QuitAccess: 엑세스 종료
