데이터베이스 제 2강[KNU 2021-2]

👨‍💻🏫KNU 2021-2 SW & media 데이터베이스 필기노트 2

1. 데이터베이스 시스템(DBS)의 정의🛢

데이터베이스에 데이터를 저장하고 이를 관리하여 조직에 필요한 정보를 생성해주는 시스템

2. 데이터베이스의 구조

• 스키마: 데이터베이스에 저장되는 데이터구조와 제약조건을 정의한 것
• 인스턴스: 스키마에따라 데이터베이스에 실제로 저장된 값

2.1. 3단계 데이터베이스 구조

데이터베이스를 쉽게 이해할 수 있도록 ANSI/SPARC에서 제안한 표준

• 외부단계: 개별사용자 관점으로 외부 스키마가 여러 개 존재가능하다.
• 개념단계: 조직전체의 관점으로 개념 스키마 하나가 존재한다.
• 내부단계: 물리적인 저장장치의 관점으로 내부 스키마 하나가 존재한다.
• 응용 인터페이스: 외부/개념 스키마간의 사상
• 저장 인터페이스: 개념/내부 스키마간의 사상

-> 각 단계별로 다른 추상화를 제공. 이로 인해 하위 스키마가 변경되어도 상위 스키마가 영향을 받지 않는 데이터 독립성을 갖는다.
(논리적, 물리적 데이터 독립성)

외부스키마: (=sub schema)각 사용자가 생각하는 데이터베이스의 모습, 논리적구조로 사용자마다 다르다.
개념스키마: 조직 전체의 관점에서 전체 데이터베이스에 어떤 데이터가있는지, 데이터간의 관계, 보안정책, 제약조건 등 의 정의 포함
내부스키마: 실제로 정의 되는 부분. 물리적으로 저장되는 레코드, 필드등의 부분을 의미한다.

2.2. 데이터 사전

데이터베이스의 데이터정보(메타데이터)를 유지하는 시스템 데이터베이스, 스키마나 사상정보등을 저장해두는것으로 DBMS가 생성하고 유지한다. 일반사용자는 수정해서는 안된다.(=datacatalog)

2.3. 데이터 디렉토리

데이터 사전의 데이터에 실제로 접근하기위해 사용되는 위치정보를 저장하는 시스템 데이터베이스

3. 데이터베이스 사용자

데이터베이스를 이용하는 모든 사람으로 일반유저 ,관리자 ,응용프로그래머로 구분된다.

• 관리자: 데이터 스키마 관리, 내부 정책 결정, 제약조건 정의
• 일반유저: 데이터 삽입,삭제,수정,검색자
• 응용프로그래머: 데이터 언어를 삽입하여 프로그램을 작성하는 자

4. 데이터언어

• 정의어(DDL): 스키마 정의, 수정, 삭제에 사용됨
• 조작어(DML): 데이터, 삽입, 삭제, 수정과 같은 처리를 요구하기위해 사용
• 제어어(DCL): 내부적으로 필요한 규칙이나 기법을 정의하기위해 사용된다.(보안, 회복, 무결성…)

5. DBMS구성

데이터베이스 관리와 사용자의 데이터 처리요구 수행

• 질의 처리기: 데이터 처리요구를 해석하여 처리(DDL COMPILER, DML PRECOMPILER, RUNTIME DATABASE PROCESSER,TRANJECTION ADMIN)
• 저장데이터 관리자: 디스크의 데이터베이스, 데이터사전의 관리자

6. 실습1. 데이터베이스 구축

데이터베이스(스키마) 생성 -> 테이블 생성-> 테이블 데이터 입력 및 SQL활용

실습1.1. 데이터베이스(스키마)생성

스키마의 빈칸을 우클릭하여’CreateSchema’로 생성하거나 메뉴바의 데이터베이스(🛢️)를 눌러 생성 가능하다.

실습1.2. 테이블 생성하고 데이터 입력

생성된 스키마를 확장후 ‘Tables’ 우클릭하여 ‘CreateTable’로 생성하거나 메뉴바의 버튼을(📋) 눌러 생성가능하다.

테이블생성시 Table Name을 입력하고 챠트를 더블클릭해 새 데이터를 입력하는것으로 테이블의 초기값을 세팅할 수 있다.

각 row데이터의 설정값은 다음과 같다

Colum Name	Datatype	PK	NN	UQ	B	UN	ZF	AI	G	Default/Expression
열의 이름	데이터타입	열의 대표값	NULL 금지	고유키	Binary	음수 미사용	Auto Increment	빈칸만큼Zero fill	다른열의 수식으로 생성된 값

테이블을 조회하기위해 선택된 테이블 우측의 최우측 버튼(📋⚡)클릭, 또는 우클릭후 Select Rows의 방법을 사용할 수 있다.

7. 실습2. SQL문 작성하기

실습2.1. 쿼리 조회

원형: SELECT (row) FROM (table);

예) SELECT Name FROM tbl;

SELECT Name	FROM tbl	;
Name 열	tbl 테이블 사용	;

예) SELECT * FROM membertbl WHERE data2 = ‘토마스’;

SELECT *	FROM tbl	WHERE Name = ‘토마스’	;
모든 열	tbl 테이블 사용	Name의 값이 토마스인 데이터(행) 출력	;

실습2.2. 테이블 관리

테이블 생성

원형: CREATE TABLE ‘table’ (Colum Name Datatype);

예) CREATE TABLE ‘testTable’ (Colum Name Datatype);

CREATE TABLE’testTable`	(id INT)	;
testTable 테이블 생성	INT 타입의 id 칼럼 생성	;

테이블 삭제

원형: DROP TABLE ‘table’;

DROP TABLE ‘table’	;
table 테이블 삭제	;

실습2.3. 인덱스 생성

인덱스는 열단위에 생성되는 색인정보로 데이터가 많은경우 속도 저하를 피라기위해 만든다.

원형: CREATE INDEX (인덱스명) ON (테이블)(열);

실습2.4. 뷰 생성

뷰는 실제 테이블로부터 상속되는 가상의 테이블로 민감한 데이터를 감추거나 열람자로부터의 삽입, 삭제, 편집을 방지할 수 있다.

원형: CREATE VIEW (뷰테이블 명) AS SELECT (열1), (열2) FROM (테이블명);

실습2.5. 스토어드프로시저 생성

스토어드프로시저는 프로그래밍언어의 함수로 이해하면된다.

--프로시저 정의:
DELIMITER // --//';'로 끝내지 않고 '//'로 처리되도록 한다.     
CREATE PROCEDURE procedurename() //프로시저정의
BEGIN//--프로시저 정의시작
쿼리문1;
쿼리문2;
쿼리문3;
END // --프로시저 정의의 끝
DELIMITER ; --디리미터 해제

프로시저 호출: CALL procedurename();

실습2.6. 트리거 생성

트리거는 특정 조건을 만족할때 작동되는 기능으로 삭제된 데이터의 백업등에 사용된다.

--삭제된 데이터의 로그를 저장할 테이블생성
CREATE TABLE testTable_deleted
( data1 char(8),
data2 char(5),
data3 char(20),
deletedDate date -- 삭제한 날짜
);

--트리거생성
DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL -- 트리거 이름
AFTER DELETE -- 삭제 후에 작동하게 지정
ON memberTBL -- 트리거를 부착할 테이블
FOR EACH ROW -- 각 행마다 적용
BEGIN
-- OLD 테이블의 내용을 백업 테이블에 삽입
INSERT INTO testTable_deleted
VALUES (OLD.data1, OLD.data2, OLD.data3, CURDATE());
END //
DELIMITER ;