[DB] 중급 SQL - 2

데이터베이스 시스템 7판을 읽으며 간략하게나마 정리하는 글입니다.

 

5. SQL의 데이터 타입과 스키마

5 - 1) 타입 변환 및 서식 함수

일부 데이터 타입 변환(Conversion)을 자동으로 수행하지만, 몇몇 시스템들은 명시해줘야 하는 경우도 있다.

case(e as t)

위와 같이 사용하면 e를 t타입으로 변환할 수 있다.

 

예를 들어 특정 연산 수행이나, 정렬 순서를 강제하기 위해 타입변환을 하기도 하는데,

varchar(5)로 지정된 ID 속성을 생각해보자.

 

ID 11111 이 ID 9 앞에 오는데, 이는 첫번째 문자 '1'이 '9'보다 앞에 오기 떄문이다.

따라서 다음과 같이 타입 변환하여 작성하면 원하는 순서대로 출력되게 된다.

SELECT CAST(ID as numeric(5)) as inst_id
FROM instructor
order by inst_id

 

또 다른 문제는 null값 처리이다. coalesce함수를 사용하여 질의 결과에서 어떻게 null을 출력할지 선택할 수 있다.

coalesce 함수는 모드 같은 타입인 임의의 개수의 인자를 취하며, 맨 처음 null이 아닌 인자를 반환한다.

 

예를 들어 다음 질의 결과는 급여가 null일 경우 0을 반환한다.

SELECT ID, coalesce(salary, 0) as salary
FROM instructor

 

5 - 2) 기본값

create table 문과 같이 속성에 대한 기본값을 지정할 수 있다.

CREATE TABLE student(
     ID             varchar(5), 
     name           varchar(20) not null, 
     dept_name      varchar(20), 
     tot_cred       numeric(3,0) default 0,
     primary key (ID)
);

tot_cred 속성의 기본값으로 0을 선언한다.

만약 insert 할때 까먹고 tot_cred 값을 선언하지 않으면 기본값인 0이 삽입된다.

 

5 - 3) 대형 객체 타입

사진, 고화질 의료 영상 이미지, 비디오 와 같은 큰 데이터 항목을 도메인으로 하여 저장할 수 있는 속성이 필요하다.

SQL은 큰 문자 데이터(clob) 이나, 큰 이진데이터(blob)에 대한 대형 객체 데이터 타입(large-object data type)을 제공하며,

clob과 blob 같은 데이터형 이름의 일부인 "lob"은 Large Object를 의미한다.

book_review clob(10KB)
image blob(10MB)
movie blob(2GB)

인반적으로 대용량 파일을 직접 저장하기 보다는, 대형 객체에 대한 "위치자"를 얻어서 사용한다.

 

5 - 4) 사용자 정의 타입

SQL 에서는 사용자 정의 타입을 두가지 형태로 지원한다.

 

1. 고유 타입(distinct type)

2. 정형 데이터 타입(structured data type)으로, 중첩 레코드, 배열, 다중 집합으로 된 복잡한 타입이다.

 

교수의 이름을 학과의 이름에 할당한다거나, 달러로 표시된 화폐 단위를 파운드로 표시된 화폐 단위와 비교하는 것은 프로그래밍 오류일 것 이다.

좋은 타입 시스템이라면 이러한 오류를 잘 탐지할 수 있어야 한다.

 

이런 종류의 검사를 지원하기 위해 SQL은 distinct type을 제공한다.

 

새로운 타입을 정의하기 위해 create type 절을 사용한다.

create type Dollars as numeric(12, 2) final;
create type Pounds as numeric(12, 2) final;

위 쿼리는 사용자 정의 타입을 정의하며, 이들 타입은 총 12자리 수 이며 그중 2자리는 소수점 이하이다.

 

위에서 새로 생성한 타입을  릴레이션 정의시 사용할 수 있다.

CREATE TABLE department (
    dept_name varchar(20),
    building  varchar(15),
    budget    Dollars
);

Dollars형의 값을 Pounds 형의 변수에 할당하려 하면 둘 다 같은 수치형일지라도 컴파일 오류를 발생시킨다.

 

SQL(1999)에서는 사용자 정의 타입 이전에 정의된 도메인 타입이 있다.

도메인 타입은 not null 과 같은 제약조건을 가질 수 있으며, 기본값 설정이 가능하지만,

사용자 정의 타입은 불가능하다.

 

또한 도메인 타입은 엄격하게 지킬 필요가 없다. 때문에 기초형이 호환 가능한 한, 한 도메인의 값을 또 다른 도메인 타입의 값으로 할당할 수 있다.

 

5 - 5) 고유 키값 생성하기

데이터베이스 시스템은 고유 키-값 쌍의 생성을 자동으로 진행하고 관리한다.

이는 해당 DBMS마다 다르며, 서령 같은 DBMS여도 버전 마다 다를수도 있다.

 

예를 들어 Oracle 에서 교수 ID를 "ID varchar(5)"로 선언하지 않고 DBMS가 고유한 교수 ID를 찾아서 설정하게 할 수도 있다.

이러한 특성은 수치형 타입에 대해서만 동작하므로 ID 타입을 number로 변경해야 한다.

ID number(5) generated always as identity

 

이후 데이터를 삽입할때는 ID 속성을 제외하고 삽입하면 된다.

insert into instructor(name, dept_name, salary)
values ('Newprof', 'Comp, Sci.', 100000);

 

MySQL에서는 generated always as identity 대신 auto increment가 사용된다.

 

또한 많은 DBMS가 create sequence 구문을 지원하는데, 이는 릴레이션으로부터 독립적인 시퀀스 카운터 객체를 생성하며, SQL 질의를 통해 이 시퀀스 객체로부터 다음 값을 가져오도록 할 수 있다.

시퀀스는 student.ID, instructor.ID와 같이 여러 릴레이션에 대해 교차적으로 사용 가능한 고유 식별자를 생성한다.

 

5 - 6) Create Table 확장

기존의 테이블과 같은 스키마를 가지고 있는 테이블을 생성해야 하는 일이 종종 있다.

SQL은 이를 위해 create table like 확장 구문을 지원한다.

create table temp_instructor like instructor;

instructor 와 같은 스키마를 갖는 temp_instructor 라는 테이블을 생성한다.

 

5 - 6) 스키마, 카탈로그, 환경

현 데이터베이스 시스템은 릴레이션에 이름을 붙일 때 3단계 계층 구조를 제공한다.

 

최상위 계층은 카탈로그(catalog)로 구성되어 있고, 각 카탈로그는 스키마(schema)를 포함할 수 있다.

SQL에서 릴레이션이나 뷰와 같은 객체는 어떤 스키마 내에 포함된다.

(일부 DBMS는 카탈로그 대신 "데이터베이스"라는 용어를 사용한다)

 

기본적으로 데이터베이스 시스템은 사용자가 시스템에 접속할 때 기본 카탈로그와 스키마를 제공한다.

 

릴레이션을 유일하게 구분하려면 다음과 같이 세 부분으로 구성된 이름을 사용하면 된다.

catalog5.univ_schema.course

여기서 카탈로그는 생략 가능한데, 생략시 현재 연결되어 사용중인 카탈로그를 기본으로 지정한다.

 

DDL, DML 구문을 포함한 모든 일반 SQL문은 스키마 맥락에서 동작한다.

 

6. SQL의 Index 정의

전체 레코드 상에서 매우 작은 일부분 만을 참조하는 경우가 있다.

ID 필드가 "12345"인지 검사한다거나, building 필드 값이 "생물학과"인 레코드를 모두 읽어서 찾는것은 매우 비효율 적 이다.

 

따라서 릴레이션의 속성에 대한 인덱스(index)를 사용한다.

인덱스는 모든 튜플을 살펴보지 않고도 효과적으로 찾을 수 있는 자료구조 이다.

 

인덱스는 데이터의 정확성 떄문에 필요한것은 아니다, 왜냐하면 인덱스는 중복 데이터 구조이기 때문이다.

인덱스는 물리 스키마의 일부이다.

 

원칙적으로 데이터베이스 시스템은  생성할 인덱스를 자동으로 결정할 수 있다.

그러나 인덱스가 소모하는 공간 비용과 갱신 처리에 대한 인덱스의 영향 때문에 어떤 인덱스를 유지할지에 대한 올바른 결정을 내리기는 쉽지 않다.

 

따라서 대부분의 경우 사용자가 DDL 명령어를 통해서 인덱스를 자유럽게 생성하고, 제거하도록 하는 제어권을 제공한다.

 

인덱스를 생성할때는 create index 명령문을 사용한다.

create index <index-name> on <relation-name> (<attribute-list>);

attribute-list는 인덱스에 대한 검색 키를 형성하는 릴레이션의 속성 목록이다.

 

dept_name을 키로 사용하여 instructor 릴레이션에 인덱스를 정의하려면 다음과 같이 작성한다.

create index dept_index as instructor (dept_name);

사용자가 인덱스를 통해 성능의 이득을 볼 수 있는 쿼리문을 입력하면 SQL 질의 처리기가 자동으로 인덱스를 사용하게 된다.

 

검색키가 후보키 라는 것을 명시하려면 인덱스 정의에 unique 속성을 추가한다.

create unique index dept_index as instructor (dept_name);

만약 dept_index가 후보키가 아니라면 오류를 발생하고 인덱스 생성에 실패한다.

 

인덱스를 삭제할때는 이름이 필요하다.

drop index <index-name>;

 

7. 권한

다음과 같이 데이터베이스 일부에 대한 여러 형태의 권한을 사용자에게 할당할 수 있다.

• 데이터를 읽을 권한
• 새로운 데이터를 삽입할 권한
• 데이터를 갱신할 권한
• 데이터를 삭제할 권한

이러한 유형의 각 권한을 특권(privilege)라고 부릅니다.

 

사용자가 질의나 갱신을 수행할 때, DBMS는 우선 해당 사용자의 권한을 토대로 해당 질의를 수행할 권한이 있는지 확인합니다.

또한 일부 형태의 권한을 소유한 사용자는 자신의 권한을 타 사용자에게 전달하거나 이전에 부여된 권한을 철회할 수 있습니다.

 

가장 권한이 높은 권한은 데이터베이스 관리자(DBA)권한 입니다.

 

7 - 1) 특권 부여 및 취소

SQL 표준은 select, insert, update, delete에 대한 privilege를 포함합니다.

허용 가능한 모든 권한의 축약형으로 all privileges 특권을 사용할수도 있다.

새로운 릴레이션을 생성하면 그 릴레이션에 대한 모든 privilege를 자동으로 부여받게 됩니다.

 

권한 수여를 위해 grant 문을 사용하게 됩니다.

grant <privilege list>
on <relation name or view name>
to <user/role list>

위 코드에서는 privilege list 는 하나의 명령어로 여러개의 특권을 부여할 수 있도록 해줍니다.

 

▶ select

다음 grant문은 department 릴레이션에 대한 select 권한을 사용자 Amit와 Shine에게 부여한다.

grant select on deparment to Amit, Shine;

 

▶ update

업데이트 권한의 경우 모든 속성에 대해 update 권한을 부여하거나, 일부 속성에만 부여할수도 있다.

일부 속성에만 부여하려면 update 키워드 다음에 괄호안에 속성 목록을 선택적으로 전달하면 된다.

grant update (budget) on department to Amit, Shine;

 

▶ insert

insert 권한 또한 위 update와 동일한 형태로 사용한다.

insert도 특정 속성에만 권한을 줄 수 있으며, 나머지 속성은 기본값을 부여하거나 Null을 설정해야 한다.

 

▶ delete

릴레이션에 대한 delete 권한은 사용자가 릴레이션의 튜플을 삭제할 수 있게 한다.

 

public이라는 사용자 이름은 시스템에서 현재와 앞으로 사용할 모든 사용자를 지칭한다.

그래서 시스템은 현재와 앞으로의 모든 사용자에게 public에 부여한 특권을 묵시적으로 부여한다.

 

기본적으로 특권을 부여받은 사용자/역할은 다른 사용자/역할에게 특권을 부여할 수 없다.

 

SQL의 권한은 전체 릴레이션 or 지정된 속성에 대해서만 권한을 부여하는 방식이라는 점에 주의하자!

특정 튜플에 권한 부여는 불가능 하다.

 

권한 취소는 revoke 문을 통해 가능한데, grant문의 형식과 유사하다.

revoke <privilege list>
on <relation name or view name>
from <user/role list>;

위에서 부여했던 특권은 다음과 같이 취소할 수 있다.

revoke select on deparment to Amit, Shine;
revoke update (budget) on department from Amit, Shine;

 

7 - 2) 역할

학교에 새로운 교수가 임명될때 마다 새롭게 교수가 필요한 privilege를 부여하기 보다는,

어떤 데이터베이스 사용자가 교수인지를 별도로 식별하게 하는 것이 더 유용하다.

 

교수의 권한을 미리 결정하고, 새로운 교수가 채용되면 사용자 식별자를 그에게 부여하여 그를 반드시 교수로 식별해야 한다.

그러면 교수에게 일일이 다시 권한을 지정할 필요가 없게된다.

 

이러한 개념이 역할(role) 이다.

시스템은 역할에 권한을 부여할수가 있다. 개별 자용자에게는 수행할 권한의 집합인 역할을 부여한다.

사용자에게 부여할 수 있는 임의의 권한을 역할에 부여할 수 있다. 권한이 사용자에게 부여되는 것 처럼 역할도 사용자에게 부여될 수 있다.

 

SQL에서는 다음과 같이 역할을 생성한다.

create role instructor;

 

역할에 특권을 다음과 같이 부여한다.

grant select on takes
to instructor;

 

다음과 같이 사용자 뿐만 아니라, 다른 역할에도 역할을 부여할 수 있다.

create role dean; // 역할 생성
grant instructor to dean; // 역할을 역할에 부여
grant dean to Shine; // 역할을 사용자에게 부여

 

사용자가 데이터베이스 시스템에 로그인할 때, 그 session 동안 해당 사용자가 실행한 동작 행위는 그 사용자에게 직접 부여된 모든 특권뿐만 아니라 그 사용자에게 부여된 역할이 수여한 모든 특권을 가지게 된다.

 

7 - 3) 뷰에 대한 권한

어떤 직원이 지질학과에 근무하는 모든 교수진의 급여정보가 필요하다고 해보자. 이 직원은 다른 학과 교수진에 관한 정보를 볼 권한은 없다.

이럴때 instructor 튜플 중 지질학과와 관련된 튜플만으로 구성된 geo_instructor와 같은 view에 대한 접근 권한을 수여할 수 있다.

 

우선 view를 생성해보자.

create view geo_instructor as (
    select *
    from instructor
    where dept_name = 'Geology'
);

이후 다음 쿼리문을 수행해보자.

select *
from geo_instructor;

직원은 위 쿼리의 결과를 볼 수 있는 권한을 가진다.

 

하지만 질의 처리기가 위 쿼리문에서 from절의 view를 실제 릴레이션으로 변환할 때, view 사용을 view 정의로 변경하여 instructor 릴레이션에 대한 질의문으로 바뀐다.

따라서 시스템은 view를 해당 view의 정의로 교체하기 전에 그 직원의 질의에 대한 권한을 반드시 검사해야 한다.

 

따라서 위 geo_instructor view 예제에서 뷰를 생성한 사용자는 instructor 릴레이션에 대한 select 권한을 가지고 있어야 한다.

 

7 - 4) 스키마에 대한 권한

SQL 표준은 스키마를 위한 기본적인 권한 부여 방법을 명시한다.

스키마의 소유자만 릴레이션의 생성과 삭제, 릴레이션 속성의 추가 및 삭제, 인덱스 추가 및 삭제와 같은 스키마 수정을 할 수 있다.

 

하지만 SQL은 사용자가 relation을 생성할 때 외래키를 선언할 수 있도록 허가하는 references 특권을 포함한다.

 

references 특권은 update 특권처럼 특정 속성에 수여한다.

다음 grant문은 Mariano라는 사용자가 department 릴레이션의 key인 dept_name속성을 외래키로 참조하는 릴레이션을 생성할 수 있게 한다.

grant references (dept_name) on department to Shine;

처음에는 사실 다른 릴레이션을 참조하는 외래 키를 사용자가 생성하지 못하도록 하는것은 어색해 보인다.

 

하지만 외래 키 제약 조건은 참조되는 릴레이션에 대한 삭제와 갱신을 제한한다는 것을 상기해 보자.

Shine이 deparment 릴레이션의 dept_name 속성을 참조하는 외래 키를 릴레이션 r에 생성하고, 지질학과와 관련된 튜플을 릴레이션r에삽입하는 상황을 생각해보자.

이해가 잘 안가, 직접 그려본 그림

이 경우 릴레이션 r을 수정하지 않고 department 릴레이션에서 지질학과를 삭제하는 것은 불가능해진다.

(지질학과를 department를 삭제해 버리면 외래키 무결성 제약조건에 위반되버림...)

 

즉, Shine이 선언한 외래 키 정의가 타 사용자의 미래 활동을 제한하게 된다. 따라서 references 특권이 필요하게 된 것 이다.

 

계속해서 department 에 대한 references 특권은 해당 제약 조건 department 릴레이션을 참조하는 하위 질의가 있다면 릴레이션 r에 대한 check 권한 생성도 필요하다.

 

이는 외래 키 제약 조건과 같은 이유인데, 릴레이션을 참조하는 check 제약 조건은 해당 릴레이션에 대한 잠재적 갱신을 제한합니다.

 

7 - 5) 특권 양도

특권을 부여하고 특권 수신자가 다른 사용자에게 그 특권을 양도하려면 grant문에 with grant option 절을 추가해야 합니다.

 

예를 들어 Shine에게 department 릴레이션에 대한 select 특권을 주고 Shine이 이 특권을 다른 사용자에게 양도할 수 있게 하려면

grant select on department to Shine with grant option;

과 같이 작성해야 한다.

 

7 - 6) 특권 취소

권한의 경우 어떤 사용자/역할의 특권이 취소될 경우 이를 양도받은 사용자/역할 도 그 특권을 잃을 수 있다.

이를 연쇄 취소(cascading revocation)이라 부른다.

 

연쇄 취소는 기본적으로 적용되며, 만약 연쇄 취소를 방지하고 싶다면 다음과 같이 restrict를 사용하면 된다.

revoke select on department from Amit, Shine restrict;

 

SQL에는 Session과 관련된 current role 이라는 개념이 있다.

기본적으로 한 세션과 연관된 현재 역할은 null이다. set role role_name을 실행하여 세션과 연관된 현재 역할을 설정할 수 있다.

 

7 - 7) 행 수준 권한

일부 데이터베이스 시스템은 릴레이션 내의 특정 튜플 수준에서 세분화된 권한 부여 방식을 제공한다.

 

예를 들어 Oracle의 가상 사설 데이터베이스(VPD) 특성은 행 수준 권한 부여 방식을 지원한다.