[데이터베이스] 총 정리

복습하기 위해 학부 수업 내용을 필기한 내용입니다.
이해를 제대로 하지 못하고 정리한 경우 틀린 내용이 있을 수 있습니다.
그러한 부분에 대해서는 알려주시면 정말 감사하겠습니다.

 

중간을 쳤을 때, 이대로 가다간 B+을 받을 거 같아서, 새롭게 마무리 정리의 필요성을 느꼈다.

키포인트나, 중요한 부분을 정리해서 올려야겠다.

 

▶10장. SQL(Structured Query Language)

1. SQL 데이터 정의어

테이블 생성/제거, 애트리뷰트 추가/제거, 뷰 생성/제거, 인덱스 생성/제거

 

2. SQL 데이터 조작어

검색(select)

select [all|distinct] 열_리스트 from 테이블_리스트 [where 조건]
	[group by 열_리스트 [having 조건]]
	[order by 열_리스트 [asc|desc]]

 

distinct : 중복 제거

select distinct 학과 from 학생;

 

asc/desc : 오름차순/내림차순

select 하건, 성적 from 수강 where 점수>= 80
	order by 학번 asc, 성적 desc;

 

'%'는 패턴에 일치하는 모든 문자를 의미

' - '는 패턴에 일치하는 하나의 문자를 의미

select 과목코드, 과목명 from 과목
	where 과목코드 like 'CO%';

 

null이나 between 사용 가능하다.

where 강의실 is null;
where 점수 between 80 and 90;

 

계산된 열도 사용 가능하고, as를 사용해 이름을 지정할 수 있다.

select 학번 as db수강학번, '최종점수 = ' as 내용, 
	점수 + 2 as 수강점수 from 수강
	where 과목코드='CO123';

 

 

삽입(insert)

insert into 학생(학번, 성명, 학과, 전화번호) 
	values(20188991, '오영주', '컴퓨터', '010-7651-7640');
insert into 컴퓨터(학번, 성명, 전화번호) select 학번, 성명, 전화번호
	from 학생 where 학과=‘컴퓨터’;

 

삭제(delete)

delete from 학생;
delete from 학생 where 학번=20181234;
delete from 수강 where 과목코드=‘co123’ and 점수>=85 and
	학번 in select 학번 from 학생 where 학과=‘컴퓨터’;

 

갱신(update)

update 학생 set 학과=‘컴퓨터’ where 성명=‘유진호’;
update 수강 set 점수=점수+2 where 과목코드=‘co123’;
update 수강 set 점수=점수+2 where 학번 in select 학번 from 학생 where 학과=‘컴퓨터’;

 

3. SQL 뷰(view)

 

뷰 생성

create view 학생뷰(학번, 성명, 전화번호) 
	as select 학번, 성명, 전화번호 from 학생
	where 학과 = '컴퓨터' with check option;

with check option : 뷰 정의 조건인 학과가 컴퓨터를 위반하면 실행이 거절된다.

 

뷰 장점

• 관련된 데이터만 이용 가능
• 중요하고 적합한 데이터만으로 구성 가능
• 민감한 데이터에 대한 접근 금지가 가능
• 복잡성 해소 / 복잡한 구조를 숨길 수 있음 / 복잡한 질의를 단순화
• 권한 부여를 단순화

 

뷰 단점

• 정의를 변경 X
• 삽입, 삭제, 갱신 연산에 제한이 많음

 

4. 삽입 SQL

exec begin declare section;
	int sno;
	...
exec end declare section;
sno = 20181234;
exec sql select 학번, 성명, 학과 
	into :sno, :sname, :sdept 
	from 학생 where 학번=:sno;

exec sql declare cur cursor for
	select 학번, 성명, 학과 from 학생 where 학과 = :sdept;
exec sql open cur;

do
	exec sql fetch cur into :sno, :sname, :sdept;
	....
	....
end;
exec sql close cur;

 

 

5. 동적 SQL

기본 명령어는 prepare, execute

staticsql = “select * from enroll where ccode=’co123’ and score>=90”;
exec sql prepare stsql from :staticssql;
exec sql execute stsql;

dynamicsql = “select * from enroll where ccode=? and score>=?”;
exec sql prepare stsql from :dynamicsql;
	code = ‘co123’; 
	score = 90;
exec sql execute stsql;

728x90

▶11장. 데이터베이스 정규화

1. 잘못 설계된 데이터베이스

삭제 이상, 삽입 이상, 갱신 이상 -> 분해, 정규화

 

2. 함수 종속성

함수 종속 : A가 B의 결정자이면 B가 A에 함수 종속된다.

 

이행적 함수 종속 : A -> B, A -> C인데 B ->C인 경우이다.

 

완전 함수 종속, 부분 함수 종속

 

다치 종속(mvd)

{A, C} 값에 대응되는 B의 집합이 A에만 종속되고 C에는 독립적이면

B는 A에 다치 종속된다. A->>B

A->>B이면 A->>C이다. A->>B|C로도 표현할 수 있다.

 

비정규 릴레이션

->(모든 도메인이 원자 값이 되도록 수정) 제1 정규형

->(부분 함수 종속 제거) 제2 정규형

->(이행적 함수 종속 제거) 제3 정규형

->(모든 결정자가 후보 키가 되도록 분해) 보이스 코드 정규형

->(다치 속성 제거) 제4 정규형

->(조인 종속을 만족하는 n-분해) 제5 정규형

 

저장 프로시저 cursor이용 반복문

delimiter $$
	create procedure cursorProc()
	begin
		declare escore int;
		declare count int default 0;
		declare total int default 0;
		declare endofrow boolean default false;

		declare cur cursor for select score from enroll;
		declare continue handler for not found set endofrow=true;
		open cur;
		cursor_loop:loop
			fetch cur into escore;
			...
		end loop cursor_loop;
		...
		close cur;
	end$$
delimiter;

 

▶12장. 데이터베이스 설계

1. 설계 개요

데이터베이스 개발의 생명주기

요구사항 분석 / 설계 / 구현 / 운영 / 감시 및 개선

 

데이터베이스 설계 단계

요구사항 분석 : 요구사항 수집/분석해 요구사항 명세서 작성

개념적 설계 : 개념적 스키마 모델링과 트랜잭션 모델링 병행 수행

논리적 설계 : 목표 DBMS가 처리 가능한 논리적 데이터 스키마 생성

물리적 설계 : 효율적이고 구현 가능한 물리적 데이터 구조 생성

구현 : 컴파일하고 실행, 변환 유틸리티에 활용

 

설계 시 고려사항

무효를 일회로 확보한다.

무결성, 효율성, 일관성, 회복, 확장성, 보안

 

2. 요구사항 분석

요구사항 명세서

 

3. 개념적 설계

개념적 스키마 모델링

추상화, 집단화(차 <- 엔진, 기어, 바퀴), 일반화(차 <- 승용차, 트럭, 버스)

 

트랜잭션 모델링

 

4. 논리적 설계

논리적 데이터 모델로 변환

트랜잭션 인터페이스 설계

스키마 평가 및 정제

 

5. 물리적 설계

저장 레코드 양식

레코드 집중화

접근 경로

트랜잭션 응답 시간 (response time), 저장 공간의 효율화, throughput

 

6. 구현

▶13장. 무결성, 보안, 회복

1. 무결성

무결성 규칙의 종류

도메인 무결성 규칙, 릴레이션 무결성 규칙

 

sql에서 무결성 규칙 정의

애트리뷰트 제약조건, 기본 테이블 제약조건, 주장(assertion)

 

트리거

테이블 이벤트(입력, 수정, 삭제)가 발생하면 자동으로 실행된다.

저장 프로시저와 비슷하지만, In, Out을 할 수 없고, 직접 실행할 수 없다.

create trigger student_ins after insert on student
begin update 학과테이블 set 학생수 = 학생수 + 1
	where 학과코드 = new_student.학과코드;
end;

 

2. 보안

데이터베이스 보안 : 계정, 암호 / 권한 관리

 

권한 부여 테이블 사용

사용자 프로파일 : 수행할 수 있는 연산에 관한 정보를 가진 테이블

권한 부여 테이블 : 모든 프로파일을 하나의 테이블로 종합 관리해서 보관

 

뷰 기법

create view 학생뷰 as select 학번, 성명, 학과 from student where 학과=‘컴퓨터’

 

grant/revoke 기법

grant select on student to A [with grant option];
revoke [grant option for] select on student from A [restrict | cascade];

 

데이터 암호화 기법

전치 암호화 : 몇 개의 낱말을 뒤집는 것

대체 암호화 : 암호화 키를 가지고 은폐시키는 것

외에도 des기법, 공개키 암호화 기법이 있다.

 

3. 회복

장애 : 트랜잭션 장애, 시스템 장애, 미디어 장애

 

회복의 개념 : 데이터의 중복 저장

-> 덤프(일정한 주기로 다른 곳에 저장)와 로그(변경될 때마다( 이전 값 이후 값 저장)

 

회복 조치의 유형 : redo(재수행), undo(취소)

 

데이터베이스 로그를 이용한 회복

<t, start>

<t, x, a, b>

<t, commit>

<t, abort>

 

즉시 갱신 회복 기법

변경되는 즉시 데이터베이스에 기록한다.

실패 시 undo를 해주어야 한다..

 

지연 갱신 회복 기법

commit이 되면 기록한다.

실패 시 undo를 할 필요가 없다.

검사 시점 회복

 

checkpoint(검사시점)을 이용하는 방식이다.

검사시점 이전에 완료된 것은 redo를 할 필요도 없다.

 

미디어 회복 기법

 

▶14장. 병행 제어

트랜잭션

모순 상태가 발생하지 않도록 완전하게 수행되거나, 하나라도 수행되지 않아야 한다.

트랜잭션은 데이터베이스에서 일어난 일련의 연산의 집합

all or nothing

 

begin_trans

...

...

end_trans

 

트랜잭션의 특성

ACID – atomicity, consistency, isolation, durability

 

트랜잭션의 연산 -> commit, rollback

트랜잭션의 상태 -> 활동, 부분 완료, 실패, 철회, 완료

 

2. 병행 제어

병행 수행이 적절하지 않으면 -> lost update, inconsistency, cascading rollback

 

트랜잭션 스케줄 – 직렬 스케줄, 직렬 가능 스케줄,

 

병행 제어 기법 – 로킹, 타임스탬프

lock-s, lock-x

 

inconsistency 모순 발생

 

2단계 로킹 규약

확장 단계 / 축소 단계

-> deadlock 발생(mutual exclusion, no preemption, circle wait, wait for)

 

회피 -> wait-die, wound-wait

예방

탐지 -> wait for graph