CodeDrive

데이터베이스에서 보안과 권한관리를 하는 것은 매우 중요하다. 데이터베이스의 보안과 권한관리가 미약해서 손실된다면 데이터베이스를 소유한 조직의 운용데 큰 타격을 입기 때문이다. 따라서 권한이 없는 사람들이 함부로 데이터베이스에 접근을 하지 못하도록 하고, 일부 사용자들에게만 적절한 수준의 권한을 허가할 수 있는 기능을 가지고 있어야 할 것이다.

따라서 데이터베이스에는 이를 위해서 접근제어와 보안 및 권한 관리에 대한 기능을 제공하고 있다.

보안기법

데이터베이스에서 제공하는 보안 기법에는 크게 두 가지가 있다.

첫번째는 임의 보안 기법이다.

임의 보안 기법은 사용자들에게 특정 릴레이션, 투플, 또는 애트리뷰트를 지정된 모드로 접근할 수 있는 권한을 허가하고 취소하는 기법이다. 대부분의 상용 관계 DBMS에서 사용되는 기법으로 시스템 카탈로그에 누가 권한을 허가받았고 취소당했는가를 유지한다.

두번째는 강제 보안 기법이다.

강제 보안 기법은 데이터와 사용자들을 다양한 보안 등급으로 분류하고 해당 조직마다 적합한 보안 정책을 적용한다. 하지만 대부분의 상용 관계 DBMS는 이러한 보안 기법을 제공하지 않는다.

이러한 보안을 계속해서 유지하기 위해서는 데이터베이스를 관리하는 데이터베이스 관리자가 필요하다. 이 사람은 권한을 부여하거나 취소를 하고 사용자가 데이터베이스에 가한 모든 연산들을 기록할 수 있다. 만약 권한이 없는 사용자가 데이터베이스를 갱신했다는 의심을 들면 데이터베이스 감사를 실시할 수 있다.

권한 관리

사용자에게 권한을 주는 권리에는 다음과 같은 것들이 있다.

권한 허가

서로 다른 객체들에 대해서 다양한 권한들이 존재한다. 객체를 생성한 사람(소유한)은 모든 권한을 가지며 이 사람은 자신이 소유한 임의의 객체에 대해서 특정 권한을 GRANT문을 사용해서 다른 사용자에게 역할이나 권한을 허가할 수 있다.

GRANT 권한[(애트리뷰트들의 리스트)] ON 객체 TO {사용자|역할|PUBLIC} [WITH GRANT OPTION];

권한 취소

다른 사용자에게 허가한 권한을 취소하기 위해서는 REVOKE문을 사용한다. 또한 어떤 사용자가 다른 사용자에게 허가했던 권한을 취소한다면 권한을 취소 당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 연쇄적으로 취소된다.

REVOKE {권한들의 리스트|ALL} ON 객체 FROM {사용자|역할|PUBLIC};

역할

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

역할은 사용자에게 허가할 수 있는 연관된 권한들의 그룹으로서 이름을 가진다. 각 사용자는 여러 역할에 속할 수 있으며 여러 사용자들이 동일한 역할을 허가받을 수 있다. 또한 어떤 역할과 연관된 권한들에 변화가 생기면 그 역할을 허가받은 모든 사용자들은 자동적으로 즉시 변경된 권한들을 가지게 된다.

뷰

관계 데이터베이스 시스템의 뷰는 다른 릴레이션으로부터 유도된 릴레이션이다. 뷰를 사용함으로써 복잡한 질의를 간단하게 표현할 수 있고, 데이터 독립성을 높히기 위해서 사용한다.

 ANSI/SPARC 3단계 아키텍처에서 외부 뷰 라는 개념이 있었는데 지금 설명하는 뷰는 다른 개념이다. 하나의 가상 릴레이션을 의미하며 기본 릴레이션에 대한 SELECT문의 형태로 정의된다. 

View에는 3가지 종류가 있다.

• Static view - base relation이 바뀌어도 view는 그대로 남아 있다. 변경시 수동으로 업데이트 해야 한다.

• Dynamic view - base relation이 바뀐다면 view도 같이 바뀐다.

• Materialized view - 위 view들이 논리적으로 실행되는 것과는 다르게 query 결과를 별도의 공간에 저장하고, query가 실행될 때 미리 저장된 결과를 출력함으로써 성능을 향상 시킨다.

  • 어느 시점의 select문의 결과를 기본 릴레이션 형태로 저장해서 이를 사진을 찍은 것과 같아 snapshot이라고도 한다.

"CREATE VIEW 뷰이름[애트리뷰트] AS SELECT문 [WITH CHECK OPTION]" 과 같이 뷰를 정의한다.

뷰의 장점

1. 뷰는 복잡한 질의를 간단하게 표현할 수 있도록 한다. 뷰가 특정한 시점(조건)의 SELECT문을 정의한 것이므로 복잡하게 검색하는 질의를 뷰로 정의해두면 뷰는 SELECT하면 같은 값을 얻을 수 있다.

2. 뷰는 데이터 무결성을 보장하는데 활용된다. 처음 뷰를 정의할 때 설정한 조건을 기준으로 뷰를 갱신가능(추가, 수정)하다. 단 WITH CHECK OPTION을 명시했다고 가정한다. 따라서 뷰에 대한 갱신을 하면 기본 릴레이션에 대한 갱신으로 변환된다. 단 릴레이션의 기본 키가 포함되지 않은 뷰이다.

3. 뷰는 데이터 독립성을 제공한다. 뷰는 데이터베이스의 구조가 바뀌더라도 기존의 질의를 다시 작성할 필요가 없다. 예를 들어 하나의 릴레이션이 있고 이를 뷰로 정의했다고 하자. 알고보니 이 릴레이션을 두 개의 릴레이션으로 분해할 필요가 생겼고, 분해했다고 가정하자. 그러면 기존의 릴레이션으로 접근하던 질의는 분해된 릴레이션으로 바꿔야 하지만 뷰로 접근하도록 했다면 바꿀 필요가 없다.

4. 뷰는 같은 데이터라도 여러 가지 뷰를 제공할 수 있다. 특정한 시점의 SELECT문이라고 생각하면 되므로 여러가지 조건으로 뷰를 생성했다면 같은 데이터라도 서로 다른 뷰가 된다.

트랜잭션

 트랜잭션은 동시성 제어를 지원하는데 동시성 제어란 동시에 수행되는 트랜잭션들이 데이터베이스에 미치는 영향이 순차적으로 수행할 때와 동일하도록 보장을 하는 것이다. 데이터베이스는 기본적으로 많은 사용자들이 동시에 접근하기 때문에 이를 보장할 필요가 있기 때문이다. 혹은 트랜잭션은 회복의 특징도 가지고 있는데 데이스베이스를 갱신하는 도중에 시스템이 고장 나도 일관성을 유지하는 것이다.

트랜잭션의 특성 (ACID)

원자성(Atomicity)

 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않아야 함(all or noting)을 의미한다. 중간에 다운되어서 트랜잭션을 완료하지 못하였다면 취소를 하고, 완료된 트랜잭션은 재수행함으로써 원자성을 보장한다. 원자성은 DBMS의 회복과 연관되어 있다.

일관성(Consistency)

 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에도 일관된 상태를 가진다. 일관성은 DBMS의 무결성 제약조건과 동시성 제어와 연관되어 있다.

고립성(Isolation)

 한 트랜잭션이 데이터를 갱신하는 동안에 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 한다. 이를 확인하는 방법은 다수의 트랜잭션이 동시에 수행하더라도 결과는 어떤 순서로 수행한 것과 같아야 한다. 고립성은 DBMS의 동시성 제어와 연관되어 있다.

지속성(Durability)

 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 시스템이 고장나도 손실되지 않는다. 지속성은 DBMS의 회복과 연관되어 있다.

완료(commit)와 철회(abort)

트랜잭션의 완료는 트랜잭션에서 변경하려는 내용이 데이터베이스에 완전하게 반영되었음을 의미하고 SQL상 COMMIT WORK이다. 트랜잭션의 철회는 변경하려는 내용이 일부만 반영된 경우에는 원자성을 보장하기 위해 트랜잭션 수행 전 상태로 되돌리는 것이다. SQL상 ROLLBACK WORK이다.

동시성 제어

대부분의 DBMS들은 다수 사용자용으로 설계되어 있고 여러 사용자들이 동시에 동일한 테이블을 접근하기도 한다. 따라서 성능을 높이기 위해 동시에 질의를 수행하는 것이 필수적이며, 부정확한 결과가 발생하지 않도록 해야한다. 동시성 제어를 설명하기에 앞서 필요한 개념의 정의다.

1. 직렬 스케줄

- 여러 트랜잭션들의 집합을 한 번에 한 트랜잭션씩 차례대로 수행한다.

2. 비직렬 스케줄

- 여러 트랜잭션들을 동시에 수행한다.

3. 직렬가능(serializable)

- 비직렬 스케줄의 결과가 어떤 직렬 스케줄의 수행 결과와 동등하다.

문제점

 만약 동시성 제어를 하지 않고 다수의 트랜잭션을 동시에 수행한다면 다음과 같은 문제가 발생할 수 있다.

1. 갱신 손실: 수행 중인 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어버려서 갱신이 무효가 되는 것이다.

위와 같은 예시에서 X 값에 대해서 갱신 손실이 발생하게 되었다. 두 개의 트랜잭션에서 각각 X를 읽었고, 값을 갱신하였다. 하지만 T1에서 먼저 갱신을 했고 (X=X-100000-> 200000) T1이 쓰기 전에 그 다음 T2에서 갱신(X=X+50000-> 350000)을 했다. 따라서 같은 X의 데이터가 서로 다른 값을 가지게 되었고, T1이 write 한 다음에 T2가 write(덮어버렸다.)해서 갱신 손실이 발생하게 되었다.

2. 오손 데이터 읽기: 완료되지 않은 트랜잭션이 갱신한 데이터를 읽는 것이다.

 T1의 값을 COMMIT하기 전에 T2에서 갱신한 데이터를 접근해버려서 오손 데이터 읽기가 발생했다.

3. 반복할 수 없는 읽기: 한 트랜잭션이 동일한 데이터를 두 번 읽었으나 서로 다른 값을 읽게 되는 것이다.

 T2에서 AVG(balance)를 두 번 수행했는데 그 사이에 balance의 값의 갱신이 있었다. 따라서 반복할 수 없는 읽기 문제가 발생했다.

로킹(locking)

 데이터 항목을 로킹하는 개념은 동시에 수행되는 트랜잭션들의 동시성을 제어하기 위해서 가장 널리 사용되는 기법이다. 로크는 데이터베이스 내의 각 데이터 항목과 연관된 하나의 변수이다. 트랜잭션에서 데이터 항목을 접근할 때 로크를 요청하고, 접근을 끝낸 후에 로크를 해제한다. 

2단계 로킹 프로토콜

 로크를 요청하는 것과 로크를 해제하는 것이 2단계로 이루어지는 것이다. 로크 확장하는 단계가 지난 후에 로크 수축 단계에 들어가게 되는 것이다.

팬텀 문제

 두 개의 트랜잭션이 동일한 애트리뷰트드에 대해 갱신하는 작업을 수행한다고 하자. 그런데 하나의 트랜잭션에서 투플을 삭제하거나 삽입한다면 T1와 T2가 같은 SELECT를 하더라도 결과가 다르게 나타나게 되는데 이를 팬텀 문제라고 부른다. 즉 알 수 없는 값이 삭제되었거나 추가된 현상을 의미한다.

릴레이션 정규화

부주의한 데이터베이스 설계는 데이터 중복을 야기해서 갱신 이상을 유발한다. 따라서 정규화를 통해서 주어진 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여, 원래의 릴레이션을 분해해 중복과 세 가지 갱신 이상을 최소화한다.

갱신 이상

1. 수정 이상

 반복된 데이터 중에 일부만 수정하면 데이터의 불일치가 발생한다.

ex) 만일 어떤 강좌의 이름이 바뀔 때 이 강좌를 수강하는 일부 학생 투플에서만 강좌 이름을 변경한다면 데이터 불일치 상태에 빠지게 된다.

2. 삽입 이상

 불필요한 정보를 함께 저장하지 않고는 어떤 정보를 저장하는 것이 불가능하다.

ex) 만일 새로운 강좌를 개설했는데 아직 학생을 한 명도 배정하지 않았다면 이 강좌에 관한 정보를 입력할 수 없다.

3. 삭제 이상

 유용한 정보를 함께 삭제하지 않고는 어떤 정보를 삭제하는 것이 불가능하다.

ex) 만약 강좌를 수강하고 있는 학생이 단 한 명이 있는데, 이 학생에 관한 투플을 삭제한다면 강좌에 관한 정보도 릴레이션에서 삭제된다.

릴레이션 분해

따라서 이러한 갱신이상을 해결하기 위해서 하나의 릴레이션을 두 개 이상의 릴레이션으로 나눈다. 릴레이션을 분해할 때에는 함수적 종속성에 관한 지식을 기반으로 하며 추후 다시 원래의 릴레이션을 다시 구할 수 있어야 한다.

함수적 종속성

정규화를 할 때 가장 중요한 이론이다. 릴레이션의 애트리뷰트들의 의미로부터 결정된다. 실세계에 대한 지식과 응용의 의미를 기반으로 어떠한 함수적 종속성이 있는지 파악해야 한다.

결정자

어떤 애트리뷰트의 값이 다른 애트리뷰트의 값을 고유하게 결정할 수 있을 때 이를 결정자라고 부르며 이를 A가 B를 결정한다 와 같이 말한다. (A->B와 같이 표기한다.)

위와 같은 학생 릴레이션이 있다고 가정하자. 그러면 다음과 같은 결정자들이 있다.

학번 -> 이름 , 학번 -> 주소, 학번 ->전화번호, 학과 번호-> 학과이름

함수적 종속성

만약 애트리뷰트 A가 애트리뷰트 B의 결정자이면 B가 A에 함수적으로 종속한다고 말한다. 즉, 각 A 값에 대해 반드시 한 개의 B 값이 대응된다는 것이다.

ex) 학번이 이름, 주소, 전화번호의 결정자이므로, 이름, 주소, 전화번호는 학번에 함수적으로 종속한다.

1. 완전 함수적 종속성 (FFD : Full Functional Dependency)

 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면 애트리뷰트 B가 애트리뷰트 A에 완전하게 함수적으로 종속한다고 말한다. 즉, 애트리뷰트 B는 릴레이션 내 결정자들에 의해 결정되는 것임을 의미한다. (여기서 A는 복합 애트리뷰트이다.)

2. 부분 함수적 종속성

부분 함수적 종속성은 완전 함수적 종속성이 아닌 함수적 종속성들을 의미한다.

3. 이행적 함수적 종속성

 한 릴레이션의 애트리뷰트 A, B, C가 주어졌을 때 애트리뷰트 C가 이행적으로 A에 종속한다.(A->C)는 것의 필요충분조건은 A->B ^ B->C 가 성립하는 것이다. 즉 3단 논법을 만족하는 함수적 종속성을 의미한다.

정규화

정규형의 종류에는 제1 정규형, 제2정규형, 제3정규형, BCNF, 제4정규형, 제 5정규형이 있으나 일반적으로 BCNF까지만 고려한다.

제1정규형

한 릴레이션 R이 제1 정규형을 만족할 필요충분조건은 릴레이션 R의 모든 애트리뷰트가 원자값만을 가진다는 것이다. 즉 애트리뷰트에 반복 그룹이 나타나지 않는다면 제1 정규형을 만족한다.

하지만 제1 정규형을 진행해도 갱신 이상이 존재할 수 있다. 아래 그림의 학생 릴레이션은 모든 애트리뷰트가 원자값을 가지므로 제 1정규형을 만족한다. 이 릴레이션의 기본 키는 (학번, 과목 번호)이다.

1. 수정 이상

- 한 학과에 소속한 학생 수만큼 그 학과의 전화번호가 중복되어 저장되므로 여러 학생이 소속된 학과의 전화번호가 변경되었을 때 모든 학생들의 투플에서 전화번호를 수정하지 않으면 수정 이상이 발생한다.

2. 삽입 이상

- 한 명의 학생이라도 어떤 학과에 소속되지 않으면 학과에 관한 투플을 삽입할 수 없다. 학번이 기본 키의 구성요소인데 엔티티 무결성 제약조건에 의해 기본 키에 널 값을 넣을 수 없기 때문이다.

3. 삭제 이상

- 어떤 학과에 소속된 마지막 학생을 삭제한다면 이 학생이 소속된 학과의 정보도 삭제된다.

why? 기본 키에 대한 부분 함수적 종속성이 있기 때문에 갱신 이상이 발생한다.

제2 정규형

제1 정규형을 만족하는 릴레이션에 대해서 어떤 후보 키에도 속하지 않는 애트리뷰트들이 R의 기본 키에 완전하게 함수적으로 종속하는 것을 의미한다. 따라서 위의 갱신 이상을 해결하기 위해서 학생 릴레이션을 학번 1, 수강 릴레이션으로 분해할 수 있다.

 따라서 위와 같이 분해한다면 아래와 같은 기본 키가 학번인 학생 1 릴레이션이 생기게 될 것이다. 하지만 제2정규형을 진행해도 아직 갱신 이상이 남아있다.

1. 수정 이상

- 여러 학생이 소속된 학과의 전화번호가 변경된다면 그 학과에 속한 모든 학생들의 투플에서 전화번호를 수정해야 일관성이 유지된다.

2. 삽입 이상

- 어떤 학과가 신설해서 소속 학생이 없다면 정보를 입력할 수 없다.

3. 삭제 이상

- 어떤 학과에서 마지막 학생의 투플이 삭제된다면 학과의 전화번호도 함께 삭제된다.

why? 학생1 릴레이션에 이행적 종속성이 존재하기 때문에 갱신 이상이 발생한다.

제3 정규형

 한 릴레이션 R이 제2 정규형을 만족하면서, 키가 아닌 모든 애트리뷰트가 R의 기본키에 이행적으로 종속하지 않는 것을 의미한다. 따라서 학생 1에 존재하는 이행적 종속성을 해결하기 위해서 학생 2, 학과 릴레이션으로 분해한다.

 이제 학생 릴레이션에서는 더 이상 갱신 이상이 발생하지는 않으므로 정규화 작업이 필요하지 않다. 하지만 제2 정규형에서 생성된 수강 릴레이션에서 갱신 이상이 발생되므로 이 릴레이션에 대해서는 추가 정규화 작업이 필요하게 된다.

 수강 릴레이션에서 각 학생은 여러 과목을 수강할 수 있고, 각 강사는 한 과목만 가르치게 된다. 이 릴레이션의 기본 키는 (학번, 과목)이다. 키가 아닌 강사 애트리뷰트가 기본 키에 완전하게 함수적으로 종속하므로 제2 정규형을 만족하고, 강사 애트리뷰트가 기본 키에 직접 종속하므로 제3 정규형도 만족한다.

따라서 이 릴레이션에는 아래와 같은 함수적 종속성이 존재한다.

(학번, 과목) -> 강사, 강사-> 과목

1. 수정 이상

- 여러 학생이 수강 중인 어떤 과목의 강사가 변경되었을 때 그 과목을 수강하는 모든 학생들의 투플에서 강사를 수정해야 한다.

2. 삽입 이상

- 어떤 과목을 신설해서 아직 수강하는 학생이 없으면 어떤 강사가 그 과목을 가르친다는 정보를 입력할 수 없다. 이 역시도 엔티티 무결성 제약조건에 의해 기본 키를 구성하는 애트리뷰트에 널 값을 넣을 수 없다는 이유 때문이다.

3. 삭제 이상

- 어떤 과목을 이수하는 학생이 한 명밖에 없는데 이 학생의 투플을 삭제하면 그 과목을 가르치는 강사에 관한 정보도 함께 삭제된다.

why? 수강 릴레이션에서 키가 아닌 애트리뷰트가 다른 애트리뷰트를 결정하기 때문에 갱신 이상이 발생한다.

BCNF

 릴레이션 R이 제3 정규형을 만족하고, 모든 결정자가 후보 키여야 한다. 수강 릴레이션에서 강사는 후보 키가 아님에도 불구하고 과목을 결정하기 때문에 문제가 발생했던 것이다.

 BCNF를 하기 위해서 키가 아니면서 결정자 역할을 하는 애트리뷰트(강사)와 그 결정자에 함수적으로 종속하는 애트리뷰트(과목)를 하나의 테이블에 넣는다. 이 릴레이션에서는 강사가 기본 키가 된다. 그다음에는 기존 릴레이션에 결정자(강사)를 남겨서 기본 키의 구성요소가 되도록 한다. (-> 외래 키가 된다)

위의 과정들을 요약을 하면 다음과 같다.

정규화의 장점은 정규화를 진행할수록 중복이 감소하고, 갱신 이상도 감소된다. 정규화가 진전될수록 무결성 제약조건을 시행하기 위해 필요한 코드의 양이 감소된다.

하지만 높은 정규형을 만족한다고 해서 릴레이션 스키마가 최적이 되는 것은 아니다. 분해되기 전의 릴레이션의 내용이 필요하다면 조인의 필요성이 증가하기 때문에 더 안 좋아질 수도 있다.

ER 스키마를 관계 모델의 릴레이션으로 사상

ER 모델을 릴레이션들로 사상하는 7개의 단계로 이루어진 알고리즘을 통해서 사상한다.

단계 1: 정규 엔티티 타입과 단일 값 애트리뷰트

ER 스키마의 각 정규 엔티티 타입 E에 대해 하나의 릴레이션 R을 생성한다.

E에 있던 단순 애트리뷰트들을 릴레이션 R에 모두 포함시킨다.

복합 애트리뷰트는 복합 애트리뷰트를 구성하는 단순 애트리뷰트들만 포함시킨다.

E의 기본 키가 릴레이션의 R의 기본 키가 된다.

단계 2: 약한 엔티티 타입과 단일 값 애트리뷰트

ER 스키마에서 소유 엔티티 타입 E를 갖는 각 약한 엔티티 타입 W에 대하여 릴레이션 R을 생성한다.

소유 엔티티 타입에 해당하는 릴레이션의 기본 키를 약한 엔티티 타입에 해당하는 릴레이션에 외래 키로 포함시킴

약한 엔티티 타입의 부분 키와 소유 엔티티 타입의 외래 키의 조합으로 기본 키를 구성한다.

단계 3: 2진 1:1 관계 타입

관계 타입 R에 대하여, R에 참여하는 엔티티 타입에 대응되는 릴레이션 S와 T를 찾음

S와 T 중에서 한 릴레이션을 선택하고, 만일 S를 선택했따면 T의 기본 키를 S의 외래 키로 포함시킨다.

보통 관계 타입에 완전하게 참여하는 릴레이션을 S 릴레이션으로 선택한다. 관계 타입 R이 가지고 있는 단순 애트리뷰트들은 S에 대응되는 릴레이션에 포함시킨다.

두 엔티티 타입이 R에 완전하게 참여할 때는 하나의 릴레이션으로 합치는 방법도 가능하다.

단계 4: 정규 2진 1:N 관계 타입

관계 타입 R에 대하여 N측의 참여 엔티티 타입에 대응되는 릴레이션 S를 찾는다. 1측의 엔티티 타입에 대응되는 릴레이션 T의 기본 키를 S에 외래 키로 포함시킨다.

S의 기본 키를 T의 외래 키로 포함시키면 정보의 중복이 발생한다.

단계 5: 2진 M:N 관계 타입

관계 타입 R에 대해서는 릴레이션 R을 생성한다.

참여 엔티티 타입에 해당하는 릴레이션들의 기본 키를 릴레이션 R에 외래 키로 포함시키고, 이들의 조합이 R의 기본키가 된다.

단계 6: 3진 이상의 관계 타입

3진 이상의 관계 타입 R에 대하여 릴레이션 R을 생성하고, R에 참여하는 모든 엔티티 타입에 대응되는 릴레이션들의 기본 키를 R의 외래 키로 포함 시킨다. 만약 R에 참여하는 엔티티 타입들의 카디날리티가 1:N:N 이면 1의 릴레이션의 기본 키를 참조하는 외래 키를 제외한 나머지 외래 키들의 조합이 릴레이션 R의 기본키가 된다.

단계 7: 다치 애트리뷰트

각 다치 애트리뷰트에 대하여 릴레이션 R을 생성한다.

다치 애트리뷰트를 애트리뷰트로 갖는 엔티티 타입이나 관계 타입에 해당하는 릴레이션의 기본 키를 릴레이션 R에 외래 키로 포함시킨다.

데이터베이스 설계

 데이터베이스 설계는 요구사항 분석 - 개념적 설계 - DBMS의 선정 - 논리적 설계 - 스키마 정제 -물리적 설계 - 보안 설계 -구현 단계로 이루어져 있다.

 개념적 데이터베이스 설계는 실제로 데이터베이스를 어떻게 구현할 것인가와는 독립적으로 정보 사용의 모델을 개발하는 과정이다. 사용자의 요구사항으로부터 개념적 스키마가 만들어지며 실세계의 엔티티, 관계, 프로세스, 무결성 제약조건을 나타내는 추상화 모델을 구축하며 주로 엔티티 - 관계(ER) 모델을 사용한다. 엔티티는 서로 구분이 되면서 조직체에서 데이터베이스에 나타내려는 객체를 의미한다.

 DBMS 선정은 기술적인 요인, 정치적인 요인, 경제적인 요인 등을 검토한 후에 DBMS를 선정한다.

 논리적 설계는 개념적 스키마에 알고리즘을 적용해서 논리적 스키마를 생성한다. 논리적 스키마를 나타내기 위해 관계 데이터 모델을 사용하는 경우에는 ER모델로 표현된 개념적 스키마를 관계 데이터베이스 스키마로 사상한다. 또한 더 좋은 스키마로 변환하기 위해서 정규화 과정을 적용한다.

 물리적 설계는 처리 요구사항들을 만족시키기 위해 저장 구조와 접근 경로 등을 결정한다. 응답 시간, 트랜잭션 처리율 등을 기준으로 성능을 평가한다.

ER 모델

 데이터베이스 설계를 용이하기 위해서 제안되었으며, 계속해서 이 모델이 강화되어서 현재는 EER(Enhanced Entity Relationship) 모델이 데이터베이스 설계 과정에 널리 사용되고 있다. 기본적인 구문으로 엔티티, 관계, 애트리뷰트가 있고, 기타 구문으로는 카디날리티 비율, 참여 제약조건 등이 있다.

엔티티

 엔티티는 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체를 의미하며 엔티티 타입은 동일한 애트리뷰트들을 가진 엔티티들의 틀이고, 엔티티 집합은 동일한 애트리뷰트들을 가진 엔티티들의 모임이다. 엔티티 타입으로는 크게 강한 엔티티 타입과 약한 엔티티 타입이 있다.

1. 강한 엔티티 타입은 독자적으로 존재하며 엔티티 타입 내에서 자신의 키 애트리뷰트를 사용하여 고유하게 엔티티들을 식별할 수 있는 타입이다.

2. 약한 엔티티 타입은 키를 형성하기에 충분한 애트리뷰트들을 가지지 못한 엔티티 타입으로 이 엔티티 타입이 존재하려면 소유 엔티티 타입이 존재해야하며 소유 엔티티 타입의 키 애트리뷰트를 결합해야만 약한 엔티티 타입을 식별할 수 있다. ER 다이어 그램에서 이중선 직사각형으로 표기하며 부분 키는 점선 밑줄을 그어서 표시한다.

ER모델에서 엔티티는 직사각형으로 표시한다.

애트리뷰트

 하나의 엔티티는 연관된 애트리뷰트들의 집합으로 설명된다. 엔티티는 독립적인 의미를 가지지만 애트리뷰트는 독립적인 의미를 가지지 않는다. ER 모델에서 타원형으로 나타내며 기본키의 경우에는 밑줄을 그어준다. 애트리뷰트와 엔티티 타입은 실선으로 연결한다.

1. 단순 애트리뷰트

 더 이상 다른 애트리뷰트로 나눌 수 없는 애트리뷰트로 ER 다이어그램에서 실선 타원으로 표현한다. ER 다이어그램에서 대부분의 애트리뷰트는 단순 애트리뷰트인 경우가 많다. 대부분의 단순 애트리뷰트는 단일 값 애트리뷰트이다. 단일 값 애트리뷰트는 각 엔티티마다 정확하게 하나의 값을 가지는 애트리뷰트를 의미한다. 예를 들면 학생의 학번 애트리뷰트는 어떠한 학생도 두 개 이상의 학번을 가지지 않으므로 단일 값 애트리뷰트이다.

2. 복합 애트리뷰트

 두 개 이상의 애트리뷰트로 이루어진 애트리뷰트로 동일한 엔티티 타입이나 관계 타입에 속하는 애트리뷰트들 중에서 연관된 것을 모아놓은 것이다.

이 외에도 다치 애트리뷰트, 저장된 애트리뷰트, 유도된 애트리뷰트가 있다.

관계와 관계 타입

관계는 엔티티들 사이에 존재하는 연관이나 연결로서 두 개 이상의 엔티티 타입들 사이의 사상으로 생각할 수 있다. 요구사항에서 동사가 ER 다이어그램에서 관계로 표현되며 다이아몬드로 표기한다. 관계 타입은 관계의 특징을 기술하는 애트리뷰트들을 가질 수 있으며, 키 애트리뷰트를 가지지 않는다. 

 관계와 관계 타입에는 차수와 카디날리티라는 것이 있다. 차수란 관계로 연결된 엔티티 타입들의 개수를 의미하며 주로 2진 관계가 흔하다. 카디날리티 비율은 한 엔티티가 참여할 수 있는 관계의 수를 나타낸다. 흔히 1 : 1, 1:N, M:N으로 구분을 한다.

1. 1:1 관계

 E1의 각 엔티티가 정확하게 E2의 한 엔티티와 연관되고, E2의 각 엔티티가 정확하게 E1의 한 엔티티와 연관되었을 경우

ex) 각 학생에 대해 최대한 한 개의 노트북이 있고, 각 노트북에 대해 최대 한 명의 학생이 있다면 학생과 노트북 간의 관계는 1 : 1 관계이다.

2. 1:N 관계

 E1의 각 엔티티가 E2의 임의의 개수의 엔티티와 연관되고, E2의 각 엔티티는 정확하게 E1의 한 엔티티와 연관되면 이 관계를 1 : N 관계라고 하며 실세계에서 가장 흔히 나타나는 관계이다.

ex) 각 학생은 한 명의 지도교수님을 가지고 있지만, 지도 교수님은 여러 학생들을 가지고 있다.

3. M:N 관계

 한 엔티티 타입에 속하는 임의의 개수의 엔티티가 다른 엔티티 타입에 속하는 임의의 개수의 엔티티와 연관될 경우 M:N 관계이다.

ex) 각 학생은 여러 강의를 수강할 수 있고, 각 강의는 여러 명의 학생들을 가질 수 있으므로 M : N 관계이다.

 카디날리티 비율의 최솟값과 최댓값을 관계와 엔티티를 연결하는 실선 위에 (min, max) 형태로 표기한다. 어떤 관계 타입에 참여하는 각 엔티티 타입에 대하여 min은 이 엔티티 타입 내의 각 엔티티는 적어도 min번 관계에 참여함을 의미한다. max는 이 엔티티 타입 내의 각 엔티티는 최대한 max 번 관계에 참여함을 의미한다.

min = 0은 어떤 엔티티가 반드시 관계에 참여해야 할 필요는 없음을 의미하고, max=*는 어떤 엔티티가 관계에 임의의 수만큼 참여할 수 있음을 의미한다.

 이와 비슷한 개념으로 전체 참여와 부분 참여가 있다. 전체 참여는 어떤 관계에 엔티티 타입 E1의 모든 엔티티들이 관계 타입 R에 의해서 어떤 엔티티 타입 E2의 어떤 엔티티와 연관되는 것을 의미한다. 부분 참여는 어떤 관계에 엔티티 타입 E1의 일부 엔티티만 참여하는 것을 의미한다. 약한 엔티티 타입은 항상 관계에 전체 참여로 표시되며, 전체 참여는 ER 다이어그램에서 이중 실선으로 표시된다. 

 위의 내용들에서 사용한 표기법으로 수십 개 이상의 애트리뷰트를 설명한다면 매우 불편하므로 새발 표기법이라는 것을 사용한다.

1. 다음 테이블 인스턴트 차트를 기반으로 DEPT 테이블을 생성하십시오.

CREATE TABLE dept(ID Number(7),NAME VARCHAR2(25));
describe dept;

2.DEPARTMENT 테이블의 데이터를 DEPT 테이블에 추가하십시오.

insert into dept(id , name)
select department_id,department_name from departments;

3. 다음 테이블 인스턴스 차트를 기반으로 emp 테이블을 생성하십시오.

Create table emp(ID Number(7),LAST_NAME VARCHAR2(25),FIRST_NAME VARCHAR2(25),DEPT_ID NUMBER(7));

4.EMP 테이블의 LAST_NAME 열의 최대 길이를 50으로 수정하십시오.

ALTER TABLE emp
modify (last_name varchar2(50));

5.EMP 테이블을 삭제하십시오.

drop table emp;

6. EMPLOYEES 테이블 구조를 기반으로 EMPLOYEES2 테이블을 생성하십시오.  EMPLOYEE_ID,first_name,last_name,salary 및  department_id 열만 포함시키고 새 테이블의 열 이름을 각각 ID,First_Name,Last_name,salary 및 dept_id로 지정하십시오.

 
create table EMPLOYEES2(ID number(6),FIRST_NAME varchar(20),LAST_NAME varchar(25),SALARY number(8,2),DEPT_ID number(4));

7. employees2 테이블의 이름을 emp로 변경하십시오.

RENAME employees2 to emp;

8.EMP 테이블에서 FIRST_NAME 열을 삭제하십시오.

ALTER TABLE emp
drop (first_name);

1. EMP 테이블의 ID 열에 테이블 레벨의 PRIMARY KEY 제약 조건을 추가. 제약 조건 이름은 my_emp_id_pk로 지정하시오.

 
alter table emp
add constraint "my_emp_id_pk" primary key(id);

2. ID 열을 사용하여 DEPT 테이블에 PRIMARY_key 제약 조 건을 생성. 제약 조건 이름은 my_dept_id_pk로 지정하시오.

alter table dept
Add (constraint "my_dept_id_pk" primary key(id));

3.EMP 테이블에 DEPT_ID 열을 추가. 존재하지 않는 부서 에 사원이 배정되지 않도록 외래 키 참조를 EMP 테이블에 추가. 제약 조건 이름은 my_emp_dept_id_fk로 지정하시오.

alter table emp
add (constraint "my_emp_dept_id_fk" foreign key(dept_id) references dept(id));

4.User_constaints 뷰를 질의하여 제약 조건이 추가되었는지 확인하시오. (EMP, DEPT 테이블만 질의)

select constraint_type,constraint_name, search_condition
from user_constraints
where table_name= 'emp' or table_name= 'dept';

5.EMP 테이블을 수정하여 십진 자릿수 2, 소수점 이하 자릿수 2인 NUMBER 데이터 유형의 COMMISSION 열을 추가하시오. 커미션 값이 0보다 크도록 커미션 열에 제약 조건을 추가하시오.

alter table emp
add (COMMISSION number(2,2));
alter table emp
add (constraint "commission_ck" check (commission>0));

1.모든 사원의 급여 최고액, 최저액, 총액 및 평균액을 표시하십시오. 열레이블을 각각 Maximum, Minimum, Sum 및Average로 지정하고 결과를 정수로 반올림하십시오.

 
select MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees;

2.1번을 수정하여 각 업무 유형별로 표시하십시오.

select job_id ,MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees group by job_id;

3.업무가 동일한 사원 수를 표시하는 질의를 작성하십시오.

select job_id,count(*)as "COUNT" from employees group by job_id;

4.관리자는 나열하지 말고 관리자 수를 확인하십시오. 열 레이블은 Number of Managers로 지정하십시오.

select count(*) as "Number of Managers" from employees group by manager_id;

5.최고 급여와 최저 급여의 차액을 표시하는 질의를 작성하고 열 레이블을 DIFFERENCE로 지정하십시오.

select MAX(salary)-Min(salary) as "DIFFERENCE" from employees;

6.관리자 번호 및 해당 관리자에 속한 사원의 최저 급여를 쵸시하십시오. 관리자를 알 수 없는 사원 및 최저 급여가 $6,000 미만인 그룹은 제외시키고 결과를 급여에 대한 내림차순으로 정렬하십시오.

select manager_id,min(salary) from employees where manager_id is not null and salary>6000 group by manager_id order by min(salary) desc;

7.각 부서에 대해 부서 이름, 위치, 사원 수, 부서 내 모든 사원의 평균 급여를 표시하는 질의를 작성하고, 열 레이블을 각각 Name,Locaton, Number of People 및 Salary로 지정하십시오. 평균 급여는 소수점 둘째 자리로 반올림하십시오.

select d.department_name as "Name", d.location_id as "Location", count(e.employee_id) as "Number of People",round(AVG(e.salary),2) as "Salary" from employees e,departments d
where e.department_id=d.department_id group by d.department_name,d.location_id;

8.총 사원 수 및 2005,2006,2007,2008년에 입사한 사원 수를 표시하는 질의를 작성하고 적합한 열 머리글을 작성하십시오.

 
select count(*) as "TOTAL", sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2005',1)) as "2005",sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2006','1')) as "2006" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2007','1')) as "2007" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2008','1')) as "2008" from employees;

9.업무를 표시한 다음 해당 업무에 대해 급여 총액과 부서 별 급여(20,50,80,90)의 총액을 각각 표시하는 형렬 질의를 작성하고 각 열에 적합한 머리글을 지정하십시오.

select job_id as "Job",NVL(sum(DECODE(department_id,20,salary)),0) as "DEPT20" ,NVL(sum(DECODE(department_id,50,salary)),0) as "DEPT50",NVL(sum(DECODE(department_id,80,salary)),0)as "DEPT80",NVL(sum(DECODE(department_id,90,salary)),0) as "DEPT90",sum(salary)as "TOTAL" from employees group by job_id;

1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select last_name,hire_date from employees where department_id =(select department_id from employees where last_name= 'Zlotkey') and not (last_name = 'Zlotkey');

2. 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) order by salary;

3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원번호와 이름을 표시하는 질의를 작성하십시오.

select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%');

4.부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.

select e.last_name,e.department_id,e.job_id from employees e,departments d where e.department_id=d.department_id and d.location_id = (select distinct location_id from departments where location_id=1700);

5.King에게 보고하는 모든 사원 이름과 급여를 표시하십시오.

select last_name,salary from employees where manager_id in (select employee_id from employees where last_name= 'King');

6.Executive 부서에 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시하십시오.

select department_id,last_name,job_id from employees where department_id =(select department_id from departments where department_name= 'Executive'); 

7.평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는 모든 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) and department_id in (select distinct department_id from employees where last_name like '%u%');

8.미국 내에서 근무하는 사원들의 평균급여보다 많은 급여를 받는 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(e.salary) from employees e,departments d where d.location_id in (select distinct location_id from locations where country_id= 'US'));

9.부서 별로 최고 급여를 받는 사원의 번호, 이름, 급여 및 부서 번호를 표시하고 부서 번호에 대해 오름 차순 정렬을 하시오.

 
select employee_id,last_name,salary, department_id from employees where (department_id,salary) in (select department_id,max(salary) from employees group by department_id) order by department_id;

10. From 절을 사용하여 9번 질의를 재 작성하시오.

select e1.employee_id, e1.last_name, e1.salary , e1.department_id
from   employees e1 inner join (select department_id, max(salary) as maxSalary
                                from   employees
                                group by department_id) e2 on e1.salary = e2.maxSalary and e1.department_id = e2.department_id order by e1.department_id;

11.사원이 한 명 이상 존재하는 부서의 번호 및 부서 이름을 표시하시오. (exists 키워드 사용)

select department_id,department_name from departments d where exists (select department_id from employees where department_id=d.department_id);

12. 다음을 참고하여 급여를 가장 적게 받는 사원 5명에 대한 정보를 표시하시오.

select rownum,employee_id,last_name,salary from (select employee_id,last_name,salary from employees order by salary) where rownum <=5; 

1.employees 테이블을복사하여cpy_emp테이블을생성하시오.describe문을사용하여employees와cpy_emp의스키마가동일한지확인하기바랍니다.

Create table cpy_emp as select * from employees;
describe cpy_emp;
describe employees;*/

2.다음 예제 데이터의 첫 번째 데이터 행을 cpy_emp 테이블에 추가하십시오. Insert 절에 열을 나열하지 마십시오.

Insert into cpy_emp
values(300,'Ralph','Patel','Rpatel',NULL,sysdate,'SA_MAN',NULL,NULL,NULL,NULL);

3.위의 목록에 있는 예제 데이터의 두 번째 행을 cpy_emp 테이블에 추가하십시오. 이번에는 Insert 절에 열을 명시적으로 나열하십시오.

 
Insert into cpy_emp(employee_id,first_name,last_name,email,hire_date,job_id)
values(301,'Dancs','Betty','Bdancs',sysdate,'SA_REP');

4.301번 사원의 Last_name을 Drexier로 변경하십시오.

update cpy_emp set last_name= 'Drexler' where last_name= 'Betty';

5. 300, 301번 사원의 나머지 사원 정보를 Vance Jones 와 동일하게 변경하시오.

update cpy_emp
set phone_number=(select phone_number from employees where first_name= 'Vance' and last_name= 'Jones'),
salary=(select salary from employees where first_name= 'Vance' and last_name= 'Jones'),
commission_pct=(select commission_pct from employees where first_name= 'Vance' and last_name= 'Jones'),
manager_id=(select manager_id from employees where first_name= 'Vance' and last_name= 'Jones'),
department_id=(select department_id from employees where first_name= 'Vance' and last_name= 'Jones')
where employee_id= '301' or employee_id= '300';

6.급여가 3000미만인 모든 사원의 급여를 3000으로 변경하십시오.

update cpy_emp
set salary=3000
where salary<3000;

7.Dancs Drexler 사원의 정보를 삭제하십시오.

delete from cpy_emp
where last_name= 'Drexler' and first_name= 'Dancs';

1.현재 날짜를 표시하는 질의를 작성하고 열 레이블을 Data로지정하십시오.

SELECT sysdate as "DATA" FROM dual;

-현재 날짜는 dual의 sysdate에 있다.

2.각 사원에 대해 사원 번호,이름, 급여및15% 인상된급여를정수로표시하십시오. 인상된 급여열의 레이블을 New Salary로 지정하십시오.

SELECT employee_id, last_name, salary*1.15 as "New Salary" FROM employees;

-애트리뷰트들을 나열하는 부분에 곱하기와 같은 사칙연산자가 들어갈 수 있다.

3.이름이 J, A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를표시하는 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오. 결과를 사원의 이름에 따라 정렬하십시오.

 
SELECT INITCAP(last_name) as "LAST_NAME",length(last_name) as "LENGTH OF LAST NAME" FROM employees WHERE last_name like 'J%' or last_name like 'A%' or last_name like 'M%' order by last_name;

-INITCAP은 첫글자만 대문자, 나머지는 소문자로 표시해주는 함수이고, length는 길이를 표시해주는 함수이다.

4.각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하십시오. 결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 정렬하십시오

SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate,hire_date)) as "MONTHS_WORKED" FROM employees ORDER BY MONTHS_WORKED;

-MONTHS_BETWEEN은 두 날짜 사이에 차이를 나타내주며 ROUND로 정수로 반올림이 가능하다.

5.각 사원에 대해 다음 항목을 생성하는 질의를 작성하십시오. earn monthly but wants <3 times salary> 열레이블을Dream Salaries로 지정하십시오 

SELECT last_name ||' earn ' || salary || ' monthly but wants '|| 3*salary as "Dream Salaries" FROM employees;

-특정한 문장으로 질의를 만들어야 하므로 이는 || 를 사용해서 이을 수 있다.

6.모든 사원의 이름과 급여를 표시하는 질의를 작성하십시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하십시오. 

SELECT last_name,LPAD(salary,15,'$') as "SALARY" FROM employees;

-글자의 길이를 설정해주고 남은 글자들을 원하는 것으로 채워주는 함수가 LPAD이다. LPAD는 왼쪽에 빈 공간을 채우고 RPAD는 오른쪽에 빈 공간을 채워준다.

7.사원의 이름, 입사일 및 급여 검토일을 표시하십시오. 급여 검토일은 여섯달이 경과한 후 첫번째 월요일입니다. 열레이블을 REVIEW로 지정하고 날짜는 “Monday, the Thirty-First of July, 2000”과 같은 형식으로 표시되도록 지정하십시오.

SELECT last_name,hire_date, To_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6),'월요일'),'DAY,DD "of the" MONTH,YYYY') as "REVIEW" FROM employees;

-ADD_MONTHS를 통해서 6달이 지난 것을 알 수 있고, NEXT_DAY로 그 다음 첫번째 월요일을 얻을 수 있다. 또한 날짜를 특정 형식으로 만들어 줘야 하므로 이를 위해 TO_CHAR로 변환한다.

8. 이름, 입사일 및 업무 시작 요일을 표시하고 열레이블을 DAY로 지정하십시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하십시오. 

SELECT last_name,hire_date,To_CHAR(hire_date,'DY') as "DAY"  FROM employees ORDER BY TO_CHAR(hire_date-1,'D');

-윗 문제처럼 날짜에서 특정 시간만 가져오려 하면 TO_CHAR를 사용하도록 한다. 또한 요일을 정렬할 때 ORDER BY를 사용하는데 이 때 월요일을 기준으로 맞춰주려면 hire-date에서 하루를 빼야한다.

9.사원의 이름과 커미션을 표시하는 질의를 작성하십시오. 커미션을 받지 않는 사원일 경우 “No Commission”을 표시하십시오. 열레이블은 COMM으로 지정하십시오.

SELECT last_name,NVL(To_CHAR(commission_pct,'0.99'),'No Comission') as "COMM" FROM employees;

-커미션을 받지 않는 사원은 null일 것 이므로 NVL를 통해서 바꿔주도록 한다.

10.사원의 이름을 표시하고 급여 총액을 별표(*)로 나타내는 질의를 작성하십시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를내림차순으로정렬하고열레이블을EMPLOYEES_AND_THEIR_SALARIES로지정하십시오.*/

SELECT last_name, salary, RPAD(' ',salary/1000+1,'*') as "EMPLOYEES_AND_THEIR_SALARIES" FROM employees ORDER BY salary DESC;

-RPAD를 사용해서 급여를 별표로 바꾸었다.

11. DECODE 함수를 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하십시오. 업무등급 AD_PRES A, ST_MAN B, IT_PROG C, SA_REP D, ST_CLERK E, 기타0 

SELECT last_name,DECODE (job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E',0) as "GRADE" FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 DECODE를 사용한 예시이다.

12. 11번 문제의 명령문을 CASE 구문을 사용하여 재작성하십시오.

SELECT last_name, 
  CASE job_id
   WHEN 'AD_PRES' THEN 'A'
   WHEN 'ST_MAN' THEN 'B'
   WHEN 'IT_PROG' THEN 'C'
   WHEN 'SA_REP' THEN 'D'
   WHEN 'ST_CLERK' THEN 'E'
  ELSE '0' END AS GRADE FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 CASE를 사용한 예시이다.

1. 모든사원의이름, 부서번호, 부서이름을표시하는질의를작성하십시오. 

select e.last_name,d.department_id,d.department_name from employees e,departments d;

- 두 개의 릴레이션에 정보를 가져오고자 할 때에는 릴레이션에도 별칭을 부여한다.

 2. 부서80에속하는모든업무의고유목록을작성하고출력결과에부서의위치를포함시키십시오. (중복제거)

select distinct e.job_id , d.location_id from employees e INNER JOIN departments d ON e.department_id=80;

- 조인을 사용하였고, 중복을 제거하기 위해서 distinct를 사용하였다.

3. 커미션을 받는 모든 사원의 이름, 부서이름, 위치 ID 및 도시를 표시하는 질의를 작성하십시오.

select e.last_name,d.department_name,d.location_id,l.city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;

-JOIN 연산자가 아닌 where 조건으로 조인을 하였다.

4. 이름에 a(소문자)가 포함된 모든 사원의 이름과 부서 이름을 표시하는 질의를 작성하십시오.

select e.last_name,d.department_name from employees e,departments d where e.last_name like '%a%' and e.department_id=d.department_id;

5.Toronto에서 근무하는 모든 사원의 이름,업무,부서 번호 및 부서 이름을 표시하는 질의를 작성하십시오. (join,on 키워드 사용)

 
select e.last_name,e.job_id,d.department_id,d.department_name
from employees e Inner Join departments d on (e.department_id=d.department_id) Inner Join locations l on (d.location_id=l.location_id) where l.city= 'Toronto';

6.사원의 이름 및 사원 번호를 관리자의 이름 및 관리자 번호와 함께 표시하고, 각각의 열 레이블을 Employee,Emp#, Manager,Mgr#로 지정하십시오.(관리자가 없는 사원도 포함)

select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id;

7.지정한 사원의 이름, 부서 번호 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고, 각 열에 적합한 레이블을 지정하십시오. 또한 부서번호, 사원 이름, 동일한 부서에서 근무하는 사원의 이름으로 오름 차순 정렬하시오.

 
select e1.department_id as "DEPARTMENT", e1.last_name as "EMPLOYEE", e2.last_name as "COLLEAGUE"
from employees e1,employees e2 where e1.department_id=e2.department_id and not e1.last_name=e2.last_name
Order by DEPARTMENT, EMPLOYEE, COLLEAGUE;

8.Davies라는 사원보다 늦게 입사한 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select last_name,hire_date from employees where hire_date >=(select hire_date from employees where last_name='Davies');

9.관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하는 질의를 작성하십시오.

select e1.last_name,e1.hire_date,e2.last_name,e2.hire_date from employees e1 Inner Join employees e2 on (e1.manager_id=e2.employee_id)
where e1.hire_date < e2.hire_date;

INSERT, DELETE, UPDATE문

INSERT문은 기존의 릴레이션에 투플을 삽입하는 질의이다. 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다. 

INSERT

INTO 릴레이션(애트리뷰트1, ...., 애트리뷰트n)

VALUES (값1, ... , 값n);

DELETE문은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제한다. 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

DELETE FROM 릴레이션 WHERE 조건;

UPDATE문은 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정한다. 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.

UPDATE 릴레이션

SET 애트리뷰트 = 값 또는 식[ , ...]

WHERE 조건;

트리거와 주장

트리거란 명시된 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문이다. 데이터베이스의 무결성을 유지하기 위한 도구이며, 트리거를 이벤트-조건-동작(Event-Condition, Action) 규칙이라고도 부른다.

CREATE TRIGER <트리거 이름>

AFTER <트리거를 유발하는 이벤트> ON <릴레이션> [WHEN <조건>]

BEGIN <SQL문(들)> END;

이벤트의 가능한 예는 테이블에 삽입, 삭제, 수정등이 있다.

주장이란 제약조건을 위반하는 연산이 수행되지 않도록 하는 것이다.

CREATE ASSERTION 이름 CHECK 조건;

일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용된다.

SQL문 실습

1. 급여가 $12,000를 넘는 사원의 이름과 급여를 표시

2. 사원 번호가 176인 사원의 이름과 부서 번호를 표시

3.급여가 $5,000에서 $12,000 사이에 포함되지 않는 모든 사원의 이름과 급여를 표시

4. 2007년 2월 20일과 2007년 5월 1일 사이에 입사한 사원의 이름, 업무 ID 및 시작일을 표시하되, 시작일을 기준으로 오름차순으로 정렬하는 질의

5. 부서 20 및 50에 속하는 모든 사원의 이름과 부서 번호를 이름을 기준으로 영문자순으로 표시

6.급여가 $5,000와 $12,000 사이이고 부서 번호가 20 또는 50인 사원의 이름과 급여를 나열하고, 열 레이블을 Employee와 Monthly Salary로 각각 지정

7.1994년에 입사한 모든 사원의 이름과 입사일을 표시

8.관리자가 없는 모든 사원의 이름과 업무 ID를 표시

9.커미션을 받는 모든 사원의 이름,급여 및 커미션을 급여 및 커미션을 기준으로 내림차순으로 정렬하여 표시

10.이름의 세 번째 문자가 a인모든사원의이름을표시

11.이름에 a와 e가 있는 모든 사원의 이름을 표시

12. 업무가 영업 사원(SA_REP)또는 사무원(ST_CLERK)이면서급여가 $2,500, $3,500 또는$7,000가 아닌 모든 사원의 이름, 업무 및 급여를 표시

13.커미션 비율이 20%인 모든 사원의 이름, 급여 및 커미션을 표시

SQL은 비절차적 언어이므로 사용자는 자신이 원하는(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없다. 또한 SQL은 대화식 SQL , 내포된 SQL 두 가지의 인터페이스를 가지고 있으며, 데이터 검색, 데이터 조작어, 데이터 정의어, 트랜잭션 제어, 데이터 제어어로 구성되어 있다.

데이터 정의어

스키마의 생성과 제거의 기능을 제공하며 다음과 같이 정의한다.

CREATE TABLE STUDENTS

(STUNO NUMBER NOT NULL,

STUNAME CHAR(10),

GRADE NUMBER,

CLASS NUMBER,

PRIMARY KEY(STUNO), [ 제약조건 ] )

STUDENTS의 스키마를 만드는데 그 안에 STUNO, STUNAME, GRADE, CLASS 와 같은 애트리뷰트를 가지는 것을 의미한다. 각 애트리뷰트명 옆에 써져 있는 것은 데이터 타입이고 다음 표와 같은 것들이 있다. 그리고 마지막에 PRIMARY KEY(STUNO)와 같이 기본키를 명시하고 제약 조건들을 명시해줄 수 있다.

이 밖에도 릴레이션 제거(DROP TABLE CLASSROOM), ALTER TABLE, 인덱스 생성 (CREATE INDEX)과 같은 기능들을 지원한다.

SELECT문

관계 데이터베이스에서 정보를 검색하는 SQL문으로 관계 대수의 실렉션과 의미가 완전히 다르다.

관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것과 같다고 볼 수 있으며 가장 많이 사용된다.

기본적인 SQL 질의에서 SELECT 절과 FROM절만 필수적인 절이고, 나머지는 선택 사항이다.

SELECT  [DISTINCT] 애트리뷰트(들)

FROM 릴레이션(들)

[WHERE 조건 [중첩 질의]] [GROUP BY 애트리뷰트(들)] [HAVING 조건] [ORDER BY 애트리뷰트(들) [ASC|DESC]];

다음은 SELECT문에서 사용할 수 있는 기능들이다.

별칭(alias)

서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

FROM EMPLOYEE AS E, DEPARTMENT AS D

모든 애트리뷰트 검색

SELECT  * FROM DEPARTMENT;

DISTINCT

모든 상이한 값들만 검색하고자 할 때 사용한다. SELECT DISTINCT TITLE FROM EMPLOYEE;

WHERE

특정한 조건의 투플들만 검색하고자 할 때 사용한다.

2번 부서에 근무하는 사원들의 정보를 검색해라 == SELECT * FROM EMPLOYEE WHERE DNO=2;

문자열 비교

이씨 성을 가진 사원들의 이름, 직급, 소속 부서 번호를 검색하라.

SELECT EMPNAME, TITLE, DNO FROM EMPLOYEE WHERE EMPNAME LIKE '이%' ;

ORDER BY절

사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시된다. ORDER BY 절을 명시하면 해당 애트리뷰트를 기준으로 검색 결과를 정렬한다. SELECT문에 가장 마지막에 사용되며 디폴트 정렬 순서를 오름차순(ASC)이며, DESC를 지정하며 내림차순으로 정렬할 수 있다.

단, SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 한다.

그룹화

GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 하나의 그룹으로 묶임 이 애트리뷰트를 그룹화 애트리뷰트라고 한다. SELECT 절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있다.

HAVING절

어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있다. 따라서 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용해서 각 그룹이 만족해야 하는 조건을 명시한다. 그룹화 애트리뷰트에 같은 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타난다.

조인

두 개 이상의 릴레이션으로부터 연관된 투플들을 결합한다. 일반적인 형식은 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함된다. 흔히 =(비교 연산자)로 연결한다.

이 밖에도 많은 기능이 있으나 실습편에서 소개하겠습니다.

중첩 질의

질의의 where 또는 FROM절에 다시 SELECT문이 포함되는 것이며 부 질의라고 한다.

부 질의에서 한 개의 스칼라 값이 반환되는 경우 where절에서 상수 또는 애트리뷰트가 사용될 위치에 나타날 수 있다.

만약 릴레이션이 반환되는 경우에는 IN,  ANY, ALL, EXISTS와 같은 연산자를 사용해야 한다.