과제1. SQL Basic 이해하기
Q. 다음 표를 보고 아래 질문에 답하시오.
💡 문제를 풀기 전에 integrity contraints에 대해 간단하게 정리해보자.
1. entity integrity constraints
- pk값은 null이 아니여야 한다.
- pk값은 중복되지 않는 값이여야 한다.
2. referential integrity constraints
- fk값은 관계성을 갖는 레코드의 유효한 PK 값이여야 한다.
Answers.
1)
PROJECT 테이블은 Pnumber를 PK 칼럼으로 갖고 있으며, Dnum은 Department 테이블의 PK값을 FK로써 가지고 있다. 이 때, 주어진 Insert 문 insert <'ProductA', 4, 'Bellaire', 2> into PROJECT에 따르면 Pnumber는 4로 입력되기 때문에 null도 아니고 중복되지도 않는 하나의 PK이므로 개체 무결성 제약조건은 충족한다. 하지만, Project 테이블의 FK인 Dnum은 참조 무결성 제약조건에 따라 관계성을 갖는 테이블의 PK 값 중 하나를 나타내야 하며, 참조할 수 없는 값이여서는 안된다. 하지만 2라는 정수값은 Department 테이블에 존재하지 않는 값이므로 참조 무결성 제약조건을 위배한다.
2)
WORKS_ON 테이블은 관계를 맺는 테이블과의 두 FK 칼럼을 복합키로써 하나의 PK로 활용되고 있다. 이 때, Project number id 값을 Pno라는 칼럼에 받고 있는데, 위 명령어 insert <'677678989', NULL, '40.0'> into WORKS_ON 이라고 되어 있으므로, NULL 값을 Pno에 입력하려고 하고 있는 것을 확인할 수 있다. 복합키를 활용되는 모든 칼럼은 단 하나의 칼럼이라도 Null이 입력되면 개체 무결성 제약조건을 위배하는 것이므로 이 명령어는 개체 무결성 제약조건을 위배한다. 또한 ESSN은 합리적으로 판단하건데, Employ Number이므로 참조 무결성 제약조건에 따라 참조할 수 있는 값이 입력되어야 한다. 하지만 ‘677678989’라는 값은 Employ테이블의 Ssn값과 비교했을 때, 존재하지 않으므로 참조 무결성 제약조건을 위배한다.
3)
DEPENDENT 테이블은 Dependent_name을 PK로, Essn을 FK로 갖는 테이블이다. 이 때, 제시된 insert <'453453453', 'John', 'M', '1990-12-12', 'spouse'> into DEPENDENT. 를 수행할 경우엔 Employee 테이블의 JOYCE가 ‘453453453’의 ssn을 갖고 있으므로 참조할 수 있는 값이 FK 칼럼에 입력되는 것이니 참조 무결성 제약조건을 충족하고 있다. 또한 ‘John’이라는 값이 null이 아님은 물론, DEPENDENT의 PK 중에서 중복되는 값이 아닌 유일한 값이므로 개체 무결성 제약조건 또한 충족하고 있다. 따라서 해당 명령어는 수행에 문제가 없다.
4)
PROJECT 테이블은 WORKS_ON 테이블과 관계성을 가지고 있으며, WORKS_ON 테이블에서 Project Number 칼럼을 참조하고 있음을 확인할 수 있다. 이러한 상황에서, 주어진 명령어 delete the project tuple with Pname='ProductX'를 수행하게 되면 Pname칼럼에 ‘ProductX’라는 값을 가진 튜플이 PROJECT 테이블에서 지워질 것이다. 하지만 이렇게 관계성을 가진 테이블의 튜플을 지우기 위해서는 참조 무결성 제약조건에 영향을 끼치지는 않을지 확인해봐야 한다. 주어진 상황에서는 ProductX라는 값을 Pname 칼럼에 가진 레코드의 Pk 값이 1인데, 이는 WORKS_ON 테이블의 두 레코드가 참조하고 있는 값이므로, 위 명령어를 수행하게 되면 WORKS_ON 테이블에서 참조하던 PROJECT 테이블의 PK값이 사라지게 되는 것이므로 참조 무결성 제약조건이 위배된다. WORKS_ON 테이블에서의 개체 무결성 제약조건은 위 명령어를 수행한다고 해서 갑자기 null값으로 설정된 것도 아니고, 중복되는 값이 발생하는 것도 아니므로 개체 무결성 제약조건은 충족된 상태이다.
5)
EMPLOYEE 테이블은 각각의 레코드가 EMPLOYEE 테이블의 다른 레코드 PK를 FK로 참조하는 구조이다. 이때, 주어진 명령어 modify the super_ssn attribute of the EMPLOYEE typle with ssn = '999887777' to '943775543'.이므로 ssn이 ‘999887777’인 레코드의 FK값인 '987654321'을 '943775543'으로 바꾸겠다는 명령어이다. 이를 원활하게 수행하기 위해서는 '943775543'이라는 값이 또다른 EMPLOYEE 테이블 레코드에 할당되어 있어야 하는데, 그 어떤 레코드도 '943775543'이라는 값을 PK로 가지고 있지 않아, 이를 설정하려고 하게 되면 참조할 수 없는 값이 FK에 할당되는 것이니 참조 무결성 제약조건을 위배한다. 반면, 이 레코드의 PK는 null이 아니고 중복되지 않는 값으로 PK가 설정되어 있으므로 개체 무결성 제약조건은 충족된 상태 그대로 유지되어 있다.