[데이터베이스] 뷰(View) 정의와 사용, 삽입

View Definition and Use

with로 만든 것도 뷰이지만, 쿼리 안에서만 유효하고, 쿼리가 끝나면 삭제된다. 뷰가 만들어지면 Db는 이 뷰를 저장할 것이고 마치 테이블 처럼 DB 어디서든 접근 할수 있고 권한 이슈도 해결 가능하다

as operator를 꼭 써야된다. 뷰는 단지 반복되는 조회문을 치환한 것이기 때문에, as 연산자가 쓰인다.

create view faculty as
select ID, name, dept_name
from instructor

뷰를 테이블처럼 이용해서 쿼리를 만들수 있다.

select name
from faculty where dept_name = 'Biology‘

뷰 만들때 view attribute 이름도 명시할 수 있다.

create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;

 

Views Defined Using Other Views

뷰 v1을 만들고 v2에서 이용된다면 v1 depend directly on v2 : v1 정의 할때 v2가 쓰였다면 v1 depend on v2 : v1 정의 할때 v2가 간접적으로 쓰였다면(예: v3를 쓰는데 v3가 v2에 의존 받는 다면) recursive : 자신을 의존하면

뷰 생성

create view physics_fall_2017 as
	select course.course_id, sec_id, building, room_number
	from course, section
	where course.course_id = section.course_id
		and course.dept_name = 'Physics'
		and section.semester = 'Fall'
		and section.year = '2017’;

기존 뷰에 조건을 추가해서 또다른 뷰 생성

create view physics_fall_2017_watson as
	select course_id, room_number
	from physics_fall_2017
	where building = 'Watson';

---

View Expansion

성능 분석을 위해서 만들때 썼던 뷰 이름를 쿼리 문으로 대체 하는 것이다.

알고리즘 주어진 뷰 -> 쿼리에서 뷰 이름이 있는지 찾는다 -> 뷰를 select 로 변경 -> loop

repeat
	Find any view relation vi in e1
	Replace the view relation vi by the expression defining vi
until no more view relations are present in e1

---

Materialized Views

• 일반 뷰는 가상의 테이블이고, 실제로는 정의된 쿼리를 실행할 때마다 데이터를 가져온다
• Materialized View는 아예 쿼리 결과 자체를 저장해 두는 것이다.
• 즉, 뷰를 정의할 때 결과를 물리적으로 복사해서 저장해 놓는 것이다.
• 기반 테이블이 바뀔 때마다 materialized view도 같이 업데이트(갱신) 해줘야하고 이것을 뷰 유지(Maintenance) 라고 해.
• 어떤 시스템에서는 자동으로 갱신, 어떤 시스템에서는 수동으로 REFRESH 명령어 사용.

---

Update of a View

뷰(faculty)에 새로운 튜플 추가하기 이전에 정의한 faculty 뷰에 새로운 튜플을 추가하려고 한다. 이 삽입은 결국 instructor 테이블에 삽입되어야 한다.

그런데 instructor 테이블에는 salary 값이 필수로 필요하다. 해결 방법은 두 가지

1. 삽입 거부하기 (Reject the insert) : salary 값이 없기 때문에, 삽입 자체를 허용하지 않음.
2. 불완전한 튜플을 instructor에 삽입하기 : salary를 null로 하여 튜플을 삽입한다. ('30765', 'Green', 'Music', null)

요약하자면, 뷰에 데이터를 삽입하려고 할 때, 그 뷰가 실제 테이블에 연결되어 있다면, 필수 컬럼이 빠졌을 경우 삽입을 거부하거나 불완전하게 삽입할 수 있다

---

Some Updates Cannot be Translated Uniquely

CREATE VIEW instructor_info AS
SELECT ID, name, building
FROM instructor, department
WHERE instructor.dept_name = department.dept_name;

이 뷰는 instructor와 department 테이블을 조인해서 각 교수(instructor)에 대해 해당 학과(department)의 건물(building) 정보를 함께 보여주는 뷰이다.

 

문제점 (Issues)

1. Taylor 건물에 여러 학과가 있는 경우 예를 들어 Taylor 건물에 Music, History 두 학과가 있다면: ‘White’ 교수는 어느 학과 소속인지 알 수 없어 → 모호함 발생
2. Taylor 건물에 학과가 아예 없는 경우 Taylor라는 건물은 있지만, 어떤 학과도 그 건물에 소속되어 있지 않다 → 어떤 dept_name과도 연결할 수 없어서 삽입 자체가 불가능

instructor_info 뷰는 instructor와 department을 조인한 결과이기 때문에, 삽입하려면 정확히 어떤 학과 인지 알아야하지만, 삽입할 때는 dept_name 정보가 없고, 건물 이름만 주어지면 매핑이 어렵거나 불가능할 수 있음.

이런 경우 대부분의 시스템은 삽입을 거부(reject) 한다.

 

And Some Not at All

create view history_instructors as
select *
from instructor
where dept_name= 'History';

만약 아래의 데이터를 뷰에 삽입한다면?

('25566', 'Brown', 'Biology', 100000)

→ History 학과 소속이 아닌 데이터

• 이 뷰는 기준이 되는 조건(dept_name = 'History')을 만족하지 않는 데이터를 삽입하려는 것이므로 일반적으로 오류(Error) 발생.
• 이유: history_instructors 뷰는 특정 조건이 적용된 필터링된 결과이기 때문에, 조건을 충족하지 않는 데이터를 삽입하면 기준 테이블인 instructor와의 일관성이 깨질 수 있음.
• 뷰가 업데이트 가능(view updatable) 하더라도, 삽입하려는 데이터가 뷰의 조건에 맞지 않으면 삽입이 허용되지 않음.

---

View Updates in SQL

Simple View에서만 가능하다 업데이트 (Update on Views)

대부분의 SQL 구현에서는 업데이트 가능한 뷰(View) 에 대해 아래와 같은 제약 조건이 있음.

업데이트 가능한 Simple View의 조건중 일부

1. FROM 절에는 하나의 테이블만 존재해야 한다. - 여러 테이블을 조인한 경우는 업데이트 불가능.
2. SELECT 절에는 순수한 속성 이름만 포함되어야 한다. - 계산식, 함수, 집계 함수(aggregate), DISTINCT 등이 포함되면 안 됨.
3. SELECT 절에 포함되지 않은 속성들은 → 업데이트 시 NULL 값으로 설정될 수 있다.
4. GROUP BY나 HAVING 절이 포함되어서는 안 된다. - 집계 그룹화가 있는 뷰는 업데이트 불가능.

---

Authorization on Views

뷰(View)를 통해 데이터 접근을 제어할 수 있으며, 사용 권한(authorization)은 뷰 단위로도 설정 가능하다.

 

1. 뷰 생성

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

→ instructor 테이블 중 Geology 학과 소속 교수들만 포함하는 뷰 생성.

 

2. 뷰에 권한 부여

grant select on geo_instructor to geo_staff;

→ geo_staff라는 사용자 그룹에게 geo_instructor 뷰에 대한 조회(select) 권한을 부여.

 

상황 1: geo_staff가 instructor 테이블에 권한이 없는 경우

• 정상 작동함.
• 뷰에 대한 select 권한만 부여되어 있으면, 기초 테이블(instructor)에 직접 접근 권한이 없어도 뷰를 통해 제한된 정보만 접근 가능.
• 이는 보안을 유지하면서 데이터 접근 범위를 세분화하는 데 매우 유용하다.

상황 2: 뷰 생성자가 instructor 테이블의 일부 컬럼이나 행에 대한 권한이 없는 경우

• 뷰 생성 자체가 실패하거나,
• 뷰 정의에 포함된 컬럼이나 행에 대해 권한이 부족하면 오류 발생.
• 뷰는 생성 시점에 기준 테이블에 대한 적절한 권한이 있는 사용자만 생성 가능