정보처리기사 25일차

3. 데이터베이스

 3.1 SQL 활용

 3.1.1 SQL 활용 능력 단위 정의

 3.1.2 기본 SQL 작성

---

- SQL 활용 능력 단위 정의

SQL 활용이란 관계형 데이터베이스에서 SQL을 사용하여 목적에 적합한 데이터를 정의하고, 조작하며, 제어하는 능력이다.

 

- 기본 SQL 작성

 

1. 데이터베이스 기본

 

1.1 정보시스템과 데이터베이스

 

1. 정보시스템

정보시스템은 조직에서 필요한 데이터를 수집 및 가공, 저장하여 업무 운영 및 의사결정에 필요한 정보를 제공하는 시설과 장비를 의미한다.

데이터는 현실 세계에서 관찰과 측정을 통해 수집한 가공되지 않은 단순한 사실이나 결과값을 의미하는 반면, 정보는 의사결정에 활용하기 위해 데이터를 유용한 형태로 가공/처리해서 얻은 결과를 말한다.

정보시스템의 데이터 처리 형태는 일괄처리, 온라인 실시간처리, 분산 처리로 구분된다.

 

일괄처리 : 일정 시간 또는 일정량의 데이터를 모아서 한번에 처리하는 방식

온라인 실시간 처리 : 사용자가 데이터 처리를 요구할 경우 즉시 처리하여 결과를 산출하는 방식

분산처리 : 지리적으로 분산된 여러 대의 컴퓨터를 네트워크로 연결하여 논리적으로 하나의 시스템을 사용하는 것처럼 운영하는 방식

 

2. 데이터베이스

데이터베이스느 조직에서 업무 운영과 의사결정에 필요한 데이터들을 여러 사용자들이 공동으로 사용할 수 있도록 구조적으로 통합하여 컴퓨터로 저장한 데이터들의 모음을 말한다.

데이터베이스는 저장된 데이터와 저장된 데이터의 구조와 제약사항 등에 대한 메타데이터를 포함한다.

 

1.2 데이터베이스 시스템

 

1. 데이터베이스 시스템 정의

데이터베이스 시스템은 데이터베이스를 이용하여 데이터를 저장하고 관리해서 필요한 정보를 생성하는 컴퓨터 시스템을 말한다.

데이터베이스 시스템은 기존 파일시스템의 문제점인 데이터의 종속성과 중복성을 개선하기 위해 개발되었다.

파일시스템은 응용프로그램과 데이터 간의 상호의존 관계가 긴밀하여 파일에 저장된 파일데이터의 구조가 변경되면 응용프로그램을 수정해야 하는 데이터 종속성이 존재한다.

파일시스템에서는 같은 내용의 데이터가 여러 파일에 중복되어 저장되므로 데이터의 일관성이나 무결성 유지와 데이터 보안이 어렵다

 

2. 데이터베이스 시스템의 구성요소

데이터베이스 시스템은 데이터베이스 스키마와 메타데이터라고 불리는 시스템카탈로그와 데이터베이스관리시스템 데이터 베이스 사용자, 데이터베이스 언어, 데이터베이스 컴퓨터 등으로 구성된다.

 

3. 시스템 카탈로그

데이터 사전 또는 시스템 테이블이라고도 하며, 데이터베이스의 객체와 구조들에 관한 모든 데이터를 포함하는 시스템 테이블을 의미한다.

시스테 카탈로그는 사용자 릴레이션처럼 SELECT 문을 사용하여 내용을 검색할 수 있지만 어떤 사용자도 직접 갱신할 수 없다. 즉 DELETE, UPDATE, INSERT문을 사용하여 시스템 카탈로그를 변경할 수 없다.

DBMS는 시스템 카탈로그에 릴레이션, 애트리뷰트, 인덱스 사용자 권한 등 각 스키마 객체 유형별로 별도의 정보 테이블을 유지한다.

 

1.3 데이터베이스 사용자

 

1. 데이터베이스 관리자

데이터베이스를 정의하고 제어하는 사람 또는 그룹을 의미하며, 데이터베이스 관리 시스템과 컴퓨터 시스템에 대한 지식을 보유해야 한다.

데이터베이스를 설계하고 관리, 운용 및 통제하는 역할을 담당하며, 데이터베이스 시스템을 감시하고 성능을 분석한다.

 

2. 데이터 설계자

조직 또는 기업의 전사 데이터 구조를 체계적으로 정의하는 사람 또는 그룹을 의미한다.

데이터 설계 원칙과 데이터 표준 정의, 데이터 관리체계 수립 등 데이터 모델링과 데이터 구조 관리를 수행한다.

 

3. 응용 프로그래머

프로그래밍 언어를 이용하여 데이터베이스에 접근하여 데이터를 검색하거나 조작하는 사람을 의미한다.

프로그래밍 언어와 데이터베이스 언어, 데이터베이스 관리 시스템에 대한 지식이 있어야한다.

 

4. 일반 사용자

데이터를 검색하거나 조작하기 위해 데이터베이스 질의어를 이용하여 데이터베이스 관리 시스템에 접근하는 사람을 의미한다.

 

1.4 데이터베이스 관리 시스템의 개념과 기능

 

1. DBMS 개념

데이터베이스 관리 시스템은 데이터베이스를 통합 관리하느 소프트웨어 패키지를 의미한다 즉 사용자가 새로운 데이터베이스를 생성하고, 데이터베이스의 구조를 명시할 수 있게하고, 데이터를 효율적으로 질의하고 수정할 수 있도록 하며 시스템의 고장이나 권한이 없는 사용자로부터 데이터를 안전하게 보호하며 동시에 여러 사용자가 데이터베이스에 접근하는 것을 제어하는 소프트웨어 패키지이다.

-데이터 베이스를 정의하고, 질의어를 지원하고, 리포트를 생성하는 등의 작업을 수행하는 소프트웨어 

-데이터베이스에 대한 효율적 접근과 질의 기능 제공, 프로그램과 데이터 간의 독립성을 보장

 

2. DBMS 필수기능

정의기능 : 데이터베이스에 저장된 데이터의 타입과 구조, 제약조건 등을 명시하는 기능 제공

조작기능 : 데이터를 검색하고 삽입, 삭제, 갱신 등 데이터 처리 기능 제공

제어기능 : 데이터의 정확성과 안전성을 유지하기 위한 무결성 관리와 병행제어, 접근제어 등의 기능 제공

 

3. DBMS 도입 시 장단점

장점 :

데이터 중복 최소화

데이터 공용

무결성

일관성

표준화

보안

 

단점:

운영비 증대

복잡화

복잡한 백업과 회복

시스템 취약성

 

4. DBMS 구성요소

데이터정의어 컴파일러

질의어 처리기

데이터베이스 처리기

트랜잭션 관리자

저장 데이터 관리자

 

5. ANSI/SPARC 3단계 스키마 구조와 데이터 독립성

 

1. ANSI/SPARC 3단계 스키마 구조

미국 국립 표준화 기관 ANSI/SPARC 에서 1978년에 제정했으며 대부분의 상용 DBMS에서 활용하는 아키텍처로 외부 단계와 개념적 단계, 물리적 단계로 구성된다.

 

외부 스키마 : 각 사용자나 응용프로그래머가 접근하는 데이터베이스

하나의 데이터베이스에 여러 개의 외부 스키마 존재

서브 스키마라고도 함

 

개념 스키마 : 범 기관적 입장에서 데이터베이스

모든 응용시스템과 사용자가 필요로 하는 데이터를 통합한 데이터베이스

모든 개체, 관계, 제약조건, 접근 권한, 보안 무결성 규칙포함

하나만 존재하고 스키마 라고 함

 

내부 스키마 : 저장 장치 측명에서의 데이터베이스 저장 구조

내부 레코드 형식 index 유무, 내부 레코드 순서 등

물리적 스키마라고도 함

 

데이터를 사용자 관점, 통합 모델 관점, 저장장치의 3가지 관점에서 아키텍처와 상호 연관 관계를 정의한 3단계 스키마 구조로 데이터의 독립성을 보장한다.

 

2. 데이터 독립성

데이터베이스의 논리적/물리적 구조를 변경하더라도 응용프로그램이 영향을 받지 않는 특성을 말한다.

데이터 독립성을 보장하기 위해서는 데이터베이스 스키마 간의 사상이 필요하다. 스키마 간 사상 정보는 시스템 카탈로그에 저장된다.

 

1.6 스키마와 인스턴스

 

1. 스키마

스키마는 데이터베이스의 구조와 제약조건에 관한 전반적인 명세를 의미하며 메타데이터 또는 내포라고도 한다.

스키마는 실세계의 특정 부분을 표현하며 특정 데이터 모델을 사용하여 만들어진다. 즉 하나의 조직에서 관심 있는 데이터의 구조를 기술한 언어적이고 그래픽적인 표현으로 시간 독립적인 고정된 집합체이다.

데이터 모델은 데이터베이스의 구조를 기술하는데 사용되는 개념들의 집합과 연산자, 제약조건들을 포함한 것으로 다양한 데이터 모델이 존재한다.

스키마는 데이터의 구조를 나타내는 데이터 사전의 시스템 카탈로그에 저장된다.

 

2. 인스턴스

인스턴스는 스키마에 의해 정의된 특정 시점의 데이터들의 모임을 말하며, 시간에 따라 동적으로 변화한다.

인스턴스는 특정 시점의 데이터베이스 상태를 나타내며 외연이라고도 한다.

 

1.7 관계형 데이터베이스 기초

현재 가장 많이 사용되는 데이터베이스 모델로 데이터가 테이블 형태로 표현되며, 사용자가 데이터를 쉽게 다룰 수 있도록 해주는 질의어를 제공한다.

테이블 형태로 표현된 데이터는 단순해서 누구나 쉽게 이해할 수 있으며 SQL은 자연어에 가까운 문법을 가지고 있어서 배우기 쉽고, 데이터를 어떻게 가져올 것인가 대신에 어떤 데이터를 원하는지만 기술해주면 되기 때문에 사용자나 개발자의 입장에서는 데이터를 다루는 작업이 매우 단순해진다

SQL 명령이나 문법은 표준화되어 있으므로 대부분의 명령어는 모든 관계형 데이터베이스 제품에서 공통으로 사용할 수 있다.

 

1. 관계형 데이터베이스 주요 용어

릴레이션 : 2차원의 테이블

레코드 : 릴레이션의 각 행

튜플 : 레코드를 좀더 공식적으로 부르는 용어

애트리뷰트(속성) : 릴레이션에서 이름을 가진 하나의 열

도메인 : 한 속성에 나타날 수 있는 값들의 집합

차수 : 한 릴레이션에 들어 있는 속성의 수 

카디날리티 : 릴레이션의 튜플 수 시간이 지남에 따라 계속해서 변한다.

널 값 : 알려지지 않음 또는 적용할 수 없음을 나타내기 위해 사용되는 개념

릴레이션 스키마 : 릴레이션의 이름과 렐레이션의 애트리뷰트들의 집합을 말하며 릴레이션을 위한 틀

릴레이션 인스턴스 : 특정 시점에 릴레이션에 들어 있는 튜플들의 집합 시간의 흐름에 따라 변한다.

 

2. 관계형 데이터 모델 개념

2차원 테이블을 구성하여 테이블 내에 있는 속성 간의 관계를 설정하거나 테이블 간의 관계를 정의하는 DB 구조를 말한다.

데이터 간의 관계를 기본키와 이를 참조하는 외래키로 표현한다.

테이블 간의 관계를 자유롭게 표현한다.

과거 계층형, 네트워크형 데이터 모델이 있엇지만 관계형 데이터 모델의 구조가 기존 데이터 모델보다 좀더 유연하여 실세계를 현실감 있게 반영할 수 있었기 때문에 많은 데이터베이스시스템에 구현되었다.

 

2. 기본 SQL

 

2.1 SQL개요 

 

1. SQL 특징

 

비절차적 언어

사용자는 자신이 우너하는 바만 명시하며, 원하는 것을 처리하는 방법은 명시하지 않는다

 

대화식 인터페이스 또는 호스트 언어에 내장된 형태로 사용할 수 있다.

대화식 SQL : 사용자가 명령문을 명령창에 입력하고 실행 절차를 확인하는 방식

내포식 SQL :  프로그래밍 언어에 SQL 문을 내포해서 응용프로그램이 실행될 때 SQL 문이 작동하는 방식

 

2. SQL 종류 

 

SQL 명령문은 사용 목적과 영향에 따라 데이터 정의어 , 제이터 조작어, 데이터 제어어로 구분한다.

데이터를 검색하는 SELECT 명령문은 데이터 질의어로 분류하거나 데이터 조작어에 포함하기도 한다.

상용관계 DBMS마다 지원하는 SQL기능에 다소 차이가 있으니 SQL 기능과 문법을 확인할 후 사용한다.

 

3. SQL 문법

전제조건

SQL은 자연어에 가까운 문법을 가지고 있어서 배우기 쉽다.

데이터를 어떻게 가져올 것인가 대신에 어떤 데이터를 원하는지만 기술해주면 되기 때문에 데이터를 다루는 작업이 매우 단순해진다.

 

SQL 명령어나 문법은 표준화되어 있으므로 대부분의 명령어는 모든 관계형 데이터베이스 제품에서 공통으로 사용할 수 있다.

 

기본규칙

SQL 명령어와 테이블명, 컬럼명과 같은 객체 이름은 대소문자를 구분하지 않으나 데이터베이스에 저장된 데이터 값은 대소문자를 구분한다.

SQL 명령문은 세미콜론으로 종료해야 한다.

 

2.2 데이터 검색

 

1. SELECT 문 형식

관계대수의 셀렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 데이터 조회 명령이다.

 

2. SELECT 문을 이용한 데이터 검색

테이블에 저장된 모든 데이터를 검색하고자 할 때는 컬럼명 없이 아크테리스크 (*) 기호를 이용한다.

테이블에 저장된 데이터 중 특정 컬럼을 조회하고자 할 때는 해당 컬럼 이름을 SELECT 절 뒤에 기술한다.

SELECT 명령을 통해 검색한 결과는 중복이 포함된 상태로 출력된다. 중복을 제거한 겨로가를 검색하고자 할 때는 DISTINCT 기워드를 SELECT 뒤, 컬럼명 앞에 기술한다.

특정 조건을 만족하는 튜플을 검색하고자 할때는 WHERE 절을 사용한다. WHERE 절이 생략되면 테이블에 저장된 순서대로 모든 행을 출력한다.

와일드카드 문자를 이요하여 특정 조건을 만족하는 문자열을 포함하는 데이터를 검색한다.

-% : 문자가 없거나 하나 이상의 어떤 문자

-_ : 하나의 어떤 문자

두개 이상의 조건식을 연결하고자 할 경우 AND, OR ,NOT 연산자를 이용한다.

수치 데이터 또는 날짜 데이터에 대해 범위 검색을 하고자 할 때는 BETWEEN ~ AND 명령을 사용할 수 있다.

BETWEEN A AND B : 특정 컬럼의 값이 A 이상 B 이하에 포함되는 데이터를 검색한다.

특정 컬럼의 값이 검색하고자 하는 값 리스트에 해당하는지 검사한 후 리스트에 해당하는 데이터들을 검색하고자 할 때는 IN 연산자를 사용한다.

산술연산자를 이용해 검색결과에 연산을 수행한 결과를 확인할 수 있다. 산술 연산식을 기술하는 방식과 연관 우선순위는 일반적인 연산자 서술양식과 같다.

데이터베이스에서 아직 정해지지 않았거나, 적용할 수 없어 비워둔 값을 표기할 때는 NULL을 사용한다.

검색 결과를 정렬하고자 할 때는 ORDER BY 절을 사용한다.

SELECT한 결과는 기본적으로 튜플이 삽입된 순서대로 조회된다. 검색 결과로 출력되는 레코드들을 특정 기준에 맞춰 정렬하고자 할 겨우 ORDER BY 절을 사용한다.

ORDER BY 절에 정렬 기준이되는 컬럼과 정렬방벙 명시한다.

오름차순 : ASC 내림차순 : DESC

 

2.3 데이터 조작

 

1. INSERT 문

테이블에 새로운 레코드를 한번에 하나씩 삽입하고자 하면 기본 INSERT 문을 사용한다.

INTO 절에 컬럼명을 생략할 경우 테이블 생성 시 정의한 순서대로 값을 입력한다.

테이블에 새로운 레코드들을 한 번에 여러 개씩 삽입하고자 하면 INSERT 문에 서브쿼리를 이용한다.

VALUES 절 대신에 SELECT 절을 기술한다.

이때 SELECT 절의 컬럼의 "개수와 타입"이 INSERT INTO 절의 컬럼 "개수의 타입"과 일치해야 한다.

 

2. DELETE 문

한 테이블로부터 한개 이상의 레코드들을 삭제할 때 사용한다.

 

3. UPDATE 문

한 테이블에 들어 있는 레코드들의 컬럼 값을 수정할 떄 사용한다.

 

2.4 데이터 권한 제어 

 

1. DCL 기본 개념

데이터베이스의 규정이나 기법을 정의하고 제어하는 언어를 말한다.

사용자 권한을 부여하거나 취소하는 명령어나 트랜잭션을 제어하는 명령어를 포함한다.

-트랜잭션 제어 명령을 이요해 데이터베이스의 무결성과 보안, 회복, 도잇성 제어를 수행할 수 있다.

 

2. DCL 종류

DCL 명령어의 종류는 다음과 같다

 

COMMIT : 수행된 겨로가를 실제 물리적 디스크로 저장

ROLLBACK : 명령수행 실패를 의미한며 수행된 결과를 원복시킨다.

SAVEPOINT : 저장된 지점, 지정된 저장점부터 현재 까지 일부만 ROLLBACK 기능

GRANT :  데이터베이스 사용자에게 사용 권한 부여

REVOKE : 데이터베이스 사용자에게 부여된 사용권한 취소

 

3. 사용자 권한 제어 개요

데이터베이스 사용자의 권한을 제어하는 DCL 명령어는 GRANT와 REVOKE가 있다.

 

4. 권한 허가

데이터베이스의 서로 다른 객체들에 대해서 다양한 권한들이 존재한다. 객체의 생성자는 객체에 대한 모든 권한을 갖늗다.

생성자는 자신이 소유한 임의의 객체에 대한 특정 권한을 GRANT문은 사용하여 다른 사용자나 역할에게 허가할 수 있다.

 

5. 권한 취소

권한을 취소하면, 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게허가했던 권한들도 연쇄적으로 취소된다.

기본적으로 권한을 허가했던 사람만 그 권한을 취소할 수 있다.

 

6. 역할

여러 사용자들에 대한 권한 관리를 단순화하기 위해 역할을 사용한다

역할은 사용자에게 허가할 수 있는 연관된 권한들의 그룹을 말하며, 각 사용자는 여러 역할에 속할 수 있으며 여러 사용자가 같은 역할을 가질 수 있다.

같은 권한들의 집합을 여러 사용자에게 허가하느 대신에 이 권한들을 역할에게 허가하고 역할을 각 사용자에게 허가하는 것이 편리하다.

 

7. 객체 권한과 시스템 권한

시스템 권한은 사용자가 데이터베이스에서 특정 작업을 수핼할 수 있도록 하는 권한이다.

 

객체권한은 사용자가 특정 객체에 대해 특정 연산을 수행할 수 있도록 한다. 객체의 소유자는 객체에 대한 모든 권한을 보유하며 자신의 객체에 대한 권한을 다른 사용자나 역할에게 허가할 수 있다.

 

3. 데이터베이스 객체관리

 

3.1 데이터 정의어

 

1. DDL 대상

데이터베이스 객체를 정의하는 언어를 DDL이라한다. DBMS가 관리하는 테이블과 뷰, 인덱스 등을 데이터베이스 객체라 한다.

 

2. 객체 유형

DDL을 통해 정의할 수 있는 대상, 객체 유형은 다음과 같다.

스키마 : DBMS 특성과 구현 환경을 감안한 데이터 구조

도메인 : 속성의 데이터 타입과 크기, 제약조건 등을 지정한 정보

테이블 : 데이터의 저장 공간

뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 논리 테이블

인덱스 : 검색을 빠르게 하기 위한 데이터 구조

 

3. DDL 조작 방법

객체를 생성, 변경, 제거하기 위해 다음과 같은 명령어를 사용한다.

 

생성 : CREATE

변경 : ALTER

삭제 : DROP(객체 삭제), TRUNCATE(객체 내용 삭제)

 

4. DDL문 특징

데이터베이스 스키마를 컴퓨터가 이해할 수 있도록 기술하는 데 사용한다.

주로 데이터베이스 관리자나 데이터베이스 설계자가 사용한다.

데이터베이스의 메타데이터로 dd에 저장한다.

데이터베이스 스키마를 컴퓨터가 이해할 수 있도록 기술하는데 사용한다.

논리적 데이터 구조를 정의한다.

물리적 데이터 구조를 정의 한다.

명령 실행 후 기본적으로 Auto Commit된다.

 

3.2 테이블 관리

 

1. 테이블의 개념

데이터를 저장하는 객체로서 관계형 데이터베이스 기본 단위이다.

 

2. 테이블의 특징

어느 특정한 주제와 목적으로 만들어진다.

반드시 하나 이상의 컬럼을 가진다.

등록된 자료들이 있으며, 이자료들은 삭제하지 않는 한 지속해서 유지된다.

 

3. 테이블의 구조

관계형 데이터베이스에서는 모든 데이터를 컬럼과 행의 2차원 구조로 나타낸다. 새로 방향을 컬럼, 가로방향을 행 이라고 하고 컬럼과 행이 겹치는 하나의 공간을 필드라고 한다.

 

3.3 데이터 사전

 

1. 데이터 사전 개념

데이터 사전 또는 시스템 테이블이라고도 부른다.

데이터베이스의 객체를 포함하는 시스템 데이터베이스이다.

데이터사전에 저장된 정보를 메타데이터라고 한다.

데이터사전은 관계 DBMS마다 표준화되어 있지 않아서 관계 DBMS 마다 서로 다른 형태로 시스템 카탈로그 기능을 제공한다.

사용자가 SQL 문으로 내용 검색이 가능하다.

데이터베이스 관리 시스템에 의해 생성 및 유지된다

기본 테이블, 뷰, 인덱스 등이 변경되면 자동으로 데이터사전은 갱신된다.

위치 투명성 및 중복 투며성을 제공하는 데 필요한 모든 제어 정보도 포함하고 있다.

 

4. 트랜잭션 관리

 

4.1 트랜잭션 개요

 

1. 트랜잭션 개념

데이터베이스의상태를 변화시키기 위해서 수행하는 작업의 단위를 뜻한다.

사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업단위를 말한다.

하나의 트랜잭션은 Commit 또는 Rollback 된다.

 

2. 트랜잭션 특징

데이터의 무결성을 보장하기 위해 DBMS의 트랜잭션이 가져야 할 특성

 

원자성 : 한가지라도 실패할 경우 전체가 취소되어 무결성 보장

일관성 : 트랜잭션이 실행 성공 후 항상 모순없이 일관성 있는 DB상태 보존

고립성 : 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션 접근 불가

영속성 : 성공이 완료된 트랜잭션의 결과는 영구적으로 데이터베이스에 저장됨