정보처리기사 실기 데이터베이스 이론

데이터베이스 개념

1. 필요성/ 정의 / 특성
2. 언어, 사용자
3. 스키마 3계층( 외부, 내부, 개념)
4. 데이터베이스 설계(개념 -> 논리 -> 물리)

5. 관계형 데이터베이스, 계층형 데이터베이스, 망형 데이터베이스

* 데이터베이스 도입배경 -> 파일시스템의 문제를 해결하기 위해 도입

* 파일 시스템 : 독립된 파일단위로 업무과 관련한 데이터를 저장하므로 “데이터 중복성”,”데이터 종속성” 발생
- 데이터 무결성에 위배될 가능성이 크다.

* 데이터베이스 정의
1. 통합된 데이터(Intergrated Data) : 자료의 중복을 배제, 최소화 한 데이터의 집합

2. 저장된 데이터(Stored Data) : 컴퓨터가 접근할 수 있는 저장매체에 저장된 데이터 집합

3. 운영 데이터(Operational Data) : 조직업무의 수행을 위해 없어서는 안될 반드시 필요한 데이터
4. 공용 데이터(Shared Data) : 여러 사람들이 공동으로 소유하고 유지하는 데이터

• 데이터 독립성
  - 물리적 데이터 독립성 : 응용프로그램에 영향을 주지 않음, 물리적 구조 변경 가능
  - 논리적 데이터 독립성 : 논리적 구조를 변경해도 응용프로그램에 영향을 주지 않음

• DBMS(Database Management System)
  - 응용프로그램과 데이터베이스 사이의 중재자

• 데이터베이스 특징
  1. 실시간 접근(Real Time Accessibility) -> 질의에 대해 즉시처리
  2. 계속적인 변화(Continious Evolution) -> 동적인 데이터로 항상 최신상태를 유지
  3. 동시사용, 공유성(Concurrent sharing) -> 여러 사용자가 동시에 사용함
  4. 내용에 의한 참조(Content Reference) -> 위치나 주소가 아니라 사용자가 요구하는 내용에 따라 참조
  
  
• 데이터 언어
  1. DDL -> 형태, 구조, 저장에 관한 내용 정의 및 변경
  2. DML -> 사용자 요구에 따라 검색, 갱신, 삽입, 삭제 수행
  3. DCL -> 정확성과 안정성을 유지
  
• 데이터 베이스 사용자
  1. 응용프로그래머 -> Data를 활용하고 인터페이스를 제작한다.
  2. 일반 사용자 -> 니가 일반 사용자
  3. DB관리자 -> 데이터베이스 설계 업무
  4. Data 관리자(Data Administration) -> 데이터를 정의한다.
  5. Data 설계자(Data Architct) -> 데이터와 구조를 체계적으로 관리하는 사람

* 스키마 3계층

• 스키마 : 데이터베이스를 구조와 제약조건에  대한 명세를 기술한 것

1. 외부스키마 : 사용자가 보는 관점
2. 개념스키마 : DB전체적인 관리구조
3. 내부스키마 : DB전체적인 물리적 구조

# 개념적 설계, 개념적 모델링 : 개체타입과 이들간의 관계를 이용해 개념적으로 표현(ERD)

db 구성요소 

개체(Entity) : 파일 처리 방식의 파일에서 레코드

속성(Attribute) : 파일 처리 방식에서 필드(항목), 개체의 성질

관계(Relationship) : 개체와 개체 또는 개체와 속성 간의 관계

# 논리적 설계 : 목표 DBMS에 맞추어 논리적 모델로 설계

# 물리적 설계 : 저장 레코드 양식의 설계 및 물리적 구조, 데이터의 표현

테이블(릴레이션) = 개체

기본키 = 주키 = 식별자 

튜플(행)들을 구분

속성(열)들을 구분

도메인 : 한 속성에 나타날 수 잇는 값들의 범위

차수(Degree) : 속성수

카디날리티(Cardinality) 튜플의 수

널(Null) : 해당없음 정보의 부재를 나타낸다.

• 관계의 종류( 1:1, 1:n , n:m )

-> 개체들간의 존재할 수 있는 데이터의 카디날리티를 파악하여 설정

키 : 특정 조건에 맞는 튜플을 구분할 수 있는 단일속성 or 속성 그룹

1. 슈퍼키 : 속성들의 집합으로 구성된 키(유일성)
2. 후보키 : 최소 슈퍼키 (유일성 + 최소성)
3. 기본키 : 후보키에서 선택한 키 ( 중복불가, 널값 불가)
4. 대체키 : 후보키에서 기본키를 제외한 키들
5. 외래키 : 참조기준이 되는 속성, 참조하고자 하는 릴레이션의 기본키와 동일
   

무결성 제약조건

1. 참조 무결성 제약조건 :  릴레이션은 참조할 수 없는 외래키 값을 가질 수 없음을 의미하는 제약조건
2. 개체 무결성 제약조건 : 한 릴레이션의 기본키를 구성하는 어떠한 속성도 NULL or 중복값을 가질 수 없다.
3. 도메인 무결성 제약조건 : 각 속성은 반드시 정의된 도메인에 속한 값이어야 한다.(도메인 범위를 지켜야 한다)

 

• 정합성

• 무모순성( 논리적인 모순이 없는 상태) 데이터무결성 + 데이터 통합 => 정합성

*CRUD 매트릭스 : 업무프로세스와 데이터간의 상관분석표는 행과 열의 교차점에 대한 방생및 이용상태
create, read, update, delete

개체-관계 모델 : 무질서한 데이터를 논리적인 관계로 표현

Mapping-rule : ERD에서 관계형 데이터베이스룰에 입각하여 데이터베이스 스키마로 변환하는 과정

• 관계유형

1. 식별관계 : 외래키가 기본키(실선으로 표시)
2. 비식별 관계 : 외래키가 기본 속성(점선으로 표시)
3. 카디날리티(1:1, 1:n, n:m)
   두테이블의 기본키를 외래키로 가지는 새로운 릴레이션 스키마를 도출 기본키가 동일하면 1:1 관계 N:M의 관계일때 교차테이블이 필요하다.

Master-Detail 관계 : 주/종 관계, 부모/자식 관계

• 정규화

• 데이터 중복을 방지하고 효율적으로 데이터를 저장하기 위함
• 릴레이션 분리 -> 삽입, 삭제, 갱신 이상의 발생 가능성을 줄인다.

*정규화 특징

-> 이상현상을 제거하는 목적

-> 속성간의 종속성을 최소화 하기 위한 목적

• 제1정규형

• 반복되는 속성을 제거한뒤 모든 속성이 원자 도메인으로만 되어있는 정규형

이상현상

• 릴레이션에서 일부 속성들의 종속으로 인해 중복이 발생하여 테이블 조작시 불일치
• 갱신이상, 삽입이상, 삭제이상

*제2정규형

• 부분 함수 종속을 제거한다.

# 함수적 종속

함수적 종속성은 애트리뷰트들 사이의 관계에 대한 제약조건으로 애트리뷰트 A가 B의 결정자이면 B는 A에 함수적으로 종속됩니다

부분/ 완전 함수 종속(기본키가 2개 이상일때)

• 완전 함수 종속 : 기본키그룹(회원번호,수강과목)이 성적 속성의 결정자
• 부분 함수 종속 : 회원번호(기본키중 일부) 만이 이름 속성의 결정자

*제3정규형

: 이행적 함수 종속을 제거

A -> B (A가 B를 결정하고) , B->C(B가 C를 결정하는 관계) 일 때, A->C가 되는 경우를 제거한기 위해 릴레이션을 분리해야 한다.

*BCNF : 결정자가 후보키가 아닌 함수종속을 제거

• 제4정규형 : 다치종속제거
• 제5정규형 : 후보키를 통하지 않은 조인종속제거

• 데이터 사전(Data Dictionary)

1. 여러가지 객체에 관한 정보를 담고있는 시스템 데이터베이스

• 사용자도 SQL을 통해 검색 가능하다.

2. 데이터베이스 관리 시스템에서 사용하는 모든 파일, 속성, 변수의 목록

관계 데이터 연산

0. 관계대수

- 절차적 언어 : 절차중심
- 순수관계 연산자, 일반 집합 연산자
순수관계 연산자 : Select Project, join, division
일반 집합 연산자 : 합지합 교직합 차집합 카티션프로덕트

SELECT : 수평적인 연산을 하는 관계 대수(시그마)

PROJECT : 수직적인 연산(파이)

JOIN : 공통된 속성을 연결하는 것(리본)
- natural join -> 공통 속성값 제거
R1 리본 R2
- equi join -> 공통 속성값 중복
R1 리본 b=b R2

DIVISION : 나누어지는 릴레이션1은 릴레이션2의 모든 내용을 호함한 것이 결과
A[b/c]B

합집합, 교집합, 차집합 연산은 이항 연산으로서 연산에 참가하는 두개의 릴레이션은 차수와 도메인이 같아야 연산을 수행할 수 있다.

차집합 :  빼준다;
카티션 프로덕트 : 다 곱해준다

0. 관계해석

- 비절자척 언어 : 결과중심
- 튜플 관계 해석, 도메인 관계 해석

DDL(database definition language)

create domain sung char(2)

default ‘여’

constraint sung_ck check(sung=‘남’ or sung=‘여’);

create table student(
number char(10),
name char(10) not null,

sex sung,
birth date,

primary key(number),

unique(name),

foreign key(code) references major(code),  => 중요

check birth>= ’1974-05-01’

);

외래키는 null이거나 상위테이블의 기본키 여야 한다.
foreign key ~ references option
- on delete[update] cascade : 부모테이블이 삭제/수정되면 관련된 자식 테이블들도 연쇄 삭제/수정 된다
- on delete[update] set null : 부모테이블을 삭제하면 참도된 속성값이 null 값이 된다.
- on delete[update] set default : default 값으로 변경된다.

- 논리적으로만 존재하는 뷰
Create view 여학생_view(이름, 성별) AS
select 이름, 성별
from 학생
where 성별=‘여’
with check option   =>  where 절 조건에 위배될 경우에는 실행을 거부한다.

view 의 정의 : 사용자에게 접그이 허용된 자료만을 제한적으로 보여주기 위해서 하나 이상의 기본 테이블로부터 유도된 가상 테이블

특징
- 구조가 기본테이블과 거의 유사
- 물리적으로 구현되지 않았다.
- 논리적 독립성 제공
- 필욯나 데이터로만 구성
- 데이터 보호 효율적
- 삽입 삭제 갱신 연산이 가능하지만 제한적이다.
- 하나의 View 를 삭제 -> 그 view를 기초로 만들어진 view 도 같이 삭제
- 독립적인 인덱스를 가질 수 없다.
- 뷰에 대한 검색은 일반 테이블과 같다
- View에 정의 변경은 불가능 한다.

Create index  : 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조

create (unique) index 이름_idx
on 학생(이름 ASC)  => default ascending

CLUSTER;

Clustered index : 물리적으로 데이터를 정렬(non-clustered 보다 검색이 빠르다)
non- Clustered index :  물리적으로 정렬하지 않음
한 테이블에 하나의 클러스터드 인덱스만 만들 수 있다.
index 를 생성하면 검색속도는 빠르나 삽입 삭제 수정이 느려질 수 있다.

Alter table 학과 add 연락처 char(18);
Alter table 학과 alter 학과명 set default’정보’;
Alter table 학과 drop 학과명


Drop table 학과 cascade;
- 참조 무결성에 위배될 수 있기 때문에 삭제를 조심하며 cascade 명령을 사용한다.
cascade : 참조하는 테이블을 연쇄적으로 제거
restrict : 참조하는 테이블이 있을 경우 제거 안됨 (생략가능)

DML(database manipulation language)
- select insert delete update

insert into R1 values(value1, val2,);
insert into R1(att1, att2) values(val1, val2);

insert into R1(att1, att2, att3) select att1, att2, att3 from R2 where 주소=‘남구’;


delete from R1 where att1 = ‘~’;

delete from R1   -> 모든 튜플 삭제
튜플 삭제 delete 테이블 삭제 Drop


update R1 set att1=‘~’ where att1 =‘~’;
update R1 set att1 = att1+10 where att2=‘~’;


select * from R1
select R1.* from R1;

select att1, att2 , att3 from R1;

select R1.att1, R1.att2 from R1;
=>  모두 같은 결과 이다.

select distinct att1 from R1

select(확장 구조)
select [distinct] 속성 from 테이블
[where 조건]
[group by 속성 [having 조건]

[order by 속성[asc | desc]]

그룹 함수
count(속성) : 그룹별 튜플 수
Avg(속성) :
sum: 합계
max : 최대값
min : 최소값

SQL 수행 순서 : FROM -> where -> group by -> having -> select -> order by

Union -> 테이블의 모든 튜플을 중복없이 하나의 결과로 엽합 검색하라.

Select * from 수강생
UNION
Select * from 정회원;

DCL(Database control Language
Grant(권한부여), REVOKE(권환회수)
COMMIT(트랙잭션 완료 -> DB 적용), ROLLBACK(트랜잭션 취소 -> DB적용 안됨)

Grand Select on 수강생 TO KWS;

-> KWS에게 수강생 테이블에 대한 Select 권한 부여
권한 종류 : ALL, INSERT, DELETE, UPDATE, SELECT
-> KWS가 다른 사람에게 권한을 주기 위해서는 

GRAND SELECT ON 수강생 TO KWS  WITH GRANT OPTION;
권환 취소

REVOKE SELECT on 수강생 from KWS CASCADE
->kws 에게 권한을 빼앗고 kws가 부여한 권한도 다 취도 된다.

COMMIT, ROLLBACK
작업의 논리적인 단위 -> Transaction

물리적 설계

: 논리 데이터베이스 구조로부터 효율적인 물리적 구조의 데이터베이스 구조를 설계하는 과정

⁃ 효율적인 물리적 구조란 성능 업 저장공간 다운 보안,백업,회복이 용이한 구조

물리적 설계 단계
1) 개발 DBMS 선정

2)칼럼의 데이터 타입과 사이즈 정의
3) 데이터 사용량 분석
4) 역정구화
5) 인덱스, 뷰, 스토어드 프로시져, 트리거 정의
6) 데이터 베이스 생성

• 스토어드 프로시져 : 함수같은 역할을 한다. 재사용성 상승!

• 역정규화(DeNormalization)
: 시스템의 성능 향상을 위해서 정규화에 위배되는 행위를 하는 것
: 테이블 통합, 중복 허용

DB 튜닝
1. db 설계 및 구축 과정 : 요구사항 분석 -> DB설계 -> DB구축 -> 테스트(DB튜닝) -> 유지보수

2. 데이터베이스 튜닝 : 데이터베이스가 일정한 성능을 유지할 수 있도록 비효율적인 요소를 제거하고 성능 개선을 위하여 SQL 문장을 포함, 데이터베이스의 여러요소를 조정(조율,최적화)하는 작업을 말한다.

3.유지보수 : 사용자의 요구와 비즈니스 환경이 변화함에 따라 요구사항을 맞춰 주는것

로킹
1. 병행 제어(동시성 제어, Concurrency Control) : 동시에 여러개를 수행할때 베이터베이스 일관성 유지를 위해 트랜잭션 간의 상호 작용을 제어

2. 병행제어 목적
데이터베이스 공유 최대화, 시스템 활용도 최대화, 일관성 유지, 사용자에 대한 응답시간 최소화

3. 병행제어 기법
1) 로킹 : 데이터에 엑세스하는 동안 다른 트랜잭션이 엑세스 할 수 없도록 하는 방법
2) 로킹단위
- 병행제어에서 한꺼번에 로킹할 수 있는 단위
- 로킹 단위가 크면 로크수가 작고 관리가 수월해지며 병행성 수준이 낮아진다.

인덱스 설계 : 테이블에 대한 검색 속도를 향상시킬 수 있는 확실한 수단.