01. 데이터베이스 시스템의 개념

데이터베이스와 데이터베이스 시스템

데이터, 정보, 지식

데이터가 모이면 정보가 되고, 정보가 모이면 지식이 된다. 데이터는 관찰의 결과로 나타난 정량적 혹은 정성적인 실제 값을 말하고, 정보는 데이터에 의미를 부여한 것을 말하며, 지식은 사물이나 현상에 대한 이해를 말한다. 예를들어 에베레스트 산의 높이는 데이터에, 에베레스트산의 지리적인 특성을 설명한 것은 정보에, 에베레스트산에 올라가는 가장 좋은 방법을 소개하는(정보를 포함한) 보고서는 지식에 해당된다.

검색과 변경 빈도에 따른 데이터베이스 유형

데이터베이스는 저장된 데이터의 양이 많고, 실시간 검색이 많은 분야일수록 활용도가 높다.

데이터베이스는 조직에 필요한 정보를 얻기 위하여 논리적으로 연관된 데이터를 모아 구조적으로 통합해 놓은 것이다.

데이터베이스의 개념

1. 통합된 데이터(integrated data) : 여러 곳에서 사용하던 데이터를 통합하여 하나로 저장한 데이터를 의미. 통합 시 중요한 점은 각자 사용하던 데이터의 중복을 최소화함으로써 중복 저장으로 인한 데이터 불일치 현상을 없애는 것이다.

2. 저장된 데이터(stored data) : 문서가 아닌 디스크, 테이프같은 컴퓨터 저장장치에 저장된 데이터. 사람의 눈으로 직접 볼 수는 없지만 컴퓨터 프로그램을 이용하여 디스플레이하거나 인쇄물로 출력할 수 있다.

3. 운영 데이터(operational data) : 조직의 목적을 위해 사용되는 데이터를 의미. 업무를 위해 검색을 할 목적으로 저장된 데이터. 목적에 관계없이 저장된 데이터나 단순 입출력 데이터같이 임시로 저장된 데이터는 운영 데이터에 속하지 않는다.

4. 공용 데이터(shared data) : 공동으로 사용되는 데이터를 의미. 데이터는 어느 순간이라도 둘 이상의 프로그램 또는 여러 사람이 동시에 사용할 수 있다.

데이터베이스의 특징

1. 실시간 접근성(real time accessibility) : 데이터베이스는 실시간으로 서비스된다. 수 초 내에 결과를 서비스한다.

2. 계속적인 변화(continuous change) : 데이터베이스에 저장된 내용은 어느 한 순간의 상태를 나타내지만, 데이터 값은 시간에 따라 항상 바뀐다. 데이터베이스는 삽입, 삭제, 수정 등의 작업으로 바뀐 데이터 값을 저장한다.

3. 동시공유(concurrent sharing) : 서로 다른 업무 또는 여러 사용자에게 동시 공유한다. 동시는 병행이라고도 하며, 데이터베이스에 접근하는 사용자 데이터 요청 프로그램이 동시에 여러개 있다는 의미이다.

4. 내용에 따른 참조(reference by content) : 데이터베이스에 저장된 데이터는 데이터의 물리적인 위치가 아니라 데이터 값에 따라 참조된다. 사용자가 원하는 데이터를 얻기 위해 데이터 값을 사용하여 조건을 제시하면 데이터베이스는 이에 해당하는 데이터를 검색해 준다. -> 정보를 가지고 검색?

데이터베이스 시스템의 구성

1. DBMS(데이터베이스 관리 시스템) : 데이터를 관리하는 소프트웨어 시스템으로 주기억장치에 상주하면서 사용자와 데이터베이스를 연결시켜준다. ex) 오라클, MySQL …

2. 데이터베이스 : 데이터를 모아둔 토대. 물리적으로는 컴퓨터 내부의 하드디스크에 저장된다.

3. 데이터 모델 : 데이터가 저장되는 기법에 관한 내용으로 눈에 보이지 않는 논리적인 개념.

데이터베이스 시스템의 발전

정보 시스템의 발전

1. 파일시스템 : DBMS가 없는 시스템으로, 데이터는 파일 단위로 저장되며 파일을 다루는 파일 서버가 있다.
2. 데이터베이스 시스템 : DBMS를 도입하여 데이터를 통합 관리하는 시스템이다.
3. 웹 데이터베이스 시스템 : 데이터베이스를 웹 브라우저에 사용하도록 제공하는 시스템이다. 웹 서버와 JSP, PHP, 웹 데이터베이스 연동 언어들을 사용한다.
4. 분산 데이터베이스 시스템 : 여러 곳에 분산된 DBMS 서버를 연결하여 운영하는 시스템으로 대규모 응용 시스템에 사용된다.

파일 시스템과 DBMS

파일 시스템과 DBMS의 비교

DBMS는 파일 시스템에 비해 CPU와 주기억장치 등 컴퓨터 자원을 많이 사용하지만 SQL 언어를 사용하여 응용 프로그램이 데이터에 쉽게 접근 할 수 있다. 즉 프로그래머가 해야 할 파일 관리를 DBMS를 통해 쉽게 할 수 있다.

DBMS의 장점

데이터를 많이 다루는 프로그램을 빨리 개발할 수 있고, 데이터 구조가 변경되어도 데이터의 독립성을 유지할 수 있다. 또 복구, 보안, 무결성 유지 등의 기능도 수행한다.

데이터베이스 시스템의 구성

데이터베이스 언어

데이터베이스 시스템은 데이터베이스 전용 언어인 SQL(Structured Query Language)를 사용한다. SQL의 핵심 조작어 중 데이터를 검색하는 질의(query)문이다. 질의문은 SELECT-FROM-WHERE 구조로 되어 있다.

//BOOK 테이블에서 모든 도서이름과출판사를 검색하시오.

SELECT bookname, publisher
FROM   Book;

//가격이 10,000원 이상인 도서이름과 출판사를 검색하시오.

SELECT bookname, publisher
Form   Book
       WHERE     price >= 10000;

SQL

데이터베이스 시스템에 사용하는 전용 언어.

• 데이터 정의어 : CREATE, ALTER, DROP 문과 같이 DBMS에 저장된 테이블의 구조를 정의한다.
• 데이터 조작어 : SELECT, INSERT, DELETE, UPDATE 문과 같이 데이터를 검색, 삽입, 삭제, 수정하는데 사용한다.
• 데이터 제어어 : GRANT, REVOKE 문과 같이 데이터의 사용 권한을 관리한다.

데이터베이스 사용자

1. 일반 사용자 : 은행의 창구 혹은 관공서의 민원 접수처 등에서 데이터를 다루는 업무를 하는 사람. 응용 프로그래머가 작성한 프로그램을 이용하는 사람이다.

2. 응용 프로그래머 : 일반사용자가 사용할 수 있도록 프로그램을 만드는 사람이다. 응용 프로그래머는 자바, C, JSP 등의 프로그래밍 언어와 SQL을 사용하여 일반 사용자를 위한 사용자 인터페이스와 데이터를 관리하는 응용 로직을 개발한다. 응용프로그래머는 데이터베이스를 이용하여 문제를 해결하는 프로그램을 작성하는 사람이다.

3. SQL 사용자 : SQL을 사용하여 업무를 처리하는 IT 부서의 담당자로, 응용 프로그램으로 구현되어 있지 않은 업무를 SQL을 사용하여 처리한다. 주로 데이터 검색, 데이터 구조 변경, 데이터에 관한 통계 처리 등 데이터를 모니터링 하는 업무를 하거나 보고서 형태로 만들어 상부에 보고하는 업무를 한다. SQL 질의를 작성하거나 SQL을 이용하여 프로그램을 작성하는 사람이다.

4. 데이터베이스 관리자(DataBase Administrator, DBA) : 데이터베이스 운영 조직의 데이터베이스 시스템을 총괄하는 사람이다. DBA는 데이터 설계, 구현, 유지보수의 전 과정을 담당한다. 또한 사용자 통제, 보안, 성능 모니터링, 데이터 전체 파악 및 관리, 데이터 이동 및 복사 등 제반 업무를 맡는다.

DBMS

DBMS는 사용자와 데이터베이스를 연결시켜주는 소프트웨어.

데이터 모델

데이터 모델은 데이터베이스 시스템에서 데이터를 저장하는 이론적인 방법에 관한 것으로, 데이터베이스에 데이터가 어떻게 구조화되어 저장되는지를 결정한다. 가장 많이 사용되는 데이터모델은 관계데이터모델(relational data model)이다.

1. 포인터 사용 : 계층 데이터모델, 네트워크 데이터 모델 : 포인터 값을 데이터에 저장하는 방법이다. 데이터를 직접 찾아갈 수 있어서 프로그램 속도는 빠르지만, 포인터를 다루는 프로그래밍을 해야하기 때문에 응용 프로그램 개발 속도면에서 느리다는 단점이 있다. (포인터는 주소를 의미한다.). 계층 데이터 모델은 실무에서 잘 안씀.

2. 속성 값 사용 : 관계 데이터 모델 : 속성값을 데이터에 직접 저장하는 방법. 데이터를 찾을 대는 값을 이용하여 알아낸다. 포인터를 사용하는 방법보다 속도는 조금 느리지만, 개념이 쉽고 프로그램 개발이 빠르기 때문에 관계 데이터 모델을 비롯한 많은 데이터베이스 시스템에서 이 방법을 사용한다. 직접적인 포인터는 없고 값을 이용하여 관계를 찾아간다. 실무에서 많이 쓴다.

3. 객체식별자 사용 : 객체 데이터 모델 : 객체의 고유 식별자를 테이블에 저장하는 방법. 실무에서 잘 안 씀

데이터베이스의 개념적 구조

1975년 ANSI(American National Standard Institue)에서 데이터베이스를 개념적으로 이해하기 위해 데이터베이스 구조에 관한 안을 만들었다. 이 안은 데이터베이스를 보는 관점(view)을 세 단계로 분리한 것으로, 3단계 데이터베이스 구조(3-layer database architecture)라고 부른다. 각 단계의 스키마 용어는 조직이나 구조를 의미한다.

1. 외부 단계 : 일반 사용자나 응용 프로그래머가 접근하는 계층으로 전체 데이터베이스 중에서 하나의 논리적인 부분을 의미한다. 여러개의 외부 스키마가 있을 수 있다. 외부 스키마는 서브 스키마라고도 부르며, 뷰의 개념이다. 예로 학생처의 학생정보, 교무처의 수강정보 등은 전체 데이터베이스의 일부분으로 각 부서의 사용자들에게만 필요한 부분 각각의 스키마라고 할 수 있다.

2. 개념 단계 : 전체 데이터베이스의 정의를 의미한다. 통합 조직별로 하나만 존재하며 DBA가 관리한다. 즉 하나의 데이터베이스에는 하나의 개념스키마가 있다. 개념 스키마는 저장 장치에 독립적으로 기술되며, 데이터의 관계, 제약사항, 무결성에 대한 내용이 포함된다. 예로 학생정보, 수강정보 등을 모두 모은 대학 전체 데이터베이스를 뜻한다. (딱 1개.)

3. 내부 단계 : 물리적 저장 장치에 데이터베이스가 실제로 저장되는 방법의 표현이다. 인덱스, 데이터 레코드의 배치 방법, 데이터 압축 등에 관한 사항이 포함된다. 내부 스키마는 하나이다. 예로 전체 데이터베이스가 실제 하드디스크에 저장되는 물리적인 구조를 뜻한다. (저장되는데 관련된 정보, 어떤식으로 저장할 것인지.)

• 외부/개념 매핑 : 사용자의 외부스키마와 개념 스키마 간의 매핑(사상)으로 외부 스키마의 데이터가 개념 스키마의 어느 부분에 해당되는지 대응시킨다.
• 개념/내부 매핑 : 개념 스키마의 데이터가 내부 스키마의 물리적 장치 어디에 어떤 방법으로 저장되는지 대응시킨다.

데이터 독립성

3단계 데이터베이스 구조의 특징은 데이터 독립성이다.(외부-개념-내부. 서로 영향력을 끼치지 않는다.) 데이터 독립성에는 두 가지가 있다.

• 논리적 데이터 독립성(logical data independence) : 외부 단계와 개념 단계 사이의 독립성으로, 개념 스키마가 변경되어도 외부 스키마에는 영향을 미치지 않도록 지원한다.

• 물리적 데이터 독립성(physical data independence) : 개념 단계와 내부 단계 사이의 독립성으로, 저장장치 구조 변경과 같이 내부 스키마가 변경되어도 개념 스키마에 영향을 미치지 않도록 지원한다.

02. 관계 데이터 모델

관계 데이터 모델의 개념

릴레이션의 개념

릴레이션은 관계 데이터 모델의 핵심적인 개념으로 행과 열로 구성된 테이블을 말한다.

릴레이션 스키마와 인스턴스

릴레이션은 스키마와 인스턴스로 이루어진다. 스키마는 관계 데이터베이스의 릴레이션이 어떻게 구성되는지, 어떤 정보를 담고 있는지에 대한 기본적인 구조를 정의한다. 테이블에서 스키마는 테이블의 헤더에 나타나며 각 데이터의 특징을 나타내는 속성, 자료타입 등의 정보를 담고있다. 인스턴스는 정의된 스키마에 따라 테이블에 실제로 저장되는 데이터의 집합을 의미한다.

릴레이션 스키마

릴레이션 스키마는 릴레이션에 어떤 정보가 담길지 정의한다.

• 속성(attribute) : 릴레이션 스키마의 열
• 도메인(domain) : 속성이 가질 수 있는 값의 집합. 값의 영역. 범주. ex) 점수 100점 만점 시 0~100까지
• 차수(degree) : 속성의 개수

릴레이션 인스턴스

릴레이션 인스턴스는 릴레이션 스키마에 실제로 저장된 데이터의 집합이다.

• 투플(tuple) : 릴레이션의 행
• 카디날리티(cardinality) : 투플의 수

릴레이션 구조와 관련된 용어

릴레이션의 특징

★★★★★기본!

• 속성은 단일 값을 가진다 : 각 속성의 값은 도메인에 정의된 값만을 가지며 그 값은 모두 단일값이어야 한다.
• 속성은 서로 다른 이름을 가진다 : 속성은 한 릴레이션에서 서로 다른 이름을 가져야만 한다.
• 한 속성의 값은 모두 같은 도메인 값을 가진다 : 한 속성에 속한 열은 모두 그 속성에서 정의한 도메인 값만 가질 수 있다.
• 속성의 순서는 상관없다 : 속성의 순서가 달라도 릴레이션 스키마는 같다.
• 릴레이션 내의 중복된 투플은 허용하지 않는다 : 하나의 릴레이션 인스턴스 내에서는 서로 중복된 값을 가질 수 없다. 즉 모둔 투플은 서로 값이 달라야 한다.
• 투플의 순서는 상관없다 : 투풀의 순서가 달라도 같은 릴레이션이다.

관계 데이터 모델

관계 데이터 모델은 데이터를 2차원 테이블 형태인 릴레이션으로 표현하며 릴레이션에 대한 제약조건과 관계연산을 위한 관계대수를 정의한다.

제약조건은 각 릴레이션에 저장된 데이터 값이 가져야 하는 제약을 말한다. 예를들어 나이는 음수가 되면 안된다든지, 주문 릴레이션의 고객번호는 고객 리레이션에서만 참조해야한다든지 하는 규칙이다.

관계연산은 릴레이션을 다루는 연산규칙으로, 예를들면 주문 릴레이션과 고객 릴레이션 등을 조작하여 특정 고객의 주문 내역을 검색하는 행위를 의미한다.

무결성 제약조건

키 (투플을 구분짓는)

• 키는 무엇인가를 유일하게 식별한다는 의미가 있다.
• 키가 되는 속성(혹은 속성의 집합)은 반드시 값이 달라서 투플들을 서로 **구별**할 수 있어야 한다.
• 키는 릴레이션 간의 관계를 맺는 데도 사용된다.

고객 테이블

키값으로 쓸 수 있는 것은 고개번호, 주민번호, 고객번호+이름(결합), 고개번호+주민번호(결합)

슈퍼키

슈퍼키는 릴레이션 내 투플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합을 말한다.

• 고객번호 : 고객별로 유일한 값이 부여되어 있으므로 투풀을 식별할 수 있다.
• 이름 : 동명이인이 있으면 투플을 유일하게 식별할 수 없다.
• 주민번호 : 개인별로 유일한 값이 부여되어 있으므로 투플을 식별할 수 있다.
• 주소 : 가족끼리는 같은 정보를 사용하므로 투플을 식별할 수 없다.
• 핸드폰 : 한 사람이 여러 개의 핸드폰을 사용할 수 있고, 반대로 핸드폰을 사용하지 않는 사람이 있을 수 있으므로 투플을 식별할 수 없다.

핸드폰 번호가 없으면 데이터베이스에서는 값이 없다는 의미로 NULL 값을 사용한다.

후보키

• 후보키는 투플을 유일하게 식별할 수 있는 속성의 최소 집합이다. (유일성 + 최소성)
• 위의 테이블에서 슈퍼키 후보는 많지만 후보키는 주민번호, 고객번호만 될 수 있다.
• 두 개 이상의 속성으로 이루어진 키를 복합키라고 한다.

기본키

• 기본키는 여러 후보키 중 하나를 선정하여 대표로 삼는 키를 말한다.
• 주민번호와 고객번호를 선택할 수 있다.
• 최근 개인정보 보호에 관한 관심이 높아지면서 주민번호같은 중요한 정보를 기본키로 사용하는 것은 부담되므로 고객번호를 기본키로 선택하는게 좋다.

기본키 선정 시 고려사항

지금까지 내용을 토대로 기본키 선정 시 고려사항을 정리한다면 다음과 같다. 다음 고려사항 중 일부는 기본키 제약조건이라고 한다.

• 릴레이션 내 투플을 식별할 수 있는 고유한 값을 가져야 한다.
• NULL 값은 허용하지 않는다.
• 키값의 변동이 일어나지 않아야 한다.
• 최대한 적은 수의 속성을 가진 것이라야 한다.
• 향후 키를 사용하는 데 있어서 문제 발생 소지가 없어야 한다.

릴레이션 스키마를 표현할 때 기본키는 밑줄을 그어 표시한다.

대리키

기본키가 보안을 필요로하거나, 여러개의 속성으로 구분되어 복잡하거나, 마땅한 기본키가 없을 때는 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 경우가 있다. 이러한 키를 대리키 혹은 인조키라고 한다.

대체키

대체키는 기본키로 선정되지 않은 후보키를 말한다. 고객 릴레이션의 경우 고객번호와 주민번호 중 고객번호를 기본키로 정하면 주민번호가 대체키가 된다.

외래키

외래키는 다른 릴레이션의 기본키를 참조하는 속성을 말한다.

• 관계 데이터 모델의 특징인 릴레이션간의 관계를 표현한다.
• 외래키는 다른 릴레이션의 기본키를 참조하는 속성이다.
• 외래키가 성립하기 위해서는 참조하고 참조되는 양쪽 릴레이션의 도메인이 서로 같아야 한다.
• 참조되는 릴레이션의 기본키 값이 변경되면 이 기본키를 참조하는 외래키 값 역시 변경되어야 한다.
• 외래키는 참조되는 릴레이션의 기본키와 달리 NULL값을 포함할 수 있고 중복 값도 허용한다.
• 외래키 사용 시 참조하는 릴레이션과 참조되는 릴레이션이 꼭 다른 릴레이션일 필요는 없다. 즉 자기 자신의 기본키를 참조할 수 도 있다.
• 외래키가 기본키의 일부가 될 수 있다.

무결성 제약조건

• 도메인 무결성 제약조건 : 도메인 제약이라고도 하며, 모든 투플은 릴레이션 스키마에 정의된 각 속성의 도메인에 지정된 값만을 가져야 한다는 조건이다. (삽입 가능한 데이터 값을 제한). ex) 나이 음수 x.

• 개체 무결성 제약조건 : 기본키 제약이라고도 한다. 릴레이션은 기본키를 지정하고 그에 따른 무결성 원칙, 즉 기본키는 NULL값을 가져서는 안되며 릴레이션 내에 오직 하나의 값만 존재해야 한다는 조건이다.

• 참조 무결성 제약조건 : 외래키 제약이라고도 한다. 참조 무결성 제약조건은 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 같아야 하며, 자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다는 것이다.

제약 조건의 정리

무결성 제약조건의 수행

릴레이션은 데이터의 변경이 일어날 때 제약조건에 위배될 수 있다. 따라서 제약조건의 준수 여부는 데이터의 변경(삽입, 수정, 삭제)이 있을 때마다 확인해야 한다.

• 개체 무결성 제약조건 관계 데이터베이스 시스템에서는 DBMS는 투플을 삽입하거나 수정할 때마다 개체 무결성 제약조건을 지키는 지 확인한다. 즉 기본키 값이 유일한지, NULL 값이 아닌지를 검사한다.

• 참조 무결성 제약조건 참조 무결성 제약조건은 개체 무결성 제약조건과 달리 단일 릴레이션에 관한 내용이 아니다. 따라서 두 릴레이션간의 참조 관계에 따라 좀 더 복잡한 처리를 수행해야 한다. 참조 무결성 제약조건을 수행하기 위한 네 가지 옵션은 RESTRICTED, CASCADE, DEFAULTE, NULL등이 있다.

03. 관계대수

관계대수

관계대수란 릴레이션에서 원하는 결과를 얻기 위해 수학의 대수와 같은 연산을 이용하여 질의하는 방법을 기술하는 언어이다. SQL 언어는 관계해석을 기반으로 하지만 DBMS 내부에서는 관계대수에 기반을 둔 연산을 수행하여 최종 결과 집합을 반환하게 된다.

관계대수 연산자

수학에서 연산자란 피연산자에 적용되는 연산기호를 말한다. 예를들어 2+3에서 2와 3은 피연산자이고, +는 연산자이다. 관계대수 연산자 역시 릴레이션 연산에 사용되는 기호로 릴레이션을 피연산자로 한다.

• 순수 관계연산(관계 데이터 모델을 위해 고안됨) : 셀렉션, 프로젝션, 조인, 디비전, 개명
• 일반 연산집합(수학의 집합이론에서 차용됨) : 합집합, 교집합, 차집합, 카티션 프로덕트

연산자 중 피연산자의 개수가 한 개인 연산자를 단항 연산자, 두 개인 연산자를 이항 연산자라고 한다. 또 관계대수 연산자는 기본 연산자 다섯 개와 기본 연산자로부터 유도가 가능한 유도 연산자로 구분한다.

관계대수식

관계대수는 릴레이션 간 연산을 통해 결과 릴레이션을 찾는 절차를 기술한 언어이다. 관계대수 연산을 수행하기 위한 식을 관계대수식이라고 한다.

• 단항 연산자 : 연산자_<조건> 릴레이션</sub>
• 이항 연산자 : 릴레이션 1 연산자_<조건> 릴레이션 2</sub>

관계대수식의 결과는 릴레이션으로 반환되며, 반환된 릴레이션은 릴레이션의 모든 특징을 따른다. 즉 중복된 투플은 존재하지 않는다. 만약 중복된 투플이 있으면 하나만 표시한다.

관계대수식의 사용 예

R1)

R2)

셀렉션(σ) σ_{A=a1 or A=a2} (R1)

R1에서 조건에 맞는 투플을 추출한다.

프로젝션(π) π_{A, B} (R2)

R2에서 조건에 맞는 속성만을 추출한다.

합집합(∪) R1 ∪ R2

R1과 R2의 합집합을 구한다.

차집합(-) R1 - R2

R1과 R2의 차집합을 구한다.

조인(⋈) R1 ⋈_R1.C=R2.C R2

R1과 R2의 카티션 프로덕트를 구하여 조건에 맞는 투플을 추출한다.

셀렉션과 프로젝션

셀렉션과 프로젝션은 관계대수에서 가장 기본적인 연산으로 하나의 릴레이션을 대상으로 하는 단항 연산자이다. 연산의 결과 역시 하나의 릴레이션인데, 셀렉션은 대상 릴레이션에서 인스턴스의 일부, 즉 투플의 집합을 반환하며 프로젝션은 대상 릴레이션에서 지정된 속성값 일부를 반환한다.

셀렉션

셀렉션은 단어 그대로 릴레이션의 투플을 추출하기 위한 연산이다. 결과 릴레이션의 차수는 대상 릴레이션과 동일하고 카디날리티는 대상 릴레이션보다 작거나 같다. 조건으로는 ‘<속성>=<상숫값 또는="" 속성="">'형태가 올 수 있고 R은 릴레이션을 나타낸다.

σ_<조건> (R)</sub>

질의) 마당서점에서 판매하는 도서 중 8,000원 이하인 도서를 검색하시오.

풀이 > σ_가격<=8000 (도서)

조건이 여러개라면 ∧(and) ∨(or) ￢(not) 기호를 이용하여 표현 할 수 있다. 예를들어 ‘가격이 8,000원 이하이고 도서번호가 3 이상인 도서’를 검색하려면 σ_{가격<=8000 ∧ 도서번호 >= 3} (도서)

프로젝션

프로젝션은 릴레이션의 속성을 추출하기 위한 연산으로 단항 연산자이다. 결과 릴레이션의 차수는 대상 릴레이션의 차수보다 작거나 같고 카디날리티는 동일하다.

π_{<속성리스트>} (R)</sub>

질의) 신간도서 안내를 위해 (이름, 주소, 핸드폰)의 카탈로그 주소록을 만드시오.

풀이 > π_{이름, 주소, 핸드폰} (고객)

집합연산

합집합, 교집합, 차집합, 카티션 프로덕트는 기본 연산자이고, 교집합은 기본 연산자로 정의가 가능한 유도된 연산자이다. 집합 연산자는 두 개의 릴레이션을 대상으로 하는 이항 연산자이다. 카티션 프로덕트를 제외한 합집합, 교집합, 차집합은 두 릴레이션의 차수 및 도메인과 속성의 순서가 동일해야 한다. 즉, 각 릴레이션의 속성 수가 같고 순서도 같아야 하며 각각 동일한 도메인으로 대응되어야 한다. 이 조건을 합병가능이라고 한다. 관계대수의 집합연산은 합병가능해야 성립된다. 이때 속성 이름은 동일하지 않아도 되며, 반환되는 릴레이션의 속성 이름은 첫 번째 릴레이션의 이름과 같다.

수학의 집합연산과 동일한 방법으로 합집합, 교집합, 차집합, 카티션 프로덕트 등이 있다.

합집합(중복 허용 x)

두 개의 릴레이션은 서로 같은 속성 순서와 도메인을 가져야 한다. 반환되는 결과는 첫 번째 릴레이션의 속성 이름을 가진다. 작성 형식은 다음과 같다. R과 S는 릴레이션을 나타낸다.

R ∪ S

질의) 마당서점은 지점 A와 지점 B가 있다. 두 지접의 도서는 각 지점에서 관리하며 릴레이션 이름은 각각 도서A, 도서B이다. 마당서점의 도서를 하나의 릴레이션으로 보이시오.

도서A ∪ 도서B

교집합

교집합 역시 합병가능한 두 릴레이션을 대상으로 하며, 두 릴레이션이 공통으로 가지고 있는 투플을 반환한다.

R ∩ S

질의) 마당서점의 두 지점에서 동일하게 보유하고 있는 도서 목록을 보이시오.

도서A ∩ 도서B

차집합

차집합은 첫 번째 릴레이션에는 속하고 두 번째 릴레이션에는 속하지 않는 투플을 반환한다.

R - S

질의) 마당서점의 두 지점 중 지점 A에서만 보유하고 있는 도서 목록을 보이시오.

도서A - 도서B

카티션 프로덕트

지금까지의 집합연산이 두 릴레이션의 수직적 연산이었다면 카티션 프로덕트는 수평적 연산이다. 카티션 프로덕트는 두 릴레이션을 연결시켜 하나로 합칠 때 사용한다. 결과 릴레이션은 첫 번째 릴레이션의 오른쪽에 두 번째 릴레이션의 모든 투플을 순서대로 배열하여 반환한다. 속성과 도메인이 같을 필요는 없다. 결과 릴레이션의 차수는 두 릴레이션의 차수의 합이며, 카디날리티는 두 릴레이션의 카디날리티의 곱이다. 예를 들어 첫 번째 릴레이션의 차수가 4, 카디날리티가 3이고 두 번째 릴레이션의 차수가 5, 카디날리티가 4라면 결과 릴레이션의 차수는 9(4+5), 카디날리티는 12(3x4)가 된다.

만약 두 릴레이션이 동일한 속성 이름을 가지고 있으면 충돌 할 수도 있다. 이 때는 결과 릴레이션의 속성 이름을 ‘<릴레이션 이름="">.<속성 이름="">'의 형태로 표현하거나 순서(위치)를 사용하여 표현한다.

R x S

질의) 고객 릴레이션과 도서 릴레이션의 카티션 프로덕트를 구하시오(결과 개수가 많으므로 투플을 일부 삭제한 릴레이션을 사용한다).

고객 x 주문

카티션 프로덕트는 두 릴레이션을 무조건 수평으로 합친 결과를 반환하기 때문에 결과에 의미가 없으며, 대부분 유용한 자료로 활용하기 힘들다. 이때 셀렉션과 프로젝션 연산을 조합하여 사용하면 유용하다. 의미있는 투플을 찾아내는 작업은 조인에서 배운다.

조인

조인은 두 릴레이션의 공통 속성을 기준으로 속성값이 같은 투플을 수평으로 결합하는 연산이다. 즉 두 릴레이션을 카티션 프로덕트 연산을 한 후 셀렉션 연산을 한 것으로 정의할 수 있다. 두 개의 릴레이션을 대상으로 하는 이항 연산자이다.

R과 S는 릴레이션이며 c는 조인 조건을 나타낸다. 오른쪽 식은 기본 연산자로 조인 연산을 표현한 것으로, 조인 연산은 카티션 프로덕트 연산에 조인 조건을 적용한 것과 같다.

R ⋈ _c S = σ_c(RxS)

조인을 수행하기 위해서는 두 릴레이션의 조인에 참여하는 속성이 서로 동일한 도메인으로 구성되어야 한다. 조인 연산의 결과는 공통 속성의 속성값이 동일한 투플만을 반환한다.

세타조인과 동등조인

세타조인

세타조인은 조인에 참여하는 두 릴레이션의 속성값을 비교하여 조건을 만족하는 투플만 반환한다. 세타조인의 조건은 {=, ≠, ≧, ≦, <, >}중 하나가 된다.

작성 형식은 다음가 같다. R과 S는 릴레이션이며 r은 R의 속성, s는 S의 속성을 나타낸다.

R ⋈ _{(r 조건 s)} S

동등조인

동등조인은 세타조인에서 = 연산자를 사용한 조인을 말한다. 보통 조인 연산이라고 하면 동등조인을 지칭한다.

R ⋈ _(r=s) S

질의 ) 고객과 고객의 주문 사항을 모두 보이시오.

고객 ⋈ _{(고객.고객번호=주문.주문번호)} 주문

일치하는 내용만 결합

p104 표 참조

자연조인

자연조인은 동등조인에서 조인에 참여한 속성이 두 번 나오지 않도록 두 번째 속성을 제거한 결과를 반환한다. 자연조인의 결과 차수는 ‘두 릴레이션의 차수의 합 -1’이다. (중복되는 속성 제거. 제대로 된 조인?)

조인에 참여한 속성이 두 번 나오지 않도록 중복된 속성을 제거한 결과를 반환한다.

R ⋈ _{N(r, s)} S

질의) 고객과 고객의 주문 사항을 모두 보여 주되 같은 속성은 한 번만 표시하시오.

고객 ⋈ _{N(고객.고객번호, 주문.고객번호)} 주문

P105 표 참조

외부조인과 세미조인

외부조인

외부조인은 자연조인의 확장된 형태이다. 외부조인은 자연조인 시 조인에 실패한 투플을 모두 보여주되 값이 없는 대응 속성에는 NULL값을 채워서 반환한다.

• 왼쪽_left 외부조인 : R ⟕ _{(r, s)} S
• 완전_full 외부조인 : R ⟗ _{(r, s)} S
• 오른쪽_right 외부조인 : R ⟖ _{(r, s)} S

질의) 마당서점의 고객과 고객의 주문 내역을 보이시오.

1. 고객 기준으로 주문 내역이 없는 고객도 모두 보이시오.
2. 주문 내역이 없는 고객과 고객 릴레이션에 고객번호가 없는 주문을 모두 보이시오.
3. 주문 내역 기준으로 고객 릴레이션에 고객번호가 없는 주문도 모두 보이시오.

고객 ⟕ _{(고객.고객번호, 주문.고객번호)} 주문 고객 ⟗ _{(고객.고객번호, 주문.고객번호)} 주문 고객 ⟖ _{(고객.고객번호, 주문.고객번호)} 주문

p107 표 참조

##### 세미조인

세미조인은 자연조인을 한 후 두 릴레이션 중 한쪽 릴레이션의 결과만 반환한다. (세미조인은 릴레이션이 줄어든다. 앞에꺼는 다 늘어남.)

세미조인은 기호에서 닫힌 쪽 릴레이션의 투플만 반환한다.

p108 참조

### 디비전

디비전은 다른 연산과 달리 릴레이션의 속성값의 집합으로 연산을 수행한다. 디비전 연산은 특정 값들을 모두 가진 투플을 찾을 때 사용한다. 그러나 실제로 자주 사용되지 않으며 조인 연산을 이용하여 디비전 연산을 수행할 수 있다.

속성 A와 B로 이루어진 릴레이션 R과 속성 B로 이루어진 릴레이션 S의 디비전 연산은, 릴레이션 S의 속성 B값과 서로 동일하게 대응하는 릴레이션 R의 속성 A의 투플들을 반환한다.

R ÷ S

### 관계대수 사용예제

관계대수식은 연산자 하나만 단독으로 사용하는 경우는 드물고, 여러 연산자를 복합하여 사용하는 경우가 많다. 예를들어 카티션 프로덕트의 경우 의미 없는 결과를 반환하므로 프로젝션과 셀렉션 연산을 조합해서 사용한다.

다음은 셀렉션, 프로젝션, 집합연산을 복합하여 사용

질의) 마당서점의 도서 중 가격이 8,000원 이하인 도서이름과 출판사를 보이시오.

마당서점의 지점이 하나인 경우 π _{도서이름, 출판사} (σ_가격<=8000 도서) p110 표 참조

마당서점의 지점이 둘 이상인 경우 π _{도서이름, 출판사} ((σ_가격<=8000 도서 A) ∪ (σ_가격<=8000 도서 B)) p111 표 참조

다음은 카티션 프로덕트와 조인을 사용한 연산이다 (방법은 동등조인 방법도 ok. 자연조인도)

질의) 마당서점의 박지성 고객의 거래 내역 중 주문번호, 이름, 가격을 보이시오.

π _{주문.주문번호, 고객.이름, 주문.판매가격} (σ_{고객.고객번호=주문.고객번호 AND 고객.이름=’박지성’} (고객 X 주문)) p112 표 참조

같은 질의를 조인을 사용하여 간결한 식으로 만든다.

π _{주문번호, 이름, 판매가격} (σ_{이름=’박지성’} (고객 ⋈ _{고객.고객번호=주문.주문번호} 주문)) p113 표 참조

데이터베이스 프로그래머

SQL 소개

DBMS에 원하는 내용을 비교적 쉽게 알려주고 결과를 얻는 데 사용하는 데이터베이스 전용 언어가 SQL이다. DBMS는 SQL문을 해석하고 프로그램으로 변환하여 실행 한 후 결과를 알려준다. SQL은 자바나 C같은 완전한 프로그래밍 언어는 아니다. 대신 데이터 부속어라고 부르는데, 그 이유는 데이터베이스의 데이터와 메타 데이터를 생성하고 처리하는 문법만 갖고 있기 때문이다.

SQL과 일반 프로그래밍 언어의 차이점

SQL은 기능에 따라 3가지로 나뉜다.

• 데이터 정의어 DDL : 테이블이나 관계의 구조를 생성하는데 사용하며 CREATE(만들고), ALTER(수정), DROP(제거)문 등이 있다.
• 데이터 조작어 DML : 테이블에 데이터를 검색,삽입,수정,삭제하는데 사용하며 SELECT(검색), INSERT(테이블이 아닌 투플을 삽입), DELETE(테이블이 아닌 투플을 수정), UPDATE(테이블이 아닌 투플을 삭제) 문 등이 있다. 여기서 SELECT문은 특별히 질의어라고 부른다.
• 데이터 제어어 DCL : 데이터의 사용 권한을 관리하는데 사용하며 GRANT, REVOKE문 등이 있다.

SQL문을 이해하기 위해 SELECT 문의 문장 구조를 살펴보면 다음과 같다.

SELECT : 질의 결과 추출되는 속성 리스트를 열거한다.
FROM : 질의에 어느 테이블이 사용되는지 열거한다.
WHERE : 질의의 조건을 작성한다.

예를들어 Customer 테이블에서 김연아 고객의 전화번호를 찾으시오라는 질의를 표현하면 다음과 같다.

SELECT phone
FROM Customer
WHERE name=’김연아’;

SQL문은 실행 순서가 없는 비절차적인 언어이다. 즉 찾는 데이터만 기술하고 어떻게 찾는지는 기술하지 않는다.

SELECT 문 문법

SQL의 SELECT문은 데이털르 검색하는 기본 문장으로, 특별히 질의어(query)라고 부른다. SELECT문의 기본 문법은 다음과 같다.

SELECT [ALL | DISTINCT] 속성이름(들)
FROM 테이블 이름(들)
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING 검색조건(들)]
[ORDER BY 속성이름 [ASC | DESC]]

[] : 대괄호 안의 SQL 예약어들은 선택적으로 사용한다(옵션)
| : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

SELECT, FROM은 기본! 무조건 써야 한다.

질의 3-3) 도서 테이블에 있는 모든 출판사를 검색하시오.

SELECT publisher
FROM Book;

중복을 제거하고 싶으면 DISTICNT 키워드 사용

SELECT DISTINCT publisher
FROM Book;

조건 검색

조건에 맞는 검색을 할 때는 WHERE 절을 사용한다.

WHERE 절에 조건으로 사용할 수 있는 술어

집합

WHERE 절에 두 개 이상의 값을 비교하려면 IN 연산자와 NOT IN 연산자를 사용하면 편리하다. 다음은 IN 연산자를 사용하여 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서 검색을 하는 SQL문이다. 문자 값들을 괄호 안에 포함시켜 비교하며, publisher 값이 이 중 하나와 같으면 선택된다.

질의 3-6) 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오.

SELECT *
FROM Book
WHERE publisher IN('굿스포츠', '대한미디어');

출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 출판사를 검색하는 문은 다음과 같다.

SELECT *
FROM Book
WHERE publisher NOT IN('굿스포츠', '대한미디어');

패턴

문자열의 패턴을 비교할 때는 LIKE 연산자를 사용한다. 만약 찾는 속성이 텍스트 혹은 날짜 데이터를 포함하면 반드시 영문 작은 따옴표(‘‘)로 둘러싸야 한다. 한글 작은따옴표(“”)는 오류난다.

일반 프로그래밍 언어에서 문자열은 “” 큰따옴표를 사용하지만 SQL언어에서는 ‘’ 작은 따옴표를 사용한다. 그 이유는 SQL문 자체가 문자열로 인용이 되어 다른 프로그래밍 언어에 삽입 될 때 혼란이 있을 수 가 있기 때문이다.

질의3-7) “축구의 역사”를 출간한 출판사를 검색하시오

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';
            //  = 써도 같음

질의3-8) 도서 이름에 “축구”가 포함된 출판사를 검색하시오.

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';

질의3-9) 도서 이름의 왼쪽 두번째 위치에 ‘구’ 라는 문자열을 갖는 도서를 검색하시오.

SELECT *
FROM Book
WHERE bookname LIKE '_구%';

복합조건 검색

질의 3-11) 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오

SELECT *
FROM Book
WHERE publisher = '굿스포츠' OR publisher = '대한미디어';
//                          IN 써도 된다

검색 결과의 정렬

SQL 문의 실행 결과 행의 순서는 각 DBMS에 저장된 위치에 따라 결정된다. 특징을 순서대로 출력하고 싶으면 ORDER BY(기본 오름차순)절을 사용한다.

질의 3-12) 도서를 이름순으로 검색하시오

SELECT *
FROM Book
ORDER BY bookname;

도서를 가격순으로 먼저 정렬 한 후 가격이 같은 도서에 대하여 이름순으로 다시 정렬하고 싶으면 원하는 열 이름을 순서대로 지정한다.

질의 3-13) 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.

SELECT *
FROM Book
ORDER BY price, bookname;

정렬의 기본은 오름차순이다. 내림차순으로 정렬하려면 열 이름 다음에 DESC 를 사용하면 된다.

질의 3-14) 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 출력하시오.

SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;
//                             ASC 생략 가능. 기본이 오름차순이기 때문

집계 함수와 GROUOP BY 검색

앞에서는 마당서점의 고객이 필요로 하는 질의를 중심으로 살펴보았다. 이제부터는 ‘운영자’가 필요로 하는 질의를 중심으로 SQL의 기능을 살펴본다.

집계함수

운영자는 전체 도서의 판매액 합계를 알고 싶어 한다. 이를 위해서 SQL의 집계함수를 이용할 수 있다. 집계함수는 테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT의 다섯 가지가 있다.

질의 3-15) 고객이 주문한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice)
FROM Orders;

의미있는 열 이름을 출력하고 싶으면 속성이름의 별칭을 지칭하는 AS 키워드를 사용하여 열 이름을 부여한다.

SELECT SUM(saleprice) AS 총매출   //AS 생략 가능
FROM Orders;

집계함수는 WHERE문과 같이 사용하면 더 유용하다.

질의 3-16) 2번 김연아 고객이 주문한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) AS 총매출
FROM Orders;
WHERE custid=2;

집계함수는 여러 개를 혼합하여 쓸 수 있다.

질의 3-17) 고객이 주문한 도서의 총판매액, 평균값, 최저가, 최고가를 구하시오

SELECT SUM(saleprice) AS Total,
        AVG(saleprice) AS Average,
        MIN(saleprice) AS Minimum,
        MAX(saleprice) AS Maximum
FROM Orders;

COUNT()

집계함수 COUNT는 행의 개수를 센다. COUNT() 의 괄호 안에는 * 혹은 특정 속성의 이름이 사용되며, 해당 속성의 투플의 개수를 세어준다. (NULL 값은 제외). COUNT(DISTINCT publisher)는 중복을 제거한 출판사의 수를 세어준다. (NULL 값은 제외).

GROUP BY

SQL문에서 GROUP BY 절을 사용하면 같은 속성값끼리 그룹을 만들 수 있다. 예로, Orders 테이블을 사용하는 SELECT 문에 GROUP BY custid라고 명시하면, DBMS는 custid가 같은 값끼리 그룹화한다. 결과는 custid가 서로 다른 값들에 대하여 그룹이 만들어진다. 속성의 공통 값에 따라 그룹을 만드는 데 사용하는 명령이다.

질의 3-19) 고객별로 주문한 도서의 총수량과 총판매액을 구하시오.

SELECT custid, COUNT (*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;

결과 릴레이션)

HAVING

HAVING 절은 GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다. 예를 들어 주문 도서가 2권 이상일 경우만 출력하려면 다음과 같이 작성한다. 주의사항: WHERE절과 HAVING절이 같이 포함된 SQL문은 검색조건이 모호해질 수 있다. HAVING절은 반드시 GROUP BY절과 같이 작성해야하고 WHERE절보다 뒤에 나와야 한다. 그리고 검색조건에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야 한다.

질의 3-20) 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총수량을 구하시오. 단, 2권 이상 구매한 고객만 구하시오.

SELECT custid, COUNT (*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

두 개 이상 테이블에서 SQL 질의

조인

한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다. 다음은 Customer 테이블을 Orders 테이블과 조건 없이 연결한 예이다. SQL 문은 다음과 같이 작성한다. 두 테이블을 아무런 조건을 주지 않고 SELECT 시키면 관계대수의 카티션 프로덕트 연산이 된다.

SELECT *
FROM Customer, Orders;

그렇지만 결과는 논리에 맞지 않는다. 필요한 것은 박지성의 custid가 1이므로 Orders 테이블에서 custid가 1인 것만 선택하면 된다.

질의 3-21) 고객과 고객의 주문에 관한 데이터를 모두 보이시오.

SELECT *
FROM Customer, Orders
WHERE Customer.custid=Orders.custid;

여러 개의 테이블을 연결하여 하나의 테이블을 만드는 과정을 조인이라고 한다. 특히 앞의 SQL문처럼 동등 조건에 의하여 테이블을 조인하는 것을 동등조인이라고 한다. 조인이라고 하면 대부분 동등조인을 말한다. 동등조인은 내부조인이라고도 한다.

세 개 이상의 테이블을 조인 할 수도 있다. 기본키와 외래키로 연결한다.

질의 3-25) 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid;

세 개 이상의 테이블을 조인하는 SQL문에 WHERE 절을 추가하여 원하는 결과만 추출할 수도 있다.

질의 3-26) 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.

SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid AND Book.price=20000;

외부조인(left, right, full outer join … on)

조인 연산의 특별한 경우로 외부조인이 있다. 질의3-23의 고객의 이름과 고객이 주문한 도서의 가격을 구하는 동등조인의 예에서 도서를 주문하지 않은 고객 박세리는 결과에 포함되지 않는다. 만약 도서를 구매하지 않은 고객 박세리까지 포함하여 고객의 이름과 고객이 주문한 도서의 가격을 구하려면 어떻게 해야 할까? 방법은 외부조인을 사용하면 된다.

질의 3-27) 도서를구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
              ON Customer.custid=Orders.custid;

결과 릴레이션)

tip) 오라클은 +기호로 외부조인을 수행할 수 있다. 주의할 점은 왼쪽 외부조인의 경우 (+) 기호를 조인조건의 오른쪽에, 오른쪽 외부조인인 경우 (+) 기호를 왼쪽에 붙인다는 점이다.

SELECT Customer.name, saleprice
FROM Customer, Orders
WHERE Customer.custid=Orders.custid(+);

부속질의

SELECT 문의 WHERE절에 또 다른 테이블 결과를 이용하기 위해 다시 SELECT 문을 괄호로 묶는 것을 부속질의라고 한다. SQL 문 내에 또다른 SQL 문을 작성해 보자. 부속질의는 질의가 중첩되어 있다는 의미에서 중첩질의라고도 한다.

“가격이 가장 비싼 도서의 이름은 얼마인가?”. 가장 비싼 도서의 가격은 다음과 같이 구할 수 있으며 답은 35,000이다.

SELECT MAX(price)
FROM Book;

만약 가장 비싼 도서의 가격을 알고 있다면 다음과 같이 가격이 35,000원인 도서의 이름을 바로 검색하면 된다.

SELECT bookname
FROM Book
SELECT price=35000;

두 질의를 하나의 질의로 작성할 수 있을까? ㅇㅇ가능. 두번째 질의의 35,000 값 위치에 첫 번째 질의를 대치하면 된다.

질의 3-28) 가장 비싼 도서의 이름을 보이시오.

SELECT bookname
FROM Book
SELECT price=(SELECT MAX(price)
              FROM Book);

세 개 이상의 중첩된 부속질의도 가능하다.

질의 3-30) ‘대한미디어’에서 출판한 도서를 구매한 고객의 이름을 보이시오.

SELECT name
FROM Customer
SELECT custid IN(SELECT custid
                  FROM Orders
                  WHERE bookid IN(SELECT bookid
                                  FROM book
                                  WHERE publisher='대한미디어'));

위 문법을 이해하려면 아래에서부터 읽어야 한다.

부속질의 간에는 상하 관계가 있으며, 실행 순서는 하위 부속질의를 먼저 실행하고 그 결과를 이용하여 상위 부속질의를 실행한다. 반면 **상관 부속질의**는 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산한다. 즉 상위, 하위부속질의는 독립적이지 않고 서로 관련을 맺고 있다.

질의 3-31) 출판사별로 출판사의 평균 도서가격보다 비싼 도서를 구하시오.

SELECT b1.bookname
FROM Book b1
SELECT b1.price > (SELECT avg(b2.price)
                  FROM Book b2
                  WHERE b2.publisher=b1.publisher;

위 SQL문을 보면 상위 부속질의에 사용된 Book 테이블을 b1으로 별칭하고, 하위 부속질의에 사용된 Book 테이블을 b2로 별칭한다. (테이블 이름이 같거나 한 개의 테이블이 두번 사용될 때 혼란을 피하고자 테이블의 별칭을 붙여 사용하는데, 이를 투플변수라고 한다. 투플변수는 FROM절의 테이블 이름 뒤에 테이블의 다른 이름을 표기한다.)

부속질의와 조인은 여러 테이블을 하나의 SQL문에서 다루는 점은 같지만, 차이점이 있다. 부속질의는 SELECT문에 나오는 결과 속성을 FROM절의 테이블에서만 얻을 수 있고, 조인은 조인한 모든 테이블에서 결과 속성을 얻을 수 있다. 조인은 부속질의가 할 수 있는 모든 것을 할 수 있다. 그러나 부속질의를 조인을 이용해서 작성해보면 부속질의만의 편리함을 알 수 있다. 한 개의 테이블에서만 결과를 얻는 여러 테이블 질의는 조인보다 부속질의로 작성하는것이 훨씬 편하다.

집합연산

SQL에서 집합연산은 합집합을 UNION, 차집합을 MINUS, 교집합을 INTERSECT로 나타낸다.

질의 3-32) 도서를 주문하지 않은 고객의 이름을 보이시오.

SELECT name
FROM Customer
MINUS
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

EXISTS

상관 부속질의문 형식이다. 조건에 맞는 투플이 존재하면 결과에 포함시킨다. 즉 부속질의문의 어떤 행이 조건에 만족하면 참이다. 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을때만 참이다.

질의 3-33) 주문이 있는 고객의 이름과 주소를 보이시오.

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
              FROM Orders od
              WHERE cs.custid=od.custid);

05. 데이터 정의어

데이터를 저장하려면 먼저 데이터를 저장 할 테이블의 구조를 만들어야한다. 데이터정의어는 바로 이 구조를 만드는 명령이다.

CREATE 문

CREATE문은 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령이다.

질의 3-34) 다음과 같은 속성을 가진 NewBook테이블을 생성하시오. 정수형은 NUMBER를 사용하며 문자형은 가변형 문자타입인 VARCHAR2을 사용한다.

• bookid(도서번호)-NUMBER
• bookname(도서이름)-VARCHAR2(20)
• publisher(출판사)-VARCHAR2(20)
• price(가격)-NUMBER

CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER);

char(n)는 저장되는 문자의 길이가 n보다 작으면 나머지는 공백으로 채워서 n바이트를 만들어 저장한다. varchar2(n)는 마찬가지로 n바이트를 가진 문자형 타입이지만 저장되는 문자의 길이만큼 기억장소를 차지하는 가변형이다.
문자형데이터를 사용할 때 char와 varchar에 저장된 값이 같아도 char는 공백을 채운 문자열이기때문에 동등비교시 실패할 수 있다.

위 예제는 아무 제약사항이 없다. 기본키를 지정하고싶으면 다음 방법이 있다.

CREATE TABLE NewBook(
bookid        NUMBER            PRIMARY KEY,     //이거 추가 또는 맨아래 줄 추가
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER,
PRIMARY KEY   (bookid));      //해당 줄 추가 또는 옆에 프라이머리 키 추가

만약 bookid 속성이 없어서 두 개의 속성이 기본키가 된다면 괄호를 사용하여 복합키를 지정한다.

CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER,
PRIMARY KEY   (bookname, publisher));

위 예제에서 좀 더 복잡한 제약사항을 추가하면 다음과 같다.

bookname은 NULL값을 가질 수 없고, publisher는 같은 값이 있으면 안된다. price에 값이 입력되지 않을 경우 기본값10000을 지정하며, 가격은 최소 1,000원 이상으로 한다.

CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20) NOT NULL,
publisher     VARCHAR2(20) UNIQUE,
price         NUMBER DEFAULT 10000 CHECK(price > 1000),
PRIMARY KEY   (bookname, publisher));

질의 3-36) 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오.

• orderid(주문번호)-NUMBER, 기본키
• custid(고객번호)-NUMBER, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄삭제)
• bookid(도서번호)-NUMBER, NOT NULL 제약조건
• saleprice(판매가격)-NUMBER
• orderdate(판매일자)-DATE

CREATE TABLE NewOrders(
orderid       NUMBER,
custid        NUMBER           NOT NULL,
bookid        NUMBER           NOT NULL,
saleprice     NUMBER,
orderdate     DATE,
PRIMARY KEY   (orderid),
FOREIGN KEY   (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);

외래키 제약조건을 명시할 때는 참조되는 테이블의 기본키여야 한다. 외래키 지정 시 ON DELETE 옵션은 참조되는 테이블의 투플이 삭제될 때 취할 수 있는 동작이다. CASCADE는 참조되는 테이블의 투플이 삭제되면 참조하는 해당 테이블의 투플이 연쇄삭제(CASCADE) 된다.

ALTER 문

ALTER 문은 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경한다.

ADD는 속성 추가, DROP은 속성 제거, MODIFY는 속성 변경할 때 사용한다. 그리고 ADD<제약이름>, DROP<제약이름>은 제약사항을 추가하거나 삭제할 때 사용한다.

질의 3-37) NewBook 테이블에서 VARCHAR2(13)의 자료형을 가진 isbn 속성을 추가하시오

ALTER TABLE NewBook ADD isbn VARCHAR2(13);

질의 3-38) NewBook 테이블의 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오.

ALTER TABLE NewBook MODIFY isbn NUMBER;

질의 3-39) NewBook 테이블에서 isbn 속성을 삭제하시오

ALTER TABLE NewBook DROP COLUMN isbn;

질의 3-40) NewBook 테이블에서 bookid속성에 NOT NULL 제약조건을 적용하시오

ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL;

질의 3-41) NewBook 테이블의 bookid 속성을 기본키로 변경하시오

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

tip) 기본키는 NOT NULL 속성만 가능하다.

DROP 문

DROP은 테이블을 삭제하는 명령. 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 한다.

DROP TABLE NewBook;

삭제하려는 테이블의 기본키를 다른 테이블에서 참조하고 있다면, 참조하는 테이블부터 삭제해야 한다.

데이터 조작어 - 삽입, 수정, 삭제

INSERT 문

INSERT문은 테이블에 새로운 투플을 삽입하는 명령이다.

질의 3-45) Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하십시오. 스포츠의학은 한솔의학서적에서 출간했으며 가격은 미정이다.

INSERT INTO Book(bookid, bookname, publisher)
      VALUESE(14, '스포츠 의학', '한솔의학서적');

INSERT문은 SELECT문을 사영하여 한꺼번에 여러개의 투플을 삽입할 수 있다.

질의 3-46) 수입도서 목록(Import_book)을 Book테이블에 모두 삽입하십시오.

INSERT INTO Book(bookid, bookname, price, publisher)
        SELECT bookid, bookname, price, publisher
        FROM Imported_book;

UPDATE 문

기존 투플에 있는 특정 속성값을 수정하는 명령이다.

질의 3-47) customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오.

UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5;

다른 테이블의 속성값을 이용할 수도 있다.

질의 3-48) customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오

UPDATE Customer
SET address=(SELECT address
              FROM Customer
              WHERE name='김연아')
WHERE name='박세리';

update문에서 여러 속성값을 한꺼번에 수정하는 작업은 가능하나, 잘못 사용하면 위험하다.

DELETE 문

테이블에 있는 기존 투플을 삭제하는 명령이다.

DELETE FROM Customer
WHERE custid=5;

SELECT *FROM Customer;

위 SQL문에서 WHERE절을 빼면 고객 테이블의 모든 투플이 삭제되고 빈 테이블만 남는다.

DELETE FROM Customer;

위 SQL문은 실행되지 않는다. 그 이유는 Orders 테이블에서 Custid.custid 속성을 외래키로 참조하고 있가 때문이다.

COMMIT 문과 ROLLACK 문

이번 절에서 배운 insert, delete, update문의 결과는 최종적으로 commit문을 만나지 않으면 실제로 데이터베이스에 반영되지 않는다. 예를 들어 다음 문장을 실행하면 임시적으로 5번 고객이 삭제된다.

DELETE FROM Customer
WHERE custid=5;

이때 일시적으로 반영된 데이터를 복원시키려면 ROLLBACK 명령어를 사용한다.

ROLLBACK;
SELECT *FROM Customer;

이번에는 다시 삭제 작업을 실행해 보고 COMMIT 명령어로 데이터베이스에 삭제 작업을 반영시켜 본다.

DELETE FROM Customer
WHERE custid=5;
COMMIT;

반영 후에는 ROLLBACK 작업을 수행해도 삭제된 데이터의 복원이 불가능하다.

ROLLBACK;
SELECT * FROM Customer;

SQL 고급

SQL 내장 함수

SQL 내장함수는 사용자가 만든 함수에 대비되는 용어로 DBMS에서 제공하는 함수이다. 상수나 열이름을 입력값으로 받아 호출되며, 단일 값을 결과로 반환한다. 모든 내장함수는 최초에 선언 될 때 유효한 입력값을 받아야 한다. 예를 들어 수학 함수의 입력값은 정수 또는 실수여야 한다. SELECT 절과 WHERE 절, UPDATE 절 등에서 모두 사용할 수 있다.

숫자 함수의 종류

질의 4-2) 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오.

SELECT ROUND(4.875, 1)
FROM dual;

//결과는 4.9

dual은 실제로 존재하는 테이블이 아니라 오라클에서 일시적인 연산 작업에 사용하기 위해 만든 가상의 테이블이다.

1     2     3   .   4   5   6   7 ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ -3   -3   -1   0   1   2   3   4

에서 반올림 한다고 생각!

문자 함수

문자 함수의 종류 : 문자값 반환 함수(s는 문자열, c는 문자, n과 k는 정수)

REPLACE 함수

실수로 도서 제목을 잘못 입력한 경우 일일이 변경하지 않고 한꺼번에 변경할 수 있다.

LENGTH 함수

주의할 점은 세는 단위가 바이트가 아니라 문자라는 점이다. 일반적으로 영문자 ‘A’는 1바이트, 한글 ‘가’는 2바이트(유니코드에서는 3바이트)지만 LEN 함수는 영문자나 한글이나 결과를 1로 반환한다. 또한 공백도 하나의 문자로 간주한다. 참고로 LENGTHB 함수를 사용하면 모두 바이트 단위로 반환한다.

질의 4-6) 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

SELECT SUBSTR(name, 1, 1) "성", COUNT(*) "인원"
FROM Customer
GROUP BY SUBSTR(name, 1, 1);

//결과
//성  인원
//장  1
//김  1
//추  1
//박  2

날짜, 시간 함수

질의 4-7) 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.

SELECT orderid "주문번호", orderdate "주문일", orderdate+10 "확정"
FROM Orders;

//결과
//주문번호  주문일        확정
//1       20/07/01     20/07/11

datetime의 주요 인자

p.217 테이블 생략,, 힘듬

NULL 값 처리

아직 지정되지 않은 값을 말한다. 값을 알수도 없고 적용할수도 없다는 뜻이다. NULL은 ‘0’, ‘(빈문자)’, ‘‘(공백) 등과 다른 특별한 값이다. 또한 비교연산자로 비교할 수 없다. NULL값에 연산을 적용하면 결과는 NULL로 반환한다.

NULL 값을 확인하는 방법 - IS NULL, IS NOT NULL

WHERE 에 price=’‘;를 넣으면 안됌. 오류임. WHERE 에 price IS NULL; 이 사용 가능.

NVL 함수

NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력.

SELECT name “이름”, NVL (phone, ‘연락처없음’) “전화번호”

ROWNUM

오라클 내장함수는 아니지만 자주 사용되는 문법이다. 오라클 내부적으로 생성되는 가상 칼럼으로 SQL 조회 결과의 순번을 나타낸다. 자료를 일부분만 확인하여 처리할 때 유용하다.

질의 4-11) 고객목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오.

SELECT ROWNUM "순번", custid, name, phone
FROM Customer
WHERE ROWNUM <=2

//결과
//두줄만 보임..

부속질의

하나의 SQL문 안에 다른 SQL문이 중첩된 형태로 표현되는 질의를 말한다. 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공하는 데 사용한다. 부속질의는 위치와 역할에 따라 SELECT 부속질의(스킬라 부속질의), FROM 부속질의(인라인 뷰), WHERE 부속질의(중첩질의)로 구분된다. 보통 부속질의라고 하면 WHERE 부속질의를 말한다. 부속질의들 간에 구분이 필요할 경우 WHERE 부속질의를 중첩질의라고 부르기도 한다.

• 스킬라 부속질의 : SELECT 절에서 사용되는 부속질의로 단일행, 단일열의 스칼라값을 반환한다.
• 인라인 뷰 : FROM 절에서 사용되는 부속질의로 결과를 뷰(view)형태로 반환한다.
• 중첩질의 : WHERE 절에서 사용되는 부속질의로 주 질의에 사용된 자료 집합의 조건을 서술한다.

P.226~227 필기 보기,,

IN, NOT IN

집합연산자 중 IN은 주 질의의 속성값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할을 한다. IN연산자에서 사용 가능한 부속질의는 결과로 다중 행, 다중 열을 반환 수 있다.

ALL, SOME(ANY)

ALL, SOME(ANY) 연산자는 비교 연산자와 함께 사용된다. ALL은 모든, SOME은 어떠한(최소한 하나라도)라는 의미를 가진다.

질의 4-15) 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

SELECT orderid, saleprice
FORM Orders
WHERE saleprice > ALL (SELECT saleprice
                        FROM Orders
                        WHERE custid='3');

EXISTS, NOT EXISTS

데이터의 존재 여부를 확인한다.

질의 4-16) EXISTS 연산자를 사용하여 ‘대한민국’에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) "total"
FROM Orders od
WHERE EXISTS (SELECT *
              FROM Customer cs
              WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

이후부터는 나중에?? CHAPTER 05 데이터베이스 프로그래밍(01. 데이터베이스 프로그래밍 방법, 02. PL/SQL, 03. 데이터베이스 연동 자바 프로그래밍, 04. 데이터베이스 연동 웹 프로그래밍) 건너뜀.

06. 데이터 모델링

데이터 모델링의 개념

데이터베이스 생명주기

1. 요구사항 수집 및 분석 : 사용자들의 요구사항을 듣고 분석하여 데이터베이스 구축의 범위를 정하는 단계이다. 마당 서점의 경우 고객, 운영자, 경영자 등 사용자의 범위와 서비스 수준을 정하는 것을 말한다.
2. 설계 : 분석된 요구사항을 기초로 주요 개념과 업무 프로세스 등을 식별하고(개념적 설계), 사용하는 DBMS의 종류에 맞게 변환(논리적 설계)한 후, 데이터베이스 스키마를 도출(물리적 설계)한다.
3. 구현 : 설계단계에서 생성한 스키마를 DBMS에 적용하여 테이블 및 관련 객체(뷰, 인덱스 등)를 만든다. 또한 관련 소프트웨어에 설계한 데이터베이스를 적용하여 서비스를 제공할 수 있도록 프로그램을 완성한다.
4. 운영 : 구현된 데이터베이스를 기반으로 소프트웨어를 구축하여 서비스를 제공한다.
5. 감시 및 개선 : 데이터베이스 운영에 따른 시스템의 문제를 간찰하고 데이터베이스 자체의 문제점을 파악항 개선한다. 이 단계에서는 데이터베이스가 지속적으로 운영될 수 있도록 변경 및 유지보수를 한다.

개념적 모델링

개념적 모델링은 요구사항을 수집하고 분석한 결과를 토대로 업무의 핵심적인 개념을 구분하고 전체적인 뼈대를 만드는 과정이다. 여기서 핵심적인 개념을 구분한다는 것은 개체를 추출하고 각 개체 간의 관계를 정의하여 ER다이어그램을 만드는 과정까지를 말한다.

논리적 모델링

논리적 모델링은 개념적 모델링에서 만든 ER 다이어그램을 사용하고자 하는 DBMS에 맞게 사상(매핑)하여 실제 데이터베이스로 구현하기 위한 모델을 만드는 과정이다.

1. 개념적 모델링에서 추출하지 않았던 상세 속성들을 모두 추출한다.
2. 정규화를 수행한다. : 여러 개체가 데이터를 중복 저장하는 문제를 해결하기 위해 정규화를 수행한다.
3. 데이터의 표준화를 수행한다. : 데이터 용어 사전과 도메인을 정의하여 동일하게 사용되는 용어를 정리하고 데이터의 형태 및 크기 등을 확정한다.

물리적 모델링

물리적 모델링은 작성된 논리적 모델을 실제 컴퓨터 저장 장치에 저장하기 위해 데이터 타입과 길이 등 물리적 구조를 정의하고 구현하는 과정이다.

다음은 물리적 모델링을 할 때 트랜잭션, 저장공간 설계 측면에서 고려해야 할 사항이다.

1. 응답시간을 최소화해야한다.
2. 얼마나 많은 트랜잭션을 동시에 발생시킬 수 있는지 검토해야 한다.
3. 데이터가 저장될 공간을 효율적으로 배치해야한다.

ER 모델

ER 모델은 세상의 사물을 개체와 개체간의 관계로 표현한다. 개체는 독립적인 의미를 지닌 유무형의 사람 또는 사물을 말하며, 개체의 특성을 나타내는 속성으로 식별한다. 또한 개체끼리는 서로 관계를 맺는다. 이처럼 개체, 속성, 관계를 규명하는 것이 ER 모델의 기본이다.

개체와 개체 타입

개체는 사람, 사물, 장소, 개념, 사건과 같이 유무형의 정보를 가지고 있는 독립적인 실체를 말한다. 개체는 비슷한 속성을 가진 개체 타입을 구성하며, 개체 집합으로 묶인다.
개체 타입 : 개체 집합은 공통된 속성을 가진 개체들의 모임이며, 개체 타입은 개체 집합의 이러한 동일한 특징을 나타내는 용어이다.

개체는 다음과 같은 특징을 가진다.

• 유일한 식별자에 의해 식별할 수 있다.
• 꾸준한 관리를 필요로 하는 정보이다.
• 두 개 이상 영송적으로 존재해야 한다.
• 업무 프로세스에 이용된다.
• 반드시 자신의 특징을 나타내는 속성을 포함한다.
• 다른 개체와 최소한 한 개 이상의 관계를 맺고 있다.

개체 타입의 ER 다이어그램 표현

ER 다이어그램상에서 개체 타입은 직사각형으로 나타낸다. 강한 개체 타입은 직사각형으로, 약한 개체 타입은 이중 직사각형으로 나타낸다. 보통 개체 타입이라고 하면 강한 개체 타입을 말한다.

강한 개체 타입은 다른 개체의 도움 없이 독자적으로 존재 할 수 있다. 반면 약한 개체 타입은 독자적으로 존재할 수 없고 반드시 상위 개체 타입을 가진다. 예를 들어 회사 데이터베이스에서 직원의 부양가족은 직원이 존재해야 존재할 수 있다. 따라서 직원은 강한 개체 타입이되고, 부양가족은 약한 개체 타입이 된다.

속성

속성은 개체가 가진 성질을 말한다.

속성의 ER 다이어그램 표현

속성은 기본적으로 타원으로 표현하며 개체 타입을 나타내는 직사각형과 실선으로 연결된다. 속성이 개체를 유일하게 식별할 수 있는 키일 경우 속성 이름에 밑줄을 긋는다. 약한 개체의 식별자는 키를 갖지 못하고 대신 식별자를 가지며, 식별자 아래에 점선을 긋는다.

속성의 유형

단순 속성과 복합 속성

속성은 더 작은 단위로 분해할 수 있는지에 따라 단순 속성과 복합속성으로 구분할 수 있다. 단순속성은 더이상 분해가 불가능한 속성이고, 복합속성은 독립적인 의미를 가진 속성으로 분해할 수 있는 속성이다. 복합속성은 큰 타원 아래 작은 타원으로 연결한다.

예를들어 주소의 경우 시, 동 단위로 분해할 수 있는 복합속성이다. 분해된 시, 동의 경우 더 작게 분해할 수 없으므로 단순속성이다.

단일값 속성과 다중값 속성

속성은 속성값의 개수에 따라 단일값 속성과 다중값 속성으로 구분할 수 있다. 단일값 속성은 하나의 값만을 가지는 속성이고, 다중값 속성은 여러 개의 값을 가지는 속성이다. 다중값 속성은 이중타원으로 표현한다.

예를 들어 학생번호나 성명의 경우 한 사람이 하나의 값밖에 가지지 못하므로 단일값 속성이다. 그러나 학위의 경우 한 사람이 학사를 취득한 후 석사를 취득하는 등 여러 개의 학위를 가질 수 있으므로 다중값 속성으로 볼 수 있다.

저장 속성과 유도 속성

속성값이 다른 속성으로부터 유도될 수 있는 지에 따라 저장 속성과 유도 속성으로 구분할 수 있다. 저장속성은 다른 속성의 영향 없이 단독으로 저장되는 속성이고, 유도 속성은 다른 저장 속성으로부터 유도된(계산된) 속성이다. 유도속성은 점선타원으로 표현한다.

예를들어 생년월일의 경우 학생 개개인이 가지고 있는 고유한 값으로 저장속성이며, 나이는 생년월일로부터 계산될 수 있는 값이므로 유도 속성이다.

관계와 관계 타입

ER 모델은 개체와 개체 사이의 관계를 표현한다. 관계는 개체 사이의 연관성을 나타내는 개념이다. 예를 들어 ‘고객이 도서를 구입한다’라고 할 때 ‘고객’ 개체타입과 ‘도서’ 개체타입은 ‘구입한다’라는 개념으로 연결된다. 이러한 개체 간의 연관성을 관계라고 한다. 관계타입은 개체 타입과 개체 타입 간의 연결 가능한 관계를 정의(주문)한 것이며, 관계 집합은 관계로 연결된 집합을 의미한다.

관계 타입의 ER 다이어그램 표현

관계 타입은 마름모로 표현한다.

차수에 따른 관계 타입의 유형

관계 집합에 참여하는 개체 타입의 수를 관계 타입의 차수라고 한다.

• 1진 관계(순환적 관계)는 한 개의 개체가 자기 자신과 관계를 맺는다. 예를들어 학생 개체에서 학생들 간에 멘토링 관계를 맺으면 관계를 맺는 멘토와 멘티 모두 학생 개체가 된다.
• 2진 관계는 두 개의 개체가 관계를 맺는다. 예를 들어 학생 개체 타입과 학과 개체 타입은 ‘소속’이라는 관계를 맺고 있다.
• 3진 관계는 세 개의 개체가 관계를 맺는다. 예를 들어 자동차 회사 직원은 부품을 조립하여 하나의 자동차를 만드는 프로젝트를 ‘수행’하는 관계를 맺고 있다.

관계 대응 수에 따른 관계 타입의 유형

두 개체 타입의 관계에 실제로 참여하는 개별 개체들의 수를 말한다.
일대일 관계, 일대다 관계, 다대일 관계, 다대다 관계가 있다.

일대일 관계(1:1) 예를들어 회사에서 사원이 개인별로 한 대의 컴퓨터만 사용한다면 사우너과 컴퓨터는 일대일 관계이다.

일대다(1:N), 다대일(N:1) 관계 실제 일상생활에서 가장 많이 볼 수 있는 관계이다. 예를들어 학과와 학생의 소속 관계를 보면 하나의 학과에는 여러 명의 학생이 소속되어 있어 일대다 관계로 표현할 수 있다.

다대다(N:M) 관계 서로 복합 관계를 맺는다. 예를들어 학생과 강좌의 수강 관계를 보면, 한 학생은 여러 강좌를 수강할 수 있고, 한 강좌 역시 여러 학생들이 들을 수 있다.

관계 대응 수의 최솟값과 최댓값

min은 관계에 참여하는 개체의 수가 적어도 min값이상이 되어야 한다는 뜻이며, max는 관계에 참여하는 개체의 수가 max 값을 넘을 수 없다는 뜻이다. 예를들어 min값이 0일 경우 관계에 참여하는 개체의 수가 0 이상이므로 반드시 참여할 필요가 없다는 뜻이고, max 값이 10일 경우는 10개의 개체까지 참여할 수 있다는 뜻이다. max 값을 *로 표시하면 임의의 수만큼 참여할 수 있다는 뜻이다.

is-a 관계

일부 개체 집합들이 맺고 있는 관계중에는 상하관계가 있다. 상위 개체 타입의 특성에 따라 하위 개체 타입이 결정되는 형태 is-a관계라고 한다. 이때 상위 개체 타입을 슈퍼 클래스라고 하며, 하위 개체 타입을 서브 클래스라고 한다. is-a 관계는 역삼각형으로 표현하며, 역삼각형 위에는 슈퍼클래스 아래에는 서브 클래스를 관계실선으로 연결한다.

예를들어 상위 개체인 학생 개체 타입은 슈퍼클래스로, 학생이면 기본으로 가지는 공통 속성(학생번호 ,이름, 성별)을 가진다. 학생을 휴학생(속성은 휴학일자, 사유), 재학생(등록학기, 지도교수), 졸업생(학위번호, 졸업일자)의 서브 클래스로 나누고 각 개체 타입에는 자신만이 가지고 있는 고유 속성을 부여한다. 재학생의 경우 슈퍼클래스의 속성인 (학생번호, 이름, 성별)을 상속받고, (등록학기, 지도교수) 속성을 포함하여 모두 다섯개의 속성을 가지게 된다.

참여 제약조건

참여 제약조건은 개체 집합 내 모든 개체가 관계에 참여하는지에 따라 전체 참여와 부분 참여로 구분할 수 있다. 전체 참여는 개체 타입과 관계를 두 줄 실선으로 표현하고, 부분 참여는 일반적인 관계 표현과 같이 단일 실선으로 표현한다. 전체 참여를 (최솟값, 최댓값)으로 표현하면 최솟값이 1 이상으로 모두 참여한다는 뜻이고, 부분 참여는 최솟값이 0 이상이다.

예를들어 학생의 경우 교환학생이나 휴학 등의 이유로 수강을 하지 않는 학생도 있으므로 수강 관계와 부분 참여를 맺는다. 그러나 강좌의 경우 폐강되는 과목 없이 수강신청을 하는 학생이 반드시 있다고 가정하면 수강 관계와 전체 참여를 맺는다.

역할

개체타입 간의 관계를 표현할 때 각 개체는 고유한 역할을 담당한다.

예를들어 학생 개체 타입과 교수 개체 타입이 지도 관계를 맺을 때, 교수 개체 타입은 ‘지도한다’, 학생 개체 타입은 ‘지도받는다’라는 역할을 한다. 일반적으로 역할은 관계만으로 알 수 있으면 생략한다.

순환적 관계

하나의 개체 타입이 동일한 개체 타입(자기자신)과 순환적으로 관계를 맺는 형태를 말한다. 순환적 관계는 관계선을 개체 타입과 관계타입 양쪽에 두개를 그린다.

예를들어 a는 학생들 간에 멘토링 관계를 맺어 맨토와 멘티 모두 학생 개체인 경우이며 b는 사원 개체는 자기보다 직위가 낮은 사원에게는 업무 지시를 내리고, 자기보다 직위가 높은 사원에게는 업무 지시를 받아 순환 관계를 맺는다.

약한 개체 타입과 식별자

직원 개체 타입처럼 독립적을 식별할 수 있는 개체를 가지고 있는 개체 타입을 개체 혹은 강한 개체 타입이라고 한다. 이와 반대로 가족 개체 타입처럼 상위 개체 타입이 결정되지 않으면 개별 개체를 식별할 수 없는 종속된 개체 타입을 약한 개체 타입(이중 직사각형으로 표현, 강한 개체 타입이 있어야 존재 가능)이라고 한다.

강한 개체 타입은 직원 개체 타입의 ‘직원번호’와 같이 각 개체를 식별할 수 있는 기본키를 가진다. 약한 개체 타입은 자신의 기본키만으로 식별이 어려우므로 강한 개체 타입의 기본키를 상속받아 사용(점선 밑줄로 표현)한다. 이를 식별자 혹은 부분키라고 한다.

거의 총정리?

위의 이미지를 보면 회사에서 직원 개체타입과 가족 개체 타입이 ‘부양’관계를 맺고 있다. 직원 개체 타입은 자기 자신만으로 존재 할 수 있고, 각각의 개체를 ‘직원번호’로 구분할 수 있다. 그러나 가족 개체 타입의 경우 직원번호 없이는 누구의 가족인지 알 수 없다. 또 ‘이름’ 속성의 경우 직원 자녀 간에 같은 이름을 가진 사람이 있을 수 있으므로 직원번호와 함께 사용해야 한다.

pdf 보기

IE 표기법

ER 모델 표기법은 지금까지 살펴본 표기법 외에도 여러 표기법이 존재한다. 대표적으로 IE표기법과 바커 표기법이 있다.
ER 다이어그램을 그리는 대표 프로그램인 erwin에서 사용하는 표기법 중 하나이다.
IE 표기법은 관계 대응수를 새발 모양의 기호로 표현하여 새발 표기법이라고도 부른다.

IE 표기법에서 개체 타입과 속성은 직사각형으로 표현한다.
관계는 마름모꼴 대신 개체 타입인 직사각형을 관계실선으로 연결하고 표와 같은 모양의 기호를 이용하여 관계 대응 수 등을 표현한다.
관계실선에서 개체 쪽의 기호는 반대쪽 개체의 최대참여(1 혹은 N)를 나타내며, ‘O’, ‘|’ 기호는 최소참여(참여 제약조건)로 선택하여 선택참여(O) 혹은 필수참여(|)를 나타낸다.

위 그림에서 부서-직원 관계는 강한 개체간의 관계이므로 비식별자 관계(점선)으로 표현한다.
직원 개체는 부서 개체 쪽에 표현된 ‘|’ 기호를 통해 ‘직원은 부서에 반드시(필수) 소속되어야 한다.’ 라는 것을 알 수 있다.
도한 부서 개체는 직원 개체 쪽에 표현된 ok기호로 ‘부서는 직원을 0명 이상 포함한다’라는 것을 알 수 있다. 이는 부서가 없는 직원은 없으며, 직원이 없는 부서는 있을 수 있다는 의미이다.
다시한번 강조하지만, 관계 대응 수는 개체의 반대편에 있는 최대에 관한 기홀르 읽으면 된다.

ER 모델을 관계 데이터 모델로 사상

완성된 ER모델은 실제 데이터베이스로 구축하기 위해 논리적 모델링 단계를 거치는데, 이 단계에서 사상(mapping)이 이루어진다.

1. 개체 타입의 사상

(1단계) 강한(정규) 개체 타입

정규 개체 타입 E의 경우대응하는 릴레이션 R을 생성한다.

ex) 사원(사번, 이름)

(2단계) 약한 개체 타입

약한 개체 타입에서 생성된 릴레이션은 자신의 키와 함께 강한 개체 타입의 키를 외래키로 사상하여 자신의 기본키를 구성한다.

ex) 가족(사번(FK), 이름, 출생일)

2. 관계 타입의 사상

p.341 쓰기 힘들다..

erwin 실습

eXERD 프로그램 사용. 사용방법 skip~

이상현상

잘못 설계된 테이블로 삽입, 삭제, 수정 같은 데이터 조작을 하면 이상현상이 일어난다. 이상현상이란 테이브렝 투플을 삽입 할 때 부득이하게 NULL값이 입력되거나, 삭제 시 연쇄삭제 현상이 발생하거나, 수정 시 데이터의 일관성이 훼손되는 현상을 말한다.

이상현상의 예

삭제이상

삭제이상이란 투플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상이다.

-> 연쇄삭제 문제 발생

ex) 장미란 학생이 스포츠 경영학 과목을 수강 취소하여 DELETE문으로 세번째 투플을 삭제하였다. 그런데 삭제 후 장미란 학생의 학생번호, 학과, 주소 정보가 없어졌다. 스포츠경영학 수강을 취소하려 했던 의도는 달성되었지만, 장미란 학생의 기본 정보가 없어진 것이다.

삽입이상

삽입이상이란 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL값을 입력해야 하는 현상이다.

-> NULL값 문제 발생

EX) 박세리 학생이 체육학과에 입학하여 INSERT문으로 (학생번호, 학생이름, 학과, 주소) 정보를 삽입하였다. 그런데 아직 수강신청은 하지 않은 상태라 (강좌이름, 강의실) 속성에는 NULL값을 입력하였다. 여기서 NULL값은 특별한 값으로 테이블에는 가능 한 없어야 한다.

수정이상

수정이상이란 투플 수정 시 중복으로 저장된 데이터 일부만 수정되어 데이터의 불일치 문제가 일어나는 현상이다.

-> 불일치 (일관성 없음) 문제 발생

EX) 박지성 학생의 주소가 대한민국 서울로 바뀌었다. 보통 UPDATE문으로 주소를 수정할 경우 두 군데 모두 바뀌지만, 만약 한 군데만 바뀐다면 문제가 된다. SELECT문으로 조회 했을 때 서로 다른 주소값이 조회되어 데이터의 일관성이 깨지는 불일치 문제가 발생하기 때문이다.

함수 종속성

함수 종속성의 개념

이상현상이 발생하는 테이블을 수정하여 정상적으로 만드는 과정을 정규화라고 한다. 정규화를 하기 위해서는 먼저 테이블을 분석하여 기본키와 함수 종속성을 파악해야 한다.

릴레이션 R과 R에 속하는 속성의 집합 X,Y가 있을 때, X 각각의 값이 Y의 값 한 개와 대응될 때 'X는 Y를 함수적으로 결정한다'라고 하고 X->Y로 표기한다.

TIP) 릴레이션과 테이블은 같은 의미로 쓰이는 용어지만 릴레이션은 구조를 강조하는 측면이 있고, 테이블은 구조보다는 데이터를 강조하는 측면이 있다. 함수 종속성은 릴레이션 구조에 관한 이론적인 내용을 많이 포함하고 있다.

어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존 관계를 ‘속성 B는 속성 A에 종속한다.’ 혹은 ‘속성 A는 속성 B를 결정한다’ 라고 한다. 이 관계를 A -> B라고 표기하며, A는 B의 결정자라고 한다.

함수 종속성 다이어그램

함수 종속성을 나타내는 표기법으로 함수 종속성 다이어그램이 있다.

학생 수강성적 릴레이션에서 함수 종속성을 찾아 정리하면 다음과 같다. 한가지 주의 깊게 봐야 할 점은 결정자가 복합 속성일 수 있다는 점이다. 예를 들어 학생번호와 강좌이름을 합하여 보면 성적을 알 수 있다.

학생번호->학생이름 학생번호->학과 학생번호->주소 학과->학과사무실 강좌이름->강의실 (학생번호, 강좌이름)->성적

이를 다이어그램으로 표현하면 아래의 그림과 같다.

함수 종속성과 기본키

릴레이션의 함수 종속성을 파악하기 위해서는 우선 기본키를 찾아야 한다.

함수 종속성과 기본키 릴레이션 R(K, A1, A2, A3…An)에서 K가 기본키이면, K->R이 성립한다. 즉 기본키는 릴레이션의 모든 속성에 대한 결정자이다.

이상현상과 결정자

이상현상은 한 개의 릴레이션에 두 개 이상의 정보가 포함되어 있을 때 나타난다.

EX) 학생수강성적 릴레이션의 경우 학생정보(학생번호, 학생이름, 주소, 학과)와 강좌 정보(강좌이름, 강의실)가 한 릴레이션에 포함되어 있기 때문에 이상현상이 나타난다.

이상현상은 기본키가 아니면서 결정자인 속성(비후보키 결정자 속성)이 있을 때 발생한다.

함수 종속성 예제

다음 릴레이션 R에서 주어진 함수 종속성이 성립하는지 살펴보시오.

[함수 종속성]

1. A->B
2. B->C
3. (B,C)->A
4. (A,B)->C

풀이)

1. X. e->i, e->b
2. o
3. x. (i,f)->e, (i,f)->g
4. o

정규화

이상현상의 원인은 여러가지가 있는데, 대부분 두 가지 이상의 정보가 한 릴레이션에 저장되어 있으므로 발생한다. 따라서 이상현상은 릴레이션을 분해하여 제거한다. 분해된 릴레이션에 이상현상이 남아 있다면 이상현상이 없어질 때까지 분해한다. 이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정을 정규화라고 한다.

제 1 정규형

제 1 정규형은 릴레이션의 속성값이 원자값이어야 한다는 조건으로 정의는 다음과 같다. (뭐 여러개면 안된다?)

제 1 정규형 릴레이션 R의 모든 속성값이 원자값을 가지면 제 1 정규형이라고 한다.

제 2 정규형

제 2 정규형은 릴레이션의 기본키가 복합키일 때, 복합키의 일부분이 다른 속성의 결정자인지 아닌지를 판단하는 것이다.(완전함수 종속일때!)

제 2 정규형 릴레이션 R이 제 1정규형이고 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때 제 2정규형이라고 한다.

완전 함수 종속 A와 B가 릴레이션 R의 속성이고 A->B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고 부분집합 속성에 함수 종속하지 않을 경우 완전함수 종속이라고 한다.

위 그림은 제 2 정규형을 만족하지 못한다.
성적은 OK, 그러나 강의실은 강좌이름만으로 가능하기 때문.(완전종속 X, 부분종속 O)

제 3 정규형

속성들이 이행적으로 종속되어 있는지를 판단하는 것이다. (이행관계를 끊어줘야 한다. 이행관계가 있으면 X)

제 3 정규형 릴레이션 R이 제 2정규형이고 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때(직접종속) 제 3정규형이라고 한다. 이행적 종속이란 A->B, B->C 가 성립할 대 A->C가 성립되는 함수 종속성을 말한다.

위 그림에서 학생번호->강좌이름, 학생번호->수강료가 되므로 이행관계이다.

BCNF

릴레이션에 존재하는 함수 종속성에서 모든 결정자가 후보키이면 BCNF 정규형이다.

BCNF 정규형 릴레이션 R에서 함수 종속성 X->Y가 성립할 때 모든 결정자 X가 후보키이면 BCNF 정규형이라고 한다.

위 그림이면 안된다.

위의 그림이어야 함,,,

05. 데이터베이스 프로그래밍

데이터베이스 프로그래밍 방법

데이터베이스 프로그래밍은 DBMS에 데이터를 정의하고 저장된 데이터를 읽어 와 데이터를 변경하는 프로그램을 작성하는 과정이며, SQL을 포함한다는 점이 일반 프로그래밍과는 다르다.

PL/SQL

PL/SQL은 Procedural Language/Structured Query Language의 줄임말로, 데이터베이스 응용프로그램을 작성하는 데 사용하는 오라클의 SQL 전용 언어이다. SQL Developer에서 바로 작성하고 컴파일 한 후 결과를 실행한다. PL/SQL로 개발한 프로그램은 SQL Developer에 프로시저로 저장할 수 있으며 나중에 필요할때마다 호출하여 사용할 수 있다.

프로시저

PL/SQL은 프로그램 로직을 프로시저로 구현하여 객체 형태로 사용한다. 프로시저는 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념으로, 작업 순서가 정해진 독립된 프로그램의 수행 단위를 말한다. 프로시저는 CREATE PROCEDURE문을 사용해 정의한다.

• PL/SQL은 선언부()와 실행부(BEGIN-END){}로 구성된다. 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현한다.
• 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
• 변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.

삽입을 작업하는 프로시저

예제 5-1) Book 테이블에 한 개의 투플을 삽입하는 프로시저를 작성하시오.
(프로시저는 없으면 만들고 있으면 재정의하라(대체))

CREATE OR REPLACE PROCEDURE InsertBook(   //프로시저 정의. 혹은 기존의 프로시저가 있으면 삭제하고 재정의한다.
  myBookID IN NUMBER,   //프로시저 매개변수 정의. IN은 입력인자, OUT은 출력인자
  myBookName IN VARCHAR2,   //프로시저 매개변수 정의
  myPublisher IN VARCHAR2,   //프로시저 매개변수 정의
  myPrice IN NUMBER)   //프로시저 매개변수 정의
AS
BEGIN 
  INSERT INTO Book(bookid, bookname, publisher, price)
    VALUES(myBookID, myBookName, myPublisher, myPrice);
END;
/
EXEC InsertBook(13, '스포츠과학', '마당과학서적', 25000);   //정의된 InsertBook 프로시저를 EXEC 문으로 호출하여 실행하면 Book테이블에 bookid가 13인 새로운 투플 한 개가 추가된다.
SELECT * FROM book;

제어문을 사용하는 프로시저

예제5-2) 동일한 도서가 있는지 점검 한 후 삽입하는 프로시저를 작성하시오

CREATE OR REPLACE PROCEDURE BookInsertOrUpdate(  //프로시저 이름과 매개변수 정의. IN/OUT을 생략하면 기본값은 IN으로 설정
  myBookID NUMBER,  //위와동일
  myBookName VARCHAR2,  //위와동일
  myPublisher VARCHAR2,  //위와동일
  myPrice INT)  //위와동일
AS
  mycount NUMBER;  //지역변수 mycount선언
BEGIN
  SELECT count (*) INTO mycount FROM Book  //myBookName과 같은 이름의 책이 몇권 있는지 확인하여 mycount 변수에 값 저장
    WHERE bookname LIKE mybookName;  //위와 동일
  IF mycount !=0 THEN  //if조건문으로 삽입하려는 도서이름(myBookName)과 같은 도서가 있는지 확인하여, 있으면 가격(price)만 새로운가격(myprice)로 수정하고 아니면 투플 전체를 새로 삽입한다.
    UPDATE Book SET price = myPrice  //위와동일
      WHERE bookname LIKE myBookName;  //위와동일
  ELSE  //위와동일
    INSERT INTO Book(bookid, bookname, publisher, price)  //위와동일
      VALUES(myBookID, myBookname, myPublisher, myPrice);  //위와동일
  END IF;  //위와동일
END;
/
EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);  //호출결과 Book테이블에 bookid가 15인 새로운 투플 한 개가 추가된다
SELECT * FROM Book;
EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);  //호출결과 이미 같은 책 제목이 있으므로 가격만 변경된다
SELECT * FROM Book;

결과를 반환하는 프로시저

예쩨5-3) Book 테이블에 저장된 도서의 평균가격을 반환하는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE AveragePrice(
  AverageVal OUT NUMBER)   //OUT은 출력 매개변수라는 의미이다
AS
BEGIN
  SELECT AVG(price) INTO AverageVal FROM Book WHERE price IS NOT NULL;  //책의 평균값을 계산하여 매개변수 AverageVal에 저장. INTO문은 변수에 값을 저장할 때 사용한다.
END;
/
SET SERVEROUTPUT ON;
DECLARE
  AverageVal NUMBER(6,1);  //변수 AverageVal을 정의하였고 AveragePrice를 호출하였다. 호출 후 결과는 매개변수 AverageVal에 저장된다.
BEGIN
  AveragePrice(AverageVal);
  DBMS_OUTPUT.PUT_LINE('책값 평균 :'||AverageVal);  //위 두번째에서 OUT했기때문에 출력가능. AverageVal 값을 출력하는 문장이다.
END;

커서를 사용하는 프로시저

예제 5-4) Orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE Interest
AS
  myInterest NUMERIC;
  Price NUMERIC;
  CURSOR InterestCursor IS SELECT saleprice FROM Orders;  //InterestCursor라는 이름의 CURSOR변수를 선언했다. InterestCursor는 SELECT 문의 결과 테이블을 차례대로 가리키는 포인터로 맨 처음에는 첫 번째 투플을 가리킨다.
BEGIN
  myInterest :=0.0;
  OPEN InterestCursor;  //커서의 사용을 알리는(OPEN) 문장이다.
  LOOP  //LOOP반복분이며 EXIT WHEN문장에서 NOTFOUND조건을 만족하면 빠져나온다. 
    FETCH InterestCursor INTO Price;  //다음 투플을 가져오는 FETCH문이다.
    EXIT WHEN InterestCursor%NOTFOUND;
    IF Price >= 30000 THEN  //IF 조건에 따라 이익금을 myInterest에 저장한다.
        myInterest := myInterest + Price * 0.1;  //위와동일
    ELSE  //위와동일
        myInterest := myInterest + Price *0.05;  //위와동일
    END IF;  //위와동일
  END LOOP;
  CLOSE InterestCursor;  //커서의 사용을 끝내는 CLOSE문이다.
  DBMS_OUTPUT.PUT_LINE(' 전체 이익 금액 = '||myInterest);  //myInterest의 결과값 출력
END;
/
SET SERVEROUTPUT ON;  //정의된 프로시저 호출하며 매개변수 없이 바로 호출된다. 실행 결과는 도서의 판매 이익금이다.
EXEC Interest;  //위와동일

트리거

트리거는 데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다. 예를들어 Book 테이블에 새로운 도서를 삽입 할 때 삽입된 내용을 백업하기 위해 다른 테이블 Book_log에 삽입된 내용을 기록한다고 하자. Book 테이블에 INSERT 문을 수행하면서 같이 실행할 수도 있지만, 사용자로서는 번거롭기도하고 보안상 백업을 감추어야 할 경우도 있다. 이때 트리거를 사용하면 편하다.

예제5-5) 신규 도서를 삽입 한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거

먼저 실습을 위해 Book_log 테이블을 다음과 같이 생성한다

create table Book_log(
    bookid_l NUMBER,
    bookname_l VARCHAR2(40),
    publisher_l VARCHAR2(40),
    price_l NUMBER);

CREATE OR REPLACE TRIGGER AfterInsertBook
AFTER INSERT ON Book FOR EACH ROW  //트리거가Book테이블에 INSERT 문이 실행되면 자동으로 실행된다는 의미이다.
DECLARE
    average NUMBER;
BEGIN
    INSERT INTO Book_log  //Book에 삽입된 투플을 다시 한 번 Book_log 테이블에 저장하는 INSERT 문을 실행하라는 의미이다. 변수 앞에 :new는 새로 삽입될 투플의 값을 가리키는 지정자이다.
      VALUES(:new.bookid, :new.bookname, :new.publisher, :new.price);
    DBMS_OUTPUT.PUT_LINE('삽입 투플을 Book_log 테이블에 백업..');  //메세지를 출력하는 문장이다.
END;
/
INSERT INTO Book VALUES(14, '스포츠 과학 1', '이상미디어', 25000);  //새로운 투플을 Book_log에 삽입하는 문장이다.
select * FROM Book WHERE BOOKID='14';  //Book 테이블에 삽입된 내용을 확인한다.
SELECT * FROM Book_log WHERE BOOKID_L='14';  //Book_log 테이블에 삽입된 내용을 확인한다.

사용자 정의 함수

예제 5-6) 판매된 도서에 대한 이익을 계산하는 함수를 작성하시오.

판매된 도서의 이익을 계산하기 위해 각 주문 건별로 실제 판매가격인 saleprice를 입력받아 가격에 맞는 이익(30,000원 이상이면 10%, 30,000원 미만이면 5%)을 계산하여 반환하는 함수를 작성해보자.

CREATE OR REPLACE FUNCTION fnc_Interest(
  Price NUMBER) RETURN INT  //매개변수
IS
  myInterest NUMBER;  //변수
BEGIN
  IF Price >= 30000 THEN myInterest := Price*0.1;
  ELSE myInterest := Price *0.05;
  END IF;
  RETURN myInterest;
END;
/
SELECT custid, orderid, saleprice, fnc_Interest(saleprice) interest
FROM Orders;