8.3 View tables

Creating views 뷰 생성

테이블 설계는 저장 공간 최소화, 빠른 쿼리 실행, 관계형 및 비즈니스 규칙 지원 등 다양한 이유로 최적화된다. 그러나 때로는 이러한 설계가 데이터베이스 사용자와 프로그래머에게 이상적이지 않을 수 있다. 예를 들어, Employee 테이블에는 이름, 결혼 여부, 생년월일 등 개인 정보가 포함될 수 있다. 별도의 Address 테이블에는 직원 주소가 포함될 수 있는데, 이 설계는 여러 직원이 동일한 주소를 공유할 수 있게 한다. 하지만 인사부 직원은 개인 정보와 주소 정보를 한 테이블에서 보고 싶어 할 수 있다.

 

뷰 테이블(View Table)은 이 문제를 해결한다. 뷰는 기본 데이터베이스 설계를 변경하지 않고 테이블 열과 데이터 유형을 재구성한다.

 

뷰 테이블은 SELECT 문과 연관된 테이블 이름으로, 이를 뷰 쿼리(View Query)라고 한다. CREATE VIEW 문은 뷰 테이블을 생성하고, 뷰 이름, 쿼리, 그리고 선택적으로 열 이름을 지정한다. 열 이름을 지정하지 않으면, 열 이름은 뷰 쿼리 결과 테이블의 열 이름과 동일하게 된다.

- CREATE VIEW statement

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement;

1. Department에는 Employee 테이블의 관리자를 식별하는 외래 키가 포함되어 있다.  
2. CREATE VIEW는 ManagerView라는 뷰 테이블을 생성한다. 뷰 쿼리는 SELECT 문이다.  
3. 뷰 쿼리는 Department와 Employee를 조인하고, EmployeeName을 ManagerName으로 이름을 변경한다.

---

Refer to the following CREATE VIEW statement and view table.

1) What is table name A?

MathFacultyView

2) What is column name B?

Professor

3) What is column name C?

DepartmentName

4) What is view column name D?

Code

5) What is value E?

MATH

---

Querying views 뷰 조회

뷰 쿼리의 FROM 절에 지정된 테이블을 기본 테이블(Base Table)이라고 한다. 기본 테이블 데이터와 달리, 뷰 테이블 데이터는 일반적으로 저장되지 않는다. 대신, 뷰 테이블이 SQL 문에 나타나면 뷰 쿼리가 해당 SQL 쿼리와 병합되고, 데이터베이스는 병합된 쿼리를 기본 테이블에 대해 실행한다.

 

일부 데이터베이스에서는 뷰 데이터를 저장할 수 있다. 물리적 뷰(Materialized View)는 데이터가 항상 저장되는 뷰이다. 기본 테이블이 변경될 때마다 해당 뷰 테이블도 변경될 수 있으므로, 물리적 뷰는 새로 고침이 필요하다. 뷰 새로 고침의 부담을 피하기 위해 MySQL을 포함한 많은 데이터베이스는 물리적 뷰를 지원하지 않는다.

용어 설명 뷰는 뷰 쿼리의 FROM 절에 추가적인 뷰 테이블을 포함할 때 다른 뷰 테이블을 기반으로 정의될 수 있다. 이 경우 추가된 뷰 테이블은 기본 테이블이 아니다. 기본 테이블들(Base tables)은 항상 소스 테이블들로, 뷰가 아닌 테이블들로 생성된 테이블들을 말한다.

 

1. 뷰 쿼리는 기본 테이블인 Department와 Employee를 조인하여 ManagerView 뷰 테이블을 생성한다.  
2. 사용자는 ManagerView 테이블에 대해 쿼리를 제출한다.  
3. 데이터베이스는 사용자 쿼리와 뷰 쿼리를 병합한다.  
4. 병합된 쿼리가 기본 테이블에 대해 실행된다. 결과는 Sales 부서의 관리자를 보여준다.

---

Refer to the following CREATE VIEW statement and view table.

1) What is missing to get all Chair names?

SELECT Chair 
FROM ______;

DepartmentView

2) What are the view query's base tables?

Faculty and Department

3) If the view table is not stored, against what tables is the query executed?

SELECT *
FROM DepartmentView;

Faculty and Department

4) If DepartmentView is a materialized view, against what tables is the query executed? (

same query as above)

DepartmentView

---

Advantages of views 뷰 사용의 장점

뷰 테이블은 다음과 같은 여러 장점이 있다:

• 민감한 데이터 보호: 테이블에는 민감한 데이터가 포함될 수 있다. 예를 들어, Employee 테이블에는 Salary와 Bonus 같은 보상 관련 열이 포함되어 있다. 뷰는 민감한 열을 제외하고 다른 모든 열을 포함할 수 있다. 뷰에 대한 접근 권한만 부여하고 기본 테이블에는 접근하지 못하게 함으로써 민감한 데이터를 보호할 수 있다.
• 복잡한 쿼리 저장: 복잡한 SELECT 문을 뷰로 저장할 수 있다. 데이터베이스 사용자는 SELECT 문을 작성하지 않고 뷰를 참조할 수 있다.
• 최적화된 쿼리 저장: 동일한 결과 테이블이 여러 SELECT 문으로 생성될 수 있지만, 성능은 문장에 따라 다를 수 있다. 빠른 실행을 보장하기 위해 최적의 문장을 뷰로 저장하고 데이터베이스 사용자에게 배포할 수 있다.

이와 같은 이유로 모든 관계형 데이터베이스에서 뷰를 지원하며, 데이터베이스 관리자가 뷰를 자주 생성한다. 데이터베이스 사용자는 뷰와 기본 테이블의 차이를 알 필요가 없다.

---

T/F

1) Using materialized views always improves database performance.

2) The performance of a user query on a view is identical to the performance of the corresponding merged query on base tables.

3) A view query can reference another view table.

4) In MySQL, two different queries that generate the same result table always have the same execution time.

5) Views can be used to hide rows as well as columns from database users.

▼View answers

F T T F T

---

Inserting, updating, and deleting views 뷰에서의 데이터 삽입, 갱신, 삭제

뷰 테이블은 일반적으로 SELECT 문에서 사용된다. 그러나 INSERT, UPDATE, DELETE 문에서 뷰를 사용하는 것은 문제가 발생할 수 있다:

1. 기본 키(Primary keys): 기본 테이블의 기본 키가 뷰에 포함되지 않은 경우, 뷰에 삽입할 때 NULL 기본 키 값이 생성된다. 기본 키는 NULL일 수 없으므로, 삽입이 허용되지 않는다.
2. 집계 값(Aggregate values): 뷰 쿼리에는 AVG() 또는 SUM()과 같은 집계 함수가 포함될 수 있다. 하나의 집계 값은 여러 기본 테이블 값에 해당한다. 뷰에 삽입 또는 업데이트할 때 새로운 집계 값을 생성할 수 있으며, 이 값을 여러 기본 테이블 값으로 변환해야 한다. 변환이 정의되지 않으므로 삽입 또는 업데이트가 허용되지 않는다.
3. 조인 뷰(Join views): 조인 뷰에서는 한 기본 테이블의 외래 키가 다른 기본 테이블의 기본 키와 일치할 수 있다. 뷰에서 삭제를 수행할 때 외래 키 행만 삭제할 수도 있고, 기본 키 행만 삭제하거나, 기본 키와 외래 키 행 모두를 삭제할 수도 있다. 조인 뷰 삭제의 결과는 정의되지 않으므로 허용되지 않는다.

위 예시는 뷰 테이블에서 데이터 변경 시 발생할 수 있는 많은 잠재적 문제 중 일부에 불과하다. 결과적으로 관계형 데이터베이스는 뷰 테이블에 대한 삽입, 업데이트, 삭제를 금지하거나 엄격히 제한한다. 특정 데이터베이스 제한과 관계없이, 뷰에 대한 삽입, 업데이트, 삭제는 피해야 하며, 뷰는 데이터를 읽는 데 최적이다.

1. Employee 테이블에는 급여와 보너스 정보가 포함되어 있다.  
2. 서로 다른 두 직원이 같은 이름을 가지고 있지만, 서로 다른 EmployeeID를 가지고 있다.  
3. CompensationView 테이블에는 총 보상이 포함되어 있다.  
4. 사용자가 작성한 쿼리가 Lisa Ellison의 보상을 업데이트하려고 한다. Lisa Ellison의 새로운 급여와 보너스는 정의되지 않았으므로, 쿼리는 거부된다.  
5. 사용자가 작성한 쿼리가 CompensationView에서 Sam Snead를 삭제하려고 한다. 기본 테이블에서 어떤 Sam Snead가 삭제되는가? MySQL에서 쿼리가 실행되지만 모호하다.

---

유효하지 않은 경우 )

INSERT INTO AccountView (DepartmentName)
VALUES ('Music Department');

INSERT 문은 기본 키 값을 지정하지 않았다. 기본 테이블의 기본 키는 NULL일 수 없으므로, 쿼리는 유효하지 않다.

UPDATE AccountView
SET Difference = 4400
WHERE Code = 'COMP';

UPDATE 문은 Difference 값을 기본 테이블 열인 Budget과 Actual에 어떻게 할당할지 명시하지 않았다.

유효한 경우 )

UPDATE AccountView
SET DepartmentName = 'Information Technology Department'
WHERE Code = 'COMP';

UPDATE 문은 기본 키를 지정하므로, 기본 테이블 행은 명확하다. 업데이트는 DepartmentName 열만 변경하므로 변경 사항에 모호함이 없다.

DELETE FROM AccountView
WHERE DepartmentName = 'History Department';

WHERE 절은 삭제할 기본 테이블 행을 명확히 나타낸다.

---

WITH CHECK OPTION 절

뷰 업데이트를 허용하는 데이터베이스는 특히 골치 아픈 문제에 직면할 수 있다. 뷰에 삽입 또는 업데이트할 때, 새로 생성된 행이 뷰 쿼리의 WHERE 절을 만족하지 않을 수 있다. 이 경우 삽입되거나 업데이트된 행이 뷰 테이블에 나타나지 않는다. 데이터베이스 사용자 관점에서는 기본 테이블이 변경되었음에도 불구하고 삽입 또는 업데이트가 실패한 것처럼 보일 수 있다.

 

이러한 실패로 보이는 삽입 또는 업데이트를 방지하기 위해, 뷰 업데이트를 지원하는 데이터베이스는 선택적으로 WITH CHECK OPTION 절을 사용할 수 있다. WITH CHECK OPTION이 지정되면, 뷰 쿼리의 WHERE 절을 만족하지 않는 삽입 또는 업데이트는 데이터베이스에서 거부된다. 대신, 데이터베이스는 위반 사항을 설명하는 오류 메시지를 생성한다.

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement
[ WITH CHECK OPTION ];

1. 두 명의 직원이 부서 51, Sales에 있다.
2. CREATE VIEW는 SalesEmployee 열에 대해 새로운 이름을 지정한다.
3. SalesEmployee 뷰는 부서 51에 있는 직원만 포함한다.
4. 사용자 쿼리는 부서 80의 새로운 직원을 뷰 테이블에 삽입하려고 한다.
5. 새로운 직원은 Employee 테이블에 삽입되지만, 뷰 테이블에는 나타나지 않는다.
6. WITH CHECK OPTION은 뷰 쿼리 WHERE 절을 만족하지 않는 삽입 및 업데이트를 방지한다.

---

▼View solution

(1), (2)

CREATE VIEW 문에서 WITH CHECK OPTION이 지정되면, WHERE 절을 위반하는 INSERT 및 UPDATE 문은 실패한다.

(1) `INSERT INTO CountryView VALUES('Myanmar', 'Asia', 53708395);`  
Population > 550000이므로 성공한다.

(2) `INSERT INTO CountryView VALUES('India', 'Asia', 1352617328);`  
Population > 550000이므로 성공한다.

(3) `UPDATE CountryView SET Continent = 'Africa', Population = 543767 WHERE Name = 'Cape Verde';`  
뷰 테이블은 유럽에 속한 국가 또는 인구가 550000 이상인 국가만 포함하므로 실패한다.